5 полезных функций Excel для начинающих программистов
vk f t

5 полезных функций Excel для начинающих программистов

Необязательно писать код только на языках программирования — Excel тоже подходит

Работу в табличном редакторе Excel тоже можно автоматизировать и запрограммировать. Вы сможете здорово помочь своим бухгалтерам с отчётами, маркетологам — с анализом рекламы, а сами научитесь разбираться в алгоритмах.

Мы разберём 5 полезных функций Excel. Если вы думаете, что Excel — это только про цифры и таблички, то вот вам видео, где в Excel запрограммировали настоящую 3D-игру.

Чтобы было проще разобраться, все формулы и примеры мы записали в Excel-файл, который можно скачать и использовать для экспериментов. Каждый пример — на отдельной вкладке снизу.

В Excel все функции начинаются с символа равенства «=». Когда табличный редактор встречает в начале строки этот символ, он понимает, что потом пойдёт какая-то специальная команда, которую нужно обработать. Чтобы всё работало правильно, проверяйте каждый раз, что вы не забыли поставить этот знак в самом начале.

ЕСЛИ

Это одна из самых важных функций в Excel. Как и в настоящем языке программирования, она проверяет какое-то условие, и если оно выполняется — пишет в ячейку что-то одно, а если нет — пишет что-то другое.

В общем виде она выглядит так:

=ЕСЛИ(условие; “значение_если_да”;”значение_если_нет”)

В условии может быть что угодно: сравнение ячеек, другие формулы, сравнения и математические команды — всё, что вам нужно проверить.

На практике можно сделать, например, так: пусть Excel проверяет возраст, и пишет документ, который в этом возрасте удостоверяет личность. До 14 лет это свидетельство о рождении, а после — паспорт. Для этого используем такую команду в ячейке:

=ЕСЛИ(C2<18;"Свидетельство о рождении";"Паспорт")

Программа проверит, что стоит в предыдущей ячейке,  и если там число меньше 18, то напишет «Свидетельство о рождении», а если уже есть 18 лет, то «Паспорт»:

СЧЁТЕСЛИ и СЧЁТЕСЛИМН

Часто бывает нужно посчитать количество строк или заполненных ячеек по каким-то параметрам, например узнать количество работников старше 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 свой семейный или личный бюджет. Используйте наши формулы и анализируйте полученные данные. В следующей статье мы расскажем о том, как менять внешний вид ячеек и поговорим о новых функциях.

Ещё по теме