Excel создать ячейку с поиском

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

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

Создайте собственное поле поиска с условным форматированием, чтобы выделить все результаты поиска

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

1. Выберите диапазон с данными, которые вам нужны для поиска в поле поиска, затем нажмите Условное форматирование > Новое правило под Главная таб. Смотрите скриншот:

2. в Новое правило форматирования диалоговое окно, вам необходимо:

2.1) Выбрать Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант в Выберите тип правила коробка;

2.2) Введите формулу = ISNUMBER (ПОИСК ($ B $ 2; A5)) в Формат значений, где эта формула истинна коробка;

2.3) Нажмите Формат кнопка, чтобы указать выделенный цвет для искомого значения;

2.4) Нажмите OK кнопку.

Заметки:

1. В формуле $ B $ 2 — это пустая ячейка, которую необходимо использовать в качестве поля поиска, а A5 — это первая ячейка выбранного диапазона, в котором необходимо искать значения. Пожалуйста, измените их по своему усмотрению.

2. В формуле не учитывается регистр.

Теперь поле поиска создано: при вводе критериев поиска в поле поиска B2 и нажатии клавиши Enter выполняется поиск всех совпадающих значений в указанном диапазоне, которые немедленно выделяются, как показано ниже.

Создайте собственное окно поиска с формулами для вывода всех результатов поиска

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

1. Выберите пустую ячейку, которая находится рядом с ячейкой E4, здесь я выбираю ячейку D4, затем вводю формулу = ЕСЛИОШИБКА (ПОИСК ($ B $ 2; E4) + СТРОКА () / 100000; «») в строку формул, а затем нажмите Enter ключ. Смотрите скриншот:

Внимание: В формуле $ B $ 2 — это ячейка, которую необходимо использовать в качестве поля поиска, E4 — это первая ячейка списка данных, в котором необходимо выполнить поиск. Вы можете менять их по своему усмотрению.

2. Продолжайте выбирать ячейку E4, затем перетащите маркер заполнения вниз в ячейку D23. Смотрите скриншот:

3. Теперь выберите ячейку C4 и введите формулу. = ЕСЛИОШИБКА (РАНГ (D4; $ D $ 4: $ D $ 23,1); «») в панель формул и нажмите Enter ключ. Выберите ячейку C4, затем перетащите маркер заполнения до C23. Смотрите скриншот:

4. Теперь вам нужно заполнить диапазон A4: A23 порядковым номером, который увеличивается на 1 от 1 до 20, как показано на скриншоте ниже:

5. Выберите пустую ячейку, в которой будет отображаться результат поиска, введите формулу. = ЕСЛИОШИБКА (ВПР (A4; $ C $ 4: $ E $ 23,3; FALSE); «») в панель формул и нажмите Enter ключ. Продолжая выбирать ячейку B4, перетащите маркер заливки вниз до B23, как показано ниже.

С этого момента при вводе данных в поле поиска B2 все совпавшие значения будут перечислены в диапазоне B4: B23, как показано на скриншоте ниже.

Внимание: этот метод не чувствителен к регистру.

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

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

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

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

• Что такое динамические массивы в Excel
• Разбор трех основных функций динамических массивов: СОРТ, ФИЛЬТР и УНИК
• 4 способа создать выпадающий список на листе Excel

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

​Смотрите также​: Через XML можно​ сделать так что​

​ вторую для вывода​ определенный месяц.​ мы использовали скелет​​ с именем Товар​​ После чего в​ данный аргумент, чтобы​​ в двойные кавычки.​​ нажмите клавишу F2,​ или больше, чем​​    Обязательный. Текст, который требуется​​, так как слово​

​, чтобы быстро скопировать​ строка («»).​ значение, тем выше​

1. ​Этот пример научит вас​​ сделать нечто вроде​​ бы окно поиска​​ названия строк таблицы​​Чтобы найти какой товар​ первой формулы без​ 4. Но так​​ ячейке F1 сработает​​ задать позицию (начиная​

   ​босс​
​ а затем — клавишу​

2. ​ длина​ найти.​ «base» начинается с​ формулу в другие​​Выберите ячейку​​ оно должно располагаться.​ создавать собственную строку​ этого(панель Поиск):​​ появлялось, например в​​ по зачиню ячейки.​​ обладал максимальным объемом​​ функции МАКС. Главная​​ как нам заранее​​ вторая формула, которая​ слева), с которой​Функция​​ ВВОД. При необходимости​​просматриваемого текста​Просматриваемый_текст​ пятого символа слова​ ячейки.​C4​И «United States», и​ поиска в Excel.​Например, должна быть создана​ правой стороне экрана,​ Название соответствующих строк​
3. ​ продаж в определенном​ структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0).​ не известен этот​ уже будет использовать​ нужно начать поиск.​FIND​ измените ширину столбцов,​​, возвращается значение ошибки​​    Обязательный. Текст, в котором​ «database». Можно использовать​Измените цвет чисел в​и вставьте функцию​​ «United Kingdom» возвращают​​Вот так выглядит таблица.​

   ​ таблица, включающая в​
​ при открытии файла​

4. ​ (товаров) выводим в​ месяце следует:​ Мы заменили функцию​​ номер мы с​​ значения ячеек B1​Примечание:​(НАЙТИ) и функция​​ чтобы видеть все​​ #ЗНАЧ!.​​ нужно найти значение​​ функции​ столбце​RANK​ значение 1. Как​ Если ввести поисковый​ себя список сотрудников​ содержащем в названии​​ F2.​​В ячейку B2 введите​ МАКС на ПОИСКПОЗ,​ помощью функции СТОЛБЕЦ​ и D1 в​Строка «o» найдена​SEARCH​ данные.​Аргумент​ аргумента​ПОИСК​А​​(РАНГ), как показано​​ быть? Чуть позже​ запрос в ячейку​ фирмы, как сделать​ определенное слово, например​ВНИМАНИЕ! При использовании скелета​ название месяца Июнь​
5. ​ которая в первом​​ создаем массив номеров​​ качестве критериев для​​ в позиции 5.​​(ПОИСК) очень похожи​Данные​

   ​начальная_позиция​
​искомый_текст​

6. ​и​на белый и​​ ниже:​​ мы присвоим всем​B2​ так, чтобы можно​​ «прайс»? Сделать силами​​ формулы для других​​ – это значение​​ аргументе использует значение,​ столбцов для диапазона​ поиска соответствующего месяца.​Примечание:​ друг на друга.​Выписки​можно использовать, чтобы​.​​ПОИСКБ​​ скройте столбцы​=IFERROR(RANK(D4,$D$4:$D$197,1),»»)​​ данным уникальные значения​​, Excel найдет совпадения​ было осуществлять поиск​ макроса это я​​ задач всегда обращайте​​ будет использовано в​
7. ​ полученное предыдущей формулой.​​ B4:G15.​​Теперь узнаем, в каком​Строка «o» найдена​ Этот пример демонстрирует​Доход: маржа​ пропустить определенное количество​Начальная_позиция​​для определения положения​​С​​=ЕСЛИОШИБКА(РАНГ(D4;$D$4:$D$197;1);»»)​​ с помощью функции​ в столбце​

   ​ по этой таблице,​
​ могу, но он​

8. ​ внимание на второй​ качестве поискового критерия.​​ Оно теперь выступает​​Это позволяет функции ВПР​ максимальном объеме и​ в позиции 7​
9. ​ разницу.​маржа​​ знаков. Допустим, что​​    Необязательный. Номер знака в​ символа или текстовой​​и​​Дважды щелкните по правому​​RANK​​E​

​ набрав конкретную фамилию.​ и будет привязан​

​ и третий аргумент​В ячейку D2 введите​
​ в качестве критерия​
​ собрать целый массив​

​ в каком месяце​

office-guru.ru

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

​ (поиск начался с​Попробуйте использовать функцию​Здесь «босс».​​ функцию​​ аргументе​​ строки в другой​​D​

Описание

​ углу ячейки​​(РАНГ), но для​​и выдаст результат​​Рубероид​​ к документу, может​ поисковой функции ГПР.​ формулу:​ для поиска месяца.​ значений. В результате​ была максимальная продажа​ позиции 6).​FIND​Формула​ПОИСК​просматриваемый_текст​

​ текстовой строке, а​

​.​​С4​​ этого нам нужно​ в столбце​: CTRL + F​

​ есть возможность как​ Количество охваченных строк​Для подтверждения после ввода​

​ И в результате​

​ в памяти хранится​​ Товара 4.​​Урок подготовлен для Вас​(НАЙТИ), чтобы найти​Описание​нужно использовать для​, с которого следует​​ затем вернуть текст​​Результат: Ваша собственная строка​​, чтобы быстро скопировать​​ слегка скорректировать результат​B​ все найдется сразу​ это автоматизировать?​ в диапазоне указанного​ формулы нажмите комбинацию​​ функция ПОИСКПОЗ нам​​ все соответствующие значения​​Чтобы выполнить поиск по​​ командой сайта office-guru.ru​ положение подстроки в​​Результат​​ работы с текстовой​​ начать поиск.​​ с помощью функций​ поиска в Excel.​ формулу в другие​

​ формулы в ячейке​​.​

• ​Еще можешь выбрать​Спасибо.​ в аргументе, должно​

• ​ клавиш CTRL+SHIFT+Enter, так​ возвращает номер столбца​ каждому столбцу по​ столбцам следует:​Источник: http://www.excel-easy.com/examples/find-vs-search.html​ строке. Как видно​=ПОИСК(«и»;A2;6)​ строкой «МДС0093.МужскаяОдежда». Чтобы​Функции​ПСТР​Урок подготовлен для Вас​ ячейки.​D4​

​Чтобы создать эту строку​ автофильтр по заголовкам​Максим Зеленский​ совпадать с количеством​ как формула будет​

Синтаксис

​ 2 где находится​

​ строке Товар 4​

​В ячейку B1 введите​Перевел: Антон Андронов​

• ​ на рисунке, эта​​Позиция первого знака «и»​ найти первое вхождение​

• ​ПОИСК​​и​ командой сайта office-guru.ru​Объяснение:​​, как показано ниже:​​ поиска, следуйте инструкции​

• ​ колонок, и уже​​: Посмотрите подход​ строк в таблице.​​ выполнена в массиве.​​ максимальное значение объема​ (а именно: 360;​

Замечание

• ​ значение Товара 4​​Автор: Антон Андронов​​ функция чувствительна к​​ в строке ячейки​​ «М» в описательной​и​ПСТРБ​​Источник: http://www.excel-easy.com/examples/search-box.html​​Функция​​IFERROR(SEARCH($B$2,E4)+ROW()/100000,»»)​​ ниже:​

• ​ затем в списке​​diman27​​ А также нумерация​ А в строке​ продаж для товара​​ 958; 201; 605;​​ – название строки,​​Имеем таблицу, в которой​​ регистру.​ A2, начиная с​ части текстовой строки,​ПОИСКБ​или заменить его​Перевел: Антон Андронов​RANK​​ЕСЛИОШИБКА(ПОИСК($B$2;E4)+СТРОКА()/100000;»»)​​Выделите ячейку​

• ​ фильтровать необходимых тебе​​: Дело в том,​​ должна начинаться со​ формул появятся фигурные​

• ​ 4. После чего​​ 462; 832). После​​ которое выступит в​ записаны объемы продаж​

• ​Теперь испытайте функцию​​ шестого знака.​​ задайте для аргумента​не учитывают регистр.​ с помощью функций​​Автор: Антон Андронов​​(РАНГ) возвращает порядковый​Снова дважды кликните по​

• ​D4​​ персон.​​ что модуль должен​ второй строки!​ скобки.​ в работу включается​​ чего функции МАКС​​ качестве критерия.​ определенных товаров в​SEARCH​7​начальная_позиция​ Если требуется учитывать​ЗАМЕНИТЬ​​В этой статье описаны​​ номер значения. Если​ правому нижнему углу​и вставьте функцию​Ilia karbofos​ находиться в книге.​Скачать пример поиска значения​В ячейку F1 введите​​ функция ИНДЕКС, которая​​ остается только взять​В ячейку D1 введите​ разных месяцах. Необходимо​(ПОИСК), чтобы найти​​=ПОИСК(A4;A3)​​значение 8, чтобы​ регистр, используйте функции​и​​ синтаксис формулы и​​ третий аргумент функции​ ячейки​SEARCH​​: В такой постановке​​ А прайс присылают​ в столбце и​ вторую формулу:​​ возвращает значение по​​ из этого массива​

Примеры

​ следующую формулу:​ в таблице найти​ положение искомого текста​Начальная позиция строки «маржа»​ поиск не выполнялся​НАЙТИ​ЗАМЕНИТЬБ​ использование функций​ равен 1, Excel​D4​(ПОИСК), как показано​ вопроса — Ctrl+F​ без него, а​

support.office.com

Функции НАЙТИ и ПОИСК в Excel

​ сложное — пиши​​ то поиск мне​​ Excel по столбцам​​ ГПР (Горизонтальный ПРосмотр)​​ диапазона. Так как​ D1, как результат​ так как формула​ Но поиск должен​

1. ​Примечание:​​ (ячейка, в которой​​ данном случае —​В аргументе​Важно:​в Microsoft Excel.​ мы добавили функцию​Объяснение:​
2. ​.​​ на мыло​​ открыть проще. Я​ и строкам​ указываем ссылку на​ у нас есть​ вычисления формулы.​

   ​ должна быть выполнена​​ быть выполнен отдельно​Текст «excel» имеет​ выполняется поиск — A3).​ «МДС0093»). Функция​искомый_текст​ ​

3. ​Функции​​ROW​​Функция​=SEARCH($B$2,E4)​Катрррин​ хочу автоматизировать этот​

   

   ​По сути содержимое диапазона​​ ячейку с критерием​ номер столбца 2,​Как видно конструкция формулы​ в массиве. Если​ по диапазону строки​ позицию 11 в​

4. ​8​ПОИСК​​можно использовать подстановочные​​Эти функции могут быть​​ПОИСК​​(СТРОКА), все значения​ROW​=ПОИСК($B$2;E4)​: Выделяешь столбец и​ процесс один раз​ нас вообще не​ для поиска. Во​ а номер строки​

   ​ проста и лаконична.​​ все сделано правильно,​ или столбца. То​

   

   ​ данной строке, даже,​​=ЗАМЕНИТЬ(A3;ПОИСК(A4;A3);6;»объем»)​начинает поиск с​ знаки: вопросительный знак​ доступны не на​

​И​ в столбце​
​(СТРОКА) возвращает номер​
​Дважды кликните по маркеру​

​ делаешь автофильтр, потом​

office-guru.ru

Поиск значения в столбце и строке таблицы Excel

​ написав макрос. Может​ интересует, нам нужен​ втором аргументе указана​ в диапазоне где​ На ее основе​ в строке формул​ есть будет использоваться​ если он используется​Заменяет слово «маржа» словом​ восьмого символа, находит​ (​ всех языках.​ПОИСКБ​D​ строки ячейки. Если​ автозаполнения, который находится​ условия задаешь и​ быть, даже скорее​

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

​ просто счетчик строк.​ ссылка на просматриваемый​ хранятся названия месяцев​ можно в похожий​ появятся фигурные скобки.​

​ только один из​ немного в другом​ «объем», определяя позицию​

​ знак, указанный в​?​Функция ПОИСКБ отсчитывает по​находят одну текстовую​стали уникальными. Как​ мы разделим номер​ в правом нижнем​ все!​ всего, это невозможно,​ То есть изменить​ диапазон таблицы. Третий​ в любые случаи​ способ находить для​В ячейку F1 введите​ критериев. Поэтому здесь​ регистре («Excel»).​ слова «маржа» в​ аргументе​) и звездочку (​

Поиск значения в строке Excel

​ два байта на​ строку в другой​ следствие, числа в​ строки на крупное​ углу ячейки​

​Jlejluk​ но, а вдруг​

1. ​ аргументы на: СТРОКА(B2:B11)​ аргумент генерирует функция​ будет 1. Тогда​ определенного товара и​ вторую формулу:​
2. ​ нельзя применить функцию​Функция​
3. ​ ячейке A3 и​искомый_текст​*​ каждый символ, только​ и возвращают начальную​ столбце​ число и прибавим​D4​: возможно ты имеешь​
4. ​.​ или СТРОКА(С2:С11) –​
5. ​ СТРОКА, которая создает​ нам осталось функцией​

​ другие показатели. Например,​Снова Для подтверждения нажмите​ ИНДЕКС, а нужна​SEARCH​ заменяя этот знак​

Принцип действия формулы поиска значения в строке Excel:

​, в следующей позиции,​). Вопросительный знак соответствует​ если языком по​ позицию первой текстовой​C​ это значение к​, чтобы быстро скопировать​ ввиду это..​V​ это никак не​ в памяти массив​ ИНДЕКС получить соответственное​ минимальное или среднее​ комбинацию клавиш CTRL+SHIFT+Enter.​ специальная формула.​(ПОИСК) более универсальна.​ и последующие пять​ и возвращает число​ любому знаку, звездочка —​ умолчанию является язык​ строки (считая от​тоже уникальны.​ результату функции​

​ формулу во все​данные -> фильтр​: сохраняете книгу с​ повлияет на качество​ номеров строк из​ значение из диапазона​ значение объема продаж​Найдено в каком месяце​Для решения данной задачи​ Вы можете использовать​ знаков текстовой строкой​ 9. Функция​ любой последовательности знаков.​ с поддержкой БДЦС.​ первого символа второй​Мы почти закончили. Функцию​SEARCH​ оставшиеся ячейки столбца​

​ -> автофильтр​ макросом как надстройку.​ формулы. Главное, что​ 10 элементов. Так​ B4:G4 – Февраль​ используя для этого​ и какая была​ проиллюстрируем пример на​ подстановочные символы, когда​ «объем.»​ПОИСК​ Если требуется найти​ В противном случае​ текстовой строки). Например,​VLOOKUP​(ПОИСК), у нас​D​Дмитрий​ Подключаете её и​ в этих диапазонах​

Как получить заголовки столбцов по зачиню одной ячейки?

​ как в табличной​ (второй месяц).​ функции МИН или​ наибольшая продажа Товара​ схематической таблице, которая​ применяете её.​Доход: объем​всегда возвращает номер​ вопросительный знак или​ функция ПОИСКБ работает​ чтобы найти позицию​(ВПР) мы будем​ всегда будут получаться​.​: Это тебе надо​ макрос в вашем​ по 10 строк,​ части у нас​​ СРЗНАЧ. Вам ни​ 4 на протяжении​ соответствует выше описанным​Примечание:​=ПСТР(A3;ПОИСК(» «;A3)+1,4)​ знака, считая от​ звездочку, введите перед​ так же, как​ буквы «n» в​ использовать, чтобы извлечь​ уникальные значения, а​Объяснение:​ освоить MSAccess​ экселе будет постоянно.​ как и в​ находится 10 строк.​Вторым вариантом задачи будет​ что не препятствует,​ двух кварталов.​ условиям.​Вопросительный знак (?)​Возвращает первые четыре знака,​ начала​ ним тильду (​ функция ПОИСК, и​ слове «printer», можно​

​ найденные страны (наименьшее​

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

​ небольшой прирост не​Функция​Jguar​diman27​ таблице. И нумерация​Далее функция ГПР поочередно​ поиск по таблице​ чтобы приведенный этот​В первом аргументе функции​Лист с таблицей для​ соответствует ровно одному​

​ которые следуют за​просматриваемого текста​~​ отсчитывает по одному​ использовать следующую функцию:​

​ значение первым, второе​ повлияет на ранжирование.​SEARCH​: Данные -> Фильтр​

1. ​: Просто создаю пустую​ начинается со второй​ используя каждый номер​ с использованием названия​ скелет формулы применить​
2. ​ ВПР (Вертикальный ПРосмотр)​ поиска значений по​
3. ​ символу. Звездочка (*)​ первым пробелом в​, включая символы, которые​).​ байту на каждый​=ПОИСК(«н»;»принтер»)​ наименьшее вторым, и​ Теперь значение для​
4. ​(ПОИСК) ищет начальную​ -> Автофильтр.​
5. ​ книгу с макросом​ строки!​

Принцип действия формулы поиска значения в столбце Excel:

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

​diman27​ соответственных значений продаж​ критерия. В такие​ сложных функций для​ ячейку где находится​Над самой таблицей расположена​ (от нуля и​ (ячейка A3).​ аргумента​искомый_текст​

​К языкам, поддерживающим БДЦС,​4​B4​ 1,00006, а для​ в строке. Функция​ то сложнее. (Когда​ макрос при открытии​: Привет всем. Давно​ из таблицы по​ случаи мы должны​

​ реализации максимально комфортного​ критерий поиска. Во​ строка с результатами.​ более).​марж​начальная_позиция​не найден, возвращается​ относятся японский, китайский​, так как «н»​и вставьте функцию​ «United Kingdom» –​SEARCH​ не знаешь)​ любого файла сам​

​ читаю форум и​ определенному месяцу (Июню).​ изменить скелет нашей​

​ анализа отчета по​ втором аргументе указывается​ В ячейку B1​Еще одна интересная особенность​

​=ПОИСК(«»»»;A5)​больше 1.​ значение ошибки #ЗНАЧ!.​ (упрощенное письмо), китайский​ является четвертым символом​VLOOKUP​ 1,00009. Кроме этого​(ПОИСК) не учитывает​Пользователь удален​ будет работать. Я​ нашел много полезного​ Далее функции МАКС​ формулы: функцию ВПР​ продажам.​ диапазон ячеек для​ водим критерий для​

exceltable.com

Как сделать постоянное окно поиска?

​ функций​​Позиция первой двойной кавычки​Скопируйте образец данных из​Если аргумент​ (традиционное письмо) и​ в слове «принтер».​(ВПР), как показано​ мы добавили функцию​ регистр. В слове​: можно использовать автофильтр​ все правильно понял?​ и нужного, но​ осталось только выбрать​ заменить ГПР, а​Например, как эффектно мы​ просмотра в процессе​ поискового запроса, то​FIND​ («) в ячейке​ следующей таблицы и​начальная_позиция​ корейский.​Можно также находить слова​ ниже.​IFERROR​
​ «Tunisia» строка «uni»​

​ или сортировка. Зайди​​vikttur​

​ вот сейчас не​​ максимальное значение из​ функция СТОЛБЕЦ заменяется​ отобразили месяц, в​ поиска. В третьем​ есть заголовок столбца​(НАЙТИ) и​ A5.​ вставьте их в​опущен, то он​ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция])​ в других словах.​=IFERROR(VLOOKUP(A4,$C$4:$E$197,3,FALSE),»»)​(ЕСЛИОШИБКА). Если ячейка​ имеет начальное положение​ в «данные «там​​: Проверить самому?​

​ смог найти. Как​​ этого массива.​ на СТРОКА.​ котором была максимальная​ аргументе функции ВПР​ или название строки.​

​SEARCH​​5​ ячейку A1 нового​ полагается равным 1.​ПОИСКБ(искомый_текст;просматриваемый_текст;[начальная_позиция])​ Например, функция​=ЕСЛИОШИБКА(ВПР(A4;$C$4:$E$197;3;ЛОЖЬ);»»)​ содержит ошибку, к​

​ 2, а в​​ увидишь​

​diman27​​ сделать постоянное окно,​​Далее немного изменив первую​

​Это позволит нам узнать​​ продажа, с помощью​ должен указываться номер​ А в ячейке​

planetaexcel.ru

Как сделать в Excel таблицу с возможностью поиска по этой таблице?

​(ПОИСК) в том,​=ПСТР(A5;ПОИСК(«»»»;A5)+1;ПОИСК(«»»»;A5;ПОИСК(«»»»;A5)+1)-ПОИСК(«»»»;A5)-1)​ листа Excel. Чтобы​Если аргумент​Аргументы функций ПОИСК и​=ПОИСК(«base»;»database»)​Дважды щелкните по правому​ примеру, когда строка​

​ слове «United States»​​»​: Все получилось​
​ или строку, поиска?​ формулу с помощью​ какой объем и​ второй формулы. Не​ столбца, из которого​ D1 формула поиска​

​ что они имеют​​Возвращает из ячейки A5​ отобразить результаты формул,​начальная_позиция​
​ ПОИСКБ описаны ниже.​возвращает​ нижнему углу ячейки​

​ не может быть​​ начальное положение равно​Zemliak​Всем спасибо!​ Приходится много искать,​

​ функций ИНДЕКС и​​ какого товара была​ сложно заметить что​
​ следует взять значение​ должна возвращать результат​

​ 3-й дополнительный аргумент.​​ только текст, заключенный​ выделите их и​

​не больше 0​​Искомый_текст​5​
​B4​ найдена, возвращается пустая​ 1. Чем меньше​

​: http://www.hardline.ru/selfteachers/Info/​​The_Prist​ может есть возможность​ ПОИСКПОЗ, мы создали​ максимальная продажа в​
​ во второй формуле​

​ на против строки​​ вычисления соответствующего значения.​

​ Вы можете использовать​

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

Поисковая функция в Excel

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

Способ 1: простой поиск

Простой поиск данных в программе Excel позволяет найти все ячейки, в которых содержится введенный в поисковое окно набор символов (буквы, цифры, слова, и т.д.) без учета регистра.

1. Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.



2. После того, как вы перешли по соответствующим пунктам на ленте, или нажали комбинацию «горячих клавиш», откроется окно «Найти и заменить» во вкладке «Найти». Она нам и нужна. В поле «Найти» вводим слово, символы, или выражения, по которым собираемся производить поиск. Жмем на кнопку «Найти далее», или на кнопку «Найти всё».



3. При нажатии на кнопку «Найти далее» мы перемещаемся к первой же ячейке, где содержатся введенные группы символов. Сама ячейка становится активной.

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

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

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

   

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

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

Способ 2: поиск по указанному интервалу ячеек

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

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



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

Способ 3: Расширенный поиск

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

К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.

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

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



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

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

   

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

   

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

   

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

   

   Ещё более точно поиск можно задать, нажав на кнопку «Формат».

   

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

   

   Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».

   

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

   

   После того, как формат поиска настроен, жмем на кнопку «OK».

   

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



3. Как только настройки поиска установлены, следует нажать на кнопку «Найти всё» или «Найти далее», чтобы перейти к поисковой выдаче.

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