Использование табличного процессора MS Excel для экономических расчетов, создания выборок, связанных таблиц и расчета промежуточных итогов

КЕЙС-СИТУАЦИЯ 4: Перед вами стоит задача с использованием MS Excel создать таблицы ведомости начисления заработной платы по месяцам и за квартал на разных листах электронной книги, выполнить расчеты, сортировку, фильтрацию данных по условию, применить расширенный фильтр, защитить данные листа от изменений. Вычислить квартальные итоги с помощью промежуточных итогов, сводных таблиц, функций работы с базой данных.

Цель учебного кейса: Освоить технологии создания связанных таблиц, вычисления промежуточных итогов, создания сводных таблиц в среде Excel.

Задания и алгоритм реализации кейс-ситуации 4:

Запустите редактор электронных таблиц MS Excel, по умолчанию откроется новая электронная книга.

Создайте базу данных (БД) для расчета заработной платы по образцу (рисунок 34) и введите исходные данные — ФИО, Подразделение и Оклад, % Премии = 27%, % Удержания = 13% к ячейкам F1: F2 примените процентный формат кнопкой .

Рисунок 34 — Исходные данные для начисления зарплаты Введите формулы для расчета зарплаты по первому сотруднику (кейс-компонент 4.1):

Премия = Оклад * % Премии (=$F$ 1*C5).

Всего начислено = Оклад + Премия (=С5+D5).

Удержания = Всего начислено * % Удержаний (=E5*$F$ 2).

К выдаче = Всего начислено — Удержания (=E5-F5).

В формулах для расчета премии и удержания используйте абсолютные ссылки на адреса ячеек (кейс-компонент 4.2).

Скопируйте набранные формулы вниз по столбцу (кейс-компонент 4.3) и примените числовой формат с двумя знаками после запятой кнопки .

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

Рисунок 35- Итоговый вид таблицы расчета заработной платы за октябрь Переименуйте ярлычок Лист1, дважды щелкните мышью по ярлыку листа и присвойте ему имя «Зарплата октябрь» .

Скопируйте переименованный лист «Зарплата октябрь» (кейс-компонент 4.6) и присвойте скопированному листу название «Зарплата ноябрь» (щелчок правой кнопкой мыши по листу — переименовать).

Исправьте название месяца в названии таблицы. Измените значение премии на 32%, в ячейку Е3 введите «Доплата», F3 — 5%.

Между полями «Премия» и «Всего начислено» вставьте новый столбец «Доплата» (кейс-компонент 4.4) и рассчитайте значение доплаты в ячейке Е5 по формуле (кейс-компонент 4.1):

Доплата = Оклад * % Доплаты (=C5*$G$ 3).

Скопируйте формулу вниз по этому полю (кейс-компонент 4.3).

Измените формулу для расчета значений поля «Всего начислено» ячейка F5 (кейс-компонент 4.1):

Всего начислено=Оклад+Премия+Доплата (=C5+D5+E5).

Скопируйте формулу вниз по этому полю (кейс-компонент 4.3).

Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10 000 — зеленым цветом шрифта, меньше 7000 — красным, больше или равно 10 000 — синим цветом шрифта (кейс-компонент 4.7).

Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (кейс-компонент 4.8).

Создайте к ячейке D4 комментарии «Премия пропорциональна окладу» (щелчок по ячейке правой кнопкой мыши — вставить примечание). Конечный вид таблицы расчета заработной платы за ноябрь представляет рисунок 36.

Защитите лист «Зарплата ноябрь» от изменений (кейс-компонент 4.9). Задайте пароль на лист — 159, сделайте подтверждение пароля.

Убедитесь, что лист защищен и удаление данных невозможно. Снимите защиту листа (кейс-компонент 4.9).

Скопируйте лист «Зарплата ноябрь» (кейс-компонент 4.6).

Присвойте скопированному листу название «Зарплата декабрь» (щелчок правой кнопкой мыши по листу — переименовать). Исправьте название месяца в названии таблицы.

Измените значение «Премии» на 46%, «Доплаты» — на 8%. Убедитесь, что программа произвела пересчет всех формул (рисунок 37).

Рисунок 36 — Конечный вид таблицы расчета зарплаты за ноябрь.

Рисунок 37 — Расчет зарплаты за декабрь Постройте гистограмму с группировкой по несмежному диапазону фамилий сотрудников и их доходов (кейс-компонент 4.10). Проведите редактирование диаграммы: добавьте подписи осей и название диаграммы (кейс-компонент 4.11). Конечный вид гистограммы представляет рисунок 38.

Рисунок 38 — Гистограмма зарплаты за декабрь Скопируйте лист «Зарплата октябрь» (кейс-компонент 4.6).

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

Отредактируйте лист «Итоги за квартал» согласно образцу (рисунок 39). Для этого удалите в основной таблице столбцы «Оклад» и «Премия» (кейс-компонент 4.4), расчетные формулы по полям «Всего начислено», «Удержания», «К выдаче», а также ячейки с данными Премии, Удержания и строку 19 (кейс-компонент 4.4). Удалите также строки с расчетом максимального, минимального и среднего доходов под основной таблицей.

Для однородности расчетных таблиц перед расчетом итоговых данных за квартал проведите сортировку по фамилиям в алфавитном порядке (по возрастанию) в таблице расчета зарплаты за октябрь и на листе «Итоги за квартал» (кейс-компонент 4.8).

Рисунок 39 — Таблица для расчета итоговой квартальной заработной платы Проведите расчет квартальных начислений, удержаний и суммы к выдаче путем создания ссылок на ячейки других листов (кейс-компонент 4.12). В ячейке С5 для расчета квартальных начислений «Всего начислено» формула имеет вид: ='Зарплата октябрь'!E5+'Зарплата ноябрь'!F5+'Зарплата декабрь'!F5 (рисунок 40). Аналогично проведите квартальный расчет полей «Удержания» и «К выдаче» или скопируйте полученную формулу на диапазон С5: Е18 (кейс-компонент 4.3).

Для дальнейших расчетов проведите сортировку по подразделениям, а внутри подразделений — по фамилиям (кейс-компонент 4.8).

Рисунок 40 — Расчет квартального начисления заработной платы путем связывания листов электронной книги Получите список фамилий БД чей доход находиться в диапазоне больше или равно 30 000 и меньше 20 000, используя отбор данных по фильтру с условием (кейс-компонент 4.13) (рисунок 41). Отмените действие фильтра (кейс-компонент 4.13).

Рисунок 41 — Условия для отбора данных по полю «К выдаче» .

Используя расширенный фильтр (кейс-компонент 4.14), постройте список фамилий, чей доход за квартал составляет меньше среднего значения суммы поля «К выдаче». В ячейку А20 введите «среднее значение», в ячейку В20 — функцию расчета среднего значения (СРЗНАЧ, категория статистические) по полю «К выдаче» (кейс-компонент 4.5). Скопируйте заголовок базы данных в строку 22, задайте условие отбора по полю «К выдаче» — <22 512 и настройте диалоговое окно расширенного фильтра (рисунок 42). Результат построения списка отображает рисунок 43.

Рисунок 42- Настройка условий для расширенного фильтра.

Рисунок 43 — Результат отбора по расширенному фильтру Проведите отбор фамилий по подразделению «Бухгалтерия», чей доход больше среднего значения итоговой квартальной суммы, изменив при этом условие отбора расширенного фильтра (рисунок 44).

Рисунок 44 — Новые условия для расширенного фильтра Получите квартальные итоги поля «К выдаче» для каждого значения поля «Подразделение» с помощью функции СУММЕСЛИ. Вставьте 7 пустых строк под БД (кейс-компонент 4.4). В ячейки А21: А23 введите названия подразделений, по которым будут вычислены итоговые значения (рисунок 45). В ячейку В21 введите формулу для подсчета итога для первого отдела:

=СУММЕСЛИ ($B$ 5:$B$ 18;A21;$E$ 5:$E$ 18) (кейс-компонент 4.5). Ссылки на диапазоны условия B5: B18 и E5: E18 сделайте абсолютными (кейс-компонент 4.2). Для подсчета итогов по другим отделам, скопируйте полученную формулу вниз (кейс-компонент 4.3).

Рисунок 45 — Расчет квартальных итогов по зарплате с помощью функции СУММЕСЛИ Получите итоговые суммы за квартал по зарплате с помощью функции БДСУММ (кейс-компонент 4.5). Вставьте 10 пустых строк под БД (кейс-компонент 4.4). Скопируйте заголовок БД и вставьте в строку 21. Ячейку В22 заполните первым подразделением — Бухгалтерия, в ячейку Е22 введите формулу =БДСУММ ($A$ 4:$E$ 18;$E$ 4;B21:B22) (рисунок 46). Ссылки на диапазон БД А4: Е…

общего итога (рисунок 51а);

итогов по подразделениям (рисунок 51б);

итог по отделу менеджмента (рисунок 51в).

а) отображение общего итога.

б) отображение итогов по подразделениям.

в) отображение итога по отделу менеджмента Рисунок 51- Работа со структурой промежуточных итогов.