Автофільтр Excel 2007
 | Продовжуючи розмову про обробку даних в Excel 2007, познайомимося з надзвичайно корисним бухгалтерським інструментом — фільтрацією даних. Цей інструмент у роботі бухгалтера посідає особливе місце. З одного боку, він є дуже гнучким засобом для пошуку даних у великих таблицях. З іншого — за допомогою фільтрів можна локалізувати область обробки даних в MS Excel, використовуючи для цього найрізноманітніші обмеження. А це якраз ті завдання, з якими кожен бухгалтер стикається практично щодня. Повертаючись до питання про фільтрацію даних в Excel 2007, зауважу: ці інструменти в новій версії стали набагато кращими. Що б не говорили скептики, а Excel 2007 у плані функціональних можливостей істотно просунувся вперед. Які новинки з’явилися в цій програмі у плані фільтрації даних, чим це допоможе бухгалтеру, нам і належить розібратися. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
Знайомство з інструментами фільтрації в Excel 2007 почнемо з визначення: спочатку пригадаємо, для чого використовують фільтри та які вони бувають. Потім подивимося, як роботу з фільтрами реалізовано у стрічковому інтерфейсі Excel. І зрозуміло, окремо зупинимося на можливостях, що з’явилися в новій версії програми. Отже, розпочнемо.
Що таке фільтрація даних
Фільтри використовують для того, щоб вибрати з бази даних набір записів за певним критерієм. У відфільтрованій базі доступні лише ті рядки, що задовольняють задану умову, решта залишаються за кадром. На відміну від сортування, фільтр не змінює порядок записів у базі. Відфільтровані рядки можна редагувати, видаляти, копіювати, форматувати, виводити на друк, створювати на їх основі діаграми тощо.
В Excel 2007 робота з фільтром набула певного розвитку. Тепер при фільтрації даних можна враховувати їх формат. При обробці фільтром дат і часу з’явилася можливість виконати їх групування. Крім того, Excel 2007 надає в розпорядження бухгалтера зручний механізм роботи з так званими динамічними фільтрами, коли умова може змінюватися залежно від конкретної ситуації. Усі ці переваги, безумовно, стануть у пригоді в роботі бухгалтера, тож розібратися з ними потрібно не поспішаючи.
Інструменти фільтрації в Excel 2007
Як і в попередній версії програми, в Excel 2007 є два інструменти фільтрації даних: автоматичний та розширений фільтри. Їх відмінність полягає головним чином у способі завдання умов для відбору записів. При роботі з автофільтром не потрібно видозмінювати вихідну таблицю. Умови для відбору записів задають у спеціальних діалогових вікнах MS Excel. Щоб застосувати розширений фільтр, у вихідній таблиці доведеться створити спеціальну область, записати до неї умови фільтрації і лише потім використовувати ці умови для відбору даних. Кожен спосіб має свої переваги та недоліки. Автофільтр зручний у роботі, але діапазон можливих умов фільтрації в нього обмежений. Розширений фільтр надає більше можливостей для відбору записів, зате для його застосування потрібно виконати певні маніпуляції з вихідною таблицею. Ми почнемо знайомство з фільтрацією даних в Excel 2007 з автофільтра, оскільки цей інструмент простіший та частіше застосовується на практиці.
Автофільтр
Основне завдання автофільтра — вибрати з бази даних записи за заданою сукупністю умов. Ці умови можна задати для одного конкретного стовпця чи для декількох колонок одночасно. Якщо в роботі використовується багато умов фільтрації, усі вони доповнюють одна одну, тобто дія кожного нового фільтра накладається на роботу попереднього, усе більше обмежуючи підмножину відфільтрованих даних.
В Excel 2007 є три способи фільтрації даних: за значеннями списку, за форматом комірок та за умовами. Усі ці способи взаємно виключають один одного, якщо ви працюєте з одним діапазоном даних чи одним стовпцем таблиці. Наприклад, автофільтром можна відібрати записи, зазначивши діапазон можливих значень або колір комірок у конкретній колонці. Але вибрати з цієї колонки дані з урахуванням і кольору, і значення одночасно в Excel 2007 не можна.
Для роботи з автофільтром Excel 2007 скористаємося добре знайомим реєстром контрагентів, фрагмент якого показано на рис. 1. Він містить сім полів: «
N», «Дата», «НаимПредпр», «ИНН», «ОбщСум», «СумБезНДС», «НДС». Область даних у реєстрі починається з другого рядка робочого листа. У базі контрагентів немає порожніх рядків, тому діапазон даних Excel зможе розпізнати автоматично. Роботу з автофільтром ми почнемо з відбору текстових значень із бази даних.
ФІЛЬТРАЦІЯ ТЕКСТУ
Наше перше завдання при роботі з автофільтром — відібрати з бази даних контрагентів за їх найменуванням (у реєстрі це поле «
Наим Предпр»). Виконуємо такі дії:1) ставимо активну комірку на область бази даних;
2) на стрічці меню «
Главная» знаходимо групу «Редактирование»;3) клацаємо лівою кнопкою по іконці «
Сортировка и фильтр» (рис. 2). Відкриється меню, зображене на рис. 3;
4) з цього меню вибираємо пункт «
Фильтр». У кожній колонці рядка заголовків з’явилися значки вибору. Вони схожі на кнопки випадаючого списку (рис. 1). За їх допомогою можна формувати умови для відбору записів з бази даних. Клацання мишкою по будь-якому значку розкриває список умов. Цей список буде побудовано з урахуванням типу даних в комірках відповідної колонки;5) клацаємо на значку вибору в колонці «
НаимПредпр». Розкриється меню, зображене на рис. 4 на с. 17. У цьому меню нас цікавить пункт «Текстовые фильтры»;
6) переводимо покажчик миші на рядок «Текстовые фильтры». Розкриється меню, зображене на рис. 5 на с. 17. У ньому є сім пунктів. Перші шість — це визначені умови для вибору записів. Останній пункт із назвою «
Настраиваемый фильтр…» дозволяє побудувати призначену для користувача умову для фільтрації записів. Припустимо, ми вирішили відібрати записи з бази за умовою рівності — нас цікавить інформація щодо фірми «Колорит»;
7) з меню на рис. 5 вибираємо пункт «
равно…». З’явиться вікно, зображене на рис. 6 на с. 18. У лівій частині цього вікна знаходиться список для визначення типу операції. Зараз там установлено поле «НаимПредпр» та операцію «равно». У правій частині вікна розташовано список для вибору конкретного значення; 
8) клацаємо лівою кнопкою по цьому списку. З’явиться перелік усіх підприємств, зареєстрованих у реєстрі;
9) вибираємо фірму «
ООО "Колорит"»;10) натискуємо на кнопку «
ОК». База даних набере вигляду, як показано на рис. 7 на с. 18. Усе правильно. На екрані залишилися тільки записи, що стосуються фірми «ООО "Колорит"». 
Порада
Для включення або відключення автофільтра використовуйте комбінацію клавіш «Ctrl+Shift+L».Зверніть увагу, що до результуючої вибірки автофільтр уключив записи з контрагентами «
ООО “Колорит”» і «Ооо “Колорит”». Висновок: при роботі автофільтра регістр символів значення не має.Порада
Якщо при роботі з автофільтром значки вибору заважають читати текст заголовків (перекривають їх), застосуйте до заголовків вирівнювання вліво.Звернутися до автофільтра можна й по-іншому. Наприклад, вибрати пункт «
Данные» головного меню, знайти групу іконок «Сортировка и фильтр» і скористатися значком «Фильтр».Відмінимо
фільтр, щоб показати всю базу даних повністю. Для цього виконуємо такі дії:1) клацаємо по значку вибору в колонці «
НаимПредпр». Розкриється меню, зображене на рис. 4;2) у цьому меню вибираємо пункт «
Снять фильтр с "НаимПредпр"». Реєстр знову можна побачити в повному обсязі.Важливо!
У списку автофільтра (рис. 4) Excel 2007 може показати до 10000 елементів. Це суттєве вдосконалення інструменту «Автофильтр» порівняно з попередніми версіями програми. Ті, кому доводилося працювати з великими базами даних, напевно стикалися із ситуацією, коли в меню автофільтра Excel 2003 міг показати не більше 1000 елементів. У багатьох випадках для бухгалтера цього явно замало.І ще одна приємна новинка. Зверніть увагу на спеціальний значок у нижньому правому куті вікна, зображеного на рис. 4. Це маркер захоплення. Він дозволяє змінити розмір вікна автофільтра і в такий спосіб показати більшу або меншу частину списку його елементів. І, що важливо, розмір цього вікна не обмежується розмірами вікна самої програми Excel. Це дуже зручно. Наприклад, ви можете збільшити ширину вікна зі списком автофільтра, щоб повністю бачити його елементи. У попередніх версіях програми ця ширина була фіксованою та визначалася за розміром відповідної колонки робочого листа. Або такий випадок. Ви коригуєте дві таблиці та хочете постійно тримати їх перед очима. Екран у вас великий, ви зручно розмістили на ньому два вікна Excel та почали роботу. У якийсь момент вам знадобився автофільтр. Працюючи з Excel 2007, ви можете викликати меню автофільтра та збільшити його до будь-якого потрібного розміру, незважаючи на те, що сам Excel при цьому займає лише половину екрана. У попередній версії програми така можливість не була доступна. Розмір списку елементів автофільтра було обмежено як за шириною, так і за висотою.
Тепер повернемося до вікна на рис. 5. У його верхній частині бачимо шість умов для фільтрації текстових даних. Назви відповідних пунктів говорять самі за себе. Думаю, докладні коментарі тут зайві. Наприклад, ми вирішили відібрати з бази даних усі товариства з обмеженою відповідальністю (у нашому випадку — це «
ООО “Колорит”» і «ООО "ЕКСС"»). Тоді можна скористатися пунктом «Начинается с…». Вікно параметрів у цьому випадку майже точно повторює зображене на рис. 6 на с. 18, тільки в області типу операції стоятиме «начинається с…», а в полі значення ми повинні ввести текст «ООО».Порада
Зверніть увагу на пункт «содержит…» у складі меню на рис. 5. Він дозволяє відібрати записи, в яких текстове поле (тобто рядок) містить указане значення у вигляді підрядка. Уявіть, що в нашому реєстрі потрібно відібрати всі підприємства, у назві яких є елемент «ООО». Але частина цих назв виглядає як «ООО "Колорит"», а частину записано як «"Колорит", ООО». У цьому випадку умова «содержит…» зі значенням «ООО» вирішить проблему за лічені секунди. При роботі з текстовими даними вибір за ознакою входження — одна з найкорисніших можливостей.Останнім пунктом у меню на рис. 5 є «
Настраиваемый фильтр». Тут зосереджено найгнучкіші можливості для відбору записів. Клацаємо по ньому лівою кнопкою миші. З’явиться вже знайоме вікно, зображене на рис. 6. Різниця вбачається тільки в одному: коли ми вибирали розділи меню з конкретною операцією, Excel відразу встановив її у списку «Показать только те строки, значения которых:». При роботі з автофільтром, що настроюється, усі умови потрібно вказати вручну. Подивимося, які можливості може запропонувати діалог на рис. 6.У лівій частині вікна знаходиться список можливих умов, що налічує 12 елементів: «
равно», «не равно», «больше», «больше или равно », «меньше», «меньше или равно », «начинается с», «не начинается с», «заканчивается на», «не заканчивается на», «содержит», «не содержит». Праворуч розташоване поле для вибору конкретного значення. Це поле можна заповнити зі списку або надрукувати з клавіатури.У вікні «
Пользовательский автофильтр» (рис. 6) можна ввести максимум дві умови вибору записів, об’єднавши їх між собою операціями «И»/«ИЛИ». Коли включено перемикач «И», Excel покаже записи, що задовольняють обидві умови. При включеному перемикачі «ИЛИ» буде показано записи, що задовольняють одну з умов. Наприклад, щоб вибрати з бази всі підприємства, в назві яких є елемент «ЧП» або слово «фирма», потрібно виконати такі дії:1) відкрити вікно
«Пользовательский автофильтр»;2) у верхньому полі з типом операції вказати «
содержит»;3) як значення для цієї умови ввести «
ЧП»;4) у другому полі з типом операції теж зазначити «
содержит»;5) як значення для цієї умови ввести «
фирма»;6) перемикач логічного зв’язку між операціями поставити в положення «
ИЛИ»;7) натиснути на «
ОК». На екрані залишаться дані щодо контрагентів «ЧП "Матадор"», «Фирма "КХК"» та «Фирма "Эталон"».Можливість використовувати логічні операції «
И"/"ИЛИ» в поєднанні зі стандартним набором умов автофільтра дозволяє побудувати досить складні правила відбору записів з бази даних.Робота з вікном «
Пользовательский автофильтр» (рис. 6) — не єдиний спосіб вибрати записи з довгого списку. Повернемося до вікна на рис. 4. У нижній частині цього вікна знаходиться список усіх підприємств, що наявні в реєстрі. Склад цього списку Excel узяв з колонки, за якою ми виконали фільтрацію даних (нагадаю, що це був стовпець «НаимПредпр»).Ліворуч від назв розташовано поля з галочками. За умовчанням їх уключено. Це означає, що всі записи бази даних можна буде побачити на екрані, тобто фільтр відключено. Виконуємо такі дії:
1) клацанням миші видаляємо всі галочки, залишаємо її тільки біля назви «
ООО "Колорит"». На екрані залишаться записи, що стосуються фірми «ООО "Колорит"». Отже, у цьому випадку Excel застосував до бази даних умову фільтрації за ознакою рівності . Він вибрав записи, в яких уміст комірок у колонці «НаимПредпр» збігається із зазначеним значенням;2) знову викликаємо меню автофільтра. Ставимо галочку біля рядка «
Фирма "Эталон"». Тепер на екрані можна побачити записи про підприємства «ООО "Колорит"» і «Фирма "Эталон"»;Важливо!
Декілька умов у меню автофільтра взаємодіють між собою за логікою «ИЛИ».3) клацаємо по значку вибору в колонці «
НаимПредпр», повертаємося до меню роботи з автофільтром;4) ставимо галочку біля пункту «
(Выделить все)». У базі даних тепер можна побачити всі записи.Важливо!
Вибір пункту «(Выделить все)» не скасовує дію автофільтра. Значки вибору в заголовках таблиці видні, а це означає, що фільтр, як і раніше, є активним.
ФІЛЬТРАЦІЯ ЧИСЕЛ
Робота автофільтра в Excel 2007 залежить від типу даних, до яких він застосовується. Повернемося до бази даних на рис. 1 та відфільтруємо її за колонкою «
ОбщСум». Припустимо, ми хочемо вибрати всі надходження, сума яких перебуває в діапазоні від 1000 до 5000 грн. Виконуємо такі дії.1) ставимо активну комірку на область бази даних;
2) у групі «
Редактирование» головного меню клацаємо по іконці «Сортировка и фильтр» (рис. 2). Відкриється меню, як показано на рис. 3;3) з цього меню вибираємо пункт «
Фильтр»;4) клацаємо по значку вибору в колонці «
Общ Сум». Розкриється меню, зображене на рис. 8. Нас цікавить пункт «Числовые фильтры»;
5) ставимо покажчик миші на цей пункт. З’явиться меню, як показано на рис. 9;
6) з цього меню вибираємо «
Между». З’явиться вікно настройки фільтра, зображене на рис. 10; 
7) як значення «
больше или равно» вказуємо «1000». У полі «меньше или равно» вводимо значення «5000»;8) натискуємо на «
ОК». Фрагмент відфільтрованої бази даних показано на рис. 11 (див. с. 20). У цьому фрагменті всі значення поля «ОбщСум» перебувають у зазначеному діапазоні. 
Більшість пунктів автофільтра для числових даних (рис. 9) не відрізняються від аналогічного меню для роботи з текстом. Серед специфічних можливостей варто звернути увагу на пункти «
между», «Первые 10…», «Выше среднего» і «Ниже среднего». Фільтром за умовою «между» ми щойно скористалися. Пункт «Первые 10…» дозволяє залишити на екрані десять найбільших або десять найменших елементів бази даних.Для умови «
Выше среднего» Excel визначить середньоарифметичне значення за даними ключової колонки та залишить на екрані тільки ті записи, що перевищують цей поріг. Для варіанта «Ниже среднего» Excel залишить записи, значення яких нижчі за середньоарифметичний поріг.Порада
Для правильної роботи автофільтра не змішуйте в одній колонці дані різного типу (текст, числа, дати тощо). В автофільтрі все одно виконуватиметься тільки одна команда для роботи з одним конкретним типом даних. Цю команду Excel вибере за переважаючими елементами, тобто за такими елементами, яких у колонці більшість.Ідеться, наприклад, про таку ситуацію. У вас є колонка з датами. Частину з них зображено у форматі «дати», а решту — у вигляді тексту. Якщо текстових рядків у колонці більше, то Excel запропонує весь стовпець обробляти текстовим автофільтром і всі дати в ньому порівнювати як текст. Зрозуміло, при цьому можуть виникнути певні проблеми.
ВІДБІР ЗНАЧЕНЬ ДАТИ Й ЧАСУ
Фільтр роботи з датою та часом в Excel 2007 істотно відрізняється від більш ранніх версій цієї програми. Тепер при роботі з датами з’явилася можливість вказувати діапазони в місяцях, кварталах, роках і, що не менш важливо, формувати динамічні умови з урахуванням поточної дати. Виконуємо такі дії:
1) відкриваємо реєстр, зображений на рис. 1;
2) ставимо покажчик активної комірки всередину області даних;
3) клацанням по іконці «
Сортировка и фильтр» уключаємо автофільтр;4) клацаємо по значку вибору в колонці «
Дата»;5) у меню, що з’явилося, стаємо на пункт «
Фильтр по дате». Розкриється вікно, як показано на рис. 12. У ньому запропоновано основні можливості щодо фільтрації дат. 
Меню складається з 22 елементів, розбитих на групи. Пункт «
равно…» дозволяє вибрати записи за точною відповідністю до вказаної дати.Пункти меню «
До…», «После…» і «Между…» відкривають вікно призначеного для користувача автофільтра. У ньому відразу буде встановлено операцію щодо вибору записів, датованих раніше зазначеної дати, пізніше зазначеної дати або в межах заданого діапазону дат. Відповідно в цьому вікні потрібно ввести одне чи два значення дати.Далі в меню розташовано три розділи: «
Завтра», «Сегодня», «Вчера». Вони дозволяють визначити умову відбору записів з урахуванням поточної дати. Наприклад, ми вибрали варіант «Вчера». Тоді сьогодні, «10/01/2010», під умову фільтра підпадуть усі записи, датовані від «09/01/2010». Відкривши файл бази даних завтра, «11/01/2010», серед відібраних записів побачимо ті, в яких значення поля «Дата» дорівнюватиме «10/01/2010». Це й є динамічна умова для фільтрації дат.Нижче в меню розташовано схожі умови, але для роботи з тижнями, а саме: «
На следующей неделе», «На этой неделе», «На прошлой неделе». Зрозуміло, є можливість вибрати записи за умовами «В следующем месяце», «В этом месяце», «В прошлом месяце» тощо.Порада
При роботі з датами зверніть увагу на можливість відфільтрувати дані за кварталами. У меню на рис. 12 це пункти «В следующем квартале», «В этом квартале», «В прошлом квартале». Ураховуючи, що вбудованої функції перерахунку дати в номер кварталу в Excel немає, така можливість може бути дуже корисною.
ПОШУК МІНІМУМУ, МАКСИМУМУ ТА ПОРОЖНІХ КОМІРОК
За допомогою автофільтра зручно аналізувати великі масиви чисел, наприклад, знаходити в них мінімальне, максимальне, середнє значення тощо. Розглянемо, як це зробити.
Приклад 1. У базі реєстру (рис. 1) я хочу знайти найсолідніше надходження (тобто запис із максимальним значенням у полі «
ОбщСум»). Виконуємо такі дії:1) включаємо автофільтр;
2) клацаємо по значку вибору в полі «
Общ Сум»;3) у меню автофільтра (рис. 8) вибираємо «
Числовые фильтры», потім — «Первые 10…». З’явиться вікно, як показано на рис. 13; 
4) у списку ліворуч вибираємо варіант «
наибольших». Кількість чисел установлюємо рівним «1». У списку праворуч залишаємо значення «элементов списка».Натискуємо на «
ОК». Excel залишить запис про надходження від «ЧП "Матадор"» за «22.11.2009» на суму 30769,2 грн. Це максимальне надходження.Зазначивши у списку ліворуч «
наименьших», легко виявити найдрібніші надходження. Крім того, у цьому варіанті роботи автофільтра можна гнучко регулювати максимальну кількість відфільтрованих записів. Її можна ввести як конкретне значення або як відсоток від загального числа записів у базі даних (рис. 13).Приклад 2.
Проаналізувати базу даних і знайти в ній усі записи з незаповненим полем «Дата». Виконуємо такі дії:1) включаємо автофільтр;
2) клацаємо по значку вибору в полі «
Дата»;3) у меню автофільтра знімаємо прапорець
«(Выделить все)»;4) у цьому ж меню включаємо прапорець «
(Пустые)»;5) натискуємо на «
ОК». На екрані залишилися тільки ті записи, в яких не заповнено поле «Дата».Важливо!
Прапорець «(Пустые)» доступний тільки за умови, що в діапазоні комірок або стовпці є хоча б одна порожня комірка.Автофільтр зручно застосовувати для пошуку даних, коли завчасно невідоме точне написання шуканого елемента.
Приклад 3. Я хочу знайти в реєстрі підприємство, назва якого схожа на «
Матадор». Точного значення я вказати не можу, але те, що в назві є «Мата», пам’ятаю напевно. Виконуємо такі дії:1) включаємо автофільтр;
2) клацаємо по значку вибору в полі «
НаимПредпр»;3) у меню автофільтра вибираємо «
Текстовые фильтры», потім — «Содержит»;4) як рядок пошуку вводимо «
Мата»;5) натискуємо на «
ОК». На екрані залишилися тільки записи про фірму «ЧП "Матадор"».Як умови порівняння при відборі текстових рядків в Excel 2007 можна використовувати підстановлювальні знаки
. Перелік таких знаків разом з їх описом наведено в табл. 1.
Таблиця 1
Підстановлювальні знаки для відбору текстових полів
| Підстановлювальний знак | Дія |
| 1 | 2 |
| «?» (знак питання) | Будь-який одиночний символ. Приклад: умові «бар?н» відповідають результати «барон» і «баран» |
| «*» (зірочка) | Будь-яка кількість знаків. Приклад: умові «*Excel» відповідають результати «Бухгалтер та Excel» і «Ms Excel» |
| «~» (тильда), за якою йде «?», «*» або «~» | Означає знак питання, зірочку або тильду. Приклад: умові «Calc або Excel~?» відповідає результат «Calc або Excel?» |
Приклад 4
. Щоб вирішити те саме завдання за допомогою підстановлювальних знаків, виконуємо такі дії:1) включаємо автофільтр;
2) розкриваємо список фільтрації для поля «
НаимПредпр»;3) у меню автофільтра вибираємо
«Текстовые фильтры», потім — «равно…»;4) як рядок пошуку вводимо «
*Мата*»;5) натискуємо на «
ОК». На екрані залишилися тільки записи про фірму «ЧП "Матадор"».Пошук даних за допомогою автофільтра дуже зручний при роботі з великими таблицями. Він дозволяє побачити знайдені значення не по одному, а все відразу. Недолік цього методу полягає в тому, що він працює лише зі структурованими масивами, тобто з базами даних. До таблиці довільної форми цей метод пошуку незастосовний.
ФІЛЬТР ЗА КІЛЬКОМА КОЛОНКАМИ
На практиці часто доводиться накладати фільтр на декілька колонок бази даних. Подивимося, як це працює в Excel 2007.
Приклад 5. Виберемо з бази реєстру (рис. 1) записи про надходження за період з «
01/11/2009» по «10/11/2009», що перевищують 5000 грн. Виконуємо такі дії:1) включаємо автофільтр;
2) розкриваємо список фільтрації для поля «
Дата»;3) у меню автофільтра вибираємо
«Фильтры по дате», потім «между…». З’явиться вікно «Пользовательский фильтр», у ньому є два поля для дат;4) напроти умови «
после или равно» вводимо значення «01/11/2009»;5) напроти умови «
до или равно» вводимо значення по «10/01/2009»;6) натискуємо на «
ОК». Тепер на екрані можна побачити лише записи за першу декаду листопада 2009 року;7) клацаємо по значку вибору в полі «
Общ Сум»;8) у меню автофільтра (рис. 8) вибираємо «
Числовые фильтры», потім — «больше…». З’явиться вікно, як показано на рис. 13;9) у списку ліворуч уже стоїть потрібна нам операція («
більше…»). У полі праворуч уводимо значення «5000»;10) натискуємо на «
ОК». Видима частина бази даних скоротилася до п’яти рядків. Усі вони відносяться до першої декади листопада, і сума в колонці «ОбщСум» перевищує 5000 грн.Важливо!
Декілька умов фільтра для різних колонок бази даних взаємодіють за умовою «И».І останнє. Не всі умови фільтрації взаємодіють однаково. Спробуйте, наприклад, такий варіант:
1) виберіть записи з бази реєстру за період з «
01/11/2009» по «10/11/2009»;2) у колонці «
ОбщСум» установіть фільтр «Первые 10…» і виберіть за його допомогою п’ять максимальних надходжень.Цілком можливо, що відфільтрована база виявиться порожньою. На даних нашого прикладу я отримав саме такий результат. Причина проста: Excel вибирає максимальні надходження з усіх значень бази даних! Роботу фільтра за датами він при цьому не враховує.
Виявилося, що п’ять максимальних надходжень перебували поза заданим часовим інтервалом, що й призвело до такого результату.
ФІЛЬТР ЗА ФОРМАТАми
У програмі Excel 2007 з’явилася можливість указувати як умови фільтрації параметри форматування комірок. Якщо ви звикли використовувати колір для виділення змістовних фрагментів у базі даних, така можливість стане в добрій пригоді.
Приклад 6. У базі даних реєстру я позначив жовтим фоном записи про контрагентів, щодо яких є дебіторська заборгованість. Тепер я хочу вибрати ці записи за допомогою автофільтра і подивитися на них. Виконуємо такі дії.
1) включаємо автофільтр;
2) розкриваємо список фільтрації для будь-якого поля. Наприклад, для колонки «
Общ Сум»;3) у меню автофільтра стаємо на рядок «
Фильтр по цвету». З’явиться меню, як показано на рис. 14. У верхній частині цього меню пропонується зазначити спосіб заливки комірок, які ми хочемо вибрати. У меню лише два варіанти: «Нет заливки» та жовтий колір. Якби я використовував багато кольорів для оформлення фону комірок, усі вони були б видні в цьому меню. Друга група параметрів меню на рис. 14 стосується кольору шрифту. Ця можливість доступна тому, що в полі «ОбщСум» є від’ємні значення. А формат комірок у цій колонці встановлено з параметром «отрицательные красным»; 
4) клацаємо лівою кнопкою по жовтому прямокутнику в групі «Фильтр по цвету ячейки». На екрані залишилися тільки записи, позначені жовтим фоном.
Важливо!
В Excel 2007 можна відфільтрувати записи за кольором комірок, за кольором шрифту або за набором значків, створених за допомогою умовного форматування.
ФІЛЬТР ЗА ЗРАЗКОМ
В Excel 2007 таблицю можна швидко відфільтрувати «за зразком», використовуючи для цього характеристики активної комірки. Щоб скористатися такою можливістю, потрібно задіяти контекстне меню правої кнопки миші.
Приклад 7. Із бази даних реєстру (рис. 1) я хочу вибрати записи за «
20/11/2009». Виконуємо такі дії:1) у колонці «
Дата» знаходимо значення «20/11/2009»;2) клацаємо по відповідній комірці правою
кнопкою миші. З’явиться контекстне меню, як показано на рис. 15; 
3) у цьому меню ставимо покажчик миші на пункт «
Фильтр». Розкриється додаткове меню (рис. 15);4) у ньому вибираємо варіант «
Фильтр по значению выделенной ячейки». На екрані залишаться записи за «20/11/2009».Через контекстне меню можна відфільтрувати також записи за кольором фону виділеної комірки, кольором шрифту і за значком умовного форматування.
АВТОФІЛЬТР І СТАТУСНИЙ РЯДОК
Автофільтр зручно використовувати разом зі статусним рядком. На мій погляд, це незамінний помічник бухгалтера при аналізі великих таблиць. Він надзвичайно корисний, коли потрібно швидко отримати контрольне значення за набором даних, при цьому жодних розрахунків у таблиці здійснювати не потрібно.
Ви, звичайно, пам’ятаєте, що в рядку стану відображається сума чисел з виділеного блока комірок. Крім того, скориставшись правою кнопкою миші, тут можна показати й інші значення — середнє арифметичне, кількість чисел, максимум, мінімум тощо. Інформація в рядку стану виводиться з урахуванням дії фільтра. Якщо ви захотіли підсумувати фрагмент бази даних, раджу виконати такі дії:
1) включити автофільтр;
2) відібрати частину записів з бази даних;
3) клацанням миші виділити колонку, значення в якій потрібно підсумувати;
4) у рядку стану прочитати результат.
АВТОФІЛЬТР ТА СОРТУВАННЯ
Може, у це важко повірити, але сортування даних працює з урахуванням дії автофільтра. Щоб переконатися в цьому, виконайте такі дії:
1) відкрийте базу даних, наприклад, як показано на рис. 1;
2) за допомогою автофільтра виберіть дані за певний період часу. Я відібрав частину записів реєстру з «
01/11/2009» по «15/11/2009»;3) не знімаючи автофільтра, відсортуйте базу за полем «
ОбщСум»;4) виділіть усі відфільтровані рядки, присвойте їм інший колір фону (наприклад, світло-жовтий);
5) відключіть автофільтр та проаналізуйте отриманий результат. Базу реєстру буде відсортовано фрагментарно
. Усі рядки, позначені світло-жовтим кольором, у базі буде розташовано за збільшенням поля «ОбщСум». Решта записів у базі не змінять свій порядок.Важливо!
При включеному автофільтрі сортування діє тільки на видимі рядки бази даних. Решта бази залишається незмінною.
ЯК ПРАЦЮВАТИ З АВТОФІЛЬТРОМ БЕЗ ПОМИЛОК
І на завершення хочу дати кілька порад щодо роботи з автофільтром. Вважаю, це допоможе уникнути різного виду помилок, які інколи важко виявити і вчасно їм запобігти.
1. У межах однієї колонки бази заносіть однотипну інформацію. Не змішуйте в одному стовпці текст, числа, дати тощо. Автофільтр може «заплутатися», неправильно відібрати записи, і ви отримаєте помилковий результат. Оскільки автофільтр зазвичай застосовують до великих таблиць, виявити таку помилку буває дуже важко.
2. Стежте, щоб у базі даних не було порожніх рядків. Це загальна вимога при роботі з інструментами обробки даних. Подібно до сортування, перед накладенням автофільтра Excel аналізує структуру робочого листа. Він намагається автоматично знайти базу даних, з’ясувати її координати і потім накласти на неї фільтр. Якщо в базі є порожні рядки, Excel обов’язково помилиться і застосує автофільтр не до всієї бази, а тільки до її фрагмента.
3. Якщо ви не впевнені, що в базі немає порожніх рядків, зазначайте область для автофільтра вручну.
4. Пам’ятайте, що автофільтр включає відображення прихованих рядків на робочому листі. Це потрібно враховувати, якщо ви користуєтеся інформацією з рядка стану MS Excel. У цьому рядку Excel показує результат обробки лише видимих комірок робочого листа. Якщо ви з будь-яких причин приховали частину рядків таблиці, то при підсумовуванні даних, при визначенні середнього тощо ці рядки не враховуватимуться. Але як тільки ви застосували автофільтр, приховані комірки перейдуть до розряду явних і сума зміниться.
От і все, що я хотів сказати щодо роботи з автофільтром. Сподіваюся, що матеріал цієї статті стане вам у пригоді й допоможе продуктивно користуватися цим чудовим бухгалтерським інструментом у вашій роботі. А ми наступного разу подивимося, що нам запропонує розширений фільтр Excel 2007 та ознайомимося з режимом підбиття підсумків.
Чекаю ваших листів, запитань і пропозицій на
bk@id.factor.ua , nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum . Успішної роботи!
