Копирование таблицы Сотрудники и организация вычислений в ней

2.1. Переименуйте Лист2 файла Фамилия_3_n.xls на Оклады.

Относительные и абсолютные ссылки на ячейки 31

2.2. Скопируйте на этот лист одноименную таблицу Сотрудники, созданную в лабораторной работе № 1 (файл Фамилия_1_n.xls).

2.3. Введите в ячейку I1 текст "Стаж на предприятии" в качестве заголовка столбца и задайте форматирование аналогичное предыдущим ячейкам "шапки" таблицы. В ячейках I2:I11 создайте формулу для вычисления стажа сотрудников на этом предприятии (формат ячеек Числовой).

2.4. Рядом в ячейках О1:Р7 сформируйте таблицу с названиями должностей в алфавитном порядке, встречающимися в столбце F, и размерами окладов для этих должностей. Задайте границы и формат ячеек подобно таблице слева. Формат ячеек Р2:Р7 – Денежный с двумя десятичными знаками c обозначением денежной единицы в тенге.

2.5. После столбца Стаж на предприятии создайте столбец с назначением оклада, значение которого для каждого сотрудника должно выбираться согласно занимаемой должности по «тарифной сетке» из ячеек О1:Р7. Результат выполнения функции выборки надо умножить на значение занимаемой сотрудником ставки, поскольку некоторые из сотрудников работают не на целую ставку.

2.6. Для определения количества сотрудников в каждом отделе сформируйте таблицу с расчетами в ячейках О10:Р14. Для этого выполните такие действия:

1) для каркаса таблицы задайте границы ячейкам О10:Р14 и введите:

– в ячейку О10 – Отдел;

– в ячейку О11 – Контроля;

– в ячейку О12 – Реализации;

– в ячейку О13 – Снабжения;

– в ячейку О14 – Итого;

– в ячейку Р10 – Количество сотрудников;

2) в ячейке Р11 сформируйте формулу количественного состава каждого из отделов (функция СЧЕТЕСЛИ);

3) выполните автозаполнение этой формулы на ячейки Р12:Р13;

4) в ячейке Р14 вычислите суммарное количество всех сотрудников.

2.7. Аналогичным образом в ячейках Q2:Q7 вычислите количественный состав для каждой должности с помощью функции СЧЁТЕСЛИ. Для этого подпишите ячейку Q1 – "Количество должностей" и задайте ее формат. Объедините ячейки О8 и Р8, впишите в них "Итого", задайте выравнивание текста по правому краю и выделите шрифт курсивом.

В ячейке Q8 вычислите суммарное количество должностей.

2.8. В ячейках R1:R7 вычислите количество оплачиваемых ставок, используя функцию СУММЕСЛИ. В ячейке R8 вычислите суммарное количество ставок.

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

2.9. Предположим, что финансовые возможности предприятия позволяют увеличить штатные оклады сотрудников на 8,5 %. При перерасчете окладов необходимо учитывать тот факт, что размер оклада должен выражаться целым числом, то есть не содержать копеек. Для этого запишите в ячейку О16 текст "Индекс", а в ячейку Р16 – число 1,085.

В столбце К вычислите новое значение оклада, округлив его значение с помощью функции ОКРУГЛ.

2.10. Вычислите в столбце L размер надбавки за выслугу лет, которая начисляется по такому алгоритму: более 3 лет – 10%; более 10 лет – 20%; более 20 лет – 30%.

Но для этого в ячейках T1:U5 сначала надо создать таблицу с размерами надбавок за выслугу лет.

Для удобства и наглядности надо скрыть столбцы О:R.

Для вычисления размера надбавки по указанному алгоритму в Excel надо использовать три вложенные друг в друга функции ЕСЛИ. Так окончательный вид формулы в ячейке L2:

=ЕСЛИ(I2<3; $U$2; ЕСЛИ(I2<10; $U$3*J2; ЕСЛИ(I2<20; $U$4*J2; $U$5*J2)))

2.11. После столбца Надбавка за стаж в столбце М создайте вычисления премии для сотрудников из отдела реализации, используя функцию ЕСЛИ (формат ячеек – Денежный с двумя десятичными знаками) (рис. 3.3).

2.12. В столбце N вычислите суммарные значения трех столбцов: Оклад с индексацией, Надбавка за стаж и Премия для отдела реализации. Задайте форматирование столбца подобно предыдущим ячейкам таблицы (рис. 3.3).

Рис. 3.3. Вид таблиц на листе Сотрудники

2.13. Выпишите в свой протокол все пять формул, созданные Вами в ячейках I2, J2, K2, L2 и M2 на листе Сотрудники.

2.14. Защитите данные листа Сотрудники.

2.15. Удерживая клавишу [Ctrl], выделите диапазоны ячеек А1:С11, Е1:Е11, Н1:Н11, К1:N11, скопируйте их на новый лист.

Скопируйте всю таблицу с этого листа в документ MS Word с помощью команды Специальная вставка  Связать  Лист Microsoft Excel (объект).

Задайте альбомную ориентацию страниц для документа Word и сохраните его с именем Фамилия_3_n.doc. Закройте этот файл.

2.16. Сохраните файл Фамилия_3_n.xls и закройте его.

2.17. Устно проработайте ответы на все контрольные вопросы и письменно ответьте только на свой контрольный вопрос, согласно номеру варианта.__

Контрольные вопросы

1) Какие типы адресации ячеек используются в формулах MS Excel и чем они отличаются?

2) В каких случаях целесообразно использовать абсолютную ссылку на ячейку?

3) Какое предназначение функции РАНГ? Какая ее синтаксическая формула? Приведите пример применения функции.

4) Для чего используется функциональная клавиша [F4] при создании формул в MS Excel?

5) Что такое абсолютный и относительный адрес ячеек, входящих в формулу?

6) Когда следует использовать относительные, а когда – абсолютные ссылки на ячейки?

7) В ячейку F13 ввели формулу =F12/$B$4. Затем эту формулу скопировали в ячейку F16. Какая формула содержится в ячейке F16?

8) Каким образом задается абсолютная ссылка на ячейку?

9) Какие ссылки называются смешанными? Приведите примеры.

10) В ячейку В7 ввели формулу =(А6+А7)*$D$4. Затем эту формулу скопировали в ячейку F7. Какая формула содержится в ячейке F7?

11) В ячейку D5 ввели формулу =$A5+B$5. Затем эту формулу скопировали в ячейку D2. Какая формула содержится в ячейке D2?

12) Что происходит во время копирования формул в MS Excel?

13) Выберите и объясните, какие из ссылок на ячейки ошибочны: 1) МЕ$55, 2) $ЕМ55, 3) CC20202, 4) Б5, 5) A70000, 6) VI65536, 7) $$CW200, 8) C$$11.

14) В ячейку С1 ввели формулу =$A1+B$1. Затем эту формулу скопировали в ячейку С2. Какая формула содержится в ячейке С2?

15) В ячейку С1 ввели формулу =$A1+B$1. Затем эту формулу скопировали в ячейку D1. Какая формула содержится в ячейке D1?

16) В ячейку С1 ввели формулу =$A1+B$1. Затем эту формулу скопировали в ячейку D2. Какая формула содержится в ячейке D2?

17) Объясните допущенные в формулах ошибки: 1) =СУММЕСЛИ(В2:В5); 2) =СРЕДНЕЕ(E7); 3) =СЧЁТЕСЛИ(">0"; В2:В5); 4) = РАНГ(E1:E7).

18) В ячейку С7 ввели формулу =$А7+В$7. Затем эту формулу скопировали в ячейку С8. Какая формула содержится в ячейке С8?

19) В ячейку С5 ввели формулу =A$5+$B5. Затем эту формулу скопировали в ячейку D5. Какая формула содержится в ячейке D5?

20) Выберите и объясните, какие из ссылок ошибочны: 1) W222, 2) СЕ$55, 3) СБ5, 4) $$S200, 5) A10000, 6) IV65536, 7) JV$70000, 8) DD$$11.

21) Какое предназначение функции СЧЁТЕСЛИ? Какая ее синтаксическая формула? Приведите пример применения функции.

22) Объясните допущенные в формулах ошибки: 1) =РАНГЕСЛИ(E7:E11); 2) ЕСЛИ(">0";1;0); 3) =ЕСЛИ(В2:В5); 4) =MINЕСЛИ(">0"; В2:В5).

23) Чем отличаются абсолютные ссылки от относительных?

24) Чем отличаются ссылки А1, $A$1, А$1 и $A1?

25) Выберите и объясните, какие из ссылок ошибочны: 1) МЕ$55, 2) $ЕМ55, 3) 55ЕМ, 4) SS202, 5) СA99000, 6) $IV$65536, 7) $$Z777, 8) Z$$777.

26) Чем отличаются функции СЧЁТЕСЛИ и СУММЕСЛИ?

27) Как ввести одно и то же значение в несколько ячеек одновременно?

28) В ячейке А1 записано число 5 (остальные ячейки пусты). В ячейку С1 ввели формулу =$A$1+А1. Затем эту формулу скопировали в ячейку С2. Какая формула и какое значение содержится в ячейке С2?

29) В ячейке А1 записано число 10 (остальные ячейки пусты). В ячейку В1 ввели формулу =$A$1*А1. Затем эту формулу скопировали в ячейку В2. Какая формула и какое значение содержится в ячейке В2?

30) В ячейке А1 записано число 100, а в ячейку В1 – число 200 (остальные ячейки пусты). В ячейку D1 ввели формулу =$A$1/В1. Затем эту формулу скопировали в ячейку D2. Какая формула и какое значение содержится в ячейке D2?__

ПРАКТИЧЕСКАЯ РАБОТА № 4