Использование фильтра для выборки заданных данных

8. Cкопировав таблицу вставить ее на 2 строки ниже – ячейки A30:I55 и сброcить промежуточные итоги – кнопка Убрать все.

9. В полученной копии необходимо получить список только тех сотрудников, которые отчисляли деньги на строительство жилья. Для этого:

   1. Установить курсор в строку заголовков копии таблицы.

   2. Выбрать команду "Данные – Фильтр – Автофильтр". У каждой ячейки заголовка появится кнопка, позволяющая вводить критерии фильтра. По этой команде можно вывести на экран только те записи, которые удовлетворяют выбранным условиям фильтра, без удаления остальной информации.

   3. Раскрыть выпадающий список ячейки "Строительство жилья". Выбрать команду «Условие». В появившемся диалоговом окне установить параметры "больше" и "0".

   4. Нажмите ОК

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

Представьте преподавателю результаты работы.

5. Для того, чтобы снова увидеть перечень всех сотрудников, нужно в выпадающем списке фильтра «Строительство жилья» выбрать критерий - Снять фильтр Строительство жилья.

Создание итоговой таблицы

10. Назовите Лист 2 именем «ИТОГИ»

11. Начиная с 3-й строки, оформите таблицу для отображения итоговых начислений сотрудников за квартал:

12. В ячейке E4 вставьте ссылку на итоговые начисления Авдеева А.С. за квартал на листе Начисления.

    1. В ячейку E4 на листе ИТОГИ вставьте знак «=»

    2. Перейдите на лист Начисления и выделите ячейку E5 с итоговыми значениями Авдеева А.С.

Нажмите клавишу ENTER и перейдите на лист ИТОГИ.

13. Далее с помощью маркера заполнения скопируйте ссылки на итоговые результаты вправо по строке 4 до столбца I.

14. Тем же способом перенесите итоговые результаты остальных работников.

Построение диаграммы

Постройте диаграмму, отражающую суммы, полученные сотрудниками.

Задание 14: Составление штатного расписания, подбор параметра

Предлагается решить следующую задачу:

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

Имеются следующие ограничения:

1. Для эффективной работы фирме необходимы следующие сотрудники:

   Должность	Минимальное количество	Среднее количество	Максимальное количество
   Директор	1	1	1
   Заместитель директора	1	1	2
   Начальник отдела	2	3	4
   Бухгалтер	1	1	1
   Адвокат	3	4	5
   Детектив	5	6	7
   Охранник	8	9	10

2. Фонд заработной платы всех сотрудников равен 650 000 руб.

3. Минимальный размер заработной платы становится базовой переменной, от величины которой зависит зарплата всех остальных сотрудников.

4. Коэффициент А показывает сколько минимальных окладов включает рассчитываемый оклад данного сотрудника.

5. Коэффициент В – показывает в рублях доплату к окладу соответствующего сотрудника.

6. Расчет распределения фонда оплаты труда между сотрудниками в штатном расписании представляет собой математическую модель, в которой зарплата всех сотрудников выражена через минимальную зарплату, т.е. коэффициент А с учетом коэффициента В.

ОКЛАД = МРОТ *A + B

где: МРОТ – минимальная оплата;

A и B - коэффициенты, которые определены для каждой должности.

Например, пусть минимальный оклад равен 5000 руб., а для оклада детектива установлены коэффициенты: А=2, В=1000. Тогда оклад детектива рассчитывается так ОКЛАД=2*5000+1000=11000 (руб.)

Задав количество человек на каждой должности, можно составить уравнение:

N1(A1C+B1)+N2(A2C+B2)+...+N7(A7C+B7)=250000

где N1 - количество охранников; N2 - количество детективов и т.д...

A1...A7 и B1...B7 – заранее определенные коэффициенты для каждой должности.

Число сотрудников (N) для каждой должности может колебаться. Поэтому необходимо рассчитывать суммарный фонд зарплаты, исходя из минимального, среднего и максимального количества работающих.

Требуется ответить на вопрос:

Каким должен быть минимальный размер оплаты труда, чтобы

Фонд оплаты труда всех сотрудников составил 650 000 рублей?

MS Excel с помощью команды «Подбор параметра» позволяет автоматически получить суммарный фонд в 650000 руб., подобрав минимальный размер оплаты при заданных коэффициентах А и В и заданной численности - N сотрудников.

Состав задания:

1. Назовите Лист 3 именем «Подбор»

2. Создайте заготовку таблицы по приведенному ниже образцу:

• Коэффициенты А и В в таблице даны для примера, определите сами.

• В ячейку G2 занесите начальное значение минимальной зарплаты (можно определить произвольно).

• Для ячеек D3:D9; F3:F10; G3$ Н16:Н18 установите денежный формат.

3. В столбце D вычислите заработную плату для каждой должности:

4. Ячейке G2 присвойте имя МРОТ (Минимальный размер оплаты труда).

(Формулы – Определенные имена – Присвоить имя.. )

5. В ячейку D3 занесите формулу =A3*МРОТ+B3.

5.1 Скопируйте формулу из ячейки D2 в ячейки D4:D9 с помощью маркера заполнения.

6. В столбце F вычислите заработную плату всех сотрудников данной должности:

6.1 В ячейку F3 занесите формулу =D3*E3 (зарплата  количество сотрудников).

6.2 Скопируйте формулу из ячейки F3 в ячейки F4:F9 с помощью маркера заполнения.

7. Определите суммарный месячный Фонд оплаты труда.

8. Добавьте на Панель быстрого доступа команду Подбор параметра (Настройка панели быстрого доступа – Все команды )

9. Составьте штатное расписание с использованием функции автоматизации расчетов Подбор параметра, указав режим