Поиск значений в excel на разных листах
Обновлено: 08.07.2024
Если необходимо найти какое-либо значение в большой таблице очень часто применяется функция ВПР. Но ВПР работает только с одной таблицей и нет никакой возможности средствами самой функции просмотреть искомое значение на нескольких листах. Если поиск необходимо осуществить только по двум листам, то можно схитрить:
=ВПР( A1 ;ЕСЛИ(ЕНД(ВПР( A1 ;Лист2!A1:B10;2;0));Лист3!A1:B10;Лист2!A1:B10);2;0)
Есть небольшой прием, который поможет искать значение в указанных листах. Для начала необходимо создать на листе список листов книги, в которых искать значение. В приложенном к статье примере они записаны в диапазоне $E$2:$E$5 .
=ВПР( A2 ;ДВССЫЛ("'"&ИНДЕКС( $E$2:$E$5 ;ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ("'"& $E$2:$E$5 &"'!A1:A50"); A2 )>0;0))&"'!A:B");2;0)
Формула вводится в ячейку как формула массива - т.е. сочетанием клавиш Ctrl+Shift+Enter. Это очень важное условие. Если формулу не вводить в ячейку как формулу массива, то необходимого результата не получить.
Попробую кратенько описать принцип работы данной формулы.
Перед чтением дальше советую скачать пример:
ВПР по всем листам (43,0 KiB, 20 229 скачиваний)
ДВССЫЛ нам нужна для преобразования текстового представления ссылок на листы в действительные. Подробно не буду останавливаться на принципе работы ДВССЫЛ, просто приведу этапы вычислений:
СЧЁТЕСЛИ(ДВССЫЛ("'"& $E$2:$E$5 &"'!A1:A50"); A2 )
В результате вычисления данного блока у нас получается массив из количества повторений искомого значения на каждом из указанных листов: СЧЁТЕСЛИ(;A2) . Поэтому следующий блок
ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ("'"& $E$2:$E$5 &"'!A1:A50"); A2 )>;0;0)
работает именно с этим:
ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(; A2 )>0;0)
Читать подробнее про СЧЁТЕСЛИ
в результате чего мы получаем позицию имени листа в массиве имен листов $E$2:$E$5 , с помощью ИНДЕКС получаем имя листа и подставляем это имя уже к ДВССЫЛ, а она в ВПР:
=ВПР( A2 ;ДВССЫЛ("'"&ИНДЕКС(;1)&"'!A:B");2;0) =>
=ВПР( A2 ;ДВССЫЛ("'Лист2'!A:B");2;0) =>
=ВПР( A2 ;'Лист2'!A:B;2;0)
Что нам и требовалось. Теперь если в книгу будут добавлены еще листы, то необходимо будет всего лишь дописать их к диапазону $E$2:$E$5 и при необходимости этот диапазон расширить. Так же можно задать диапазон $E$2:$E$5 как динамический и тогда необходимость в правке формулы отпадет вовсе.
Используемые в формуле величины:
A2 - ссылка на ячейку с искомым значением. Т.е. указывается то значение, которое требуется найти на листах.
$E$2:$E$5 - диапазон с именами листов, в которых требуется осуществлять поиск указанного значения ( A1 ).
Диапазон "'!A1:A50" - это диапазон, в котором СЧЁТЕСЛИ ищет совпадения. Поэтому указывается только один столбец данных. При необходимости следует расширить или изменить. Можно указать так же "'!A:A" , но при этом следует учитывать, что указание целого столбца может привести к значительному увеличению времени выполнения функции. Поэтому имеет смысл просто задать диапазон с запасом, например "'!A1:A10000" .
"'!A:B" - диапазон для аргумента ВПР - Таблица. В первом столбце этого диапазона на каждом из указанных листов ищется указанное значение ( A2 ). При нахождении возвращается значение из указанного столбца. Читать подробнее про ВПР>>
В примере к статье так же можно посмотреть формулу, которая для каждого значения подставляет имя листа, в котором это значение было найдено.
ВПР по всем листам (43,0 KiB, 20 229 скачиваний)
Так же можно искать по нескольким листам разных книг , а не только по нескольким листам одной книги. Для этого необходимо будет в списке листов вместе с именами листов добавить имена книг в квадратных скобках: [Книга1.xlsb]Май
[Книга1.xlsb]Июнь
[Книга2.xlsb]Май
[Книга2.xlsb]Июнь
Перечисленные книги обязательно должны быть открыты
Решил добавить простенькую функцию пользователя(UDF) для тех, кому проще "общаться" с VBA, чем с формулами. Функция ищет указанное значение во всех листах книги, в которой записана(даже в скрытых):
Function VLookUpAllSheets(vCriteria As Variant, rTable As Range, lColNum As Long, Optional iPart As Integer = 1) As Variant Dim rFndRng As Range If iPart <> 1 Then iPart = 2 For i = 1 To Worksheets.Count If Sheets(i).Name <> Application.Caller.Parent.Name Then With Sheets(i) Set rFndRng = .Range(rTable.Address).Resize(, 1).Find(vCriteria, , xlValues, iPart) If Not rFndRng Is Nothing Then VLookUpAllSheets = rFndRng.Offset(, lColNum - 1).Value Exit For End If End With End If Next i End Function
Функция попроще, чем ВПР - последний аргумент(интервальный_просмотр) выполняет несколько иные, чем в ВПР функции. Хотя полагаю немногие его используют в классическом варианте.
rTable - указывается таблица для поиска значений(как в стандартной ВПР)
vCriteria - указывается ссылка на ячейку или текстовое значение для поиска
lColNum - указывается номер столбца в таблице rTable, значение из которого необходимо вернуть - может быть ссылкой на столбец - СТОЛБЕЦ().
iPart - указывается метод просмотра. Если не указан, либо указана цифра 1, то поиск осуществляется по полному совпадению с ячейкой. Но в таком варианте допускается применение подстановочных символов * и ?. Если указано значение, отличное от 1, то совпадение будет отбираться по части вхождения. Если в vCriteria указать "при", то совпадением будет считаться и слово "прибыль"(первый буквы совпадают) и "неприятный"(в середине встречается "при"). Но в этом случае знаки * и ? будут восприниматься "как есть". Может пригодиться, если в искомом тексте присутствуют символы звездочки и вопросительного знака и надо найти совпадения, учитывая эти символы.
Вы когда-нибудь задумывались о поиске определенного значения на нескольких листах или книгах в Excel? В этом руководстве представлены различные методы решения проблем, связанных с поиском на нескольких листах или поиском в нескольких книгах.
Быстрый поиск значения в нескольких открытых книгах с помощью Kutools for Excel
Поиск значения на нескольких листах книги с помощью функции поиска и замены
С помощью Excel Найти и заменить функция, вы можете найти определенное значение на нескольких листах.
1. Выберите несколько вкладок листов, на которых вы хотите найти значение, удерживая Ctrl и щелкнув листы в Панель вкладок листа по одному. Смотрите скриншот:
2. Затем нажмите Ctrl + F для Найти и заменить окна и введите значение, которое вы хотите найти, в Найти то, что текстовое поле под Найдите Вкладка, а затем нажмите кнопку Найти все кнопку, чтобы просмотреть все результаты. Смотрите скриншот:
Поиск и замена значений на листах и в книгах
Искать значение во всех книгах папки с VBA
Если вы хотите найти значение во всех закрытых книгах из папки, вы можете применить только VBA для его решения.
1. Включите новую книгу и выберите ячейку, затем нажмите Alt + F11 ключи для открытия Microsoft Visual для базовых приложений окно.
2. Нажмите Вставить > Модуль и вставьте ниже VBA в новое окно модуля.
VBA: поиск значения во всех книгах папки.
3. Затем нажмите F5 ключа или Запустите кнопку для выполнения этого VBA и Выберите папку Появится диалоговое окно, напоминающее вам о выборе папки, в которой вы хотите искать значение. Смотрите скриншот:
4. Затем щелкните OK и другое диалоговое окно появляется, чтобы напомнить вам, сколько ячеек было найдено. Смотрите скриншот:
5. Нажмите OK в Закрыть его, и все найденные ячейки будут перечислены на текущем листе с соответствующей информацией.
Наконечник: В приведенном выше VBA вы ищете значение «КТЕ» , и вы можете изменить «КТЕ» из этого xStrSearch на другое значение по мере необходимости.
Быстрый поиск значения в нескольких открытых книгах с помощью Kutools for Excel
Если вы просто хотите найти значение в нескольких открытых книгах, вы можете использовать Kutools для Excel 'продвинутый Найти и заменить утилита.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. В одной из открытых книг щелкните Kutools > Навигация, а затем нажмите Найти и заменить кнопка идти на Найти и заменить панель. Смотрите скриншот:
2. Затем нажмите Найдите вкладка и введите значение, которое вы хотите найти в Найти то, что текстовое поле, а затем выберите Все книги из В раскрывающийся список и нажмите Найти все чтобы перечислить все найденные ячейки. Смотрите скриншот:
Наконечник:
Работы С Нами Kutools for Excelпродвинутый Найти и заменить Утилита, вы можете искать и заменять значения на выбранных листах в нескольких книгах, во всех книгах, активной книге, активном листе или выборе.
Заранее благодарю за любую помощь!
__________________Помощь в написании контрольных, курсовых и дипломных работ здесь
Поиск заданного значения в нескольких листах и при нахождении перенос значений в итоговый лист
Боссу на работе нужно автоматизировать поиск в таблицах excel. Есть отдельный файл excel где.
Поиск и копирование определенных значений в определенные ячейки в листах книги
никогда не писал макросы. поверхностно знаю формулы. - задача такова: есть книга в ней.
Поиск нужных строк в HTML-файлах по заданному ключу и запись определённых слов из найденных строк в CSV-файл
Есть 100 файлов в папке BDVK с форматом .html Нужно сделать программу которая ищет в html файле по.
Как организовать поиск в другой книге или в этой же книге, поиск по слову на определенных листах
Всем привет!) Как организовать поиск в другой книге или в этой же книге, поиск по слову на.
Для каждого листа получится своя колонка, в каждую из которых вбейте формулу для каждого листа, они же разные.
kasan, с ВПР знаком только отчасти, пытаясь как раз решить эту задачу.
Данные одни и те же, только расположены они по-разному (в разных столбцах) и разные названия столбцов.
Т.е. для первого листа все данные будут подтягиваться в одни и те же ячейки.
Скажем, лист 1, столбец1, строка2.
В листе2 ищем значение ячейки А2 листа1 в столбце G и при совпадении, копируем некоторые значения этой строки (например, D2 Листа2 копируем в B2 Листа1, F2 в С2). В листе3 данные те же, но они в других столбцах.
Таким образом нужно все значения столбца А в Листе1 искать в трех других листах и подтягивать нужные данные. Совпадений на этих трех листах не может быть (т.е. искомое значение не может быть одновременно, например, в листе2 и листе3, а только в каком-то из них).
Множество листов в одном доке. Первый столбец у всех одинаковый (например, название). У меня есть макрос, который выделяет на одном листе повторяющиеся значения РАЗНЫМ цветом. Хотелось бы, чтобы и на других тоже выделялось ТЕМ ЖЕ цветом, что и на первом. Как реализовать?
Я бы использовал коллекции, в которых можно отсекать дубликаты значений. Раз вы привыкли к выделению блоков, то вот пример перебора выделенных блоков в листах справа от активного.
Спасибо за ответ!
Получается, если на первом листе не будет значений больше 1, то он не будет подсвечиваться, даже, если они имеются на других?
Если прописать = 0, то на первом листе будут подсвечены ВСЕ значения => и на других листах тоже.
Придется выбирать между этими вариантами.
Да. Одновременно с этим участвует условие добавление значения в коллекции с исходного (активного) листа BaseSheetIndex = ActiveSheet.Index . Только учтите, что количество значений индекса палитры, который вы изначально решили применять, довольно ограничено Excel 2010 и 2013 по разному кодируют цвета? И вообще где почитать про различия для разных версий офиса?
Дѣаволъ, простите, не могли бы вы мне помочь? Можно ли реализовать поиск дублей по ВСЕМ листам сразу? Чтобы не удалялось автоматически, а пользователь смог принять решение - удалять или нет? Или, хотя бы, одинаковым цветом выделить для всех, например, только розовым?
То есть: первый лист смотрит на другие и сверяет со своим списком; потом второй лист сверяет свои значения со всеми, включая ПЕРВЫЙ и т.д.
Буду очень благодарен, спасибо!
AlexCruel, создайте новый вопрос (как требуют правила площадки). Под ним будет ссылка "Пригласить эксперта".
Читайте также: