Зведені таблиці пропонують фільтр Top 10. Це круто. Це гнучко. Але я ненавиджу це, і я скажу вам, чому.
Ось зведена таблиця, яка відображає дохід за клієнтом. Загальний дохід - 6,7 мільйона доларів. Зверніть увагу, що найбільший клієнт, Roto-Rooter, становить 9% від загального доходу.
Що робити, якщо мій менеджер приділяє увагу золотої рибки і хоче бачити лише п’ять найкращих клієнтів? Для початку відкрийте спадне меню в A3 і виберіть Фільтри значень, Топ 10.
Надзвичайно гнучке діалогове вікно Топ-10 фільтра дозволяє зверху / знизу. Це може бути 10, 5 або будь-яке інше число. Ви можете попросити п’ять найкращих позицій, 80% або достатню кількість клієнтів, щоб отримати 5 мільйонів доларів.
Але тут проблема: Отриманий звіт показує п’ять клієнтів та загальну кількість цих клієнтів замість загальної суми всіх. Roto-Rooter, який раніше становив 9% від загальної кількості, становить 23% від загальної кількості.
Але спочатку кілька важливих слів про автофільтр
Я усвідомлюю, що це виглядає як поза стіною питання. Якщо ви хочете ввімкнути спадні меню Фільтр на звичайному наборі даних, як це зробити? Ось три справді поширені способи:
- Виділіть одну клітинку у ваших даних і натисніть піктограму Фільтр на вкладці Дані або натисніть Ctrl + Shift + L.
- Виділіть усі свої дані за допомогою Ctrl + * і клацніть піктограму Фільтр на вкладці Дані.
- Натисніть Ctrl + T, щоб відформатувати дані у вигляді таблиці.
Це три справді хороші способи. Поки ви знаєте будь-якого з них, зовсім не потрібно знати іншого шляху. Але ось неймовірно незрозумілий, але чарівний спосіб увімкнути фільтр:
- Перейдіть до свого рядка заголовків, а потім перейдіть до самої правої комірки заголовка. Перемістіть одну клітинку вправо. З якоїсь невідомої причини, коли ви знаходитесь у цій комірці та натискаєте піктограму Фільтр, Excel фільтрує дані, встановлені зліва від вас. Я не уявляю, чому це працює. Про це насправді не варто говорити, тому що вже є три справді хороших способи ввімкнути спадні меню Фільтр. Я називаю цю клітину чарівною клітиною.
А тепер повернімося до зведених таблиць
Існує правило, згідно з яким ви не можете використовувати автофільтр, коли ви знаходитесь у зведеній таблиці. Дивіться нижче? Значок Фільтр сірий, оскільки я вибрав клітинку у зведеній таблиці.
Я не знаю, чому Microsoft це виводить у сірий. Це має бути щось внутрішнє, що говорить, що AutoFilter і зведена таблиця не можуть співіснувати. Отже, у команді Excel є хтось, хто відповідає за сіре зображення піктограми Фільтр. Ця людина ніколи не чула про чарівну клітинку. Виділіть комірку у зведеній таблиці, і фільтр буде сірим. Клацніть за межами зведеної таблиці, і фільтр знову ввімкнено.
Але почекай. А як щодо чарівної клітини, про яку я тобі що розповідав? Якщо натиснути клітинку праворуч від останнього заголовка, Excel забуде виділити піктограму фільтра сірим кольором!
Звичайно, Excel додає спадні меню AutoFilter у верхній рядок зведеної таблиці. І AutoFilter працює інакше, ніж фільтр зведеної таблиці. Перейдіть до спадного меню Дохід і виберіть Фільтри чисел, Топ 10 ….
У діалоговому вікні Top 10 AutoFilter виберіть Top 6 Items. Це не друкарська помилка … якщо ви хочете п’ять клієнтів, виберіть 6. Якщо ви хочете 10 клієнтів, виберіть 11.
Для AutoFilter загальний рядок є найбільшим елементом у даних. П’ять найкращих клієнтів займають позиції з 2 по 6 у даних.
Обережно
Очевидно, що цим трюком ви рвете дірку в тканині Excel. Якщо пізніше ви зміните базові дані та оновите свою зведену таблицю, Excel не оновить фільтр, оскільки, наскільки відомо Microsoft, немає можливості застосувати фільтр до зведеній таблиці!
Примітка
Наша мета - зберегти це в секреті від Microsoft, оскільки це досить класна функція. Він був «зламаний» протягом досить тривалого часу, тому є багато людей, які, можливо, вже покладаються на нього.
Повністю юридичне рішення в Excel 2013+
Якщо ви хочете зведену таблицю, що відображає п’ять найкращих клієнтів, але загальну кількість усіх клієнтів, вам доведеться перемістити дані за межі Excel. Якщо у вас є Excel 2013 або новіша версія, яка працює в Windows, існує дуже зручний спосіб зробити це. Щоб показати вам це, я видалив оригінальну зведену таблицю. Виберіть Вставити, зведену таблицю. Перш ніж клацнути OK, поставте прапорець Додати ці дані до моделі даних.
Створіть свою зведену таблицю як зазвичай. За допомогою спадного меню в A3 виберіть «Фільтри цінностей», «Топ 10» і попросіть п’ять найкращих клієнтів. Вибравши одну комірку у зведеній таблиці, перейдіть на вкладку Дизайн на стрічці та відкрийте спадне меню Проміжні підсумки. Останній вибір у спадному меню - Включити відфільтровані елементи в підсумки. Зазвичай цей вибір сірий. Але оскільки дані зберігаються в Моделі даних замість звичайного зведеного кешу, ця опція тепер доступна.
Виберіть варіант Включити відфільтровані елементи в підсумки, і ваш загальний підсумок тепер включає зірочку та сукупність усіх даних, як показано нижче.
Цей фокус із чарівною клітиною спочатку прийшов до мене від Дана на моєму семінарі у Філадельфії і був повторений через 15 років іншим Даном із мого семінару в Цинциннаті. Дякуємо Мігелю Кабальеро, що запропонував цю функцію.
