Практическая работа 9

Работа с базами данных в Microsoft Excel

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

Заполнить таблицу произвольными данными (10 строк); в столбце Отдел использовать 3−4 названия (например, бухгалтерия, отдел кадров, транспортный отдел, конструкторский отдел), в столбце Должность использовать 5−6 названий (например, техник, инженер, экономист, водитель и т. д.).

Добавить к таблице вычисляемые столбцы: Стаж работы, Оклад, Надбавка, Премия, Всего начислено, Пенсионный фонд, Налог, Выплатить.

Значения столбца Оклад определить с помощью функции просмотр, составив отдельную таблицу должностных окладов.

Стаж работы рассчитывается как частное от деления разности между текущей датой и датой найма на количество дней в году;

Надбавка составляет 5% от оклада для работников со стажем от 5 до 10 лет и 10% от оклада для работников со стажем более 10 лет;

Остальные столбцы рассчитываются по следующим формулам:

Премия = 20% (Оклад + Надбавка);

Всего начислено = Оклад + Надбавка + Премия;

Пенсионный фонд = 1% Всего начислено;

Налог = 13% (Всего начислено – Пенсионный фонд);

Выплатить = Всего начислено – Пенсионный фонд – Налог.

Добавить к таблице еще 5 строк.

Добавить к таблице итоговую строку, содержащую суммы значений столбцов Оклад, Надбавка, Премия, Всего начислено, Пенсионный фонд, Налог, Выплатить.

Назначить всем столбцам, содержащим денежные величины, финансовый формат.

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

Используя форму данных, добавить в таблиц еще 5 записей.

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

• заданная должность;

• заданный отдел;

• стаж работы больше заданного;

• заданная должность и оклад меньше заданного;

• заданный отдел и стаж работы больше заданного.

Отсортировать таблицу по фамилиям.

Отсортировать таблицу по отделам (от А до Я), а внутри отделов – по стажу работы сотрудников (от максимального к минимальному). Использовать способ многократной сортировки от менее значимых к более значимым столбцам.

Отсортировать таблицу по отделам, внутри каждого отдела – по должностям, а для одинаковых значений поля Должность – по фамилиям. Порядок сортировки по всем столбцам ‑ от А до Я. Использовать диалоговое окно команды Настраиваемая сортировка.

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

Используя автофильтр, отобрать данные:

• о сотрудниках двух заданных отделов;

• о сотрудниках, занимающих одну из трех заданных должностей;

• о сотрудниках заданного отдела, занимающих одну из двух заданных должностей.

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

• о сотрудниках, чья фамилия начинается с заданной буквы;

• о сотрудниках со стажем работы больше заданного;

• о сотрудниках, стаж работы которых находится в заданном диапазоне;

• о сотрудниках с окладом выше среднего;

• о 5-ти сотрудниках с наибольшими премиями;

• о сотрудниках, дата найма которых находится в заданном диапазоне;

• о сотрудниках, нанятых в прошлом году;

• о сотрудниках, нанятых во 2-м квартале;

• о сотрудниках заданного отдела со стажем работы выше среднего.

Используя расширенный фильтр, отобрать данные о сотрудниках с окладом в заданном диапазоне. В отфильтрованную таблицу включить поля Фамилия, Должность, Отдел, Оклад.

Используя расширенный фильтр, отобрать данные о сотрудниках, у которых либо стаж работы больше заданного, либо оклад меньше заданного. В отфильтрованную таблицу включить поля Фамилия, Отдел, Стаж работы, Оклад.

Используя расширенный фильтр, отобрать данные о сотрудниках со стажем работы в заданном диапазоне и с окладом в заданном диапазоне. В отфильтрованную таблицу включить поля Фамилия, Должность, Стаж работы, Оклад.

Используя расширенный фильтр, отобрать данные о сотрудниках заданного отдела, занимающих заданную должность, у которых либо стаж работы больше заданного, либо оклад больше заданного. В отфильтрованную таблицу включить поля Фамилия, Отдел, Должность, Стаж, Оклад.

Используя расширенный фильтр, отобрать данные о сотрудниках, у которых стаж работы либо меньше 2 лет, либо от 10 до 15 лет. В отфильтрованную таблицу включить поля Фамилия, Отдел, Стаж, Оклад.

Используя расширенный фильтр, отобрать данные о сотрудниках:

• оклад которых выше среднего;

• оклад которых выше среднего, а стаж работы от 5 до 15 лет;

• оклад которых выше среднего, а стаж работы – меньше среднего;

В условиях отбора использовать функцию СРЗНАЧ.

В отфильтрованные таблицы включить поля Фамилия, Стаж работы, Оклад.

Используя функции базы данных, вычислить:

• количество сотрудников со стажем работы больше заданного;

• количество сотрудников с окладом меньше среднего;

• количество сотрудников заданного отдела с заданной должностью;

• сумму окладов всех сотрудников с заданной должностью;

• сумму надбавок всех сотрудников заданного отдела со стажем больше 10 лет;

• наименьшую премию среди сотрудников со стажем работы от 5 до 15 лет;

• наименьший налог среди сотрудников с окладом больше среднего.

Используя таблицу подстановки и функции базы данных, создать таблицы, отображающие:

• количество сотрудников, занимающих каждую должность;

• сумму выплат, окладов, надбавок и премий в каждом отделе;

• сумму выплат, «всего начислено» и налогов по каждой должности;

• количество различных должностей по отделам.