Как сделать ячейку поиска в excel?

Программа Excel ориентирована на ускоренные расчеты. Зачастую документы здесь состоят из большого ко…

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

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

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

Поиск слов

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

• запустить программу Excel;
•  проверить активность таблицы, щелкнув по любой из ячеек;
•  нажать комбинацию клавиш «Ctrl + F»;
•  в строке «Найти» появившегося окна ввести искомое слово;
•  нажать «Найти».

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

Существует также способ нестрогого поиска, который подходит для ситуаций, когда искомое слово помнится частично. Он предусматривает использование символов-заменителей (джокерные символы). В Excel их всего два:

•  «?» – подразумевает любой отдельно взятый символ;
•  «*» – обозначает любое количество символов.

 Примечательно, при поиске вопросительного знака или знака умножения дополнительно впереди ставится тильда («~»). При поиске тильды, соответственно – две тильды.

 Алгоритм неточного поиска слова:

•  запустить программу;
•  активировать страницу щелчком мыши;
•  зажать комбинацию клавиш «Ctrl + F»;
•  в строке «Найти» появившегося окна ввести искомое слово, используя вместо букв, вызывающих сомнения, джокерные символы;
•  проверить параметр «Ячейка целиком» (он не должен быть отмеченным);
•  нажать «Найти все».

 Все слова, подходящие под параметры поиска, подсветятся, поэтому их легко будет увидеть и проанализировать.

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

Не зная, как найти слово в таблице в Еxcel, следует также воспользоваться функцией раздела «Редактирование» – «Найти и выделить». Далее нужно отталкиваться от искомой фразы:

•  если фраза точная, введите ее и нажмите клавишу «Найти все»;
•  если фраза разбита другими ключами, нужно при написании ее в строке поиска дополнительно проставить между всеми словами «*».

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

Поиск ячеек

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

Для поиска ячеек с формулами выполняются следующие действия.

1. В открытом документе выделить ячейку или диапазон ячеек (в первом случае поиск идет по всему листу, во втором – в выделенных ячейках).
2. Во вкладке «Главная» выбрать функцию «Найти и выделить».
3. Обозначить команду «Перейти».
4. Выделить клавишу «Выделить».
5. Выбрать «Формулы».
6. Обратить внимание на список пунктов под «Формулами» (возможно, понадобится снятие флажков с некоторых параметров).
7. Нажать клавишу «Ок».

 Для поиска объединенных ячеек потребуется выполнение следующих манипуляций.

1. Перейти во вкладку «Главная».
2. Выбрать функцию «Найти и выделить».
3. Нажать на команду «Найти».
4. Перейти в «Параметры» и выбрать «Формат».
5. Здесь выделить функцию «Выравнивание», поставить отметку «Объединить ячейки».
6. Нажать на «Ок».
7. Нажать на кнопку «Найти все» и проанализировать список ячеек, которые объединены на соответствующем листе.

При нажимании кнопкой мыши на элемент в списке происходит выделение объединенной ячейки на листе. Дополнительно доступна функция «Отменить объединение ячеек».

 Выполнение представленных выше действий приводит к нахождению всех объединенных ячеек на листе и при необходимости отмене данного свойства. Для поиска скрытых ячеек проводятся следующие действия.

1. Выбрать лист, требующий анализа на присутствие скрытых ячеек и их нахождения.
2. Нажать клавиши «F5_гт_ Special».
3. Нажать сочетание клавиш «CTRL + G_гт_ Special».

Можно воспользоваться еще одним способом для поиска скрытых ячеек:

1. Открыть функцию «Редактирование» во вкладке «Главная».
2. Нажать на «Найти».
3. Выбрать команду «Перейти к разделу». Выделить «Специальные».
4. Попав в группу «Выбор», поставить галочку на «Только видимые ячейки».
5. Нажать кнопку «Ок».

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

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

Чтобы найти ячейки, для которых применено условное форматирование:

• нажать на ячейку, не предусматривающую условное форматирование;
• выбрать функцию «Редактирование» во вкладке «Главная»;
• нажать на кнопку «Найти и выделить»;
• выделить категорию «Условное форматирование».

Чтобы найти ячейки, для которых применено одинаковое условное форматирование:

• выбрать ячейку, предусматривающую условное форматирование, требующую поиска;
• выбрать группу «Редактирование» во вкладке «Главная»;
• нажать на кнопку «Найти и выделить»;
• выбрать категорию «Выделить группу ячеек»;
• установить свойство «Условные форматы»;
• напоследок нужно зайти в группу «Проверка данных» и установить аналогичный пункт.

Поиск через фильтр

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

• выделить заполненную ячейку;
• во вкладке «Главная» выбрать функцию «Сортировка»;
• нажать на кнопку «Фильтр»;
• открыть выпадающее меню;
• ввести искомый запрос;
• нажать кнопку «Ок».

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

Источник: https://FreeSoft.ru/blog/kak-iskat-v-excel-poisk-slov-i-yacheek-v-tablitsakh

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

9641 04.10.2019 Скачать пример

Классический выпадающий список в ячейке листа 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+стрелка вниз, чтобы развернуть выпадающий список без мыши.

P.S. 

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

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

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

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

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/1/9645/

4 способа поиска данных в таблице Excel

     Добрый день уважаемый читатель!

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

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

В связи с этим будем искать варианты, и применять хитрости для расширения её базового функционала.

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

     Теперь на примерах рассмотрим все 4 способа поиска данных в таблице Excel и комбинаций работы функции ВПР с другими функциями:

Используем функцию СУММПРОИЗВ

     Как я уже описывал ранее в своей статье о функции СУММПРОИЗВ, она является одной из мощнейших в арсенале Excel. И именно первый способ мы сделаем с помощью возможностей формулы при использовании функции СУММПРОИЗВ. Для наших целей формула будет выглядеть так:

          =СУММПРОИЗВ((C2:C11=G2)*(B2:B11=G3);D2:D11)      Принцип работы формулы следующий: создается условная таблица, в которой значения ячеек «G2» сравнивается с диапазоном «C2:C11» и ячейка «G3» с диапазоном «B2:B11». После этого сравниваются и сопоставляются все эти два массива и переводятся в единицы и нули, где значение единицы ставится строке, где все условия формулы выполнены. Следующая операция – это умножения полученного условного массива на диапазон «D2:D11», а поскольку в массиве всего одна единичка то формула получит результат 146.

     Обращаю ваше внимание, если в диапазоне «D2:D11» будут найдены текстовые значения, формула откажется работать. Для более углублённого ознакомления с функцией СУММПРОИЗВ советую почитать мою статью.

Применение функции ВЫБОР

     Я описывал уже функцию ВЫБОР, но в таком исполнении еще не упоминал. В нашем случае нужно создать новую таблицу, в которой будут совместными столбики «Период» и «Месяц», всё это виртуально создаст функция ВЫБОР.  Формула для работы будет выглядеть так:

              {=ВПР(G2&G3;ВЫБОР({1;2};C2:C11&B2:B11;D2:D11);2;0)}       Основная работа, которую проделывает функция ВЫБОР в своей части «ВЫБОР({1;2};C2:C11&B2:B11;D2:D11)» это объединение значений столбиков «Период» и «Город» в общий массив, значения в котором будут прописаны как: «МоскваЯнварь», «БрянскФевраль», …. и т.д… Получив такое объединённое значения столбиков мы сможем легко сделать просмотр и отбор нужного значения, вот теперь я думаю, формула стала ближе.

     Очень важно! Поскольку мы работаем с формулой массива, то ввод необходимо производить горячим сочетаниям клавиш Ctrl+Shift+Enter. В этом случае система определит формулу как созданную для массивов и установит фигурные скобочки по обеим сторонам формулы.

Создаем дополнительные столбики

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

     Рассмотрим на стандартном примере, когда необходимо определить продажи по двум показателям: «Период» и «Город». В этом случае обыкновенное использование функции ВПР не будет нам подходить, так как функция может возвращать значение по одному условию.

В таком случае нам необходимо создать дополнительный столбик, в котором произойдёт объединение двух критериев в один, поэтому в созданном столбике приписываем формулу слияния значений: =B2&C2.

А вот теперь результат из столбика D, мы сможем использовать в ячейке H4 нашу формулу:

           =ВПР(H2&H3;D2:E11;2;0)

     Как видите, наши отдельные условия отбора значений также объединяются  аргументом H2&H3 в один критерий. После поиска в указанном диапазоне D2:E11, формула вернёт найденное значение со столбика 2.

Совмещаем функции ПОИСКПОЗ и ИНДЕКС для работы

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

       А для нашего поиска данных в таблице Excel будем использовать такую формулу:

          ={ИНДЕКС(D2:D11;ПОИСКПОЗ(1;(B2:B11=G3)*(C2:C11=G2);0))}

      Что же она делает, такая большая и непонятная…. Рассмотрим ее в разрезе нескольких блоков или этапов.

     Теперь, когда функция ПОИСКПОЗ нашла в массиве значение, которое соответствует «1» и указала его позицию в шестой строке, а значит, в функцию ИНДЕКС был передан аргумент «6» для диапазона D2:D11.

     Ну, подведя итог можно ответить на закономерный вопрос: «а что же делать?» и «какой способ использовать?». Использовать вы можете абсолютно любой способ, но я бы рекомендовал выбрать вам наиболее удобный, простой и понятный. Я, к примеру, люблю использовать таблицы, которые просто изменять и просты для работы и понимания, чего советую и вам.

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

     Не забудьте подкинуть автору на кофе…

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Источник: http://topexcel.ru/4-sposoba-poiska-dannyx-v-tablice-excel/

Поиск в таблице Excel

Рубрика Excel

Также статьи о работе с таблицами в Экселе:

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

Для осуществления поиска данных в таблице Excel необходимо использовать пункт меню «Найти и выделить» на вкладке «Главная», в котором нужно выбирать вариант «Найти» или воспользоваться для вызова комбинацией клавиш «Ctrl + F».

Для примера попробуем найти необходимое число среди данных нашей таблицы, так как именно при поиске чисел необходимо учитывать некоторые тонкости поиска. Будем искать в таблице Excel число «10».

После выбора необходимого пункта меню в появившемся окошке поиска вводим искомое значение.

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

При использовании кнопки «Найти далее» следует также учитывать текущее расположение активной ячейки, так как поиск начнется именно с этой позиции.

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

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

При поиске слов в таблице Excel следует также учитывать все эти тонкости и к примеру, можно учитывать даже регистр букв.

Ну и на последок рассмотрим, как сделать поиск данных в Экселе только в необходимой области листа. Как видно из нашего примера, искомое значение «10» встречается сразу во всех столбцах данных.

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

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

Источник: http://officeprogs.ru/excel/poisk-v-tablitse-excel.html

Поиск значений в таблице «Excel» . Макрос

Как выполнить поиск значений в программе «Excel»

Поиск в Эксель

Далее описаны несколько вариантов поиска и фильтрации данных в таблице «Эксель».

1) Классический поиск (обыкновенный)

Вызвать панель (меню) поиска можно сочетанием горячих клавиш ctrl+F. (Легко запомнить: F- Found).

Окно поиска состоит из поля, в которое вводится искомый фрагмент текста или искомое число, вкладки с дополнительными настройками («Параметры») и кнопки «Найти».

Классический поиск в Excel

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

Условное форматирование для искомых ячеек.

2) Еще один вариант поиска заключается в выделении всех ячеек содержащих искомое слово каким-нибудь цветом

Такой поиск реализуется через опцию условное форматирование.

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

Настройка форматирования для выделения искомых слов

В меню условного форматирования выбрать «Правила выделения ячеек» … «Текст содержит…».

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

3) Третий способ поиска слов в таблице «Excel» — это использование фильтров

Фильтр устанавливается во вкладке «Данные» или сочетанием клавиш  ctrl+shift+L.

Настройка фильтра для поиска слов

• Кликнув по треугольнику фильтра можно в контекстном меню выбрать пункт «Текстовые фильтры»,  далее «содержит…» и указать искомое слово.
• После нажатия кнопки «Ок» на Экране останутся только ячейки столбца, содержащие искомое слово.

4) Способ поиска номер четыре — это макрос VBA для поиска (перебора значений)

1. В зависимости от назначения и условий использования макрос может иметь разные конфигурации,  но основная часть цикла перебора VBA макроса приведена ниже.
2. Sub Poisk()
3. ‘ ruexcel.

   ru макрос проверки значений (поиска)

4. Dim keyword As String
5. keyword = «Искомое слово» ‘присвоить переменной искомое слово
6. On Error Resume Next ‘при ошибке пропустить
7. For Each cell In Selection ‘для всх ячеек в выделении (выделенном диапазоне)
8. If cell.Value = «» Then GoTo Line1 ‘если ячейка пустая перейти на «Line1″

If InStr(StrConv(cell.Value, vbLowerCase), keyword) > 0 Then cell.Interior.Color = vbRed ‘если в ячейке содержится слово окрасить ее в красный цвет (поиск)

• Line1:
• Next cell
• End Sub

Источник: http://RuExcel.ru/poisk-v-excel/

Найти в ячейке любое слово из списка

Хитрости » 4 Январь 2016       Дмитрий       53701 просмотров

Как видно — в нашем каталоге только артикулы без наименований. У заказчика же помимо артикулов еще и название товара, т.е. много лишнего. И вам надо понять какие товары присутствуют в вашем каталоге, а какие нет:
Стандартных формул в Excel для подобного поиска и сравнения нет. Конечно, можно попробовать применить ВПР с подстановочными символами сначала к одной таблице, а затем к другой. Но если подобную операцию необходимо проделывать раз за разом, то прописывать по несколько формул к каждой таблице прямо скажем — не комильфо.
Поэтому я и решил сегодня продемонстрировать формулу, которая без всяких доп. манипуляций поможет такое сравнение сделать. Чтобы разобраться самостоятельно рекомендую скачать файл:

• Скачать файл:
•   Tips_All_AnyoneOfArray.xls (49,5 KiB, 14 583 скачиваний)

На листе «Заказ» в этом файле таблица, полученная от заказчика, а на листе «Каталог» наши артикулы.
Сама формула на примере файла будет выглядеть так:

=ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$A$2:$A$11)
=LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11)

эта формула вернет название артикула, если в тексте есть хоть один артикул из каталога и #Н/Д(#N/A) если артикул не найден в каталоге.
Прежде чем облагородить эту формулу всякими дополнениями(вроде виде убирания ненужных #Н/Д) давайте разберемся как она работает.
Функция ПРОСМОТР(LOOKUP) ищет заданное значение(2) в указанном диапазоне(массиве — второй аргумент). В качестве диапазона обычно приводится массив ячеек, но функция ПРОСМОТР имеет первую нужную нам особенность — она старается преобразовать непосредственно в массив любое выражение, записанное вторым аргументом. Иными словами она вычисляет выражение в этом аргументе, чем мы и пользуемся, подставив в качестве второго аргумента выражение: 1/ПОИСК(Каталог!$A$2:$A$11;A2). Часть ПОИСК(Каталог!$A$2:$A$11;A2) ищет поочередно каждое значение из списка Каталога в ячейке A2(наименование из таблицы Заказчика). Если значение найдено, то возвращается номер позиции первого символа найденного значения. Если значение не найдено — возвращается значение ошибки #ЗНАЧ!(#VALUE!). Теперь вторая особенность: функция требует расположения данных в массиве в порядке возрастания. Если данные расположены иначе — функция будет просматривать массив до тех пор, пока не найдет значение больше искомого, но максимально к нему приближенное(хотя если данные позволяют — для более точного поиска все же лучше отсортировать список по возрастанию). Поэтому сначала мы 1 делим на выражение ПОИСК(Каталог!$A$2:$A$11;A2), чтобы получить массив вида:{0,0181818181818182:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!}
Ну а в качестве искомого значения мы подсовываем функции число 2 — заведомо большее число, чем может вообще встретиться в массиве(т.к. единица, поделенная на любое число будет меньше двух). И как результат мы получим позицию в массиве, в которой встречается последнее совпадение из каталога. После чего функция ПРОСМОТР запомнит эту позицию и вернет значение из массива Каталог!$A$2:$A$11(третий аргумент), записанное в этом массиве для этой позиции.
Вы можете просмотреть этапы вычисления функции самостоятельно для каждой ячейки, я здесь просто приведу этапы чуть в расширенном для понимания виде:

1. =ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$A$2:$A$11)
2. =ПРОСМОТР(2;
   1/{55:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!};

   Каталог!$A$2:$A$11)

3. =ПРОСМОТР(2;{0,0181818181818182:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!};Каталог!$A$2:$A$11)
4. =ПРОСМОТР(2;
   1;

   {«FM2-3320″:»CV455689″:»Q5949X»:»CE321A»:»CE322A»:»CE323A»:»00064073″:»CX292708″:»CX292709″:»CX292710″})

5. =»FM2-3320″

Теперь немного облагородим функцию и сделаем еще пару реализаций
Реализация 1:
Вместо артикулов и #Н/Д выведем для найденных позиций «Есть», а для отсутствующих «Не найден в каталоге»:
=ЕСЛИ(ЕНД(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2)));»Не найден в каталоге»;»Есть»)
=IF(ISNA(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2))),»Не найден в каталоге»,»Есть»)
работа функции проста — с ПРОСМОТР(LOOKUP) разобрались, поэтому остались только ЕНД и ЕСЛИ.
ЕНД(ISNA) возвращает ИСТИНА(TRUE) если выражение внутри неё возвращает значение ошибки #Н/Д(#N/A) и ЛОЖЬ(FALSE) если выражение внутри не возвращает значение этой ошибки.
ЕСЛИ(IF) возвращает то, что указано вторым аргументом если выражение в первом равно ИСТИНА(TRUE) и то, что указано третьим аргументом, если выражение первого аргумента ЛОЖЬ(FALSE).

Реализация 2:
Вместо #Н/Д выведем «Не найден в каталоге», но при этом если артикулы найдены — выведем названия этих артикулов:
=ЕСЛИОШИБКА(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$A$2:$A$11);»Нет в каталоге»)
=IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11),»Нет в каталоге»)
Про функция ЕСЛИОШИБКА(IFERROR) я подробно рассказывал в этой статье: Как в ячейке с формулой вместо ошибки показать 0.
Если вкратце, то если выражение, заданное первым аргументом функции, возвращает значение любой ошибки, то функция вернет то, что записано вторым аргументом(в нашем случае это текст «Не найден в каталоге»). Если же выражение не возвращает ошибку, то функция ЕСЛИОШИБКА запишет то значение, которое было получено выражением в первом аргументе(в нашем случае это будет наименование артикула).

Реализация 3
Надо не просто определить какому артикулу соответствует, но и вывести цену для наименования по этому артикулу(сами цены должны быть расположены в столбце B листа Каталог):

=ЕСЛИОШИБКА(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$B$2:$B$11);»»)
=IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$B$2:$B$11),»»)

Пара важных замечаний:

• данные на листе с артикулами не должны содержать пустых ячеек. Иначе с большой долей вероятности формула будет возвращать значение именно пустой ячейки, а не то, которое подходит под условия поиска
• формула осуществляет поиск таким образом, что ищется любое совпадение. Например, в качестве артикула записана цифра 1, а в строке наименований может встречаться помимо целой 1 еще и 123, 651123, FG1412NM и т.п. Для всех этих наименований может быть подобран артикул 1, т.к. он содержится в каждом наименовании. Как правило это может произойти, если артикул 1 расположен в конце списка

1. Поэтому желательно перед использованием формулы отсортировать список по возрастанию(от меньшего к большему, от А до Я).
2. В приложенном в начале статьи примере вы найдете все разобранные варианты.
3. Если же вам понадобится выводить все наименования, то можно воспользоваться функцией СОДЕРЖИТ_ОДНО_ИЗ из моей надстройки MulTEx.
4. Так же см.:
   Сравнение текста по части предложения
   Что такое формула массива
   Как найти значение в другой таблице или сила ВПР
   ВПР с поиском по нескольким листам

Статья помогла? Поделись ссылкой с друзьями!

Источник: https://www.excel-vba.ru/chto-umeet-excel/najti-v-yachejke-lyuboe-slovo-iz-spiska/

Как правильно искать в таблицах Excel — Трюки и приемы в Microsoft Excel

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

Выполните команду Главная ► Редактирование ► Найти и выделить ► Найти (или нажмите Ctrl+F), чтобы открыть диалоговое окно Найти и заменить. Если вам нужно заменить данные, то выберите команду Главная ► Редактирование ► Найти и выделить ► Заменить (или нажмите Ctrl+H).

Если в открывшемся диалоговом окне Найти и заменить нажать кнопку Параметры, то отобразятся дополнительные параметры поиска информации (рис. 21.1).

Рис. 21.1. Вкладка Найти диалогового окна Найти и заменить

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

Однако вполне вероятно, что вы ничего не найдете, так как вы могли ввести имя клиента по-другому, например ИванСмирнов или И. Смирнов, или даже допустили ошибку в фамилии.

При поиске такого имени лучше всего использовать подстановочные знаки.

Введите ив*смир* в поле Найти, а затем нажмите кнопку Найти все.

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

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

При поиске с помощью диалогового окна Найти и заменить можно использовать два подстановочных знака:

• ? — соответствует любому символу;
• * — соответствует любому количеству символов.

Кроме того, данные подстановочные символы можно также применять при поиске числовых значений. Например, если в строке поиска задать 3*, то в результате отобразятся все ячейки, которые содержат значение, начинающееся с 3, а если вы введете 1?9, то получите все трехзначные записи, которые начинаются с 1 и заканчиваются 9.

• Для поиска вопросительного знака или звездочки поставьте перед ними символ тильды (~).
  Например, следующая строка поиска находит текст *NONE*: -*N0NE~*
• Чтобы найти символ тильды, поставьте в строке поиска две тильды.
• Если вам кажется, что поиск работает неправильно, проверьте три следующие настройки (которые иногда изменяются сами по себе).

• Флажок Учитывать регистр — установите его, чтобы регистр искомого текста совпадал с регистром заданного текста. Например, если вы зададите в поиске слово иван и установите указанный флажок, то слово Иван в результатах поиска не отобразится.
• Флажок Ячейка целиком — установите его, чтобы найти ячейку, которая содержит в точности тот текст, который указан в строке поиска. Например, набрав в строке поиска слово Excel и установив указанный флажок, вы не найдете ячейку, содержащую словосочетание Microsoft Excel.
• Раскрывающийся список Область поиска — список содержит три пункта: значения, формулы и примечания. Например, если в строке поиска вы зададите число 900 и в раскрывающемся списке Область поиска выберете пункт значения, то в результатах поиска вы не увидите ячейку, содержащую значение 900, если оно получено при использовании формулы.

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

Кроме того, учтите, что с помощью окна Найти и заменить нельзя найти отформатированные числовые значения. Например, если в строку поиска вы введете $5*, то значение, к которому применено денежное форматирование и которое выглядит как $54.00, не будет найдено.

Работа с датами может оказаться непростой, поскольку Excel поддерживает очень много форматов дат.

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

Например, если ваша система использует формат даты m/d/y, строка поиска 10/*/2010 находит все даты в октябре 2010 года, независимо от того, как они отформатированы.

Используйте пустое поле Заменить на, чтобы быстро удалить какую-нибудь информацию на рабочем листе. Например, введите — * в поле Найти и оставьте поле Заменить на пустым. Затем нажмите кнопку Заменить все, чтобы Excel нашел и убрал все звездочки на листе.

Источник: http://excelexpert.ru/kak-pravilno-iskat-v-tablicax-excel

Секреты поиска в Excel

При большом количестве данных на листе Excel рано или поздно придется выполнить поиск переделенного значения. Простой поиск выполняется с помощью команды: «Найти и выделить», которая находится в группе «Редактирование» на вкладке «Главная».

Смотрите также видеоверсию статьи «Секреты поиска в Excel».

Вызвать диалоговое окно поиска можно также и с помощью горячего сочетания клавиш «Ctrl+F«.

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

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

В этом материале и рассмотрим нюансы и секреты окна поиска, которые большинству пользователей не знакомы.

Некоторые настройки, которые могут помочь в решении поставленной задачи скрываются за кнопкой «Параметры».

Рассмотрим ситуацию, когда нужно найти сотрудника Комарова Александра Ивановича, но есть ряд факторов, из-за которых возникают сложности:

• мы не знаем, как он записан, с полной расшифровкой имени и отчества, или только инициалы;
• есть определенная неуверенность в том какая вторая буква «а» или «о»;
• не понятно, как точно записан сотрудник сначала фамилия, потом имя и отчество или наоборот.

Комаров Александр Иванович может быть записан как: А. Комаров, Комаров Александр, Камаров Александр И. и т.д. вариантом достаточно.

Чтобы выполнить нестрогий поиск, следует воспользоваться символами-заменителями их еще называют джокерными символами. Окно поиска в Excel поддерживает работу с двумя такими символами: «*» и «?»:

• «*» соответствует любому количеству символов;
• «?» соответствует любому отдельно взятому символу.

Здесь сразу же возникает вопрос, а что делать если необходимо выполнить поиск вопросительного знака или астериск (известный как знак умножения)? Все просто, достаточно перед искомыми знаками просто поставить тильду «~», соответственно, если необходимо выполнить поиск тильды, тогда необходимо поставить две тильды.

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

Параметр «Ячейка целиком» означает поиск строго текстовой строки, введенной в поле найти и ничего более, другими словами, если кроме фамилии есть еще инициалы, то запись найдена не будет.

При осуществлении поиска в Excel нужно помнить несколько важных правил:

1. Поиск выполняется в выделенной области, а если нет выделения, тогда на всем листе. Поэтому не стоит удивляться, если вы случайно выделите пару пустых ячеек, запустите окно поиска и не сможете ничего найти.
2. При поиске не учитывается форматирование, поэтому, никаких знаков обозначения валюты добавлять не стоит.
3. При работе с датами, лучше выполнять поиск их в формате по умолчанию для конкретной системы, в этом случае, в Excel будут найдены все даты, удовлетворяющие условию. Например, если в системе используется формат д/м/г, то поисковый запрос */12/2015 выведет все даты за декабрь 2015 года, независимо от того, как они отформатированы (28.12.2015, 28/12/2015, или 28 декабря 2015 и т.д.).

Источник: https://msoffice-prowork.com/sekrety-poiska-v-excel/