Якщо ви не зовсім початківець користувач, то напевно вже помітили, що 99% всього в Excel призначено для роботи з вертикальними таблицями, де по стовпцях проходять параметри або атрибути (поля), а також розташовується інформація про об'єкти або події. в рядках. Зведені таблиці, проміжні підсумки, копіювання формул подвійним клацанням миші – все створено спеціально для цього формату даних.
Однак правил без винятків не буває, і мене досить часто запитують, що робити, якщо в роботі зустрілася таблиця з горизонтальною семантичною орієнтацією або таблиця, в якій рядки і стовпці мають однакову вагу за значенням:
І якщо Excel ще вміє сортувати по горизонталі (за допомогою команди Дані – Сортування – Параметри – Сортувати стовпці), то з фільтрацією ситуація гірша – в Excel просто немає вбудованих засобів для фільтрації стовпців, а не рядків. Так що, якщо перед вами стоїть таке завдання, доведеться придумувати обхідні шляхи різного ступеня складності.
Спосіб 1. Нова функція FILTER
Якщо ви використовуєте нову версію Excel 2021 або підписку на Excel 365, ви можете скористатися перевагами нової функції ФІЛЬТР (ФІЛЬТР), який може фільтрувати вихідні дані не лише за рядками, а й за стовпцями. Для роботи цієї функції потрібен допоміжний горизонтальний одновимірний масив-рядок, де кожне значення (ІСТИНА чи ЛОЖЬ) визначає, будемо показувати чи, навпаки, приховувати наступний стовпець у таблиці.
Давайте додамо такий рядок над нашою таблицею та запишемо в ньому статус кожного стовпця:
- Скажімо, ми завжди хочемо відображати перший і останній стовпці (заголовки та підсумки), тому для них у першій і останній клітинках масиву ми встановлюємо значення = TRUE.
- Для решти стовпців вмістом відповідних комірок буде формула, яка перевіряє потрібну умову за допомогою функцій И (І) or OR (АБО). Наприклад, що загальна сума знаходиться в межах від 300 до 500.
Після цього залишиться тільки скористатися функцією ФІЛЬТР щоб вибрати стовпці, над якими наш допоміжний масив має значення TRUE:
Так само ви можете фільтрувати стовпці за заданим списком. У цьому випадку допоможе функція COUNTIF (COUNTIF), який перевіряє кількість входжень імені наступного стовпця із заголовка таблиці в дозволеному списку:
Спосіб 2. Зведена таблиця замість звичайної
Наразі Excel має вбудовану горизонтальну фільтрацію за стовпцями лише у зведених таблицях, тому, якщо нам вдасться перетворити нашу оригінальну таблицю на зведену, ми зможемо використовувати цю вбудовану функцію. Для цього наша вихідна таблиця повинна задовольняти наступним умовам:
- мати «правильний» однорядковий рядок заголовка без порожніх і об’єднаних комірок – інакше побудувати зведену таблицю не вийде;
- не містити дублікатів в мітках рядків і стовпців – вони «згорнуться» в підсумку в список тільки унікальних значень;
- містити тільки числа в діапазоні значень (на перетині рядків і стовпців), тому що зведена таблиця обов'язково застосує до них якусь функцію агрегування (сума, середнє і т.д.), а це не буде працювати з текстом
Якщо всі ці умови виконуються, то для того, щоб побудувати зведену таблицю, схожу на нашу початкову таблицю, її (оригінальну) потрібно буде розгорнути з крос-таблиці в плоску (нормалізовану). І найпростіший спосіб зробити це за допомогою надбудови Power Query, потужного інструменту перетворення даних, вбудованого в Excel з 2016 року.
До них відносяться:
- Давайте перетворимо таблицю на «розумну» динамічну команду Домашня сторінка – Формат у вигляді таблиці (Домашня сторінка — форматувати як таблицю).
- Завантаження в Power Query за допомогою команди Дані – з таблиці / діапазону (Дані – з таблиці / діапазону).
- Фільтруємо рядок із підсумками (підсумки будуть свої).
- Клацніть правою кнопкою миші заголовок першого стовпця та виберіть Розгорнути інші стовпці (Скасувати зведення інших стовпців). Усі невибрані стовпці перетворюються на два – ПІБ працівника та значення його показника.
- Фільтрування стовпця з підсумками, які ввійшли до стовпця атрибут.
- За отриманою плоскою (нормалізованою) таблицею за допомогою команди будуємо зведену таблицю Головна — Закрийте та завантажте — Закрийте та завантажте в… (Додому — Закрити та завантажити — Закрити та завантажити до…).
Тепер ви можете використовувати можливість фільтрації стовпців, доступних у зведених таблицях – звичайні галочки перед іменами та елементами Фільтри підписів (Фільтри міток) or Фільтри за значенням (Фільтри значень):
І звичайно, змінюючи дані, вам потрібно буде оновити наш запит і резюме за допомогою комбінації клавіш Ctrl+інший+F5 або команда Дані – оновити все (Дані — оновити все).
Спосіб 3. Макрос у VBA
Усі попередні методи, як ви можете легко побачити, не зовсім фільтрують – ми не приховуємо стовпці у вихідному списку, а формуємо нову таблицю із заданим набором стовпців із початкової. Якщо потрібно відфільтрувати (приховати) стовпці у вихідних даних, то потрібен принципово інший підхід, а саме макрос.
Припустімо, ми хочемо фільтрувати стовпці на льоту, де ім’я менеджера в заголовку таблиці відповідає масці, указаній у жовтій комірці A4, наприклад, починається з літери «A» (тобто отримуємо «Анна» та «Артур» " в результаті).
Як і в першому методі, ми спочатку реалізуємо допоміжний діапазон-рядок, де в кожній комірці наш критерій буде перевірятися формулою і логічні значення TRUE або FALSE будуть відображатися для видимих і прихованих стовпців відповідно:
Тоді давайте додамо простий макрос. Клацніть правою кнопкою миші вкладку аркуша та виберіть команду Source (Вихідний код). Скопіюйте та вставте такий код VBA у вікно, що відкриється:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then Для кожної комірки в Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = Правда Кінець Якщо наступна комірка Кінець Якщо Кінець Sub Його логіка така:
- Загалом, це обробник подій Робочий лист_Зміна, тобто цей макрос автоматично запускатиметься після будь-якої зміни будь-якої комірки на поточному аркуші.
- Посилання на змінену клітинку завжди буде в змінній Мета.
- Спочатку перевіряємо, що користувач змінив саме клітинку з критерієм (A4) – це робить оператор if.
- Потім починається цикл Для кожного… для повторення сірих клітинок (D2:O2) зі значеннями індикатора TRUE / FALSE для кожного стовпця.
- Якщо значення наступної сірої комірки TRUE (істина), то стовпець не прихований, інакше ми його приховуємо (властивість прихований).
- Функції динамічного масиву з Office 365: FILTER, SORT і UNIC
- Зведена таблиця з багаторядковим заголовком за допомогою Power Query
- Що таке макроси, як їх створювати та використовувати