Клавиши поиска в excel: Сочетания клавиш в Excel — Служба поддержки Майкрософт

Содержание

Поиск в Экселе – комбинация клавиш, какая формула соответствует, как искать по нужному слову, фразе, одному или нескольким значениям в Excel

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

Поиск в Excel

Самый простой и быстрый способ искать по слову, фразе, формуле и т.д в документе это использовать комбинацию клавиш Ctrl и F одновременно. Появится следующее окно.

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

Второй вариант поиска в Excel — на главной странице нажать вкладку «Найти и выделить». Выбираем «Найти», вводим нужные символы и запускается поиск.

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

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

В поле можно использовать подстановочные знаки * и ?.

Обычно их применяют в таких ситуациях:

  1. * — может быть использована для замены букв. К примеру, если ввести «к*т», то найдутся все слова, которые начинаются на букву «к» и заканчиваются на «т», кит, кот и т.д. Звездочка заменяет как один символ, так и любое количество. Т.е. в нашем примере найдутся и слова кабинет, кабриолет и т.д.
  2. ? — такой подстановочный знак заменяет только одну букву. Например, «к?т», выйдут слова, которые состоят из трех букв «кот», «кит».

Настройки поиска

Если необходимо установить определенные настройки, то в окне необходимо кликнуть «Параметры».

Тут можно задать следующие настройки:

  • в строке «Искать» можно ограничить поиск только листом;
  • в строке «Просматривать» можно выбрать вариант просмотра по строкам или по столбцам;
  • строка «Область» позволяет выбрать искомые символы: формула либо значение;
  • отметив галкой «Учитывать регистр» производится нахождение искомой комбинации с учетом регистра;
  • если необходимо увидеть ячейку, которая содержит только заданные символы, нужно отметить «Ячейка целиком»;
  • во вкладке «Формат» можно выбрать искомое форматирование.

Нажимая кнопку «Заменить», можно сразу заменить найденное на указанные значения.

В строку «Найти» нужно ввести искомую комбинацию значений, а в строке «Заменить» те символы, на которые следует заменить найденные.

Поиск по указанному интервалу

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

Этим способом ограничивается диапазон для нахождения комбинации.

Порядок действий:

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

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

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

Приведем пример поиска нескольких значений одновременно. В таблице указана разная цена различного гранита. Необходимо с помощью функции ВПР определить цену гранита 7.

Составим таблицу.

Формула будет в ячейке Е4, но можно использовать любую другую. Затем придерживаемся алгоритма:

  1. В ячейке ставим знак равенства и прописываем функцию: «=ВПР(».
  2. Аргумент 1 это Гранит7. Пишем «ВПР(«Гранит7»».
  3. Аргумент 2 – это ячейки А4:А20. Формула получается такой: «ВПР(«Гранит7»; А4:А20;».
  4. Аргумент 3, цифра 2, т.к. ищем значение во втором столбце. «ВПР(«Гранит7»; А4:А20;2».
  5. Аргумент 4, дает понять функции, какое значение нужно искать, точное или приблизительное. Оно может быть ИСТИНА или ЛОЖЬ. ИСТИНА – приблизительно совпавшие значения, а ЛОЖЬ – точные. Мы ищем точные. Выглядит запись так: «ВПР(«Гранит7»; А4:А20;2;ЛОЖЬ)» или «=ВПР(A10;A4:B20;2;ЛОЖЬ)».
  6. Кликаем по Enter и получаем искомое.

Аналогичным способом можно добавлять условия в более сложных таблицах.

К примеру, добавим в таблицу столбец с месяцем. Попробуем увидеть в каком месяце  Гранит17 стоит 1016.

Составляем формулу, добавляя новые значения. Аргументом 3 будет цифра 3.

Нажимаем ввод и получаем  месяц «май».

Функция поиск в Excel

Также для обнаружения можно использовать функции ПОИСК и НАЙТИ.

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

Пример: найдем положение буквы «а» в слове Гранит6. Введем формулу =НАЙТИ(«а»;A9;3) и получим 3, т.е. «а» третья буква.

Теперь рассмотрим функцию ПОИСК. В данной формуле можно использовать подстановочные символы (? — один, * — несколько). Например, найдем в той же ячейке положение цифры 6. Введем формулу =ПОИСК(«6»;A9), получаем 8.

Поиск в Эксель: способы нахождения нужного слова

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

Содержание

  1. Простой поиск
  2. Расширенный поиск
  3. Разновидности поиска
  4. Поиск совпадений
  5. Фильтрация
  6. Видео: Поиск в таблице Excel

Простой поиск

Чтобы произвести поиск значения в таблице Excel, необходимо на вкладке «Главная» открыть выпадающий список инструмента «Найти и заменить» и щёлкнуть пункт «Найти». Тот же эффект можно получить, используя сочетание клавиш Ctrl + F.

В простейшем случае в появившемся окне «Найти и заменить» надо ввести искомое значение и щёлкнуть «Найти всё».

Как видно, в нижней части диалогового окна появились результаты поиска. Найденные значения подчёркнуты красным в таблице. Если вместо «Найти все» щёлкнуть «Найти далее», то сначала будет произведён поиск первой ячейки с этим значением, а при повторном щелчке – второй.

Аналогично производится поиск текста. В этом случае в строке поиска набирается искомый текст.

Если данные или текст ищется не во всей экселевской таблице, то область поиска предварительно должна быть выделена.

Расширенный поиск

Предположим, что требуется найти все значения в диапазоне от 3000 до 3999. В этом случае в строке поиска следует набрать 3???. Подстановочный знак «?» заменяет собой любой другой.

Анализируя результаты произведённого поиска, можно отметить, что, наряду с правильными 9 результатами, программа также выдала неожиданные, подчёркнутые красным. Они связаны с наличием в ячейке или формуле цифры 3.

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

Щёлкнув «Параметры», пользователь получает возможность осуществлять расширенный поиск. Прежде всего, обратим внимание на пункт «Область поиска», в котором по умолчанию выставлено значение «Формулы».

Это означает, что поиск производился, в том числе и в тех ячейках, где находится не значение, а формула. Наличие в них цифры 3 дало три неправильных результата. Если в качестве области поиска выбрать «Значения», то будет производиться только поиск данных и неправильные результаты, связанные с ячейками формул, исчезнут.

Для того чтобы избавиться от единственного оставшегося неправильного результата на первой строчке, в окне расширенного поиска нужно выбрать пункт «Ячейка целиком». После этого результат поиска становимся точным на 100%.

Такой результат можно было бы обеспечить, сразу выбрав пункт «Ячейка целиком» (даже оставив в «Области поиска» значение «Формулы»).

Теперь обратимся к пункту «Искать».

Если вместо установленного по умолчанию «На листе» выбрать значение «В книге», то нет необходимости находиться на листе искомых ячеек. На скриншоте видно, что пользователь инициировал поиск, находясь на пустом листе 2.

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

При поиске в документах Microsoft Excel, можно использовать и другой подстановочный знак – «*». Если рассмотренный «?» означал любой символ, то «*» заменяет собой не один, а любое количество символов. Ниже представлен скриншот поиска по слову Louisiana.

Иногда при поиске необходимо учитывать регистр символов. Если слово louisiana будет написано с маленькой буквы, то результаты поиска не изменятся. Но если в окне расширенного поиска выбрать «Учитывать регистр», то поиск окажется безуспешным. Программа станет считать слова Louisiana и louisiana разными, и, естественно, не найдёт первое из них.

Разновидности поиска

Поиск совпадений

Иногда бывает необходимо обнаружить в таблице повторяющиеся значения. Чтобы произвести поиск совпадений, сначала нужно выделить диапазон поиска. Затем, на той же вкладке «Главная» в группе «Стили», открыть инструмент «Условное форматирование». Далее последовательно выбрать пункты «Правила выделения ячеек» и «Повторяющиеся значения».

Результат представлен на скриншоте ниже.

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

Фильтрация

Другая разновидность поиска – фильтрация. Предположим, что пользователь хочет в столбце B найти числовые значения в диапазоне от 3000 до 4000.

  1. Выделить первый столбец с заголовком.
  2. На той же вкладке «Главная» в разделе «Редактирование» открыть инструмент «Сортировка и фильтр», и щёлкнуть пункт «Фильтр».
  3. В верхней строчке столбца B появляется треугольник – условный знак списка. После его открытия в списке «Числовые фильтры» щёлкнуть пункт «между».
  4. В окне «Пользовательский автофильтр» следует ввести начальное и конечное значение плюс OK.

Как видно, отображаться стали только строки, удовлетворяющие введённому условию. Все остальные оказались временно скрытыми. Для возврата к начальному состоянию следует повторить шаг 2.

Различные варианты поиска были рассмотрены на примере Excel 2010. Как сделать поиск в эксель других версий? Разница в переходе к фильтрации есть в версии 2003. В меню «Данные» следует последовательно выбрать команды «Фильтр», «Автофильтр», «Условие» и «Пользовательский автофильтр».

Видео: Поиск в таблице Excel

Лучшие сочетания клавиш для поиска в Excel

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

Для поиска определенного значения на листе нажмите Ctrl + F на клавиатуре. Это откроет диалоговое окно «Найти и заменить». Введите искомое значение в поле «Найти», а затем нажмите кнопку «Найти все». Excel выделит все ячейки, содержащие искомое значение.

Если вы хотите найти определенное значение в определенном столбце, вы можете использовать сочетание клавиш Ctrl + Shift + F . Откроется диалоговое окно «Найти и заменить», но в раскрывающемся меню «Искать в» установлено значение «Формулы». Введите искомое значение в поле «Найти», а затем нажмите кнопку «Найти все». Excel выделит все ячейки в столбце, содержащие искомое значение.

Если вы хотите найти все ячейки, содержащие определенный фрагмент текста, вы можете использовать сочетание клавиш Ctrl + * . Это выделит все ячейки, содержащие текст. Затем вы можете использовать сочетание клавиш Ctrl + F для поиска нужного текста. Excel выделит все ячейки, содержащие искомый текст.

Если вы хотите найти все пустые ячейки, вы можете использовать Ctrl + Shift + Пробел ярлык. Это выделит все ячейки на листе. Затем вы можете использовать сочетание клавиш Ctrl + F для поиска слова «пусто». Excel выделит все пустые ячейки.

Если вы хотите найти все непустые ячейки, вы можете использовать сочетание клавиш Ctrl + Пробел . Это выделит все ячейки на листе. Затем вы можете использовать сочетание клавиш Ctrl + F для поиска слова «пусто». Excel выделит все непустые ячейки.

Если вы хотите найти все ячейки, содержащие определенное значение, вы можете использовать сочетание клавиш Ctrl + Shift + 7 . Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, содержащие искомое значение.

Если вы хотите найти все ячейки, не содержащие определенного значения, вы можете использовать Ctrl + Shift + 8 ярлык. Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, которые не содержат искомое значение.

Если вы хотите найти все ячейки, которые больше определенного значения, вы можете использовать сочетание клавиш Ctrl + Shift + 9 . Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, которые больше искомого значения.

Если вы хотите найти все ячейки, которые меньше определенного значения, вы можете использовать сочетание клавиш Ctrl + Shift + 0 . Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, которые меньше искомого значения.

Если вы хотите найти все ячейки, которые равны определенному значению, вы можете использовать Ctrl + Shift + = ярлык. Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, которые равны искомому значению.

Если вы хотите найти все ячейки, которые не равны определенному значению, вы можете использовать Ctrl + Shift + ! Ярлык . Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, которые не равны искомому значению.

Если вы хотите найти все ячейки, содержащие определенное значение, вы можете использовать сочетание клавиш Ctrl + Shift + @ . Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, содержащие искомое значение.

Если вы хотите найти все ячейки, не содержащие определенного значения, вы можете использовать Ctrl + Shift + # Ярлык . Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, которые не содержат искомое значение.

Если вы хотите найти все ячейки, которые больше определенного значения, вы можете использовать ярлык Ctrl + Shift + $ . Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, которые больше искомого значения.

Если вы хотите найти все ячейки, которые меньше определенного значения, вы можете использовать сочетание клавиш Ctrl + Shift + % . Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, которые меньше искомого значения.

Если вы хотите найти все ячейки, которые равны определенному значению, вы можете использовать Ctrl 9Ярлык . Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, которые равны искомому значению.

Если вы хотите найти все ячейки, которые не равны определенному значению, вы можете использовать сочетание клавиш Ctrl + Shift + ~ . Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, которые не равны искомому значению.

Если вы хотите найти все ячейки, содержащие определенное значение, вы можете использовать сочетание клавиш Ctrl + Shift + и . Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, содержащие искомое значение.

Если вы хотите найти все ячейки, не содержащие определенного значения, вы можете использовать Ctrl + Shift + _ ярлык. Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, которые не содержат искомое значение.

Если вы хотите найти все ячейки, которые больше определенного значения, вы можете использовать сочетание клавиш Ctrl + Shift + + . Откроется диалоговое окно «Перейти». Введите искомое значение в поле «Ссылка» и нажмите кнопку «ОК». Excel выделит все ячейки, которые больше искомого значения.

Если вы хотите найти все ячейки меньше

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

Найти и заменить

Самый простой способ поиска информации в Excel — использовать функцию «Найти и заменить». Чтобы получить доступ к этой функции, нажмите Ctrl + F. Это откроет диалоговое окно «Найти и заменить», где вы можете ввести текст, который вы ищете, и указать, хотите ли вы искать во всей книге или только на текущем листе.

Если вы хотите заменить найденный текст чем-то другим, вы можете использовать сочетание клавиш Ctrl + H, чтобы открыть диалоговое окно «Найти и заменить» с выбранной вкладкой «Заменить».

Перейти

Если вам нужно быстро перейти к определенной ячейке или диапазону ячеек, вы можете использовать функцию Перейти. Чтобы получить доступ к этой функции, нажмите Ctrl + G. Это откроет диалоговое окно «Перейти», где вы можете ввести ссылку на ячейку или диапазон, к которому хотите перейти.

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

Фильтрация

Если вы работаете с большим набором данных и вам нужно быстро найти определенную информацию, вы можете использовать функцию фильтрации Excel. Чтобы включить фильтрацию, выберите диапазон ячеек, которые вы хотите отфильтровать, и нажмите Ctrl + Shift + L. Это добавит стрелки фильтра вверху каждого столбца в выбранном диапазоне.

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

Автофильтр

Если вам часто нужно фильтровать данные определенным образом, вы можете использовать функцию автофильтра Excel, чтобы сэкономить время. Чтобы включить автофильтр, выберите диапазон ячеек, которые вы хотите отфильтровать, и дважды нажмите Ctrl + Shift + L.

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

Заключение

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

Расширенный ВПР в Excel: множественный, двойной, вложенный

Эти примеры научат вас выполнять ВПР по нескольким критериям, возвращать конкретный экземпляр или все совпадения, выполнять динамический ВПР на нескольких листах и ​​т. д.

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

Прежде чем двигаться дальше, позвольте мне кратко напомнить вам синтаксис:

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

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

Как выполнить визуальный поиск нескольких критериев

Функция Excel VLOOKUP очень полезна, когда речь идет о поиске в базе данных определенного значения. Однако ему не хватает важной функции — его синтаксис допускает только одно значение поиска. Но что, если вы хотите найти несколько условий? Есть несколько различных решений на ваш выбор.

Формула 1. ВПР с двумя критериями

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

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

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

Итак, добавьте столбец слева от таблицы и скопируйте приведенную ниже формулу в этот столбец. Это заполнит вспомогательный столбец значениями из столбцов B и C (символ пробела соединен между ними для лучшей читаемости):

=B2&""&C2

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

=ВПР("Джереми Свитс", A2:D11, 4, ЛОЖЬ)

Или введите критерии в отдельные ячейки (в нашем случае G1 и G2) и соедините эти ячейки:

=ВПР(G1&""&G2, A2:D11, 4, ЛОЖЬ)

Поскольку мы хотим вернуть значение из столбца D, который является четвертым в массиве таблиц, мы используем 4 для col_index_num . Аргумент range_lookup имеет значение FALSE для Vlookup точного совпадения. На скриншоте ниже показан результат:

Если ваша таблица поиска находится на другом листе , включите имя листа в формулу ВПР. Например:

=ВПР(G1&""&G2, Заказы!A2:D11, 4, ЛОЖЬ)

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

=ВПР(G1&""&G2, Заказы, 4, ЛОЖЬ)

Дополнительные сведения см. в разделе Как выполнить визуальный поиск с другого листа в Excel.

Примечание. Чтобы формула работала правильно, значения во вспомогательном столбце должны быть объединены точно так же, как в аргументе искомое_значение . Например, мы использовали символ пробела для разделения критериев как во вспомогательном столбце (B2&» «&C2), так и в формуле ВПР (G1&» «&G2).

Формула 2. ВПР Excel с несколькими условиями

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

К счастью, Microsoft Excel часто предоставляет несколько способов сделать одно и то же. Для поиска нескольких критериев можно использовать либо комбинацию ПОИСКПОЗ ИНДЕКС, либо функцию XLOOKUP, недавно представленную в Office 365.

Например, для поиска по трем различным значениям ( Дата , Имя клиента и Продукт ) используйте одну из следующих формул:

=ИНДЕКС(D2:D11, ПОИСКПОЗ(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))

=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)

Где:

  • G1 – критерий 1 (дата)
  • G2 — критерий 2 (имя заказчика)
  • G3 — критерий 3 (продукт)
  • A2:A11 — диапазон поиска 1 (даты)
  • B2:B11 — диапазон поиска 2 (имена клиентов)
  • C2:C11 — диапазон поиска 3 (продукты)
  • D2:D11 — диапазон возврата (количество)

Примечание. Во всех версиях, кроме Excel 365, ПОИСКПОЗ ИНДЕКС следует вводить как формулу массива CSE, нажав Ctrl + Shift + Enter. В Excel 365, который поддерживает динамические массивы, он также работает как обычная формула.

Подробное объяснение формул см.:

  • XLOOKUP с несколькими критериями
  • Формула INDEX MATCH с несколькими критериями

Как использовать функцию ВПР, чтобы 2

-й , 3 -й или n -й соответствовали

Как вы уже знаете, ВПР Excel может выбрать только одно совпадающее значение, точнее, возвращает первое найденное совпадение. Но что, если в вашем массиве поиска есть несколько совпадений, и вы хотите получить 2 -й или 3-й -й экземпляр? Задача звучит довольно запутанно, но решение существует!

Формула 1. ВПР N-й экземпляр

Предположим, у вас есть имена клиентов в одном столбце, продукты, которые они приобрели, в другом, и вы ищете продукт 2 nd или 3 rd , купленный данным клиентом.

Самый простой способ — добавить вспомогательный столбец слева от таблицы, как мы сделали в первом примере. Но на этот раз мы заполним его именами клиентов и номерами экземпляров, такими как « Джон Доу1 «, « Джон Доу2 » и т. д.

Чтобы получить вхождение, используйте функцию СЧЁТЕСЛИ со ссылкой на смешанный диапазон (первая ссылка является абсолютной, а вторая — относительной, например, $B$2:B2). Поскольку относительная ссылка изменяется в зависимости от положения ячейки, в которую копируется формула, в строке 3 она станет $B$2:B3, в строке 4 — $B$2:B4 и так далее.

В сочетании с именем клиента (B2) формула принимает следующий вид:

=B2&СЧЁТЕСЛИ($B$2:B2, B2)

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

После этого введите целевое имя и номер вхождения в отдельные ячейки (F1 и F2) и используйте приведенную ниже формулу для поиска конкретного вхождения:

=ВПР(F1&F2, A2:C11, 3, ЛОЖЬ)

Формула 2.

ВПР 2-е вхождение

Если вы ищете 2-й экземпляр искомого значения, вы можете обойтись без вспомогательного столбца. Вместо этого создайте массив таблиц динамически, используя функцию ДВССЫЛ вместе с ПОИСКПОЗ:

=ВПР(E1, НЕПРЯМАЯ("A"&(СООТВЕТСТВИЕ(E1, A2:A11, 0)+2)&":B11"), 2, ЛОЖЬ)

Где:

  • E1 — это значение поиска
  • A2:A11 — это диапазон поиска

    • .

  • B11 — последняя (нижняя правая) ячейка справочной таблицы

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

= ВПР(E1, НЕПРЯМАЯ("A"&(СООТВЕТСТВИЕ(E1, A2:A11, 0)+1+СТРОКА(A1))&":B11"), 2, ЛОЖЬ)

Как работает эта формула

Вот ключевая часть формулы, которая создает динамический диапазон просмотра :

.

НЕПРЯМАЯ("A"&(ПОИСКПОЗ(E1, A2:A11, 0)+2)&":B11")

Функция ПОИСКПОЗ, настроенная на точное совпадение (0 в последнем аргументе), сравнивает целевое имя (E1) со списком имен (A2:A11) и возвращает позицию первого найденного совпадения, в нашем случае это 3. Это число будет использоваться в качестве начальной координаты строки для диапазона vlookup, поэтому мы добавляем к нему 2 (+1, чтобы исключить первый экземпляр, и +1, чтобы исключить строку 1 с заголовками столбцов). Кроме того, вы можете использовать 1+ROW(A1) для автоматического расчета необходимой корректировки на основе положения строки заголовка (в нашем случае A1).

В результате мы получаем следующую текстовую строку, которую ДВССЫЛ преобразует в ссылку на диапазон:

ДВССЫЛ ("A"&5&":B11") -> A5:B11

Этот диапазон переходит к аргументу table_array функции ВПР, заставляя его начать поиск в строке 5, пропуская первый экземпляр искомого значения:

ВПР(E1, A5:B11, 2, ЛОЖЬ)

Как выполнять ВПР и возвращать несколько значений в Excel

Функция ВПР Excel предназначена для возврата только одного совпадения. Есть ли способ использовать Vlookup для нескольких экземпляров? Да, есть, хоть и не из легких. Это требует комбинированного использования нескольких функций, таких как ИНДЕКС, МАЛЕНЬКИЙ и СТРОКА — формула массива.

Например, приведенный ниже код может найти все вхождения искомого значения F2 в диапазоне поиска B2:B16 и вернуть несколько совпадений из столбца C:

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$11, МАЛЕНЬКИЙ(ЕСЛИ($F$1=$B$2:$B$11, СТРОКА($C$2:$C$11)-1,""), СТРОКА ()-1)),"")}

Есть 2 способа ввести формулу на лист:

  1. Введите формулу в первую ячейку, нажмите Ctrl + Shift + Enter, а затем перетащите ее вниз еще на несколько ячеек.
  2. Выберите несколько соседних ячеек в одном столбце (F1:F11 на скриншоте ниже), введите формулу и нажмите Ctrl + Shift + Enter, чтобы завершить ее.

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

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

Как выполнять поиск в строках и столбцах (двусторонний поиск)

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

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

С именами элементов в A2:A9, названиями месяцев в B1:F1, целевым элементом в I1 и целевым месяцем в I2 формула выглядит следующим образом:

= ВПР(I1, A2:F9, ПОИСКПОЗ(I2, A1:F1, 0), ЛОЖЬ)

Как работает эта формула

Ядром формулы является стандартная функция ВПР, которая ищет точное совпадение с искомым значением в I1. Но так как мы не знаем, в какой именно колонке находятся продажи за конкретный месяц, мы не можем поставить номер колонки напрямую в col_index_num аргумент. Чтобы найти этот столбец, мы используем следующую функцию ПОИСКПОЗ:

.

СОВПАДЕНИЕ(I2, A1:F1, 0)

В переводе на английский формула гласит: найдите значение I2 в A1:F1 и верните его относительное положение в массиве. Указав 0 в третьем аргументе, вы указываете ПОИСКПОЗ найти значение, в точности равное искомому значению (это похоже на использование ЛОЖЬ для аргумента range_lookup функции ВПР).

Поскольку март находится в 4-м столбце массива поиска, функция ПОИСКПОЗ возвращает 4, что соответствует непосредственно 9-му столбцу.0205 col_index_num аргумент функции ВПР:

ВПР(I1, A2:F9, 4, ЛОЖЬ)

Обратите внимание, хотя названия месяцев начинаются в столбце B, мы используем A1:I1 для массива поиска. Это делается для того, чтобы число, возвращаемое ПОИСКПОЗ, соответствовало положению столбца в table_array ВПР.

Чтобы узнать больше о том, как выполнять матричный поиск в Excel, см. ИНДЕКС ПОИСКПОЗ ПОИСКПОЗ и другие формулы для двумерного поиска.

Как сделать множественный ВПР в Excel (вложенный ВПР)

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

На рисунке ниже показана ситуация:

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

Для удобства сначала создадим пару именованных диапазонов:

  • Таблица поиска 1 названа Продукты (D3:E10)
  • Таблица поиска 2 названа Цены ( G3:h20 )

Таблицы могут находиться на одном или разных рабочих листах.

А теперь выполним так называемый двойной ВПР , он же вложенный ВПР .

Сначала создайте формулу ВПР, чтобы найти название продукта в таблице поиска 1 (с именем Продукты ) на основе идентификатора элемента (A3):

=ВПР(A3, Произведения, 2, ЛОЖЬ)

Затем поместите приведенную выше формулу в аргумент lookup_value другой функции ВПР, чтобы получить цены из таблицы поиска 2 (с именем Цены ) на основе имени продукта, возвращенного вложенной функцией ВПР:

=ВПР(ВПР(A3, Товары, 2, ЛОЖЬ), Цены, 2, ЛОЖЬ)

На приведенном ниже снимке экрана показана наша вложенная формула Vlookup в действии:

Как динамически выполнять Vlookup на нескольких листах

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

Это может быть легче понять на примере. Допустим, у вас есть несколько региональных отчетов о продажах в одном формате, и вы хотите получить данные о продажах определенного продукта в определенных регионах:

Как и в предыдущем примере, мы начинаем с определения нескольких имен:

  • Диапазон A2:B5 на листе CA называется CA_Sales .
  • Диапазон A2:B5 на листе FL называется FL_Sales .
  • Диапазон A2:B5 на листе KS называется KS_Sales .

Как видите, все именованные диапазоны имеют общую часть ( Sales ) и уникальные части ( CA , FL , KS ). Обязательно назовите свои диапазоны аналогичным образом, так как это важно для формулы, которую мы собираемся построить.

Формула 1. НЕПРЯМАЯ ВПР для динамического извлечения данных из разных листов

Если ваша задача состоит в извлечении данных из нескольких листов, формула ВПР ДВССЫЛ — лучшее решение — компактное и простое для понимания.

Для этого примера мы организуем сводную таблицу следующим образом:

  • Введите интересующие продукты в A2 и A3. Это наши значения поиска.
  • Введите уникальные части именованных диапазонов в B1, C1 и D1.

Теперь мы объединяем ячейку, содержащую уникальную часть (B1), с общей частью («_Sales») и передаем полученную строку в ДВССЫЛ:

НЕПРЯМОЙ(B$1&"_Продажи")

Функция ДВССЫЛ преобразует строку в имя, понятное Excel, и вы помещаете его в аргумент table_array функции ВПР:

=ВПР($A2, ДВССЫЛ(B$1&"_Продажи"), 2, ЛОЖЬ)

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

Обратите внимание, что в искомом значении ($A2) мы заблокировали координату столбца с абсолютной ссылкой на ячейку, чтобы столбец оставался фиксированным при копировании формулы вправо. В ссылке B$1 мы заблокировали строку, потому что мы хотим, чтобы координата столбца изменялась, и снабжали соответствующей частью имени ДВССЫЛ в зависимости от столбца, в который копируется формула:

Если ваша основная таблица организована по-другому, значения поиска в строке и уникальные части имен диапазонов в столбце, то вы должны заблокировать координату строки в значении поиска (B$1) и координату столбца в частях имени ( $А2):

=ВПР(B$1, ДВССЫЛ($A2&"_Продажи"), 2, ЛОЖЬ)

Формула 2.