- •В.П.Обоскалов
- •Предисловие
- •Excel. Теоретические аспекты
- •Основные понятия
- •Основные термины
- •Работа с объектами
- •Функциональные клавиши
- •Указатель мыши
- •Формулы. Имена ячеек. Функции
- •Диаграммы
- •Функции списка
- •Формы данных
- •Фильтры
- •Автофильтр
- •Расширенный фильтр
- •Промежуточные итоги
- •Сводные таблицы
- •Консолидация данных
- •Нелинейные уравнения. Оптимизационные задачи
- •Поиск решения
- •Подбор параметра
- •Проектирование вычислений с помощью таблицы подстановки
- •Внешняя среда
- •Экспорт и импорт данных
- •Связь с базами данных
- •Вызов программы ms Query
- •Мастер запросов
- •Макросы
- •Запись макроса
- •Относительные ссылки
- •Выполнение макроса
- •Подключение макроса к объектам Назначение сочетания клавиш для запуска записанного ранее макроса
- •Назначение макроса кнопке или графическому объекту
- •Текст макроса
- •Excel. Лабораторный практикум
- •Начальное знакомство
- •Выделение блоков
- •Прозрачность ячеек. Перемещение данных. Копирование
- •Относительная и абсолютная адресация
- •Заполнение таблицы. Формулы. Относительная адресация. Суммирование
- •Абсолютная адресация. Имена ячеек
- •Форматирование таблицы
- •Вставка и удаление строк и столбцов
- •Форматирование ячеек
- •Форматирование столбцов и строк
- •Сетка. Показ формул
- •Автозаполнение. Списки
- •Автозаполнение
- •Диаграммы
- •Диаграмма. Общий случай
- •Гистограмма и круговая диаграмма
- •Графики функций
- •Работа с массивами данных
- •Подбор параметра. Поиск решения
- •Подбор параметра
- •Поиск Решения
- •Транспортная задача
- •Функции списка
- •Сортировка
- •Формы данных
- •Вычисление итогов
- •Консолидация данных
- •Сводная таблица
- •Внешняя среда
- •Экспорт и импорт данных. Связь документов
- •Процедуры пользователя
- •Диалоговые окна
- •UserForm
- •Решение инженерных задач наExcel
- •Проектирование эт
- •Основные функции
- •Суммирование
- •Примеры
- •Функции даты
- •Формульные расчеты
- •Тип оборудования
- •Самостоятельная работа
- •Самостоятельная работа
- •Коэффициенты, характеризующие график нагрузки
- •Проектирование таблицы.
- •Проектирование таблицы
- •Самостоятельная работа
- •Температурный режим трансформатора
- •Математический метод
- •Самостоятельная работа
- •Ремонтная ведомость Самостоятельная работа
- •Работа с матрицами
- •Расчет сети постоянного тока
- •Самостоятельная работа
- •Расчет токов трехфазного короткого замыкания
- •Метод z-матрицы
- •Эквивалентирование сети
- •Расчет токов кз по модели сети постоянного тока
- •Оптимизационные задачи
- •Решение систем нелинейных уравнений
- •Самостоятельная работа
- •Оптимальное распределение мощности нагрузки между параллельно работающими агрегатами
- •Линейное программирование
- •Самостоятельная работа
- •Двойственная задача линейного программирования
- •Самостоятельная работа
- •Работа с комплексными числами
- •Самостоятельная работа
- •Прогнозирование нагрузок
- •Метод наименьших квадратов
- •Самостоятельная работа
- •Метод скользящего среднего
- •Линейный тренд
- •Самостоятельная работа
- •Вероятность и статистика
- •Функции расчета вероятностных параметров
- •Математическое ожидание
- •Дисперсия
- •Корреляционный момент
- •Коэффициент корреляции
- •Гистограммы
- •Нормальное распределение
- •Расчет вероятностного режима электрической сети
- •Самостоятельная работа
- •Самостоятельная работа
- •Гистограмма случайной величины
- •Самостоятельная работа
- •Регрессия
- •Дополнительная регрессионая статистика
- •Простая линейная регрессия
- •Самостоятельная работа
- •Использование f-статистики
- •Вычисление t-статистики
- •Объектное программирование. Visual Basic
- •Основные понятия
- •Основные методы
- •МетодыRange, Cells, Offset
- •Самостоятельная работа
- •Методы и свойства активности объекта
- •Методы активности
- •Свойства, характеризующие активность
- •Макрорекордер
- •Самостоятельная работа
- •Свойства, характеризующие содержимое
- •Самостоятельная работа
- •Основные элементы языка
- •Общие сведения и структура программных модулей
- •Самостоятельная работа
- •Типы данных и переменные
- •Оператор Dim
- •Массивы
- •Пользовательские типы данных. Структуры
- •Динамическое перераспределение памяти
- •Константы
- •Видимость переменных и констант
- •Операторы присваивания
- •Математические и строковые операции
- •Процедуры
- •Вызов процедуры
- •Самостоятельная работа
- •Использование необязательных аргументов
- •Самостоятельная работа
- •Функции
- •Передача массива
- •Ссылки на внешние библиотеки
- •1. Скалярное произведение векторов (столбцы Excel) разной размерности
- •2. Поэлементное произведение массивов
- •Функции Application
- •Операторы принятия решения
- •ОператорIf
- •Блочная структура if
- •Многоблочная структура if
- •Select Case
- •Безусловный переход
- •Циклы объектного типа
- •Пример двойного цикла
- •Передача массива данных в таблицу
- •Коррекция блока ячеек таблицы
- •Ввод и вывод данных
- •Чтение и запись последовательных файлов
- •Диалоговые функции
- •MsgBox()
- •Самостоятельная работа
- •Функция InputBox()
- •Ввод названия файла
- •Самостоятельная работа
- •Диалоговые окна Excel
- •Самостоятельная работа
- •Диалоговые окна пользователя
- •Элементы управления
- •Командная кнопка (CommandButton)
- •Самостоятельная работа
- •Надпись (Label)
- •Самостоятельная работа
- •Окно редактирования (TextBox)
- •Окно списка (ListBox)
- •Самостоятельная работа
- •Выпадающее окно (ComboBox)
- •Групповое окно (Frame)
- •Контрольный индикатор (CheckBox)
- •Кнопка выбора (OptionButton)
- •Заполнение массива случайными числами
- •Линейная интерполяция
- •Самостоятельная работа
- •Оптимальное распределение нагрузки
- •Самостоятельная работа
- •Функции комплексных чисел
- •Самостоятельная работа
- •Обращение комплексной матрицы
- •Самостоятельная работа
- •Решение систем дифференциальных уравнений
- •' Модуль "Ввод исходных данных из таблицы Excel"
- •Перенумерация узлов
- •Самостоятельная работа
- •Матрица инциденций по узлам
- •Индивидуальные задания
- •Библиографический список
- •620002, Екатеринбург, ул. Мира,19
- •620002, Екатеринбург, ул. Мира,19 в.П.Обоскалов
Коэффициент корреляции
Коэффициент корреляции определяется соотношением
. |
(3.0) |
Величина rij, близкая к единице, говорит о тесной функциональной взаимосвязи анализируемых случайных величин. Именно поэтому на диагонали матрицы мы видим единицы. Близкая к нулю величина rij говорит о практической независимости случайных величин.
Гистограммы
Статистическим аналогом функции плотности распределения является гистограмма, показывающая поинтервальное распределение частот реализаций случайной величины. Для расчета гистограмм в Excel имеется встроенная функция ЧАСТОТА().
Функция ЧАСТОТА(М-данных; М-карманов) возвращает распределение частот в виде вертикального массива. Для заданного множества значений и некоторого множества "карманов" (интервалов) частотное распределение подсчитывает, сколько исходных значений попадает в каждый интервал.
М-данных - это массив данных, для которых вычисляются частоты. Если М-данных не содержит значений, то функция ЧАСТОТА() возвращает массив нулей.
М-карманов - это совокупность интервалов, в которые группируются значения аргумента М-данных. Если М-карманов не содержит значений, то функция ЧАСТОТА() возвращает количество элементов в аргументе М-данных.
Замечания
Функция ЧАСТОТА() вводится как формула массива после выделения интервала смежных ячеек, в которые нужно вернуть полученный массив распределения.
Количество элементов в возвращаемом массиве на единицу больше количества элементов в аргументе М-карманов .
Нормальное распределение
Функция нормального распределения имеет широкое применение в инженерных расчетах, основанных на статистическом анализе, включая проверку гипотез. Поэтому следует более детально рассмотреть специфику ее использования в среде Excel.
Функция НОРМРАСП(x;среднее;ст-откл;интегр)
возвращает нормальную функцию распределения для указанного среднего и стандартного отклонения. В формате функции: х - значение, для которого строится распределение; «среднее» и «ст-откл» - соответственно среднее арифметическое и стандартное отклонение рассматриваемого распределения; «интегр» - логическое значение, определяющее форму функции: ИСТИНА (1) - интегральная функция; ЛОЖЬ (0) - плотность распределения.
Уравнение для плотности нормального распределения имеет следующий вид:
.
Функция НОРМОБР(вероятность;среднее; ст-откл) возвращает обратное нормальное распределение (по вероятности определяется соответствующая ей физическая величина).
Пример. НОРМОБР(0,908789;40;1,5) равняется 42.
Замечания
Если какой-либо из аргументов не является числом, то функции НОРМРАСП(), НОРМОБР() возвращают значение ошибки #ЗНАЧ!.
Рассматриваемые функции возвращают значение ошибки #ЧИСЛО!, если вероятность отрицательна или больше единицы, или стандартное отклонение σ≤0.
Расчет вероятностного режима электрической сети
Задача 3.3. Вероятностные параметры нагрузок
Рис.
3.67.
Расчетная схема сети
Таблица 3.26
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
Графики нагрузок |
|
|
|
| |||
2 |
Период |
P1 |
P2 |
P3 |
|
Матрица корреляционных моментов | ||
3 |
1 |
50 |
20 |
-200 |
|
200 |
267 |
500 |
4 |
2 |
50 |
20 |
-200 |
|
267 |
1422 |
1333 |
5 |
3 |
80 |
20 |
-200 |
|
500 |
1333 |
2500 |
6 |
4 |
80 |
20 |
-100 |
|
|
|
|
7 |
5 |
80 |
100 |
-100 |
|
Матрица коэффициентов корреляции | ||
8 |
6 |
80 |
100 |
-100 |
|
1,00 |
0,50 |
0,71 |
9 |
|
|
|
|
|
0,50 |
1,00 |
0,71 |
10 |
Среднее |
70 |
46,67 |
-150 |
|
0,71 |
0,71 |
1,00 |
11 |
Дисперсия |
200 |
1422 |
2500 |
|
|
|
|
12 |
СКО |
14,14 |
37,71 |
50 |
|
|
|
|
Графики нагрузок (как случайных величин) и расчет искомых величин (математических ожиданий, дисперсий и корреляционных моментов нагрузок ЭЭС) представлены в табл. 3.16, где блок В3:D8 представляет статистическую выборку наблюдаемых величин, в ячейках В10, В11 записаны соответственно формулы =СРЗНАЧ(B3:B8), В11:=ДИСПР(B3:B8). Блоки С10:С11, D10:D11 является копированием В10:В11.
Матрица корреляционных моментов строится следующим образом По диагонали записываются формулы F3, G4, H5: =КОВАР($B$3:$B$8;B3:B8); =КОВАР(C3:C8;$C$3:$C$8); =КОВАР($D$3:$D$8;D3:D8). Ячейки справа и слева заполняются копированием соответствующих строчных диагональных элементов.
Диагональ матрицы ковариаций состоит из дисперсий, в чем нетрудно убедиться (см. табл. 3.16, В11:D11).
Получение матрицы коэффициентов корреляции можно выполнить по аналогии с построением матрицы корреляционных моментов (F8: =КОРРЕЛ($B$3:$B$8;B3:B8)), но если матрица {Кij} существует, то проще матрицу {rij} получить, исходя из определения ( 3 .0) с помощью дополнительной матрицы перекрестных произведений S=tгде - вектор- строка среднеквадратических отклонений. Элемент матрицы . Вычисление {rij} реализуется поэлементным делением массивов ковариаций на (в блокеF8:H10 записана формула {=E3:G5/МУМНОЖ(ТРАНСП(B12:D12);B12:D12))}. Такой прием особенно ценен при большой размерности матрицы ковариаций; его следует принять на вооружение, поскольку перекрестные произведения часто встречаются в иных технических приложениях.