Как сделать поиск в списке excel

Поиск значений в списке данных

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

Что необходимо сделать

Точное совпадение значений по вертикали в списке

Для этого можно использовать функцию ВLOOKUP или сочетание функций ИНДЕКС и НАЙТИПОЗ.

Примеры ВРОТ

Пример 1 функции ВПР

Пример 2 функции ВПР

Дополнительные сведения см. в этой информации.

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

Функции ИНДЕКС и ПОИСКПОЗ можно использовать вместо функции ВПР

=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение "Капуста" в массиве B2:B10))

Формула ищет в C2:C10 первое значение, соответствующее значению "Ольга" (в B7), и возвращает значение в C7 (100),которое является первым значением, которое соответствует значению "Ольга".

Подыыывка значений по вертикали в списке с помощью приблизительного совпадения

Для этого используйте функцию ВЛВП.

Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.

Пример формулы ВЛП, которая ищет приблизительное совпадение

В примере выше ВРОТ ищет имя учащегося, у которого 6 просмотров в диапазоне A2:B7. В таблице нет записи для 6 просмотров, поэтому ВРОТ ищет следующее самое высокое совпадение меньше 6 и находит значение 5, связанное с именем Виктор,и таким образом возвращает Его.

Дополнительные сведения см. в этой информации.

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

Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.

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

Пример функций OFFSET и MATCH

C1 — это левые верхние ячейки диапазона (также называемые начальной).

MATCH("Оранжевая";C2:C7;0) ищет "Оранжевые" в диапазоне C2:C7. В диапазон не следует включать запускаемую ячейку.

1 — количество столбцов справа от начальной ячейки, из которых должно быть возвращено значение. В нашем примере возвращается значение из столбца D, Sales.

Точное совпадение значений по горизонтали в списке

Для этого используйте функцию ГГПУ. См. пример ниже.

Пример формулы ГВП, которая ищет точное совпадение

Г ПРОСМОТР ищет столбец "Продажи" и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.

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

Для этого используйте функцию ГГПУ.

Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.

Пример формулы ГВП, которая ищет приблизительное совпадение

В примере выше ГЛЕБ ищет значение 11000 в строке 3 указанного диапазона. Она не находит 11000, поэтому ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.

Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.

Создание формулы подступа с помощью мастера подметок (толькоExcel 2007 )

Примечание: В Excel 2010 больше не будет надстройки #x0. Эта функция была заменена мастером функций и доступными функциями подменю и справки (справка).

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

Щелкните ячейку в диапазоне.

На вкладке Формулы в группе Решения нажмите кнопку Под поиск.

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

Загрузка надстройки "Мастер подстройок"

Нажмите кнопку Microsoft Office , выберите Параметры Excel и щелкните категорию Надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

В диалоговом окне Доступные надстройки щелкните рядом с полем Мастер подстрок инажмите кнопку ОК.

Делаем поиск в Excel

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

  1. Как работать с поиском в Excel
  2. Вариант 1: Быстрый поиск
  3. Вариант 2: поиск в указанном интервале
  4. Вариант 3: Расширенный поиск

Как работать с поиском в Excel

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

Далее рассмотрим варианты поиска данных по таблице Excel.

Вариант 1: Быстрый поиск

Это наиболее простой вариант поиска данных по всей таблице. В итоге он находит все ячейки, где содержится введенный набор символов в точном совпадении. Регистр при вводе не учитывается.

Использование инструмента происходит по данной инструкции:

  1. Откройте нужный документ в Excel. В верхнем меню переключитесь на вкладку «Главная». Вообще она открыта всегда по умолчанию.
  2. Теперь кликните по блоку «Найти и выделить». В контекстном меню выберите «Найти». Вместо описанных действий можно просто воспользоваться сочетанием клавиш Ctrl+F.

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

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

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

      1. Выделите область ячеек, внутри которых нужно провести поиск. Если вам нужно выделить несколько областей, разделенных между собой другими ячейками, то зажмите клавишу Ctrl, чтобы добавить новый блок к выделению.
      2. После выделения вызовите окошко поиска, воспользовавшись сочетанием клавиш Ctrl+F.
      3. Откроется такое же окно для поиска, как и в предыдущем варианте с той только поправкой, что оно будет искать только в выделенной области. В поле «Найти» введите то, что вам нужно отыскать.
      4. Воспользуйтесь кнопкой «Найти все» или «Найти далее». Подробно о том, чем обе кнопки отличаются друг от друга мы писали выше.

      Вариант 3: Расширенный поиск

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

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

      1. Вызовите окно обычного поиска. Это можно сделать через кнопку «Найти» или воспользовавшись сочетанием клавиш Ctrl+F.
      2. В открывшемся окне настроек воспользуйтесь кнопкой «Параметры».
      3. После этого появится целый ряд параметров, которые можно настроить под себя, например, можно сделать так, чтобы во время поиска учитывался регистр, в поиске выводились элементы, содержащие точное наименование. Также здесь можно задать, что конкретно просматривать, в какой части документа искать и даже формат, в котором должен быть результат.

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

      Ссылка на основную публикацию