Читать интересную книгу Google Таблицы. Это просто. Функции и приемы - Евгений Намоконов

В таких случаях я предлагаю следующее решение: написать скрипт, который будет вставлять формулы в требуемые ячейки, а потом сразу же заменять их на значения. Такой скрипт можно запускать как вручную, так и по расписанию, скажем, каждые два часа, и в этом случае необязательно находиться в файле – скрипт отработает в офлайн-режиме.

Работа с формулами и диапазонами

НЕСКОЛЬКО БАЗОВЫХ ПРАВИЛ

• Любая формула, как и в Excel, вводится со знака «равно».

• Текст указывается в кавычках, после названий листов ставится восклицательный знак, названия листов берутся в апострофы, если в них есть пробелы (‘Название листа’!A1).

• Аргументы функций разделяются символом (каким именно – зависит от региональных настроек), для России это точка с запятой.

• Если у вас в настройках выбран английский язык, то вы все равно можете вводить привычные русские названия, но всплывающих подсказок в этом случае не будет. После ввода русские названия будут автоматически заменены на английские.

РАЗБИРАЕМ НА ПРИМЕРЕ СУММЕСЛИ (SUMIF), КАК ЗАДАТЬ (ВЫБРАТЬ) В ФОРМУЛЕ ДИАПАЗОНЫ И УСЛОВИЯ

Эта глава будет полезна тем, у кого совсем мало опыта в написании формул. В ней я по шагам расскажу, как начать формулу; как выбрать диапазон; что делать, если он на другом листе; что нажать, чтобы перейти от выбора диапазона к условию; как сделать ссылки абсолютными и не потратить на все это слишком много нервов.

Возьмем простую формулу СУММЕСЛИ (SUMIF).

По ссылке https://goo.gl/1dIZMI вы найдете Google Документ с примером, на котором можно потренироваться. Для редактирования выберите:

Файл → Создать копию.

Начнем.

Чтобы немного усложнить себе задачу, вводить формулу мы будем на одном листе, а диапазон суммирования и диапазон условия – на другом (оба листа должны находиться в одном документе – в отличие от Excel, где можно ссылаться и на другие книги).

Формулы всегда начинаются со знака «равно».

Итак, выделяем ячейку В2 и начинаем вводить формулу. Уже после нескольких символов =СУ появляются варианты формул с этим слогом в названии, выбираем мышкой СУММЕСЛИ и кликаем на нее:

Видим вот такое окно (формулу можно писать как в самой ячейке, так и в строке формул – это не принципиально):

Под формулой видим окно справки. В нем цветом подсвечивается тот элемент, который нужно ввести сейчас. У нас это диапазон условия (если справка не открылась, нажмите на? под формулой).

Выбираем лист «Диапазоны» и выделяем диапазон условия – для этого кликаем на его первой ячейке и «протягиваем» до последней (в данном примере это С1:C7). Выделять ячейки можно и в обратном порядке: начать с С7 и протянуть до С1; или можно кликнуть на названии столбца С, и он выберется целиком.

Если вам мешает справка формулы, то закройте ее, нажав на крестик. Если все равно что-то мешает и никак не получается выбрать нужный диапазон, как B1:B7 на скриншоте ниже, – его можно ввести с помощью клавиатуры, прямо в строке формул (не забывайте, что буквы в ссылках Таблиц латинские).

Диапазон выбран, но по умолчанию он будет относительным, то есть при копировании формулы сместится вслед за ней. Чтобы этого избежать, нажмите на клавиатуре F4. Теперь в адресе появились символы $, и он стал абсолютным – это означает, что и строки, и столбцы зафиксированы, ничего сдвигаться не будет.

(Если нажать F4 еще раз, то зафиксируются только строки, при повторном нажатии – только столбцы.)

Мы выбрали диапазон условия. Вводим точку с запятой (;), этот символ отделяет аргументы формулы друг от друга. Теперь нужно выбрать ячейку с условием.

Кликнем на вкладку Сводный отчет и на ячейку А2. Не будем делать условие абсолютным, так как планируем скопировать формулу на ячейку ниже и нам нужно, чтобы условие с А2 поменялось на А3.

Вводим точку с запятой, возвращаемся на вкладку Диапазоны и выбираем последний диапазон суммирования. Его тоже сделаем абсолютным.

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

Формула готова, теперь скопируем ее из ячейки B2 в ячейку В3 (можно через пункты меню Копировать и Вставить, можно кликнуть на В2 и протянуть на ячейку ниже, можно использовать сочетания клавиш Ctrl + C, Ctrl + V):

АБСОЛЮТНЫЕ, ОТНОСИТЕЛЬНЫЕ И СМЕШАННЫЕ ССЫЛКИ. ФУНКЦИЯ ДВССЫЛ (INDIRECT)

Ссылки в Таблицах, как и в Excel, могут быть абсолютными и относительными.

Ровно так же они изменяются с помощью клавиши F4 или путем ввода знака доллара перед номером строки и/или перед номером столбца.

Читать этот подраздел дальше имеет смысл, если вы не работали с абсолютными и относительными ссылками в Excel или хотите повторить этот материал.

Относительные ссылки – это обычные ссылки вида A1, D10, AX127, которые по умолчанию появляются при щелчке на ячейку во время ввода формул. Такие ссылки смещаются вместе с формулой. Они являются относительными, так как ссылаются не на конкретную ячейку, а на ячейку, отстоящую на N строк и M столбцов от той, в которую введены. Так, если вы ввели в ячейку B1 формулу

=A1

то вы ссылаетесь не на A1, а на ячейку слева от ячейки с формулой, или отстоящую от нее на -1 (минус один) столбец и 0 (ноль) строк.

Поэтому при копировании этой формулы в ячейку C4 она будет выглядеть следующим образом:

=B4

то есть по-прежнему ссылаться на ячейку слева от себя.

Это не всегда подходит для ваших задач.

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

Уже во второй строке формула ссылается не на нужную ячейку, а на пустую ячейку под ней.

Для таких случаев используются абсолютные ссылки. Абсолютная ссылка – это ссылка вида $A$1 или $B$5, которая не изменяется при перемещении формулы:

Ссылку можно сделать абсолютной, нажав F4 при вводе формулы (или щелкнув курсором на ссылку в строке формул). Последовательно нажимая F4, вы будете перебирать все 4 возможные комбинации:

A1 → $A$1 → A$1 → $A1 → A1.

A$1 и $A1 – это смешанные ссылки, в которых закреплена только строка или только столбец. Например, для составления обычной таблицы умножения требуются именно такие ссылки в формуле, ведь нам нужно перемножать числа из первой строки (фиксируем ее) и первого столбца (фиксируем столбец A):

На скриншоте ниже видно, как меняются адреса разных типов, если мы вводим их в столбец А и копируем в столбцы С, D и Е:

Абсолютная ссылка тоже может изменяться. Например, если вы добавите строку в примере выше, ссылка по-прежнему укажет на ячейку с 20 %, но это будет уже не $E$1, а $E$2:

В большинстве случаев такая ситуация приемлема.

Если вы хотите создать по-настоящему абсолютную ссылку, всегда указывающую на A1 даже при изменении строк, воспользуйтесь функцией INDIRECT (ДВССЫЛ). Ее единственный аргумент – адрес ячейки:

=INDIRECT("A1")

=ДВССЫЛ("A1")

Теперь при вставке строки результат вычисления обнулился, так как формула не стала ссылаться на E2, а осталась на E1:

ТИПЫ ССЫЛОК A1 И R1C1

Стиль ссылок А1. Является классической системой адресации в электронных таблицах; как в MS Excel, так и в Google Таблицах сначала идет имя столбца, потом – номер ячейки.

Стиль ссылок R1C1. В этой системе строки (ROW) и столбцы (COLUMN) обозначаются цифрами. Например, R3C2: 3-я строка и 2-й столбец – ячейка B3.

В Google Таблицах этот стиль используется редко, например в формуле ДВССЫЛ (INDIRECT). С помощью этой формулы мы приводим текстовое наименование адреса ячейки, записанное в стиле A1 или R1C1, в вид настоящей ссылки на ячейку, и формула возвращает содержимое этой ячейки.

Синтаксис ДВССЫЛ (INDIRECT) состоит из двух аргументов: адреса ячейки и стиля адреса ячейки. Если вы хотите использовать в этой формуле стиль R1C1, то поставьте 0 во втором аргументе.

На скриншоте ниже подробнее:

ДИАПАЗОНЫ ВИДА A2:A

Скорее всего, со временем ваши таблицы будут пополняться и обновляться путем добавления новых строк.

Если при этом на листе с таблицей нет никаких других данных, кроме заголовков полей (столбцов) и данных под ними (то есть нет нескольких таблиц, расположенных одна под другой), есть смысл указывать в аргументах открытые диапазоны вида A2:A, а не A2:A100. Тогда вам не придется каждый раз менять формулы.

Можно указывать столбцы/строки целиком, если для вас приемлемо включить заголовки в расчет: