Лаб. раб - Электронная таблица для дневников. Табличный процессор ms excel

• Название: Табличный процессор ms excel
• Дата: 16.11.2022
• Размер: 4.22 Mb.
• Имя файла: Лаб. раб - Электронная таблица для дневников.doc
• Тип: Документы; #792299
• страница: 2 из 3

Тема 4: ПОДБОР ПАРАМЕТРА. ОРГАНИЗАЦИЯ ОБРАТНОГО РАСЧЕТА

Цель занятия. Изучение технологии подбора параметра при об- атных расчетах.

Задание 1. Используя режим подбора параметра, определить, ри каком значении % Премии общая сумма заработной платы за ктябрь будет равна 250 000 р. (на основании файла «Зарплата», эзданного в Практических работах 9... 10).

Краткая справка. К исходным данным этой таблицы гносятся значения Оклада и % Премии, одинакового для всех этрудников. Результатом вычислений являются ячейки, содер­жите формулы, при этом изменение исходных данных приво- ит к изменению результатов расчетов. Использование операции Подбор параметра» в MS Excel позволяет производить обратный асчет, когда задается конкретное значение рассчитанного пара метра, и по этому значению подбирается некоторое удовлетворя­ющее заданным условиям, значение исходного параметра рас­чета.

Порядок работы

1. 
   Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в прошлых работах файл «Зарплата».
   
2. 
   Скопируйте содержимое листа «Зарплата октябрь» на новый лист электронной книги (Правка/Переместить/Скопировать лист). Не забудьте для копирования поставить галочку в окошке Созда­вать копию. Присвойте скопированному листу имя «Подбор пара­метра».
   
3. 
   Осуществите подбор параметра командой Сервис/Подбор па­раметра (рис. 11.1).
   

В диалоговом окне Подбор параметра на первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарп­латы (ячейка G19), на второй строке наберите заданное значение 250 000, на третьей строке укажите адрес подбираемого значения % Премии (ячейка D4), затем нажмите кнопку ОК. В окне Результат подбора параметра дайте подтверждение подобранному параметру на­жатием кнопки ОК{ рис. 11.2).

Произойдет обратный пересчет % Премии. Результаты подбора (рис. 11.3):

если сумма к выдаче равна 250 000 р., то % Премии должен быть 203 %.

Рис.11.1 Задание параметров подбора параметров

Рис. 11.2. Подтверждение результатов подбора параметров

Задание 2. Используя режим подбора параметра, определить штатное расписания фирмы. Исходные данные приведены на рис. 11.4.

Краткая справка. Известно, что в штате фирмы состоит:

6 курьеров;8 младших менеджеров;10 менеджеров;3 заведующих отделами;1 главный бухгалтер;1 программист;1 системный аналитик;1 генеральный директор фирмы.

Рис. 11. 4. Исходные данные для Задания 2

Общий месячный фонд зарплаты составляет 100000 р. Необ­ходимо определить, какими должны быть оклады сотрудников фирмы.

Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата =А,-*х+ В, где х — оклад курьера; А₍ и В — коэффициенты, показывающие:

Aj — во сколько раз превышается значение х;

В— на сколько превышается значение х.

Порядок работы

1. 
   Запустите редактор электронных таблиц Microsoft Excel.
   
2. 
   Создайте таблицу штатного расписания фирмы по приведен­ному образцу (см. рис. 11.4). Введите исходные данные в рабочий лист электронной книги.
   
3. 
   Выделите отдельную ячейку D3 для зарплаты курьера (пере­менная «х») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.
   
4. 
   В столбце D введите формулу для расчета заработной платы по каждой должности. Например, для ячейки D6 формула расчета имеет следующий вид: = В6 * $D$3 + С6 (ячейка D3 задана в виде абсо­лютной адресации). Далее скопируйте формулу из ячейки D6 вниз по столбцу автокопированием.
   

В столбце F задайте формулу расчета заработной платы всех ра­ботающих в данной должности. Например, для ячейки F6 формула расчета имеет вид = D6 * Е6. Далее скопируйте формулу из ячейки F6 вниз по столбцу автокопированием.

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

5. 
   Произведите подбор зарплат сотрудников фирмы для суммар­ной заработной платы, равной 100000 р. Для этого в меню Сервис активизируйте команду Подбор параметра.
   

В поле Установить в ячейке появившегося окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной платы;

в поле Значение наберите искомый результат 100 000;

в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 р.

6. Присвойте рабочему листу имя «Штатное расписание 1». Со­храните созданную электронную книгу под именем «Штатное рас­писание» в своей папке.

Анализ задач показывает, что с помощью MS Excel можно ре­шать линейные уравнения. Задания 11.1 и 11.2 показывают, что поиск значения параметра формулы — это не что иное, как чис­ленное решение уравнений. Другими словами, используя возмож­ности программы MS Excel, можно решать любые уравнения с одной переменной.

Задание 3. Используя режим подбора параметра и таблицу расчета штатного расписания (см. задание 2), определить зара­ботные платы сотрудников фирмы для ряда заданных значений фонда заработной платы.


Порядок работы

1. 
   Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2». Выберите коэффициенты уравнений для расчета согласно табл. 1 (один из пяти вариантов расчетов).
   
2. 
   Методом подбора параметра последовательно определите зарпла­ты сотрудников фирмы для различных значений фонда заработной платы: 100000, 150000, 200000, 250000, 300000, 350000, 400000 р. Результаты подбора значений зарплат скопируйте в табл.2 . в виде специальной вставки.
   

Краткая справка. Для копирования результатов расчетов в виде значений необходимо выделить копируемые данные, произ­вести запись в буфер памяти (Правка/Копировать), установить кур­сор в соответствующую ячейку таблицы ответов, задать режим спе­циальной вставки (Правка/ Специальная вставка), отметив в каче­стве объекта вставки — значения (Правка/ Специальная вставка/ вставить — значения)

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

Тема 5: ЗАДАЧИ ОПТИМИЗАЦИИ (ПОИСК РЕШЕНИЯ)

Цель занятия. Изучение технологии поиска решения для задач оптимизации (минимизации, максимизации).

Задание 1. Минимизация фонда заработной платы фирмы.

Пусть известно, что для нормальной работы фирмы требуется 5...7 курьеров, 8... 10 младших менеджеров, 10 менеджеров, 3 за­ведующих отделами, главный бухгалтер, программист, системный аналитик, генеральный директор фирмы.

Общий месячный фонд зарплаты должен быть минимален. Не­обходимо определить, какими должны быть оклады сотрудников фирмы, при условии, что оклад курьера не должен быть меньше 1400 р.

В качестве модели решения этой задачи возьмем линейную мо­дель. Тогда условие задачи имеет вид

N₁ * A₁ * x + N₂ * (А₂ * х + В₂) + . . . + N₈ * ( А₈ * х + В₈) = Минимум,

где N/ — количество работников данной специальности; х — зарп­лата курьера; А, и В,- — коэффициенты заработной платы сотруд­ников фирмы.

Порядок работы

1. 
   Запустите редактор электронных таблиц Microsoft Excel и от­кройте созданный файл «Штатное рас­писание».
   

Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2».

2. 
   В меню Сервис активизируйте команду Поиск решения (рис. 12.1).
   
3. 
   В окне Установить целевую ячейку укажите ячейку F14, содер­жащую модель — суммарный фонд заработной платы.
   

Поскольку необходимо минимизировать общий месячный фонд зарплаты, активизируйте кнопку равный — Минимальному значе­нию.

В окне Изменяя ячейки укажите адреса ячеек, в которых будет отражено количество курьеров и младших менеджеров, а также зарплата курьера — $E$6:$E$7:$D$3 (при задании ячеек Е6, Е7 и D3 держите нажатой клавишу [Ctrl]).



Используя кнопку Добавить в окнах Поиск решения и Добавле­ние ограничений, опишите все ограничения задачи: количество ку­рьеров изменяется от 5 до 7, младших менеджеров от 8 до 10, а зарплата курьера > 1400 (рис. 12.2). Ограничения наберите в виде

$D$3 > = 1400

$Е$6> = 5

$Е$6< = 7

$Е$7> = 8

$Е$7 < = 10.

Активизировав кнопку Параметры, введите параметры поиска, как показано на рис. 12.3.

Окончательный вид окна Поиск решения приведен на рис. 12.1.

Запустите процесс поиска решения нажатием кнопки Выпол­нить. В открывшемся диалоговом окне Результаты поиска решения задайте опцию Сохранить найденное решение (рис. 12.4).

Решение задачи приведено на рис. 12.5. Оно тривиально: чем меньше сотрудников и чем меньше их оклад, тем меньше месяч­ный фонд заработной платы.








Задание 2. Составление плана выгодного производства

Фирма производит несколько видов продукции из одного и того же сырья — А, В и С. Реализация продукции А дает прибыль 10 р., В — 15 р. и С — 20 р. на единицу изделия.

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

Нормы расхода сырья на производство продукции каждого вида приведены в табл. 12.1.

Порядок работы

1. 
   Запустите редактор электронных таблиц Microsoft Excel и со­здайте новую электронную книгу.
   
2. 
   Создайте расчетную таблицу как на рис. 12.6. Введите исход­ные данные и формулы в электронную таблицу. Расчетные форму­лы имеют такой вид:
   

Расход сырья 1 = (количество сырья 1) * (норма расхода сырья А) + (количество сырья 1) * (норма расхода сырья В) + (количес­тво сырья 1) * (норма расхода сырья С).

Значит, в ячейку F5 нужно ввести формулу = В5 * $В$9 + С5 * * $С$9 + D5 * $D$9.

Обратите внимание, что значения количества сырья каждого вида пока не известны и будут подобраны в процессе решения задания (ячейки B9:D9 пока пустые).

(Общая прибыль по А) = (прибыль на ед. изделий А) * (количе­ство А),

следовательно в ячейку В10 следует ввести формулу = В8 * В9.

Итоговая общая прибыль = (Общая прибыль по А) + (Общая прибыль по В) + (Общая прибыль по С), значит в ячейку Е10 следует ввести формулу = CYMM(B10:D10).




3. В меню Сервис активизируйте команду Поиск решения и введи­те параметры поиска, как указано на рис. 12.7.

В качестве целевой ячейки укажите ячейку «Итоговая общая прибыль» (ЕЮ), в качестве изменяемых ячеек — ячейки количе­ства сырья — (B9:D9).

Не забудьте задать максимальное значение суммарной прибыли и указать ограничения на запас сырья:

расход сырья 1 < = 350; расход сырья 2 < = 200; расход сырья 3 < = 100, а также положительные значения количества сырья А, В, С > = 0.

Установите параметры поиска решения (рис. 12.8). Для этого кнопкой Параметры откройте диалоговое окно Параметры поискарешения, установите параметры по образцу, задайте линейную модель расчета (.Линейность модели).

4. 
   Кнопкой Выполнить запустите Поиск решения. Если вы сдела­ли все верно, то решение будет как на рис. 12.9.
   
5. 
   Сохраните созданный документ под именем «План производ­ства».
   

Выводы. Из решения видно, что оптимальный план выпуска предусматривает изготовление 5,56 кг продукции В и 22,22 кг про­дукции С. Продукцию А производить не стоит. Полученная при­быль при этом составит 527,78 р.

Дополнительные задания

Используя файл «План производства» (см. задание 12.2), оп­ределить план выгодного производства, т. е. какой продукции и сколько необходимо произвести, чтобы общая прибыль от реа­лизации была максимальной.

Выберите нормы расхода сырья на производство продукции каждого вида и ограничения по запасам сырья из таблицы соот­ветствующего варианта (5 вариантов):




Лабораторная работа

Тема 6:СВЯЗИ МЕЖДУ ФАЙЛАМИ И КОНСОЛИДАЦИЯ ДАННЫХ В MS EXCEL

Цель занятия. Изучение технологии связей между файлами и консолидации данных в MS Excel.

Задание 1. Задать связи между файлами.

Порядок работы

1. 
   Запустите редактор электронных таблиц Microsoft Excel и со­здайте новую электронную книгу.
   
2. 
   Создайте таблицу «Отчет о продажах 1 квартал» по образцу рис. 13.1. Введите исходные данные (Доходы и Расходы):
   

Доходы = 234,58 р.;

Расходы = 75,33 р. и проведите расчет Прибыли: Прибыль = Доходы - Расходы. Со­храните файл под именем «1 квартал».

3. 
   Создайте таблицу «Отчет о продажах 2 квартал» по образцу рис. 13.1 в виде нового файла. Для этого создайте новый документ (Файл/Создать) и скопируйте таблицу отчета о продаже за пер­вый квартал, после чего исправьте заголовок таблицы и измените исходные данные:
   

Доходы = 452,6 р.;

Расходы = 185,8 р.

Обратите внимание, как изменился расчет Прибыли. Сохрани­те файл под именем «2 квартал».

4. 
   Создайте таблицу «Отчет о продажах за полугодие» по образцу рис. 13.1 в виде нового файла. Для этого создайте новый документ (Файл/Создать) и скопируйте таблицу отчета о продаже за пер­вый квартал, после чего подправьте заголовок таблицы и в колон­ке В удалите все значения исходных данных и результаты расчетов. Сохраните файл под именем «Полугодие».
   

5. Для расчета полугодовых итогов свяжите формулами файлы «1 квартал» и «2 квартал».

Краткая справка. Для связи формулами файлов Excel вы­полните действия:

6. 
   откройте эти файлы (все три файла);
   
7. 
   начните ввод формулы в файле-клиенте (в файле «Полугодие» введите формулу для расчета «Доход за полугодие»).
   

Формула для расчета:

Доход за полугодие = Доход за 1 квартал + Доход за 2 квартал.

Чтобы вставить в формулу адрес ячейки или диапазона ячеек из другого файла (файла-источника), щелкните мышью по этим ячей­кам, при этом расположите окна файлов на экране так, чтобы они не перекрывали друг друга.

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

В ячейке ВЗ файла «Полугодие» формула для расчета полугодо­вого дохода имеет следующий вид:

=

Аналогично рассчитайте полугодовые значения Расходов и При­были, используя данные файлов «1 квартал» и «2 квартал». Резуль­таты работы представлены на рис. 13.1. Сохраните текущие резуль­таты расчетов.

Примечание.'>Примечание. Если файл-источник данных закрыт, в форму­ле, которая на него ссылается, будет указан весь путь для этого файла.

Задание 2. Обновить связи между файлами.

Порядок работы

1. 
   Закройте файл «Полугодие» предыдущего задания.
   
2. 
   Измените значения «Доходы» в файлах первого и второго квар­талов, увеличив их на 100 р.:
   

Доходы 1 квартала = 334,58 р.;

Доходы 2 квартала = 552,6 р.

Сохраните изменения и закройте файлы.

3. 
   Откройте файл «Полугодие». Одновременно с открытием файла появится окно с предложением обновить связи (рис. 13.2). Для об­новления связей нажмите кнопку Да. Проследите, как изменились данные файла «Полугодие» (величина «Доходы» должна увеличиться на 200 р. и принять значение 887,18 р.).
   

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

4. 
   Изучим процесс ручного обновления связи. Сохраните файл «Полугодие» и закройте его.
   
5. 
   Вновь откройте файлы первого и второго кварталов и измени­те исходные данные Доходов, увеличив значения на 100 р.:
   

Доходы 1 квартала = 434,58 р.;

Доходы 2 квартала = 652,6 р.

Сохраните изменения и закройте файлы.

6. 
   Откройте файл «Полугодие». Одновременно с открытием файла появится окно с предложением обновить связи, нажмите кнопку Нет. Для ручного обновления связи в меню Правка выберите ко­манду Связи, появится окно, как на рис. 13.3. В окне перечислены все файлы, данные из которых используются в активном файле «Полугодие».
   

Расположите его так, чтобы были видны данные файла «По­лугодие», выберите файл «1 квартал» и нажмите кнопку Обно­вить и проследите, как изменились данные файла «Полугодие». Аналогично выберите файл «2 квартал» и нажмите кнопку Обно­вить. Проследите, как вновь изменились данные файла «Полу­годие».

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

Задание 3. Консолидация данных для подведения итогов по таблицам данных сходной структуры.

Краткая справка. В Excel существует удобный инструмент для подведения итогов по таблицам данных сходной структуры, расположенных на разных листах или разных рабочих книгах, — Консолидация данных. При этом одна и та же операция (суммирова­ние, вычисление среднего и др.) выполняется по всем ячейкам нескольких прямоугольных таблиц, и все формулы Excel строит автоматически.

Порядок работы

1. 
   Откройте все три файла задания 13.2 и в файле «Полугодие» в колонке В удалите все численные значения данных. Установите курсор в ячейку ВЗ.
   
2. 
   Выполните команду Данные/Консолидация (рис. 13.4). В появив­шемся окне Консолидация выберите функцию — «Сумма».
   

В строке «Ссылка» сначала выделите в файле «1 квартал» диапа­зон ячеек ВЗ:В5 и нажмите кнопку Добавить, затем выделите в фай­ле «2 квартал» диапазон ячеек ВЗ:В5 и опять нажмите кнопку Добавить (см. рис. 13.4). В списке диапазонов будут находиться две области данных за первый и второй кварталы для консолидации. Далее нажмите кнопку ОК, произойдет консолидированное сум­мирование данных за первый и второй кварталы.

Вид таблиц после консолидации данных приведен на рис. 13.5.





Дополнительные задания

Задание 4. Консолидация данных для подведения итогов по таблицам неоднородной структуры.

Порядок работы

1. 
   Запустите редактор электронных таблиц Microsoft Excel и со­здайте новую электронную книгу. Наберите отчет по отделам за третий квартал по образцу (рис. 13.6). Произведите расчеты и со­храните файл с именем «3 квартал».
   
2. 
   Создайте новую электронную книгу. Наберите отчет по отде­лам за четвертый квартал по образцу (рис. 13.7). Произведите рас­четы и сохраните файл с именем «4 квартал».
   
3. 
   Создайте новую электронную книгу. Наберите название таб­лицы «Полугодовой отчет о продажах по отделам». Установите курсор на ячейку A3 и проведите консолидацию за третий и чет­вертый кварталы по заголовкам таблиц. Для этого выполните команду Данные/Консолидация. В появившемся окне консолида­ции данных сделайте ссылки на диапазон ячеек АЗ:Е6 файла «3 квартал» и A3:D6 файла «4 квартал» (рис. 13.8). Обратите вни­мание, что интервал ячеек включает имена столбцов и строк таб­лицы.
   

В окне Консолидация активизируйте опции (поставьте галочку):

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

После нажатия кнопки ОК произойдет консолидация (рис. 13.9). Сохраните все файлы в папке вашей группы.

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



Лабораторная работа