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

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

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

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

У вас должен получиться вот такой выпадающий список:

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

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

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

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

Интервальный_просмотр - ЛОЖЬ - поскольку нам нужно точное совпадение данных с заданным в поиске.

Нажимаем кнопку ОК.

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

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

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

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

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

Затем, устанавливаем курсор в свободную ячейку, с помощью вкладки Формулы или кнопки Вставить функцию на Панели формул выбираем функцию ПОИСКПОЗ.

Искомое_значение - указываем ячейку, которая содержит поле со списком, созданный нами в самом начале.

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

Тип_сопоставления - указываем "0" (ноль) - это позволит функции находить первый аргумент, который соответствует искомому значению.

Нажимаем кнопку ОК.

Функция ПОИСКПОЗ указывает в какой строчке содержится искомое значение. Став курсором на ячейку, которая содержит формулу, в строке формул вы увидите синтаксис функции, выделяем его до знака "=" и копируем (нажав Ctrl+C) и нажимаем Enter, чтобы выйти из ячейки.

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

В диалоговом окне Аргументы функции указываем следующие значения:

Массив - выделяем весь массив данных (без шапки таблицы), которые содержит как искомые значения, так и данные, которые в последствии мы хотим что бы отображались. В нашем примере это столбцы, которые содержат марку телефона и цену.

Номер_сроки - здесь мы вставляем функцию ПОИСКПОЗ путем нажатия клавиш Ctrl+V.

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

Нажимаем кнопку ОК.

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

Примеры использования функции ПОИСК в Excel

Для нахождения позиции текстовой строки в другой аналогичной применяют ПОИСК и ПОИСКБ. Расчет ведется с первого символа анализируемой ячейки. Так, если задать функцию ПОИСК “л” для слова «апельсин» мы получим значение 4, так как именно такой по счету выступает заданная буква в текстовом выражении.

Функция ПОИСК работает не только для поиска позиции отдельных букв в тексте, но и для целой комбинации. Например, задав данную команду для слов «book», «notebook», мы получим значение 5, так как именно с этого по счету символа начинается искомое слово «book».

Используют функцию ПОИСК наряду с такими, как:

• НАЙТИ (осуществляет поиск с учетом регистра);
• ПСТР (возвращает текст);
• ЗАМЕНИТЬ (заменяет символы).

Важно помнить, что рассматриваемая команда ПОИСК не учитывает регистра. Если мы с помощью нее станем искать положение буквы «а» в слове «Александр», в ячейке появится выражение 1, так как это первый символ в анализируемой информации. При задании команды НАЙТИ «а» в том же отрезке текста, мы получим значение 6, так как именно 6 позицию занимает строчная «а» в слове «Александр».

Кроме того, функция ПОИСК работает не для всех языков. От команды ПОИСКБ она отличается тем, что на каждый символ отсчитывает по 1 байту, в то время как ПОИСКБ - по два.

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

ПОИСК(нужный_текст;анализируемый_текст;[начальная_позиция]).

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

1. Искомый текст. Это числовая и буквенная комбинация, позицию которой требуется найти.
2. Анализируемый текст. Это тот фрагмент текстовой информации, из которого требуется вычленить искомую букву или сочетание и вернуть позицию.
3. Начальная позиция. Данный фрагмент необязателен для ввода. Но, если вы желаете найти, к примеру, букву «а» в строке со значением «А015487.Мужская одежда», то необходимо указать в конце формулы 8, чтобы анализ этого фрагмента проводился с восьмой позиции, то есть после артикула. Если этот аргумент не указан, то он по умолчанию считается равным 1. При указании начальной позиции положение искомого фрагмента все равно будет считаться с первого символа, даже если начальные 8 были пропущены в анализе. То есть в рассматриваемом примере букве «а» в строке «А015487.Мужская одежда» будет присвоено значение 14.

При работе с аргументом «искомый_текст» можно использовать следующие подстановочные знаки.

1. Вопросительный знак (?). Он будет соответствовать любому знаку.
2. Звездочка (*). Этот символ будет соответствовать любой комбинации знаков.

Если же требуется найти подобные символы в строке, то в аргументе «искомый_текст» перед ними нужно поставить тильду (~).

Если искомый текст не был найден приложением или начальная позиция установлена меньше 0, больше общего количества присутствующих символов, в ячейке отобразиться ошибка #ЗНАЧ.

Если «искомый_текст» не найден, возвращается значение ошибки #ЗНАЧ.

Пример использования функции ПОИСК и ПСТР

Пример 1. Есть набор текстовой информации с контактными данными клиентов и их именами. Информация записана в разных форматах. Необходимо найти, с какого символа начинается номер телефона.

Введем исходные данные в таблицу:

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

ПОИСК(“, тел.”;адрес_анализируемой_ячейки).

Нажмем Enter для отображения искомой информации:

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

Пример формулы ПОИСК и ЗАМЕНИТЬ

Пример 2. Есть таблица с текстовой информацией, в которой слово «маржа» нужно заменить на «объем».

Откроем книгу Excel с обрабатываемыми данными. Пропишем формулу для поиска нужного слова «маржа»:

Теперь дополним формулу функцией ЗАМЕНИТЬ:

Чем отличается функция ПОИСК от функции НАЙТИ в Excel?

Функция ПОИСК очень схожа с функцией НАЙТИ по принципу действия. Более того у них фактически одинаковые аргументы. Только лишь названия аргументов отличаются, а по сути и типам значений – одинаковые:

Но опытный пользователь Excel знает, что отличие у этих двух функций очень существенные.

Отличие №1. Чувствительность к верхнему и нижнему регистру (большие и маленькие буквы). Функция НАЙТИ чувствительна к регистру символов. Например, есть список номенклатурных единиц с артикулом. Необходимо найти позицию маленькой буквы «о».

Теперь смотрите как ведут себя по-разному эти две функции при поиске большой буквы «О» в критериях поиска:

Отличие №2. В первом аргументе «Искомый_текст» для функции ПОИСК мы можем использовать символы подстановки для указания не точного, а приблизительного значения, которое должно содержаться в исходной текстовой строке. Вторая функция НАЙТИ не умеет использовать в работе символы подстановки масок текста: «*»; «?»; «~».

Для примера попробуем в этих же исходных строках столбца «наименования» найти приблизительный текст. Для этого укажем следующий вид критерия поиска используя символы подстановки: «н*ая».

Как видим во втором отличии функция НАЙТИ совершенно не умеет работать и распознавать спецсимволы для подстановки текста в критериях поиска при неточном совпадении в исходной строке.

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

Как найти заданные слова в строках Excel? Поиск слов, букв, цифр и символов в Excel

Простейшим способом поиска заданного слова, буквы, цифры, символа либо сочетания из них является стандартный поиск. Все параметры для поиска вводятся в диалоговом окне «Найти и заменить», которое можно вызвать из главного меню, либо при помощи сочетания горячих клавиш «Ctrl+f» (где f-первая буква английского слова find – найти). Кроме обычного поиска можно выполнять также поиск с заменой.

Использование фильтров для поиска заданного слова, буквы, цифры или символа в Excel

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

Как программно найти и удалить определенные строки в Excel, используя VBA-оператор Like?

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

Sub Udalenie_Strok_Po_Shablonu() Dim r As Long, FirstRow As Long, LastRow As Long Dim Region As Range, iRow As Range, Cell As Range Dim Shablon As String Shablon = "здесь вводится искомый текст" Set Region = ActiveSheet.UsedRange FirstRow = Region.Row LastRow = Region.Row - 1 + Region.Rows.Count For r = LastRow To FirstRow Step -1 Set iRow = Region.Rows(r - FirstRow + 1) For Each Cell In iRow.Cells If Cell Like Shablon Then Rows(r).Delete End If Next Cell Next r End Sub

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

Для решения подобных задач могут использоваться также VBA-функции Instr и Find.

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

Часто возникает вопрос « Как найти в Excel »? В Excel можно найти любую информацию: текст, часть текста, цифру, номер телефона, эл. адрес , фамилию, формулу, примечание, формат ячейки, т.д.
Найти ячейку на пересечении строки и столбца Excel – « Как найти в Excel ячейку на пересечении строки и столбца » (функция "ИНДЕКС" в Excel ).
Найти и перенести в другое место в Excel (например, в бланк) несколько данных сразу – смотрите в статье «Найти в Excel несколько данных сразу» (функция "ВПР" в Excel ).
Или найти ячейку с ссылкой в формуле Excel, чтобы заменить ссылку, смотрите « Поменять ссылки на другие листы в формулах Excel ».
Найти в Excel ячейки с примечанием - статья "Вставить примечание в Excel" .
Для быстрого поиска существует сочетание клавиш – Ctrl + F . Нажимаем клавишу Ctrl и, удерживая её, нажимаем клавишу F. Появится окно поиска.
Ещё окно поиска можно вызвать так - на закладке "Главная" нажать кнопку "Найти и выделить".
На вкладке «Найти» в ячейке «найти» пишем искомое слово (можно часть слова) и нажимаем «найти далее». Будет найдено первое такое слово. Затем нажимаете «найти далее» и поиск перейдет на второе такое слово.

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

Если поиск ничего не нашел, а вы знаете, что эти данные точно есть, то попробуйте убрать из ячеек таблицы отступ. Как убрать отступ в ячейках, смотрите в статье "Текст Excel. Формат".
Поиск числа в Excel требует небольшой настройки условий поиска - применим расширенный поиск в Excel .
Совет.
Если вы работаете с таблицей продолжительное время и вам часто надо переходить к поиску от одного слова к другому. Тогда удобнее окно поиска не закрывать каждый раз, а сдвинуть его в ту часть таблицы, где оно не будет мешать. Сдвинуть можно ниже экрана, оставив только ячейку ввода искомого слова («найти») и нажимать потом Enter.
Это диалоговое окно поиска всегда остается на экране, даже если вы переходите на другую страницу. С помощью этого окна можно производить поиск на любой странице, надо только его активизировать на открытой странице. Для этого нажать курсор на строке "найти".
Для более расширенного поиска нажмите кнопку "Параметры" и выберите нужный параметр поиска.
Например, выберем - "Значение". Тогда будет искать и числа, и номер телефона, т.д.
Если нужно найти все одинаковес слова, но в падежах (молоко, молоком, молоку, т.д.), то напишем формулу с подстановочными знаками. Смотрите об этом "Подстановочные знаки в Excel".
Функция в Excel "Найти и выделить" поможет не только найти данные, но и заменить их. Смотрите статью "Как скопировать формулу в Excel без изменения ссылок" .
Как убрать лишние пробелы, которые мешают обрабатывать данные в таблице, читайте в статье "Как удалить лишние пробелы в Excel" .
В Excel можно найти любую информацию не только функцией "Поиск" или формулами, но и функцией условного форматирования. Читайте об этом статью "Условное форматирование в Excel" .
Ещё прочитать о функции "Найти и выделить" можно в статье " Фильтр в Excel ".

Функция ПОИСК (SEARCH) в Excel используется для определения расположения текста внутри какого-либо текста и указания его точной позиции.

Что возвращает функция

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

Синтаксис

=SEARCH(find_text, within_text, ) – английская версия

=ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция]) – русская версия

Аргументы функции

• find_text (искомый_текст) – текст или текстовая строка которую вы хотите найти;
• within_text (просматриваемый_текст) – текст, внутри которого вы осуществляете поиск;
• ([начальная_позиция]) – числовое значение, обозначающее позицию, с которой вы хотите начать поиск. Если не указать этот аргумент, то функци начнет поиск с начала текста.

Дополнительная информация

• Если стартовая позиция поиска не указана, то поиск текста осуществляется сначала текста;
• Функция не чувствительна к регистру. Если вам нужна чувствительность к регистру, то используйте функцию ;
• Функцияможет обрабатывать подстановочные знаки. В Excel существует три подстановочных знака – ?, *, ~.
  • знак “?” – сопоставляет любой одиночный символ;
  • знак “*” – сопоставляет любые дополнительные символы;
  • знак “~” – используется, если нужно найти сам вопросительный знак или звездочку.
• Функция возвращает ошибку, в случае если искомый текст не найден.

Примеры использования функции ПОИСК в Excel

Пример 1. Ищем слово внутри текстовой строки (с начала)

На примере выше видно, что когда мы ищем слово “доброе” в тексте “Доброе утро”, функция возвращает значение “1”, что соответствует позиции слова “доброе” в тексте “Доброе утро”.

Так как функция не чувствительна к регистру, нет разницы каким образом мы указываем искомое слово “доброе”, будь то “ДОБРОЕ”, “Доброе”, “дОброе” и.т.д. функция вернет одно и то же значение.

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

Пример 2. Ищем слово внутри текстовой строки (с указанием стартовой позиции поиска)

Третий аргумент функцииуказывает на порядковый номер позиции внутри текста, с которой будет осуществлен поиск. На примере выше, функция возвращает значение “1” при поиске слова “доброе” в тексте “Доброе утро”, начиная свой поиск с первой позиции.

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

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

Пример 3. Поиск слова при наличии нескольких совпадений в тексте

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

Если мы укажем функции начало поиска, например, со второго символа, то результатом вычисления функции будет “8”.

Пример 4. Используем подстановочные знаки при работе функции ПОИСК в Excel

При поиске функция учитывает подстановочные знаки. На примере выше мы ищем текст “c*l”. Наличие подстановочного знака “*” в данном запросе обозначает что мы ищем любо слово, которое начинается с буквы “c” и заканчивается буквой “l”, а что между этими двумя буквами не важно. Как результат, функция возвращает значение “3”, так как в слове “Excel”, расположенном в ячейке А2 буква “c” находится на третьей позиции.