Фільтрування та сортування даних зведеної таблиці в Excel 2019

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

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

Фільтрація зведених таблиць у Excel 2019

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

Наприклад, у зразку зведеної таблиці Excel, яка використовує поле Стать зі списку Дані працівника як поле фільтра звіту, ви можете відобразити суму зарплат чоловіків або жінок за відділами та місцеположенням у тілі зведеної таблиці, виконуючи будь-які дії. з наступного:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сортування зведених таблиць у Excel 2019

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

Клацніть параметр Сортувати від А до Я, якщо потрібно змінити порядок таблиці шляхом сортування міток у вибраному полі за алфавітом або, у випадку значень, від найменшого до найбільшого чи, у випадку з датами, від найстарішого до найновіша дата. Клацніть параметр Сортувати від Z до A, якщо потрібно змінити порядок таблиці, відсортувавши мітки в зворотному алфавітному порядку, значення від найвищого до найменшого та дати від новітнього до найстарішого.