Информатика_Excel_лаб_раб. Л. Г. Чернова пакеты прикладных программ раздел iii. Microsoft Excel лабораторный практикум новотроицк 2010 2 удк 681. 31 378 ббк 32.
 Скачать 1.91 Mb.
|
|
I Цель работы Научиться задавать формат данных по заданным условиям освоить использование надстройки Поиск решения при нахождении оптимального значения формулы. II Теоретическое введение и упражнения Условное форматирование Удобной возможностью Excel является различное форматирование ячеек в зависимости от информации, содержащейся в них. Например, можно залить ячейку зеленым цветом, если число в ней положительное, или залить ее красным цветом, если число отрицательное. То есть дать разное форматирование ячейке в зависимости от условия, которое может выполняться или не выполняться. Поэтому данный вид форматирования называется условным форматированием. В качестве условного форматирования можно задавать параметры шрифта, обрамления или заливки ячеек. Рисунок 7.1 – Выбор правила выделения значений 105 Для вызова условного форматирования необходимо сделать активной ячейку, к которой будет применено условное форматирование, после чего выберать команду меню Главная / Стили Условное форматирование. В галерее выбрать команду Правила выделения ячеек а затем в подчиненном меню указать условие для форматирования, рисунок 7.1, ив появившемся диалоговом окне настроить параметры условия и выбрать способ выделения, рисунок 7.2. Рисунок 7.2 - Диалог настройки условного форматирования Название и содержание окна настройки параметров условия зависит от выбранного условия. Например, при выборе условия Между можно указать минимальное и минимальное значения, а также выбрать способ выделения. При выборе условия Дата можно выбрать отношение выделяемых ячеек к сегодняшней дате Вчера, Сегодня, Завтра, За последние 7 дней и т.д.) и выбрать способ выделения. Настройка условного форматирования Иногда в качестве условия форматирования нужно использовать не значение данной ячейки, а информацию из других ячеек или информацию, которая в ячейках не хранится, например информацию о текущей дате, времени итак далее. В этом случаев качестве условия нужно выбирать элемент формула. Щелкнув по кнопке Условное форматирование группы Стили вкладки Главная и выбрать команду Создать правило. В появившемся окне выберать Использовать формулу для определения форматируемых ячеек, рисунок 7.3. 106 Рисунок 7.3 – Окно создания правил форматирования В поле вводится формула, которая используется в качестве условия, она должна быть логической, то есть результатом должно быть не число, алогическое значение ИСТИНА или ЛОЖЬ. Например, если необходимо, чтобы другое форматирование применялось, когда значение в ячейке А больше значения в ячейке В, нужно ввести формулу =А1>В1. После ввода формулы нажать кнопку Формат и настроить форматирование ячейки. Если было создано условие, но потом решено удалить условие, то надо нажать выполнить Главная / Стили Условное форматирование Удалить правила. Будет удален способ форматирования по условий. Упражнение 7.1. Сформировать арифметическую прогрессию. При формировании последовательности учитывать следующие правила если число лежит в диапазоне от 10 до 20, то числа прописываются курсивом если число лежит в 107 диапазоне от 20 до 40, то выводится полужирным курсивом в рамке если число больше 40, то оно выводится красными цифрами на голубом фоне. Введите в ячейку А число 3, в блок В1:В15 числовую последовательность 1, 2, ..., 15, в С введите формулу =$А$1*В1 и скопируйте ее в С2:С15. Материал для экспериментов подготовлен Выделите блок С1:С15. Выберите вменю Главная / Стили Условное форматирование Установите Правила выделения ячеек Между В диалоговом окне укажите условие между 10 и 20, и укажите Пользовательский формат / Курсив. Аналогично укажите остальные правила форматирования в соответствии с заданием. Удалите правило форматирования.Для этого выделите С1:С15, выполните Главная / Стили Условное форматирование Удалить правила. Условное форматирование можно задавать формулой. Упражнение 7.2. Сформировать числовую последовательность от 1 до 15. Используя условное форматирование выделить полужирным шрифтом числа, кратные пяти, ноне кратные двум. Введите в ЕЕ числовую последовательность 1, 2, ..., 15. Выделите блок ЕЕ, выберите Главная / Стили Условное форматирование / Создать правило / Использовать формулу для определения форматируемых ячеек. В диалоговом окне укажите формулу. В поле ввода введите формулу =И(ОСТАТ(Е1;5)=0;ОСТАТ(Е1;2) <> 0). Щелкните кнопку Формат и выберите полужирный шрифт. Будут выделены числа 3, 9, 15. Примечание Для ввода формулы сформируйте формулу с помощью мастера функции в любой ячейке и вставьте е, предварительно скопировав в поле для формулы Ошибочные значения 108 Часто при выполнении вычислений в Excel возникают ошибки. Например ДЕЛО — попытка деления на нуль. Возможно, делитель ссылается на пустую ячейку ИМЯ — используется имя, отсутствующее в рабочей книге. Скорее всего, неверно набрано имя функции или при наборе строки Вы забыли окружить ее двойными кавычками ЗНАЧ — в формуле имеется ссылка на текстовое значение, которое Excel не может преобразовать в числовое после ввода или редактирования табличной формулы нажата клавиша [Enter], а не [Ctrl]+[Shift]+[Enter]; при вычислении матричных функций матрицы имеют несогласованные размеры ССЫЛКА — отсутствует именованный блок, на который ссылается формула (скорее всего, он был удален #Н/Д — значение недоступно. Это значение возвращают функции просмотра (ВПР, ГПР, ПОИСКПОЗ и т.д.), если они не могут отыскать подходящего значения в инфо_таблице. Полный перечень ошибочных значений и возможных причин их возникновения можно найти в Справке Создание формул и проверка книг Разрешение вопросов, возникающих при появлении ошибок. Тип ошибочного значения можно выяснить с помощью функции ТИП.ОШИБКИ. Для выявления ошибок в процессе вычислений служат три функции ЕОШ, ЕОШИБКА и ЕНД. Все эти три функции возвращают логическое значение ИСТИНА, если их аргумент — ошибочное значение, и ЛОЖЬ — в противном случае. ЕОШИБКА реагирует на все ошибочные значения, ЕОШ — на все, кроме #Н/Д, а ЕНД — только на #Н/Д. Поиск решения Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется 109 целевой. При этом, чтобы получить нужный результат, происходят изменения в группе ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Такие ячейки называются влияющими ячейками. Процедура одновременного изменения значений в нескольких влияющих ячейках достаточно трудоемкая, и, чтобы сузить множество значений, используемых при поиске решения, накладываются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки. Чтобы использовать процедуру поиска решения, программа должна быть соответствующим образом настроена. Для настройки необходимо с помощью кнопки Office вызвать параметры Excel / Надстройки / Поиск решения. Нажать Ок. После будет произведена доустановка неустановленного инструмента. Элемент управления Поиск решения появится во вкладке Данные, группе Анализ. Чтобы начать поиск решения, следует вызвать команду Поиск решения. Будет открыт диалог настройки параметров поиска, рисунок 7.4. Поле ввода Установить целевую ячейку используется для указания целевой ячейки, решение в которой нужно найти. После нажатия кнопки в правой части поля ввода, свернется диалоги можно выбрать нужную ячейку с помощью мыши. Затем нажать кнопку в правой части свернутого диалога, чтобы развернуть его. Переключатель Равной определяет цель поиска. Можно выбрать максимальное значение, минимальное значение или конкретное значение, которое указывается в поле ввода справа от положения переключателя. Поле ввода Изменяя ячейки служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле Установить целевую ячейку. Можно ввести ячейки с помощью клавиатуры или свернуть диалоги выбрать ячейки с помощью мыши. Также можно нажать кнопку Предположить, и программа сама попробует найти ячейки, влияющие на формулу. 110 Рисунок 7.4 – Окно поиска решения Список Ограничения служит для отображения граничных условии поставленной задачи. Нажав кнопку Добавить откроется диалог добавления ограничения, рисунок 7.5. Рисунок 7.5 – Окно ограничений Поле ввода Ссылка на ячейку используется для указания ячейки или диапазона, назначения которых необходимо наложить ограничение. Поле ввода Ограничение служит для задания условия, которое накладывается назначения ячейки или диапазона, указанного в поле Ссылка на ячейку. Между полями расположен открывающийся список для выбора вида ограничения. Нажав кнопку Добавить, добавляется ограничение. При этом диалог небу- дет закрыт, и можно добавлять другие ограничения. Выбрав ограничение в списке и нажав кнопку Изменить, снова откроется диалог добавления ограничения с уже заполненными, полями. Можно изменить значения в полях ввода и открывающемся списке. Если нужно удалить ограничение из списка, его необходимо выделить, а затем нажать кнопку Удалить. После нажатия кнопки Восстановить очищаются поля ввода и список ограничений, а также восстановите значения параметров поиска решения, используемые по умолчанию. Если нужно уточнить параметры поиска решения, необходимо нажать кнопку Параметры Будет открыт диалог дополнительной настройки, рисунок 7.6. Рисунок 7.6 – Настройка параметров поиска решений Поле ввода Максимальное время используется для ограничения времени, отпускаемого на поиск решения задачи. В поле можно ввести время в секундах. Если при поиске решения это время превышено, поиск будет остановлен. Поле ввода Предельное число итераций служит для ограничения числа промежуточных вычислений. Поле ввода Относительная погрешность определяет точность, с которой определяется соответствие ячейки целевому значению. Поле должно содержать десятичную дробь от нуля до единицы. Чем больше десятичных знаков в задаваемом числе, тем выше точность. Поле ввода Допустимое отклонение 112 используется для задания допуска на отклонение от оптимального решения. При указании большего допуска поиск решения заканчивается быстрее. Поле ввода Сходимость используется для определения условия окончания поиска. Если относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в этом поле, поиск прекращается. Если установлен флажок Линейная модель, для поиска используется линейная модель, что в некоторых случаях может увеличить скорость поиска. Если установлен флажок Показывать результаты итераций, поиск идет в пошаговом режиме. Флажок Автоматическое масштабирование служит для включения автоматической нормализации параметров поиска решения, например, если цель решения определение прибыли в процентах, а параметры исчисляются в рублях, нужна нормализация. Установив флажок Значения неотрицательны, ограничиваются значения влияющих ячеек положительными величинами. Группа переключателей в нижней части диалога служит для настройки таких оптимизации, как экстраполяция значений, а также метода поиска. Нажав кнопку Сохранить модель, вы откроете диалог сохранения, в котором можно задать ссылку на область ячеек, предназначенную для хранения модели оптимизации. После настроек дополнительных параметров необходимо нажать кнопку ОК, чтобы закрыть диалоги вернуться к диалогу настройки параметров поиска решения. Чтобы начать поиск, нажмите кнопку Выполнить. Диалог настройки параметров будет закрыт, и начнется процесс поиска решения. При этом в строке состояния программы будет отображаться вспомогательная информация о ходе поиска текущий шага также значения целевой функции и влияющих ячеек. Чтобы досрочно прервать поиск решения, можно нажать клавишу [Esc]. По окончании поиска или по истечении ограничений на время и количество шагов появится диалог с результатами. Установив переключатель в этом диалоге в положение Сохранить найденное решение, сохраняется решение во влияющих 113 ячейках модели. Если переключатель установлен в положение Восстановить исходные значения, во влияющих ячейках останутся исходные значения параметров. Программа может показать ход поиска решения. Для этого используются отчеты, формируемые на отдельных листах рабочей книги. В списке Тип отчета можно выбрать, какой отчет нужно сформировать. Отчет Результаты состоит из целевой ячейки и списка влияющих ячеек модели, их исходных и конечных значений, а также формул ограничений и дополнительных сведений о наложенных ограничениях. Отчет Устойчивость содержит сведения о чувствительности решения к малым изменениям в формуле. Отчет Пределы состоит из целевой ячейки и списка влияющих ячеек модели, их значений, а также нижних и верхних границ. Нажав кнопку ОК, закроется диалог результатов поиска решения, и если указано формирование отчета, он будет сформирован на отдельном листе. Упражнение 7.3. Фирма производит две модели Аи В сборных книжных полок. Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки. Для каждого изделия модели А требуется 3 м досок, а для изделия модели В — 4 м. Фирма может получать от своих поставщиков дом досок в неделю. Для каждого изделия модели А требуется 12 мин машинного времени, а для изделия модели В — 30 мин. В неделю можно использовать 160 ч машинного времени. Сколько изделий каждой модели следует выпускать фирме в неделю, если каждое изделие модели А приносит 2 долл. прибыли, а каждое изделие модели В — 4 долл. прибыли Составьте математическую модель. Обозначим х — количество изделий модели А, выпускаемых в течение недели, у — количество изделий модели В. Прибыль от этих изделий равна 2х+4у долл. Эту прибыль нужно максимизировать. Функция, для которой ищется экстремум (максимум или минимум, носит название целевой функции.Беспредельному увеличению количества изделий препятствуют ограничения. Ограничено количество материала для полок, отсюда неравенство х. Ограничено машинное время на изготовление полок. На изделие А 114 уходит 0.2 часа, на изделие В - 0.5 часа, а всего не более 160 ч, поэтому х + 0.5y ≤160 . Кроме того, количество изделий — неотрицательное число, поэтому х ≥ 0, у ≥ 0. Формально задача оптимизации записывается так х + 4y → max 3x + 4y ≤ 1700 0.2x + 0.5y ≤ 160 х, у Теперь решим эту задачу в Excel. Создайте новую рабочую книгу, сохраните ее х. Введите в ячейки рабочего листа информацию с рисунка. Ячейкам В и ВЗ присвойте именах и у (в поле имени. A B C D 1 Переменные 2 Изделие Ах Изделие В 0 у 4 5 Целевая функция 6 Прибыль 0 =2*х+4*у 7 8 Ограничения 9 Материал 0 =3*х+4*у <=1700 10 Время изготовления 0 =0,2*х+0,5*у <=160 Рисунок 7.7 – Образец заполнения В ячейках С, Си С представлены формулы, занесенные в соответствующие ячейки столбца В. Выделите ячейку, в которой вычисляется целевая функция, и вызвать команду Поиск решения. В диалоговом окне в поле ввода Установить целевую ячейку уже содержится адрес ячейки с целевой функцией В. Установите переключатель 115 Равной максимальному значению. Перейдите к полю ввода Изменяя ячейки. В нашем случае достаточно щелкнуть кнопку Предположить ив поле ввода появится адрес блока $В$2:$В$3. Перейдите к вводу ограничений. Щелкните кнопку Добавить. Появится диалоговое окно Добавление ограничения. В поле ввода Ссылка на ячейку укажите В. Правее расположен выпадающий список с условными операторами (раскройте его и посмотрите. Выберите условие <=. В поле ввода Ограничение введите число 1700. У насесть еще одно ограничение, поэтому, не выходя из этого диалогового окна, щелкните кнопку Добавить и введите ограничение В. Ввод ограничений закончен, поэтому нажмите ОК . В диалоговом окне Поиск решения появятся введенные ограничения В и В. Справа имеются кнопки Изменить и Удалить. Сих помощью можете изменить ограничение или стереть его. Щелкните кнопку Параметры. Появится диалоговое окно Параметры поиска решения. Чтобы узнать назначение полей ввода этого окна, щелкните кнопку Справка. Менять ничего не будем, только установим два флажка Линейная модель так как наши ограничения и целевая функция являются линейными попеременным хи у) и Неотрицательные значения (для переменных хи у. Щелкните ОК и появится исходное окно. Подготовлена задача оптимизации. Нажмите кнопку Выполнить. Появляется диалоговое окно Результаты условия оптимальности выполнены. На выбор предлагаются варианты Сохранить найденное решение или Восстановить исходные значения. Выберите первое. Можно также вывести отчеты по результатам, по устойчивости, по пределам. Выделите их все, чтобы иметь представление о том, какая информация в них размещена. После нажатия ОК вид таблицы меняется в ячейках хи у появляются оптимальные значения. Числовые данные примера специально подобраны, поэтому в ответе получились круглые цифры изделие А нужно выпускать в количестве 300 штук 116 в неделю, а изделие В — 200 штук. Соответственно пересчитываются все формулы. Целевая функция достигает значения 1400. III Задания для самостоятельной работы Задание 1. Имеется список задолжников по зачетам, таблица 7.1. Составить экзаменационную ведомость, таблица 7.2, в которой определяется, является ли данный студент задолжником (в зависимости от введенной фамилии студента, в поле отметка напротив фамилии студента, не допущенного до экзамена, выводится не допущена в поле подпись выводится деканат, фамилии должников в ведомости выделяются красным цветом. Таблица 7.1- Задолжники Имеют задолженности Алексеев АИ. Волков А.В. Смирнов Д.С. Таблица 7.2 – Экзаменационная ведомость ФИО Отметка Подпись Алексеев АИ. не допущен деканат Бондарь В.С. Волков А.В. Долгов АИ. Иванов В.С. Петров А.В. Смирнов АИ. Терентьев В.С. Примечания 1. Фамилии должников в ведомости выделяются красным цветом с помощью условного форматирования. Условием является наличие соответствующей фамилии в списке задолжников. Для формирования условия с помощью формулы, использовать функции |