Главная | Обратная связь

Тема: ВЫЧИСЛИТЕЛЬНЫЕ ФУНКЦИИ ТАБЛИЧНОГО ПРОЦЕССОРА MICROSOFT EXCEL

Цель занятия. Изучение технологии исполь­зования встроенных вычислительных функций Microsoft Excel (финансового анализа).

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

1. Создайте новую электронную книгу (при стандартной установке Microsoft Office выполните Пуск/Все программы/Microsoft Excel).

		A	B	C	D
		Финансовая сводка за неделю (тыс.руб.)
	Дни недели	Доход	Расход	Фин. результат
	понедельник	3 245,20	3 628,50	?
	вторник	4 572,50	5 320,50	?
	среда	6 251,66	5 292,10	?
	четверг	2 125,20	3 824,30	?
со	пятница	3 896,60	3 020,10	?
	суббота	5 420,30	4 262,10	?
	воскресенье	6 050,60	4 369,50	?
	Ср.значение	?	?	?
		Общий финансовый результат за неделю:
	Общий финансовый результат за неделю:	?

 

Введите заголовок таблицы «Финансовая сводка за неделю (тыс. руб.)», начиная с ячейки А1.

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

нажмите кнопку Добавить.

4. На третьей строке введите названия колонок таблицы — «Дни
недели», «Доход», «Расход», «Финансовый результат», далее запол-
ните таблицу.

Наберите «Понедельник» и произведите автокопирование до «Воскресенья» (левой кнопкой мыши за маркер автозаполнения в правом нижнем углу ячейки).

5. Произведите расчеты в графе «Финансовый результат» по
следующей формуле:

Финансовый результат = Доход - Расход.

Для этого в ячейке D4 наберите формулу = В4 - С4.

Вводите расчетные формулы только для расчета по строке «Понедельник», далее произведите автокопирова­ние формул (так как в графе «Расход» нет незаполненных данными ячеек, можно производить автокопирование двойным щелчком мыши по маркеру автозаполнения в правом нижнем углу ячейки)

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

Обратите внимание, как изменился цвет отрицательных значе­ний финансового результата на красный.

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

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

8. В ячейке D13 выполните расчет общего финансового резуль­тата (сумма по столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться кнопкой Автосуммирование (∑) на панели инструментов или функцией СУММ {Вставка/Функция, категория — Математические/СУММ). В качестве первого числа выделите группу ячеек с данными для расчета суммы — D4:D10

9.Проведите форматирование заголовка таблицы. Для этого вы­делите интервал ячеек от А1 до D1, объедините их кнопкой панели инструментов Объединить и поместить в центре или командой меню Формат/Ячейки/вкладка Выравнивание/отображение — Объединение ячеек. Задайте начертание шрифта — полужирное, цвет — по вашему усмотрению.

Проведите форматирование ячейки D13 таблицы. Для выделе­ния результата финансового анализа выполните заливку цветом ячейки D13 (Формат/Ячейки/вкладка Вид).

Конечный вид таблицы

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

Для этого выделите интервал ячеек с данными финансового ре­зультата D4:D10 и выберите команду Вставка/Диаграмма. На пер­вом шаге работы с Мастером диаграмм выберите тип диаграммы — линейчатая; на втором шаге на вкладке Ряд в окошке Подписи оси X укажите интервал ячеек с днями недели А4:А10

Далее введите название диаграммы и подписи осей; дальнейшие шаги построения диаграммы осуществляются по подсказкам масте­ра.

10.

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

 

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

Для установления режима фильтра установите курсор внутри со­зданной таблицы и воспользуйтесь командой Данные/Фильтр/Ав­тофильтр. В заголовках полей появятся стрелки выпадающих списков.

Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столб­це «Доход»), и вы увидите список всех не­повторяющихся значений этого поля. Выбе­рите команду для фильтрации — условие

В открывшемся окне «Пользовательский автофильтр» задайте условие «Больше 14200»

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

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

12. Сохраните созданную электронную книгу в своей папке.

Задание 1.2. Заполнить таблицу «Анализ продаж», произвести расчеты, выделить минимальную и максимальную сум­мы покупки; по результатам расчета построить круговую диаграмму суммы продаж. Использовать созданный стиль «Шапка таблиц» (Формат/Стиль/Шапка таблиц)

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

Сумма = Цена х Количество; Всего = сумма значений колонки «Сумма».

Для определения максимального (мини­мального) значения данных установите курсор в ячейке расчета, вы­берите встроенную функцию МАКС (МИН) из категории «Ста­тистические», в качестве первого числа выделите диапазон ячеек значений столбца «Сумма» (ячейки ЕЗ:Е10).