Как заменить содержание ячеек в excel - Word и Excel - помощь в работе с программами

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

Поиск

Чтобы найти что-то, нажмите клавиши CTRL+F или перейдите в раздел Главная > Редактирование > найти & Выберите > Найти.

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

В поле Найти что: введите текст или цифры, которые вы хотите найти, или щелкните стрелку в поле Найти что: и выберите последний элемент поиска из списка.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
Нажмите кнопку Найти все или Найти далее , чтобы выполнить поиск.

Совет: При нажатии кнопки Найти все будут перечислены все вхождения условий, которые вы ищете, а щелкнув определенное вхождение в списке, будет выбрана его ячейка. Вы можете отсортировать результаты поиска Найти все , щелкнув заголовок столбца.
Щелкните Параметры>> , чтобы при необходимости определить поиск:
- В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
- Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните Формулы, Значения, Заметки или Примечания.
  
  Примечание: Формулы, значения, заметки и примечания доступны только на вкладке Найти . На вкладкеЗаменить доступны только формулы.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .
Если вы хотите найти текст или цифры с определенным форматированием, нажмите кнопку Формат, а затем выберите нужные значения в диалоговом окне Поиск формата .

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

Заменить

Чтобы заменить текст или цифры, нажмите клавиши CTRL+H или перейдите в раздел Главная > Редактирование > Найти & Выберите > Заменить.

В поле Найти что: введите текст или цифры, которые вы хотите найти, или щелкните стрелку в поле Найти что: и выберите последний элемент поиска из списка.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
В поле Заменить на введите текст или числа, которые нужно использовать для замены текста поиска.
Нажмите Заменить все или Заменить.

Совет: При нажатии кнопки Заменить все все вхождения условий, которые вы ищете, будут заменены, в то время как Replace будет обновлять одно вхождение за раз.
Щелкните Параметры>> , чтобы при необходимости определить поиск:
- В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
- Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните Формулы, Значения, Заметки или Примечания.
  
  Примечание: Формулы, значения, заметки и примечания доступны только на вкладке Найти . На вкладкеЗаменить доступны только формулы.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .
Если вы хотите найти текст или цифры с определенным форматированием, нажмите кнопку Формат, а затем выберите нужные значения в диалоговом окне Поиск формата .

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

Существует два разных метода поиска или замены текста или чисел на компьютере Mac. Во-первых, используется диалоговое окно «Найти & заменить «. Во-вторых, используется панель поиска на ленте.

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

Панель поиска и параметры

Нажмите клавиши CTRL+F или выберите Главная > Найти & Выберите > Найти.
В поле Найти: введите текст или цифры, которые нужно найти.
Нажмите кнопку Найти далее , чтобы выполнить поиск.
Вы можете дополнительно определить поиск:
- В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
- Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните Формулы, Значения, Заметки или Примечания.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .

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

Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».

Нажмите клавиши CTRL+F или выберите Главная > Найти & Выберите > Найти.
В поле Найти: введите текст или цифры, которые нужно найти.
Выберите Найти все , чтобы выполнить поиск всех вхождений.

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

Примечание: Вы можете изменить содержимое выделенной ячейки.

Нажмите клавиши CTRL+H или перейдите на главную страницу > Найти & выберите > Заменить.
В поле Найти введите текст или цифры, которые требуется найти.
Вы можете дополнительно определить поиск:
- В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .
  Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
  - Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
  - Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
  - Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
В поле Заменить на введите текст или числа, которые вы хотите использовать для замены текста поиска.
Выберите Заменить или Заменить все.
Советы:
- При нажатии кнопки Заменить все все вхождения ищемого условия будут заменены.
- При нажатии кнопки Заменить можно заменить один экземпляр за раз, нажав кнопку Далее , чтобы выделить следующий экземпляр.

Выберите любую ячейку для поиска по всему листу или выберите определенный диапазон ячеек для поиска.
Нажмите клавиши COMMAND+F или выберите лупу, чтобы развернуть панель поиска и ввести текст или число, которые нужно найти в поле поиска.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
Нажмите клавишу RETURN.
Примечания:
- Чтобы найти следующий экземпляр элемента, который вы ищете, снова нажмите клавишу RETURN или в диалоговом окне Найти и выберите Найти далее.
- Чтобы указать дополнительные параметры поиска, выберите увеличительное стекло и выберите Поиск на листе или Поиск в книге. Можно также выбрать параметр Дополнительно , который открывает диалоговое окно Поиск .
Совет: Вы можете отменить выполняемый поиск, нажав клавишу ESC.

Поиск

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

В поле Найти что: введите текст или числа, которые нужно найти.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
Нажмите кнопку Найти далее или Найти все , чтобы выполнить поиск.

Совет: При нажатии кнопки Найти все будут перечислены все вхождения условий, которые вы ищете, а щелкнув определенное вхождение в списке, будет выбрана его ячейка. Вы можете отсортировать результаты поиска Найти все , щелкнув заголовок столбца.
Щелкните > Параметры поиска , чтобы при необходимости дополнительно определить поиск:
- В: Чтобы найти данные в пределах определенного выделенного фрагмента, нажмите кнопку Выбор. Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Направление: Вы можете выполнить поиск вниз (по умолчанию) или вверх.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти .

Заменить

В поле Найти что: введите текст или числа, которые нужно найти.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
В поле Заменить на введите текст или числа, которые нужно использовать для замены текста поиска.
Щелкните Заменить или Заменить все.

Совет: При нажатии кнопки Заменить все все вхождения условий, которые вы ищете, будут заменены, в то время как Replace будет обновлять одно вхождение за раз.
Щелкните > Параметры поиска , чтобы при необходимости дополнительно определить поиск:
- В: Чтобы найти данные в пределах определенного выделенного фрагмента, нажмите кнопку Выбор. Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Направление: Вы можете выполнить поиск вниз (по умолчанию) или вверх.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти .

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Замена нескольких значений на несколько

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

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

Эта задача более сложная, чем замена на одно значение. Как ни странно, функция «ЗАМЕНИТЬ» здесь не подходит — она требует явного указания позиции заменяемого текста. Зато может помочь функция «ПОДСТАВИТЬ».

Массовая замена с помощью функции “ПОДСТАВИТЬ”

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

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

Но у решения есть и свои недостатки:

Функция ПОДСТАВИТЬ регистрозависимая, что заставляет при замене одного символа использовать два его варианта — в верхнем и нижнем регистрах. Хотя в некоторых случаях, как, например, на картинке выше, это и преимущество.
Максимум 64 замены — хоть и много, но все же ограничение.
Формально процедура замены таким способом будет происходить массово и моментально, однако длительность написания таких формул сводит на нет это преимущество. За исключением случаев, когда они будут использоваться многократно.

Файл-шаблон с формулой множественной замены

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

Файл доступен по ссылке, но можно и не скачивать его, а просто скопировать текст формулы ниже и вставить ее в любую ячейку, кроме диапазона A1:B64. Формула заменяет в ячейке C1 значения в столбце A стоящими напротив в столбце B.

Формула в файле-шаблоне для множественной замены на примере транслитерации

А вот и она сама (тройной клик по любой части текста = выделить всю формулу): обращается к ячейке D1, делая 64 замены по правилам, указанным в ячейках A1-B64. При этом в столбцах можно удалять значения — это не нарушит её работу.

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(D1;$A$1;$B$1);$A$2;$B$2);$A$3;$B$3);$A$4;$B$4);$A$5;$B$5);$A$6;$B$6);$A$7;$B$7);$A$8;$B$8);$A$9;$B$9);$A$10;$B$10);$A$11;$B$11);$A$12;$B$12);$A$13;$B$13);$A$14;$B$14);$A$15;$B$15);$A$16;$B$16);$A$17;$B$17);$A$18;$B$18);$A$19;$B$19);$A$20;$B$20);$A$21;$B$21);$A$22;$B$22);$A$23;$B$23);$A$24;$B$24);$A$25;$B$25);$A$26;$B$26);$A$27;$B$27);$A$28;$B$28);$A$29;$B$29);$A$30;$B$30);$A$31;$B$31);$A$32;$B$32);$A$33;$B$33);$A$34;$B$34);$A$35;$B$35);$A$36;$B$36);$A$37;$B$37);$A$38;$B$38);$A$39;$B$39);$A$40;$B$40);$A$41;$B$41);$A$42;$B$42);$A$43;$B$43);$A$44;$B$44);$A$45;$B$45);$A$46;$B$46);$A$47;$B$47);$A$48;$B$48);$A$49;$B$49);$A$50;$B$50);$A$51;$B$51);$A$52;$B$52);$A$53;$B$53);$A$54;$B$54);$A$55;$B$55);$A$56;$B$56);$A$57;$B$57);$A$58;$B$58);$A$59;$B$59);$A$60;$B$60);$A$61;$B$61);$A$62;$B$62);$A$63;$B$63);$A$64;$B$64)

Заменить несколько значений на одно

С помощью функции «ПОДСТАВИТЬ»

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

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;"1";"");"2";"");"3";"");"4";"");"5";"");"6";"");"7";"");"8";"");"9";"");"0";"")

С помощью регулярных выражений

Важно: регулярные выражения не поставляются в Excel “из коробки”, но формулы ниже доступны бесплатно, если установить надстройку !SEMTools.

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

=regexreplace(A1;"d";"#")
=regexreplace(A1;"w";"#")
=regexreplace(A1;"а-яА-Я";"#")
=regexreplace(A1;"s";"_")

Первая заменяет на символ “#” все цифры, вторая — все английские буквы, а третья — все кириллические символы в верхнем и нижнем регистре. Четвёртая заменяет любые пробелы, в том числе табуляцию и переносы строк, на нижнее подчеркивание.

Массовая замена символов регулярными выражениями при установленной надстройке !SEMTools

Если же нужно заменять не символы, а несколько значений, состоящих в свою очередь из нескольких букв, цифр или знаков, синтаксис предполагает уже использование круглых скобок и вертикальной черты “|” в качестве разделителя.

Массовая замена в !SEMTools

Надстройка для Excel !SEMTools позволяет в пару кликов производить замены на всех уровнях:

символов и их сочетаний,
паттернов регулярных выражений,
слов,
целых ячеек (в некоторой степени аналог ВПР).

Меню инструментов массовой замены в !SEMTools

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

Пример: замена символов по вхождению

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

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

Массовая замена символов по вхождению на примере Leet Language (некоторые английские буквы заменяются на похожие цифры)

Пример: замена списка слов на другой список слов

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

замена списка слов на одно и то же слово

Замена списка слов на другой список в !SEMTools

С версии !SEMTools 9.18.18 появилась опция: при замене списка слов не учитывать пунктуацию в исходных предложениях, а регистр слов теперь сохраняется.

замена списка слов на другой список в Excel

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

Скачивайте надстройку !SEMTools и делайте массовую замену символов, слов или целых списков в Excel!

Смотрите также по теме поиска и замены данных в Excel:

Найти заглавные/строчные буквы в ячейке;
Найти латиницу или кириллицу в тексте;
Найти числа в текстовых ячейках;
Обнаружить текстовые символы;
Функция НАЙТИ в Excel;
Функция ПОИСК в Excel;
Функция ЗАМЕНИТЬ в Excel;
Найти определенные символы в ячейках Excel.

Источник

На чтение 3 мин Опубликовано 10.01.2015

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

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

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

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

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

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

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

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

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

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

На вкладке Главная нажмите команду Найти и выделить, а затем из раскрывающегося списка выберите пункт Заменить.
Появится диалоговое окно Найти и заменить. Введите текст, который Вы ищете в поле Найти.
Введите текст, на который требуется заменить найденный, в поле Заменить на. А затем нажмите Найти далее.
Если значение будет найдено, то содержащая его ячейка будет выделена.
Посмотрите на текст и убедитесь, что Вы согласны заменить его.
Если согласны, тогда выберите одну из опций замены:
- Заменить: исправляет по одному значению зараз.
- Заменить все: исправляет все варианты искомого текста в книге. В нашем примере мы воспользуемся этой опцией для экономии времени.
Появится диалоговое окно, подтверждающее количество замен, которые будут сделаны. Нажмите ОК для продолжения.
Содержимое ячеек будет заменено.
Закончив, нажмите Закрыть, чтобы выйти из диалогового окна Найти и заменить.

Оцените качество статьи. Нам важно ваше мнение:

Источник

Download Article

This wikiHow will show you how to find and replace cell values in Microsoft Excel. The Find and Replace tool is available in all versions of Excel, including the mobile Excel app.

1
Open your workbook in Excel. You can open your project by clicking the File menu and selecting Open. You can also open it by right-clicking the file and selecting Open With > Excel.
- This method should work for all versions of Microsoft Excel beginning with Excel 2007.
2

Click the Home tab. You’ll find this in the editing menu above your document.

Advertisement
3

Click Find & Select. You’ll find this in the «Editing» grouping of the Home tab with the icon of a magnifying glass.
4

Click Replace. This is usually the first listing in the drop-down menu.
5
Enter the original value in the «Find what» text box field. This is the text that will be replaced.
- You can use * to find a string of characters. For example, s*d will find «sad» as well as «started.»^[1]
- You can use ? to replace a single character in a search. For example, s?t will find «sat» and «set.»^[2]
6
Enter the new value in the «Replace with» text box field. This text will replace the original text.
- You can opt to match the case, so you can change every instance of «wikihow» to «wikiHow.»
- You can chose to contain your find and replace to the current worksheet or apply it to the entire workbook.
7

Click Replace All or Replace. If you want to decide to replace each original value individually, choose Replace, but if you want to replace all the original values at once, choose replace all.^[3]

1
Open your project in Excel. This app icon looks like a green-and-white spreadsheet icon with an «X» next to it. You can find this app on the Home screen, in the app drawer, or by searching.
- If you don’t have the Excel app, you can get it for free from the App Store (iOS) and the Google Play Store (Android).
- Tap the Open tab in Excel to find all your recent documents.
2
Tap the search icon

that looks like a magnifying glass. You’ll see this in the top right corner of your screen.
- A search bar will appear at the top of your document.
3

Tap the gear icon

. You’ll see this next to the search bar that dropped down. Search options will appear.
4
Tap to select Find and Replace or Find and Replace All. As you tap an option, you’ll see the search bar at the top of the screen change to fit your selection.
- Tap Find and Replace if you want to replace each original value individually.
- Tap Find and Replace All if you want to replace all the original values instantly.
5

Tap Done (iOS) or X (Android). You’ll see this option at the top right of the search settings.
6

Type your original value into the «Find» bar. This is the current value located in your document.
7

Type the new value into the «Replace» bar. This value will replace the current value in the document.
8

Tap Replace or Replace All. You’ll see this with your «find and replace» terms.

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Thanks for submitting a tip for review!

wikiHow Video: How to Replace Values in Excel

References

About This Article

Article SummaryX

To find and replace cell values on your computer, open your worksheet in Excel and click the Home tab. Click the Find & Select button on the toolbar, and then select Replace on the menu. On the «»Find and Replace»» window, enter the text you want to find into the «»Find what»» field. You can even use wildcards for a string of text. Wildcards are special characters that replace other characters in searches, like a question mark in place of a letter or an asterisk. In the «»Replace with»» field, type your replacement text exactly how it should appear. To replace all original values at once, click the Replace All button. If you’d rather approve each replacement, click Replace instead.
To replace cell values in the mobile Excel app, open Excel and select a file to edit. Tap the search icon at the top-right corner, and then tap the gear icon next to the search bar to view your options. If you want to replace multiple instances of the same text all at once, tap Find and Replace All. If you’d rather manually approve each replacement, select Replace instead. Then, Tap Done at the top-right corner. Now, type the text you want to replace into the «»Find»» bar, and the replacement text into the «»Replace»» bar. Finally, tap Replace or ‘Replace All to replace the cell values.

Did this summary help you?

Thanks to all authors for creating a page that has been read 18,665 times.

Is this article up to date?

Источник

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

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

Далее вызываем Главная → Редактирование → Найти и выделить → Найти (кнопка с рисунка выше). Поиск также можно включить с клавиатуры комбинацией клавиш Сtrl+F. Откроется диалоговое окно под названием Найти и заменить.

В единственном поле указывается информация (комбинация символов), которую требуется найти. Если не использовать подстановочные символы или т.н. джокеры (см. ниже), то Excel будет искать строгое совпадение заданных символов. Для вывода результатов поиска предлагается два варианта: выводить все результаты сразу – кнопка Найти все; либо выводить по одному найденному значению – кнопка Найти далее.

После запуска поиска программа Excel быстро-быстро просматривает содержимое листа (или указанного диапазона) на предмет наличия искомой комбинации символов. Если такая комбинация обнаружена, то в случае нажатия кнопки Найти все Excel вываливает все найденные ячейки.

Если в нижней части окна выделить любое значение и затем нажать Ctrl+A, то в диапазоне поиска будут выделены все соответствующие ячейки.

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

Поиск нестрогого соответствия символов

Иногда пользователь не знает точного сочетания искомых символов что существенно затрудняет поиск. Данные также могут содержать различные опечатки, лишние пробелы, сокращения и пр., что еще больше вносит путаницы и делает поиск практически невозможным. А может случиться и обратная ситуация: заданной комбинации соответствует слишком много ячеек и цель поиска снова не достигается (кому нужны 100500+ найденных ячеек?).

Для решения этих проблем очень хорошо подходят джокеры (подстановочные символы), которые сообщают Excel о сомнительных местах. Под джокерами могут скрываться различные символы, и Excel видит лишь их относительное расположение в поисковой фразе. Таких джокеров два: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «?» (один «?» – один неизвестный символ).

Так, если в большой базе клиентов нужно найти человека по фамилии Иванов, то поиск может выдать несколько десятков значений. Это явно не то, что вам нужно. К поиску можно добавить имя, но оно может быть внесено самым разным способом: И.Иванов, И. Иванов, Иван Иванов, И.И. Иванов и т.д. Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В нашем примере достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч. Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам «и» + что-еще + «иванов». Этот прием значительно повышает эффективность поиска, т.к. позволяет оперировать не точными критериями.

Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к. 200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал. Часто достаточно ввести первые буквы искомых слов (которые наверняка присутствуют), разделяя их звездочками, чтобы Excel показал чудеса поиска. Главное, чтобы последовательность символов была правильной.

Есть еще один джокер – знак «?». Под ним может скрываться только один неизвестный символ. К примеру, указав для поиска критерий 1?6, Excel найдет все ячейки содержащие последовательность 106, 116, 126, 136 и т.д. А если указать 1??6, то будут найдены ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и т.д. Таким образом, джокер «?» накладывает более жесткие ограничения на поиск, который учитывает количество пропущенных знаков (равный количеству проставленных вопросиков «?»).

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

Продвинутый поиск

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

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

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

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

Поиск формата – это хорошо, но чаще искать приходится конкретные значения. И тут Excel предоставляет дополнительные возможности для расширения и уточнения параметров поиска.

Первый выпадающий список Искать предлагает ограничить поиск одним листом или расширить его до целой книги.

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

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

В следующем выпадающем списке находится замечательная возможность поиска по формулам, значениям, а также примечаниям. По умолчанию Excel производит поиск в формулах либо, если их нет, в содержимом ячейки. Например, если искать фамилию Иванов, а фамилия эта есть результат формулы (копируется из соседнего листа), то поиск нечего не даст, т.к. в ячейке нет искомого перечня символов. По той же причине не удастся отыскать число, являющееся результатом работы какой-либо функции. Поэтому бывает смотришь в упор на ячейку, видишь искомое значение, а Excel его почему-то не видит. Это не глюк, это настройка поиска. Измените данный параметр на Значения и поиск будет осуществляться по тому, что отражено в ячейке, независимо от содержимого. Например, если в ячейке содержится результат вычисления 1/6 (как значение, а не формула) и при этом формат отражает только 3 знака после запятой (т.е 0,167), то поиск символов «167» при выборе параметра Формулы эту ячейку не обнаружит (реальное содержимое ячейки — это 0,166666…), а при выборе Значения поиск увенчается успехом (искомые символы совпадают с тем, что отражается в ячейке). И последний пункт в данном списке – Примечания. Поиск осуществляется только в примечаниях. Очень может помочь, т.к. примечания часто скрыты.

В диалоговом окне поиска есть еще две галочки Учитывать регистр и Ячейка целиком. По умолчанию Excel игнорирует регистр, но можно сделать так, чтобы «иванов» и «Иванов» отличались. Галочка Ячейка целиком также может оказаться весьма полезной, если ищется ячейка не с указанным фрагментом, а полностью состоящая из искомых символов. К примеру, как найти ячейки, содержащие только 0? Обычный поиск не подойдет, т.к. будут выдаваться и 10, и 100. Зато, если установить галочку Ячейка целиком, то все пойдет, как по маслу.

Поиск и замена данных

Данные обычно ищутся не просто так, а для каких-то целей. Такой целью часто является замена искомой комбинации (или формата) на другую. Чтобы найти и заменить в выделенном диапазоне Excel одни значения на другие, в окне Найти и заменить необходимо выбрать вкладку Замена. Либо сразу выбрать на ленте команду Главная → Редактирование → Найти и выделить → Заменить.

Еще удобнее применить сочетание горячих клавиш найти и заменить в Excel – Ctrl+H.

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

По аналогии с простым поиском, менять можно и формат.

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

Напоследок рассмотрим один классный трюк с поиском и заменой. Многие знают, что в ячейку можно вставить разрыв строк с помощью комбинации Alt+Enter.

А как быстро удалить все разрывы строк? Обычно это делают вручную. Однако ловкое использование поиска и замены сэкономит много времени. Вызываем команду поиска и замены с помощью комбинации Ctrl+H. Теперь в строке поиска нажимаем Ctrl+J — это символ разрыва строки — на экране появится точка. В строке замены указываем, например, пробел.

Жмем Ok. Все переносы строк заменились пробелами.

Функция поиска и замены при правильном использовании заменяет часы работы неопытного пользователя. Настоятельно рекомендую использовать все вышеизложенное. Если что-то не ищется в ваших данных или наоборот, выдает слишком много лишних ячеек, то попробуйте уточнить поиск с помощью подстановочных символов «*» и «?» или настраиваемых параметров поиска. Важно понимать, что если вы ничего не нашли, это еще не значит, что там этого нет.

Теперь вы знаете, как в эксель сделать поиск по столбцу, строке, любому диапазону, листу или даже книге.

Поделиться в социальных сетях:

Источник

Формула ЗАМЕНИТЬ и ПОДСТАВИТЬ для текста и чисел

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

Как работает функция ЗАМЕНИТЬ
Использование функции ЗАМЕНИТЬ с числами
Как заменить часть даты
Как заменить сразу несколько букв или слов
Как заменить текст в разных местах
Заменить заглавные буквы на строчные и наоборот
Описание функции ПОДСТАВИТЬ
Замена нескольких значений одной формулой
Что лучше использовать — ЗАМЕНИТЬ или ПОДСТАВИТЬ?

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

Как работает функция ЗАМЕНИТЬ

Функция ЗАМЕНИТЬ позволяет заместить слово, один или несколько символов в текстовой строке другим словом или символом.

ЗАМЕНИТЬ(старый_текст; начальная_позиция; число_знаков, новый_текст)

Как видите, функция ЗАМЕНИТЬ имеет 4 аргумента, и все они обязательны для заполнения.

Старый_текст — исходный текст (или ссылка на ячейку с исходным текстом), в котором вы хотите поменять некоторые символы.
Начальная_позиция — позиция первого символа в старый_текст, начиная с которого вы хотите сделать замену.
Число_знаков — количество символов, которые вы хотите заместить новыми.
Новый_текст – текст замены.

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

=ЗАМЕНИТЬ(«кит»;2;1;»о»)

И если вы поместите исходное слово в какую-нибудь ячейку, скажем, A2, вы можете указать соответствующую ссылку на ячейку в аргументе старый_текст:

=ЗАМЕНИТЬ(А2;2;1;»о»)

Примечание. Если аргументы начальная_позиция или число_знаков отрицательные или не являются числом, формула замены возвращает ошибку #ЗНАЧ!.

Использование функции ЗАМЕНИТЬ с числами

Функция ЗАМЕНИТЬ предназначена для работы с текстом. Но безусловно, вы можете использовать ее для замены не только букв, но и цифр, являющихся частью текстовой строки, например:

=ЗАМЕНИТЬ(A1; 9; 4; «2023»)

Обратите внимание, что мы заключаем «2023» в двойные кавычки, как вы обычно делаете с текстовыми значениями.

Аналогичным образом вы можете заменить одну или несколько цифр в числе. Например формула:

=ЗАМЕНИТЬ(A1;3;2;»23″)

И снова вы должны заключить значение замены в двойные кавычки («23»).

Примечание. Формула ЗАМЕНИТЬ всегда возвращает текстовую строку, а не число. На скриншоте выше обратите внимание на выравнивание по левому краю возвращаемого текстового значения в ячейке B1 и сравните его с исходным числом, выровненным по правому краю в A1. А поскольку это текст, вы не сможете использовать его в других вычислениях, пока не преобразуете его обратно в число, например, умножив на 1 или используя любой другой метод, описанный в статье Как преобразовать текст в число.

Как заменить часть даты

Как вы только что видели, функция ЗАМЕНИТЬ отлично работает с числами, за исключением того, что она возвращает текстовую строку Помните, что во внутренней системе Excel даты хранятся в виде чисел. Поэтому нельзя пытаться заменить часть даты, работая с ней как с текстом.

Например, у вас есть дата в A3, скажем, 15 июля 1992г., и вы хотите изменить «июль» на «май». Итак, вы пишете формулу ЗАМЕНИТЬ(A3; 4; 3; «Май»), которая предписывает Excel поменять 3 символа в ячейке A3, начиная с четвертого. Мы получили следующий результат:

Почему так? Потому что «15-июл-92» — это только визуальное представление базового серийного номера (33800), представляющего дату. Итак, наша формула замены заменяет цифры начиная с четвертой (а это два нуля) в указанном выше числе на текст «Май» и возвращает в результате текстовую строку «338Май».

Чтобы заставить функцию ЗАМЕНИТЬ правильно работать с датами, вы должны сначала преобразовать даты в текстовые строки, используя функцию ТЕКСТ. Кроме того, вы можете встроить функцию ТЕКСТ непосредственно в аргумент старый_текст функции ЗАМЕНИТЬ:

=ЗАМЕНИТЬ(ТЕКСТ(A3; «дд-ммм-гг»); 4; 3; «Май»)

Помните, что результатом приведенной выше формулы является текстовая строка, и поэтому это решение работает только в том случае, если вы не планируете использовать измененные даты в своих дальнейших расчетах. Если вам нужны даты, а не текстовые строки, используйте функцию ДАТАЗНАЧ , чтобы преобразовать значения, возвращаемые функцией Excel ЗАМЕНИТЬ, обратно в даты:

=ДАТАЗНАЧ(ЗАМЕНИТЬ(ТЕКСТ(A3; «дд-ммм-гг»); 4; 3; «Май»))

Как заменить сразу несколько букв или слов

Довольно часто может потребоваться выполнить более одной замены в одной и той же ячейке Excel. Конечно, можно было сделать одну замену, вывести промежуточный результат в дополнительный столбец, а затем снова использовать функцию ЗАМЕНИТЬ. Однако лучший и более профессиональный способ — использовать вложенные функции ЗАМЕНИТЬ, которые позволяют выполнить сразу несколько замен с помощью одной формулы. В этом смысле «вложение» означает размещение одной функции внутри другой.

Рассмотрим следующий пример. Предположим, у вас есть список телефонных номеров в столбце A, отформатированный как «123456789», и вы хотите сделать их более похожими на привычные нам телефонные номера, добавив дефисы. Другими словами, ваша цель — превратить «123456789» в «123-456-789».

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

=ЗАМЕНИТЬ(A3;4;0;»-«)

Результат приведенной выше формулы замены выглядит следующим образом:

А теперь нам нужно вставить еще один дефис в восьмую позицию. Для этого вы помещаете приведенную выше формулу в еще одну функцию Excel ЗАМЕНИТЬ. Точнее, вы встраиваете её в аргумент старый_текст другой функции, чтобы вторая функция ЗАМЕНИТЬ обрабатывала значение, возвращаемое первой формулой, а не первоначальное значение из ячейки А3:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;4;0;»-«);8;0;»-«)

В результате вы получаете номера телефонов в нужном формате:

Аналогичным образом вы можете использовать вложенные функции ЗАМЕНИТЬ, чтобы текстовые строки выглядели как даты, добавляя косую черту (/) там, где это необходимо:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»)

Кроме того, вы можете преобразовать текстовые строки в реальные даты, обернув приведенную выше формулу ЗАМЕНИТЬ функцией ДАТАЗНАЧ:

=ДАТАЗНАЧ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»))

И, естественно, вы не ограничены в количестве функций, которые вы можете последовательно, как матрёшки, вложить друг в друга в одной формуле (современные версии Excel позволяют использовать до 8192 символов и до 64 вложенных функций в одной формуле).

Например, вы можете попробовать 3 вложенные функции ЗАМЕНИТЬ, чтобы число отображалось как дата и время:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»);9;0;» «);12;0;»:»)

Как заменить текст в разных местах

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

Предположим, у вас есть список адресов электронной почты в столбце A. И название одной компании изменилось с «ABC» на, скажем, «BCA». Изменилось и название их почтового домена. Таким образом, вы должны соответствующим образом обновить адреса электронной почты всех клиентов и заменить три буквы в адресах электронной почты, где это необходимо.

Но проблема в том, что имена почтовых ящиков имеют разную длину, и поэтому нельзя указать, с какой именно позиции начинается название домена. Другими словами, вы не знаете, какое значение указать в аргументе начальная_позиция функции Excel ЗАМЕНИТЬ. Чтобы узнать это, используйте функцию Excel НАЙТИ, чтобы определить позицию, с которой начинается доменное имя в адресе электронной почты:

=НАЙТИ(«@abc»; A3)

Затем вставьте указанную выше функцию НАЙТИ в аргумент начальная_позиция формулы ЗАМЕНИТЬ:

=ЗАМЕНИТЬ(A3; НАЙТИ(«@abc»;A3); 4; «@bca»)

Примечание. Мы включаем символ «@» в нашу формулу поиска и замены Excel, чтобы избежать случайных ошибочных замен в именах почтовых ящиков электронной почты. Конечно, вероятность того, что такие совпадения произойдут, очень мала, и все же вы можете перестраховаться.

Как вы видите на скриншоте ниже, у формулы нет проблем, чтобы поменять символы в разных позициях. Однако если заменяемая текстовая строка не найдена и менять в ней ничего не нужно, формула возвращает ошибку #ЗНАЧ!:

И мы хотим, чтобы формула вместо ошибки возвращала исходный адрес электронной почты без изменения. Для этого заключим нашу формулу НАЙТИ И ЗАМЕНИТЬ в функцию ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ЗАМЕНИТЬ(A3; НАЙТИ(«@abc»;A3); 4; «@bca»);A3)

И эта доработанная формула прекрасно работает, не так ли?

Заменить заглавные буквы на строчные и наоборот

Еще один полезный пример – заменить первую строчную букву в ячейке на прописную (заглавную). Всякий раз, когда вы имеете дело со списком имен, товаров и т.п., вы можете использовать приведенную ниже формулу, чтобы изменить первую букву на ЗАГЛАВНУЮ. Ведь названия товаров могут быть записаны по-разному, а в списках важно единообразие.

Таким образом, нам нужно заменить первый символ в тексте на заглавную букву. Используем формулу

=ЗАМЕНИТЬ(СТРОЧН(A3);1;1;ПРОПИСН(ЛЕВСИМВ(A3;1)))

Как видите, эта формула сначала заменяет все буквы в тексте на строчные при помощи функции СТРОЧН, а затем первую строчную букву меняет на заглавную (прописную).

Быть может, это будет полезно.

Описание функции ПОДСТАВИТЬ

Функция ПОДСТАВИТЬ в Excel заменяет один или несколько экземпляров заданного символа или текстовой строки указанными символами.

Синтаксис формулы ПОДСТАВИТЬ в Excel следующий:

ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_вхождения])

Первые три аргумента являются обязательными, а последний – нет.

Текст – исходный текст, в котором вы хотите заменить слова либо отдельные символы. Может быть тестовой строой, ссылкой на ячейку или же результатом вычисления другой формулы.
Старый_текст – что именно вы хотите заменить.
Новый_текст – новый символ или слово для замены старого_текста.
Номер_вхождения — какой по счёту экземпляр старый_текст вы хотите заменить. Если этот параметр опущен, все вхождения старого текста будут заменены новым текстом.

Например, все приведенные ниже формулы подставляют вместо «1» – цифру «2» в ячейке A2, но возвращают разные результаты в зависимости от того, какое число указано в последнем аргументе:

=ПОДСТАВИТЬ(A3;»1″;»2″;1) — Заменяет первое вхождение «1» на «2».

=ПОДСТАВИТЬ(A3;»1″;»2″;2) — Заменяет второе вхождение «1» на «2».

=ПОДСТАВИТЬ(A3;»1″;»2″) — Заменяет все вхождения «1» на «2».

На практике формула ПОДСТАВИТЬ также используется для удаления ненужных символов из текста. Вы просто меняете их на пустую строку “”.

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

=ПОДСТАВИТЬ(A3;» «;»»)

Примечание. Функция ПОДСТАВИТЬ в Excel чувствительна к регистру . Например, следующая формула меняет все вхождения буквы «X» в верхнем регистре на «Y» в ячейке A2, но не заменяет ни одной буквы «x» в нижнем регистре.

=ПОДСТАВИТЬ(A3;»Х»;»Y»)

Замена нескольких значений одной формулой

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

Предположим, у вас есть текстовая строка типа « пр1, эт1, з1 » в ячейке A3, где «пр» означает «Проект», «эт» означает «этап», а «з» означает «задача». Вы хотите заместить три этих кода их полными эквивалентами. Для этого вы можете написать 3 разные формулы подстановки:

=ПОДСТАВИТЬ(A3;»пр»;»Проект «)

=ПОДСТАВИТЬ(A3;»эт»;»Этап «)

=ПОДСТАВИТЬ(A3;»з»;»Задача «)

А затем вложить их друг в друга:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A3;»пр»;»Проект «); «эт»;»Этап «);»з»;»Задача «)

Обратите внимание, что мы добавили пробел в конце каждого аргумента новый_текст для лучшей читабельности.

Другие полезные применения функции ПОДСТАВИТЬ:

Замена неразрывных пробелов в ячейке Excel обычными
Убрать пробелы в числах
Удалить перенос строки в ячейке
Подсчитать определенные символы в ячейке

Что лучше использовать – ЗАМЕНИТЬ или ПОДСТАВИТЬ?

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

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

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

Источник

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

Смотрите такжеУстановите для выделенного диапазона «Разделитель целой и то вместе с и подготовить импортированные «Заменить». ссылка, но может подсчета данных, смотрите в ячейке «2016.» выбора различных смежные. ALT + ВВОД.. ячейки непосредственно в ПОДСТАВИТЬ() справляется с

заменить. которые для ВасПоиск и замена в текстовый формат ячеек, дробной части» следует данными копируется и данные.Если нужно стоять и относительная. в статье «Формула на «2017 г.». ячейки.Примечание:До нажатия клавиши ВВОДЧтобы начать работу в ячейку. Можно также задачей гораздо проще.Урок подготовлен для Вас отыскал Excel. Excel – это

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

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

Есть еще такие в Excel». Применим функцию «Заменить».Выберите пункты Также ширину столбца можно или TAB и режиме редактирования, выполните изменить содержимое ячейки,Кроме того, функция ЗАМЕНИТЬ()

командой сайта office-guru.ruЗакончив поиск, воспользуйтесь кнопкой достаточно мощный и автоматического преобразования в ввести точку.
случаи формат ячеек из ниже приведенной Excel на всем же два листа,ЧтобыУ нас такие
Файл увеличить, изменив формат перед или после одно из указанных
введя в строка заменяет по понятнымИсточник: http://www.gcflearnfree.org/office2013/excel2013/7/full Закрыть, чтобы выйти удобный инструмент, который формат даты некоторыхПосле выполнения вычислений настоятельно «Общий» (по умолчанию) таблицы: листе
но: «февраль» ипоменять в формулах Excel данные в ячейке. >

столбца или отдельной нажатия клавиши F2, ниже действий. формул. причинам только одно

Автор/переводчик: Антон Андронов из диалогового окна позволяет находить, а чисел. Для этого рекомендуется вернуть настройки

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

может быть изменен.№ п/п, то нажмем кнопку «касса февраль». ссылки на другойСтавим курсор в ячейкеПараметры ячейки. Например, можно можно нажать клавишуДважды щелкните ячейку, содержимоеПри редактировании содержимого ячейки вхождение строки, функцияАвтор: Антон Андронов Найти и Заменить. при необходимости и выберите текстовый формат

по умолчанию. Тогда нельзя визуальноВалютная пара «Заменить все», иДля этих таблиц
лист В19. На закладке > изменить формат даты, ESC, чтобы отменить
которой требуется изменить. Excel работает в ПОДСТАВИТЬ() может заменитьФункция ЗАМЕНИТЬ(), английский вариантВы можете получить доступ
заменять, информацию на из выпадающего спискаВнимание! Данный способ сработает,
отличить, где распознаноПересчет заменятся ссылки во
мы на листе, нужно воспользоваться функцией «Формулы» заходим в
- Дополнительно чтобы отображался только
- все изменения, внесенныеПри этом запускается режим режиме редактирования. Некоторые все вхождения или REPLACE(), замещает указанную к команде Найти
рабочем листе. В на закладке «Главная» если сделать все число, а где
Продажа
всех формулах листа, «Итого» копируем таблицу «Найти и выделить». раздел «Текстовые», выбираем

. день и месяц
в содержимое ячейки.
редактирования и перемещает

функции Excel работают

office-guru.ru

Функция ЗАМЕНИТЬ() в MS EXCEL

только первое, только часть знаков текстовой с помощью комбинации рамках данного урока в разделе «Число». изменения до импорта текст.Покупка даже в тех, «итого январь» иДопустим, мы копируем функцию «ЗАМЕНИТЬ».ИЛИ (формат «дд.мм»), например

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

После нажатия клавиши ВВОД курсор в ячейку

 по-разному или недоступны второе и т.д. строки другой строкой клавиш Ctrl+F на
 Вы научитесь выполнять Или нажмите CTRL+1 данных, а неВсе последующие действия необходимоСпрэд которых нет в назовем ее «итого
 лист Excel (листДиалоговое окно заполнилиВ Excel 2007 только: 31.12, или представить или TAB, операцию в нужное место
 в режиме редактирования.Поясним на примере. текста. «Указанную часть клавиатуре. поиск в заданной

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

, в появившимся после. выполнять с чистого1 списке. февраль». 1) с таблицей, так. нажмите число в экспоненциальном

можно отменить изменения, при двойном щелчке.При Excel находится в Пусть в ячейке

знаков» означает, чтоЧтобы увидеть дополнительные параметры области документа Excel, окне «Формат ячеек»Принцип этого способа похож листа. Удалите все,

EUR/USD
Можно настроить список

В этой, скопированной в которой естьВ строке «Старый текст»Кнопку Microsoft Office формате, например 4E+08. нажав клавиши CTRL Содержимое ячейки также режиме редактирования, как

А2 нужно указать начальную поиска и замены, а также изменять выберите закладку «Число», на предыдущий. Только что есть на1
для удобной ориентировки таблице «итого февраль» формулы со ссылкой указываем ячейку, в, выберите пунктПри наличии нескольких строк
+ Z или,
отображаются в строке
показано на приведенномвведена строка Продажи позицию и длину нажмите кнопку Параметры найденную информацию на а в разделе
здесь мы меняем листе или откройте1.1347 в нем –

на листе «Итого», на «лист 2». которой нужно заменитьПараметры Excel текста в ячейке, нажав кнопку формул. ниже рисунке слово (январь), прибыль (январь).

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

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

excel2.ru

Изменение содержимого ячейки

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

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

2 просмотр списка по листов на февральские в формулах ссылку какого по счету.

образом. Можно отобразитьБыстрого доступа а затем щелкните нижнем углу окна=ПОДСТАВИТЬ(A2; «январь»;»февраль») позволяет легко вставить ошибка, повторяющаяся во Excel, иногда достаточно правой кнопкой мышки

Как Excel работает по-разному в режиме редактирования?

системы нужно заменить запятую в импортированныхGBP/USD строкам или по листы. «лист 2» на символа нужно менять

В разделе несколько строк текста. в любом месте программы в Microsoftполучим в первом в указанную позицию всей книге Excel. трудно отыскать какую-то по ячейке A1.

Включение и отключение режима редактирования

запятую, на точку. данных можно использовать1 столбцам, искать формулы1. В таблице, ссылку «лист 3». текст (считаем с

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

Например, неправильно написано

конкретную информацию. И, Из контекстного меню Теперь подробнее о 4 способа:1.5438 на этом листе в которой будем Удобнее здесь выделить
пробелами).выполните требуемое действие. текстом.
- может потребоваться настроитьПри этом запускается режимМногие команды недоступны в (февраль), прибыль (январь),ЗАМЕНИТЬисходный_текстнач_позчисло_знаковновый_текст
- чье-то имя или как правило, такой выберите опцию «Специальная том, как это

Режим редактирования

1.5442 или в книге. менять ссылки, выделяем диапазон и заменить

«Число знаков» -Чтобы оставаться в ячейку,

Примечание: способ их отображения. редактирования и размещение режиме редактирования. Например во втором -) необходимо изменить определенное поиск занимает очень
вставка». Выберите «Текст сделать.Программа Блокнот Windows не0.0004Всё, ссылки в

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

Исходный_текст слово или фразу много времени. Excel в кодировке Unicode»

Вставка, удаление или замена содержимого ячейки

Открываем «Пуск»-«Панель управления»-«Языки и требует использования сложных3 формулах заменены. ячеек). Диапазон ячеек
всех нужных нам т. ч. пробелов, выделения, снимите флажок режиме редактирования, не могут отображаться формул в месте в режиме редактирования,
(февраль). — текст, в котором на другую. Вы
предлагает прекрасный инструмент и нажмите ОК. региональные стандарты». Нажимаем настроек и функций,USD/CHF

Также меняем ссылки быстрее и удобнее формулах. Делают это нужно заменить.после нажатия клавиши ВВОД, удается изменить вид# щелчка. нельзя применить условноеЗаписав формулу =ПОДСТАВИТЬ(A2; требуется заменить некоторые можете воспользоваться инструментом
поиска. Вы можете Обратите внимание, как на кнопку «Дополнительно». а лишь выступает1 «’касса январь’!» на выбрать по имени с помощью макросов.

Отмена или Отмена изменений

«Новый текст» - переход к другой текста переносится.. Это может произойти,Щелкните ячейку, которая содержит форматирование или изменить «январь»;»февраль»;2) получим строку знаки.

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

Изменение способа отображения содержимого ячейки

ячейкеПеренос текста в ячейке если ячейка содержит

данные, которые вы выравнивание содержимого ячейки. Продажи (январь), прибыльНач_поз чтобы быстро внести любую нужную информацию в ячейках: D3, изменяем в первом и предварительной подготовки0.9547 В этом названии ячейки не смежные. другой вариант, немного который нужно вставить..Щелкните ячейку, в которой числа или даты хотите изменить иКроме того клавиши со (февраль). — позиция знака в исправления. В нашем в рабочей книге D5, E3, E5,

поле «Разделитель целой
1. данных.0.0004 ссылки нужно указывать
2. Читайте в статье трудоемкий, но всеНажимаем «ОК». ПолучилосьЧтобы управлять направлением выделения, требуется выполнить перенос и ширину столбца, нажмите клавишу F2.
3. стрелками поведение которыхКроме того, функция ПОДСТАВИТЬ()Исходном_тексте примере мы воспользуемся
  - Excel с помощью в отличии от и дробной части»Скопируйте данные из исходной4
  - и одинарные кавычки, «Что такое диапазон равно легче и так. установите флажок текста. его не удается
    
    При этом запускается режим отличается при Excel чувствительна к РЕгиСТру., начиная с которой командой Заменить, чтобы команды Найти, которая самого первого копирования — вводим нужное таблички на этойUSD/JPY писать строго, как в Excel».
быстрее, чем вручнуюВ ячейке В 19после нажатия клавиши ВВОД,На вкладке отобразить все знаки, редактирования и перемещает находится в режиме Записав =ПОДСТАВИТЬ(A2; «ЯНВАРЬ»;»февраль»)

 знаки заменяются текстом исправить список адресов к тому же таблицы. нам значение. Дальше

странице. Откройте программу
1. 100 написано в формуле.2. На закладке
2. писать ссылку в будет такая формула. переход к другойГлавная которые требуется его курсор в конце редактирования. Без перемещения
  
   получим строку безновый_текст электронной почты. позволяет еще иНажмите на инструмент «Главная»-«Найти ОК и ОК. Блокнот Windows («Пуск»-«Все1.1948Посмотреть о видах «Главная» в разделе каждой формуле. Делаем =ЗАМЕНИТЬ(A19;5;7;»2017 г.») ячейке
  
  в группе формат. Например предположим, содержимого ячейки. курсора между ячейками изменений Продажи (январь),.На вкладке Главная нажмите изменить данные с и выделить»-«Заменить» (илиВнимание! Если вы откроете программы»-«Стандартные»-«Блокнот») и вставьте

Выход из режима редактирования

1.1945 ссылок можно в «Редактирование» нажимаем кнопку замену ссылки функцией

Второй вариант.

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

в режиме редактирования, прибыль (январь), т.к.Число_знаков команду Найти и
1. помощью инструмента Найти нажмите комбинацию CTRL+H). этот файл на в него скопированные-0.0003 статье «Относительные и «Найти и выделить».
  
  «Найти и выделить»,
  
  Как заменить текст в свыберите пункт формат даты «мм/дд/гггг» ячейку, которую вы клавиши со стрелками для функции ПОДСТАВИТЬ() — число знаков в
2. выделить, а затем и заменить.В появившемся окне введите
  - другом компьютере, где данные для подготовки.Теперь перейдите на рабочий абсолютные ссылки в Смотрите статью «Поиск расположенной на закладкеExcel.
  - направлениемПереносить текст содержит 31/12/2007. Тем хотите вставить их перемещение курсора в «ЯНВАРЬ» не тожеИсходном_тексте из раскрывающегося спискаВ нашем примере мы в поле «Найти»
установлены другие системные

Выберите в меню «Правка» лист и щелкните Excel». в Excel» тут. «Главная».Нам нужно заменить
и затем выберите

. не менее столбец и введите новые ячейке.
самое, что «январь».

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

support.office.com

Функция «ЗАМЕНИТЬ» в Excel.

параметры региональных стандартов, опцию «Заменить» (или  правой кнопкой мышкиВ таблице Excel В строке «Найти»Но, начало фразы. Слово в списке направление.Примечание: только ширину, чтобы символы.Если ничего не происходит Функцию ЗАМЕНИТЬ() удобно использоватьновый_текст
Появится диалоговое окно Найти  чтобы отыскать требуемое
в во второе то могут возникнуть комбинацию горячих клавиш по ячейке A1.
можно закрепить верхние пишем ссылку, которую
здесь есть нюансы «Год» поменять наНажмите клавишу TAB. Если текст одного слова отобразить шесть знаков.
Чтобы удалить знаки, щелкните при попытке использовать
для вставки в. и заменить. Введите имя в длинном
поле введите запятую. проблемы с вычислениями. CTRL+H). В появившимся Из появившегося контекстного строки (шапку таблицы)
будем менять., поэтому рассмотрим подробно слово «Дата». ВЭто прекращает режим редактирования
много времени, не В ячейке отображается ячейку, которую вы
режим редактирования, могут строку нового текста.
Новый_текст текст, который Вы списке сотрудников.
 И нажмите «Заменить
Данный способ похож на  диалоговом окне, введите
меню выберите опцию и левые столбцыНажимаем кнопку «Найти все». на примере. В ячейке В19 формулу и Выбор ячейки будут перенесены символы;
# хотите удалить их, быть отключены. Можно Например, имеется перечень — текст, который заменяет ищете в поле
Если перед использованием команды
все». первый. Только здесь в поле: «Что» «Специальная вставка». В в таблице. И, Вышел список ячеек примере все таблицы
напишем такую формулу. справа от текущей можно расширить столбец. Чтобы просмотреть все затем нажмите клавишу включить или отключить артикулов товаров вида

excel-office.ru

Поменять ссылки на другие листы в формулах Excel.

 знаки в Найти. Найти Вы выделитеСнова выделите 3 столбца мы используем ту точку (.), а диалоговом окне выберите тогда, при прокрутке с формулами с придуманы для этого =ЗАМЕНИТЬ(A19;1;3;»Дата»)
ячейки. Выбор ячейки или уменьшить размер содержимое ячейки с BACKSPACE, или выделите режим редактирования, изменив «ID-567(ASD)», необходимо перед
Исходном_текстеВведите текст, на который одну ячейку, то D:F и измените же функцию из в поле «Чем» «Текст в кодировке таблицы вниз или ссылкой «январь!». примера, поэтому неВ формуле мы слева, нажав клавиши шрифта, чтобы просмотреть его текущий формат, символы и нажмите параметр Microsoft Excel. текстом ASD вставить
. требуется заменить найденный, Excel будет выполнять формат ячеек на Блокнота, но уже запятую (,). И Unicode» и нажмите в сторону, этиНюанс! ищите в них написали – заменить,
SHIFT + TAB. весь текст. Если необходимо увеличить ширину клавишу DELETE.Выберите пункты новый текст Micro,Функция ПОДСТАВИТЬ() используется, когда в поле Заменить поиск во всем
«Числовой» CTRL+SHIFT+1. Не в самом Excel. нажмите кнопку «Заменить ОК.
строки и столбцыБлагодаря тому, что бухгалтерскую логику.
начиная с первогоЩелкните другую ячейку. все виден текст
столбца.Чтобы заменить определенные символы,Файл чтобы получилось «ID-567(MicroASD)». нужно заменить на. А затем листе. А если забудьте увеличить разрядностьВ этом способе, в
все».Как видно Excel распознает всегда будут видны мы предварительно отметилиИтак, есть первый символа (1), триВыход из режима редактирования в ячейке послеИзменение ширины столбца выделите их и
> Для этого напишемопределенный нажмите Найти далее.
диапазон ячеек, то до 4-х, как отличие от вышеПрограмма Блокнот заменила все числа только в на экране монитора.
диапазон с нужными лист «январь», в символа (слово «год») в Excel и переносить текст, возможно,Щелкните ячейку, для которой введите новые символы.
Параметры простую формулу:текст в текстовойЕсли значение будет найдено, только внутри этого описано в пункте приведенных, мы сначала точки, на запятые. колонке C. Значения Так удобнее работать нам ячейками, в нем, список сотрудников
на слово «Дата». выбирает ячейку, которая потребуется изменить высоту требуется изменить ширинуЧтобы включить режим замены, >=ЗАМЕНИТЬ(A2;8;0;»Micro»). строке; функция ЗАМЕНИТЬ() то содержащая его
диапазона №2. вставляем скопированную табличку Теперь данные готовы
 в этой колонке
с большой таблицей. списке будут показаны и их доход.Получилось так. была нажата кнопка. строки. столбца. в котором прежниеДополнительноПримечание: используется, когда нужно ячейка будет выделена.На вкладке Главная воспользуйтесьВсе точки сменились на на чистый лист, для копирования и выровнены по правой
Как сделать это, ячейки только изВторой лист «касса январь»,Есть много разных функций,Нажмите клавишу F2.
На вкладкеНа вкладке знаки заменяются новыми.Мы стараемся как заменитьПосмотрите на текст и
командой Найти и запятые. А текст а потом подготавливаем вставки на лист. стороне. В других смотрите в статье отмеченного диапазона. Если в нем - формул, чтобы найтиExcel выход из режимаГлавная
Главная по мере ввода,ИЛИ можно оперативнее обеспечиватьлюбой убедитесь, что Вы выделить, а затем автоматически преобразился в ее к выполнениюЭто весьма простой, но
колонках по левому «Как закрепить строку
бы не выделяли суммы по сотрудникам. данные в Excel. редактирования и оставляетв группев группе нажмите клавишу INSERT.В Excel 2007 только:
вас актуальными справочнымитекст начиная с согласны заменить его. из раскрывающегося списка число.
вычислений и расчетов. очень эффективный способ. краю. Во всех в Excel и предварительно диапазон, тоТретий лист «Итого», в О разных способах курсор там, гдеячейкиЯчейкиПримечание: нажмите материалами на вашем
определенной позиции.Если согласны, тогда выберите выберите пункт Найти.Вместо 4-го и 5-гоВажным недостатком данного способа

excel-office.ru

4 способа заменить точку на запятую в Excel

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

Режим замены можно включитьКнопку Microsoft Office языке. Эта страницаПри замене определенного текста одну из опцийПоявится диалоговое окно Найти пункта можно использовать является сложность его точку на запятую умолчанию «Общий», аТочка вместо запятой дробных

Как в Excel заменить точку, на запятую?

бы ячейки со январь». статье «Поиск в

Есть	Формат	Формат	или отключить только	, выберите пункт	переведена автоматически, поэтому
функцию ЗАМЕНИТЬ() использовать	замены:	и заменить. Введите	в отдельной колонке	реализации, если некоторые	в Excel правильно
в ячейках D3,	чисел может привести	всего листа. В	В таблицу «итого	Excel».	в
и в разделе	.	в том случае,	Параметры Excel	ее текст может	неудобно. Гораздо удобнее
Заменить: исправляет по одному	данные, которые необходимо	формулу с функциями:	дробные числа с	оцените поставленную задачу.	D5, E3, E5

к существенным последствиям т.ч., и за январь» переносится поБывает в таблицеExcel функции найти, выделитьРазмер ячейкиВыделите пункт если вы находитесьи выберите категорию содержать неточности и воспользоваться функцией ПОДСТАВИТЬ().

значению зараз. отыскать. В нашемНапример, выделите диапазон ячеек точкой после вставки Возможно, лучше заставить вообще отображается формат при расчетах в январь, где нам формуле из листа Excel не работают и заменитьвыберите пунктРазмер ячейки в режиме редактирования.Дополнительно грамматические ошибки. ДляПусть в ячейкеЗаменить все: исправляет все примере мы введем G2:I5, введите эту были распознаны как программу временно воспринимать «Дата». Мы копировали программе Excel. Чаще

не надо менять «январь» итоговая сумма формулы. О причинахданные, текст, т.д.Автоподбор размера строкии выполните одно При включенном режиме. нас важно, чтобыА2 варианты искомого текста имя сотрудника. формулу и нажмите

дата, а не точку, как разделитель данные через специальную всего такие ошибки ссылки. Это бы по каждому сотруднику и способах исправить

Подробно все эти. из следующих действий. замены, будут перезаписаны

В разделе

Способ 1 замена точки на запятую в Excel через Блокнот

эта статья былавведена строка Продажи в книге. ВНажмите Найти далее. Если CTRL+Enter. А потом как текст. Поэтому десятых в дробных

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

данные в таблицуНажимаем вкладку «Заменить» и «касса январь» переносится статье «Не работают в статье «Найти

режиме редактирования, выполните под текст, выберите

Способ 2 временно меняем настройки Excel

выделенного знака справавыполните требуемое действие. вас уделить пару слово январь, на воспользуемся этой опцией листе, то они диапазона G2:I5 в от дат, а настройках мы указываем, устранены. Причина только из других источников. в строке «Заменить сумма по каждому

формулы в Excel». и заменить в одно из указанных команду от точки вставкиЧтобы включить режим редактирования, секунд и сообщить, февраль, запишем формулы: для экономии времени. будут выделены.

диапазон D2:F5. потом разберемся с что в дробных

одна – вместоЕсли в дробных числах на» пишем новую сотруднику.Работа с формулами Excel

Способ 3 временно меняем системные настройки Windows

Excel». Здесь рассмотрим, ниже действий.Автоподбор ширины в строке формул. установите флажок помогла ли она=ЗАМЕНИТЬ(A2;10;6;»февраль»)Появится диалоговое окно, подтверждающееЕсли Вы еще разЭта формула находит в текстом и точками.

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

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

Способ 4 используем функцию найти и заменить в Excel.

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

заменить вВыход из режима редактированияЧтобы увеличить ширину столбца, в определенном месте ячейке кнопок внизу страницы.т.е. для функции ЗАМЕНИТЬ() будут сделаны. Нажмите

Найти далее, то помощью функции НАЙТИ. будут находится дробныеДля этого нужно открыть не подготовлен и автоматически воспринимает ихА вот теперь придется странице «Итого» стоят и заменить ссылкиExcel формулой в Excel и выберите команду

текста в ячейке,. Для удобства также потребовалось вычислить начальную ОК для продолжения. увидите следующий вариант Потом вторая функция
числа с точкой «Файл»-«Параметры»-«Дополнительно». В разделе его нельзя использовать как текстовый тип поработать над каждой в формулах ссылки в формулах, при. выбирает ячейку непосредственноШирина столбца нажмите кнопку вЧтобы отключить режим редактирования, приводим ссылку на позицию слова январьСодержимое ячеек будет заменено. поиска. Вы также меняет ее на
в качестве разделителя. «Параметры правки» следует для вычислений. данных. Поэтому перед ячейкой. Наводим курсор на страницы «январь» необходимости. Как написатьЗаменить значение в под текущей ячейки.и введите нужное где нужно вставить снимите флажок оригинал (на английском (10) и егоЗакончив, нажмите Закрыть, чтобы
можете выбрать Найти запятую. А функция В данном случаи
временно убрать галочкуПримечание. Если копировать данные тем как выполнять на каждую строку и «касса январь». разные формулы, гдеExcel.
Также можно указать, что значение в поле разрыв строки, аРазрешить редактирование непосредственно в языке) . длину (6). Это выйти из диалогового все, что бы

ЗНАЧЕН преобразует полученный это 3 столбца «Использовать системные разделители». из других источников

математические расчеты и списка ячеек и Смотрите строку формул. применить формулы, кроме

Нам нужно заменить нажатие клавиши ВВОДШирина столбца затем нажмите клавиши ячейкеВы можете редактировать содержимого не удобно, функция

окна Найти и увидеть все варианты, результат в число. D:F. А в поле без специальной вставки, вычисления следует отформатировать затем нажимаем кнопку

exceltable.com

Здесь стоит абсолютная

Источник

Функция ЗАМЕНИТЬ( ) , английский вариант 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»).

Как убрать повторяющиеся значения в Excel?

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

Хотя Microsoft Excel является в первую очередь инструментом для расчетов, его таблицы часто используются в качестве баз данных для отслеживания запасов, составления отчетов о продажах или ведения списков рассылки.

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

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

В нескольких наших недавних статьях мы обсуждали различные способы выявления дубликатов в Excel и выделения неуникальных ячеек или строк (см.ссылки в конце статьи). Однако могут возникнуть ситуации, когда вы захотите в конечном счете устранить дубли в ваших таблицах. И это как раз тема этого руководства.

Удаление повторяющихся строк вручную

Если вы используете последнюю версию Microsoft Excel с 2007 по 2019, у вас есть небольшое преимущество. Эти версии содержат встроенную функцию для поиска и удаления повторяющихся значений.

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

Важно! Поскольку инструмент «Удалить дубликаты» навсегда удаляет идентичные записи, рекомендуется создать копию исходных данных, прежде чем удалять что-либо.

Для этого выполните следующие действия.

Для начала выберите диапазон, в котором вы хотите работать. Чтобы выделить всю таблицу, нажмите Ctrl + A ,
Указав диапазон, перейдите на вкладку «Данные» > и нажмите кнопку «Удалить дубликаты» .

Откроется диалоговое окно. Выберите столбцы для проверки на наличие дублей и нажмите кнопку «ОК».
- Чтобы удалить повторяющиеся строки, которые имеют абсолютно одинаковые данные во всех колонках, оставьте флажки рядом со всеми столбцами, как на скриншоте ниже.
- Чтобы удалить частичные совпадения на основе одного или нескольких ключевых столбцов, выберите только их. Если в вашей таблице много колонок, самый быстрый способ — нажать кнопку «Снять выделение». А затем отметить те, которые вы хотите проверить.
- Ежели в вашей таблице нет заголовков, снимите флажок Мои данные в верхнем правом углу диалогового окна, который обычно включается по умолчанию.
Если указать в диалоговом окне все столбцы, строка будет удалена только в том случае, если повторяются значения есть во всех них. Но в некоторых ситуациях не нужно учитывать данные, находящиеся в определенных колонках. Поэтому для них снимите флажки. К примеру, если каждая строчка содержит уникальный идентификационный код, программа никогда не найдет ни одной повторяющейся. Поэтому флажок рядом с колонкой с такими кодами следует снять.

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

Важное замечание. Повторяющиеся значения определяются по тому, что отображается в ячейке, а не по тому, что в ней записано на самом деле. Представим, что в A1 и A2 содержится одна и та же дата. Одна из них представлена в формате 15.05.2020, а другая отформатирована в формате 15 май 2020. При поиске повторяющихся значений Excel считает, что это не одно и то же. Аналогично значения, которые отформатированы по-разному, считаются разными, поэтому $1 209,32 — это совсем не одно и то же, что 1209,32.

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

Удаление дубликатов в «умной таблице».

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

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

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

Но, в отличие от ранее рассмотренного инструмента удаления, операцию можно отменить, если что-то пошло не так.

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

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

Выберите диапазон или всю таблицу, которую вы хотите обработать (1).
Перейдите на вкладку «Данные» (2) и нажмите кнопку «Фильтр — Дополнительно» (3-4).
В диалоговом окне «Расширенный фильтр» (5) выполните следующие действия:
- Выберите переключатель скопировать в другое место (6).
- Убедитесь, что в списке диапазонов указан правильный диапазон. Это должен быть диапазон из шага 1.
- В поле «Поместить результат в…» (7) введите диапазон, в который вы хотите скопировать уникальные записи (на самом деле достаточно указать его верхнюю левую ячейку).
- Выберите только уникальные записи (8).
Наконец, нажмите кнопку ОК, и уникальные значения будут скопированы в новое место:

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

Я думаю, вы понимаете, что можно обойтись и без копирования. Просто выберите опцию «Фильтровать список на месте», и дублирующиеся записи будут на время скрыты при помощи фильтра. Они не удаляются, но и мешать вам при этом не будут.

Как убрать дубликаты строк с помощью формул.

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

Преимущество этого подхода заключается в универсальности: он позволяет вам:

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

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

В зависимости от вашей задачи используйте одну из следующих формул для обнаружения повторов.

Формулы для поиска повторяющихся значений в одном столбце

Добавляем еще одну колонку, в которой запишем формулу.

Повторы наименований товаров, без учета первого вхождения:

Как видите, когда значение встречается впервые (к примеру, в B4), оно рассматривается как вполне обычное. А вот второе его появление (в B7) уже считается повтором.

Отмечаем все повторы вместе с первым появлением:

Где A2 — первая, а A10 — последняя ячейка диапазона, в котором нужно найти совпадения.

Ну а теперь, чтобы убрать ненужное, устанавливаем фильтр и в столбце H и оставляем только «Дубликат». После чего строки, оставшиеся на экране, просто удаляем.

Вот небольшая пошаговая инструкция.

Выберите любую ячейку и примените автоматический фильтр, нажав кнопку «Фильтр» на вкладке «Данные».
Отфильтруйте повторяющиеся строки, щелкнув стрелку в заголовке нужного столбца.
И, наконец, удалите повторы. Для этого выберите отфильтрованные строки, перетаскивая указатель мыши по их номерам, щелкните правой кнопкой мыши и выберите «Удалить строку» в контекстном меню. Причина, по которой вам нужно сделать это вместо простого нажатия кнопки «Удалить» на клавиатуре, заключается в том, что это действие будет удалять целые строки, а не только содержимое ячейки.

Формулы для поиска повторяющихся строк.

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

Отмечаем при помощи формулы неуникальные строчки, кроме 1- го вхождения:

=ЕСЛИ(СЧЁТЕСЛИМН($B$2:$B$17; $B2;$A$2:$A$17;$A2;$C$2:$C$17;$C2)>1; «Дубликат»; «»)

Далее вновь устанавливаем фильтр и действуем аналогично описанному выше.

Насколько удобен этот метод – судить вам.

Duplicate Remover — универсальный инструмент для поиска и удаления дубликатов в Excel.

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

Он безупречно работает во всех операционных системах и во всех версиях Microsoft Excel 2019 — 2003.

Как избавиться от дубликатов в Excel в 2 клика мышки.

Предполагая, что в вашем Excel установлен Ultimate Suite, выполните следующие простые шаги, чтобы удалить повторяющиеся строки или ячейки:

Выберите любую ячейку в таблице, c которой вы хотите работать, и нажмите Quick Dedupe на вкладке Ablebits Data.

Откроется диалоговое окно, и все столбцы будут выбраны по умолчанию. Выберите те, которые вам нужны, а также в выпадающем списке в правом нижнем углу укажите желаемое действие.

Поскольку моя цель – просто выделить повторяющиеся данные, я выбрал «Закрасить цветом».

Помимо выделения цветом, вам доступны и другие операции:

Удалить дубликаты
Выбрать дубликаты
Указать их в столбце статуса
Копировать дубликаты на новый лист
Переместить на новый лист

Нажимаем кнопку OK и оцениваем получившийся результат:

Как вы можете видеть на скриншоте выше, строки с повторяющимися значениями в первых 3 столбцах были обнаружены (первые вхождения здесь по умолчанию не считаются как дубликаты).

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

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

Больше возможностей для поиска дубликатов при помощи Duplicate Remover.

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

Удаление дубликатов в Excel — обычная операция. Однако в каждом конкретном случае может быть ряд особенностей. В то время как инструмент Quick Dedupe фокусируется на скорости, Duplicate Remover предлагает ряд дополнительных опций для работы с дубликатами и уникальными значениями.

Выберите любую ячейку в таблице, где вы хотите удалить дубликаты, переключитесь на вкладку Ablebits Data и нажмите кнопку Duplicate Remover.

Вам предложены 4 варианта проверки дубликатов в вашем листе Excel:
- Дубликаты без первых вхождений повторяющихся записей.
- Дубликаты с 1-м вхождением.
- Уникальные записи.
- Уникальные значения и 1-е повторяющиеся вхождения.
В этом примере выберем второй вариант, т.е. Дубликаты + 1-е вхождения:

Все ваши данные будут автоматически выделены.
Теперь выберите столбцы, в которых вы хотите проверить дубликаты. Как и в предыдущем примере, мы выбираем первые 3 столбца:

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

Чтобы более наглядно увидеть результат, отметим параметр «Закрасить цветом» (Fill with color) и нажимаем Готово.

Мастеру Duplicate Remover требуется совсем немного времени, чтобы проанализировать вашу таблицу и показать результат:

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

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

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

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

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

Еще на эту же тему:

Подсчет уникальных значений в Excel — В этом руководстве вы узнаете, как посчитать уникальные значения в Excel с помощью формул и как это сделать в сводной таблице. Мы также разберём несколько примеров счёта уникальных текстовых и числовых…
Как посчитать количество пустых и непустых ячеек в Excel — Если ваша задача — заставить Excel подсчитывать пустые ячейки на листе, прочтите эту статью, чтобы найти 3 способа для этого. Узнайте, как искать и выбирать среди них нужные с помощью стандартных…
Как сравнить два столбца на совпадения и различия — На прочтение этой статьи у вас уйдет около 10 минут, а в следующие 5 минут (или даже быстрее) вы легко сравните два столбца Excel на наличие дубликатов и выделите найденные…
Как выделить цветом повторяющиеся значения в Excel? — В этом руководстве вы узнаете, как отображать дубликаты в Excel. Мы рассмотрим различные методы затенения дублирующих ячеек, целых строк или последовательных повторений с использованием условного форматирования. Ранее мы исследовали различные…
Как посчитать количество повторяющихся значений в Excel? — Зачем считать дубликаты? Мы можем получить ответ на множество интересных вопросов. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если…
Как найти повторяющиеся значения в таблице Excel — В этой статье мы рассмотрим разные подходы к одной из самых распространенных и, по моему мнению, важных задач в Excel — как найти в ячейках и в столбцах таблицы повторяющиеся…
Функция СЧЁТЕСЛИМН в Excel с несколькими условиями — объясняем на примерах. — В этом руководстве объясняется, как использовать функцию СЧЕТЕСЛИМН с несколькими критериями в Excel на основе логики И и ИЛИ. Вы найдете примеры для разных типов данных — числа, даты, текст,…
СЧЕТЕСЛИ в Excel — примеры функции с одним и несколькими условиями — В этой статье мы сосредоточимся на функции Excel СЧЕТЕСЛИ (COUNTIF в английском варианте), которая предназначена для подсчета ячеек с определённым условием. Сначала мы кратко рассмотрим синтаксис и общее использование, а затем я…
Как проверить правильность ввода данных в Excel? — Подтверждаем правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода в соответствии с имеющимся списком допустимых значений. В случае правильного ввода в отдельном столбце ставить…

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

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

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

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

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

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

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

Источник

Поиск

Заменить

Поиск

Заменить

Дополнительные сведения

Рекомендуемые статьи

Горячие клавиши

Процедура «Найти и заменить» не работает

Подстановочные знаки, или как найти “звёздочку”

Замена нескольких значений на несколько

Массовая замена с помощью функции “ПОДСТАВИТЬ”

Файл-шаблон с формулой множественной замены

Заменить несколько значений на одно

С помощью функции «ПОДСТАВИТЬ»

С помощью регулярных выражений

Массовая замена в !SEMTools

Пример: замена символов по вхождению

Пример: замена списка слов на другой список слов

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

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

wikiHow Video: How to Replace Values in Excel

References

About This Article

Is this article up to date?

Поиск нестрогого соответствия символов

Продвинутый поиск

Поиск и замена данных

Как работает функция ЗАМЕНИТЬ

Использование функции ЗАМЕНИТЬ с числами

Как заменить часть даты

Как заменить сразу несколько букв или слов

Как заменить текст в разных местах

Заменить заглавные буквы на строчные и наоборот

Описание функции ПОДСТАВИТЬ

Замена нескольких значений одной формулой

Что лучше использовать – ЗАМЕНИТЬ или ПОДСТАВИТЬ?

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

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

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

Функция ЗАМЕНИТЬ() в MS EXCEL

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

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

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

Изменение содержимого ячейки

Как Excel работает по-разному в режиме редактирования?

Включение и отключение режима редактирования

Режим редактирования

Вставка, удаление или замена содержимого ячейки

Отмена или Отмена изменений

Изменение способа отображения содержимого ячейки

Выход из режима редактирования

Функция «ЗАМЕНИТЬ» в Excel.

Поменять ссылки на другие листы в формулах Excel.

4 способа заменить точку на запятую в Excel

Как в Excel заменить точку, на запятую?

Способ 1 замена точки на запятую в Excel через Блокнот

Способ 2 временно меняем настройки Excel

Способ 3 временно меняем системные настройки Windows

Способ 4 используем функцию найти и заменить в Excel.

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

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

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

Как убрать повторяющиеся значения в Excel?

Удаление повторяющихся строк вручную

Удаление дубликатов в «умной таблице».

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

Как убрать дубликаты строк с помощью формул.

Формулы для поиска повторяющихся значений в одном столбце

Формулы для поиска повторяющихся строк.

Duplicate Remover — универсальный инструмент для поиска и удаления дубликатов в Excel.

Как избавиться от дубликатов в Excel в 2 клика мышки.

Больше возможностей для поиска дубликатов при помощи Duplicate Remover.

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

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

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

А вот еще интересные статьи: