Сводка и группировка в 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 с.
Подробное, пошаговое описание хода выполнения лабораторной работы.
Бесплатный доступ:
/
Комментарии к книге «Сводка и группировка в Excel», Валентин Юльевич Арьков
Всего 0 комментариев