Excel поиск в таблице: Поиск или замена текста и чисел на листе

Содержание

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


Excel

Формулы и функции

Для работы со ссылками



Для работы со ссылками

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

Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

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

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



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


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


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


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


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


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

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


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



Примеры ВРОТ


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



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


Что означает:


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

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

Дополнительные сведения см. в функциях ИНДЕКС иФУНКЦИЯ MATCH.


К началу страницы



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


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

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

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

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


К началу страницы


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


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

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


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


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


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


К началу страницы


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


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

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

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


К началу страницы


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


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

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

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

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


К началу страницы


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


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

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

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

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

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

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

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

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

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

  7. Следуйте инструкциям мастера.


К началу страницы


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

Основное назначение офисной программы 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

спросил

Изменено
13 дней назад

Просмотрено
201 тысяч раз

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

  • microsoft-excel

Вы не указали / не отметили, какую версию Excel вы используете, но поскольку вы называете их таблицами, а не списками, я предполагаю, что это 2007 или 2010 год.
Если вы перейдете на вкладку «Формулы» на ленте > «Диспетчер имен», вы увидите имена таблиц, перечисленные среди других определенных имен.
Рядом с ними отображается другой значок, но, чтобы сделать ситуацию еще понятнее, вы можете использовать кнопку «Фильтр» в правом верхнем углу, чтобы отображать только таблицы.

По мере того, как вы выясняете, что они делают, это переименовывает таблицы в разумные имена (из ленты «Инструменты для таблиц»> «Дизайн» или «Диспетчер имен») и, возможно, добавляет комментарии об их назначении, чтобы напомнить вам позже (сделайте это в «Диспетчере имен»> «Правка»)

В более поздних версиях Excel вы можете:

 Главная страница -> Найти и выбрать -> Перейти

Появится список именованных элементов, включая таблицы, к которым можно перейти непосредственно к

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

Это было источником моей проблемы.

1

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания, политикой конфиденциальности и политикой использования файлов cookie

.

Окно поиска с динамическим фильтром Excel (извлечение данных по мере ввода)

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

Примерно так, как показано ниже:

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

Загрузите пример файла и следуйте ему

Посмотрите видео. Создание окна поиска с динамическим фильтром Excel уникальный список элементов (стран в данном случае). Это будет использоваться при создании раскрывающегося списка.

  • Создание окна поиска. Здесь я использовал поле со списком (управление ActiveX).
  • Установка данных. Здесь я бы использовал три вспомогательных столбца с формулами для извлечения соответствующих данных.

    • Вот как выглядят необработанные данные:

    ПОЛЕЗНЫЙ СОВЕТ. Почти всегда полезно преобразовать данные в таблицу Excel. Вы можете сделать это, выбрав любую ячейку в наборе данных и нажав сочетание клавиш Control + T.

    Шаг 1. Получение уникального списка элементов

    1. Выберите все страны и вставьте его в новый рабочий лист.
    2. Выберите список стран –> Перейти к данным –> Удалить дубликаты.
    3. В диалоговом окне «Удалить дубликаты» выберите столбец, в котором у вас есть список, и нажмите «ОК». Это удалит дубликаты и даст вам уникальный список, как показано ниже:

      4. .

    4. Еще один шаг — создать именованный диапазон для этого уникального списка. Сделать это:
      • Перейти на вкладку «Формула» -> «Определить имя»
      • В диалоговом окне «Определить имя»:
        • Имя: CountryList
        • Область применения: Рабочая тетрадь
        • Относится к: =UniqueList!$A$2:$A$9 (у меня есть список на отдельной вкладке с именем UniqueList в A2:A9. Вы можете обратиться туда, где находится ваш уникальный список)

    ПРИМЕЧАНИЕ. Если вы используете метод «Удалить дубликаты» и расширяете свои данные, чтобы добавить больше записей и новых стран, вам придется повторить этот шаг еще раз. В качестве альтернативы вы также можете использовать формулу, чтобы сделать этот процесс динамичным.

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

    Шаг 2 – Создание окна поиска с динамическим фильтром Excel

    Чтобы этот метод работал, нам нужно создать «окно поиска» и связать его с ячейкой.

    Мы можем использовать поле со списком в Excel, чтобы создать этот фильтр окна поиска. Таким образом, всякий раз, когда вы вводите что-либо в поле со списком, это также будет отражаться в ячейке в режиме реального времени (как показано ниже).

    Вот шаги для этого:

    1. Перейдите на вкладку «Разработчик» —> «Элементы управления» —> «Вставить» —> «Элементы управления ActiveX» —> «Поле со списком» (элементы управления ActiveX).
      • Если у вас не отображается вкладка «Разработчик», выполните следующие действия, чтобы включить ее.
    2. Щелкните в любом месте рабочего листа. Он вставит поле со списком.
    3. Щелкните правой кнопкой мыши поле со списком и выберите «Свойства».
    4. В окне свойств внесите следующие изменения:
      • Связанная ячейка: K2 (вы можете выбрать любую ячейку, где вы хотите, чтобы она отображала входные значения. Мы будем использовать эту ячейку при настройке данных).
      • ListFillRange: CountryList (это именованный диапазон, который мы создали на шаге 1. В раскрывающемся списке будут показаны все страны).
      • MatchEntry: 2-fmMatchEntryNone (это гарантирует, что слово не заполняется автоматически при вводе)
    5. Выбрав поле со списком, перейдите на вкладку «Разработчик» -> «Управление» -> нажмите «Режим дизайна» (это выводит вас из режима дизайна, и теперь вы можете вводить что угодно в поле со списком. Теперь все, что вы вводите, будет отражаться в ячейке). К2 в реальном времени)

    Шаг 3. Установка данных

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

    Вспомогательный столбец 1: Введите порядковый номер для всех записей (в данном случае 20). Для этого можно использовать формулу ROWS().

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

    Это можно сделать с помощью комбинации функций ЕСЛИ, IЧИСЛО и ПОИСК.

    Вот формула:

     =ЕСЛИ(ЧИСЛО(ПОИСК($K$2,D4)),E4,"")

    Эта формула будет искать содержимое в поле поиска (которое связано с ячейкой K2) в ячейке с названием страны.

    Если есть совпадение, эта формула возвращает номер строки, в противном случае возвращается пробел. Например, если поле со списком имеет значение «США», все записи со страной «США» будут иметь номер строки, а все остальные будут пустыми («»)

    Вспомогательный столбец 3: В вспомогательного столбца 3, нам нужно собрать все номера строк из вспомогательного столбца 2 вместе. Для этого мы можем использовать комбинацию формул ЕСЛИОШИБКА и МАЛЕНЬКИЙ. Вот формула:

     =ЕСЛИОШИБКА(МАЛЕНЬКИЙ($F$4:$F$23,E4),"")

    Эта формула складывает вместе все совпадающие номера строк. Например, если в поле со списком указано значение США, все номера строк со словом «США» складываются вместе.

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

     =ЕСЛИОШИБКА(ИНДЕКС($B$4:$D$23,$G4,СТОЛБЦ($I$3:I3)),"")

    Эта формула состоит из 2 частей:
    ИНДЕКС  – извлекает данные. исходя из номера строки.
    ЕСЛИОШИБКА  – возвращает пустое значение при отсутствии данных.

    Вот снимок того, что вы в итоге получили:

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

    Загрузите пример файла динамического фильтра Excel

    Проявите творческий подход! Попробуйте некоторые варианты

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

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

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