В Excel 2013 есть еще одна группа инструментов для наглядного представления изменений в данных, которые называются спарклайны (в предыдущей версии Excel они назывались инфокривые). Они очень похожи на гистограммы, однако рассчитаны на размещение внутри одной ячейки. Это очень удобно, чтобы показать, например, изменение какого-то одного показателя во времени. Все эти инструменты собраны в группе Спарклайны на вкладке Вставка.
Для вставки, например, спарклайн-графика нужно выделить ячейку для вставки и щелкнуть мышью на кнопке График в группе Спарклайны на вкладке Вставка. Появится окно, где нужно указать исходные данные для построения графика, то есть диапазон ячеек. Можно ввести адреса ячеек в поле Диапазон данных вручную или выделить нужные ячейки мышью (с нажатой левой кнопкой мыши выделить те ячейки, по значениям которых нужно строить график).
Щелкните мышью в этом окне на кнопке OK. График будет помещен в выбранную вами ячейку.
При выделении ячейки с графиком становится доступной вкладка Конструктор (в верхней части окна Excel 2013). С помощью инструментов этой панели можно менять внешний вид графика. На рисунке показан режим вывода на графике максимального и минимального значений. При изменении высоты или ширины ячейки, где расположен график, меняется и его масштаб.
Для удаления графика из ячейки щелкните правой кнопкой мыши на этой ячейке, в появившемся контекстном меню раскройте подменю Спарклайны и выберите пункт Очистить выбранные спарклайны.
Вопросы для самопроверки
1. Опишите этапы построения диаграммы.
2. Какие виды диаграмм вам известны?
3. Из каких элементов состоит диаграмма?
4. Как отредактировать на диаграмме:
а) название диаграммы;
б) названия осей Х и Y;
в) легенду;
г) изменить тип диаграммы;
д) добавить новые данные.
Практическая работа №19
Расчеты в Excel. Работа с процедурой Подбор параметра На этом занятии вы сможете узнать, как:
Цель работы: научиться строить модель решения задачи на подбор данных, зависящих от одного параметра; использовать режим Подбор параметра.
Технологию разработки и использование электронных таблиц рассмотрим на примере задачи о распределении зарплаты работникам больницы.
ЗАДАЧА
Заведующий больницей должен составить штатное расписание, т. е. определить, сколько сотрудников, на каких должностях и с каким окладом он должен принять на работу. При этом общий фонд заработной платы составляет 180 минимальных заработных плат (МЗ). Требуется получить такое штатное расписание, в котором предусмотрено оптимальное количество работников и достойная зарплата каждому.
ВВОД ИСХОДНЫХ ДАННЫХ
Пусть R — размер минимальной заработной платы. Тогда фонд зарплаты больницы равен 180 R.
ЗАДАНИЕ 1
Узнайте у преподавателя размер минимальной заработной плат по Единой Тарифной Сетке (к примеру, на 1 января 2015 года она составляла 5965 руб.). Запустите МS Ехсеl. Затем:
· в ячейку А15 введите текст Мин. зарплата:
· в ячейку В15 введите число, равное размеру минимальной заработной платы на текущий день (здесь и будет размещаться значение R);
· в ячейку А16 введите текст Кол-во зарплат:
· в ячейку В16 введите количество минимальных заработных плат, которые приходятся на больницу (по условию задачи их 180);
· в ячейку А17 введите текст Фонд зарплаты:
· в ячейку В17 введите размер фонда заработной платы, равный произведению количества минимальных заработных плат на размер минимальной заработной платы.
Полученное число в ячейке В17 и будет размером фонда зарплаты больницы.
ПОСТРОЕНИЕ МОДЕЛИ
Построим модель для решения этой задачи. Прежде всего, поясним, что является для нее исходными данными. Казалось бы, ничего, кроме общего фонда заработной платы, в условии не дано. Как же можно решить задачу, располагая только этими данными?
На самом деле заведующему больницей (на то он и заведующий) известно гораздо больше. Он знает, что для нормальной работы больницы обязательно нужны:
5 – 7 | — | санитарок | — | зав. отделениями | — | главврач | ||
8 – 12 | — | медсестер | — | зав. аптекой | — | зав. больницей | ||
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(а2 m + b2) +... + n8(а8 m + b8) = 180R,
где n1 — количество санитарок, n2 — медсестер и т. д. В этом уравнении нам известны а1,..., а8, b1..., b8 и неизвестны m, n1..., n8. Решить такое уравнение известными вам методами невозможно, да у него и нет единственного решения. Остается решать его путем подбора. Взяв за первоначальное решение какие-нибудь приемлемые значения неизвестных, подсчитаем необходимую сумму. Если выяснится, что эта сумма соответствует фонду зарплаты — вам повезло. Если нет — берите другие значения. Если вы превысили фонд зарплаты, вам придется либо снизить оклад, санитарки (а вместе с ним и другие оклады), либо отказаться от услуг какого-либо работника. Принимайте решение и снова пересчитывайте сумму и т. д., пока не придете к нужному результату.
Ясно, что проделывать такую работу вручную, да еще достаточно часто, весьма трудоемкое и неприятное занятие. В этом случае вам и поможет табличный процессор.
ЗАДАНИЕ 2
Нарисуйте шапку таблицы и оформите.
После этого в ячейки А3-А10 введите название должностей в следующем порядке: санитарка, медсестра, врач, зав. отделением, аптекой, завхоз, главврач, зав. больницей. В ячейки В3-В10 — соответствующие коэффициенты а, в С3-С10 — коэффициенты b (вместо R используйте имя ячейки В15).
РУЧНОЙ ПОДБОР ДАННЫХ
ЗАДАНИЕ 3
1) В ячейку DЗ введите зарплату санитарки (сначала — минимальную заработную плату — пусть будет 5965 рублей). В ячейку D4 введите формулу вычисления зарплаты медсестры: =b4*d13+с4. В ячейку D5 — формулу, с помощью которой вычисляется зарплата врача: =b5*dЗ+с5 и т. д.
2) Затем займитесь столбцом Е. В этот столбец введите количество сотрудников каждой специальности. В столбец F — формулы, позволяющие рассчитать общее количество денег на каждую специальность (например, в ячейку F3 следует ввести произведение зарплаты санитарки на количество санитарок: d3*е3 и т. д.).
3) В ячейку С15 введите слово Средний:, в ячейку D15 — формулу для вычисления среднего значения зарплаты (если забыли, как это делается, посмотрите Справку).
4) В ячейку Е15 — слово Итого, в ячейку F15 — формулу для расчета общей суммы распределенной заработной платы.
5) В ячейку Е16 введите слово Остаток, а в ячейке F16 задайте формулу разности между суммой, которая имеется в банке (если помните, ее вы вычисляли в ячейке В17), и распределенной заработной платой (F15).
Рассмотрите построенную таблицу. Обратите внимание, что теперь если изменить какое-нибудь исходное значение (т. е. то, где не заданы формулы), таблица будет автоматически пересчитываться. Давайте этим воспользуемся.
ЗАДАНИЕ 4
Теперь осталась самая малость: подобрать зарплату всем сотрудникам так, чтобы сумма фонда зарплаты полностью совпадала с заданной в В17. Подбирать можно лишь изменяя зарплату санитарки и количество сотрудников (в заданном диапазоне, разумеется). Разница между суммами не должна быть больше 10 рублей,
В случае, если зарплата персонала окажется слишком маленькой, вам дается право получить от страховой компании до 300 минимальных зарплат.
Проверьте, будет ли сохранятся распределение, если минимальную зарплату сделают, например, в 10000 рублей.
ЗАДАНИЕ 5
Сохраните окончательный вариант таблицы. Она Вам пригодится для дальнейшей работы.