Сводка и группировка в 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) или пузырьковую диаграмму — Точечная — Точечная с прямыми отрезками.

Задайте исходные данные для графика.

Верхние границы — по оси X:

Series X Values

Значения Х.

Накопленные частоты — по оси Y:

Series Y Values

Значения Y.

Функция FREQUENCY

Определите количество попаданий в интервалы с помощью функции

FREQUENCY

ЧАСТОТА.

Функцию массива вводите следующим образом:

— введите формулу в одну ячейку

— выделите диапазон ячеек

— нажмите F2

— нажмите Ctrl + Shift + Enter.

Определите относительные частоты и накопленные частоты.

Постройте полигон — ломаную линию относительных частот.

Функция COUNTIF

Проведите группировку данных и постройте гистограмму и использованием функции

COUNTIF

СЧЁТЕСЛИ.

Сравнение

Сравните построенные графики с теоретическими. Для этого вычислите значения функции распределения:

NORM. DIST

НОРМ. РАСП.

Наложите теоретические и эмпирические графики и сравните их.

Скопируйте на один лист графики, полученные разными способами. Сравните графики.

Показатели

Вычислите статистические показатели тремя способами:

— надстройка;

— функции;

— формулы.

Надстройка

Вычислите показатели с помощью надстройки:

Data — Analysis — Data Analysis — Descriptive Statistics

Данные — Анализ — Анализ данных — Описательная статистика.

Оформите таблицу с показателями, указав корректные названия — см. таблицу.

В отдельном столбце приведите теоретические оценки показателей.

Функции

Вычислите статистические показатели с помощью готовых функций.

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

AVERAGE

СРЗНАЧ.

Генеральная дисперсия:

VAR. P

ДИСПP.

Выборочная дисперсия:

VAR. S

ДИСП.

Генеральное стандартное отклонение:

STDEV. P

СТАНДОТКЛОНP.

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

STDEV. S

СТАНДОТКЛОН.

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

MAX

МАКС.

Минимальное значение

MIN

МАКС.

Коэффициент асимметрии:

SKEW

СКОС.

Коэффицииент эксцесса:

KURT

ЭКСЦЕСС.

Среднее линейное отклонение:

AVEDEV

СРОТКЛ.

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

COUNT

СЧЕТ.

Медиана:

MEDIAN

МЕДИАНА.

Мода:

MODE

МОДА.

Сумма:

SUM

СУММ.

Формулы

Вычислите показатели с помощью формул — см. таблицу.

Для вычисления абсолютного значения используйте функцию ABS.

Для вычисления разностей и сумм различных степеней постройте вспомогательную таблицу.

Для вывода относительных коэффициентов задайте вывод в процентах:

Format Cells — Number — Percentage

Формат ячеек — Число — Процентный.

Сравнение

Сравните значения показателей, полученные разными способами. Для этого скопируйте все показатели в общую таблицу. Сделайте выводы о качестве вычислений.

Реальные данные

Загрузите дневные данные о биржевых котировках (ценах на акции) за последний год:

FINAM.RU — Теханализ — Экспорт котировок.

Выберите соответствующую акцию из списка Индекса МосБиржи 10 (MOEX10). Уточните базу расчёта на сайте Мосбиржи:

MOEX.COM.

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

Delimeters

Символом-разделителем является.

Задайте формат даты:

Column Data Format — Date — YMD

Формат данных столбца — Дата — ГМД.

Укажите десятичный разделитель:

Advanced — Decimal separator

Подробнее — Разделитель целой и дробной части.

В русской версии пакета используется точка, в английской — запятая.

Проведите анализ объёма торгов VOLUME. Постройте графики распределения и вычислите основные статистические показатели.

Литература

1. Теория статистики: Учебник / Р. А. Шмойлова, В. Г. Минашкин, Н. А. Садовникова, Е. Б. Шувалова; под ред. Р. А. Шмойловой. — М.: Финансы и статистика, 2014. — 656 с.

Базовый учебник по предмету.

2. Практикум по теории статистики: Учеб. пособие / Р. А. Шмойлова, В. Г. Минашкин, Н. А. Садовникова; под ред. Р. А. Шмойловой. — М.: Финансы и статистика, 2014. — 416 с.

Сборник типовых задач + методика решения.

3. Арьков В. Ю. Анализ распределения в Excel: Учебное пособие.— [б. м.]: Издательские решения, 2019. — 158 с.

Подробное, пошаговое описание хода выполнения лабораторной работы.

Бесплатный доступ:

https://ridero.ru/books/analiz_raspredeleniya_v_excel/

Content

Сводка и группировка в Excel
Лабораторный практикум
Валентин Юльевич Арьков

Введение

Общие сведения

Отчёт

Зарисовки

Генератор

Группировка

Диаграмма

Надстройка

Функция FREQUENCY

Функция COUNTIF

Сравнение

Показатели

Надстройка

Функции

Формулы

Сравнение

Реальные данные

Литература