Впр и если в excel примеры: инструкция на примере / Skillbox Media

Функция ВПР в Excel — Функции Ссылок и Массивов

Функция ВПР имеет один практичный нюанс, узнайте о нем в этой статье.

Оглавление

1. Введение
2. Функция ВПР — Описание
3. Синтаксис
4. Пример
5. Заключение

Введение

Функция ВПР является одной из наиболее часто используемых и универсальных при работе с данными в Excel.

Причем, ВПР расшифровывается как «Вертикальный Просмотр» .

Функция ВПР — Описание

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

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

Синтаксис

=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])

В рамках этой статьи заострим наше внимание на аргументе «интервальный_просмотр» .

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

Аргумент «интервальный_просмотр» может принимать два значения:

• ИСТИНА, при котором ищется значение ближайшее к критерию или совпадающее с ним — обозначается числом 1.
• ЛОЖЬ, при котором ведется поиск значения в точности совпадающее с критерием — обозначается числом 0.

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

И еще, если этот аргумент имеет значение ЛОЖЬ, функция ВПР ищет только точное совпадение.

Пример

В представленном перед вами рабочем листе содержатся 3 колонки и ключ в лице дисконтной таблицы.

Вообще, в аргументе «интервальный_просмотр» в 99% случаев приходится использовать значение ЛОЖЬ, то есть точное совпадение.

Но я покажу вам в этой статье тот самый 1%, когда оно бессильно и какую альтернативу применить.

Сперва хочу пояснить вам суть этой дисконтной таблицы:

• Если покупатель потратит от 0$ до 24.99$, то скидка для него составит 0%, то есть ее не будет
• При сумме заказа на сумму от 25$ до 49.99$ — скидка 5%.
• В случае, если клиент затратит от 50$ до 99.99$ — он будет иметь право на скидку в 10%.
• А если он раскошелится так, что от него доход составит от 100$ и выше — награда для него 20% скидки.

Наша задача узнать какие будут скидки по колонке B, имея в основании дисконтную таблицу.

Введем значение ЛОЖЬ (точное совпадение — «0») в аргументе функции ВПР «интервальный_просмотр» .

Как вы думаете, что из этого получится?

В большинствах ячейках Excel нам выводит ошибку #Н/Д, потому что не удается найти точное совпадение. Лишь в 2 мы получаем правильный ответ, потому что «Сумма Заказа» (колонка B) точь в точь совпадает с «Минимум» (колонка F).

Какая есть альтернатива?

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

И вот, совсем другое дело!

К примеру, сумма заказа составляет 82.68$, а в дисконтной таблице прописано, что от 50$ до 99.99$ полагается скидка в 10% — в колонке С так и выдается.

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

И еще, убедитесь, что ваши минимальные значения находятся в порядке возрастания, потому как функция ВПР будет сверху вниз искать подходящие значения.

Заключение

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

Мой свежий видеоурок по функции ВПР()

💡 Кстати, получите бесплатно мое PDF руководство по 6 самым распространенным ошибкам в формуле Excel, с которыми отчаянно борются многие!

📚 Список курсов.

Если вам понравилась данная статья, то обязательно поделитесь ею и подпишитесь!

💥💻📊 Мой девиз: Учись сегодня, создай что-то практичное и успешное завтра. Пусть он станет вашим!

31.05.2020

484

ВПР и СЧЁТЕСЛИ по части текста

А вы знали, что критерием поиска в ВПР или СУММЕСЛИ / СЧЁТЕСЛИ и т.д. может быть часть ячейки?

Покажу на примере.

Пример с функцией ВПР

Предположим, в желтую ячейку нужно вывести день рождения сотрудницы по имени Ирина. Критерий поиска — только имя.

Но ведь в таблице-источнике, в которой мы будем искать совпадение — нет столбца Имя. Только ФИО.

Если сразу хочется разделить столбец ФИО на Фамилию, имя и отчество и уже оттуда тянуть — не торопитесь.

Напишем в желтую ячейку формулу:

=ВПР(«*ирина*»;C2:D11;2;0)

где «*ирина*» — и есть тот самый критерий поиска по части ячейки.

Здесь используются два значка-оператора — кавычки и звездочка.

• звездочка * — заменяет любое количество символов. Т.е.перед и после слова ирина может находиться любое количество любых символов.
• кавычки «» — обязательно ставятся для текстового аргумента поиска в функциях excel.

Остальные аргументы стандартные для функции ВПР:

C2:D11 — таблица для поиска, здесь ее не закрепляем абсолютными ссылками, т.к. копировать формулу не будем.

2 — номер столбца в таблице, из которого будут возвращаться данные.

0 — аргумент интервальный просмотр.

Как видите, день рождения сотрудницы по имени Ирина (Лесиченко Ирина Ивановна) подтянулся правильно.

Пример с функцией СЧЁТЕСЛИ

На примере той же таблицы, найдем количество сотрудниц по имени Елена.

Напишем формулу:

=СЧЁТЕСЛИ(C2:C11;»*елена*»)

Где

C2:C11 — диапазон для поиска

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

Формула посчитала значение 3 — и действительно, в списке три сотрудницы с таким именем.

Аналогично этот прием работает в функциях СУММЕСЛИ и СУММЕСЛИМН.

Ловушка этого способа

В эту ловушку я тоже попадала.

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

Например, я считала количество сотрудников с именем Петр (буква ё не использовалась).

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

В таком случае после слова-критерия (или перед ним) нужно поставить пробел. В данном случае, критерием поиска будет «*Петр *» (с пробелом перед второй звездочкой).

Вам может быть интересно:

Функция ЕСЛИ – вложенные формулы и избежание ловушек

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

Таким образом, оператор IF может иметь два результата. Первый результат — если ваше сравнение истинно, второй — если ваше сравнение ложно.

Операторы

IF невероятно надежны и составляют основу многих моделей электронных таблиц, но они также являются основной причиной многих проблем с электронными таблицами. В идеале оператор ЕСЛИ должен применяться к минимальным условиям, таким как Мужчина/Женщина, Да/Нет/Возможно, и это лишь некоторые из них, но иногда вам может понадобиться оценить более сложные сценарии, требующие вложения* более 3 функций ЕСЛИ вместе.

* «Вложенность» означает объединение нескольких функций в одну формулу.

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

Синтаксис

ЕСЛИ(логическая_проверка, значение_если_истина, [значение_если_ложь])

Например:

Примечания

Хотя Excel позволяет вкладывать до 64 различных функций ЕСЛИ, делать это совсем не рекомендуется. Почему?

• Множественные операторы IF требуют тщательного обдумывания, чтобы правильно построить и убедиться, что их логика может правильно вычислять каждое условие до конца. Если вы не вложите формулу на 100 % точно, она может работать в 75 % случаев, но возвращать неожиданные результаты в 25 % случаев. К сожалению, шансы на то, что вы поймаете 25%, невелики.

• Сопровождение нескольких операторов IF может стать невероятно трудным, особенно когда вы возвращаетесь через некоторое время и пытаетесь выяснить, что вы или, что еще хуже, кто-то другой пытался сделать.

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

Давайте посмотрим, как правильно создать сложную вложенную инструкцию ЕСЛИ, используя несколько ЕСЛИ, и когда понять, что пришло время использовать другой инструмент в вашем арсенале Excel.

Примеры

Ниже приведен пример относительно стандартного вложенного оператора IF для преобразования результатов тестов учащихся в эквивалентные им буквенные оценки.

• org/ListItem»>

  =ЕСЛИ(D2>89,»A»,ЕСЛИ(D2>79,»B»,ЕСЛИ(D2>69,»C»,IF(D2>59,»D»,»F»))))

  Этот сложный вложенный оператор IF следует простой логике:

1. Если результат теста (в ячейке D2) больше 89, учащийся получает A

2. Если результат теста выше 79, учащийся получает B

3. Если результат теста выше 69, учащийся получает C

4. Если результат теста больше 59, то студент получает D

5. org/ListItem»>

   В противном случае учащийся получает F

Этот конкретный пример относительно безопасен, потому что маловероятно, что корреляция между результатами тестов и буквенными оценками изменится, поэтому он не потребует особого обслуживания. Но вот мысль — что, если вам нужно сегментировать оценки между A+, A и A- (и так далее)? Теперь нужно переписать оператор IF с четырьмя условиями, чтобы он содержал 12 условий! Вот как теперь будет выглядеть ваша формула:

• =ЕСЛИ(В2>97,»А+»,ЕСЛИ(В2>93,»А»,ЕСЛИ(В2>89,»А-«,ЕСЛИ(В2>87,»В+»,ЕСЛИ(В2>83,» В», ЕСЛИ(В2>79,»В-«, ЕСЛИ(В2>77,»С+»,ЕСЛИ(В2>73,»С»,ЕСЛИ(В2>69,»С-«,ЕСЛИ(В2>57) ,»D+»,ЕСЛИ(B2>53,»D»,ЕСЛИ(B2>49,»D-«,»F»))))))))))))

Он по-прежнему функционально точен и будет работать так, как ожидалось, но его написание и тестирование занимает много времени, чтобы убедиться, что он работает так, как вы хотите. Еще одна вопиющая проблема заключается в том, что вам приходилось вводить баллы и эквивалентные буквенные оценки вручную. Какова вероятность того, что вы случайно сделаете опечатку? А теперь представьте, что вы пытаетесь сделать это 64 раза с более сложными условиями! Конечно, это возможно, но вы действительно хотите подвергать себя таким усилиям и вероятным ошибкам, которые будет очень трудно обнаружить?

Совет: Каждая функция в Excel требует открывающей и закрывающей скобки (). Excel попытается помочь вам понять, что и куда нужно, окрашивая различные части формулы, когда вы ее редактируете. Например, если вы отредактируете приведенную выше формулу, то при перемещении курсора за каждую из закрывающих скобок «)» соответствующая открывающая скобка окрасится в тот же цвет. Это может быть особенно полезно в сложных вложенных формулах, когда вы пытаетесь выяснить, достаточно ли у вас совпадающих скобок.

Дополнительные примеры

Ниже приведен очень распространенный пример расчета комиссии с продаж на основе уровня достижения дохода.

• =ЕСЛИ(С9>15000,20%,ЕСЛИ(С9>12500,17,5%,ЕСЛИ(С9>10000,15%,ЕСЛИ(С9>7500,12,5%,ЕСЛИ(С9>5000,10%,0)) )))

Эта формула гласит: ЕСЛИ(C9 больше 15 000, то возвращается 20 %, ЕСЛИ(C9 больше 12 500, возвращается 17,5 % и т. д.)

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

Совет: Вы можете вставлять разрывы строк в строку формул, чтобы упростить чтение длинных формул. Просто нажмите ALT+ENTER перед текстом, который вы хотите перенести на новую строку.

Вот пример сценария комиссии с неисправной логикой:

Вы видите, что не так? Сравните порядок сравнения Revenue с предыдущим примером. В какую сторону идет этот? Правильно, она идет снизу вверх (от 5000 до 15000 долларов), а не наоборот. Но почему это должно быть таким большим делом? Это большое дело, потому что формула не может пройти первую оценку для любого значения, превышающего 5000 долларов. Допустим, у вас есть доход в размере 12 500 долларов – оператор IF вернет 10 %, потому что он больше 5 000 долларов, и на этом он остановится. Это может быть невероятно проблематично, потому что во многих ситуациях такие типы ошибок остаются незамеченными, пока они не окажут негативного влияния. Итак, зная, что сложные вложенные операторы IF сопряжены с некоторыми серьезными ловушками, что вы можете сделать? В большинстве случаев можно использовать функцию ВПР вместо построения сложной формулы с помощью функции ЕСЛИ. Используя функцию ВПР, сначала необходимо создать справочную таблицу:

Эта формула предлагает искать значение в C2 в диапазоне C5:C17. Если значение найдено, вернуть соответствующее значение из той же строки в столбце D.

Аналогично, эта формула ищет значение в ячейке B9 в диапазоне B2:B22. Если значение найдено, вернуть соответствующее значение из той же строки в столбце C.

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

VLOOKUP рассматривается здесь более подробно, но это, безусловно, намного проще, чем 12-уровневый сложный вложенный оператор IF! Есть и другие менее очевидные преимущества:

• Справочные таблицы

  VLOOKUP открыты и легко видны.

• org/ListItem»>

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

• Если вы не хотите, чтобы люди видели вашу справочную таблицу или мешали ей, просто поместите ее на другой рабочий лист.

Знаете ли вы?

Теперь есть функция IFS, которая может заменить несколько вложенных операторов IF одной функцией. Итак, вместо нашего примера с начальными оценками, который имеет 4 вложенные функции ЕСЛИ:

• =ЕСЛИ(D2>89,»A»,ЕСЛИ(D2>79,»B»,ЕСЛИ(D2>69,»C»,IF(D2>59,»D»,»F»))))

Это можно сделать намного проще с помощью одной функции IFS:

• org/ListItem»>

  =ЕСЛИ(D2>89,»A»,D2>79,»B»,D2>69,»C»,D2>59,»D»,ИСТИНА,»F»)

Функция IFS великолепна, потому что вам не нужно беспокоиться обо всех этих операторах IF и скобках.

Примечание. Эта функция доступна только при наличии подписки на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена ​​последняя версия Office.

Купить или попробовать Microsoft 365

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Связанные темы

Видео: расширенные функции ПЧ

Функция IFS (Microsoft 365, Excel 2016 и более поздние версии)

Функция СЧЁТЕСЛИ будет подсчитывать значения на основе одного критерия.

Функция СЧЁТЕСЛИМН будет подсчитывать значения на основе нескольких критериев.

Функция СУММЕСЛИ будет суммировать значения на основе одного критерия.

Функция СУММЕСЛИМН будет суммировать значения на основе нескольких критериев.

И-функция

Функция ИЛИ

Функция ВПР

Обзор формул в Excel

Как избежать некорректных формул

Обнаружение ошибок в формулах

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

функции Excel (по алфавиту)

функции Excel (по категориям)

НЕ в Excel (формула, примеры)

Категория «Логическая функция» включает встроенную функцию «НЕ»: логическая функция работает в рамках логического теста.

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

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

Примечание: Используйте функцию НЕ, если вы хотите убедиться, что значение не равно одному конкретному или конкретному значению

Это функция рабочего листа; Вы также можете использовать функцию НЕ как часть формулы в ячейке вместе с другими функциями Excel.

• Если задано значение ИСТИНА, функция НЕ возвращает ЛОЖЬ
• Если задано значение ЛОЖЬ, функция НЕ возвращает ИСТИНА

Формула НЕ в Excel

Ниже приведена формула НЕ в Excel:

Вы можете оценить значение как ИСТИНА или ЛОЖЬ в «логическом» аргументе встроенной функции « = НЕ(логическое) ».

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

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

Как использовать функцию НЕ в Excel?

Функция НЕ в Excel очень проста и удобна в использовании. Давайте теперь посмотрим, как использовать функцию НЕ в Excel с помощью нескольких примеров.

Вы можете скачать этот шаблон функции НЕ в Excel здесь – Шаблон функции НЕ в Excel

Пример #1 Функция НЕ в Excel

Логический тест выполняется на заданном наборе значений (оценка учащихся) с использованием функции НЕ. Здесь мы проверим, какое значение больше или равно 50 .

В таблице у нас есть 2 столбца, первый столбец содержит баллы учащихся, а второй столбец — это столбец логического теста, в котором выполняется функция НЕ.

Результат: Возвращает обратное значение, если значение больше или равно 50, затем возвращает ЛОЖЬ, а если меньше или равно 50, возвращает ИСТИНА в качестве вывода.

Результат будет таким, как показано ниже:

Пример #2 Использование функции НЕ с ЕПУСТО

Логический тест выполняется для ячеек H5 и H6 с использованием функции НЕ вместе с функцией ЕПУСТО; здесь проверим, являются ли ячейки H5 и H6 пустыми ИЛИ не используют функцию НЕ вместе с IПУСТО в Excel.

ВЫВОД будет ИСТИНА.

Пример #3 Функция НЕ вместе с функциями «ЕСЛИ» и «ИЛИ» или” функции.

Здесь мы должны выбрать цвет «БЕЛЫЙ» или «КРАСНЫЙ» из заданного набора данных

= ЕСЛИ (НЕ (ИЛИ (h21 = «КРАСНЫЙ», h21 = «БЕЛЫЙ»)), «x» «»,») используется формула

Мы применяем это логическое условие к столбцу под названием «Цвет», который содержит автомобили с цветами «КРАСНЫЙ» или «БЕЛЫЙ».

, если условие верно, то оно возвращает пустое значение в качестве вывода,

, если оно неверно, то оно возвращает x в качестве вывода

Пример #4 ЗНАЧЕНИЕ! Ошибка

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

Допустим, если мы задаем диапазон в логическом аргументе

Выбрали диапазон C22:D22 в логическом аргументе, т.е. =НЕ (C22:D22)

функция возвращает ошибку #ЗНАЧ, потому что функция НЕ допускает любой диапазон и может принимать только логический тест, аргумент или одно условие.

Пример #5 Функция НЕ для пустой ячейки или пробела или «0»

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

Здесь в ячейке «I23» хранится значение «0». Предположим, я применяю функцию «НЕ» с логическим аргументом или значением «0» или «I23»; вывод будет TRUE.

Пример #6 Функция НЕ для десятичных дробей

Когда значение в ячейке десятичное

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