Light-electric.com

IT Журнал
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Команда заменить в excel

Поиск и замена в Excel

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

При работе с большим количеством данных в Excel, иногда достаточно трудно отыскать какую-то конкретную информацию. И, как правило, такой поиск занимает очень много времени. Excel предлагает прекрасный инструмент поиска. Вы можете с легкостью отыскать любую нужную информацию в рабочей книге Excel с помощью команды Найти, которая к тому же позволяет еще и изменить данные с помощью инструмента Найти и заменить.

Поиск данных в ячейках Excel

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

Если перед использованием команды Найти Вы выделите одну ячейку, то Excel будет выполнять поиск во всем листе. А если диапазон ячеек, то только внутри этого диапазона

  1. На вкладке Главная воспользуйтесь командой Найти и выделить, а затем из раскрывающегося списка выберите пункт Найти.
  2. Появится диалоговое окно Найти и заменить. Введите данные, которые необходимо отыскать. В нашем примере мы введем имя сотрудника.
  3. Нажмите Найти далее. Если данные присутствуют на листе, то они будут выделены.
  4. Если Вы еще раз нажмете на кнопку Найти далее, то увидите следующий вариант поиска. Вы также можете выбрать Найти все, что бы увидеть все варианты, которые для Вас отыскал Excel.
  5. Закончив поиск, воспользуйтесь кнопкой Закрыть, чтобы выйти из диалогового окна Найти и Заменить.

Вы можете получить доступ к команде Найти с помощью комбинации клавиш Ctrl+F на клавиатуре.

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

Замена содержимого ячейки в Excel

Бывают случаи, когда допущена ошибка, повторяющаяся во всей книге Excel. Например, неправильно написано чье-то имя или необходимо изменить определенное слово или фразу на другую. Вы можете воспользоваться инструментом Найти и заменить, чтобы быстро внести исправления. В нашем примере мы воспользуемся командой Заменить, чтобы исправить список адресов электронной почты.

  1. На вкладке Главная нажмите команду Найти и выделить, а затем из раскрывающегося списка выберите пункт Заменить.
  2. Появится диалоговое окно Найти и заменить. Введите текст, который Вы ищете в поле Найти.
  3. Введите текст, на который требуется заменить найденный, в поле Заменить на. А затем нажмите Найти далее.
  4. Если значение будет найдено, то содержащая его ячейка будет выделена.
  5. Посмотрите на текст и убедитесь, что Вы согласны заменить его.
  6. Если согласны, тогда выберите одну из опций замены:
    • Заменить: исправляет по одному значению зараз.
    • Заменить все: исправляет все варианты искомого текста в книге. В нашем примере мы воспользуемся этой опцией для экономии времени.

  • Появится диалоговое окно, подтверждающее количество замен, которые будут сделаны. Нажмите ОК для продолжения.
  • Содержимое ячеек будет заменено.
  • Закончив, нажмите Закрыть, чтобы выйти из диалогового окна Найти и заменить.
  • Функция ЗАМЕНИТЬ() в EXCEL

    Функция ЗАМЕНИТЬ( ) , английский вариант REPLACE(), замещает указанную часть знаков текстовой строки другой строкой текста. «Указанную часть знаков» означает, что нужно указать начальную позицию и длину заменяемой части строки. Функция используется редко, но имеет плюс: позволяет легко вставить в указанную позицию строки новый текст.

    Синтаксис функции

    ЗАМЕНИТЬ ( исходный_текст ; нач_поз ; число_знаков ; новый_текст )

    Исходный_текст — текст, в котором требуется заменить некоторые знаки. Нач_поз — позиция знака в Исходном_тексте , начиная с которой знаки заменяются текстом новый_текст . Число_знаков — число знаков в Исходном_тексте , которые заменяются текстом новый_текст . Новый_текст — текст, который заменяет знаки в Исходном_тексте .

    Функция ЗАМЕНИТЬ() vs ПОДСТАВИТЬ()

    Функция ПОДСТАВИТЬ() используется, когда нужно заменить определенный текст в текстовой строке; функция ЗАМЕНИТЬ() используется, когда нужно заменить любой текст начиная с определенной позиции.

    При замене определенного текста функцию ЗАМЕНИТЬ() использовать неудобно. Гораздо удобнее воспользоваться функцией ПОДСТАВИТЬ() .

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

    =ЗАМЕНИТЬ(A2;10;6;»февраль») =ПОДСТАВИТЬ(A2; «январь»;»февраль»)

    т.е. для функции ЗАМЕНИТЬ() потребовалось вычислить начальную позицию слова январь (10) и его длину (6). Это не удобно, функция ПОДСТАВИТЬ() справляется с задачей гораздо проще.

    Кроме того, функция ЗАМЕНИТЬ() заменяет по понятным причинам только одно вхождение строки, функция ПОДСТАВИТЬ() может заменить все вхождения или только первое, только второе и т.д. Поясним на примере. Пусть в ячейке А2 введена строка Продажи (январь), прибыль (январь) . Запишем формулы: =ЗАМЕНИТЬ(A2;10;6;»февраль») =ПОДСТАВИТЬ(A2; «январь»;»февраль») получим в первом случае строку Продажи (февраль), прибыль (январь) , во втором — Продажи (февраль), прибыль (февраль) . Записав формулу =ПОДСТАВИТЬ(A2; «январь»;»февраль»;2) получим строку Продажи (январь), прибыль (февраль) .

    Кроме того, функция ПОДСТАВИТЬ() чувствительна к РЕгиСТру. Записав =ПОДСТАВИТЬ(A2; «ЯНВАРЬ»;»февраль») получим строку без изменений Продажи (январь), прибыль (январь) , т.к. для функции ПОДСТАВИТЬ() «ЯНВАРЬ» не тоже самое, что «январь».

    Использование функции для вставки нового текста в строку

    Функцию ЗАМЕНИТЬ() удобно использовать для вставки в строку нового текста. Например, имеется перечень артикулов товаров вида » ID-567(ASD) «, необходимо перед текстом ASD вставить новый текст Micro , чтобы получилось » ID-567(MicroASD) «. Для этого напишем простую формулу: =ЗАМЕНИТЬ(A2;8;0;»Micro»).

    Замена символов в Microsoft Excel

    Бывают ситуации, когда в документе нужно заменить один символ (или группу символов) на другой. Причин может быть множество, начиная от банальной ошибки, и, заканчивая переделкой шаблона или удалением пробелов. Давайте выясним, как быстро заменить символы в программе Microsoft Excel.

    Способы замены символов в Excel

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

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

    Поиск с заменой

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

      Кликаем по кнопке «Найти и выделить», которая располагается во вкладке «Главная» в блоке настроек «Редактирование». В появившемся после этого списке делаем переход по пункту «Заменить».

    Открывается окно «Найти и заменить» во вкладке «Заменить». В поле «Найти» вводим число, слова или символы, которые требуется отыскать и заменить. В поле «Заменить на» выполняем ввод данных, на которые будет производиться замена.

    Как видим, в нижней части окна имеются кнопки замены – «Заменить всё» и «Заменить», и кнопки поиска — «Найти всё» и «Найти далее». Жмем на кнопку «Найти далее».

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

  • Чтобы продолжить поиск данных, опять жмем на кнопку «Найти далее». Тем же способом меняем следующий результат, и т.д.
  • Можно найти все удовлетворяющие запросу результаты сразу.

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

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

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

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

    Процедура выполняется практически моментально.

    Плюсы этого способа — быстрота и удобство. Главный минус состоит в том, что вы должны быть уверены, что введенные символы нуждаются в замене во всех ячейках. Если в предыдущих способах была возможность найти и отобрать нужные ячейки для изменения, то при использовании данного варианта такая возможность исключается.

    Дополнительные параметры

    Кроме того, существует возможность расширенного поиска и замены по дополнительным параметрам.

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

    Открывается окно дополнительных параметров. Оно практически идентично окну расширенного поиска. Единственное отличие – присутствие блока настроек «Заменить на».

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

    Также, можно указать среди ячеек какого формата будет производиться поиск. Для этого нужно кликнуть по кнопке «Формат» напротив параметра «Найти».

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

    Единственной настройкой значения для вставки будет являться все тот же формат ячеек. Для выбора формата вставляемого значения жмем на одноименную кнопку напротив параметра «Заменить на…».

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

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

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

  • Не забываем в поля «Найти» и «Заменить на…» вписать соответствующие значения. Когда все настройки указаны, выбираем способ выполнения процедуры. Либо жмем на кнопку «Заменить все», и замена происходит автоматически, согласно введенным данным, или же жмем на кнопку «Найти все», и отдельно производим замену в каждой ячейке по тому алгоритму, о котором писалось уже выше.
  • Как видим, программа Microsoft Excel предоставляет довольно функциональный и удобный инструмент для поиска и замены данных в таблицах. Если нужно заменить абсолютно все однотипные значения на конкретное выражение, то это можно сделать нажатием только одной кнопки. В случае, если выборку нужно делать более подробно, то и эта возможность в полной мере предоставлена в данном табличном процессоре.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Поиск или замена текста и чисел на листе

    В этом курсе:

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

    Поиск

    Чтобы найти какой-либо элемент, нажмите клавиши CTRL + Fили перейдите на вкладку главная > редактирование> найти & выберите > найти.

    Примечание: В приведенном ниже примере мы настроили кнопку » параметры >> » для отображения всего диалогового окна «найти». По умолчанию отображаются скрытые параметры.

    В поле найти: введите текст или числа, которые вы хотите найти, или щелкните стрелку в поле найти: и выберите в списке последний элемент поиска.

    Советы: Вы можете использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (

    ) — в критериях поиска.

    Используйте вопросительный знак (?), чтобы найти любой одиночный символ (например, «s», «Кот» и «Set».

    Используйте звездочку (*), чтобы найти любое количество символов (например, s * d — «Sad» и «запущено»).

    , чтобы найти вопросительные знаки, звездочки или другие знаки, например ан 91

    ? Поиск слова «Ан 91?».

    Чтобы выполнить поиск, нажмите кнопку найти все или Найти далее .

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

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

    Вне Для поиска данных на листе или во всей книге выберите лист или Книга.

    См Вы можете выбрать вариант поиска по строкам (по умолчанию) или по столбцам.

    Поиск: Чтобы найти данные с определенными подробностями, выберите в поле формулы, значения, заметкиили Примечания.

    Примечание: Формулы, значения, заметки и Примечания доступны только на вкладке » Поиск «. на вкладке заменить доступны только формулы .

    С учетом регистра . Установите этот флажок, если вы хотите искать данные с учетом регистра.

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

    Если вы хотите найти текст или числа с определенным форматированием, нажмите кнопку Формати выберите необходимые параметры в диалоговом окне Найти формат .

    Совет: Чтобы найти ячейки, точно соответствующие определенному формату, можно удалить все условия в поле Найти, а затем выбрать ячейку с нужным форматированием в качестве примера. Щелкните стрелку рядом с кнопкой Формат, выберите пункт Выбрать формат из ячейки, а затем щелкните ячейку с форматированием, которое требуется найти.

    Заменить

    Чтобы заменить текст или числа, нажмите клавиши CTRL + Hили перейдите на вкладку главная > редактирование> найти & выберите > заменить.

    Примечание: В приведенном ниже примере мы настроили кнопку » параметры >> » для отображения всего диалогового окна «найти». По умолчанию отображаются скрытые параметры.

    В поле найти: введите текст или числа, которые вы хотите найти, или щелкните стрелку в поле найти: и выберите в списке последний элемент поиска.

    Советы: Вы можете использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (

    ) — в критериях поиска.

    Используйте вопросительный знак (?), чтобы найти любой одиночный символ (например, «s», «Кот» и «Set».

    Используйте звездочку (*), чтобы найти любое количество символов (например, s * d — «Sad» и «запущено»).

    , чтобы найти вопросительные знаки, звездочки или другие знаки, например ан 91

    ? Поиск слова «Ан 91?».

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

    Нажмите Заменить все или Заменить.

    Совет: При нажатии кнопки заменить всевсе вхождения искомого условия будут заменены, в то время как Замена будет обновляться по одному вхождению за раз.

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

    Вне Для поиска данных на листе или во всей книге выберите лист или Книга.

    См Вы можете выбрать вариант поиска по строкам (по умолчанию) или по столбцам.

    Поиск: Чтобы найти данные с определенными подробностями, выберите в поле формулы, значения, заметкиили Примечания.

    Примечание: Формулы, значения, заметки и Примечания доступны только на вкладке » Поиск «. на вкладке заменить доступны только формулы .

    С учетом регистра . Установите этот флажок, если вы хотите искать данные с учетом регистра.

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

    Если вы хотите найти текст или числа с определенным форматированием, нажмите кнопку Формати выберите необходимые параметры в диалоговом окне Найти формат .

    Совет: Чтобы найти ячейки, точно соответствующие определенному формату, можно удалить все условия в поле Найти, а затем выбрать ячейку с нужным форматированием в качестве примера. Щелкните стрелку рядом с кнопкой Формат, выберите пункт Выбрать формат из ячейки, а затем щелкните ячейку с форматированием, которое требуется найти.

    Существует два различных способа поиска и замены текста или чисел на компьютере Mac. Первый — Использование диалогового окна найти & заменить . Вторая — использовать строку поиска на ленте.

    Диалоговое окно «Поиск» & «заменить»

    Команда заменить в excel

    Други — срочно!
    кто знает как в множестве ячеек листа избавиться от ненужного символа переноса строки?
    чертов квадратик не дает вычислять и корректно конвертировать содержимое.
    имею ввиду символ нажатия «enter» , который приводит к переносу вводимого текста на строку ниже.

    Добавление от 12.12.2004 01:23:

    замена сработала с кодом 010 — НО! после этого функция замены вырубается
    как пофиксить?
    и еще — нужно в пустые ячейки автоматом вставлять некое значение — возможно ли? — путем автозамены? или только вручную? — большой гимор.

    спасибо за ответы

    после конвенртирования данных из 1с в Excel имеем:
    часть данных конвертируется некорректно, имея в ячейках спецсимвол, с этим почти разобрались — см. выше.
    некоторые массивы имеют хаотично разбросанные пустые поля, которые надо заполнить одинаковым значением, в моем случае — нулями, иначе не работает формулы вычисления для соседних ячеек, итоговая автосумма по столбцу и т.п.

    вопрос — можно ли в ПУСТЫЕ ячейки забить нули автозаменой? похоже нет. а лопатить огромные листы на тысячи позиций и вручную заполнять пустые позиции — это тупо и долго.

    Наверно мы с Вами говорим о разных вещах.

    Настоящая автозамена, это когда мы вводим в ячейку, например z, а получаем 0 (Сервис-Автозамена)
    Естественно, что для этого нам необходимо предварительно указать, что z — это неправильный вариант, а 0 — правильный.

    Вы, по всей видимости, имеете ввиду меню Правка — команда Заменить

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

    — Выделяете диапазон ячеек (можно выделить несмежные и не обязательно выделять строго пустые ячейки), затем в меню Правка-Перейти-Выделить-Пустые ячейки-ОК
    — Вводим 0, затем CTRL + ENTER *

    SS-18
    Чтобы определить код символа можно воспользоваться функцией КОДСИМВ.
    Допустим, символ переноса строки находится на пятой позиции в ячейке G30, тогда формула:
    =КОДСИМВ(ПСТР(G30;5;1))
    возвратит значение 10

    Удалить этот символ из ячейки можно с помощью формулы:
    =ПОДСТАВИТЬ(G30;СИМВОЛ(10);»»)

    В некоторых случаях может понадобиться заменить этот символ другим, например, пробелом:
    =ПОДСТАВИТЬ(G30;СИМВОЛ(10);СИМВОЛ(32))

    Другой способ удалить символ с кодом 10 — использовать функцию ПЕЧСИМВ:
    =ПЕЧСИМВ(G30)

    SS-18
    замена сработала с кодом 010 — НО! после этого функция замены вырубается

    Когда вызываешь Замену во второй раз, в полях ввода ОСТАЮТСЯ предыдущие значения!
    То есть там стоИт сомвол 010, но его не видно, ты дописываешь что-то. и такая комбинация символов, действительно, не встречается.
    Метод: после вызова Замены поставить курсор в поле «Что» и несколько раз нажать BackSpace и Delete.

    Забить пустые ячейки нулями? Нет проблем!
    Выдели ячейку где-то внутри таблицы, можно в первой строке, где заголовки.
    Правка-Перейти-Выделить-Текущую область (выделил всю таблицу).
    Ctrl-H — Что:пустая строка, Заменить на:0 — Заменить все.

    Поторопился я обрадоваться
    — не помогает стирание спецсимвола и не работает функция замены после его удаления. Стирание поля в графе «заменить» — не приводит к ее активизации.
    — не работают оба способа замены массивы пустых ячеек на ноль — в способе Nosorog
    выделяется ТОЛЬКО вся таблица ( включая заголовки и графы с названием) и нули вставляются ТОЛЬКО вокруг ячеек массива.
    в способе Vitaly K.
    вообще не активна функция ОПЕРАЦИИ в специальной вставке — ее просто НЕТ ТАМ.

    Попробуй задействовать такой макрос:

    код: Он, собственно, применяет функцию ПЕЧСИМВ к каждой ячейке таблицы, и пишет 0 в каждую пустую ячейку.

    2pashulka
    хммм.. уточняю — у меня дома стоит excel 2000-й. попытка использовать режим «правка-спецвставка» показывает окошко в котором и в помине нет группы ОПЕРАЦИЯ, а есть только выбор вида текста.. ну там Unicode или другой. и больше никаких ..
    -отсюда вопрос — как в Excel 2000 получить указанный режим функции спец вставка?
    Vic_K
    Спасибо за вопрос, но я не знаю как формируется файл из 1С.. знаю, что у жены на работе что-то сбито в ее настройках, после того как там покопался вызванный программист по 1С. а вызывать их на каждый сбой ох как дорого, да еще с такими результатами. все вышеописанные глюки появились именно после визита программиста. т.е. справеливее сказать, что он настраивал именно то, что просили добавить, но потом обнаруживались ужасные последствия в других местах.
    Политика у них такая что-ли, чтобы делать людей зависимыми от их «услуг» и на бабки разводить?

    извините, ответил не квалифицировано, как смог.

    Читать еще:  Прописью в excel
    Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector