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 на с. 24), а вихідна таблиця набере вигляду, як показано на рис. 11 (с. 24). Усі ноутбуки марки «
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.
