Создание таблицы данных с двумя переменными
В таблицах данных с двумя переменными используется одна формула с двумя наборами входных значений. Формула должна ссылаться на две различных ячейки входных данных.
1. В ячейку листа введите формулу, которая ссылается на две ячейки ввода.
В приведенном ниже примере, где исходные значения формулы введены в ячейки B3, B4 и B5, формулу =ПЛТ(B3/12;B4;-B5) следует ввести в ячейку C2.
2. В том же столбце ниже формулы введите значения подстановки для первой переменной.
В приведенном ниже примере различные процентные ставки следует ввести в ячейки C3, C4 и C5.
3. Введите значения подстановки для второй переменной справа от формулы в той же строке.
Введите срок погашения ссуды (в месяцах) в ячейки D2 и E2.
4. Выделите диапазон ячеек, содержащий формулу (C2), строки и столбцы значений (C3:C5 и D2:E2), а также ячейки, в которых должны находиться вычисленные значения (D3:E5).
В данном случае выделите диапазон C2:E5.
5. На вкладке Данные в группе Средства обработки данных выберите команду Анализ условия, а затем выберите в списке пункт Таблица.
6. В поле Подставлять значения по столбцам в введите ссылку на ячейку ввода для значений подстановки в строке.
В поле Подставлять значения по столбцам в введите значение B4.
7. В поле Подставлять значения по строкам в введите ссылку на ячейку ввода для значений подстановки в столбце.
В поле Подставлять значения по строкам в введите значение B3.
8. Нажмите кнопку ОК.
Ускорение пересчета листа, содержащего таблицы данных
1. Нажмите кнопку Microsoft Office, щелкните Параметры Excel, а затем выберите категорию Формулы.
2. В разделе Параметры вычисления установите переключатель Вычисления в книге в положение автоматически, кроме таблиц данных.
Совет. Того же результата можно добиться, если на вкладке Формулы в группе Вычисления щелкнуть стрелку рядом с пунктом Параметры вычислений, а затем выбрать параметр Автоматически, кроме таблиц данных.
Примечание. Если выбран этот вариант вычисления, то при пересчете книги таблицы данных не пересчитываются. Чтобы выполнить пересчет таблицы данных вручную, выделите ее формулы и нажмите клавишу F9.
32. Оптимизация данных с одним неизвестным. Инструмент Подбор параметра. Условия применения. Точность и множественность решений. Оптимизация данных со многими неизвестными. Инструмент Поиск решения. Условия применения. Определение цели, переменных, ограничений. Сохранение поиска решения. Создание отчётов.
Часто уравнения не имеют точного аналитического решения. Тогда их решают методом последовательных приближений (итераций) неизвестных параметров так, чтобы они давали минимум ошибки целевой функции.
Для численного решения уравнений с одним неизвестным в 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 грн. Для закрытия окна диалога Результат подбора параметра щелкните на кнопке ОК.
Команда Поиск решения
Для решения сложных задач, требующих применения линейного и нелинейного программирования, а также методов исследования операций применяется надстройка - Поиск решения. Чтобы использовать надстройку Поиск решения не обязательно знать методы программирования и исследования операций, но необходимо определять, какие задачи можно решать этими методами.
Пользователь должен уметь с помощью диалоговых окон надстройки Поиск решения правильно сформулировать условия задачи, и если решение существует, то “Поиск решения” отыщет его. В основе надстройки лежат итерационные методы.
В том случае, когда оптимизационная задача содержит несколько переменных величин, для анализа сценария необходимо воспользоваться надстройкой Поиск решения. “Поиск решения” позволяет использовать одновременно большое количество изменяемых ячеек (до 200) и задавать ограничения для изменяемых ячеек.
Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения:
· Существует единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или же равным, какому-то конкретному значению.
· Формула в этой целевой ячейке содержит ссылки на ряд изменяемых ячеек. Поиск решения заключается в том, чтобы подобрать такие значения переменных в изменяемых ячейках, которые бы обеспечили оптимальное значение для формулы в целевой ячейке.
· Может быть задано некоторое количество ограничений — условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек.
Постановка задачи
Первым шагом при работе с командой Поиск решения является создание специализированного листа. Для этого необходимо создать целевую ячейку, в которую вводится основная формула.
Кроме того, лист может включать другие значения и формулы, использующие значения целевой и переменных ячеек. Формула в целевой ячейке должна опираться в вычислениях на значения переменных ячеек.
После того, как задача оптимизации будет подготовлена на листе, можно приступать к работе.
1. Выделите на листе целевую ячейку, в которую введена формула.
2. Выполните команду Сервис/Поиск решения. Открывается окно диалога Поиск решения. Поскольку была выделена ячейка, в текстовом поле «Установить целевую ячейку» появится правильная ссылка на ячейку. В группе «Равной» переключатель по умолчанию устанавливается в положение «Максимальному значению».
3. Перейдите к полю "Изменяя ячейки" и введите переменные ячейки листа
4. Добавьте ограничения на переменные в изменяемых ячейках. Для ввода ограничений нажмите кнопку Добавить, чтобы задать первое ограничение в окне диалога, затем можно ввести второе, третье и т.д.
5. Когда оптимизационная задача будет готова к выполнению, можно нажать кнопку Выполнить для получения ответа. Появится окно диалога с описанием результатов процесса оптимизации.
6. Чтобы отобразить найденное решение в ячейках листа, установите переключатель "Сохранить найденное решение" и нажмите кнопку ОК. Найденная максимальная величина помещается в целевую ячейку, а переменные ячейки заполняются оптимальными значениями переменных, которые удовлетворяют установленным ограничениям.
Диспетчер сценариев «что – если»
При работе с командами Подбор параметра и Поиск решения не существует удобного способа сравнения результатов вычислений – при каждом изменении данных предыдущее значение пропадает.
Чтобы устранить эти ограничения, разработчики Excel создали Диспетчер сценариев, помогающий работать с несколькими моделями «что – если». Командой Сервис/Сценарии можно создавать новые и просматривать существующие сценарии для решения задач, и отображать консолидированные отчеты.
Создание сценария
Сценарием называется модель «что – если», в которую входят переменные ячейки, связанные одной или несколькими формулами. Перед созданием сценария необходимо спроектировать лист так, чтобы на нем была хотя бы одна формула, зависящая от ячеек, которые могут принимать различные значения. Например, может возникнуть потребность в сравнении лучшего и худшего сценариев.
Создание сценариев происходит следующим образом:
· Выполните команду Сервис/Сценарии. Открывается изображение окна диалога Диспетчер сценариев.
· Нажмите кнопку Добавить, чтобы создать первый сценарий. Откроется окно диалога Добавление сценария.
· Введите Лучший вариант (или любое другое имя) в поле Название сценария, затем с помощью окон диалога введите изменяемые ячейки. Когда этот сценарий будет готов, введите следующий.
· Нажмите кнопку Добавить, чтобы создать второй сценарий. Введите название Худший вариант. После завершения создания двух сценарием можно приступить к просмотру результатов.
· Закройте окно диалога Диспетчер сценариев кнопкой Закрыть.
Просмотр сценария
Excel сохраняет сценарии вместе с листом текущей книги, и просмотр их командой Сервис /Сценарии возможен только при открытии данного листа. Просмотр сценария выполняется следующим образом:
· Выполните команду Сервис/Сценарии. Открывается окно диалога:
· Выберите из списка сценарий для просмотра.
· Нажмите кнопку Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе.
· Выберите из списка другие сценарии и воспользуйтесь кнопкой Вывести для сравнения результатов моделей «что – если». После завершения нажмите кнопку Закрыть. Значения последнего активного сценария остаются в ячейках листа.
Создание отчетов по сценарию
Сравнивать различные сценарии можно, переходя от сценария к сценарию с помощью кнопки показать в окне диалога Диспетчер сценариев, но иногда возникает необходимость в создании отчета с обобщенной информацией о различных сценариях листа.
Эту задачу можно выполнить с помощью кнопки Отчет в окне диалога Диспетчер сценариев. Созданный сводный отчет будет автоматически отформатирован и скопирован на новый лист текущей книги.
Создание отчета по сценарию происходит следующим образом:
· Выполните команду Сервис/Сценарии. Откроется окно диалога Диспетчер сценариев.
· Нажмите кнопку Отчет. Открывается окно диалога Отчет по сценарию, в котором предлагается выбрать ячейки, входящие в отчет, а также его тип. Отчет типа структура представляет собой форматированную таблицу, которая выводится на отдельном листе. Отчет сводная таблица является специальной таблицей, которую можно настраивать за счет перестановки столбцов и строк.
33. Системы диагностики. Анализ предметной области. Постановка задачи. Решение задач диагностики с помощью механизма "весового фактора".
Системы принятия решений.
Системы принятия решения.
1.1. Основные понятия.
Системы искусственного интеллекта – это программная имитация человеческого мышления.
Системы искусственного интеллекта, которые разработаны для конкретной предметной области, называются экспертной системой или системой принятия решений.
Экспертные системы применяются в медицине (диагностика болезни), в военном деле (распознавание целей), бизнеса (системы поддержки и принятия решений).
В разработке систем принимают участие:
1) эксперты в данной области, они разрабатывают наиболее важные характеристики предметной области. Они называют факты или атрибуты, а так же вырабатывают правила принятия решения.
2) Специалисты инженерии знаний. Они разрабатывают реализацию экспертной системы на компьютере. Факты и правила принятия решений хранятся в специальной организованной области памяти, которая называется базой знаний.
Информация о состоянии предметной области в данный момент, которая представляется экспертной системе для анализа, называется базой данных.
Задание 1
Разработать систему принятия решения для аттестации знаний студентов по разделу «Текстовой процессор Word». База знаний имеет вид:
| № п/п | Атрибут | Весовой фактор атрибута | |
| Умеет создавать документ | |||
| Умеет редактировать документ | |||
| Умеет форматировать документ | |||
| Умеет создавать документ со списками | |||
| Умеет работать с табуляторами | |||
| Умеет работать с колонками | |||
| Умеет работать с колонтитулами | |||
| Умеет нумеровать страницы | |||
| Умеет работать с панелью «рисование» | |||
| Умеет вставлять рисунки и схемы | |||
| Умеет работать с таблицами | |||
| Умеет вырезать и вставлять рисунки с использованием редактора Paint | |||
| Умеет создавать макросы | |||
| Умеет проводить слияние документов из разных приложений |
Правила вывода:
Если студент набрал меньше 330 баллов, оценка «неудовлетворительно»
Если сумма баллов 330-390 – оценка «удовлетворительно».
При сумме баллов 390-440 – оценка «хорошо».
Если сумма баллов больше 440 – оценка «отлично».
Решение:
Разработка системы принятия решений включает три этапа:
1. Разработка дерева принятия решений.
2. Разработка базы данных.
3. Компьютерная реализация.
1. Разработка дерева принятия решений.
Это дерево строится на основе правил вывода и представляет собой графическое изображение схемы решений. Дерево имеет вершины и ветви. Вершины обозначаются либо окружностями или эллипсами (проверка условий), либо прямоугольниками (принятие решений). Ветви соединяют вершины и показывают направление решений.
Сумма баллов абитуриента – S.
2. База данных создается на основе базы знаний, в которую добавляется столбец ответов, если ответ положительный весовой фактор атрибута сохраняется, если отрицательный фактор обнуляется.
3. Реализация экспертной системы в электронной таблице.
1.1. В ячейки A1:D3 введем заголовки и комментарии.
1.2. В A4:D17 размещается база знаний.
1.3. В C4:C17 – ответы.
1.4. В D4:D17 вводим формулы для обработки ответов.
1.1.1. в D4 вводится формула =B4*C4
1.1.2. копируем ее в ячейки D5:D17
1.5. В строке 18 вычислим суммарный весовой фактор S.
1.6. В 20-ой введем формулу для принятия решения.
35. Анализ данных "что-если". Диспетчер сценариев. Определение сценария. Просмотр и модификация сценария. Объединение сценариев. Создание отчёта по сценарию
Сценарии являются частью блока задач, которые называются инструментами анализа «что - если». Сценарий – это инструмент, позволяющий моделировать различные физические, экономические и другие виды задач. Он представляет собой зафиксированный в памяти компьютера набор значения ячеек рабочего листа.
Рассмотрим задачу математического маятника, для этого построим таблицу исходных данных и таблицу результатов.
Для того, чтобы получить таблицу исходных данных:
1. Введем названия исходных данных и значения;
2. Для того чтобы не загромождать отчет лишними таблицами, присвоим имена:
1. Выделите диапазон А2:В7.
2. В пункте меню Вставка выберете пункт Имя, затем Создать.
3. Откроется окно Создать имена. Установите в нем флажок в столбце слева.
4. Нажмите Ok.
Теперь, если выделить какую-либо ячейку, то в поле Имена, находящемся справа от строки формул, будет выведено ее имя
Теперь создадим таблицу результатов:
1. В столбец D введем то, что нам надо высчитать.
2. В ячейку E3 введем формулу: =2*ПИ()*КОРЕНЬ(B3/B5).
3. В ячейку E4 введем формулу: =B6*B3/COS(B6).
4. В ячейку E5 введем формулу: =КОРЕНЬ(B5/B3).
5. В ячейку E6 введем формулу: =E4*COS(E5*B7+B6).
6. В ячейку E7 введем формулу: =E4*E5*SIN(E5*B7+B6).
7. В ячейку E8 введем формулу: =B4*B5*B3*(1-COS(E6/B3)).
8. В ячейку E9 введем формулу: =B4*(E7*E7)/2.
9. В ячейку E10 введем формулу: =E8+E9.
Теперь создадим сценарий. Его можно создать двумя способами:
1. Раскрывающийся список Сценарий на панели инструментов.
2. При помощи Диспетчера сценариев.
Рассмотрим использование списка Сценарий:
1. Установим список Сценарий на панели инструментов:
1. В пункте меню Сервис выберем пункт Настройка.
2. Откроется диалоговое окно, в котором откроем вкладку Команды и выберем в левом списке элемент Сервис.
3. В правом списке найдем элемент Сценарий и перетащим его на панель инструментов.
4. Нажмите Ok.
2. Введите в ячейки исходных данных некоторые значения.
3. Выделите ячейки B3-B7.
4. Установите указатель в области списка Сценарий и введите имя сценария.
5. Нажмите Enter.
Чтобы создать другой сценарий, надо изменить значения исходных данных, а дальше проделать действия описанные ранее.
Чтобы перейти от одного сценария к другому, надо установить указатель на стрелку справа от списка Сценарий, щелкнуть кнопкой мыши для открытия списка сценариев и выбрать нужный сценарий из списка.
Теперь рассмотрим Диспетчер сценариев. Этот способ более медленный, но довольно эффективный.
1. Введите в ячейки исходных данных новые значения и выделите эти значения.
2. В пункте меню Сервис выберете пункт Сценарий.
3. Откроется диалоговое окно Диспетчер сценариев. Нажмите в нем кнопку Ok.
4. Откроется диалоговое окно Добавление сценария. В поле Название сценария введите имя создаваемого сценария. Если необходимо, измените диапазон изменяемых ячеек в поле Изменяемые ячейки.
5. Нажмите кнопку Ok.
6. Откроется диалоговое окно Значения ячеек сценария. Здесь вы можете изменить значения выбранных ячеек.
7. Нажмите кнопку Ok.
8. Снова откроется диалоговое окно Диспетчер сценариев, в списке Сценарий появиться имя вновь созданного сценария. Нажмите кнопку Закрыть.
Чтобы перейти от одного сценария к другому при помощи диспетчера сценариев надо:
1. В пункте меню Сервис выберете пункт Сценарий.
2. Откроется окно Диспетчер сценариев, в списке окна выберете имя нужного сценария и дважды щелкните кнопкой мыши.
3. Нажмите кнопку Закрыть.
Уже созданные сценарии можно изменять, также двумя способами: при помощи списка и диспетчера сценариев
Проще всего изменить его с помощью списка. Для этого:
1. В раскрывающемся списке Сценарий на панели инструментов выберете нужный сценарий.
2. Отредактируйте значения в ячейках.
3. Остановите указатель в области списка Сценарий и щелкните кнопкой мыши.
4. Появиться окно следующего содержания: Переопределить сценарий Название сценария, основываясь на текущих значениях ячеек. Нажмите кнопку Да.
При помощи Диспетчера сценариев это делается так:
1. В пункте меню Сервис выберете пункт Сценарий.
2. Выйдет диалоговое окно Диспетчер сценариев.
3. Выберете изменяемый сценарий из списка двойным щелчком или с помощью кнопки Изменить.
4. Откроется диалоговое окно, в котором можно изменить имя сценария а также диапазон ячеек.
5. Нажмите кнопку Ok.
6. Откроется диалоговое окно Значения ячеек сценария. В нем можно изменить значения ячеек.
7. Нажмите кнопку Ok.
8. Откроется окно Диспетчер сценариев.
9. Нажмите кнопку Закрыть.
Иногда в задачах возникает необходимость исследовать зависимость результата от некоторого подмножества исходных данных, при этом значения остальных исходных данных фиксировано. Можно рассматривать всевозможные комбинации исходных данных. Для этого в раскрытом списке сценариев создают различные сценарии. Например: нормальное ускорение, большое ускорение, груз1, груз2, время1, время2. Такие сценарии создаются обычным способом, но в качестве диапазона изменяемых ячеек для каждого из них взято по одной ячейке.
В результате работы со сценариями мы получаем большие документы, но иногда их надо распечатать на принтере. Для таких случаев Excel предлагает создавать отчеты. Существует два вида отчетов:
· итоговый отчет
· отчет в виде сводной таблицы
Рассмотрим сначала итоговый отчет:
1. В пункте меню Сервис выберем пункт Сценарий.
2. В диалоговом окне Диспетчер сценариев. нажмите кнопку Отчет.
3. Откроется диалоговое окно Отчет по сценарию.
4. Установите флажок около типа отчета – структура.
5. При необходимости измените диапазон ячеек в поле Ячейки результата.
6. Нажмите Ok.
Рассмотрим отчет в виде сводной таблицы:
1. В пункте меню Сервис. выберем пункт Сценарий.
2. В диалоговом окне Диспетчер сценариев нажмите кнопку Отчет.
3. Откроется диалоговое окно Отчет по сценарию.
4. Установите флажок около типа отчета – сводная таблица.
5. При необходимости измените диапазон ячеек в поле Ячейки результата.
6. Нажмите Ok.
Со временем может накопиться очень много сценариев их все можно объединить. Для этого:
1. В пункте меню Сервис выберем пункт Сценарий.
2. В окне Диспетчер сценариев нажмите кнопку Объединить.
3. Откроется диалоговое окно Объединение сценариев.
4. Выберете в списке Книга рабочую книгу, содержащую необходимый сценарий.
5. В списке Лист выберете нужный лист.
6.Нажмите кнопку Ok.