Поиск в списке в excel

Поиск в списке в excel

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

Выпадающий список с контекстным поиском

Итак, речь пойдёт о выпадающем списке (так называемый combo box), в который встроена возможность динамического поиска по подстроке, которую пользователь вводит с клавиатуры. Посмотрите пример, в котором мы имеем топ 300 крупнейших городов России. На анимированной иллюстрации видно, как мы динамически сужаем список выбора, вводя подстроку “кр” или “ниж”, экономя огромное количество времени. Более того, список меняется после ввода каждого нового символа! Выглядит чрезвычайно привлекательно и профессионально, не так ли? Давайте разбираться, как это устроено.

Файл примера

Скачать

Пошаговая инструкция

Предварительные замечания

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

Шаг 1. Готовим таблицу для списка

Подготовьте таблицу с четырьмя колонками: Город (или то, что вам нужно), Статус , Индекс , Фильтр . Заполните столбец Город значениями. В остальных трёх колонках будут формулы, которые мы обсудим ниже. Я всем рекомендую использовать умную таблицу, так как это значительно проще.

Шаг 2. Формулы для столбца Статус

На примере ячейки F2 рассмотрим формулу, аналогичную для всего столбца Статус (столбец F ). Из F2 формулу можно протягивать вниз до конца, а в случае умной таблицы Excel это сделает за вас. Это также относится ко всем формулам, которые мы будем обсуждать в этой статье.

$B$2 – ячейка, с которой будет связан выпадающий список (добавляется на шаге 6). Что значит связано? Всё, что вы введёте в выпадающий список, тут же отразится в ячейке B2 .

Формула ПОИСК вернёт ошибку, если содержимое B2 не найдено в $E2 . ЕОШИБКА перехватит ошибку и вернёт ИСТИНА, если действительно была ошибка, и – ЛОЖЬ, если строка таки была найдена. Функция НЕ делает из истины ложь и наоборот (инверсирует результат). Таким образом, мы получим в этом столбце ИСТИНА, если подстрока найдена в текущем городе, и наоборот. Обратите внимание, что пустая подстрока содержится в любой строке, поэтому все ячейки столбца Статус имеют значения ИСТИНА, когда мы не ввели ещё ничего в B2 .

= НЕ( ЕОШИБКА ( ПОИСК ( $B$2 ; $E2 ) ) )

=NOT( ISERROR( SEARCH( $B$2; $E2) ) )

= НЕ ( ЕОШИБКА ( ПОИСК ( $B$2 ; [@ Город ]) ) )

=NOT( ISERROR( SEARCH( $B$2; [@Город]) ) )
[@ Город ] – на языке структурных формул умных таблиц это ссылка на ячейку столбца Город в той же строке, в которой находится сама формула. Поскольку ссылка идёт внутри таблицы, то имя самой таблицы в формуле можно не использовать. В остальном всё – тоже самое.

Шаг 3. Формула для столбца Индекс

Если B2 содержит подстроку поиска, то в столбце Статус не все ячейки примут значение ИСТИНА. Статус ИСТИНА будет только там, в чьи названия городов входит соответствующая подстрока. А в столбце Индекс мы рассчитываем номер по порядку для всех строк, которые содержат искомую подстроку. Например, на рисунке ниже B2 содержит “ни”, что заставляет столбец Статус быть истинным у строк с городами Нижний Новгород , Калининград , Магнитогорск и т.д., а в столбце Индекс мы начинаем считать факты срабатываний в F : Нижний Новгород – первое срабатывание, Калининград – второе и так далее.

Функция ЕСЛИ отсекает все значения в F , которые не равны ИСТИНА. Функция СЧЁТЕСЛИ подсчитывает количество значений ИСТИНА в F .

Обычный диапазон Умная таблица

= ЕСЛИ( $F2 ; СЧЁТЕСЛИ ( $F$2:$F2 ; ИСТИНА ); “”)

=IF( $F2; COUNTIF( $F$2:$F2; TRUE ); “”)

= ЕСЛИ ( [@ Статус ]; СЧЁТЕСЛИ ( $F$2 :[@ Статус ]; ИСТИНА ); “”)

=IF( [@Статус]; COUNTIF( $F$2:[@Статус]; TRUE ); “”)

Обратите внимание, что диапазон условия в СЧЁТЕСЛИ введен скользящий – вторая координата не закреплена – и во время протягивания она растёт пропорционально таблице. За счёт этого трюка мы получаем механизм подсчёта значения ИСТИНА. Например, 6-я строка будет подсчитывать ИСТИНУ по диапазону $F$2:$F6 (там одно значение – от Нижнего Новгорода ), а 41-я строка будет подсчитывать ИСТИНУ уже по диапазону $F$2:$F41 (а там уже 2 значения – от Нижнего Новгорода и от Калининграда ). Вот суть механизма. Это полезный приём, который стоит запомнить.

Шаг 4. Формула для столбца Фильтр

Теперь наша задача, опираясь на столбец Индекс , сформировать в столбце H отфильтрованный список городов, который необходимо показывать в выпадающем списке.

Обычный диапазон Умная таблица

= ЕСЛИОШИБКА( ИНДЕКС ( стлГород ; ПОИСКПОЗ ( ЧСТРОК ( $G$2:$G2 ); стлИндекс ; 0) ); “”)

=IFERROR( INDEX( стлГород; MATCH( ROWS($G$2:$G2); стлИндекс; 0) ); “” )

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

= ЕСЛИОШИБКА ( ИНДЕКС ( [ Город ]; ПОИСКПОЗ ( ЧСТРОК ( $G$2 :[@ Индекс ]); [ Индекс ]; 0) ); “”)

=IFERROR( INDEX( [Город]; MATCH( ROWS($G$2:[@Индекс]); [Индекс]; 0) ); “” )

Не путайте: [ Индекс ] – ссылка на весь столбец, а [@ Индекс ] – ссылка на ячейку из этого столбца в текущей строке. Никакие дополнительные именованные диапазоны нам создавать нет никакой необходимости, так как мы пользуемся встроенным в умные таблицы сервисом при ссылке на столбцы.

Формула ЧСТРОК ( $G$2:$G2 ) используется для генерации последовательных номеров от 1 (для второй строки) до N (в строке N+1), равному количеству найденных подстрок. Просто генерируется диапазон соответствующего размера, а формула ЧСТРОК возвращает его высоту в строках.

Формула ПОИСКПОЗ ищет номер реальной строки, содержащий соответствующий индекс. Например, в столбце Фильтр мы видим Магнитогорск на третьей позиции, но в реальности он взят из E45 , так как в G45 стоит цифра 3, которую мы и нашли через ПОИСКПОЗ . То есть ПОИСКПОЗ сказал нам, что Магнитогорск находится в 45-й строке, а извлекли мы его оттуда уже при помощи формулы ИНДЕКС .

Если же при извлечении возникает ошибка (текущая строка находится ниже строки N+1), то формула возвращает пустую строку. За это отвечает ЕСЛИОШИБКА .

Источник: perfect-excel.ru

Поиск слов в Excel

В Microsoft Excel часто работают с большими объёмами информации. В нём создаются огромные таблицы на тысячи строк, столбцов и позиций. Отыскать какие-то конкретные данные в таком массиве бывает трудно. А иногда — вообще невозможно. Эту задачу можно упростить. Разберитесь, как в Экселе найти нужное слово. Так вам легче будет ориентироваться в документе. И вы сможете быстро перейти к искомой информации.

В редакторе таблиц существует несколько способов поиска

Поиск ячеек

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

  1. Если у вас Office 2010, перейдите в Меню — Правка — Найти.
  2. Откроется окно с полем для ввода. Напишите в нём фразу для поиска.
  3. В Excel 2007 эта кнопка есть в меню «Главная» на панели «Редактирование». Она находится справа.
  4. Аналогичного результата во всех версиях можно добиться, если нажать клавиши Ctrl+F.
  5. В поле напечатайте слово, фразу или цифры, которые надо отыскать.
  6. Нажмите «Найти всё», чтобы запустить поиск по целому документу. Если кликните «Далее», программа будет по одной выделять клетки, которые находятся ниже курсора-ячейки Excel.
  7. Подождите, пока процесс закончится. Чем больше документ, тем дольше система будет искать.
  8. Появится список с результатами: именами и адресами клеток, в которых есть совпадения с заданной фразой, и текстом, который в них написан.
  9. При клике на каждую строчку будет выделяться соответствующая ячейка.
  10. Для удобства можете «растянуть» окно. Так в нём будет видно больше строчек.
  11. Чтобы отсортировать данные, кликайте на названия столбцов над результатами поиска. Если нажмёте на «Лист», строчки выстроятся по алфавиту в зависимости от названия листа, если выберите «Значения» — встанут по значению.
  12. Эти столбцы также «растягиваются».

Параметры поиска

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

  1. Введите только часть надписи. Можно хоть одну букву — будут выделены все места, в которых она есть.
  2. Используйте символы * (звёздочка) и ? (вопросительный знак). Они замещают пропущенные знаки.
  3. Вопрос обозначает одну отсутствующую позицию. Если вы напишите, к примеру, «П. », отобразятся ячейки, в которых есть слово из четырёх символов, начинающееся на «П»: «Плуг», «Поле», «Пара» и так далее.
  4. Звезда (*) замещает любое количество знаков. Чтобы отыскать все значения, в которых содержится корень «раст», начните поиск по ключу «*раст*».

Также вы можете зайти в настройки:

  1. В окне «Найти» нажмите «Параметры».
  2. В разделах «Просматривать» и «Область поиска», укажите, где и по каким критериям надо искать совпадения. Можно выбрать формулы, примечания или значения.
  3. Чтобы система различала строчные и прописные буквы, поставьте галочку в «Учитывать регистр».
  4. Если вы отметите пункт «Ячейка целиком», в результатах появятся клетки, в которых есть только заданная поисковая фраза и ничего больше.

Параметры формата ячеек

Чтобы отыскать значения с определённой заливкой или начертанием, используйте настройки. Вот как найти в Excel слово, если оно имеет отличный от остального текста вид:

  1. В окне поиска нажмите «Параметры» и кликните на кнопку «Формат». Откроется меню с несколькими вкладками.
  2. Можете указать определённый шрифт, вид рамки, цвет фона, формат данных. Система будет просматривать места, которые подходят к заданным критериям.
  3. Чтобы взять информацию из текущей клетки (выделенной в этот момент), нажмите «Использовать формат этой ячейки». Тогда программа отыщет все значения, у которых тот же размер и вид символов, тот же цвет, те же границы и тому подобное.

Поиск нескольких слов

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

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

  1. Напишите их в строке поиска.
  2. Поставьте между ними звёздочки. Получится «*Текст* *Текст2* *Текст3*». Так отыщутся все значения, содержащие указанные надписи. Вне зависимости от того, есть ли между ними какие-то символы или нет.
  3. Этим способом можно задать ключ даже с отдельными буквами.

Фильтр

Вот как искать в Экселе, используя фильтр:

  1. Выделите какую-нибудь заполненную ячейку.
  2. Нажмите Главная — Сортировка — Фильтр.
  3. В верхней строке у клеток появятся стрелочки. Это выпадающее меню. Откройте его.
  4. В текстовом поле введите запрос и нажмите «OK».
  5. В столбце будут отображаться только ячейки, содержащие искомую фразу.
  6. Чтобы сбросить результаты, в выпадающем списке отметьте «Выделить всё».
  7. Чтобы отключить фильтр, повторно нажмите на него в сортировке.

Этот способ не подойдёт, если вы не знаете, в каком ряду нужное вам значение.

Чтобы найти в Excel какую-то фразу или число используйте встроенные возможности интерфейса. Можно выбрать дополнительные параметры поиска и включить фильтр.

Источник: nastroyvse.ru

Выпадающий список с быстрым поиском

Классический выпадающий список в ячейке листа Excel, сделанный через Данные – Проверка (Data – Validation) – простая и удобная штука, которую ежедневно применяют очень многие пользователи. Однако, у этого списка есть один весьма серьезный недостаток – в нём нет быстрого поиска по первым символам, т.е. фильтрации (отбора) только тех значений, куда введённый фрагмент входит как подстрока. Это серьезно ухудшает удобство пользования даже если в списке всего пара-тройка десятков позиций, а при нескольких сотнях убивает юзабилити напрочь.

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

Конечная цель – создать выпадающий список (ячейка G3), в котором можно будет быстро находить нужные фильмы, введя только жанр, год или фрагмент названия, например “гамп”.

Шаг 1. Определяем, кто нам нужен

Сначала нам нужно понять, какие из исходных ячеек нужно показывать в списке, т.е. определить содержится ли введённый в выпадающем списке текст (например, жанр “детектив”) в названии фильма. Для этого добавим слева от исходных данных еще один столбец с функцией ПОИСК (SEARCH ) , которая ищет заданную подстроку в тексте и выдает либо порядковый номер символа, где он был обнаружен, либо ошибку, если его там нет:

Теперь завернем нашу формулу в функцию проверки ЕЧИСЛО (ISNUMBER) , которая превратит числа в логическую ИСТИНУ (TRUE) , а ошибки – в ЛОЖЬ (FALSE) :

Теперь сделаем так, чтобы ЛОЖЬ превратилась в 0, а вместо ИСТИНА в столбце появились последовательно возрастающие индексы-числа 1,2,3. и т.д. Это можно сделать с помощью добавления к нашей же формуле ещё парочки функций:

Здесь функция ЕСЛИ (IF) проверяет что мы имеем (ИСТИНУ или ЛОЖЬ), и

  • если была ИСТИНА, то выводит максимальное значение из всех вышестоящих чисел + 1
  • если была ЛОЖЬ, то выводит 0

Шаг 2. Отбираем в отдельный список

Дальше – проще. Теперь банальной функцией ВПР (VLOOKUP) просто выведём все найденные названия (я добавил столбец с порядковыми номерами для удобства):

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

Шаг 3. Создаем именованный диапазон

Теперь создадим именованный диапазон, который будет ссылаться на отобранные фильмы. Для этого выбрем на вкладке Формулы команды Диспетчер имен – Создать (Formulas – Name Manager – Create) :

Имя диапазона может быть любым (например, Фильмы), а самое главное – это функция СМЕЩ (OFFSET) , которая и делает всю работу. Напомню её синтаксис, если вы подзабыли:

=СМЕЩ( начальная_ячейка ; сдвиг_вниз ; сдвиг_вправо ; высота ; ширина )

  • В качестве начальной ячейки задаём первую ячейку списка отобранных элементов (E2).
  • Сдвиги вниз и вправо у нас отсутствуют, т.е. равны нулю.
  • Высота диапазона у нас соответствует максимальному значению индекса из столбца А.
  • Ширина диапазона – 1 столбец.

Осталось сделать выпадающий список.

Шаг 4. Создаем выпадающий список

Выделим жёлтую ячейку (G2) и выберем на вкладке Данные команду Проверка данных (Data – Validation) . В открывшемся окне выбрем Список (List) в поле Тип данных (Allow) , а в качестве источника введем имя нашего созданного диапазона со знаком равно перед ним:

Чтобы Excel не ругался при вводе на неточное совпадение наших фраз с исходным списком, на вкладке Сообщение об ошибке (Error Alert) в этом окне нужно выключить флажок Выводить сообщение об ошибке (Show error alert) :

Вот и всё. Можно жать на ОК и наслаждаться результатом:

Для пущего удобства при вводе с клавиатуры можно использовать Ctrl + Enter вместо Enter после ввода текста (так активная ячейка не уходит вниз) и сочетание клавиш Alt + стрелка вниз , чтобы развернуть выпадающий список без мыши.

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

Всё, что мы делали на Шагах 1-3 заменяется одной(!) формулой, где новая функция ФИЛЬТР (FILTER) отбирает из исходного диапазона A2:A251 только те фильмы, которые содержат заданную подстроку.

А дальше останется при создании выпадающего списка указать в качестве источника первую ячейку диапазона отобранных фильмов (C2) и добавить к ней знак #, чтобы получить ссылку на весь динамический массив:

И всё. Никаких именованных диапазонов и медленных СМЕЩ, никаких танцев с дополнительными столбцами и формулами. Песня!

Источник: www.planetaexcel.ru

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

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

В этой статье

Поиск значений в списке по вертикали по точному совпадению

Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций индекс и ПОИСКПОЗ.

Примеры использования функции ВПР

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

Примеры ИНДЕКСов и СОВПАДЕНИй

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

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

Дополнительные сведения можно найти в разделе Функция индекс и функция ПОИСКПОЗ.

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

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

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

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

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

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

Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

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

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

Match (“апельсины”; C2: C7; 0) ищет оранжевый цвет в диапазоне C2: C7. Не следует включать начальную ячейку в диапазон.

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

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

Для выполнения этой задачи используется функция ГПР. Ниже приведен пример.

Функция ГПР выполняет поиск по столбцу Sales и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения можно найти в разделе функции ГПР.

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

Для выполнения этой задачи используется функция ГПР.

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

В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение, которое меньше 1100 и возвращает число 10543.

Дополнительные сведения можно найти в разделе функции ГПР.

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

Примечание: Надстройка “Мастер подстановок” прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

В Excel 2007 мастер подстановок создает формулу подстановки на основе данных листа, имеющих заголовки строк и столбцов. Мастер подстановок помогает находить другие значения в строке, когда вы знаете значение в одном столбце, и наоборот. Мастер подстановок использует индекс и СОВПАДЕНИе в создаваемых формулах.

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

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

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

Загрузка программы-надстройки “Мастер подстановок”

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

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

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

Источник: support.office.com

Примеры формул ИНДЕКС и ПОИСКПОЗ для выборки из списка в Excel

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

Как применить формулу ИНДЕКС и ПОИСКПОЗ в Excel

Ниже на рисунке представлены города и области, в которых находятся магазины сети. Допустим, что после выбора пользователем названия области с выпадающего списка в ячейке G1 должны автоматически заполнится поля «Город» G2 и «№ магазина» G3:

Функция ИНДЕКС возвращает значение ячейки, находящееся в определенной строке и столбце указанного диапазона ячеек. В данном случае в аргументе функции как диапазон ячеек указана исходная таблица магазинов. Во втором аргументе указывается сначала номер строки, а в третьем – номер столбца. В формуле для поиска города значения считываются с первого столбца, поэтому последний аргумент содержит число 1.

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

Поэтому последний аргумент в функции ВПР равен числу 4.

Если диапазон ячеек не начинается с ячейки A1 аргументы, определяющие строку и столбец, не соответствуют номерам строк и столбцов рабочего листа Excel. Отсчет номеров производится, начиная от ячейки левого верхнего угла указанного диапазона (в данном примере это A2) в первом аргументе функции ИНДЕКС. Например, формула =ИНДЕКС(G2:P10;2;2) возвращает значение ячейки H3, которая находится во второй строке второго столбца диапазона G2:P10.

Во втором аргументе функции ИНДЕКС содержится функция ПОИСКПОЗ. В ее аргументах всегда указывается диапазон ячеек, которые содержат только одну строку или один столбец. Если указать диапазон из более чем одной строки или столбца, тогда функция ПОИСКПОЗ возвращает ошибку #Н/Д!

Чтобы получить номер соответствующей строки для функции ИНДЕКС необходимо использовать функцию ПОИСКПОЗ, которая возвращает позицию найденного значения в таблице. Функция ПОИСКПОЗ использует 3 аргумента в своем синтаксисе:

  1. Искомое значение – то что следует найти.
  2. Просматриваемый массив – одномерный массив или диапазон ячеек из одной строки или из одного столбца где должно быть найдено искомое значение.
  3. Тип сопоставления – точность соответствия (необязательный аргумент для заполнения), при точном соответствии указывается значение ЛОЖЬ или 0. Если требуется неточное совпадение значений, тогда ИСТИНА или 1.

В данном примере искомым значением выступает название области указана в ячейке G1. Данное значение формула ищет в списке областей диапазона C2:C11. Функция ПОИСКПОЗ поочередно проверяет все ячейки, пока не найдет строку «Свердловская», находящееся на 5-ой позиции. После чего данная функция возвращает значение 5, которое будет использовано как второй аргумент с номером строки для функции ИНДЕКС.

После возвращения результата через функцию ПОИСКПОЗ функция ИНДЕКС будет иметь все необходимые данные для отображения соответственного значения. Перейдет на 5-тую строку диапазона ячеек и выберет значение из первого столбца «Город» или из четвертого «№ магазина».

Внимание! Если в аргументах функции ИНДЕКС указать номер строки больше чем количество строк в диапазоне ячеек или номер столбца больше чем количество столбцов, тогда функция возвращает ошибку #ССЫЛКА!

Как сделать выборку из списка формулой ПРОСМОТР в Excel

Формула из комбинации функций ИНДЕКС и ПОИСКПОЗ – это самая популярный тип формул для поиска значений в таблице Excel. Значительно реже используется функция ПРОСМОТР. В ее синтаксисе используется 3 аргумента:

  1. Искомое значение – значение позицию которого необходимо найти.
  2. Просматриваемый вектор – одномерный массив или диапазон ячеек из одной строки или из одного столбца где будет вестись поиск искомого значения.
  3. Вектор результатов – одномерный массив или диапазон ячеек из одной строки или из одного столбца из которого следует вернуть результат (необязательный для заполнения).

Внимание! Сразу же отметим недостатки функции ПРОСМОТР, а они весьма существенные:

  1. Нет возможности установить точное совпадение как в функциях ВПР, ГПР и ПОИСКПОЗ.
  2. Просматриваемый одномерный массив вектора должен быть отсортирован по возрастанию иначе функция будет возвращать ошибочные результаты как оказано на рисунке:

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

И соответственного номера магазина:

Таблица пересортирована по диапазону C2:C11 по возрастанию и теперь все работает как надо.

Первые два аргумента функции ПРОСМОТР такие же, как и у функции ПОИСКПОЗ. Эти две функции работают по одному и тому же принципу. То есть ищет заданное значение в одном направлении вектора. Отличие в том, что она возвращает не позицию где находится найденная ячейка в диапазоне с нужным значением, а содержимое соответственной ячейки находящиеся на позиции (указанной в векторе результатов) от нее. Ну и не стоит забывать, что для функции ПРОСМОТР нужно сортировать таблицу по возрастанию просматриваемого диапазона и у нее нет аргумента точности совпадения при поиске значений.

Чтобы найти необходимый город, функция ПРОСМОТР сначала определяет, что строка «Свердловская» находится на 10-ой позиции (после сортировки таблицы) просматриваемого вектора по диапазону C2:C11. После формула чего возвращает содержимое 10-ой ячейки, но уже по вектору в диапазоне A2:A11. Аналогичным принципом функция ищет номер магазина, но там результирующий вектор – это уже диапазон D2:D11.

Источник: exceltable.com

Поиск ТЕКСТовых значений в EXCEL с выводом их в отдельный список. Часть1. Обычный поиск

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

Пусть Исходный список значений (например, перечень инструментов) находится в диапазоне A10:A19 (см. Файл примера ).

Задача

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

А. Найти значения, которые содержат критерий

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

Выведем в отдельный диапазон все значения Исходного списка, в которых содержится текст-критерий (например, слово дрель ). Критерий вводится в ячейку С6 .

Для создания списка, содержащего найденные значения, воспользуемся формулой массива :

=ИНДЕКС(Список; НАИМЕНЬШИЙ( ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($C$6;Список);0)>0;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(“A1:A”&ЧСТРОК(Список)))))

Алгоритм работы формулы следующий (для просмотра промежуточных шагов работы формулы воспользуйтесь клавишей F9 ):

  • Функция ПОИСК() , перебирая элементы исходного списка, определяет, содержится ли в нем значение-критерий. Если значение не содержится, то возвращается ошибка #ЗНАЧ! В противном случае возвращается числовое значение, соответствующее номеру начальной позиции вхождения критерия в значение из списка (здесь нам не важен номер позиции, важно, что это число);
  • Функция ЕСЛИОШИБКА() используется для подавления ошибки #ЗНАЧ! заменяя ее на число 0;
  • Функция ЕСЛИ() заменяет числовые значения, возвращенные функцией ПОИСК() , на номер позиции значения в списке. Если значение =0 (соответствует ошибке #ЗНАЧ!), то возвращается число 30. В принципе, вместо 30 можно указать любое число, которое больше номера последней заполненной позиции Исходного списка (это нужно для правильной сортировки функцией НАИМЕНЬШИЙ() );
  • Функция НАИМЕНЬШИЙ() сортирует массив номеров строк по возрастанию;
  • Функция ДВССЫЛ() возвращает массив последовательных чисел ;
  • Функция ИНДЕКС() возвращает текстовые значения из Исходного списка, из строк, номера которых были получены на предыдущем шаге.

В предельном случае м.б. найдено столько же значений, сколько содержится в исходном списке (когда все значения удовлетворяют критерию). Поэтому формулу массива нужно распространять на диапазон той же размерности, что и исходный список. Вышеуказанная формула массива будет возвращать несколько значений , поэтому перед вводом формулы нужно выделить сразу весь диапазон, т.е. ячейки С10:С19 , ввести формулу в Строке формул и нажать CRTL+SHIFT+ENTER .

Для скрытия ошибок #ССЫЛКА!, возвращаемой формулой массива, к диапазону С10:С19 применено правило Условного форматирования .

Б. Найти значения, которые совпадают с критерием (точное совпадение)

В этом случае будут выведены все значения, которые совпадают с критерием (без учета РЕгиСТра ). Критерий вводится в ячейку E 6 .

Для создания списка, содержащего найденные значения, воспользуемся формулой массива : =ИНДЕКС(Список; НАИМЕНЬШИЙ( ЕСЛИ($E$6=Список;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(“A1:A”&ЧСТРОК(Список)))))

B. Найти значения, которые начинаются с критерия

В этом случае будут выведены все значения, которые начинаются или совпадают с критерием. Критерий вводится в ячейку G 6 .

Для создания списка, содержащего найденные значения, воспользуемся формулой массива : = ИНДЕКС(Список;НАИМЕНЬШИЙ( ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($G$6;Список);0)=1;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(“A1:A”&ЧСТРОК(Список)))))

Г. Найти значения, которые заканчиваются на критерий

В этом случае будут выведены все значения, которые заканчиваются или совпадают с критерием. Критерий вводится в ячейку I 6 .

Для создания списка, содержащего найденные значения, воспользуемся формулой массива : =ИНДЕКС(Список;НАИМЕНЬШИЙ( ЕСЛИ($I$6=ПРАВСИМВ(Список;ДЛСТР($I$6));СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(“A1:A”&ЧСТРОК(Список)))))

СОВЕТ: О поиске текстовых значений с использованием подстановочных знаков читайте в статье Поиск текстовых значений в списках. Часть2. Подстановочные знаки . В статье Выделение ячеек c ТЕКСТом с применением Условного форматирования приведено решение аналогичной задачи с использованием Условного форматирования .

Источник: excel2.ru

Добавить комментарий

Adblock
detector
Обычный диапазон Умная таблица