Сделай Сам Свою Работу на 5

Лабораторная работа 5 Аппроксимация зависимостей





 

Цель работы: Освоить навыки работы со средствами Excel, позволяющие прогнозировать процессы.

 

Порядок работы:

1. Запустите программу MS Excel 2010.

2. В открывшемся окне нажмите кнопку Новая книга для создания новой рабочей области. Щелкните по кнопке Создать.

3. В поле Имя файла (файл сохранить как) задайте имя вашей рабочей книги lab5_FIO (где FIO - ваши инициалы) и выберите диск D/папка студент/папка FIO и номер группы (созданная на первом занятии).

4. При этом откроется окно вашей рабочей книги.

 

Пусть имеется функция, заданная таблицей. Ее аналитическое выражение неизвестно. Для аргумента, не совпадающего с табличным, невозможно определить значение функции. У многих функций, аналитическое выражение которых известно, можно вычислить значение функции для любого значения аргумента, но эти вычисления, как правило, трудоемки. Поэтому в обоих случаях ищется функция, приближенно совпадающая с данной, аналитическое выражение которой известно и определение ее значения для любого значения аргумента не представляет трудностей. Найденная функция называется аппроксимирующей. Используя ее, можно определить приближенно значения исходной функции для любых значений аргумента.



Задача аппроксимации позволяет решать задачи, связанные с процессом прогнозирования. Если зависимость удаётся найти, можно сделать прогнозо поведении исследуемой системы в будущем, возможно, выбрать оптимальное направление развития. Такая аналитическая функция может иметь разный вид и разный уровень сложности в зависимости от сложности системы и желаемой точности представления.

EXCEL располагает средствами, позволяющими решать подобные задачи.

Функция ТЕНДЕНЦИЯ

Функция имеет следующий синтаксис:

ТЕНДЕНЦИЯ (известные значения_ у; известные значения_х; новые значения_ х; конст).

Первые два аргумента представляют собой известные значения зависимых и независимых переменных соответственно. Аргумент у (соответственно и аргумент х) может быть столбцом, строкой или прямоугольным диапазоном.

Третий и четвертый аргумент являются необязательными, если аргумент новые значения_х совпадают с аргументом известные значения_х. Если используется аргумент конст, его значение должно быть истина или ложь (1 или 0). Массив результатов будет иметь такой же размер, как и диапазон, известныезначения_х.



Если в данной функции имеется массив, для его ввода используются клавиши Ctrl+Shift+Enter.

Для обращения к функции ТЕНДЕНЦИЯ следует вызвать Мастер функций с помощью команды ВСТАВКА, Функция или нажатием одноименной кнопки на панели инструментов Стандартная. Выбрав из категории Статистические в появившемся списке Функция, выделить ТЕНДЕНЦИЯ.

Пример: =ТЕНДЕНЦИЯ (В2:В20; А2:А20; А22:А24).

В2:В20-массив значений Y (зависимой переменной);

А2:A20-массив значений Х (независимой переменной);

А22:А24 – новые значения X.

Функция ТЕНДЕНЦИЯ определяет данные, которые могут быть графически отображены в виде линии ТРЕНДА.

В Ехсel имеется быстрый способ определения аппроксимирующей функции. Для этого нужно выделить известные значения зависимой величины и потянуть за маркер заполнения, удерживая правую кнопку мыши. Затем, в появившемся контекстном меню выбрать пункт линейныеприближения, если прогнозируется равномерный рост, или команду экспоненциальныеприближения, если предполагаются экспоненциальные изменения.

Построение линии тренда

Средства графики позволяют найти уравнение аппроксимирующей функции. Если построена кривая У(х) и на ней щелкнуть правой кнопкой мыши, в появившемся контекстном меню можно выбрать пункт добавитьлиниютренда, который открывает окно линиятренда. Здесь можно выбрать вид уравнения аппроксимации и его степень. Если во вкладке параметры установить показыватьуравнениенадиаграмме, то на графике увидим не только линию тренда, но и ее уравнение. Здесь же можно визуально оценить поведение анализируемого процесса в будущем / прошлом, если установить прогнозвперед/назад на заданное число единиц независимой величины х.



Функция используется при решении задач прогнозирования в любой сфере деятельности человека (анализ объектов продаж, планирование производства, развитие бизнеса и т.д.).

Пример 18.

Имеются сведения об объеме продаж за полгода с января по июнь. Что ожидается в июле, августе, сентябре? Данные размещаются в таблице. Месяцы располо­жены в ячейках A2:A10, значения объемов продаж - в ячейках B2:B7, объемы продаж, которые нужно определить, будут располагаться в ячейках B8:B10. Для решения этой задачи используется функция ТЕНДЕНЦИЯ, ее вид:

= ТЕНДЕНЦИЯ(B2:B7;A2:A7;A8:A10)

Алгоритм решения.

В вашей рабочей книге lab5_FIO, откройте Лист 1.

1. Данные занести в таблицу.

2. Выделить ячейку, где разместится первый результат, это ячейка В8. Ввести функцию, в которой содержатся массивы, для этого используются клавиши CTRL+SHIFT+ENTER.

3. В ячейке B8 появится искомое значение, затем формулу скопировать на ячейки B9, В10.

5. Построить диаграмму.

6. Курсор установить на элементе диаграммы.

7. Во вкладке Макетвыбрать линия тренда.

8. Для получения информации о параметрах линий тренда следует щелкнуть на стрелку (выпадающие меню) – дополнительные параметры линии тренда. В параметрах линии тренда можно выбрать – полиномиальная.

9. Чтобы указать дополнительные параметры: название линии тренда, параметры прогнозирования и т.д., следует выбрать вкладку Параметры.

 

Рис.32. Построение диаграммы и линии тренда

Задания для самостоятельного выполнения.

Задание 5 (По построению диаграмм и линий тренда)

 

5.1.В табл. 5.1 приведены сведения о сбыте мороженого при разных температурах наружного воздуха. Построить на базе этих данных диаграммы типа Точечная и График. Отформатировать диаграммы. Проанализировать различия в расположении точек и использовании аргументов в этих диаграммах. Построить тренд и сделать прогноз сбыта при температурах –20 и +30 оС.

Таблица 5.1

t, оС   –3 –17   –6     –3       –17
Сбыт, кг                        

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

Таблица 5.2

Неделя                        
Продано товара, шт.                     ? ?

5.3. Введите и отформатируйте ведомость оплаты счетов за IV квартал (табл. 5.3). Графу "Долг" и строку "Общий итог" заполните с помощью формул.

Таблица 5.3

Фирма Сумма в счете Сумма оплаты Долг
"Василек" $600 $550 $50
"Гвоздика" $400 $300 $100
"Ландыш" $900 $900 $0
"Ромашка" $800 $800 $0
Общий итог $2 700 $2 550 $150

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

5.4. Изобразите на круговых диаграммах показатели Сумма в счете и Сумма оплаты из табл. 5.3. Отформатируйте эти диаграммы разными способами и сравните разные варианты подписей около секторов.

5.5. Изобразите на гистограммах с накоплением данные о сумме счетов за I квартал (табл. 5.4). В первом варианте ряды данных относятся к разным месяцам, во втором – к фирмам. Представьте эти диаграммы в абсолютном и процентном масштабе.

Таблица 5.4

Фирма Месяц Итого
январь февраль март
"Василек" $200 $300 $100 $600
"Ландыш" $500 $400 $200 $1 100
"Ромашка" $300 $600 $400 $1 300
Итого: $1 000 $1 300 $700  

5.6. Составьте таблицу значений функций x(t) = sin(kt)cos(t) и
y(t) = sin(kt)sin(t) в соответствии с рис. 33.

  A B C D E F
  Параметр функций Расчет шага по аргументу        
  K =   tнач tкон число шагов шаг
      –3,14 3,14    
             
  t x(t) y(t)      
             
             
             
             
             

Рис. 33. Значения функций

Формулу для расчета длины шага составьте самостоятельно. Постройте две точечные диаграммы по полученной таблице. В первой диаграмме изобразите функции x(t) и y(t) (по горизонтальной оси значения t, по вертикальной – два ряда данных: x(t) и y(t)). Во второй – изобразите неявную зависимость y(x) (по горизонтальной оси значения х, по вертикальной – значения y). Посмотрите, как меняется вид диаграмм при изменении значения параметра k (k = 1, 2, 3, ...).

5.7. В табл. 5.5 представлена ежедневная выручка пяти филиалов магазина в течение недели. Оформите эти данные в виде таблицы Excel. Введите формулы для итоговой выручки. Для чисел используйте денежный формат. Дни недели вводите протяжкой. Изобразите эти данные в виде двух гистограмм с накоплением. На первой в качестве рядов данных рассматривайте выручку того или иного филиала по дням недели (по горизонтальной оси откладываются дни недели). На второй – выручку всех филиалов в определенный день недели (по горизонтальной оси – номера филиалов).

Таблица 5.5

День недели Филиал Всего за день
№ 1 № 2 № 3 № 4 № 5
Понедельник 30 250 25 940 35 970 19 430 15 760  
Вторник 28 400 25 820 35 590 17 830 18 590  
Среда 35 240 24 760 36 120 15 120 23 790  
Четверг 32 680 26 550 34 540 19 560 22 320  
Пятница 34 630 27 230 33 170 20 890 20 210  
Суббота 26 570 26 120 32 850 22 360 17 530  
Воскресенье 24 780 25 310 30 390 23 150 16 910  
Всего            
Общая выручка за неделю:            

5.8. В табл. 5.6 представлены данные о численности населения в некоторой стране за ряд лет. Представьте эти данные на диаграмме, подберите тренд и сделайте прогноз численности населения на следующие 5 лет.

Таблица 5.6

Год Население, тыс. чел. Год Население, тыс. чел. Год Население, тыс. чел. Год Население, тыс. чел.
               
               
               
               
               
               
               
               

5.9. В табл. 5.7 представлена ведомость, которая заполняется по мере поступления заказов от покупателей на определенный товар. В нее также внесены графы, обобщающие характеристики покупок. Оформите эту ведомость в виде таблицы Excel, рассчитанной на 20–40 покупателей, придумайте и введите объемы заказов в графу 2. Введите в отдельную ячейку цену на товар и заполните формулами графы 3–7. Графы 6 и 7 проиллюстрируйте диаграммами.

 

Таблица 5.7

№ п/п Заказано покупателем Стоимость покупки Итого за день В среднем на покупку
Куплено Выручка Куплено Выручка  
             
             
             
             
               

Подсказка

· Графа 3. Составьте формулу самостоятельно.

· Графа 4. В первой строке "Итого за день куплено" совпадает с заказом первого покупателя. В остальных – к итогу по предыдущим строкам добавляется объем заказа очередного покупателя.

· Графа 5 – аналогично графе 4.

· Графа 6 – "Итого за день куплено" / "№ п/п".

· Графа 7 – аналогично графе 6.

Пояснение к задачам 5.10–5.16

В этих задачах константы, необходимые для выделения нужных данных, удобно предварительно ввести или рассчитать в отдельных ячейках и затем использовать их для сравнения в команде Формат ® Условное форматирование…

5.10. Составьте ведомость, в которой отражены оценки 10 студентов по разным дисциплинам (4–6 дисциплин), средний балл по каждой из них, средний балл каждого студента. С помощью условного форматирования выделите в ведомости двойки и пропуски экзаменов. Постройте в цветном и черно-белом варианте гистограммы, представляющие средние баллы по разным дисциплинам (по оси Х – дисциплина, по оси Y – средний балл по ней).

5.11. Составьте ведомость, отражающую цену, объем продаж и выручку по ряду продуктов. Выделите продукты, дающие максимальную выручку, максимальный объем продаж. Представьте объем продаж и выручку в виде круговых диаграмм.

5.12. Составьте ведомость, в которой учтено время ежедневного простоя оборудования из-за нехватки сырья в течение месяца (дни месяца вводите протяжкой). Отметьте в ней дни, когда простои превысили 80 мин. Изобразите эти данные точечной диаграммой, подберите функцию тренда, сделайте прогноз на следующие 7 дней.

5.13. Составьте таблицу, в которой приведены сведения о среднем количестве покупателей в магазине в разное время дня (9:00 – 9:30; 9:30 – 10:00 и т. д.). Найдите среднее количество посетителей за день. Отметьте в таблице с помощью условного форматирования время, когда количество посетителей превышает норму обслуживания одним кассиром. Представьте эти данные в виде диаграммы типа График.

5.14. Предприятие реализует свой товар в нескольких районах области. Представьте данные об объемах и выручке по районам в виде ведомости (в разных районах цены могут быть разными). Найдите общий объем реализации. Отметьте районы, в которых реализация превысила накладные расходы. Сравните результаты реализации на круговой диаграмме.

5.15. Имеются данные об объеме затрат каждого кандидата на рекламную кампанию и его рейтинге. Представьте их в виде таблицы. Выделите в таблице кандидатов, затраты которых на рекламную кампанию больше нормы. Подсчитайте общую сумму затрат всех кандидатов на рекламу. Проиллюстрируйте эти данные пузырьковой диаграммой.

5.16.Предприятие проводит рекламные мероприятия. Отразите сведения о сбыте товаров в каждый день рекламной кампании в виде таблицы и ломаной линии. Отметьте день с максимальным сбытом. Определите общий объем сбыта за время кампании. Подберите на графике функцию тренда.

5.17. Постройте таблицу и диаграмму функции при значениях a = 0,2; b = 0,3. Аргументы х перебирайте в диапазоне от 1 до 2 с шагом 0,1.

5.18. Постройте таблицу и график функции . Аргументы х перебирайте в диапазоне от 0 до 2 с шагом 0,2. вычисляется либо как , либо как функция КОРЕНЬ(х).

5.19. Создайте и отформатируйте таблицу предложенного ряда функций (табл. 5.8) для аргументов х, меняющихся от 0 до 6 с шагом 0,3. Постройте диаграмму, на которой будут представлены все функции из таблицы и их сумма.

Таблица 5.8

x sin(x) sin(x) + 0,5 sin(2x + 1) cos(4x) Сумма функций
           
0,3          
         
           
Среднее значение          

5.20. Создайте и отформатируйте таблицу предложенного ряда функций (табл. 5.9) для аргументов х, меняющихся от 0,5 до 5 с шагом 0,25. Постройте диаграмму, на которой будут представлены все функции из таблицы и их сумма.

Таблица 5.9

x   ln(x) 5lg(x) 0,25x2 Сумма функций
0,5          
0,75          
         
           
Среднее значение          

5.21.В табл. 5.10 представленызначениятемпературы в июле.

Таблица 5.10

Время дня Число месяца
             
10:00 19,00 18,72 18,73 18,97 19,37 19,37 19,31
11:00 20,00 19,86 19,71 19,34 19,16 18,92 18,99
12:00 22,00 21,55 21,12 20,67 20,37 20,75 21,02
13:00 23,00 23,22 23,06 23,48 23,12 22,85 23,31
14:00 25,00 25,47 25,09 24,91 24,78 25,09 25,02
15:00 27,00 27,40 27,89 27,62 27,84 27,42 27,61
16:00 25,00 25,11 25,41 25,25 25,46 25,12 24,97
17:00 24,00 23,63 23,56 23,33 23,68 23,43 23,76

Сделайте в табл. 5.11 статистическую обработку этих данных. Необходимые функции находятся в Мастере функций в категории Статистические. Постройте две диаграммы: на одной – реальные значения температур для каждого времени дня, указанного в таблице (8 рядов данных), на другой – усредненные по времени характеристики.

Таблица 5.11

Показатель Число месяца
             
Средняя температура              
Стандартное отклонение              
Средняя температура минус стандартное отклонение              
Средняя температура плюс стандартное отклонение              

Порядок выполнения заданий:

 

1. Каждое задание делается в одной рабочей книге на новом рабочем листе.

2. Варинаты заданий задает преподаватель.

3. Сравните правильность выполнения заданий, с ответами приведенные в самих за-даниях.

4. .Отчеты предоставляются в электронном виде (электронная тетрадь), в формате .xlsx

Контрольные вопросы

1. Дайте определения понятия «прогноз».

2. В чем состоит сущность прогнозирования?

3. Покажите роль прогнозирования в функционировании предприятия или организации при принятии управленческих решений.

4. Что может выступать в качестве объектов прогнозирования?

5. Укажите основные отличия и сходства прогноза и гипотезы; прогноза и плана. Как изменяется уровень определенности информации в зависимости от вида предсказания?

6. Определите признаки классификации прогнозов.

7. Опишите суть поискового и нормативного способов прогнозирования.

8. Назовите и дайте краткую характеристику основных подходов к прогнозированию.

9.Перечислите и укажите существенные особенности основных функций и принципов прогнозирования.

10.Выделите основные этапы процедуры прогнозирования.

11.Приведите основные типы прогнозов социально-экономических процессов.

12.Что такое метод прогнозировании?

13.Назовите характерные черты, присущие методам прогнозирования.

14.Какие бывают методы прогнозирования и в чем их отличия?

15.Каков общий алгоритм построения прогнозов?

16.Какие средства для построения математических моделей и прогнозирования состояния моделируемого объекта содержит Excel?

17.Что такое уравнение регрессии?

18.Какие виды регрессии различают? Приведите примеры.

19.Что показывает коэффициент детерминации?

20.В чем заключается задача построения регрессионной зависимости?

21.Какие графически средства Excel применяются для получения прогнозов?

22.Изложите процедуру построения линии тренда.

23.Можно ли, используя графические средства построения прогнозов, определить числовые величины прогнозных значений?

24.Какая статистическая функция применяется для нахождения коэффициентов линейной однофакторной регрессии?

25.С помощью какой функции можно найти прогнозные значения, если функция прогнозирования экспоненциально зависит от нескольких факторов?

26.Что такое доверительные интервалы? Изложите алгоритм построения доверительных интервалов.

27.Какие статистические функции используются для вычисления доверительных интервалов?

28.Существуют ли в Excel встроенные функции для выделения сезонной составляющей?

 


[1] Функция ДНЕЙ360возвращает количестводней между двумя датами на основе 360-дневного года (двеннадцать месяцев по 30 дней). Эта функция используется для расчета платежей, если системы бухгалтерского учета основанна на двенадцати 30-дневных месяцах

[2] Аннуитет – это одинаковые по сумме ежемесячные платежи

[3] увеличение, наращивание вкладов, происходящее регулярно в конце каждого расчетного периода.

 








Не нашли, что искали? Воспользуйтесь поиском по сайту:



©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.