«Найти и заменить» в Excel
Процедура поиска и замены данных — одна из самых востребованных в Excel. Базовая процедура позволяет заменять только одно значение за раз, но разными способами. Давайте рассмотрим, как с ним эффективно работать.
Горячие клавиши
Сочетания клавиш ниже значительно ускорят вашу работу с инструментом:
- Для запуска диалога поиска — Ctrl + F
- Для запуска окна поиска и замены — Ctrl + H
- Выбрать все найденные ячейки (после нажатия кнопки «найти все» — Ctrl + A
- Чтобы очистить все найденные ячейки — Ctrl + Del
- Чтобы вставить одни и те же данные во все найденные ячейки — Вставить текст, Ctrl + Enter
Смотрите примеры gif: здесь мы ищем ячейки с дальнейшими модификациями. В отличие от замены, изменение найденных ячеек позволяет быстро изменить все содержимое.
Найдите все пустые ячейки в диапазоне и заполните их нулями или значением с помощью горячих клавиш
Процедура «Найти и заменить» не работает
Я сам однажды неоднократно впадал в ступор в подобных ситуациях. Я уверен, что вы сами видите, что желаемый шаблон есть в данных, но Excel при выполнении процедуры поиска сообщает:
Я не нашел ничего по вашему запросу
или в случае замены:
Ничего на замену не нашли
Что ж, совет по нажатию кнопки «Параметры» в обоих этих сообщениях действительно полезен. Вероятно, там активен флажок «Сравнить регистр» или «Вся ячейка», что не позволяет Excel найти то, что вы ищете. Excel сохраняет конфигурацию последнего поиска.
Статус параметров «С учетом регистра» и «Вся ячейка» отображается после нажатия кнопки «Параметры».
Подстановочные знаки, или как найти «звездочку»
В официальной сухой справке Excel написано, что символы подстановки «*» и «?» может быть использован. Что означает несколько символов, включая их отсутствие, и любой один символ. И это можно использовать для соответствующих исследовательских процедур.
В справке не сказано, что в сочетании с опцией «целая ячейка» эти символы позволяют, не прибегая к расширенному фильтру и процедуре поиска группы ячеек:
- Найти ячейки, заканчивающиеся определенным символом, словом или текстом
- Аналогичным образом найдите ячейки, которые начинаются с определенного символа, слова или текста
- Найдите непустые ячейки
В приведенном ниже примере мы находим все двузначные числа, затем числа, заканчивающиеся и начинающиеся на 7, и, наконец, все непустые ячейки. Напомню, комбинация горячих клавиш Ctrl + A помогает выделить все результаты поиска
Так как же найти звездочку?
Фактически, я забыл. Чтобы найти «звездочку», вам нужно поставить перед ней знак ~ (тильда) в поле поиска, которое обычно находится под клавишей Esc. Это позволяет избежать «звездочки», например вопросительного знака, и не восприниматься как служебный символ.
Замена нескольких значений на несколько
Массовая замена в Excel — довольно распространенная необходимость. Чаще всего необходимо массово и одновременно быстро заменить несколько символов, слов и так далее на другие. В то же время в стандартной функциональности Excel на данный момент нет простого инструмента.
Однако, если это действительно необходимо, любую проблему можно решить. В зависимости от того, что вы хотите заменить, могут помочь комбинации функций, регулярные выражения и, в более сложных случаях, надстройка! SEMTools.
Эта задача сложнее, чем замена на ценность. Как ни странно, функция REPLACE здесь не подходит — она требует явного указания позиции заменяемого текста. Но может помочь функция «ЗАМЕНИТЬ».
Массовая замена с помощью функции «ПОДСТАВИТЬ»
Используя несколько условий в сложной формуле, вы можете одновременно подставлять несколько значений. Excel позволяет использовать до 64 уровней вложенности — свобода действий высока. Например, вот как можно перевести кириллицу на латынь:
В то же время, если вы используете пробел в качестве заменяемого фрагмента, вы можете использовать функцию для удаления нескольких символов — посмотрите, как таким образом удалить числа из ячейки.
Но у решения есть и недостатки:
- Функция REPLACE чувствительна к регистру, поэтому при замене символа используются два варианта: прописные и строчные. Хотя в некоторых случаях, как в примере, изображенном выше, это является преимуществом.
- максимум 64 замены — пусть много, но все равно предел.
- формально процедура замены таким образом будет происходить массово и мгновенно, однако длительность написания таких формул нивелирует это преимущество. За исключением случаев, когда они будут использоваться несколько раз.
Файл-шаблон с формулой множественной замены
Вместо того, чтобы явно писать замещающие шаблоны в формуле, вы можете использовать ссылки на ячейки в формуле, значения, которые вы можете записать по своему усмотрению. Это сократит время, поскольку не требует изменения сложной формулы.
Файл доступен здесь, но вам не нужно его загружать, просто скопируйте текст формулы ниже и вставьте его в любую ячейку, кроме диапазона A1: B64. Формула заменяет значения в столбце A в ячейке C1 противоположными значениями в столбце B.
Формула в файле шаблона для множественных замен на примере транслитерации
И вот она (тройной щелчок по любой части текста = выделение всей формулы). Получите доступ к ячейке D1, сделав 64 замены в соответствии с правилами в ячейках A1-B64. В этом случае вы можете удалить значения в столбцах — это не остановит его работу.
= ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ)) (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ)) (ПОДСТАВИТЬ (ПОДСТАВИТЬ) ($ A $ 1; $ A $ B $ 2); $ B $ 2); $ B $ 2) A $ 3; $ B $ 3); $ A $ 4; $ B $ 4); $ A $ 5; $ B $ 5); $ A $ 6; $ B $ 6); $ A $ 7; $ B $ 7); $ A $ 8; $ B $ 8); $ A $ 9; $ B $ 9); $ A $ 10; $ B $ 10); $ A $ 11; $ B $ 11); $ A $ 12; $ B $ 12); $ A $ 13; $ B $ 13); $ A $ 14; $ B $ 14); 15 австралийских долларов; 15 млрд долларов); 16 австралийских долларов; $ B $ 16); 17 австралийских долларов; $ B $ 17); 18 австралийских долларов; $ B $ 18); 19 австралийских долларов; $ B $ 19); 20 австралийских долларов; $ B $ 20); 21 австралийский доллар; $ B $ 21); 22 австралийских доллара; $ B $ 22); 23 австралийских доллара; 23 миллиарда долларов); 24 австралийских доллара; 24 миллиарда долларов); 25 австралийских долларов; 25 миллиардов долларов); 26 австралийских долларов; $ 26 млрд); 27 австралийских долларов; $ 27 млрд); 28 австралийских долларов; 28 долларов США); 29 австралийских долларов; 29 бразильских долларов); 30 австралийских долларов; 30 млрд долларов); 31 австралийский доллар; $ 31 млрд); 32 австралийских доллара; $ 32 млрд); 33 австралийских доллара; $ 33 млрд); 34 австралийских доллара; $ 34 млрд); 35 австралийских долларов; $ 35 млрд); 36 австралийских долларов; $ 36 млрд.); 37 австралийских долларов; $ 37 млрд); 38 австралийских долларов; $ 38 млрд); 39 австралийских долларов; $ 39 млрд); 40 австралийских долларов; 40 бразильских долларов); 41 австралийский доллар; 41 доллар США); 42 австралийских доллара; $ 42 млрд); 43 австралийских доллара; $ 43 млрд); 44 австралийских доллара; 44 доллара США); 45 австралийских долларов; 45 долларов США); 46 австралийских долларов; 46 бразильских долларов); 47 австралийских долларов; $ B $ 47); 48 австралийских долларов; $ B $ 48); 49 австралийских долларов; 49 долларов США); 50 австралийских долларов; 50 бразильских долларов); 51 австралийский доллар; 51 доллар США); 52 австралийских доллара; $ 52 млрд); 53 австралийских доллара; $ 53 млрд); 54 австралийских доллара; 54 доллара США); 55 австралийских долларов; 55 долларов США); 56 австралийских долларов; 56 долларов США); 57 австралийских долларов; 57 долларов США); 58 австралийских долларов; 58 долларов США); 59 австралийских долларов; $ B $ 59); 60 австралийских долларов; 60 бразильских долларов); 61 австралийский доллар; 61 доллар США); 62 австралийских доллара; 62 бразильских доллара); 63 австралийских доллара; 63 млрд долларов); 64 австралийских доллара; 64 доллара США)
Заменить несколько значений на одно
С помощью функции «ПОДСТАВИТЬ«
При замене нескольких значений одним и тем же механизм формул, основанный на нескольких уровнях вложенности, не будет отличаться от замены нескольких значений разными. Просто третий аргумент (что заменить) будет одинаковым на всех уровнях вложенности. Кстати, если оставить его пустым (кавычки без символов между ними), некоторые символы будут удалены. Например, чтобы удалить числа из ячейки, заменив их пустыми:
= ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ) (ПОДСТАВИТЬ) (ПОДСТАВИТЬ) (ПОДСТАВИТЬ) (ПОДСТАВИТЬ (A1; «1»; «»); «2»; «»); «3»; «3» ; «4»; «»); «5»; «»); «6»; «»); «7»; «»); «8»; «»); «9»; «»); «0»;»»)
С помощью регулярных выражений
Важно: регулярные выражения поставляются с Excel не сразу, но следующие формулы доступны бесплатно при установке надстройки
Регулярные выражения (RegEx) — наиболее удобное решение, когда вам нужно заменить несколько символов одним. Все эти несколько символов обычным образом, без разделителей, необходимо перечислить в квадратных скобках. Примеры формул:
= regexreplace (A1; «\ d»; «#») = regexreplace (A1; «\ w»; «#») = regexreplace (A1; «a-zA-Z»; «#») = regexreplace (A1; «\ S»; «_»)
Первый заменяет все цифры символом «#», второй — все английские буквы, а третий — все кириллические символы в верхнем и нижнем регистре. Четвертый заменяет любые пробелы, включая табуляции и переводы строк, подчеркивания.
Массовая замена символов регулярными выражениями при установленной надстройке! SEMTools
Если вам нужно заменить не символы, а несколько значений, которые, в свою очередь, состоят из нескольких букв, цифр или знаков, синтаксис уже предполагает использование скобок и вертикальной черты «» в качестве разделителя.
Массовая замена в !SEMTools
Надстройка для Excel! SEMTools позволяет в пару кликов делать замены на всех уровнях:
- символы и их комбинации
- модели регулярных выражений
- слова!
- целые клетки (несколько аналогично ВПР)
Массовая замена меню «Инструменты»
При этом процедуры изменяют исходный интервал, экономя время. Все, что вам нужно сделать, это сначала выбрать его, определить задачу, вызвать необходимую процедуру и выбрать 2 столбца для соответствия замененным и переопределенным значениям (предполагается, что если вы знаете, что изменить, то такие списки есть).
Пример: замена символов по вхождению
Аналог обычной процедуры подстановки, без учета регистра заменяемых символов, по вхождению. С одним отличием: подстановка здесь огромная, и вы можете выбрать столько строк с парами значений подстановки-подстановки, сколько захотите.
Ниже приведен пример с одиночными символами, но шаблоны могут быть любыми в зависимости от активности.
Массовая подстановка символов по вхождению на примере Leet Language (некоторые английские буквы заменены на аналогичные числа)
Пример: замена списка слов на другой список слов
В этом примере замена одного списка слов другим списком, в данном случае тем же словом. Здесь проблема набора разнородных предложений решается заменой слов, содержащих латинский алфавит и числа, на слово. Кроме того, после этого вы сможете вычислить уникальные значения в столбце, чтобы определить наиболее популярные комбинации.
Замена списка слов другим списком в! SEMИнструменты
Начиная с версии! SEMTools 9.18.18, появилась опция: при замене списка слов игнорировать знаки препинания в исходных предложениях и регистр слов теперь сохраняется:
Инструменты расположены в группе макросов «ИЗМЕНИТЬ» в отдельном меню и продублированы для удобства в меню «Изменить символы», «Изменить слова» и «Изменить ячейки».