Эксель поиск в другом файле
Обновлено: 04.07.2024
Здравствуйте. Поиск данных с помощью ВПР в другом файле практически ничем не отличается от классического способа, когда все таблицы собраны в одной рабочей книге. Однако, здесь есть некоторые особенности, которые следует учитывать.
Напомню, ВПР записывается так:
=ВПР( искомое_значение ; массив_с_данными ; номер_выводимого_столбца ; способ_поиска )
Аргумент «массив с данными» как раз указывает на диапазон, в котором искать нужное значение. Обычно он записывается примерно так: A1:C10. Однако, для поиска в другом документе, формуле нужно дополнительно указать название рабочей книги и листа, на котором хранится таблица с данными. Вот так:
Правила создания ссылок на другой документ
- Сначала в квадратных скобках указывается название документа с расширением;
- Далее следует имя листа и восклицательный знак
- Если в имени файла или листа есть пробелы – эта часть (до восклицательного знака (см пример в таблице) – обрамляется в одинарные кавычки
- За восклицательным знаком записываем диапазон ячеек
Неудобно вручную записывать такую ссылку, поэтому лучше предварительно открыть книгу с исходной таблицей, и при записи формулы выделить нужный массив мышью. Весь синтаксис программа оформит сама.
Вот, как это выглядит в реальном документе. Здесь ВПР ищет значение в документе «прайс», на листе «дилерам», в диапазоне B3:C21.
Ничего сложного, но вы должны понимать особенности такой связи между документами:
- Когда файл, на который вы ссылаетесь, закрыт, программа добавляет к рассмотренной выше ссылке его полный адрес
- Если оба файла открыты, изменения в исходной таблице сразу же «подтягиваются» и в результаты работы ВПР
- Если, при открытии книги с ВПР, исходный документ закрыт, программа предложит обновить связанные значения. Согласитесь, или обновите их позже вручную (на ленте: Данные – Подключения – Обновить все )
Как видите, поиск в другой книге лишь немного сложнее обычного и не создает никаких трудностей. Главное – понимать, как это работает. Если вы после прочтения этой статьи не до конца поняли что к чему – пишите свои вопросы в комментариях!
В этой статье рассказывается о том, как искать значения и возвращать данные из другой книги, а также как находить / искать значения из другой книги. Здесь я подробно расскажу о трех решениях.
Данные Vlookup и возвращаемые значения из другой книги в Excel
Например, вы создаете таблицу «Покупка фруктов» в Excel, и теперь вам нужно просмотреть фрукты из другой книги и вернуть соответствующие цены, как показано на скриншотах ниже. Здесь я расскажу вам, как решить эту проблему с помощью функции VLOOKKUP в Excel.
1. Откройте обе книги, из которых вы будете просматривать значения и возвращать значения.
2. Выберите пустую ячейку, в которую вы вернете цену, введите формулу = ВПР (B2; [Price.xlsx] Sheet1! $ A $ 1: $ B $ 24,2; FALSE) в него, а затем перетащите его маркер заполнения, чтобы применить эту формулу к диапазону по мере необходимости.
Заметки:
(1) В приведенной выше формуле B2 - это плод, который вы будете искать в другой книге, Price.xlsx указывает имя файла книги, из которой вы будете искать, Sheet1 означает имя листа, из которого вы будете искать, и A $ 1 : $ B $ 24 - это диапазон, из которого вы будете искать. Вы можете изменить их по своему усмотрению.
(2) После закрытия книги, из которой вы искали, формула автоматически обновится до = ВПР (B2; 'W: \ test \ [Price.xlsx] Sheet1'! $ A $ 1: $ B $ 24,2, FALSE) , W: \ test \ - это путь сохранения книги, из которой вы искали.
Пока все цены вернулись правильно, как показано на скриншоте слева. И эти цены будут обновляться автоматически, если исходная книга, которую вы искали, изменилась.
 | Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем! Подробнее . Бесплатная пробная версия |
Данные Vlookup и возвращаемые значения из другой закрытой книги с VBA
Может возникнуть путаница при настройке пути сохранения, имени файла и рабочего листа в функции VLOOKUP. Этот метод позволит легко решить эту проблему с помощью VBA.
1. нажмите другой + F11 клавиши, чтобы открыть окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модуль, а затем вставьте ниже код VBA в открывающееся окно модуля.
VBA: данные Vlookup и возвращаемые значения из другой закрытой книги
Внимание: Этот VBA будет возвращать значения в столбце, который находится на 2 столбца позади выбранного столбца. Например, я выбираю столбец B при применении этого VBA, значения возвращаются в столбце D. Если вам нужно изменить столбец назначения, узнайте код xRg.Offset (0, 2) .Formula = xString & GetColumn (xFCell.Column + 1) & "$" & xFCell.Row и заменить 2 на другой номер по мере необходимости.
3. нажмите F5 ключ или щелкните Запустите кнопку для запуска этого VBA.
4. В открывшемся диалоговом окне укажите диапазон данных, который вы будете искать, и щелкните значок OK кнопку.
5. Теперь выберите книгу, в которой вы будете искать значения в открывшемся диалоговом окне «Выбор книги», и нажмите кнопку Откройте кнопку.
Теперь все выбранные значения ищутся в указанной закрытой книге, и соответствующие значения возвращаются в указанном столбце. Смотрите скриншот:
В документах Microsoft Excel, которые состоят из большого количества полей, часто требуется найти определенные данные, наименование строки, и т.д. Очень неудобно, когда приходится просматривать огромное количество строк, чтобы найти нужное слово или выражение. Сэкономить время и нервы поможет встроенный поиск Microsoft Excel. Давайте разберемся, как он работает, и как им пользоваться.
Поисковая функция в Excel
Поисковая функция в программе Microsoft Excel предлагает возможность найти нужные текстовые или числовые значения через окно «Найти и заменить». Кроме того, в приложении имеется возможность расширенного поиска данных.
Способ 1: простой поиск
Простой поиск данных в программе Excel позволяет найти все ячейки, в которых содержится введенный в поисковое окно набор символов (буквы, цифры, слова, и т.д.) без учета регистра.
- Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.
Поиск и выдача результатов производится построчно. Сначала обрабатываются все ячейки первой строки. Если данные отвечающие условию найдены не были, программа начинает искать во второй строке, и так далее, пока не отыщет удовлетворительный результат.
Поисковые символы не обязательно должны быть самостоятельными элементами. Так, если в качестве запроса будет задано выражение «прав», то в выдаче будут представлены все ячейки, которые содержат данный последовательный набор символов даже внутри слова. Например, релевантным запросу в этом случае будет считаться слово «Направо». Если вы зададите в поисковике цифру «1», то в ответ попадут ячейки, которые содержат, например, число «516».
Для того, чтобы перейти к следующему результату, опять нажмите кнопку «Найти далее».
Способ 2: поиск по указанному интервалу ячеек
Если у вас довольно масштабная таблица, то в таком случае не всегда удобно производить поиск по всему листу, ведь в поисковой выдаче может оказаться огромное количество результатов, которые в конкретном случае не нужны. Существует способ ограничить поисковое пространство только определенным диапазоном ячеек.
-
Выделяем область ячеек, в которой хотим произвести поиск.
Способ 3: Расширенный поиск
Как уже говорилось выше, при обычном поиске в результаты выдачи попадают абсолютно все ячейки, содержащие последовательный набор поисковых символов в любом виде не зависимо от регистра.
К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.
Но, как отсечь такие, и другие заведомо неприемлемые результаты выдачи поиска? Именно для этих целей существует расширенный поиск Excel.
-
После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры».
По умолчанию, функции «Учитывать регистр» и «Ячейки целиком» отключены, но, если мы поставим галочки около соответствующих пунктов, то в таком случае, при формировании результата будет учитываться введенный регистр, и точное совпадение. Если вы введете слово с маленькой буквы, то в поисковую выдачу, ячейки содержащие написание этого слова с большой буквы, как это было бы по умолчанию, уже не попадут. Кроме того, если включена функция «Ячейки целиком», то в выдачу будут добавляться только элементы, содержащие точное наименование. Например, если вы зададите поисковый запрос «Николаев», то ячейки, содержащие текст «Николаев А. Д.», в выдачу уже добавлены не будут.
По умолчанию, поиск производится только на активном листе Excel. Но, если параметр «Искать» вы переведете в позицию «В книге», то поиск будет производиться по всем листам открытого файла.
В параметре «Просматривать» можно изменить направление поиска. По умолчанию, как уже говорилось выше, поиск ведется по порядку построчно. Переставив переключатель в позицию «По столбцам», можно задать порядок формирования результатов выдачи, начиная с первого столбца.
В графе «Область поиска» определяется, среди каких конкретно элементов производится поиск. По умолчанию, это формулы, то есть те данные, которые при клике по ячейке отображаются в строке формул. Это может быть слово, число или ссылка на ячейку. При этом, программа, выполняя поиск, видит только ссылку, а не результат. Об этом эффекте велась речь выше. Для того, чтобы производить поиск именно по результатам, по тем данным, которые отображаются в ячейке, а не в строке формул, нужно переставить переключатель из позиции «Формулы» в позицию «Значения». Кроме того, существует возможность поиска по примечаниям. В этом случае, переключатель переставляем в позицию «Примечания».
Ещё более точно поиск можно задать, нажав на кнопку «Формат».
При этом открывается окно формата ячеек. Тут можно установить формат ячеек, которые будут участвовать в поиске. Можно устанавливать ограничения по числовому формату, по выравниванию, шрифту, границе, заливке и защите, по одному из этих параметров, или комбинируя их вместе.
Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».
После этого, появляется инструмент в виде пипетки. С помощью него можно выделить ту ячейку, формат которой вы собираетесь использовать.
После того, как формат поиска настроен, жмем на кнопку «OK».
Как видим, программа Excel представляет собой довольно простой, но вместе с тем очень функциональный набор инструментов поиска. Для того, чтобы произвести простейший писк, достаточно вызвать поисковое окно, ввести в него запрос, и нажать на кнопку. Но, в то же время, существует возможность настройки индивидуального поиска с большим количеством различных параметров и дополнительных настроек.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Я использую Microsoft Office 2007. Как я могу искать в нескольких файлах Excel?
В папке, в которой хранятся файлы, нажмите Ctrl + f, чтобы использовать окно поиска в папке. Затем выполните поиск
для поиска этой строки в файлах в текущем каталоге.
Используйте IceTeaReplacer . Это бесплатная программа.
Поиск & Замените файлы .docx, .xlsx и .xls
IceTeaReplacer - простой, но мощный инструмент для поиска внутри несколько Microsoft Office Word 2007 (docx), Excel 2007 (xlsx) и Файлы Excel 2003 (xls) в каталоге (и это подкаталоги) и заменить предоставленную фразу. Поддерживает UTF-8.
ExcelPipe .
После поиска в Интернете, я обнаружил, что не существует универсального решения проблемы поиска нескольких таблиц Excel в Интернете, поэтому я решил написать свою собственную таблицу Excel, которая ищет другие таблицы Excel, которые можно найти здесь .
Обратите внимание, что он был протестирован только в Excel 2010, но он должен работать в 2007 году и более ранних версиях. Если нет, не стесняйтесь изменять код, как вы считаете нужным.
Электронная таблица использует пользовательскую форму и использует код VBA (который вы можете использовать для любых целей). Естественно, макросы и т. Д. Должны быть включены, чтобы это работало, и форма появляется при открытии электронной таблицы (вы можете получить доступ к коду, нажав Alt + F11, перейдя к пользовательской форме и дважды щелкнув кнопку «Начать поиск» в окна дизайна).
Полное описание функций и функций можно найти в файле чтения Github, но в основном это позволяет вам указать два условия текстового поиска для поиска в пределах указанного каталога (к которому вы можете перейти), он может искать рекурсивно этот каталог, и открытые электронные таблицы, защищенные паролем (если вы предоставляете пароль).
Он будет искать отдельные листы в каждой книге при поиске на уровне ячейки. В зависимости от размера рабочей книги для сканирования каждой книги может потребоваться примерно одна секунда.
Он отображает все результаты поиска в боковом окне, включая любые электронные таблицы, которые он не смог открыть. Эти результаты можно сохранить в текстовом файле для последующей справки.
Пользовательская форма должна быть относительно понятной, однако readme для нее на Github очень подробно описывает, как ее использовать. Опять же, свободный для использования, это с открытым исходным кодом.
В Excel 2003 у вас была опция дополнительного поиска в меню файла, где вы могли искать файлы. Я больше не могу найти этот вариант в 2007 году, поэтому я думаю, что он ушел.
Если у вас есть Vista или Windows 7, вы сможете использовать встроенный поиск окна. В более старых версиях вы можете установить поисковое приложение, например Поиск рабочего стола Windows , Google Desktop Search , Copernic , . они могут свободно использовать.
Читайте также: