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

Тема: Работа с логическими функциями. Обработка таблиц-списков.
Подведение итогов. Консолидация данных.

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

Рекомендуется изучить:

· Логические функции ЕСЛИ, И, ИЛИ, НЕ;

· Средства консолидации данных и подведения итогов;

• Сортировку и фильтрацию данных

Порядок выполнения:

1. Создайте 6 листов и переименуйте их ярлыки соответственно в «Справочник», «Зарплата_январь», «Зарплата_февраль», «Зарплата_март», «Итоги», «Консолидация по фамилиям 1кв».

2. На лист «Справочник» внесите следующую информацию (рис. 5.1)

Рис. 5.1. Информация на листе «Справочник»

Дадим пояснения. НДФЛ взимается не со всех доходов, которые получает налогоплательщик. Из них вычисляются некоторые суммы, которые в Налоговом кодексе РФ (НК РФ) назвали налоговыми вычетами. Налоговые вычеты определены ст. 218 НК РФ. Налогоплательщикам за каждый месяц налогового периода предоставляются следующие стандартные налоговые вычеты:

1. 3000 руб. – пострадавшим или участвовавшим в ликвидации последствий катастрофы на Чернобыльской АЭС (ячейка C6);

2. 500 руб. – Героям Советского Союза, Героям РФ, лицам, награжденным орденом Славы трех степеней (ячейка C7);

3. 400 руб. – налогоплательщикам, которые не перечислены в подпунктах 1 и 2. Вычет предоставляется до месяца, в котором доход, начисленный с начала года нарастающим итогом, превысит 20000 руб (ячейка C9);

4. 600 руб. – на каждого ребенка. Вычет предоставляется на каждого ребенка до месяца, в котором доход, начисленный с начала года нарастающим итогом, превысит 40000 руб (ячейка C10).

Налоговые вычеты уменьшают налоговую базу, т.е. сумму доходов, с которых исчисляется НДФЛ.

3. Три созданных листа «Зарплата_январь», «Зарплата_февраль», «Зарплата_март» «склейте» и создайте таблицу по образцу рис 5.2. и рис. 5.3.

Рис. 5.2. Таблица для расчета зарплаты

Рис. 5.3. Продолжение таблицы для расчета зарплаты

Примечание. В ряде случаев требуется ввести одни и те же данные в несколько рабочих листов или одинаково отформатировать определенные диапазоны ячеек. Для этого необходимо предварительно склеить листы и «писать под копирку».

· Для «склеивания» всех листов книги необходимо поместить указатель мыши на ярлычок любого листа и дать команду Click правой клавишей мыши → Выбрать все листы.

· Для «склеивания» нескольких смежных листов - Click левой клавишей мыши на первом листе → Shift+ click на последнем.

· Для «склеивания» нескольких несмежных листов - Click левой клавишей мыши на первом листе → Ctrl+ click на каждом последующем.

· Для отмены «склеивания» листов Click правой клавишей мыши → Разгруппировать листы.

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

Столбец №4 заполнить с использованием пользовательского списка автозаполнения. Для этого через команду меню Сервис → Параметры → Списки создать новый список, внеся фамилии и инициала десяти сотрудников. Далее в ячейку D9 ввести «Афтаев Д.И.», установить марка заполнения и растянуть до ячейки D18.

Столбцы №2 и №5 создать с использованием списка допустимых значений. Для этого выделяется нужный диапазон ячеек (например, B9:B18) и задается команда меню Данные → Проверка. В появившемся диалоговом окне «Проверка вводимых значений» (рис. 5.4.) выберите в списке «Тип данных» элемент «Список», а в поле ввода «Источник» занесите названия всех подразделений через точку с запятой. При этом флажок «Список допустимых значений» должен быть включен. Далее активизируйте ячейку B9. При вводе справа от активной ячейки будет появляться кнопка открытия списка . Нажав эту кнопку, откроется список допустимых значений, из которого выбирается информация для ввода. Аналогичным образом создайте список должностей.

Рис. 5.4. Диалоговое окно «Проверка вводимых значений»

Столбцы 11, 17 и 25 отформатируйте с использованием пользовательского формата 0;0;---. Для этого откройте окно «Формат ячеек» (меню Формат → Ячейки) В списке форматов выберите самый последний – «Все форматы» и введите свой формат 0;0;---, нажмите ОК. Здесь первый ноль задает формат вывода положительных чисел, второй ноль – формат вывода отрицательных чисел, третий элемент – формат вывода нулевых значений. В данном случае введен текст из трех дефисов для имитации прочерка в ячейке.

Столбец «Оклад» заполняется с использование межлистовой ссылки в зависимости от столбца «Разряд». Например, для ячейки I9 формула будет следующая =Справочник!H28.

Количество рабочих дней в месяце нужно для расчета заработной платы с учетом числа фактически отработанных дней. Введите в ячейку J9 формулу =$F$2 и маркером заполнения скопируйте формулу до ячейки J18. Измените значения в ячейках J12 и J15 соответственно на 12 и 13

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

(1)

где «НРД» - начислено за рабочие день, «ОРД» - отработано рабочих дней, «КРД» - количество рабочих дней за данный месяц.

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

Премия рассчитывается от суммы заработной платы, начисленной за работу в рабочие дни с учетом стажа работы. Размер премии задан на листе «Справочник».

Столбец «Начислено всего» определяется как сумма трех столбцов «Начислено за рабочие дни», «Начислено за выходные дни», «Начислено премия».

Столбцы №19 и №20 вычисляются с использование функции «если». Столбец №21 определяется как сумма трех столбцов №19, 20, 21.

В столбце №23 налог берется с листа «Справочник», в столбце №24 используется формула:

где «НП» - налог в профсоюз, «НВ» - начислено всего, «ВВ» - всего вычетов, «ЧП» - член профсоюза.

Столбец № 27 определяется как разница «НВ» и «ИУ», где «ИУ» - итого удержано.

Подвести итоги в ячейках I19:Q19 и S19:AA19.

4. Листы «расклеить». Изменить следующие данные. На листе «Зарплата_февраль» изменить F2=19; F3=9. Изменить данные в столбцах №10, 11, 16 (рис. 5.5.).

Рис. 5.5. Данные для февраля

5. На листе «Зарплата_март» изменить F2=22; F3=9. Изменить данные в столбцах №10, 11, 16 (рис. 5.6.).

Рис. 5.6. Данные для марта

6. Отсортировать таблицу на листе «Зарплата_январь» по двум ключам «Подразделение» и «Фамилия». Используя «Пользовательский автофильтр» создать запрос к таблице «Вывести фамилии всех сотрудников, у которых оклады лежат в диапазоне 8080<= оклад <=11336, а количество детей 1 или 3».

7. Скопировать данные листа «Зарплата_март» на лист «Итоги» без строки 19. Выделите диапазон ячеек B8:AA18, и отсортируйте таблицу по подразделению. Выполните команду Данные → Итоги (рис. 5.7.).

Рис. 5.7. Итоги

В разделе «Добавить итоги по» включите еще 12 и 13 столбцы.

В результате программа изменяет разметку списка за счет группировки однотипных данных и вывода итогов в дополнительные строки (рис. 5.8.). В левой части списка после вычисления итогов появляются элементы управления структурой таблицы. Нажав кнопку , откроется список элементов в группе, и кнопка превратиться в . Нажав кнопку , список свернется, и кнопка опять станет , а вместо группы останется только строка итога. Подобным образом можно свернуть часть информации в таблице, а можно свернуть все, оставив только итоги.

Рис. 5.8. Промежуточные итоги и элементы структуры

8. В MS Office Excel под консолидацией понимается объединение данных, представленных в одинаковых по строению таблицах (или их фрагментах), а также в таблицах состоящих из одинаковых столбцов и разных строк, или наоборот. В подавляющем числе случаев для объединения используют функцию суммирования, можно так же использовать функции – среднее, минимум, максимум и так далее. Можно выделить два типа таблиц и соответственно два подхода к их консолидации:

· Консолидация по расположению;

· Консолидация по категориям.

Создадим консолидацию по категориям. Перейдите на лист «Зарплата_февраль» и в середину списка добавьте двух новых сотрудников. Задайте им уникальные табельные номера, должности, оклады, количество отработанных дней, количество детей, разряды. Скопируйте все формулы. Выделив правильно диапазон, отсортируйте таблицу по фамилиям. Перенумеруйте сотрудников, используя арифметическую прогрессию. Скройте на листах «Зарплата_январь», «Зарплата_февраль» и «Зарплата_март» следующие столбцы № 5 по 8, 16 по 21.

Перейдите лист «Консолидация по фамилиям за 1кв». Установите курсор в позицию, соответствующую фамилии первого сотрудника (D9). Выберите пункт меню Данные → Консолидация и установите в качестве консолидирующей функцию Сумма. Поместите курсор в строку Ссылка, затем перейдите на лист «Зарплата_январь» и выделите диапазон, начиная с фамилии первого сотрудника (D9:AA17). Нажмите кнопку Добавить. Выделенный диапазон перенесется в нижнюю часть окна консолидации. Внесите в список такие же диапазоны из листов «Зарплата_февраль» и «Зарплата_март». Обратите внимание, что они будут разные, так как число сотрудников на этих листах неодинаковое (рис. 5.9).

Рис. 5.9. Заполненное диалоговое окно «Консолидация»

Обратите внимание, что флажок «Использовать в качестве имени значения левого столбца» должен быть включен, остальные выключите. Нажмите кнопку ОК для выполнения операции. На новом листе будет создана таблица без заголовков, но фамилиями. Копированием через буфер обмена перенесите заголовки. Отформатируйте консолидированную таблицу. Проверьте правильность вычислений по нескольким строкам таблицы.

Список литературы

1. Коцюбинский, А.О. Excel для менеджера и экономиста в примерах/ А.О. Коцюбинский, С.В. Грошев. –М:ГроссМедиа, 2004. – 304 с.

2. Музычкин, П.А. Excel в экономических расчетах./ П. А. Музычкин, Ю.Д. Романова – М. Эксмо, 2009. – 304 с.

Дата добавления: 2015-07-13