Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

Так как у нас в качестве обрабатываемого массива выступает столбец «Выручка», мы не можем напрямую в LAMBDA ссылаться на столбец «Кто работал»: его в SCAN нет. Но мы можем ссылаться на другие ячейки относительно ячеек обрабатываемого массива — тут пригодится функция СМЕЩ / OFFSET. Чтобы ссылаться на ячейку слева от заданной (в нашем случае это очередная ячейка массива), нам нужна такая конструкция (смещение на один столбец влево, «минус один» в аргументе СМЕЩ):

СМЕЩ(ячейка из массива;0;-1;1;1)

Вся формула будет выглядеть так. Здесь 0 — начальное значение; Выручка2[Выручка] — ссылка на столбец с данными; lemur — накопленный итог; revenue — очередное значение из массива (выручка за конкретную дату); СМЕЩ(…) — ссылка на ячейку слева от ячейки с выручкой:

=SCAN(0;Выручка2[Выручка];

LAMBDA(lemur;revenue;

ЕСЛИ(СМЕЩ(revenue;0;-1;1;1)="Лемур";revenue + lemur;lemur)))

Отладка формул и ошибки

Какие бывают ошибки в формулах

Начнем с ошибки ИМЯ и на ее примере обсудим, как ошибка может передаваться дальше по цепочке формул.

#ИМЯ! / #NAME! — ошибка в имени функции, именованном диапазоне, ссылке на диапазон. Пробегитесь по всем этим пунктам в вашей формуле. Кроме того, не забывайте, что текстовые значения указываются внутри формул в кавычках.

В следующем примере — ошибка в названии функции, не хватает второй буквы «м».

Если в ссылке на ячейку/диапазон ввести кириллические буквы вместо латинских, тоже будет ошибка, хотя внешне ссылка выглядит корректно.

Помните, что ошибки в формулах могут быть и в том случае, если эти ошибки есть в ячейках, на которые формулы ссылаются. Надо разматывать всю цепочку. На следующем скриншоте в формуле нет ничего криминального: к ячейке A4 прибавляем число 10. Но в ячейке A4 ошибка #ИМЯ? — она и отображается в результате расчета новой формулы.

Отследить, на какие ячейки ссылается формула, можно следующими способами.

1. Войти в режим редактирования формулы (двойной щелчок по ячейке или F2 или щелчок по строке формул).

Ссылки выделяются цветом — в формуле и собственно на листе

Клавиша F2 при работе с формулой переключает два режима: либо клавиши со стрелками перемещают курсор в самой формуле, либо позволяют ссылаться на ячейки, двигаясь от одной к другой с помощью стрелок и сочетаний (Ctrl + стрелки — перемещение до конца диапазона, Ctrl + Shift + стрелки — выделение до конца диапазона).

2. Включить режим «Показать формулы», который мы вкратце упоминали ранее, — на вкладке ленты «Формулы». Будут видны все формулы, значения будут видны «как есть» (без форматирования). Ссылки будут выделяться цветом для формулы в активной ячейке.

3. Стрелки трассировки: там же на вкладке «Формулы» — «Влияющие ячейки» (Trace Precedents).

Нажимаем один раз и видим, куда ссылается формула (на ячейку B4).

После второго нажатия увидим, куда ссылается формула в B4.

Чтобы убрать стрелки, нажмите соответствующую кнопку на той же вкладке: Убрать стрелки (Remove Arrows).

Рассмотрим другие ошибки в формулах.

#ЗНАЧ! / #VALUE! — арифметические операции с разными типами данных — сложение текста и чисел.

#ССЫЛ! / #REF — ссылка на несуществующий диапазон. Появляется, когда ячейка, на которую ссылалась формула, была удалена (вместе со строкой или столбцом, например) или когда вы пытаетесь, протягивая формулу, сослаться на ячейку A0, например (то есть на ячейку за пределами листа). Также возникает, если в функции СМЕЩ / OFFSET высота или ширина диапазона задана как ноль, что невозможно.

В ячейке B4 ссылка на A1. При попытке протянуть такую формулу вверх появляется ошибка #ССЫЛ! так как выше уже ничего нет

Циклическая ссылка (в Google Таблицах выглядит как #ССЫЛ! / #REF!). Возникает, если формула ссылается на собственное значение. При возникновении смотрите на диапазоны в формуле (бывает, что вы ссылаетесь на весь столбец, а формула стоит в нем же под таблицей, и т. д.)

В Excel при попытке ввести формулу с циклической ссылкой появится сообщение об ошибке (оно же будет появляться при открытии книги, в которой есть хотя бы одна циклическая ссылка):