Валентин Арьков - Сводка и группировка в Excel

© Валентин Юльевич Арьков, 2019

ISBN 978-5-0050-4207-1

Создано в интеллектуальной издательской системе Ridero

Сводка и группировка данных является базовым инструментом анализа данных. Лабораторная работа выполняется в пакете Microsoft Excel. Аналогичные возможности по обработке табличных данных имеются и в других электронных таблицах, таких как Libre Office Calc, Google Sheets и других. В описании приводятся как английские, так и русские названия пунктов меню.

Названия функций и пунктов меню выделены жирным шрифтом. Слово КНОПКА означает элемент оконного интерфейса — «кнопку» на экране. Слово КЛАВИША означает клавишу на клавиатуре. Для быстрого доступа к функциям программы нужно нажать КОМБИНАЦИЮ клавиш.

В качестве исходных данных будут использоваться псевдослучайные числа, а также реальные данные из сети интернет.

Данные в электронных таблицах традиционно располагаются по столбцам — с возможностью подсчёта итогов по каждому столбцу. В данной работе нужно будет освоить различные способы обработки табличных данных.

Лабораторная работа выполняется в пакете Microsoft Excel. Отчёт оформляется в одной рабочей книге.

Каждое задание выполняйте на отдельном листе.

Комментарии должны пояснять ход выполнения работы.

Название файла должно быть коротким и информативным:

Иванов ПИ-333 БАС-1.xlsx.

Создайте пустую рабочую книгу и сохраните её в файле. Проверьте, как выглядит выбранное название файла в Проводнике, в электронной почте и на облачном диске. В качестве названия страниц используйте номера страниц.

Создайте первую страницу отчёта — титульный лист — в соответствии со стандартами вуза на оформление текстовых документов. Расположите весь текст на видимой части экрана.

Создайте второй лист отчёта — оглавление:

Insert — Links — Link

Вставка — Ссылки — Ссылка

На новой странице опишите свой вариант задания.

Чтобы ознакомиться с формой распределения, будем использовать зарисовки. Зарисовки нужно сделать на бумаге от руки, сфотографировать и отредактировать в графическом пакете GIMP. Примеры зарисовок см. ниже.

В соответствии со своим вариантом сделайте зарисовки кривых распределения и вставьте в отчёт.

Нормальное распределение

Равномерное распределение

Исходя из параметров задания, оцените характеристики распределения:

— среднее;

— сигму;

— минимум;

— максимум;

— размах;

— асимметрию;

— эксцесс.

При оценке минимума-максимума используйте «правило трёх сигм».

На первом этапе исходные данные создаются с помощью генератора случайных чисел, доступного в надстройке Анализ данных:

Data — Analysis — Data Analysis — Random Number Generation

Данные — Анализ — Анализ данных — Генерация случайных чисел.

Сгенерируйте исходные данные в соответствии с вариантом задания. Установите необходимые настройки генератора.

Количество столбцов:

Number of Variables

Число переменных

Объём выборки:

Number of Random Numbers

Число случайных чисел

Распределение:

Distribution — Normal

Распределение — Нормальное

Среднее значение:

Mean

Среднее

Сигма:

Standard deviation

Стандартное отклонение

Начальное состояние генератора — любые четыре цифры:

Random Seed

Случайное рассеивание

Интервал для вывода случайных чисел:

Output options — Output Range

Параметры вывода — Выходной интервал

На новом листе сгенерируйте случайные числа с равномерным распределением.

Распределение:

Distribution — Uniform

Распределение — Равномерное

Минимальное и максимальное значения:

Parameters — Between ___ and ___

Параметры — Между ___ и ___

Начальное состояние генератора, не совпадающее с предыдущим:

Random Seed

Случайное рассеивание

Округлите сгенерированные числа до целых значений и поместите их в новый столбец.

ROUND

ОКРУГЛ

В пакете Excel есть несколько способов для группировки данных:

— готовые диаграммы;

— надстройка (Add-on);

— готовые функции;

— формулы.

Все эти способы должны давать одинаковые или очень близкие результаты.

Выделите столбец случайных чисел и вставьте гистограмму как диаграмму:

Insert — Charts — Insert Statistic Chart — Histogram

Вставка — Диаграммы — Вставить статистическую диаграмму — Гистограмма.

Подберите оптимальное количество столбцов графика:

Axis Options — Bins — Number of bins

Параметры оси — Интервалы — Количество интервалов.

Установите нулевой зазор:

Format Data Series — Series Options — Gap Width — 0%

Формат ряда данных — Параметры ряда — Боковой зазор — 0%.

Уберите заливку:

Format Data Series — Series Options — Fill & Line — Fill — No fill

Формат ряда данных — Параметры ряда — Заливка и границы — Заливка — Нет заливки.

Сгруппируйте крайние значения:

Format Axis — Axis Options — Underflow bin / Overflow bin

Формат оси — Параметры оси — Выход за нижнюю / верхнюю границу интервала.

Постройте гистограмму с помощью надстройки:

Data — Analysis — Data Analysis — Histogram

Данные — Анализ — Анализ данных — Гистограмма.

Подготовьте столбец правых границ интервалов и укажите его в разделе:

Input — Bin Range

Входные данные — Интервал карманов.

Выберите подсчёт накопленных частот:

Cumulative Percentage

Интегральный процент.

Постройте график:

Chart Output

Вывод графика.

Скопируйте диаграмму и настройте гистограмму и кумуляту как отдельные графики.

Вычислите относительные частоты и постройте гистограмму:

Insert — Charts — Inset Column or Bar Chart — 2-D Column — Clustered Column

Вставка — Диаграммы — Вставить гистограмму или линейчатую диаграмму — Гистограмма — Гистограмма с группировкой.

Установите метки (подписи под столбиками):

Select Data Source — Horizontal (Category) Axis Labels

Выбор источника данных — Подписи горизонтальной оси (категории).

Постройте график накопленных частот (кумуляту):

Insert — Charts — Insert Scatter (X, Y) or Bubble Chart — Scatter — Scatter with Straight Lines

Вставка — Диаграммы — Вставить точечную (X, Y) или пузырьковую диаграмму — Точечная — Точечная с прямыми отрезками.