Файл: Лабораторная работа 1. Операционная система Windows. Основные принципы работы. Цель работы.doc

Пакет анализа.

1. Используя программу Поиск решения решить следующую задачу.

Составить штатное расписание хозрасчетной больницы, т.е. определить сколько сотрудников, на каких должностях и с каким окладом нужно принять на работу. Общий месячный фонд заработной платы составляет $10000. При решении принять во внимание, что для нормальной работы больницы нужно 5-7 санитарок, 8-10 медсестер, 10-12 врачей, 1 зав. аптекой, 3 зав. отделениями, 1 глав. врач, 1 завхоз, 1 зав. больницей.

За основу берется оклад санитарки, а оклады всех остальных сотрудников вычисляются исходя из него: a*x+b, где x- оклад санитарки, a и b - коэффициенты, которые для каждой должности определяются решением совета трудового коллектива. Оклад санитарки должен быть не менее $50.

Для решения задачи создать следующую таблицу на рабочем листе:


Таблица 11. – Штатное расписание

Должность	Коэф.А	Коэф.В	З/пл	Кол-во чел.	Сум. З/пл	Исходная З/пл санитарки
Санитарка	1	0		6
Медсестра	1,5	0		9		50
Врач	3	0		11		Мес. Фонд З/пл
Зав. отдел.	3	30		3
Зав. аптекой	2	0		1
Завхоз	1,5	40		1		10000
Глав. врач	4	0		1
Зав. больницей	4	20		1
				Итого:

---

Перед тем, как запускать процедуру Поиск решения, надо заполнить все графы таблицы! В качестве исходного значения оклада санитарки ввести – 50 (в столбце G). Вычислить значения окладов всех сотрудников (включая санитарку) по формуле (в столбце С). Вычислить суммы зарплат по должностям и итоговую сумму по больнице.

При заполнении диалогового окна Поиска решения учесть следующее:

• 
  Необходимо, чтобы итоговая з/п по больнице совпадала с месячным фондом з/п. Таким образом, целевой ячейкой будет являться ячейка, содержащая итоговую з/п. Устанавливаемое значение целевой ячейки равно 10000.
  
• 
  Изменяемыми ячейками (т.е. ячейками результата) являются ячейки, содержащие исходную з/п санитарки и количество санитарок, медсестер и врачей.
  
• 
  В качестве ограничений нужно указать допустимый диапазон варьирования изменяемых ячеек. Например, количество санитарок по условию задачи должно быть больше 5, но меньше 7, а также целым. Ограничения заполняются посредством кнопки Добавить, с помощью которой выводится дополнительное окно, в котором нужно указать в левой части адрес изменяемой ячейки (например, адрес ячейки, содержащей количество санитарок), выбрать нужный знак (например, - <), а в правой части ввести предельно допустимое значение (например, - 5). Аналогично заполняются все другие ограничения.
  

2. Используя Поиск решения решить систему линейных уравнений (см. п. 4 в разделе Элементы линейной алгебры). Для этого:

• 
  В ячейки А1:А4 ввести соответственно тексты «х1=», «х2=», «х3=», «х4=».
  
• 
  В ячейки B1:B4 ввести начальные значения неизвестных (любые).
  
• 
  В ячейки А6:А9 ввести формулы, соответствующие левым частям уравнений ( в качестве неизвестных указывать ссылки на ячейки, содержащие начальные значения неизвестных).
  
• 
  В ячейки B6:B9 ввести текст «=».
  
• 
  В ячейки С6:С9 ввести столбец свободных членов.
  
• 
  В ячейку А11 ввести формулу = x₁*x₂*x₃*x₄ . (Внимание! В качестве x₁,x₂,x₃,x₄ в формуле указываются адреса ячеек, содержащих начальные значения неизвестных), которая будет являться формальной функцией цели.
  
• 
  Запустить программу Поиск решения. Задать направление функции цели (max или min). Решение системы обеспечивается заданием жестких ограничений. В качестве ограничений использовать равенство левой и правой части каждого уравнения системы.
  

---

3. Используя Поиск решения, подберите значения срока вклада и процентной ставки, при которых сумма вклада будет составлять 8000 рублей, учитывая, что величина процентной ставки не может превышать 7% (по данным задачи 3 см. Табл.10). Для этого скопировать исходную таблицу на другой лист.

---

Лабораторная работа №17. Использование сводных таблиц

Цель работы: освоение приемов создания и редактирования сводных таблиц. Работа с надстройкой Мастер сводных таблиц.

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

2. Переименовать ярлычки листов – Квартал1, Квартал2, Квартал3, Квартал4.

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

4. Перейти на лист Квартал1. Ввести следующую шапку для таблицы:

• 
  В ячейки В2:D2 ввести последовательно названия супермаркетов - «Европа», «Эдельвейс», «Балтика».
  
• 
  В ячейки А3:А6 ввести по порядку – «Продовольственные товары», «Хозяйственные товары», «Бытовая техника», «Косметика и парфюмерия».
  

5. Выделить диапазон будущей таблицы – А2:D6, вызвать команду Автоформат меню Формат, в предлагаемом списке выбрать по желанию любой вид таблицы, например, Цветной 2.

6. Расклеить листы. Для этого необходимо щелкнуть правой кнопкой мыши на ярлычке листа и выбрать команду Разгруппировать.

7. Заполнить готовые шаблоны таблиц на листах исходными числовыми данными.

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

• 
  Вызвать Мастер сводных таблиц с помощью меню Данные и команды Сводная таблица.
  
• 
  В диалоговом окне Шаг 1 из 3 в разделе Создать таблицу на основе данных.. активизировать переключатель В нескольких диапазонах консолидации, так как данные расположены на различных листах рабочей книги. В разделе Вид создаваемого отчета выбрать Сводная диаграмма. В этом случае будет автоматически сформирована не только сводная таблица, но и построена сводная диаграмма по данным сводной таблицы. Нажать кнопку Далее.
  
• 
  В диалоговом окне Шаг 2а из 3 выбрать переключатель Создать одно поле страницы, так как информация на листах рабочей книги отличается только одним параметром – Квартал. Перейти к следующему шагу.
  
• 
  В диалоговом окне Шаг 2б из 3 указать диапазон данных таблицы. Для этого нужно щелкнуть мышкой в поле Диапазон, перейти на лист Квартал 1, выделить ячейки А2:D6, нажать кнопку Добавить. Затем перейти на лист Квартал 2 и т.д. Внимание! В поле Список диапазонов введенные адреса с различных листов располагаются по порядку ввода (Квартал 1, Квартал 2 и т.д.), в соответствии с которым затем заполняются строки сводной таблицы. Если ярлычки листов имеют только символьные наименования (например, Июнь, Июль, Август), то адреса диапазонов в списке будут расположены в алфавитном порядке, а не в порядке их ввода. Следовательно, и строки в сводной таблицы будут располагаться также (например, Август, Июль, Июнь).
  

---

Таблица 12. – Объем продаж товаров в крупнейших супермаркетах города, млн. руб.

	«Европа»	«Эдельвейс»	«Балтика»
	Квартал 1
Прод. Товары	3500	15000	2500
Хоз. товары	1200	700	1345
Быт. техника	34000	1300	25000
Косметика и парф.	520	278	900
	Квартал 2
Прод. Товары	12000	32000	2400
Хоз. товары	3000	1200	780
Быт. техника	45000	1100	3500
Косметика и парф.	450	270	121
	Квартал 3
Прод. Товары	2300	12000	2450
Хоз. товары	890	1500	3000
Быт. техника	23000	17000	32000
Косметика и парф.	560	590	660
	Квартал 4
Прод. Товары	19000	15000	6800
Хоз. товары	1890	1200	4500
Быт. техника	46000	10000	19000
Косметика и парфюмерия	1890	1200	956

• 
  Перейти в диалоговое окно Шаг 3 из 3 и задать поле вывода сводной таблицы – Новый рабочий лист. В этом же окне вызвать кнопку Макет для организации информации в сводной таблице. По умолчанию установлен следующий макет сводной таблицы – в столбцах выводятся наименования супермаркетов, в строках – наименование товара. В этом случае суммирование объема продаж будет производиться по кварталам. По условию нужно получить сводную таблицу по общим объемам продаж по видам товаров. Для изменения алгоритма суммирования в макете таблицы нужно поменять местами поля Строка и Страница. Для этого нужно захватить мышкой нужный элемент из ряда кнопок, расположенных справа, а затем нанести его на нужное место - область строки или столбца, или страницы. После создания макета можно выбрать кнопку Параметры и задать заголовок сводной таблицы.
  

---