Как в Excel массово найти и заменить несколько значений на другие
Процедура поиска и замены данных — одна из самых востребованных в Excel. Базовая процедура позволяет заменить за один заход только одно значение, но зато множеством способов. Рассмотрим, как эффективно работать с ней.
Горячие клавиши
Сочетания клавиш ниже заметно ускорят работу с инструментом:
- Для запуска диалогового окна поиска — Ctrl + F
- Для запуска окна поиска и замены — Ctrl + H
- Для выделения всех найденных ячеек (после нажатия кнопки «найти все» — Ctrl + A
- Для очистки всех найденных ячеек — Ctrl + Delete
- Для ввода одних и тех же данных во все найденные ячейки — Ввод текста, Ctrl + Enter
Смотрите gif-примеры: здесь мы производим поиск ячеек с дальнейшим их редактированием. В отличие от замены, редактирование найденных ячеек позволяет быстро менять их содержимое целиком.
Находим все пустые ячейки в диапазоне и заполняем их нулями или одним значением с помощью горячих клавиш
Процедура «Найти и заменить» не работает
Я сам когда-то неоднократно впадал в ступор в подобных ситуациях. Уверен и видишь своими глазами, что искомый паттерн в данных есть, но Excel при выполнении процедуры поиска сообщает:
Не удалось ничего найти по вашему запросу
Мы не нашли ничего, что нужно было заменить
Так вот, совет нажать кнопку «Параметры» в обоих этих сообщениях действительно полезен. Там наверняка активен чекбокс «Учитывать регистр» или «Ячейка целиком», которые мешают Excel найти искомое. Excel сохраняет конфигурацию последнего поиска.
Статус опций «Учитывать регистр» и «Ячейка целиком» виден после нажатия кнопки «Параметры».
Подстановочные знаки, или как найти «звездочку»
Сухая официальная справка по Excel сообщает, что можно использовать подстановочные символы «*» и «?». Что они означают несколько символов, включая их отсутствие, и один любой символ. И что их можно использовать для соответствующих процедур поиска.
Чего не говорит справка — это того, что в комбинации с опцией «ячейка целиком» эти символы позволяют, не прибегая к помощи расширенного фильтра и процедуры поиска группы ячеек:
- Находить ячейки, заканчивающиеся на определенный символ, слово или текст
- Аналогично, находить ячейки, начинающиеся с определенного символа, слова или текста
- Находить непустые ячейки
На примере ниже мы находим все двузначные числа, затем числа, заканчивающиеся и начинающиеся на 7, и, наконец, все непустые ячейки. Напомню, выделить все результаты поиска помогает горячее сочетание клавиш Ctrl + A
Действительно, забыл. Чтобы найти «звездочку», нужно в окошке поиска ставить перед ней знак ~ (тильда), находится обычно под клавишей Esc . Это позволяет экранировать «звездочку», как и вопросительный знак, и не воспринимать их как служебные символы.
Как сделать чтобы в excel цифры не менялись на дату?
- Функция ПОДСТАВИТЬ регистрозависимая, что заставляет при замене одного символа использовать два его варианта — в верхнем и нижнем регистрах. Хотя, в некоторых случаях, как пример на картинке выше, это и преимущество.
- максимум 64 замены — хоть и много, но все же ограничение.
- формально процедура замены таким способом будет происходить массово и моментально, однако, длительность написания таких формул сводит на нет это преимущество. За исключением случаев, когда они будут использоваться многократно.
Если же нужно заменять не символы, а несколько значений, состоящих в свою очередь из нескольких букв, цифр или знаков — синтаксис предполагает уже использование круглых скобок и вертикальной черты «|» в качестве разделителя.
Как отобразить в ячейках формулы вместо их значений?
В окне «Параметры Excel» на вкладке «Дополнительно» в группе «Показать параметры для следующего листа» выбери те в списке лист для настройки, а затем активируйте пункт «Показывать формулы, а не их значения». Закройте окно кнопкой «ОК».
Почему так произошло и что нужно сделать, чтобы увидеть результат вычислений? Ответ простой: в этой ячейке нужно просто изменить формат самой ячейки с Текстового на Общий или иной, потому что текстовый формат ячейки предполагает, что мы указываем в ней именно текст, а не формулу, вот и выводится этот текст целиком.
Как сделать чтобы excel не ставил дату?
- Откройте вкладку Файл, чтобы перейти к представлению Backstage.
- Нажмите Параметры.
- В появившемся диалоговом окне Параметры Excel выберите раздел Формулы. Поставьте флажок Стиль ссылок R1C1 и нажмите OK. Excel перейдет к стилю ссылок R1C1.
При этом процедуры изменяют исходный диапазон, что экономит время. Все что нужно — предварительно выделить его, определиться с задачей, вызвать нужную процедуру и выделить 2 столбца сопоставления заменяемых и замещающих значений (предполагается, что если вы знаете, что на что менять, то и такие списки есть).
Поиск и замена данных
Еще удобнее применить сочетание горячих клавиш найти и заменить в Excel – Ctrl+H.
Диалоговое окно увеличится на одно поле, в котором указываются новые символы, которые будут вставлены вместо найденных.
По аналогии с простым поиском, менять можно и формат.
Кнопка Заменить все позволяет одним махом заменить одни символы на другие. После замены Excel показывается информационное окно с количеством произведенных замен. Кнопка Заменить позволяет производить замену по одной ячейке после каждого нажатия. Если найти и заменить в Excel не работает, попробуйте изменить параметры поиска.
Теперь вы знаете, как в эксель сделать поиск по столбцу, строке, любому диапазону, листу или даже книге.
Найти и заменить в Excel.
Хочу обратить внимание, что Вы можете выбрать число десятичных знаков, то есть, какое количество цифр у Вас будет после запятой. Так же есть возможность автоматического форматирования разделителя групп разрядов, то есть число “123456” будет выглядеть, как “123 456”.
