Зведені таблиці Excel чудові (я знаю, що згадую про це щоразу, коли пишу про зведені таблиці, але це правда).

З базовим розумінням та невеликим перетягуванням, ви можете виконати більшу частину роботи за кілька секунд.

Хоча в зведених таблицях можна зробити багато за допомогою декількох клацань мишею, є деякі речі, які вимагатимуть додаткових дій або невеликої роботи.

І одна з таких речей – підрахунок різних значень у зведеній таблиці.

У цьому посібнику я покажу вам, як підраховувати окремі значення, а також унікальні значення в таблиці Excel.

Але перш ніж я перейду до підрахунку різних значень, важливо зрозуміти різницю між «унікальним рахунком» та «унікальним рахунком».

Відмінний рахунок проти унікального лічильника

Хоча це може здатися одним і тим же, це не.

Нижче наведено приклад набору даних з іменами, і я окремо перерахував унікальні та унікальні імена.

Унікальні значення / імена ті, що виникають лише один раз. Це означає, що всі імена, що повторюються і повторюються, не унікальні. Унікальні імена перераховані в стовпці C у наведеному вище наборі даних.

Відмінні цінності/імена ті, що зустрічаються в наборі даних хоча б один раз. Таким чином, якщо ім'я зустрічається тричі, воно все одно вважається одним окремим ім'ям. Цього можна досягти, вилучивши значення/імена, що повторюються, і зберігши всі різні. Відмінні імена перераховані у стовпці B наведеного вище набору даних.

Ґрунтуючись на тому, що я бачив, у більшості випадків, коли люди кажуть, що вони хочуть отримати унікальну кількість у зведеній таблиці, вони насправді мають на увазі окрему кількість, про що я й розповідаю в цьому посібнику.

Підрахунок різних значень у зведеній таблиці Excel

Припустимо, у вас є дані про продаж, як показано нижче:

Натисніть тут, щоб завантажити файл з прикладом, і дотримуйтесь інструкцій.

Припустимо, ви хочете знайти відповідь на такі запитання:

1. Скільки торгових представників працює у кожному регіоні (це не що інше, як окрема кількість торгових представників у кожному регіоні)?
2. Скільки торгових представників продали принтер у 2021-2022 рр.?

Хоча зведені таблиці можуть миттєво підсумовувати дані за допомогою кількох натискань мишею, щоб отримати кількість різних значень, вам потрібно буде зробити ще кілька кроків.

Якщо ви використовуєте Excel 2013 або пізніші версіїУ зведену таблицю є вбудована функція, яка швидко дає вам точний рахунок. І якщо ви використовуєте Excel 2010 або попередні версіїВам доведеться змінити вихідні дані, додавши допоміжний стовпець.

У цьому посібнику розглядаються такі два методи:

• Додавання допоміжного стовпця до вихідного набору даних для підрахунку унікальних значень (працює у всіх версіях).
• Додавання даних до моделі даних та використання опції Distinct Count (доступно в Excel 2013 та пізніших версіях).

У цій статті Роджер показує третій метод (який він називає методом Pivot the Pivot Table).

Додавання допоміжного стовпця до набору даних

Примітка.Якщо ви використовуєте Excel 2013 і новіші версії, пропустіть цей метод і перейдіть до наступного (оскільки він використовує вбудовану функцію зведеної таблиці – Відмінний граф).

Це простий спосіб підрахунку різних значень у зведеній таблиці, оскільки вам потрібно лише додати допоміжний стовпець до вихідних даних. Після того, як ви додали допоміжний стовпець, ви можете використовувати новий набір даних для обчислення окремого числа.

Хоча це простий обхідний шлях, цей метод має деякі недоліки (які будуть розглянуті пізніше в цьому посібнику).

Дозвольте мені спочатку показати вам, як додати допоміжний стовпець та отримати точну кількість.

Припустимо, у мене є набір даних, як показано нижче:

Додайте наступну формулу в стовпець F і застосуйте її до всіх осередків, які містять дані в сусідніх стовпцях.

= ЯКЩО (ПОЛІЧИЛИ ($ C $ 2: C2, C2; $ B $ 2: B2, B2)> 1,0,1)

У наведеній вище формулі використовується функція РАХУНОК, щоб підрахувати, скільки разів ім'я з'являється в даному регіоні. Також зверніть увагу, що діапазон критеріїв – $ C $ 2: C2 і $ B $ 2: B2. Це означає, що він продовжує розширюватися в міру того, як ви спускаєтеся по стовпцю.

Наприклад, в клітинці E2 діапазони критеріїв: $ C $ 2: C2 і $ B $ 2: B2, а в комірці E3 ці діапазони розширюються до $ C $ 2: C3 і $ B $ 2: B3.

Це гарантує, що функція РАХУНОК вважає перший екземпляр імені як 1, другий екземпляр імені як 2 і так далі.

Оскільки ми хочемо отримати лише окремі імена, використовується функція ЯКЩО, яка повертає 1, коли ім'я з'являється для регіону вперше, і повертає 0, коли воно з'являється знову.Це гарантує, що враховуються лише окремі імена, а чи не повторення.

Нижче показано, як виглядатиме ваш набір даних після додавання допоміжного стовпця.

Тепер, коли ми змінили вихідні дані, ми можемо використовувати їх для створення зведеної таблиці та використовувати допоміжний стовпець для отримання окремої кількості торгових представників у кожному регіоні.

Нижче наведено кроки для цього:

1. Виберіть будь-яку комірку в наборі даних.
2. Клацніть вкладку "Вставити".
3. Натисніть на зведену таблицю (або скористайтеся поєднанням клавіш – ALT + N + V)
4. У діалоговому вікні «Створення зведеної таблиці» переконайтеся, що таблиця/діапазон вказані правильно (і включають допоміжний стовпець) та вибрано параметр «Новий робочий лист».
5. Натисніть кнопку ОК.

Вищезгадані кроки дозволять вставити новий аркуш зі зведеною таблицею.

Перетягніть поле «Регіон» у область «Рядки» та поле «D Count» у область «Значення».

Ви отримаєте зведену таблицю, як показано нижче:

Тепер ви можете змінити заголовок стовпця з "Sum of D count" на "Sales Rep".

Недоліки використання допоміжної колонки:

Хоча цей метод досить простий, я мушу виділити кілька недоліків, пов'язаних із зміною вихідних даних у зведеній таблиці:

• Джерело даних з допоміжним стовпцем не таке динамічне, як зведена таблиця. Хоча за допомогою зведеної таблиці ви можете розрізати дані як завгодно, коли ви використовуєте допоміжний стовпець, ви втрачаєте частину цієї можливості. Допустимо, ви додали допоміжний стовпець, щоб отримати кількість окремих торгових представників у кожному регіоні. Тепер, якщо ви також хочете отримати виразну кількість торгових представників, що продають принтери.Вам потрібно буде повернутися до вихідних даних та змінити формулу допоміжного стовпця (або додати новий допоміжний стовпець).
• Оскільки ви додаєте більше даних у джерело зведеної таблиці (яка також додається у зведений кеш), це може призвести до збільшення розміру файлу Excel.
• Оскільки ми використовуємо формулу Excel, це може уповільнити роботу вашої книги Excel, якщо у вас тисячі рядків даних.

Додати дані в модель даних та підбити підсумки, використовуючи певну кількість

У зведену таблицю Excel 2013 додано нові функції, які дозволяють отримувати точну кількість під час підсумовування набору даних.

Якщо ви використовуєте попередню версію, ви не зможете використовувати цей метод (слід також спробувати додати допоміжний стовпець, як показано в методі вище).

Припустимо, у вас є набір даних, як показано нижче, і ви хочете отримати кількість унікальних торгових представників у кожному регіоні.

Нижче наведено кроки, щоб отримати чітке значення лічильника у зведеній таблиці:

1. Виберіть будь-яку комірку в наборі даних.
2. Клацніть вкладку Вставка.
3. Натисніть на зведену таблицю (або скористайтеся поєднанням клавіш – ALT + N + V)
4. У діалоговому вікні «Створення зведеної таблиці» переконайтеся, що таблиця/діапазон вказано правильно і «Новий робочий лист» вибраний.
5. Встановіть прапорець «Додати ці дані до моделі даних».
6. Натисніть кнопку ОК.

Вищезгадані кроки дозволять вставити новий аркуш із новою зведеною таблицею.

Перетягніть область в область "Рядки" та "Торговий представник" в область "Значення". Ви отримаєте зведену таблицю, як показано нижче:

У наведеній вище зведеній таблиці зазначено загальну кількість торгових представників у кожному регіоні (а чи не окрему кількість).

Щоб отримати точну кількість у зведеній таблиці, виконайте такі дії:

1. Клацніть правою кнопкою миші будь-яку комірку в стовпці «Кількість торгових представників».
2. Натисніть кнопку Параметри поля значень.
3. У діалоговому вікні "Параметри поля значення" виберіть "Distinct Count" як тип розрахунку (можливо, вам доведеться прокрутити список вниз, щоб знайти його).
4. Натисніть кнопку ОК.

Ви помітите, що назва стовпця зміниться з «Кількість торгових представників» на «Відмінну кількість торгових представників». Ви можете змінити його на все, що захочете.

Деякі речі, які ви знаєте, додаючи дані до моделі даних:

• Якщо ви збережете дані в моделі даних, а потім відкриєте їх у старішій версії Excel, з'явиться попередження: "Деякі функції зведеної таблиці не будуть збережені". Ви можете не побачити окрему кількість (і модель даних) при відкритті у старішій версії, яка його не підтримує.
• Коли ви додаєте дані в модель даних і складаєте зведену таблицю, в ній не відображатимуться параметри для додавання полів і обчислюваних стовпців.

Натисніть тут, щоб завантажити файл прикладу

Що робити, якщо хочете підраховувати унікальні значення (а не окремі значення)?

Якщо ви хочете підрахувати унікальні значення, у вас немає вбудованих функцій у зведеній таблиці, і вам доведеться покладатися лише на допоміжні стовпці.

Пам'ятайте: унікальні значення та різні значення – це не одне й те саме. Натисніть тут, щоб дізнатися різницю.

Одним із прикладів може бути той випадок, коли у вас є наведений нижче набір даних, і ви хочете дізнатися, скільки торгових представників є унікальними для кожного регіону. Це означає, що вони працюють лише в одному конкретному регіоні, а не в інших.

У таких випадках вам потрібно створити один із кількох допоміжних стовпців.

У цьому випадку допоможе наступна формула:

= ЯКЩО (ЯКЩО (ЗЛІЧИЛИ ($ C $ 2: $ C $ 1001; C2; $ B $ 2: $ B $ 1001; B2) / ЗЛІЧИЛИ ($ C $ 2: $ C $ 1001; C2) 1,0, 1);

Наведена вище формула перевіряє, чи зустрічається ім'я торгового представника лише у одному регіоні чи кількох регіонах. Це робиться шляхом підрахунку кількості входжень імені в регіоні та поділу його на загальну кількість входжень імені. Якщо значення менше 1, це означає, що ім'я зустрічається у двох або більш ніж двох регіонах.

Якщо ім'я зустрічається більш ніж в одному регіоні, то повертається 0, інакше повертається одиниця.

Формула також перевіряє, чи повторюється ім'я в тому ж регіоні чи ні. Якщо ім'я повторюється, перший екземпляр імені повертає значення 1, а всі інші екземпляри повертають 0.

Це може здатися трохи складним, але це знову ж таки залежить від того, чого ви намагаєтеся досягти.

Отже, якщо ви хочете підрахувати унікальні значення зведеної таблиці, використовуйте допоміжні стовпці, а якщо ви хочете підрахувати окремі значення, ви можете використовувати вбудовані функції (в Excel 2013 і вище) або можете використовувати допоміжний стовпець.

Натисніть тут, щоб завантажити файл прикладу

Вам також можуть сподобатися такі посібники зі зведених таблиць:

• Як фільтрувати дані у зведеній таблиці в Excel
• Як групувати дати у зведених таблицях в Excel
• Як згрупувати числа у зведеній таблиці в Excel
• Як застосувати умовне форматування у зведеній таблиці в Excel
• Зрізи у зведеній таблиці Excel
• Як оновити зведену таблицю в Excel
• Видалити зведену таблицю в Excel

Модель даних – ще одна річ, яка мені подобається у нових версіях Microsoft Excel. Якщо ви використовуєте Excel для Microsoft 365, Excel 2019, Excel 2016 та Excel 2013, у вас є доступ до моделі даних.

1. Щоб почати, клацніть будь-яку комірку даних і перейдіть на вкладку «Вставка» на стрічці.
2. Натисніть тут на зведеній таблиці і з'явиться діалогове вікно.
3. Тепер встановіть прапорець у нижній частині діалогового вікна «Додати ці дані до моделі даних» та натисніть «OK».
4. Після цього ви отримаєте звичайну зведену таблицю та впорядкуєте свої дані у полях зведеної таблиці, як ви це робили раніше. Це дасть вам ту саму зведену таблицю, що й раніше, але поля зведеної таблиці трохи відрізняються.
5. Ось у чому хитрість: клацніть маленьку стрілку поруч із написом «Кількість постачальників послуг» у полях зведеної таблиці.
6. Після цього натисніть «Параметри поля значень».
7. Тепер прокрутіть до кінця, щоб знайти окремий обліковий запис, і натисніть ОК.
8. Почнемо: ви маєте окремий/унікальний номер для кожного регіону в зведеній таблиці.

Тому у нас у країні лише 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 в стовпці «Не рахувати».

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

Використовуйте Power Pivot для підрахунку унікальних значень

Ось найпотужніший метод ідентифікації унікальних записів; Силовий стрижень. Переконайтеся, що на стрічці є вкладка Power Pivot. Якщо ви не можете знайти вкладку, перегляньте цей посібник .

1. Як було сказано раніше, перш за все переконайтеся, що вкладку Power Pivot увімкнено.
2. Після цього перейдіть до моделі даних та натисніть кнопку «Управління» .
3. Тут відкриється вікно, яке, напевно, буде порожнім, якщо ви імпортуєте дані вперше.
4. Натисніть «Додому» → «Отримати зовнішні дані».
5. Тут ви знайдете кілька варіантів та джерел, доступних для завантаження даних. Але нам потрібно завантажити простий Excel. Тому виконуйте інструкції та скріншоти та натисніть «З інших джерел».
6. Тепер ви знову отримаєте відкрите діалогове вікно. Прокрутіть до кінця, щоб вибрати опцію "Файл Excel", та натисніть "Далі".
7. Тут можна перейменувати з'єднання, використовуючи ім'я за промовчанням «Excel». Натисніть Огляд, щоб вибрати шлях до файлу даних.
8. Крім того, якщо ви хочете, щоб верхній стовпець був рядком заголовка, встановіть прапорець "Використовувати перший рядок як заголовок стовпця" і натисніть "Далі".
9. Наприкінці файл імпортується в модель даних та натисніть «Готово».
10. Отже, усі 28 рядків успішно імпортовано. Тепер вдарте ближче.
11. Ось як це виглядає.
12. Звідси ми створимо зведену таблицю, обравши Головна → Зведена таблиця.
13. Оскільки ми маємо дані на Листі 1, ми розширимо стовпці, клацнувши маленький трикутник поруч із ним.
14. Тепер вкажіть місцезнаходження у рядках та постачальників послуг у значеннях, як ми робили раніше. В результаті вийде проста зведена таблиця із загальною кількістю постачальників послуг.
15. Ось у чому хитрість. Тепер перейдіть у вікно PowerPivot і натисніть «Виміряти» , щоб отримати опцію «Новий вимір» .
16. Тепер додайте опис потрібного імені та почніть вводити формулу у розділі формул.
17. Коли ви почнете друкувати, автоматично отримаєте пропозиції. Тут нам знадобиться окрема функція підрахунку. Виберіть окрему опцію підрахунку.
18. Після цього натисніть кнопку табуляції або поставте дужку (і виберіть стовпець, для якого нам потрібна певна кількість. Як і тут, нам потрібна певна кількість постачальників послуг. Тому наша формула буде виглядати так = DISTINCTCOUNT(Лист1[Постачальник послуг]) )
19. Наприкінці виберіть категорію. Оскільки ми з'ясовуємо унікальний номер постачальників послуг, виберіть категорію «Номери».
20. Змініть формат на «Ціле число» та натисніть «OK». До зведеної таблиці буде додано ще один стовпець, який надасть вам унікальні записи.

Докладніше про зведені таблиці…

За умовчанням при створенні зведеної таблиці на основі діапазону даних, що містять значення, що повторюються, всі записи також підраховуються. Однак, іноді ми просто хочемо підрахувати унікальні значення на основі одного стовпця, щоб отримати результат, як показано на правому знімку екрана. У цій статті я розповім про те, як підрахувати унікальні значення у зведеній таблиці.

Підрахунок унікальних значень у зведеній таблиці з налаштуваннями поля значень

В Excel 2013 і пізніших версіях новий Відмінний граф У зведену таблицю додано функцію, ви можете застосувати цю функцію, щоб швидко і легко вирішити це завдання.

1. Виберіть діапазон даних та натисніть Вставити > PivotTable, В Створити зведену таблицю у діалоговому вікні виберіть новий аркуш або існуючий аркуш, на якому ви хочете розмістити таблицю, і встановіть прапорець Додайте ці дані до моделі даних прапорець, див. знімок екрана:

2. Тоді в Поля зведеної таблиці панелі, перетягніть Клас поле до Рядок поле та перетягніть ПІБ поле до Наші цінності box, див. знімок екрана:

3. А потім натисніть Граф імені список, що розкривається, виберіть Налаштування поля значень, див. знімок екрана:

4. У Налаштування поля значень діалогове вікно, натисніть Узагальнити цінності по вкладка, а потім прокрутіть, щоб клацнути Відмінний граф варіант, див.знімок екрана:

5, Потім натисніть OK, Ви отримаєте зведену таблицю, в якій враховуватимуться лише унікальні значення.

• Увага: Якщо ви перевірите Додайте ці дані до моделі даних варіант у Створити зведену таблицю діалогове вікно Розрахункове поле функцію буде вимкнено.

Підрахунок унікальних значень у зведеній таблиці за допомогою допоміжного стовпця (для версій Excel старше 2013 року)

У старіших версіях Excel вам необхідно створити допоміжний стовпець для визначення унікальних значень, виконайте такі дії:

1. У новому стовпці, окрім даних, введіть цю формулу =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) у комірку C2, а потім перетягніть маркер заповнення по діапазону осередків, щоб застосувати цю формулу. Унікальні значення будуть ідентифіковані як показано на знімку екрана нижче.

2. Тепер можна створити зведену таблицю. Виберіть діапазон даних, включаючи допоміжний стовпець, а потім клацніть Вставити > PivotTable > PivotTable, див. знімок екрана:

3. Тоді в Створити зведену таблицю У діалоговому вікні виберіть новий робочий лист або існуючий робочий лист, на якому ви хочете розмістити таблицю, див. знімок екрана:

4. Натисніть OK, потім перетягніть Клас поле до заголовки рядків поле та перетягніть Помічник огляд поле до Наші цінності і ви отримаєте зведену таблицю, в якій враховуються лише унікальні значення.

Додаткові відносні статті зведеної таблиці:

• Застосування одного і того ж фільтра до кількох зведених таблиць
• Іноді ви можете створити кілька зведених таблиць на основі того самого джерела даних, і тепер ви фільтруєте одну зведену таблицю і хочете, щоб інші зведені таблиці фільтрувалися таким же чином, це означає, що ви хочете змінити кілька фільтрів зведеної таблиці одночасно в Excel.У цій статті я розповім про використання нової функції Slicer в Excel 2010 та пізніших версіях.

• Групувати дату за місяцями, роками, півріччями або іншими конкретними датами у зведеній таблиці
• Якщо в Excel дані у зведеній таблиці містять дату, і чи намагалися ви згрупувати дані за місяцями, кварталами чи роками? Тепер у цьому посібнику розповідається, як згрупувати дату за місяцями / роками / кварталами у зведеній таблиці в Excel.

• Оновити діапазон зведеної таблиці в Excel
• У Excel, коли ви видаляєте чи додаєте рядки чи стовпці в діапазон даних, відносна зведена таблиця не оновлюється одночасно. Тепер цей посібник розповість вам, як оновити зведену таблицю при зміні рядків або стовпців таблиці даних.

• Приховати порожні рядки у зведеній таблиці в Excel
• Як відомо, зведена таблиця зручна для аналізу даних в Excel, але іноді в рядках з'являється порожній вміст, як показано на скріншоті нижче. Тепер я розповім, як приховати ці порожні рядки у зведеній таблиці Excel.