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

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

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

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

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

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

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

Веб-разработка — это новый черный
А мы знаем толк в моде и поможем освоить новую специальность за полгода.
Посмотреть
Фронтенд — это новый черный
Еще по теме
prev
next
10 интересных вещей, которые можно сделать на Arduino

Красивые, полезные и странные — всё как мы любим.

За что все любят Python

И стоит ли вам его учить.

Пишем программу, которая одобрит вам кредит

Или не одобрит, хехех.

Тест на циклы и условия
5 вещей с Али, которые стоит заказать сегодня, чтобы они приехали к концу карантина
5 вещей с Али, которые стоит заказать сегодня, чтобы они приехали к концу карантина

Собери их все.

Нестыдные вопросы об ИТ

С чего начать, на что не тратить время, кому уже поздно.

Полезные плагины для сайта на jQuery

Галереи, параллакс, меню и всплывающие уведомления — обзор проверенных jQuery-плагинов.

Тест: правда или вымысел
Тест: правда или вымысел
Выбираем компьютер для программиста

Как выбрать то, что действительно нужно.

Что читать начинающему программисту
Что читать начинающему программисту

Книги, которые будут красиво смотреться на фото ваших рабочих мест.

Пять подкастов для программистов
Пять подкастов для программистов

И для тех, кто хочет им стать.

Тест: реальная технология или фейк ньюс?
Тест: реальная технология или фейк ньюс?
Как писать код и сразу видеть результат

Обзор инструментов крутых программистов.

easy