Excel Distinct Count или Уникальный счет. Сводные таблицы будут предлагать отдельный счетчик, если вы установите один крошечный флажок при создании сводной таблицы.
Вот и раздражение сводных таблиц. Перетащите столбец «Клиент» из таблицы «Данные» в область ЗНАЧЕНИЯ. В поле указано «Количество клиентов», но на самом деле это количество счетов-фактур, принадлежащих каждому сектору. Что, если вы действительно хотите узнать, сколько уникальных клиентов принадлежит каждому сектору?
Выберите ячейку в столбце «Количество клиентов». Щелкните Параметры поля. Поначалу Summarize Values By выглядит как та же сумма, среднее и количество, что у вас всегда было. Но прокрутите вниз до самого низа. Поскольку сводная таблица основана на модели данных, теперь у вас есть Distinct Count.
После того, как вы выберете Distinct Count, в сводной таблице будет показано отдельное количество клиентов для каждого сектора. Это было очень сложно сделать в обычных сводных таблицах.
Подсчет отличий в Excel 2010
Чтобы объединить две таблицы в Excel 2010, вам необходимо загрузить бесплатную надстройку Power Pivot от Microsoft. После того, как вы его установили, выполните дополнительные действия по переносу данных в Power Pivot:
- Выберите ячейку в таблице данных. На вкладке PowerPivot выберите Создать связанную таблицу. Если Excel оставляет вас в сетке PowerPivot, используйте alt = "" + Tab, чтобы вернуться в Excel.
- Выберите ячейку в таблице секторов. Выберите Создать связанную таблицу.
- На вкладке PowerPivot на ленте Excel или на вкладке Главная на ленте PowerPivot выберите создание сводной таблицы.
Когда приходит время создавать отношения, у вас есть только одна кнопка под названием «Создать». Excel 2010 сначала попытается определить взаимосвязи автоматически. В этом простом примере соотношение будет правильным.
Спасибо Колину Майклу и Алехандро Кисено за то, что они предложили Power Pivot в целом.
Смотреть видео
- В подкасте 2014 года представлена модель данных для объединения таблиц
- Еще одно преимущество - способность четко подсчитывать
- Обычная сводная таблица не может подсчитывать клиентов по секторам
- Добавьте данные в модель данных, и у вас будет доступное количество Distinct.
- До Excel 2013 вам нужно было добавить 1 / COUNTIF к исходным данным.
Стенограмма видео
Изучите Excel по подкасту, выпуск 2015 - Отличный счет!
Хорошо, все советы в этой книге будут подкастами, просмотрите этот плейлист для всего набора!
Итак, сегодня мы должны создать отчет, который показывает, сколько клиентов находится в каждом секторе, а обычная сводная таблица НЕ МОЖЕТ этого сделать. Таким образом, Insert PivotTable поместит секторы вниз с левой стороны, а затем запросит количество клиентов, и в нем говорится, что они дают нам количество клиентов. Но это не количество клиентов, это количество записей, хорошо, у нас нет 563 клиентов, совершенно, совершенно бесполезные. Но посмотрите на это, удивительно легко решить это, в вчерашнем подкасте мы говорили об использовании модели данных для объединения двух таблиц вместе. Сегодня у меня только одна таблица, там, вы знаете, вы не подумаете, что есть какие-то причины для использования модели данных, кроме этого, поэтому установите флажок «Добавить эти данные в модель данных».
Кстати, это новинка в Excel 2013, поэтому вам нужно 13 или 16, если вы застряли на Mac или вернулись в Excel 2010, я покажу вам старое решение здесь, в конце. Нажмите OK, создайте точно такой же отчет, секторы слева, количество клиентов, точно такой же неправильный ответ, но вот разница. Когда мы заходим в настройки поля, мы видим, что это выглядит так же: Sum, Count, Average, Max, Min, несколько пропущено, а в самом низу есть новый, который называется Distinct Count. Вау, это было так сложно сделать в старых версиях Excel, позвольте мне показать вам, как мы это делали в Excel 2010.
Итак, вот данные, вам нужно выйти и сделать СЧЁТЕСЛИ, посчитать, сколько раз Vertex42 появляется в столбце D, и оказывается, что их 6 раз. Значит, Distinct Count = 1, деленный на это, хорошо, посмотрите, что мы делаем, если есть шесть записей с Vertex42, мы даем каждой из них 1/6 или 0,16611, и когда мы складываем все это, получаем приведет нас к 1, верно? Здесь 5 записей, каждая получает 1/5 или 20%, сложите все, каждая из них приведет нас к 1. Итак, вернемся к Excel 2010 или 7 или 3, или где бы вы ни находились, у вас нет модели данных, поэтому вы добавляете туда эти дополнительные поля: Сектор, а затем - Счетчик отличий. Это было намного сложнее, чем новый способ, поэтому я, безусловно, ценю модель данных для этого. Итак, этот совет и многое другое в книге, нажмите «i» в правом верхнем углу, вы можете купить книгу,25 долларов в печати, 10 долларов за электронную книгу, это дешево!
Во вчерашнем подкасте 2014 года мы говорили о модели данных для объединения таблиц, еще одно преимущество - это возможность делать отдельный подсчет. Обычная сводная таблица не может подсчитывать клиентов по сектору, добавлять данные в модель данных, и у вас есть доступный счетчик Distinct. До Excel 2013 вы делали 1 / СЧЁТЕСЛИ в исходных данных, и, конечно, если вы хотите произвести отдельный подсчет для чего-то еще, вам, возможно, придется изменить эту формулу, что действительно очень расстраивает. Красивое, красивое побочное преимущество всего движка Power Pivot!
Скачать файл
Загрузите образец файла здесь: Podcast2015.xlsx
