Построение диаграмм и их форматирование в табличном процессоре

Практическая работа 4

Тема: Построение диаграмм и их форматирование в табличном процессоре

Цель работы: Закрепление навыков профессиональной работы в табличном процессоре MS Excel.

Пособие и оборудование:

1. IBM PC;
2. Инструкция и методические указания по выполнению практической работы.

Ход работы:

1. Запустил редактор электронных таблиц Microsoft Excel и создал новую электронную книгу;
2. Ввел заголовок таблицы «Финансовая сводка за неделю (тыс. р.)», начиная с ячейки А1;
3. Выделил ячейки на третьей строке A3:D3 и создал стиль для оформления. Для этого выполнил команду – формат/стиль и в открывшемся окне Стиль набрал имя стиля «Шапка таблиц» и нажал кнопку Изменить. В открывшемся окне на вкладке Выравнивание задал Переносить по словам и выбрал горизонтальное и вертикальное выравнивание — по центру, на вкладке Число указал формат — Текстовый. После этого нажал кнопку Добавить;
4. На третьей строке ввел названия колонок таблицы — «Дни недели», «Доход», «Расход», «Финансовый результат», далее заполнил таблицу исходными данными согласно заданию;
5. Произвел расчеты в графе «Финансовый результат», набрав в ячейке D4 формулу = В4-С4;
6. Для ячеек с результатом расчетов задал формат — «Денежный» с выделением отрицательных чисел красным цветом – формат/ячейки/вкладка число/формат — Денежный/отрицательные числа — красные, число десятичных знаков задал равное 2;
7. Рассчитал средние значения Дохода и Расхода, пользуясь мастером функций. Для этого установил курсор в соответствующей ячейке для расчета среднего значения (В11), запустил мастер функций – вставка/функция/категория — Статистические/СРЗНАЧ. В качестве первого числа выделил группу ячеек с данными для расчета среднего значения — В4:В10;
8. Аналогично рассчитал «Среднее значение» расхода;
9. В ячейке D13 выполнил расчет общего финансового результата. Для этого воспользовался функцией СУММ, в качестве первого числа выделил группу ячеек с данными для расчета суммы - D4:D10;
10. Провел форматирование заголовка таблицы. Для этого выделил интервал ячеек от А1 до D1, объединил их кнопкой на панели инструментов Объединить и поместить в центре. Задал начертание шрифта — полужирное;
11. Построил диаграмму линейчатого типа изменения финансовых результатов по дням недели с использованием мастера диаграмм. Для этого выделил интервал ячеек с данными финансового результата и выбрал команду – вставка/диаграмма. На первом шаге работы с мастером диаграмм выбрал тип диаграммы — линейчатая, на втором шаге на вкладке Ряд в окошке Подписи оси Х указал интервал ячеек с днями недели — А4:А10, далее ввел название диаграммы и подписи осей;
12. Произвел фильтрацию значений дохода, превышающих 4000 р. Для этого установил курсор внутри таблицы и воспользовался командой – данные/ фильтр/автофильтр, щелкнул по появившейся стрелке в заголовке поля в столбце Доход и выбрал команду для фильтрации – усовие;
13. В открывшемся окне Пользовательский автофильтр задал условие «Больше 4000»;
14. Сохранил созданную электронную книгу;

[pic 1]

Рисунок 1 - таблица финансовой сводки за неделю

1. Создал новую таблицу;
2. Заполнил таблицу приведенными из задания данными;
3. Произвел расчеты путем умножения значения из ячейки Цена на значение из ячейки Кол-во;
4. Для вычисления общей суммы (Всего) использовал функцию СУММ;
5. Выделил минимальную и максимальную суммы покупки с помощью функций МИН и МАКС соответственно;
6. По результатам расчета построил круговую диаграмму суммы продаж;

[pic 2]

Рисунок 2 - Таблица анализа продаж

1. Создал новую таблицу – ведомость учета брака;
2. Заполнил таблицу приведенными из задания данными;
3. В колонке «Процент брака» установил процентный формат чисел – формат/ячейки/вкладка число/формат — Процентный;
4. Рассчитал Сумму брака как Процент брака х Сумма зарплаты;
5. Выделил минимальную, максимальную и среднюю суммы брака, а также средний процент брака;
6. Произвел фильтрацию данных по условию процента брака <8 % - числовые фильтры/меньше;
7. Построил график отфильтрованных значений изменения суммы брака по месяцам;

[pic 3]

Рисунок 3 - Таблица ведомости учета брака

1. Создал таблицу анализа продаж и заполнил ее данными из задания;
2. Значения в столбце Всего рассчитал как Безналичные платежи + Наличные платежи;
3. Значения в столбце Выручка от продажи как Цена х Всего;
4. Выделил минимальную и максимальную продажи – количество и сумму;
5. Произвел фильтрацию по цене, превышающей 9000 р.;
6. Построил гистограмму отфильтрованных значений изменения выручки по видам продукции;

[pic 4]

Рисунок 4 - Таблица анализа продаж

1. Создал таблицу расчета заработной платы по образцу;
2. Ввел исходные данные — Табельный номер, ФИО и Оклад, % Премии, % Удержания;
3. Выделил отдельные ячейки для значений % Премии и % Удержания;
4. Произвел расчеты во всех столбцах таблицы. Для расчёта Премий и Удержаний использовал формулы – Оклад х % Премии и Всего начислено х % Удержания соответственно, при этом ячейки со значением % использовал в виде абсолютной адресации ($D$4 и $F$4);
5. Рассчитал итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки;
6. Дважды щелкнул мышью по ярлычку Лист 1 и присвоил ему новое имя – Зарплата октябрь;
7. Скопировал содержимое листа «Зарплата октябрь» на новый лист - правка/переместить/ скопировать лист;
8. Присвоил скопированному листу название «Зарплата ноябрь»;
9. Исправил название месяца в названии таблицы;
10. Изменил значение Премии на 32 %;
11. Между колонками «Премия» и «Всего начислено» вставил новую колонку «Доплата» - вставка/столбец и рассчитал значение доплаты по формуле Доплата = Оклад х % Доплаты, значение доплаты принял равным 5 %;
12. Изменил формулу для расчета значений колонки «Всего начислено»: Всего начислено = Оклад + Премия + Доплата;
13. Провел условное форматирование значений колонки «К выдаче»: установил формат вывода значений между 7000 и 10 000 — зеленым цветом шрифта, меньше 7000 — красным, больше или равно 10 000 — синим цветом шрифта – формат/условное форматирование;
14. Провел сортировку по фамилиям в алфавитном порядке по возрастанию: выделил фрагмент с 5 по 18 строки таблицы, выбрал меню – данные/сортировка, сортировать по — Столбец В;
15. Поставил к ячейке D3 комментарий «Премия пропорциональна окладу» - вставка/примечание;
16. Задал пароль на лист – сервис/защита/защитить лист, сделал его подтверждение;
17. Снял защиту листа – сервис/защита/снять защиту листа;
18. Сделал примечания к трём ячейкам;
19. Выполнил условное форматирование оклада и премии за ноябрь: до 2000 р. — желтым цветом заливки, от 2000 до 10 000 р. — зеленым цветом шрифта, свыше 10000 р. — малиновым цветом заливки, белым цветом шрифта;
20. Защитил лист зарплаты за октябрь от изменений – сервис/защита/защитить лист, убедился в неизменяемости данных и снял защиту со всех листов;
21. Построил круговую диаграмму начисленной суммы к выдаче всех сотрудников за ноябрь месяц;

[pic 5]

Рисунок 5 - Таблица начисления заработной платы за октябрь

[pic 6]

Рисунок 6 - Таблица начисления заработной платы за ноябрь