- •Защита ячеек листа от изменений
- •Защита листов книги
- •Шифрование файла
- •Анализ данных при помощи диаграмм. Прогнозирование значений. Линия тренда. Виды аппроксимирующих функций. Установка вида эмпирической формулы и численных значений неизвестных параметров.
- •Создание диаграммы:
- •Изменение диаграммы:
- •Прогнозирование значений
- •Автоматическое заполнение значений
- •Заполнение значений вручную
- •Вычисление тенденций с помощью добавления линии тренда на диаграмму
- •Прогнозирование значений с функцией
- •Выполнение регрессионного анализа с надстройкой «Пакет анализа»
- •Анализ данных при помощи структуры. Автоматическое создание структуры. Создание структуры вручную.
- •Анализ данных при помощи консолидации. Объединение данных при помощи консолидации. Функции консолидации.
- •Создание промежуточных итогов.
- •Сводные таблицы. Способы создания.
- •Сводные таблицы. Создание групп и вычисляемых полей. Анализ данных с помощью сводной таблицы.
- •Группировка и обновление данных в сводных таблицах
- •Сводные диаграммы. Анализ данных с помощью сводной диаграммы
- •Анализ данных "что-если". Таблица подстановки с одним неизвестным.
- •Создание таблицы данных с одной переменной
- •Добавление формулы в таблицу данных с одной переменной
- •Анализ данных "что-если". Таблица подстановки с двумя неизвестными
- •Создание таблицы данных с двумя переменными
- •Ускорение пересчета листа, содержащего таблицы данных
- •Подбор параметров
- •Команда Поиск решения
- •Диспетчер сценариев «что – если»
- •Системы диагностики. Анализ предметной области. Постановка задачи. Решение задач диагностики с помощью механизма "весового фактора".
- •1. Системы принятия решения.
- •Анализ данных "что-если". Диспетчер сценариев. Определение сценария. Просмотр и модификация сценария. Объединение сценариев. Создание отчёта по сценарию
- •Информационные модели предметных областей. Анализ предметной области. Выявление сущностей и связей между ними. Построение информационно-логической модели предметной области.
Ускорение пересчета листа, содержащего таблицы данных
Нажмите кнопку Microsoft Office, щелкните Параметры Excel, а затем выберите категорию Формулы.
В разделе Параметры вычисления установите переключатель Вычисления в книге в положение автоматически, кроме таблиц данных.
Совет. Того же результата можно добиться, если на вкладке Формулы в группе Вычисления щелкнуть стрелку рядом с пунктом Параметры вычислений, а затем выбрать параметр Автоматически, кроме таблиц данных.
Примечание. Если выбран этот вариант вычисления, то при пересчете книги таблицы данных не пересчитываются. Чтобы выполнить пересчет таблицы данных вручную, выделите ее формулы и нажмите клавишу F9.
Оптимизация данных с одним неизвестным. Инструмент Подбор параметра. Условия применения. Точность и множественность решений. Оптимизация данных со многими неизвестными. Инструмент Поиск решения. Условия применения. Определение цели, переменных, ограничений. Сохранение поиска решения. Создание отчётов.
Часто уравнения не имеют точного аналитического решения. Тогда их решают методом последовательных приближений (итераций) неизвестных параметров так, чтобы они давали минимум ошибки целевой функции.
Для численного решения уравнений с одним неизвестным в Excel имеется эффективный инструмент Подбор параметра. Целевая функция может быть линейной, квадратичной, третьей и выше степени.
Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если". Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра» выбрав команду Подбор параметра в меню Сервис. При подборе параметра Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат.
Подбор параметров
Основной командой для решения оптимизационных задач в Excel является команда Сервис/Подбор параметра. Эта команда определяет неизвестную величину, приводящую к требуемому результату. Если команда Подбор параметра отсутствует в меню Сервис, выполните команду Сервис/Надстройка и установите флажок Пакет анализа в окне диалога Надстройка Для работы с командой Подбор параметра необходимо подготовить лист, чтобы в листе находились:
формула для расчета;
пустая ячейка для искомого значения;
другие величины, которые используются в формуле. Ссылка на пустую ячейку должна обязательно присутствовать в формуле, так как именно она является переменной, значение которой ищет Excel. Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи. Такой процесс называется итерацией, и продолжается он до тех пор, пока редактор не выполнит 100 попыток или не найдет решения, лежащее в пределах точности 0,001 от точного значения (настройка этих параметров осуществляется с помощью команды Сервис/Параметры, вкладка Вычисления) Оптимизация с помощью команды Подбор параметров выполняется так:
1. Создайте лист, например, с формулой =B1*B2 в ячейке B3, пустой (переменной) ячейкой (B2) и другими данными (B1), которые могут понадобиться при вычислениях. Например, необходимо определить количество книг по цене 23,75 грн., которые необходимо продать, чтобы объем продаж составил 10000,00 грн
2. Выделите ячейку листа (B3), в которой содержится формула (эта ячейка появится в поле "Установить в ячейке" в окне диалога Подбор параметра). Выполните команду Сервис/Подбор параметра. Открывается окно диалога Подбор параметра..
3. Введите в текстовое поле Значение число, соответствующее объему продаж - 10000. Переместите курсор в текстовом поле Изменяя значения ячейки. Выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Выделенная ячейка (B2) выделяется на листе рамкой. Нажмите кнопку ОК, чтобы найти решение.
После завершения итерационного цикла в окне диалога Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа. Решение показывает, что для достижения объема продаж 10000 грн. необходимо продать 421 книгу по цене 23,75 грн. Для закрытия окна диалога Результат подбора параметра щелкните на кнопке ОК.