Обчислення кількості унікальних значень серед повторюваних

Обчислення кількості унікальних значень серед повторюваних

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

  • Значення 5, 6, 7 і 6, результат складається з трьох унікальних значень – 5, 6 і 7.

  • Значення "Бредлі", "Дойл", "Дойл", "Дойл", результат – два унікальні значення – "Бредлі" і "Дойл".

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

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

  1. Виділіть діапазон клітинок або переконайтеся, що активна клітинка міститься в таблиці.

    Переконайтеся, що діапазон клітинок має заголовок стовпця.

  2. На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.

    Відкриється діалогове вікно Розширений фільтр .

  3. Виберіть команду Копіювати до іншого розташування.

  4. У полі Копіювати до введіть посилання на клітинку.

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

  5. Установіть прапорець лише унікальні записи та натисніть кнопку OK.

    Унікальні значення з виділеного діапазону копіюються до нового розташування, яке починається з вибраної в полі копія .

  6. У пустій області нижче останньої клітинки в діапазоні вкажіть функцію " рядки ". Використовуйте діапазон унікальних значень, які ви щойно скопіювали як аргумент, виключаючи заголовок стовпця. Наприклад, якщо діапазон унікальних значень має значення B2: B45, ви ввели = рядки (B2: B45).

Щоб виконати це завдання, скористайтеся комбінацією функцій IF, SUM, частота, Matchі LEN .

  • використайте функцію IF, щоб призначити значення 1 для кожної виконаної умови;

  • Додайте підсумок за допомогою функції SUM .

  • Підраховуйте кількість унікальних значень за допомогою функції " частота ". Функція частота ігнорує текст і нульові значення. Для першого входження певного значення ця функція повертає число, що дорівнює числу екземплярів цього значення. Для кожного входження такого самого значення після першого, ця функція повертає нуль.

  • Повернути розташування текстового значення в діапазоні за допомогою функції Match . Після цього значення буде використано як аргумент для функції частота , щоб відповідні текстові значення можна було обчислити.

  • Для пошуку пустих клітинок за допомогою функції LEN . Пусті клітинки мають довжину 0.

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

Примітки.: 

  • Формули в цьому прикладі необхідно вводити як формули масивів. Якщо у вас є поточна версія Microsoft 365, ви можете просто ввійти у верхню ліву частину діапазону виведення, а потім натисніть клавішу вводу , щоб підтвердити формулу як динамічну формулу масиву. В іншому разі формулу знадобиться ввести по-старому, тобто спочатку вибрати діапазон вихідних даних, ввести формулу в його верхню ліву клітинку, а потім натиснути клавіші Ctrl+Shift+Enter, щоб підтвердити введення. Excel автоматично вставляє фігурні дужки на початку та в кінці формул. Докладні відомості про формули масивів див. у статті Приклади формул масивів і рекомендації.

  • Щоб переглянути Поетапне виконання функцій за кроком, виділіть клітинку, яка міститиме формулу, а потім на вкладці формули в групі аудит формули натисніть кнопку обчислити формулу.

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

  • Функція Match виконує пошук вказаного елемента в діапазоні клітинок, а потім повертає відносне розташування цього елемента в діапазоні. Наприклад, якщо діапазон a1: a3 містить значення 5, 25 і 38, формула = Match (25; a1: a3; 0) Повертає число 2, тому що 25 – другий елемент в діапазоні.

  • Функція LEN Повертає кількість символів у текстовому рядку.

  • Функція SUM додає всі числа, указані як аргументи. Для кожного аргументу може бути діапазон, посилання на клітинку, масив, константу, формулу або результат від іншої функції. Наприклад, SUM (a1: A5) додає всі числа, які містяться у клітинках a1 – a5.

  • Функція IF повертає одне значення, якщо умова, яку потрібно вказати значення TRUE, і інше значення, якщо цей стан ОБЧИСЛЮЄТЬСЯ на false.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Додаткові відомості

Фільтрування за унікальними значеннями або вилучення повторюваних значень

Примітка.:  Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була ця інформація корисною. Для довідки цю статтю можна переглянути англійською мовою.

Потрібна додаткова довідка?

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо, що знайшли час і надіслали нам відгук! Можливо, у нас не буде часу відповісти на кожен коментар, але докладемо максимум зусиль, щоб переглянути їх усі. Вас цікавить, як ми використовуємо ваші відгуки?

×