Поиск значений в таблице «Excel» . Макрос.
Поиск в Эксель
Далее описаны несколько вариантов поиска и фильтрации данных в таблице «Эксель».
Классический поиск «MS Office».
Условное форматирование (выделение нужных ячеек цветом)
Настройка фильтров по одному или нескольким значениям.
Фрагмент макроса для перебора ячеек в диапазоне и поиска нужного значения.
1) Классический поиск (обыкновенный).
Вызвать панель (меню) поиска можно сочетанием горячих клавиш ctrl+F. (Легко запомнить: F- Found).
Окно поиска состоит из поля, в которое вводится искомый фрагмент текста или искомое число, вкладки с дополнительными настройками («Параметры») и кнопки «Найти».
Классический поиск в Excel
В параметрах поиска можно указать, где искать текст, искать ли слово в ячейке целиком или вхождение слова в предложения, учитывать ли регистр или нет.
Условное форматирование для искомых ячеек.
2) Еще один вариант поиска заключается в выделении всех ячеек содержащих искомое слово каким-нибудь цветом.
Такой поиск реализуется через опцию условное форматирование.
Для форматирования ячеек следует выделить диапазон ячеек, которых нужно найти слово. Далее на вкладке «Главная» нажать кнопку «Условное форматирование».
Настройка форматирования для выделения искомых слов
В меню условного форматирования выбрать «Правила выделения ячеек» … «Текст содержит…».
В открывшемся окне указать искомое слово и цвет заливки ячейки. Нажать «ОК». Все ячейки, содержащие нужное слово будут окрашены.
3) Третий способ поиска слов в таблице «Excel» — это использование фильтров.
Фильтр устанавливается во вкладке «Данные» или сочетанием клавиш ctrl+shift+L.
Настройка фильтра для поиска слов
Кликнув по треугольнику фильтра можно в контекстном меню выбрать пункт «Текстовые фильтры», далее «содержит…» и указать искомое слово.
После нажатия кнопки «Ок» на Экране останутся только ячейки столбца, содержащие искомое слово.
4) Способ поиска номер четыре — это макрос VBA для поиска (перебора значений).
В зависимости от назначения и условий использования макрос может иметь разные конфигурации, но основная часть цикла перебора VBA макроса приведена ниже.
Sub Poisk()
‘ ruexcel.ru макрос проверки значений (поиска)
Dim keyword As String
keyword = «Искомое слово» ‘присвоить переменной искомое слово
On Error Resume Next ‘при ошибке пропустить
For Each cell In Selection ‘для всх ячеек в выделении (выделенном диапазоне)
If cell.Value = «» Then GoTo Line1 ‘если ячейка пустая перейти на «Line1″
If InStr(StrConv(cell.Value, vbLowerCase), keyword) > 0 Then cell.Interior.Color = vbRed ‘если в ячейке содержится слово окрасить ее в красный цвет (поиск)
Как правильно искать в таблицах 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 нашел и убрал все звездочки на листе.
excel найти ячейки содержащие текст
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Допустим, вы хотите убедиться, что столбец имеет текст, а не числа. Или перхапсйоу нужно найти все заказы, соответствующие определенному продавцу. Если вы не хотите учитывать текст верхнего или нижнего регистра, есть несколько способов проверить, содержит ли ячейка.
Вы также можете использовать фильтр для поиска текста. Дополнительные сведения можно найти в разделе Фильтрация данных.
Поиск ячеек, содержащих текст
Чтобы найти ячейки, содержащие определенный текст, выполните указанные ниже действия.
Выделите диапазон ячеек, которые вы хотите найти.
Чтобы выполнить поиск на всем листе, щелкните любую ячейку.
На вкладке Главная в группе Редактирование нажмите кнопку найти _амп_и выберите пункт найти.
В поле найти введите текст (или числа), который нужно найти. Вы также можете выбрать последний поисковый запрос из раскрывающегося списка найти .
Примечание: В критериях поиска можно использовать подстановочные знаки.
Чтобы задать формат поиска, нажмите кнопку Формат и выберите нужные параметры в всплывающем окне Найти формат .
Нажмите кнопку Параметры , чтобы еще больше задать условия поиска. Например, можно найти все ячейки, содержащие данные одного типа, например формулы.
В поле внутри вы можете выбрать лист или книгу , чтобы выполнить поиск на листе или во всей книге.
Нажмите кнопку найти все или Найти далее.
Найдите все списки всех вхождений элемента, который нужно найти, и вы можете сделать ячейку активной, выбрав определенное вхождение. Вы можете отсортировать результаты поиска ” найти все “, щелкнув заголовок.
Примечание: Чтобы остановить поиск, нажмите клавишу ESC.
Проверка ячейки на наличие в ней текста
Для выполнения этой задачи используйте функцию текст .
Проверка соответствия ячейки определенному тексту
Используйте функцию Если , чтобы вернуть результаты для указанного условия.
Проверка соответствия части ячейки определенному тексту
Для выполнения этой задачи используйте функции Если, Поиски функция номер .
Примечание: Функция Поиск не учитывает регистр.
Прежде, чем ответить на вопрос, как в таблице excel найти нужное слово, нужно понять, каким будет формат вывода — мы хотим напротив каждой ячейки в диапазоне проставить статус — есть слово в ячейке, или нет, или хотим просто узнать, есть ли слово в любой из ячеек диапазона?
Чем отличается обнаружение целых слов в массиве ячеек от обнаружения простых текстовых паттернов?
В первую очередь тем, что если искать короткие слова обычным вхождением, мы можем нарваться на ситуации, когда слово находится внутри другого слова. В таких кейсах в результат фильтрации попадут ячейки, которые нам не нужны. Чтобы избежать таких ситуаций, нужна более сложная фильтрация.
Как найти слово в экселе — способы фильтрации
Excel содержит в себе 3 способа текстовой фильтрации, иными словами, фильтровать можно по 3 критериям вхождения.
Ячейка содержит, начинается с, либо заканчивается на — какой то конкретной паттерн.
В нашем случае для фильтрации целого слова, когда слово содержится в середине строки — во всех таких случаях после слова и перед будут располагаться пробелы. Поэтому мы можем задать именно такой критерий фильтрации, (пример — «* слово *») . Пробелы являются тем самым ограничителем, который позволит ячейки, в которых слово середине фразы, а фильтровать полностью и исключить ситуаций, когда оно будет входить в другие слова. «Звездочки» нужны для того, чтобы сообщить Excel, что в строке действительно есть пробелы — по умолчанию он их просто убирает перед поиском, по-видимому, воспринимая их наличие как случайность.
Критерий номер 2. — Строка заканчивается на наше слово. В этом случае она будет заканчиваться наше слово, и перед ним обязательно должен быть пробел, допустим. У нас будет рот и в конце строки не может быть слово крот. Такая строка не должна фильтроваться. Нужно чтобы перед словом рот был пробел.
И критерий номер 3 — ячейка должна начинаться на наше слово — в таком случае пробел должен быть после нашего слова, по аналогичной причине.
Проблема заключается в том, что в Excel нельзя фильтровать сразу по 3 критериям — можно только по двум. Этой ситуации есть простой лайфхак:
1. Перед каждой строкой и после нее добавляем пробел (=» «&A1&» «)
2. После этого фильтруем по полученному столбцу уже наше слово с пробелами перед и после него и специальными «звездочками» перед и после этих пробелов соответственно (пример — «* слово *»), для того, чтобы отфильтровать слова, которое обязательно содержат перед ними и после них пробел. Это и будут ячейки, содержащие наше слово.
Решение задачи с помощью формулы
Однозначной формулой, возвращающей «ИСТИНА» или «ЛОЖЬ» для проверки наличия в строке целого слова, будет:
=ПОИСК(» «&B1&» «;» «&A1&» «)>0
Поиск целых слов в ячейках в SEMTools
Пожалуй, самое быстрое решение. Алгоритм простой — выделяем диапазон, жмем макрос, вводим слово, жмем «ОК».
Функция ЕСЛИ СОДЕРЖИТ
Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ» , чтобы применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусок слова , или отрицание, или часть наименования контрагента, особенно при нестандартном заполнении реестров вручную.
Такой функционал возможно получить с помощью сочетания двух обычных стандартных функций – ЕСЛИ и СЧЁТЕСЛИ .
Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)
У нас есть множество строк с документами Реализации и Возвратов .
Все документы имеют свое наименование за счет уникального номера .
Нам необходимо сделать признак « Только реализация » напротив документов продажи, для того, чтобы в дальнейшем включить этот признак в сводную таблицу и исключить возвраты для оценки эффективности деятельности отдела продаж.
Выражение должно быть универсальным , для того, чтобы обрабатывать новые добавляемые данные .
Для того, чтобы это сделать, необходимо:
-
Начинаем с ввода функции ЕСЛИ (вводим «=» , набираем наименование ЕСЛИ , выбираем его из выпадающего списка, нажимаем fx в строке формул).
В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ() , выделяем его и нажимаем 2 раза fx.
Далее в открывшемся окне аргументов функции СЧЁТЕСЛИ в поле «Критерий» вводим кусок искомого наименования *реализ* , добавляя в начале и в конце символ * .
Такая запись даст возможность не думать о том, с какой стороны написано слово реализация (до или после номера документа), а также даст возможность включить в расчет сокращенные слова «реализ.» и «реализац.»
Теперь мы можем работать и сводить данные только по документам реализации исключая возвраты . При дополнении таблицы новыми данными, остается только протягивать строку с нашим выражением и обновлять сводную таблицу.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее “перевести”)