Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ
СРЕДНЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ
ВОЛГОГРАДСКИЙ ТЕХНОЛОГИЧЕСКИЙ КОЛЛЕДЖ
А.В. Данилькевич
Информационные технологии
в профессиональной деятельности
методические указания по выполнению
практических работ
Раздел 2
ТАБЛИЧНЫЙ ПРОЦЕССОР MS EXCEL
Волгоград - 2008
УДК 004.9
ББК 32.97
Д 18
Специальности: 032401 Реклама, 070602 Дизайн (по отраслям)
Автор:
преподаватель кафедры дизайн
ГОУ СПО «Волгоградского технологического колледжа»
А.В. Данилькевич
Рецензенты:
канд. пед. наук, доцент кафедры информатики и информатизации образования
ГОУ ВПО «Волгоградский государственный социально-педагогический университет»
С.Н. Касьянов
заведующий кафедрой программное обеспечение и вычислительная техника
ГОУ СПО «Волгоградский технологический колледж»
А.А. Теткин
|
Данилькевич А.В. Информационные технологии в профессиональной деятельности: методические рекомендации по выполнению практических работ в 4 ч. Часть 2. Табличный процессор MS Excel / А.В. Данилькевич. – Волгоград: РИО ГОУ СПО ВТК, 2008. – 28 с. |
|
|
Д 18 |
|
|
Методические рекомендации по выполнению практических работ, посвящены вопросам проектирования и производства деловой документации и создания графических элементов, могут быть использованы в качестве дидактического материала при изучении дисциплин информатического цикла. Предназначено для студентов, обучающихся по специальностям «Реклама» и «Дизайн»; теоретикам и практикам, занимающимся проблемами проектирования и производства рекламного продукта, специалистам по рекламе, графическим дизайнерам. |
УДК 004.9
ББК 32.97
©РИО ГОУ СПО «ВТК», 2008
©Данилькевич А.В., 2008
СОДЕРЖАНИЕ
|
Практическая работа 1 ………………………………………………………… |
4 |
|
Практическая работа 2 ………………………………………………………… |
10 |
|
Практическая работа 3 ………………………………………………………… |
14 |
|
Практическая работа 4 ………………………………………………………… |
19 |
|
Практическая работа 5 ………………………………………………………… |
23 |
|
Список литературы …………………………………………………………….. |
29 |
|
Для заметок ………………………………………………………………………. |
30 |
Практическая работа 1
Тема: ОРГАНИЗАЦИЯ РАСЧЕТОВ В ТАБЛИЧНОМ ПРОЦЕССОРЕ MS EXCEL
Цель занятия. Изучение информационной технологии использования встроенных вычислительных функций Excel для финансового анализа.
Задание 1.1. Создать таблицу финансовой сводки за неделю, произвести расчеты, построить диаграмму изменения финансового результата, произвести фильтрацию данных.
Исходные данные представлены на рис. 1.1, результаты работы – на рис. 1.7, 1.9 и 1.12.
Порядок работы
1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу (при стандартной установке MS Office выполните Пуск/Программы/Microsoft Excel).
рис. 1.1. Исходные данные для задания 1.1
рис. 1.2. Создание стиля оформления шапки таблицы
рис. 1.3. Форматирование ячеек – перенос по словам
рис. 1.4. Задание формата отрицательных чисел красным цветом
Краткая справка. Для ввода дней недели наберите «Понедельник» и произведите автокопирование до «Воскресенья» (левой кнопкой мыши за маркер автозаполнения в правом нижнем углу ячейки).
5. Произведите расчеты в графе «Финансовый результат» по следующей формуле:
Финансовый результат = Доход – Расход,
для этого в ячейке D4 наберите формулу = В4 – С4.
Краткая справка. Введите расчетную формулу только для расчета по строке «Понедельник», далее произведите автокопирование формулы (так как в графе «Расход» нет незаполненных данными ячеек, можно производить автокопирование двойным щелчком мыши по маркеру автозаполнения в правом нижнем углу ячейки).
6. Для ячеек с результатом расчетов задайте формат – «Денежный» с выделением отрицательных чисел красным цветом (рис. 1.4) (Формат/Ячейки/вкладка Число/ формат – Денежный/ отрицательные числа – красные. Число десятичных знаков задайте равное 2).
Обратите внимание, как изменился цвет отрицательных значений финансового результата на красный.
7. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопка fx). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические». Для расчета функции СРЗНАЧ дохода установите курсор в соответствующей ячейке для расчета среднего значения (В 11), запустите мастер функций (Вставка/Функция/категория – Статистические/СРЗНАЧ) (рис. 1.5). В качестве первого числа выделите группу ячеек с данными для расчета среднего значения – В4:В10.
рис. 1.5. Выбор функции расчета среднего значения
Аналогично рассчитайте «Среднее значение» расхода.
8. В ячейке D13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться кнопкой Автосуммирования (∑) на панели инструментов или функцией СУММ (рис. 1.6). В качестве первого числа выделите группу ячеек с данными для расчета суммы – D4:D10.
рис. 1.6. Задание интервала ячеек при суммировании функцией СУММ
9. Проведите форматирование заголовка таблицы. Для этого выделите интервал ячеек от А1 до D1, объедините их кнопкой панели инструментов Объединить и поместить в центре или командой меню Формат/Ячейки/ вкладка Выравнивание/ отображение – Объединение ячеек). Задайте начертание шрифта — полужирное; цвет – по вашему усмотрению.
Конечный вид таблицы приведен на рис. 1.7.
10. Постройте диаграмму (линейчатого типа) изменения финансовых результатов по дням недели с использованием мастера диаграмм.
Для этого выделите интервал ячеек с данными финансового результата и выберите команду Вставка/Диаграмма. На первом шаге работы с мастером диаграмм выберите тип диаграммы – линейчатая; на втором шаге на вкладке Ряд в окошке Подписи оси Х укажите интервал ячеек с днями недели – А4:А10 (рис. 1.8).
Далее введите название диаграммы и подписи осей; дальнейшие шаги построения диаграммы осуществляются автоматически по подсказкам мастера.
Конечный вид диаграммы приведен на рис. 1.9.
рис. 1.7. Таблица финансового результата (Задание 1.1)
11. Произведите фильтрацию значений дохода, превышающих 4000 р.
Краткая справка. В режиме фильтра в таблице видны только те данные, которые удовлетворяют некоторому критерию, при этом остальные строки скрыты. В этом режиме все операции форматирования, копирования, автозаполнения, автосуммирования и т.д. применяются только к видимым ячейкам листа.
рис. 1.8. Задание Подписи оси Х при построении диаграммы
рис. 1.9. Конечный вид диаграммы Задания 1.1
Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Данные/Фильтр/Автофильтр. В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «Доход»), и вы увидите список всех неповторяющихся значений этого поля. Выберите команду для фильтрации – Условие (рис. 1.10).
В открывшемся окне Пользовательский автофильтр задайте условие «Больше 4000» (рис. 1.11).
рис. 1.11. Пользовательский автофильтр
рис. 1.12. Вид таблицы после фильтрации данных
Произойдет отбор данных по заданному условию.
Проследите, как изменились вид таблицы (рис. 1.12) и построенная диаграмма.
12. Сохраните созданную электронную книгу в своей папке.
Дополнительные задания
Задание 1.2. Заполнить таблицу, произвести расчеты, выделить минимальную и максимальную суммы покупки (рис. 1.13); по результатам расчета построить круговую диаграмму суммы продаж.
рис. 1.13. Исходные данные для задания 1.2
Используйте созданный стиль (Формат/Стиль/Шапка таблиц). Формулы для расчета:
Сумма = Цена * Количество;
Всего = сумма значений колонки «Сумма».
Краткая справка. Для выделения максимального/минимального значений установите курсор в ячейке расчета, выберите встроенную функцию Excel МАКС (МИН) из категории «Статистические», в качестве первого числа выделите диапазон ячеек значений столбца «Сумма» (ячейки ЕЗ:Е10).
Задание 1.3. Заполнить ведомость учета брака, произвести расчеты, выделить минимальную, максимальную и среднюю суммы брака, а также средний процент брака; произвести фильтрацию данных по условию процента брака < 8 %, построить график отфильтрованных значений изменения суммы брака по месяцам (рис. 1.14).
Формула для расчета:
Сумма брака = Процент брака * Сумма зарплаты.
Краткая справка. В колонке «Процент брака» установите процентный формат чисел (Формат/Ячейки/вкладка Число/формат – Процентный).
рис. 1.14. Исходные данные для задания 1.3
Задание 1.4. Заполнить таблицу анализа продаж, произвести расчеты, выделить минимальную и максимальную продажи (количество и сумму); произвести фильтрацию по цене, превышающей 9000 р., построить гистограмму отфильтрованных значений изменения выручки по видам продукции (рис. 1.15).
Формулы для расчета:
Всего = Безналичные платежи + Наличные платежи;
Выручка от продажи = Цена * Всего.
рис. 1.15. Исходные данные для задания 1.4
Практическая работа 2
Тема: СОЗДАНИЕ ЭЛЕКТРОННОЙ КНИГИ. ОТНОСИТЕЛЬНАЯ И АБСОЛЮТНАЯ АДРЕСАЦИИ В MS EXCEL
Цель занятия. Применение относительной и абсолютной адресаций для расчетов. Сортировка, условное форматирование и копирование созданных таблиц. Работа с листами электронной книги.
Задание 2.1. Создать таблицы ведомости начисления заработной платы за два месяца на разных листах электронной книги, произвести расчеты, форматирование, сортировку и защиту данных.
Исходные данные представлены на рис. 2.1, результаты работы – на рис. 2.6.
Порядок работы
1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.
2. Создайте таблицу расчета заработной платы по образцу (см. рис. 2.1). Введите исходные данные – Табельный номер, ФИО и Оклад, % Премии = 27 %, % Удержания = 13 %.
Примечание. Выделите отдельные ячейки для значений % Премии (D4) и % Удержания (F4).
рис. 2.1. Исходные данные для Задания 9.1
Произведите расчеты во всех столбцах таблицы.
При расчете Премии используется формула Премия = Оклад * % Премии, в ячейке D5 наберите формулу = $D$4 * С5 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.
Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка (т. е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символов $ с клавиатуры или нажатием клавиши [F4]).
Формула для расчета «Всего начислено»:
Всего начислено = Оклад + Премия.
При расчете Удержания используется формула:
Удержание = Всего начислено * % Удержания,
для этого в ячейке F5 наберите формулу = $F$4 * Е5.
Формула для расчета столбца «К выдаче»:
К выдаче = Всего начислено - Удержания.
Краткая справка. Каждая рабочая книга Excel может содержать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того, чтобы хранить большие последовательные наборы данных на одном листе.
рис. 2.2. Итоговый вид таблицы расчета заработной платы за июнь
5. Скопируйте содержимое листа «Зарплата июнь» на новый лист (Правка/Переместить/Скопировать лист). Можно воспользоваться командой Переместить/Скопировать контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окошке Создавать копию (рис. 2.3).
Краткая справка. Перемещать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]).
6. Присвойте скопированному листу название «Зарплата июль». Исправьте название месяца в названии таблицы. Измените значе ние Премии на 32 %. Убедитесь, что программа произвела пересчет формул.
рис. 2.3. Копирование листа электронной книги
8. Измените формулу для расчета значений колонки «Всего начислено»:
Всего начислено = Оклад + Премия + Доплата.
рис. 2.4. Условное форматирование данных
рис. 2.5. Сортировка данных
12. Защитите лист «Зарплата июль» от изменений (Сервис/Зaщита/Защитить лист). Задайте пароль на лист (рис. 2.7), сделайте подтверждение пароля (рис. 2.8)
Убедитесь, что лист защищен и невозможно удаление данных. Снимите защиту листа (Сервис/Защита/Снять защиту листа).
рис. 2.6. Конечный вид ведомости зарплаты за июль
рис. 2.7. Защита листа электронной книги
рис. 2.8. Подтверждение пароля
13. Сохраните созданную электронную книгу под именем «Задание 2.1» в своей папке.
Дополнительные задания
Задание 2.2. Сделать примечания к двум-трем ячейкам.
Задание 2.3. Выполнить условное форматирование оклада и премии за июль месяц:
до 2000 р. – желтым цветом заливки;
от 2000 до 9000 р. – зеленым цветом шрифта;
свыше 9000 р. – малиновым цветом заливки, белым цветом
шрифта.
Задание 2.4. Защитить лист зарплаты за июнь от изменений.
Проверьте защиту. Убедитесь в неизменяемости данных. Снимите защиту со всех листов электронной книги «Задание 2.1».
Задание 2.5. Построить круговую диаграмму начисленной суммы к выдаче всех сотрудников за июль месяц.
Практическая работа 3
Тема: СВЯЗАННЫЕ ТАБЛИЦЫ. РАСЧЕТ ПРОМЕЖУТОЧНЫХ ИТОГОВ В ТАБЛИЦАХ MS EXCEL
Цель занятия. Связывание листов электронной книги. Расчет промежуточных итогов. Структурирование таблицы.
Задание 3.1. Рассчитать зарплату за август и построить диаграмму. Создать итоговую таблицу ведомости квартального начисления заработной платы, провести расчет промежуточных итогов по подразделениям.
Порядок работы
1. Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в Практической работе 2 файл «Зарплата».
2. Скопируйте содержимое листа «Зарплата июль» на новый лист электронной книги (Правка/Переместить/Скопировать лист). Не забудьте для копирования поставить галочку в окошке Создавать копию.
3. Присвойте скопированному листу название «Зарплата август». Исправьте название месяца в ведомости на август.
рис. 3.1. Ведомость зарплаты за август
5. По данным таблицы «Зарплата август» постройте гистограмму доходов сотрудников. В качестве подписей оси X выберите фамилии сотрудников. Проведите форматирование диаграммы. Конечный вид гистограммы приведен на рис. 3.2.
рис. 3.2. Гистограмма зарплаты за август
Краткая справка. Чтобы вставить в формулу адрес или диапазон ячеек с другого листа, следует во время ввода формулы щелкнуть по закладке этого листа и выделить на нем нужные ячейки. Вставляемый адрес будет содержать название этого листа.
В ячейке D5 для расчета квартальных начислений «Всего начислено» формула имеет вид: = 'Зарплата июнь'!F5 + 'Зарплата июль'!F5 + 'Зарплата август'!F5.
Аналогично произведите квартальный расчет «Удержания» и «К выдаче».
Примечание. При выборе начислений за каждый месяц делайте ссылку на соответствующую ячейку из таблицы соответствующего листа электронной книги «Зарплата». При этом произойдет связывание информации соответствующих ячеек листов электронной книги.
рис. 3.3. Таблица для расчета итоговой квартальной заработной платы.
12. В силу однородности расчетных таблиц зарплаты по месяцам для расчета квартальных значений столбцов «Удержание» и «К выдаче» достаточно скопировать формулу из ячейки D5 в ячейки Е5 и F5 (рис. 3.4).
Для расчета квартального начисления заработной платы для всех сотрудников скопируйте формулы в столбцах D, Е и F. Ваша электронная таблица примет вид, как на рис. 3.4.
13. Для расчета промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений – по фамилиям. Таблица примет вид, как на рис. 3.5.
14. Подведите промежуточные итоги по подразделениям, используя формулу суммирования. Для этого выделите всю таблицу и выполните команду Данные/Итоги (рис. 3.6). Задайте параметры подсчета промежуточных итогов:
при каждом изменении в – Подразделение;
операция – Сумма;
добавить итоги по: Всего начислено, Удержания, К выдаче.
рис. 3.4. Расчет трехмесячного начисления заработной платы, связыванием листов электронной книги
рис. 3.5. Вид таблицы начисления квартальной заработной платы после сортировки по подразделениям
рис. 3.6. Окно задания параметров расчета промежуточных итогов
рис. 3.7. Итоговый вид таблицы расчета квартальных итогов по зарплате
Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными».
Примерный вид итоговой таблицы представлен на рис. 3.7.
15. Изучите полученную структуру и формулы подведения промежуточных итогов, устанавливая курсор на разные ячейки таблицы. Научитесь сворачивать и разворачивать структуру до разных уровней (кнопками «+» и «-»).
Краткая справка. Под структурированием понимается многоуровневая группировка строк и столбцов таблицы и создание элементов управления, с помощью которых легко можно скрывать и раскрывать эти группы.
16. Сохраните файл «Зарплата» с произведенными изменениями (Файл/Сохранить).
Дополнительные задания
Задание 3.2. Исследовать графическое отображение зависимостей ячеек друг от друга.
Порядок работы
Скопируйте содержимое листа «Зарплата июнь» на новый лист. Копии присвойте имя «Зависимости». Откройте панель «Зависимости» (Сервис/Зависимости/Панель зависимостей) (рис. 3.8). Изучите назначение инструментов панели, задерживая на них указатель мыши.
Устанавливайте курсор на ячейку в каждом столбце и вызывайте зависимости кнопками Влияющие ячейки и Зависимые ячейки панели «Зависимости». Появятся стрелки, указывающие на зависимость ячейки от других ячеек и ее влияние на другие ячейки. Примерный вид таблицы с зависимостями приведен на рис. 3.9. Сохраните файл «Зарплата» с произведенными изменениями.
рис. 3.8. Панель зависимостей
рис. 3.9. Зависимости в таблице расчета зарплаты
Практическая работа 4
Тема: ПОДБОР ПАРАМЕТРА. ОРГАНИЗАЦИЯ ОБРАТНОГО РАСЧЕТА
Цель занятия. Изучение технологии подбора параметра при обратных расчетах.
Задание 4.1. Используя режим подбора параметра, определить, при каком значении % Премии общая сумма заработной платы за июнь будет равна 250000 р. (на основании файла «Зарплата», созданного в Практических работах 2... 3).
Краткая справка. К исходным данным этой таблицы относятся значения Оклада и % Премии, одинакового для всех сотрудников. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчетов. Использование операции «Подбор параметра» в MS Excel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое удовлетворяющее заданным условиям, значение исходного параметра расчета.
Порядок работы
1. Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в Практических работах 2…3 файл «Зарплата».
2. Скопируйте содержимое листа «Зарплата июнь» на новый лист электронной книги (Правка/Переместить/Скопировать лист). Не забудьте для копирования поставить галочку в окошке Создаватъ копию. Присвойте скопированному листу имя «Подбор параметра».
3. Осуществите подбор параметра командой Сервис/Подбор параметра (рис. 4.1).
рис. 4.1. Задание параметров подбора параметра
рис. 4.2. Подтверждение результатов подбора параметра
В диалоговом окне Подбор параметра на первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарплаты (ячейка G19), на второй строке наберите заданное значение 250000, на третьей строке укажите адрес подбираемого значения % Премии (ячейка D4), затем нажмите кнопку ОК. В окне Результат подбора параметра дайте подтверждение подобранному параметру нажатием кнопки ОК (рис. 4.2).
Произойдет обратный пересчет % Премии. Результаты подбора (рис. 4.3):
если сумма к выдаче равна 250000 р., то % Премии должен быть 222 %.
рис. 4.3. Подбор значения % Премии для заданной общей суммы заработной платы, равной 250000 р.
Задание 4.2. Используя режим подбора параметра, определить штатное расписания фирмы. Исходные данные приведены на рис. 4.4.
Краткая справка. Известно, что в штате фирмы состоит:
6 курьеров;
8 младших менеджеров;
9 менеджеров;
3 заведующих отделами;
1 главный бухгалтер;
1 дизайнер;
1 специалист по рекламе;
1 системный аналитик;
1 генеральный директор фирмы.
рис. 4.4. Исходные данные для Задания 4.2
Общий месячный фонд зарплаты составляет 150000 р. Необходимо определить, какими должны быть оклады сотрудников фирмы.
Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата = Ai * x + Bi, где x – оклад курьера; Ai и Bi – коэффициенты, показывающие:
Ai – во сколько раз превышается значение х;
Bi – на сколько превышается значение х.
Порядок работы
= В6 * $D$3 + С6 (ячейка D3 задана в виде абсолютной адресации). Далее скопируйте формулу из ячейки D6 вниз по столбцу автокопированием.
В столбце F задайте формулу расчета заработной платы всех работающих в данной должности. Например, для ячейки F6 формула расчета имеет вид = D6 * Е6. Далее скопируйте формулу из ячейки F6 вниз по столбцу автокопированием.
В столбцах D и F установите числовой формат с 2-мя десятичными знаками.
В ячейке F15 автосуммированием вычислите суммарный фонд заработной платы фирмы.
5. Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы, равной 150000 р. Для этого в меню Сервис активизируйте команду Подбор параметра.
В поле Установить в ячейке появившегося окна введите ссылку на ячейку F15, содержащую формулу расчета фонда заработной платы;
в поле Значение наберите искомый результат 150000;
в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 150000 р. (см. рис. 4.5)
рис. 4.5. Результат обратного расчета зарплаты сотрудников, при фонде 150000 р.
6. Присвойте рабочему листу имя «Штатное расписание 1». Сохраните созданную электронную книгу под именем «Штатное расписание» в своей папке.
Анализ задач показывает, что с помощью MS Excel можно решать линейные уравнения. Задания 4.1 и 4.2 показывают, что поиск значения параметра формулы – это не что иное, как численное решение уравнений. Другими словами, используя возможности программы MS Excel, можно решать любые уравнения с одной переменной.
Задание 4.3. Используя режим подбора параметра и таблицу расчета штатного расписания (см. задание 4.2), определить заработные платы сотрудников фирмы для ряда заданных значений фонда заработной платы.
Порядок работы
Методом подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 150000, 250000, 250000, 300000, 350000, 400000, 450000 р. Результаты подбора значений зарплат скопируйте в табл. 4.2. в виде специальной вставки.
Таблица 4.1
|
Должность |
Вариант 1 |
Вариант 2 |
Вариант 3 |
Вариант 4 |
Вариант 5 |
|||||
|
коэф. А |
коэф. В |
коэф. А |
коэф. В |
коэф. А |
коэф. В |
коэф. А |
коэф. В |
коэф. А |
коэф. В |
|
|
Курьер |
1 |
0 |
1 |
0 |
1 |
0 |
1 |
0 |
1 |
0 |
|
Младший менеджер |
1,4 |
0 |
1,2 |
300 |
2 |
300 |
1,7 |
400 |
1,44 |
500 |
|
Менеджер |
2,2 |
900 |
1,6 |
1000 |
2,5 |
600 |
2,3 |
900 |
2,45 |
1000 |
|
Зав. отделом |
3 |
1200 |
1,9 |
1300 |
2,7 |
900 |
2,8 |
1400 |
3,6 |
2000 |
|
Главный бухгалтер |
4,2 |
2000 |
2,5 |
2030 |
3,9 |
3500 |
4,1 |
3000 |
4,25 |
4000 |
|
Дизайнер |
3,8 |
1900 |
3,3 |
2000 |
2,5 |
1500 |
2,6 |
2600 |
2,95 |
2600 |
|
Специалист по рекламе |
3,1 |
0 |
2,5 |
200 |
2 |
1000 |
2,4 |
2000 |
2,15 |
800 |
|
Системный аналитик |
3,4 |
1300 |
1,8 |
600 |
1,9 |
950 |
2,1 |
2000 |
1,95 |
750 |
|
Ген. директор |
5,8 |
3000 |
5 |
2100 |
4,8 |
4000 |
4,4 |
3900 |
5,55 |
3590 |
Таблица 4.2
|
Фонд заработной платы (тыс. руб.) |
150 |
200 |
250 |
300 |
350 |
400 |
450 |
|
Должность |
Зарплата сотруд. |
Зарплата сотруд |
Зарплата сотруд |
Зарплата сотруд |
Зарплата сотруд |
Зарплата сотруд |
Зарплата сотруд |
|
Курьер |
? |
? |
? |
? |
? |
? |
? |
|
Младший менеджер |
? |
? |
? |
? |
? |
? |
? |
|
Менеджер |
? |
? |
? |
? |
? |
? |
? |
|
Зав. отделом |
? |
? |
? |
? |
? |
? |
? |
|
Главный бухгалтер |
? |
? |
? |
? |
? |
? |
? |
|
Дизайнер |
? |
? |
? |
? |
? |
? |
? |
|
Специалист по рекламе |
? |
? |
? |
? |
? |
? |
? |
|
Системный аналитик |
? |
? |
? |
? |
? |
? |
? |
|
Ген. директор |
? |
? |
? |
? |
? |
? |
? |
Краткая справка. Для копирования результатов расчетов в виде значений необходимо выделить копируемые данные, произвести запись в буфер памяти (Правка/Копировать), установить курсор в соответствующую ячейку таблицы ответов, задать режим специальной вставки (Правка/Специальная вставка), отметив в качестве объекта вставки – значения (Правка/ Специальная вставка/ вставить – значения) (рис. 4.6).
рис. 4.6. Специальная вставка значений данных
Специальная вставка информации в виде значений позволяет копировать значения, полученные в результате расчетов, без дальнейшей их зависимости от пересчета формул.
Практическая работа 5
Тема: ЗАДАЧИ ОПТИМИЗАЦИИ (ПОИСК РЕШЕНИЯ)
Цель занятия. Изучение технологии поиска решения для задач оптимизации (минимизации, максимизации).
Задание 5.1. Минимизация фонда заработной платы фирмы.
Пусть известно, что для нормальной работы фирмы требуется 5...7 курьеров, 8... 10 младших менеджеров, 10 менеджеров, 2…3 заведующих отделами, главный бухгалтер, дизайнер, специалист по рекламе, системный аналитик, генеральный директор фирмы.
Общий месячный фонд зарплаты должен быть минимален. Необходимо определить, какими должны быть оклады сотрудников фирмы, при условии, что оклад курьера не должен быть меньше 1400 р.
В качестве модели решения этой задачи возьмем линейную модель. Тогда условие задачи имеет вид:
N1 * А1 * х + N2 * (А2 * х + В2) + . . . + N8 * (А8 * х + В8) = Минимум,
где Ni – количество работников данной специальности; х – зарплата курьера; А i и В i – коэффициенты заработной платы сотрудников фирмы.
Порядок работы
1. Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в Практической работе 4 файл «Штатное расписание».
Скопируйте содержимое листа «Штатное расписание 2».
2. В меню Сервис активизируйте команду Поиск решения (рис. 5.1).
3. В окне Установить целевую ячейку укажите ячейку F15, содержащую модель – суммарный фонд заработной платы.
Поскольку необходимо минимизировать общий месячный фонд зарплаты, активизируйте кнопку равный – Минимальному значению.
В окне Изменяя ячейки укажите адреса ячеек, в которых будет отражено количество курьеров и младших менеджеров, а также зарплата курьера – $E$6:$E$7:$D$3 (при задании ячеек Е6, Е7 и D3 держите нажатой клавишу [Ctrl]).
рис. 5.1. Задание условий для минимизации фонда заработной платы
рис. 5.2. Добавление ограничений для минимизации фонда заработной платы
Используя кнопку Добавить в окнах Поиск решения и Добавление ограничений, опишите все ограничения задачи: количество курьеров изменяется от 5 до 7, младших менеджеров от 8 до 10, а зарплата курьера > 1400 (рис. 5.2). Ограничения наберите в виде:
$D$3 > = 1400
$Е$6 > = 5
$Е$6 < = 7
$Е$7 > = 8
$Е$7 < = 10.
Активизировав кнопку Параметры, введите параметры поиска, как показано на рис. 5.3.
Окончательный вид окна Поиск решения приведен на рис. 5.1.
рис. 5.3. Задание параметров поиска решения по минимизации фонда заработной платы
Запустите процесс поиска решения нажатием кнопки Выполнить. В открывшемся диалоговом окне Результаты поиска решения задайте опцию Сохранить найденное решение (рис. 5.4).
Решение задачи приведено на рис. 5.5. Оно тривиально: чем меньше сотрудников и чем меньше их оклад, тем меньше месячный фонд заработной платы.
рис. 5.4. Сохранение найденного при поиске решения
рис. 5.5. Минимизация фонда заработной платы
Таблица 5.1
|
Сырье |
Нормы расхода сырья |
Запас сырья |
||
|
А |
В |
С |
||
|
Сырье 1 |
20 |
16 |
13 |
400 |
|
Сырье 2 |
8 |
6 |
5 |
210 |
|
Сырье 3 |
7 |
5 |
5 |
130 |
|
Прибыль |
25 |
20 |
18 |
Задание 5.2. Составление плана выгодного производства.
Фирма производит несколько видов продукции из одного и того же сырья – А, В и С. Реализация продукции А дает прибыль 25., В – 20 р. и С – 18 р. на единицу изделия.
Продукцию можно производить в любых количествах, поскольку известно, что сбыт обеспечен, но ограничены запасы сырья. Необходимо определить, какой продукции и сколько надо произвести, чтобы общая прибыль от реализации была максимальной.
Нормы расхода сырья на производство продукции каждого вида приведены в табл. 5.1.
Порядок работы
Расход сырья 1 = (количество сырья 1) * (норма расхода сырья А) + (количество сырья 1) * (норма расхода сырья В) + (количество сырья 1) * (норма расхода сырья С).
Значит, в ячейку F5 нужно ввести формулу:
= В5 * $В$9 + С5 * $С$9 + D5 * $D$9.
Обратите внимание, что значения количества сырья каждого вида пока не известны и будут подобраны в процессе решения задания (ячейки B9:D9 пока пустые).
(Общая прибыль по А) = (прибыль на ед. изделий А) * (количество А), следовательно в ячейку В10 следует ввести формулу = В8 * В9.
Итоговая общая прибыль:
= (Общая прибыль по А) + (Общая прибыль по В) + (Общая прибыль по С), значит в ячейку Е10 следует ввести формулу = СУММ(В10:О10).
рис. 5.6. Исходные данные для Задания 5.2
3. В меню Сервис активизируйте команду Поиск решения и введите параметры поиска, как указано на рис. 5.7.
В качестве целевой ячейки укажите ячейку «Итоговая общая прибыль» (Е10), в качестве изменяемых ячеек – ячейки количества сырья – (B9:D9).
Не забудьте задать максимальное значение суммарной прибыли и указать ограничения на запас сырья:
расход сырья 1 < = 400; расход сырья 2 < = 210; расход сырья 3 < = 130, а также положительные значения количества сырья А, В, C > = 0.
рис. 5.7. Задание условий и ограничений для поиска решений
Установите параметры поиска решения (рис. 5.8). Для этого кнопкой Параметры откройте диалоговое окно Параметры поиска решения, установите параметры по образцу, задайте линейную модель расчета (Линейность модели).
рис. 5.8. Задание параметров поиска решения
рис. 5.9. Найденное решение максимизации прибыли при заданных ограничениях
Замечание. В ячейках значений результатов Количество сырья, Общая прибыль, Расход сырья установлен числовой формат с 2-мя десятичными знаками для округления результатов найденных решений максимизации прибыли.
Выводы. Из решения видно, что оптимальный план выпуска предусматривает изготовление 20,67 кг продукции В и 5,33 кг продукции С. Продукцию А производить не стоит. Полученная прибыль при этом составит 509,33 р.
Дополнительные задания
Используя файл «План производства» (см. задание 5.2), определить план выгодного производства, т.е. какой продукции и сколько необходимо произвести, чтобы общая прибыль от реализации была максимальной.
Выберите нормы расхода сырья на производство продукции каждого вида и ограничения по запасам сырья из таблицы соответствующего варианта (4 варианта):
Вариант 1
|
Сырье |
Нормы расхода сырья |
Запас сырья |
||
|
А |
В |
С |
||
|
Сырье 1 |
15 |
18 |
23 |
405 |
|
Сырье 2 |
9 |
13 |
18 |
350 |
|
Сырье 3 |
5 |
8 |
3 |
100 |
|
Прибыль на ед. изделия |
12 |
15 |
18 |
|
|
Количество продукции |
? |
? |
? |
|
|
Общая прибыль |
? |
? |
? |
? |
Вариант 2
|
Сырье |
Нормы расхода сырья |
Запас сырья |
||
|
А |
В |
С |
||
|
Сырье 1 |
12 |
10 |
19 |
800 |
|
Сырье 2 |
3 |
5 |
3 |
400 |
|
Сырье 3 |
25 |
19 |
30 |
1200 |
|
Прибыль на ед. изделия |
25 |
20 |
24 |
|
|
Количество продукции |
? |
? |
? |
|
|
Общая прибыль |
? |
? |
? |
? |
Вариант 3
|
Сырье |
Нормы расхода сырья |
Запас сырья |
||
|
А |
В |
С |
||
|
Сырье 1 |
29 |
18 |
6 |
5000 |
|
Сырье 2 |
12 |
16 |
25 |
4000 |
|
Сырье 3 |
9 |
22 |
7 |
3000 |
|
Прибыль на ед. изделия |
200 |
230 |
180 |
|
|
Количество продукции |
? |
? |
? |
|
|
Общая прибыль |
? |
? |
? |
? |
Вариант 4
|
Сырье |
Нормы расхода сырья |
Запас сырья |
||
|
А |
В |
С |
||
|
Сырье 1 |
8 |
9 |
10 |
840 |
|
Сырье 2 |
11 |
8 |
5 |
480 |
|
Сырье 3 |
2 |
6 |
8 |
330 |
|
Прибыль на ед. изделия |
120 |
180 |
100 |
|
|
Количество продукции |
? |
? |
? |
|
|
Общая прибыль |
? |
? |
? |
? |
Список литературы
Для заметок
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
Данилькевич А.В.
Информационные технологии в профессиональной деятельности
методические указания по выполнению практических работ: в 4 частях.
Часть 2. Табличный процессор MS Excel
Ответственный за издание – главный методист Камынина Т.В.
Нач. редакционно-издательского отдела – Черницын А.Ю.
Корректор – Козырева Л.Н.
Подписано в печать 20.03.2008 г. Формат 140х210 1/16
Times NR. Бумага офсетная.
Усл. печ. 1,5 .л. Тираж 100 Заказ 206
ГОУ СПО «Волгоградский технологический колледж»
Редакционно-издательский отдел
400107, г. Волгоград, проспект Маршала Жукова, 83
Отпечатано: Волгоградский технологический колледж