Двойные функции в excel

Логические функции в Excel. Часть №2

Доброго времени суток уважаемый читатель!

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

Главная характеристика работы логических функций, это проверка данных на соответствие результату «ЛОЖЬ» или «ИСТИНА», то есть когда полученный результат равен заданному условию, получаем результат «ИСТИНА», а «ЛОЖЬ» если условия не соответствуют. Вот, исходя из этих основ, я хочу объяснить их работу так, чтобы вы смогли понять логику работы данных функций и могли их эффективно использовать.

В наличии Excel владеет семью логическими функциями, но их может быть или меньше или больше, так как версии использования программы отличаются. Найти полный перечень доступных функций, вы можете в панели управления на вкладке «Формулы», в блоке «Библиотека функций» и нажав кнопку «Логические» с выпадающем перечнем логических функций. Или используя мастер функций:

А вот собственно и перечень логических функций в Excel, которые мы будем рассматривать во второй части этой статьи:

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

Простая логическая функция И, которая возвращает значение «ИСТИНА» при условии, что все аргументы отвечают истинному значению. Если же даже один аргумент из указанных вернет значение «ЛОЖЬ», то и вся функция также получит данное значение.

Для аргументов этой функции можно использовать математические, логические, текстовые и прочие условия, а также ссылки на ячейки которые могут вернуть логическое значение. Функция И имеет возможность оперировать 255 аргументами для получения результата, аргумент который будет первым — обязательный.

Более обширную информацию о функции вы узнаете с моей отдельной статьи «Функция И в Excel».

Синтаксис функции:

=И(_Логическое_значение№1_;_[Логическое_значение№2]_;…), где:

• Логическое значение№1 – является обязательным аргументом, проверка которого должна вернуть значение ЛОЖЬ или ИСТИНА;
• [Логическое значение№2] – это необязательный аргумент, который является дополнительным условиям проверки. Позволяет получить вычисляемое значение ЛОЖЬ или ИСТИНА. Таких условий позволяется вводить не более 255.

Пример:

Рассмотрим усложнённый пример функции И которая интегрирована как первый аргумент для функции ЕСЛИ, так функционал ее имеет урезанное использование при возвращении результата работы. Если значения в ячейках D2 и E2 больше 5, будет возвращен результат «Лимит превышен», если же хотя бы в одном из условий значение 5;E2>5);»Лимит превышен»;»В границах лимита»)

Следующая простая логическая функция ИЛИ может возвращать значение «ИСТИНА» при условии, что один из аргументов содержит истинное значение. Функция воспринимает как аргументы любые условия или ссылка на ячейки, которые возвращают логические значения. Доступно для использования 255 аргументов, первый из которых обязательный. Очень часто функцию ИЛИ используют для проверки значений в массиве и ее необходимо вводить как функцию массива с помощью горячего сочетания клавиш Ctrl+Shift+Enter.

Более детально возможности функции ИЛИ описаны в статье «Функции ИЛИ в Excel».

Синтаксис функции:

=ИЛИ(Логическое_значение№1;[логическое значение№2];. ), где:

• Логическое_значение№1 – является обязательным в использовании аргументом, вычисление которого дает результат ЛОЖЬ или ИСТИНА;
• [Логическое значение№2] – является необязательным дополнительным проверяемым условием, также дающим результат вычисления ИСТИНА или ЛОЖЬ. Количество таких условий не должно превышать 255.

Пример:

Функция ИЛИ также наделена узким диапазоном использования, а значит, самым рациональным будет ее интеграция в более продвинутую функцию, к примеру, функцию ЕСЛИ. Поскольку возвращаемые значения, рассматриваемой функции, бывают только «ИСТИНА» и «ЛОЖЬ», то ее, возможно, использовать как аргумент для логической выборки, используя формулу:

=ЕСЛИ(ИЛИ(D2>5;E2>5);»Лимит завышен»;»В пределах лимита»)

Как видите, если в любой из ячеек в критериях значение превышает цифру 10, результат будет «Лимит завышен», а если все ячейки содержат значение меньше 10, будет результат «В пределах лимита».

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

Синтаксис функции:

=ЕСЛИОШИБКА(_значение_;_значение_если_ошибка_), где:

• Значение – является обязательным аргументом и может быть ссылкой на ячейку, логическим выражением или любым значением, которое возвращается функцией, если ошибка не обнаружена;
• Значение если ошибка – являет собой любое значение, ссылка на ячейку или логическое выражение, которое возвращается, если первый аргумент вернет результат с ошибкой.

Пример:

В небольшом примере рассмотрим принцип действия функции ЕСЛИОШИБКА. Как видите, что при простом делении значений в ячейках возвращается результат с ошибкой, так как производится деление на 0 (у меня просто пустая ячейка). После того как я вложил это действие в первый аргумент функции, то ошибка стала перехватываться и производится подмена на второй аргумент « » (пустое текстовое поле) =ЕСЛИОШИБКА(D3/E3;»«) или же на информативную надпись «недопустимо» =ЕСЛИОШИБКА(D4/E4;»недопустимо»). В вашем случае это может быть любая другая функция в зависимости от задач, которые перед вами стоят.

Обращаю ваше внимание! Абсолютно все логические функции в Excel используют знаки сравнения «=», « », « =» или «<>» в своих выражениях, при работе которых получаются значения «ИСТИНА» и «ЛОЖЬ».

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

Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

Иногда деньги обходятся нам слишком дорого.
Ральф Уолдо Эмерсон (Ralph Waldo Emerson)

Функция IF (ЕСЛИ) в Excel. Как использовать?

Функция IF (ЕСЛИ) в Excel – это отличный инструмент для проверки условий на ИСТИНУ или ЛОЖЬ. Если значения ваших расчетов равны заданным параметрам функции как ИСТИНА, то она возвращает одно значение, если ЛОЖЬ, то другое.

Что возвращает функция

Заданное вами значение при выполнении двух условий ИСТИНА или ЛОЖЬ.

Синтаксис

=IF(logical_test, [value_if_true], [value_if_false]) – английская версия

=ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь]) – русская версия

Аргументы функции

• logical_test (лог_выражение) – это условие, которое вы хотите протестировать. Этот аргумент функции должен быть логичным и определяемым как ЛОЖЬ или ИСТИНА. Аргументом может быть как статичное значение, так и результат функции, вычисления;
• [value_if_true] ([значение_если_истина]) – (не обязательно) – это то значение, которое возвращает функция. Оно будет отображено в случае, если значение которое вы тестируете соответствует условию ИСТИНА;
• [value_if_false] ([значение_если_ложь]) – (не обязательно) – это то значение, которое возвращает функция. Оно будет отображено в случае, если условие, которое вы тестируете соответствует условию ЛОЖЬ.

Дополнительная информация

• В функции IF (ЕСЛИ) может быть протестировано 64 условий за один раз;
• Если какой-либо из аргументов функции является массивом – оценивается каждый элемент массива;
• Если вы не укажете условие аргумента FALSE (ЛОЖЬ) value_if_false (значение_если_ложь) в функции, т.е. после аргумента value_if_true (значение_если_истина) есть только запятая (точка с запятой), функция вернет значение “0”, если результат вычисления функции будет равен FALSE (ЛОЖЬ).
  На примере ниже, формула =IF(A1> 20,”Разрешить”) или =ЕСЛИ(A1>20;”Разрешить”) , где value_if_false (значение_если_ложь) не указано, однако аргумент value_if_true (значение_если_истина) по-прежнему следует через запятую. Функция вернет “0” всякий раз, когда проверяемое условие не будет соответствовать условиям TRUE (ИСТИНА).

|

Функция Если в Excel примеры с несколькими условиями

Пример 1. Проверяем простое числовое условие с помощью функции IF (ЕСЛИ)

При использовании функции IF (ЕСЛИ) в Excel, вы можете использовать различные операторы для проверки состояния. Вот список операторов, которые вы можете использовать:

Ниже приведен простой пример использования функции при расчете оценок студентов. Если сумма баллов больше или равна “35”, то формула возвращает “Сдал”, иначе возвращается “Не сдал”.

Пример 2. Использование вложенной функции IF (ЕСЛИ) для проверки условия выражения

Функция может принимать до 64 условий одновременно. Несмотря на то, что создавать длинные вложенные функции нецелесообразно, то в редких случаях вы можете создать формулу, которая множество условий последовательно.

В приведенном ниже примере мы проверяем два условия.

• Первое условие проверяет, сумму баллов не меньше ли она чем 35 баллов. Если это ИСТИНА, то функция вернет “Не сдал”;
• В случае, если первое условие – ЛОЖЬ, и сумма баллов больше 35, то функция проверяет второе условие. В случае если сумма баллов больше или равна 75. Если это правда, то функция возвращает значение “Отлично”, в других случаях функция возвращает “Сдал”.

Пример 3. Вычисляем сумму комиссии с продаж с помощью функции IF (ЕСЛИ) в Excel

Функция позволяет выполнять вычисления с числами. Хороший пример использования – расчет комиссии продаж для торгового представителя.

В приведенном ниже примере, торговый представитель по продажам:

• не получает комиссионных, если объем продаж меньше 50 тыс;
• получает комиссию в размере 2%, если продажи между 50-100 тыс
• получает 4% комиссионных, если объем продаж превышает 100 тыс.

Рассчитать размер комиссионных для торгового агента можно по следующей формуле:

В формуле, использованной в примере выше, вычисление суммы комиссионных выполняется в самой функции ЕСЛИ . Если объем продаж находится между 50-100K, то формула возвращает B2 * 2%, что составляет 2% комиссии в зависимости от объема продажи.

Пример 4. Используем логические операторы (AND/OR) (И/ИЛИ) в функции IF (ЕСЛИ) в Excel

Вы можете использовать логические операторы (AND/OR) (И/ИЛИ) внутри функции для одновременного тестирования нескольких условий.

Например, предположим, что вы должны выбрать студентов для стипендий, основываясь на оценках и посещаемости. В приведенном ниже примере учащийся имеет право на участие только в том случае, если он набрал более 80 баллов и имеет посещаемость более 80%.

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

Формула для этого расчета:

=IF(AND(B2>80,C2>80%),”Да”,”Нет”) – английская версия

=ЕСЛИ(И(B2>80;C2>80%);”Да”;”Нет”) – русская версия

Пример 5. Преобразуем ошибки в значения “0” с помощью функции IF (ЕСЛИ)

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

Формула для преобразования ошибок в ячейках следующая:

=IF(ISERROR(A1),0,A1) – английская версия

=ЕСЛИ(ЕОШИБКА(A1);0;A1) – русская версия

Формула возвращает “0”, в случае если в ячейке есть ошибка, иначе она возвращает значение ячейки.

ПРИМЕЧАНИЕ. Если вы используете Excel 2007 или версии после него, вы также можете использовать функцию IFERROR для этого.

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

=IF(ISBLANK(A1),0,A1) – английская версия

=ЕСЛИ(ЕПУСТО(A1);0;A1) – русская версия

Упрощаем бинарный поиск в Excel — реализация Double VLOOKUP Trick с помощью UDF

Предыстория

Все началось с того, что я открыл для себя т.н. Double-TRUE VLOOKUP trick (трюк с двойным использованием ВПР и ИСТИНА в 4-м параметре). Развернутое описание алгоритма можно найти в статье Charles Williams «Why 2 VLOOKUPS are better than 1 VLOOKUP» (в конце статьи).

Поняв принцип работы и открыв для себя, что этот подход может быть в тысячи раз быстрее обычного линейного поиска (ВПР с 4-м параметром ЛОЖЬ), я начал продумывать варианты раскрыть его возможности. В ходе реализации получилось несколько годных инструментов для контекстной рекламы, один из которых я еще продолжаю улучшать, и уже посвятил проекту пару статей на Хабре. Чтиво рекомендуется SEO-специалистам и специалистам по контекстной рекламе (сразу оговорюсь, по ссылкам в статьях уже устаревшие версии, последняя версия условно 6.0, ссылки на скачивание всех версий, включая самую свежую, будут в конце этой статьи):

» Анализ больших семантических ядер, или «Робот-распознаватель»
» Лемматизация в Excel, или «Робот-распознаватель 3.0

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

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

Синтаксис выглядит так:

Если(ВПР(искомое; массив;1; ИСТИНА)

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

Вместо «ИСТИНА» в 4-м параметре можно использовать «1» для номинального сокращения длины формул, это не меняет их сути.

Если озвучить ход работы формулы, будет нижеследующее:

«Если бинарный поиск ключа по первому столбцу массива возвращает значение, меньшее, чем сам ключ, возвращаем пустую строку. Иначе — возвращаем результат бинарного поиска ключа со смещением n».

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

Напомню, на карту поставлен прирост скорости, исчисляющийся трех-четырехзначными числами. Если подходить чисто математически — на массиве в 2^20 строк обычный бинарный поиск будет делать

10 вычислений, формула выше — около 20, в то время, как линейный поиск —

500.000, т.е. прирост формулы выше — в 25.000 раз. Если голые цифры не впечатляют, более красноречивое эквивалентное сравнение — 1 секунда против

На практике прирост не столь существенный (в конце статьи ссылка на статью, где сравнивались разные способы). Это во многом связано с затратами процессорного времени на дополнительные процедуры, которые выполняет программа (например, запись значений в ячейки). НО прирост по-прежнему критически значимый (

Но одновременно с этим мы имеем сложный, совершенно неюзабельный синтаксис. Не всем смертным дался ВПР, что говорить о комбинациях 2х ВПР с ЕСЛИ.

Вопрос со сложным синтаксисом я решил с помощью VBA — написал UDF (user-defined function, пользовательская функция), которая прячет под капот наши условные конструкции, оставляя нам привычный синтаксис всем известного ВПР.

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

Функция принимает на вход 3 параметра, синтаксис аналогичен обычному ВПР, за исключением 4-го параметра, т.к. он не нужен: (искомое; массив; номер столбца).

Так на выходе мы получили функцию с привычным синтаксисом и привычным поведением, но со скоростью, в десятки-сотни-тысячи раз быстрее обычного ВПР, в зависимости от длины массива. С единственным ограничением — функция работает корректно только на массиве, сортированном от меньшего к большему. Зачастую последний момент не является непреодолимым препятствием.

Логические функции Excel

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

= Больше или равно

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция ЕСЛИ

Функция ЕСЛИ (IF) имеет следующий синтаксис:

=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)

Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае — 20:

В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например:

=ЕСЛИ(А1>=4;»Зачет сдал»;»Зачет не сдал»)

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

Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например:

Эта формула возвращает значение 10, если ячейка А1 содержит строку «Динамо», и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

Функции И, ИЛИ, НЕ

Функции И (AND), ИЛИ (OR), НЕ (NOT) — позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

=И(логическое_значение1;логическое_значение2. )
=ИЛИ(логическое_значение1;логическое_значение2. )

Функция НЕ имеет только один аргумент и следующий синтаксис:

=НЕ(логическое_значение)

Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Приведем пример. Пусть Excel возвращает текст «Прошел», если ученик имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:

Если значение в ячейке А1 является целым числом, формула читается следующим образом: «Если значение в ячейке А1 равно 100, возвратить строку «Всегда». В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить «Обычно». В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку «Иногда». И, если ни одно из этих условий не выполняется, возвратить строку «Никогда». Всего допускается до 7 уровней вложения функций ЕСЛИ.

Функции ИСТИНА и ЛОЖЬ

Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:

=ИСТИНА()
=ЛОЖЬ()

Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение «Проходите», если выражение в ячейке А1 имеет значение ИСТИНА:

В противном случае формула возвратит «Стоп».

Функция ЕПУСТО

Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:

=ЕПУСТО(значение)

Аргумент значение может быть ссылкой на ячейку или диапазон. Если значение ссылается на пустую ячейку или диапазон, функция возвращает логическое значение ИСТИНА, в противном случае ЛОЖЬ.

В начало страницы

В начало страницы

Поиск и подстановка по нескольким условиям

Постановка задачи

Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно 🙂 — без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

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

Способ 1. Дополнительный столбец с ключом поиска

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы : Простой способ, знакомая функция, работает с любыми данными.

Минусы : Надо делать дополнительный столбец и потом, возможно, еще и прятать его от пользователя. При изменении числа строк в таблице — допротягивать формулу сцепки на новые строки (хотя это можно упростить применением умной таблицы).

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

Плюсы : Не нужен дополнительный столбец, решение легко масштабируется на большее количество условий (до 127), быстро считает.

Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

1. Выделите пустую зеленую ячейку, где должен быть результат.
2. Введите в строке формул в нее следующую формулу:

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).