Поиск по всем листам Excel

Обновлено: 03.07.2024

В Excel относительно легко искать значения на одной конкретной вкладке.

Но как найти значения на нескольких вкладках, используя только одну формулу?

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

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

В другом подходе используются имена и ссылки таблиц INDEX & MATCH и Excel.

Ключевым моментом здесь является то, что функция ДВССЫЛ действует как мессенджер, который динамически возвращает правильный адрес листа в различные формулы поиска.

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

Имейте в виду, что ДВССЫЛ — это изменчивая функция.

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

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

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

Данные на вкладках "Отдел" организованы простым способом, где они указывают сумму, выставленную в счете, за каждый месяц.

Этот макет одинаков для всех трех вкладок: раздел "Игры", раздел "Производительность" и раздел "Утилиты".

< /p>

Почему бы не использовать функцию ЕСЛИ()

Здесь можно использовать функцию IF(), настроив ее так, чтобы она проверяла, является ли дивизион игровым дивизионом. затем посмотрите на вкладке Game Div и на вкладке Productivity Div, если это подразделение Productivity Div, и т. д.

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

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

Решение 1. ВПР с использованием имен листов и ссылок на ячейки

Для начала давайте сначала напишем базовую формулу ВПР.

Мы также предполагаем, что Game Div исправлен и в отчете есть только эта вкладка.

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

Начните с ячейки C6.

Синтаксис функции ВПР():

lookup_value: поскольку вы знаете, что будете искать раздел Game Div. tab, это не обязательно должен быть аргумент. Искомое значение — это ячейка, содержащая месяц, ячейка B4. Поскольку вы хотите иметь возможность вытащить эту формулу вниз, исправьте эту ссылку на ячейку на $B$4.
table_array: перейдите в Game Div. выделите всю таблицу и добавьте еще несколько строк, чтобы включить будущие данные ("Game Div."!$A$4:$B$24)
col_index_num: указывает, на какой столбец смотреть. В этом случае, поскольку нам нужен второй столбец в области массива таблиц, мы используем 2.
range_lookup: выберите TRUE для приблизительного совпадения или false для точного совпадения. В этом примере нам нужно точное совпадение.

Однако, когда эта формула сводится к C8, мы получаем то же значение, поскольку наша вкладка привязана к Game Div..

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

Здесь пригодится функция ДВССЫЛ().

Эта функция получает правильный адрес из ячейки или текстовой ссылки. (Вот ссылка на руководство по использованию функции ДВССЫЛ().)

Синтаксис функции ДВССЫЛ():

Чтобы понять, как это работает, формула =ДВССЫЛ("A3") указывает функции перейти к ячейке A3 и возвращает значение в ячейке A3, то есть "Сводный отчет".

Точно так же, если вы используете другую ячейку (E5), содержащую «A1», и используете эту ячейку в качестве ref_text в формуле

он возвращает значение в ячейке A1.

Формула использует ячейку E5 в качестве адреса, который указывает формуле на ячейку A1.

Это была наша основная формула ВПР:

Где используется функция ДВССЫЛ?

Первый параметр lookup_value фиксирован, а также col_index_num и range_lookup.

Поскольку table_array меняется в зависимости от подразделения, здесь будет использоваться функция ДВССЫЛ().

Прежде чем применять функцию ДВССЫЛ(), стоит отметить, что если имена вкладок содержат пробелы, например «Game Div.», то любая ссылка на эту вкладку должна заключаться в одинарные кавычки, например «Игровой отдел»!.

Имя вкладки нельзя заменить ссылкой на ячейку, не поместив ее в функцию ДВССЫЛ(), поскольку функция ДВССЫЛ() преобразует имя вкладки как адрес для функции ВПР().

В качестве первого шага поместите аргумент table_array функции ВПР внутрь функции ДВССЫЛ.

Полная формула гласит:

Поскольку функция ДВССЫЛ() теперь включена в формулу, все, что осталось сделать, это сделать имена вкладок динамическими.

Замените «Game Div». со ссылкой на ячейку и не забудьте сохранить одинарные кавычки.

Параметр table_array теперь выглядит следующим образом:

Символ & используется для объединения одинарных кавычек и содержимого ячейки B6.

Окончательная формула выглядит следующим образом:

Растяните формулу до C8, и теперь она должна отображать правильные значения суммы счета.

Решение 2. Подход INDEX-MATCH с использованием имен таблиц

Этот подход включает преобразование всех данных на вкладках отдела в таблицы данных Excel.

Нажмите любую ячейку данных на вкладке "Отдел".

Нажмите CTRL + T, чтобы открыть окно "Создать таблицу".

При этом вам будет предложено указать область таблицы данных.

Это преобразует данные в таблицу данных Excel.

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

Укажите имя таблицы на вкладке "Дизайн".

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

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

Вернитесь на вкладку "Сводка" и создайте формулу, используя подход ИНДЕКС-ПОИСКПОЗ. (Вот ссылка на руководство по использованию функций ИНДЕКС() и ПОИСКПОЗ().)

Синтаксис функции ИНДЕКС():

массив: это область, где находится ответ.
row_num: сколько строк нужно пройти, чтобы найти ответ.
column_num: сколько столбцов нужно пройти вправо, чтобы найти ответ.

Как и прежде, давайте начнем с основной формулы.

Мы предполагаем, что наша единственная вкладка — Game Div.

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

Запустите формулу, введя:

Перейдите в раздел игр. и выберите столбец Сумма счета.

Вместо отображения ссылки на ячейку теперь будет отображаться как Game_Div.[Сумма счета].

Вторым параметром функции ИНДЕКС() является row_num.

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

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

Синтаксис функции ПОИСКПОЗ():

Формула соответствия становится следующей:

Окончательная формула выглядит следующим образом:

Перенос этой формулы в раздел Utility Div. row будет возвращать те же значения, так как они жестко закодированы для поиска внутри Game Div. вкладка.

Чтобы исправить это, мы воспользуемся функцией ДВССЫЛ(), которая поможет нам получить динамические имена вкладок.

Однако соглашение об именах в ячейках B6:B8 отличается от имен таблиц, поскольку в последних вместо пробела используется символ подчеркивания.

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

Функция ПОДСТАВИТЬ () поможет нам сделать эту замену.

В качестве первого шага давайте познакомимся с функцией ДВССЫЛ.

Добавьте функцию ДВССЫЛ(), заменив Game_Div. [Сумма по инвойсу] с INDIRECT("Game_Div.[Сумма по инвойсу]")

<р>2. Замените Game_Div. со ссылкой на ячейку B6 и объедините ее с заголовком таблицы [Сумма счета] с помощью символа &.

<р>3. Чтобы заменить пробел символом подчеркивания, используйте функцию ПОДСТАВИТЬ().

Синтаксис: ПОДСТАВИТЬ(текст,старый_текст,новый_текст,[номер_экземпляра]).

текст: ячейка, в которую вы хотите произвести замену.
old_text: какой конкретный символ вы хотите заменить. В данном случае это " ".
новый_текст: Чем заменить старый_текст. В данном случае это «_».
Instance_num: сколько раз мы хотим произвести замену. Это необязательный аргумент. Мы можем опустить его, что означает, что мы хотели бы, чтобы все экземпляры " " были заменены на "_".

=ПОДСТАВИТЬ(B6," ","_")&"[Сумма счета]"…

<р>4. Такую же замену следует применить к ссылке части ПОИСКПОЗ(). Окончательная формула теперь выглядит так:

Ячейка D6 = ИНДЕКС(ДВССЫЛ(ЗАМЕНИТЬ(B6," ",_")&"[Сумма счета-фактуры]"), ПОИСКПОЗ(Сводка!$B$4, ДВССЫЛ(ЗАМЕНИТЬ(B6," ", "_" &“[Дата]”),0))

<р>5. Перетащите формулу в ячейку D8.

С помощью этих двух методов вы можете автоматически искать значения на разных вкладках.

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

Видео и рабочая тетрадь

Вы можете скачать рабочую тетрадь ЗДЕСЬ.

Информационные панели Excel, которые информируют и впечатляют

Используйте эти приемы в своих отчетах

Непревзойденная цена!

16 комментариев

Привет, классная статья, спасибо за идеи.
В этой окончательной формуле решения Index-Match есть опечатка:

<р>4. Такую же замену следует применить к ссылке части ПОИСКПОЗ(). Окончательная формула теперь выглядит так:

Ячейка D6 = ИНДЕКС(ДВССЫЛ(ЗАМЕНИТЬ(B6," ",_")&"Сумма счета-фактуры]"), ПОИСКПОЗ(Сводка!$B$4, ДВССЫЛ(ЗАМЕНИТЬ(B6," ","_"& «[Дата]»),0))

В первом НЕПРЯМОМ сообщении в заголовке таблицы "Сумма счета" отсутствует [

Только что исправил формулу. Спасибо, что указали на это.

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

Это было потрясающе! Спасибо!

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

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

Не совсем с Index Match — для этого вам нужен упорядоченный набор данных. Если значения разбросаны, вы можете создать макрос, который перебирает каждый лист и возвращает адрес ячейки и листа, совпадение найдено, или возвращает значение соседней ячейки. Зависит от того, что вы хотите вернуть.

так как я изучил только функции Index и Match и думал о переключении на другой Div. через функцию IFS и получите тот же результат.

=IFS(B13="Игровой отдел",INDEX(Игровой_раздел.[Сумма счета],MATCH(Сводка!$B$11,Игровой_раздел.[Дата],0)),B13="Производственный отдел",INDEX (Productivity_Div.[Сумма по счету],MATCH(Сводка!$B$11,Productivity_Div.[Дата],0)),B13="Отдел_полезности.",INDEX(Div_Utility.[Сумма по счету],MATCH(Сводка!B11,Div_Полезности .[Дата],0)))

Я знаю, что это выглядит ужасающе, но оно основано на простой логике индексации и сопоставления.

СКАЖИТЕ МНЕ, КАК ЭТОТ СПОСОБ МОЖЕТ ПОКАЗАТЬ МЕНЯ?

Мне все еще нужно понять Indirect и заменить

Хорошее объяснение, действительно полезно.

У меня текущая версия 365, и я копирую формулу точно так, как вы поместили ее в свою таблицу, и она продолжает работать с ошибками. Я в недоумении, работаю над этим уже 2 дня. Так потеряно!

Я также сделал это через табличную версию, а также через индекс и сопоставление. Все равно появляются ошибки.

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

Спасибо – команда XelPlus

Мне нужно исправить формулу ниже, чтобы получить результат на листе 4
Пример Если я введу только 160011, должно появиться 1,8 в соответствии с ASA44
…. Пожалуйста, помогите мне

Лист1 № ОПЦИИ JOB ASV50
160011 РАДИАТОР 1.2

Лист2 № ОПЦИИ JOB ASA44
160011 РАДИАТОР 1.8

Лист3 ОПЦИЯ №. JOB AXV70
160011 РАДИАТОР 1.4

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

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

С более чем 25 тысячами участников и почти 30 тысячами сообщений ваше решение либо готово и ждет, либо может получить ответ быстрее, чем мы могли бы быть в состоянии.

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

Команда XelPlus

Вместо того, чтобы извлекать одно число из других вкладок, не могли бы вы СУММИРОВАТЬ несколько ячеек из каждой вкладки?

Чтобы выполнить поиск значения на нескольких листах книги и получить числовое значение, можно использовать формулу, основанную на функциях СЧЁТЕСЛИ и ДВССЫЛ. С некоторой предварительной настройкой вы можете использовать этот подход для поиска определенного значения во всей книге. В показанном примере формула в C5 выглядит так:

Контекст — пример данных

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

Диапазон B7:B9 содержит имена листов, которые мы хотим включить в поиск.Это просто текстовые строки, и нам нужно проделать некоторую работу, чтобы они распознавались как действительные ссылки на листы.

Действуя изнутри наружу, это выражение используется для построения полной ссылки на лист:

Одинарные кавычки добавляются, чтобы имена листов содержали пробелы, а восклицательный знак является стандартным синтаксисом для диапазонов, содержащих имя листа. Текст "1:1048576" представляет собой диапазон, включающий все строки на листе.

После вычисления B7 и объединения значений приведенное выше выражение возвращает:

который входит в функцию ДВССЫЛ в качестве аргумента 'ref_text'. ДВССЫЛ оценивает этот текст и возвращает стандартную ссылку на каждую ячейку в Sheet1. Это входит в функцию СЧЁТЕСЛИ как диапазон. Критерий предоставляется как абсолютная ссылка на C4 (заблокирована, поэтому формулу можно скопировать вниз по столбцу C).

СЧЕТЕСЛИ затем возвращает количество всех ячеек со значением, равным "mary", в данном случае 25.

Примечание. СЧЁТЕСЛИ регистр не учитывается.

Содержит или равно

Если вы хотите подсчитать все ячейки, содержащие значение в C4, а не все ячейки, равные C4, вы можете добавить подстановочные знаки к критериям, например:< /p>

Теперь СЧЁТЕСЛИ будет подсчитывать ячейки с подстрокой "Джон" в любом месте ячейки.

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

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

По возможности ограничивайте диапазоны до разумного размера. Например, если вы знаете, что данные не будут отображаться после строки 1000, вы можете выполнить поиск только в первых 1000 строк следующим образом:

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

Всё, что приходит мне в голову

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

Думайте об этом блоге как о своего рода рассаднике моих полусырых идей, отсюда и «вещи, которые приходят мне в голову».

Контактное лицо: @JoBrodie Электронная почта: jo DOT brodie AT gmail DOT com

Вторник, 12 ноября 2013 г.

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

<р>
1. Листы Excel
2. Google-таблицы

<р>1. Таблицы Excel
Теперь, когда я знаю, как это сделать, я не могу поверить, сколько времени я потратил, повторяя процесс по отдельности, выполняя поиск на каждой вкладке. Стыдно.

Спасибо @Richard_Black и @marklardner за объяснение.

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

Пользователи ПК – Ctrl+F открывает меню поиска.
Пользователи Mac – Command+F открывает меню поиска

<р>1. Ctrl+F или Command+F, чтобы открыть меню поиска
В моей версии это выглядит так

2. Нажмите кнопку «Параметры», и она будет выглядеть так: там, где написано «В пределах: лист» (настройка по умолчанию), измените это на «Рабочая книга». Затем вы можете переключаться между вкладками, нажимая Enter (аналогично нажатию «Найти далее») по мере продвижения.

<р>2. Таблицы Google
Не так уж и отличаются, см. серию изображений ниже.

Подождите, пока лист загрузится, если вы этого не сделаете, нажмите Ctrl+F, вы ищете "в браузере", а хотите искать "в листах", так что наберитесь терпения.

После загрузки нажмите Ctrl+F, и вы увидите диалоговое окно [ 1 ] в верхней правой части страницы (если вы видите диалоговое окно в левом нижнем углу окна браузера [ 1a ], обновите страницу и терпеливо подождите, это неправильно). Нажмите на три вертикальные точки, чтобы открыть параметры и выбрать один из них.

<р>. на «Все листы» [ 3 ], чтобы выполнять поиск по всему документу, а не только на текущей вкладке. После того, как вы введете поисковый запрос, ссылка «Найти» станет активной, продолжайте нажимать на нее, чтобы просмотреть результаты.

Изменить: 30 июля 2017 г.
Кто-то из Португалии очень хотел, чтобы я рассказал вам о ВПР. Я понятия не имею, что это такое (и в этом нет необходимости, поэтому, пожалуйста, не говорите мне), но вот собственная страница Microsoft об этом — они написали программное обеспечение, поэтому я предполагаю, что они знают, о чем говорят.

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

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

Как выполнять ВПР между двумя листами

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

Общая формула для ВПР с другого листа выглядит следующим образом:

В качестве примера давайте перетащим данные о продажах из отчета Январь на лист Сводка. Для этого мы определяем следующие аргументы:

Искомые_значения находятся в столбце A на листе Summary, и мы ссылаемся на первую ячейку данных, которая называется A2.
Table_array — это диапазон A2:B6 на листе за январь. Чтобы сослаться на него, добавьте к ссылке на диапазон имя листа, за которым следует восклицательный знак: Jan!$A$2:$B$6.

Соединяя аргументы, мы получаем следующую формулу:

=ВПР(A2, Январь!$A$2:$B$6, 2, ЛОЖЬ)

Перетащите формулу вниз по столбцу, и вы получите следующий результат:

Аналогичным образом вы можете выполнять визуальный поиск данных с листов Фев и Мар:

=ВПР(A2, февраль!$A$2:$B$6, 2, ЛОЖЬ)

=ВПР(A2, март!$A$2:$B$6, 2, ЛОЖЬ)

Если имя листа содержит пробелы или небуквенные символы, оно должно быть заключено в одинарные кавычки, например 'Jan Sales'!$A$2:$B$6. Дополнительную информацию см. в разделе Как создать ссылку на другой лист в Excel.
Вместо того, чтобы вводить имя листа непосредственно в формуле, вы можете переключиться на лист поиска и выбрать диапазон. Excel автоматически вставит ссылку с правильным синтаксисом, избавив вас от необходимости проверять имя и устранять неполадки.

Вид из другой книги

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

Например, чтобы найти значение A2 в диапазоне A2:B6 на листе Jan в книге Sales_reports.xlsx, используйте следующую формулу:

=ВПР(A2, [Sales_reports.xlsx]Ян!$A$2:$B$6, 2, ЛОЖЬ)

Виртуальный поиск по нескольким листам с помощью IFERROR

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

Чтобы увидеть, как этот подход работает с реальными данными, рассмотрим следующий пример. Ниже приведена таблица Summary, которую мы хотим заполнить названиями и суммами товаров, просматривая номер заказа на листах West и East: < бр />

Сначала мы вытащим элементы. Для этого мы указываем формуле ВПР искать номер заказа в A2 на листе East и возвращать значение из столбца B (2-й столбец в table_array A2:C6 ).Если точное совпадение не найдено, выполните поиск на листе Запад. Если оба ВПР не пройдены, возвращается "Не найдено".

=ЕСЛИОШИБКА(ВПР(A2, Восток!$A$2:$C$6, 2, ЛОЖЬ), ЕСЛИОШИБКА(ВПР(A2, Запад!$A$2:$C$6, 2, ЛОЖЬ), "Не найдено" ))

Чтобы вернуть сумму, просто измените номер индекса столбца на 3:

=ЕСЛИОШИБКА(ВПР(A2, Восток!$A$2:$C$6, 3, ЛОЖЬ), ЕСЛИОШИБКА(ВПР(A2, Запад!$A$2:$C$6, 3, ЛОЖЬ), "Не найдено" ))

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

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

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

=ЕСЛИОШИБКА(ВПР(A2, [Book1.xlsx]Восток!$A$2:$C$6, 2, ЛОЖЬ), ЕСЛИОШИБКА(ВПР(A2, [Book2.xlsx]Запад!$A$2:$C$6) , 2, ЛОЖЬ),"Не найдено"))

Сделать номер столбца динамическим для Vlookup нескольких столбцов

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

Для аргумента col_index_num используйте функцию COLUMNS, которая возвращает количество столбцов в указанном массиве: COLUMNS($A$1:B$1). (Координата строки не имеет особого значения, это может быть просто любая строка.)
В аргументе lookup_value заблокируйте ссылку на столбец с помощью знака $ ($A2), чтобы он оставался фиксированным при копировании формулы в другие столбцы.

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

=ЕСЛИОШИБКА(ВПР($A2, Восток!$A$2:$C$6, СТОЛБЦЫ($A$1:B$1), ЛОЖЬ), ЕСЛИОШИБКА(ВПР($A2, Запад!$A$2:$C$6) , COLUMNS($A$1:B$1), FALSE), "Не найдено"))

При вводе в столбец B значение COLUMNS($A$1:B$1) оценивается как 2, что указывает функции ВПР вернуть значение из второго столбца в массиве таблиц.

При копировании в столбец C (т. е. при перетаскивании формулы из B2 в C2) B$1 меняется на C$1, поскольку ссылка на столбец является относительной. Следовательно, COLUMNS($A$1:C$1) оценивается как 3, заставляя VLOOKUP возвращать значение из 3-го столбца.

Эта формула отлично подходит для 2–3 поисковых листов. Если их больше, повторяющиеся IFERROR становятся слишком громоздкими. Следующий пример демонстрирует немного более сложный, но гораздо более элегантный подход.

Виртуальный поиск нескольких листов с помощью НЕПРЯМОГО

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

Общая формула для Vlookup по листам выглядит следующим образом:

ВПР(искомое_значение, ДВССЫЛ("'"&ИНДЕКС(Листы_просмотра, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'" & Список_листов< /em> & "'!искомый_диапазон"), искомое_значение)>0), 0)) & "'!табличный_массив"), < em>col_index_num, FALSE)

Lookup_sheets – именованный диапазон, состоящий из имен поисковых листов.
Искомое_значение – значение для поиска.
Диапазон поиска — диапазон столбцов в таблицах поиска, в которых следует искать значение поиска.
Table_array – диапазон данных в таблицах поиска.
Col_index_num — номер столбца в массиве таблиц, из которого нужно вернуть значение.

Чтобы формула работала правильно, помните о следующих предостережениях:

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

Как использовать формулу для ВПР по листам

Чтобы выполнять визуальный просмотр нескольких листов одновременно, выполните следующие действия:

Запишите все имена поисковых листов где-нибудь в своей книге и назовите этот диапазон (в нашем случае Lookup_sheets).
Настройте общую формулу для своих данных. В этом примере мы будем:
- поиск значения A2 (lookup_value)
- в диапазоне A2:A6 (lookup_range) на четырех рабочих листах (Восток, Север, Юг и Запад) и
- извлечь совпадающие значения из столбца B, который является столбцом 2 (col_index_num) в диапазоне данных A2:C6 (table_array).

С приведенными выше аргументами формула принимает следующий вид:

=ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Просмотр_листов, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'"& Просмотр_листов&"'!$A$2:$A$6"), $A2) >0), 0)) &"'!$A$2:$C$6"), 2, ЛОЖЬ)

Обратите внимание, что мы блокируем оба диапазона ($A$2:$A$6 и $A$2:$C$6) абсолютными ссылками на ячейки.

Чтобы вернуть сумму, просто замените 2 на 3 в аргументе col_index_num, так как суммы находятся в 3-м столбце массива таблицы:

=ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Просмотр_листов, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'" & Просмотр_листов & "'!$A$2:$A$6")), $A2 )>0), 0)) & "'!$A$2:$C$6"), 3, ЛОЖЬ)

=IFNA(ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Лист_просмотра, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'" & Листы_просмотра & "'!$A$2:$A$6")), $A2)>0), 0)) & "'!$A$2:$C$6"), 3, FALSE), "Не найдено")

Виртуальный поиск нескольких листов между книгами

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

=IFNA(VLOOKUP($A2, INDIRECT("'[Book1.xlsx]" & INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'[Book1.xlsx]" & Lookup_sheets & "') !$A$2:$A$6"), $A2)>0), 0)) & "'!$A$2:$C$6"), 2, FALSE), "Не найдено")

Виртуальный поиск между листами и возврат нескольких столбцов

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

В этом примере мы хотим вернуть названия элементов (столбец B) и суммы (столбец C), которые являются 2-м и 3-м столбцами в массиве таблицы соответственно. Итак, требуемый массив .

=ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Лист_просмотра, ПОИСКПОЗ(1, --(СЧЁТЕСЛИ(ДВССЫЛ("'"& Таблицы_просмотра &"'!$A$2:$C$6")), $A2 )>0), 0)) &"'!$A$2:$C$6"), , FALSE)

Чтобы правильно ввести формулу в несколько ячеек, вам нужно сделать следующее:

В первой строке выберите все ячейки для заполнения (в нашем примере B2:C2).
Введите формулу и нажмите Ctrl + Shift + Enter . При этом в выбранные ячейки вводится одна и та же формула, которая возвращает разные значения в каждом столбце.
Перетащите формулу в оставшиеся строки.

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

Чтобы лучше понять логику, давайте разобьем эту базовую формулу на отдельные функции:

Действуя изнутри наружу, формула делает следующее:

СЧЁТЕСЛИ и ДВССЫЛ

В двух словах, ДВССЫЛ создает ссылки для всех листов поиска, а СЧЁТЕСЛИ подсчитывает количество вхождений значения поиска (A2) в каждом листе:

--(СЧЁТЕСЛИ( ДВССЫЛ("'"&Lookup_sheets&"'!$A$2:$A$6"), $A2)>0)

Сначала вы объединяете имя диапазона (Lookup_sheets) и ссылку на диапазон ($A$2:$A$6), добавляете апострофы и восклицательный знак в нужных местах, чтобы получилась внешняя ссылка, и передаете полученную текстовую строку в функция ДВССЫЛ для динамического обращения к таблицам поиска:

СЧЁТЕСЛИ сравнивает каждую ячейку в диапазоне A2:A6 на каждом листе подстановки со значением в A2 на основном листе и возвращает количество совпадений для каждого листа. В нашем наборе данных номер заказа в A2 (101) находится на листе West, который является 4-м в именованном диапазоне, поэтому COUNTIF возвращает этот массив:

Далее вы сравниваете каждый элемент приведенного выше массива с 0:

Это дает массив ИСТИННЫХ (больше 0) и ЛОЖНЫХ (равных 0) значений, которые вы приводите к 1 и 0 с помощью двойного унарного (--), и в результате получаете следующий массив: /p>

Эта операция является дополнительной мерой предосторожности для обработки ситуации, когда лист поиска содержит несколько вхождений искомого значения, и в этом случае СЧЁТЕСЛИ вернет число больше 1, в то время как нам нужны только 1 и 0 в конечном массиве (в момент, вы поймете, почему).

После всех этих преобразований наша формула выглядит следующим образом:

ВПР($A2, ДВССЫЛ("'"&ИНДЕКС(Лист_просмотра, ПОИСКПОЗ(1, , 0)) &"'!$A$2:$C$6"), 2, ЛОЖЬ)

ИНДЕКС и ПОИСКПОЗ

На этом этапе вступает в действие классическая комбинация INDEX MATCH:

ИНДЕКС(Листы поиска, ПОИСКПОЗ(1, , 0))

Функция ПОИСКПОЗ, настроенная на точное совпадение (0 в последнем аргументе), ищет значение 1 в массиве и возвращает его позицию, которая равна 4:

Функция ИНДЕКС использует число, возвращенное функцией ПОИСКПОЗ, в качестве аргумента номера строки (номер_строки) и возвращает четвертое значение в именованном диапазоне Просмотр_листов, то есть Запад.

Итак, формула сводится к следующему:

ВПР($A2, ДВССЫЛ("'"&" Запад "&"'!$A$2:$C$6"), 2, ЛОЖЬ)

ВПР и НЕПРЯМАЯ

Функция ДВССЫЛ обрабатывает текстовую строку внутри нее:

И преобразует его в ссылку, которая переходит в аргумент table_array функции ВПР:

ВПР($A2, 'Запад'!$A$2:$C$6 , 2, ЛОЖЬ)

Наконец, эта очень стандартная формула ВПР ищет значение A2 в первом столбце диапазона A2:C6 на листе Запад и возвращает совпадение из второго столбца. Вот и все!

Динамический ВПР для возврата данных с нескольких листов в разные ячейки

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

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

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

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

East_Sales — A2:B6 на листе "Восток"
North_Sales — A2:B6 на северном листе
Продажи на юге — A2:B6 на листе "Юг"
West_Sales – A2:B6 на листе West

ВПР и вложенные ЕСЛИ

Если у вас есть достаточное количество листов для поиска, вы можете использовать вложенные функции ЕСЛИ, чтобы выбрать лист на основе ключевых слов в предопределенных ячейках (в нашем случае ячейки с B1 по D1).

Для искомого значения в A2 формула выглядит следующим образом:

=ВПР($A2, ЕСЛИ(B$1="восток", Восток_Продажи, ЕСЛИ(B$1="север", Север_Продажи, ЕСЛИ(B$1="юг", Юг_Продажи, ЕСЛИ(B$1="запад" , West_Sales)))), 2, FALSE)

В переводе на английский часть ЕСЛИ гласит:

Если B1 – Восток, просмотрите диапазон с именем East_Sales; если B1 — Север, ищите в диапазоне с именем North_Sales; если B1 — Юг, ищите в диапазоне с именем Юг_Продажи; а если B1 — Запад, ищите в диапазоне с именем Запад_Продажи.

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

Умное использование смешанных ссылок для искомого значения ($A2 — абсолютный столбец и относительная строка) и логической проверки ЕСЛИ (B$1 — относительный столбец и абсолютная строка) позволяет копировать формулу в другие ячейки без каких-либо изменений — Excel автоматически корректирует ссылки в зависимости от относительного положения строки и столбца.

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

КОСВЕННЫЙ ВПР

При работе с большим количеством листов несколько уровней вложенности могут сделать формулу слишком длинной и трудной для чтения. Гораздо лучший способ — создать динамический диапазон vlookup с помощью INDIRECT:

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

Здесь мы объединяем ссылку на ячейку, содержащую уникальную часть именованного диапазона (B1) и общую часть (_Sales). Это создает текстовую строку, например "East_Sales", которая ДВССЫЛЕННО преобразуется в имя диапазона, понятное Excel.

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

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

Читайте также: