РАБОТА С ФОРМУЛАМИ И ДИАПАЗОНАМИ

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

• Любая формула, как и в Excel, вводится со знака «равно».
• Текст указывается в кавычках, после названий листов ставится восклицательный знак, названия листов берутся в апострофы, если в них есть пробелы (‘Название листа’!A1).
• Аргументы функций разделяются символом (каким именно — зависит от региональных настроек), для России это точка с запятой.
• Если у вас в настройках выбран английский язык, то вы все равно можете вводить привычные русские названия, но всплывающих подсказок в этом случае не будет. После ввода русские названия будут автоматически заменены на английские.

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

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

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

По ссылке вы найдете 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. Тогда вам не придется каждый раз менять формулы.

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

A:A — весь столбец A

3:3 — вся строка 3

Итак, вы указали обычный закрытый диапазон — до 12-й строки:

И после добавления данных в 13-ю результат расчета формулы не изменится:

С открытым диапазоном таких проблем не будет:

Почему в примере (да и вообще) лучше использовать C2:C, чем весь столбец C:C? Чтобы не учитывать заголовок.

Он может означать год, и тогда (типичная ошибка) к общей сумме добавится, например, 2016:

ИМЕНОВАННЫЕ ДИАПАЗОНЫ

Как и в Excel, диапазонам в Таблицах можно присваивать имена. Именованные диапазоны делают формулы наглядными, ведь вместо

=A7*$E$1

вы будете видеть

=Продажи*­Налог

Чтобы задать диапазону имя, выделите его и нажмите на кнопку Именованные диапазоны в разделе меню Данные (или сначала вызовите окно Именованные диапазоны, а потом выделите то, чему будете присваивать имя):

После чего справа в появившемся окне введите название диапазона и нажмите Готово:

После присвоения диапазонам имен вы можете использовать их в формулах:

КАК ПРАВИЛЬНО ПИСАТЬ УСЛОВИЯ В ФОРМУЛАХ И ИСПОЛЬЗОВАТЬ СИМВОЛЬНЫЕ ШАБЛОНЫ

Небольшое пояснение, как писать условия в формулах, чтобы все работало.

• Числовые и текстовые условия, знаки >, <, = нужно писать в кавычках (кстати, не все знают, что сочетание <> обозначает неравенство):
  • например, так нашим условием будет Вася
    
  • а так все, кроме Васи
    
  • Если вам нужно объединить несколько элементов в условии формулы, скажем, адрес ячейки и знак >= или формулу и текст, используйте амперсанд (&)
    
• В условиях некоторых формул (СЧЁТЕСЛИМН (COUNTIF), СУММ­ЕСЛИМН (SUMIFS) и других) можно использовать символьные шаблоны * и ?:
  • * — любая последовательность любых символов;
  • ? — любой один символ.
• Например, Василий* будет означать, что формула отберет Василиев с любыми фамилиями (и без них), а если написать В?силий, то условием будут все В(любая буква)силий в наших данных.
  • если вам нужно, чтобы символьные шаблоны * или ? формула восприняла как обычный текст, поставьте перед ними тильду ~:

ВЫЧИСЛЕНИЕ ЧАСТИ ФОРМУЛЫ В СТРОКЕ ФОРМУЛ

Разбирать чужие (а иногда и свои, но забытые) сложные формулы — то еще удовольствие. Особенно если они «многоэтажные» и состоят из многих вложенных друг в друга функций.

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

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

Примечание: в Excel это тоже работает — необходимо не только выделить фрагмент формулы, но и нажать F9 после этого. Важно, что в конце нужно нажать Escape, а не Enter, иначе этот фрагмент так и останется числом, а не функцией/диапазоном. В Google Таблицах этого нет.

Содержание формулы в данном примере не играет решающей роли, но ниже будет дан краткий комментарий по поводу того, что в ней происходит.

По ссылке — файл с примером. Копируйте на свой диск, изучайте формулу, играйтесь: .

Эта формула (далее мы подробно рассмотрим такие конструкции) возвращает название книги с N-ми по счету продажами, где N берется из ячейки D3. То есть, вводя туда число 4, вы задаете вопрос: «Какая книга занимает 4-е место в рейтинге продаж?».

Формула работает так: функция LARGE (НАИБОЛЬШИЙ) находит N-е по величине значение (в примере 310 — 7-я по счету величина). Функция MATCH (ПОИСКПОЗ) находит порядковый номер строки, в которой находится это значение (1-я строка в диапазоне), а функция INDEX (ИНДЕКС) возвращает значение, стоящее в этой строке, но в диапазоне A3:A14 (названия книг).

НЕСКОЛЬКО ПРАВИЛ ОФОРМЛЕНИЯ ТАБЛИЦ И РАБОТЫ С ДАННЫМИ

1.-Используйте разделители разрядов: не 12948224, а 12 948 224:

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

Сравнение может быть абсолютным (в отделе стало работать на 3 человека больше) и относительным (было 6 сотрудников, а стало 9: прирост на 50%!).

Ни то ни другое сравнение не показательно само по себе.

Используйте оба.

Приросты считаются так:

Абсолютный прирост = текущий показатель – базисный показатель (прошлый месяц, аналогичный месяц прошлого года и т. д.).

Относительный прирост = текущий показатель / базисный показатель –1.

Итого: смотрим на динамику в рамках года и изменение год к году — в относительных и абсолютных величинах.

3.-Управленческая информация должна быть актуальной и «живой». Что это значит? Например, что ее нельзя хранить в форматах PDF или Word, потому что тогда вы не сможете:

• быстро обновить данные;
• поменять одну из переменных и посмотреть на то, как это изменение отразится на итоговых показателях (провести сценарный анализ);
• добавить новые показатели.

Все это возможно в Таблицах, где легко обрабатывать и обновлять данные. Вы можете извлечь данные из таблицы и представить в наглядном отчете в PDF; но помните, что ни одно изменение в исходных данных в этом отчете не отобразится.

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

4.-Не стоит в аналитических и финансовых расчетах (речь, конечно, про управленческую отчетность и внутренние расчеты, а не про бухгалтерию) стремиться к абсолютной точности, к 3–4 знакам после запятой.

Станет ли более взвешенным решение, если вы будете знать о росте показателя на 1,247% вместо того, чтобы знать о его росте на 1,2%? Стоит ли более точный расчет того времени и внимания, которое на него потратите вы / аналитик / маркетер / кто-либо другой из ваших коллег?

5.-Не забывайте закреплять заголовки таблиц, в которых много данных:

Отправить