Работу в табличном редакторе Excel тоже можно автоматизировать и запрограммировать. Вы сможете здорово помочь своим бухгалтерам с отчётами, маркетологам — с анализом рекламы, а сами научитесь разбираться в алгоритмах.
Мы разберём 5 полезных функций Excel. Если вы думаете, что Excel — это только про цифры и таблички, то вот вам видео, где в Excel запрограммировали настоящую 3D-игру.
Чтобы было проще разобраться, все формулы и примеры мы записали в Excel-файл, который можно скачать и использовать для экспериментов. Каждый пример — на отдельной вкладке снизу.
ЕСЛИ
Это одна из самых важных функций в Excel. Как и в настоящем языке программирования, она проверяет какое-то условие, и если оно выполняется — пишет в ячейку что-то одно, а если нет — пишет что-то другое.
В общем виде она выглядит так:
=ЕСЛИ(условие; “значение_если_да”;”значение_если_нет”)
В условии может быть что угодно: сравнение ячеек, другие формулы, сравнения и математические команды — всё, что вам нужно проверить.
На практике можно сделать, например, так: пусть Excel проверяет возраст, и пишет документ, который в этом возрасте удостоверяет личность. До 14 лет это свидетельство о рождении, а после — паспорт. Для этого используем такую команду в ячейке:
=ЕСЛИ(C2<14;"Свидетельство о рождении";"Паспорт")
Программа проверит, что стоит в предыдущей ячейке, и если там число меньше 14, то напишет «Свидетельство о рождении», а если уже есть 14 лет, то «Паспорт»:
СЧЁТЕСЛИ и СЧЁТЕСЛИМН
Часто бывает нужно посчитать количество строк или заполненных ячеек по каким-то параметрам, например узнать количество работников старше 40 лет, или посчитать все строки, где есть слово «переменная». Для этого используют функцию СЧЁТЕСЛИ:
= СЧЁТЕСЛИ(диапазон;”условие”)
Диапазон — это группа ячеек, в которых мы будем что-то искать, а условие — это по какому параметру мы решим, что ячейка нам подходит.
Допустим мы ведём семейный бюджет и нам нужно узнать, сколько раз у нас были покупки больше 5000 рублей в одном чеке. Для этого используем такое:
=СЧЁТЕСЛИ(C2:C10;">=5000")
Теперь посложнее. Если нам нужно посчитать количество ячеек по нескольким условиям одновременно, то используют функцию СЧЁТЕСЛИМН:
= СЧЁТЕСЛИМН(диапазон;условие;[диапазон1;условие1;диапазон2;условие2;]…)
То, что в прямоугольных скобках — дополнительные условия, их может быть максимум 127, а сами скобки при этом ставить не нужно. Обратите внимание что для каждого нового условия можно задать свой диапазон. Функция проверит все условия, каждое в своём диапазоне, и если все условия одновременно выполняется — посчитает количество таких ячеек.
Для примера найдём все траты больше 5000 в одном чеке, за которые отвечает Миша:
=СЧЁТЕСЛИМН(C2:C10;">=5000";B2:B10;"=Миша")
Теперь у нас первый диапазон отвечает за сумму в чеке, а второй — за имена, поэтому и условия у каждого из них разные.
МАКС
Функция возвращает максимальное значение из выбранного диапазона:
=МАКС(диапазон_ячеек)
Давайте используем её, чтобы найти самую дорогую покупку в бюджете из предыдущего примера. Для этого запишем её так:
=МАКС(C2:C10)
ПОИСКПОЗ
Поиск позиции часто используют, когда нужно найти положение определённого элемента в таблице:
=ПОИСКПОЗ(значение;диапазон_просмотра)
Значение — то, что нам нужно найти в таблице, диапазон просмотра — где именно мы будем искать наше значение. Если в качестве диапазона укажем столбец с цифрами, то функция вернёт номер в этом столбце, где встречается нужная цифра.
Давайте найдём номер строки с максимальной покупкой:
=ПОИСКПОЗ(9000;C2:C10)
Это число нам понадобится для следующей функции
ИНДЕКС
Эта функция возвращает значение конкретной ячейки в указанном диапазоне:
=ИНДЕКС(диапазон; номер_строки; [номер_столбца])
Если в диапазон ячеек входит только один столбец, достаточно будет указать только номер строки. Когда в выбранном диапазоне получается два столбца и больше, можно будет выбрать его номер, чтобы точно обозначить ячейку.
Попробуем в нашем примере с бюджетом найти того, кто сделал самую большую покупку. Мы уже знаем номер строки, в которой записана самая большая сумма, и на этой же строке записан тот, кто эту сумму потратил. Используем это:
=ИНДЕКС(B1:B10;E10)
Когда Excel встретит такую формулу, он возьмёт наш диапазон из второго столбца, затем возьмёт номер строки с самой большой покупкой (она у нас в ячейке Е10, нашли в прошлом примере), и определит, что самую крупную покупку сделал Миша.
Что дальше
Вы уже знаете достаточно для того, чтобы вести в Excel свой семейный или личный бюджет. Используйте наши формулы и анализируйте полученные данные. В следующей статье мы расскажем о том, как менять внешний вид ячеек и поговорим о новых функциях.
