Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как выполнить ВПР с учетом регистра или без учета регистра в Excel?

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

ВПЛ, чувствительные к записи

Комбинируя VLOOKUP, EXACT, MAX и ROW, мы можем создать формулу VLOOKUP с учетом регистра, которая возвращает соответствующее значение для нашего VLOOKUP с учетом регистра. Давайте рассмотрим пример.

Здесь есть список продуктов и их цены (обратите внимание, что идентификатор продукта чувствителен к регистру):

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

Для этого мы должны сначала создать вспомогательную колонку:

= STRING () Нажмите и перетащите (или дважды нажмите), чтобы предварительно заполнить все строки в диапазоне.

Затем соедините VLOOKUP, MAX, EXACT и ROW в следующей формуле:

Vlookup

Функция БПФ широко используется в Excel. На этой странице приведено множество простых примеров БПФ.

Точное совпадение

В большинстве случаев при использовании функции БПФ в Excel вы ищете точное совпадение. Рассмотрим аргументы функции VPR.

1. Как показано ниже, функция БПФ ищет значение 53 в крайнем левом столбце красной таблицы (второй аргумент).

Аргументы Vlookup

2. В соответствии со значением 4 (третий аргумент) функция БПФ вернет значение в той же строке из четвертого столбца красной таблицы.

Результат Vlookup в Excel

В четвертом аргументе логическое значение FALSE указывает функции VLOOKUP получить точное совпадение. Если функция VLOOKUP не сможет найти значение 53 в первом столбце, она вернет # N/A error .

3. Другой пример. Вместо того чтобы возвращать зарплату, функция VRP, показанная ниже, возвращает фамилию (третий аргумент установлен в 3) ID 79.

Читайте так же:
Как выбрать все ячейки формулы на листе?

Точное совпадение

Приблизительное совпадение

Рассмотрим пример функции БПФ в режиме приблизительного совпадения (четвертый аргумент установлен в TRUE).

1. Приведенная ниже функция БПФ ищет значение 85 (первый аргумент) в крайнем левом столбце красной таблицы (второй аргумент). Есть только одна проблема. В первой колонке нет значения 85.

Функция Vlookup в режиме приблизительного совпадения

2. В этом случае БПФ возвращает приблизительное совпадение, поскольку булево значение TRUE (четвертый аргумент) указывает, что значение должно быть TRUE. Если функция FFT не может найти значение 85 в первом столбце, она возвращает наибольшее значение, которое меньше 85. В данном примере это будет 80.

Наибольшее значение меньше, чем значение поиска

3. Третий аргумент функции FFT (второй аргумент) предписывает ей вернуть значение той же строки из второго столбца красной таблицы.

Примерное совпадение в Excel

Если функция БПФ используется в режиме приближенного совпадения (четвертый аргумент имеет значение TRUE), отсортируйте крайний левый столбец красной таблицы по возрастанию.

Vlookup выглядит правильно

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

1. В этом случае функция VPR ищет имя и возвращает фамилию.

Правильный поиск

2. Если изменить номер индекса столбца (третий аргумент) на 3, функция VRP найдет имя и вернет зарплату.

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

F FT не может найти имя в данном контексте и возвращает идентификатор. Используя VRP, вы можете видеть только справа. Не волнуйтесь, вы можете использовать ИНДЕКС и ПОИСК в Excel для поиска слева.

Первый матч

Она совпадает с первым экземпляром, если крайний левый столбец содержит дубликаты. Например, посмотрите на приведенную ниже функцию БПФ.

Первый матч

Функция WRP будет возвращать зарплату Мии Кларк, а не Мии Рид.

Vlookup нечувствителен к регистру

Функция БПФ в Excel выполняет поиск без учета регистра. Например, следующая функция БПФ ищет MIA (ячейка G2) в левом столбце таблицы.

Читайте так же:
Как выделить строки на основе нескольких значений ячеек в Excel?

Поиск без учета регистра

Пояснение: Функция VRP не чувствительна к регистру, поэтому она ищет MIA или Mia или mia или mia или mia или miA и т.д. и т.п. В результате функция VRP возвращает зарплату Мии Кларк (первый экземпляр). Используйте ИНДЕКС, ПОИСК и ВЫВОД в Excel для выполнения поиска с учетом регистра.

Множественные критерии

Вы хотите найти значение на основе нескольких критериев? Используйте ИНДЕКС и ПОИСК в Excel для поиска в двух столбцах.

Поиск по двум столбцам

Примечание: Приведенная выше формула ищет зарплату Джеймса Кларка, а не зарплату Джеймса Смита или Джеймса Андерсона.

# Н / Д ошибка

Ошибка № N/A возвращается, если функция VRP не может найти совпадение.

1. Например, приведенная ниже функция БПФ не может найти значение 28 в крайнем левом столбце.

# Н / Д Ошибка

(2) При желании можно использовать функцию IFNA, чтобы заменить ошибку #N/A понятным сообщением.

Функция Ифна

Примечание: Функция IFNA была введена в Excel 2013. Если вы используете Excel 2010 или Excel 2007, просто замените IFNA на ELIGIBILITY. Помните, что функция IFNA отлавливает и другие ошибки. Например, #NAME ? ошибка, если вы случайно неправильно написали слово ERR.

Несколько таблиц поиска

Когда вы используете функцию БПФ в Excel, вы можете иметь несколько таблиц поиска. Вы можете использовать функцию IF для проверки выполнения условия и возврата одной таблицы поиска, если TRUE, и другой таблицы поиска, если FALSE.

Создайте два диапазона с именами Table1 и Table2.

Первый стол

2. Функция БПФ показана ниже. Введите ее в ячейку E4.

Функция Vlookup с несколькими таблицами поиска

Пояснение: премия зависит от рынка (Великобритания или США) и от объема продаж. Второй аргумент в функции VLOOKUP делает свое дело. Если Великобритания, то функция VLOOKUP использует таблицу1, если США, то функция VLOOKUP использует таблицу2. Установите четвертый аргумент функции VLOOKUP на TRUE, чтобы получить приблизительное совпадение.

Читайте так же:
Как вставить пустые строки при изменении значения в Excel?

3. Нажмите Enter.

4 Выберите ячейку E4, нажмите в правом нижнем углу ячейки E4 и перетащите ее в ячейку E10.

Копировать функцию Vlookup

Примечание: Уокер, например, получает премию в размере 1500 долларов. Поскольку мы используем именованные диапазоны, мы можем легко скопировать эту функцию БПФ в другие ячейки, не заботясь о ссылках на ячейки.

Индекс и соответствие

Вместо WRP используйте INDEX и SEARCH. Для расширенного поиска используйте ИНДЕКС и ПОИСК. Это может быть слишком далеким шагом для вас на данном этапе, но это показывает одну из многих других мощных формул, которые может предложить Excel.

Индекс и соответствие

Xlookup

В Excel 365 вместо БПФ можно использовать XLOOKUP. С помощью XLOOKUP вы можете делать больше с меньшими усилиями.

Функция XLOOKUP в Excel

2/14 Завершено! Узнать больше о поиске и справочнике>
Перейти к следующей главе: Финансовые функции

12.Набор горячих клавиш Excel, без которых вам не обойтись

Применение этих сочетаний клавиш в Excel ускорит работу и поможет в выполнении анализа данных, построении графиков и форматировании таблиц.
F4 — при вводе формулы, регулирует тип ссылок (относительные, фиксированные). Можно использовать для повтора последнего действия.
Shift+F2 — редактирование примечаний
Ctrl+; — ввод текущей даты (для некоторых компьютеров Ctrl+Shift+4)
Ctrl+’ — копирование значений ячейки, находящейся над текущей (для некоторых компьютеров работает комбинация Ctrl+Shift+2)
Alt+F8 — открытие редактора макросов
Alt+= — суммирование диапазона ячеек, находящихся сверху или слева от текущей ячейки
Ctrl+Shift+4 — определяет денежный формат ячейки
Ctrl+Shift+7 — установка внешней границы выделенного диапазона ячеек
Ctrl+Shift+0 — определение общего формата ячейки
Ctrl+Shift+F — комбинация открывает диалоговое окно форматирования ячеек
Ctrl+Shift+L — включение/ отключение фильтра
Ctrl+S — сохранение файла (сохраняйтесь как можно чаще, чтобы не потерять ценные данные).

Читайте так же:
Как вставить или начать новую строку в ячейке Excel?

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector