ПР №4 Сортировка и фильтрация данных

Рис. 1.

Область таблицы A2:L15 можно рассматривать как базу данных. Столбцы A, B, C, D, E, F, H, I, J, K, L этой таблицы называются полями, а строки с 3 по 15 - записями. Область A2:L2 содержит имена полей.

Существуют ограничения, накладываемые на структуру базы данных:

• первый ряд базы данных должен содержать неповторяющиеся имена

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

• информация по полям (столбцам) должна быть однородной, т.е. только цифры или только текст

Основная работа с любой базой данных заключается в поиске информации по определенным критериям. С увеличением количества записей поиск информации затрудняется. Excel позволяет упростить этот процесс путем фильтрации данных.

Цель работы: познакомиться с использованием электронной таблицы как базы данных. Научиться осуществлять поиск информации в базе по различным критериям. Научиться производить сортировку информации.

• 1.Создайте в свою рабочую папку файл Microsoft Excel Расчет заработной платы, откройте файл и заполните таблицу данными, которые показаны на рисунке, кроме ячеек «Основная з/п», «Доплата», «Премия», «Удержано», «Сумма к оплате».

• 2.Заполните пустые ячейки:

• Столбец «Основная з/п» рассчитывается по формуле: = Почасовая тарифная ставка * (Количество отработанных дней*8)

• Столбец «Доплата» рассчитывается по формуле: = 0,12*(Почасовая, тарифная ставка*Количество отработанных дней*8)

• Столбец «Премия» рассчитывается по формуле: = Основная з/п * Премия ,%

• Столбец «Удержано» рассчитывается по формуле: =0,14*(Основная з/п + Доплата + Премия)

• Столбец «Сумма к оплате» рассчитать самостоятельно используя формулу.

• 3.Отредактируйте исходную таблицу, приведите её к виду как на Рис.1

 1. Перейдите на вкладку Данные и нажать кнопку Фильтр. После этого к базе данных будут применимы функции фильтрации.

 2. Нажмите на стрелку в правом нижнем углу ячейки ФИО и в выпадающем меню уберите флажки напротив всех фамилий, кроме одной. Убедитесь, что в результате в базе данных видны данные только об одном рабочем. Поставьте флажок напротив строчки (Выделить всё), чтобы снова были видны все данные (аналогично делайте после выполнения и зачтения следующих заданий).

 3. Нажмите на стрелку в правом нижнем углу ячейки Специальность и аналогично п. 2 покажите только рабочих со специальностью кузнец.

 4. Покажите только рабочих с повременной (пов) формой оплаты. Покажите результат преподавателю

 5. Покажите только рабочих со сдельной (сд) формой оплаты и количеством отработанных дней – 30. Покажите результат преподавателю

 1. Отсортируйте фамилии рабочих по алфавиту. Для этого нажмите на стрелку в правом нижнем углу ячейки ФИО и в выпадающем меню выберите верхнюю строчку – Сортировка от А до Я.

 2. Отсортируйте рабочих по возрастанию разряда. Для этого нажмите на стрелку в правом нижнем углу ячейки Разряд и в выпадающем меню выбрать Сортировать от минимального к максимальному.

 3. Отсортируйте рабочих по убыванию Основной заработной платы (Сортировать от максимального к минимальному). Покажите результат преподавателю

 4. Покажите только рабочих с количеством отработанных дней 18 и 28 и среди них провести сортировку по возрастанию Суммы к оплате. Покажите результат преподавателю

 1. Покажите рабочих с Почасовой тарифной ставкой больше 9 рублей. Для этого нажмите на стрелку в правом нижнем углу ячейки Почасовая тарифная ставка и в выпадающем меню выберите Числовые фильтры\больше, в появившемся текстовом поле наберите 9 и нажмите ОК. Чтобы снять фильтрацию, нажмите на стрелку в правом нижнем углу ячейки Почасовая тарифная ставка и в выпадающем меню выберите Снять фильтр.

 2. Отфильтруйте рабочих с Премией меньше 5% (Числовые фильтры\меньше, 5%, ОК). Покажите результат преподавателю

 3. Отфильтруйте рабочих с зарплатой от 2000 до 3000 р. (Числовые фильтры\между) и имеющих пятый разряд. Покажите результат преподавателю

3