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

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

Задание 7.1. Создание таблицы финансовой сводки за неделю.

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

Исходные данные представлены на рис. 2.7, результаты рабо­ты – на рис. 2.8, 2.9 и 2.10.

Технология работы

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

Рис. 2.7. Исходные данные для Задания 7.1.

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

3. Для оформления шапки таблицы выделите ячейки на тре­тьей строке (массив A3:D3) и создайте стиль для оформления. Для этого выполните команду Формат/Стиль и в открывшемся окне Стиль (рис. 2.8) наберите имя стиля «Обычный» и нажмите кнопку Изменить.

Рис. 2.8. Создание стиля оформления шапки таблицы

В открывшемся окне на вкладке Выравнивание задайте Переносить по словам и выберите горизонтальное и вертикальное выравнивание – по центру (рис. 2.9), на вкладке Число укажите формат – Текстовый. После этого нажмите кнопку Добавить.

Рис. 2.9. Окно форматирования ячеек

4. На третьей строке введите названия колонок таблицы – «Дни недели», «Доход», «Расход», «Финансовый результат», далее запол­ните таблицу исходными данными согласно Заданию 7.1.

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

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

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

для этого в ячейке D4 наберите формулу

= В4 - С4.

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

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

Рис. 2.10. Задание формата отрицательных чисел красным цветом

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

Рис. 2.11. Выбор функции расчета среднего значения

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

Рис. 2.12. Задание интервала ячеек при использовании функции СУММ

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

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

Рис. 2.13. Таблица расчета финансового результата

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

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

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

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

Рис. 2.14. Конечный вид диаграммы Задания 7.1

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

Рис. 2.15. Выбор условия фильтрации

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

Рис. 2.16. Окно пользовательского автофильтра

Произойдет отбор данных по заданному усло­вию. Проследите, как изменились вид таблицы (рис. 2.17) и построенная диаграмма.

Рис. 2.17. Вид таблицы после фильтрации данных

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

Задание 7.2. Оформление ведомости учета брака.

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

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

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

Сумма брака = Процент брака х Сумма зарплаты.

Краткая справка. В колонке «Процент брака» установите процентный формат чисел (Формат/Ячейки/вкладка Число/фор­мат – Процентный). Проведите сортировку по фамилиям в алфавитном порядке (или табельным номерам) по возрастанию. С этой целью выделите фрагмент таблицыс заголовка до последней строки с7писка фамилий (без итогов). Затем выберете меню Данные/ Сортировка, сортировать по Ф.И.О. (или Табельный номер).