Практическое занятие Microsoft Office Excel. Создание и редактирование таблиц. Работа с взаимосвязанными таблицами. Построение диаграмм.
учебно-методический материал

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

Microsoft Office Excel. Создание и редактирование таблиц. Работа с взаимосвязанными таблицами. Построение диаграмм.

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

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

Задание 1.  

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

Таблица 1

4. Рассчитайте сумму выручки от реализации книжной продукции в июне месяце одним из двух способов:

1. установите курсор в ячейку В7, введите в ячейку В7 формулу (формула начинается со знака =, занесение имён ячеек в формулу осуществляем щелчками мыши по соответствующим ячейкам): =В4+В5+В6. Нажмите клавишу Enter.

В ячейке появится сумма диапазона ячеек В4:В6;

2. очистите ячейку В7 (установите курсор в ячейку В7 и нажмите кнопку Del). Выделите диапазон ячеек В4:В7и нажмите кнопку Автосумма .

В ячейке В7 появится сумма диапазона ячеек В4:В6. Сравните полученный результат со значением ячейки В7 в таблице 2.

Таблица 2

5. Распространите операцию суммирования на диапазон С7:F7 одним из способов:

1. скопируйте содержимое ячейки В7 в ячейки C7:F7. Для этого схватите ячейку В7 за правый нижний угол (зону  автозаполнения) и не отпуская кнопку мыши перетащите её в ячейку F7. Очистите диапазон C7:F7.

2. выделите ячейку В7, выполните команду Копировать, выделите ячейки С7:F7 и выполните команду  Вставить. Снимите выделение, нажав кнопку Esc.

 6. Убедитесь в правильности выполненной операции:

1. выделите ячейку В7. В строке формул должно отобразиться выражение:=СУММ(В4:В6);
2. выделите ячейку С7. В строке формул должно отобразиться выражение:=СУММ(С4:С6).

7. Подсчитайте суммарную выручку от реализации книжной продукции (столбец Итого). Для этого:

1. выделите в строке ячейки B4:G4;
2. нажмите кнопку;
3. в ячейке G4 появится сумма диапазона B4:F4.

8. Подсчитайте суммы в остальных ячейках столбца Итого. Для этого: схватите ячейку G4 за правый нижний угол (зону автозаполнения) и, не отпуская кнопку мыши, протащите её до ячейки G7. В ячейках G5, G6,G7 появятся суммарная выручка от реализации книжной продукции.

9. Определите долю выручки, полученной от продажи партий товара. Для этого:

1. выделите ячейку Н4;
2. введите формулу =G4/G7;
3. нажмите клавишу Enter;
4. выделите ячейку Н4;
5. нажмите кнопку  (Вкладка Главная, Группа Число).

      В ячейкеН4 появится значение доли выручки в процентах.

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

В результате автозаполнения в ячейках Н5, Н6 и Н7 появится сообщение #ДЕЛ/0! (деление на ноль). Такой результат связан с тем, что в знаменатель формулы введён относительный адрес ячейки, который в результате копирования будет смещаться относительно ячейки G7(G8, G9  G10 - пустые ячейки). Измените относительный адрес ячейки G7 на абсолютный —$G$7, это приведёт к получению правильного результата расчёта. Еще раз попробуйте рассчитать доли выручки в процентах. Для этого:

1. очистите диапазон Н4:Н7;
2. выделите ячейку Enter;
3. рассчитайте долю выручки для других строк таблицы, используя автозаполнение.

В результате в ячейках диапазона Н4:Н7 появится доля выручки в процентах.

• В ячейку Н4;
• введите формулу =G4/$G$7;
• нажмите клавишу Enter;
• рассчитайте долю выручки для других строк таблицы, используя автозаполнение.

В результате в ячейках диапазона Н4:Н7 появится доля выручки в процентах.

11. Оформите таблицу по своему усмотрению.

12. Сохраните результат.

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

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

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

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

Таблица 3

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

12. Сохраните результат.

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

Таблица 4

Используйте созданный стиль (Формат/Стиль/Шапка таблиц).

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

Сумма = Цена * Количество;

Всего = сумма значений колонки «Сумма».

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

 Сохраните результат.

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

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

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

Краткая справка. В колонке «Процент брака» установите процентный формат чисел (Формат/Ячейки/вкладка Число/формат — Процентный).

Таблица 3

 Сохраните результат.