Сортировка и фильтрация
Иногда таблицы могут содержать довольно большое количество данных. В таком случае, очень помогают в работе такие инструменты, как сортировка и фильтрация. Но, при этом таблица должна быть оформлена определенным образом, в противном случае инструменты сортировки и фильтрации работать не будут.
Как правило, таблица состоит из записей (строк) и полей (столбцов). Столбцы должны содержать однотипные данные. Таблица не должна содержать пустых строк или столбцов.
Сортировка
Сортировка позволяет выстраивать данные списков в алфавитном, цифровом и хронологическом порядке. При этом можно задать возрастающий, убывающий и пользовательский порядок сортировки.
Если задан возрастающий порядок сортировки, то данные списка упорядочиваются следующим образом:
· числа выстраиваются от наименьшего отрицательного к наибольшему положительному;
· значения даты и времени сортируются в хронологическом порядке от самого раннего к самому позднему;
· текстовые данные сортируются по алфавиту;
· столбец логических значений будет начинаться со значения ЛОЖЬ и заканчиваться значением ИСТИНА;
· пустые ячейки располагаются в конце списка.
Если задан убывающий порядок сортировки, то пустые ячейки также располагаются в конце списка, а данные всех остальных типов сортируются в порядке, обратном порядку, описанному ранее.
Для того чтобы начать сортировку таблицы:
1) щёлкните по любой ячейке внутри таблицы;
2) выполните команду: вкладка Данные – группа команд Сортировка и фильтр – команда Сортировка.
Сортировка по одному полю
1) Отсортируйте таблицу по полю Пол – по возрастанию.
В диалоговом окне Сортировка укажите следующие значения:
и нажмите кнопку ОК.
Сначала в списке будут фигурировать женщины, затем мужчины, так как по алфавиту буква «ж» предшествует букве «м».
2) Заново отсортируйте таблицу, но чтобы на этот раз мужчины предшествовали женщинам.
3) Отсортируйте таблицу по полю Отдел по возрастанию (от А до Я).
Сотрудники будут упорядочены по отделам в алфавитном порядке их аббревиатур: АПС, ОНК, ОТД и ТБК.
Сортировка по двум или трем полям
Сложная сортировка подразумевает упорядочение данных по нескольким полям. Чтобы добавить поля для сортировки в окне Сортировка используют кнопку «Добавить уровень ».
Перед началом сортировки добавьте в таблицу после столбца Дата рождения еще один столбец – Возраст. Для этого:
· щёлкните мышью по имени столбца G (новый столбец должен располагаться перед этим столбцом);
· щёлкните по выделенному столбцу правой кнопкой мыши и в открывшемся списке выберите команду Вставить (вторую в списке) – см. рис. на следующей странице;
· в «шапочку» нового столбца введите название Возраст.
Перед тем, как заполнить столбец формулой в ячейку N3 (вне таблицы) введите текущую дату. Для этого:
· щёлкните мышью по ячейке N3;
· перейдите в окно мастера функций (вкладка Формулы – группа команд Библиотека функций – Вставить функцию);
· в списке Категория выберите Дата и время;
· в списке Выберите функцию – СЕГОДНЯ и нажмите кнопку ОК;
· в появившемся окне Аргументы функции нажмите кнопку ОК.
В ячейки столбца Возраст введите формулу для расчета возраста сотрудников:
(Текущая дата – Дата рождения) / 365
Если вместо числа (обозначающего возраст сотрудника) в ячейке появилась дата, то выполните следующее:
· щёлкните по этой ячейке правой кнопкой мыши и в открывшемся списке выберите Формат ячеек;
· перейдите на вкладку Число, выберите формат Числовой, установите число десятичных знаков – 0, нажмите кнопку ОК.
!!! Выполните Автозаполнение для остальных ячеек столбца Возраст не методом перетаскивания за квадратик, а двойным щелчком мыши по квадратику Автозаполнения.
Сортировка:
1) Отсортируйте таблицу по полю Пол по убыванию, а по полю Отдел по возрастанию. Для этого:
· щёлкните по любой ячейке внутри таблицы;
· выполните команду: вкладка Данные – группа команд Сортировка и фильтр – команда Сортировка;
· сначала для поля Пол установите порядок от Я до А (по убыванию);
· затем щёлкните по кнопке Добавить уровень;
· выберите из списка Затем по поле Отдел и установите Порядок от А до Я;
· нажмите кнопку ОК.
2) Отсортируйте таблицу по отделам, а внутри отделов – сначала старших, а затем младших по возрасту сотрудников (по убыванию).
3) Отсортируйте таблицу сначала по отделам, внутри отделов – сначала мужчин, а потом женщин, и внутри каждой группы по возрасту - сначала старшие, затем – младшие.
4) Отсортируйте список по отделам, а внутри отделов по возрастанию табельных номеров.
Фильтрация
Одним из удобных методов анализа созданной таблицы является использование фильтрации данных, которая позволяет находить по условиям и отображать нужные данные в таблицах. Фильтрация позволяет выводить на экран только те значения, которые отвечают заданным условиям отбора для столбцов, не внося изменений в саму таблицу.
Возможен отбор сразу по нескольким столбцам, то есть можно применять одновременно несколько фильтров. При этом каждый следующий фильтр отбирает данные, уже отфильтрованные предыдущим фильтром.
Если фильтрация в списке не применялась, то значок фильтрации имеет вид:
Если фильтрация в списке применялась, то значок фильтрации имеет вид: .
Если подвести курсор к значку примененной фильтрации, то во всплывающей подсказке будет отображено условие фильтрации.
!!! Не забывайте перед каждым новым заданием по фильтрации отменять результаты предыдущей фильтрации.
Отбор по одному полю
1. Отобразите строки с информацией о сотрудниках отдела ТКБ. Для этого:
· выделите любую ячейку внутри таблицы.
· вкладка Данные – группа команд Сортировка и фильтр – команда Фильтр. У каждого столбца появиться значок фильтрации.
· раскройте список, соответствующий полю Отдел;
· снимите галочку Выделить все и установите галочку напротив названия нужного отдела – ТКБ и нажмите ОК.
· у столбца Отдел изменился значок фильтрации;
· просмотрите результат фильтрации;
· снимите фильтр: щёлкните по значку фильтрации столбца Отдел и выберите Снять фильтр с «Отдел».
2. Выведите список сотрудников отделов АПС и ОТД.
3. Выведите список женщин работающих на предприятии.
4. Выведите список мужчин, работающих на предприятии.
5. Выведите список сотрудников предприятия, чей оклад выше среднего по предприятию. Для этого:
· раскройте список фильтрации для столбца Оклад;
· выберите Числовые фильтры;
· в открывшемся списке выберите Выше среднего.
6. Выведите список сотрудников предприятия, чей оклад составляет от 25000 до 35000 рублей. Для этого:
· раскройте список фильтрации для столбца Оклад;
· выберите Числовые фильтры;
· в открывшемся списке выберите Настраиваемый фильтр.
· заполните окно Пользовательского фильтра по приведенному образцу:
7. Выведите список сотрудников с наименьшим окладом.
Для этого щёлкните по значку фильтрации столбца Оклад – Числовые фильтры – Первые 10. Заполните окно Наложение условия по списку по образцу (см. рис. на следующей стр.):
8. Выведите список сотрудников с наибольшим окладом.