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

1543

Для вызова команды «Поиск» можно воспользоваться следующими вариантами:

— На вкладке ленты «Главная» в группе «Редактирование» нажать кнопку «Найти и выделить», в появившемся меню выбрать пункт «Найти» (рис. 47);

— Нажатием на клавиатуре комбинации «горячих клавиш» «CTRL» + «F».

Для вызова команды «Заменить» можно воспользоваться следующими вариантами:

— На вкладке ленты «Главная» в группе «Редактирование» нажать кнопку «Найти и выделить», в появившемся меню выбрать пункт «Заменить» (рис. 48);

— Нажатием на клавиатуре комбинации «горячих клавиш» «CTRL» + «H».

01

Рис. 47

02

Рис. 48

После выполнения действий, указанных выше откроется окно «Найти и заменить». Причем при выполнении команды «Найти» в этом окне будет активизирована вкладка «Найти» (рис. 49), а при выполнении команды «Заменить», соответственно вкладка «Заменить» (рис. 50).

03

Рис. 49

04

Рис. 50

На рисунках 49 и 50 показан сокращенный вид окна «Найти и заменить», развернутый вид окно приобретает при нажатии на кнопку «Параметры >>» (рис. 51 – красным отмечены элементы, отсутствующие на вкладке «Найти»). Как видно из рисунков 49-51, разница между вкладками «Найти» и «Заменить» в дополнительных параметрах (поле «Заменить на» с вариантом форматирования, и кнопки «Заменить» и «Заменить все»).

05

Рис. 51

Значения элементов окна «Найти и заменить»:

— Поле «Найти» – заполняется текстом, который нужно найти;

— Выбор варианта форматирования для поля «Найти» – дополнительное условие для поиска, при котором будет учитываться только текст, имеющий требуемый формат;

— Поле «Заменить на» – заполняется текстом, на который нужно заменить искомый текст;

— Выбор варианта форматирования для поля «Заменить на» – выбор формата, который будет применен при замене;

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

— Поле «Искать» – можно выбрать место для поиска: активный лист, или вся книга;

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

— Поле «Область поиска» – если выбран вариант «формулы», то поиск будет осуществляться в тексте формул (если в ячейке нет формулы, то по значениям). Если выбран вариант «значения», то поиск будет осуществляться по значениям ячеек (если в ячейке стоит формула, то анализироваться будет результат этой формулы). Если выбран вариант «примечания», то поиск будет осуществляться в примечаниях к ячейкам;

— Метка «Учитывать регистр» – при установке этой метки, во время поиска будет учитываться регистр, т.е. совпадение искомого текста в плане строчных или заглавных букв;

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

— Кнопка «Найти далее» – при нажатии на эту кнопку будет произведен поиск заданного текста до первого совпадения, при каждом последующем нажатии будет произведен поиск до следующего совпадения;

— Кнопка «Найти все» – при нажатии на эту кнопку будет произведен поиск всех совпадений с указанным текстом, результаты поиска будут выведены в списке в нижней части окна «Найти и заменить» в виде ссылок на ячейки, удовлетворяющие критериям поиска. Путём нажатия на соответствующую ссылку можно перемещаться между найденными ячейками (рис. 52).

— Кнопка «Заменить» – при нажатии на эту кнопку будет произведена замена заданного текста в первой найденной ячейке (поэтому перед первым нажатием на кнопку «Заменить» нужно нажать на кнопку «Найти далее») и поиск следующей ячейки, удовлетворяющей критериям поиска. При последующих нажатиях на кнопку «Заменить» будет произведена замена искомого текста в найденной ячейке и поиск следующего соответствия искомому тексту;

— Кнопка «Заменить все» – замена всех совпадений с искомым текстом на требуемый текст.

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

06

Рис. 52

Примеры использования критериев для поиска

Рисунок 53 – поиск осуществляется по строкам, список найденных ячеек имеет вид: A2, B4, A6.

07

Рис. 53

Рисунок 54 – поиск осуществляется по столбцам, список найденных ячеек имеет вид: A2, A6, B4. Видно явное отличие в порядке найденных ячеек от рис. 53.

08

Рис. 54

Рисунок 55 – поиск осуществляется по столбцам, но только в формулах. В столбце «С» стоит формула, которая «склеивает» текст, находящийся в ячейках слева. Однако, в ячейке C8 стоит формула «=»Текст»&-5». Как видно из рисунка, в результате поиска найдено 2 совпадения – это ячейка A6 (в которой нет формулы, а содержится слово «Текст») и ячейка C8, в которой стоит формула, содержащая слово «Текст».

09

Рис. 55

Рисунок 56 – условия аналогичны предыдущему абзацу, но поиск осуществляется только в значениях. Как видно из рисунка, в результате поиска найдено 5 совпадений – это ячейка A6 (в которой содержится слово «Текст»), ячейки C2, C4, C6 и C8, результаты формул в которых содержат слово «Текст».

10

Рис. 56

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

11

Рис. 57

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

12

Рис. 58

Рисунок 59 – поиск осуществляется только в значениях, но с учетом формата. В качестве критериев формата выбраны:

— Шрифт – Times New Roman;

— Размер шрифта – 12;

— Начертание – курсив;

— Цвет шрифта – белый;

— Цвет фона – красный;

— Границы – нижняя, сплошная, тонкая.

Не смотря на то, что все ячейки с A2 по A12 содержат искомое слово «Текст», только одна ячейка содержит требуемое форматирование – это ячейка A9.

13

Рис. 59

Примеры использования замены

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

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

14

Рис. 60