Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:

Лабораторная№8 по Excel 2010

.pdf
Скачиваний:
81
Добавлен:
08.03.2015
Размер:
310.31 Кб
Скачать

Задание №8. Расчет платежной ведомости

Задание Рассчитать платежную ведомость по алгоритму (1)-(9) (лекции) для сотрудников

отдела.

Порядок выполнения расчетов.

8.1Открыть (книгу) Excel.

8.2Оформить таблицу и заполнить данными диапазон ячеек A7:AG11 в соответствии с образцом (рис. 2.31).

8.3Произвести расчеты по учету использования дней месяца сотрудниками по образцу формулы (9)(лекции).

Действия:

В ячейку АН7 ввести формулу для расчета количества больничных дней (б/л)

сотрудника

=ЕСЛИ(СЧЁТЕСЛИ(С7:АG7;"б/л")=0;"";СЧЁТЕСЛИ(С7:АG7;"б/л"))

Формулой ячейки АН7 заполнить диапазон АН8:АН11

В ячейку AI7 ввести формулу для расчета количества дней без сохранения содержания

(б/с) сотрудника

=ЕСЛИ(СЧЁТЕСЛИ(С7:АG7;"б/с")=0;"";СЧЁТЕСЛИ(С7:АG7;"б/с"))

Формулой ячейки AI7 заполнить диапазон АI8:АI11

По аналогии с действиями п.8.3 произвести расчеты количества сверхурочно отработанных (с/у) дней в диапазоне AJ7:AJ11.

По аналогии с действиями п.8.3 произвести расчеты количества рабочих (Я) дней в диапазоне АК7:АК11.

8.4Присвоить листу имя Турд.

Результат представлен на рис. 2.31.

Рис. 2.1 Таблица Турд

8.5 Перейти на чистый лист книги, оформить таблицу табельной ведомости, заполнив данными все, за исключением диапазона D10:G14.

1

8.6 Присвоить диапазонам D10:D14, E10:E14, F10:F14, G10:G14 значения

соответствующие расчетным результатам таблицы Турд.

Действия:

В ячейку D10 ввести формулу =Турд!АК7 и заполнить ею диапазон D11:D14.

В ячейку Е10 ввести формулу =ЕСЛИ(Турд!АН7=””; 0; Турд!АН7) и заполнить ею диапазон Е11:Е14.

В ячейку F10 ввести формулу =ЕСЛИ(Турд!АI7=””; 0; Турд!АI7) и заполнить ею диапазон F11:F14.

В ячейку G10 ввести формулу =ЕСЛИ(Турд!АJ7=””; 0; Турд!АJ7) и заполнить ею диапазон G11:G14.

8.7Присвоить имя листу Тбв (сокращенная аббревиатура слов Табельная ведомость).

Образец таблицы представлен на рис. 2.32

Рис. 2.2 Таблица Тбв

8.8Перейти на чистый лист книги и оформить таблицу платежной ведомости, оставив пустыми столбцы для расчетных граф.

8.9Произвести расчеты по алгоритму (1) - (8) (лекции).

2

Действия:

• В ячейку В4 ввести формулу

=TБB!C10*T6B!D10+T6B!$B$3*T6B!C10*T6B!G10+T6B!C10*T6B!B10*T6в!Е10/100

Нажать клавишу <Enter>. Произойдет расчет по алгоритму (2)—(4) (лекции)

Формулой ячейки В4 заполнить диапазон ячеек В5:В8

В ячейку С4 ввести формулу

=T6B!C10*T6B!F10+T6B!$B$1*(B4+D4)+T6B!$B$4*(B4+D4)

Нажать клавишу <Enter>. Произойдет расчет по алгоритму (6)—(8) (лекции)

Формулой ячейки С4 заполнить диапазон ячеек С5:С8

В ячейку D4 ввести функцию, используя Мастер функции:

=ЕСЛИ(Тбв!D10+Тбв!G10-Тбв!$В$2>0;(Тбв!D10*Тбв!С10+Тбв!G10*Тбв!С10*

*Тбв!$В$3)*Тбв!$В$5;0)

Произойдет расчет по формуле (5)

Формулой ячейки D4 заполнить диапазон ячеек D5:D8

В ячейку Е4 ввести формулу: =B4+D4-C4-T6B!H10

Произойдет расчет по формуле (1)

Формулой ячейки Е4 заполнить диапазон ячеек Е5:Е8

8.10Произвести расчеты итоговых сумм по столбцам.

8.11Переименовать стандартное имя листа на имя Плв (сокращенная аббревиатура слов (Платежная ведомость).

Образец таблицы с результатами расчетов представлен в таблице (рис.2.33).

Рис. 2. 3 Таблица Плв

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

Использование массива формул

Использование массивов формул рекомендуется применить в самостоятельной работе

3

задания №8 для расчетов в таблице платежной ведомости. Создавая массив формул, надо

помнить следующее:

Форма и размер диапазона, в которой помещаются результаты, должны повторять форму и размер диапазонов, содержащих исходные данные.

Массив формул заключается в фигурные скобки, но не надо их вводить, Excel сделает это автоматически.

Построение массива формул нельзя завершать нажатием клавиши <Enter>. Следует использовать вместо нее комбинацию клавиш — <Shift+Ctrl+Enter>.

Ниже демонстрируется технология выполнения задания на примере создания массива формул для расчета графы Сумма начислено в таблице Платежная ведомость.

Порядок выполнения расчетов:

1.Добавьте новый лист. Дайте имя листу Плв2. Скопируйте таблицу Плв на лист Плв2. В

скопированной таблице очистить блок ячеек В4:Е8 от содержимого по схеме Выделить

блок►Главная►Редактирование►Очистить►Очистить содержимое.

2.Перейти на лист Тбв и выбрать команду Формулы►Определенные имена►Присвоить имя. Появится окно Создание имени.

3.В поле Имя ввести СумНач, затем перейти в поле Диапазон и щелкнуть на правой кнопке с красной стрелкой. Окно уменьшится до размеров поля Создание имени-

Диапазон.

4.Выделить первый диапазон ячеек С10:С14, на которую ссылается формула (смотреть пункт предыдущих расчетов для ячейки В4) и задать знак операции умножения «*».

Выбрать второй диапазон ячеек D10:D14, на которую ссылается формула и задать следующий знак операции сложения «+». Выбрать третий диапазон ячеек и т.д.,

действовать по аналогии для получения всей формулы расчета для этой графы.

Одновременно в поле Диапазон появляется отображение массива формул. Ниже представлен окончательный вид массива формул с именем СумНач.

=TБB!$C$10:$C$14*TБB!$D$10:$D$14+TБB!$B$3*TБB!$C$10:$C$14*TБB!$G$10:$G$14+TБ

B!$C$10:$C$14*TБB!$B$10:$B$14*TБB!$E$10:$E$14/100

5. Нажать комбинацию клавиш <Shift+Ctrl+Enter>, чтобы сообщить Excel о том, что вводится не обычная формула, а массив формул.

В ответ Excel заключит формулу в фигурные скобки {}.

4

6. Использовать полученное имя для расчетов.

Действия:

Перейти на лист Плв2 и выделить диапазон ячеек В4:В8.

Выбрать команду Формулы►Определенные имена►Использовать в формуле.

В окне списка имен выбрать имя СумНач.

После появления в ячейке В4 имени вставляемого диапазона =СумНач, нажать комбинацию клавиш <Shift+Ctrl+Enter>.

В итоге, в диапазоне В4:В8 появятся результаты расчета (рис.2.33), а в окне ввода формул будет отображаться имя массива формул в виде {=СумНач}.

Для выполнения расчетов по остальным графам таблицы Плв2 действовать по аналогии с действиями пунктов 2-6. Имена для массивов формул придумать самостоятельно и, в

итоге, Вы должны получить вид таблицы Плв2 совпадающую с видом таблицы Плв (рис.

2.33).

Самостоятельная работа.

1.Добавить две позиции начислений:

районный коэффициент 20%,

 

3

 

 

 

 

 

 

 

Y5 Kф3 Yi ,

г де Kф3

20% ;

 

 

 

 

i 1

 

 

 

 

 

 

 

выслуга,

 

 

 

 

 

 

 

 

 

K

ф4

0%,

если n4к

1,

 

 

 

ф4

10%,

если 1 n4к

3,

3

K

Y6 Kф4 Yi ,

г де

 

20%,

если 3 n4к

7,

i 1

Kф4

 

K

ф4

30%,

если n

4к

7,

 

 

 

 

 

 

n4k - количество отработанных лет.

2.Добавить позицию удержаний:

профсоюзный взнос, если работник состоит в профсоюзной организации,

N

X 4 m4 /100 * Y (i), г де m4 1%.

i 1

5