Поиск и замена данных в Excel
Введение
Excel — один из самых мощных и эффективных приспособлений, который создан корпорацией Microsoft, программный продукт разрешает пользователю утилизировать в работе с текстом и таблицами специализированные инструменты, позволяющие быстро находить необходимые элементы, которые впоследствии нужно поменять. Инструментарий приложения помогает в автоматическом режиме просматривать по несколько листов документа или даже всю рабочую книгу. Работая в Excel, юзеру нет необходимости вручную искать требуемый фрагмент текста, чтобы впоследствии заменить его.
Иногда пользователям необходимо заменить одинаковые данные по всему документу
Чтобы активировать диалоговое окно, позволяющее находить и менять, например, запятую на точку, предварительно нужно обозначить диапазон ячеек. В выделенном фрагменте будет производиться непосредственный поиск. Юзер должен знать, что в случае выделения одной-единственной ячейки, инструмент программы проверит на совпадение с введённым элементом весь лист. Чтобы воспользоваться особенностями программы Excel и сделать замену (например, поменять запятую на точку), в разделе «Главная» нужно перейти в подкатегорию «Редактирование» и нажать «Найти и выделить». Вызвать диалоговое окно поможет совместное нажатие на клавиши Ctrl и F.
Открытое перед юзером окно позволяет эксплуатировать в работе команды: «Найти» (поиск фрагмента текста) и «Заменить». Перейти в расширенный режим просмотра файла поможет кнопка «Параметры».
Инструменты поиска
Итак, если перед юзером стоит необходимость определить фрагмент текста в документе, например, точку или запятую, её нужно ввести в поле поиска. Далее, нужно указать параметры, в соответствии с которыми будет производиться обнаружение символа.
В первую очередь необходимо указать место, которое можно задать в категории «Искать», в зависимости от указанного диапазона поиск может производиться как на определённом листе, так и во всей книге.
Кроме всего прочего, в разделе «Просматривать» можно указать инструменту приложения производить просмотр документа исключительно по строкам или столбцам.
Область просмотра может быть задана как в формулах, так и в значениях или примечаниях.
Перечень различных особенностей настолько широк, что система может искать информацию с учётом регистра и просмотра всего содержимого конкретной ячейки или некоторого фрагмента.
Юзер должен быть готовым к тому, что посредством поиска будет просмотрен весь указанный диапазон ячеек, программа последовательно найдёт и выделит каждое совпадение. Просмотреть всё найденное можно поэтапно, передвигаясь по ячейкам с нужным содержимым за один клик. «Найти всё» позволит отобразить в документе список, в котором содержатся адреса абсолютно всех совпадений.
Инструменты замены
В программе Excel достаточно просто сделать замену одного символа на другой (поменять запятую на точку), для этого следует открыть раздел замены в категории «Найти и заменить». Посредством поиска в окно ввести запятую (символ или элемент, который впоследствии будет изменён). В разделе «Заменить» ввести точку или букву, имеется ввиду тот текст, который появится на месте изменённого.
Символы поменяются после нажатия на кнопку «Далее». Инструментарий софта покажет пользователю ячейки, в которых необходимо будет произвести замещение. По клику Excel сможет пройти весь документ, поэтапно выделяя каждое совпадение. В случае если в каком-то месте нет нужды менять символы (запятую на точку), нужно пропустить замещение, попросту кликнув на «Далее». Если юзеру не нужно проверять каждое совпадение, можно прибегнуть к помощи кнопки «Заменить всё». Отменить действие поможет совместное нажатие на клавиши Ctrl и Z.
Форматы приложения
Кроме данных, которые могут меняться местами, инструментарий программного продукта, предложенного фирмой Microsoft, позволяет определить в тексте места, которые имеют нестандартное форматирование. Естественно, посредством набора команд можно одно форматирование сделать совершенно другим. К примеру, данные с курсивом следует дополнительно отметить жирным шрифтом. Для этого в категории «Главная»необходимо определить подразделы «Редактирование» и «Найти и выделить», способные произвести необходимую замену.
Заключение
Инструментарий Microsoft Excel позволяет максимально быстро и эффективно производить замену одних символов на другие, пользователю нет нужды искать каждое совпадение вручную, посредством всего нескольких команд можно с лёгкостью пройти по всем совпадениям и изменить данные.
VBA найти и заменить — voxt.ru
Функция «Найти и заменить» в VBA
Если ваша работа в Excel связана с рутинными задачами по поиску чего-то и замене этого чем-то, то вам нужна эта статья любой ценой. Потому что после прочтения этой статьи вы, вероятно, сэкономите 80% своего времени, изучив эту технику кодирования VBA. Найти и заменить в ExcelНайти и заменить в Excel«Найти и заменить» — это функция Excel, которая позволяет искать любой текст, числовой символ или специальный символ не только на текущем листе, но и во всей книге. Ctrl+F — это сочетание клавиш для поиска, а Ctrl+H — это сочетание клавиш для поиска и замены.читать далее — часто используемый инструмент, и мы можем реализовать то же самое с помощью VBA. В нашей предыдущей статье «Поиск VBA» мы показали вам, как использовать метод FIND в VBA. В этой статье мы покажем вам, как использовать метод VBA «Найти и заменить».
Следуйте статье, чтобы изучить эту технику.
Вы можете использовать это изображение на своем веб-сайте, в шаблонах и т. д. Пожалуйста, предоставьте нам ссылку на авторствоКак предоставить атрибуцию?Ссылка на статью должна быть гиперссылкой
Например:
Источник: VBA «Найти и заменить» (wallstreetmojo.com)
VBA найти и заменить синтаксис
Чтобы использовать метод «Найти и заменить» в VBA, мы должны выполнить следующие шаги. Во-первых, мы выбрали диапазон ячеек, поэтому укажите диапазон ячеек, нажав используя объект RANGE в VBAИспользование объекта RANGE в VBAДиапазон — это свойство в VBA, которое помогает указать конкретную ячейку, диапазон ячеек, строку, столбец или трехмерный диапазон. В контексте рабочего листа Excel объект диапазона VBA включает одну или несколько ячеек, распределенных по различным строкам и столбцам.читать далее.
Теперь поставьте точку (.), чтобы увидеть список IntelliSense.
Выберите метод замены из списка.
Мы видим огромный список параметров метода Replace. Теперь мы увидим объяснение каждого параметра ниже.
- Что: Это не что иное, как то, что нам нужно найти, чтобы заменить значение.
- Замена: С найденным значением, каким должно быть новое значение для замены.
- Посмотри на: Это говорит о том, хотим ли мы смотреть на весь контент или только на его часть. Здесь мы можем указать два параметра: «xlWhole» и «xlPart».
- Порядок поиска: Это касается порядка поиска, строк или столбцов. Здесь мы можем указать два параметра «xlByRows» и «xlByColumns».
- Учитывать регистр: Содержимое, которое мы ищем, чувствительно к регистру или нет. Если аргумент с учетом регистра равен TRUE или FALSE.
- Формат поиска: Мы также можем искать содержимое по форматированию значения, которое мы ищем.
- Заменить формат: Мы также можем заменить один формат другим форматом.
Примеры поиска и замены VBA в Excel
Ниже приведены некоторые примеры метода поиска и замены Excel VBA.
Вы можете скачать этот шаблон Excel для поиска и замены VBA здесь — VBA найти и заменить шаблон Excel
Пример № 1 — VBA найти и заменить слово
Хорошо, давайте посмотрим на следующий пример, чтобы понять метод поиска и замены VBA. Взгляните на следующие данные.
Шаг 1: Во-первых, укажите диапазон ячеек, которые мы заменяем. В этом примере Range — от A1 до B15, поэтому код будет Range («A1: B15»).
Код:
Sub Replace_Example1() Range ("A1:B15") End Sub
Шаг 2: Теперь поставьте точку, чтобы увидеть список IntelliSense.
Шаг 3: Выберите метод замены из списка IntelliSense.
Шаг 4: Укажите какой параметр как «сентябрь».
Код:
Range("A1:B15").Replace What:="September"
Шаг 5: Затем «Заменить на параметр» должно быть нашим новым значением, которое мы заменяем, например, «Декабрь».
Код:
Range("A1:D4").Replace What:="September", Replacement:="December"
Хорошо, на данный момент игнорируйте все остальные параметры. Теперь запустите Код VBAКод VBAКод VBA относится к набору инструкций, написанных пользователем на языке программирования приложений Visual Basic в редакторе Visual Basic (VBE) для выполнения определенной задачи.читать далее чтобы увидеть метод замены с помощью VBA.
Так, он заменил весь сентябрь словом «декабрь».
Пример № 2. Замена с учетом регистра
Более продвинутый пример метода поиска и замены VBA будет использовать метод замены с учетом регистра. Для этого примера я создал этот образец данных, как показано на изображении ниже.
У нас есть данные двух ячеек заглавными буквами «HELLO». Везде, где у нас есть заглавная буква «HELLO», ее следует заменить новым словом «Hiii».
Как обычно, напишите код, упомяните, что найти и что заменить в первую очередь.
Код:
Sub Replace_Example2() Range("A1:D4").Replace What:="HELLO", Replacement:="Hiii" End Sub
Теперь для следующего аргумента «Учитывать регистр» напишите условие как ИСТИНА.
Код:
Range("A1:D4").Replace What:="HELLO", Replacement:="Hiii", MatchCase:=True
Теперь запустите код. Он заменит только заглавную букву «HELLO» на «Hiii».
Представьте, что вы не применили Аргумент Match Case в VBAАргумент соответствия регистру в VBAВ VBA функция match используется как функция поиска, и к ней обращается приложение. Метод рабочего листа. Аргументы функции Match аналогичны функции рабочего листа.читать далее, тогда он заменит все «Hello» на «Hiii».
Примечание. Здесь я удалил аргумент Match Case. По умолчанию значение аргумента MATCH CASE равно FALSE.
Как мы видим на изображении выше, он заменил все слова «привет» на «привет».
Таким образом, всякий раз, когда мы хотим использовать критерии MATCH CASE, мы должны применять аргумент как «ИСТИНА», и по умолчанию значение этого аргумента равно «ЛОЖЬ». Таким образом, мы можем использовать метод «НАЙТИ И ЗАМЕНИТЬ», чтобы найти что-то и заменить найденное значение чем-то другим.
УЗНАТЬ БОЛЬШЕ >>
Post Views: 2 076
Похожие записи
Прокрутить вверхКак использовать функцию поиска и замены в Excel: полное руководство (2023)
Перейти к содержимому Как использовать функцию поиска и замены в Excel: полное руководство (2023)Поиск и замена — одна из самых распространенных, но в то же время самых полезных функций Excel.
Поиск по сотням тысяч строк определенно не самый лучший вариант. А заменить что-то внутри этих рядов еще хуже.
Таким образом, функция «Найти и заменить» позволяет найти определенное слово или фразу за считанные секунды независимо от объема данных.
Это, безусловно, одна из самых необходимых функций Excel. Если вы еще не использовали эту функцию, перейдите прямо к приведенному ниже руководству, чтобы узнать все об этом 🧐
Вы также можете загрузить наш образец рабочей тетради для этой статьи здесь, если хотите попрактиковаться.
Содержание
Как пользоваться функцией «Найти и заменить»
Использовать функцию «Найти и заменить» довольно просто. Нажмите CTRL F , чтобы открыть диалоговое окно . Или выберите его в группе Редактирование.
У нас есть следующий пример данных.
Предположим, мы хотим найти Эмили, имя сотрудника, работающего в компании. Для этого мы введем ее имя в диалоговом окне «Найти» следующим образом:
После ввода имени нажмите Enter. Excel выделит ячейку, содержащую результат.
Профессиональный совет!
Обратите внимание, что имя здесь не пишется с заглавной буквы. Это потому, что в функции поиска заглавные буквы не имеют значения 😉
Кнопка Найти далее перемещает вас к следующему экземпляру, содержащему элемент поиска, путем выделения ячейки.
Кнопка Найти все показывает список всех вхождений, в которых встречается искомый элемент. Вот так:
Затем вы можете выбрать любое значение, которое вы хотите, и оно направит вас к ячейке.
Если вы хотите просмотреть все ячейки одновременно, нажмите CTRL A или вручную выберите весь список. Excel выделит все ячейки одновременно:
Заменить данные с помощью функции «Найти и заменить»
Теперь посмотрим, как работает функция замены
Предположим, мы хотим найти в этих данных должность бухгалтера. И замените его на Auditor.
Для этого:
- Выберите вкладку «Заменить» в диалоговом окне «Найти и заменить». Или нажмите CTRL H . Вы также можете открыть его с помощью параметра «Найти и выбрать» в группе «Редактирование».
- Введите поисковый запрос в поле Find what .
- Введите новый термин, которым вы хотите заменить поисковый запрос.
- Нажмите Enter.
Обратите внимание, что мы ввели условия поиска с заглавной буквы. Это потому, что замене нужно найти точную текстовую строку. А затем заменить его каким-либо другим термином.
Каспер Лангманн , специалист по Microsoft Office
Подобно функции поиска в Excel, вкладка «Заменить» также содержит параметры «Заменить далее» и «Заменить все».
Параметр Заменить следующий заменяет каждый поисковый запрос один за другим.
Заменить все заменяет все термины за один раз. И выводит сообщение о том, что все замены произведены.
Вот как вы используете функцию «Найти и заменить». Довольно легко, нет? 😀
Расширенные настройки функции «Найти и заменить»
Теперь, когда вы знаете все основы функции «Найти и заменить», давайте рассмотрим некоторые дополнительные функции, которые она может предложить.
Чтобы открыть расширенные функции функции поиска, нажмите кнопку «Параметры» в диалоговом окне.
Отображает множество вариантов на выбор.
Обратите внимание, что список параметров на вкладке «Найти и заменить» такой же. Единственное отличие состоит в том, что на вкладке «Заменить» вы получаете дополнительный параметр формата для поля замены.
Рассмотрим все эти опции подробно ниже.
Формат: Параметр формата позволяет указать формат элемента поиска. Щелкните параметр «Формат» вверху, чтобы выбрать формат.
Вы также можете выбрать формат для текстовой строки из определенной ячейки. Для этого
- Щелкните стрелку раскрывающегося списка рядом с Формат.
- Удалите существующий формат, нажав «Очистить формат поиска».
- Нажмите «Выбрать формат из ячейки»
Появится курсор с пипеткой.
Просто щелкните ячейку, формат которой вы хотите скопировать, и нажмите Заменить все. Диалоговое окно также показывает *Preview* .
В пределах: Опция в пределах позволяет выполнять поиск определенной текстовой строки по всей книге 😵
Таким образом, даже если вы не помните, где находится определенный фрагмент текста в вашей книге, вы можете просто найти его. Эта функция на самом деле очень полезна, когда вам нужно искать один термин в обширных данных.
Учитывать регистр: Параметр «Учитывать регистр» включает чувствительность к регистру. Это означает, что Excel будет искать определенный термин с точным использованием заглавных букв.
Скажем, мы ищем «hr» в нижнем регистре, а не в верхнем, и ставим галочку в опции «Учитывать регистр».
Нажмите «Найти далее».
Excel покажет предупреждающее сообщение, потому что мы неправильно использовали заглавные буквы.
Если мы напишем тот же поисковый запрос заглавными буквами, Excel выполнит поиск и покажет результат как:
Разве это не удивительно? 🤩
Поиск: Параметр поиска позволяет выбрать порядок поиска.
Если вы хотите искать элемент поиска построчно, выберите «по строке» . И если вы хотите искать столбец за столбцом, выберите «по столбцу» опция.
Совпадение со всем содержимым ячейки : Этот параметр ограничивает поиск и замену поиском только тех символов, которые вы ввели в поле. Если вы даже пропустите период, Excel покажет предупреждающее сообщение.
Скажем, мы ставим галочку на опции « Соответствовать всему содержимому ячейки ». И введите имя « John », тогда как в наборе данных правильное имя « John O. »
Вот что произойдет:
А если мы добавим «О.» в то же поле, результат будет следующим:
Excel возвращает правильный результат. Этот инструмент отлично подходит, если ваша электронная таблица содержит большое количество данных.
Поиск : Параметр поиска позволяет указать тип данных элемента поиска.
Вкладка «Поиск» предлагает три типа, а именно: «Формулы», «Значения» и «Комментарии». Вкладка «Заменить», с другой стороны, использует только один тип данных, то есть формулы.
Если вы хотите искать только формулы, вы можете найти их с помощью параметра «Перейти к специальному» в меню «Найти и выбрать».
Он предлагает множество опций, и вы можете установить опции по своему выбору. Вы также можете использовать его для поиска других предметов.
Просто выберите параметры, и Excel автоматически применит их при следующем поиске.
Вот и все – Теперь что
И Тада! Вот как легко и весело использовать инструменты поиска и замены Excel 🤗
Вы можете не только найти и заменить определенную текстовую строку за считанные секунды, но также указать ее формат, местоположение, порядок и чувствительность к регистру.
Несомненно, функция Excel «Найти и заменить» очень мощная. Но если вы хотите стать мастером Excel, вам нужно еще многому научиться.
Если вы не знакомы с этим программным обеспечением для работы с электронными таблицами, мы предлагаем вам начать изучение встроенных функций Excel; ЕСЛИ, СУММЕСЛИ и ВПР.
Вы можете изучить эти превосходные функции БЕСПЛАТНО в моем 30-минутном курсе Excel, отправленном прямо на ваш почтовый ящик.
Другие ресурсы
Функция «Найти и заменить» в Excel великолепна. Особенно, когда дело доходит до внесения изменений и сужения критериев поиска.
Функция проста для понимания и использования. Но немного попрактиковавшись, вы сможете освоить это и вносить крупномасштабные изменения в свои данные за считанные секунды.
Если вам понравилась эта статья, мы уверены, что вы хотели бы прочитать больше. Некоторые связанные статьи включают функции ПОИСК, НАЙТИ, ЗАМЕНИТЬ и ПОДСТАВИТЬ в Excel.
Часто задаваемые вопросы
Часто задаваемые вопросы
Где найти и заменить в Excel?Вы можете открыть диалоговое окно «Найти и заменить», нажав CTRL + F .
Или вы можете найти его в меню «Найти и выбрать» в группе «Редактирование» на вкладке «Главная». Если вам нужен быстрый доступ к функции замены, вы можете использовать сочетание клавиш CTRL + H .
Как найти и заменить формулами?Если вы предпочитаете использовать формулы для поиска и замены элементов в Excel, попробуйте функцию ПОДСТАВИТЬ. Он используется, когда вы знаете точные символы текста, который нужно заменить. Функция ПОДСТАВИТЬ является хорошей альтернативой Заменить.
Kasper Langmann2023-01-19T12:05:02+00:00 Ссылка для загрузки страницыПоиск и замена в Excel
Функция поиска и замены Excel ищет значение данных и заменяет его другим значением данных. Это значение данных может быть текстовой строкой, числом, датой или специальным символом. Функция «Найти и заменить» может выполнять поиск на листе или в книге, по строкам или столбцам, а также по формулам, значениям или комментариям.
Например, в финансовом отчете Excel текстовую строку «актив» может потребоваться заменить на «активы».
Целью использования функции «Найти и заменить» в Excel является поиск определенной информации в базе данных. Он также позволяет изменять существующие значения данных несколькими щелчками мыши. По умолчанию функция Excel «Найти и заменить» ищет частичное совпадение. Но он также может искать точное совпадение, если выбран параметр «соответствовать всему содержимому ячейки».
Содержание
- Что такое функция поиска и замены в Excel?
- Как получить доступ к функции поиска и замены в Excel?
- Пример № 1. Поиск частичного совпадения в рабочей таблице
- Пример № 2. Поиск частичного совпадения в рабочей книге
- Пример № 3. Поиск точного совпадения в рабочей книге
- Пример № 4. Замена ссылки на диапазон a Формула
- Пример 5. Замена существующего текста, чтобы сделать две строки идентичными
- Пример 6. Замена старого форматирования ячейки
- Пример 7. Поиск комментария на листе
- Ключевые моменты, связанные к функции «Найти и заменить» в Excel
- Часто задаваемые вопросы
- Рекомендуемые статьи
- Как получить доступ к функции поиска и замены в Excel?
Как получить доступ к функции поиска и замены Excel?
Ярлыки для функции «Найти и заменить» в Excel выглядят следующим образом:
- «Ctrl+F» открывает вкладку «Поиск» функции «Найти и заменить».
- «Ctrl+H» открывает вкладку «Замена» функции «Найти и заменить».
Кроме того, параметры «найти» и «заменить» можно выбрать в раскрывающемся списке «найти и выбрать» (группа «редактирование») на вкладке «Главная». Эти параметры открывают вкладки «Найти и заменить» функции «Найти и заменить» в Excel.
Давайте рассмотрим несколько примеров, чтобы понять работу функции «Найти и заменить» в Excel.
Вы можете скачать этот шаблон Excel для поиска и замены здесь — Шаблон Excel для поиска и замены
Пример № 1.
Поиск частичного совпадения на листеНа следующем изображении показаны два рабочих листа с названиями «Янвь» и «Февраль». Эти рабочие листы содержат наборы данных, которые сообщают о продажах (столбец C) различным клиентам (столбец B) в январе и феврале.
Наименования продуктов, себестоимость проданных товаров (COGS), прибыль и регионы также отображаются в столбцах A, D, E и F соответственно. Аналогичный набор данных (с другими номерами) также находится в столбцах с H по M.
Найдите имя «Митчел» в рабочем листе «Ян». Используйте функцию «Найти и заменить» Excel.
Шаги по использованию функции поиска и замены в Excel следующие:
- Перейдите на рабочий лист «Январь». Далее нажмите клавиши «Ctrl+F» вместе. Откроется вкладка «Найти» диалогового окна «Найти и заменить». Это показано на следующем изображении.
Примечание. По умолчанию Excel выполняет поиск на текущем активном листе.
- Введите слово для поиска в поле «Найти». Мы набираем «Митчел».
Обратите внимание, что этот поиск не чувствителен к регистру. Таким образом, даже если рабочий лист содержит «MITCHEL», он будет отображаться в результатах поиска. - Нажмите либо клавишу «Ввод», либо опцию «Найти далее». Выбирается первое вхождение имени «Дэниел Митчел». Итак, выбрана ячейка B7, как показано на следующем изображении. Нажимая «Найти далее» снова и снова, вы выбираете последующие вхождения «Митчел».
Обратите внимание: несмотря на то, что мы ввели «Митчел» в поле «Найти», Excel выбрал имя «Дэниел Митчел». Это называется частичным совпадением. Это означает, что если есть какие-либо дополнительные слова, предшествующие или следующие за искомым значением, в результате возвращается вся строка, содержащая искомое значение.
- Нажмите «найти все», чтобы увидеть все строки, содержащие «Митчел». Результаты показаны на следующем изображении. В этих результатах отображается следующая информация:
• В столбце «книга» отображается имя рабочей книги, содержащей искомое значение.
• В столбце «лист» отображается имя рабочего листа, содержащего искомое значение.
• В столбце «ячейка» отображается ссылка на ячейки, содержащие искомое значение.
• В столбце «значение» отображается вся строка, содержащая искомое значение.Обратите внимание, что в нижней части диалогового окна Excel отображает количество ячеек, содержащих искомое значение. В этом случае на листе «Ян» есть 8 ячеек, которые содержат либо «Митчел», либо «Дэниел Митчел».
Разница между «найти все» и «найти следующее»: Опция «найти все» показывает все вхождения искомого значения. Кроме того, щелчок по любой из записей, показанных этой опцией, переводит пользователя в соответствующую ячейку.
Опция «Найти далее» помогает просматривать различные ячейки, содержащие искомое значение. Щелчок «Найти далее» в первый раз выбирает первое вхождение искомого значения. Аналогичным образом, нажатие «найти далее» во второй раз выбирает второе вхождение искомого значения и так далее.
Пример № 2. Поиск частичного совпадения в рабочей книге
Работая с набором данных примера № 1, найдите имя «Митчел» во всей рабочей книге. Используйте функцию «Найти и заменить» Excel.
Шаги для поиска указанной строки во всей рабочей книге перечислены ниже:
Шаг 1: Нажмите одновременно клавиши «Ctrl+F». Откроется вкладка «Найти» окна «Найти и заменить». Введите имя «Митчел» в поле «Найти».
Затем нажмите «Параметры», показанные в черном поле на следующем изображении.
Шаг 2: При нажатии кнопки «Параметры» открывается диалоговое окно «Найти и заменить». Щелкните раскрывающийся список рядом с параметром «внутри» и выберите «книга».
Выбор показан на следующем рисунке.
Шаг 3: Нажмите «найти все», чтобы увидеть все результаты. Результаты показаны на следующем изображении. На этот раз строка «Митчел» была найдена в 16 ячейках рабочей тетради.
Щелчком по любой из записей будет выбрана соответствующая ячейка.
Пример № 3. Поиск точного совпадения в рабочей книге
Работая с набором данных примера № 1, найдите точное совпадение слова «Митчел» во всей рабочей книге. Используйте функцию «Найти и заменить» Excel.
Шаги для поиска точного имени во всей книге перечислены ниже:
Шаг 1: Нажмите клавиши «Ctrl+F». На вкладке «Найти» окна «Найти и заменить» выполните следующие задачи:
- Нажмите «Параметры», чтобы развернуть окно «Найти и заменить».
- Введите имя «Митчел» в поле «Найти».
- Выберите «рабочая книга» в поле рядом с «внутри».
- Установите флажок «соответствовать всему содержимому ячейки».
- Оставьте для параметров «поиск» и «поиск» значения по умолчанию, т.е. «по строкам» и «по формулам» соответственно.
Название поля «Найти что» и варианты выбора показаны на следующем рисунке.
Примечание: Если выбран параметр «соответствовать всему содержимому ячейки», функция Excel «Найти и заменить» ищет именно те значения данных, которые были введены в поле «Найти».
Шаг 2: Нажмите «найти все», чтобы просмотреть все точные совпадения имени «Митчел». В текущей книге 8 ячеек содержат точное имя.
В результатах (показанных на следующем рисунке) отображаются ссылки на ячейки, точное значение и имена рабочего листа и рабочей книги.
Пример № 4. Замена ссылки на диапазон в формуле
На следующем рисунке показаны имена (столбец A), оклады в год (в долларах в столбце B), оклады в месяц (в долларах в столбце C) и отделы. (столбец D) некоторых сотрудников организации.
Общая годовая заработная плата показана в ячейке G3. В этой сумме неправильно учитывается диапазон B2:B10 вместо диапазона B2:B22. Исправьте диапазон формул в ячейках G3 и h4, чтобы общая годовая заработная плата рассчитывалась точно. Используйте функцию «Найти и заменить» Excel.
Действия по редактированию формулы с помощью функции «Найти и заменить» в Excel перечислены ниже:
Шаг 1: Скопируйте функцию СУММ в ExcelФункция СУММ в Excel складывает числовые значения в диапазоне ячеек. Будучи отнесенной к категории функции «Математика и тригонометрия», она вводится путем ввода «= СУММ», за которым следуют значения для суммирования. Значения, предоставляемые функции, могут быть числами, ссылками на ячейки или диапазонами. Подробнее формула ячейки G3. Для этого выполните данные задания:
- Выберите ячейку G3. В строке формул появится формула [=СУММ(B2:B10)].
- Скопируйте эту формулу, нажав одновременно клавиши «Ctrl+C».
- Нажмите клавишу Escape (Esc), когда формула будет скопирована. Это поможет выйти из строки формул.
Можно также скопировать формулу ячейки h4, нажав клавиши «Ctrl+C». Затем нажмите «Ctrl + H».
Шаг 2: Откроется вкладка «Заменить» окна «Найти и заменить». Вставьте скопированную формулу в поле «Найти что», как показано на следующем рисунке. Чтобы вставить формулу, нажмите одновременно клавиши «Ctrl+V».
Примечание: Вместо копирования и вставки можно ввести формулу непосредственно в поле «Найти».
Шаг 3: Введите правильную формулу в поле «заменить на». Итак, введите формулу «=СУММ(B2:B22)» без начальных и конечных двойных кавычек. Это показано на следующем изображении.
Далее нажмите «заменить все».
Шаг 4: Нажмите «ОК» в сообщении с указанием количества произведенных замен. Формулы ячеек G3 и h4 меняются с «=СУММ(B2:B10)» на «=СУММ(B2:B22)». В результате общая заработная плата в год меняется.
Новый вывод показан в красной рамке на следующем изображении. Следовательно, общая заработная плата, выплачиваемая данному 21 сотруднику, составляет 11 179 954 доллара в год.
Пример № 5. Замените существующий текст, чтобы сделать две строки идентичными
Имеется два изображения с названиями «изображение 1» и «изображение 2». Оба изображения находятся на разных листах. Далее приводится следующая информация:
- На рисунке 1 показаны код продукта (столбец А) и цены (в долларах в столбце В) 21 продукта многонациональной организации.
- На изображении 2 показан код продукта (столбец A) и пустой столбец (столбец B) для цен на 21 продукт.
Обратите внимание, что коды продуктов обоих изображений состоят из нескольких букв, предшествующих числу. Буквы на обоих изображениях разные, но последующие цифры одинаковые.
Сделайте код продукта идентичным на обоих изображениях, используя функцию «Найти и заменить» в Excel. Это поможет получить цены «изображения 1» в «изображении 2» (столбец B) с помощью функции ВПРФункция ВПР ExcelФункция ВПР ищет определенное значение и возвращает соответствующее совпадение на основе уникального идентификатора. Уникальный идентификатор однозначно связан со всеми записями базы данных. Например, идентификатор сотрудника, номер списка учащихся, контактный номер клиента, адрес электронной почты продавца и т. д. являются уникальными идентификаторами. читать далее.
Изображение 1
Изображение 2
Шаги, чтобы сделать код продукта идентичным с помощью функции поиска и замены Excel, перечислены ниже:
Шаг 2: Введите «pdc» в поле «Найти». Введите «prdct» в поле «заменить на». Далее нажмите «заменить все». Появится сообщение о количестве сделанных замен. Нажмите «ОК», чтобы продолжить.
Записи и выводы показаны на следующем рисунке. Теперь можно легко применить функцию ВПР для получения цен на этом изображении. Если бы замены не были сделаны, функция ВПР не могла быть применена.
Разница между «заменить все» и «заменить»: Опция «заменить все» заменяет все вхождения искомого значения за один раз. Напротив, опция «заменить» заменяет одно вхождение за раз.
Поэтому используйте «заменить», если не уверены, какое вхождение нужно заменить. Используйте «заменить все», когда необходимо заменить все вхождения искомого значения.
Пример № 6. Замена старого форматирования ячейки
На следующем рисунке показаны некоторые отделы организации. Ячейка A4 выделена серым цветом, а ячейки B5, C2 и D5 — синим. Мы хотим, чтобы все ячейки, содержащие «маркетинг», были одного цвета.
Поэтому замените серый цвет ячейки A4 на синий. Используйте функцию «Найти и заменить» Excel.
Шаги по изменению форматирования с помощью функции «Найти и заменить» в Excel перечислены ниже:
Шаг 1: Выберите набор данных, форматирование которого необходимо изменить. Мы выбрали диапазон A2:D6.
Далее нажмите клавиши «Ctrl+H», чтобы открыть вкладку «Заменить» окна «Найти и заменить». На этой вкладке нажмите «Параметры», показанные справа.
Весь этот шаг показан на следующем рисунке.
Шаг 2: При нажатии кнопки «Параметры» открывается окно «Найти и заменить». Щелкните стрелку раскрывающегося списка первого параметра «Формат» (справа от поля «Найти»). Затем выберите «выбрать формат из ячейки».
Выбор показан в черном поле на следующем изображении.
Шаг 3: Выберите ячейку, форматирование которой необходимо изменить. Выбираем ячейку А4. В результате «формат не установлен» слева от «формата» меняется на «предварительный просмотр». Этот параметр «предварительного просмотра» отражает цвет ячейки A4, как показано на следующем рисунке.
Примечание: Параметр «предварительный просмотр» следующего изображения и ячейка A4 первого изображения (в начале этого примера) показывают небольшое изменение цвета. Не обращайте внимания на это изменение, которое может быть связано с тем, что для создания изображений используются разные версии Excel.
Шаг 4: Щелкните стрелку раскрывающегося списка второго параметра «формат» (справа от поля «заменить на»). Выберите «выбрать формат из ячейки» и щелкните любую из синих ячеек (B5, C2 или D5). Следовательно, опция «предварительный просмотр» отображается синим цветом, как показано на следующем изображении.
Далее нажмите «заменить все».
Шаг 5: Excel показывает количество произведенных замен. Нажмите «ОК», чтобы продолжить. Результат показан на следующем изображении.
Следовательно, ячейка A4 также окрашена в синий цвет. Таким образом, форматирование ячейки может быть найдено и заменено. Итак, мы нашли серый цвет и заменили его синим цветом. Теперь все ячейки, содержащие «маркетинг», окрашены в один цвет.
Примечание: В качестве альтернативы можно выбрать параметр «формат» в обоих раскрывающихся списках «формат». После этого на соответствующих вкладках «заливка» выберите цвет для поиска и цвет для замены.
Пример № 7. Поиск комментария на листе
На следующем изображении представлены два набора данных (диапазон A1:D11 и диапазон A13:D23), которые показывают валовые продажи (столбец B), себестоимость проданных товаров (COGS в столбце C). ) и прибыль (столбец D) от десяти продуктов организации. Далее приводится следующая информация:
- Оба набора данных относятся к разным периодам времени. Цифры в скобках в столбце D — это потери, понесенные организацией.
- В обоих наборах данных каждая ячейка столбца D содержит комментарииКомментарии В ExcelВ Excel вставка комментариев — это функция, используемая для обмена советами или подробностями с разными пользователями, работающими в одной электронной таблице. Вы можете щелкнуть правой кнопкой мыши нужную ячейку, нажать «Вставить комментарий» и ввести комментарий, используя сочетание клавиш, например, Shift + F2, или щелкнуть вкладку «Рецензирование» и выбрать «Новый комментарий». читать далее. На это указывают маленькие красные треугольники в верхней правой части каждой ячейки.
- Существует три типа комментариев, а именно: «без комиссии», «комиссия @ 5%» и «комиссия @ 10%».
Найдите комментарий «без комиссии» на текущем листе, который был переименован в «комментарий». Используйте функцию «Найти и заменить» Excel.
Шаги для поиска определенного комментария с помощью функции «Найти и заменить» в Excel перечислены ниже:
Шаг 1: Откройте вкладку «Найти» диалогового окна «Найти и заменить». Для этого нажмите одновременно клавиши «Ctrl+F». Далее нажмите «Параметры». В этом окне сделайте следующие выборы:
- Выберите «лист» и «по строкам» в параметрах «внутри» и «поиск». Это выбор Excel по умолчанию.
- Выберите опцию «комментарии» из раскрывающегося списка «заглянуть».
Выбор второго указателя показан в черном поле на следующем изображении.
Шаг 2: В поле «Найти что» введите строку комментария, который необходимо найти. Мы ввели «без комиссии», как показано на следующем изображении.
Далее нажмите «найти все».
Шаг 3: Результаты поиска показаны на следующем изображении. Обратите внимание, что 13 ячеек (см. нижнюю часть изображения) содержат комментарий «без комиссии».
Ссылки на ячейки, содержащие конкретный комментарий (без комиссии), отображаются в столбце «ячейка». Формулы (столбца D) и имена рабочей книги и рабочего листа отображаются в столбцах «формула», «книга» и «лист» соответственно.
Если щелкнуть любую запись этих результатов, будет выбрана ячейка, содержащая комментарий.
Важные моменты, связанные с функцией «Найти и заменить» в Excel, перечислены ниже:
- Поиск можно ограничить определенным диапазоном рабочего листа. Для такого ограничения выберите диапазон до открытия вкладки «Найти» окна «Найти и заменить». Если диапазон не выбран, Excel выполняет поиск на текущем активном листе. Если искомое значение не может быть найдено в выбранном диапазоне или на активном рабочем листе, отображается соответствующее сообщение.
- Замены также могут быть ограничены определенной областью рабочего листа. Для этого выберите область (или диапазон) до открытия вкладки «Заменить» окна «Найти и заменить». Если диапазон не выбран, Excel производит замену на всем активном листе.
- Функция Excel «Найти и заменить» может заменить существующее форматирование ячеек новым форматированием. Для этого используйте два раскрывающихся списка «Формат» на вкладке «Заменить».
- Значение поиска можно найти на защищенном листе, но нельзя произвести замену. Для замены сначала снимите защиту с листа.
Часто задаваемые вопросы
1. Определите функцию «Найти и заменить» и укажите, как она используется в Excel.
Функция «Найти и заменить» в Excel помогает найти значение данных и заменить его другим значением данных. Необходимость поиска значения данных возникает, когда в базе данных необходимо найти определенную информацию. Необходимость замены значения данных возникает, когда существующая информация требует изменения.
Функцию «Найти и заменить» (или окно) Excel можно использовать одним из следующих способов:
• Используйте вкладку «Найти», когда необходимо найти значение данных. Укажите значение данных в поле «найти что» и нажмите «найти далее» или «найти все» для поиска.
• Используйте вкладку «Заменить», когда нужно заменить одно значение данных другим. Укажите значение для поиска и значение для замены в полях «найти» и «заменить на» соответственно. Нажмите «заменить» или «заменить все», чтобы произвести замену.
Примечание: Нажмите кнопку «Параметры», чтобы просмотреть дополнительные функции окна «Найти и заменить» Excel.
2. Как использовать функцию «Найти и заменить» с подстановочными знаками в Excel?
Функцию «Найти и заменить» можно использовать с подстановочными знаками, такими как звездочка (*), вопросительный знак (?) и тильда (~), следующими способами:
• Звездочка ищет любое количество персонажи. Например, «ro*» найдет строки rose, product, root, metro, rhinoceros и т. д. Таким образом, слова, содержащие «ро» в любом месте строки, будут возвращены в результатах поиска.
• Знак вопроса ищет один символ строки. Например, «s?m» найдет строки простота, выборка, сходство, расцвет, хлопотно и так далее. Таким образом, в результатах поиска возвращаются слова, имеющие любой символ между «s» и «m» (расположенные в любом месте строки).
• Тильда ищет звездочку, вопросительный знак или тильду в строках. Например, «~?» найдете «счастливый?», «веселый», «?радостный», «веселый?» и так далее. Таким образом, слова со знаком вопроса в любом месте строки возвращаются в результатах поиска. Аналогично, «~*» и «~~» ищут слова, содержащие звездочку или тильду в любом месте строки.
Подстановочные знаки, указанные в предыдущих указателях [например, «ro* », «s?m», «~?», «~ *» или «~~»] должны быть введены в поле «найти что». » коробка. Значение, которое необходимо заменить, необходимо ввести в поле «заменить на».
Примечание: Чтобы найти точное совпадение, выберите параметр «соответствовать всему содержимому ячейки».
3. Как функция «Найти и заменить» используется для следующих задач Excel?
1. Заменить значение ничем
2. Назначение формата определенным ячейкам
1. Ниже перечислены шаги по замене значения ничем:
a. Откройте вкладку «Заменить», нажав клавиши «Ctrl+H».
б. Введите значение, которое нужно заменить ничем, в поле «Найти».
с. Оставьте поле «заменить на» пустым.
д. Нажмите кнопку «заменить все».
Все вхождения значения, введенного в поле «найти», удаляются.
2. Шаги для назначения формата определенным ячейкам перечислены ниже:
а. Откройте вкладку «Заменить», нажав клавиши «Ctrl+H».
б. В поле «Найти что» введите текстовую строку для форматирования.
с. В поле «заменить на» введите ту же текстовую строку, что и на предыдущем шаге.
д. Нажмите «Параметры». Раскроется окно «Найти и заменить».
эл. Щелкните раскрывающийся список «Формат» справа от «Заменить на». Выберите опцию «Формат». Откроется окно «заменить формат».
ф. Назначьте нужное форматирование на вкладках «выравнивание», «шрифт», «граница» и «заливка».
г. Нажмите «ОК» в окне «заменить формат». Нажмите «заменить все» в окне «найти и заменить». Нажмите «ОК» в сообщении о количестве сделанных замен.
Новое форматирование будет присвоено ячейкам, содержащим строку «найти что». Обратите внимание, что замены значений данных производиться не будут. Это связано с тем, что текст полей «найти что» и «заменить на» одинаковый. Итак, происходит только изменение форматирования конкретных ячеек.
Рекомендуемые статьи
Это руководство по поиску и замене в Excel. Здесь мы обсуждаем, как найти и заменить в Excel с помощью сочетаний клавиш, а также практические примеры и загружаемые шаблоны Excel. Вы также можете просмотреть эти полезные функции Excel:
- Как найти и выбрать в Excel? Как найти и выбрать в Excel? Найти и выбрать в Excel — это функция, доступная на вкладке «Главная» Excel, которая облегчает пользователю быстро обнаруживать конкретный текст или значение в заданных данных. Горячая клавиша для мгновенного использования этой функции — Ctrl+F.Подробнее
- FIND VBA FunctionFIND VBA FunctionVBA Find дает точное совпадение аргумента.