Вычисляемые поля выводятся на экран без окон редактирования.
Электронные таблицы MS Excel 2007.
Цели лабораторной работы.
Освоить следующие функции MS Excel:
· Ввод данных в таблицу в режиме формы
· Скрытие и отображение элементов листа
· Сортировка данных по нескольким ключам
· Подсчет промежуточных итогов
· Ссылки на ячейки другого листа
· Подготовка документа к печати (ориентация страницы, масштаб, поля, колонтитулы)
· Присвоение имен ячейкам
· Подбор параметра
Задание 13: Расчет начислений и удержаний
Состав задания.
1. Назовите Лист1 именем «Начисления»
2. Оформите таблицу для расчета удержаний и сумм к выдаче:
2.1. Оформление строки (Заголовки столбцов):
2.1.1. Выделите ячейки А1:В1; D1; F1:I1 (несмежные диапазоны выделяются с нажатой клавишей CTRL) и измените ориентацию текста в ячейках с помощью команды «Главная - Выравнивание - Формат ячеек –– стр. Выравнивание – гр. Ориентация»
2.1.2. Для всех ячеек диапазона А1:К1выберите следующие параметры:
· Полужирное начертание шрифта
· Выравнивание по горизонтали и вертикали – по центру
· Отображение – переносить по словам
2.1.3. Подберите высоту 1-й строки, чтобы текст в ячейках располагался оптимально.
2.1.4. Для столбцов А:К выполните автоподбор ширины столбца
Главная – Ячейки – Автоподбор ширины столбца
2.2. Для ячеек А2:А3 установите формат даты
2.3. Для ячеек Е2:I3; J2:K2 используйте денежный формат с двумя десятичными знаками и обозначением «р.» - Главная - Число
2.4. Расчет Подоходного налога и Суммы к выдаче произведите по следующим формулам:
Подоходный = Процент*(Начислено – ИндВыч –КолДет*ДетВыч - Благотвор. – Строит.)
Т.к. при расчете облагаемой части начисленийпроцент (ячейкаK2)индивидуальный вычет (ячейка L2) и детский вычет (ячейка M2) являются постоянными величинами, то следует зафиксировать адреса этих ячеек присвоив этим ячейкам ИМЕНА (Процент, Инд.Выч Дет.Выч,)
К выдаче = Начислено - Подоходный - Благотворительность
После проведения расчетов таблица должна принять такой вид:
3. Введите данные по остальным работающим в режиме формы. Для этого:
3.1. Выделите построенную таблицу (A1:I7)
3.2. Выполните команду Кнопка «Office» | Параметры Excel | Настройка | Все команды | Форма
3.3. Получаем имена полей и окон редактирования, в которых можно вводить и редактировать текст.
Вычисляемые поля выводятся на экран без окон редактирования.
3.4. Нажмите кнопку «Добавить» и введите данные о начислениях и удержаниях Иванова И.П., в следующую запись формы – данные по Карцеву П.Р. и т.д. (см. таблицу ниже)
| 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 р. |
3.5. По окончании ввода записей «Закройте» форму, отсортируйте таблицу по Фамилия И.О. (Данные - Сортировка)– ваша таблица должна иметь следующий вид:
4. Познакомиться с помощью справочной системы MS Excel с темой Оглавления «Работа с книгами и листами – Форматирование листов – “Отображение и скрытие строк и столбцов”».
4.1. Скройте столбцы D:H. Таблица должна принять следующий вид:
4.2. Предъявите преподавателю результат работы.
4.3. Отобразите скрытые столбцы, т.е. приведите таблицу к начальному виду.
5. Выделите диапазон ячеек А2:I7, скопируйте его и вставьте в ячейку А8, а затем в ячейку А14.
5.1. В ячейках А8:А13 измените дату получения с 05.01.2008 на 05.02.2008, а в ячейках А14:А19 – на 05.03.2008.
5.2. В ячейках Е8: G19 произвольно измените суммы Начислений, Благотворительности и Строительства.
5.3. Проверьте, изменились ли автоматически суммы подоходного налога и «К выдаче»? Если нет, то заполните формулами с помощью маркера заполнения ячейки H8:I19.
Таблица в результате должна выглядеть примерно так:
6. Сгруппировать данные по каждому из сотрудников, упорядочив их по месяцам. Для этого:
6.1. Выделите диапазон ячеек А1:I19.
6.2. Выполните команду «Данные – Сортировка» и установите следующие параметры Сортировки:
В результате произведенной сортировки вы получаете следующую таблицу:
7. Для каждого из сотрудников вставить строку с денежными итогами за квартал. Для этого:
7.1. Оставьте выделенными ячейки А1:I19 и выполните команду «Данные – Структура – Промежуточные итоги», установив в открывшемся одноименном окне следующие параметры:
Эта команда позволяет подсчитывать промежуточные итоги по заданным группам записей.
7.2. Нажмите ОК. Таблица с промежуточными итогами должна иметь следующий вид: