Вычисляемые поля выводятся на экран без окон редактирования.

Электронные таблицы 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) и детский вычет (ячейка M 2) являются постоянными величинами, то следует зафиксировать адреса этих ячеек присвоив этим ячейкам ИМЕНА (Процент, Инд.Выч Дет.Выч,)

 

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

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

 

 

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. Нажмите ОК. Таблица с промежуточными итогами должна иметь следующий вид: