Резюме
Один із способів створити гістограму - це функція ЧАСТОТА. У наведеному прикладі формула в клітинках G5: G8 є:
(=FREQUENCY(data,bins))
де дані (C5: C16) та контейнери (F5: F8) називаються діапазонами.
Ця формула вводиться як формула багатоклітинного масиву в діапазоні G5: G8.
Пояснення
Примітка: пізніші версії Excel містять власну діаграму гістограм, яку легко створити, але не настільки гнучку для форматування. Приклад на цій сторінці показує один із способів створити власні дані гістограми за допомогою функції FREQUENCY та використовувати графік звичайних стовпців для побудови результатів. Оскільки FREQUENCY - це формула, результати та діаграма динамічно оновлюватимуться у разі зміни даних.
Функція FREQUENCY повертає розподіл частоти, що є підсумковою таблицею, яка показує кількість кожного значення в діапазоні "bin". Частоту трохи складно використовувати, оскільки її потрібно вводити як формулу масиву. З іншого боку, як тільки ви правильно встановите свої смітники, FREQUENCY дасть вам всі рахунки одночасно!
Налаштування та формула
У наведеному прикладі ми маємо список з 12 балів у названому діапазоні "дані" (C5: C16). Діапазон F5: F8 - це названий діапазон "бункерів". FREQUENCY буде розглядати кожне значення бункера як верхню межу для цього кошика. Іншими словами, кожен смітник включатиме кількість балів до значення сміття включно. FREQUENCY також поверне "кількість переповнення" - кількість значень, більшу за останню бункер.
Щоб ввести формулу ЧАСТОТА, виконайте ці дії у доданій книзі.
1. Видаліть існуючі формули, якщо це необхідно (див. Примітку нижче).
2. Виберіть діапазон G5: G8 (усі чотири комірки).
3. Вставте або введіть цю формулу в рядок формул:
=FREQUENCY(data,bins)
4. Введіть формулу як формулу масиву з елементом керування + shift + enter. Excel автоматично додасть фігурні дужки ().
Примітка. Щоб відредагувати існуючі формули, потрібно спочатку виділити всі чотири клітинки, а потім натиснути Видалити. Ви можете виділити всі клітинки одночасно за допомогою комбінації клавіш + /.
FREQUENCY генерує підрахунок для кожного смітника і повертає масив результатів, подібних до цього:
(2;4;5;1;0) // array returned by FREQUENCY
Excel помістить перші 4 значення в діапазон G5: G8. Зверніть увагу, що кількість переповнення, нуль, також повертається як п’ятий елемент у масиві, оскільки немає значень, більших за 100. Це значення не відображається на аркуші, оскільки ми ввели формулу лише у чотирьох клітинках.
Excel 365
В Excel 365 ви можете просто ввести одну формулу в комірку G5, і результати автоматично перекинуться на аркуш. Немає необхідності використовувати управління + shift + enter. Однак, якщо ви скористаєтесь цим підходом, ви побачите і кількість вихідних даних біна. Введення формули лише в 4 клітинки (як зазначено вище) пригнічує це останнє значення. Якщо ви не заперечуєте над додатковим значенням, варіант однієї формули простіший, і ви можете вирішити не наносити значення на діаграму.
Етикетки
Етикетки в E5: E8 призначені лише для читання та презентації. Їх можна налаштувати як завгодно. У наведеному прикладі формула у клітинці E5, скопійована вниз, має вигляд:
=IF(F5=MIN(bins),"≤"&F5,F4+1&"-"&F5)
Ця формула просто створює мітку для кожного контейнера, використовуючи значення контейнера у стовпці F.
Діаграма гістограми
Щоб скласти графік результату з ЧАСТОТИ, виконайте наступні дії. Спочатку утримуйте клавішу управління та виберіть два діапазони: E4: E8 та G4: G8. Потім вставте стовпчасту діаграму (Вставка> Діаграми> Кластерний стовпець):
Потім клацніть панель правою кнопкою миші та відформатуйте ряд даних, щоб зменшити ширину зазору до 5% (або за бажанням):
Змінюйте заголовок діаграми як завгодно. У наведеному прикладі ми підбираємо значення в комірці B2:
Підсумкова діаграма з наведеними значеннями:
Гістограма з інструментом аналізу даних
Інший спосіб створити гістограму в Excel - це використовувати надбудову Data Analysis ToolPak. Це дуже простий метод, і він працює в старих версіях Excel. Однак одне обмеження полягає в тому, що вихід є статичним і не оновлюється автоматично, якщо значення в даних змінюються.
