Практическая работа №25
Выполнил студент группы № ______________________________________
Тема: Использование различных возможностей динамических (электронных) таблиц для выполнения учебных заданий.
Цель: освоить способы построения по экспериментальным данным диаграмм средствами электронных таблиц.
Теоретические сведения
Статистика – наука о сборе, измерении и анализе массовых количественных данных.
Функция, которая удовлетворяет требованиям:
должна быть достаточно простой для использования ее в дальнейших вычислениях;
График этой функции должен проходить вблизи экспериментальных точек так, чтобы отклонения этих точек о графика были минимальны и равномерны, называется регрессионной моделью.
Получение регрессионной модели происходит в два этапа:
l подбор вида функции;
l вычисление параметров функции.
Чаще всего выбор производится среди следующих функций:
y =аx+b - линейная функция;
y=аx2+bx+c - квадратичная функция;
y=аln(x)+bx - логарифмическая функция;
y=aebx - экспоненциальная функция;
y=axb- степенная функция.
Во всех этих формулах x – аргумент, y – значение функwи, a, b, c – параметры функций.
При выборе одной из функций нужно подобрать параметры так, чтобы функция располагалась как можно ближе к экспериментальным точкам.
Существует метод наименьших квадратов (мнк). Его суть – искомая функция должна быть построена так, чтобы сумма квадратов отклонений y-координат всех экспериментальных точек от y-координат графика функции была бы минимальна.
Графики регрессионной модели называются трендами. (английское слово trend переводиться как общее направление или тенденция).
Алгоритм получения с помощью MS Excel регрессионных моделей по мнк с построением тренда.
· вводим табличные данные.
· строим точечную диаграмму, где в качестве подписи к оси Ox выбрать текст «линейный тренд» (остальные надписи и легенду можно игнорировать).
· Щелкнуть мышью по полю диаграммы; выполнить команду диаграмма – добавить линию тренда;
· в открывшемся окне на закладке «тип» выбрать «линейный тренд»;
· перейти к закладке «параметры» и установит галочки на флажках «показать уравнения на диаграмме» и «поместить на диаграмме величину достоверности аппроксимации R^2» и щелкнуть OK.
· аналогично получаем и другие тренды.
Раздел математической статистики, который исследует такие зависимости, называется корреляционным анализом. Корреляционный анализ изучает усредненный закон поведения каждой из величин в зависимости от значений другой величины, а также меру такой зависимости.
Оценку корреляции величин начинают с высказывания гипотезы о возможном характере зависимости между их значениями. Чаще всего допускают наличие линейной зависимости. В таком случае мерой корреляционной зависимости является величина, которая называется коэффициентом корреляции.
Коэффициент корреляции (обычно обозначаемый греческой буквой r) есть число, заключенное в диапазоне от -1 до +1.
Eсли это число по модулю близко к 1, то имеет место сильная корреляция, если к 0, то слабая.
Близость r к +1 означает, что возрастанию одного набора значений соответствует возрастание другого набора, близость к -1 означает обратное.
Значение r легко найти с помощью Excel (встроенные статистические функции).
В Excel функция вычисления коэффициента корреляции называется КОРЕЛЛ и входит в группу статистических функций.
Задание для самостоятельной работы
Задание 1. Создать таблицу финансовой сводки за неделю, произвести расчеты, построить диаграмму изменения финансового результата, произвести фильтрацию данных. Исходные данные представлены на рис. 1.
Порядок работы
1. Запустите редактор электронных таблиц MicrosoftExcel и создайте в своей папке новую электронную книгу под своей фамилией.
Рис. 1. Исходные данные для Задания 1
Произведите расчеты в графе «Финансовый результат» по следующей формуле:
Финансовый результат = Доход – Расход.
Для этого в ячейке D4 наберите формулу =В4-С4.
Задание 2. Постройте диаграмму (линейчатого типа) изменения финансовых результатов по дням недели с использованием мастера диаграмм.
Форматирование выполните самостоятельно в соответствии с видом диаграммы на рис. 2.
Рис. 2. Конечный вид диаграммы Задания 1
Сохраните созданную электронную книгу в своей папке.
Задание 3. Заполнить таблицу, произвести расчеты, выделить минимальную и максимальную суммы покупки (рис. 3). По результатам расчета построить круговую диаграмму суммы продаж с обозначением долевых значений вырученных сумм.
Рис. 3. Исходные данные для Задания 3
Формулы для расчета:
Сумма = Цена х Количество;
Всего = сумма значений колонки «Сумма».
Краткая справка. Для выделения максимального/минимального значений установите курсор в ячейке расчета, выберите встроенную функцию МАКС (МИН) из категории «Статистические», в качестве первого числа выделите диапазон ячеек значений столбца «Сумма» (ячейки ЕЗ:Е10).
Построить гистограмму значений изменения выручки по видам продукции.
Задание 4. Рассчитать зарплату за декабрь и построить диаграмму. Создать итоговую таблицу ведомости квартального начисления заработной платы, провести расчет промежуточных итогов по подразделениям.
Порядок работы
1. Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книги. Назовите скопированный лист «Зарплата декабрь». Исправьте название месяца в ведомости на декабрь.
2. Измените значение Премии на 46 %, Доплаты — на 8 %. Программа произведет пересчет формул (рис. 4).
Рис. 4. Ведомость зарплаты за декабрь
3. По данным таблицы «Зарплата декабрь» постройте гистограмму доходов сотрудников. В качестве подписей оси X выберите фамилии сотрудников. Проведите форматирование диаграммы в соответствии с рис. 5.
Рис.. Гистограмма зарплаты за декабрь
4. Проведите сортировку по фамилиям в алфавитном порядке (по возрастанию) в ведомостях начисления зарплаты за октябрь-декабрь.
Сделать вывод о проделанной работе