Функции проверки свойств и значений

Функции проверки свойств и значений ExcelФункции проверки свойств и значений в Excel.

Функции проверки свойств, чаще всего, используются как вспомогательные внутри логических функций.

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

Синтаксис этих функций — простейший. Пишется через знак равно имя, и в скобках указывается ссылка на ячейку, которую необходимо проверить. Основную проблему у новичков вызывает вопрос, как их использовать. Между тем, во многих случаях, без них обойтись бывает сложно.

Для меня, как для бывшего HR-а, самое очевидное, где можно использовать функцию проверки текста, это табельный учет. Допустим, у нас имеется excel-форма ввода данных об отработанном времени, которую заполняют руководители. Если сотрудник работал, то руководитель ставит количество отработанных часов. Если сотрудник не работал, то руководитель ставит условное обозначение, обозначающий причину отсутствия сотрудника.

Вот, примерно такой табель у меня получился в свое время.

Нам, на основании этих данных, необходимо заполнить законодательно установленную форму табеля рабочего времени. Проблема заключается в том, что, в табеле необходимо указать и код рабочего времени, и количество отработанных часов. Как их развести?

Для этого используем две функции — ЕСЛИ (о ней в статье «Логические функции и сложные алгоритмы»), для создания условия, и ЕТЕКСТ, для критерия распределения данных по ячейкам. Логика формулы для отображения кодов будет такая:

Пишется эта формула так: =ЕСЛИ(ЕТЕКСТ(C3);C3;»Я»).

Для отображения только числовых значений лучше использовать функцию ЕЧИСЛО: =ЕСЛИ(ЕЧИСЛО(C3);C3;»»). Пустые кавычки вставят пустое значение, если проверяемая ячейка не будет числом.

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

Иногда в формулах необходимо предусмотреть возможность появления ошибки. Например, если есть вероятность деления на 0. Поскольку делить на 0 нельзя, то Excel выдаст значение «#ДЕЛ/0! «. Если на эту ячейку ссылаются еще и другие формулы, то все расчеты выдадут информацию об ошибке, то есть никаких расчетов не будет.

В этом случае можно использовать формулу: =ЕСЛИ(ЕОШИБКА(A1/B1);0;A1/B1). Логика ее такова:

Пример использования функции ЕПУСТО описан у меня в статье «Оформление командировки в Excel«. Мы, с помощью нее, добились отображение пустой ячейке, если в ячейке, на которую ссылались, тоже не было никаких записей. В противном случае, ссылка на пустую ячейку выводила бы цифру 0.

Использование функций ЕЧЁТН и ЕНЕЧЁТ можно привести из области работы с номерами домов. Мы знаем, что, как правило, четные номера домов располагаются по одну сторону улицы, а нечетные — по другую. Допустим, у нас есть список, который необходимо рассортировать по сторонам А и Б.  В колонку А вносим четные номера, в колонку Б — нечетные.

Формулы, соответственно, будут такие:

А — =ЕСЛИ(ЕЧЁТН(C2);C2;»»);

Б — =ЕСЛИ(ЕНЕЧЁТ(C2);C2;»»).

На этом я заканчиваю цикл статей, посвященных начинающим любителям Excel.

Дальнейшие темы для начинающих буду развивать уже опираясь на запросы читателей этого сайта. Пишите мне в комментариях и предлагайте темы.

Логические функции и сложные алгоритмы

Логические функции и сложные алгоритмы ExcelЛогические функции и сложные алгоритмы в Excel.

Логические функции Excel, пожалуй, одни из моих самых любимых.

Ведь с помощью них можно закладывать практически любые алгоритмы расчетов!

Фактически, мы занимаемся программированием, комбинируя различные функции друг с другом.

И это не более сложно, чем писать формулы сложения или умножения, о которых я писал в статье «Excel для начинающих II«.

Мы рассмотрим функции ЕСЛИ, И, ИЛИ.

Начнем с ключевой логической функции ЕСЛИ. Она, фактически, задает алгоритмы, которые многие учились строить на информатике в школе.

Структура ее такова:

Выглядеть это будет так: =ЕСЛИ(A1>10;»Много»;»Мало»).

Внесем эту формулу в ячейку А3. А в ячейке А1 запишем число 5. Должно появиться слово «Мало». Исправим 5 на 50. Появится слово «Много». Все работает!

Очень часто, одного условия бывает недостаточно.

В этом случае мы можем вкладывать функцию ЕСЛИ саму в себя, таким образом, увеличивая количество условий. Допустим, нам необходимо определить сумму надбавки за разряд работнику (см.рисунок).

Логика очевидна — если разряд 4-й, то платим 4000 руб, если 3-й3000 руб, и т.д. Вот как это выглядит в формуле (для знающих оговорюсь — мне известно, что оптимальнее, в данном случае, использовать ВПР :-)): =ЕСЛИ(C8=A2;B2;ЕСЛИ(C8=A3;B3;ЕСЛИ(C8=A4;B4;ЕСЛИ(C8=A5;B5;0)))). Лично я, для себя, когда начинаю писать нечто подобное, чтобы не сбиться, начинаю про себя проговаривать алгоритм (читаем формулу слева на право):

Таким образом у нас получилось четыре условия ЕСЛИ, вложенных друг в друга.

Эти две функции, как правило, используются как вспомогательные для функции ЕСЛИ. С помощью них нельзя вызвать какое-либо значение, так как они просто проверяют — истинны или ложны условия, которые в них заложены.

Рассмотрим пример — нашему работнику положена премия, но только в том случае, если им была пройдена аттестация, и не было дисциплинарных взысканий.

То есть, только при соблюдении двух условий одновременно.

Напишем формулу: =ЕСЛИ(И(F8=»Да»;G8=»Нет»);5000;0). Пропишем текстом:

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

Во втором случае нужно понимать, что если ячейки F8 и G8 не будут заполнены, то премия работнику будет выплачена.

Поэтому нельзя сказать, что обе формулы идентичны. Первая говорит о том, что только при выполнении обоих условий премия будет выплачена, а вторая — если будет отмечено хотя бы одно нарушение, то премия не будет выплачена.

Суммирование по условию

Суммирование по условию ExcelСуммирование по условию в Excel.

В прошлой статье я писал о функции суммирования — СУММ.

Она позволяет сложить ряд чисел в диапазоне.

Но иногда бывает необходимо просуммировать не все числа, а только те, которые отвечают какому-либо условию.

Условия могут быть совершенно разные. Например, в ряду чисел (см. рисунок) просуммировать только те, что больше  10.

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

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

Вот как это будет выглядеть: =СУММЕСЛИ(A1:A12;»>10″)

А1:А12 — это, как вы понимаете, диапазон суммирования. А вот то, что указано через точку с запятой в кавычках — условие суммирования, больше 10. Соответственно, во всем диапазоне будут суммироваться только числе больше 10.

Признаться, раньше, когда я еще только осваивал Excel, мне было проще использовать диалоговые окна. То есть, не вносить имя функции в строку формул. а выбрать необходимую в библиотеке. Поскольку суммирование (пусть даже и условное), это математическая функция, то выбираем в библиотеке функций (в закладке меню «Формулы») пункт «Математические».

Библиотека математических функций достаточно большая, и в выпавшем списке мы сразу имя СУММЕСЛИ не увидим. Для этого прокрутите каретку полосы прокрутки выпавшего списка (справа от него) вниз — подводим курсор мыши к каретке, зажимаем левую кнопку и ведем курсор мыши вниз. Поскольку все имена расположены по алфавиту, найти нужное труда не составит.

Кликаем на имени СУММЕСЛИ. Откроется диалоговое окно, в котором для каждого аргумента предусмотрено отдельное поле. Обратите внимание — названия двух полей в окне выделены жирным, а название третьего поля написано обычным шрифтом. Это означает, что два первых поля — «Диапазон» и «Критерий» — должны быть заполнены обязательно, а поле «Диапазон_суммирования» — не обязательное для заполнения.

Диапазон суммирования, в нашем случае, совпадает с диапазоном критерия суммирования. То есть, нам необходимо просуммировать те же значения, в отношении которых должно работать правило «>10«. А вот для второй задачи, где нужно сложить значения, соответствующие надписи «Еда», эти диапазоны отличаются. Тут мы и используем третий аргумент.

Открываем таблицу, и вызываем диалоговое окно условного суммирования. В первое поле («Диапазон«) заносим не диапазон суммирования, а диапазон, на основании которого будут отбираться складываемые ячейки. Это B2:B7.

Второе поле для критерия. Вносим сюда в кавычках слово «Еда«. А в третье поле, собственно, вносим через двоеточие адреса верхней и нижней ячеек складываемого массива. Сразу можно проверить, какие числа суммируются (справа от поля в фигурных скобках), и какой получается ответ — 437.

Нажимаем «ОК» и любуемся результатом.

Помимо суммирования, по условию можно подсчитывать количество ячеек, отвечающих какому-либо условию. За это отвечает функция СЧЁТЕСЛИ. Ее синтаксис абсолютно идентичен — указываем диапазон, критерий и, при необходимости, диапазон суммирования.

Третья идентичная функция, которая часть используется — СРЗНАЧЕСЛИ. Как вы понимаете, это расчет среднего значения в ряду чисел, отобранных по какому либо условию.

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

Обратите внимание на каретку в правой части окна. Если ее прокрутить вниз, то будут появляться поля для новых условий, до 127. То есть, мы можем заложить 127 условий суммирования.

Ну, и конечно, расчет среднего значения и подсчет количества ячеек по нескольким условиям тоже возможны. Это, соответственно, функции СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН.

Суммирование и среднее арифметическое

Суммирование и среднее арифметическое ExcelСуммирование и среднее арифметическое в Excel.

Пожалуй, самая понятная и простая функция Excel — это суммирование.

Если вы начнете вводить в ячейку «=СУММ«, то уже при вводе первых букв откроется вспомогательное окошко, позволяющее выбрать из выпавшего списка ту функцию, которая необходима.

Подведите курсор мыши к нужной строке, и дважды кликните по ней.

Помимо того, что правильная запись сразу же будет введена, кроме нее появиться строка, позволяющая сориентироваться в синтаксисе функции.

Между скобками находятся аргументы — данные, с помощью которых осуществляются вычисления. В нашем случае, это будет, допустим, ряд чисел, которые необходимо просуммировать. Начинаем вводить «=СУММ«, открываем скобку и, зажав левую кнопку мыши, выбираем весь диапазон суммирования.

Обратите внимание — после открывающей скобки программа показывает диапазон «А1:А6«. То есть, мы выбрали диапазон с ячейки А1 до ячейки А6. Закрываем скобку и нажимаем «Enter». Смотрим получившийся результат — сумма равна 21.

Нередко бывает необходимо просуммировать значения в несвязанных ячейках. Допустим, наши ячейки разбросаны на листе, но их необходимо просуммировать. В этом случае мы задаем не диапазон суммирования, а вносим адрес каждой ячейки в качестве отдельного аргумента. Во всех функциях аргументы разделяются друг от друга точкой с запятой.

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

Кому-то, возможно, будет удобнее вносить функции и аргументы полностью с клавиатуры в строке формул. Такой вариант тоже возможен, а, в некоторых случаях, является более удобным.

Поскольку функция суммирования используется наиболее часто, в меню на закладке «Главная» в правой части имеется кнопка автосуммирования (значок ). Если на кнопку с этим значком нажать, предварительно установив табличный курсор рядом с диапазоном суммирования, то программа предложить просуммировать этот диапазон сразу. Достаточно будет лишь нажать клавишу Enter.

Среднее арифметическое вычисляет функция CРЗНАЧ. Ее синтаксис абсолютно такой же, что и в функции СУММ. То есть, в скобках нужно ввести либо диапазон, либо перечислить все адреса ячеек, среднее арифметическое значений которых необходимо вычислить.

Поиск среднего арифметического используется почти также часто, что и суммирование. Поэтому в панели меню, если нажать на треугольничек правее символа , выпадет список, из которого можно выбрать авторасчет среднего значения (строка с надписью «Среднее«).

Помимо этого, в выпадающем списке, как видим, имеются:

Другие функции — вызов окна библиотеки прочих функций.