пр ворд. Практикум по Эксель. Практикум по информатике 11 класс Тема Microsoft Excel Тема Microsoft Excel
 Скачать 2.01 Mb.
|
|
11. Для поиска оптимального набора значений параметров, который соответствует минимальному значению целевой функции, воспользуемся надстройкой "Поиск решений. Вменю Сервис Надстройки установите флажок перед элементом списка Поиск решений. см. рис. 45). 12. В появившемся диалоге установите адрес целевой ячейки, в нашей таблице ей является ячейка 4 B : Вариант оптимизации значения целевой ячейки, в нашей задаче это будет минимизация Адреса ячеек, значения которых изменяются в процессе поиска решения (ячейки в которых хранятся значения параметров) ограничения равенствами для ячеек с количеством деталей, и неравенствами типа больше или равно для ячеек с параметрами. Для этого нажмите кнопку ДОБАВИТЬ ив открывшемся диалоге введите ограничения Добавьте три условия "целое" для ячеек с параметрами, так как количество перевозок не может быть дробным. В результате набор ограничений должен выглядеть так 13. Нажмите кнопку "Выполнить. Ив таблице появится значение целевой функции равное 7 значения параметров. Таким образом, для перевозки нужного количества груза потребуется 7 рейсов и при этом 3 рейса по первому варианту, 2 по второму и 2 рейса по третьему. 14. Готовую таблицу сохраните на рабочую дискету и покажите учителю. 22 Практикум по информатике 11 класс Тема 2. Microsoft Excel Практическая работа №14 Тема Оптимизационное моделирование экономических задач в Excel. Задание. Самостоятельно решите задачу оптимизации. Составьте формальную модель задачи. Введите параметры и ограничения. Компьютерную модель реализуйте в программе Excel. Вариант 1. Цех выпускает товары двух видов валы и втулки. На изготовление одного вала работник затрачивает 3 часа, а одной втулки – 2 часа. За реализацию одного вала цех получает 8 грн, а одной втулки – 6 грн. В месяц цех должен выпускать не менее 100 валов и 200 втулок. Сколько валов и сколько втулок нужно выпускать в месяц чтобы получить максимальную прибыль, если резерв рабочего времени в цехе составляет 900 человеко- часов. Практическая работа №14 Тема Оптимизационное моделирование экономических задач в Excel. Задание. Самостоятельно решите задачу оптимизации. Составьте формальную модель задачи. Введите параметры и ограничения. Компьютерную модель реализуйте в программе Excel. Вариант 2. Для перевозки груза используют машины марки Газельи. Грузо- подъѐмность машины каждой марки составляет 3 т. За 1 рейс "Газель" тратит 1,5 кг масла ил. бензина, а "Ford" – 2 кг. масла ил. бензина. На базе есть 35 кг масла ил. бензина. Затраты на эксплуатацию машины "Газель" составляют 8 грн, а "Ford" – 5 грн. Требуется перевезти 60 т. груза. Сколько машин "Газельи сколько "Ford" нужно использовать, чтобы затраты на эксплуатацию были минимальными. Практическая работа №14 Тема Оптимизационное моделирование экономических задач в Excel. Задание. Самостоятельно решите задачу оптимизации. Составьте формальную модель задачи. Введите параметры и ограничения. Компьютерную модель реализуйте в программе Excel. Вариант 3. Фирма выпускает 2 вида мороженого сливочное и шоколадное. Для изготовления мороженого используются два исходных продукта молоко и наполнители, расходы которых на 1 кг мороженого и суточные запасы даны в таблице. Изучение рынка сбыта показало, что суточный спрос на сливочное мороженое превышает спрос на шоколадное не более чем на 100 кг. Кроме того, установлено, что спрос на шоколадное мороженое не превышает 350 кг в сутки. Розничная цена 1 кг сливочного мороженого 16 грн., шоколадного — 14 грн. Какое количество мороженого каждого вида должна производить фирма, чтобы доход от реализации продукции был максимальным Практическая работа №14 Тема Оптимизационное моделирование экономических задач в Excel. Задание. Самостоятельно решите задачу оптимизации. Составьте формальную модель задачи. Введите параметры и ограничения. Компьютерную модель реализуйте в программе Excel. Вариант 4. Предприятие получает трубы длиной 2,6 м. из которых требуется изготовить заготовок длиной м, 20 заготовок длиной 0,5 ми заготовок длиной 0,8 м. Определить способ раскроя труб, при котором остаток будет минимальным. Тема 2. Microsoft Excel Практикум по информатике 10 класс 23 Практическая работа №14 Тема Оптимизационное моделирование экономических задач в Excel. Задание. Самостоятельно решите задачу оптимизации. Составьте формальную модель задачи. Введите параметры и ограничения. Компьютерную модель реализуйте в программе Вариант 5. Строительный участок карьера имеет экскаваторы четырѐх типов, которыми выполняются четыре вида земляных работ. Продуктивность использования машины каждого типа в зависимости от вида работ приведена в таблице Требуется распределить экскаваторы по видам работы, обеспечив максимальную производительность участка. Практическая работа №14 Тема Оптимизационное моделирование экономических задач в Excel. Задание. Самостоятельно решите задачу оптимизации. Составьте формальную модель задачи. Введите параметры и ограничения. Компьютерную модель реализуйте в программе Excel. Вариант 6. Фирма занимается доставкой продукции с оптовых складов в магазины. Запасы продукции на складах и потребности магазинов приведены в таблице. Требуется составить оптимальный план перевозки продукции (указать количество единиц перевозимых сданного склада в данный магазин, так чтобы затраты на перевозку были минимальными. Практическая работа №14 Тема Оптимизационное моделирование экономических задач в Excel. Задание. Самостоятельно решите задачу оптимизации. Составьте формальную модель задачи. Введите параметры и ограничения. Компьютерную модель реализуйте в программе Excel. Вариант 7. Мебельная фабрика изготавливает столы стулья, бюро и мебельные шкафы, используя дубовые и буковые доски. На фабрике имеется 500 м дубовых досок им буковых досок. Кроме того запас трудового времени составляет 800 челове- ко-часов в месяц. Нормы расхода ресурсов и прибыль от одного изделия приведены в таблице. Определить оптимальный ассортимент товаров, который бы максимизировал прибыль. Учесть, что каждого изделия должно выпускаться не менее одного. Практическая работа №14 Тема Оптимизационное моделирование экономических задач в Excel. Задание. Самостоятельно решите задачу оптимизации. Составьте формальную модель задачи. Введите параметры и ограничения. Компьютерную модель реализуйте в программе Excel. Вариант 8. На свиноферме кормовой рацион свиней состоит из трѐх видов кормов и для обеспечения прироста массы животным необходимо в сутки потреблять некоторое минимальное количество биологически активных веществ. Требуется составить план суточной закупки кормов, так чтобы потребление веществ животными было не меньше нормы, а стоимость закупки была минимальной. тип экскав. вид работы 1 2 3 4 1 1,2 0,9 1,0 1,4 2 0,6 0,8 0,2 1,0 3 1,0 0,6 0,6 1,2 4 0,5 0,6 0,1 0,7 тип экскав. маг маг маг запас склад 1 1,1 1,0 0,8 800 склад 2 0,6 0,8 0,8 500 склад 1,0 0,9 0,9 330 потреб 400 120 260 550 ресурсы. вид изделия стул стол бюро шкаф дубовые 5 1 4 12 буковые 2 3 9 1 трудовые 3 2 5 10 прибыль 120 50 100 250 вещество вид работы по- треб н комбикорм овѐс кукуруза 4 аминокислоты 1,2 0,9 1,0 1,4 протеин 0,6 0,8 0,2 1,0 кальций 1,0 0,6 0,6 1,2 витамины 0,2 0,15 0,1 0,4 стоимость 4,5 2,20 3,8 24 Практикум по информатике 11 класс Тема 2. Microsoft Excel Практическая работа №14 Тема Оптимизационное моделирование экономических задач в Excel. Задание. Самостоятельно решите задачу оптимизации. Составьте формальную модель задачи. Введите параметры и ограничения. Компьютерную модель реализуйте в программе Excel. Вариант 9. На приобретение оборудования для нового участка цеха выделено 20000 долларов США. При этом можно занять площадь не болеем. Имеется возможность приобрести станки типа Аи станки типа Б. При этом станки типа А стоят 5000 долларов США, занимают площадь 8 м (включая необходимые технологические проходы) и имеют производительность 7 тыс. единиц продукции за смену. Станки типа Б стоят 2000 долларов США, занимают площадь 4 ми имеют производительность 3 тыс. единиц продукции за смену. Необходимо рассчитать оптимальный вариант приобретения оборудования, обеспечивающий при заданных ограничениях максимум общей производительности участка. Практическая работа №14 Тема Оптимизационное моделирование экономических задач в Excel. Задание. Самостоятельно решите задачу оптимизации. Составьте формальную модель задачи. Введите параметры и ограничения. Компьютерную модель реализуйте в программе Excel. Вариант 10. Составить рацион продуктов, учитывая минимальные нормы потребления, так чтобы стоимость набора продуктов была минимальной. Практическая работа №14 Тема Оптимизационное моделирование экономических задач в Excel. Задание. Самостоятельно решите задачу оптимизации. Составьте формальную модель задачи. Введите параметры и ограничения. Компьютерную модель реализуйте в программе Excel. Вариант 11. Завод получил заказ навыпуск приборов 3 типов, для их производства есть запасы материалов 2 типов. Если этих запасов будет мало, завод будет должен купить часть приборов на другом заводе. Нужно определить план исполнения заказа, при котором затраты будут минимальные. В таблице наведены данные относительно заказа, запасов, норм затрат материалов и затратна производство и покупку. ] Тема 2. Microsoft Excel Практикум по информатике 10 класс 25 Практическая работа №14 Тема Оптимизационное моделирование экономических задач в Excel. Задание. Самостоятельно решите задачу оптимизации. Составьте формальную модель задачи. Введите параметры и ограничения. Компьютерную модель реализуйте в программе Excel. Вариант 12. Задано 10 неделимых грузов (машины, контейнеры и т. д, каждый из них имеет стоимость (полезность, вес, объем, и другие параметры нужно определить, который грузи в каком количестве нужно загрузить в трюм судна с определенными значениями грузоподъемности и объема т. д, чтобы общая стоимость (полезность) была максимальной, количество грузов - целое число. Практическая работа №14 Тема Оптимизационное моделирование экономических задач в Excel. Задание. Самостоятельно решите задачу оптимизации. Составьте формальную модель задачи. Введите параметры и ограничения. Компьютерную модель реализуйте в программе Excel. Вариант 13. Расписание касается людей или машин, состояние которых на определенном этапе определяются двумя вариантами – человек в определенный день вышел не вышел, машина на определенной стадии работает не работает т. д. Соответственно, расписание имеет вид комбинаций типу 011100101. В нашем случае 10 работников фирмы исполняют положение на них функции, на каждый день известны потребности в их численности, причем установлена дневная рабочая неделя. Нужно определить расписание выхода на роботу, чтобы удовлетворить эти потребности с минимальным количеством чел./день. 26 Практикум по информатике 11 класс Тема 2. Microsoft Excel Практическая работа №15. Тема Запись и редактирование макросов в Excel. Дополнительные возможности. Задание Создать несколько тестовых вопросов с возможностью выбора вариантов ответа (выбор вариантов из предложенного списка. Подсчѐт результатов и ввод данных об ученике организовать при помощи записи макроса. Ограничить путѐм защиты ячеек доступ к ячейкам с результатами. Указания к выполнению работы 1. Откройте чистую книгу Excel и введите 5 вопросов для теста. 2. Введите варианты ответов на вопросы. Под каждым списком вариантов ответа введите слово "Ответ" и ячейку рядом с этим словом пометьте цветом (чтобы пользователю было понятно, куда вводить свой вариант ответа. Примерный вариант оформления показан на рисунке. 3. Выделите одну из ячеек для ответов, отмеченную цветом, войдите вменю ДАННЫЕ и выберите команду . В графе "Условие проверки" укажите тип данных "Список, а в графе "Источник" введите через точку с запятой возможные варианты ответов. Например так, как показано на рисунке. Помните, что среди вариантов ответа обязательно должен быть один правильный. 4. Организуйте проверку правильности ответа для этого в ячейке соседней с введѐнным вариантом ответа нужно сравнить выбранный вариант с правильным при помощи логической функции ЕСЛИ. В качестве результата вывести либо количество баллов за правильный ответ, либо слова "да" или "нет. Пример формулы показан на рисунке. 5. Теперь надо организовать подсчѐт результатов. Для этого нужно сосчитать либо количество баллов в столбце с проверкой результатов, либо количество ответов с результатом "да. Для этого установите маркер выделения в ячейку, в которой будет вычисляться общий результат. Войдите вменю РАЗРАБОТЧИК и нажмите . После начала записи проделайте действия а задайте имя макроса б в выделенную ячейку введите функцию СУММ и обведите мышью проверяемый диапазон ( если подсчитывается число баллов, или функцию =СЧЁТЕСЛИ( и обведите мышью диапазон, если проверяется количество ответов с результатом "да, поэтому для правильной проверки функция будет выглядеть, например, следующим образом =СЧЁТЕСЛИ(D2:D35; "дав в любую свободную ячейку введите "Тест выполнил ИВАНОВ И" ив соседнюю ячейку "А" класс. Остановите запись макроса либо на панели инструментов "Остановить запись, либо выполнив команду РАЗРАБОТЧИК 6. Создайте автофигуру, например, объѐмный прямоугольник такого вида. Внесите текст (правой кнопкой мыши по рамке авто- фигуры и выберите команду изменить текст. 7. Присвойте этой фигуре макрос для этого щѐлкни- те по рамке фигуры правой кнопкой мыши и выберите команду " Назначить макрос. Выберите из списка, тот макрос, который записали. Тема 2. Microsoft Excel Практикум по информатике 10 класс 27 8. Проверьте, как работает макрос, предварительно ПРОВЕРЬТЕ включены ли макросы, выполнив команду на ленте РАЗРАБОТЧИК. 9. Снова щѐлкните правой кнопкой и нажмите "Назначить макроса затем нажмите кнопку ПРАВКА. Откроется окно редактора кода макроса. Найдите строку ActiveCell.FormulaR1C1 = (Выполнил Иванов) И внесите изменения, так чтобы текст команды был такой ActiveCell.FormulaR1C1 = Введи имя и фамилию) Аналогично преобразуйте строку ниже, в которой введѐн класс учащегося. Нажмите клавишу F5 и проверьте, как работает макрос. Закройте окно редактора Visual Basic. 10. Для того, чтобы пользователь не видел правильно ли он ввѐл ответ и не перебирал все варианты скроем отображение данных в ячейке. Для этого выделите ячейку с результатом и выберите команду ГЛАВНАЯ => ФОРМАТ ЯЧЕЕК. На вкладке число выберите раздел "Все форматы" и введите в качестве формата такой "; ; ;" (без кавычек, то есть в ячейке ничего не отображать. Убедитесь, что результат скрыт. 11. Защитите ячейки с результатами от изменений для этого выделите их маркером выделения, войдите вменю ГЛАВНАЯ => ФОРМАТ ЯЧЕЕК и установите параметры защиты запретить изменения и скрыть формулы, а затем зайдите вменю РЕЦЕНЗИРОВАНИЕ => ЗАЩИТИТЬ ЛИСТ и задайте пароль от изменения ячейки. НЕ ЗАЩИЩАЙТЕ ячейку в которой подсчитывается итоговая сумма, фамилия учащегося и класс, иначе макрос не сможет записать туда значение. 12. Проверьте работу вашей программы от начала до конца. 13. Сохраните работу и предоставьте на проверку учителю. 28 Практикум по информатике 11 класс Тема 2. Microsoft Excel Практическая работа № 16. (Физмат проф) Тема Макросы в приложениях MS Office : использование языка VBA в макросах. Задание Загрузите или откройте шаблон квитанции по плате услуг. При помощи записи макроса автоматизируйте ввод данных в квитанцию и подсчѐт суммы платежа за услуги ЖКХ. 1. Нажмите кнопку Office и выберите пункт "Создать, а затем в списке шаблонов выберите или введите это слово в окно поиска шаблонов. Загрузите шаблон "Оплата ЖКХ". 2. Выделите пустые ячейки таблицы и на панели внизу выберите язык "Русский. 3. Установите курсор в первую ячейку столбца "Месяц. 4. На вкладке "Разработчик" нажмите кнопку . В открывшемся диалоге введите имя макроса, например, "Месяц, нажмите назначить макрос кнопке. В открывшемся диалоге создаваемый макрос при помощи кнопки перенесите в список кнопок на ленте и нажав на кнопку "Изменить" выберите вид кнопки для макроса на ленте. 5. В режиме записи мышь в области документа НЕАКТИВНА, но активна вменю. Щѐлкните на вкладке "Вставка" и выберите пункта затем В открывшемся диалоге выберите "Date", а затем справа выберите сокращѐнный формат даты, если требуется исправьте вручную Выделите вставленный текст при помощи кнопок клавиатуры 6. Аналогично поступите с ячейками в столбце ГОД. Формат года "yyyy". 7. В столбец "Показания счѐтчика" для всех ячеек кроме "Квартплата" и "Интернет" введите произвольные показания. 8. Перейдите в первую ячейку столбца "Разница" и войдите в режим записи макроса. В режиме записи перейдите на вкладку "Макет, выберите в разделе "Данные" пункт и введите . Остановите запись. 9. На вкладке "Разработчик" нажмите кнопку "Макросы" и выберите только что записанный макроса затем нажмите кнопку "Изменить. В открывшемся редакторе в коде этого макроса скопируйте текст и вставьте его 8 раз с новой строки. В каждом вставленном фрагменте измените номер строки, увеличивая на 1. 10. Проверьте работу макроса. 11. Аналогичным образом запишите макрос для столбца сумма, в формулу введите "=g2*0,28", где 0,28 – тариф за 1 кВт/ч электроэнергии. В режиме изменения макроса скопируйте строку и измените тариф для каждого вида услуги, кроме квартплаты и интернета, а для последней ячейки введите формулу "=sum(above)". 12. Сумму для квартплаты и интернет введите вручную. 13. Выполните макрос. 14. Проверьте работу макросов в документе и покажите учителю. Тема 2. Microsoft Excel Практикум по информатике 10 класс 29 Задание используя управляющие элементы, создайте в Excel программу калькулятор. 1. Откройте программу Excel и сразу войдите в редактор Visual Basic (Вкладка Разработчик или Alt+F11). 2. Создайте форму для размещения управляющих элементов. Для этого войдите вменю и выберите команду User Form. 3. Создайте текстовое окно (TextBox) и 5 кнопок. Форма должна быть заполнена примерно так, как показано на рисунке. Не забудьте задать надписи на кнопках. Для этого выберите нужную кнопку и на панели свойств (Properties) слева в разделе Caption задайте надписи на кнопках. Также назовите окошко программы, для этого щѐлкните на строку заголовка ив разделе Caption введите название "Калькулятор. 5. Начнѐм вводить событийные процедуры. Для этого дважды щѐлкните по нужной кнопке или правой кнопкой мыши и выберите пункт View Code. Откроется окно редактора кода. Будьте внимательны, вводите процедуру между разделителями "Private Sub" и "End Sub". Команды вводите с новой строки. Обращайте внимание на соответствие имени кнопки, надписи на ней и программируемым действиям. 6. Разберѐм событийную процедуру для кнопки "+". Необходимо считать значение из первого текстового окна и сложить со значением второго текстового окна, аре- зультат записать в третье. Для преобразования текста в число используется функция, а для записи текста в текстовое окно используется оператор присваивания свойству Text текстового окна значения. В нашем случае оператор будет выглядеть следующим образом TextBox3.Text = Val(TextBox1.Text)+Val(TextBox2.Text) 7. Для остальных кнопок математических действий все действия и команды будут теми же самыми, за исключением знаков математических действий. 8. При нажатии на кнопку "Сброс" очистим содержимое всех окон TextBox1.Text = "" TextBox2.Text = "" TextBox3.Text = "" 9. После того, как введены команды, запустите программу на исполнение войдите вменю и выберите команду Run или нажмите клавишу F5. Проверьте работу вех кнопок. Продумайте, как усовершенствовать программу так, чтобы для ввода и вывода было только одно текстовое окно ( как в обычном устройстве калькулятор 10. Продемонстрируйте результаты учителю. Рис. 52 30 Практикум по информатике 11 класс Тема 2. Microsoft Excel Задание Используя редактор Visual Basic добавьте к списку функций Excel свою. 1. Откройте редактор Visual Basic. Слева в окне проекта найдите объект Эта книга . Щелкните по нему правой кнопкой мыши и выберите пункта затем 2. В открывшемся диалоге введите текст функции так, как показано на рисунке. 3. В программе Excel в диалоге вставки функции (На вкладке Формулы) выберите Полный алфавитный перечень и найдите в нѐм свою функцию. Проверьте работу функции. 4. Из функции которая введена обратимся к некоторым объектам листа, например, изменим шрифт ячейки с результатом. Для этого дополним текст функции командами, как показано на рисунке. Здесь ActiveCell возвращает ячейку, как объект листа, а Acti- veCell.Font – обращение к свойству этой ячейки, в частности к шрифту (Bold – полужирный, Italic – курсив, Color – цвет в формате RGB). Visual Basic for Applications предоставляет широкие возможности работы с объектами приложения и их свойствами. Задание В приложении PowerPoint создайте слайд на котором разместите 2-3 тестовых вопроса и организуйте проверку результатов тестирования. |