Задача 2: «Расчет начислений и удержаний»

Цели задания.

Освоить следующие функции MS Excel:

· Скрытие и отображение элементов листа

· Сортировка данных по нескольким ключам

· Подсчет промежуточных итогов

· Ссылки на ячейки другого листа

· Подготовка документа к печати (ориентация листа, масштаб, поля, колонтитулы)

Состав задания.

10. Создайте на собственном диске файл «Начисления»

11. Назовите Лист1 именем «Начисления».

12. Оформите таблицу для расчета удержаний и сумм к выдаче:

Оформление 1-й строки:

1.12.1. Пометьте ячейки А1:В1; D1; F1:I1 (несмежные диапазоны выделяются с нажатой клавишей CTRL) и измените ориентацию текста в ячейках с помощью команды «Главная – Формат – Формат Ячеек – Выравнивание – Ориентация»

2.12.1. Для всех ячеек диапазона А1:К1выберите следующие параметры:

· Полужирное начертание шрифта

· Выравнивание по горизонтали и вертикали – по центру

· Отображение – переносить по словам

3.12.1. Подберите высоту 1-й строки, чтобы текст в ячейках располагался оптимально.

4.12.1. Для столбцов А:К выполните автоподбор ширины столбца (Главная – Ячейки – Формат – Автоподбор ширины столбца)

12.2. Для ячеек А2:А3 установите формат даты (Главная – Число)

12.3. Для ячеек Е2:I3; J2:K2 используйте денежный формат с двумя десятичными знаками и обозначением «р.» (Формат – Число)

12.4. Расчет удержаний и суммы к выдаче произведите по следующим формулам:

1.12.4. Подоходный = 13%*(Начислено – ИндВыч –КолДет*ДетВыч - Благотвор. – Строит.)

Т.к. при расчете облагаемой части начисленийиндивидуальный вычет (ячейка J2) и детский вычет (ячейка К2) являются постоянными коэффициентами, то в формуле следует зафиксировать адреса этих ячеек.

2.12.4. К выдаче = Начислено - Подоходный - Благотворительность

После проведения расчетов таблица должна принять такой вид:

13. Введите данные по остальным работающим:

05.01.04		Иванов И.П.		7450,00р.	500,00р.
05.01.04		Карцев П.Р.		5800,00р.		1450,00 р.
05.01.04		Авдеев А.С.		6560,00 р.	50,00 р.
05.01.04		Якушев А.Р.		8760,00 р.	300,00 р.

По окончании ввода записей убедитесь, что таблица приняла следующий вид:

14. Познакомиться с помощью справочной системы MS Excel (F1) с темой Оглавления «Основные сведения о листах и таблицах Excel – Управление листами – “Скрытие и отображение строк и столбцов”».

· Скройте столбцы D:H; J:K. Таблица должна принять следующий вид:

· Предъявите преподавателю результат работы.

· Отобразите скрытые столбцы, т.е. приведите таблицу к виду

· п. 4.

15. Выделите диапазон ячеек А2:I7, скопируйте его и вставьте в ячейку А8, а затем в ячейку А14.

15.1. В ячейках А8:А13 измените дату получения с 05.01.2004 на 05.02.2004, а в ячейках А14:А19 – на 05.03.2004.

15.2. В ячейках Е8: G19 произвольно измените суммы Начислений, Благотворительности и Строительства.

15.3. Проверьте, изменились ли автоматически суммы подоходного налога и «К выдаче»? Если нет, то заполните формулами с помощью маркера заполнения ячейки H8:I19.

Таблица в результате должна выглядеть примерно так:

16. Сгруппировать данные по каждому из сотрудников, упорядочив их по месяцам. Для этого:

· Выделите диапазон ячеек А1:I19 (без J1:К2). Это важно, т.к. влияющие ячейки J2, К2 не должны смещаться при сортировке.

· Выполните команду «Данные – Сортировка и фильтр – Сортировка».

· В открывшемся окне убедитесь, что активен переключатель «Идентифицировать диапазон данных по подписям (первая строка диапазона)».

· Далее укажем критерии сортировки.

ü Т.к. необходимо получить группы записей по каждому из сотрудников, то в качестве 1-го уровня сортировки можно выбрать «Табельный №» или «Фамилию И.О.» с сортировкой по возрастанию

ü Второй уровень сортировки предполагает упорядочение записей внутри каждой группы. В нашем случае данные о сотрудниках нужно выстроить по месяцам. Поэтому 2-м критерием будет «Дата получения» с сортировкой по возрастанию.

Третьего критерия здесь быть не может, т.к. каждая дата получения внутри группы «Фамилия И.О.» повторяется только раз.

· Нажмите «ОК». Отсортированная таблица должна иметь такой вид:

17. Для каждого из сотрудников вставить строку с денежными итогами за квартал. Для этого:

Ø Оставьте выделенными ячейки А1:I19 и вызовите команду «Данные – Структура – Промежуточные итоги».

Эта команда позволяет подсчитывать промежуточные итоги по заданным группам записей.

· В нашем примере необходимо подсчитать суммы по сотрудникам, поэтому меняться должна не Дата получения, а Фамилия И.О. или Табельный №

· Операция – Сумма

· Добавить итоги по: Начислено, Благотворительность, Строительство, Подоходный налог, К выдаче (Проставьте флажки в данных полях).

· Нажмите ОК. Таблица должна иметь следующий вид:

8. Скопировать таблицу и вставить ее на 2 строки ниже. В полученной копии необходимо получить список только тех сотрудников, которые отчисляли деньги на строительство жилья. Для этого:

· Установить курсор в строку заголовков копии таблицы.

· Выбрать команду "Данные – Фильтр". У каждой ячейки заголовка появится кнопка, позволяющая вводить критерии фильтра. По этой команде можно вывести на экран только те записи, которые удовлетворяют выбранным условиям фильтра, без удаления остальной информации.

· Раскрыть выпадающий список ячейки "Строительство жилья". Выбрать команду «Числовые фильтры – Настраиваемый фильтр». В появившемся диалоговом окне установить параметры "больше" и "0".

· Нажмите ОК

На экране останется информация только о тех сотрудниках, которые предоставляли в бухгалтерию сведения о расходах на строительство жилья.

8.1 Для того, чтобы снова увидеть перечень всех сотрудников, нужно в выпадающем списке фильтра «Строительство жилья» выбрать команду «Снять фильтр со строительства жилья».

9. Познакомиться с помощью справочной системы MS Excel с темой «Основные сведения о листах и книгах MS Excel – Управление листами – “Закрепление и блокировка строк и столбцов”».

10. С помощью команды «Вид – Окно – Закрепить области» зафиксировать области слева и над ячейкой D2.

11. Установить параметры страницы и создайте колонтитулы. Для этого:

· Выполните команду «Разметка страницы – Параметры страницы».

В MS Excel существуют 3 взаимосвязанные команды: «Параметры страницы», «Печать» и «Просмотр». Все они позволяют настроить параметры страницы, отправляемой на печать. При этом в обычном виде рабочего листа ни один из примененных параметров не отображается. Они видны лишь в режиме Разметки страницы, при просмотре страницы и при печати.

· На вкладке «Страница» установите переключатель «Масштаб» в положение «Разместить не более чем на 1 стр. в ширину и 1 стр. в высоту».

Обратите внимание: этот переключатель позволяет автоматически подобрать масштаб для печати широких и слишком длинных таблиц.

· На вкладке «Поля» установите все поля по 2 см.

· На вкладке «Колонтитулы» создайте верхний и нижний колонтитулы.

Колонтитул – текст, характеризующий содержание данного рабочего листа и повторяющийся автоматически при печати каждой страницы. Колонтитулы могут располагаться вверху и(или) внизу печатной страницы. Структура колонтитула:

11.1Верхний колонтитул.

• Слева поместите № страницы
• Справа – Текущую дату

11.2Нижний колонтитул

• В центре поместите текст «Лист начислений и удержаний».

12. Выполните команду «Ms Office – Печать – Предварительный просмотр».

В результате перед печатью страница должна выглядеть следующим образом:

13. Назовите Лист 2 именем «Итоги»

14. Начиная с 5-й строки, оформите таблицу для отображения итоговых начислений сотрудников за квартал:

15. В ячейке D6 нужно разместить ссылку на итоговые начисления Карцева П.Р. за квартал.

Обратите внимание: необходимо не скопировать итоговую сумму, а сослаться на нее. Как это сделать?

15.1В ячейку D6 введите знак =

15.2Щелкните мышкой на ячейке Е17 листа «Начисления». (Если итоговые начисления Карцева Р.П. находятся в вашем файле в другой ячейке, укажите соответственно другой адрес).

15.3Нажмите Enter.

15.4С помощью маркера заполнения размножьте ссылки на итоговые результаты Карцева П.Р. вправо по строке до ячейки Н6.

16. Аналогично получите итоговые результаты остальных работающих.

17. Измените в исходной таблице данные о начислениях одного из сотрудников. Проанализируйте, как изменились итоговые данные на листах «Начисление» и «Итоги».

18. По данным таблицы постройте диаграмму, отражающую сумму, полученную каждым сотрудником. Для этого выполните Вставка – Диаграмма. Укажите:

18.1тип диаграммы (Например, объемный вариант круговой диаграммы);

Макет диаграммы появляется на рабочем листе и MS Excel добавляет в Командное меню пункт «Работа с диаграммами».

18.2исходные данные: Выполните команду Работа с диаграммами – Конструктор – раздел Данные – Выбрать данные. Выделите 2 столбца таблицы «Фамилия И.О.» и «К выдаче» (В6:В11; Н6:Н11).

Excel позволяет выделять несмежные ячейки, если при выделении диапазона ячеек удерживать нажатой клавишу Ctrl.

18.3размещение диаграммы: Выполните команду Работа с диаграммами – Конструктор – раздел Расположение – Переместить диаграмму. Разместите диаграмму на отдельном листе с именем "Итоговые начисления".

18.4параметры: Выполните команду Работа с диаграммами – Макет – раздел Подписи.

· название диаграммы: «Начисления сотрудников»;

· легенда, размещенная внизу страницы;

· подписи данных в виде доли (Дополнительные параметры подписей данных);

19. Назовите таблицу «Итоговые начисления сотрудников», оформив название как объект WordArt:

· Выполните «Вставка – раздел Текст – WordArt».

· Выберите любую надпись из предложенной коллекции

· Введите вместо слов «Текст надписи» текст Итоговые начисления сотрудников

· Измените цвет заливки и форму объекта WordArt с помощью соответствующих кнопок панели инструментов.

20. Придумайте и постройте 2 другие диаграммы или графика, отображающих данные таблиц на листах «Начисления» и «Итоги».

21. Установите для вновь созданных листов новые параметры страницы:

· поля: верхнее, нижнее – 2 см, левое – 3 см, правое – 1 см.

· создайте колонтитулы, содержащие номера страниц и имя файла.