Зведена таблиця Top 10 фільтр дає загальну кількість видимих рядків
Зведені таблиці пропонують фільтр Top 10. Це круто. Це гнучко. Але я ненавиджу це, і я скажу вам, чому.
Ось зведена таблиця, що відображає дохід за клієнтом. Загальний дохід - 6,7 мільйона доларів.
Що робити, якщо мій менеджер приділяє увагу золотої рибки і хоче бачити лише п’ять найкращих клієнтів?
Для початку відкрийте спадне меню в A3 і виберіть Фільтри значень, Топ 10.
Надзвичайно гнучке діалогове вікно Топ-10 фільтра дозволяє зверху / знизу. Це може бути 10, 5 або будь-яке інше число. Ви можете попросити п’ять найкращих позицій, 80% або достатню кількість клієнтів, щоб отримати 5 мільйонів доларів.
Але тут проблема: Отриманий звіт показує п’ять клієнтів та загальну кількість цих клієнтів замість загальної суми всіх.
Але спочатку кілька важливих слів про автофільтр
Я усвідомлюю, що це виглядає як поза стіною питання. Якщо ви хочете ввімкнути спадні меню Фільтр на звичайному наборі даних, як це зробити? Ось три справді поширені способи:
- Виділіть одну клітинку у своїх даних і натисніть піктограму Фільтр на вкладці Дані.
- Виділіть усі свої дані за допомогою Ctrl + * і клацніть піктограму Фільтр на вкладці Дані.
- Натисніть Ctrl + T, щоб відформатувати дані у вигляді таблиці.
Це три справді хороші способи. Поки ви знаєте будь-якого з них, зовсім не потрібно знати іншого шляху. Але ось неймовірно незрозумілий, але чарівний спосіб увімкнути фільтр:
- Перейдіть до свого рядка заголовків, перейдіть до самої правої комірки заголовка. Перемістіть одну клітинку вправо. З якоїсь невідомої причини, коли ви знаходитесь у цій комірці та натискаєте піктограму Фільтр, Excel фільтрує дані, встановлені зліва від вас. Я не уявляю, чому це працює. Про це насправді не варто говорити, тому що вже є три справді хороших способи ввімкнути спадні меню Фільтр. Я називаю цю клітину Чарівною клітиною.
А тепер, повернення до зведених таблиць …
Отже, існує правило, яке говорить, що ви не можете використовувати автофільтри, коли ви знаходитесь у зведеній таблиці. Дивіться нижче? Значок Фільтр сірий, оскільки я вибрав клітинку у зведеній таблиці.
Я ніколи не думав, чому Microsoft це сіре. Це має бути щось внутрішнє, що говорить, що автофільтр і зведена таблиця не можуть співіснувати. Отже, у команді Excel є хтось, хто відповідає за сіре зображення піктограми Фільтр. Ця людина ніколи не чула про Чарівну клітину. Виділіть комірку у зведеній таблиці, і фільтр буде сірим. Клацніть за межами зведеної таблиці, і фільтр знову ввімкнено.
Але почекай. А як щодо Чарівної клітини, про яку я тобі що розповідав? Якщо натиснути клітинку праворуч від останнього заголовка, Excel забуде виділити піктограму фільтра сірим кольором!
Звичайно, Excel додає спадні меню AutoFilter у верхній рядок зведеної таблиці. А автофільтр працює інакше, ніж фільтри зведеної таблиці. Перейдіть до спадного меню Дохід і виберіть Кількісні фільтри, Топ 10 …
У діалоговому вікні Top 10 AutoFilter виберіть Top 6 Items. Це не помилка … Якщо ви хочете п’ять клієнтів, виберіть 6. Якщо ви хочете 10 клієнтів, виберіть 11.
Для AutoFilter загальний рядок є найбільшим елементом у даних. П’ять найкращих клієнтів займають позиції з 2 по 6 у даних.
Обережно
Очевидно, що цим трюком ви рвете дірку в тканині Excel. Якщо пізніше ви зміните базові дані та оновите свою зведену таблицю, Excel не оновить фільтр, оскільки, наскільки відомо Microsoft, немає можливості застосувати фільтр до зведеній таблиці!
Примітка
Наша мета - зберегти це в таємниці від Microsoft, оскільки це досить класна функція. Він був «зламаний» протягом досить тривалого часу, тому є багато людей, які, можливо, вже покладаються на нього.
Повністю юридичне рішення в Excel 2013+Якщо ви хочете зведену таблицю, що відображає п’ять найкращих клієнтів, але загальну кількість усіх клієнтів, вам доведеться перемістити дані за межі Excel. Якщо у вас є Excel 2013 або 2016, це дуже зручний спосіб зробити це. Щоб показати вам це, я видалив оригінальну зведену таблицю. Виберіть Вставити, зведену таблицю. Перш ніж натискати OK, поставте прапорець Додати ці дані до моделі даних.
Створіть свою зведену таблицю як зазвичай. За допомогою спадного меню в A3 виберіть «Фільтри цінностей», «Топ 10» і попросіть п’ять найкращих клієнтів. Вибравши одну клітинку у зведеній таблиці, перейдіть на вкладку Дизайн на стрічці та відкрийте спадне меню Проміжні підсумки. Останній вибір у спадному меню - Включити відфільтровані елементи в підсумки. Зазвичай цей вибір сірий. Але оскільки дані зберігаються в Моделі даних замість звичайного зведеного кешу, ця опція тепер доступна.
Виберіть варіант Включити відфільтровані елементи в загальну суму, і ваш загальний підсумок тепер включає зірочку та загальну кількість усіх даних.
Цей фокус спочатку прийшов до мене від Дена на моєму семінарі у Філадельфії. Дякуємо Мігелю Кабальеро, що запропонував цю функцію.
Переглянути відео
- Зведена таблиця Top 10 фільтр дає загальну кількість видимих рядків
- Включити відфільтровані елементи до підсумків виділено сірим кольором
- Дивний спосіб викликати фільтр даних із чарівної комірки
- Фільтри даних не дозволяються у зведених таблицях
- Excel не вдається виділити фільтр даних із чарівної комірки
- Попросіть 6 найкращих, щоб отримати топ 5 плюс загальний підсумок
- Корисно для фільтрації за певним елементом основи
- Excel 2013 або новішої версії: інший спосіб отримати справжній підсумок
- Надсилайте свої дані через модель даних
- Включити відфільтровані елементи до підсумків буде доступним
- Отримайте Total із зірочкою
- Цьому фокусу я навчився понад 10 років тому від Дену у Філадельфії
Стенограма відео
Дізнайтеся Excel для подкасту, епізод 1999 - зведена таблиця Точна п’ятірка
Я подкастирую всю цю книгу. Є список відтворення, натисніть I у верхньому правому куті, щоб слідувати за цим списком відтворення. Ласкаво просимо до мережі. Я Білл Джелен.
Гаразд, отже, ми збираємося створити зведену таблицю і хочемо показати не всіх клієнтів, а лише п’ять найкращих клієнтів. ВСТАВИТИ, зведену таблицю. Гаразд, я відкладу Клієнта ліворуч та Дохід. Гаразд, от і весь наш список клієнтів, який зазначив 6,7 мільйона доларів. Excel, дозволяє легко скласти п’ятірку найкращих. Перейдіть до Етикетки рядків, Фільтри значень, топ 10. Не обов’язково. Це може бути зверху або знизу. Не повинно бути п’ять. Це може бути двадцять, сорок, може бути що завгодно. Найкращі вісімдесят відсотків, дайте мені достатньо записів, щоб дістатися до трьох мільйонів доларів або чотирьох мільйонів доларів, але ось ми йдемо. П’ять найкращих предметів. А тепер пам’ятайте 6,7 мільйона доларів, натисніть ОК, і моя велика проблема тут полягає в тому, що загальна сума - це не 6,7 мільйона. Коли я даю це віце-президенту з продажів, він збентежиться, сказавши, почекайте секунду,Я знаю, що заробив більше 3,3 мільйона доларів. Так, отже, ми збираємося скасувати, скасувати це і повернутися до вихідних даних.
Тепер цей наступний трюк я дізнався під час одного зі своїх семінарів Power Excel у Філадельфії. Хлопець на ім’я Ден у другому ряду показав мені це. Це було більше десяти років тому, коли він показав мені цей фокус, і спочатку ми повинні поговорити про фільтри. Отже, як правило, якщо ви збираєтеся використовувати звичайний фільтр, цей фільтр тут, ви вибираєте будь-яку клітинку у вашому наборі даних і клацаєте піктограму фільтра, або деякі люди вибирають весь набір даних, CONTROL * і клацають піктограму фільтра, але є третій шлях. Спосіб, про який ніхто не дбає. Якщо ви перейдете до останньої заголовкової комірки, в моєму випадку, це вартість у L1, і перейдете на одну клітинку праворуч. Я називаю це чарівною клітиною, я не уявляю, чому, але з невідомої причини з цієї комірки я можу відфільтрувати сусідній набір даних. Добре, це як дивний спосіб, і ніхто про це не дбає.
Правильно, оскільки є два інших дійсно хороших способи викликати фільтр, ніхто не повинен знати про чарівну клітинку, але ось у чому річ, див. Всередині зведеної таблиці, вона сіра. Вам не дозволено використовувати ці фільтри. Це суперечить правилам. Тепер, якщо я вийду сюди, я більше ніж можу скористатися фільтром, але всередині вони оцінюються. Я не знаю, хто це людина, яка це затьмарює, але вони ніколи не чули моєї маленької розмови про чарівну клітину, тому що якщо я перейду до останньої Заголовочної комірки і перейду на одну клітинку праворуч, дивись на це, вони забувають виділити фільтр сірим кольором, і тепер я щойно додав старі автоматичні фільтри до зведеної таблиці. Тому я приходжу сюди, переходжу до фільтрів чисел, які відрізняються від фільтрів значень. Він все ще називається Десятка. Трохи інакше, я збираюся попросити п’ятірку, а не шість найкращих.Шість найкращих, оскільки для цього фільтру загальний підсумок - це лише черговий рядок, а загальний підсумок - найбільший предмет, а потім, коли запитують пункти з 2 по 6, я отримую п’ять найкращих елементів.
Гаразд, отже, ми там. Прохолодний фільтр, який дає нам п’ять найкращих елементів і справжню загальну кількість усіх. Гаразд, пара речей. Не забувайте про чарівну клітинку. Добре, немає можливості вимкнути цей фільтр, якщо ви не повернетесь до чарівної комірки. Добре, так що вам потрібно пам’ятати чарівну клітинку. Крім того, якщо ви зміните базові дані та оновите зведену таблицю, вони не збираються оновлювати фільтр, оскільки, наскільки відомо Microsoft, вам не дозволено мати фільтр.
Це корисно для інших речей. Іноді у нас є продукти, що йдуть по вершині. Давайте підемо сюди у вигляді таблиці. Не потрібно, я просто люблю отримувати справжні заголовки. Gizmo, Widget, Gadgets, Doodads. Добре, і, можливо, ви менеджер Doodads, і вам потрібно бачити лише клієнтів, які мали певну цінність, і Doodads. Тож я заходжу до чарівної комірки, вмикаю Фільтр, а потім під Doodads я можу запитати предмети, більші за нуль. Клацніть OK. Добре, такий тип фільтрації неможливий для звичайної зведеної таблиці, але це можливо за допомогою магічної комірки.
Гаразд, давайте скасуємо список. Давайте вимкнемо цей фільтр і видалимо зведену таблицю, і якщо ви перебуваєте у програмі Excel 2013 або новішій версії, я покажу вам абсолютно законний спосіб отримати правильну загальну суму внизу. Вставте зведену таблицю внизу, починаючи з Excel 2013, це дуже нешкідливе вікно звучить не дуже захоплююче, додайте ці дані до моделі даних. Це надсилає дані за кадром до Power Pivot Engine. Складіть точно такий же звіт. Клієнти вниз ліворуч. Дохід у центрі зведеної таблиці. Потім перейдіть до звичайних фільтрів, фільтри значень - топ 10. Попросіть перші п’ять. Зверніть увагу, у нас після цього я маю 6,7 мільйона доларів, 3,3 мільйона доларів, але ось різниця. Коли я переходжу на вкладку «Дизайн» у розділі «Проміжні підсумки», ця функція називається «Включити відфільтровані елементи в підсумки»,більше не сірий. У звичайній зведеній таблиці немає. Ми отримуємо там маленьку зірочку, і це всього. Добре, тепер, звичайно, це працює лише в Excel 2013 або новіших версіях.
Добре, це займе шість тижнів, поки я отримаю всю цю книгу тут, на YouTube. Тут так багато хороших порад. Поради, які можуть почати економити ваш час, відразу. Придбайте всю книгу прямо зараз, і ви матимете доступ до всіх 40, насправді це набагато більше 40 підказок. Клавіші швидкого доступу Excel. Різноманітні чудові речі в цій книзі.
Добре, резюме. Отже, коли ми робимо фільтр верхньої 10 таблиці зведеної таблиці, він дає нам загальну кількість, але лише видимі рядки, а не матеріали, які він відфільтрував. Так, якщо ми перейдемо до другої вкладки і будемо шукати проміжні підсумки, відфільтровані елементи та підсумки, це буде сірим, але існує дивний спосіб викликати старий фільтр даних із чарівної комірки. Остання комірка заголовка, перейдіть на одну клітинку праворуч, ви не можете використовувати фільтри та зведені таблиці, але якщо ви перейдете до чарівної комірки, вони забудуть її сірим кольором. Тепер у фільтрі чисел ви просите шість найкращих, щоб отримати п’ятірку, плюс загальний підсумок. Також корисно для фільтрації до певного елемента зведення: Doodads - все, що мало більше 0 у Doodads або 5 найкращих Doodads. Excel 2013 або новішої версії, існує інший спосіб отримати True Total.Поставте прапорець біля Моделі даних, а потім включіть Відфільтровані елементи в Підсумки. Підсумок ви отримуєте зірочкою. І завдяки Дену у Філадельфії, який показав мене на одному з моїх семінарів Power Excel, більше десяти років тому, та дав мені цей чудовий маленький фокус. Спосіб для фільтра пробратися крізь стіну клубного зведеного столу. Зазвичай вони не дозволяють цей автоматичний фільтр.
Гей, я хочу подякувати вам за те, що завітали. Ми побачимось наступного разу, для чергової трансляції від MRExcel.
Завантажити файл
Завантажте зразок файлу тут: Podcast1999.xlsx
