Будь умным!


У вас вопросы?
У нас ответы:) SamZan.ru

ЛАБОРАТОРНАЯ РАБОТА 1 ЗАДАНИЕ 1

Работа добавлена на сайт samzan.ru: 2015-07-10


ЛАБОРАТОРНАЯ РАБОТА № 1

ЗАДАНИЕ 1.

 Перед вами стоит задача рассчитать заработную плату работников организации. Форма оплаты – оклад. Расчет необходимо оформить в виде таблиц. При расчете следует использовать данные таблицы 2

Оклад работника зависит от его квалификации (разряда). Эта зависимость должна быть представлена в виде соответствующей таблицы.

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

Выполнение.

1. Формирование таблиц.

1. Введите заголовок таблицы. Для этого установите указатель в ячейку A1. Введите текст: «Лицевой счет». Затем выделите диапазон ячеек A1:J1, выберите Объединить и поместить в центре.

2. Установите для диапазона ячеек шапки таблицы режим переноса текста при достижении правого края и выровняйте его по центру. Для этого:

  •  выделите диапазон ячеек A2:J2;
  •  выполните команду Главная/Выравнивание/Перенос текста;

3. Выполните команды Главная/Выравнивание/Выровнять по середине и Главная/Выравнивание/По центру.

4. Введите текст шапки таблицы в соответствии со следующим правилом:

Текущая клетка

Набираемый текст

A2

Таб. номер

B2

Фамилия

C2

Разряд

D2

Должность

E2

Отдел

F2

Кол-во льгот

G2

Факт. время (дн.)

H2

Начислено з/п

I2

Удержано

J2

З/п к выдаче

5. Разлинуйте таблицу (диапазон ячеек A2:J9).

6. Введите заголовок следующей таблицы. Установите указатель в ячейку B11. Введите текст: «Разрядная сетка».

7. Заполните шапку таблицы в соответствии с приведенными ниже рекомендациями:

Текущая клетка

Набираемый текст

B12

Разряд

C12

Оклад

8. Разлинуйте таблицу (диапазон ячеек В12:C30).

9. Введите заголовок следующей таблицы. Установите указатель в ячейку Е11. Введите текст: «Справочник по исполн. листам», нажмите Enter.

10. Заполните шапку таблицы в соответствии с приведенными ниже рекомендациями:

Текущая клетка

Набираемый текст

Е12

Таб. номер

F12

% удерж.

11. Разлинуйте таблицу (диапазон ячеек E12:F15).

12. Введите заголовок следующей таблицы. Для этого установите указатель в ячейку L1. Введите текст «Справочник работников», нажмите Enter. Затем выделите диапазон ячеек L1:P1 и выполните команду Объединить и поместить в центре.

13. Установите для диапазона ячеек шапки таблицы режим переноса текста при достижении правого края для диапазона L2:P2.

14. Заполните таблицу в соответствии с приведенными ниже рекомендациями:

Текущая клетка

Набираемый текст

L2

Табельный номер

M2

Фамилия

N2

Должность

O2

Отдел

P2

Дата поступления на работу

15. Разлинуйте таблицу (диапазон ячеек L2:P9).

16. Введите заголовок следующей таблицы. Для этого установите указатель в ячейку A32. Введите текст «Ведомость начислений», нажмите Enter.

17. Установите для диапазона ячеек шапки таблицы режим переноса текста при достижении правого края для диапазона А33:D33.

18. Установите ширину столбца А равную 10. Для этого:

  •  установите указатель в ячейку А33;
  •  выполните команду Главная/Ячейки/Формат/Ширина столбца;
  •  в окне Ширина столбца введите значение 10 и нажмите ОК.

19. В ячейку А33 введите границу, разделяющую ячейку по диагонали (слева вниз направо).

20. Заполните шапку таблицы в соответствии с приведенными ниже рекомендациями:

Текущая клетка

Набираемый текст

А33

Начисл. таб., пробелы, номер

В33

По окладу

С33

Премия

D33

Всего

21. Разлинуйте таблицу (диапазон ячеек А33:D40).

22. Введите заголовок следующей таблицы. Для этого установите указатель в ячейку A42. Введите текст «Ведомость удержаний», нажмите Enter.

23. Установите для диапазона ячеек шапки табл. 4 режим переноса текста при достижении правого края для диапазона А43:Е43.

24. В ячейку А43 введите границу, разделяющую ячейку по диагонали.

25. Заполните шапку таблицы в соответствии с приведенными ниже рекомендациями:

Текущая клетка

Набираемый текст

А43

Удерж. таб., пробелы, номер

В43

Подоходный налог

С43

Пенсионный налог

D43

Исполнительные листы

E43

Всего

26. Разлинуйте таблицу (диапазон ячеек А43:Е50).

2. Заполнение таблиц исходными данными

1. Заполните таблицу «Лицевой счет» на основании данных, приведенных в табл. 1

Таблица 1

Лицевой счет 

Таб. номер

Фамилия

Разряд

Долж-ность

Отдел

Кол- во льгот

Факт. время (дн.)

Начис- лено з/п

Удер- жано

З/п к вы-даче

1001

13

1

23

1002

17

3

23

1003

11

2

17

1004

5

0

8

1005

12

3

22

1006

7

2

23

1007

3

1

20

2. Заполните таблицу «Справочник работников» на основании данных, приведенных в табл. 2

Таблица 2

Справочник работников 

Таб. номер

Фамилия

Должность

Отдел

Дата поступления на работу

1001

Алексеева

Нач. отдела

1

15.04.2005

1002

Иванов

Ст. инженер

2

01.12.1999

1003

Петров

Инженер

2

12.01.2001

1004

Сидоров

Экономист

1

22.06.2010

1005

Кукушкин

Секретарь

1

24.04.1987

1006

Павленко

Экономист

2

12.12.1980

1007

Давыдова

Инженер

1

17.08.2008

3. Заполните числами колонку B в таблице «Разрядная сетка», используя функцию автозаполнения. Для этого:

  •  введите «1» в ячейку B13; введите «2» в ячейку В14;
  •  выделите две ячейка и установите указатель в ячейку B14 на маркер в правом нижнем углу. Указатель мыши примет форму креста;
  •  протащите указатель по диапазону B14:B30. Диапазон ячеек B14:B30 заполнился числами от 1 до 18.

4. Заполните колонку С в таблице «Разрядная сетка». Для этого:

  •  введите «10000» в ячейку C13.
  •  выделите диапазон ячеек С13:С30;
  •  выполните команду Гланая/Редактирование(Правка)/Заполнить /Прогрессия(Ряд);
  •  в окне выберите Расположение – по столбцам, Тип – арифметическая (линейная), в поле ввода Шаг введите 500;

5. Заполните таблицу «Справочник по исполнительным листам» на основании данных, приведенных в табл. 3

   Таблица 3

Справочник

по исп. листам

Таб. номер

% удерж.

1002

25

1005

20

1007

5

6. Заполните колонку А в формах таблиц «Ведомость начислений» и «Ведомость удержаний», скопировав в нее табельные номера из таблицы «Лицевой счет».

7. Введите дополнительную информацию:

Текущая клетка

Вводимые данные

E20

Размер мин. з/п

F21

6000

E22

Кол. раб. дней в месяце

F23

23

E24

% премии

F24

Меньше 5 лет

G24

От 5 до 10 лет

H24

Больше 10 лет

F25

 10

G25

 15

H25

 25

E26

Размер прожит. минимума

F27

5000

3. Ввод в таблицу формул

1. Установите курсор в клетку B3 и введите формулу заполнения фамилии на основании данных «Справочника работников» (ссылки на ячейки и диапазоны ячеек вводите, выделяя ячейки мышью, для ввода знаков $ нажимайте F4 после ввода каждого диапазона или ссылки, по окончании ввода формулы нажмите Enter):

=ВПР(А3;$L$3:$P$9;2;ложь)

Знак $ фиксирует координаты ячеек и диапазонов (при копировании формул они не изменяются).

Функция ВПР ищет в первом столбце таблицы искомое значение, затем перемещается по найденной строке к соответствующей ячейке и возвращает ее значение.

ВПР(искомое_значение;табл_массив;номер_столбца;интервальный_просмотр)

Искомое_значение – это значение, которое должно быть найдено в первом столбце таблицы. Искомое_значение может быть значением, ссылкой или текстовой строкой.

Табл_массив – это таблица с информацией, в первом столбце которой ищется искомое значение.

Номер_столбца – это номер столбца в таблице, из которого должно быть взято соответствующее значение.

Интервальный_просмотр – это логическое значение, которое определяет, нужно ли искать точное или приближенное значение. Если этот аргумент имеет значение ИСТИНА или опущен и точное значение не найдено, то возвращается приблизительно соответствующее значение, а именно: наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное значение. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

2. Скопируйте формулу определения фамилии в диапазон ячеек B4:B9. Для этого выделите ячейку, содержащую копируемую формулу, а затем перетащите маркер заполнения по диапазону, который нужно заполнить. Аналогично заполните диапазоны ячеек D3:D9 и E3:E9 (столбцы «Должность» и «Отдел») на основании данных «Справочника работников»)

3. Рассчитайте зарплату по окладу по формуле: ЗП окл = ОКЛ * ФТ/Т;

где ОКЛ – оклад работника в соответствии с его разрядом;

ФT – фактически отработанное время в расчетном месяце (дн.);

Т – количество рабочих дней в месяце.

Установите курсор в клетку В34 и наберите на клавиатуре формулу расчета начислений по окладу (текст формулы вводите без переноса в одну строку):

=ВПР(ВПР(A34;$А$3:$J$9;3;ложь);$В$13:$С$30;2; ложь)*ВПР(A34;$А$3:$J$9;7;ложь) /$F$23

Расшифруйте формулу письменно.

4. Скопируйте формулу начисления ЗП по окладу в диапазон В35:В40.

5. В ячейку С34 введите формулу расчета премии. Размер премии зависит от выслуги лет, определяемой как разность между текущей датой и датой поступления на работу. Соответственно формула для расчета премии будет иметь следующий вид:

=ЕСЛИ((СЕГОДНЯ()-ВПР(A34;$L$3:$P$9;5;ЛОЖЬ))/365<5;

B34*$F$25/100;ЕСЛИ((СЕГОДНЯ()-ВПР(A34;$L$3:$P$9;5;

ЛОЖЬ))/365<10;B34*$G$25/100;B34*$H$25/100))

Расшифруйте формулу письменно.

Функция ЕСЛИ возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)

Логическое_выражение – это любое выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Значение_если_истина – это значение, которое возвращается, если логическое_выражение имеет значение ИСТИНА. Если логическое_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА. Значение_если_истина может быть другой формулой.

Значение_если_ложь – это значение, которое возвращается, если логическое_выражение имеет значение ЛОЖЬ. Если логическое_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь может быть другой формулой.

Скопируйте формулу в ячейки С35:С40.

6. В ячейку D34 введите формулу расчета начисленной ЗП:

=B34+C34

Скопируйте формулу в ячейки D35:D40.

7. В ячейку H3 самостоятельно введите формулу для нахождения начисленной ЗП из таблицы «Ведомость начислений». Скопируйте формулу в ячейки H4:H9.

44. Рассчитайте величину подоходного налога: У пн = (ЗП - МЗП * Л ) * 0,12; где МЗП – минимальная зарплата; Л – количество льгот.

В ячейку B44 введите формулу расчета подоходного налога:

=(ВПР(A44;$A$3:$J$9;8;ЛОЖЬ)-$F$21*ВПР(A44;$A$3:$J$9;6; ЛОЖЬ))*0,12

Расшифруйте формулу письменно.

Скопируйте формулу в ячейки B45:B50.

8. Рассчитайте величину пенсионного налога: У пф = ЗП * 0,01.

В ячейку С44 введите формулу расчета пенсионного налога:

=ВПР(А44;$A$3:$J$9;8;ложь)*0,01

Расшифруйте формулу письменно.

Скопируйте формулу в ячейки С45:С50

9. Рассчитайте величину удержания по исполнительным листам: Уил = (ЗП - Упн ) * %ИЛ; где %ИЛ – процент удержания по исполнительным листам.

В ячейку D44 введите формулу расчета удержания по исполнительным листам:

=ЕСЛИ(ЕНД(ВПР(А44;$Е$13:$F$15;2;ложь));0;(ВПР(A44;$A$3:$J$9;8;ложь)-B44)*ВПР(А44;$E$13:$F$15;2;ложь)/100)

Расшифруйте формулу письменно.

Функция ЕНД проверяет значение ячейки.

ЕНД(значение)

Если значение ячейки ошибка #Н/Д, то функция возвращает значение ИСТИНА, в противном случае – ЛОЖЬ.

Скопируйте формулу в ячейки D45:D50.

10. В ячейку Е44 введите формулу расчета общей суммы удержания: =B44+C44+D44

Скопируйте формулу в ячейки E45:E50.

11. В ячейку I3 самостоятельно введите формулу для нахождения общей суммы удержания из таблицы «Ведомость удержаний». Скопируйте формулу в ячейки I4:I9.

12. Вычислите сумму к выдаче с помощью формулы массива {=H3:H9 - I3:I9}. Для этого выделите блок ячеек J3:J9, нажмите клавишу «=», выделите блок H3:H9, нажмите клавишу «-», выделите блок I3:I9, нажмите клавиши Ctrl +Shift+ Enter.

13. Используя автосуммирование, рассчитайте итоги в табл.1. Для этого в ячейку А10 введите текст «Итого:», установите указатель в ячейку H10 и выполните команду Автосумма. Если выбранный командой блок окажется верным – H3:H9, нажмите Enter. В противном случае выделите блок H3:H9 и нажмите Enter.

Повторите указанные действия для ячеек I10, J10.

4.Получение итоговых данных

1. Рассчитайте сумму начисленной заработной платы по отделу 1. Для этого в ячейку I12 введите «Итого по отделу 1». В ячейку J12 введите формулу:

=СУММЕСЛИ(Е3:E9;1;J3:J9)

Функция СУММЕСЛИ суммирует ячейки, отвечающие заданному критерию.

СУММЕСЛИ(диапазон;условие;диапазон_суммирования)

Диапазон – определяет интервал вычисляемых ячеек.

Условие – задает критерий в форме числа, выражения, который определяет, какая ячейка будет суммироваться.

Диапазон_суммирования – фактические ячейки для суммирования. Суммируются те ячейки диапазона, которые удовлетворяют условию. Если диапазон суммирования отсутствует, то суммируются ячейки аргумента «диапазон».

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

3. Рассчитайте количество работников отдела 1. Для этого в ячейку I14 введите «Работает в 1 отделе». В ячейку J14 введите формулу:

=СЧЕТЕСЛИ(Е3:E9;1)

Функция СЧЕТЕСЛИ подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному критерию.

СЧЕТЕСЛИ(диапазон;критерий)

Диапазон – определяет интервал, в котором подсчитывается количество ячеек.

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

4. Самостоятельно введите в ячейку J15 формулу для расчета количества работников отдела 2.

5. Аналогично рассчитайте суммарную начисленную заработную плату и количество работников по каждой должности.

5. Использование условного форматирования

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

Рассмотрим возможности условного форматирования с гистограммой, отображающей пропорциональное значение величин в диапазоне ячеек G3: G10:

  •  выделить диапазон ячеек G3: G10;
  •  на вкладке Главная нажать Условное форматирование выбрав Гистограммы – Зеленая гистограмма.

Используем возможности условного форматирования для диапазона ячеек J3:J9. Если значение суммы З/П к выдаче меньше прожиточного минимума, то необходимо значение вывести красным цветом с двойным подчеркиванием, иначе значение выводить синим цветом.

Для этого необходимо:

  •  выделить диапазон ячеек J3:J9;
  •  на вкладке Главная нажать Условное форматирование;
  •  в диалоговом окне команды Условное форматирование выбрать Правила выделения ячеек и выбрать Другие правила;
  •  в диалоговом окне Создание правила форматирования выбрать Значение ячейки «меньше» и, нажав кнопку Свернуть диалоговое окно, ввести ссылку на ячейку $F$27 щелчком мыши;
  •  Цвет выбрать «красный»;
  •  нажать ОК;
  •  в диалоговом окне команды Условное форматирование выбрать Правила выделения ячеек, затем выбрать Другие правила и создать еще одно условие – операцию «больше или равно», со ссылкой на ячейку $F$27;
  •  Цвет выбрать синий;
  •  дважды нажать OK.

2. Для диапазона ячеек G3:G9 установите следующие форматы: если работник проработал целый месяц, вывести значение зеленым цветом, если проработал меньше месяца – вывести значение оранжевым цветом с одинарным подчеркиванием.

3. Сохраните результаты лабораторной работы.

ЗАДАНИЕ 2.

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

Изучите синтаксис функций ПЛТ, КПЕР, СТАВКА, ПС и заполните свободные поля.




1. статья будет полезна как опытным игрокам так и тем кто впервые решил принять участие в турнирных сражениях
2. реферат дисертації на здобуття наукового ступеня кандидата технічних наук Харків 2001 Д
3. і. Махові й стернові пера редуковані або перетворилися на прикрасу.
4. Оценка стоимости бизнеса ОАО «Аэрофлот»
5. ТЕМА при которой параллельно существовали места заключения для политических противников режима и для обычн
6. Предмет и метод экономической теории2
7. Персей
8. Юриспруденция Рецензенты- кафедра военной администрации административного и финансового права ю
9. РЕФЕРАТ Вибір обладнання технологічного оснащення та інструменту при розробці технологічного процесу виг
10. Путешествие в страну знаний Разработала музыкальный р
11. Ограниченные вещные права
12. ТЕМА- Обслуживание вводавывода
13. Теория коллективного воспитания в историческом контексте
14. Тема 1. Предмет и метод политической экономии 1.html
15. Советская молодежь ~ газета о жизни молодежи Курсовая работа по истории отечественной журналистики
16. технического уровня информационной безопасности
17. Тема 4- Роль темпераменту в діяльності людини
18. Жесткие переговоры- как избежать поражения
19. Роспечать и магазина цветов Аленький цветочек 18-40 Факультеты Евразии и Востока и
20. Работа таможенных органов с обращениями граждан