зміст
Необхідність пошуку найвищих і найнижчих цінностей у будь-якому бізнесі очевидна: найприбутковіші продукти або цінні клієнти, найбільші партії або партії тощо.
Але разом з цим іноді доводиться шукати в даних не верхні, а найбільш часто зустрічаються значення, що хоч і звучить схоже, але насправді зовсім не те саме. Стосовно магазину, наприклад, це може бути пошук товарів, які найчастіше купуються, а не найвигідніших, або найчастіше зустрічається кількість товарів у замовленні, хвилини розмови тощо.
У такій ситуації проблему доведеться вирішувати трохи інакше, залежно від того, з чим ми маємо справу – з числами чи текстом.
Знаходження найпоширеніших чисел
Припустимо, що перед нами стоїть завдання проаналізувати наявні дані про продажі в магазині, щоб визначити кількість товарів, що купуються найчастіше. Щоб визначити найбільш часте число в діапазоні, можна скористатися функцією FASHION (РЕЖИМ):
Тобто, за нашою статистикою, найчастіше покупці купують 3 шт. товарів.
Якщо є не одне, а відразу кілька значень, які зустрічаються однакову максимальну кількість разів (кілька режимів), то для їх ідентифікації можна використовувати функцію FASHION.NSK (MODE.MULT). Його потрібно ввести у вигляді формули масиву, тобто виділити відразу кілька порожніх комірок, щоб вистачило на всі режими з запасом і ввести в рядок формул =MODA.NSK(B2:B16) і натиснути комбінацію клавіш. Ctrl + Shift + Enter.
На виході ми отримаємо список всіх модів з наших даних:
Тобто, судячи з наших даних, часто беруть не лише 3, а й 16 штук. товарів. Зверніть увагу, що в наших даних є лише два режими (3 і 16), тому решта комірок, виділених «у резерві», матиме помилку #N/A.
Аналіз частот за діапазонами за допомогою функції FREQUENCY
Якщо необхідно аналізувати не цілі, а дробові числа, то правильніше буде оцінювати не кількість однакових значень, а їх потрапляння в задані діапазони. Наприклад, нам потрібно розуміти, яку вагу найчастіше купують товари, щоб правильно підібрати для магазину візки і пакувальні пакети потрібного розміру. Іншими словами, нам потрібно визначити, скільки чисел потрапляє в інтервал 1..5 кг, скільки в інтервал 5..10 кг і т.д.
Для вирішення подібної задачі можна скористатися функцією ЧАСТОТИ (ЧАСТОТА). Для цього потрібно заздалегідь підготувати комірки з інтервалами (кишенями), які нас цікавлять, а потім вибрати порожній діапазон комірок (G2: G5) на одну клітинку більше діапазону кишень (F2: F4) і ввести його як формулу масиву, натиснувши комбінацію в кінці Ctrl + Shift + Enter:
Частотний аналіз зі зведеною таблицею з групуванням
Альтернативне вирішення проблеми: створіть зведену таблицю, де в область рядків помістіть вагу покупок, а в область значень – кількість клієнтів, а потім застосуйте групування – клацніть правою кнопкою миші на значеннях ваги та виберіть команду Group (група). У вікні, що з’явиться, можна встановити межі та крок групування:
… і після натискання кнопки OK отримати таблицю з підрахунком кількості звернень покупців у кожному діапазоні групування:
мінуси сюди:
- крок групування може бути лише постійним, на відміну від функції ЧАСТОТИ, де кишені можна вказати абсолютно будь-які
- зведену таблицю потрібно оновлювати при зміні вихідних даних (клацнувши правою кнопкою миші – Оновити), а функція перераховується автоматично на льоту
Пошук тексту, який найчастіше зустрічається
Якщо ми маємо справу не з цифрами, а з текстом, то підхід до вирішення буде принципово іншим. Припустимо, у нас є таблиця зі 100 рядками товарів, що продаються в магазині, і ми хочемо визначити, які товари купували найчастіше?
Найпростішим і найочевиднішим рішенням було б додати стовпець поруч із функцією COUNTIF (COUNTIF)щоб підрахувати кількість входжень кожного елемента в стовпці A:
Потім, звичайно, відсортуйте отриманий стовпець за спаданням і подивіться на перші рядки.
Або додайте стовпець з одиницями до вихідного списку та побудуйте зведену таблицю на основі отриманої таблиці, підраховуючи загальну кількість одиниць для кожного продукту:
Якщо вихідних даних небагато, і ви в принципі не хочете використовувати зведені таблиці, то можете скористатися формулою масиву:
Давайте розберемо це по частинах:
- COUNTIF(A2:A20;A2:A20) — це формула масиву, яка по черзі шукає кількість входжень кожного продукту в діапазоні A2:A100 і створює масив із кількістю повторень на виході, тобто фактично замінює додаткову колонку
- MAX – знаходить найбільше число в масиві входжень, тобто найбільш купований товар
- MATCH – обчислює порядковий номер рядка в таблиці, де MAX знайшов найбільше число
- INDEX – повертає з таблиці вміст комірки з числом, знайденим за допомогою MATCH
- Підрахунок кількості унікальних значень у списку
- Вилучення унікальних елементів зі списку дублікатів
- Групування у зведених таблицях