Функция ПОИСК (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” находится на третьей позиции.
Допустим ваш отчет содержит таблицу с большим количеством данных на множество столбцов. Проводить визуальный анализ таких таблиц крайне сложно. А одним из заданий по работе с отчетом является – анализ данных относительно заголовков строк и столбцов касающихся определенного месяца. На первый взгляд это весьма простое задание, но его нельзя решить, используя одну стандартную функцию. Да, конечно можно воспользоваться инструментом: «ГЛАВНАЯ»-«Редактирование»-«Найти» CTRL+F, чтобы вызвать окно поиска значений на листе Excel. Или же создать для таблицы правило условного форматирования. Но тогда нельзя будет выполнить дальнейших вычислений с полученными результатами. Поэтому необходимо создать и правильно применить соответствующую формулу.
Поиск значения в массиве Excel
Схема решения задания выглядит примерно таким образом:
- в ячейку B1 мы будем вводить интересующие нас данные;
- в ячейке B2 будет отображается заголовок столбца, который содержит значение ячейки B1
- в ячейке B3 будет отображается название строки, которая содержит значение ячейки B1.
Фактически необходимо выполнить поиск координат в Excel. Для чего это нужно? Достаточно часто нам нужно получить координаты таблицы по значению. Немного напоминает обратный анализ матрицы. Конкретный пример в двух словах выглядит примерно так. Поставленная цель в цифрах является исходным значением, нужно определить кто и когда наиболее приближен к этой цели. Для примера используем простую матрицу данных с отчетом по количеству проданных товаров за три квартала, как показано ниже на рисунке. Важно, чтобы все числовые показатели совпадали. Если нет желания вручную создавать и заполнять таблицу Excel с чистого листа, то в конце статьи можно скачать уже с готовым примером.
Последовательно рассмотрим варианты решения разной сложности, а в конце статьи – финальный результат.
Поиск значения в столбце Excel
Сначала научимся получать заголовки столбцов таблицы по значению. Для этого выполните следующие действия:
- В ячейку B1 введите значение взятое из таблицы 5277 и выделите ее фон синим цветом для читабельности поля ввода (далее будем вводить в ячейку B1 другие числа, чтобы экспериментировать с новыми значениями).
- В ячейку C2 вводим формулу для получения заголовка столбца таблицы который содержит это значение:
- После ввода формулы для подтверждения нажимаем комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно в строке формул по краям появятся фигурные скобки { }.
Поиск значения в строке Excel
Теперь получим номер строки для этого же значения (5277). Для этого в ячейку C3 введите следующую формулу:
После ввода формулы для подтверждения снова нажимаем комбинацию клавиш CTRL+SHIFT+Enter и получаем результат:
Формула вернула номер 9 – нашла заголовок строки листа по соответствующему значению таблицы. В результате мы имеем полный адрес значения D9.
Как получить заголовок столбца и название строки таблицы
Теперь научимся получать по значению координаты не целого листа, а текущей таблицы. Одним словом, нам нужно найти по значению 5277 вместо D9 получить заголовки:
- для столбца таблицы – Март;
- для строки – Товар4.
Чтобы решить данную задачу будем использовать формулу с уже полученными значениями в ячейках C2 и C3. Для этого делаем так:
В результате получены внутренние координаты таблицы по значению – Март; Товар 4:
Поиск одинаковых значений в диапазоне Excel
Чтобы проконтролировать наличие дубликатов среди значений таблицы создадим формулу, которая сможет информировать нас о наличии дубликатов и подсчитывать их количество. Для этого в ячейку E2 вводим формулу:
Более того для диапазона табличной части создадим правило условного форматирования:
Как видно при наличии дубликатов формула для заголовков берет заголовок с первого дубликата по горизонтали (с лева на право). А формула для получения названия (номера) строки берет номер с первого дубликата по вертикали (сверху вниз). Для исправления данного решения есть 2 пути:
В данном случаи изменяем формулы либо одну либо другую, но не две сразу. Стоит напомнить о том, что в ячейке С3 должна оставаться старая формула:
Здесь правильно отображаются координаты первого дубликата по вертикали (с верха в низ) – I7 для листа и Август; Товар2 для таблицы. Оставим такой вариант для следующего завершающего примера.
Поиск ближайшего значения в диапазоне Excel
Данная таблица все еще не совершенна. Ведь при анализе нужно точно знать все ее значения. Если введенное число в ячейку B1 формула не находит в таблице, тогда возвращается ошибка – #ЗНАЧ! Идеально было-бы чтобы формула при отсутствии в таблице исходного числа сама подбирала ближайшее значение, которое содержит таблица. Чтобы создать такую программу для анализа таблиц в ячейку F1 введите новую формулу:
После чего следует во всех остальных формулах изменить ссылку вместо B1 должно быть F1! Так же нужно изменить ссылку в условном форматировании. Выберите: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»-«Изменить правило». И здесь в параметрах укажите F1 вместо B1 . Чтобы проверить работу программы, введите в ячейку B1 число которого нет в таблице, например: 8000. Это приведет к завершающему результату:
Теперь можно вводить любое исходное значение, а программа сама подберет ближайшее число, которое содержит таблица. После чего выводит заголовок столбца и название строки для текущего значения. Например, если ввести число 5000 получаем новый результат:
Наша программа в Excel нашла наиболее близкое значение 4965 для исходного – 5000. Такая программа может пригодится для автоматического решения разных аналитических задач при бизнес-планировании, постановки целей, поиска рационального решения и т.п. А полученные строки и столбцы позволяют дальше расширять вычислительные возможности такого рода отчетов с помощью новых формул Excel.
Зачастую, при работе с данными в 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 рано или поздно придется выполнить поиск переделенного значения. Простой поиск выполняется с помощью команды: «Найти и выделить», которая находится в группе «Редактирование» на вкладке «Главная».
Смотрите также видеоверсию статьи .
Вызвать диалоговое окно поиска можно также и с помощью горячего сочетания клавиш «Ctrl+F «.
На самом деле, диалоговое окно называется «Найти и заменить «, поскольку оно решает эти две смежные задачи и если его вызвать через горячее сочетание «Ctrl+H «, то будет все тоже самое, только окно откроется с активированной вкладкой «Заменить».
Здесь, на первый взгляд, все просто: ввел слово и нашел необходимую ячейку на листе Excel. Конечно, нельзя исключать и такой вариант развития, когда поиск будет выполнен быстро, однако, более вероятна ситуация, когда вы вводите искомое значение, а табличный процессор не ищет его, или нужно выполнить нестрогий поиск, когда искомый текст помнится приблизительно, что кстати, часто бывает при поиске людей по фамилии.
В этом материале и рассмотрим нюансы и секреты окна поиска, которые большинству пользователей не знакомы.
Некоторые настройки, которые могут помочь в решении поставленной задачи скрываются за кнопкой «Параметры».
Рассмотрим ситуацию, когда нужно найти сотрудника Комарова Александра Ивановича, но есть ряд факторов, из-за которых возникают сложности:
- мы не знаем, как он записан, с полной расшифровкой имени и отчества, или только инициалы;
- есть определенная неуверенность в том какая вторая буква «а» или «о»;
- не понятно, как точно записан сотрудник сначала фамилия, потом имя и отчество или наоборот.
Комаров Александр Иванович может быть записан как: А. Комаров, Комаров Александр, Ка маров Александр И. и т.д. вариантом достаточно.
Чтобы выполнить нестрогий поиск, следует воспользоваться символами-заменителями их еще называют джокерными символами. Окно поиска в Excel поддерживает работу с двумя такими символами: «*» и «?»:
- «*» соответствует любому количеству символов;
- «?» соответствует любому отдельно взятому символу.
Здесь сразу же возникает вопрос, а что делать если необходимо выполнить поиск вопросительного знака или астериск (известный как знак умножения)? Все просто, достаточно перед искомыми знаками просто поставить тильду «~», соответственно, если необходимо выполнить поиск тильды, тогда необходимо поставить две тильды.
Итак, для осуществления неточного поиска по вышеупомянутой фамилии, следует записать в окно поиска «К?маров» и нажать на кнопку «Найти все», в этом случае в окне поиска будет выведены все записи, удовлетворяющие поставленному условию, но только, если не отмечен параметр «Ячейка целиком».
Параметр «Ячейка целиком» означает поиск строго текстовой строки, введенной в поле найти и ничего более, другими словами, если кроме фамилии есть еще инициалы, то запись найдена не будет.
В тоже время, если немного перестроить запрос «*К?маров*», тогда записи будут найдены вне зависимости от активности параметра «Ячейка целиком».
При осуществлении поиска в Excel нужно помнить несколько важных правил:
- Поиск выполняется в выделенной области, а если нет выделения, тогда на всем листе. Поэтому не стоит удивляться, если вы случайно выделите пару пустых ячеек, запустите окно поиска и не сможете ничего найти.
- При поиске не учитывается форматирование, поэтому, никаких знаков обозначения валюты добавлять не стоит.
- При работе с датами, лучше выполнять поиск их в формате по умолчанию для конкретной системы, в этом случае, в Excel будут найдены все даты, удовлетворяющие условию. Например, если в системе используется формат д/м/г, то поисковый запрос */12/2015 выведет все даты за декабрь 2015 года, независимо от того, как они отформатированы (28.12.2015, 28/12/2015, или 28 декабря 2015 и т.д.).
В документах Microsoft Excel, которые состоят из большого количества полей, часто требуется найти определенные данные, наименование строки, и т.д. Очень неудобно, когда приходится просматривать огромное количество строк, чтобы найти нужное слово или выражение. Сэкономить время и нервы поможет встроенный поиск Microsoft Excel. Давайте разберемся, как он работает, и как им пользоваться.
Поисковая функция в программе Microsoft Excel предлагает возможность найти нужные текстовые или числовые значения через окно «Найти и заменить». Кроме того, в приложении имеется возможность расширенного поиска данных.
Способ 1: простой поиск
Простой поиск данных в программе Excel позволяет найти все ячейки, в которых содержится введенный в поисковое окно набор символов (буквы, цифры, слова, и т.д.) без учета регистра.
Способ 2: поиск по указанному интервалу ячеек
Если у вас довольно масштабная таблица, то в таком случае не всегда удобно производить поиск по всему листу, ведь в поисковой выдаче может оказаться огромное количество результатов, которые в конкретном случае не нужны. Существует способ ограничить поисковое пространство только определенным диапазоном ячеек.
Способ 3: Расширенный поиск
Как уже говорилось выше, при обычном поиске в результаты выдачи попадают абсолютно все ячейки, содержащие последовательный набор поисковых символов в любом виде не зависимо от регистра.
К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.
Но, как отсечь такие, и другие заведомо неприемлемые результаты выдачи поиска? Именно для этих целей существует расширенный поиск Excel.
- После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры» .
- В окне появляется целый ряд дополнительных инструментов для управления поиском. По умолчанию все эти инструменты находятся в состоянии, как при обычном поиске, но при необходимости можно выполнить корректировку.
По умолчанию, функции «Учитывать регистр» и «Ячейки целиком» отключены, но, если мы поставим галочки около соответствующих пунктов, то в таком случае, при формировании результата будет учитываться введенный регистр, и точное совпадение. Если вы введете слово с маленькой буквы, то в поисковую выдачу, ячейки содержащие написание этого слова с большой буквы, как это было бы по умолчанию, уже не попадут. Кроме того, если включена функция «Ячейки целиком» , то в выдачу будут добавляться только элементы, содержащие точное наименование. Например, если вы зададите поисковый запрос «Николаев», то ячейки, содержащие текст «Николаев А. Д.», в выдачу уже добавлены не будут.
По умолчанию, поиск производится только на активном листе Excel. Но, если параметр «Искать» вы переведете в позицию «В книге» , то поиск будет производиться по всем листам открытого файла.
В параметре «Просматривать» можно изменить направление поиска. По умолчанию, как уже говорилось выше, поиск ведется по порядку построчно. Переставив переключатель в позицию «По столбцам» , можно задать порядок формирования результатов выдачи, начиная с первого столбца.
В графе «Область поиска» определяется, среди каких конкретно элементов производится поиск. По умолчанию, это формулы, то есть те данные, которые при клике по ячейке отображаются в строке формул. Это может быть слово, число или ссылка на ячейку. При этом, программа, выполняя поиск, видит только ссылку, а не результат. Об этом эффекте велась речь выше. Для того, чтобы производить поиск именно по результатам, по тем данным, которые отображаются в ячейке, а не в строке формул, нужно переставить переключатель из позиции «Формулы» в позицию «Значения» . Кроме того, существует возможность поиска по примечаниям. В этом случае, переключатель переставляем в позицию «Примечания» .
Ещё более точно поиск можно задать, нажав на кнопку «Формат» .
При этом открывается окно формата ячеек. Тут можно установить формат ячеек, которые будут участвовать в поиске. Можно устанавливать ограничения по числовому формату, по выравниванию, шрифту, границе, заливке и защите, по одному из этих параметров, или комбинируя их вместе.
Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…» .
После этого, появляется инструмент в виде пипетки. С помощью него можно выделить ту ячейку, формат которой вы собираетесь использовать.
После того, как формат поиска настроен, жмем на кнопку «OK» .
Бывают случаи, когда нужно произвести поиск не по конкретному словосочетанию, а найти ячейки, в которых находятся поисковые слова в любом порядке, даже, если их разделяют другие слова и символы. Тогда данные слова нужно выделить с обеих сторон знаком «*». Теперь в поисковой выдаче будут отображены все ячейки, в которых находятся данные слова в любом порядке.
- Как только настройки поиска установлены, следует нажать на кнопку «Найти всё» или «Найти далее» , чтобы перейти к поисковой выдаче.
Как видим, программа Excel представляет собой довольно простой, но вместе с тем очень функциональный набор инструментов поиска. Для того, чтобы произвести простейший писк, достаточно вызвать поисковое окно, ввести в него запрос, и нажать на кнопку. Но, в то же время, существует возможность настройки индивидуального поиска с большим количеством различных параметров и дополнительных настроек.
