Google Excel найти и заменить

Обновлено: 03.07.2024

Мы разделили ячейки с текстом в электронных таблицах, убрали и добавили различные символы, изменили регистр текста. Теперь самое время научиться находить и заменять специальные символы в Google Таблицах за один раз.

Поиск и замена символов с помощью формул Google Таблиц

Начну с обычного: есть 3 функции, которые я считаю особенно полезными, когда дело доходит до поиска и замены специальных символов в ячейках Google Sheets.

ЗАМЕНИТЬ

Первый – ЗАМЕНИТЬ. Он буквально ищет определенный символ в нужном диапазоне и заменяет его другой конкретной строкой:

text_to_search – это ячейка/конкретный текст, в который вы хотите внести изменения. Обязательно.
search_for — это символ, который вы хотите заменить. Обязательно.
replace_with – это новый символ, который вы хотите получить вместо символа из предыдущего аргумента. Обязательно.
occurrence_number – совершенно необязательный аргумент. Если есть несколько экземпляров персонажа, он позволит вам выбрать, какой из них заменить. Опустите аргумент — и все экземпляры будут заменены.

=ПОДСТАВИТЬ(A2,"","""")

Видите? Я смотрю на A2, ищу открывающие умные кавычки — " (которые должны быть заключены в двойные кавычки в соответствии с запросом функции), и заменяю их прямыми кавычками — "

Примечание. Прямые кавычки не только заключены в двойные кавычки, но и добавлены еще ", так что всего 4 двойных кавычки.

Как добавить в эту формулу закрывающие умные кавычки? Легко :) Просто используйте эту первую формулу с другой ЗАМЕНОЙ:

=SUBSTITUTE(SUBSTITUTE(A2,"“",""""),""","""")

ЗАМЕНА внутри сначала заменяет открывающие скобки, и его результат становится диапазон, с которым нужно работать для второй ЗАМЕНЫ.

Совет. Чем больше символов вы хотите найти и заменить, тем больше функций ПОДСТАВКИ вам потребуется. Вот пример с дополнительной одинарной умной кавычкой:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2,"“",""""),""",""""),"'","'" )

REGEXREPLACE

REGEXREPLACE — это еще одна функция, которую я буду использовать для поиска и замены умных кавычек прямыми.

текст — это место, где вы хотите внести изменения
regular_expression – это комбинация символов (своего рода маска), которая указывает, что нужно найти и заменить.
замена — это новый текст вместо старого.

В основном здесь используется то же, что и в случае с SUBSTITUTE. Единственный нюанс — правильно построить regular_expression.

Сначала найдем и заменим все открывающие и закрывающие умные кавычки:

=REGEXREPLACE(A2,"[""]","""")

Формула учитывает A2.
Поиск всех экземпляров каждого символа, указанного в квадратных скобках: ""

Примечание. Не забудьте заключить все регулярное выражение в двойные кавычки, так как это требуется функцией.

Почему две пары двойных кавычек? Ну, первый и последний требуются функции так же, как и в предыдущем аргументе — вы просто вводите все между ними.

Вы можете задаться вопросом: почему я не могу добавить сюда еще одну умную цитату?

Потому что во втором аргументе можно перечислить все символы, которые нужно искать, но нельзя перечислить различные эквиваленты для возврата в третьем аргументе. Все найденное (из второго аргумента) будет заменено строкой из третьего аргумента.

Вот почему, чтобы включить в формулу эту единственную умную кавычку, вы должны объединить две функции REGEXREPLACE:

=REGEXREPLACE(REGEXREPLACE(A2,"[""]",""""),"'","'")

Как видите, формула, которую я использовал ранее ( здесь он посередине) становится диапазоном для обработки для другого REGEXREPLACE. Вот как эта функция шаг за шагом находит и заменяет символы в Google Таблицах.

Инструменты для Google Таблиц для поиска и замены символов

Когда дело доходит до поиска и замены данных в Google Таблицах, формулы – не единственный вариант. Есть 3 специальных инструмента, которые делают эту работу. В отличие от формул, они не требуют дополнительных столбцов для возврата результатов.

Стандартный инструмент поиска и замены из Google Таблиц

Держу пари, вы знакомы со стандартным инструментом поиска и замены для Google Таблиц:

Вы нажали Ctrl+H.
Укажите, что нужно найти.
Введите значение замены.
Выберите между всеми листами / текущим листом / конкретным диапазоном для обработки.
И сразу нажмите "Найти и заменить" или "Заменить все".

Ничего особенного — это минимум, необходимый многим из нас для успешного поиска и замены данных в электронных таблицах. Но что, если я скажу вам, что этот минимум можно расширить, не создавая ни малейших затруднений в использовании?

Расширенный поиск и замена — надстройка для Google Таблиц

Представьте себе инструмент более мощный, чем стандартный инструмент "Найти и заменить". Хотите попробовать? Я говорю о нашей надстройке Advanced Find & Replace для Google Sheets. Даже новичок будет чувствовать себя уверенно в электронных таблицах.

Основы те же, но с несколькими плюсами:

Вы будете искать не только в значениях и формулах, но также в примечаниях, гиперссылках и ошибках.
Комбинация дополнительных параметров (Вся ячейка + По маске + звездочка (*)) позволит найти все ячейки, содержащие только указанные гиперссылки, примечания и ошибки. :
Вы можете выбрать любое количество электронных таблиц для просмотра — каждую из них можно отменить.
Все найденные записи аккуратно сгруппированы по листам в виде дерева, что позволяет заменить все или только выбранные записи за один раз:
Вы даже можете выполнять поиск и замену, сохраняя форматирование значений!
Есть 6 дополнительных способов работы с найденными записями: извлечь все/выбранные найденные значения; извлечь целые строки со всеми/выбранными найденными значениями; удалить строки со всеми/выбранными найденными значениями:

Это то, что я называю расширенным поиском и заменой в Google Таблицах ;) Не верьте мне на слово — установите расширенный поиск и замену из магазина электронных таблиц (или найдите его в Power Tools вместе с инструментом замены символов, описанным ниже). . Эта страница справки поможет вам на всем пути.

Заменить символы для Google Таблиц — специальное дополнение от Power Tools

Если ввод каждого символа, который вы хотите найти и заменить в Google Таблицах, невозможен, вам может помочь замена символов из Power Tools. Только не судите его по размеру — для определенных случаев он достаточно мощный:

Во всех трех случаях вам просто нужно выбрать диапазон, выбрать нужный переключатель и нажать "Выполнить". Вот демонстрационное видео в подтверждение моих слов ;)

Это дополнение является частью Power Tools, которое можно установить в вашу электронную таблицу из магазина Google Таблиц вместе с более чем 30 другими инструментами для экономии времени.

Найти и заменить текст в большой электронной таблице — это своего рода искусство.

Чтобы тратить меньше времени на просеивание нерелевантных результатов, вы должны иметь возможность выполнять точный поиск, чтобы найти именно то, что вам нужно.

В этом руководстве мы покажем вам, как эффективно использовать функцию поиска и замены Google Таблиц, чтобы быстро находить то, что вам нужно, и заменять именно то, что вам нужно.

Оглавление

Почему полезен поиск и замена в Google Таблицах

Функция "Найти и заменить" в Google Таблицах позволяет искать определенные значения во всех книгах и заменять их тем, что вам нужно.

Преимущество функции "Найти и заменить" заключается в том, что она дает возможность заменить все экземпляры строки поиска сразу или заменить их по одному.

Вот некоторые другие удобства, которые вы получаете от поиска и замены Google:

Он позволяет использовать регулярные выражения для поиска значений, соответствующих определенному шаблону.
Он позволяет использовать подстановочные знаки для поиска значений, похожих на строку поиска.
Он позволяет выполнять поиск в формулах и результатах формул.
У вас есть возможность поиска точных совпадений ячеек или ячеек, содержащих строку поиска.
У вас есть возможность указать, хотите ли вы сделать поиск чувствительным к регистру.

Мы рассмотрим вышеуказанные приложения на примерах. Мы будем использовать воображаемый набор данных (показан ниже), чтобы применить наши поиски:

Как использовать поиск и замену в Google Таблицах

Существует три способа использования функции "Найти и заменить" в Google Таблицах:

Переход к Правка->Найти и заменить
Используя сочетание клавиш CTRL+H (на ПК с Windows) или CMD+SHIFT+H (на Mac)
Используя сочетание клавиш CTRL+F (на ПК с Windows) или CMD+F (на Mac)

Между упомянутыми здесь первым и вторым быстрыми методами есть разница. Когда вы нажимаете CTRL+H, открывается диалоговое окно поиска и замены Google Таблиц.

С другой стороны, нажатие CTRL+F открывает стандартное окно поиска, которое вы можете использовать в любое время на своем компьютере.

Есть еще одно небольшое, но часто полезное отличие. Диалоговое окно "Найти и заменить" в Google Таблицах позволяет найти строку поиска по одному экземпляру за раз.

Поэтому, когда вы нажимаете кнопку "Найти" в диалоговом окне, вы видите только первое вхождение строки поиска на листе.

Чтобы увидеть следующее вхождение, нужно нажать кнопку "Найти далее". Так что на самом деле это не дает вам способа узнать, сколько существует экземпляров строки, пока вы фактически не замените все.

С другой стороны, если вы используете стандартное поле поиска, вы можете увидеть все выделенные экземпляры строки поиска, по которым затем можно перемещаться по одному, используя стрелки вверх и вниз.

Использование диалогового окна «Найти и заменить» в Google Таблицах

Диалоговое окно "Найти и заменить" предоставляет ряд параметров для точной настройки поиска и замены найденных экземпляров строки поиска.

Давайте рассмотрим каждый из вариантов один за другим, используя разные примеры поиска.

Поиск и замена сразу всех экземпляров строки поиска

Вот рабочий лист, над которым мы хотим еще раз поработать:

Допустим, вы хотите заменить все вхождения строки поиска "Производство" строкой "Производство".

Теперь вы должны увидеть, что все экземпляры слова "Производство" заменены словом "Производство".

Примечание. Диалоговое окно «Найти и заменить» позволяет найти то, что вам нужно, на текущем листе, на всех листах или даже в выбранном диапазоне ячеек. Просто выберите раскрывающийся список со стрелкой рядом с «Поиск» и выберите нужный вариант.

Замена экземпляров строки поиска по одному

Теперь предположим, что вы хотите заменить строку "Операции" другими номерами операционного отдела, например Операция-1 и Операция-2.

В таких случаях не имеет смысла заменять все экземпляры слова «Операции» сразу. Чтобы заменить экземпляры строки поиска один за другим, выполните следующие действия:

Вот как теперь должен выглядеть рабочий лист:

Соответствие всему содержимому ячейки

Что делать, если вы хотите заменить все экземпляры имени «Пол Ли» именем «Пола Ли»?

Обратите внимание, что если вы ищете имя "Пол", функция "Найти и заменить" учитывает как имена "Пол Ли", так и "Пол Родригес".

Однако, если вы хотите заменить только те экземпляры, в которых встречается имя "Пол Ли", а не "Пол Родригес", вам потребуется сделать следующее:

Вы должны обнаружить, что все экземпляры имени «Пол Ли» заменены именем «Пола Ли».

Примечание. Если вы также хотите, чтобы поиск зависел от регистра, вам необходимо установить флажок рядом с параметром Учитывать регистр.

Соответствие регулярным выражениям

В диалоговом окне "Найти и заменить" есть отличная возможность использовать регулярные выражения (Regex) для точной настройки поиска.

Используя это, вы можете искать строки, соответствующие определенному шаблону, например формату идентификатора, адресу электронной почты или URL-адресу.

Что такое регулярное выражение?

Регулярное выражение – это последовательность символов, включая символы, буквы и цифры, которые объединяются в строку поиска.

Регулярное выражение часто характеризуется определенными специальными символами, также известными как «метасимволы». Вот несколько примеров метасимволов, которые вы можете использовать:

Метасимвол	Что он представляет
^	Начало строки
$	Конец строки
.	Один символ
?	Ноль или один вхождение символа или строки
*	Ноль или более вхождений символа или строки
+	Одно или несколько вхождений символа или строки
\|	Операция Or
()	Это содержит группу метасимволов внутри и представляет эту последовательность символов
[]	Это содержит набор символов и представляет любой из символов внутри него
[^]	Это содержит набор символов и представляет любой из символов, не перечисленных внутри него
\	Это используется для экранирования специального символа

Поиск и замена текстовых строк, начинающихся с определенного алфавита

Предположим, вы хотите найти все имена, начинающиеся с буквы "П".

Допустим, вы хотите заменить имена словом «секретно». В этом случае вы можете использовать регулярное выражение ^P[a-zA-Z ]+$, чтобы найти все имена, начинающиеся с буквы P.

Обратите внимание, что символы «[a-zA-Z].

Вот шаги, которые необходимо выполнить, чтобы использовать это регулярное выражение в диалоговом окне "Найти и заменить":

Выберите столбец «Имя» (диапазон A2:A8).
Выберите Правка->Найти и заменить
Введите следующие данные:
1. Найти: ^P[a-zA-Z ]+$
2. Заменить на: классифицировано
3. Поиск: определенный диапазон
Вы должны найти все имена, начинающиеся с буквы "P", замененные словом "секретно".

Поиск в формулах

Как правило, функция «Найти и заменить» просто выполняет поиск по содержимому ячеек и результатам формул. Если вы также хотите увидеть, содержится ли искомое слово в фактической формуле ячеек, то для этого тоже есть возможность.

В нашем примере, если вы взглянете на содержимое столбца E, вы заметите, что мы использовали формулу для расчета суммы, а затем отобразили ее вместе со строкой «только».

Допустим, вы хотите удалить слово "только" из всех формул. Для этого вам необходимо выполнить следующие действия:
1. Выберите Правка->Найти и заменить
2. Введите следующие данные в диалоговом окне «Найти и заменить» (поскольку мы хотим удалить слово «только», мы просто оставляем поле «Заменить на» пустым:
  1. Найти: только
  2. Заменить на:
  3. Поиск: этот лист
  Вы должны найти все вхождения слова «только», удаленные из формул (и, следовательно, из результатов формул).
  
  В этом руководстве мы обсудили различные стратегии, чтобы помочь вам быстро и эффективно найти то, что вам нужно, чтобы вы могли впоследствии заменить это тем, что вам нужно.
  
  Диалоговое окно "Найти и заменить" в листах Google – очень полезный инструмент. Он предоставляет вам ряд параметров, в том числе возможность замены некоторых или всех вхождений строки поиска, поиска строк, соответствующих шаблону, а также просмотра формул.
  
  Лучший способ освоить этот инструмент — это практика. Со временем вы обнаружите, что все лучше и лучше придумываете правильную строку поиска, чтобы выполнять поиск и замену с легкостью и точностью.
  
  В нашей профессиональной жизни мы часто имеем дело с огромными объемами данных, которые постоянно меняются с течением времени. Поиск этих данных и замена существующих значений новыми — это то, что приходится делать многим из нас. Это может быть достаточно просто, когда есть несколько строк данных. Но это становится еще более сложным и трудоемким, когда эти данные значительно увеличиваются. Google Таблицы — отличный инструмент для работы с электронными таблицами для хранения и управления большими объемами данных. Но он также предлагает фантастическую функцию, которая еще больше упрощает идентификацию и изменение данных. В Google Таблицах это называется «Найти и заменить».
  
  Функция поиска и замены в Google Sheets – это отличный инструмент для работы с большим количеством данных, которые необходимо постоянно обновлять. Особенно данные, отражающие процесс или статус. Например, предположим, что я управляю статусом заказа последних покупок продуктов. Будет постоянное изменение между различными значениями, такими как статус заказа, уровень запасов или даже изменения цен. Используя функцию поиска и замены, я мог бы найти и изменить эти конкретные фрагменты данных из сотен строк и изменить их соответствующим образом. Это не только сократит время, необходимое мне для поиска конкретных значений, но и сделает весь процесс управления данными более эффективным.
  
  Давайте более подробно рассмотрим функцию поиска и замены и пошагово рассмотрим, как находить и заменять значения в Google Таблицах.
  
  Зачем использовать функцию поиска и замены в Google Таблицах?
  
  Функция «Найти и заменить» в Google Таблицах имеет несколько вариантов, которые выполняют разные задачи при управлении вашими данными. Вы можете не только идентифицировать и изменять значения в электронной таблице, как мы покажем в этой статье, но и применять дополнительные функции, которые выведут ваши поисковые запросы на новый уровень.
  
  Вот некоторые дополнительные функции поиска и замены в Google Таблицах:
  - Выберите фильтрацию, где вы ищете свое значение: вы можете сузить поиск значения от всей электронной таблицы до одного листа или даже определенного диапазона.
  - Учитывать регистр: сделайте поиск чувствительным к регистру для дополнительной фильтрации или даже для выявления любых грамматических ошибок.
  - Соответствие всему содержимому ячейки: убедитесь, что ваш поиск максимально точен, выбрав эту функцию.
  - Поиск с использованием регулярных выражений: поиск ячеек, соответствующих шаблону. Вы можете узнать больше об этом более сложном процессе на сайте поддержки Google.
  - Поиск в формулах: вы также можете искать определенные формулы, которые вы ввели в свои электронные таблицы.
  Теперь, когда вы хорошо понимаете, что предлагает функция "Найти и заменить" в Google Таблицах, давайте применим ее на практике!
  
  Как найти и заменить значения в Google Sheets
  
  Используя приведенный выше вариант использования, давайте найдем и заменим значения в нашей электронной таблице, полной данных о статусах заказов покупок наших клиентов.
  
  Давайте рассмотрим различные способы использования функции «Найти и заменить» для ваших данных.
  
  1. Как найти значение в Google Sheets
  
  Допустим, мы хотим найти количество заказов продуктов Jam в нашей электронной таблице.
  
  Чтобы открыть функцию "Найти и заменить", у вас есть два варианта:
  1. В меню навигации Google Таблиц нажмите "Правка" > "Найти и заменить".
  2. Используйте сочетание клавиш Ctrl + H (Cmd + H на Mac)
  В поле "Найти" введите значение, которое вы хотите найти в электронной таблице (в данном случае "Jam"). Если вы хотите сделать поиск чувствительным к регистру, установите флажок Учитывать регистр.
  
  Затем нажмите кнопку «Найти», расположенную в нижней части всплывающего окна. Будет выделено первое значение, содержащее значение «Jam».Продолжайте нажимать кнопку "Найти", чтобы увидеть, когда слово используется в следующий раз.
  
  Кроме того, существует более простой способ поиска значений, не использующий функцию "Найти и заменить" в Google Таблицах.
  
  Просто нажмите комбинацию клавиш Ctrl + F (Cmd + F на Mac). Вы увидите небольшое текстовое поле в верхней правой части электронной таблицы. Введите значение, которое мы ищем в электронной таблице (в данном случае «Jam»). Затем функция выделит все ячейки, содержащие искомый текст. Мы можем использовать кнопки со стрелками вверх и вниз, чтобы перейти к следующему совпадению.
  
  2. Как найти и заменить одно значение в Google Sheets
  
  Допустим, мне нужно изменить статус этого конкретного заказа с "Отправлено" на "Получено".
  
  Сначала откройте функцию "Найти и заменить" удобным для вас способом. В поле «Найти» введите значение, которое вы хотите найти и заменить. В этом случае я наберу «Отправлено».
  
  В поле Получено введите новое значение. В этом примере это будет «Получено». Чтобы найти точное значение, которое вы искали, установите флажок Совпадать со всем содержимым ячейки.
  
  Нажмите кнопку "Найти". Убедитесь, что выбрана ячейка, принадлежащая правильной строке.
  
  После выбора нажмите кнопку «Заменить», расположенную в нижней части всплывающего окна.
  
  Эта функция уведомит вас об изменении значения.
  
  Если вы довольны результатом, нажмите "Готово", чтобы вернуться к электронной таблице.
  
  2. Как найти и заменить одно значение в Google Sheets
  
  Допустим, мне нужно изменить статус этого конкретного заказа с «Отправлено» на «Получено». Выполните те же действия, что и выше. Вместо кнопки «Заменить» выберите «Заменить все». С этой опцией каждое отдельное значение «Отправлено» будет заменено на «Получено».
  
  Как видите, функция определила 25 значений как «Отправлено» и успешно заменила их на «Получено».
  
  Если вы довольны результатом, нажмите "Готово", чтобы вернуться к электронной таблице.
  
  Функция поиска и замены в Google Таблицах
  
  И вот оно! Функция «Найти и заменить» — это фантастический инструмент для управления данными, которые постоянно требуют внесения изменений вручную. С различными комбинациями многочисленных доступных опций вы можете выполнять ряд различных задач от поиска определенных значений до поиска и замены нескольких значений. Хотите узнать о других функциях Google Таблиц? Взгляните на нашу статью об истории изменений в Google Sheets! Кроме того, ознакомьтесь с нашими соответствующими статьями ниже.
  
  Примечание редактора. Это переработанная версия предыдущего сообщения, которая была обновлена для обеспечения точности и полноты.
  
  В Excel можно одновременно найти и заменить несколько ячеек, содержащих определенное значение. Допустим, у вас есть следующий набор данных с данными о продажах.
  
  Вы хотите заменить все вхождения слова Веб-камера на Камера в столбце B (ячейки B7, B13 и B19).
  <р>1. Выберите диапазон, в котором вы хотите заменить значения (здесь B2:B19), и на ленте перейдите в раздел «Главная» > «Найти и выделить» > «Заменить…» (или используйте сочетания клавиш CTRL + F, чтобы открыть поиск (затем перейдите к пункту «Заменить» или «Заменить»). CTRL + H, чтобы сразу перейти к меню «Найти и заменить»).
  
  <р>2. В окне "Найти и заменить" введите значение, которое нужно найти (Веб-камера), и значение, которое заменит его (Камера), и нажмите "Заменить все".< /p>
  
  <р>3. Вы получите всплывающее окно с информацией о том, сколько ячеек было заменено новым значением (в данном случае 3). Нажмите "ОК".
  
  <р>4. Нажмите «Закрыть», чтобы закрыть окно «Найти и заменить».
  
  В результате все ячейки, в которых была Веб-камера, теперь заменены на Камера (B7, B13 и B19).
  
  Примечание. Другой способ замены нескольких значений — использование функции ПОДСТАВИТЬ. Кроме того, вы можете использовать код VBA для поиска и замены значений.
  
  Найти и заменить в Google Таблицах
  
  Как и в Excel, вы также можете находить и заменять несколько значений в Google Таблицах.
  <р>1. Выберите диапазон, в котором вы хотите заменить значения (здесь B2:B19), и в меню выберите Правка > Найти и заменить (или используйте сочетание клавиш CTRL + H).
  
  <р>2. В окне "Найти и заменить" введите значение, которое нужно найти (Веб-камера), и значение, которое заменит его (Камера), и нажмите "Заменить все".< /p>
  
  <р>3. В нижней части окна вы получите информацию о том, сколько замен было сделано (в данном случае 3). Нажмите «Готово», чтобы закрыть окно.
  
  Результат тот же, что и в Excel, все вхождения Веб-камера в столбце B теперь заменены на Камера.
  
  Читайте также: