Найти и заменить в excel — как найти и заменить данные в excel?

Как заменить звездочку «*» в Excel?

Практически наверняка каждый сталкивался со следующей ситуацией — в тексте присутствует символ звездочки, который необходимо удалить или заменить на какой-либо другой текст.Однако при попытке заменить звездочку возникают трудности — при замене меняются абсолютно весь текст, что естественно и логично, так как Excel воспринимает символ «*» как любой произвольный текст.Но мы теперь уже знаем как с этим бороться, поэтому в поле Найти указываем текст «~*» (явно показываем, что звездочка является специальным символом), а в поле Заменить на указываем на что заменяем звездочку, либо оставляем поле пустым, если хотим удалить звездочку:

Аналогичная ситуация и при замене или удалении вопросительного знака и тильды.Производя замену «~?» (для тильды — «~~») мы также без проблем сможем заменить или удалить спецсимвол.

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru

Функция ПОДСТАВИТЬ при условии подставляет значение

Пример 1. В результате расчетов, произведенных в некотором приложении, были получены некоторые значения, записанные в таблицу Excel. Некоторые величины рассчитать не удалось, и вместо числового представления была сгенерирована ошибка “NaN”. Необходимо заменить все значения “NaN” на число 0 в соответствующих строках.

Таблица данных:

Для замены и подстановки используем рассматриваемую формулу в качестве массива. Вначале выделим диапазон ячеек C2:C9, затем введем формулу через комбинацию Ctrl+Shift+Enter:

Функция ЧЗНАЧ выполняет преобразование полученных текстовых строк к числовым значениям. Описание аргументов функции ПОДСТАВИТЬ:

• B2:B9 – диапазон ячеек, в которых требуется выполнить замену части строки;
• “NaN” – фрагмент текста, который будет заменен;
• 0 – фрагмент, который будет вставлен на место заменяемого фрагмента.

Для подстановки значений во всех ячейках необходимо нажать Ctrl+Shift+Enter, чтобы функция была выполнена в массиве. Результат вычислений:

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

Пример использования функции СТАНДОТКЛОНА в Excel

Пример 2. В двух различных группах студентов колледжа проводился экзамен по одной и той же дисциплине. Оценить успеваемость студентов.

Таблица исходных данных:

Определим стандартное отклонение значений для первой группы по формуле:

=СТАНДОТКЛОНА(A2:A11)

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

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

Обратите внимание на то, что функция СТАНДОТКЛОНА преобразует текстовое значение «не сдал» в числовое значение 0 (нуль) и учитывает его в расчетах

Сочетания клавиш для галочки

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

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

После форматирования ячейки просто удерживайте клавишу Shift и нажмите P. Сочетание клавиш здесь — Shift + P.

Это вставит в ячейку символ галочки. Вы также можете вставить это в конец любой строки текста в ячейке.

Другой символ — это противоположность галочки, «x», где вы можете обозначить отрицательный ответ. Удерживая нажатой клавишу Shift, нажмите O. Сочетание клавиш здесь — Shift + O.

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

К другим кодам клавиатуры Wingdings2 относятся следующие.

Если вам нужно больше разнообразия, отформатируйте ячейки шрифтом Wingdings.

Это позволяет использовать четыре возможных сочетания клавиш с галочкой.

Чтобы использовать эти коды клавиатуры, удерживайте нажатой клавишу ALT, а затем введите четырехзначное число с цифровой клавиатуры.

Как видите, существует множество опций и стилей, которые можно использовать для использования флажков в Excel.

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

|Аннушка|

Функция ПОДСТАВИТЬ при условии подставляет значение

Пример 1. В результате расчетов, произведенных в некотором приложении, были получены некоторые значения, записанные в таблицу Excel. Некоторые величины рассчитать не удалось, и вместо числового представления была сгенерирована ошибка “NaN”. Необходимо заменить все значения “NaN” на число 0 в соответствующих строках.

Для замены и подстановки используем рассматриваемую формулу в качестве массива. Вначале выделим диапазон ячеек C2:C9, затем введем формулу через комбинацию Ctrl+Shift+Enter:

Функция ЧЗНАЧ выполняет преобразование полученных текстовых строк к числовым значениям. Описание аргументов функции ПОДСТАВИТЬ:

• B2:B9 – диапазон ячеек, в которых требуется выполнить замену части строки;
• “NaN” – фрагмент текста, который будет заменен;
• 0 – фрагмент, который будет вставлен на место заменяемого фрагмента.

Для подстановки значений во всех ячейках необходимо нажать Ctrl+Shift+Enter, чтобы функция была выполнена в массиве. Результат вычислений:

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

Метод 3: использование фильтра

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

1. Как обычно, выделяем все ячейки таблицы.
2. Во вкладке “Данные” в разделе инструментов “Сортировка и фильтр” ищем кнопку “Фильтр” (иконка напоминает воронку) и кликаем на нее.
3. После этого в строке с названиями столбцов таблицы появятся значки перевернутых треугольников (это значит, что фильтр включен). Чтобы перейти к расширенным настройкам, жмем кнопку “Дополнительно“, расположенную справа от кнопки “Фильтр“.
4. В появившемся окне с расширенными настройками:
   • как и в предыдущем способе, проверяем адрес диапазон ячеек таблицы;
   • отмечаем галочкой пункт “Только уникальные записи“;
   • жмем ОК.
5. После этого все задвоенные данные перестанут отображаться в таблицей. Чтобы вернуться в стандартный режим, достаточно снова нажать на кнопку “Фильтр” во вкладке “Данные”.

Дополнительные опции

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

Искать во всей книге

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

1. В диалоговом окне « Найти и заменить » нажмите « Параметры» .
2. Рядом с меткой « Внутри» щелкните раскрывающееся меню и выберите « Книга» вместо « Лист» .
3. Теперь, когда вы выбираете опцию « Найти все» , Excel будет отображать результаты ячеек из всей книги, а не из одного листа.

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

Учитывать регистр при поиске

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

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

Изменить форматирование значений

Хотя Excel позволяет находить и заменять значения ячеек, вы также можете легко изменить форматирование ячеек. Начать:

1. Нажмите « Главная»> «Найти и выбрать», а затем выберите « Заменить» в раскрывающемся меню.
2. Введите значение ячейки, которую вы хотите найти, в поле Найти .
3. Щелкните параметр « Форматировать» рядом с полем « Заменить меткой».
4. Измените стиль форматирования и цвет фона в соответствии с вашими потребностями.
5. Нажмите « Найти все», чтобы найти ячейки, соответствующие вашему поисковому запросу.
6. Чтобы изменить форматирование ячеек, нажмите « Заменить» или « Заменить все» .
7. Excel автоматически применит стиль форматирования к выделенным ячейкам.

Выделение группы ячеек

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

1. Выделите одну ячейку.
2. На вкладке Home (Главная) кликните по Find & Select (Найти и выделить) и выберите Go To Special (Выделение группы ячеек).

Примечание: Формулы, комментарии, условное форматирование, константы и проверка данных – все это можно найти с помощью команды Go To Special (Выделение группы ячеек). Поставьте галочку напротив Formulas (Формулы) и нажмите ОК.

Примечание: Вы можете искать ячейки с формулами, которые возвращают числа, текст, логические операторы (ИСТИНА и ЛОЖЬ) и ошибки. Также эти опции станут доступны, если вы отметите пункт Constants (Константы).

Excel выделит все ячейки с формулами:

Примечание: Если вы выделите одну ячейку, прежде чем нажать Find (Найти), Replace (Заменить) или Go To Special (Выделение группы ячеек), Excel будет просматривать весь лист. Для поиска в диапазоне ячеек, сначала выберите нужный диапазон.

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

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

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

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

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

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

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

= regexreplace (A1; «\ d»; «#») = regexreplace (A1; «\ w»; «#») = regexreplace (A1; «a-zA-Z»; «#») = regexreplace (A1; «\ S»; «_»)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Хотите так же быстро производить массовую замену в Excel?

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

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

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

Появится диалоговое окно, подтверждающее количество замен, которые будут сделаны. Нажмите ОК для продолжения.

Содержимое ячеек будет заменено.

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

Синтаксис и параметры метода

Синтаксис

Синтаксис при замене подстроки и присвоении переменной возвращаемого значения типа Boolean:

Синтаксис при замене подстроки без присвоения переменной возвращаемого значения:

• variable – переменная (тип данных — Boolean);
• expression – выражение, возвращающее объект Range.

Параметры

Параметр	Описание
What	Искомая подстрока или шаблон*, по которому ищется подстрока в диапазоне ячеек. Обязательный параметр.
Replacement	Подстрока, заменяющая искомую подстроку. Обязательный параметр.
LookAt	Указывает правило поиска по полному или частичному вхождению искомой подстроки в текст ячейки:1 (xlWhole) – поиск полного вхождения искомого текста;2 (xlPart) – поиск частичного вхождения искомого текста. Необязательный параметр.
SearchOrder	Задает построчный или постолбцовый поиск:1 (xlByRows) – построчный поиск;2 (xlByColumns) – постолбцовый поиск. Необязательный параметр.
MatchCase	Поиск с учетом или без учета регистра:0 (False) – поиск без учета регистра;1 (True) – поиск с учетом регистра. Необязательный параметр.
MatchByte	Способы сравнения двухбайтовых символов:0 (False) – двухбайтовые символы сопоставляются с однобайтовыми эквивалентами;1 (True) – двухбайтовые символы сопоставляются только с двухбайтовым символами. Необязательный параметр.
SearchFormat	Формат поиска. Необязательный параметр.
ReplaceFormat	Формат замены. Необязательный параметр.

* Смотрите знаки подстановки для шаблонов, которые можно использовать в параметре What.

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

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

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

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

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

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

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

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

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

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

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

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

Как поменять форматирование слова

Иной полезной функцией данного инструмента является не подмена, а изменение форматирования какого-нибудь слова во всем документе. Допустим, если требуется выделить жирным некое слов в документе, то это производится последующим образом:

1. Также нажмите сочетание кнопок Ctrl + H в документе.
2. В открывшемся окне поле Найти оставьте пустым, а в Заменить на введите разыскиваемое слово.
3. Нажмите клавишу Формат понизу окна и в показавшемся перечне изберите пункт Шрифт.

Формат. Шрифт

4. В настройках шрифта изберите полужирное начертание и нажмите клавишу Ок.

5. В основном окне поиска и подмены нажмите Заменить все.

Заменить всё

6. Опосля окончания подмены все вхождения разыскиваемых слов будут выделены полужирным шрифтом и также будет выведен отчет.

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

Инструменты поиска

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как заменить значения в Excel

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

Чтобы найти и заменить значения ячеек в Microsoft Excel:

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

С помощью функции «Заполнить»

Функция «Заполнить», это довольно давний инструмент. Он может помочь нам и в этом случае.

Как он работает?

Очень просто — вы просто делаете что угодно и после используете функцию. Она пытается понять логику ваших действий и продолжить её.

Давайте рассмотрим пример.

Допустим, у нас есть та же табличка:

Пошаговая инструкция:

В первую ячейку столбика В введите то, что должно получиться после обработки;

В следующую ячейку, то же самое;

А теперь выделите столбик;

И щелкните на «Главная» -> «Заполнить» -> «Мгновенное заполнение»;

Готово! Вот результат:

Эту функцию, естественно, можно использовать не только для удаления текста после символа. Она работает там, где есть логика.

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

Применение в функциях

Как уже говорилось выше, подстановочные знаки в Excel могут использоваться в качестве критерия при сравнении текста в различных функциях Excel (например, СЧЁТЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН, ГПР, ВПР и другие).

Повторим задачу из предыдущего примера и подсчитаем количество сотрудников компании, фамилии которых начинаются на букву «п».
Воспользуемся функцией СЧЁТЕСЛИ, которая позволяет посчитать количество ячеек соответствующих указанному критерию.
В качестве диапазона данных укажем диапазон с сотрудниками (A2:A20), а в качестве критерия укажем запись «п*» (т.е. любая фраза начинающаяся на букву «п»):

Как и в первом примере, в результате мы получили ровно 3 фамилии.

Однако не все функции поддерживают применение подстановочных знаков. Некоторые из них (к примеру, функция НАЙТИ) любой символ воспринимают как текст, даже несмотря на то, что он может быть служебным.
С помощью функции НАЙТИ найдем в тексте позицию вхождения вопросительного знака и звездочки:

Обратным примером служит аналогичная функция ПОИСК, в которой мы должно четко указать что ищем именно служебный символ:

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

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

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

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

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

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

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

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

Способ 3

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

Одна из них – это СИМВОЛ(). Здесь в скобках нужно указать значение от единицы до 255. Число берется со специальной таблицы, в которой указано, какому символу оно соответствует. Для переноса строчки используется код 10.

Теперь о том, как работать с формулой. Например, возьмем данные с ячеек A1:D2 и то, что написано в разных столбцах (A, B, C, D), сделаем в отдельных строчках.

Ставлю курсив в новую ячейку и в строку формул пишу:

Знаком «&» мы сцепляем ячейки А1:А2 и так далее. Нажмите «Enter».

Не пугайтесь результата – все будет написано в одну строку. Чтобы это поправить, откройте окно «Формат ячеек» и поставьте галочку в пункте перенос, про это написано выше.

В результате, мы получим то, что хотели. Информация будет взята с указанных ячеек, а там, где было поставлено в формуле СИМВОЛ(10), сделается перенос.

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

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

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

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

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

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

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

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

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

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

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

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

Хотите так же быстро производить массовую замену в Excel?