Использование электронных таблиц при решении экономических задач

Информационные процессы являются фундаментальной частью современной картины мира. Представление любого процесса, в частности информационного в некотором языке является моделью. Автоматизация информационного процесса, т. е. возможность его реализации с помощью некоторого технического устройства, требует его представления в форме доступной данному техническому устройству. В этом случае информационный процесс становится «информационной технологией». Рассмотрение эволюции развития информационных процессов и выявление закономерностей позволит осознать необходимость автоматизации информационных процессов и значение информационных технологий в экономике и управлении информационного общества.

Предлагаемый материал ориентирован на математическую подготовку в классах экономического профиля. С одной стороны предоставляется достаточно доступный математический аппарат для математической формализации экономических ситуаций, явлений и процессов. С другой стороны – отрабатываются навыки экономического анализа получаемых результатов решения математических моделей. Экономическое толкование – анализ результатов Поиска решений по типу «Устойчивость».

В современной школе организуются классы по профильным направлениям. Основной проблемой для работы в таких классах является отсутствие соответствующих учебных пособий. Разумеется, в высшей школе подобные пособия имеются, но они совершенно не адаптированы на школьный контингент учащихся. В какой-то степени решению указанной проблемы и способствует предлагаемая нами работа. Еще один не маловажный аспект – демонстрация практического применения материала, изучаемого на уроках информатики. На уроках информатики учащиеся знакомятся с основными приемами работы с электронными таблицами. Здесь же рассматривается углубленный аспект пакета Excel, а именно использование его надстройки «Поиск решения». По смыслу во многих задачах требуется получение целочисленного решения и Поиск решения такую возможность предоставляет, но при этом станет невозможным построение отчета «Устойчивость», а значит и построение экономических выводов. Отсюда, решают любую задачу прежде всего без требования целочислености, а уже после установленных экономических выводов, можно решить эту же задачу и целочисленно.

Цели изучений:

1. Обучение приемам и методам построения математических моделей экономических задач.

2. Формирование навыков использования Excel для решения экономических задач.

3. развитие компетентности проводить анализ и поиск рационального решения

4. Формирование и развитие навыков анализа полученных числовых результатов с экономической точки зрения.

Ожидаемые результаты обучения.

В результате изучения данного курса, учащиеся должны:

1. Уметь строить математическую модель экономической задачи.

2. Уметь заполнять исходную таблицу в Excel.

3. Знать алгоритм использования надстройки «Поиск решения».

4. уметь формировать отчет «Устойчивости».

5. Иметь представление о построении экономических выводов по отчету.

формы организации учебной деятельности - лабораторные и практические занятия. Методы: частично – поисковый, проблемный. При этом учащимся не придется осваивать дополнительный материал по информатике; курс составлен таким образом, что задания опираются на те знания и умения, которые уже сформированы на уроках информатики.

ТЕМА 1: Решение задач линейного программирования в Excel

В настоящее время наиболее мощным средством решения таких задач на компьютере является пакет Excel с его надстройкой «Поиск решения».

Это средство позволяет получить не только значения неизвестных, целевой функции и правых частей ограничений, но и ряд важных, дополнительных сведений:

- пределов изменения целевых коэффициентов и правых частей ограничений, в границах которых решение поставленной задачи возможно;

- нормированные стоимости единицы определяемых неизвестных;

- теневые цены единиц объемов предложенных ресурсов.

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

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

Рис. 1

В ячейках, содержащих формулы (они выделены) чаще всего используется функция СУММПРОИЗВ (массив1;массив2), где массив1 – это перечень ячеек для значений переменных, в таблице они обведены пунктиром, а массив2 – коэффициенты при переменных. Обычно ячейки массива1 фиксируются, т.е. их адреса делают абсолютными, для того, чтобы иметь возможность писать формулу только один раз, а во всех остальных случаях ее копировать.

Все сведения о модели заносят в окно «Поиск решения» (рис.2).

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

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

Рис. 2

Продемонстрируем использование «Поиска решения» на примере.

Условие задачи. Найти минимум функции Z=X₁+X₂ при выполнении следующей системы ограничений:

3x₁+x₂≥8

x₁-4x₂≤19

2x₁+3x₂≤28

x₁-x₂≤4

x₁+3x₂≥8

В таблице Excel условие задачи можно представить так, как на рис. 3:

Рис. 3

тогда окно Поиска решения будет выглядеть так (рис.4):

Рис.4

Следует заметить, что при записи ограничений указывается сравнение адреса ячейки, хранящей формулу соответствующего ограничения с адресом, где хранится значение объема данного ограничения. Это связано с тем, что окно записи ограничения не позволяет записывать саму формулу ограничения, а предполагает лишь запись адресов. Полезно напомнить также, что запись адресов в окне «Поиск решения» целесообразнее делать не с клавиатуры, а с помощью мыши так называемым методом «взять и тащить».

По этому окну будет получен следующий результат (см. рис. 5):

Рис. 5

В ячейке D4 имеем минимальное значение целевой функции равное 4. Оптимальные значения переменных в ячейках B3 и C3 равны по 2.

Все ограничения выполняются. В частности, 2-ое ограничение: по его формуле результат равен –6, а его объем равен 19, следовательно, левая часть меньше правой на 25, но именно такой же результат был получен и предыдущими способами.

Контрольные вопросы.

1. Возможно ли решение задачи, если в окне «Поиск решения» отсутствуют сведения об ограничениях?
2. Можно ли использовать средство «Поиск решения», если в системе ограничений есть неравенство строгой формы?

ТЕМА 2: ПОСТРОЕНИЕ ЛИНЕЙНЫХ ЭКОНОМИЧЕСКИХ МОДЕЛЕЙ

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

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

С другой стороны, целью любого производства может быть либо обеспечение максимально возможного дохода, либо минимум понесенных затрат. Это принято называть целью задачи или ее критерием. Во всех таких задачах часто можно наблюдать так называемые «экономические ножницы», т.е. одни условия задачи достижимы при минимальном производстве, например, будут минимальны расходы, а другие, наоборот, – при максимальном (большой доход от большого количества, произведенного продукта). Очевидно, существует, как правило, множество вариантов решения, которые удовлетворяют ограничениям по ресурсам, но важно из этого множества найти только такое решение, которое обеспечит достижение желаемой цели. Такие задачи принято называть оптимизационными задачами в экономике.