 |
Задание № 3. Базы данных (списки) в Excel
|
|
|
|
1. Создайте новую книгу, дайте ей имя. Переименуйте Лист1 на Список, а Лист2 – на Сортировка.
2. На листе Список создайте таблицу, как показано в задании. Сделайте вычисления по формулам.
3. Скройте все столбцы, кроме «ФИО» и «Начислено», выделите диапазон данных для построения круговой диаграммы.
4. По выделенным данным постройте на отдельном листе с названием Заработная плата круговую диаграмму, отражающую долю заработной платы каждого рабочего от общего фонда заработной платы цеха.
5. Произведите копирование списка (базы данных) с листа Список на лист Сортировка. Сделайте сортировку по возрастанию: по фамилиям, по разряду, по окладу.
6. Переименуйте новый лист на Автофильтр и скопируйте на него исходную базу данных. Выберите из списка данные, используя критерии:
a) все работники, имеющие разряд ниже 4;
b) все работники, имеющие оклад ниже среднего;
c) все работники, имеющие начисленную сумму выше средней и разряд ниже.
7. Переименуйте новый лист на Структура и скопируйте на него исходную базу данных. Отсортируйте строки списка по разряду работников. Произведите автоструктурирование таблицы по столбцам.
8. Структурирование таблицы с автоматическим подведением итогов. Создайте первый уровень итогов – средние значения Общего налога, Итого доплат и Начислено по разрядам.
Задание3.1. Создайте таблицу и произведите расчёты по формулам при начислении месячной заработной платы рабочих производственного цеха в зависимости от их разряда.
В отдельную ячейку введите минимальную оплату труда за один рабочий день в размере 150 руб. Дайте имя ячейке «Минимальная ставка».
Столбцы Разряд, Количество отработанных дней – невычисляемые.
|
|
|
Формулы для расчета:
· Оклад – произведение Минимальной ставки на разряд и на количество отработанных дней в месяце;
· Подоходный налог – 12 % от оклада;
· Пенсионный налог – 1 % от оклада;
· Надбавка за вредные условия труда – 10 % от оклада при условии, что разряд рабочего выше третьего.
· Премия – 20 % от оклада;
· Начислено к выдаче – к окладу прибавляются доплаты и вычитаются налоги.
| ФИО | Разряд | Кол-во отработанных дней | Оклад | Подоходный налог | Пенсион-ный фонд | Общий налог | Надбавка | Премия | Итого доплат | Начислено |
| Волков А.С. | ||||||||||
| Сидоров В.К. | ||||||||||
| Грачёв Т.О. | ||||||||||
| Иванов И.И. | ||||||||||
| Котов Н.А. | ||||||||||
| Абрамов П.С. | ||||||||||
| Петров А.С. | ||||||||||
| Итого |
Примечание:
Оформите таблицу соответствующим обрамлением.
Введите в столбцы, где это необходимо, денежный формат. Для этого воспользуйтесь командой Формат, расположенной на вкладке Главная в группе Ячейки, либо с помощью контекстного меню.
Чтобы скрыть столбцы, сначала их выделите, щелкнув по их заголовкам кнопкой мыши и удерживая клавишу Ctrl. На вкладке Главная в группе Ячейки кнопкой Формат откройте диалоговое окно, в котором выберите строку Видимость, а затем команду Скрыть столбцы. Выделите мышью нужные данные для построения диаграммы. На вкладке Вставка выберите тип диаграммы – круговая, уточните тип, отформатируйте полученную диаграмму, дайте ей название, разместите на отдельном листе.
Для сортировки данных выделите таблицу вместе с заголовками, перейдите на вкладку Данные, группа Сортировка и фильтр, откройте окно Сортировка. Здесь выберите столбец, по которому будет сортироваться список и порядок сортировки. В случае необходимости добавьте второй уровень сортировки.
|
|
|
Для фильтрации данных выделите таблицу и включите фильтр (Данные - Сортировка и фильтр – Фильтр ). Выберите критерий для выбора.
Структурирование таблицы – это группирование строк и столбцов. Обычно выполняется при работе с большими таблицами, когда приходится временно закрывать или открывать вложенные друг в друга части таблицы. Предварительно необходимо произвести сортировку записей по группам (в нашем примере группы – это разряды). Автоструктурирование выполняется для таблиц, содержащих формулы, которые ссылаются на ячейки, расположенные выше или левее результирующих, образуя с ними смежную сплошную область.
Для проведения автоструктурирования установите курсор в произвольную ячейку списка, перейдите на вкладку Данные, группа Структура, откройте кнопку Группировать, выполните команду Создание структуры. В результате создаётся структура таблицы (рисунок 1) со следующими элементами на служебном поле:
· Линии уровней структуры;
· Кнопка <плюс> - для раскрытия групп структурированной таблицы;
· Кнопка <минус> - для скрытия групп структурированной таблицы;
· Кнопки <номера уровней 1, 2, 3> - для открытия или скрытия уровня.
Представьте разные виды таблицы, используя эти элементы на служебном поле.
Для структурирования таблицы с автоматическим подведением итогов выделите таблицу, перейдите на вкладку Данные, группа Структура, откройте окно Промежуточные итоги. В первом текстовом поле окна выберите группу, по которой будет проводится итог, во втором – операцию, в третьем – какие данные с промежуточными итогами отображать в таблице (рисунок 2).
Для отмены всех структурированных компонентов таблицы используется команда Убрать все диалогового окна Промежуточные итоги.
Рисунок 1 - Автоструктурирование таблицы
Рисунок 2 - Структурированная таблица с автоматическим подведением итогов
|
|
|
