Excel 2007: Поиск и замена
До настоящего момента мы работали с Excel как с мощным инструментом организации вычислений. Безусловно, без формул, форматов и представлений работать с электронной таблицей нельзя. Но для бухгалтера важно не только это: в его работе наиболее полезными являются средства обработки данных. Именно они позволяют одним действием отсортировать таблицу, получить из нее итоги, построить сводный отчет, отсортировать его и т. д. Но одна функция Excel занимает особое положение. Речь идет об инструменте «Поиск и замена». Формально (!) «Поиск и замена» не имеет отношения к обработке данных. Он не требует специальной организации рабочего листа, наличия в нем заголовков (полей) и т. п. С другой стороны, «Поиск и замена» обладает главным преимуществом, которое отличает инструменты работы с данными от обычных вычислений, — он оперирует с большими массивами, рассматривая их как единое целое. Как работать с поиском и заменой и как применить их бухгалтеру в своей работе, мы рассмотрим в этой статье.
Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
Скажу с самого начала: революционных изменений в поиске и замене Excel 2007 не предлагает. Все, как и в старом добром Excel 2003. Поэтому мы сосредоточимся не на самом инструменте, а на примерах его использования и, конечно, коснемся некоторых недокументированных возможностей поиска и замены в Excel 2007.
Зачем нужен поиск бухгалтеру
При работе с большими таблицами часто приходится искать информацию по некоторому условию или выполнять замены одних элементов данных другими. Вот несколько простых примеров.
Есть большой прайс, в котором несколько сотен строк с наименованиями товаров. Наименования подробные: в них указана не только марка товара, но и ее характеристики. Мне нужно быстро найти в прайсе товары с определенной характеристикой. Обратите внимание: никакой обработки таблицы я делать не хочу. Моя задача — быстро найти нужную информацию, и не более того.
Еще пример. В таблице записаны сведения по оплатам поставок ТМЦ за предыдущий период. В тех ячейках таблицы, где не было оплат, стоят прочерки. Я хочу заменить прочерки на пустые значения, чтобы получить возможность выполнять арифметические действия над всеми элементами таблицы. Вариантов масса, но все они сводятся к одному — нужно найти данные по определенному условию и затем (при необходимости) заменить одно значение на другое. Для решения такой задачи в Excel предусмотрен специальный инструмент «
Поиск и замена». Работу с ним мы рассмотрим на примере прайса, изображенного на рис. 1.
В этом документе есть данные о ценах на ноутбуки. В первой колонке находится код товара, затем подробное описание. Правее расположены две цены — оптовая и розничная, причем розничная цена посчитана по формуле как оптовая плюс фиксированная наценка. Формула в ячейке «
D2» выглядит так: «=ОКРУГЛ(C2+C2*0,01;0)». Эта формула скопирована на всю высоту таблицы. Наша первая задача будет такой — среди многообразия предлагаемых товаров найти ноутбуки марки « Lenovo».
К
ак найти информациюОбратиться к инструменту «
Поиск и замена» можно через ленту основного меню программы. Дальше делаем так:1) вызываем меню «
Главная» и находим группу иконок «Редактирование». Она расположена слева на ленте основного меню (рис. 2 на с. 19);
2) щелкаем левой кнопкой мыши на иконке «
Найти и выделить». Раскроется меню, изображенное на рис. 3. В этом меню нас интересуют пункты «Найти…» и «Заменить…»;
3) щелкаем на пункте «
Найти». Появится окно, как на рис. 4.
Совет
Для вызова окна «Найти и заменить» пользуйтесь комбинацией «Ctrl+F». Для переключения закладок «Найти» и «Заменить» используйте комбинации «Ctrl+PgUp», «Ctrl+PgDn».В верхней части окна есть две закладки («Найти» и «Заменить») и четыре кнопки. Начнем с закладки «Найти» — это и есть инструмент поиска. Он позволяет быстро находить информацию по принципу совпадения с некоторым условием. Это средство анализирует содержимое ячеек, находит среди них первую, которая удовлетворяет критерию, и перемещает в нее указатель активной ячейки. Продолжив поиск, вы найдете следующую ячейку и т. д. Обычно в качестве условия берут строку символов или чисел. Обратите внимание: осуществляя поиск, Excel анализирует факт присутствия указанной строки в содержимом ячейки.
В режиме поиска (рис. 4) доступны кнопки «Найти все», «Найти далее» и «Закрыть».
По кнопке «Найти все» Excel сформирует список из всех ячеек, попавших в результат поиска. Этот список он покажет в нижней части окна «Найти и заменить».
Кнопка «Найти далее» находит ячейку, удовлетворяющую заданному условию, делает ее текущей и останавливает поиск. Повторное нажатие «Найти далее» продолжает поиск до следующего совпадения. Кнопка «Закрыть» закрывает работу с окном.
Теперь посмотрим, как все это работает на практике. Делаем так:
1) ставим указатель активной ячейки в начало документа, например на ячейку «A1»;
2) в поле «Найти» (рис. 4) вводим текст «lenovo»;
3) нажимаем кнопку «Найти далее». Указатель активной ячейки остановится на ячейке «B3». Все верно. В ней есть текст «Ноутбук lenovo IdeaPad S10…»;
4) еще раз нажимаем кнопку «Найти далее». Указатель переместится на ячейку «B6» с текстом «Ноутбук lenovo IdeaPad S12 (59-025907) Black» и т. д. Когда после очередного нажатия кнопки «Найти далее» будет достигнут конец списка, Excel возобновит поиск с начала документа;
5) нажимаем кнопку «Найти все». В нижней части окна поиска и замены появится список указателей на ячейки, в которых есть текст «lenovo» (рис. 5). В этом списке видно название рабочей книги, имя листа, адрес ячейки и найденное в ней значение;
6) щелкаем на любом элементе списка. Активная ячейка переместится на соответствующий адрес рабочего листа.
Важно!
Работа в окне «Найти и заменить» позволяет быстро переходить на отдельные ячейки листа или рабочей книги. Для этого предварительно найдите эти ячейки, обратившись к режиму «Найти все». Excel построит список элементов. Теперь достаточно одного щелчка мышью, чтобы мгновенно попасть на любую ячейку из предложенного списка.Для примера сделайте так:
1) в окне «Найти и заменить» (рис. 5) щелкните на первой строке в списке найденных значений. Активная ячейка переместится на адрес «B6»;
2) не закрывая окно, щелкните на ячейке «A1»;
3) вернитесь в окно «Найти и заменить». Выберите третий элемент в списке найденных значений. Активная ячейка станет на адрес «B10». Думаю, идея понятна.
Важно!
Для перемещения по рабочему листу закрывать окно «Найти и заменить» необязательно. Лист доступен и при открытом окне. Это несомненное преимущество инструмента поиска и замены.Кнопка «Параметры» открывает дополнительные возможности для управления режимом поиска и замены. На рис. 2 эти параметры скрыты, окно представлено в «минимальном» варианте. Использование параметров позволяет не просто отыскать элемент на рабочем листе, но и выполнить это с учетом формата. Сделайте так:
1) поставьте указатель активной ячейки в любом месте прайс-листа, например на ячейке «B4»;
2) перейдите в меню «Главная»;
3) на закладке «Шрифт» найдите кнопку «Цвет заливки». Щелкните на ней левой кнопкой мыши;
4) из палитры выберите цвет фона. Я остановился на варианте «Желтый». Теперь ячейка «B4» в прайсе оформлена на желтом фоне;
5) нажмите «Ctrl+F». Появится окно «Найти и заменить»;
6) поле «Найти:» оставьте пустым;
7) щелкните на кнопке «Параметры». Окно «Найти и заменить» примет вид, как на рис. 6;
8) щелкните на кнопке «Формат…». Раскроется меню из трех пунктов (рис. 7 на с. 21);
9) выберите пункт «Формат…». Появится стандартное окно форматирования ячеек, только называется оно «Найти формат». В нем пять закладок: «Число», «Выравнивание», «Шрифт», «Граница», «Заливка» и «Защита»;
10) перейдите на закладку «Заливка», выберите цвет фона «Желтый»;
11) в окне форматирования нажмите «ОК»;
12) в окне «Найти и заменить» нажмите «Найти все». В списке найденных ячеек появится значение «B4». Excel обнаружил единственную ячейку с желтым фоном . Точно так вы можете найти элементы таблицы с любыми атрибутами форматирования. Например, отыскать ячейки, где текст «lenovo» напечатан красными буквами на сером фоне.
Важно!
При работе с большими базами данных форматирование часто используют для пометок на рабочем листе. В этом случае инструмент поиска и замены дает уникальную возможность. Зная формат, я могу выбрать все записи с цветными пометками и затем быстро перемещаться между ними, как бы далеко они ни находились.Возможность искать ячейки с учетом форматирования открывает очень интересную возможность. Я предлагаю рассмотреть ее на отдельном примере. Пример этот построен исключительно на личном опыте, но я думаю, что многим он будет интересен.
Пример 1. Находим сумму по формату.
Проблема такова. Работая с таблицами, я часто выделяю цветом ячейки одной смысловой группы, например доходы, определенные виды платежей, поступлений и т. п. В какой-то момент возникает желание просуммировать эти ячейки. И единственный критерий для такого суммирования — это формат. В Excel нет средств прямого решения такой задачи, т. е. я не могу просуммировать «все желтые» или «все красные числа». Не могу, если не знаю один секрет: через функцию поиска можно не только найти ячейки, но и выполнить над ними арифметические операции. Иногда это очень удобно, особенно если ячейки находятся на разных листах, а листы имеют неодинаковую структуру. Делаем так:
1) на листе «ПЛ» выделяем желтым фоном несколько ячеек, например «D7», «D8», «D10»;
2) нажимаем «Ctrl+F»;
3) поле «Найти:» окна «Найти и заменить» оставляем пустым, поскольку мы будем искать информацию не по значению, а по форматированию ячеек;
4) щелкаем на кнопке «Формат». Появится окно «Найти формат»;
5) переходим на закладку «Вид», выбираем из палитры желтый цвет фона;
6) в окне «Найти формат» нажимаем «ОК»;
7) в окне «Найти и заменить» нажимаем «Найти все». В нижней части окна появится список со ссылками на ячейки «D7», «D8», «D10»;
8) удерживая клавишу «Ctrl» (или «Shift»), последовательно щелкаем по элементам этого списка. В информационной строке Excel (правый нижний угол окна) видим сумму выделенных ячеек.
Важно
! Таким простым приемом вы можете обработать значения не только на разных листах, но даже в разных рабочих книгах!Теперь предлагаю вернуться к дополнительным возможностям окна «Найти и заменить» и посмотреть, что еще они нам предлагают.
Параметр «Искать» регулирует область поиска в пределах рабочей книги. Он может принимать два значения: «на листе» или «в книге». Параметр «Просматривать» изменяет последовательность поиска данных на рабочем листе. По умолчанию средства поиска и замены сканируют таблицу «по строкам». Можно указать направление просмотра «по столбцам».
Щелчок на параметре «Область поиска» раскрывает список из трех значений: «формулы», «значения» и «примечания». Этот параметр тоже ограничивает область поиска, только действует на уровне элементов таблицы. Вариант «формулы» означает, что при поиске содержимое ячеек с формулами Excel будет воспринимать как обычный текст. Когда параметр «Область поиска» установлен в положение «значения», поиск ведется не по содержимому формул, а по результату вычислений. Наконец, выбрав из списка пункт «примечания», вы сможете искать данные в текстах примечаний к ячейкам. Думаю, чтобы разобраться с этими параметрами, достаточно одного примера.
Пример 2. Поиск формул и значений.
Сейчас мы посмотрим, как можно регулировать область поиска при организации поиска в документе. Делаем так:
1) создаем новый лист, я назвал его «ПЛ_». Копируем на него содержимое прайса с листа «ПЛ»;
2) возвращаемся на исходный лист «ПЛ»;
3) выделяем колонку «D». Напомню, что в ней записана формула для определения розничной цены товара;
4) нажимаем «Ctrl+F»;
5) в поле «Найти:» окна «Найти и заменить» вводим значение «3296»;
6) параметр «Искать» оставляем в положении «на листе»;
7) список «Просматривать» ставим в положение «по столбцам»;
8) параметр «Область поиска» оставляем в положении «формулы»;
9) нажимаем «Найти все» и видим сообщение, что поиск неуспешен (рис. 8). Все верно. Excel попытался найти значение «3296» в тексте формул! Разумеется, что его там нет;
10) в окне «Найти и заменить» щелкаем на списке «Область поиска». Выбираем вариант «значения»;
11) нажимаем «Найти все». В нижней части окна «Найти и заменить» Excel сформировал список из трех ячеек: «D3», «D4», «D5». Действительно, в этих ячейках колонки «D» записана розничная цена «3296». Excel обработал результаты формул в пределах колонки «D»;
12) в окне «Найти и заменить» изменим параметр «Искать:». Переключаем его в положение «в книге»;
13) нажимаем «Найти все». В списке найденных ячеек будет шесть элементов: «ПЛ!D3», «ПЛ!D4», «ПЛ_!D5», «ПЛ_!D3», «ПЛ_!D4», «ПЛ_!D5».
Иногда при выполнении поиска нужно учитывать регистр символов. Такая ситуация возникает, например, при работе с именами собственными. Включите флажок «Учитывать регистр», и поиск в этом случае будет работать корректно.
Наконец, последний параметр — флажок «Ячейка целиком». Если он включен, Excel рассматривает содержимое ячейки как единое целое. Попробуйте включить параметр «Ячейка целиком» и найти все ноутбуки марки «lenovo», как мы делали в нашем примере. У вас ничего не получится. Поиск не найдет ни одного элемента, ведь в прайсе нет ячеек с текстом «lenovo». А сравнение «lenovo», например, с названием «Ноутбук lenovo IdeaPad S12 (59-025907) Black» при включенном флажке «Ячейка целиком» даст ложный результат. Excel в данном случае будет анализировать соответствие всего текста в ячейке критерию поиска. По умолчанию параметр « Ячейка целиком» выключен.
Однако вернемся к меню на рис. 7. Мы пропустили два пункта:
— «Выбрать формат из ячейки…». Здесь вы можете определить параметры форматирования, выбрав в качестве эталона ячейку рабочего листа. В каком-то смысле этот инструмент похож на «формат по образцу». Вместо того чтобы вводить параметры в окне «Найти формат», вы просто щелкаете на ячейке рабочего листа, после чего форматирование этой ячейки Excel будет учитывать при поиске данных;
— «Очистить формат поиска» отменяет форматирование, указанное в дополнительных параметрах окна «Поиск и замена».
Вне всякого сомнения, в руках опытного главбуха поиск — чрезвычайно мощный инструмент для обработки данных. И все же, на мой взгляд, на практике намного нужнее операция замены. Этой операцией мы сейчас и займемся.
Как выполнить замену данных
Функция замены похожа на поиск. Но у нее есть важное отличие — контекстная замена позволяет не только найти заданный элемент, но и заменить его другим. Например, просмотреть содержимое таблицы и заменить текст «
Lenovo» на текст «Lenovo (IBM)».Важно!
Excel позволяет искать и заменять значения в пределах всей рабочей книги, но по умолчанию областью поиска является текущий рабочий лист. Если предварительно выделить на листе блок ячеек, то область поиска и замены будет ограничена выделенным блоком.Вызвать функцию замены можно через иконку «
Найти и выделить» на ленте «Главная» основного меню. Но есть и другой способ.Совет
Для быстрого вызова контекстной замены используйте комбинацию «Ctrl+H».Окно «
Найти и заменить» в режиме замены выглядит, как показано на рис. 9 (с. 23). Большинство параметров этого окна нам уже знакомы, правда, некоторые из них «удвоились». Так, в поле «Найти:» мы указываем искомый элемент (т. е. что мы хотим отыскать). Кнопка «Формат…» справа от поля «Найти:» дополняет искомое значение еще и параметрами форматирования. Ниже поля «Найти:» расположен параметр «Заменить на:». Сюда мы пишем текст (или число), которое должно заменить собой найденные значения. 
Изменился и состав элементов управления окном: в него добавились кнопки «Заменить» и «Заменить все». Первая выполняет замены по одной. Кнопка «Заменить все» осуществляет все контекстные замены в области поиска. Испытаем работу этих кнопок на практике:
1. Открываем прайс-лист. Убираем все форматирование ячеек.
2. Нажимаем «Ctrl+H».
3. В окне «Найти и заменить» в поле «Найти:» печатаем текст «Lenovo».
4. В поле «Заменить на:» пишем «Lenovo (IBM)».
5. Нажимаем «Заменить все». Во всем прайсе строка «Lenovo» превратилась в «Lenovo (IBM)».
Нажимаем иконку отмены последнего действия. Прайс нам понадобится в первоначальном виде.
Секрет
С помощью кнопок «Формат» справа от поля «Заменить на:» можно заменить одну текстовую строку на другую и выделить все замены специальным форматированием, например красным цветом на светло-желтом фоне. Все сделанные замены будут хорошо видны на рабочем листе.Замена данных с одновременным изменением формата — очень мощный инструмент при работе с данными. Вот пример, как им воспользоваться.
Пример 3. Итак, наша задача: в документе прайс-листа найти и выделить все позиции производителя «lenovo». Делаем так:
1) открываем документ (у нас он показан на рис. 1). Для ускорения поиска можно выделить колонку «B» с названиями ТМЦ;
2) нажимаем «Ctrl+H». Появится окно «Найти и заменить», открытое на закладке «Заменить»;
3) в поле «Найти:» печатаем текст «lenovo»;
4) в поле «Заменить на:» вводим точно такой же текст. Чтобы обеспечить идентичность содержимого в обоих полях, советую пользоваться буфером обмена;
5) в окне «Найти и заменить» щелкаем на кнопке «Параметры», чтобы раскрыть дополнительные настройки поиска и замены;
6) щелкаем на кнопке «Формат» справа от поля «Заменить на:». Появится окно «Заменить формат». Состав и параметры этого окна полностью идентичны окну форматирования ячеек;
7) переходим на закладку «Заливка». В палитре «Цвет фона:» выбираем светло-серый вариант;
8) в окне «Заменить формат» нажимаем «ОК»;
9) в окне «Найти и заменить» нажимаем «Заменить все».
Через некоторое время появится окно с сообщением о количестве выполненных замен (рис. 10), а исходная таблица примет вид, как на рис. 11. Все ноутбуки марки «lenovo» подсвечены серым фоном. Увидеть их в прайсе не составляет никакого труда.
Инструменты поиска и замены можно с успехом (и очень эффективно!) применять для редактирования данных и формул.
Совет
Импортируя в Excel данные из других программ, вы можете столкнуться с ситуацией, когда разделителем целой и дробной части в числах является точка. В Excel десятичным разделителем является знак, который установлен в настройках Windows. Для русского и украинского языков этот разделитель — запятая. Поэтому числа с разделителем-точкой Excel обработает неправильно: он воспримет их как текст. Конечно, можно поменять разделитель в настройках Windows. Но есть и другой способ — замена в блоке ячеек с числами символа «.» (точка) на символ «,» (запятая) — и проблема будет решена.Этим советом не ограничена область использования поиска и замены при работе с рабочим листом. Сейчас самое время вспомнить, что формула — это обычный текст. Это означает, что инструментом «Поиск и замена» можно с успехом корректировать содержимое формул. Иногда это самый быстрый способ исправить ситуацию, если в тексте формул допущена ошибка.
Пример 4. Правка формулы заменой.
Допустим, что в прайсе коэффициент для розничной цены находится в рабочей книге «Наценки». В расчетную формулу этот коэффициент подставлен как ссылка, т. е. формула в ячейке «D2» выглядит так: «=ОКРУГЛ(C2+C2*’D:\!Фактор\ №53(окт)\2007\[Наценки.xls]Лист1’!$A$1;0)». Представим, что я перенес файл с информацией о наценках в другую папку или изменил имя файла и теперь он называется «Наценки_». Разумеется, формула будет работать неправильно. В данном случае можно сделать так:
1) открываем прайс-лист, выделяем колонку «D» и нажимаем «Ctrl+H»;
2) в поле «Найти:» указываем «Наценки.xls». В поле «Заменить на:» — текст «Наценки_.xls»;
3) раскрываем дополнительные параметры;
4) проверяем, чтобы список «Область поиска» находился в положении «формулы»;
5) нажимаем «Заменить все». Excel исправил формулы и тут же пересчитал их значения.
Конечно, в этом примере можно обойтись и без поиска и замены. Достаточно откорректировать одну формулу и затем скопировать ее на всю таблицу. Но это возможно не всегда. Например, если формулы расположены в разных, отдельно расположенных ячейках рабочего листа, прием копирования малоэффективен. В этом случае самый быстрый способ решить проблему — воспользоваться инструментом «Найти и заменить». Кстати, в наших примерах мы не раз вернемся к этому приему.
И последнее. В документации по Excel нет ни слова о возможности использовать подстановочные знаки при работе с инструментом поиска и замены в Excel. Так вот подстановочные знаки использовать можно. Правда, работа с шаблонами поиска в Excel не так актуальна, как, скажем, в Word. Но такая возможность есть, и ей можно пользоваться.
На этой мажорной ноте мы завершим вопрос о работе с инструментами поиска и замены. Надеюсь, что эти сведения будут вам полезны и востребованы на практике. А мы в следующей статье вплотную займемся инструментами обработки данных в Excel 2007.
Желаю успешной работы! Жду ваших писем и предложений на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .
