- •Содержание
- •VII. Статистические методы 167
- •Введение
- •I. Общие методы работы
- •1.1. Работа с формулами
- •1.1.1. Общие сведения
- •Вычисления сложных выражений
- •1.1.2. Задание
- •1.2. Математические функции
- •1.2.1. Общие сведения
- •1.2.2. Пример
- •1.2.3. Варианты заданий
- •1.3. Вычисления с условиями
- •1.3.1. Общие сведения
- •1.4. Работа со справочниками
- •1.4.1. Общие сведения
- •1.4.2. Варианты заданий
- •«Разносортица»
- •«Маршрутное такси»
- •«Гостиница»
- •«Автовокзал»
- •«Книжное издательство»
- •«Продукты»
- •«Коттеджи»
- •«Гастроли»
- •«Туристическое агентство»
- •«Комплектующие»
- •«Авиаперевозки»
- •«Винный погребок»
- •«Сберкасса»
- •«Мебельная фабрика»
- •16. «Сага о таре»
- •1.5. Работа с диаграммами
- •1.5.1. Общие сведения
- •1.5.2. Задание на построение диаграммы
- •1. Изменение настроек параметров диаграммы:
- •3. Форматирование рядов данных и их элементов:
- •4. Форматирование осей диаграммы:
- •5. Форматирование сетки, стен и основания:
- •6. Форматирование легенды:
- •1.5.3. Варианты заданий
- •1.6. Собственные функции
- •1.6.1. Общие сведения
- •1.6.2. Общие сведения о Visual Basic for Excel
- •Математические операции
- •Математические функции
- •1.6.3. Варианты заданий
- •II. Численные методы
- •2.1. Решение алгебраических уравнений Средство «Подбор параметра»
- •2.1.1. Общие сведения
- •2.1.2. Пример
- •2.1.3. Варианты заданий
- •2.2. Решение систем уравнений
- •2.2.1. Общие сведения
- •2.2.2. Реализация расчетов в Excel
- •2.2.3. Варианты заданий
- •2.3. Задачи оптимизации
- •2.3.1. Общие сведения
- •2.3.2. Пример
- •2.3.3. Варианты заданий
- •III. Базы данных в ms Excel
- •Каждое из последующих заданий необходимо выполнять на отдельном листе!!!
- •Сортировка
- •3.1.1. Общие сведения
- •3.1.2. Варианты заданий
- •Фильтрация данных
- •3.2.1. Общие сведения
- •Варианты заданий
- •Средство «Итоги»
- •3.3.1. Общие сведения
- •Сводные таблицы
- •3.5. Функции для работы с базами данных
- •3.6. Консолидация данных
- •3.6.2. Варианты заданий
- •3.7. Контрольная работа по теме «Базы данных в Excel»
- •3.7.1. Указания
- •2. Скопируйте указанный файл в свою рабочую папку и вся дальнейшая работа должна производиться только с этой копией.
- •3.7.2. Варианты заданий
- •Вариант 12
- •Вариант 13
- •Вариант 14
- •Вариант 15
- •IV. Макросы в ms Excel
- •4.1. Макросы для автоматизации работ
- •4.1.1. Пример
- •4.2. Вычислительные макросы
- •4.2.1. Пример 1. Расчет точки безубыточности
- •4.2.2. Пример 2. Моделирование процесса налогообложения [8]
- •4.3. Использование макросов для создания интерфейса
- •V. Технология создания информационной системы средствами ms Excel
- •5.1. Постановка задачи
- •5.2. Требования к системе
- •5.3. Общая архитектура ис
- •5.3.1. Проектирование общей архитектуры
- •5.3.2. Создание общей архитектуры
- •5.3.2.1. Создание объектов ис
- •5.3.2.2. Организация переходов между объектами
- •5.3.2.3. Этапы создания интерфейса
- •5.4. Организация работы с базой данных
- •5.4.1. Заполнение таблиц модельными данными
- •5.4.2. Работа с данными
- •5.4.3. Сортировка
- •5.4.4. Поиск данных
- •5.4.5. Отчеты
- •5.4.5.1. Использование функций
- •5.4.5.2. Использование сводных таблиц
- •5.4.5.3. Использование элементов управления
- •5.4.5.5. Использование встроенных функций
- •Функция должна быть в англоязычном варианте.
- •5.4.5.6. Варианты заданий
- •5.4.6. Расчет заработной платы
- •5.4.6.1. Постановка задачи
- •5.4.6.2. Интерфейс расчета заработной платы
- •5.4.6.3. Реализация расчетов
- •VI. Экономические расчеты
- •6.1. Задачи на проценты
- •6.1.1. Общие сведения
- •6.1.2. Пример.
- •6.1.3. Варианты заданий
- •6.2. Финансовые функции
- •6.2.1. Общие сведения
- •Бс(Ставка, Кпер, Плт, Пс, Тип).
- •6.2.3. Варианты заданий
- •6.3. Анализ межотраслевого баланса (модель Леонтьева)
- •Основные понятия
- •Математическая модель межотраслевого баланса
- •6.3.4. Варианты заданий
- •6.4. Задача об эквивалентности ставок [1]
- •6.4.1. Основные формулы
- •6.4.2. Постановка задачи
- •6.4.3. Варианты заданий
- •6.5. Методы анализа проектов (использование средства «Подбор параметра»)
- •6.5.1. Термины и определения
- •6.5.2. Примеры
- •Варианты заданий
- •6.6. Выбор оптимального портфеля инвестиций
- •6.6.1. Основные определения
- •6.6.2. Пример
- •6.6.3. Варианты заданий
- •6.7. Вычисление налогов
- •6.7.1. Предварительные замечания
- •6.7.2. Пример.
- •6.7.3. Варианты заданий
- •6.8. Моделирование динамических процессов
- •6.8.1. Общие сведения
- •6.8.2. Порядок выполнения работы
- •6.8.3. Пример
- •Результаты должны отражать основные закономерности процесса
- •6.8.4. Варианты заданий
- •Производство в условиях постоянного спроса
- •Конкуренция
- •Сезонное производство
- •Рыночные отношения
- •Взаимопоставки
- •Цены в условиях ограниченного объема выпуска
- •Северный завоз
- •Два пароходства
- •Последовательные перевозки
- •Антимонопольная система
- •Конъюнктура
- •Количество информации в Интернет
- •Валютная интервенция
- •Реклама
- •VII. Статистические методы
- •7.1. Определение характеристик случайных величин
- •7.1.1. Содержание работы
- •7.1.2. Варианты заданий
- •7.2. Дисперсионный анализ
- •7.2.1. Общие сведения
- •7.2.1. Пример
- •7.2.3. Методы, применяемые после дисперсионного анализа
- •7.2.4. Варианты заданий
- •7.3. Регрессионный анализ
- •7.3.1.Общие сведения
- •7.3.2. Порядок выполнения работы
- •7.3.3. Проверка уравнения регрессии на адекватность
- •7.3.4. Использование уравнения для прогноза
- •7.4. Кластерный анализ
- •7.4.1. Общие положения.
- •7.4.2. Примеры
- •7.4.3. Формализация процесса кластеризации
- •7.4.4. Порядок выполнения работы
- •7.4.5. Задания
- •7.5. Анализ временных рядов
- •7.5.1. Общие сведения
- •7.5.2. Пример
- •Литература
- •Приложения
- •Технология генерации модельных данных
- •Приложение 2 Районы и города Чувашии в цифрах [9]
- •Тексты макросов Текст макроса для кластерного анализа
- •Текст макроса для решения систем дифференциальных уравнений
- •Текст макроса для генерации временного ряда
- •Приложение 4 Транспорт и связь
- •Статистические данные по регионам рф [5]
7.2.3. Методы, применяемые после дисперсионного анализа
Дисперсионный анализ показывает – существует ли статистически существенное влияние изучаемого фактора на свойства объекта? Экспериментатора же помимо этого интересует также вопрос – а каково конкретное влияние фактора и как меняются свойства объекта при переходе от одного уровня фактора к другому? Другими словами – экспериментатору важно выяснить – существует ли статистически существенное различие в средних значениях по уровням фактора. Заметим, что в случае фактора с двумя уровнями этот вопрос не стоит. В самом деле, если дисперсионный анализ показал, что имеется статистически существенное влияние фактора, то автоматически существенно различаются и средние по этим двум уровням. А как быть, если число уровней фактора больше двух? Например, в рассмотренном выше примере, экспериментатора может заинтересовать вопрос: а если разница между красками B и D? Для них средние значения вроде бы близки (11,8 и 9,8 соответственно).
Для этих целей наиболее часто используется ранговый критерий Дункана
Общую схему применения этого критерия рассмотрим на вышеприведенном примере. Она состоит из следующих этапов.
-
Упорядочить k средних по возрастанию.
В нашем примере k=4 и упорядоченные средние представляются рядом:
Средние |
9,8 |
11,8 |
20,6 |
29,8 |
Тип краски |
D |
B |
A |
C |
2. Из таблицы дисперсионного анализа берется дисперсия ошибки с соответствующим числом степеней свободы.
В нашем случае: s2e = 8,35 при f = 16.
3. Вычисляется нормированная ошибка для среднего по испытанию:
, (7.4)
где m – число опытов в одном варианте испытаний.
В нашем случае:
.
4. Из таблицы критерия Дункана выписываются (k-1) рангов при выбранном уровне значимости и числе степеней свободы, соответствующем ошибке.
F |
Ранг |
|||||||||||||
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
12 |
14 |
16 |
18 |
||
1 |
18,0 |
18,0 |
18,0 |
18,0 |
18,0 |
18,0 |
18,0 |
18,0 |
18,0 |
18,0 |
18,0 |
18,0 |
18,0 |
|
2 |
6,09 |
6,09 |
6,09 |
6,09 |
6,09 |
6,09 |
6,09 |
6,09 |
6,09 |
6,09 |
6,09 |
6,09 |
6,09 |
|
3 |
4,50 |
4,50 |
4,50 |
4,50 |
4,50 |
4,50 |
4,50 |
4,50 |
4,50 |
4,50 |
4,50 |
4,50 |
4,50 |
|
4 |
3,93 |
4,01 |
4,02 |
4,02 |
4,02 |
4,02 |
4,02 |
4,02 |
4,02 |
4,02 |
4,02 |
4,02 |
4,02 |
|
5 |
3,64 |
3,74 |
3,79 |
3,83 |
3,83 |
3,83 |
3,83 |
3,83 |
3,83 |
3,83 |
3,83 |
3,83 |
3,83 |
|
6 |
3,46 |
3,58 |
3,64 |
3,68 |
3,68 |
3,68 |
3,68 |
3,68 |
3,68 |
3,68 |
3,68 |
3,68 |
3,68 |
|
7 |
3,35 |
3,47 |
3,54 |
3,58 |
3,60 |
3,61 |
3,61 |
3,61 |
3,61 |
3,61 |
3,61 |
3,61 |
3,61 |
|
8 |
3,26 |
3,39 |
3,47 |
3,52 |
3,55 |
3,56 |
3,56 |
3,56 |
3,56 |
3,56 |
3,56 |
3,56 |
3,56 |
|
9 |
3,20 |
3,34 |
3,41 |
3,47 |
3,50 |
3,52 |
3,52 |
3,52 |
3,52 |
3,52 |
3,52 |
3,52 |
3,52 |
|
10 |
3,15 |
3,30 |
3,37 |
3,43 |
3,46 |
3,47 |
3,47 |
3,47 |
3,47 |
3,47 |
3,47 |
3,47 |
3,47 |
|
11 |
3,11 |
3,27 |
3,35 |
3,39 |
3,43 |
3,44 |
3,45 |
3,46 |
3,46 |
3,46 |
3,46 |
3,46 |
3,46 |
|
12 |
3,08 |
3,23 |
3,33 |
3,36 |
3,40 |
3,42 |
3,44 |
3,44 |
3,46 |
3,46 |
3,46 |
3,46 |
3,46 |
|
13 |
3,06 |
3,21 |
3,30 |
3,35 |
3,38 |
3,41 |
3,42 |
3,44 |
3,45 |
3,45 |
3,46 |
3,47 |
3,47 |
|
14 |
3,03 |
3,18 |
3,27 |
3,33 |
3,37 |
3,39 |
3,41 |
3,42 |
3,44 |
3,45 |
3,46 |
3,47 |
3,47 |
|
15 |
3,01 |
3,16 |
3,25 |
3,31 |
3,36 |
3,38 |
3,40 |
3,42 |
3,43 |
3,44 |
3,45 |
3,46 |
3,47 |
|
16 |
3,00 |
3,15 |
3,23 |
3,30 |
3,34 |
3,37 |
3,39 |
3,41 |
3,43 |
3,44 |
3,45 |
3,46 |
3,47 |
|
17 |
2,98 |
3,13 |
3,22 |
3,28 |
3,33 |
3,36 |
3,38 |
3,40 |
3,42 |
3,44 |
3,45 |
3,46 |
3,47 |
|
18 |
2,97 |
3,12 |
3,21 |
3,27 |
3,32 |
3,35 |
3,37 |
3,39 |
3,41 |
3,43 |
3,45 |
3,46 |
3,47 |
|
19 |
2,96 |
3,11 |
3,19 |
3,26 |
3,31 |
3,35 |
3,37 |
3,39 |
3,41 |
3,43 |
3,44 |
3,46 |
3,47 |
|
20 |
2,95 |
3,10 |
3,18 |
3,25 |
3,30 |
3,34 |
3,36 |
3,38 |
3,40 |
3,43 |
3,44 |
3,46 |
3,47 |
|
22 |
2,93 |
3,08 |
3,17 |
3,24 |
3,29 |
3,32 |
3,35 |
3,37 |
3,39 |
3,42 |
3,44 |
3,45 |
3,46 |
|
… |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
100 |
2,80 |
2,95 |
3,05 |
3,12 |
3,18 |
3,22 |
3,26 |
3,29 |
3,32 |
3,36 |
3,40 |
3,42 |
3,45 |
|
… |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
∞ |
2,77 |
2,92 |
3,02 |
3,09 |
3,15 |
3,19 |
3,23 |
3,26 |
3,29 |
3,34 |
3,38 |
3,41 |
3,44 |
В нашем примере для f = 16 и =0,05 выписанные ранги выглядят следующим образом:
№ |
2 |
3 |
4 |
Ранги |
3,00 |
3,15 |
3,23 |
5. Получить наименьшие значимые ранги путем умножения выписанных рангов на нормированную ошибку.
В нашем случае:
№ |
2 |
3 |
4 |
НЗР |
3,876 |
4,070 |
4,173 |
6. Произвести сравнение наблюдаемых разностей между средними с вычисленными НЗР по схеме:
разница между рядом стоящими средними сравнивается с минимальным НЗР (при № = 2);
разница между средними через одно сравнивается с НЗР при № = 3;
разница между средними через два сравнивается с НЗР при № = 4 и т. д.
Если НЗР больше наблюдаемых разностей, то сравниваемые средние отличаются несущественно. Иначе различие между средними признается статистически значимым.
В нашем случае:
-
11,8 – 9,8 = 2 <3,876
-
20,6 – 9,8 = 10,8 > 4,070
-
29,8 – 9,8 = 20 > 4,173
-
20,6 – 11,8 = 8,8 >3,876
-
29,8 – 11,8 = 18 > 4,070
-
29,8 – 20,6 = 9,2 > 3,876
В результате сравнения обнаружено, что первое и второе среднее отличаются несущественно, а разница между остальными средними статистически значима.
Результаты сравнения можно наглядно представить на одномерной шкале.
D B A C
9 ,8 11,8 20,6 29,8
Здесь средние, отличающиеся несущественно, имеют одну общую черту.
Таким образом по результатам испытаний защитных красок можно сказать следующее:
краски D и B обладают наилучшими защитными свойствами и примерно одинаковы. Наихудшими защитными свойствами обладает краска C. Краска А является промежуточной по защитным свойствам.