Подсчет уникальных значений в сводной таблице

Использование модели данных со сводной таблицей

Модель данных — еще одна вещь, которая мне нравится в новых версиях Microsoft Excel. Если вы используете Excel для Microsoft 365, Excel 2019, Excel 2016 и Excel 2013, у вас есть доступ к модели данных.

Чтобы начать, щелкните любую ячейку в данных и перейдите на вкладку «Вставка» на ленте.
Нажмите здесь на сводной таблице, и появится диалоговое окно.
Теперь установите флажок в нижней части диалогового окна «Добавить эти данные в модель данных» и нажмите «ОК».
После этого вы получите обычную сводную таблицу и упорядочите свои данные в полях сводной таблицы, как вы это делали ранее. Это даст вам ту же сводную таблицу, что и раньше, но поля сводной таблицы немного отличаются.
Вот в чем хитрость: щелкните маленькую стрелку рядом с надписью «Количество поставщиков услуг» в полях сводной таблицы.
После этого нажмите «Настройки поля значений».
Теперь прокрутите до конца, чтобы найти «Отдельная учетная запись», и нажмите «ОК».
Начнем: у вас есть отдельный/уникальный номер для каждого региона в сводной таблице.

Поэтому у нас в стране всего 18 уникальных поставщиков услуг.

Использование функции СЧЕТЕСЛИ

Другой подход к вычислению уникальных записей — просто использовать формулу СЧЕТЕСЛИ в таблице данных.

Начнем с добавления столбца к вашим данным с заголовком по вашему выбору. Здесь мы назовем его «Счет №».
Добавьте эту формулу (=ЕСЛИ (СЧЕТЕСЛИ ($B$2:B2,B2)>1,0,1)) в ячейку D2 и перетащите ее в конец.

Как работает эта формула??

Сначала мы устанавливаем начальную точку диапазона, также называемую Абсолютной, то есть $B$2. Это означает, что она не изменится, даже если вы перетащите формулу вниз. Теперь, когда вы перетаскиваете формулу вниз на D3, эта формула принимает вид IF(COUNTIF($B$2:B3,B3)>1,0,1)

Прочтите это как

Countif ( $B$2:B3 , B3 ) даст вам количество раз, когда B3 существует в диапазоне $B$2:B3. Функция ЕСЛИ используется для добавления условия: ЕСЛИ (( количество раз, когда B3 существует в заданном диапазоне ) больше 1, то введите 0, в противном случае верните 1)

Теперь, если имя в данном столбце встречается более одного раза, формула вернет вам 0, в противном случае вы получите 1. Следовательно, для всех этих повторяющихся имен вы получите 0 в столбце «Не считать».

Теперь создайте сводную таблицу с вашими данными.
Здесь вам нужно добавить «Местоположение» в «СТРОКИ» и «Количество номеров» в значения.
Бум!! Сводная таблица готова с уникальными записями в каждой сводной таблице.

Используйте Power Pivot для подсчета уникальных значений

Вот самый мощный метод идентификации уникальных записей; Силовой стержень. Убедитесь, что на ленте есть вкладка Power Pivot. Если вы не можете найти вкладку, ознакомьтесь с этим руководством .

Как было сказано ранее, прежде всего убедитесь, что вкладка Power Pivot включена.
После этого перейдите к модели данных и нажмите кнопку «Управление» .
Здесь откроется окно, которое наверняка будет пустым, если вы импортируете данные впервые.
Нажмите «Домой» → «Получить внешние данные».
Здесь вы найдете несколько вариантов и источников, доступных для загрузки данных. Но нам нужно скачать простой Excel. Поэтому следуйте инструкциям и скриншотам и нажмите «Из других источников».
Теперь вы снова получите открытое диалоговое окно. Прокрутите до конца, чтобы выбрать опцию «Файл Excel», и нажмите «Далее».
Здесь вы можете переименовать соединение, используя имя по умолчанию «Excel». Нажмите «Обзор», чтобы выбрать путь к файлу данных.
Кроме того, если вы хотите, чтобы верхний столбец был строкой заголовка, установите флажок «Использовать первую строку в качестве заголовка столбца» и нажмите «Далее».
В конце файл импортируется в модель данных и нажмите «Готово».
Итак, все 28 строк успешно импортированы. Теперь ударьте поближе.
Вот как это выглядит.
Отсюда мы создадим сводную таблицу, выбрав Главная → Сводная таблица.
Поскольку у нас есть данные на Листе 1, мы расширим столбцы, щелкнув маленький треугольник рядом с ним.
Теперь укажите местоположение в строках и поставщиков услуг в значениях, как мы делали раньше. В результате получится простая сводная таблица с общим количеством поставщиков услуг.
Вот в чем хитрость. Теперь перейдите в окно PowerPivot и нажмите «Измерить» , чтобы получить опцию «Новое измерение» .
Теперь добавьте описание нужного имени и начните вводить формулу в разделе формул.
Когда вы начнете печатать, вы автоматически получите предложения. Здесь нам понадобится отдельная функция подсчета. Выберите отдельную функцию подсчета.
После этого нажмите кнопку табуляции или поставьте скобку (и выберите столбец, для которого нам нужно определенное число. Как и здесь, нам нужно определенное количество поставщиков услуг. Поэтому наша формула будет выглядеть так = DISTINCTCOUNT(Лист1[Поставщик услуг]) )
В конце выберите категорию. Поскольку мы выясняем уникальный номер поставщиков услуг, выберем категорию «Номера».
Измените формат на «Целое число» и нажмите «ОК». В сводную таблицу будет добавлен еще один столбец, который предоставит вам уникальные записи.