Начало тут
Середина тут

III. Пойми, что может Excel!

Вот прям как в знаменитом меме «А что, разве так можно было?!!!»

Профессионалы Экселя не знают программу. Они её ПОНИМАЮТ!

Я же не сумасшедший учить все формулы, функции, фишки и инструменты в Экселе или Гугл таблицах! И ты не псих. Но ты обязан понимать, что есть формулы/функции. Что они могут работать с числами, с текстом, с таблицами, с массивами, с датами, с логическими операторами. Ты должен понимать, что есть разделы формул, выполняющие различные операции. Ты должен понимать СУТЬ работы формул Экселя. Ты понимать, что Эксель может обрабатывать большие объёмы через Power Querry и Power Pivot с мерами. Нужно понимать, что многое можно автоматизировать через макросы и то, что Эксель может самостоятельно их записывать. Нужно понимать, что многие действия из верхней панели можно делать короткими сочетаниями клавиш и что всю верхнюю панель можно настраивать под себя. Нужно понимать разницу между тем что ты видишь отображённым на экране и тем, что происходит внутри программы.

Ты должен понимать, ЧТО УМЕЕТ ЭКСЕЛЬ! Не уметь, а именно понимать. И тогда, когда припрёт, ты сможешь! Сможешь найти нужную функцию и в целом решение.

У меня есть красивый пример. В одном обучающем видео про эксель на ютубе некий индус произнёс фразу «В Excel невозможно создать колонку с нарастающим итогом введя всего одну формулу» (если не брать новые функции Эксель 365). И меня это раззадорило. Я понятия не имел, можно ли это сделать. Но я понимал Эксель! И что даже более ранние версии программы могут работать с массивами. Просто надо хитрить.

И вот! В результате 2-х часов поисков решения получилась таки формула!

Ради интереса, рекомендую решить задачку самому. Если у тебя получится – значит ты действительно понимаешь Эксель! Ну а я в комментарии выложу решение.

Умение хитрить с формулами в Эксельке можно сравнить с пониманием того, что молоток это не только про забивать гвозди. А плоскогубцами вообще можно творить чудеса.

Про весь Эксель в рамках кратких статей рассказывать смысла нет, так как там миллионы нюансов на каждом этапе, но вот про формулы(функции) рассказать надо. Они основа программы, её главная и самая используемая часть. И их надо понимать…

Как?

1.      Читай описание формул правильно!

Вот есть описание непростой, но крайне полезной функции СМЕЩ / Offset

Вот мы видим описание 4ого параметра «Высота». И что там написано? «Количество столбцов вправо или влево….». А знаете, чего здесь НЕ НАПИСАНО? Что «количеством» может быть только число!!! Вот именно. В параметре формулы на 4ом месте должно появиться количество столбцов. Не просто число, обозначающее кол-во, а нечто, что в своём финальном виде будет обозначать кол-во!!!

Каждая формула с картинки сверху вернёт одинаковый результат! Именно потому, что на 4ом месте после всех преобразований будет цифра 6.

И так в любой формуле Эксель!

Не важно, что это будет: ссылка на ячейку, формула внутри формулы внутри формулы, имя диапазона, руками забитый параметр! Главное – поймите что от вас хочет формула и как ей это дать.

Ну и не поленитесь узнать, а что же означают имена формул! Они тоже бывают полезными (на английском это правда чуть легче получается, русский перевод так себе). Как пример, все знают ВПР. Но то, что это «Вертикальный поиск результата» знают не все.
Имея же такое знание легко предположить наличие «Горизонтального поиска результата». Вот ты и нашёл функцию ГПР ?

2.      Понимай, что функция берет как параметр и что выдаёт как свой результат.

СМЕЩ / OFFSET выдает массив. Который, в свою очередь, может состоять из одной ячейки или ровно из того же количества ячеек, что и соседний столбец ? Где-то это может пригодиться.

А СУММЕСЛИМН / SUMIFS берет в качестве параметров набор условий и выдает как результат сумму по числовой колонке. То есть если мне надо сделать подобие сводной таблицы руками, то я найду сумму сразу по нескольким параметрам. Удобно! Хм… Вот бы ВПР / VLOOKUP мог искать данные по нескольким условиям и не слева направо, а в произвольном порядке. Он так не умеет… Хотя подожди. У меня в таблице может быть только одна строчка по продажам одного продукта за одну конкретную дату. Это что же получается? Мне нужно найти число по нескольким параметрам в таблице, где для этого набора параметров будет только одно уникальное число? Так сумма этого числа и будет этим самым искомым числом!

Вуаля! Для определённого набора параметров неочевидная функция выполняет роль гибкого ВПР без всяких ИНДЕКС и ПОИСКПОЗ.

Зачастую не очень понятно какую функцию тебе нужно использовать здесь и сейчас и есть ли вообще подходящая к твоей задаче.

Но тут то и нужно включать понимание:

• мне нужно найти значение – значит пойду в раздел функций для поиска и ссылок

• мне нужно преобразовать даты – это наверняка в разделе функций с датами и время

• а если мне нужно преобразовать текст, то мне явно нужно в раздел с текстовыми функциями.

Ну а там уже надо внимательно читать название функций и их описание?

3.      Пойми, что ты видишь, и что есть на самом деле.

Как ни странно, очень многие люди не осознают разницу между тем что они видят на экране и тем, что есть в файле.

Лист и файл Экселя огромны. И вам никто не мешает добавлять нужные вам столбцы, строки и листы с нужной вам информацией, а также подменять информацию выгодным вам способом, скрыв это всё для итогового отображения:

• Добавляйте технический лист со списками и различными мэппингами!

• Не стесняйтесь вставить посреди таблицы скрытые столбец или строку, куда проставите необходимый параметр для поиска через ВПР или СУММЕСЛИ

• А иногда можно вставить значение в пустую строчку и просто покрасить шрифт белым

• На графиках можно прятать элементы и накладывать один график на другой

• Не все элементы сводной нужно оставлять для печати

• И если для формулы вам нужны дополнительные параметры никто не мешает вам их добавить

Главное – не бойтесь использовать весь лист там, где это возможно!

Например, у нас есть реестр с продажами и надо собрать агрегированные данные формулами (а не сводной).

Нам же никто не мешает добавить строку с номером месяца, добавить колонку, где также посчитать месяц через функцию МЕС/MONTH и уже оттуда собрать данные с помощью СУМЕСЛИМН/SUMIFS:

А по результату всё ненужное скрываем через группировку столбцов и строк.

Однако, может так статься, что по каким-то причинам в реестр нельзя добавлять колонки (вдруг это заблокированный лист на внешнем файле). Не беда!

Добавляем строку с номером месяца и чуть усложнив формулу, получаем тот же результат:

=СУММЕСЛИМН(Таблица13457[Сумма];Таблица13457[Раздел выручки];$B4;Таблица13457[Дата];">="&ДАТА($B$3;C$2;1);Таблица13457[Дата];"<="&КОНМЕСЯЦА(ДАТА($B$3;C$2;1);0))

Также очень полезно помнить, что в Экселе даты – это, на самом деле, числа.  Т.е. Эксель сам высчитывает любую дату, взяв за единицу первое января1900 года. Двойка – это второе января 1900ого и т.д. Как итог, в ячейке, где вы видите 15.11.2025, на самом деле хранится число 45976. И это очень помогает в работе с датами и форматами дат!!

Как результат, в предыдущем примере даже не надо заморачиваться с дополнительной строкой. Можно просто поменять ячейки с текстовыми названиями месяцев на их числовые значения, заменив формат ячейки!

А в итоге ничего не добавляя мы получим тот же результат:

=СУММЕСЛИМН(Таблица134578[Сумма];Таблица134578[Раздел выручки];$B3;Таблица134578[Дата];">="&C$2;Таблица134578[Дата];"<="&КОНМЕСЯЦА(C$2;0))

4.      Многие ограничения находятся только в твоей голове!

Один из ярких примеров таких ограничений, это использование формулы СУММЕСЛИ в той же колонке/строке что и суммируемые значения.

Допустим, у нас есть небольшая табличка, где нужен итог

Мы можем поставить простое суммирование. Вот только ты точно знаешь, что будут добавляться новые месяца и тебе лень каждый раз править формулу. Как бы это дело автоматизировать и быть независимым от количества колонок?

Можно взять СУММЕСЛИМН/SUMIFS, но за что зацепиться? По какому условию считать? А вдруг сейчас месяц только к одному году относится, а в итоге и на второй год пойдёт? А вдруг ещё что добавиться? Как быть? Видимо придётся руками править…

А можно подумать не о том, что измениться, а о том, что будет постоянно! В данном случае первые 3 колонки =) И вот уже есть решение =)

На сим серию статей про грамотные файлы Экселя позвольте закончить.

Да прибудет с вами сила формул и волшебство форматирования.

Пы.Сы.
Эксель умрёт последним!