Структура документа Excel

номера
строк
строка
столбец
имена столбцов
активная ячейка
неактивная ячейка
текст
числа
формулы
время
дата
Рабочие книги и рабочие листы

Методы адресации
адрес активной ячейки
ячейка B2
диапазон B2:С7
Тип ссылки – А1
Абсолют.ссылка-$B$2;Относительная-B2;Смешанная-B$2.
Косвенная адресация-имя ячейки
Тип ссылки – R1C1
R2C2; R[2]C[2]; R[-2]C.
Именованная область – Вставка-Имя-Присвоить
B2
С7

Типы ссылок
относительные (меняются так же, как и адрес формулы )
формула «переехала» на один столбец вправо и на одну строку вниз;
абсолютные
(не меняются)
смешанные
(меняется только относительная часть)
имя столбца ↑ на 1
номер строки ↑ на 1

Ввод и редактирование данных
адрес активной ячейки
отменить (Esc)
принять (Enter)
строка редактирования
ЛКМ
F2 – редактировать прямо в ячейке

Выделение данных
ячейка:
+ЛКМ
– ЛКМ
диапазон:
вся таблица:
ЛКМ
ЛКМ
строки:
ЛКМ
столбцы:
ЛКМ
несвязанные диапазоны:
+Ctrl и выделять второй

Операции со строками и столбцами
размеры
высота строк
ширина
столбцов
добавление, удаление
ПКМ

Перемещение и копирование
перетащить ЛКМ
за рамку (!)
+Ctrl = копирование
+Alt = на другой лист
перемещение со сдвигом (+Shift)

Заполнение рядов
арифметическая прогрессия
маркер заполнения
копирование формул
ЛКМ
даты
списки
время
ЛКМ

Оформление ячеек
все свойства
↑↓ размер
направление
в несколько
строк
денежный
формат
количество знаков
в дробной части

Форматирование рабочих листов
Автоматическое форматирование таблиц (формат-автоформат).
Копирование и вставка форматов.
Ориентация текста и чисел.
Задача форматирования чисел.
Автоформат чисел.
Потенциальная опасность форматирования
(сервис-параметры-вычисления-точность как на экране).

Числовые форматы
(число десят. знаков, разделители групп, отриц. числа)
Общий формат (123.0 → 123; .123 → 0.123)
Финансовый и денежный формат (устанавливается обозначение денежной единицы)
Процентный формат (выводит числа с символом %, запятая сдвигается на два знака вправо 0.1234 → 12.34%)
Дробный формат (дробные значения как простые дроби)
Экспоненциальный формат 987654321 → 9,88Е+10
Текстовый формат (обрабатывается как строка вне зависимости от содержания)
Дополнительный формат (почт.индекс, ТЛФ префикс)
Формат дат (варианты представления дат, 65380 дат)

Пользовательские форматы
Станд.символы форматирования - $ + - / ( )
Используются спец.символы – 0 ? #
Код формата Введено Отображение
0,00 5,6 5,60
6 6,00
25,3 ####
0,2 0,20
0,?? 5,6 5,6
##00 3 03
# ###,## р 1234567 1 234,57 р

Работа с формулами
Арифметическое выражение (АВ)
Приоритет операций - % ^ * / + -
Операции связи : ; &
Логическое выражение (ЛВ) < <= > >= = <>
Правила записи формул
Ввод ссылок на ячейки – другие листы, книги, 3D
Адресные операции - : ; пробел ..
Ручной режим вычислений (итерации, цикл. Ссылки)
Взаимосвязь ячеек (влияющие, зависимые)
Заполнение ячеек формулами, поиск ошибок

Ошибки в формулах
Выделить ячейку, дающую значение ошибки или неверный результат.
В строке формул выделить вызывающий сомнение элемент формулы.
Для вычислений нажать F9 (выдел. часть заменяется результатом).
Выделять и вычислять ост. части формулы.

Основные виды ошибок
Первый символ ошибки - #
Последний символ - ! или ?
Установка просмотра формул (Сервис-Параметры-Вид-Формулы)
#ДЕЛ/0 !
#ИМЯ ?
#ЗНАЧ !
#ССЫЛКА !
#ЧИСЛО !
# Н/Д !

Вычисления с массивами
Массив - прямоугольный диапазон формул или значений, обрабатываемый как единая группа
Массив – средство выполнения больших объемов вычислений в малом пространстве (в памяти массив формул хранится как единая формула)
Формула массива заключается в фигурные скобки, которые нельзя ввести с клавиатуры

Ввод массива
A B C D
1 10 20 30 =60
2 40 50 60 150
3 70 80 90 240
4 10 30 50 90
Есть числовой диапазон А1 : С4
Выделить D1 : D4
Ввести формулу =A1:A4+B1:B4+C1:C4
Комбинация клавиш
+
+
В столбце D4 возникает сумма
Этот столбец изменять уже нельзя (warning – нельзя изменять часть массива)
Нельзя: вставлять ячейки, столбцы в диапазон массива, удалять часть диапазона, редактировать отдельную ячейку
Редактирование формул массива

Особенности вычислений в Excel
Точность числовых значений 1.00Е-307 – 9.99Е+307
Упрощение вычислений
Автосуммирование
Автовычисления
Объемные формулы
Арифметические операции при вставке (правка – спец.вставка)
Вставить (все-формулы-значения-форматы-примечания-без рамки)
Операция (нет-сложить-вычесть-умножить-разделить)

Использование функций
Ячейка Excel – текст, число, формула, функция
Категории функций
10 недавно использовавшихся
Полный алфавитный перечень (более 300)
Финансовые
Дата и время
Математические
Статистические
Ссылки и массивы
Базы данных
Текстовые
Логические
Проверка свойств и значений

Синтаксис функций
Обращение к функции – имя функции, аргументы
Исключение – ПИ( ), ИСТИНА( ) не имеют аргументов
Правила использования аргументов:
Аргументы заключаются в ( );
Разделитель аргументов ; может редактироваться;
Число аргументов не более 30;
Аргументы – числа, текст, АВ, ЛВ, ссылки, имена диапазонов, функции;
В аргументе не должно быть пробелов (подчерк.)
Многоточие–замена аргумента, напр. ИЛИ(Л1; Л2; …);
Необязат. аргументы могут опускаться с сохр. пунктуац.
Текст (до 255 символов) в кавычки;
Цитата в двойные кавычки.

Функции
ввод в ячейке
ввод в строке редактирования
диапазон
ячейка
мастер функций

Ввод функций
Ввод с клавиатуры – строгость синтаксиса и аргументов
Типичные ошибки – «Слишком мало!» #Имя? #Знач?
Мастер функций → Вставка-Функция
Первое окно мастера тематическое → Категории и функции категории
Второе окно → поля аргументов, их значения, текущее значение функции

Математические функции
ABS (число)
EXP (число)
LN (число) LOG(число,базис)
LOG10 (число)
ФАКТР (число)
СУММ (число1;число2; …)
ПРОИЗВ (число1;число2; …)
СУММЕСЛИ(инт; критерий; диап.)
ЗНАК (число)
КОРЕНЬ (число)
СТЕПЕНЬ (число;показатель)
ОСТАТОК (число;делитель)
СЛЧИС ( )
СЛЧИСМЕЖДУ (начало;конец)
РИМСКОЕ(число;форма)
ЦЕЛОЕ (число)
ОКРУГЛ(число;колич.цифр)
ОТБР(число;точность усечения)
ЧЕТН (число)
НЕЧЕТН (число)

Статистические функции
Анализ статистических данных, создание гистограмм, ранжирование данных, извлечение выборок, генерация случайных чисел и т.д.
СРЗНАЧ (число1;число2;…) СЧЕТ(знач1;знач2;…)
СЧЕТЗ(знач1;знач2;…) СЧЕТЕСЛИ(диапазон; критерий)
МАКС {МИН} (число1;число2;…)
МОДА (число1;число2;…)
ДИСП (число1;число2;…)
СТАНДОТКЛОН (число1;число2;…)
РАНГ (число; ссылка; порядок)
ТЕНДЕНЦИЯ (изв_знач_у; изв_знач_х;нов_знач_х)
ПРЕДСКАЗ (х; изв_знач_у; изв_знач_х)
РОСТ (изв_знач_у; изв_знач_х; нов_знач_х)

Некоторые функции
СУММ – сумма значений ячеек и диапазонов
СРЗНАЧ – среднее арифметическое
МИН – минимальное значение
МАКС – максимальное значение
ЕСЛИ – выбор из двух вариантов

Логические функции
И (лог_знач1;лог_знач2;…) ИЛИ (лог_знач1;лог_знач2;…) НЕ(знач)
ЕСЛИ (ЛВ; знач_TRUE; знач_FALSE)
Функции просмотра и ссылок
АДРЕС (№_строки; №_столбца; тип_ссылки)
ВЫБОР (номер_арг; знач1; …; знач29)
ПРОСМОТР (иск_знач; просматр_вектор; вектор_результатов)
Текстовые функции
СИМВОЛ (число) СЦЕПИТЬ (текст1; текст2; …)
СОВПАД (текст1; текст2) НАЙТИ (иском; просматр; нач_позиц)
ДЛСТР (текст) ЗНАЧЕН (текст)
ТЕКСТ(значен; формат)

Логические функции
ЕСЛИ – выбор из двух вариантов
НЕ – обратное условие, НЕ(B2<10) ⇔ ?
И – одновременное выполнение всех условий
ИЛИ – выполнение хотя бы одного из условий
B2>=10

Функции даты и времени
ДАТА (год;месяц;день) СЕГОДНЯ ( ) ВРЕМЯ (час;мин;сек)
ТДАТА ( ) ДЕНЬНЕД (дата; тип) ДАТАЗНАЧ (текст_дата)
Финансовые функции
Планирование и анализ финансово-хозяйственной деятельности
Четыре блока:
Вычисление амортизации
Вычисление рентных платежей (анализ инвестиций)
Вычисление доходов от ценных бумаг
Вычисление скорости оборота вложений
Амортизация – отчисления для возмещения износа
Рента – ряд денежных потоков, регулярно поступающих в течение периода времени
Ценные бумаги – твердопроцентные и беспроцентные
Кредитные и дивидентные операции

Графическое представление табличных данных с помощью диаграмм

диаграммы строятся на основе данных таблицы
проще всего сначала выделить все нужные данные, а потом…
все данные, которые должны обновляться автоматически, нужно выделить
для выделения несвязанных диапазонов используем +Ctrl
Общий подход

Основные типы диаграмм
Гистограмма (столбчатая диаграмма): сравнение значений одного или нескольких рядов данных
График: показывает изменение процесса во времени (равномерные отсчеты)
Круговая: доли в сумме
Точечная: связь между
парами значений (график функции)

Элементы диаграмм
название диаграммы
легенда
ряды данных
ось
сетка
подписи данных

Настройка диаграммы и ее элементов
Конструктор: общие свойства
Макет: настройка свойств отдельных элементов
Формат: оформление отдельных элементов

Графики функций
Задача: построить график функции для .
Таблица значений функции:
шаг 0,5
ЛКМ
ЛКМ

Графики функций
Вставка диаграммы «Точечная»:
выделить данные
результат:

Управление списками и базами данных
Основная терминология (на примере картотеки)
Запись (строка) аналог карточки;
Поле (столбец) содержит данные одного типа;
Имена полей (строка заголовка) д.б. уникальны;
Форма данных (ведение и обработка базы данных).
База данных
Диапазон базы данных;
Диапазон критериев;
Диапазон для извлечения.

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

Сортировка
Сортировка – это расстановка элементов в заданном порядке.
Сортировка одного столбца

Сортировка связанных данных
критерий
строки или столбцы
первая строка – это заголовки

Многоуровневая сортировка
Задача: расставить фамилии по алфавиту, а людей с одинаковыми фамилиями расставить в алфавитном порядке по именам.
ЛКМ

Использование промежуточных итогов для анализа списка
Команда «Данные-Итоги»добавляет строки промежу-точных итогов для каждой группы элементов.
Для вычисления итогов можно использовать различные функции на уровне группы.
При выводе промежуточных итогов создается структура списка с соответствующими уровнями.
Диалоговое окно «Промежуточные итоги» имеет окна:
при каждом изменении в – список имен столбцов;
операция – список операций;
добавить итоги по - список имен столбцов.

Обработка списков с помощью формы
Активизация формы – «Данные-Форма».
В форме отображается одна запись списка, поля ото-бражаются вертикально для удобства просмотра.
Окно формы:
в верхней части имя листа;
слева вертикально имена полей и поля ввода;
справа вертикально действия.
Действия с записями:
просмотр, удаление, добавление, редактирование, поиск.
Поиск по критерию:
способы записи (напр. В*), условные выражения (>700).

Применение фильтров для анализа списков
Вывод результатов запроса по критериям, анализ.
Автофильтр
активизация – «Данные-Фильтр-Автофильтр»;
рядом с каждым столбцом устанавливается автофильтр (кнопка со стрелкой);
отфильтрованные строки отображаются синим цветом, информация в строке состояния
критерии отбора м.б. в несколькольких столбцах (логическое И);
критерии автофильтра:
Все записи;
Значения полей;
Пустые, непустые;
Первые 10 (новое окно диалога);
Условие.

Расширенный фильтр
Дает возможность использования различных критериев (логические функции И, ИЛИ, вычисляемые условия).
Обработка – на месте или копирование рез-та в другую область.
Обращение – «Данные-Фильтр-Расширенный фильтр».
Окно – исходный диапазон (список).
След. окно – диапазон условий (1-я строка - заголовки столбцов, точно совпадающие с заголовками списка, 2-я и последующие – условия отбора).
Минимально 1 пустая строка между списком и значениями условий.
ИЛИ – условия отбора в разных строках, И – в одной.
Все три области не должны пересекаться (список, условия, результат).
Вычисляемые условия – значения, возвращаемые формулой.
Правила создания вычисляемых условий:
Заголовок над ВУ должен отличаться от любого заголовка столбца;
Ссылки на ячейки, находящиеся вне списка д.б. абсолютными;
Ссылки на ячейки в списке – относительны.

Структуризация рабочих листов
Назначение структуризации – разбить данные на определенные уровни детализации.
Структуризация упрощает подведение промежуточных итогов. Для подведения итогов все данные д.б. согласованы в одном направлении. Общее число уровней не более 8.
Символы структуры:
кнопки показа (скрытия) детальных данных;
последовательные уровни для строк и столбцов.
Создание структуры (автоматически):
убедиться, что в итоговых формулах содержатся ссылки на детальные данные, расположенные в одном направлении (итоги – в строках под детальными, в столбцах справа от детальных);
выделить требуемый диапазон (для части листа) или ячейку (лист);
на панели инструментов «Данные-Группа и структура-Создание структуры»;
при распознавании организации документа создается структура; в противном случае – сообщение о невозможности создания.

Автоматическое подведение промежуточных итогов
Назначение промежуточных итогов – обобщение данных («Данные-Итоги). Автоматически создаются необходимые математические выражения, вставляются строки промежуточных и общих итогов, структурируются данные.
Промежуточные итоги позволяют:
указать, как группировать данные;
вывести промежуточные и общие итоги как для одной, так и для нескольких групп в списке;
выполнить расчеты над данными.
Подготовка данных:
расположить данные в столбцах с подписями;
сгруппировать данные по некоторому признаку (сортировка, фильтрация).
Создание промежуточных итогов:
выделить ячейку списка («Данные-Итоги-Диал. окно Промежут. Итоги»);
указать, как группировать данные (при каждом изменении в – столбцы);
выбрать операцию над данными из списка операций;
выбрать данные для расчета (флажки «добавить итоги по»);
для замены старых итогов на новые (флажок «заменить текущие итоги»).

Дополнительные надстройки Excel
Дополнительные надстройки позволяют улучшить работу Excel. Запуск – «Сервис - Надстройки».
Пакет анализа (финансовый, статистический, инженерный и научный анализ):
добавляет по 5 функций в категории финансовые, дата и время;
создает новые категории и функции (математика 7 ф-ий, информационная 2 ф-ии, инженерная 40 ф-ий).
Пакет автосохранения.
Диспетчер отчетов (объединение разнотипных листов).
Поиск решения (линейная и нелинейная задача оптимизации).
Диспетчер видов (определяет имя, сохраняет виды рабочего листа или области печати).

Анализ данных средствами Excel
Вычисление таблицы подстановок (моделирование ситуации: «А что, если …».
анализ чувствительности (влияние исходных данных на результат);
«Данные-Таблица подстановок» (проводится анализ чувствительности для сколь угодно широкого диапазона исходных данных);
два варианта анализа:
варьируя одно исходное значение, просматривать результаты вычисления по одной или нескольким формулам;
варьируя два исходных значения, просматривать результаты вычисления только по одной формуле;
Пример 1: Вычисление размера единовременных выплат займа для различных процентных ставок.
Пример 2: Вычисление размера единовременных выплат займа для различных процентных ставок и величины займа.

Подбор параметра (вычисление величины, обеспечивающей определенное значение формулы).
известен результат, который необходимо получить, но неизвестен аргумент, при котором достигается это решение;
указывается формула, ее значение и аргумент (ячейка), влияющий на эту формулу;
изменяемая ячейка должна содержать значение, а не формулу и должна влиять на результат; влияние может быть опосредованным;
«Сервис-Подбор параметра».
Поиск решения (найти величину, наилучшую среди множества возможных, т.е. найти оптимальное решение при заданных ограничениях).
используется для решения системы уравнений с несколькими неизвестными и набором ограничений на решение;
«Сервис-Поиск решения».

Основные задачи, для которых требуется оптимальное решение
Ассортимент продукции
Максимализация выпуска товаров при ограничениях на сырье для производства этих товаров.
Штатное расписание
Составление штатного расписания для достижения наилучших результатов при наименьших расходах.
Планирование перевозок
Минимизация затрат на перевозку товаров.
Составление смеси
Достижение заданного качества смеси при наименьших расходах.

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

Анализ данных с помощью сценариев
Ситуация, когда необходимо иметь различные варианты решения.
Предоставляется средство для работы с различными наборами входных данных для заданной модели.
Набор носит название – сценарий и хранится на рабочем листе под определенным именем.
Создание сценария
модель, включающая в себя сценарий, должна иметь определенный набор ключевых ячеек для вводимых данных и набор ячеек, зависимых от эти данных;
Рассмотрим, например, модель прогноза продаж на несколько лет
Исходные данные: известно, что в истекшем году объем продаж составил … Этот объем состоит из: стоимости проданных товаров, общих и административных расходов и расходов на маркетинг, т.е. общих затрат и полученной прибыли.
Задается прогнозируемая оценка роста всех показателей и в столбцах соответствующих следующим годам записываются формулы, позволяющие вычислить компоненты, входящие в объем продаж.
Модель готова. Теперь при вводе новых значений для оценок роста все зависимые величины будут изменяться автоматически.

Пакет «Анализ данных»
Пакет анализа данных – набор инструментов, поставляемый в виде надстройки и предназначенный для решения трех типичных задач:
Моделирование реальных данных (построение модели ситуации);
Оценка некоторых характеристик случайной выборки;
Сглаживание временных рядов.
Генерация последовательностей – «Сервис-Анализ данных-Диалог окно-Генерация случайных чисел-Вид распределения».
По набору значений некоторой случайной величины строятся гистограммы (частотное распределение) для оценки распределения этой величины.
Исключение флуктуаций (сглаживание колебаний):
Скользящее среднее (расчет среднего значения в прогнозируемом периоде на основе средних значений переменной в предшествующих периодах);
Экспоненциальное сглаживание (предсказание значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом периоде).Обычно константа сглаживания 0.2-0.3 (ошибка текущего прогноза 20 – 30% ошибки предыдущего прогноза).

Сводные таблицы
Сводные таблицы, группируя и обобщая информацию, помещенную в таблицы и списки, дают возможность глубокого анализа этой информации.
Основное достоинство сводных таблиц – возможность быстрого изменения размещения полей таблицы.
Цели использования сводных таблиц:
Создание обобщающих таблиц (по группам, регионам, однотипным данным);
Реорганизация таблиц с помощью «перетаскивания» (обобщение итогов);
Отбор и группировка данных в сводной таблице (автоматический отбор информации по конкретной выборке);
Построение диаграмм на основе сводных таблиц.
Пользователь задает распределение информации в таблице, указывая необходимые поля и элементы.
Поле – категория. Элемент – значение внутри категории. Источник данных – рабочий лист Excel.

Создание сводных таблиц
Сводные таблицы создаются с помощью Мастера сводных таблиц «Данные – Сводная таблица».
Пример: рабочая книга содержит отчет о времени, затраченном на производство неких работ (дата, сотрудник, фирма-клиент, код работы, трудозатраты). Необходимо представить заказчику счет за выполненную работу.
Исходная таблица: дата-фамилия-клиент-код-трудозатраты
4 шага Мастера:
Создать таблицу на основе исходных данных (MS Excel);
Указать диапазон, по которому будет строиться таблица;
Определить, какая информация и как будет выводиться в сводн. табл.
Выбрать поле, по которому будут подводиться итоги и «перенести» его в область данных;
Определить размещение информации в таблице;
Указать месторасположение сводной таблицы (на этом листе, в другом месте).

Связь, внедрение и консолидация
рабочих листов
Консолидация – объединение данных с различных рабочих листов на итоговом листе.
Связь рабочего листа с ячейками другого листа:
связь изображения области рабочего листа с другим листом;
связь ячейки или диапазона одного листа с ячейкой или диапазоном другого листа.
Обработка связанных изображений:
Преимущества
связанные изображения м.б. легко открыты и обновлены;
связанные объекты в отличие от самих ячеек листа м.б. свободно перемещены, увеличены или уменьшены;
связанные изображения м.б. выведены на печать вместе с зависящими от них диаграммами;
связанные изображения м.б. ассоциированы с макросами, выполняющимися в момент выбора объекта.

Недостатки
нельзя вводить данные непосредственно в связанные изображения ячеек;
связанные изображения ячеек нельзя использовать в вычислениях; надо использовать связи с самими ячейками;
Связывание изображений
выделить исходный диапазон;
«Правка – Копировать»;
перейти на лист вставки;
+«Правка-Вставить связь с рисунком»;
на листе – изображение ячеек, выделенное черными маркерами;
в строке формул =[Имя книги] Sheet1! $A1:$D10.
Связывание с ячейками и диапазонами:
Преимущества
с помощью связей можно передавать любые данные (числа, текст) и затем использовать их в формулах;
связанные данные м.б. отформатированы как и любые другие данные;
экономия памяти (не все книги д.б. открыты);
быстрый пересчет небольших рабочих книг.

Формула внешней ссылки =‘Путь \ [Имя книги] Имя стр.’! Адрес ячейки
Замораживание ссылок – можно разорвать связь с исходным документом; при этом внешние ссылки в формулах заменяются на значения.
Сохранение связанных книг – сначала сохраняются книги, на которые есть ссылки, а затем книги, содержащие ссылки.
При загрузке книги со ссылками задается вопрос «Данный документ содержит связи. Переустановить связи?».

Консолидация данных
Можно объединить данные из исходных листов (до 255) в одном итоговом листе.
Консолидация двух типов: с созданием связей (динамическая), без создания связей (статическая).
Консолидация может выполняться:
по расположению; информация собирается из одинаково расположенных ячеек исходных листов; используется, если данные одного типа на всех листах расположены в одних и тех же позициях относительно исходных диапазонов; сами диапазоны могут располагаться по-разному;
по категориям; для объединения используются заголовки столбцов или строк; такой вид консолидации представляет большую свободу для организации данных в исходных листах; используется достаточно редко, поскольку необходимо, чтобы исходные диапазоны имели абсолютно одинаковую структуру.
Консолидировать данные можно, используя любую функцию окна «Данные-Консолидация» (по умолчанию «сумма»)

Макропрограммирование
Задача – создание простого языка программирования, используемого во всех приложениях MS. Создан Visual Basic for Applications (VBA).
Макрос и его разновидности
Макрос – единица программного кода, записанного на языке VBA.
Макрооператор – инструкция VBA. Макрос хранится на листе – модуле.
2 режима создания макросов:
- вручную («Вставка-Макрос-Модуль»); необходимо знать VBA;
- с помощью макрорекордера (автоматич. запись кодов VBA).
Разновидности макросов:
Командный макрос. Состоит из инструкций, эквивалентных командам меню или параметрам диалоговых окон. Назначение – изменение основных объектов приложения, изменение окружения.
Sub Adress( ) ….. End Sub
2. Пользовательские функции. Аналог встроенных функций Excel. Используя переданные значения аргументов, производят вычисления и возвращают результат в точку вызова.
Function Name (parameters) ….. End Function

Создание макросов
Создание макроса с помощью макрорекордера:
Активизировать режим записи макроса «Сервис-Запись макроса-Начать запись»;
Дать макросу имя;
Выполнить действия, которые надо записать;
Остановить запись «Сервис-Запись макроса-Остановить запись».
Выполнение созданного макроса «Сервис-Макрос-Имя макроса-Выполнить»
Просмотр программного кода макроса – макрос записывается на листе, который вставляется в конце книги и называется модуль.
Внесение в макрос изменений:
Вставка инструкций в существующий макрос. Записать с отметки – позволяет после остановки записи продолжить с точки останова. Отметить позицию – позволяет корректировать макрос на только что открытом листе.
Оформление изменений в виде собственной процедуры. Создается процедура Sub Name ( ) и помещается в модуль. Вызов по имени по мере необходимости.

Численные методы

Решение уравнений
Задача: найти все решения уравнения на интервале [-5,5]
Методы решения уравнений:
аналитические: решение в виде формулы
численные: приближенное решение, число
выбрать начальное приближение «рядом» с решением
по некоторому алгоритму вычисляют первое приближение, затем – второе и т.д.
вычисления прекращают, когда значение меняется очень мало (метод сходится)

Решение уравнения
1. Таблица значений функций на интервале [-5,5]
2. Графики функций (диаграмма «Точечная»)
2 решения:
начальные приближения

Решение уравнения
3. Подготовка данных
начальное приближение
целевая ячейка
Цель: H2=0

Решение уравнения
4. Подбор параметра
ошибка
решение уравнения

Решение задач оптимизации
средствами Excel

Постановка задачи проектирования:
заданная стоимость с наилучшими свойствами;
заданные свойства с наименьшей стоимостью.
Массовое производство – задача распределения ресурсов во времени.
Категория несовместных задач.
Задачи стохастической оптимизации – дополнит. условия.
В итоге:
задача проектирования изделия;
задача распределения ресурсов;
задача стохастической оптимизации;
выбор критерия;
анализ решения.

Математическая модель решения задачи оптимизации:
Целевая функция (критерий оптимизации – макс., мин., заданное значение).
Ограничения (устанавливают зависимости между переменными).
Граничные условия (пределы изменения переменных в оптимальном решении).
Допустимое решение должно удовлетворять п.п. 2 и 3.

Классы задач оптимизации

Аналитический метод решения
найти вершины области допустимых решений (ОДР) как точки пересечения ограничений;
определить значения ЦФ в вершинах;
найти оптимальную вершину (по max или min ЦФ);
найти координаты вершины – искомые оптимальные значения переменных.
Аналитический метод решения задачи линейного программирования – симплекс-метод.

Оптимизация
Оптимизация – это поиск оптимального (наилучшего) варианта в заданных условиях.
Оптимальное решение – такое, при котором некоторая заданная функция (целевая функция) достигает минимума или максимума.
Постановка задачи:
целевая функция
ограничения, которые делают задачу осмысленной
(расходы, потери, ошибки)
(доходы, приобретения)
Задача без ограничений: построить дом
при минимальных затратах.
Решение: не строить дом вообще.

Оптимизация
локальный минимум
глобальныйминимум
обычно нужно найти глобальный минимум
большинство численных методов находят только локальный минимум
минимум, который найдет Excel, зависит от выбора начального приближения («шарик на горке скатится в ближайшую ямку»)

Поиск минимума функции
1. Строим график функции (диаграмма «Точечная»)
2. Подготовка данных
начальное приближение
начальное приближение
целевая
ячейка

Поиск минимума функции
3. Надстройка «Поиск решения»
изменяемые ячейки:
E2
D2:D6
D2:D6; C5:C8
целевая
ячейка
ограничения
A1 <= 20
B2:B8 >= 5
A1 = целое

Параметры оптимизации

Оптимизация
Надстройка «Поиск решения» позволяет:
искать минимум и максимум функции
использовать несколько изменяемых ячеек и диапазонов
вводить ограничения (<=, >=, целое, двоичное)

Статистика

Ряд данных и его свойства
Ряд данных – это упорядоченный набор значений
Основные свойства (ряд A1:A20):
количество элементов =СЧЕТ(A1:A20)
количество элементов, удовлетворяющих некоторому условию:
= СЧЕТЕСЛИ(A1:A20;"<5")
минимальное значение =МИН(A1:A20)
максимальное значение =МАКС(A1:A20)
сумма элементов =СУММ(A1:A20)
среднее значение =СРЗНАЧ(A1:A20)

Дисперсия
Для этих рядов одинаковы МИН, МАКС, СРЗНАЧ
Дисперсия («разброс») – это величина, которая характеризует разброс данных относительно среднего значения.

Дисперсия
среднее арифметическое
квадрат отклонения от среднего
средний квадрат отклонения от среднего значения

Дисперсия и СКВО
Стандартная функция
=ДИСПР(A1:A20)
Что неудобно:
если измеряется в метрах, то – в м2
Функции – Другие – Статистические
СКВО = среднеквадратическое отклонение
=СТАНДОТКЛОНП(A1:A20)

Взаимосвязь рядов данных
Два ряда одинаковой длины:
Вопросы:
есть ли связь между этими рядами (соответствуют ли пары какой-нибудь зависимости )
насколько сильна эта связь?

Взаимосвязь рядов данных
Ковариация:
Как понимать это число?
если
если
если
увеличение приводит к увеличению
в среднем!
увеличение приводит к уменьшению
связь обнаружить не удалось
Что плохо?
единицы измерения: если в метрах, в литрах,
то – в м⋅л
зависит от абсолютных значений и , поэтому ничего не говорит о том, насколько сильна связь

Взаимосвязь рядов данных
Коэффициент корреляции:
– СКВО рядов и
безразмерный!
Как понимать это число?
если : увеличение приводит к увеличению
если : увеличение приводит к уменьшению
если : связь обнаружить не удалось
=КОРРЕЛ(A1:A20;B1:B20)

Взаимосвязь рядов данных
Как понимать коэффициент корреляции?
: очень слабая корреляция
: слабая : средняя
: сильная
: очень сильная
: линейная зависимость
: линейная зависимость

Восстановление зависимостей

Восстановление зависимостей
Два ряда одинаковой длины:
задают некоторую неизвестную функцию
Зачем:
найти в промежу-точных точках
(интерполяция)
найти вне диапазона измерений
(экстраполяция, прогнозирование)

Какое решение нам нужно?
Вывод: задача некорректна, поскольку решение неединственно.

Восстановление зависимостей
Корректная задача: найти функцию заданного вида, которая лучше всего соответствует данным.
Примеры:
линейная
полиномиальная
степенная
экспоненциальная
логарифмическая

Что значит «лучше всего соответствует»?
заданные пары значений
Метод наименьших квадратов (МНК):
чтобы складывать положительные значения
решение сводится к системе линейных уравнений (просто решать!)

МНК для линейной функции
неизвестно!
a
-b
c

Коэффициент достоверности
заданные пары значений
Крайние случаи:
если график проходит через точки:
если считаем, что y не меняется и :
– среднее значение

Восстановление зависимостей
Диаграмма «График»:
ПКМ

Восстановление зависимостей

Восстановление зависимостей

Восстановление зависимостей
Сложные случаи (нестандартная функция):
Алгоритм:
выделить ячейки для хранения
построить ряд для тех же
построить на одной диаграмме ряды и
попытаться подобрать так, чтобы
два графика были близки
вычислить в отдельной ячейке
функции: СУММКВРАЗН – сумма квадратов разностей рядов
ДИСПР – дисперсия
Поиск решения:

Моделирование

Модель деления
– начальная численность
– после 1 цикла деления
– после 2-х циклов
Особенности модели:
не учитывается смертность
не учитывается влияние внешней среды
не учитывается влияние других видов

Рождаемость и смертность
– коэффициент рождаемости
– коэффициент смертности
Особенности модели:
не учитывается влияние численности N и внешней среды на K
не учитывается влияние других видов на K
Коэффициент изменения
численности

Влияние численности и внешней среды
A – коэффициент устойчивости вида
B – коэффициент среды обитания
Варианты:
устанавливается постоянная численность
постоянно меняется (колебания)
вымирание

Влияние других видов
Ni – численность белок, Mi – численность бурундуков
K2, K4 – взаимное влияние
если K2 >K1 или K4 >K3 – враждующие виды