Методичка по информационным технологиям

71

телевидение. Опыт прошлых лет показал, что объем сбыта, который обеспечивает каждая минута телерекламы, в 25 раз больше объема сбыта, обеспечиваемого одной минутой радиорекламы. Определить оптимальное распределение ежемесячно отпускаемых средств между радио- и телерекламой.

Вариант 2. Два кинограбителя из известного голливудского фильма отправились в канун рождества на дело в магазин игрушек. К большому их сожалению, в кассе наличности правонарушители не обнаружили. Уходить с пустыми руками им не хотелось, поэтому они решили наполнить имеющийся у них рюкзак наиболее дорогими игрушками. Особенно их привлекли четыре вида игрушек. Быстренько сравнив их цены и вес, грабители определили, чем и в каком количестве нужно заполнить рюкзак грузоподъемностью 50 кг так, чтобы стоимость награбленного была максимальной.

Используя имеющиеся в таблице данные, найдите оптимальный план заполнения рюкзака.

Вариант 3. Девушка готовиться к отпуску на море. Ей нужно заполнить чемодан купальными принадлежностями, топиками, шортами, юбками, теплыми кофтами и джинсами так, чтобы полезность от выбранного набора вещей была максимальной. При этом топиков можно взять не больше 10, число пар шорт не должно превышать половины количества топиков, а теплая кофта, джинсы и купальные принадлежности должны присутствовать в чемодане в количестве не меньше 1.

Место в чемодане, доступное для данных вещей, составляет 9000 см3. Как заполнить чемодан наилучшим образом?

Вариант 4. Предприятие электронной промышленности выпускает две модели радиоприемников, причем каждая модель производится на отдельной технологической линии. Суточный объем производства первой линии — 60 изделий, второй линии — 75 изделий. На радиоприемник первой модели расходуется 10 однотипных элементов электронных схем, на радиоприемник второй модели — 8 таких же элементов. Максимальный суточный запас используемых элементов равен 800 единицам. Прибыль от реализации одного радиоприемника первой и второй моделей равна 30 и 20 долларов, соответственно. Определить оптимальный суточный объем производства первой и второй моделей.

Вариант 5. Процесс изготовления двух видов промышленных изделий состоит в последовательной обработке каждого из них на трех станках. Время

72

использования этих станков для производства данных изделий ограничено 10-ю часами в сутки. Время обработки и прибыль от продажи одного изделия каждого вида приведены в таблице. Найти оптимальный объем производства изделий каждого вида.

Вариант 6. Служба снабжения завода получила от поставщиков 500 стальных прутков длиной 5 м. Их необходимо разрезать на детали А и B длиной соответственно 2 и 1,5 м, из которых затем составляются комплекты. В каждый комплект входят 3 детали А и 2 детали B. Характеристики возможных вариантов раскроя прутков представлены в таблице.

шт./компл.

Постройте математическую модель задачи, позволяющую найти план раскроя прутков, максимизирующий количество комплектов. Решите задачу.

Вариант 7. С вокзала можно отправлять ежедневно курьерские и скорые поезда. Вместимость вагонов и наличный парк вагонов на станции указаны в таблице.

Постройте математическую модель задачи, на основании которой можно найти такое соотношение между числом курьерских и скорых поездов, чтобы число ежедневно отправляемых пассажиров достигло максимума.

Вариант 8. Фирма выпускает три вида изделий. В процессе производства используются три технологические операции. На рис. 1.1 показана технологическая схема производства изделий

73

Фонд рабочего времени ограничен следующими предельными значениями: для первой операции - 430 мин; для второй операции - 460 мин; для третьей операции - 420 мин. Изучение рынка сбыта показало, что ожидаемая прибыль от продажи одного изделия видов 1 , 2 и 3 составляет 3, 2 и 5 рублей соответственно.

Постройте математическую модель, позволяющую найти наиболее выгодный суточный объем производства каждого вида продукции?

Вариант 9. Требуется распределить имеющиеся денежные средства по четырем альтернативным вариантам. Игра имеет три исхода. В таблице приведены размеры выигрыша (или проигрыша) на каждый доллар, вложенный в соответствующий альтернативный вариант, для каждого из трех исходов. У игрока имеется $500, причем использовать их в игре можно только один раз. Точный исход игры заранее неизвестен. Учитывая эту неопределенность, распределить деньги так, чтобы максимизировать минимальную отдачу от этой суммы.

Вариант 10. Полуфабрикаты поступают на предприятие в виде листов фанеры. Всего имеется две партии материала, причем первая партия содержит 400 листов, а вторая 250 листов фанеры. Из поступающих листов фанеры необходимо изготовить комплекты, включающие 4 детали 1-го типа, 3 детали 2-го типа и 2 детали 3-го типа. Лист фанеры каждой партии может раскраиваться различными способами.

Количество деталей каждого типа, которое получается при раскрое одного листа соответствующей партии по тому или иному способу раскроя, представлено в таблице.

74

Требуется раскроить материал так, чтобы обеспечить изготовление максимального количества комплектов. Составить и решить двойственную задачу.

Вариант 11. Фирма выпускает ковбойские шляпы двух фасонов (А и В). Трудоемкость изготовления шляпы фасона А вдвое выше трудоемкости изготовления шляпы фасона В. Если бы фирма выпускала только шляпы фасона А, суточный объем производства мог бы составить 500 шляп. Суточный объем сбыта обоих фасонов ограничен диапазоном от 150 до 200 штук. Прибыль от продажи шляпы фасона А равна $8, а фасона В — $5. Определить, какое количество шляп каждого фасона следует изготовить, чтобы максимизировать прибыль.

Тема 9. Дополнительные возможности Excel

Задание 41. Выпадающий список в ячейке

Способ 1. Простой

Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш Alt+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка.

Способ 2. Стандартный

1.Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).

2.Выберите в меню Вставка - Имя - Присвоить (Insert - Name - Define) и введите

имя (можно любое, но обязательно без пробелов!) для выделенного диапазона (например Товары). Нажмите ОК.

3. Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню Данные - Проверка (Data - Validation). На первой вкладке Параметры из выпадающего списка Тип данных выберите вариант Список и введите в строчку Источник знак равно и имя диапазона (т.е. =Товары).

75

Задание 42. Выпадающий список с данными из другого файла

Создать простой выпадающий список в ячейке с данными из этого же файла - легкая задача. Все усложняется, когда данные для списка находятся в другом файле. Для решения этой проблемы можно использовать функцию ДВССЫЛ (INDIRECT), чтобы сформировать правильную ссылку на внешний файл.

Например, если необходимо поместить в список содержимое ячеек А1:А10 из файла Товары.xls, нужно в поле Источник ввести следующую конструкцию:

=ДВССЫЛ("[Товары.xls]Список!$A$1:$A$10")

76

Функция ДВССЫЛ преобразует текстовую строку аргумента в реальный адрес, используемый для ссылки на данные. Обратите внимание, что имя файла заключается в квадратные скобки, а восклицательный знак служит разделителем имени листа и адреса диапазона ячеек.

Если файл с исходными данными для списка лежит в другой папке необходимо указать полный путь к файлу, например, следующим образом:

=ДВССЫЛ("'C:\TEMP\[Товары.xls]Список'!$A$1:$A$10")

В данном случае не забудьте заключить в апострофы полный путь к файлу и имя листа.

Минус всей этой системы только один - выпадающий список будет корректно работать только в том случае, если файл Товары.xls открыт.

Задание 43. Связанные выпадающие списки

Этот способ основан на применении функции ДВССЫЛ (INDIRECT), которая умеет преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. То есть, если в ячейке лежит текст "А1", то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово "Маша", то функция выдаст ссылку на именованный диапазон с именем Маша и т.д.

Возьмем, например, вот такой список моделей автомобилей Toyota, Ford и

Nissan:

Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota в меню Вставка - Имя - Присвоить (Insert - Name - Define). Затем повторим то же самое со списками Форд и Ниссан, задав соответственно имена диапазонам Ford и Nissan.

Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку и откройте меню Данные - Проверка (Data - Validation), затем из выпадающего списка Тип данных выберите вариант Список и в поле

77

Источник - выделите ячейки с названиями марок (желтые ячейки в нашем примере). После нажатия на ОК первый выпадающий список готов:

Теперь создадим второй выпадающий список, в котором будут отображаться модели выбранной в первом списке марки. Точно так же, как в предыдущем случае, выделите пустую ячейку и откройте меню Данные - Проверка - далее Список. В поле Источник нужно будет ввести вот такую формулу:

=ДВССЫЛ(F3)

где F3 - адрес ячейки с первым выпадающим списком - замените на свой.

После нажатия на ОК содержимое второго списка будет выбираться по имени диапазона, выбранного в первом списке.

Задание 44. Выпадающий список в ячейке с удалением использованных элементов

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

Идеальным вариантом было бы организовать в ячейках B2:B8 выпадающий список, но при этом сделать так, чтобы уже занятые сотрудники автоматически убирались из выпадающего списка, оставляя только свободных.

Шаг 1. Кто сколько работает?

78

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

=СЧЁТЕСЛИ($B$2:$B$8;E2)

Шаг 2. Кто еще свободен?

Теперь выясним, кто из наших сотрудников еще свободен, т.е. не исчерпал запас допустимых смен. Добавим еще один столбец и введем в него формулу, которая будет выводить номера свободных сотрудников:

=ЕСЛИ(F2-G2<=0;"";СТРОКА(E2)-1)

Шаг 3. Формируем список

79

Теперь надо сформировать непрерывный (без пустых ячеек) список свободных сотрудников для связи - на следующем шаге - с выпадающим списком. Для этого добавим еще один столбец и введем в него такую формулу:

=ЕСЛИ(СТРОКА(E2)-

СТРОКА(E$2)+1>СЧЁТ($H$2:$H$10);"";ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;1+С ТРОКА(E2)-СТРОКА(E$2));1))

Эта формула делает одну простую вещь - выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.

Шаг 4. Создаем именованный диапазон свободных сотрудников

Теперь идем в меню Вставка - Имя - Присвоить (Insert - Name - Define) и создаем новый именованный диапазон Имена по следующей формуле:

=СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10))

80

Фактически, мы просто даем диапазону занятых ячеек в синем столбце собственное название Имена.

Шаг 5. Создаем выпадающий список в ячейках

Осталось выделить ячейки B2:B8 нашего графика и добавить в них выпадающий список с элементами диапазона Имена. Для этого откроем меню Данные - Проверка (Data - Validation), выберем в списке допустимых значений вариант

Список и укажем Источник данных:

Вот и все! Теперь при назначении сотрудников на дежурство их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто еще свободен.

Задание 45. Выпадающий список с добавлением новых элементов

Итак, имеем следующую ситуацию: