Excel находит позицию и заменяет значение в соседней ячейке
Обновлено: 23.11.2024
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции СМЕЩ в Microsoft Excel.
Описание
Возвращает ссылку на диапазон, который представляет собой указанное количество строк и столбцов из ячейки или диапазона ячеек. Возвращаемая ссылка может быть одной ячейкой или диапазоном ячеек. Вы можете указать количество строк и количество возвращаемых столбцов.
Синтаксис
СМЕЩЕНИЕ(ссылка, строки, столбцы, [высота], [ширина])
Синтаксис функции СМЕЩ имеет следующие аргументы:
Обязательные строки. Количество строк вверх или вниз, на которое должна ссылаться верхняя левая ячейка. Использование 5 в качестве аргумента rows указывает, что верхняя левая ячейка в ссылке находится на пять строк ниже ссылки. Строки могут быть положительными (что означает ниже начальной ссылки) или отрицательными (что означает выше начальной ссылки).
Обязательные столбцы. Количество столбцов слева или справа, на которые должна ссылаться верхняя левая ячейка результата. Использование 5 в качестве аргумента cols указывает, что верхняя левая ячейка в ссылке находится на пять столбцов справа от ссылки. Столбцы могут быть положительными (что означает справа от начальной ссылки) или отрицательными (что означает слева от начальной ссылки).
Высота Необязательно. Высота в количестве строк, которой должна быть возвращенная ссылка. Высота должна быть положительным числом.
Ширина Необязательно. Ширина (количество столбцов), которой должна быть возвращенная ссылка. Ширина должна быть положительным числом.
Примечания
Если высота или ширина не указаны, предполагается, что они равны высоте или ширине ссылки.
СМЕЩЕНИЕ на самом деле не перемещает ячейки и не изменяет выделение; он просто возвращает ссылку. OFFSET можно использовать с любой функцией, ожидающей ссылочного аргумента. Например, формула СУММ(СМЕЩ(C2,1,2,3,1)) вычисляет общее значение диапазона из 3 строк на 1 столбец, который находится на 1 строку ниже и на 2 столбца справа от ячейки C2.< /p>
Пример
Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете изменить ширину столбцов, чтобы увидеть все данные.
В учебнике объясняются функции Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ с примерами их использования. Узнайте, как использовать функцию ЗАМЕНИТЬ с текстовыми строками, числами и датами, а также как вложить несколько функций ЗАМЕНИТЬ или ПОДСТАВИТЬ в одну формулу.
На прошлой неделе мы обсуждали различные способы использования функций НАЙТИ и ПОИСК на листах Excel. Сегодня мы более подробно рассмотрим две другие функции для замены текста в ячейке в зависимости от ее местоположения или замены одной текстовой строки другой в зависимости от содержимого. Как вы уже догадались, я говорю о функциях Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ.
Функция Excel ЗАМЕНИТЬ
Функция ЗАМЕНИТЬ в Excel позволяет заменить один или несколько символов в текстовой строке другим символом или набором символов.
Как видите, функция ЗАМЕНА в Excel имеет 4 аргумента, и все они обязательны.
- Старый_текст — исходный текст (или ссылка на ячейку с исходным текстом), в котором вы хотите заменить некоторые символы.
- Начальный_номер — позиция первого символа в старом_тексте, который вы хотите заменить.
- Num_chars — количество символов, которые вы хотите заменить.
- Новый_текст – замещающий текст.
Например, чтобы заменить слово "sun" на "son", можно использовать следующую формулу:
=ЗАМЕНИТЬ("солнце", 2, 1, "о")
И если вы поместите исходное слово в какую-либо ячейку, скажем, A2, вы можете указать ссылку на соответствующую ячейку в аргументе old_text:
=ЗАМЕНИТЬ(A2, 2, 1, "o")
Использование функции Excel REPLACE с числовыми значениями
Функция ЗАМЕНИТЬ в Excel предназначена для работы с текстовыми строками. Конечно, вы можете использовать его для замены цифровых символов, являющихся частью текстовой строки, например:
=REPLACE(A2, 7, 4, "2016")
Обратите внимание, что мы заключаем «2016» в двойные кавычки, как обычно делаем с текстовыми значениями.
Аналогичным образом вы можете заменить одну или несколько цифр в числе.Например:
И снова нужно заключить значение замены в двойные кавычки ("6").
Примечание. Формула ЗАМЕНИТЬ в Excel всегда возвращает текстовую строку, а не число. На снимке экрана выше обратите внимание на выравнивание по левому краю возвращаемого текстового значения в ячейке B2 и сравните его с исходным числом, выровненным по правому краю в ячейке A2. А поскольку это текстовое значение, вы не сможете использовать его в других вычислениях, пока не преобразуете его обратно в число, например, умножив на 1 или используя любой другой метод, описанный в разделе Как преобразовать текст в число.
Использование функции Excel REPLACE с датами
Как вы только что видели, функция ЗАМЕНИТЬ прекрасно работает с числами, за исключением того, что она возвращает текстовую строку :) Помня, что во внутренней системе Excel даты хранятся в виде чисел, вы можете попробовать использовать некоторые формулы замены дат. . Результаты будут весьма неловкими.
Например, у вас есть дата в формате A2, скажем, 1-окт-14, и вы хотите изменить "октябрь" на "ноябрь". Итак, вы пишете формулу REPLACE(A2, 4, 3, "Nov"), которая указывает Excel заменить 3 символа в ячейках A2, начиная с 4-го символа… и получили следующий результат:
Почему? Потому что «01-Oct-14» — это только визуальное представление базового серийного номера (41913), представляющего дату. Таким образом, наша формула замены изменяет последние 3 цифры в приведенном выше серийном номере на "Nov" и возвращает текстовую строку "419Nov".
Чтобы заставить функцию Excel ЗАМЕНИТЬ правильно работать с датами, вы можете сначала преобразовать даты в текстовые строки, используя функцию ТЕКСТ или любой другой метод, показанный в разделе Как преобразовать дату в текст в Excel. Кроме того, вы можете встроить функцию ТЕКСТ непосредственно в аргумент old_text функции ЗАМЕНИТЬ:
=ЗАМЕНИТЬ(ТЕКСТ(A2, "дд-ммм-гг"), 4, 3, "ноябрь")
Помните, что результатом приведенной выше формулы является текстовая строка, и поэтому это решение работает, только если вы не планируете использовать измененные даты в дальнейших расчетах. Если вам нужны даты, а не текстовые строки, используйте функцию DATEVALUE, чтобы преобразовать значения, возвращаемые функцией Excel REPLACE, обратно в даты:
=ДАТАЗНАЧ(ЗАМЕНИТЬ(ТЕКСТ(A2, "дд-ммм-гг"), 4, 3, "ноябрь"))
Вложенные функции REPLACE для выполнения нескольких замен в ячейке
Довольно часто может потребоваться выполнить несколько замен в одной и той же ячейке. Конечно, можно было сделать одну замену, вывести промежуточный результат в дополнительный столбец, а затем снова использовать функцию ЗАМЕНИТЬ. Однако лучший и более профессиональный способ — использовать вложенные функции REPLACE, которые позволяют выполнять несколько замен с помощью одной формулы. В этом контексте "вложение" означает помещение одной функции в другую.
Рассмотрите следующий пример. Предположим, у вас есть список телефонных номеров в столбце A, отформатированный как «123456789», и вы хотите сделать их более похожими на телефонные номера, добавив дефисы. Другими словами, ваша цель — превратить "123456789" в "123-456-789".
Вставить первый дефис очень просто. Вы пишете обычную формулу замены Excel, которая заменяет нулевые символы дефисом, т.е. добавляет дефис на 4-й позиции в ячейке:
Результат приведенной выше формулы замены выглядит следующим образом:
Хорошо, а теперь нам нужно вставить еще один дефис в 8-ю позицию. Для этого вы помещаете приведенную выше формулу в другую функцию Excel REPLACE. Точнее, вы вставляете его в аргумент old_text другой функции, чтобы вторая функция REPLACE обрабатывала значение, возвращаемое первой REPLACE, а не значение в ячейке A2:
В результате вы получаете телефонные номера в нужном формате:
Аналогичным образом вы можете использовать вложенные функции REPLACE, чтобы текстовые строки выглядели как даты, добавляя косую черту (/) там, где это необходимо:
=(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2,3,0,"/"),6,0,"/"))
Кроме того, вы можете преобразовать текстовые строки в реальные даты, заключив приведенную выше формулу ЗАМЕНИТЬ в функцию ДАТАЗНАЧ:
И, естественно, вы не ограничены в количестве функций, которые вы можете вложить в одну формулу (современные версии Excel 2010, 2013 и 2016 позволяют использовать до 8192 символов и до 64 вложенных функций в формуле).
Например, вы можете использовать 3 вложенные функции REPLACE, чтобы число в A2 отображалось как дата и время:
=REPLACE(REPLACE(REPLACE(REPLACE(A2,3,0,"/") ,6,0,"/"), 9,0, " "), 12,0, ":")
Замена строки, которая появляется в разных позициях в каждой ячейке
До сих пор во всех примерах мы имели дело со значениями схожего характера и производили замены в одной и той же позиции в каждой ячейке. Но реальные задачи часто бывают сложнее. В ваших рабочих листах заменяемые символы могут не обязательно появляться в одном и том же месте в каждой ячейке, и поэтому вам придется найти позицию первого символа, который необходимо заменить. Следующий пример продемонстрирует то, о чем я говорю.
Предположим, у вас есть список адресов электронной почты в столбце A. И название одной компании изменилось с "ABC" на, скажем, "BCA". Таким образом, вам необходимо соответствующим образом обновить адреса электронной почты всех клиентов.
Но проблема в том, что имена клиентов имеют разную длину, и поэтому вы не можете указать, где именно начинается название компании. Другими словами, вы не знаете, какое значение указать в аргументе start_num функции Excel REPLACE. Чтобы узнать это, используйте функцию Excel НАЙТИ, чтобы определить позицию первого символа в строке «@abc»:
Затем укажите указанную выше функцию НАЙТИ в аргументе start_num формулы ЗАМЕНИТЬ:
=ЗАМЕНИТЬ(A2, НАЙТИ("@abc",A2), 4, "@bca")
Совет. Мы включаем «@» в нашу формулу поиска и замены Excel, чтобы избежать случайных замен в части имени адресов электронной почты. Конечно, вероятность того, что такие совпадения произойдут, очень мала, и все же вы можете перестраховаться.
И мы хотим, чтобы формула возвращала исходный адрес электронной почты вместо ошибки. Итак, давайте заключим нашу формулу НАЙТИ И ЗАМЕНИТЬ в функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ЗАМЕНИТЬ(A2, НАЙТИ("@abc",A2), 4, "@bca"),A2)
И эта улучшенная формула отлично работает, не так ли?
Совет. Если вы хотите произвести замену в исходных данных, проще использовать диалоговое окно Excel НАЙТИ и ЗАМЕНИТЬ.
Функция Excel ПОДСТАВИТЬ
Функция ПОДСТАВИТЬ в Excel заменяет один или несколько экземпляров заданного символа или текстовой строки указанными символами.
Синтаксис функции ПОДСТАВИТЬ в Excel следующий:
Первые три аргумента являются обязательными, а последний необязательным.
- Текст — исходный текст, в котором вы хотите заменить символы. Может быть предоставлена в виде тестовой строки, ссылки на ячейку или результата другой формулы.
- Old_text — символы, которые вы хотите заменить.
- Новый_текст — новые символы для замены старого_текста.
- Instance_num – экземпляр old_text, который вы хотите заменить. Если его не указать, каждое вхождение старого текста будет заменено новым текстом.
Например, все приведенные ниже формулы заменяют "1" на "2" в ячейке A2, но возвращают разные результаты в зависимости от того, какое число указано в последнем аргументе:
=SUBSTITUTE(A2, "1", "2", 1) — первое вхождение "1" заменяется на "2".
=SUBSTITUTE(A2, "1", "2", 2) — заменяет второе вхождение "1" на "2".
=SUBSTITUTE(A2, "1", "2") — заменяет все вхождения "1" на "2".
Примечание. Функция ПОДСТАВИТЬ в Excel чувствительна к регистру.Например, следующая формула заменяет все вхождения буквы «X» в верхнем регистре на «Y» в ячейке A2, но не заменяет ни одной буквы «x» в нижнем регистре.
Замена нескольких значений одной формулой (вложенная ПОДСТАВКА)
Как и в случае с функцией ЗАМЕНА Excel, вы можете вложить несколько функций ПОДСТАВИТЬ в одну формулу, чтобы выполнять несколько подстановок одновременно, т. е. заменять несколько символов или подстрок одной формулой.
Предположим, что у вас есть текстовая строка вида "PR1, ML1, T1" в ячейке A2, где "PR" означает "Проект", "ML" означает "Веха", а "T" означает "Задача". Вам нужно заменить три кода полными именами. Для этого вы можете написать 3 разные формулы ПОДСТАВКИ:
=ПОДСТАВИТЬ(A2, "ML", "Веха")
=ПОДСТАВИТЬ(A2, "Т", "Задание")
А затем вложите их друг в друга:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2,"PR","Проект"),"ML","Веха"),"T","Задача")
Обратите внимание, что мы добавили пробел в конце каждого аргумента new_text для удобства чтения.
ЗАМЕНА в Excel и ЗАМЕНА в Excel
Функции Excel REPLACE и SUBSTITUTE очень похожи друг на друга в том смысле, что обе предназначены для замены текстовых строк. Различия между этими двумя функциями заключаются в следующем:
- SUBSTITUTE заменяет один или несколько экземпляров заданного символа или текстовой строки. Итак, если вы знаете текст, который нужно заменить, используйте функцию Excel ПОДСТАВИТЬ.
- REPLACE изменяет символы в указанной позиции текстовой строки. Итак, если вы знаете положение заменяемых символов, используйте функцию Excel REPLACE.
- Функция ПОДСТАВИТЬ в Excel позволяет добавить необязательный параметр (instance_num), указывающий, какое вхождение old_text следует заменить на new_text.
Вот как вы используете функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel. Надеюсь, эти примеры окажутся полезными при решении ваших задач. Я благодарю вас за чтение и надеюсь увидеть в нашем блоге на следующей неделе!
это мой первый пост здесь, я знаю, что плохо формулирую.
Я пытаюсь найти ячейки, содержащие определенную фразу, в столбце дат. Эта фраза отмечает начало раздела. Затем я хочу указать количество дней, прошедших с первой даты в каждом разделе до других дат в разделе. Возвращаемые значения должны отображаться в соседнем столбце. Ниже приведен пример столбцов.
Я использую эту формулу во 2-м столбце:
А затем перетащите его вниз в ячейки соответствующего раздела. Я пытаюсь автоматизировать этот процесс.
Я нашел этот код на этом сайте и немного изменил его, чтобы получить это:
Итак, мне трудно понять, что сказать в поле 2nd Value. Мне как-то нужно, чтобы каждый раздел вычитал первую дату каждого раздела (дата прямо под «Фразой»).
Еще одна задача состоит в том, чтобы скопировать первую соседнюю ячейку, которая была изменена, а затем вставить специальный код в ячейки ниже, но остановившись, как только появится следующая фраза.
Я уточню, как смогу. Спасибо.
1 Ответ 1
Я думаю, справедливо будет сказать, что ваш вопрос не требует особых усилий для решения этой проблемы, и фрагмент кода просто ставит тире рядом с ячейкой «Фраза». Однако для более широкой аудитории этот вопрос интересен, потому что он подчеркивает разницу между автоматизацией задачи нажатия клавиш Excel и написанием кода для обработки данных, которые затем записываются на лист Excel. Оба используют VBA, но первый представляет собой просто программную запись нажатий клавиш, а второй представляет собой алгоритмическое решение.
Полезная фраза в вашем вопросе: Я использую эту формулу во 2-м столбце . а затем перетащите его вниз в ячейки в соответствующем разделе. Я пытаюсь автоматизировать этот процесс. Это можно было бы сделать, используя VBA для воспроизведения набора функций рабочего листа, но это неудобно и может усложниться. Я предоставлю кому-то другому ответить на этот вопрос, так как ему потребуется больше времени на ответ, чем вам на вопрос (одно из моих правил не делать!).
Если, с другой стороны, вы откажетесь от попыток автоматизировать нажатия клавиш и переключитесь на VBA для обработки данных, проблема станет очень тривиальной. Это действительно хороший пример того, как VBA всего несколькими строками может решить проблемы, на решение которых функции Excel могут уйти несколько страниц, и, вероятно, ненадежно.
Итак, вот код решения VBA. Потребуется добавить несколько строк проверки данных для обработки пустых ячеек, не дат и т. д., но я передам эту задачу вам:
В этом руководстве вы узнаете, как использовать функцию «Найти и заменить» в Excel для поиска определенных данных на листе или в книге, а также узнать, что можно делать с этими ячейками после их обнаружения. Мы также рассмотрим расширенные функции поиска Excel, такие как подстановочные знаки, поиск ячеек с формулами или определенным форматированием, поиск и замена во всех открытых книгах и многое другое.
При работе с большими электронными таблицами в Excel очень важно иметь возможность быстро находить нужную информацию в любой конкретный момент. Сканирование сотен строк и столбцов, безусловно, не лучший способ, поэтому давайте подробнее рассмотрим, что может предложить функция поиска и замены в Excel.
Как использовать функцию «Найти» в Excel
Ниже вы найдете обзор возможностей поиска в Excel, а также подробные инструкции по использованию этой функции в Microsoft Excel 365, 2019, 2016, 2013, 2010 и более ранних версиях.
Поиск значения в диапазоне, на листе или в книге
Следующие рекомендации рассказывают, как найти определенные символы, текст, числа или даты в диапазоне ячеек, на листе или во всей книге.
При нажатии кнопки "Найти далее" Excel выбирает первое вхождение искомого значения на листе, второй щелчок выбирает второе вхождение и т. д.
При нажатии кнопки «Найти все» Excel открывает список всех вхождений, и вы можете щелкнуть любой элемент в списке, чтобы перейти к соответствующей ячейке.
Поиск в Excel — дополнительные параметры
Чтобы точно настроить поиск, нажмите «Параметры» в правом углу диалогового окна Excel Найти и заменить, а затем выполните одно из следующих действий:
- Чтобы найти указанное значение на текущем листе или во всей книге, выберите Лист или Рабочая книга в пределах.
- Для поиска в активной ячейке слева направо (построчно) выберите По строкам в поле поиска. Для поиска сверху вниз (по столбцу) выберите < em>По столбцам.
- Для поиска среди данных определенного типа выберите Формулы, Значения или Комментарии в окне поиска.
- Для поиска с учетом регистра установите флажок Учитывать регистр.
- Чтобы найти ячейки, содержащие только те символы, которые вы ввели в поле Найти, установите флажок Совпадать со всем содержимым ячейки.
Совет. Если вы хотите найти заданное значение в диапазоне, столбце или строке, выберите этот диапазон, столбцы или строки, прежде чем открывать Найти и заменить в Excel. Например, чтобы ограничить поиск определенным столбцом, сначала выберите этот столбец, а затем откройте диалоговое окно Найти и заменить.
Поиск ячеек определенного формата в Excel
Чтобы найти ячейки с определенным форматированием, нажмите сочетание клавиш Ctrl + F, чтобы открыть диалоговое окно Найти и заменить, нажмите Параметры, затем нажмите кнопку Формат… в верхнем правом углу и определите свой выбор в диалоговом окне Excel Найти формат.
Если вы хотите найти ячейки, которые соответствуют формату какой-либо другой ячейки на вашем листе, удалите все критерии в поле Найти, нажмите стрелку рядом с элементом Формат. , выберите «Выбрать формат из ячейки» и щелкните ячейку с нужным форматированием.
Примечание. Microsoft Excel сохраняет указанные вами параметры форматирования. Если вы ищете какие-либо другие данные на листе, а Excel не может найти значения, которые, как вы знаете, там есть, снимите параметры форматирования из предыдущего поиска. Для этого откройте диалоговое окно Найти и заменить, нажмите кнопку Параметры на вкладке Найти, затем нажмите стрелку рядом с Формат.. и выберите Очистить формат поиска.
Найти ячейки с формулами в Excel
С помощью функции Найти и заменить в Excel вы можете выполнять поиск заданного значения только в формулах, как описано в дополнительных параметрах функции поиска Excel. Чтобы найти ячейки, содержащие формулы, используйте функцию Перейти к специальному.
Если Microsoft Excel находит какие-либо ячейки, соответствующие вашим критериям, эти ячейки выделяются, в противном случае будет отображаться сообщение о том, что такие ячейки не найдены.
Совет.Чтобы быстро найти все ячейки с формулами, независимо от результата формулы, нажмите Найти и выделить > Формулы.
Как выбрать и выделить все найденные записи на листе
Чтобы выбрать все вхождения заданного значения на листе, откройте диалоговое окно Excel Найти и заменить, введите условие поиска в поле Найти и нажмите «Найти все». .
Excel отобразит список найденных объектов, и вы щелкните любое вхождение в списке (или просто щелкните в любом месте области результатов, чтобы переместить туда фокус) и нажмите сочетание клавиш Ctrl + A. При этом будут выбраны все найденные вхождения как в диалоговом окне Найти и заменить, так и на листе.
Выбрав ячейки, вы можете выделить их, изменив цвет заливки.
Как использовать «Заменить» в Excel
Ниже вы найдете пошаговые инструкции по использованию функции «Замена» в Excel для замены одного значения на другое в выбранном диапазоне ячеек, во всем листе или книге.
Заменить одно значение другим
Чтобы заменить определенные символы, текст или числа на листе Excel, используйте вкладку "Заменить" диалогового окна Excel Найти и заменить. Подробные шаги приведены ниже.
- Выделите диапазон ячеек, в которых вы хотите заменить текст или числа. Чтобы заменить символы на всем листе, щелкните любую ячейку на активном листе.
- Нажмите сочетание клавиш Ctrl + H, чтобы открыть вкладку "Замена" в диалоговом окне Excel Найти и заменить.
Можно также перейти на вкладку Главная > группу Редактирование и нажать Найти и выбрать > Заменить…
Совет. Если что-то пошло не так и вы получили результат, отличный от ожидаемого, нажмите кнопку Отменить или нажмите Ctrl + Z, чтобы восстановить исходные значения.
Для доступа к дополнительным функциям замены Excel нажмите кнопку "Параметры" в правом углу вкладки Заменить. По сути, они аналогичны параметрам поиска Excel, которые мы только что обсуждали.
Заменить текст или число ничем
Чтобы заменить все вхождения определенного значения ничем, введите символы для поиска в поле Найти, оставьте поле Заменить на пустым и нажмите кнопку Кнопка Заменить все.
Как найти или заменить разрыв строки в Excel
Чтобы заменить разрыв строки пробелом или любым другим разделителем, введите символ разрыва строки в поле Найти что, нажав Ctrl + J . Этот ярлык представляет собой управляющий код ASCII для символа 10 (разрыв строки или перевод строки).
После нажатия Ctrl + J , поле Найти что на первый взгляд будет выглядеть пустым, но при ближайшем рассмотрении вы заметите маленькую мерцающую точку, как на снимке экрана ниже. Введите символ замены в поле Заменить на, например пробел и нажмите Заменить все.
Чтобы заменить какой-либо символ разрывом строки, сделайте наоборот — введите текущий символ в поле Найти, а разрыв строки ( Ctrl + J ) в поле Заменить на.
Как изменить формат ячеек на листе
В первой части этого руководства мы обсуждали, как найти ячейки с определенным форматированием с помощью диалогового окна поиска Excel. Excel Replace позволяет сделать еще один шаг и изменить форматирование всех ячеек на листе или во всей книге.
- Откройте вкладку Заменить диалогового окна Excel "Найти и заменить" и нажмите Параметры
- Рядом с полем Найти нажмите стрелку кнопки Формат, выберите Выбрать формат из ячейки и щелкните любую ячейку. с форматом, который вы хотите изменить.
- Рядом с полем Заменить на нажмите кнопку Формат… и задайте новый формат в диалоговом окне Excel Заменить формат; или нажмите стрелку кнопки Формат, выберите Выбрать формат из ячейки и щелкните любую ячейку с нужным форматом.
- Если вы хотите заменить форматирование всей книги, выберите Книга в поле Внутри. Если вы хотите заменить форматирование только на активном листе, оставьте выбор по умолчанию (Лист).
- Наконец нажмите кнопку Заменить все и проверьте результат.
Примечание. Этот метод изменяет форматы, примененные вручную, он не будет работать для ячеек с условным форматированием.
Excel найти и заменить с помощью подстановочных знаков
Использование подстановочных знаков в критериях поиска может автоматизировать многие задачи поиска и замены в Excel:
- Используйте звездочку (*), чтобы найти любую строку символов. Например, sm* находит "улыбка" и "запах".
- Используйте вопросительный знак (?), чтобы найти любой отдельный символ. Например, gr?y находит "Серый" и "Серый".
Например, чтобы получить список имен, начинающихся с "ad", используйте "ad*" в качестве критерия поиска. Кроме того, имейте в виду, что с параметрами по умолчанию Excel будет искать критерии в любом месте ячейки. В нашем случае будут возвращены все ячейки, содержащие «ad» в любой позиции. Чтобы этого не произошло, нажмите кнопку Параметры и установите флажок Совпадать со всем содержимым ячейки. Это заставит Excel возвращать только значения, начинающиеся с "ad", как показано на снимке экрана ниже.
Как найти и заменить подстановочные знаки в Excel
Если вам нужно найти настоящие звездочки или вопросительные знаки на листе Excel, введите перед ними символ тильды (~). Например, чтобы найти ячейки, содержащие звездочки, введите ~* в поле Найти. Чтобы найти ячейки, содержащие вопросительные знаки, используйте ~? в качестве критерия поиска.
Вот как вы можете заменить все вопросительные знаки (?) на листе другим значением (числом 1 в этом примере):
Как видите, Excel успешно находит и заменяет подстановочные знаки как в тексте, так и в числовых значениях.
Сочетания клавиш для поиска и замены в Excel
Если вы внимательно следили за предыдущими разделами этого руководства, вы, возможно, заметили, что Excel предоставляет 2 разных способа взаимодействия с командами Найти и заменить — с помощью кнопок на ленте и с помощью кнопки сочетания клавиш.
Ниже приведен краткий обзор того, что вы уже изучили, и еще несколько сокращений, которые могут сэкономить вам еще несколько секунд.
- Ctrl+F — сочетание клавиш Excel для поиска, открывающее вкладку Найти на вкладке Найти и заменить
- Ctrl+H — сочетание клавиш «Заменить» в Excel, которое открывает вкладку Заменить в разделе Найти и заменить
- Ctrl+Shift+F4 — найти предыдущее вхождение искомого значения.
- Shift+F4 — найти следующее вхождение искомого значения.
- Ctrl+J — найти или заменить разрыв строки.
Поиск и замена во всех открытых книгах
Как вы только что видели, функция «Найти и заменить» в Excel предоставляет множество полезных опций. Однако он может выполнять поиск только в одной книге за раз. Чтобы найти и заменить во всех открытых книгах, вы можете использовать надстройку Advanced Find and Replace от Ablebits.
Следующие Расширенные функции поиска и замены делают поиск в Excel еще более эффективным:
- Найти и заменить во всех открытых книгах или выбранных книгах и листах.
- Одновременный поиск по значениям, формулам, гиперссылкам и комментариям.
- Экспорт результатов поиска в новую книгу одним щелчком мыши.
Чтобы запустить надстройку «Расширенный поиск и замена», щелкните ее значок на ленте Excel, которая находится на вкладке Утилиты Ablebits > в группе Поиск. Кроме того, вы можете нажать Ctrl + Alt + F или даже настроить его на открытие знакомым сочетанием клавиш Ctrl + F.
Откроется панель "Расширенный поиск и замена", и вы выполните следующие действия:
- Введите символы (текст или число) для поиска в поле Найти
- Выберите, в каких книгах и листах вы хотите выполнить поиск. По умолчанию выбираются все листы во всех открытых книгах.
- Выберите типы данных для поиска: значения, формулы, комментарии или гиперссылки. По умолчанию выбраны все типы данных.
Кроме того, у вас есть следующие варианты:
- Выберите параметр Учитывать регистр, чтобы искать данные с учетом регистра.
- Установите флажок Вся ячейка, чтобы выполнить поиск с точным и полным соответствием, т. е. найти ячейки, содержащие только те символы, которые вы ввели в поле Найти
Нажмите кнопку «Найти все», и вы увидите список найденных записей на вкладке «Результаты поиска». И теперь вы можете заменить все или выбранные вхождения каким-либо другим значением или экспортировать найденные ячейки, строки или столбцы в новую книгу.
Если вы хотите попробовать расширенный поиск и замену на листах Excel, вы можете загрузить полнофункциональную пробную версию.
Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе. В нашем текстовом руководстве мы остановимся на функциях Excel ПОИСК, НАЙТИ и ЗАМЕНИТЬ, поэтому, пожалуйста, следите за этим пространством.
Читайте также: