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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Існує третій метод, який Роджер показує в цій статті (який він називає методом зведення зведеної таблиці).

Давайте розпочнемо!

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

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

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

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

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

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

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

= IF (COUNTIFS ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)

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

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

Це гарантує, що функція COUNTIFS підраховує перший екземпляр імені як 1, другий екземпляр імені як 2 тощо.

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

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

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

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

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

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

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

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

Тепер ви можете змінити заголовок стовпця з "Сума кількості D" на "Представник продажів".

Недоліки використання стовпця -помічника:

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

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

Додайте дані до моделі даних та узагальнюйте за допомогою окремого підрахунку

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

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

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

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

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

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

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

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

Щоб отримати чіткий підрахунок у зведеній таблиці, виконайте наведені нижче дії.

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

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

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

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

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

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

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

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

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

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

У цьому випадку формула допомагає:

= IF (IF (COUNTIFS ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2)/COUNTIF ($ C $ 2: $ C $ 1001, C2) 1,0,1), 0)

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

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

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

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

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

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

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

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