Підрахунок унікальних значень у Excel — це функція, яка може допомогти вам знайти значення, які трапляються лише один раз у величезному списку Excel. Нам часто потрібно повідомляти про унікальну кількість придбаних клієнтів, кількість продуктів на нашому складі, список регіонів, які охоплює наш бізнес.
У цій статті ми пояснюємо, як підрахувати унікальні значення в стовпці Excel.
Як підрахувати унікальні значення в Excel? 5 простих способів
Перш ніж рухатися вперед, ви повинні знати різницю між унікальними значеннями та різними значеннями.
- Унікальні цінності: Це значення, які зустрічаються лише один раз. Унікальні значення зі списку {1,2,3,1,4,2,7,5,7}: {3,4,5}.
- Відмінні значення: Це різні цінності. Різні значення зі списку {1,2,3,1,4,2,7,5,7} є {1,2,3,4,5,7}.
Як підрахувати унікальні значення в Excel?
Унікальні значення в Excel відображаються в списку елементів, які трапляються лише один раз. Основна мета підрахунку унікальних і відмінних значень — відокремити їх від дублікатів списку в Excel.
Є багато способів знайти кількість унікальних і дублікатів у Excel, деякі з них такі:
Зміст
- Підрахуйте унікальні значення в стовпці за допомогою функцій SUM і COUNTIF
- Як підрахувати унікальні текстові значення в Excel
- Як підрахувати унікальні числові значення в Excel
- Як використовувати функцію UNIQUE для підрахунку унікальних значень у Excel
- Як використовувати функцію COUNTA для підрахунку унікальних значень у Excel
- Як підрахувати різні значення за допомогою розширених фільтрів у Excel
- Як підрахувати різні значення за допомогою зведеної таблиці
- Як підрахувати різні значення за допомогою VBA
Зразок даних
Ми надали зразкові дані з двома полями Замовник і Продукти придбаний. Необхідно створити зведений звіт, щоб показати, скільки клієнтів є в нашій базі даних.
Підрахунок унікальних значень у стовпці Використання функцій SUM і COUNTIF
Використання SUM і COUNTIF формули разом. Це найлегший і найпростіший спосіб знайти різні значення в Excel.
Примітка: Ми також можемо використовувати SUMPRODUCT замість SUM.
Синтаксис:
COUNTIF (діапазон, критерії)
- Діапазон – Список даних
- Критерії – Ряд текстів. Нанесіть на вхідний діапазон
Повертає ціле значення, яке відповідає критеріям.
Крок 1: Тип Кількість клієнтів в камері.
Крок 2: Запишіть наведену нижче формулу в будь-яку клітинку, щоб підрахувати кількість унікальних клієнтів. Ми використовуємо Формула масиву щоб обчислити унікальну кількість, тому переконайтеся, що вам потрібно натиснути Ctrl+Shift+Enter для фігурних дужок, як показано на зображенні 1 нижче:
формула:
=SUM(1/COUNTIF(B2:B22;B2:B22))
Ваша формула в комірці D7 повинна бути покрита {} фігурними дужками, як показано нижче:
Примітка: Результат формули COUNTIF використовується як дільник із 1 як чисельник. Це змінює результат COUNTIF формули, значення, яке з’являється лише один раз у масиві, стає 1, а числа, які мають повторювані значення, стануть дробами, що відповідають кратному.
бази даних
Як підрахувати унікальні текстові значення в Excel
Якщо ваш список Excel містить як текстові, так і числові значення, і ви хочете підрахувати лише унікальні текстові значення, ви можете підрахувати унікальні текстові значення в Excel за допомогою ISTEXT Функція до формули.
Синтаксис:
=SUM(IF(ISTEXT(A2:A10)*COUNTIF(A22:A10;A2:A10)=1;1;0))
ISTEXT повернення функцій ПРАВДА якщо позначене значення є текстовим або інакше ПОМИЛКОВИЙ. Тут у формулі (*) зірочка працює як І оператор у формулі, що означає, що функція ЯКЩО повертає 1, лише якщо значення одночасно є текстовим і унікальним, інакше 0. А функція СУММА додає всі 1, і ви можете отримати кількість текстових значень як результат.
Примітка : Завжди пам’ятайте про натискання Ctrl + Shift + Enter для формули масиву.
Як бачите, формула повертає значення 2, тому що ми маємо лише два текст +унік значення.
Як підрахувати унікальні числові значення в Excel
Якщо ваш список Excel складається як з числових, так і з текстових значень. Але ви хочете, щоб Count of Numeric був унікальним у списку. У такому випадку ви можете скористатися ISNUMBER функція у формулі.
Синтаксис:
=SUM(IF(ISNUMBER(A2:A10)*COUNTIF(A2:A10;A2:A10)=1;1;0))
Підрахунок регістру – чутливі унікальні значення в Excel
Простим способом підрахунку унікальних значень, коли у вас є дані з урахуванням регістру, було б створити допоміжний стовпець із наведеною нижче формулою масиву для визначення дублікатів і унікальних елементів.
формула:
=ЯКЩО(СУМА((–Точний($A:$A,A2)))=1,Унікальний,Дублікати)
а потім використовуйте a COUNTIF функція підрахунку унікальних значень:
=COUNTIF(B2:B10,Унікальний)
На зображенні вище ви бачите, що кількість унікальних значень дорівнює 5.
Як використовувати функцію UNIQUE для підрахунку унікальних значень у Excel
Якщо ви використовуєте MS Office 365 , ви можете використати наведену нижче формулу для обчислення унікальна кількість.
=COUNTA(УНІКАЛЬНИЙ(B2:B30)
Синтаксис:
COUNTA(Значення1, Значення2,…..)
- Значення1 – Комірка, діапазон Excel, масив тексту
- Повернути кількість непорожніх клітинок.
UNIQUE(масив, за_стовпцем, точно_один раз)
- масив – унікальний список значень даних для повернення
- by_col – Логічне значення [ True – повертає унікальні стовпці / False – повертає унікальні рядки]
- Рівно_раз – Логічне значення [повертає лише різні значення]
Як використовувати функцію COUNTA для підрахунку унікальних значень у Excel
COUNTIFS дозволяє підраховувати значення на основі одного або кількох критеріїв. COUNTIFS Функція підраховує різні значення з діапазону.
Використана формула: Сума (1/COUNTIFS(B2:B22;B2:B22))
Примітка: Не забудьте натиснути CTRL+SHIFT+ENTER щоб поставити фігурні дужки, щоб використовувати формулу масиву. Оскільки формула масиву не працює в старіших версіях Excel.
Як підрахувати різні значення за допомогою розширених фільтрів у Excel
Розширені фільтри – це функція в Excel, яка дозволяє вказувати складні критерії за допомогою формул.
Щоб використовувати розширені фільтри в Excel, користувачеві потрібно перейти до ДАНІ виберіть вкладку Розширений фільтр і визначте діапазон критеріїв діапазону та вихідний діапазон.
Це також може допомогти вам знайти різні значення в Excel.
Проміжний підсумок це функція, яку можна використовувати для підрахунку лише видимих елементів у вашому списку. Як показано на зображенні нижче, тут 103 аргумент використовується, щоб сказати ПРОМІЖНИЙ ПІДСУМОК функція підрахунку всіх видимих елементів у певному діапазоні.
Кроки для підрахунку різних значень за допомогою розширеного фільтра
Крок 1: Виберіть стовпець.
Виберіть стовпець, у якому потрібно підрахувати окремі значення з таблиці.
Крок 2: Виберіть Попередній фільтр .
Тепер перейдіть до Дані на стрічці та виберіть Розширений фільтр з цього.
крок 3: Поставте галочку Унікальний тільки записи.
Крок 4 : Натисніть в порядку
крок 5: Тепер ви можете побачити кількість різних записів тільки.
Як підрахувати різні значення за допомогою зведеної таблиці
Виконайте кроки, щоб підрахувати різні значення за допомогою зведеної таблиці:
Крок 1: Створіть зведену таблицю даних
Вам спочатку потрібно створити зведену таблицю даних, у яких потрібно підрахувати різні значення.
Крок 2: Додайте свою річ як лайк Ім'я клієнта в рядки шляхом перетягування
крок 3: Тепер, коли ви бачите мітки рядків із значеннями Distinct у стовпці, виберіть діапазон даних, для якого потрібно визначити кількість різних значень.
Крок 4: попередній перегляд Кількість різних значень
Як рахувати Різні значення за допомогою VBA
В Excel немає вбудованої функції для підрахунку окремих значень із діапазону. Але за допомогою VBA ми можемо змусити нашу функцію підраховувати окремі значення в Excel.
Виконайте кроки, щоб підрахувати різні значення за допомогою VBA:
Крок 1: Відкрийте VBA
Виберіть Вкладка розробника на стрічці, а потім виберіть Visual Basic , Тепер натисніть на Вставка і переходимо до модуля.
Крок 2: Тепер напишіть наведений нижче код на екрані VBA
крок 3: Ця функція допоможе вам створити нову функцію в Excel ПОРАХУВАТИ РІЗНІ ЗНАЧЕННЯ яку можна використовувати так само, як і інші функції в Excel.
Ви можете використати наведений вище код VBA, щоб створити нову функцію для підрахунку окремих значень у Excel.
Читайте також : Функція Countif в Excel
Як підрахувати унікальні значення в Excel – поширені запитання
Чи існує формула для підрахунку унікальних значень у Excel?
Так, ви можете використовувати формулу для підрахунку унікальних значень у Excel. Одним із поширених способів зробити це є використання комбінації функцій COUNTIF і SUM разом із формулою масиву. Ось як ви можете це зробити:
Що таке унікальні значення в Excel?
Унікальні значення в Excel відображаються в списку елементів, які трапляються лише один раз. Основна мета підрахунку унікальних та окремих значень — відокремити їх від дублікатів списку в Excel.
Як ми можемо підрахувати різні значення в кількох стовпцях одночасно в Excel?
Ми можемо підрахувати різні значення в кількох стовпцях за допомогою SUM і Функції COUNTIFS разом.
- Крок 1: Виберіть будь-яку випадкову клітинку, де ви хочете відобразити результат.
- Крок 2: Використовуйте формулу =SUM(1/COUNTIFS(діапазон1;діапазон1;діапазон 2;діапазон2…) де діапазони – це комірки, у яких потрібно підрахувати окремі значення.
- крок 3: Прес CTRL+SHIFT+ENTER через формулу масиву.
Що таке різні значення в Excel?
Різні значення в Excel – це унікальні значення, які з’являються лише один раз у діапазоні клітинок. Наприклад, окремими значеннями з діапазону {A, B, B, C, C, D} є {A, B, C, D}.
Поясніть формулу =SUM(1/COUNTIFS(B5:B14;B5:B14).
The COUNTIFS функція використовується, щоб побачити, скільки разів значення з’являється у списку. Це дасть вам дробове значення, яке додасть до 1 для кожного окремого значення в списку після інвертування цього.
І SUM потім функція додає всі дроби, і підсумок є кількістю різних значень у списку.
Чи є якась вбудована функція для підрахунку окремих значень у Excel без використання будь-якої формули?
В Excel немає вбудованої функції для підрахунку різних значень без використання формул. Альтернативним способом є використання Видалити дублікати Ця функція допоможе вам видалити дублікати та дасть вам кількість унікальних значень.