СОЗДАние ГРАФИКов-СПАРКЛАЙНов В EXCEL 2013




В 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) + n22 m + b2) +... + n88 m + b8) = 180R,

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

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

ЗАДАНИЕ 2

Нарисуйте шапку таблицы и оформите.

После этого в ячейки А3-А10 введите название должностей в следующем порядке: санитарка, медсестра, врач, зав. отделением, аптекой, завхоз, главврач, зав. больницей. В ячейки В3-В10 — соответствующие коэффициенты а, в С3-С10 — коэффициенты b (вместо R используйте имя ячейки В15).

РУЧНОЙ ПОДБОР ДАННЫХ

ЗАДАНИЕ 3

1) В ячейку введите зарплату санитарки (сначала — минимальную заработную плату — пусть будет 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

Сохраните окончательный вариант таблицы. Она Вам пригодится для дальнейшей работы.



Поделиться:




Поиск по сайту

©2015-2024 poisk-ru.ru
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2017-03-31 Нарушение авторских прав и Нарушение персональных данных


Поиск по сайту: