Массовый поиск в excel
В Excel функция «Найти и заменить» широко и удобно используется. Однако функция «Найти и заменить» может искать или находить значение только за один раз. Иногда, если вы хотите найти два значения одновременно, как это сделать? Теперь я расскажу вам, как быстро находить в Excel более одного значения за раз.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Искать сразу несколько значений с помощью Kutools for Excel
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Если вы установили Kutools for Excel - полезная и удобная надстройка Excel, вы можете использовать ее Выбрать определенные ячейки функция, позволяющая быстро находить несколько значений одновременно.
1. Выберите диапазон, в котором нужно искать значения, и щелкните Kutools > Выберите Инструменты > Выбрать определенные ячейки. Смотрите скриншот:
2. в Выбрать определенные ячейки диалог, сделайте следующее:
Проверьте Ячейка вариант в Тип выбора раздел;
Оба выбирают Равно в двух раскрывающихся списках и введите значения, которые вы хотите найти, в каждое текстовое поле в Конкретный тип раздел;
Проверьте Or опцию.
3. Нажмите Ok or Применить , чтобы применить функцию, и появится диалоговое окно, в котором указано количество выбранных ячеек. Смотрите скриншот:
4. Нажмите OK > Ok чтобы закрыть диалоги. Теперь вы можете видеть, что значения, которые вы хотите сразу найти, выбраны.
Ноты:
1. Если вы хотите искать значения на всем листе, вы можете щелкнуть по нему, чтобы выбрать весь лист:
2. Выбрать определенные ячейки Утилита может искать или находить только два разных значения одновременно.
Процедура поиска и замены данных — одна из самых востребованных в Excel. Базовая процедура позволяет заменить за один заход только одно значение, но зато множеством способов. Рассмотрим, как эффективно работать с ней.
Горячие клавиши
Сочетания клавиш ниже заметно ускорят работу с инструментом:
- Для запуска диалогового окна поиска — Ctrl + F
- Для запуска окна поиска и замены — Ctrl + H
- Для выделения всех найденных ячеек (после нажатия кнопки «найти все» — Ctrl + A
- Для очистки всех найденных ячеек — Ctrl + Delete
- Для ввода одних и тех же данных во все найденные ячейки — Ввод текста, Ctrl + Enter
Смотрите gif-примеры: здесь мы производим поиск ячеек с дальнейшим их редактированием. В отличие от замены, редактирование найденных ячеек позволяет быстро менять их содержимое целиком.
Находим все пустые ячейки в диапазоне и заполняем их нулями или одним значением с помощью горячих клавишПроцедура «Найти и заменить» не работает
Я сам когда-то неоднократно впадал в ступор в подобных ситуациях. Уверен и видишь своими глазами, что искомый паттерн в данных есть, но Excel при выполнении процедуры поиска сообщает:
Не удалось ничего найти по вашему запросу
Мы не нашли ничего, что нужно было заменить
Статус опций «Учитывать регистр» и «Ячейка целиком» виден после нажатия кнопки «Параметры».
Подстановочные знаки, или как найти «звездочку»
Сухая официальная справка по Excel сообщает, что можно использовать подстановочные символы «*» и «?». Что они означают несколько символов, включая их отсутствие, и один любой символ. И что их можно использовать для соответствующих процедур поиска.
Чего не говорит справка — это того, что в комбинации с опцией «ячейка целиком» эти символы позволяют, не прибегая к помощи расширенного фильтра и процедуры поиска группы ячеек:
- Находить ячейки, заканчивающиеся на определенный символ, слово или текст
- Аналогично, находить ячейки, начинающиеся с определенного символа, слова или текста
- Находить непустые ячейки
На примере ниже мы находим все двузначные числа, затем числа, заканчивающиеся и начинающиеся на 7, и, наконец, все непустые ячейки. Напомню, выделить все результаты поиска помогает горячее сочетание клавиш Ctrl + A
Так а как найти звездочку?
Действительно, забыл. Чтобы найти «звездочку», нужно в окошке поиска ставить перед ней знак
(тильда), находится обычно под клавишей Esc . Это позволяет экранировать «звездочку», как и вопросительный знак, и не воспринимать их как служебные символы.
Замена нескольких значений на несколько
Массовая замена в Excel — довольно частая потребность. Очень часто нужно массово и при этом быстро заменить несколько символов, слов и т.д. на другие. При этом на текущий момент простого инструмента в стандартном функционале Excel нет.
Тем не менее, если очень нужно, любую задачу можно решить. В зависимости от того, на что вы хотите заменить, могут помочь комбинации функций, регулярные выражения, а в самых сложных случаях — надстройка !SEMTools.
Эта задача более сложная, чем замена на одно значение. Как ни странно, функция «ЗАМЕНИТЬ» здесь не подходит — она требует явного указания позиции заменяемого текста. Зато может помочь функция «ПОДСТАВИТЬ«.
Массовая замена с помощью функции «ПОДСТАВИТЬ»
Используя несколько условий в сложной формуле, можно производить одновременную замену нескольких значений. Excel позволяет использовать до 64 уровней вложенности — свобода действий высока. Например, вот так можно перевести кириллицу в латиницу:
При этом, если использовать в качестве подставляемого фрагмента пустоту, можно использовать функцию для удаления нескольких символов — смотрите как удалить цифры из ячейки этим способом.
Но у решения есть и свои недостатки:
- Функция ПОДСТАВИТЬ регистрозависимая, что заставляет при замене одного символа использовать два его варианта — в верхнем и нижнем регистрах. Хотя, в некоторых случаях, как пример на картинке выше, это и преимущество.
- максимум 64 замены — хоть и много, но все же ограничение.
- формально процедура замены таким способом будет происходить массово и моментально, однако, длительность написания таких формул сводит на нет это преимущество. За исключением случаев, когда они будут использоваться многократно.
Файл-шаблон с формулой множественной замены
Вместо явного прописывания заменяемых паттернов в формуле, можно использовать внутри формулы ссылки на ячейки, значения в которых можно прописывать на свое усмотрение. Это сократит время, т.к. не требует редактирования сложной формулы.
Файл доступен по ссылке, но можно и не скачивать его, а просто скопировать текст формулы ниже и вставить ее в любую ячейку, кроме диапазона A1:B64. Формула заменяет в ячейке C1 значения в столбце A стоящими напротив в столбце B.
А вот и она сама (тройной клик по любой части текста = выделить всю формулу). Обращается к ячейке D1, делая 64 замены по правилам, указанным в ячейках A1-B64. При этом в столбцах можно удалять значения — это не нарушит ее работу.
Заменить несколько значений на одно
С помощью функции «ПОДСТАВИТЬ«
При замене нескольких значений на одно и то же механика работы формул на основе нескольких уровней вложенности не будет отличаться от замены нескольких на несколько. Просто третий аргумент (на что заменить) на всех уровнях вложенности будет один и тот же. Кстати, если оставить его пустым (кавычки без символов между ними), то это позволит удалить определенные символы. Пример — удалить цифры из ячейки путем замены на пустоту:
С помощью регулярных выражений
Важно: регулярные выражения не поставляются в Excel «из коробки», но формулы ниже доступны бесплатно, если установить надстройку !SEMTools
Регулярные выражения (RegEx, регулярки) — наиболее удобное решение, когда нужно заменить несколько символов на один. Все эти несколько символов обычным способом безо всяких разделителей нужно перечислить внутри квадратных скобок. Примеры формул:
Массовая замена символов регулярными выражениями при установленной надстройке !SEMToolsЕсли же нужно заменять не символы, а несколько значений, состоящих в свою очередь из нескольких букв, цифр или знаков — синтаксис предполагает уже использование круглых скобок и вертикальной черты «|» в качестве разделителя.
Массовая замена в !SEMTools
Надстройка для Excel !SEMTools позволяет в пару кликов производить замены на всех уровнях:
- символов и их сочетаний
- паттернов регулярных выражений
- слов!
- целых ячеек (В некоторой степени аналог ВПР)
При этом процедуры изменяют исходный диапазон, что экономит время. Все что нужно — предварительно выделить его, определиться с задачей, вызвать нужную процедуру и выделить 2 столбца сопоставления заменяемых и замещающих значений (предполагается, что если вы знаете, что на что менять, то и такие списки есть).
Пример: замена символов по вхождению
Аналог обычной процедуры замены без учета регистра заменяемых символов, по вхождению. С одним отличием — здесь замена массовая и можно выбрать сколько угодно строк с парами заменяемое-заменяющее значение.
Ниже пример с единичными символами, но паттерны могут быть какими угодно в зависимости от вашей задачи.
Массовая замена символов по вхождению на примере Leet Language (некоторые английские буквы заменяются на похожие цифры)Пример: замена списка слов на другой список слов
На этом примере — замена списка слов на другой список, в данном случае на одно и то же слово. Здесь решается задача типизации разнородных фраз путем замены слов, содержащих латиницу и цифры, на одно слово. Далее после этой операции можно будет посчитать уникальные значения в столбце, чтобы выявить наиболее популярные сочетания.
С версии !SEMTools 9.18.18 появилась опция — при замене списка слов не учитывать пунктуацию в исходных предложениях, а регистр слов теперь сохраняется:
Инструменты находятся в группе макросов «ИЗМЕНИТЬ» в отдельном меню и для удобства продублированы в меню «Изменить символы«, «Изменить слова» и «Изменить ячейки«.
За исключением использования функции «Найти» в 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?
Найти и заменить сразу несколько значений кодом VBA
Если вы устали снова и снова находить и заменять значения, следующий код VBA может помочь вам заменить сразу несколько значений необходимыми текстами.
1. Создайте условия, которые вы хотите использовать, которые содержат исходные значения и новые значения. Смотрите скриншот:
2. Затем удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.
3. Нажмите Вставить > Модульи вставьте следующий код в окно модуля.
Код VBA: найти и заменить сразу несколько значений
4, Затем нажмите F5 Чтобы запустить этот код, во всплывающем окне подсказки укажите диапазон данных, в котором вы хотите заменить значения новыми значениями.
5. Нажмите OK, и отображается другое окно подсказки, напоминающее вам о выборе критериев, созданных на шаге 1. См. снимок экрана:
6. Затем нажмите OK, все конкретные значения были заменены новыми значениями по мере необходимости.
Читайте также: