Відмінний підрахунок Excel або унікальний підрахунок. Зведені таблиці пропонуватимуть різний підрахунок, якщо під час створення зведеної таблиці ви встановите один крихітний прапорець.
Ось роздратування зведених таблиць. Перетягніть стовпець Клієнт із таблиці Дані в область ЦІННОСТІ. У полі вказано "Кількість клієнтів", але насправді це кількість, яка належить рахункам-фактурам у кожному секторі. Що робити, якщо ви дійсно хочете побачити, скільки унікальних клієнтів належить до кожного сектора?
Виберіть клітинку в стовпці Кількість клієнтів. Клацніть Налаштування поля. Спочатку «Підсумовування значень за» виглядає так само, як і завжди, сума, середнє та підрахунок. Але прокрутіть униз. Оскільки зведена таблиця базується на моделі даних, тепер у вас є чіткий рахунок.
Після вибору "Відмінний підрахунок" у зведеній таблиці відображається окремий підрахунок клієнтів для кожного сектора. Це було дуже важко зробити в звичайних зведених таблицях.
Підрахувати чітко в Excel 2010
Щоб приєднати дві таблиці в Excel 2010, потрібно завантажити безкоштовну надбудову Power Pivot від Microsoft. Після того, як ви це встановите, ось додаткові кроки для завантаження ваших даних у Power Pivot:
- Виділіть комірку в таблиці Дані. На вкладці PowerPivot виберіть Створити зв’язану таблицю. Якщо Excel залишає вас у сітці PowerPivot, використовуйте alt = "" + Tab, щоб повернутися до Excel.
- Виділіть комірку в таблиці Сектори. Виберіть Створити зв’язану таблицю.
- На вкладці PowerPivot на стрічці Excel або на вкладці Домашня сторінка на стрічці PowerPivot виберіть створити зведену таблицю.
Коли приходить час створювати стосунки, у вас є лише одна кнопка під назвою Створити. Excel 2010 спробує спочатку автоматично визначити відносини. У цьому простому прикладі це налагодить стосунки.
Дякуємо Коліну Майклу та Алехандро Кікено за те, що вони загалом запропонували Power Pivot.
Переглянути відео
- Ввів модель даних у Podcast 2014 для об’єднання таблиць
- Ще однією перевагою є можливість робити чіткий підрахунок
- Звичайна зведена таблиця не може враховувати клієнтів за сектором
- Додайте дані до моделі даних, і у вас буде доступний виразний підрахунок
- До Excel 2013 потрібно було додати 1 / COUNTIF до вихідних даних
Стенограма відео
Дізнайтеся Excel з подкасту, серія 2015 - Визначний рахунок!
Добре, усі поради в цій книзі будуть підкастом, перегляньте цей список відтворення для всього набору!
Добре, тому сьогодні ми маємо створити звіт, який показує, скільки клієнтів у кожному секторі, і звичайна зведена таблиця НЕ МОЖЕ це робити. Отже, Insert PivotTable розмістить сектори в лівій частині, а потім запитає кількість клієнтів, і він каже, що це дає нам кількість клієнтів. Але це не підрахунок Клієнта, це стільки записів, добре, у нас немає 563 клієнтів, абсолютно, абсолютно марних. Але перевірте це, надзвичайно легко вирішити це, вчорашній подкаст, про який ми говорили, використовуючи модель даних, щоб об’єднати 2 таблиці разом. Сьогодні у мене є лише одна таблиця. Ви знаєте, ви не думаєте, що існує будь-яка причина використовувати модель даних, крім цієї, тож поставте прапорець “Додати ці дані до моделі даних”.
До речі, це абсолютно нове в Excel 2013, тому вам потрібно 13 або 16, якщо ви застрягли на Mac або в Excel 2010, я покажу вам старе рішення тут наприкінці. Натисніть кнопку ОК, створіть точно такий самий звіт, сектори вниз ліворуч, підрахунок Клієнта, точно таку ж неправильну відповідь, але тут різниця. Коли ми потрапляємо в поле Налаштування полів, воно виглядає однаково: Сума, Підрахунок, Середнє, Макс., Хв., Там декілька відсутніх, а в самому низу є нове під назвою Виразний підрахунок. Ого, це те, що було так важко зробити у старих версіях Excel, насправді дозвольте мені показати вам, як ми робили це в Excel 2010.
Отже, ось дані, вам потрібно було б вийти і зробити COUNTIF, підрахувати, скільки разів Vertex42 з’являється у стовпці D, і, здається, це 6 разів. Отже, тоді Distinct Count = 1 ділиться на це, добре, подивіться, що ми робимо, якщо є шість записів з Vertex42, ми даємо кожному з них 1/6 або 0,16611, а коли все це складемо, це приведе нас до 1, так? Тут є 5 записів, кожен отримує 1/5 або 20%, складаємо всі, кожен з них отримує нас до 1. Отже, в Excel 2010 або 7 або 3 або де б ви не були, у вас немає моделі даних, отже, ви додаєте там додаткові поля, Sector, а потім Distinct Count. Це було набагато складніше, ніж новий спосіб, тому я, безумовно, ціную модель даних для цього. Що ж, цю пораду та багато іншого в книзі натисніть кнопку "i" у верхньому правому куті. Ви можете придбати книгу,25 доларів у друкованому вигляді, 10 доларів за електронну книгу, це дешево!
У вчорашньому подкасті 2014 року ми говорили про модель даних для об’єднання таблиць, ще однією перевагою є можливість робити чіткий підрахунок. Звичайна зведена таблиця не може підрахувати клієнтів за сектором, додати дані до моделі даних, і у вас є доступний чіткий підрахунок. До Excel 2013 ви робите 1 / COUNTIF у вихідних даних, і звичайно, якщо ви хочете зробити чіткий підрахунок чогось іншого, можливо, вам доведеться змінити цю формулу, дійсно, дуже розчаровуючи. Прекрасна, красива бічна перевага всього двигуна Power Pivot!
Завантажити файл
Завантажте зразок файлу тут: Podcast2015.xlsx
