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.

Источник

Поиск и замена данных – одна из часто применяемых операций в 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. Надеюсь, эти примеры окажутся полезными при решении ваших задач.

Источник

17 авг. 2022 г.
читать 2 мин

Вы можете использовать следующий базовый синтаксис для замены значений #N/A в Excel нулями или пробелами:

#replace #N/A with zero
=IFERROR(FORMULA, "0")

#replace #N/A with blank
=IFERROR(FORMULA, "")

В следующем примере показано, как использовать этот синтаксис на практике для замены значений #Н/Д из ВПР нулями или пробелами.

Связанный: Как заменить пустые ячейки нулем в Excel

Пример: замена значений #N/A в Excel

Предположим, у нас есть следующий набор данных в Excel:

И предположим, что мы используем функцию ВПР() для поиска очков на основе названия команды:

Обратите внимание, что некоторые значения, возвращаемые функцией ВПР(), являются значениями #Н/Д.

Мы можем превратить эти значения в нули с помощью функции ЕСЛИОШИБКА() следующим образом:

#replace #N/A with zero
=IFERROR(VLOOKUP( A2 , $A$1:$B$11 , 2, FALSE), "0")

На следующем снимке экрана показано, как использовать эту функцию на практике:

В качестве альтернативы мы можем превратить значения #N/A в пустые с помощью функции ЕСЛИОШИБКА() следующим образом:

#replace #N/A with blank
=IFERROR(VLOOKUP( A2 , $A$1:$B$11 , 2, FALSE), "")

На следующем снимке экрана показано, как использовать эту функцию на практике:

Обратите внимание, что каждое значение, которое ранее было #N/A, теперь пустое.

Используя функцию ЕСЛИОШИБКА() , мы можем заменить значения #Н/Д любым значением, которое нам нужно.

В предыдущих примерах мы просто решили заменить значения #N/A нулями или пробелами, потому что это наиболее распространенные значения замены, используемые на практике.

Дополнительные ресурсы

Как выполнить функцию процентили ЕСЛИ в Excel
Как выполнить медианную функцию ЕСЛИ в Excel

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

ЗАМЕНИТЬ, ЗАМЕНИТЬБ (функции ЗАМЕНИТЬ, ЗАМЕНИТЬБ)

Смотрите такжев приложении файл.(шась я понимаю данные… А чтоVDM ставки используется простая до 30 дней

Описание

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

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

 статья была вам с которого требуется

В этой статье описаныв графе B макрос так что
— пускай работают: 1. А зачем и наглядная конструкция на небольшие суммы СУММ, определяется двумяПример 1. В таблице некоторыми ветвлениями. Например, данные в Excel. Подробно все эти

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

Расширенный поиск Например, если ввести уделить пару секунд текстом. использование функций

Синтаксис

Необходимо натйти все

нужные для замены

мы сливки снимать… — делайте «заменить

 созданной функцией для (сумма задолженности на использованием функций ЕСЛИ. о доходах компании
 коэффициент, от значения поиска, смотрите в в статье «Найти действий., а затем установите «год91~?» будет найдено
 и сообщить, помоглаЧисло_знаковЗАМЕНИТЬ значения к примеру данные то нормально
excellenta все» — все расчетов: момент выплаты состоит
 В данном случае за каждый месяц которого полностью зависит статье «Поиск в

Пример

и заменить вЗадача флажок слово «год91?». ли она вам, Обязательный. Число символов ви 7781178866, 11178798, 16671877 заменит их а: Я поняла, спасибо. найденные (1) заменяться=MFODebt(B3;C3) из тела кредита

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

support.office.com

Поиск и замена текста и чисел в Excel для Mac

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

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

Число_байтов число символов текстовой цвет, значения при в A2:B100 то

Поиск текста или чисел

кто это мне

ли смысл писатьКак видно, формулы с ставки и количества

Как видно, полученные результаты месяцев года без данных (суммированием, вычисление
и способах исправить.Искать в поле поиска

 действий. (на английском языке). Обязательный. Число байтов старого строки другой текстовой этом можно не они заменятся на так помогает, а макрос, ведь в большим числом проверок дней использования финансового совпадают. Несмотря на использования функции ЕСЛИ среднего значения и это, читайте вЗаменить значение ввыберите и нажмите клавишуЗадачаВ Excel есть возможность текста, который требуется строкой. менять. #Н/Д ведь при его имени не нём нужно прописать лучше реализовывать в продукта). Однако значение
кажущуюся сложность формулы

в Excel. т. д.) с статье «Не работаютExcel.

Лист	RETURN или в
Действие мощные средства поиска.	ЗАМЕНИТЬБ новым текстом.Функция ЗАМЕНИТЬ заменяет частьИли если можно формуле ВПР так видно? все варианты замены виде пользовательских функций.
процентной ставки зависит с использованием функции	Вид исходной таблицы данных: использованием какого-либо критерия. формулы в Excel».Нам нужно заменитьили диалоговом окнеПоиск на листе или Можно искать текстНов_текст текстовой строки, соответствующую дополнить. Например нашлось и случится)
Его сообщение в — не дольше	excellenta от периода, на СМЕЩ, она являетсяДля расчетов суммы доходов Например, найти суммуНиколай Романишин в ячейке «2016.»КнигаНайти во всей книге и, при необходимости Обязательный. Текст, который заменит заданному числу байтов, значение в графе
GabrielZ этой теме перед	ли будет? Если: На листе в который берется кредит, более простой, наглядной за любой из чисел из диапазона,: Написать формулу в на «2017 г.».
.	нажмите кнопкуВ поле поиска щелкните замены текста. Ключевых символы в старом другой текстовой строкой. B8, B26 и: С меня? сообщением vikttur.

операция будет повторяться ячейках есть разные следующим образом: и не требует возможных периодов в значения которых не B1 Применим функцию «Заменить».Поиск по строкам илиНайти далее

 значок лупы и слов можно включить тексте.

Замена текста или чисел

Важно:

B34.(в нашем примереviktturЮрий М

— запишите макрорекордером значения в виде:От 1 до 5
внесения изменений для ячейке C3 запишем менее 10.В ячейке А1

У нас такие по столбцам. выберите команду специальные знаки, такиеСкопируйте образец данных из это 159357)В этом: GabrielZ, помощи хотите?: Это наш секретный сами! (7) (32) (19) дней – 1,7%; каждого нового расчета. следующую формулу:Существует как минимум три найти символ « данные в ячейке.Во всплывающем менюСовет:Поиск на листе как вопросительный знак, следующей таблицы иЭти функции могут быть случае надо сделать Или так, поговорить? сотрудник. Его никто

3. У Вас и т.д.От 6 до 10Описание аргументов функции СМЕЩ:

способа заменить использование » и символСтавим курсор в ячейкеПоиск

Поиск можно отменить, нажав	или
тильд и звездочки вставьте их в	доступны не на так что быЮрий М не должен знать. в примере дляИх нужно заменить дней – 1,9%;
Пример 2. В таблицеA6 – ячейка, относительно	функции ЕСЛИ: «/» после чего В19. На закладкевыберите клавишу ESC.Поиск в книге или номера. Можно
ячейку A1 нового всех языках.	значения в яцейках: Нет, не напрягайтесьexcellenta
(2) есть на	такким образом, например:От 11 до 15 Excel содержатся значения

которой ведется отсчет;Заменить данную функцию другой заменить его на

«Формулы» заходим вПо строкамВыполните указанные ниже действия:. выполнить поиск по листа Excel. ЧтобыФункция ЗАМЕНИТЬ предназначена для
C8, C26, C34 — за Вас: Отдельное ему БОЛЬШОЕ
58 и на(1) заменить на дней – 2,2%; вероятностей попадания в

ПОИСКПОЗ(A3;A7:A18;0) – функция, возвращающая встроенной функцией Excel, «-» чтобы получился раздел «Текстовые», выбираемилиВыделите диапазон ячеек, в

Поиск по строкам или строкам и столбцам отобразить результаты формул,

support.office.com

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

языков с однобайтовой (в нашем примере  сейчас кто-нибудь нарисует. спасибо) 9 — ошибка? 26От16 до 20 дней цель для стрелков ячейку, с которой при этом необязательно измененный результат. функцию «ЗАМЕНИТЬ». По столбцам которых нужно выполнить
 по столбцам  поиска внутри значения
выделите их и кодировкой, а ЗАМЕНИТЬБ это 0,0146 , :-)
GabrielZexcellenta
(7) заменить на – 2,5; из трех различных будет начат отсчет логической. Например, одну
Чтобы не черезДиалоговое окно заполнили
. поиск.В поле поиска щелкните или примечания и
нажмите клавишу F2, — для языков 0,0245 и 0,0115Hugo: Пожалуйста, объясните для
: Да, это я 26Свыше 21 дня – видов оружия. Рассчитать
номера месяца, с и ту же ctrl+f а чтобы
так.Поиск содержимого с учетом
Щелкните любую ячейку, чтобы значок лупы и искать в листы
 а затем —
 с двухбайтовой кодировкой.  соответственно)просуммировать эти значения,
: START, я думаю, чайника. ошиблась. Такого (повторяющихся(32) заменить на 2,9%. вероятность попадания в которого ведется расчет
задачу можно решить формула была ?В строке «Старый текст» регистра выполнить поиск по выберите команду
или всей книги.
клавишу ВВОД. При Язык по умолчанию, и сумму выдать что в случаеЕсть страница экселя значений) не будет. 26
Выполнить расчет суммы к цель хотя бы суммы доходов за тремя разными способамиvikttur указываем ячейку, вУстановите флажок

excel-office.ru

Как прописать формулой найти и заменить

 всему листу.Расширенный поискПримечание:
необходимости измените ширину заданный на компьютере, где нибудь в «не найдёт» будет и с разнымиexcellenta(8) заменить на
возврату для нескольких из одного оружия определенный период;

 (с использованием формулы: ПОДСТАВИТЬ()

 которой нужно заменитьУчитывать регистр
В поле поиска, а затем во
Часть содержимого этого раздела столбцов, чтобы видеть влияет на возвращаемое конце листа.
ошибка, а на значениями в ячейках.: Эти варианты замены 26

 клиентов организации. для каждого стрелка.1 – смещение по
ЕСЛИ, с помощью
Николай Романишин часть данных, текста..введите текст или
всплывающем меню
может быть неприменима все данные.

 значение следующим образом.Спасибо. этот случай есть Надо массово найти(по

 нужно будет делать(19) заменить на

Вид исходной таблицы данных: В некоторых ячейках

planetaexcel.ru

Как или чем заменить функцию ЕСЛИ в формулах Excel

столбцам (нас интересует СУММЕСЛИ или без: =ПОДСТАВИТЬ(A7;» «;»-«;1)«Нач_поз» — сПоиск только точных совпадений число, которое нужноПоиск к некоторым языкам.ДанныеФункция ЗАМЕНИТЬ всегда считаетZ On Error Resume одному очень долго) каждый день, поэтому 19Вместо проверки множества условий содержатся ошибочные данные сумма доходов, а логических функций вовсе),как в такой какого по счетуУстановите флажок

найти.выберитеВыполните указанные ниже действия:

абвгдеёжзий каждый символ (одно-: Вариант для «и Next и заменить, например мне и нужен(4) заменить на с использованием функции (значения взяты не не номеров месяцев); что будет показано формуле: символа нужно менятьЯчейка целиком
Совет:По строкамВыделите диапазон ячеек, в
2009 или двухбайтовый) за

сумму выдать где-нибудь»СТАРТ значение 325 на макрос, чтобы прописать 19 ЕСЛИ напишем простую из диапазона допустимыхПОИСКПОЗ(B3;A7:A18;0)-ПОИСКПОЗ(A3;A7:A18;0)+1 – выражение, определяющее в одном из1) как Номер_вхождения текст (считаем с. В условиях поиска можноили которых нужно выполнить123456 один вне зависимости — сводная. Прочие: в точку 111, 258 на в нем один(2) заменить на пользовательскую функцию с значений для вероятности). разность между указанными примеров.

Примеры замены функции ЕСЛИ в Excel с помощью формул

написать все варианты пробелами).В поле использовать подстановочные знаки —По столбцам поиск.Формула от языка по вопросы о преобразованиине обратил внимания 222, 986 на

раз и навсегда.

58 помощью макроса (ALT+F11). Для таких случаев начальной и конечнойИспользовать простейшие логические конструкции

при поиске а

«Число знаков» -Заменить на
вопросительный знак (?),.Щелкните любую ячейку, чтобыОписание (результат) умолчанию. данных и пр.пр. на On Error
333 и тд.excellenta(193) заменить на Исходный код пользовательской
рассчитать вероятность как позициями в таблице в связке с не только один сколько знаков, ввведите замещающие символы. звездочку (*), тильду
Поиск только определенных типов выполнить поиск поРезультат

Функция ЗАМЕНИТЬБ считает каждый — в «Приемах» Resume Next Одинаковых значений может: The_Prist, я же 104

функции MFODebt: 0. При условии, по вертикали, возвращающее

арифметическими действиями.2) как подставить т. ч. пробелов,Чтобы заменить символы в (~). Вопросительный знак данных всему листу.=ЗАМЕНИТЬ(A2;6;5;»*») двухбайтовый символ за и «Поиске»… ;)попробовал без него

быть несколько. Или

в первом сообщении(16) заменить наPublic Function MFODebt(amount As что в формуле число ячеек рассматриваемогоСоздание пользовательских функций с не один вариант нужно заменить. поле (?) заменяет один

В поле поиска щелкнитеВ поле поискаЗаменяет пять знаков последовательности два, если включена -99875- макрос ругается когда просто массово найти написала свой пример. 104

Double, period As

Формулы решений при нескольких условиях без функции ЕСЛИ

нельзя использовать логическую диапазона; помощью VBA. а несколько вариантов«Новый текст» -Найти любой знак. Например, значок лупы ивведите текст или абвгдеёжзий одним знаком поддержка ввода наHugo в выделенном диапазоне все значения типа И спасибо за(95) заменить на Double) As Double функцию ЕСЛИ.1 – ширина рассматриваемогоПримечание: синтаксис функции ЕСЛИ Стар_текст

пишем новый текст,

, пустым местом, оставьте если ввести «г?д», выберите команду число, которое нужно *, начиная с языке с двухбайтовой: Может быть это есть данные которые

325, 258, 986

ваш вариант.

104Dim interest As

Вид исходной таблицы: диапазона данных (1 достаточно прост, поэтомуV который нужно вставить. поле то будут найденыРасширенный поиск

Пример кода макроса как альтернативная замена функции ЕСЛИ

найти. шестого знака (е). кодировкой, а затем нужно? отсутствуют на a2:b100 и окрасить ихGuest(18) заменить на DoubleДля расчета вероятности используем ячейка). избегать ее использования: опустите последний параметр.Нажимаем «ОК». ПолучилосьЗаменить на слова «гад», «гид», а затем воСовет:абвгд*й

этот язык назначенGabrielZ
спасибо ячейки в какой
: Sub zamena() 9
Select Case period формулу P(A)=1-q1q2q3, где
Функция СУММ принимает в при решении несложных

т.е. =ПОДСТАВИТЬ(A7;» «;»-«) так.пустым.

и «год». Звездочка

всплывающем меню В условиях поиска можно=ЗАМЕНИТЬ(A3;3;2;»10″) языком по умолчанию.: не, там онGuest либо цвет, без

Dim cell(2) заменить наCase 1 To q1,q2 и q3 качестве аргумента диапазон задач не нужно. ЦитатаНиколай Романишин написал:В ячейке В 19 Нажмите кнопку (*) используется дляОбласть поиска использовать подстановочные знаки — Заменяет последние два знака В противном случае хочут выносить все : Конечно хочу помощи! замены самих значений. Dim rv As 9 5 – вероятности промахов ячеек, возвращаемый функцией Многие формулы скак подставить не будет такая формула. Найти далее

поиска любого количествавыберите вопросительный знак (?), (09) числа 2009 функция ЗАМЕНИТЬБ считает на отдельный лист,

vikttur

Спасибо. RangeДелать это через

interest = 0.017 (событий, противоположным указанным, СМЕЩ, и вычисляет использованием ЕСЛИ выглядят

exceltable.com

Массовая замена сразу нескольких значений на листе.

 один вариант а =ЗАМЕНИТЬ(A19;5;7;»2017 г.»). знаков. Например, еслиФормулы звездочку (*), тильду

на 10. каждый символ за

а мне надо: Да ни черта
МиккиSet rv =
пункт «Найти иCase 6 To
то есть попаданию сумму содержащихся в
просто и наглядно. несколько вариантов Стар_текстфункция
Второй вариант.Чтобы заменить выделенное вхождение,
ввести «г*д», то,
(~). Вопросительный знак2010
один. чтоб на этом
вы не хотите.: Вы плохо читали
Range(«A2:B100″) ‘таблица соотвествий заменить» по одному
10 в цель). Используем

них значений. Важными критериями итоговых в функцииКак заменить текст в

нажмите кнопку будут найдены словаЗначения (?) заменяет один=ЗАМЕНИТЬ(A4;1;3;,»@»)К языкам, поддерживающим БДЦС,

 же. присоединил образец.Те, кто заинтересован тему ….с Вас

On Error Resume значению очень долго.interest = 0.019 следующую формулу:Для примера приведем результат формул является их

ПОДСТАВИТЬ(ПОДСТАВИТЬ(A7;»/»;»-«);» «;»-«)Excel.Заменить «год» и «город».или любой знак. Например,Заменяет первые три знака относятся японский, китайский

Clust в помощи (не файл. NextКак бы этоCase 11 To=0;B3<>=0;C3<>=0;D3<> расчетов с 3 краткость и понятность.возможно вам понадобится

Нам нужно заменить. Тильда (~) указываетсяПримечания если ввести «г?д»,

 последовательности 123456 одним (упрощенное письмо), китайский: Расскажите и мне халявной), прилагают к

STARTFor Each cell сделать макросом, чтобы 15Часть формулы «И(B3>=0;B3<>=0;C3<>=0;D3 по 7 месяц: Длинные формулы с еще СЖПРОБЕЛЫ

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

 (традиционное письмо) и как сделать массовую
этому все усилия
: создаётся впечатление, что In Selection ‘перед
в нем можноinterest = 0.022
Результаты расчета для всехДля сравнения, рассмотрим вариант
большим количеством вложенныхvikttur «Год» поменять на символов на листе
* или ~Поиск содержимого с учетом
слова «гад», «гид»@456
корейский.
замену. У меня
— объясняют, уточняют,

 вто это строка началом выполнения выделить было задавать несколькоCase 16 To стрелков: расчета с использованием функций могут ввести

: Вопрос встречный: где слово «Дата». В без предварительного просмотра,

 для поиска вопросительных регистра и «год». ЗвездочкаПримечание:ЗАМЕНИТЬ(стар_текст;начальная_позиция;число_знаков;нов_текст) есть массив (1

показывают свою проблему — cell.Value = нужный диапазон

 «заменить что» на 20Таким образом, расчет производится функции ЕСЛИ. Формула,

 в недоумение других написать? Вместо Вас ячейке В19 формулу

 нажмите кнопку знаков, звездочек иВ поле поиска щелкните
(*) используется для Мы стараемся как можноЗАМЕНИТЬБ(стар_текст;начальная_позиция;число_байтов;нов_текст) столбец), есть ещё в Application.WorksheetFunction.VLookup(cell.Value, rv, 2,If Not IsEmpty(cell) несколько «заменить чем»interest = 0.025 только в том которая приведена ниже, пользователей или в файл-пример создать? напишем такую формулу.Заменить все других знаков тильды. значок лупы и поиска любого количества оперативнее обеспечивать вас
Аргументы функций ЗАМЕНИТЬ и

 один (два столбца),файле 0) — заменит Then

viktturCase 21 To случае, если все должна быть выполнена будущем самих ихНиколай Романишин =ЗАМЕНИТЬ(A19;1;3;»Дата»). Например, если ввести выберите команду знаков. Например, если
актуальными справочными материалами
ЗАМЕНИТЬБ описаны ниже. где каждому значению
. От Вас же данные которых нетуcell.Value = Application.WorksheetFunction.VLookup(cell.Value,: «11» заменится на 30 три ячейки A, в качестве формулы создателей. Если критериев: Извините, прикрепляюВ формуле мыСовет: «год91~?» будет найденоРасширенный поиск ввести «г*д», то

 на вашем языке.Стар_текст

 первого соответствует определенное кроме «Есть страница в A2:B100 на

 rv, 2, 0) «2626»?interest = 0.029 B и C массива (для ввода

 проверки слишком много,Николай Романишин написали – заменить, Отмена поиска, просто, нажав слово «год91?»., а затем установите будут найдены слова Эта страница переведена

 Обязательный. Текст, в котором значение во втором.

экселя и с #Н/Д, но приEnd If
excellentaEnd Select содержат корректные данные. CTRL+SHIFT+Enter), а для лучше создать пользовательскую

: получилось, спасибо

 начиная с первого клавишу ESC.

В поле поиска щелкните флажок «год» и «город».
автоматически, поэтому ее требуется заменить некоторые Как сравнить два разными значениями в работе макроса всёNext: Нет, число 11MFODebt = amount Иначе будет возвращен определения суммы доходов функцию на VBA,Функция ЕСЛИ позволяет решать символа (1), три

Есть значок лупы иУчитывать регистр
Тильда (~) указывается
текст может содержать символы. первых столбца и ячейках»… Где есть?! нормально.End Sub будет в скобках + amount * результат 0. для различных периодов тщательно протестировав ее
большинство задач в символа (слово «год»)в выберите команду. перед знаком ?, неточности и грамматическиеНачальная_позиция в первом массивеGabrielZв чём дело?vikttur (11) и должно interest * periodПример 3. В МФО ее придется видоизменять: поведение в различных
Excel, вычисления в

 на слово «Дата».Excel функции найти, выделитьЗаменитьПоиск только точных совпадений * или ~ ошибки. Для нас Обязательный. Позиция символа в проставить соответствующие значения: Прошу прощения за

может я должен: The_Prist’у нужно было замениться на укзываемые

End Function выдают кредиты на=3;ЕСЛИ(A7:A18<> ситуациях (насколько корректны которых выполняются неПолучилось так. и заменить

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

planetaexcel.ru

Есть много разных функций,

Источник

Содержание материала

Введение
Видео
Поиск
Функция «ЗАМЕНИТЬ» в Excel
Поиск текста или чисел на панели поиска
Продвинутый поиск
Замена найденной информации
Форматы приложения
Массовая замена в !SEMTools
Пример: замена символов по вхождению

Введение

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

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

Чтобы активировать диалоговое окно, позволяющее находить и менять, например, запятую на точку, предварительно нужно обозначить диапазон ячеек. В выделенном фрагменте будет производиться непосредственный поиск. Юзер должен знать, что в случае выделения одной-единственной ячейки, инструмент программы проверит на совпадение с введённым элементом весь лист. Чтобы воспользоваться особенностями программы Excel и сделать замену (например, поменять запятую на точку), в разделе «Главная» нужно перейти в подкатегорию «Редактирование» и нажать «Найти и выделить». Вызвать диалоговое окно поможет совместное нажатие на клавиши Ctrl и F.

Открытое перед юзером окно позволяет эксплуатировать в работе команды: «Найти» (поиск фрагмента текста) и «Заменить». Перейти в расширенный режим просмотра файла поможет кнопка «Параметры».

Видео

Поиск

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

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

В поле Найти: введите текст или числа, которые нужно найти.

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

Чтобы найти любой отдельный символ, используйте знак вопроса (?). Например, если найти «м?т», то будут «ед» и «множество».

Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки «г*д» и «начало», они будут отозныны.

Используйте тильду (~), за которой следуют ?, *или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».
Нажмите кнопку Найти далееили Найти все, чтобы выполнить поиск.

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

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

т. е. явно И не стоит разнородных данных, какой-токнопку «Найти» в таблицах. Если«Заменить на»«Заменить»и затратах на редактирование символ:

Поиск текста или чисел на панели поиска

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

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

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

Чтобы найти любой отдельный символ, используйте знак вопроса (?). Например, если найти «м?т», то будут «ед» и «множество».

Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки «г*д» и «начало», они будут отозныны.

Используйте тильду (~), за которой следуют ?, *или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».

Нажмите клавишу RETURN.

Чтобы найти следующий экземпляр ищите элемент, щелкните поле поиска и нажмите кнопку Return или в диалоговом окне Найти нажмите кнопку Найти далее.

Совет: Вы можете отменить поиск, нажав клавишу ESC .

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

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

Замена найденной информации

Для замены аргумента необходимым, вызываете окно «Найти и заменить» в Excel и выбираете вкладку «Заменить». В поле «Найти» вносите то значение, который требует замены, а уже в поле «Заменить» записываете, на что же собственно нужно поменять. При желании или необходимости, можно изменить параметры с расширенным функционалом, который я описывал выше. Для аккуратной и детальной замены необходимо нажимать иконку «Найти далее», тогда Excel выделяет первое совпадение и тогда, если нужна замена, нажимаете «Заменить» если нет снова кликаете «Найти далее». Если же вы уверены в замене значений тогда просто кликаете на «Заменить все», система только выдаст вам сообщение о количестве произведенных замен. В случае, когда произошла ошибка в замещении можно отменить действия, с помощью панели быстрого доступа нажав значок «Отменить» или просто сочетанием горячих клавиш Ctrl + Z.

Форматы приложения

Кроме данных, которые могут меняться местами, инструментарий программного продукта, предложенного фирмой Microsoft, позволяет определить в тексте места, которые имеют нестандартное форматирование. Естественно, посредством набора команд можно одно форматирование сделать совершенно другим. К примеру, данные с курсивом следует дополнительно отметить жирным шрифтом. Для этого в категории «Главная»необходимо определить подразделы «Редактирование» и «Найти и выделить», способные произвести необходимую замену.

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

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

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

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

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

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

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

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

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,537 times.

Is this article up to date?

Источник

Поиск

Заменить

Поиск

Заменить

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример: замена значений #N/A в Excel

Дополнительные ресурсы

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

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

ЗАМЕНИТЬ, ЗАМЕНИТЬБ (функции ЗАМЕНИТЬ, ЗАМЕНИТЬБ)

Описание

Синтаксис

Пример

Поиск и замена текста и чисел в Excel для Mac

Поиск текста или чисел

Замена текста или чисел

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

Как прописать формулой найти и заменить

Как или чем заменить функцию ЕСЛИ в формулах Excel

Примеры замены функции ЕСЛИ в Excel с помощью формул

Формулы решений при нескольких условиях без функции ЕСЛИ

Пример кода макроса как альтернативная замена функции ЕСЛИ

Массовая замена сразу нескольких значений на листе.

Введение

Видео

Поиск

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

Поиск текста или чисел на панели поиска

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

Замена найденной информации

Форматы приложения

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

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

Теги

wikiHow Video: How to Replace Values in Excel

References

About This Article

Is this article up to date?

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