Тема: комплексное использование возможностей MS Excel для создания документов

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

Тема; ПОСТРОЕНИЕ И ФОРМАТИРОВАНИЕ ДИАГРАММ

Задание 2.1. Создать таблицу «Сводка о выполнении плана». Построить график и диаграмму по результатам расчетов.

 

Исходные данные представлены на рис.1.

 

	A	B	C	D
	Сводка о выполнении плана
	Наименование	План выпуска	Фактически выпушено	% выполнения плана
	Филиал № 1
	Филиал № 2
	Филиал № 3
	Филиал № 4
	Филиал № 5
	Филиал № 6
	Филиал № 7
	Филиал № 8
	Всего

Рис.1.Исходные данные для задания 2.1

 

При необходимости добавляются новые листы электронной книги командой Вставить лист (Shift+F11).

Переименуйте ярлычок Лист 4, присвоив ему имя «Выполнение плана».

 

Расчетные формулы:

% выполнения плана = Фактически выпущено / План выпуска;

Всего = сумма значений по каждой колонке.

 

Сохраните созданную электронную книгу в своей папке с именем «Расчеты» (кнопка «Office» /Сохранить как)

 

Дополнительное задание

Задание 2.2 Создать таблицу «Расчет заработной платы». Построить гистограмму и круговую диаграмму по результатам расчетов.

Данные для построения диаграммы выделяйте при нажатой клавише (Ctrl).

Исходные данные представлены на рис. 2.

 

	A	B	C	D	E	F
	РАСЧЕТ ЗАРАБОТНОЙ ПЛАТЫ
						ЗА ЯНВАРЬ
	ФИО	Оклад	Премия 20%	Итого начислено	Подоходный налог 13%	Итого к выдаче
	Баранова Л.В.
	Васильев С.Н.
	Петрова А.Г.
	Петухова О.С.
	Савин С.Н.

Рис.2. Исходные данные для задания 2.2

 

Расчетные формулы:

 

Премия = оклад × 0,2

Итого начислено = оклад + премия

Подоходный налог = итого начислено × 0,13

Итого к выдаче = итого начислено – подоходный налог.

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

Тема: ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ В РАСЧЕТАХ MS EXCEL

 

Цель занятия. Изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах MS EXCEL

 

Задание 3.1 Создать таблицу динамики розничных цен и произвести расчет средних значений

 

Исходные данные представлены на рис. 1.

 

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск/ Программы/ Microsoft office / Microsoft Excel)

2. Откройте файл «Расчеты», созданный в практических работах 1…2 (кнопка «Office» /Открыть).

3. Переименуйте ярлычок Лист 5, присвоив ему имя «Динамика цен».

4. На листе «динамика цен» создайте таблицу по образцу, как на рис..1.

5. Произведите расчет изменения цены в колонке «Е» по формуле

Изменение цены = Цена на 01.06.2008/Цена на 01.04.2008.

Не забудьте задать процентный формат чисел в колонке «Е» (Формат ячейки/Число/Процентный).

 

	А	В	С	D	E
	Динамика розничных цен на молоко цельное разливное, руб./литр
	Регионы Российской Федерации	На 01.04.2008г	На 01.05.2008г	На 01.06.2008г	изменение цены, в % (01.06.2008 к 01.04.2008)
	Поволжский р-н
	Республика Калмыкия	7,36	7,36	6,29	?
	Республика Татарстан	3,05	3,05	3,05	?
	Астраханская обл.	8,00	7,85	7,75	?
	Волгоградская обл.	12,08	12,12	11,29	?
	Пензенская обл.	8,68	8,75	9,08	?
	Самарская обл.	7,96	7,96	7,96	?
	Саратовская обл.	11,40	11,10	11,08	?
	Ульяновская обл.	5,26	5,26	5,26	?
	среднее значение по району	?	?	?

Рис.1. Исходные данные для задания 3.1

6. Рассчитайте средние значения по колонкам, пользуясь мастером функций f_x. Функция СРЗНАЧ находится в разделе «Статистические». Для расчета функции среднего значения установите курсор в соответствующей ячейке для расчета среднего значения (В14), запустите мастер функций (кнопкой Вставка функции f_x или меню Формулы/ Вставка функции) и на первом шаге мастера выберите функцию СРЗНАЧ (категория Статистические / СРЗНАЧ).

После нажатия на кнопку ОК откроется окно для выбора диапазона данных для вычисления заданной функции. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В6:В13 и нажмите кнопку ОК. В ячейке В14 появится среднее значение данных колонки «В».

Аналогично рассчитайте средние значения в других колонках.

7. В ячейке А2 задайте функцию СЕГОДНЯ, отображающую текущую дату, установленную в компьютере (Формулы/ /Дата и Время/Сегодня).

8. Выполните текущее сохранение файла ( кнопка «Office» /Сохранить).

 

Задание 3.2. Создать таблицу изменения количества рабочих дней наемных работников и произвести расчет средних значений. Построить график по данным таблицы.

 

Исходные данные представлены на рис. 2

 

Порядок работы.

1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию. Объединение выделенных ячеек произведите кнопкой панели инструментом Объединить и поместить в центре или командой меню (Формат ячейки/вкладка Выравнивание/ отображение- Объединение ячеек).

 

Рис.2. Исходные данные для задания 3.2

Краткая справка. Изменение направления текста в ячейках производится путем поворота текста на 90^о в зоне Ориентация окна Формат ячеек, вызываемого меню Главная/ кнопка/Ориентация - поворот надписи на 90^о.

2. Произвести расчет средних значений по строкам и столбцам с использованием функции СРЗНАЧ.

3. Построить график изменения количества рабочих дней по годам и странам. Подписи оси «Х» задайте с помощью контекстного меню Выбоать данные / Подписи горизонтальной оси (категории)/ Изменить выделить диапазон ячеек и нажать ОК.

4. После построения графика произвести форматирование вертикальной оси, задав минимальное значение 1500, максимальное значение 2500, цену деления 100. Для форматирования оси выполните щелчок правой кнопкой мыши на вертикальной оси, выберите команду Формат оси/Параметры оси и задайте соответствующие параметры оси.

 

Задание 3.3. Применение функции ЕСЛИ при проверке условий. Создать таблицу расчета премии за экономию горючесмазочных материалов (ГСМ).

 

Исходные данные представлены на рис. 3.

Порядок работы

1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию.

2. Произвести расчет Премии (25% от базовой ставки) по формуле

 

Премии = Базовая ставка × 0,25 при условии, что План расходования ГСМ › Фактически израсходовано ГСМ.

 

	А	В	С	D	E	F
	Расчет премии за экономию горючесмазочных материалов (ГСМ)
	Табельный №	Ф.И.О.	План расходования ГСМ (литр.)	Фактически израсходовано ГСМ (литр.)	Базовая Ставка (руб.)	Премия (25% от базовой ставки),если План > Фактич. Израсходов.
		Сергеев А.В.			2000,00
		Петров С.П.			2000,00
		Сидоров А.О.			2000,00
		Кремнев В.В.			1000,00
		Андреев П.Р.			1000,00
		Васильев П.Л.			2000,00
		Гордеев А.В.			2000,00
		Серов В.В.			2000,00
		Рогов Р.Р.			1000,00
		Марков А.Л.			2000,00
		Диев Д.Ж.			2000,00
		Жданов П.О.			2000,00

Рис. 3.Исходные данные для задания 3.3

 

Для проверки условия используйте функцию ЕСЛИ.

Для расчета Премии установите курсор в Ячейке F3, запустите мастер функции (кнопкой Вставка функции f_x или меню Формулы/ Логические /ЕСЛИ).

Задайте условие и параметры функции ЕСЛИ.

В первой строке «Логическое выражение» задайте условие С3 > D3.

Во второй строке задайте формулу расчета премии, если условие выполняется Е3*0,25.

В третьей строке задайте значение 0, поскольку в этом случае (невыполнение условия) премия не начисляется.

3. Произведите сортировку по столбцу фактического расходования ГСМ по возрастанию. Для сортировки установите курсор на любую ячейку таблицы, выберите в меню Данные команду Сортировка, задайте сортировку по столбцу «Фактически израсходовано ГСМ».

4. Конечный вид расчетной таблицы начисления премии приведен на рис. 4.

5. Выполните текущее сохранение файла «Расчеты» (кнопка «Office» /Сохранить)

 

Расчет премии за экономию горючесмазочных материалов (ГСМ)
Табельный №	Ф.И.О.	План расходования ГСМ (литр.)	Фактически израсходовано ГСМ (литр.)	Базовая Ставка (руб.)	Премия
	Андреев П.Р.			1 000,00р.
	Кремнев В.В.			1 000,00р.	250,00 р.
	Рогов Р.Р.			1 000,00р.	250,00 р.
	Жданов П.О.			2 000,00р.	500,00 р.
	Диев Д.Ж.			2 000,00р.	500,00 р.
	Сергеев А.В.			2 000,00р.	500,00 р.
	Васильев П.Л.			2 000,00р.
	Петров С.П.			2 000,00р.
	Гордеев А.В.			2 000,00р.
	Марков А.Л.			2 000,00р.	500,00 р.
	Сидоров А.О.			2 000,00р.
	Серов В.В.			2 000,00р.

Конечный вид задания 3.3

 

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

Тема: ОТНОСИТЕЛЬНАЯ И АБСОЛЮТНАЯ АДРЕСАЦИЯ MS EXCEL

 

Цель занятия. Изучение информационной технологии организации расчетов с абсолютной адресацией данных (при работе с константами) в таблицах MS EXCEL.

Задание 4.1 Создать таблицу расчета рентабельности продукции. Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены на рис.1.

 

РАСЧЕТ РЕНТАБЕЛЬНОСТИ ПРОДУКЦИИ
	Отпускная цена одного изделия:	57,00 р.
№ п/п	Показатель	Квартал 1	Квартал 2	Квартал 3
	Количество выпущенных изделий,шт.	1750,00	2150,00	2415,00
	Себестоимость одного изделия, руб.	49,50	47,30	48,60
	Выпуск продукции, руб.
	Себестоимость выпускаемой продукции, руб
	Прибыль от реализации продукции, руб.
	Рентабельность продукции, %

Рис.1 Исходные данные для задания 4.1

 

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск / Программы / Microsoft Office / MS Excel).

2. Откройте файл «Расчеты», созданный в Практических работах № 1… 3 (кнопка «Office» /Открыть).

3. На новом листе электронной книги «Расчеты» создайте таблицу констант (отпускная цена одного изделия) и основную расчетную таблицу по заданию.

4. Введите исходные данные. При вводе номеров в колонку «А» (числа 1, 2, 3 и т.д.) используйте прием автозаполнения ряда чисел. Для этого наберите два первых числа ряда (числа 1 и 2), выделите их мышкой и подведите курсор к правому нижнему углу выделенных ячеек до изменения вида курсора на черный крестик. Прихватите мышью маркер автозаполнения и потяните его вниз до нужного значения – произойдет создание ряда натуральных чисел (арифметическая прогрессия).

5. Выделите цветом ячейку со значением константы – отпускной цены 57,00 р.

 

Р е к о м е н д а ц и и и. Для удобства работы и формирования навыков работы с абсолютным видом адресации, рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул окрашенная ячейка (т.е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символа $ с клавиатуры или нажатием клавиши [F4]).

6. Произведите расчеты во всех строках таблицы.

Формулы для расчета:

Выпуск продукции = Количество выпущенных изделий × Отпускная цена одного изделия, в ячейку С7 введите формулу =С5*$E$2 (ячейка Е2 задана в виде абсолютной адресации);

Себестоимость выпускаемой продукции = Количество выпущенных изделий × Себестоимость одного изделия, в ячейку С8 введите формулу = С5*С6;

Прибыль от реализации продукции = Выпуск продукции – себестоимость выпускаемой продукции, в ячейку С9 введите формулу = С7-С8;

Рентабельность продукции = Прибыль от реализации продукции/Себестоимость выпускаемой продукции, в ячейку С10 введите формулу = С9/С8.

На строку расчёта рентабельности продукции наложите Процентный формат чисел. Остальные расчеты производите в Денежном формате.

Формулы из колонки «D» и «E».

7. выполните текущее сохранение файла(кнопка «Office» /Сохранить).

 

Задание 4.2. Создать таблицу расчета дохода сотрудников организации. Константы вводить в расчетные формулы в виде абсолютной адресации.

 

Исходные данные представлены на рис. 2.

Порядок работы.

1. На очередном свободном листе электронной книги «расчеты» создайте таблицу по заданию.

2. Введите значения констант и исходные данные. Форматы данных (денежный или процентный) задайте по образцу задания.

3. Произведите расчеты по формулам, применяя константам абсолютную адресацию.

 

Формулы для расчета:

Подоходный налог = (Оклад – Необлагаемый налогом доход)* % подоходного налога, в ячейку D10 введите формулу =(C10 - $C$3)* $C$4;

Отчисления в благотворительный фонд = Оклад * % отчисления в благотворительный фонд, в ячейку E10 введите формулу = C10*$C$5;

Всего удержано = Подоходный налог + отчисления в благотворительный фонд, в ячейку F10 введите формулу = D10 + E10;

К выдаче = Оклад – Всего удержано, в ячейку G10 введите формулу
= C10 – F10.

4. Постройте объемную гистограмму по данным столбца «К выдаче», проведите форматирование диаграммы.

5. Переименуйте лист электронной книги, присвоив ему имя «Доход сотрудников».

6. Выполните текущее сохранение файла (кнопка «Office» /Сохранить).

 

	А	В	С	D	E	F	G
		Расчет дохода сотрудников организации
		Таблица констант:
		Необлагаемый налогом доход	400,00
		% подоходного налога	13,00%
		% отчисления в благотворительный фонд	3,00%
		Таблица расчета заработной платы
	№ п/п	Ф.И.О.	Оклад	Подоходный налог	Отчисления в благотворительный фонд	Всего удержано	К выдаче
		Петров В.С.		?	?	?	?
		Антонова Н.Г.		?	?	?	?
		Виноградова Н.Н.		?	?	?	?
		Гусева И.Д.		?	?	?	?
		Денисова Н.В.		?	?	?	?
		Зайцев К.К.		?	?	?	?
		Иванова К.Е.		?	?	?	?
		Кравченко Г.Ш.		?	?	?	?
		Итого:	?	?	?	?	?

Рис. 2. Исходные данные для задания 4.2

 

Дополнительное задание

Задание 4.3. Создать таблицу расчета квартальной и годовой прибыли. Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены на рис. 3.

 

	А	В	С	D	E	F
	Расчет квартальной и годовой прибыли
		Квартал 1	Квартал 2	Квартал 3	Квартал 4	За год
	Кол-во проданных изделий					?
	Доход	?	?	?	?	?
	Себестоимость	?	?	?	?	?
	Расходы	8 000 000	6 000 000	7 300 000	5 800 000	?
	прибыль	?	?	?	?	?
	Таблица констант:
	Розничная цена	50000,00
	Процент себестоимости	40%
	Доход = Розничная цена x Кол-во проданных изделий
	Себестоимость = Розничная цена x Процент себестоимости
	Прибыль = Доход – Себестоимость - Расходы

Рис. 3. Исходные данные для задания 4.3

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

Тема: ФИЛЬТРАЦИЯ ДАННЫХ И УСЛОВНОЕ
ФОРМАТИРОВАНИЕ В MS EXCEL

Цель занятия. Изучение информационной технологии организации отбора и сортировки данных в таблицах MS Excel.

Задание 5.1. В таблице «Доход сотрудников» выполнить сортировку и фильтрацию данных.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel. Откройте файл «Расчеты», созданный в Практических работах 1…4.

2. Скопируйте таблицы задания 4.2. (лист «Доход сотрудников»).

3. Произведите сортировку по фамилиям сотрудников в алфавитном порядке по возрастанию (выделите блок ячеек B10:G17 без итогов, выберите в меню Данные команду Сортировка, сортировать по Ф.И.О.)

4. Постройте диаграмму по итогам расчета (данные столбца «К выдаче»). В качестве подписей оси «Х» укажите фамилии сотрудников.

5. Произведите фильтрацию значений дохода, превышающих 1600 р.

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

Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Данные / Фильтр. В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «К выдаче»), и вы увидите список всех неповторяющихся значений этого поля. Выберите команду для фильтрации Числовые фильтры/ больше. В открывшемся окне Пользовательский автофильтр задайте условие – больше 1600.

 

	А	В	С	D	E	F	G
	№ п/п	Ф.И.О.	Оклад	Подоходный налог	Отчисления в благотворительный фонд	Всего удержано	К выдаче
		Гусева И.Д.		190,06	55,86	245,92	1 616,08
		Денисова Н.В.		208,00	60,00	268,00	1 732,00
		Зайцев К.К.		240,50	67,50	308,00	1 942,00
		Иванова К.Е.		305,50	82,5	388,00	2 362,00
		Кравченко Г.Ш.		396,50	103,50	500,00	2 950,00
		Итого:	16 812,00	1 769,56	504,36	2 273,92	14 538,08

 

Рис.5.1. Конечный вид таблицы после сортировки и фильтрации

 

Произойдет отбор данных по заданному условию.

Проследите, как изменился вид таблицы и построенная диаграмма.

Конечный вид таблицы после сортировки и фильтрации представлен на рис. 5.1.

6. Выполните текущее сохранение файла (кнопка «Office» /Сохранить).

 

Задание 5.2. В таблице «Средняя годовая температура воздуха» выполнить условное форматирование и ввод данных.

Порядок работы

1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию (рис.5.2.).

2. При наборе месяцев используйте автокопирование, не забудьте повернуть данные на 90°.

3. Используйте автоподбор ширины ячеек, предварительно выделив ячейки (Главная / Формат/Автоподбор ширины).

4. Проведите условное форматирование значений температур в ячейках B4:M9 (Главная/Условное форматирование).

Установите формат данных:

меньше 0 – синим цветом шрифта (полужирный),

равное 0 – зеленый фон, цвет шрифта – белый,

больше 0 – красным цветом шрифта (полужирный).

 

	А	В	С	D	E	F	G	H	I	J	K	L	M
		Средняя годовая температура воздуха
	Город	январь	февраль	март	апрель	май	июнь	июль	август	сентябрь	октябрь	ноябрь	декабрь
	Москва	-12	-10	-3								-4	-12
	Саратов	-13	-11									-5	-13
	Батуми
	Владивосток	-14	-10	-3									-10
	Омск	-19	-18	-10								-10	-17
	Норильск	-23	-19	-11	-3						-3	-13	-22

Рис.5.2. Исходные данные для задания 5.2.

 

Примечание: Условное форматирование можно задавать как до набора данных, так и после.

5. Выполните текущее сохранение файла (кнопка «Office» /Сохранить).

 

 

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

Тема: КОМПЛЕКСНОЕ ИСПОЛЬЗОВАНИЕ ВОЗМОЖНОСТЕЙ MS EXCEL ДЛЯ СОЗДАНИЯ ДОКУМЕНТОВ

Задание 1. Создать таблицу «Расчет заработной платы».

 

РАСЧЕТ ЗАРАБОТНОЙ ПЛАТЫ ЗА 1 КВАРТАЛ
Ф.И.О.	Оклад	Премия 20%	Итого начислено	Подоходный налог 13%	Итого к выдаче
Баранова Л.В
Васильев С.Н.
Петрова А.Г.
Петухова О.С.
Савин И.Н.

 

Расчетные формулы:

Премия = Оклад û 0,2;

Итого начислено = Оклад + Премия;

Подоходный налог = Итого начислено û 0,13;

Итого к выдаче = Итого начислено – Подоходный налог.

Скопируйте заданную таблицу на новый лист электронной таблицы

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

в январе = 20%;

в феврале = 27%;

в марте = 35%.

 

Рассчитайте среднее значение зарплаты за каждый месяц.

Проведите форматирование средних значений, шрифт – курсив 12 пт., желтая заливка ячейки.

Проведите форматирование заголовка – объединить ячейки и разместить по центру таблицы, шрифт – полужирный курсив 14 пт. зеленого цвета.

Постройте гистограмму заработной платы сотрудников за март.