Самостоятельная лабораторная работа

ТЕХНОЛОГИЯ ИСПОЛЬЗОВАНИЯ ЭЛЕКТРОННОЙ ТАБЛИЦЫ EXCEL

1. Постановка задачи

Технологию разработки и эксплуатации электронной таблицы (ЭТ) рассмотрим на примере задачи о распределении зарплаты работникам больницы.

ЗАДАЧА

Заведующий больницей должен составить штатное расписание, т.е. определить, сколько сотрудников, на каких должностях и с каким окладом он должен принять на работу. При этом общий фонд заработной платы составляет 180 минимальных заработных плат. Требуется получить такое штатное расписание, в котором предусмотрено оптимальное количество работников и достойная зарплата каждому.

Пусть R - размер минимальной заработной платы.

Тогда фонд зарплаты больницы равен 80 • R.

Задание 1. Подготовка таблицы

Узнайте у преподавателя размер минимальной заработной платы по Единой Тарифной Сетке (к примеру, 4000 руб.).

Выполните следующие действия:

-  запустите EXCEL;

-  в клетку А15 введите текст: “Миним. зарплата:”;

-  в клетку С15 введите число, равное размеру минимальной заработной платы (здесь и будет размещаться значение R);

-  в клетку А16 введите текст: “Фонд зарплаты:”;

-  в клетку С16 введите произведение: =80*С15. Полученное число в клетке С16 и будет размером фонда зарплаты больницы.

Построим модель для решения этой задачи. Прежде всего, поясним, что является для нее исходными данными. Казалось бы, ничего, кроме общего фонда заработной платы, в условии не дано. Как же можно решить задачу, располагая только этими данными?

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

1 - завбольницей 3 завотделениями,

1 - главврач, 5-7 санитарок,

1 - завхоз, 8-12 медсестер,

1 - зав аптекой, 6-8 врачей.

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

Итак, заведующий принимает для себя такую модель задачи:

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

Если обозначить оклад санитарки через m, то все остальные оклады будут считаться по формуле: am+b,

где коэффициенты а и b для каждой должности определяются заведующим.

Например, заведующий решил, что:

-  зарплата санитарки выступает в качестве точки отсчета (а=1, b=0);

-  медсестра должна получать в 1,5 раза больше санитарки (т. е. а=1.5, b=0);

-  врач - в 3 раза больше санитарки (а=3, b=0);

-  завотделением - на 1 минимальную зарплату больше, чем врач (а=3, b= R);

-  заваптекой - в 2 раза больше санитарки (а=2, b=0);

-  завхоз - на половину минимальной заработной платы больше, чем медсестра

(а=1.5, b= 0.5*R);

-  главврач - в 4 раза больше санитарки (а=4, b=0);

-  завбольницей - на одну минимальную зарплату больше главврача (а=4, b=R).

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

n1(a1 m+b1)+n2(a2 m+b2)+...+n8(a8 m+b8)= 180 *R,

где n1 - количество санитарок, n2 - медсестер и т. д.

В этом уравнении нам известны значения a1,...,a8, bb...,b8 и неизвестны m, n1,...,n8. Решить такое уравнение известными Вам методами невозможно, да и у него нет единственного решения. Остается решать его путем подбора. Взяв за первоначальное решение какие-нибудь приемлемые значения неизвестных, подсчитаем необходимую сумму. Если выяснится, что эта сумма соответствует фонду зарплаты - Вам повезло. Если нет - берите другие значения. Если Вы превысили фонд зарплаты, Вам придется либо снизить оклад санитарки (а вместе с ним и другие оклады), либо отказаться от услуг какого-либо работника. Принимайте решение и снова пересчитывайте сумму и т. д., пока не придете к нужному результату.

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

2. Пример использования электронной таблицы

Постарайтесь точно выполнить все инструкции, предложенные далее.

Задание 2. Подготовка таблицы (продолжение)

Ÿ  Сначала напишите заголовок таблицы. Для этого установите курсор в клетку D1 и введите туда следующий текст: “Зарплата работников больницы”.

Ÿ  Оформите шапку таблицы следующим образом:

-  в клетку А2 запишите текст “Должность”

(в этой колонке будете размещать названия должностей),

-  в клетку С2 – “Коэффициент-1”

(в этой колонке будете размещать значения коэффициента а),

-  в клетку D2 –“Коэффициент-2” (в этой колонке - значения коэффициента b),

-  в клетку Е2 – “Оклад”,

-  в клетку F2 – “Кол-во”,

-  в клетку G2 – “Сумма”.

Ÿ  Сохраните результаты работы на диске.

Задание 3. Занесение исходных данных

После этого:

-  в клетки А4-А11 введите название должностей в следующем порядке: санитарка, медсестра, врач, завотделением, заваптекой, завхоз, главврач, завболъницей;

-  в клетки С4-С11 - соответствующие коэффициенты а (приведены выше);

-  в клетки D4-D11 - коэффициенты b (вместо R используйте имя клетки С15);

-  в клетку Е4 введите зарплату санитарки (сначала - минимальную заработную плату);

-  в клетку Е5 введите формулу: =C5*E4+D5 (формула, по которой рассчитывается зарплата медсестры).

-  в клетку Е6 – формулу =C6*E4+D6 и т. д.

Напоминаем, что имена клеток начинаются с АНГЛИЙСКИХ букв!

При вводе формул в клетке будет появляться не формула,

а уже вычисленное значение зарплаты!

-  теперь займемся столбцом F. В этот столбец введите количество сотрудников для каждой должности (специальности);

-  в столбец G - формулы, позволяющие рассчитать общее количество денег на каждую специальность (например, в клетку G4: =F4*E4 и т. д.);

-  в клетку D13 введите текст: “Среднее:” ;

-  в клетку Е13 - формулу для вычисления среднего значения зарплаты;

-  в клетку F13 - текст “Итого:”;

-  в клетку G13 - формулу для расчета общей суммы фонда заработной платы: СУММ(G4:G11).

Примечание

Диапазон (интервал) клеток должен быть указан с помощью имен начальной и последней клеток, разделенных двоеточием. Например, если требуется указать диапазон клеток от Е4 до Е11. используется обозначение Е4:Е11. Запись СРЗНАЧ(E4:E11) означает, что требуется найти среднее значение чисел, размещенных в клетках с Е4 до E11;. СУММ(G4:G11) - сумма чисел в клетках с G4 по G11.

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

Задание 4. Обработка результатов

Ÿ  Подберите зарплату всем сотрудникам так, чтобы сумма фонда зарплаты ПОЛНОСТЬЮ совпадала с заданным значением в клетке С16. При этом старайтесь изменять только зарплату санитарки (лучше всего, если в клетке с зарплатой санитарки сначала будет стоять С15, а затем С15+n, где n- изменяемая часть зарплаты) и количество сотрудников (в заданном диапазоне, разумеется).

Задание 5. Оформление таблицы

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

Ÿ  Обязательно запишите окончательный вариант таблицы в файл: он Вам пригодится при выполнении следующего задания.

3. Графическая обработка результатов в EXCEL

С помощью Мастера построения диаграмм выполните графическую обработку результатов.

Для построения графика (диаграммы) следует воспользоваться Мастером построения диаграмм, кнопка которого расположена на панели инструментов.

Задание 6. Построение диаграммы

Ÿ  Постройте на одной диаграмме два набора данных:

-  “Зарплата каждого работника”;

-  “Общая зарплата всех работников для каждой должности”.

Ÿ  Выполните оформление диаграммы:

-  общий заголовок (“Зарплата работников больницы”);

-  заголовки и разметки осей (“Сумма (Руб)”, ”Должность”);

-  фон диаграммы (цветовая/текстурная подложка или графическое изображение);

-  оформите легенду диаграммы.

Ÿ  Сохраните результаты всей работы, чтобы продемонстрировать ее преподавателю.