Лаб раб Оптимизация штатного расписания

Министерство образования и науки Российской Федерации Государственное образовательное учреждение

высшего профессионального образования žКузбасский государственный технический университет имени Т. Ф. Горбачева£

Кафедра прикладных информационных технологий

ОПТИМИЗАЦИЯ ШТАТНОГО РАСПИСАНИЯ СРЕДСТВАМИ MS EXCEL

(ПОДБОР ПАРАМЕТРА, ПОИСК РЕШЕНИЯ)

Методические указания к лабораторной работе по дисциплине žЭкономическая информатика£ для студентов направления 080100.62 Экономика£, профиль žБухгалтерский учет, анализ и аудит£

очной формы обучения

Утверждены на заседании кафедры Протокол № 5 от 25.11.2011 Рекомендованы к печати учебно-методической комиссией направления 080100.62 Протокол № 11 от 21.12.2011 Электронная копия хранится в библиотеке КузГТУ

Кемерово 2012

Содержание

Введение……………………………………………………………………2

1.Основные понятия MS Excel ………………………………………3

1.1.Формулы…………………………………………………………..3

1.2.Использование функций в формулах……………………………5

1.3.Типы ссылок. Копирование и перемещение формул…………..8

2.Инструменты финансового анализа данных………………………..10

2.1.Подбор параметра ……………………………………………….10

2.2.Поиск решения ………………………………………..................11 2.2.1.Загрузка надстройки Поиск решения………………………..12 2.2.2.Элементы диалогового окна Параметры поиска решения…14 2.2.3.Порядок решения задачи с помощью инструмента Поиск решения………………………………………....................................17

3.Технология решения задачи žОптимизация штатного расписания фирмы£……………………………………………………………………21

3.1.Постановка задачи ………………………………………………21

3.2.Решение задачи с помощью инструмента Поиск решения…...22

3.3.Решение задачи с помощью инструмента Подбор параметра..24

4.Задание для выполнения лабораторной работы ………………….25

5.Контрольные вопросы ……………………………………………...26 Список рекомендуемой литературы …………………………………….27

Приложение. Пример оформления отчета ……………………………...28

1

Введение

Данная лабораторная работа выполняется студентами первого курса в рамках изучения дисциплины "Экономическая информатика". Рассматриваются инструменты MS Excel, используемые для решения задач оптимизации и финансового анализа данных в экономике.

Цель работы: изучение технологии реализации инструментов Поиск решения и Подбор параметра для решения оптимизационных задач в экономике в среде MS Excel.

Под оптимизацией будем понимать поиск наилучшего решения задачи в смысле указанного критерия. Различают безусловную и условную оптимизацию. В случае условной оптимизации поиск наилучшего решения осуществляется с учетом имеющихся ограничений (условий) на переменные задачи. Различные аспекты оптимизации занимают очень важное место в бизнесе и производственной деятельности современных предприятий и организаций. Проблемы (задачи) оптимизации присутствуют в самых различных процессах деятельности. Примером могут служить задачи оптимизации перевозок, распределения ресурсов, штатного расписания, цель решения которых - достижение наилучших результатов при наименьших расходах; задача максимизации доходов за счет оптимизации средств на разные инвестиционные проекты и др.

Для осуществления финансового анализа вида žчто-если£ (рис. 1) используются средства: финансовые функции, Подбор параметра, Диспетчер сценариев и Таблица данных (подстановки) (рис. 2).

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

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

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

2

Многовариантность финансовых расчетов с целью выбора лучшего варианта может быть выполнена с помощью средства Таблица данных (подстановки).

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

Рисунок 2 - Вкладка Разработчик гументов под заданное значение функции. Такая необходимость возникает в случае отсутствия соответствующих žсимметричных£ финан-

совых функций. Для этой цели предназначен инструмент Подбор параметра.

Рассмотрим решение задачи žОптимизация штатного расписания фирмы£ с помощью двух инструментов: Подбор параметра и Поиск решения. Для лучшего понимания технологии решения задачи средствами MS Excel рассмотрим основы работы в этой среде.

1. Основные понятия MS Excel

Содержимым ячейки таблицы MS Excel может быть: текст, число, дата и формула.

1. 1. Формулы

Формулы представляют собой выражения, по которым выполняются вычисления на рабочем листе. Формула начинается со знака равно ž=£. Она может включать в своей структуре различные элементы: ссылки на ячейки и диапазоны, операторы, константы и функции. Функция - стандартная формула, которая возвращает результат выполнения определенных действий над значениями, выступающими в качестве аргументов. Функции позволяют упростить формулы в ячейках листа, особенно, если они длинные или сложные. Оператор - знак или символ, задающий тип вычисления в выражении. Существуют математические, логические операторы, операторы сравнения и ссылок. Константа - постоянное (не вычисляемое) значение. Например, число ž25£ или текст žвесна£ являются константами. Выражение и результат вычисления выражения константами не являются.

Рассмотрим формулу ПИ()* A2^ 2 . Элементы формулы: функция ПИ() возвращает значение числа (3,142...); ссылка A2 возвращает зна-

3

чение, хранящееся в ячейке A2; константы: числовые или текстовые значения, вводимые непосредственно в формулу, например ž2£; оператор ž^£ возводит число в степень, а оператор ž*£ умножает числа. Операторами обозначаются операции, которые следует выполнить над операндами формулы. Существует стандартный порядок выполнения вычислений, однако его можно изменить с помощью скобок. В MS Excel включено четыре вида операторов: арифметические, текстовые, операторы сравнения и операторы ссылок. Арифметические операторы приведены в табл. 1, они используются для выполнения арифметических операций: сложение, вычитание, умножение, возведение в степень.

Таблица 1 - Арифметические операторы

Операторы сравнения приведены в табл. 2. Используются для сравнения двух значений. Результатом является логическое значение: ИСТИНА или ЛОЖЬ.

Таблица 2 - Операторы сравнения

4

Текстовый оператор конкатенации (знак амперсанд ž&£) используется для объединения нескольких текстовых строк в одну. Для описания ссылок на диапазоны ячеек используются операторы, представленные в табл. 3.

Таблица 3 - Операторы ссылок

Значения обрабатываются формулой в определенном порядке. Формула в MS Excel всегда начинается со знака равно ž=£. Элементы, следующие за знаком равенства, являются операндами, которые разделены операторами вычислений. Формула вычисляется слева направо в соответствии с определенным порядком для каждого оператора в формуле.

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

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

1.2. Использование функций в формулах

Рассмотрим использование функций и вложенных функций в формулах. Следующий пример функции ОКРУГЛ(), округляющей число в ячейке A10, иллюстрирует синтаксис любой функции (рис 3.1). Рассмотрим подробно элементы структуры функции.

1 Пример и рисунок взят из справочной системы MS Excel

5

1. Структура функции начинается со знака равно ž=£, за которым следуют имя функции, открывающая скобка, список аргументов, разделенных точкой с запятой, закрывающая скобка.

Рисунок 3 – Структура функции

Таблица 4 - Приоритет операций

2.Имя функции. Чтобы отобразить список доступных функций, надо щелкнуть ячейку и нажать комбинацию клавиш <SHIFT+F3>.

3.Аргументы. Существуют различные типы аргументов: число, текст, логическое значение (ИСТИНА, ЛОЖЬ), массивы.

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

6

используемых в качестве аргументов, значение ошибки (например, ž#Н/Д£) или ссылки на ячейку. Ссылка на ячейку - координаты, определяющие расположение ячейки на листе. Например, B3 представляет ссылку на ячейку, находящуюся на пересечении столбца B и строки 3.

4. Всплывающая подсказка аргумента. Всплывающая подсказка с синтаксисом и аргументами появляется после ввода функции. Например, всплывающая подсказка появится после ввода выражения ž=ОКРУГЛ(£. Всплывающие подсказки отображаются только для встроенных функций.

Для ввода функций можно использовать Мастер функций. Диалоговое окно (ДО) Мастера функций - шаг 1 из 2 упрощает ввод функций при создании формул, в которых они содержатся. При вводе функции в поле (формулу) ДО мастера отображается имя функции, все ее аргументы, описание функции, каждого из аргументов и текущий результат функции и всей формулы. Чтобы упростить создание и редактирование формул и свести к минимуму количество опечаток и синтаксических ошибок, можно воспользоваться автоматическим завершением формул. После ввода знака равно ž=£ и начальных букв имени функции, открывается динамический раскрывающийся список доступных функций, аргументов и имен, которые соответствуют этим буквам. Выбрав элемент из раскрывающегося списка мышью, можно вставить его в формулу.

В некоторых случаях может потребоваться использование функции в качестве одного из аргументов. Аргументы - значения, используемые функцией для выполнения операций. Тип аргумента зависит от конкретной функции. Обычно аргументы, используемые функциями, являются числами, текстом, ссылками на ячейки и именами других функций. Например2, в следующей формуле (рис. 4) применяется вложенная функция СРЗНАЧ() и выполняется сравнение результата со значением 50. Функции СРЗНАЧ() и СУММ() вложены в функцию ЕСЛИ().

2 Пример и рисунок взяты из справочной системы MS Excel

7

1.3. Типы ссылок. Копирование и перемещение формулы

Различают абсолютные, относительные и смешанные ссылки. Относительная ссылка в формуле, например A1, основана на отно-

сительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании или заполнении формулы вдоль строк и вдоль столбцов ссылка соответственно автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании или заполнении относительной ссылки из ячейки B2 в ячейку B3, содержащей формулу, в которой в качестве операнда используется ссылка на ячейку žA1£, она автоматически изменяется с žA1£ на žA2£ (меняется строка).

Абсолютная ссылка ячейки в формуле, например, $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. Например, при копировании или заполнении абсолютной ссылки из ячейки B2 в ячейку B3 она останется прежней - $A$1.

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов имеет вид: $A1, $B1. Абсолютная ссылка строки имеет вид: A$1, B$1. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании или заполнении формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в ячейку B3 она изменяется с žA$1£ на žB$1£. Для изменения типа ссылки (относительная, абсолютная, смешанная) надо выделить ячейку с формулой и в строке формул ссылку, которую нужно изменить. Строка формул - панель в верхней части окна MS Excel, используется для ввода или изменения значений и формул в ячейках или в диаграммах. В ней отображается константа или формула, содержащаяся в активной (выделенной) ячейке. Для переключения между типами ссылок следует нажать клавишу F4. В табл. 5 показано, как изменяется ссылка определенного типа, если формула, записанная в A1 со ссылкой на B1 ($B$1, $B1, B$1) копируется на две ячейки вниз и на две ячейки вправо.

8

Таблица 5 - Изменение ссылок при копировании

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

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

9