ЗАДАНИЕ
Выполните задания в Excel по инструкции
Выполняйте каждую функцию (таблицу и график) на отдельном листе книги Excel, называя его именем функции.
Каждый график должен иметь название по функции. Выполните заливку каждого графика своим градиентом.
Присылайте работу одним файлом, подписанный ФИО, группа и №,название работы Скрин присылать нельзя, я должна просматривать формулы.
Срок сдачи работы –до завтра. Нарушение срока – снижение оценки.
Мотивация обучения ( только ознакомьтесь )
Существуют различные виды зависимостей, которые требуется просчитать и обосновать. Например, изучить зависимости заболевших астмой от концентрации угарного газа в атмосфере. Как найти наиболее удачную функцию, описывающую приведённую зависимость.
Суть такой модели не в точном подсчёте, а в прогнозировании тенденций и приблизительных значений.
Сегодня вы познакомитесь с подбором такой функции методом МНК (метод наименьших квадратов) и регрессионной моделью.
Эти методы непременно пригодятся вам для использования в профессиональной деятельности.
Цели:
Дидактическая:
§ освоение приемов прогнозирования количественных характеристик системы по регрессионной модели путем восстановления значений и экстраполяции.
§ изучение особенности статистических моделей, вспомнить обобщённую формулу квадратичной функции, ознакомиться с методом наименьших квадратов, изучить несколько новых функций Excel.
Воспитательная:
§ воспитывать активность и сознательность будущих специалистов;
§ умение контролировать и оценивать свою деятельность.
Развивающая:
Интеграционные связи
| Дисциплина | Знать |
| Обеспечивающие: - математика; - медицинская информатика. Обеспечиваемые:математические науки; технические науки; гуманитарные науки; общественные науки; естественные науки. | - алгоритмы расчетов, системы счисления; - источники медицинских изображений. - способы работы с прикладной информацией |
Краткие теоретические сведения и ход работы
Способ 1: оператор ПРЕДСКАЗ
Экстраполяцию для табличных данных можно произвести через стандартную функцию Эксель ПРЕДСКАЗ. Этот аргумент относится к категории статистических инструментов и имеет следующий синтаксис:
=ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)
«X» – это аргумент, значение функции для которого нужно определить. В нашем случае в качестве аргумента будет выступать год, на который следует произвести прогнозирование.
«Известные значения y» — база известных значений функции. В нашем случае в её роли выступает величина прибыли за предыдущие периоды.
«Известные значения x» — это аргументы, которым соответствуют известные значения функции. В их роли у нас выступает нумерация годов, за которые была собрана информация о прибыли предыдущих лет.
Естественно, что в качестве аргумента не обязательно должен выступать временной отрезок. Например, им может являться температура, а значением функции может выступать уровень расширения воды при нагревании.
При вычислении данным способом используется метод линейной регрессии.
Давайте разберем нюансы применения оператора ПРЕДСКАЗ на конкретном примере. Возьмем всю ту же таблицу. Нам нужно будет узнать прогноз прибыли на 2018 год.
- Выделяем незаполненную ячейку на листе, куда планируется выводить результат обработки. Жмем на кнопку «Вставить функцию».
- Открывается Мастер функций. В категории «Статистические» выделяем наименование «ПРЕДСКАЗ», а затем щелкаем по кнопке «OK».
- Запускается окно аргументов. В поле «X» указываем величину аргумента, к которому нужно отыскать значение функции. В нашем случаем это 2018 год. Поэтому вносим запись «2018». Но лучше указать этот показатель в ячейке на листе, а в поле «X» просто дать ссылку на него. Это позволит в будущем автоматизировать вычисления и при надобности легко изменять год.
В поле «Известные значения y» указываем координаты столбца «Прибыль предприятия». Это можно сделать, установив курсор в поле, а затем, зажав левую кнопку мыши и выделив соответствующий столбец на листе.
Аналогичным образом в поле «Известные значения x» вносим адрес столбца «Год» с данными за прошедший период.
После того, как вся информация внесена, жмем на кнопку «OK».
- Оператор производит расчет на основании введенных данных и выводит результат на экран. На 2018 год планируется прибыль в районе 4564,7 тыс. рублей. На основе полученной таблицы мы можем построить график при помощи инструментов создания диаграммы, о которых шла речь выше.
- Если поменять год в ячейке, которая использовалась для ввода аргумента, то соответственно изменится результат, а также автоматически обновится график. Например, по прогнозам в 2019 году сумма прибыли составит 4637,8 тыс. рублей.
Но не стоит забывать, что, как и при построении линии тренда, отрезок времени до прогнозируемого периода не должен превышать 30% от всего срока, за который накапливалась база данных.
Экстраполяция в Excel