Як відфільтрувати дані зведеної таблиці в Excel 2016

Коли ви створюєте нову зведену таблицю, ви помітите, що Excel 2016 автоматично додає кнопки спадного меню до поля фільтра звіту. Ці кнопки фільтра дозволяють фільтрувати всі записи, крім певних, у будь-якому з цих полів.

Фільтрування звіту

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

Ця зведена таблиця використовує поле Стать як поле фільтра звіту.

Наприклад, у зразковій зведеній таблиці (показано тут), яка використовує поле Стать зі списку Дані працівника як поле фільтра звіту, ви можете відобразити суму зарплат тільки чоловіків або жінок за відділами та місцем розташування в тілі зведеної точки. таблиця, яка виконує одну з наступних дій:

• Натисніть кнопку фільтра поля Стать, а потім клацніть M у спадному списку, перш ніж натиснути кнопку OK, щоб побачити лише загальні зарплати чоловіків за відділами.

• Натисніть кнопку фільтра в полі Стать, а потім натисніть F у спадному списку, перш ніж натиснути кнопку OK, щоб побачити лише загальні зарплати жінок за відділами.

Якщо пізніше ви захочете знову відобразити підсумок зарплат для всіх співробітників, ви потім знову виберіть параметр (Усі) у випадаючому списку фільтрів поля Стать, перш ніж натиснути кнопку OK.

Коли ви фільтруєте поле «Фільтр звіту про стать» таким чином, Excel відображатиме M або F у полі «Фільтр звіту про стать» замість значення за замовчуванням (Усі). Програма також замінює стандартну кнопку спадного меню на конусоподібну піктограму фільтра, яка вказує, що поле відфільтровано, і показує лише деякі значення у джерелі даних.

Фільтрація полів стовпців і рядків

Кнопки фільтра на полях стовпців і рядків, доданих до їхніх міток, дозволяють відфільтрувати записи для певних груп, а в деяких випадках — окремих записів у джерелі даних. Щоб відфільтрувати підсумкові дані в стовпцях або рядках зведеної таблиці, натисніть кнопку фільтра стовпця або поля рядка та почніть, натиснувши прапорець для параметра (Вибрати все) у верхній частині розкривного списку, щоб очистити це поле. його галочки. Потім установіть прапорці для всіх груп або окремих записів, сумарні значення яких ви все ще хочете відображати в зведеній таблиці, щоб повернути прапорці в кожному з їхніх прапорців. Потім натисніть OK.

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

На цьому малюнку показано зведену таблицю після фільтрації поля «Фільтр гендерного звіту» для жінок і його поля «Стовпець відділу» до «Облік, адміністрування та кадри».

Зведена таблиця після фільтрації полів «Фільтр звіту про стать» та поля «Стовпець відділу».

Окрім фільтрації окремих записів у зведеній таблиці, ви також можете використовувати параметри в меню продовження «Фільтри міток» і «Фільтри значень», щоб фільтрувати групи записів, які не відповідають певним критеріям, наприклад, розташування компанії, які не починаються з конкретний лист або зарплати від 45 000 до 65 000 доларів США.

Фільтрування за допомогою слайсерів

Слайсери в Excel 2016 дозволяють швидко фільтрувати вміст вашої зведеної таблиці в кількох полях. (Вони навіть дозволяють підключатися до полів інших зведених таблиць, створених вами в книзі.)

Щоб додати слайсери до зведеної таблиці, виконайте всього два кроки:

Клацніть одну з клітинок у вашій зведеній таблиці, щоб виділити її, а потім натисніть кнопку «Вставити слайсер», розташовану в групі «Фільтр» на вкладці «Аналіз» на контекстній вкладці «Інструменти зведеної таблиці».

Excel відкриває діалогове вікно Вставити слайси зі списком усіх полів активної зведеної таблиці.

Установіть прапорці для всіх полів, які потрібно використовувати для фільтрації зведеної таблиці та для яких потрібно створити слайсери, а потім натисніть кнопку OK.

Потім Excel додає слайсери для кожного вибраного поля зведеної таблиці та автоматично закриває панель завдань Поля зведеної таблиці, якщо вона відкрита в цей момент.

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

На цьому малюнку показано зведену таблицю після використання слайсерів, створених для полів «Стать», «Відділ» і «Розташування», щоб відфільтрувати дані, щоб відображалися лише зарплати чоловіків у відділах кадрів та адміністрації в офісах у Бостоні, Чикаго та Сан-Франциско. .

Зразок зведеної таблиці відфільтровано за допомогою слайсерів, створених для полів Стать, Відділ і Місцезнаходження.

Оскільки слайсери — це графічні об’єкти Excel (хоча й досить химерні), ви можете переміщати, змінювати розмір та видаляти їх так само, як і будь-яку іншу графіку Excel. Щоб видалити слайсер зі зведеної таблиці, клацніть його, щоб вибрати його, а потім натисніть клавішу Delete.

Фільтрація з часовими шкалами

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

Щоб створити шкалу часу для зведеної таблиці, виділіть клітинку у зведеній таблиці, а потім натисніть кнопку Вставити шкалу часу в групі «Фільтр» на контекстній вкладці «Аналіз» на вкладці «Інструменти зведеної таблиці» на стрічці. Потім Excel відобразить діалогове вікно «Вставити часові шкали», яке відображає список полів зведеної таблиці, які можна використовувати для створення нової шкали часу. Після встановлення прапорця для поля дати, яке потрібно використовувати в цьому діалоговому вікні, натисніть кнопку OK.

На цьому малюнку показано часову шкалу, створену для зразка списку даних співробітників, вибравши його поле «Дата найму» в діалоговому вікні «Вставити часові шкали». Як ви можете бачити, Excel створив плаваючу часову шкалу «Дата найму» з розмежованими роками та місяцями та смугою, яка вказує вибраний період часу. За замовчуванням у часовій шкалі в якості одиниць використовуються місяці, але ви можете змінити це на роки, квартали чи навіть дні, натиснувши кнопку спадного меню МІСЯЦІ та вибравши потрібну одиницю часу.

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

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