Упражнение 3. Группировка с помощью статистической надстройки
Группировка данных проводится двумя способами: с помощью стандартных функций Excel и статистической надстройки.
Вначале следует создать таблицу нижних границ интервалов группирования (рис. 1.10).
Рис. 1.10. Настройка функции Гистограмма
Для группировки данных с помощью статистической надстройки выбираем меню [Сервис → Анализ данных → Гистограмма]. Указываем следующие параметры: Входные данные:
− Входной интервал – выборка исходных данных;
− Интервал карманов – нижние границы интервалов группирования.
Параметры вывода:
− Выходной интервал – расположение результатов группировки на листе;
− Вывод графика – построение гистограммы.
− Интегральный процент – вычисление накопленных частостей.
Результат работы функции Гистограмма представлен на рис. 1.11. Сгенерированную таблицу необходимо дополнить недостающими столбцами. График необходимо настроить для корректного отображения. На рис. 1.12 показан пример рекомендуемого стиля оформления таблицы и графика.
Рис. 1.11. Результат вызова функции «Гистограмма»
Рис. 1.12. Настройка результатов группировки
Таблицу необходимо дополнить следующими столбцами:
− Интервал – подписи для столбцов гистограммы в виде текстовых меток, описывающих границы интервалов группировки;
− Частость ni (%); − Кумулята Ki (%).
В графе Всего выполняют подсчет суммы частот.
Для вычисления недостающих характеристик интервалов следует использовать формулы. Например, для вычисления накопленной частости для интервала 90..100 (ячейка G3) используется формула =G2+F3. Вычисление относительной частоты для интервала 80..90 (ячейка F3) выполняется с помощью формулы:
=100*E3/$E$8.
После вычислений следует убедиться в отсутствии грубых ошибок. Например, накопленная частость должна быть равна 100%.
Расположение столбиков гистограммы должно соответствовать границам интервалов группирования данных. Для настройки графика щелкните по столбику гистограммы курсором и нажмите правую
кнопку мыши. Выберите [Формат рядов данных → Параметры] и установите нулевое значение параметров Перекрытие и Ширина зазора.
Упражнение 4. Группировка с помощью формул
Пример группировки с помощью формул приводится на рис. 1.13.
Подсчет частоты попадания в интервал значений определяется как разность количества значений меньше верхней границы и меньше нижней границы интервала. Например, частота для первого интервала (ячейка G6) рассчитана с помощью функции СЧЁТЕСЛИ:
СЧЁТЕСЛИ(B:B;"<="&E6)-СЧЁТЕСЛИ(B:B;"<="&D6).
Первая часть приведённой формулы вычисляет количество ячеек столбца B, значения которых меньше или равно содержимого ячейки E6 (верхняя граница диапазона). Вторая часть формулы вычисляет количество ячеек столбца B, значения которых меньше или равно содержимого ячейки D6 (нижняя граница диапазона). Таким образом, функция в целом даёт количество ячеек столбца B, значения которых попадают в интервал между значениями ячеек D6 и E6.
Рис. 1.13. Группировка данных с использованием формул
Рис. 1.14. Гистограмма по результатам группировки
По рассматриваемой таблице строится гистограмма (рис. 1.14).
для этого выбираем в меню [Вставка → Диаграмма → Гистограмма → Обычная гистограмма]. Переходим на закладку Ряд и нажимаем кнопку Добавить. Нажимаем кнопку Значения и указываем диапазон значений частот. Нажимаем кнопку Подписи оси Х и указываем диапазон меток для оси Х. Нажимаем кнопки Далее → Готово. В качестве меток можно указать середины интервалов группировки.