Фільтр вихідних даних у зведеній таблиці
У будь-якого тренера, що поважає себе, завжди є запас "вау-фішок" - простих, але ефектних прийомів, таких собі killing-features для швидкої чарівності складної аудиторії. У зведених таблицях однієї з таких фішок, безумовно, є подвійне клацання лівою кнопкою миші за будь-яким числом в області значень:
Якщо це зробити, то вас винесе на новий аркуш, куди Excel вивантажить деталізацію по цій клітинці - всю "піднаготну", що пояснює як вийшло дане значення, з чого воно склалося:
Офіційно, ця процедура називається drill-down, неофіційно її зазвичай називають "провалитися".
Ключовий аспект в тому, що отримана в результаті такого провалювання подвійним клацанням таблиця - це копія вихідних даних, а не вони самі. Отримана таблиця абсолютно автономна і не пов'язана ні з вихідними даними, ні зі зведеною. Іноді це нам на руку - ми можемо використовувати її для своєї мети, змінювати її і т.д.
Але часом виникає інше бажання: а чи можна побачити не копію вихідних даних, а самі дані? Тобто відфільтрувати ті рядки у вихідній таблиці, які беруть участь у розрахунку даного осередку? Їх, наприклад, можна було б потім змінити, підкоригувавши цим результат у зведеній таблиці.
Стандартними засобами таке неможливо, але для макросів межі можливого в Excel суттєво ширші :)
- У Excel 2003 і більше для цього потрібно вибрати в менюСервіс - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor)
- У нових версіях Excel 2007-2013 перейти на вкладкуРозробник (Developer) та натиснути кнопкуVisual Basic. Якщо такої вкладки у вас не видно, увімкніть її в налаштуванняхФайл - Параметри - Налаштування стрічки (File - Options - Customize Ribbon)
Тепер, якщо виділити будь-яку комірку з даними в зведеній таблиці і запустити наш перший макрос FilterPivot за допомогою поєднання клавішAlt+F8 або через менюСервіс - Макрос - Макроси (Tools - Macro - Macros), то ми перейдемо на аркуш з вихідними даними для зведеної, де автоматично будуть застосовані фільтри, які відбирають тільки ті рядки, які брали участь у розрахунку поточного осередку:
Тепер їх можна, наприклад, змінити, щоб досягти звіту зведеної таблиці потрібного результату. Тільки не забудьте оновити зведену після внесення змін: правою кнопкою миші -Обновити (Refresh).
Другий макрос ShowAllData потрібний, щоб повернути колишній вигляд вихідної таблиці - він робить усі рядки на поточному аркуші видимими. Для більшої зручності можна повісити ці два макроси на зручні для вас поєднання клавіш, використовуючи кнопкуПараметри (Options) у вікніМакроси, яке відображається поAlt+F8.