Создание связи рабочих листов с таблицами и построение диаграмм

Цель работы: Освоение принципов работы в EXCEL. Создание таблиц со связанными для расчета листами. Построение диаграмм.

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

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

Создадим с помощью EXCEL электронные таблицы, позволяющие рассчитать процент выполнения плана.

• запустите программу Microsoft Excel;

• создайте в рабочей книге шесть листов (по умолчанию их 3). Для этого во вкладке Главная в блоке Ячейки выберите пункт Вставить лист (см. рис. 3.1). Или на нижней панели щелкнуть на инструменте .

• озаглавьте листы: подойдите курсором мыши к вкладке листа, вызовите контекстное меню нажатием правой клавиши мыши и выберите в нем пункт ПЕРЕИМЕНОВАТЬ. Введите имя 1-го листа Данные, 2-го листа – Цех 1, 3-го – Цех 2, 4-го – Цех З, 5-го - Результаты, 6-го - График.

  Рисунок 3.1

• введите в лист1 - Данные:

в ячейку А1 - Таблица данных по предприятию;

А3- Наименование; СЗ - Количество;

А4 – К-во цехов; А5 - К-во человек;

В6 – 1 цех; В7 - 2 цех;

В8 – 3 цех; А9 - Норма на 1 чел.,

В10 – 1 цех; В11 -2 цех;

В12 – 3 цех; С4-3;

С6 – 20; С7 - 26;

С8 – 17; С10-105;

С11 – 84; С12-102

• объедините ячейки А и В строк: 4, 5, 6 и 9 и столбец А строки 6,7,8 и 10,11,12 используя инструмент .

• произведите обрамление таблицы так, чтобы Ваша таблица приняла следующий вид (см. Рис. 3.2).

Рисунок 3.2

• перейдите на лист Цех1 и введите: в А1 - Таблица показателей работы цеха № 1;

АЗ - Месяц,

А4 - Январь;

• далее произведите автоматическую вставку месяцев до декабря (подобный пример описан в лабораторной работе № 1 задание 8);

А16-Итого;

ВЗ - Кол-во дней;

СЗ - План;

D3- Факт;

В4- 20; В5 – 19;
В6 - 20; В7 – 21;
В8 - 23; В9 – 22;

В10-20; В11 – 21;

В12-22; В13 – 21;

В14-20; В15 – 23.

После ввода данных таблица будет иметь вид, представленный на Рис. 3.3.

Рисунок 3.3

• Сделайте копию диапазона А1:D16 таблицы и вставьте ее на листы Цех 2 и Цех З для дальнейшего заполнения. (выделите диапазон на листе 1 и выполните операции через Буфер обмена, КОПИРОВАТЬ и затем в нужном листе ВСТАВИТЬ).

Заполните таблицы данными: Цех 1, в ячейки

D4 - 59789; D5 - 40050;

D6-39450; D7-45120;

D8-49134; D9 - 39876,

D10-28979, D11- 45000;

D12-49876; D13-57889;

D14-41568; D15-56235

лист Цех2, в ячейки D4 – 42023; D5 – 40050;

D6 – 50356; D7 – 56123;

D8 - 48796; D9 - 59546,

D10-28987; D11 -36548;

D12-50265; D13-58499;

D14-55648; D15- 65578.

лист Цех3, в ячейки D4 - 34568; D5 - 36579;

D6-32156; D7-36895;

D8-41256; D9-38954;

D10 - 35468, D11-22456;

D12-35657, D13-38945;

D14-31236; D15-41204

• Перейдите на лист Цех 1 и внесите в ячейку С4 формулу расчета планового задания: =В4*Данные!$С$6*Данные!$С$10, (в ячейку С4 будет внесен результат произведения значений: ячейки В4 на значение ячейки С6 листа Данные и значения ячейки С10 листа Данные).

Примечание: символ $ - (знак доллара) признак абсолютной адресации. Она применяется для того, чтобы при копировании формулы ссылка на указанную ячейку не изменялась.

Данные! - имя неактивного рабочего листа откуда берутся данные: например Данные!$С$6 означает, что в формуле используется абсолютный адрес ячейки С6, взятой из листа с именем - Данные)

Ввод формул можно упростить, используя автозапись позиции текущей ячейки:

• активируйте ячейку С4;

• введите знак равенства и сделайте щелчок указателем в ячейке В4;

• введите знак умножения *;

• сделайте щелчок указателем мыши на названии листа Данные (обратите внимание, что цвет вкладки листа Данные стал ярким, а предыдущего листа Цех 1 не изменился. Это означает, что вносимые данные из листа Данные будут вносится в ячейку листа Цех 1);

• активируйте ячейку С6 и нажмите клавишу F4 (адрес ячейки приобретет признак абсолютной адресации);

• введите знак умножения *;

• активируйте ячейку С10 и нажмите клавишу F4;

• нажмите клавишу Enter. Программа представит результат расчета формулы.

• скопируйте введенную формулу на диапазон С5:С15;

• в ячейке В16 подсчитайте сумму значений диапазона ячеек В4:В15;

• в ячейках С16 и D16 подсчитайте суммы диапазонов С4:С15 и D4:D15;

• перейдите на лист Цех 2 и внесите в ячейку С4 формулу =В4*Данные!$С$7*Данные!$С$11;

• перейдите на лист Цех З и внесите в ячейку С4 формулу =В4*Данные!$С$8*Данные!$С$12.

Задание 2.

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

• Перейдите на лист Результаты и введите в ячейки:

В2 – Таблица показателей выполнения плана;

В5 – Месяц; D5- % выполнения;
В7 – Январь; С6 -1 цех;

В8 – Февраль; D6- 2 цех;
….. Е6 - 3 цех;

В18 – Декабрь; F6 - предприятие;

В19 - Итого

• внесите формулу в ячейку С7 для расчета процента выполнения фактических работ по отношению к плановым по 1-му цеху: =Цех 1!D4/Цех 1!С4;

• скопируйте введенную формулу в ячейки С8:С18;

• аналогичную формулу введите в ячейку D7: =Цех 2!D4/Цех 2!С4 и далее скопируйте ее в ячейки D8:D18;

• аналогичную формулу введите в ячейку Е7: =Цех 3!D4/Цех 3!С4 и далее скопируйте ее скопируйте в ячейки Е8:Е18.

После ввода формул результатам нужно придать процентный формат представления:

• выделите диапазон С7:F18;

• из контекстного меню выберите пункт ФОРМАТ ЯЧЕЕК,

• во вкладке ЧИСЛО, в окне ЧИСЛОВЫЕ ФОРМАТЫ установите ПРОЦЕНТНЫЙ формат и число десятичных знаков -1, ОК.

Вычислите среднее значение процента выполнения плана по всему предприятию в целом в каждом месяце:

• в ячейку F7 внесите формулу =СРЗНАЧ(С7:Е7), вычисляющую среднее значение указанного в скобках диапазона;

• скопируйте введенную формулу в ячейки F8:F18.

Вычислите среднее значение процента выполнения плана по цехам отдельно за год:

• в ячейку С19 внесите формулу =СРЗНАЧ(С7:С18), после чего скопируйте ее в ячейки D19, Е19, F19.

После проведенных расчетов таблица будет иметь следующий вид (см. рис. 3.4).

Рисунок 3.4 Результаты выполнения плана

Задание 3.

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

• перейдите на лист Цех 1;

• выделите диапазон таблицы А3:D16.

Рисунок 3.5

• во вкладке меню Главная выберите блок Стили Форматировать как таблицу и в списке предложенных форматов выберите понравившийся. После чего Ваша таблица будет иметь вид показанный в качестве примера на Рис. 3.5.

• аналогично оформите таблицы на листах Цех 2 и Цех З.

• дополнительно строку Итого выделите полужирным шрифтом.

• таблицу результатов выполнения плана оформите с выделением «проблемных» показателей. Для этого примените Условное форматирование в блоке Стили. Получим ее следующее изображение (см. рис.3.6).

Рисунок 3.6 Таблица после применения условного форматирования

Обратите внимание, что лучшие показатели работы имеют более темный оттенок избранного цвета, худшие - выделены красным цветом и более темного оттенка. Попробуйте различные варианты отображения показателей: гистограммы, цветовые шкалы или наборы значков.

Задание 4.

Постройте график выполнения плана по месяцам для каждого цеха по данным листа Результаты:

• выделите диапазон ячеек В6:F18, по вкладке Вставка блока Диаграммы укажите точечную с гладкими кривыми и маркерами. Получим следующее изображение диаграммы см. рис.3.7

Рисунок 3.7

Изображение диаграммы далеко до окончательного вида. Ее доработку проведем по шагам:

1. изменим габариты графика. Для этого щелчком выделим область диаграммы и растянем ее с помощью правого маркера до правой внешней границы(двойная рамка). Общие размеры диаграммы можно изменить с помощью маркеров внешней рамки;

2. удалим изображение сетки. Щелкнем по вкладке Макет и в блоке Оси Сетка отменим показ горизонтальных линий сетки.

3. изменим вид горизонтальной оси. Сделать щелчок на горизонтальной оси, вызываем контекстное меню и в меню Формат оси установим фиксированные значения максимального и минимального значения параметра месяц года: max - 12, min – 1, цена деления шкалы - 1;

4. дадим название осям и всей диаграмме. Для этого по вкладке Макет и в блоке Подписи воспользуемся элементами Название диаграммы и Название осей. Название  График выполнения плана. Вертикальная ось Y  % выполнения, горизонтальная Х  месяц года.

5. изменим изображение линий графиков. Вызовем контекстное меню на линии графика и в каждом графике изменим толщину линии (1пт). Толщину линии Предприятие оставить прежней(2.25 пт).

6. В формате области диаграммы примените градиентную заливку, переместите легенду диаграммы в нижнюю свободную область графика.

7. Переместите диаграмму на лист Диаграмма

После проведения операции оформления графики примут следующий вид см. рис. 3.8:

Рисунок 3.8 Внешний вид оформленной диаграммы

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

• Выделите два диапазона: сначала выделите В6:Е6 (данные для легенды), а затем удерживая нажатой клавишу Ctrl выделите В19:Е19;

• далее аналогично предыдущей работе, на листе Диаграмма создайте Круговую диаграмму. В ней должны быть указаны КАТЕГОРИИ (названия цехов) и ДОЛИ (относительный процент выполнения плана) деятельности цехов предприятия и название - Годовое выполнение плана

Полученная диаграмма должна иметь вид, представленный на Рис. 3.9.

Рисунок 3.9

• Сохраните Вашу работу на диске и покажите их преподавателю.

Контрольные вопросы:

1. Как размножить формулу на другие части электронной таблицы текущего листа?

2. Какие виды данных можно вводить в ячейку таблицы?

3. Как ввести формулу в ячейку?

4. Как осуществляется построение диаграмм на листе?

5. Как устанавливаются маркеры на графиках?

6. Как задать автоформат таблицы и чем он удобен?

7. Что означает следующая формула в ячейке =Результаты!$В$6:$F$19?

8. Как выполнить автоматический ввод месяцев?

9. Как установить процентный формат ячейки, с точностью до сотых?