Таблицы

Автозаполнение и адреса ссылок

Урок 4. Оптимизация работы формул

В предыдущих уроках вам нужно было создать отчёт за два месяца. После того, как написали нужные формулы для первого месяца, вы, возможно, пробовали сделать так:

А хотели, чтобы получилось вот так:

Почему в первом случае получается 0, а во втором всё работает корректно?

В результате этого урока вы не только узнаете ответ на этот вопрос, но и научитесь делать вот так:

Основы работы с ячейками

Объяснить логику работы Google Таблиц при протягивании или копировании ячеек проще всего на примере.

В ячейке E1 таблицы напишем простую формулу =A1:

Протянув эту формулу вниз и вправо, увидим следующие значения:

То есть при протягивании вниз меняется адрес строки:

А при протягивании вправо меняется адрес столбца:

То же самое происходит при копировании формулы. На сколько ячеек переместили формулу, на столько значений и поменяется ссылка на ячейку.

Этим свойством смены адресов вы уже пользовались в первом уроке, когда писали формулу, растягивали её вниз и сразу видели сумму по каждой категории доходов и расходов. Но это же самое свойство мешает, когда вы протягиваете формулу вправо или влево.

Например, исходная формула для подсчёта доходов выглядела так:

=SUMIFS(Export!L:L;Export!C:C;"Доход";Export!G:G;6)

Когда вы протягиваете её на одну клетку вправо, она меняется на эту:

=SUMIFS(Export!M:M;Export!D:D;"Доход";Export!H:H;6)

Смещается каждый диапазон. Поэтому формула и выдаёт значение 0: в диапазоне Export!M:M находятся пустые ячейки, в Export!D:D — нет строк со значением "Доход" , а в Export!H:H нет строк со значением 6.

Чтобы так не происходило, используйте функцию закрепления.

Закреплённая ячейка выглядит так: $A$1.
Закреплённый диапазон — так: $L:$L или $L$1:$L$100.

Знак доллара обозначает закрепление. Если внутри формулы есть закреплённый адрес, то как бы вы ни растягивали и ни копировали формулу, она всегда будет брать значения из указанных ячеек или диапазонов.

В ваш салон периодически приезжают с мастер-классами зарубежные стилисты, которые специализируются на стрижках для кудрявых. Вам нужно оплачивать гонорар, проезд и проживание мастера. Сложность в том, что все расчёты производятся в долларах, а у вас в кассе рубли.

Скопируйте таблицу с заданием и выполните все шаги инструкции.

Евгений

Готово!

Таблица для самопроверки

Только что вы использовали несколько горячих клавиш. Соотнесите клавиши с функциями, которые они выполняют.

Сочетания клавиш указаны для Windows, но пользователи macOS знают, что с этим делать.

Заполняет диапазон значениями из первой строки диапазона.

Ctrl+D

Да! А если выделить не диапазон, а пустую ячейку и нажать Ctrl+D, то в неё скопируется значение или формула из ячейки выше.

Заполняет ячейку значениями из ячейки, стоящей слева.

Такой клавиши не было в задании.

Действительно, этой полезной горячей клавиши пока не было. Скоро узнаете, как она работает.

Закрепляет ячейку.

Использовать эту клавишу гораздо удобнее, чем два раза вставлять значок $.

Выделяет диапазон.

Shift

Вы правы. Можно, конечно, выделить диапазон с помощью мышки, но иногда через Shift быстрее.

Что именно вы только что сделали?

Поскольку у стилистов разные гонорары, а курс доллара один для всех, вы использовали вычисления с двумя переменными: D7 (гонорар) и $D$2 (курс доллара).

Переменная D7 динамическая — в каждой следующей строчке она меняет значение на D8, D9…D14, а при копировании формулы в ячейки G7 и I7 смещается на нужное количество столбцов, превращаясь в F7 и H7 соответственно.

Переменная $D$2 фиксированная — адрес она не меняет. Использовать фиксированную переменную в подобных расчётах очень удобно: когда курс доллара изменится, достаточно исправить значение в ячейке D2, и все суммы пересчитаются.

Евгений

Закреплять можно только всю ячейку целиком?

Не только. Адрес ячейки — это название столбца и номер строки, на пересечении которых она находится. Почти как улица и номер дома.

Фиксировать можно не только весь адрес ячейки, но и отдельную его часть. Всего три варианта закрепления:

$B$1 — полный адрес ячейки. Одно нажатие F4.
B$1 — только номер строки. Два нажатия F4.
$B1 — только название столбца. Три нажатия F4.

В документе из задания 1 откройте скрытый лист «Задание 2» и следуйте инструкции в красной рамке.

Чтобы открыть лист, нажмите на полосатый квадратик в левом нижнем углу экрана.

Евгений

Готово!

💡 Вот вы и нашли недостающую клавишу.

Ctrl+R (⌘+R) работает как Ctrl+D, только вправо. Либо заполняет ячейку по аналогии с левым соседом, либо — весь выделенный диапазон по подобию ближайших ячеек с левой стороны.

Получается, закрепление части ячейки работает следующим образом.

В формуле =B7*C$6 закреплена шестая строка. В таблице «Закрепление строки» переменная C$6 принимает только пять значений: C6, D6, E6, F6, G6. Когда протягиваете формулу:

вниз — переменная продолжает ссылаться на ячейку C6;
вправо — меняется адрес столбца: D6, E6, F6, G6.

В формуле =$B17*C16 закреплён столбец B. В таблице «Закрепление столбца» переменная $B17 принимает только пять значений: B17, B18, B19, B20, B21. Когда протягиваете формулу:

вниз — меняется адрес строки: B18, B19, B20, B21;
вправо — адрес ячейки не меняется, и переменная продолжает ссылаться на B17.

💡 Запомнить правила закрепления просто:

Если $ стоит перед буквой, то не меняется буква.
Если $ стоит перед числом, то не меняется число.

Откройте скрытый лист «Задание 3».
В таблице записаны смены мастеров вашего салона: 1 — мастер работал в этот день, 0 — не работал. Также указаны оклады, сколько сотрудник получает за смену.
Напишите формулу с двумя переменными, которая будет считать доход каждого мастера в определённый день месяца.
Важно! Вам нужно написать одну формулу и растянуть её на все ячейки.

Теперь вернёмся к отчёту из первого урока.

Скопируйте таблицу с данными о доходах и расходах за 6 месяцев.
На листе Report есть готовый шаблон отчёта и даже написаны некоторые формулы.
Используя знания о закреплении адресов ячеек, измените формулы так, чтобы их можно было протянуть вправо и заполнить отчёт за все шесть месяцев.

Евгений

Проверить

Таблица для самопроверки

Чтобы сделать много вычислений одной формулой, в качестве аргументов используйте переменные, а не абсолютные значения.
Переменные можно делать динамическими C12, полностью фиксированными $C$12 или частично фиксированными: $C12 — зафиксирован столбец, C$12 — зафиксирована строка.
Горячие клавиши ускоряют работу с таблицами. Весь список горячих клавиш можно увидеть, нажав в таблице Справка → Быстрые клавиши.

💡 Немного постмодернизма: в Google Таблицах есть горячая клавиша для вызова списка горячих клавиш — Ctrl+/ (⌘+/).

2 — 2