Теоретические сведения
Сортировка и фильтр в Excel на примере базы данных клиентов
Сортировка в Excel – это распределение строк таблицы в определенном порядке, который соответствует конкретным условиям пользователя. Упорядочивание строк при сортировке всегда выполняется относительно выбранного столбца.
Работа в Excel c фильтром и сортировкой
Чтобы выполнить сортировку Excel можно воспользоваться несколькими простыми способами. Сначала рассмотрим самый простой.
Способ 1:
- Заполните таблицу как на рисунке:
- Перейдите на любую ячейку столбца F.
- Выберите инструмент: «Главная»-«Редактирование»-«Сортировка и фильтр»-«Сортировка от А до Я».
Способ 2:
- Перейдите на любую ячейку таблицы с данными.
- Выберите инструмент: «Главная»-«Редактирование»-«Сортировка и фильтр»-«Фильтр»
- Щелкните по выпадающему списку опций в заголовке «Город» таблицы и выберите опцию «Сортировка от А до Я».
Первый способ более простой, но он может выполнить сортировку только по одному столбцу (критерию). Для сортировки по нескольким столбцам следует использовать «Способ 2».
Полезный совет! В таблицах рекомендуется всегда использовать столбец с номерами строк. Он позволит всегда вернуться к первоначальному расположению строк после нескольких сортировок. Для этого достаточно выполнить сортировку этого столбца (например, №п/п).
Следует помнить, что сортировка выполняется над данными таблицы без пустых строк. Если нужно отсортировать только часть данных таблицы, тогда следует выделить это диапазон непосредственно перед сортировкой. Но такое фрагментированное сортирование данных очень редко имеет смысл и легко приводит к ошибкам.
Сортировка по нескольким столбцам в Excel
Отсортируем базу данных клиентов в соответствии с двумя критериями:
- Наименование городов в алфавитном порядке.
- Возраст клиентов от младших до старших.
Выполним сортировку по отдельным столбцам таблицы:
- Перейдите курсором клавиатуры на любую ячейку таблицы и выберите инструмент: «Данные»-«Сортировка и фильтр»-«Сортировка».
- В появившемся диалоговом окне укажите параметры сортировки так, как указано ниже на рисунке и нажмите на кнопку «Добавить уровень».
- Заполните параметры второго уровня как ниже на рисунке и нажмите ОК.
В результате таблица Excel отсортирована по нескольким критериям.
Чем выше уровень параметров сортировки, тем выше его приоритет.
Примечание. В Excel начиная с версии 2010-го года, количество уровней может быть столько, сколько столбцов в таблице. В старших версиях Excel разрешалось только 3 уровня.
Как сделать фильтр в Excel по столбцам
Фильтрация данных – это выборка из базы строк соответствующим определенным условиям. Например, отбор всех клиентов женского пола с одного или нескольких городов.
С данной таблицы нужно выбрать всех клиентов в возрасте до 30-ти лет проживающих в городах Москва и Санкт-Петербург.
- Снова перейдите на любую ячейку таблицы базы данных клиентов и выберите инструмент: «Главная»-«Редактирование»-«Сортировка и фильтр»-«Фильтр».
- Щелкните по выпадающему списку столбца «Город» и отметьте галочками только Москву и Санкт-Петербург. И нажмите ОК.
- Щелкните по выпадающему списку столбца «Возраст» и выберите опцию: «Числовые фильтры»-«Настраиваемый фильтр».
- Заполните поля в окне «Пользовательский автофильтр» как указано на рисунке и нажмите ОК.
Как видно в столбце «№п/п» отсутствуют некоторые номера, что подтверждает о фильтрации данных в таблице. Так же обратите внимание, как изменились кнопки выпадающих списков на столбцах C и F. Так как они содержать настройки их Excel отметил значком воронки. Теперь если нам нужно вернуть исходный вид базы данных о клиентах мы знаем, по каким столбцам нужно изменить настройки фильтрации. Но еще быстрее сбросить фильтр можно выбрав по новой инструмент «Главная»-«Редактирование»-«Сортировка и фильтр»-«Фильтр».
Выполнение практической части:
1. Открыть рабочую книгу, созданную в ПР №12 (Или создать по образцу)
2. Удалить лист Вычисления.
3. Скопировать лист Фирма Консульт четыре раза. Новым листам рабочей книги присвоить имена: Сортировка 1, Сортировка 2, Автофильтр 1, Автофильтр
4. На листе Сортировка 1 выполнить сортировку данных по возрастанию по полю К выплате
в таблице Фирма «Консульт».
5. На листе Сортировка 2 выполнить сортировку по убыванию по полям Отдел и ФИО в таблице Фирма «Консульт».
6. На листе Автофильтр 1 отобрать двух сотрудников, имеющих наибольшую заработную плату в фирме.
7. На листе Автофильтр 2 отобрать консультантов с заработной платой более 14000 руб..
8. Сохраните рабочую книгу, тип файла - xlsх
ü выберите команду Файл → Сохранить как;
ü имя файла - Пр№13_Иванов1_1ТОРАТ19 (указать вашу фамилию)
Технология работы:
Технология выполнения задания приведена в видеоинструкции в отдельной ссылке.
Контрольные вопросы:
Выберите один или несколько правильных ответов.
1. Какую формулу необходимо записать в ячейку А5, чтобы вычислить сумму чисел, которые
находятся в диапазоне ячеек А1:А4?
a. = А1 + А2 + А3 + А4
b. = СУММ(А1:А4)
c. А5 = СУММА(А1:А4)
d. А1 + А2 + А3 + А4 = А5
2. В ячейку С1 записана формула = A1*A2 и скопирована в ячейку С2. Какая формула будет отображаться в ячейке С2?
a. = В1*В2
b. = А1*А2
c. = А2*А3
d. = В2*В3
3. В ячейки А1:В2 (см. рисунок) записаны числа. В ячейку С1 записана формула = A1*$A$2 и скопирована в ячейку С2. Какое числовое значение будет выведено в ячейке С2?
a. 30
b. 36
c. 48
d. 42
4. Какие типы диаграмм являются стандартными?
a. Круговая
b. Логарифмическая
c. Гистограмма
d. Пирамидальная
Отчет о выполнении практической работы выполняется в тетради и должен содержать:
1. Номер практической работы.
2. Тему работы.
3. Цель работы.
4. Ответы на контрольные вопросы.
5. Файл Excel с выполненным заданием.
Отчет (в виде фотографий (или скан-копий) тетрадных страниц, файл с таблицей Excel) отправить по электронной почте на адрес преподавателя Пожидаева Д.А. denis.pozhidaev.80@gmail.com тел. 071 362 51 91