Теми статей
Обрати теми

Excel 2007: фільтрація даних у зведених таблицях

Редакція БК
Стаття

Excel 2007: фільтрація даних у зведених таблицях

 

img 1

Якось мені в руки потрапила книга по роботі з програмою PhotoShop. Розмір цього видання становив близько 1000 сторінок, а сама книга була присвячена одному-єдиному (щоправда, і найпотужнішому) пункту меню! Те ж саме можна сказати про зведені таблиці Excel 2007. Говорити про цей чудовий інструмент можна нескінченно. І сьогодні, продовжуючи тему роботи зі зведеними звітами, розглянемо особливості фільтрації даних у зведених таблицях. А заразом подивимося, як взаємодіють стандартний та розширений фільтри зі зведеними звітами, познайомимося з нюансами використання цих інструментів щодо зведених таблиць.

Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства

 

Зведені таблиці часто використовують для отримання різного виду заготовок — своєрідного матеріалу для подальшої обробки. Отримавши таку заготовку, її беруть для подальшої роботи — організують обчислення, застосовують до неї інші інструменти обробки даних тощо. Саме в останньому випадку і виникає проблема. Не всі засоби роботи з даними «дружать» зі зведеними таблицями. Зокрема, на зведений звіт не можна накласти автофільтр, а іноді це просто необхідно. Та все ж дещо в цій ситуації можна зробити, а точніше, скористатися вбудованими засобами фільтрації зведених таблиць.

 

ФІЛЬТРИ У ЗВЕДЕНІЙ ТАБЛИЦІ

Повернемося до зведеного звіту, який наведено на рис. 1 (ми його побудували в попередній статті). У цій таблиці в області рядків розташовані назви контрагентів. В області даних знаходиться два поля: сума за кількістю реалізованих товарів (поле «

Кол») та сума за обсягами продажів у гривнях (поле «Сумма»). Перша наша дія буде такою:

img 2

1) ставимо покажчик активної комірки всередину зведеного звіту;

2)  клацаємо по пункту «Главная» основного меню Excel 2007;

3) у групі «Редактирование» клацаємо по іконці «Сортировка и фильтр». Розкриється меню із шести пунктів. Ми бачимо, що в цьому меню пункт «Фильтр» недоступний.

Тому для відбору записів у зведеному звіті нам знадобляться інші засоби. І тепер наше завдання буде таким: увести у звіт додаткове поле та скористатися цим полем для фільтрації записів зведеної таблиці. Як поле для фільтру я вибрав дату. Робимо так:

1) ставимо покажчик активної комірки всередину зведеної таблиці. На екрані має з’явитися вікно «Список полей сводной таблицы»;

2) якщо це вікно не з’явилося, клацаємо правою кнопкою миші та вибираємо із контекстного меню пункт «Показать список полей»;

3) у вікні «Список полей сводной таблицы» в області «Выберите поля для добавления в отчет» знаходимо елемент «Дата» та перетягуємо його у віконце «Фильтр отчета» (рис. 2). Зведена таблиця набуде форми, як зображено на рис. 3.

img 3

 

img 4

Важливо! Є інший спосіб уключити роботу фільтра зведеної таблиці. Для цього потрібно перетягнути поле фільтрації на робочий лист у так звану область сторінок зведеного звіту. Ця область розташована на один рядок вище основного розділу таблиці та добре видна на рис. 1 (заголовок «Перетащите сюда поля страниц»).

Отже, що ж змінилося після наших дій? У верхній частині звіту з’явилося поле «Дата». Праворуч від цього поля знаходиться додатковий елемент — значок випадного списку. Зараз дані зведеної таблиці показують значення, пораховані за всіма записами вихідної бази даних. Про це свідчить напис «Все» у значку списку, що розкривається. Подивимося, як видозміниться звіт, якщо змінити це значення. Робимо так:

1) клацаємо по значку списку, що розкривається, праворуч від елемента «Дата». З’явиться вікно, зображене на рис. 4;

img 5

2) клацанням миші виділяємо будь-яку дату. Наприклад, «03/01/2009»;

3) натискуємо «ОК». Тепер зведений звіт виглядає, як показано на рис. 5.

img 6

Форма зведеної таблиці не змінилася. А от вміст став іншим. Тепер всі значення у звіті показують обсяги реалізації за контрагентами (у кількостях та в штуках), але тільки за один день, а точніше,  за «03/01/2009». Це нескладно перевірити:

1) відкриваємо лист з вихідною базою даних;

2) ставимо активну комірку всередину таблиці з даними;

3) на стрічці «Главная» у розділі «Редактирование» знаходимо іконку «Сортировка и фильтр». Клацаємо по ній лівою кнопкою миші;

4) у меню, що розкрилося, вибираємо пункт «Фильтр». У заголовках таблиці з’являться значки вибору;

5) клацаємо на значку вибору в колонці «Дата». Відкриється випадне меню з переліком дат. Елементи цього меню може бути згруповано за місяцями («Январь», «Февраль» тощо);

6) клацанням миші прибираємо галочки біля назви кожної групи. Ми маємо намір відфільтрувати дані тільки за один день;

7) знаходимо значок «+» (плюс) біля назви групи, нас в цьому випадку цікавить «Январь»;

8) клацанням миші розкриваємо його. З’явиться перелік усіх дат за січень, які є в базі даних;

9) ставимо галочку біля дати «03/01/2009»;

10)  у вікні роботи з фільтром натискуємо «ОК»;

11) клацаємо на значку вибору в колонці «Покупатель»;

12) у списку покупців, що з’явився, залишаємо галочку тільки біля елемента «ООО «Эталон»;

13) у вікні роботи з фільтром натискуємо «ОК»;

14) клацанням миші в області заголовків листа виділяємо колонку «D» (у ній записані відомості про кількість проданого товару);

15) у статусному рядку читаємо значення суми за цим полем. Вона дорівнює «25», саме таку кількість ТМЦ було продано контрагенту «ООО «Эталон» у суботу «03/01/2009». Повертаємося до зведеного звіту (рис. 5) і бачимо, що це саме число стоїть у комірці «С5».

За умовчанням у вікні управління фільтром можна вказати тільки один елемент, але це легко змінити. Для цього у вікні роботи з полем фільтра (рис. 4) потрібно включити прапорець «Выделить несколько элементов». Ліворуч біля кожного значення у вікні управління фільтром з’являться порожні квадратики. Клацанням миші по будь-якому квадратику ставимо в ньому галочку. Усі помічені елементи братимуть участь у роботі фільтра.

В Excel 2007 до області сторінок можна помістити декілька полів з основної бази даних. У цьому випадку з’явиться можливість формувати більш складні умови фільтрації. Наприклад, ми могли б поставити в область сторінок два поля: «Дата» і «НомДок». Тоді при роботі зі зведеним звітом ми змогли б відбирати та рахувати значення не лише для вибраних дат, а й для будь-якого набору документів.

Важливо!

Декілька умов у фільтрі зведеної таблиці взаємодіють за логікою «И».

Припустимо, ми поставили в область сторінок поля «Дата» і «НомДок». У списку поля «Дата» позначили «03/01/2009», а у списку поля «НомДок» вибрали елементи «НК-001», «НК-002», «НК-015». У підсумковій зведеній таблиці залишиться тільки один контрагент — «ООО «Эталон» (із кількістю продажів «25 шт.» та обсягом реалізації «28,25 грн.»). У розрахунку цих значень братимуть участь дані тільки за документами «НК-001», «НК-002», оскільки накладну «НК-015» датовано «04/01/2009», у розрахунках вона участі не бере.

Видалити поле з фільтра можна різними способами. Ось два варіанти можливих дій.

Спосіб 1

1) поставити покажчик миші на поле, що видаляється, в області «Фильтр отчета» вікна «Список полей сводной таблицы» (рис. 2);

2) утримуючи ліву кнопку миші, перетягнути поле на робочий лист або в інше місце вікна «Список полей сводной таблицы»;

3) як тільки покажчик набуде хрестоподібної форми (у вигляді літери «Х»), відпустити кнопку миші.

Спосіб 2

1)  поставити покажчик миші на поле, що видаляється, в області зведеної таблиці» (комірка «A2» на рис. 3);

2) утримуючи ліву кнопку миші, перетягнути поле на робочий лист або всередину вікна «Список полей сводной таблицы»;

3) як тільки покажчик набуде форми літери «Х», відпустити кнопку миші.

Тепер робимо так:

1)  залишаємо в області сторінок зведеного звіту тільки поле «Дата»;

2) переходимо у вікно «Список полей сводной таблицы». Зараз поле «Покупатель» розташоване в області «Названия строк»;

3) знаходимо поле «Покупатель» у віконці «Выберите поля для добавления в отчет:» та перетягуємо його в область сторінок зведеного звіту. Елемент «Покупатель» із області «Названия строк» Excel прибере автоматично.

Важливо!

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

Така логіка розробників Excel має резон. Річ у тім, що поля в області рядків та колонок мають свої фільтри (значок списку, що розкривається, у полі «Покупатель» на рис. 5). Тому створення двох однакових полів для фільтрації даних може спричинити створення суперечливих умов при відборі записів.

 

ТА ВСЕ Ж… АБО АЛЬТЕРНАТИВНІ СПОСОБИ ФІЛЬТРАЦІЇ ЗВЕДЕНИХ ТАБЛИЦЬ

З можливостями відбору записів безпосередньо у зведеному звіті все зрозуміло. Інструмент у принципі працює, але звичайний автофільтр усе-таки набагато цікавіший. Наприклад, у ньому можна побудувати досить гнучкі умови фільтрації, вибрати записи за ознакою «

больше», «меньше», «содержит», «не содержит» тощо. У разі роботи зі вбудованими засобами зведених таблиць такі можливості використовувати не можна. Однак є один секрет, який допоможе нам вирішити цю проблему. Але перш ніж продовжити роботу, перебудуємо зведений звіт, привівши його до форми, зображеної на рис. 6. У цій таблиці в області рядків розташовано поле «НаимТов», в області колонок — «Покупатель», а в області даних я залишив єдине поле «Сумма» та вказав для нього операцію підсумовування. У такому вигляді звіт більше схожий на базу даних, і це саме те, що нам потрібно. Тепер наше завдання буде таким: поряд зі зведеною таблицею побудувати робочу колонку. Потім перенести до цієї колонки потрібні дані зі зведеного звіту та застосувати до робочої колонки автофільтр.

Припустимо, що ми вирішили відфільтрувати таблицю за значеннями «

НаимТов». Це означає, що в робочий стовпець нам потрібно перенести значення з колонки «А» зведеного звіту (рис. 6). Причому при оновленні зведеної таблиці значення в робочій колонці теж повинні оновлюватися. Імовірно, досягти цього можна за допомогою посилань на відповідні комірки. Зробимо це:

img 7

1) ставимо покажчик активної комірки на «F4», щоб увести формулу (імовірно це буде посилання на комірку «=A4»);

2)  натискуємо знак «=»;

3)  клацаємо по комірці «A4». Excel 2007 замість очікуваного посилання написав формулу «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма";$A$2;"Покупатель";"ЧП «Лапина»"; "НаимТов"; "Блокнот")». Схоже, це не зовсім те, чого ми очікували. Доведеться застосувати одну хитрість. Робимо так:

1) ставимо покажчик активної комірки на «F3», — на рівні заголовка зведеної таблиці;

2)  натискуємо на знак «=»;

3) клацаємо по комірці «A3». У «F3» з’явилася формула «=A3» і тут же бачимо результат її роботи — текст «НаимТов»;

4) копіюємо формулу за колонкою на всю висоту таблиці (рис. 7);

img 8

5) повертаємося до комірці «A3», уводимо до неї заголовок. Наприклад, «Раб»;

6) не покидаючи колонки «Раб» (активна комірка повинна залишатися в цьому стовпці!), клацаємо по пункту «Главная» основного меню. Потім — по іконці «Сортировка и фильтр» (група «Редактирование»). З’явиться випадне меню, пункт «Фильтр» тепер у ньому доступний;

7) клацаємо по цьому пункту лівою кнопкою миші. У заголовках зведеної таблиці та в колонці «Раб» з’явилися значки вибору;

8)  клацаємо по значку біля поля «Раб». Відкриється стандартне меню автофільтра, наведене на рис. 8. Далі все за звичною схемою;

img 9

9) клацаємо по пункту «Текстовые фильтры» (у полі «Раб» записані назви товарів, це й є причиною появи інструменту фільтрації текстових значень). Розкриється додаткове меню;

10) у ньому вибираємо пункт «Содержит». З’явиться вікно призначеного для користувача фільтра, наведене на рис. 9 (типовий інструмент для автофільтрації!);

img 10

11) у цьому вікні як значення вводимо, наприклад, символи «Ск». На екрані залишиться частина зведеного звіту для товарів « Скотч» та « Скрепка канц.» (рис. 10). Автофільтр працює!

img 11

Цікавий момент. Зверніть увагу, що значки вибору з’явилися в усіх колонках зведеного звіту. Клацаємо по будь-якому з цих значків лівою кнопкою миші. Відкриється меню автофільтра, наведене на рис. 8. Тобто після створення допоміжної колонки ми можемо користуватися автофільтром стосовно зведеної таблиці так само, як ми робили щодо баз даних.

І ще один нюанс. Зробіть так:

1) відкрийте зведену таблицю з робочою колонкою, як показано на рис. 7;

2) клацніть по іконці «Сортировка и фильтр

» (меню «Главная»). Розкриється випадне меню;

3) клацніть по іконці «Фильтр», щоб відмінити дію автофільтра;

4) зітріть усі формули в колонці «Раб

»;

5) не покидаючи допоміжної колонки, уключіть автофільтр. Зведена таблиця набуде вигляду, наведеного на рис. 7, — усі можливості автофільтра знову до ваших послуг! Звідси

висновок.

Важливо!

Автофільтр не можна застосувати безпосередньо до зведеної таблиці. Але його можна застосувати до допоміжної колонки. Бажано (але не обов’язково), щоб ця колонка була розташована поряд зі зведеною таблицею. Значення до робочої колонки можна не вводити. Застосувавши автофільтр до робочої колонки, його дія пошириться на всю зведену таблицю.

 

ЗВЕДЕНА ТАБЛИЦЯ ТА РОЗШИРЕНИЙ ФІЛЬТР

Наш останній експеримент стосуватиметься взаємодії зведених таблиць із розширеним фільтром. Повернемося до звіту, зображеного на рис. 6. Робимо так:

1) додаємо до верхньої частини робочого листа два (або більше) додаткові рядки;

2)  копіюємо заголовки зведеної таблиці (у нашому прикладі після додавання двох рядків — це комірки «

A5:E5») до першого рядка робочого листа, починаючи з «A1». Тут є один нюанс;

Секрет Виділяючи заголовки зведеного звіту перед копіюванням у буфер обміну, починайте з комірки «E5» (тобто виділення блока робіть справа-наліво). Якщо ви спробуєте виділити блок «A5:E5», починаючи з «A5», нічого не вийде. Як тільки ви поставите покажчик на комірку «A5» та натиснете ліву кнопку миші, Excel перейде в режим коригування структури зведеного звіту. Замість того, щоб позначати блок, він переміщатиме поле «НаимТов» та коригуватиме тим самим макет зведеного звіту.

3) виділивши заголовки, копіюємо їх у буфер обміну;

4)  переходимо на комірку «

A1», вставляємо вміст із буфера. У заголовках області критеріїв з’явилися назви підприємств. Змінювати ці значення не можна! Вони повинні точно збігатися з назвами заголовків основної бази даних;

5) у комірку «A2» друкуємо текст. Наприклад «Блокнот»;

6) викликаємо меню «Данные»;

7) у групі «Сортировка и фильтр» на стрічці цього меню клацаємо по іконці «Дополнительно». З’явиться вікно, зображене на рис. 11;

img 12

8) заповнюємо параметри «Исходный диапазон:» та «Диапазон условий:», як показано на рис. 11. Тобто як базу вибираємо вміст зведеної таблиці. Умови в нашому прикладі знаходяться над зведеним звітом у блоці «$A$1:$E$2»;

9) натискуємо «ОК». На екрані залишився єдиний рядок — сумарний обсяг реалізації ТМЦ «Блокнот» (рис. 12).

img 13

Важливо!

Розширений фільтр нормально працює зі зведеними таблицями.

Підсумуємо. Зведені таблиці мають вбудований інструмент для фільтрації записів. Для цього у зведеному звіті передбачено спеціальну область сторінок. У цій області можна розташувати декілька

полів і тим самим розширити можливості зі створення умов для відбору записів. Як додатковий інструмент можна використовувати вікно фільтрації, яке працює з полями в області рядка та колонок зведеного звіту. Проте можливості вбудованих фільтрів у зведених таблиць обмежені. Але ви можете вирішити проблему, застосувавши автофільтр до додаткової колонки, розмістивши цю колонку безпосередньо біля зведеного звіту. Значення до колонки вводити не обов’язково, але їх наявність зробить процес роботи зі звітом більш наочним. Пам’ятайте, що до зведеної таблиці можна застосувати розширений фільтр.

На цьому ми завершимо тему фільтрації даних у зведеному звіті, та наступного разу подивимося, які способи обчислення пропонують зведені таблиці, у чому їх особливості, як можна вставити формулу до зведеного звіту та що це дає бухгалтеру на практиці.

 

Чекаю ваших запитаннь, листів, пропозицій та зауважень на

bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum . Успішної роботи!
App
Завантажуйте наш мобільний додаток Factor

© Factor.Media, 1995 -
Всі права захищені

Використання матеріалів без узгодження з редакцією заборонено

Ознайомитись з договором-офертою

Приєднуйтесь
Адреса
м. Харків, 61002, вул. Сумська, 106а
Ми приймаємо
ic-privat ic-visa ic-visa

Ми використовуємо cookie-файли, щоб зробити сайт максимально зручним для вас та аналізувати використання наших продуктів та послуг, щоб збільшити якість рекламних та маркетингових активностей. Дізнатися більше про те, як ми використовуємо ці файли можна тут.

Дякуємо, що читаєте нас Увійдіть і читайте далі