Групування за зведеними таблицями в Excel
Іншим підходом до побудови розподілу частоти є використання зведеної таблиці. Інструмент пов'язана, зведена таблиця, базується на аналізі, проведеному зведеною таблицею. Я віддаю перевагу цьому
метод, використовуючи формулу масиву, яка використовує ЧАСТОТА (). За допомогою зведеної таблиці, коли початкове заснування зроблено, я можу використовувати ту саму зведену таблицю для аналізу, який виходить за рамки розподілу базової частоти. Але якщо все, що я хочу, - це швидкий груповий рахунок, ЧАСТОТА () зазвичай це найшвидший спосіб.
Знову ж таки, у розділах 2 далі є детальніше про зведені таблиці, але в цьому розділі показано, як їх використовувати для встановлення розподілу частоти.
Побудова зведеної таблиці вимагає вказувати класи, як і при використанні F ЗАПИТ () зроблено, але це відбувається трохи далі.
Примітка
Нагадування: при використанні методу ЧАСТОТА () як описано в попередньому розділі, заголовок у верхній частині стовпця необроблених даних може бути корисним, але не є обов’язковим. При використанні методу зведеної таблиці, описаному в цьому розділі, потрібен заголовок.
Почніть із зразків даних у форматі A1: A101 на малюнку 1.16, як і раніше. Виділіть одну з комірок цього діапазону, а потім виконайте такі дії:
1. Клацніть на вкладку Вставка. Натисніть кнопку Зведена діаграма в групі Графіки. (До Excel 2013 клацніть випадаючий список зведеної таблиці в групі «Таблиці» та виберіть у розкривному списку «Зведену діаграму».) Вибравши зведену діаграму, ви автоматично отримуєте зведену таблицю. З'явиться діалогове вікно на малюнку 1.19.
2. Натисніть кнопку Параметр наявного робочого аркуша. Клацніть у полі редагування діапазону позицій. Потім, щоб уникнути перезапису важливих даних, клацніть на порожній комірці на аркуші, яка має інші порожні клітинки праворуч та нижче.
3. Клацніть на OK. Тепер аркуш з’являється, як показано на малюнку 1.20.
Малюнок 1.19. Якщо ви починаєте з вибору однієї комірки з діапазону, що містить ваші вхідні дані, Excel автоматично пропонує діапазон сусідніх комірок, що містять дані.
4. Клацніть поле Вага у списку Поля зведеної таблиці та перетягніть його вВісь (категорії) районований.
5. Клацніть ще раз на поле Вага і перетягніть його в Цінності. Незважаючи на велику грецьку сигму, яка є символом підсумовування, значення в зведеній таблиці можуть відображати значення, відліки, стандартні відхилення та різноманітну статистику, крім суми. Однак, Сума є статистикою за замовчуванням для поля, яке містить лише числові значення.
6. І зведена таблиця, і зведена діаграма заповнюються, як показано на малюнку 1.21. Клацніть правою кнопкою миші будь-яку комірку, що містить мітку рядка, наприклад C2. Виберіть у контекстному меню групу. З'явиться діалогове вікно Групування, показано на рисунку 1.22.
Малюнок 1.21. Поле Weight містить лише числові значення, тому зведеною таблицею за замовчуванням є Sum як зведена статистика.
Малюнок 1.22. Цей крок встановлює групи, які функція FREQUENCY () називає групами.
7. У діалоговому вікні Групування встановіть значення Starting At на 81 і введіть 10 у поле By. Натисніть на
ГАРАЗД.
8. Клацніть правою кнопкою миші клітинку у зведеній таблиці під заголовком Сума ваги. Виберіть Параметри поля значення у контекстному меню. Виберіть Число в полі Підсумувати значення за полем списку, а потім натисніть кнопку ОК.
9. Зводна таблиця та діаграма переналаштовуються, як показано на малюнку 1.23. Щоб видалити кнопки полів у верхньому та нижньому лівому кутах зведеної діаграми, виберіть діаграму, перейдіть на вкладку Аналіз, натисніть кнопку Кнопки поля та виберіть Сховати все.
Малюнок 1.23. Розподіл частоти цієї вибірки показує невеликий зсув вправо, але досить близький до нормальної кривої.
Побудуйте модельовані розподіли частоти
Може бути корисним побачити, як розподіл частоти набуває певної форми із збільшенням кількості основних записів. У робочій книзі для глави 1 є аркуш із назвою Рисунок 1.24, який випадково відбирає записи із сукупності значень, що відповідає нормальному розподілу.
Наступний малюнок, разом з електронною таблицею, на якій вона базується, показує, як розподіл частоти стає все ближчим і ближчим до розподілу сукупності, коли збільшується кількість записованих записів.
Малюнок 1.24. Цей розподіл частоти базується на сукупності записів, які відповідають нормальному розподілу.
Почніть із натискання кнопки з написом Очистити записи у стовпці А. Усі цифри буде видалено зі стовпця А, залишивши лише значення заголовка в комірці А1. Зведена таблиця та зведена таблиця оновляться відповідно. Незважаючи на те, що зведені таблиці та зведені таблиці не відразу реагують на зміни в основних джерелах даних, код VBA, який запускається при натисканні кнопки, викликає оновлення зведеної таблиці.
Визначте, скільки записів потрібно додати, а потім введіть це число в комірку D1. Ви завжди можете змінити його на інший номер.
Клацніть на кнопку Додати записи до діаграми. Коли ви це робите, відбувається кілька подій, спричинених збереженими процедурами Visual Basic у книзі:
• Зразок береться з основного нормального розподілу. Зразок має стільки записів, скільки вказано в комірці D1. (Основна, нормально розподілена сукупність зберігається в окремому аркуші з назвою Випадкові нормальні значення, ви можете переглянути аркуш, клацнувши правою кнопкою миші на вкладці аркуша та вибравши Перегляд у контекстному меню.
• Зразок записів додається до стовпця А. Якщо в стовпці А запису не було, новий зразок записується з комірки А2. Якби вже було, скажімо, 100 записів у стовпці A, новий зразок починався б у комірці A102.
• Зведена таблиця та зведена діаграма оновлюються (або, кажучи Excel, оновляються). Коли ви кілька разів натискаєте кнопку Додати записи на графік, на графіку використовується все більше записів. Чим більше число записів, тим більше таблиця нагадує базовий
нормальний розподіл.
Дійсно, саме це відбувається в експерименті, коли ви збільшуєте обсяг вибірки. Більші зразки більше нагадують сукупність, з якої ви їх отримуєте, ніж менші зразки. Ця більша схожість не обмежується формою розподілу: вона включає середнє значення та міри того, як значення змінюються навколо середнього. За інших рівних умов ви віддаєте перевагу більшій вибірці, ніж меншій, оскільки вона, швидше за все, буде представляти сукупність більш точно.
Але цей ефект створює проблему витрат і вигод. Як правило, чим більша вибірка, тим точніші результати експерименту та дорожчий експеримент.
Тут поставлено безліч питань (і ця книга їх обговорює), але в певний момент поступова точність додавання, скажімо, ще 10 експериментальних предметів вже не виправдовує зайвих витрат на їх додавання. Однією з порад, яку дає статистичний аналіз, є підказка про те, коли ви досягнете точки, коли прибутки починають зменшуватися.
З матеріалом у цій главі - шкалами вимірювань, характером осей на діаграмах Excel та розподілом частот - Глава 2 переходить до початків практичного статистичного аналізу, вимірюючи центральну тенденцію.