Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ
ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ
САМАРСКИЙ МУНИЦИПАЛЬНЫЙ ИНСТИТУТ УПРАВЛЕНИЯ
(СМИУ)
Кафедра «Математических методов и информационных технологий»
Сахабиева Г.А., Орлова Л.В., Васяйчева В.А.
Математические методы анализа статистической информации
Методические указания
Самара 2008
Введение
Математические методы обработки информации уже давно применяются в самых разнообразных областях человеческой деятельности. Трудно назвать ту сферу общественно-политической жизни, в которой они бы не использовались. Но, пожалуй, ни в одной области знаний и практической деятельности обработка этой информации (так называемых статистических данных) не играет такой исключительно большой роли, как в экономике, имеющей дело с обработкой и анализом огромных массивов количественной информации о социально-экономических явлениях и процессах. Всесторонний и глубокий анализ статистических данных предполагает использование различных специальных математических методов, важное место среди которых занимает, например, корреляционно - регрессионный анализ.
Для достоверного отображения объективно существующих в экономике процессов необходимо выявить существенные взаимосвязи и не только выявить, но и дать им количественную оценку. Этот подход требует вскрытия причинных зависимостей. Под причинной зависимостью понимается такая связь между процессами, когда изменения, происходящие в одном из них являются следствием изменений, происходящих в другом.
Основными задачами корреляционного анализа являются оценка силы связи и проверка статистических гипотез о наличии и силе корреляционной зависимости между введенными для изучения процесса переменными (факторами и результатом). Не все факторы, влияющие
на экономические процессы, являются случайными величинами, поэтому при анализе экономических явлений обычно рассматриваются связи между случайными и неслучайными величинами. Такие связи называются регрессионными, а метод математической статистики,
их изучающий, называется регрессионным анализом.
Использование возможностей современной вычислительной техники, оснащенной пакетами программ машинной обработки информации, позволяет оперативно решать задачи изучения взаимосвязи показателей реальных процессов методами корреляционно-регрессионного анализа. При компьютерной обработке исходной информации, вычисление параметров применяемых математических функций и анализ статистических зависимостей являются быстро выполняемыми счетными операциями.
Корреляционно-регрессионный анализ считается одним из главных методов
в экономике, наряду с оптимизационными расчетами, а также математическим и
графическим моделированием трендов (тенденций).
Данная работа предназначена для изучения возможностей обработки статистических данных различными методами (в том числе, методами корреляционного и регрессионного анализа) с использованием пакета прикладных программ Microsoft Excel.
1. Корреляционно-регрессионный анализ.
Корреляционный анализ и регрессионный анализ являются смежными разделами математической статистики, и предназначаются для изучения по выборочным данным статистической зависимости ряда величин, некоторые из которых являются случайными. При статистической зависимости величины не связаны функционально, но как случайные величины заданы совместным распределением вероятностей. Исследование взаимосвязи случайных величин приводит к теории корреляции, как разделу теории вероятностей и математической статистики. Исследование зависимости случайных величин приводит к моделям регрессии и регрессионному анализу на базе выборочных данных. Теория вероятностей и математическая статистика представляют лишь инструмент для изучения статистической зависимости, но не ставят своей целью установление причинной связи. Представления и гипотезы о причинной связи основываются на некоторой другой теории, которая позволяет содержательно объяснить изучаемое явление.
Экономические данные почти всегда представлены в виде таблиц. Числовые данные, содержащиеся в таблицах, обычно имеют между собой явные (известные) или неявные (скрытые) связи.
Явно связаны показатели, которые получены методами прямого подсчета.
Связи же второго типа (неявные) заранее неизвестны. Однако необходимо уметь объяснять и предсказывать (прогнозировать) сложные явления для того, чтобы управлять ими. Поэтому специалисты с помощью наблюдений стремятся выявить скрытые зависимости и выразить их в виде формул, т. е. математически смоделировать явления или процессы. Математические модели строятся и используются для трех обобщенных
целей:
• для объяснения;
• для предсказания;
• для управления.
Представление экономических и других данных в электронных таблицах в наши дни стало простым и естественным. Оснащение же электронных таблиц средствами корреляционно-регрессионного анализа способствует тому, что из группы сложных, глубоко научных и потому редко используемых, корреляционно-регрессионный анализ превращается для специалиста в повседневный, эффективный и оперативный аналитический инструмент. Однако, в силу сложности метода, освоение его требует значительно больших знаний и усилий, чем освоение простых электронных таблиц.
Пользуясь методами корреляционно-регрессионного анализа, аналитики измеряют тесноту связей показателей с помощью коэффициента корреляции. При этом обнаруживаются связи, различные по силе (сильные, слабые, умеренные и др.) и различные по направлению (прямые, обратные и др.). Если связи окажутся существенными, то целесообразно будет найти их математическое выражение в виде регрессионной модели и оценить статистическую значимость модели. В экономике значимое уравнение используется, как правило, для прогнозирования изучаемого явления или показателя.
Регрессионный анализ называют основным методом современной математической статистики для выявления неявных и завуалированных связей между данными наблюдений в различных социально-экономических системах. Электронные таблицы делают такой анализ легко доступным.
Корреляционный анализ является одним из методов статистического анализа взаимосвязи нескольких признаков. Он определяется как метод, применяемый тогда, когда данные наблюдения можно считать случайными и выбранными из генеральной совокупности, распределенной по многомерному нормальному закону. Основная задача корреляционного анализа (являющаяся основной и в регрессионном анализе) состоит в оценке уравнения регрессии.
Корреляция – это статистическая зависимость между случайными величинами, не имеющими строго функционального характера, при которой изменение одной из случайных величин приводит к изменению математического ожидания другой.
1. Парная корреляция – связь между двумя признаками (результативным и факторным или двумя факторными).
2. Частная корреляция – зависимость между результативным и одним факторным признаками при фиксированном значении других факторных признаков.
3. Множественная корреляция – зависимость результативного и двух или более факторных признаков, включенных в исследование.
Корреляционный анализ имеет своей задачей количественное определение тесноты связи между двумя признаками (при парной связи) и между результативным признаком и множеством факторных признаков (при многофакторной связи).
Теснота связи количественно выражается величиной коэффициентов корреляции. Коэффициенты корреляции, представляя количественную характеристику тесноты связи между признаками, дают возможность определить «полезность» факторных признаков при построении уравнений множественной регрессии. Величина коэффициентов корреляции служит также оценкой соответствия уравнения регрессии выявленным причинно-следственным связям.
Общим условием, позволяющим получить более стабильные результаты при математико-статистическом исследовании социально-экономических процессов, является требование однородности исходной информации. Эта информация должна быть обработана на предмет аномальных, т.е. резко выделяющихся из массива данных, наблюдений. Процедура обработки информации выполняется за счет количественной оценки однородности совокупности по какому-либо одномерному или многомерному критерию (в зависимости от исходных данных). После обработки на предмет «аномальности» проводится проверка того, насколько оставшаяся информация удовлетворяет предпосылкам для использования статического аппарата при построении моделей, так как даже незначительные отступления от этих предпосылок часто сводят к нулю получаемый эффект. Следует иметь ввиду, что вероятностное или статистическое решение любой экономической задачи должно основываться на подробном осмыслении исходных математических понятий и предпосылок, корректности и объективности сбора исходной информации, в постоянном сочетании с теснотой связи экономического и математико-статистического анализа.
Для применения корреляционного анализа необходимо, чтобы все рассматриваемые переменные были случайными и имели нормальный закон распределения. Причем выполнение этих условий необходимо только при вероятностной оценке выявленной тесноты связи.
В состав Microsoft Excel входит набор средств анализа данных (так называемый пакет анализа), предназначенный для решения сложных статистических и инженерных задач. Для проведения анализа данных с помощью этих инструментов следует указать входные данные и выбрать параметры; анализ будет проведен с помощью подходящей статистической или инженерной макрофункции, а результат будет помещен в выходной диапазон. Другие средства Excel позволяют получить результаты анализа в графическом виде, позволяющем наглядно представить статистические данные, быстро выявить аномалии и отклонения и осуществить контроль полноты и достоверности исходного статистического материала. При одновременном изучении нескольких взаимосвязанных экономических явлений, графическое изображение позволяет установить существующие между ними соотношения и связи, различие и подобие, а также выявить особенности их изменений во времени.
Корреляционный анализ дает возможность установить ассоциированы ли наборы данных по величине, то есть: большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция); или, наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная корреляция); или данные двух диапазонов никак не связаны (корреляция близка к нулю).
Регрессия также является инструментом пакета анализа данных Microsoft Excel.. Линейный регрессионный анализ заключается в подборе графика для набора наблюдений с помощью метода наименьших квадратов. Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или более независимых переменных.
Наиболее сложным этапом, завершающим регрессионный анализ, является интерпретация полученных результатов, т.е. перевод их с языка статистики и математики на язык экономики. Интерпретация моделей регрессии осуществляется методами той отрасли знаний, к которой относятся исследуемые явления.
Всякая интерпретация начинается со статистической оценки уравнения регрессии в целом и оценки значимости входящих в модель факторных признаков, т.е. с изучения, как они влияют на величину результативного признака. Чем больше величина коэффициента регрессии, тем значительнее влияние данного признака на моделируемую обработку результата. Особое значение при этом имеет знак перед коэффициентом регрессии. Знаки коэффициентов регрессии говорят о характере влияния введенных факторов на результативный признак статистической обработки. Интерпретация этих знаков полностью определяется социально-экономическим содержанием моделируемого признака. Если его величина изменяется в сторону увеличения, то плюсовые знаки факторных признаков имеют положительное влияние. При изменении результативного признака в сторону снижения положительные значения имеют минусовые знаки факторных признаков. Если экономическая теория подсказывает, что факторный признак должен иметь положительное значение, а он получен со знаком минус, то необходимо проверить расчеты параметров уравнения регрессии.
В исследованиях широко применяются как парные, так и множественные регрессионные модели.
Лабораторная работа №1
Задание 1. При многократном измерении одной и той же физической величины Q получена серия из 24 результатов измерений Qi; . Исключить ошибки из результатов измерений. Исходные данные:
Таблица 1
|
№ измерения |
Qi |
№ измерения |
Qi |
№ измерения |
Qi |
№ измерения |
Qi |
|
1 |
482 |
7 |
483 |
13 |
483 |
19 |
483 |
|
2 |
485 |
8 |
483 |
14 |
483 |
20 |
482 |
|
3 |
486 |
9 |
481 |
15 |
483 |
21 |
481 |
|
4 |
486 |
10 |
480 |
16 |
483 |
22 |
481 |
|
5 |
483 |
11 |
492 |
17 |
484 |
23 |
483 |
|
6 |
483 |
12 |
486 |
18 |
484 |
24 |
495 |
Решение. 1). Определяем оценки результата измерения и среднего квадратического отклонения по формулам:
= ,
= ,
и проверяем с помощью Microsoft Excel следующим образом: на листе Excel записываем столбцы 1 и 2 таблицы 1:
Таблица 2
|
№ |
Qi |
||
|
1 |
482 |
-1,9583 |
3,8351 |
|
2 |
485 |
1,0417 |
1,0851 |
|
3 |
486 |
2,0417 |
4,1684 |
|
4 |
486 |
2,0417 |
4,1684 |
|
5 |
483 |
-0,9583 |
0,9184 |
|
6 |
483 |
-0,9583 |
0,9184 |
|
7 |
483 |
-0,9583 |
0,9184 |
|
8 |
483 |
-0,9583 |
0,9184 |
|
9 |
481 |
-2,9583 |
8,7517 |
|
10 |
480 |
-3,9583 |
15,6684 |
|
11 |
492 |
8,0417 |
64,6684 |
|
12 |
486 |
2,0417 |
4,1684 |
|
13 |
483 |
-0,9583 |
0,9184 |
|
14 |
483 |
-0,9583 |
0,9184 |
|
15 |
483 |
-0,9583 |
0,9184 |
|
16 |
483 |
-0,9583 |
0,9184 |
|
17 |
484 |
0,0417 |
0,0017 |
|
18 |
484 |
0,0417 |
0,0017 |
|
19 |
483 |
-0,9583 |
0,9184 |
|
20 |
482 |
-1,9583 |
3,8351 |
|
21 |
481 |
-2,9583 |
8,7517 |
|
22 |
481 |
-2,9583 |
8,7517 |
|
23 |
483 |
-0,9583 |
0,9184 |
|
24 |
495 |
11,0417 |
121,9184 |
|
483,9583 |
|||
|
258,9582 |
а). Выделяем ячейку для искомого значения (щелчком по левой кнопке), выбираем значок fx на панели инструментов, затем находим функцию Статистические - СРЗНАЧ в окне ниспадающего меню, нажимаем OK, в поле Число1 заносим столбец значений х с помощью мыши, нажимаем OK.
b). Для удобства вычисления среднего квадратического отклонения результата измерения добавим в таблицу столбцы 3 и 4. Для этого выделяем ячейку в строке рядом со значением Q1, в выделенной ячейке ставим знак “ = “, щелкаем мышью по ячейке, содержащей значение Q1, знак « - », щелкаем по ячейке, содержащей , Enter. В строке формул выделяем знаком «$» букву номера ячейки, содержащей , и протягивая мышью до последней строки, заполняем остальные ячейки третьего столбца.
Четвертый столбец заполняется по той же схеме: в выбранной ячейке ставим знак “ = “, щелкаем мышью по ячейке, содержащей значение Q1 -, знак « * », щелкаем по ячейке, содержащей Q1 -, Enter. Протягивая мышью до последней строки, заполняем остальные ячейки четвертого столбца.
с). Суммируем все значения (Qi -)2 – содержимое ячеек четвертого столбца, используя значок ∑ (автосумма) на панели инструментов. Результат делим на 24 и используя значок fx на панели инструментов, находим функцию Математические – КОРЕНЬ, получаем:
2. Чтобы обнаружить и исключить ошибки результатов измерений:
– вычисляем наибольшее по абсолютному значению нормированное отклонение
– задаемся доверительной вероятностью P = 0,95 и из соответствующих таблиц с учетом q = 1 – P находим соответствующее ей теоретическое (табличное) значение :
при n = 24;
– сравниваем и : . Это означает, что результат измерения Q24 является ошибочным, он должен быть отброшен.
Необходимо повторить вычисления согласно п.п. 1 и 2 для сокращенной серии результатов измерений и проводить их до тех пор, пока не будет выполняться условие для всех результатов измерений.
Повторяем вычисления, отбросив измерение №24. Получим согласно таблице 3:
Таблица 3
|
№ измерения |
Qi |
||
|
1 |
482 |
-1,4783 |
2,1853 |
|
2 |
485 |
1,5217 |
2,3157 |
|
3 |
486 |
2,5217 |
6,3592 |
|
4 |
486 |
2,5217 |
6,3592 |
|
5 |
483 |
-0,4783 |
0,2287 |
|
6 |
483 |
-0,4783 |
0,2287 |
|
7 |
483 |
-0,4783 |
0,2287 |
|
8 |
483 |
-0,4783 |
0,2287 |
|
9 |
481 |
-2,4783 |
6,1418 |
|
10 |
480 |
-3,4783 |
12,0983 |
|
11 |
492 |
8,5217 |
72,6200 |
|
12 |
486 |
2,5217 |
6,3592 |
|
13 |
483 |
-0,4783 |
0,2287 |
|
14 |
483 |
-0,4783 |
0,2287 |
|
15 |
483 |
-0,4783 |
0,2287 |
|
16 |
483 |
-0,4783 |
0,2287 |
|
17 |
484 |
0,5217 |
0,2722 |
|
18 |
484 |
0,5217 |
0,2722 |
|
19 |
483 |
-0,4783 |
0,2287 |
|
20 |
482 |
-1,4783 |
2,1853 |
|
21 |
481 |
-2,4783 |
6,1418 |
|
22 |
481 |
-2,4783 |
6,1418 |
|
23 |
483 |
-0,4783 |
0,2287 |
|
Σ |
0 |
131,7391 |
Имеем:
при n = 23;
Сравниваем и : . Отбрасываем измерение №11 и повторяем вычисления. Для n = 22 аналогично получим:
, , , при n = 22;
Сравниваем и . Так как , то результат измерения №10 не является ошибочным. Следовательно, окончательно остается 22 измерения, т.е. n = 22.
Задание 2. При многократных измерениях одной и той же величины получены две серии по 12 результатов измерений в каждой. Эти результаты после внесения поправок представлены в таблице 5. Исключить ошибки из результатов измерений.
Исходные данные:
Таблица 4
|
Серия 1 |
Серия 2 |
||||||
|
№ измерения |
Результат измерения |
№ измерения |
Результат измерения |
№ измерения |
Результат измерения |
№ измерения |
Результат измерения |
|
1 |
482 |
7 |
483 |
1 |
483 |
7 |
483 |
|
2 |
485 |
8 |
483 |
2 |
483 |
8 |
482 |
|
3 |
486 |
9 |
481 |
3 |
483 |
9 |
481 |
|
4 |
486 |
10 |
480 |
4 |
483 |
10 |
481 |
|
5 |
483 |
11 |
492 |
5 |
484 |
11 |
483 |
|
6 |
483 |
12 |
486 |
6 |
484 |
12 |
495 |
Решение.1). Обрабатываем экспериментальные данные по алгоритму, изложенному в п.п. 1,2 задания 1, при этом:
– определяем оценки результата измерения и среднеквадратического отклонения ,
j =1,2, используя таблицу 5 и этапы a) – c) :
Таблица 5
|
Серия 1 |
Серия 2 |
||||||
|
№ из-мерения |
Q1i |
№ из-мерения |
Q2i |
||||
|
1 |
482 |
-2,1667 |
4,6944 |
1 |
483 |
-0,7500 |
0,5625 |
|
2 |
485 |
0,8333 |
0,6944 |
2 |
483 |
-0,7500 |
0,5625 |
|
3 |
486 |
1,8333 |
3,3611 |
3 |
483 |
-0,7500 |
0,5625 |
|
4 |
486 |
1,8333 |
3,3611 |
4 |
483 |
-0,7500 |
0,5625 |
|
5 |
483 |
-1,1667 |
1,3611 |
5 |
484 |
0,2500 |
0,0625 |
|
6 |
483 |
-1,1667 |
1,3611 |
6 |
484 |
0,2500 |
0,0625 |
|
7 |
483 |
-1,1667 |
1,3611 |
7 |
483 |
-0,7500 |
0,5625 |
|
8 |
483 |
-1,1667 |
1,3611 |
8 |
482 |
-1,7500 |
3,0625 |
|
9 |
481 |
-3,1667 |
10,0278 |
9 |
481 |
-2,7500 |
7,5625 |
|
10 |
480 |
-4,1667 |
17,3611 |
10 |
481 |
-2,7500 |
7,5625 |
|
11 |
492 |
7,8333 |
61,3611 |
11 |
483 |
-0,7500 |
0,5625 |
|
12 |
486 |
1,8333 |
3,3611 |
12 |
495 |
11,2500 |
126,5625 |
|
Σ |
0 |
109,6667 |
Σ |
0 |
148,2500 |
, ;
, ;
– обнаруживаем и исключаем ошибки:
, ;
при n = 12;
– сравниваем и с : и . Результаты измерения Q1,11 и Q2,12 являются ошибочными, они должны быть отброшены.
Повторяем вычисления, при этом отбрасываем измерения 11 из серии №1 и 12 из серии №2 и используем таблицу 6:
, ;
;
Таблица 6
|
Серия 1 |
Серия 2 |
||||||
|
№ из-мерения |
Q1i |
№ из-мерения |
Q2i |
||||
|
1 |
482 |
-1,4545 |
2,1157 |
1 |
483 |
0,2727 |
0,0744 |
|
2 |
485 |
1,5455 |
2,3884 |
2 |
483 |
0,2727 |
0,0744 |
|
3 |
486 |
2,5455 |
6,4793 |
3 |
483 |
0,2727 |
0,0744 |
|
4 |
486 |
2,5455 |
6,4793 |
4 |
483 |
0,2727 |
0,0744 |
|
5 |
483 |
-0,4545 |
0,2066 |
5 |
484 |
1,2727 |
1,6198 |
|
6 |
483 |
-0,4545 |
0,2066 |
6 |
484 |
1,2727 |
1,6198 |
|
7 |
483 |
-0,4545 |
0,2066 |
7 |
483 |
0,2727 |
0,0744 |
|
8 |
483 |
-0,4545 |
0,2066 |
8 |
482 |
-0,7273 |
0,5289 |
|
9 |
481 |
-2,4545 |
6,0248 |
9 |
481 |
-1,7273 |
2,9835 |
|
10 |
480 |
-3,4545 |
11,9339 |
10 |
481 |
-1,7273 |
2,9835 |
|
11 |
486 |
2,5455 |
6,4793 |
11 |
483 |
0,2727 |
0,0744 |
|
Σ |
0 |
42,7273 |
Σ |
0 |
10,1818 |
; ;
при n = 11;
Сравниваем и с : и . Результаты измерений 10 из серии №1 и 9 из серии №2 не являются ошибочными и окончательно остается 11 измерений для обеих серий, т.е. n = 11.
Считая результаты измерений распределенными нормально с вероятностью ,
проверим значимость различия средних арифметических серий. Для этого:
– вычисляем моменты закона распределения разности:
, n1 = n2 = n
Задавшись доверительной вероятностью P = 0,95, определяем из таблицы интегральной функции нормированного нормального распределения Ф(t) значение t
t = 1,645
и сравниваем с , при : <. Различия между средними арифметическими в сериях с доверительной вероятностью P можно признать незначимыми.
3. Обрабатываем совместно результаты измерения обеих серий с учетом весовых коэффициентов:
1) определяем оценки результата измерения и среднеквадратического отклонения S
,
,
.
2) задавшись доверительной вероятностью P = 0,95, определяем по таблице t = 1,96 и доверительный интервал:
,
,
.
Лабораторная работа №2
Вычисление структурных средних величин (СВ).
Задание. Создайте лист Microsoft Excel. Заполните ранжированный ряд из следующих чисел: 20, 22, 90, 20, 26. По этому ряду необходимо найти медиану, моду, среднее арифметическое значение.
1. Медиана – середина ранжированного ряда, т.е. это значение признака х, которое делит ранжированный ряд пополам.
Выберите из списка функций Статистические – МЕДИАНА, нажмите ОК. В поле Число 1, выберите интервал вашего ряда, нажмите Enter, а затем ОК. Полученное значение медианы должно быть равным 22.
2. Мода – наиболее часто встречающееся значение х в ряду.
Выберите из списка функций Статистические – МОДА, нажмите ОК. В поле Число 1, выберите интервал вашего ряда, нажмите Enter, а затем ОК. Полученное значение моды должно быть равным 20.
3. Среднее арифметическое значение: выбираем из списка функций Статистические – СРЗНАЧ, нажмите ОК. В поле Число 1, выберите интервал вашего ряда, нажмите Enter, а затем ОК. Полученное значение средней арифметической должно быть равным 35,6.
Упражнение. Дан ранжированный ряд 20, 20, 22, 26, 90, 80. Найти медиану, моду, среднее арифметическое.
Ответы: Медиана = 24, Мода = 20, Среднее арифметическое значение = 43.
Выполните задание по ранжированному ряду: 20+к, 22+к, 90+к, 20+к, 26+к, к – номер фамилии студента в списке группы.
Лабораторная работа №3
Модель парной регрессии
Используя исходные данные, построить модель парной регрессии для изучения зависимости х от у, построить диаграмму рассеяния.
Исходные данные представлены в таблице.
|
Номер наблюдения |
х |
у |
|
1 |
-2,54 |
-5,31 |
|
2 |
26,65 |
16,84 |
|
3 |
4,44 |
0,07 |
|
4 |
17,12 |
10,03 |
|
5 |
10,19 |
4,98 |
|
6 |
13,88 |
7,52 |
|
7 |
4,55 |
0,23 |
|
8 |
10,28 |
5,3 |
|
9 |
11,76 |
5,94 |
|
10 |
11,89 |
6,09 |
|
11 |
5,14 |
0,93 |
|
12 |
7,7 |
3,22 |
|
13 |
7,17 |
2,08 |
|
14 |
7,57 |
2,81 |
|
15 |
17,46 |
10,73 |
Ход работы
” = “, щелкаем мышью по ячейке, содержащей первое значение в выборке х, ставим знак “ * “, далее щелкаем мышью по ячейке, содержащей первое значение в выборке у, нажимаем Enter. Удерживая левую кнопку мыши, протягиваем вниз еще на 14 ячеек, получая тем самым произведения соответствующих значений х и у.
b = , (1)
а = (2)
следующим образом: выделяем ячейку, ставим знак “ = “, щелкаем мышью по ячейке, содержащей значение, полученное в пункте 7) , ставим знак “ – “, щелкаем мышью по ячейке, содержащей значение, полученное в пункте 5), заключаем в скобки данное выражение, ставим знак “ / “, щелкаем мышью по ячейке, содержащей значение, полученное в пункте 3) , ставим знак “ – “, щелкаем мышью по ячейке, содержащей значение, полученное в пункте 9) , заключаем в скобки, нажимаем Enter. Таким образом, мы получили коэффициент b.
Выделяем ячейку, в выделенной ячейке ставим знак “ = “, щелкаем мышью по ячейке, содержащей значение , ставим знак “ – “, щелкаем мышью по ячейке, содержащей значение коэффициента b, ставим знак “ * “, щелкаем мышью по ячейке, содержащей значение , нажимаем Enter. Получили коэффициент а.
Пример оформления работы на листе Excel.
|
t |
Y |
X |
X*Y |
xср*yср |
xср*yср*t |
x^2 |
||
|
1 |
-2,54 |
-5,31 |
y среднее |
х среднее |
13,4874 |
48,6754 |
730,131 |
28,196 |
|
2 |
26,65 |
16,84 |
10,21733 |
4,764 |
448,786 |
283,59 |
||
|
3 |
4,44 |
0,07 |
0,3108 |
0,0049 |
||||
|
4 |
17,12 |
10,03 |
171,7136 |
100,6 |
||||
|
5 |
10,19 |
4,98 |
х ср^2 |
50,7462 |
24,8 |
|||
|
6 |
13,88 |
7,52 |
22,6957 |
104,3776 |
56,55 |
|||
|
7 |
4,55 |
0,23 |
1,0465 |
0,0529 |
||||
|
8 |
10,28 |
5,3 |
54,484 |
28,09 |
||||
|
9 |
11,76 |
5,94 |
69,8544 |
35,284 |
||||
|
10 |
11,89 |
6,09 |
72,4101 |
37,088 |
||||
|
11 |
5,14 |
0,93 |
4,7802 |
0,8649 |
||||
|
12 |
7,7 |
3,22 |
24,794 |
10,368 |
||||
|
13 |
7,17 |
2,08 |
14,9136 |
4,3264 |
||||
|
14 |
7,57 |
2,81 |
21,2717 |
7,8961 |
||||
|
15 |
17,46 |
10,73 |
187,3458 |
115,13 |
||||
|
Сумма |
1240,322 |
Сумма |
732,84 |
|||||
|
b |
1,30016 |
Модель парной регрессии y = 4,02 + 1,3*x |
||||||
|
a |
4,02337 |
|||||||
Лабораторная работа №4
Проверка адекватности модели парной регрессии
Задание:
2. Проверить значимость коэффициента детерминации на основании F – теста.
Основная формула
Σ (yt - )2 = Σ (yэмп - )2 + Σ (yt-yэмп)2
или
TSS = ESS + RSS ,
где TSS – полная сумма квадратов ;
ESS - сумма квадратов, объясненная моделью ;
RSS – остаточная сумма квадратов.
Ход работы
1). Находим эмпирическое значение yэмп величины y: выделяем ячейку, в выделенной ячейке ставим знак =, щелкаем мышью по ячейке, содержащей значение а , ставим в строке формул знак $ перед буквой и цифрой, чтобы зафиксировать значение, ставим знак +, щелкаем мышью по ячейке, содержащей значение b ,снова ставим в верхней строке формулы знак $ перед буквой и перед цифрой, чтобы зафиксировать значение, нажимаем знак *, щелкаем мышью по ячейке, содержащей значение x , нажимаем Enter. Удерживая левую кнопку мыши, протягиваем вниз, чтобы получилось 15 ячеек. Нашли 15 значений yэмп, соответствующие значениям х.
2). Находим ESS: выделяем одну ячейку, в выделенной ячейке ставим знак «=», открываем скобку, щелкаем мышью по ячейке, содержащей первое значение yэмп , ставим знак « - », далее щелкаем мышью по ячейке, содержащей среднее значение , фиксируем его в строке формул знаком $ перед буквой и перед цифрой, закрываем скобку, и умножаем на аналогичную скобку, так как нам нужен её квадрат, нажимаем Enter. Удерживая левую кнопку мыши, протягиваем вниз еще на 14 ячеек. Затем находим сумму получившихся значений: щелкаем по ячейке, находящейся ниже этих значений, ставим знак =, выбираем значок ∑ (автосумма) на панели инструментов, и выделяем с помощью мыши все получившиеся значения произведений, нажимаем Enter.
3). Находим TSS: делаем все то же самое что и в пункте 2) , только вычитаем среднее значение не от yэмп , а от y .
4). Находим RSS: выделяем 1 ячейку, в выделенной ячейке ставим знак «=», открываем скобку, щелкаем мышью по ячейке, содержащей первое значение y , ставим знак «-», далее щелкаем мышью по ячейке, содержащей значение yэмп , закрываем скобку и умножаем на аналогичную скобку, нажимаем Enter. Удерживая левую кнопку мыши, протягиваем вниз еще на 14 ячеек. Находим сумму получившихся значений: щелкаем по ячейке, находящейся ниже этих значений, ставим знак =, выбираем значок ∑ (автосумма) на панели инструментов, и выделяем с помощью мыши все получившиеся значения произведений, нажимаем Enter.
5). Находим коэффициент детерминации по первой формуле:
R2 = ESS/TSS.
Выделяем ячейку, ставим знак «=», щелкаем мышью по ячейке, содержащей значение суммы ESS, ставим знак « / », щелкаем мышью по ячейке, содержащей значение суммы TSS, нажимаем Enter.
6). Находим коэффициент детерминации по второй формуле:
R2 = 1- (RSS/TSS).
Выделяем ячейку, ставим знак «=»,нажимаем 1, ставим знак «-», затем открываем скобку , щелкаем мышью по ячейке, содержащей значение суммы RSS, ставим знак « / », щелкаем мышью по ячейке, содержащей значение суммы TSS, закрываем скобку, нажимаем Enter.
7). Находим коэффициент детерминации по третьей формуле :
R2 = r(y, yэмп )2 ,
где
r(y, yэмп )2 =cov(y, yэмп) / (var(y)*var(yэмп)) ,
cov(y, yэмп)= 1/( t-1)* Σ (y- )* (yэмп-).
Для этого выполняем промежуточные действия :
1). Находим аналогичноиз первого задания.
2). Находим сумму из формулы ковариации: выделяем ячейку, в выделенной ячейке ставим знак «=», открываем скобку, щелкаем мышью по ячейке, содержащей первое значение y , ставим знак « - », далее щелкаем мышью по ячейке, содержащей значение , фиксируем его в верхней строке формулы знаком $ перед буквой и перед цифрой, закрываем скобку, ставим знак «*», открываем скобку, щелкаем мышью по ячейке, содержащей первое значение yэмп , ставим знак « - », далее щелкаем мышью по ячейке, содержащей значение yэмп и фиксируем его в верхней строке формулы знаком $ перед буквой и перед цифрой, закрываем скобку, нажимаем Enter. Удерживая левую кнопку мыши, протягиваем вниз еще на 14 ячеек. Находим сумму получившихся значений: щелкаем по ячейке, находящейся ниже этих значений, ставим знак «=», выбираем значок ∑ (автосумма) на панели инструментов, и выделяем с помощью мыши все получившиеся значения произведений, нажимаем Enter.
3). Находим ковариацию. Выделяем ячейку, ставим знак «=» , нажимаем 1, ставим знак « / » на клавиатуре набираем 14, [так как t =15 , в формуле t-1], щелкаем мышью по ячейке, содержащей значение суммы из пункта 2), нажимаем Enter.
4). Находим var- дисперсию y и yэмп . Выделяем ячейку, ставим знак «=», выбираем значок fx на панели инструментов, затем находим ДИСП в окне меню, нажимаем OK, выделяем область по y, которая заносится в поле Число 1.Нажимаем OK. Аналогично находим дисперсию для величины yэмп .
5). Находим произведение дисперсий. Выделяем ячейку, ставим знак «=», щелкаем мышью по ячейке, содержащей значение дисперсии y , ставим знак «*», далее щелкаем мышью по ячейке, содержащей значение дисперсии yэмп , нажимаем Enter.
6). Извлекаем корень из произведения дисперсий. Выделяем ячейку, ставим знак «=», выбираем значок fx на панели инструментов, затем находим КОРЕНЬ в окне меню, нажимаем OK, выделяем ячейку со значением из пункта 5), которая заносится в поле. Нажимаем OK.
7). Находим коэффициент корреляции r. Выделяем ячейку, ставим знак «=», щелкаем мышью по ячейке, содержащей значение ковариации из пункта 3) , ставим знак « / », далее щелкаем мышью по ячейке, содержащей значение корня из пункта 6) , нажимаем Enter.
8). Находим коэффициент детерминации по третьей формуле. Выделяем ячейку, ставим знак «=», щелкаем мышью по ячейке, содержащей значение коэффициента корреляции r из пункта 7) , ставим знак «*», и щелкаем по той же ячейке, нажимаем Enter.
8). Проверяем адекватность модели с помощью F- теста. Вычисляем значение F-критерия на основе формулы
F = R2/((1- R2)/(t-2)) ,
где t =15- число наблюдений.
Выделяем ячейку, ставим знак «=», щелкаем мышью по ячейке, содержащей значение коэффициента детерминации R2 из пункта 5) , ставим знак « / », ставим скобки (( , затем 1, ставим знак «-», и щелкаем по той же ячейке R2, первую скобку закрываем , ставим знак « / », число 13, вторую скобку закрываем, нажимаем Enter.
9). Находим F табличное для уровней значимости 0,05 и 0,01.
Если F , полученное в пункте 8), больше F табличного для данного уровня значимости, то нулевая гипотеза H0 отклоняется на этом уровне значимости.
Варианты для парной регрессионной модели
Вариант № 1
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
162 |
151 |
190 |
178 |
161 |
175 |
144 |
191 |
160 |
161 |
|
|
95 |
107 |
125 |
111 |
89 |
97 |
95 |
131 |
92 |
102 |
Вариант № 2
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
131 |
124 |
152 |
150 |
139 |
157 |
129 |
160 |
135 |
153 |
|
|
85 |
91 |
115 |
111 |
94 |
115 |
95 |
130 |
90 |
122 |
Вариант № 3
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
118 |
111 |
126 |
114 |
118 |
129 |
126 |
122 |
105 |
113 |
|
|
98 |
98 |
118 |
103 |
95 |
121 |
99 |
114 |
93 |
107 |
Вариант № 4
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
120 |
112 |
133 |
123 |
126 |
140 |
131 |
133 |
114 |
120 |
|
|
88 |
87 |
110 |
101 |
93 |
118 |
93 |
111 |
93 |
102 |
Вариант № 5
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
130 |
122 |
145 |
134 |
137 |
152 |
141 |
144 |
124 |
132 |
|
|
91 |
90 |
113 |
104 |
97 |
121 |
96 |
114 |
97 |
106 |
Вариант № 6
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
124 |
117 |
138 |
128 |
131 |
145 |
136 |
138 |
119 |
125 |
|
|
95 |
94 |
117 |
108 |
101 |
125 |
101 |
119 |
101 |
110 |
Вариант № 7
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
137 |
128 |
156 |
148 |
149 |
162 |
152 |
156 |
134 |
142 |
|
|
89 |
87 |
113 |
108 |
100 |
119 |
97 |
115 |
97 |
106 |
Вариант № 8
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
147 |
138 |
164 |
157 |
160 |
175 |
163 |
168 |
145 |
154 |
|
|
99 |
97 |
120 |
115 |
110 |
130 |
107 |
125 |
107 |
99 |
Вариант № 9
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
118 |
130 |
159 |
138 |
140 |
152 |
158 |
145 |
141 |
127 |
|
|
69 |
87 |
112 |
95 |
88 |
105 |
100 |
100 |
101 |
99 |
Вариант № 10
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
107 |
97 |
115 |
106 |
130 |
135 |
114 |
107 |
108 |
117 |
|
|
59 |
55 |
73 |
66 |
83 |
95 |
59 |
65 |
72 |
83 |
Вариант № 11
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
117 |
108 |
125 |
118 |
137 |
130 |
141 |
107 |
108 |
116 |
|
|
67 |
65 |
82 |
77 |
90 |
87 |
88 |
61 |
69 |
79 |
Вариант № 12
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
134 |
125 |
143 |
136 |
156 |
148 |
160 |
124 |
126 |
125 |
|
|
78 |
76 |
93 |
88 |
101 |
98 |
99 |
72 |
80 |
80 |
Вариант № 13
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
129 |
121 |
139 |
132 |
152 |
144 |
156 |
119 |
121 |
119 |
|
|
79 |
77 |
94 |
89 |
102 |
99 |
100 |
73 |
81 |
79 |
Вариант № 14
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
136 |
129 |
139 |
140 |
154 |
145 |
160 |
154 |
139 |
137 |
|
|
67 |
67 |
74 |
77 |
83 |
79 |
83 |
90 |
80 |
79 |
Вариант № 15
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
150 |
129 |
133 |
145 |
141 |
147 |
146 |
132 |
134 |
145 |
|
|
87 |
73 |
74 |
88 |
76 |
87 |
75 |
74 |
81 |
93 |
Лабораторная работа №5
Планирование правовой, производственно-хозяйственной, управленческой и административной деятельности приводит к задачам, имеющим множество допустимых решений. Из этого множества решений нужно выбрать такое, которое бы оптимальным образом учитывало внутренние возможности и внешние условия для хозяйствующего или управляющего субъекта (выбор производственной программы, прикрепление к поставщикам, маршрутизация, раскрой материалов, приготовление смесей и т.д.).
Суть принципа оптимальности состоит в стремлении выбрать такое планово-управленческое решение , где – его компоненты, которое наилучшим образом учитывало бы внутренние возможности и внешние условия производственной деятельности хозяйствующего субъекта. С этой целью нужно выбрать некоторый критерий оптимальности экономического или правового показателя, позволяющего сравнивать эффективность тех или иных планово-управленческих решений («максимум прибыли», «минимум затрат»,
«максимум рентабельности» и т.д.). При этом выбор планово-управленческого решения осуществляется из некоторой области возможных (допустимых) решений D; эту область называют также областью определения задачи.
На практике принцип оптимальности в планировании и управлении означает решить экстремальную задачу об отыскании максимума или минимума функции
при ограничениях
Вектор называется допустимым решением, или планом задачи оптимального программирования, если он удовлетворяет системе ограничений. А то допустимое решение , которое доставляет максимум или минимум целевой функции , называется оптимальным планом (решением) задачи.
Программирование – это процесс распределения ресурсов.
Математическое программирование - это использование математических моделей и методов для решения проблем программирования.
Если целевая функция является линейной, а система ограничений на ресурсы представляет собой систему линейных взаимосвязей между переменными, то такая задача является задачей линейного программирования.
Стандартом предусмотрено, что октановое число автомобильного бензина А-76 должно быть не ниже 76, а содержание серы в нем – не более 0,3%. Для изготовления такого бензина на заводе используется смесь из четырех компонентов. Данные о ресурсах смешиваемых компонентов, их себестоимости и их октановом числе, а также о содержании серы приведены в таблице
|
Характеристика |
Компонент автомобильного бензина |
|||
|
№ 1 |
№2 |
№ 3 |
№4 |
|
|
Октановое число |
68 |
72 |
80 |
90 |
|
Содержание серы, % |
0,35 |
0,35 |
0,3 |
0,2 |
|
Ресурсы, т |
700 |
600 |
500 |
300 |
|
Себестоимость, у.е./т |
40 |
45 |
60 |
90 |
Приказом директора завода-изготовителя установлен следующий расход каждого компонента: 1 – 550 т, 2 – 10 т, 3 – 150 т, 4 – 290 т. Требуется определить, сколько на самом деле тонн каждого компонента следует использовать для получения 1000 т автомобильного бензина А-76, чтобы его себестоимость была минимальной. Какова упущенная выгода предприятия при производстве каждых 1000 т бензина при таком решении дирекции?
Ход работы
Пусть – количество в смеси компонента с номером i. С учетом этих обозначений задача минимума себестоимости принимает вид
Первое функциональное ограничение отражает необходимость получения заданного количества смеси (1000 т), второе и третье – ограничения по октановому числу и содержанию серы в смеси, остальные – ограничения на имеющиеся объемы соответствующих ресурсов (компонентов). Прямые ограничения очевидны, но принципиально важны для выбора метода решения. Для решения задачи средствами Excel необходимо составить таблицу.
|
Решение задачи о смесях средствами Excel |
|||||
|
Переменные |
Зна-чения |
Критерий и |
Результаты расчетов |
Знак отношения |
Ресурс |
|
X1 |
0 |
Целевая функция |
=40*B3+45*B4+60*B5+90*B6 |
||
|
X2 |
0 |
Ограничение 1 |
=СУММ(B3:B6) |
= |
1000 |
|
X3 |
0 |
Ограничение 2 |
=68*B3+72*B4+80*B5+90*B6 |
=> |
76000 |
|
X4 |
0 |
Ограничение 3 |
=0,35*B3+0,35*B4+0,3*B5+0,2*B6 |
<= |
300 |
|
Ограничение 4 |
=B3 |
<= |
700 |
||
|
Ограничение 5 |
=B4 |
<= |
600 |
||
|
Ограничение 6 |
=B5 |
<= |
500 |
||
|
Ограничение 7 |
=B6 |
<= |
300 |
Для решения задачи средствами Excel нужно воспользоваться программой-надстройкой Поиск решения, расположенной в пункте меню Сервис.
В открывшемся диалоговом окне следует установить:
Добавления, изменения и удаления ограничений производятся с помощью кнопок Добавить, Изменить, Удалить. Кнопка Параметры открывает окно, в котором следует установить флажок Неотрицательные решения. Для нахождения оптимального решения следует нажать кнопку Выполнить.
Диалоговое окно Результаты поиска решения позволяет:
Если щелкнуть по кнопке ОК, то на месте исходной таблицы получим таблицу с найденными оптимальными значениями.
Оптимальное решение задачи имеет вид:
.
Решение дирекции:
.
Таким образом упущенная выгода предприятия при производстве каждых 1000 т бензина при таком решении дирекции составляет 407 у.е..
Лабораторная работа №6
Модель Леонтьева
Имеется n отраслей. Рассматривается процесс производства за один год. Обозначим через хi общий (валовой) объём продукции i - той отрасли, хij - объём продукции i - той отрасли, потребляемый j – той отраслью в процессе производства, уi - объём конечного продукта i - той отрасли для непроизводственного потребления. Выполняются соотношения баланса:
хi = Σ хij + уi , i = 1,…,n, j = 1,…,n.
Коэффициенты прямых затрат
аij = хij / хj
показывают затраты продукции i - той отрасли на производство единицы продукции j – той отрасли. Считаем, что аij = const . Тогда хij = аij . хj , т.е.
хi = Σ аij . хj + уi .
Если обозначить вектор валового выпуска через
,
матрицу прямых затрат через
А = ,
а вектор конечного продукта через
,
то получаем матричное уравнение: Х = АХ + У.
Матрица А ≥ 0 (все элементы неотрицательны) называется продуктивной, если для любого вектора У ≥ 0 существует решение Х ≥ 0 этого уравнения. Модель Леонтьева в этом случае также называется продуктивной.
Условие продуктивности матрицы А : наибольшая из сумм элементов в столбцах матрицы А не превосходит 1, причем в А есть хотя бы один столбец, сумма элементов которого меньше 1.
Задание: Выяснить продуктивность модели Леонтьева, учитывая схему:
Ход работы
По условию х1 = 400, х2 = 500, у1 =150, у2 = 250, х11 = 50, х12 = 200, х21 = 100,
х22 = 150 .
1). Находим элементы аij = хij / хj матрицы А средствами Excel:
А = А = .
2). Вычисляем максимум сумм элементов в столбцах матрицы А:
max (0,125 + 0,25; 0,4 + 0,3) = 0,7 < 1 А - продуктивная матрица.
3). Пусть новый вектор валового выпуска
Х = ,
тогда из равенства Х = АХ + У получаем формулу для определения соответствующего вектора конечного продукта:
У = Х – АХ = (Е – А)Х =
Замечание: действия с матрицами выполняем средствами Excel. Для выполнения вычитания матриц, стоящих в скобках, необходимо разместить обе матрицы в некоторых диапазонах и определить диапазон для размещения результата вычислений. Затем в левую верхнюю ячейку новой матрицы ввести формулу для вычисления (например, для вычитания матриц
« =A1- A5» и скопировать ее методом протягивания в остальные ячейки диапазона, отведенного под результат. Для выполнения умножения матриц нужно выделить на рабочем листе блок ячеек под матрицу – произведение . Размер этой матрицы в данном случае будет 2×1. Затем следует воспользоваться функцией МУМНОЖ из категории Математические. В качестве аргументов этой функции указать диапазоны перемножаемых матриц и нажать сочетание клавиш CTRL+SHIFT+ENTER.
4). Зададим новый вектор конечного продукта
У =
и найдем соответствующий вектор валового выпуска
Х = (Е – А)-1∙ У = .
Матрица (Е – А)-1 называется матрицей полных затрат и определяется так: на рабочем листе нужно выделить блок ячеек под обратную матрицу и воспользоваться встроенной функцией МОБР из категории Математические. В качестве аргумента этой функции указать диапазон исходной матрицы и нажать сочетание клавиш CTRL+SHIFT+ENTER.
Варианты на решение задачи о продуктивности модели Леонтьева.
|
201 |
202 |
203 |
204 |
205 |
206 |
207 |
208 |
209 |
210 |
|
|
х1 |
500 |
400 |
200 |
600 |
700 |
800 |
900 |
700 |
600 |
200 |
|
х2 |
700 |
900 |
300 |
300 |
400 |
300 |
300 |
600 |
900 |
900 |
|
х11 |
50 |
30 |
45 |
20 |
75 |
85 |
150 |
120 |
200 |
100 |
|
х12 |
100 |
40 |
90 |
80 |
125 |
95 |
70 |
150 |
400 |
75 |
|
х21 |
60 |
70 |
100 |
70 |
200 |
170 |
40 |
80 |
20 |
50 |
|
х22 |
90 |
120 |
70 |
100 |
150 |
100 |
120 |
75 |
125 |
120 |
Лабораторная работа №6
Фильтрация списков.
Задание: При работе с базами данных важно иметь возможность быстро находить нужную информацию. Допустим, у вас имеется список телефонных номеров или таблица ассортимента товаров, и вам требуется, например, выбрать номера одной АТС или все коды моделей факсов. Конечно же, вам не захочется просматривать весь список и выписывать необходимую информацию. С помощью имён в полях вашего списка вы можете автоматически отфильтровать и вывести на экран только нужные вам данные.
Ход работы
1. Подготовьте данные к фильтрации. Создайте таблицу Ассортимент. Для этого вам необходимо создать лист Microsoft Excel и заполнить поля в соответствии с рис.1.
|
|
B |
C |
D |
E |
F |
G |
|
1 |
Товар |
Модель |
Название |
Цена (тыс.руб) |
Количество |
Сумма |
|
2 |
Ксерокс |
C100 GLS |
Персональный |
827.00 |
564.00 |
466 428.00 |
|
3 |
Ксерокс |
C110 GLS |
Персональный |
993.00 |
623.00 |
618 639.00 |
|
4 |
Ксерокс |
C200 GLS |
Персональный Плюс |
1 430.00 |
568.00 |
812 240.00 |
|
5 |
Ксерокс |
C210 GLS |
Персональный Плюс |
1 716.00 |
269.00 |
461 604.00 |
|
6 |
Ксерокс |
C300 GLS |
Деловой |
2 470.00 |
412.00 |
1 017 640.00 |
|
7 |
Ксерокс |
C310 GLS |
Деловой |
2 698.00 |
574.00 |
1 548 652.00 |
|
8 |
Ксерокс |
C400 GLS |
Профессиональный |
4 270.00 |
223.00 |
952 210.00 |
|
9 |
Ксерокс |
C410 GLS |
Профессиональный |
5 124.00 |
652.00 |
3 340 848.00 |
|
10 |
Ксерокс |
C420 GLS |
Профессиональный |
6 415.00 |
895.00 |
5 741 425.00 |
|
11 |
Факс |
F100 G |
Персональный |
7 378.00 |
154.00 |
1 136 212.00 |
|
12 |
Факс |
F150 G |
Персональный |
1 608.00 |
214.00 |
344 112.00 |
|
13 |
Факс |
F200 G |
Персональный Плюс |
1 840.00 |
235.00 |
432 400.00 |
|
14 |
Факс |
F250 G |
Персональный Плюс |
1 730.00 |
221.00 |
382 330.00 |
|
15 |
Факс |
F300 G |
Деловой |
2 076.00 |
541.00 |
1 123 116.00 |
|
16 |
Факс |
F350 G |
Деловой |
2 551.00 |
544.00 |
1 387 744.00 |
|
17 |
Факс |
F400 G |
Профессиональный |
2 761.00 |
53.00 |
146 333.00 |
|
18 |
Факс |
F450 G |
Профессиональный |
3 513.00 |
542.00 |
1 904 046.00 |
|
19 |
Факс |
F500 G |
Профессиональный |
4 878.00 |
325.00 |
1 585 350.00 |
Рис 1.
Значения колонки Сумма получают путём умножения значений столбца Цена на значения столбца Количество: в ячейке G2 поставьте знак =, затем выделите значение ячейки F2, поставьте знак умножения и далее значение ячейки Е2, нажмите Enter. Получив число, протяните мышкой до конечной ячейки столбца G.
Вы получили полную таблицу данных, в которой вы будете проводить фильтрацию.
2. Выделите ячейку В1. В меню Данные (Data) выберите команду Фильтр-Автофильтр (Filter-AutoFilter). В заголовках каждого столбца появятся кнопки фильтра со стрелочками вниз.
Примечание. В некоторых базах данных при этом появляется диалоговое окно, спрашивающее, хотите ли вы использовать верхнюю строку в качестве заголовка. В диалоге щёлкните на кнопке ОК.
3. Щёлкните на стрелке фильтра в ячейке D1. Это заголовок Название. В появившемся окне списка критериев необходимо выбрать нужный критерий (ключ) фильтрации.
|
Название |
|
(Все) |
|
(Первые 10…) |
|
Деловой |
|
Персональный |
Критерии фильтрации находятся в списке в алфавитном порядке.
4. В качестве ключа фильтрации в ячейке D1 выделите слово Персональный.
В списке останутся только названия факсов и ксероксов, включающие слово «Персональный». Все остальные записи таблицы не удалены, а лишь временно скрыты. Порядковые номера строк в списке теперь не являются последовательными (рис. 2).
|
Товар |
Модель |
Название |
Цена (тыс.руб) |
Количество |
Сумма |
|
Ксерокс |
C100 GLS |
Персональный |
827 |
564 |
466428 |
|
Ксерокс |
C110 GLS |
Персональный |
993 |
623 |
618639 |
|
Факс |
F100 G |
Персональный |
7378 |
154 |
1136212 |
|
Факс |
F150 G |
Персональный |
1608 |
214 |
344112 |
Рис 2.
5. Щёлкните на кнопке со стрелкой в ячейке В21 (рис 2.). Выделите в списке ключ Ксерокс. Список персональных ксероксов станет видимым на вашем рабочем листе. Чтобы снова вывести на экран полный список, нужно щёлкнуть на любой кнопке со стрелкой фильтра, и в списке критериев фильтрации выделить строку Все (All). Или в меню Данные (Data) выбрать команду Фильтр – Показать все (Filter – Show All)
6. Восстановите список. В меню Данные выберите команду Фильтр - Отобразить всё. Теперь на рабочем листе снова находится полный список товаров.
7. Составьте список десяти наиболее доходных видов товаров.
В поле Сумма установите критерий фильтрации Первые 10 (Тор 10). Появится диалоговое окно Наложение условия по списку (Тор10 AutoFilter).
Убедитесь, что параметры окна в группе Вывести установлены так: «10», «наибольших», «элементов списка», и щёлкните на кнопке ОК.
На рабочем листе останется список товаров, значения в поле Сумма которых составляют десять наибольших в полном списке величин.
Восстановите полный список, выбрав команду Фильтр - Отобразить всё в меню Данные.
8. Задайте свои критерии поиска данных.
В этом упражнении вы составите список ксероксов и факсов с названиями «Персональный» и «Персональный Плюс».
Щёлкните на стрелке фильтра поля Название (ячейка D1 см приложение рис.1). В списке ключей выберите Условие. Откроется диалоговое окно Пользовательский автофильтр (Custom AutoFilter). Убедитесь, что в группе Название в верхнем поле операторов находится слово «равно». Щёлкните на стрелке соседнего поля и выделите строку Профессиональный. Включите опцию ИЛИ (OR). Щёлкните на стрелке нижнего поля операторов и выделите «равно». Щёлкните на стрелке нижнего поля критериев и выделите строку Персональный Плюс. Нажмите ОК.
4
Литература
1. Приходько А.И. Регрессионный анализ средствами EXCEL.Учебное пособие. - Ростов -на – Дону , издательство «Феникс»,2007, 250стр.
2. Демиденко Е.З. Линейная и нелинейная регрессия. – М. : Финансы и статистика. 2001, 302 стр.
3. Шалабанов А.К., Роганов Д.А. Эконометрика. Учебно-методическое пособие. – Академия управления «Тисби», Казань, 2004, 133 стр.
4. Федосеев В.В., Гармаш А.Н., Дайитбегов Д.М., Орлова И.В., Половников В.А. Экономико-математические методы и прикладные модели. Учебное пособие. Москва. ЮНИТИ, 2002,388стр.
Нет
Да
EMBED Equation.3
EMBED Equation.3
n = n – 1
Нет
Рис. 1. Алгоритм обработки результатов нескольких измерений
EMBED Equation.3
EMBED Equation.3
EMBED Equation.3
Определение t по таблицам
EMBED Equation.3
Qj отбрасывается
Да
EMBED Equation.3
EMBED Equation.3
Да
EMBED Equation.3
EMBED Equation.3
EMBED Equation.3
Определение Ψ0 по таблицам
EMBED Equation.3
Да
Нет
EMBED Equation.3
Определение t по таблицам
EMBED Equation.3
EMBED Equation.3
Закон распред. нормальный
Нет
EMBED Equation.3
EMBED Equation.3
Определение t по таблицам
EMBED Equation.3