Использование логических функций в Excel
Табличный редактор Эксель – очень гибкий и мощный инструмент для структурированной работы с данными. Среди большого количества его эффективных инструментов стоит отдельно выделить логические операторы, в основе использования которых лежит признание выражения истинным или ложным. Эти функции эффективно встроены в инструментарий формул и позволяют создавать гибкие условия для различных вычислений или заполнения ячеек на основе логических выражений. В данной статье будут описаны основные используемые функции, а также приведен практический пример использования логических операторов в Excel.
Вложенный ЕСЛИ (IF) для шкал
Вам часто будут встречаться вложенные ЕСЛИ, настроенные для обработки «шкал» … например, для назначения оценок, стоимости доставки, налоговых ставок или других значений, которые изменяются в шкале с числовым вводом. Пока в шкале не слишком много уровней, вложенные ЕСЛИ работают нормально. Но нужно быть внимательными, иначе формула может стать не читабельной.
Хитрость заключается в том, чтобы выбрать направление (от высокого к низкому или от низкого к высокому), а затем соответствующим образом структурировать условия. Например, чтобы присвоить оценки в порядке «от низкого до высокого», мы можем представить решение, отраженное в следующей таблице
Обратите внимание, что нет условия для «Отлично», потому что, как только мы выполним все остальные условия, мы знаем, что баллов должно быть больше 90, и, следовательно, «Отлично»
| Баллы | Оценка | Условие |
| 0 — 63 | Неуд. | < 64 |
| 64 — 72 | Удовл. | < 73 |
| 73 — 89 | Хорошо | < 90 |
| 90 — 100 | Отлично |
С четко понятными условиями мы можем ввести первый оператор ЕСЛИ:
Мы позаботились о «Неуд.». Теперь, чтобы обработать «Удовл.», нам нужно добавить еще одно условие:
Обратите внимание, что я просто добавил еще один ЕСЛИ в первый для «ложного» результата. Чтобы расширить формулу для обработки оценки «Хорошо», мы повторяем процесс:. Мы обработали все оценки и дошли до последнего уровня «Отлично»
Вместо добавления еще одного ЕСЛИ, просто добавьте итоговую оценку для ЛОЖЬ
Мы обработали все оценки и дошли до последнего уровня «Отлично». Вместо добавления еще одного ЕСЛИ, просто добавьте итоговую оценку для ЛОЖЬ.
Вот последняя вложенная формула ЕСЛИ в действии:
Завершенный вложенный пример ЕСЛИ для расчета оценок
Внедрение логических функций в Excel
Табличный редактор Эксель – весьма гибкий и мощнейший инструмент для структурированной работы с данными. Посреди огромного количества его действенных инструментов стоит раздельно выделить логические операторы, в базе использования которых лежит признание выражения настоящим либо неверным. Эти функции отлично интегрированы в инструментарий формул и разрешают создавать гибкие условия для разных вычислений либо наполнения ячеек на базе логических выражений. В данной статье будут описаны главные применяемые функции, также приведен практический пример использования логических операторов в Excel.
Уменьшите количество ЕСЛИ с И и ИЛИ
Вложенные ЕСЛИ — мощный инструмент, но формулы быстро становятся громоздкими, когда вы добавляете больше уровней. Один из способов избежать большего количества уровней — использовать ЕСЛИ в сочетании с функциями И (AND) и ИЛИ (OR). Эти функции возвращают простой результат ИСТИНА / ЛОЖЬ, который отлично работает внутри ЕСЛИ, поэтому вы можете использовать их для расширения логики одного ЕСЛИ.
Например, в приведенной ниже задаче мы хотим поставить «х» в столбце D, чтобы отметить строки, где цвет «красный», а размер «маленький».
ЕСЛИ с функцией И проще, чем два вложенных ЕСЛИ
Мы могли бы написать формулу с двумя вложенными ЕСЛИ, вот так:
Однако, заменив одну проверку на функцию И, мы можем упростить формулу:
Таким же образом, мы можем легко расширить эту формулу с помощью функции ИЛИ, чтобы проверить наличие красного ИЛИ синего И маленького:
Все то же самое можно сделать с помощью вложенных ЕСЛИ, но формула быстро станет сложной.
Пример применения функций
Теперь давайте рассмотрим применение некоторых из вышеперечисленных функций на конкретном примере.
Имеем список работников предприятия с положенными им заработными платами. Но, кроме того, всем работникам положена премия. Обычная премия составляет 700 рублей. Но пенсионерам и женщинам положена повышенная премия в размере 1000 рублей. Исключение составляют работники, по различным причинам проработавшие в данном месяце менее 18 дней. Им в любом случае положена только обычная премия в размере 700 рублей.
Попробуем составить формулу. Итак, у нас существует два условия, при исполнении которых положена премия в 1000 рублей – это достижение пенсионного возраста или принадлежность работника к женскому полу. При этом, к пенсионерам отнесем всех тех, кто родился ранее 1957 года. В нашем случае для первой строчки таблицы формула примет такой вид: . Но, не забываем, что обязательным условием получения повышенной премии является отработка 18 дней и более. Чтобы внедрить данное условие в нашу формулу, применим функцию НЕ: .
Для того, чтобы скопировать данную функцию в ячейки столбца таблицы, где указана величина премии, становимся курсором в нижний правый угол ячейки, в которой уже имеется формула. Появляется маркер заполнения. Просто перетягиваем его вниз до конца таблицы.
Таким образом, мы получили таблицу с информацией о величине премии для каждого работника предприятия в отдельности.
Урок: полезные функции Excel
Как видим, логические функции являются очень удобным инструментом для проведения расчетов в программе Microsoft Excel. Используя сложные функции, можно задавать несколько условий одновременно и получать выводимый результат в зависимости от того, выполнены эти условия или нет. Применение подобных формул способно автоматизировать целый ряд действий, что способствует экономии времени пользователя.
Ошибка #ДЕЛ/0!
- Какую функцию вы используете?
- СРЗНАЧ
- СЦЕПИТЬ
- СЧЁТЕСЛИ, СЧЁТЕСЛИМН
- ДАТАЗНАЧ
- ДНИ
- НАЙТИ, НАЙТИБ
- ЕСЛИ
- ИНДЕКС, ПОИСКПОЗ
- ПОИСК, ПОИСКБ
- СУММ
- СУММЕСЛИ, СУММЕСЛИМН
- СУММПРОИЗВ
- ВРЕМЗНАЧ
- ТРАНСП
- ВПР
- * Другая функция
Найдите пункт Разделитель элементов списка. Если в поле разделителя элементов списка указан знак «минус», замените его на что-то другое. Например, разделителем нередко выступает запятая. Также часто используется точка с запятой. Однако для вашего конкретного региона может подходить другой разделитель элементов списка.
Технические характеристики → Полезное видео → Общие данные → Таблица размеров → Полезные советы → Как правильно рассчитать → Сыпучие продукты → Натяжные потолки и технологии→ Мера жидких продуктов
Оформление и примеры использования
Алгоритм написания логических формул в Эксель следующий:
- Нужно выделить пустую ячейку, в которую будет записываться формула и выводиться результат действия.
Вписывать можно и в строке формул, после выделения ячейки.
- Перед формулами в программе ставится знак «=». Поставить его.
- Напечатать название оператора.
- После этого вписываются аргументы, если они есть. Начинается запись со знака «открывающаяся круглая скобка “(“».
- Аргументы вводятся последовательно через знак ”;”. Также, если после ввода названия функции нажать клавиши Ctrl + A, то откроется меню аргументов и вписать их можно здесь.
- В конце ставится символ «закрывающаяся круглая скобка “)”». Контролировать написание можно в строке формул.
- После завершения нажать кнопку ENTER. Результат появится в ячейке.
Работа с ПЕРЕКЛЮЧ
Сравнивает указанную величину в ячейке или формулу со списком данных и вписывает в ячейку первое совпавшее значение. Если совпадений не будет, и не проставлена величина по умолчанию, оператор выдаст ошибку «#Н/Д». Функция схожа с ЕСЛИМН, но в отличие от нее условие ставится точно, без сравнительных знаков.
Работа оператора иллюстрируется на рисунке.
Здесь вместо чисел 1, 2, 7 — нужно проставить прописью дни недели им соответствующие. Если будут другие цифры, то возвратится значение по умолчанию «Нет совпадений (No match)».
Использование ЕСЛИОШИБКА
Оператор используется для нахождения ошибки в таблице. Найдя ее, функция не пишет в ячейке какую-либо из ошибок, а возвращает указанный ответ, который может быть текстом, пустой строкой: =ЕСЛИОШИБКА(Что_проверять;Что_выводить_вместо_ошибки).
Например, нужно поделить значения в столбце А на величины в столбце В. Если по ошибке в строках стоят 0, то получится деление на 0.
Применение оператора «=ЕСЛИОШИБКА(A2/B2;»»)» скрывает ошибки.
Здесь сравнивается выражение A2/B2. В случае обнаружения ошибки в ячейку ставится пустая строка, указанная пробелом в кавычках ““.
ЕСЛИОШИБКА появилась в Excel 2007. До этого использовалась функция ЕОШИБКА, которая самостоятельно не могла обработать ошибку, так как имела только один аргумент, проверяющий указанную ячейку. Для ввода ответа в случае обнаружения ошибки, нужно было использовать оператор ЕСЛИ: «ЕСЛИ(ЕОШИБКА(А2/В2);”“;А2/В2)».
И/ИЛИ
Простые операторы, редко применяются без связки с другими функциями.
На рисунке показан принцип действия функции И.
Пример использования: «=И(A1>B1; A2<>25)». Здесь созданы два условия:
- Значение в ячейке А1 должно быть больше числа в В1.
- Число в А2 должно быть не равно 25.
При исполнении обоих получается ИСТИНА.
Если одно из заданий нарушено, получается ЛОЖЬ. В данном случае число в А1 меньше чем в В1.
Ниже представлен алгоритм функционирования оператора ИЛИ.
Пусть даны 3 выражения: A1>B1; A2>B2; A3>B3. Требуется применить к ним действие ИЛИ: «=ИЛИ(A1>B1; A2>B2; A3>B3)». Возможные варианты показаны на рисунках:
Здесь конечный результат ИСТИНА, так как из трех выражений одно верно: A3>B3. На следующем изображении функция выдала ответ «ЛОЖЬ», так как на все вопросы получены аналогичные ответы.
Функция ИЛИ
Составить таблицу (Рисунок 9)
Рисунок 9
Перед нами составлена таблица зачисленных и не зачисленных студентов. Принцип их отбора очень проста. Для зачисления студента в учебное заведение необходимо иметь бал выше 3.50 или рейтинг не менее 50.00. Если ни один из критерия не будет соответствовать проходным баллам, соответственно в столбце Зач. / Н.Зач будет иметь соответствующий результат.
В данной формуле будут присутствовать функции ЕСЛИ и НЕ.
Выбираем нужную нам ячейку и введём функцию ЕСЛИ . В синтаксисе функции “лог вырождения” необходимо прописать следующую функцию НЕ. Через точку запятой пропишем наши условия (Рисунок 10).
Рисунок 10
Завершающим шагом будет являться прописи “Зач” “Н.Зач”. Посредством закрытия скобки обозначим окончание формулы.
Функция ИСКЛИЛИ
Составим таблицу “Распорядок рабочего дня сотрудников”. Рисунок 11
В нескольких торговых точках в разное время каждый сотрудник работает на конкретном местоположении. При изменении времени в столбце “местоположение” меняется значение тем самым узнав где и какой сотрудник находится в то или иное время.
Например: Влада Евдакимова с 7:00 до 12:00 находится в торговой точке ТЦ “Велес”, с 12 до 13:00 обед, с 13:00 до 16:00 ТЦ “Самолёт”, с 16:00 до 18:00 окончание рабочего дня, 19:00 полное закрытие.
Рисунок 11
Для реализации данной задачи достаточно логической функции ЕСЛИ и ИСКЛИЛИ.
Для удобства создадим список (Вкладка данные – Проерка данных – СПИСОК) выделить ячейки со временем 7:00 12:00 13:00 16:00 18:00 19:00.
Что бы сравнить тот или иной диапазон времени как раз такки на помощь и приходит функция ИСКЛИЛИ. Задача будет состоять в том что бы сравнить тот или диапазон времени знаком больше с временем которые мы будем задавать в столбце время.
После создания списка в столбце местоположение пропишем следующую формулу =ЕСЛИ(ИСКЛИЛИ( . Рисунок 12
Рисунок 12
В синтаксисе логическое значение1 пропишем наше первое условие выделим первую ячейку с временем 7:00 и пропишем знак > и выберем ячейку где указывается нынешнее время. Точка с запятой и слово берем время, но только уже 12:00 снова знак > и выберем ячейку где указывается нынешнее время
Обратите внимание что при выборе ячейки нынешнего времени обязательно укажите знак $ означающей что при перемещении формулы нумерация четки останется прежней. Например: ИСКЛИЛИ(B7>$P$5….
C7>$P$5).
После того как все значения в функции ИСКЛИЛИ будут прописаны, закроем скобку для окончания формулы ИСКИЛИ. После окончания формулы ИСКИЛИ автоматический подключаться не оконченная функция ЕСЛИ. В синтаксисе функции ЕСЛИ значение если истина прописываем то значение которое будет соответствовать промежутку местоположения с 7:00 до 12:00. Например “ТЦ “”Ласточка”” “. Рисунок 13
Рисунок 13
В случаи если промежуток времени не будет совпадать с настоящем временем функция примет позицию ЛЖИ. Соответственно в положение ЛЖИ прописываем сравнения следующего промежутка времени аналогично той формуле которая составлялась (Рисунок 12, Рисунок 13).
По окончанию формулы в последней позиции ЛЖИ (19:00) прописать о том что рабочий день сотрудников окончен.
Скопируем формулу используя ползунок (рисунок 14)
Рисунок 14
Путём изменение настоящего времени проверьте работоспособность формулы.
Функция ЕСЛИОШИБКА
В Excel, некоторые функции и формулы могут выдавать ошибки не потому, что вы ошиблись при вводе, а из-за временного отсутствия данных или копирования формул “с запасом” на избыточные ячейки. Классический пример – ошибка деления на ноль при вычислении среднего (Рисунок 15)
Рисунок 15
Причем заметьте, что итоги в нашей таблице тоже уже не считаются – одна ошибка начинает порождать другие, передаваясь по цепочке от одной зависимой формулы к другой. Так что из-за одной ошибочной ячейки, в конце концов, может перестать работать весь расчет.
Для лечения подобных ситуаций в Microsoft Excel есть мегаполезная функция ЕСЛИОШИБКА (IFERROR), которая умеет проверять заданную формулу или ячейку и, в случае возникновения любой ошибки, выдавать вместо нее заданное значение: ноль, пустую текстовую строку “” или что-то еще.
Синтаксис функции следующий:
=ЕСЛИОШИБКА(Что_проверяем; Что_выводить_вместо_ошибки)
Так, в нашем примере можно было бы все исправить так:
Рисунок 16
Все красиво и ошибок больше нет.
Рисунок 17
Нетривиальные функции Microsoft Excel
Рассмотрим несколько простых, но нетривиальных и изящных инструментов, с помощью которых можно работать с большими объемами данных в Excel, реализовать эффективную выборку элементов из заданного массива, получать динамические итоги, объединенные данные из диапазонов, автоматически обрабатывать ошибки.
CHOOSE (ВЫБОР)
На основании индекса возвращает значение из списка аргументов. Используют функцию «CHOOSE» для выбора одного максимум из 254 значений на основании номера индекса. Сначала может показаться, что это обычная функция, ведь все, что она делает — выбирает элемент в своем списке на основе номера индекса. К тому же есть еще и функция «INDEX», которая делает то же, но если комбинировать «ВЫБОР» с другими формулами, она может быть довольно мощной.
Пример
Тривиальным примером использования данной функции является возвращение разных значений, основанных на условиях. Например, есть список фамилий и их оценки (1); нужно разделить их на основе определенных значений (2) и вывести полученные данные в поле «Результат».
Чтобы решить эту задачу, достаточно использовать функцию «CHOOSE» следующим образом:
Функции группы LOOKUP (ПРОСМОТР)
Функции группы «LOOKUP» (VLOOKUP, HLOOKUP) по праву можно назвать одними из самых важных и полезных. Они помогают найти данные в определенном столбце, строке или таблице. «VLOOKUP (ВПР)» позволяет искать значение в столбце («V» — по вертикали) и возвращать другое значение из той же строки. «HLOOKUP (ГПР)» позволяет искать значение в строке («H» — по горизонтали) и возвращать другое значение из того же столбца. Довольно удобные и часто используемые, так как сопоставить вручную обширные диапазоны с десятками тысяч наименований проблематично.
Пример
В самом простом виде использование «ВПР» можно представить следующим образом. Пусть в некой таблице имеется список сотрудников (фамилия и идентификатор) и необходимо вытянуть информацию об имени сотрудника с фамилией Сидоров.
Для этого используем функцию «ВПР», где указываем, что искать, диапазон поиска, номер столбца и указать нужно ли искать точное вхождение.
IFERROR (ЕСЛИОШИБКА)
«ЕСЛИОШИБКА» в Microsoft Excel используется для обработки ситуаций, когда в результате работы формулы возникла некая ошибка. Работая с этой функцией, можно указать, какое именно значение должно возвращаться с помощью «IFERROR» вместо полученной ошибки. Если формула в ячейке не возвращает ошибку, вместо этого возвращается результат.
Пример
Простым примером использования «IFERROR» является замена ошибки в ячейке табличного процессора на значения, которые сопоставляются ошибке.
В данном случае стандартная ошибка, вызванная делением на 0 (числа первого столбца делятся на второй) заменяется на сообщение «Деление на 0» с помощью функции «ЕСЛИОШИБКА».
SUMPRODUCT (СУММПРОИЗВ)
«SUMPRODUCT» возвращает сумму произведений определенных массивов или диапазонов. Ее часто используют бухгалтера для расчета заработной платы и подсчетов объемов продаж.
Пример
Хорошим примером использования функции «СУММПРОИЗВ» является расчет объемов продаж определенной категории товара по заданным параметрам (категория, размер).
XIRR (ЧИСТВНДОХ)
«XIRR» — финансовая функция MS Excel, используемая для вычисления внутренней ставки доходности по определенному ряду нерегулярных потоков денег (для постоянных потоков используется функция «ВСД»).
Пример
В качестве примера можно рассмотреть вычисления внутренней ставки по заданным непостоянным потокам.
С помощью этих мало известных функций MS Excel можно существенно упростить работу с объемными данными и улучшить эффективность взаимодействия с таблицами. Более подробную информацию об этих функциях и их синтаксисе можно получить в самом табличном процессоре, просто кликнув по интересующему варианту.
Расставляй круглые скобки как профессионал
Одной из проблем с вложенными ЕСЛИ является сопоставление или «балансировка» скобок. Если круглые скобки стоят не там, где нужно или их меньше, чем требует формула, результат вы не получите. К счастью, Excel предоставляет несколько инструментов, которые помогут вам убедиться, что круглые скобки «сбалансированы» при редактировании формул.
Во-первых, если у вас несколько наборов скобок, круглые скобки имеют цветовую кодировку, поэтому открывающие скобки соответствуют закрывающим скобкам. Эти цвета нелегко рассмотреть, но при желании — можно:
Цветные скобки
Во-вторых, когда вы закроете круглые скобки, Excel выделит соответствующую пару. Вы также можете щелкнуть в формуле и использовать клавишу со стрелкой для перемещения по скобкам, и Excel будет выделять обе скобки, когда есть совпадающая пара. Если нет совпадений, вы не увидите жирного шрифта.
Пара скобок выделена жирным
К сожалению, выделение шрифтом — это функция только для Windows. Если вы используете Excel на Mac для редактирования сложных формул, иногда имеет смысл скопировать и вставить формулу в хороший текстовый редактор, чтобы получить лучшие инструменты для сопоставления скобок. Вы можете вставить формулу обратно в Excel после того, как вы все исправите.