Итак, мы переходим к одной из важнейших, на мой взгляд, тем – к логическим операторам. Глобально, на основе таких конструкций строятся программы и следовательно, понимая базовую логику и умея выстраивать ее в таблицах будет проще перейти к чему-то более сложному, например, к языку программирования. Если же у вас такого желания нет или не возникнет, то логические выражения применимы и в обычной жизни – позволяют более логично выстраивать мысли и обоснованнее принимать решения... И все же вернемся к таблицам.
К делу
Основные компоненты, которые мы рассмотрим сегодня – это условные операторы. Они являются основой любого алгоритма и базой для решения задач в таблицах. Речь пойдет о таких функциях ЕСЛИ и ее вариациях СЧЁТЕСЛИ, ЕСЛИМН, СУММЕСЛИ, ЕСЛИОШИБКА и других, где присутствует данное условие.
Последовательность аргументов (то, что в скобках после названия функции) типична и состоит из диапазона данных, который проверяется и условия в отношении него; результата, если диапазон соответствует условию; результата – если не соответствует.
Пример представлен на рисунке. Важно, если формула должна выводить текстовый результат, то необходимо прописывать его в кавычках.
Формулы в данном случае говорящие. Рассмотрим их кратко:
=ЕСЛИ(проверяемое условие; вывод, в случае удовлетворения условию; вывод, в случае неудовлетворения условию). Используется в основном для того, чтобы разделить объекты на группы или провести математические или текстовые операции над объектами.
=СУММЕСЛИ(диапазон проверки; условие проверки; диапазон суммирования). Суммирование по условию, например, если вам нужно отобрать конкретные объекты (менеджеры, товары и т.д.).
=СУММЕСЛИМН(диапазон суммирования; диапазон 1 условия; условие для 1 диапазона; диапазон 2 условия; условие для 2 диапазона и т.д.). Аналогична предыдущей, но принимает большее количество условий. Можно делать суммирование по условиям из разных столбцов, например, по группе товаров и по городу.
=СЧЁТЕСЛИ(диапазон; условие). Подсчет количества объектов. Важно, операции сравнения пишутся в кавычках. Например, =СЧЁТЕСЛИ(C23:C31;">50"). Иначе таблица вас не поймет и выдаст ошибку.
=СЧЁТЕСЛИМН(диапазон 1 условия; условие 1; диапазон 2 условия; 2 условие и т.д.). Множественный аналог предыдущей функции.
=ЕСЛИМН(проверяемое условие 1; вывод, если условие 1 истинно; проверяемое условие 2; вывод, если условие 2 истинно и т.д.). Стоит отметить, что порядок условий в данной функции важен, т.к. работает она сверху вниз и возвращает значение первого истинного условия.
Разберем типичный пример с баллами студентов. Если студент написал на 90 баллов и выше, то его результатом станет оценка «отлично», если 70-89 – «хорошо», 50-69 – «удовлетворительно», а ниже 49 – «неудовлетворительно».
Если посмотреть на рисунок ниже, то заметим, что на первый взгляд, одинаковые формулы дают совершенно разный результат.
Функция работает сверху вниз, а это значит, что сначала проверяется 1 условие, если оно ложно, то таблица переходит ко второму и так до тех пор, пока не найдет истинного и не выведет соответствующий результат.
1. В столбце «Верно» для 43 логика компьютера будет такая. Идем прямо по формуле.
43 больше или равно 90? Нет.
43 больше или равно 70? Нет.
43 больше или равно 50? Нет.
43 меньше 50? Да – вывести «неудовлетворительно».
2. В столбце «Неверно» аналогично, только на 2 шаге, любое число будет прерывать цикл перебора и выдавать результат или «неудовлетворительно», или «удовлетворительно», ведь любое число или меньше 50, или больше или равно 50.
=ЕСЛИОШИБКА(любая ячейка; вывод в случае ошибки). Классная функция, позволяющая работать с ошибками и в случае ее возникновения выводить нужный результат. В моей практике обычно в нее оборачивается какая-то сложная формула вместо ячейки и вывод пишется примерно так: «недостаточно данных». Актуально будет, например, если захотите посчитать премию отдела продаж, а в ячейке с суммами будет текст.
=СРЗНАЧЕСЛИ(диапазон условия; условие; диапазон, который нужно усреднить) / =СРЗНАЧЕСЛИМН(диапазон усреднения; диапазон 1 условия; 1 условие; диапазон 2 условия; 2 условие и т.д.). Первая функция позволяет находить среднее по условию, а вторая по нескольким.
Важность
Главная сила этих функций в их комбинации и возможности фактически взаимодействовать с таблицей с целью сегментации (рукописные условия ветвления). Это важно для бизнеса и предпринимателей, ведь голые данные не несут никакой информации. Только став организованной структурой и получив возможность на ее основе делать выводы данные приносят прибыль. Поэтому вариации функции ЕСЛИ нужно осваивать и применять в работе. В последствии будет проще освоить более сложные функции (пугающий многих ВПР, например), позволяющие не просто разделять данные на группы, но и работать в формате вопрос-ответ (автоматизированный сбор нужной информации).