Тема: Создание электронной книги. Относительная и абсолютная адресация в MS EXCEL.

Организация расчетов в табличном процессоре MS Excel

Цель: Формирование готовности применения информационной технологии использования функций Excel для проведения расчетов и построения диаграмм различного вида

Задание 1

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

 

Финансовая сводка за неделю (тыс. руб.)
Дни недели	Доход	Расход	Финансовый результат
понедельник	3245,20	3628,50	?
вторник	4572,50	5320,50	?
среда	6251,66	5292,10	?
четверг	2125,20	3824,30	?
пятница	3896,60	3020,10	?
суббота	5420,30	4262,10	?
воскресенье	6050,60	4369,50	?
Ср. значение	?	?
Общий финансовый результат за неделю:	?

 

Для оформления шапки таблицы выделите ячейки на третьей строке A3:D3 и создайте стиль для оформления. Для этого выполните команду Формат/Стиль и в открывшемся окне Стиль наберите имя стиля «Шапка таблиц» и нажмите кнопку Изменить. В открывшемся окне на вкладке Выравнивание задайте «Переносить по словам» и выберите горизонтальное и вертикальное выравнивание — «по центру», на вкладке Число укажите формат — «Текстовый». После этого нажмите кнопку Добавить.

Для ввода дней недели наберите «Понедельник» и произведите автокопирование до «Воскресенья». Произведите расчеты в графе «Финансовый результат» по следующей формуле: Финансовый результат = Доход - Расход, для этого в ячейке E3 наберите формулу = С3-D3.

Для ячеек с результатом расчетов задайте формат — «Денежный» с выделением отрицательных чисел красным цветом (Формат/ Ячейки/Вкладка Число/формат — Денежный/отрицательные числа — красные. Число десятичных знаков задайте равное 2). Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопка ). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические».

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

Рисунок 1 – Образец таблицы расчета финансовых результатов

 

2. Постройте диаграмму (линейчатого типа) изменения финансовых результатов по дням недели с использованием мастера диаграмм.

На втором шаге на вкладке Ряд в окошке Подписи оси укажите интервал ячеек с днями недели — А4:А10, введите название диаграммы и подписи осей.

Рисунок 2 – Образец диаграммы

 

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

В режиме фильтра в таблице должны быть видны только те данные, которые удовлетворяют некоторому критерию.

Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Данные/Фильтр/Автофильтр. В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «Доход»). Выберите команду для фильтрации — Условие. В открывшемся окне «Пользовательский автофильтр» задайте условие «Больше 4000». Проследите, как изменились вид таблицы и построенная диаграмма.

 

Задание 2

Заполнить таблицу, произвести расчеты, вычислить минимальную и максимальную суммы покупки (рисунок 3); по результатам расчета построить круговую диаграмму суммы продаж.

 

Анализ продаж
№ п/п	Наименование	Цена, руб	Количество	Сумма, руб.
	Туфли	820,00		?
	Сапоги	1530,00		?
	Куртки	1500,00		?
	Юбки	750,00		?
	Шарфы	125,00		?
	Зонты	500,00		?
	Перчатки	400,00		?
	Варежки	350,00		?
			Всего:	?
		Минимальная сумма покупки:	?
		Максимальная сумма покупки:	?

 

Используйте созданный стиль (Формат/Стиль/Шапка таблиц). Формулы для расчета: Сумма = Цена * Количество; Всего = сумма значений колонки «Сумма».

Для вычисления максимального и минимального значения выберите встроенную функцию МАКС или МИН соответственно из категории «Статистические».

Построенная по таблице диаграмма должна выглядеть следующим образом (рисунок 3).

 

Рисунок 3 – Образец полученной в работе диаграммы

 

Задание 3

Заполнить ведомость учета брака, произвести расчеты, выделить минимальную, максимальную и среднюю суммы брака, а также средний процент брака; произвести фильтрацию данных по условию процента брака < 8 %, построить график отфильтрованных значений изменения суммы брака но месяцам (рис. 3).

ВЕДОМОСТЬ УЧЕТА БРАКА

 

Формула для расчета: Сумма брака = Процент брака х Сумма зарплаты.

В колонке «Процент брака» установите процентный формат чисел.

 

Задание 4.

Заполнить таблицу анализа продаж, произвести расчеты, выделить минимальную и максимальную продажи (количество и сумму), произвести фильтрацию по цене, превышающей 9000р., построить гистограмму отфильтрованных значений изменения выручки по видам продукции.

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

Всего = Безналичные платежи + Наличные платежи; Выручка от продажи = Цена х Всего.

 

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

Время выполнения – 2 часа

Тема: Создание электронной книги. Относительная и абсолютная адресация в MS EXCEL.