Припустимо, що потрібно з’ясувати, скільки унікальних значень існує в діапазоні, який містить повторювані значення. Наприклад, якщо стовпець містить:
-
значення 5, 6, 7 і 6, то результатом будуть три унікальні значення — 5, 6 і 7.
-
Для значень "Бондаренко", "Діденко", "Діденко", "Діденко" результатом будуть два унікальні значення — "Бондаренко" та "Діденко".
Передбачено кілька способів обчислення кількості унікальних значень серед повторюваних.
За допомогою діалогового вікна Розширений фільтр можна видобути унікальні значення зі стовпця даних і вставити їх у нове місце. Після цього можна обчислити кількість елементів у новому діапазоні за допомогою функції ROWS.
-
Виберіть діапазон клітинок або переконайтеся, що активна клітинка входить у таблицю.
Переконайтеся, що діапазон клітинок містить заголовок стовпця.
-
На вкладці Дані в групі Сортування & Фільтр натисніть кнопку Додатково.
Відображається діалогове вікно Розширений фільтр.
-
Виберіть Копіювати до іншого розташування.
-
У полі Діапазон для результату введіть посилання на клітинку.
Також можна вибрати команду Згорнути діалогове вікно
-
Установіть прапорець Лише унікальні записи та натисніть кнопку OK.
Унікальні значення з вибраного діапазону буде скопійовано до нового розташування, починаючи із клітинки, указаної в полі Діапазон для результату.
-
У пустій клітинці під останньою клітинкою діапазону введіть функцію ROWS. Використовуйте діапазон унікальних значень, щойно скопійованих як аргумент, за винятком заголовка стовпця. Наприклад, якщо діапазон унікальних значень — B2:B45, введіть =ROWS(B2:B45).
Використовуйте поєднання функцій IF, SUM, FREQUENCY, MATCH і LEN для виконання цього завдання.
-
використайте функцію IF, щоб призначити значення 1 для кожної виконаної умови;
-
За допомогою функції SUM додайте підсумок.
-
Обчислення кількості унікальних значень за допомогою функції FREQUENCY. Функція FREQUENCY ігнорує текст і нульові значення. Для першого екземпляра певного значення ця функція повертає число, яке дорівнює кількості екземплярів цього значення. Для кожного екземпляра цього ж значення після першого ця функція повертає нуль.
-
За допомогою функції MATCH визначте позицію текстового значення в діапазоні. Повернуте функцією значення надалі використовується як аргумент для функції FREQUENCY для обчислення відповідних текстових значень.
-
За допомогою функції LEN знайдіть пусті клітинки. Довжина пустих клітинок дорівнює 0.
Примітки.:
-
Формули в цьому прикладі необхідно вводити як формули масивів. Якщо ви маєте поточну версію Microsoft 365, ви можете ввести формулу у верхню ліву клітинку діапазону вихідних даних, а потім натиснути клавішу Enter, щоб підтвердити введення формули динамічного масиву. В іншому разі формулу знадобиться ввести по-старому, тобто спочатку вибрати діапазон вихідних даних, ввести формулу в його верхню ліву клітинку, а потім натиснути клавіші Ctrl+Shift+Enter, щоб підтвердити введення. Excel автоматично вставляє фігурні дужки на початку та в кінці формул. Докладні відомості про формули масивів див. у статті Приклади формул масивів і рекомендації.
-
Щоб переглянути функцію, обчислену покрокові вказівки, виділіть клітинку з формулою, а потім на вкладці Формули в групі Аудит формули натисніть кнопку Обчислити формулу.
-
Функція FREQUENCY обчислює частоту розташування значень у певному інтервалі та повертає вертикальний масив значень. Наприклад, за допомогою функції FREQUENCY можна обчислити кількість тестових оцінок, які перебувають у певних діапазонах. Оскільки ця функція повертає масив, її потрібно вводити як формулу масиву.
-
Функція 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 чи отримати підтримку в спільнотах.
Додаткові відомості
Фільтрування за унікальними значеннями або вилучення повторюваних значень
