✅ Як створити гістограму в Excel (Покроковий посібник)

Зміст

Створення гістограми в Excel 2016
Створення гістограми за допомогою пакета інструментів аналізу даних
Створення гістограми за допомогою функції ЧАСТОТИ

Перегляд відео - 3 способи створення діаграми гістограми в Excel

Гістограма - поширений інструмент аналізу даних у світі бізнесу. Це стовпчаста діаграма, яка показує частоту появи змінної у зазначеному діапазоні.

Відповідно до Investopedia, гістограма-це графічне зображення, подібне до стовпчастої діаграми за структурою, яке впорядковує групу точок даних у задані користувачем діапазони. Гістограма конденсує ряд даних у візуалізацію, що легко інтерпретується, беручи безліч точок даних і групуючи їх у логічні діапазони або біни.

Простим прикладом гістограми є розподіл оцінок, набраних у предметі. Ви можете легко створити гістограму і побачити, скільки учнів набрало менше 35, скільки було між 35-50, скільки між 50-60 і так далі.

Існує кілька способів створення гістограми в Excel:

Якщо ви використовуєте Excel 2016, є вбудована опція гістограми, яку можна використовувати.
Якщо ви використовуєте Excel 2013, 2010 або попередні версії (і навіть у Excel 2016), ви можете створити гістограму за допомогою пакета інструментів аналізу даних або за допомогою функції ЧАСТОТИ (про що йдеться далі в цьому підручнику)

Давайте подивимося, як створити гістограму в Excel.

Створення гістограми в Excel 2016

Excel 2016 отримав нове доповнення у розділі діаграм, де гістограму було додано як вбудовану діаграму.

Якщо ви використовуєте Excel 2013 або попередні версії, перегляньте наступні два розділи (про створення гістограм за допомогою пакета аналізу даних або формули частоти).

Припустимо, у вас є набір даних, як показано нижче. Він має оцінки (зі 100) 40 студентів з предмета.

Ось кроки для створення діаграми гістограми в Excel 2016:

Виберіть весь набір даних.
Перейдіть на вкладку Вставка.
У групі Діаграми натисніть на опцію "Вставити статичну діаграму".
У групі HIstogram натисніть піктограму діаграми гістограми.

Вищевказані кроки вставляють діаграму гістограми на основі вашого набору даних (як показано нижче).

Тепер ви можете налаштувати цю діаграму, клацнувши правою кнопкою миші по вертикальній осі та вибравши Формат осі.

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

Ось деякі речі, які ви можете зробити, щоб налаштувати цю діаграму гістограми:

За категоріями: Ця опція використовується, якщо у вас є текстові категорії. Це може бути корисним, якщо у вас повторюються категорії, і ви хочете дізнатися суму або кількість категорій. Наприклад, якщо у вас є дані про продажі таких товарів, як принтер, ноутбук, миша та сканер, і ви хочете дізнатися загальний обсяг продажів кожного з цих товарів, ви можете скористатися опцією "За категоріями". У нашому прикладі це не корисно, оскільки всі наші категорії різні (учень 1, учень 2, учень 3 тощо).
Автоматичний: Ця опція автоматично вирішує, які бункери створювати в гістограмі. Наприклад, у нашому графіку було вирішено, що має бути чотири контейнери. Ви можете змінити це, скориставшись опціями "Ширина контейнера/кількість контейнерів" (описано нижче).
Ширина відсіку: Тут ви можете визначити, яким має бути розмір контейнера. Якщо я введу тут 20, він створить бункери, такі як 36-56, 56-76, 76-96, 96-116.
Кількість контейнерів: Тут ви можете вказати, скільки контейнерів ви хочете. Він автоматично створить діаграму з такою кількістю бінів. Наприклад, якщо я вкажу 7 тут, він створить діаграму, як показано нижче. У певний момент можна вказати ширину контейнера або кількість ящиків (не обидва).
Переповнювач: Використовуйте цю кошик, якщо ви хочете, щоб усі значення вище певного значення об'єднувалися в діаграму гістограми. Наприклад, якщо я хочу знати кількість студентів, які набрали більше 75 балів, я можу ввести 75 як значення переповнювача. Це покаже мені щось, як показано нижче.
Контейнер з нижчим потоком: Подібно до переповнення, якщо я хочу знати кількість студентів, які набрали менше 40 балів, я можу ввести 4o як значення та показати діаграму, як показано нижче.

Після того як ви вказали всі параметри та отримали потрібну діаграму гістограми, ви можете додатково її налаштувати (зміна заголовка, видалення ліній сітки, зміна кольорів тощо).

Створення гістограми за допомогою пакета інструментів аналізу даних

Метод, описаний у цьому розділі, також працюватиме для всіх версій Excel (включаючи 2016). Однак, якщо ви використовуєте Excel 2016, я рекомендую вам використовувати вбудовану діаграму гістограм (як показано нижче)

Щоб створити гістограму за допомогою набору інструментів аналізу даних, спочатку потрібно встановити надбудову Analysis Toolpak.

Ця надбудова дозволяє швидко створити гістограму, взявши дані та діапазон даних (біни) як вхідні дані.

Встановлення пакета інструментів аналізу даних

Щоб встановити надбудову Data Analysis Toolpak:

Перейдіть на вкладку Файл і виберіть «Параметри».

У діалоговому вікні Параметри Excel виберіть Надбудови на панелі навігації.

У спадному меню Керування виберіть Надбудови Excel і натисніть Перейти.

У діалоговому вікні Надбудови виберіть Панель інструментів аналізу та натисніть OK.

Це встановить пакет інструментів аналізу, і ви зможете отримати до нього доступ на вкладці Дані в групі Аналіз.

Створення гістограми за допомогою пакета інструментів аналізу даних

Увімкнувши пакет інструментів аналізу, ви можете використовувати його для створення гістограми в Excel.

Припустимо, у вас є набір даних, як показано нижче. Він має оцінки (зі 100) 40 студентів з предмета.

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

З наведеним вище набором даних біни будуть інтервалами відміток.

Вам потрібно вказати ці контейнери окремо у додатковому стовпці, як показано нижче:

Тепер, коли у нас є всі дані, давайте подивимося, як створити гістограму за допомогою цих даних:

Перейдіть на вкладку Дані.
У групі Аналіз натисніть Аналіз даних.

У діалоговому вікні "Аналіз даних" виберіть Гістограма зі списку.

Натисніть OK.
У діалоговому вікні гістограма:
- Виберіть діапазон введення (усі позначки в нашому прикладі)
- Виберіть діапазон контейнерів (клітинки D2: D7)
- Залиште прапорець Мітки не позначеним (вам потрібно поставити цей прапорець, якщо ви включили мітки до вибору даних).
- Вкажіть діапазон виводу, якщо ви хочете отримати гістограму на тому ж робочому аркуші. В іншому випадку виберіть Новий аркуш/робочу книгу, щоб отримати його в окремому аркуші/робочій книзі.
- Виберіть Вихід діаграми.

Натисніть OK.

Це додасть таблицю розподілу частот та діаграму у вказане місце.

Тепер є кілька речей, які вам потрібно знати про гістограму, створену за допомогою пакета інструментів аналізу:

Перший контейнер містить усі значення під ним. У цьому випадку 35 показує 3 значення, що вказують на те, що є три учні, які набрали менше 35 балів.
Остання вказана кошик - 90, однак Excel автоматично додає іншу кошик - Більше. Ця кошик включатиме будь -яку точку даних, яка лежить після останньої зазначеної кошика. У цьому прикладі це означає, що є 2 учні, які набрали більше 90 балів.
- Зауважте, що навіть якщо я додаю останній контейнер як 100, цей додатковий контейнер все одно буде створено.
Це створює статичну діаграму гістограми. Оскільки Excel створює та вставляє розподіл частот як значення, діаграма не оновлюватиметься, якщо ви зміните основні дані. Щоб оновити його, вам доведеться знову створити гістограму.
- Діаграма за замовчуванням не завжди в найкращому форматі. Ви можете змінити форматування, як і будь -яку іншу звичайну діаграму.
Після створення ви не можете використовувати Control + Z, щоб повернути його. Вам доведеться вручну видалити таблицю та діаграму.

Якщо ви створюєте гістограму без визначення бінів (тобто залишаєте діапазон кошиків порожнім), гістограма все одно створюватиметься. Він автоматично створить шість однаково розташованих ящиків і використовує ці дані для створення гістограми.

Створення гістограми за допомогою функції ЧАСТОТИ

Якщо ви хочете створити гістограму, яка є динамічною (тобто оновлюється при зміні даних), вам потрібно вдатися до формул.

У цьому розділі ви дізнаєтесь, як використовувати функцію ЧАСТОТИ для створення динамічної гістограми в Excel.

Знову ж таки, беручи дані оцінок учня, вам потрібно створити інтервали даних (біни), в яких ви хочете показувати частоту.

Ось функція, яка буде обчислювати частоту для кожного інтервалу:

= ЧАСТОТА (B2: B41, D2: D8)

Оскільки це формула масиву, вам потрібно використовувати Control + Shift + Enter, а не просто Enter.

Ось кроки, щоб переконатися, що ви отримали правильний результат:

Виділіть усі комірки, прилеглі до контейнерів. У цьому випадку це E2: E8.
Натисніть F2, щоб увійти в режим редагування комірки E2.
Введіть формулу частоти: = ЧАСТОТА (B2: B41, D2: D8)
Натисніть Control + Shift + Enter.

В результаті, який ви отримаєте, тепер ви можете створити гістограму (яка є нічим іншим, як простою стовпчастою діаграмою).

Ось деякі важливі речі, які вам потрібно знати під час використання функції ЧАСТОТИ:

В результаті виходить масив, і ви не можете видалити частину масиву. Якщо потрібно, видаліть усі комірки, які мають функцію частоти.
Коли бін 35, функція частоти повертає результат, що включає 35. Отже, 35 означає оцінку до 35, а 50 означатиме оцінку більше 35 і до 50.

Крім того, припустимо, що ви хочете, щоб зазначені інтервали даних були до 80, і ви хочете згрупувати всі результати вище 80 разом, ви можете зробити це за допомогою функції ЧАСТОТИ. У цьому випадку виберіть ще одну клітинку, ніж кількість контейнерів. Наприклад, якщо у вас є 5 контейнерів, виберіть 6 комірок, як показано нижче: