Работа 3.3. Сортировка данных. Фильтры

Цель работы:Изучить основные способы сортировки и фильтрации данных в электронных таблицах MS Excel 2010.

Ход работы:

2. Изучить теоретическую часть.

3. Выполнить задания практической части.

4. Представить файлы для проверки.

Теоретическая часть

Сортировка– расположение данных таблицы в строго определенной последовательности.

Виды сортировок и способы их выполнения:

- Простая сортировка. Осуществляется с помощью кнопок Сортировка от А до Я (по возрастанию) и Сортировка от Я до А (по убыванию)(рис. 3.3.1). Сортировка осуществляется по тому полю, в котором находится активная ячейка.

-

Рис. 3.3.1 Вкладка Главная – Сортировка и фильтр

- Сложная сортировка. Осуществляется с помощью пункта Настраиваемая сортировка… списка Сортировка и фильтрация (рис. 3.3.1) и кнопки Сортировка вкладки Данные (рис. 3.3.2).

-

Рис. 3.3.2 Вкладка Данные – Сортировка и фильтр

В диалоговом окне Сортировка (рис. 3.3.3)указывается столбец, по которому нужно отсортировать данные, а также порядок сортировки (по возрастанию или по убыванию).

Рис. 3.3.3 Окно Сортировка

Здесь же можно задать многокаскадную сортировку (Добавить уровень; Затем по). Это означает, что если по первому сортируемому значению некоторые строки совпадают, то они между собой будут отсортированными по указанному в пункте Затем по столбцу. Третий столбец указывается, если совпадения будут по обоим первым столбцам.

Кнопка Параметры… вызывает диалоговое окно Параметры сортировки, которое позволяет определить, что будет подвергнуто сортировке – строки или столбцы. По умолчанию сортируются строки таблицы.

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

Фильтрация данных с помощью фильтра:

- выделить диапазон, для которого будет создан фильтр;

- нажать кнопку Фильтр на вкладке Данные (рис. 3.3.4) или выбрать пункт Фильтр из списка Сортировка и фильтрация (рис. 3.3.1);

- раскрыть список столбца, по которому будет осуществляться фильтрация, щелкнув по кнопке ;

- указать требуемые значения или выбрать строку Числовые фильтры / Настраиваемый фильтр… (рис. 3.3.4)и задать критерий выборки в диалоговом окне Пользовательский автофильтр (рис. 3.3.5).

Рис. 3.3.4 Вкладка Данные – Фильтр

Рис. 3.3.5 Окно Пользовательский автофильтр

Условия для отбора записей в определенном столбце могут состоять из двух самостоятельных частей, соединенных логической связкой И/ИЛИ. Каждая часть условия может включать:

Ø значение, которое может выбираться из списка или содержать шаблонные символы подстановки * и ?;

Ø оператор сравнения.

Способы восстановления всех строк исходной таблицы:

– щелкнуть по кнопке и в раскрывшемся списке выбрать Выделить все;

– нажать кнопку Очистить на вкладке Данные.

Отмена режима фильтра: нажать кнопку Фильтр на вкладке Данные.

Фильтрация данных с помощью расширенного фильтра:

- сформировать специальную область для задания условий фильтрации данных – диапазон условий отбора (интервал критериев). Диапазон условий должен содержать строку с заголовками столбцов и несколько строк для задания условий отбора. Если условия вводятся в одной строке для разных столбцов, то они считаются связанными логической связкой И. Если критерии отбора записываются в разных строках, то они считаются связанными логической связкой ИЛИ. Между значениями условий отбора и таблицей должна находиться как минимум одна пустая строка;

- установить курсор внутри таблицы;

- нажать кнопку Дополнительно на вкладке Данные (рис. 3.3.4);

- в диалоговом окне Расширенный фильтр указать диапазон ячеек таблицы и диапазон условий отбора.

Рис. 3.3.6 Окно Расширенный фильтр

Отмена режима расширенного фильтра: нажать кнопку Очистить на вкладке Данные.

Практическая часть

До начала выполнения работы, создайте файл отчета по форме представленной в приложении 1 и заполните шапку отчета.

Выполнение работы.

1. Запустите Excel. Сохраните файл под именем «Ваша фамилия33» (например, Иванов33). Переименуйте Лист1, дав ему имя Склад.

2. Создайте и заполните следующую таблицу:

3. В столбце Итого произведите вычисления по формуле: =Цена*Количество.

Занесите копию экрана в правую колонку файла отчета.

4. Используя простую сортировку по возрастанию, отсортируйте данные таблицы по полю Цена.

Занесите копию экрана в правую колонку файла отчета.

5. Отсортируйте наименования организаций в алфавитном порядке, внутри каждой организации наименование товара в алфавитном порядке и внутри каждого наименования товара – по возрастанию количества проданного товара. Для этого:

- установите курсор в область таблицы;

- выполните Данные / Сортировка;

- в первом уровне сортировки выберите поле Организация, во втором -Товар, в третьем - Кол-во и нажмите OK.

Занесите копию экрана в правую колонку файла отчета.

6. Скопируйте таблицу с листа Склад на Лист2, дав ему имя Фильтр.

7. Выделите первый столбец, создайте для него Фильтр, выбрав из таблицы только те строки, которые относятся к АОЗТ «Белокуриха».

Занесите копию экрана в правую колонку файла отчета.

8. Отключите Фильтр.

9. Создайте Фильтр для всех столбцов таблицы.

10. Используя фильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», где в столбце «Товар» присутствует «хлеб», т.е. осуществите выборку по двум полям.

Занесите копию экрана в правую колонку файла отчета.

11. Восстановите все строки исходной таблицы.

12. Используя фильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», в которых цена товара не превышает 16.

Занесите копию экрана в правую колонку файла отчета.

13. Восстановите все строки исходной таблицы.

14. Используя фильтр, выведите на экран записи, содержащие колхоз «Восток» и дату покупки товара в промежутке после 2 марта до 13 июня.

Занесите копию экрана в правую колонку файла отчета.

15. Скопируйте таблицу с листа Склад на Лист 3 и дайте имя листу Расширенный фильтр.

16. Используя расширенный фильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», где в столбце «Товар» присутствует «хлеб», т.е. осуществите выборку по двум полям.

Для этого:

- ниже таблицы, оставив пустые 2 строки, скопируйте строку заголовка таблицы;

- в следующей строке сформируйте критерий отбора записей: в столбец Организация введите АОЗТ «Белокуриха», в столбец Товар — «хлеб»;

- установите курсор в область таблицы, из которой будет производиться выборка данных;

- нажать кнопку Дополнительно на вкладке Данные;

- в диалоговом окне Расширенный фильтр в строке Исходный диапазон появится диапазон, соответствующий расположению анализируемой таблицы;

- в диалоговом окне Расширенный фильтр установите курсор в строку Диапазон условий, перейдите на рабочий лист и выделите диапазон условий отбора;

- для того чтобы новые данные печатались в другом месте, установите флажок Скопировать результат в другое место и укажите ячейку, начиная с которой будут выводиться отсортированные данные.

Занесите копию экрана в правую колонку файла отчета.

17. Используя расширенный фильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», в которых цена товара не превышает 16. При этом в диапазоне условий отбора в поле Цена укажите условие: <=16. Занесите копию экрана в правую колонку файла отчета.

18. Сохраните файл и закройте MS Excel. Представьте преподавателю файл отчета и файл MS Excel с результатами Вашей работы.

Контрольные вопросы

1. Что такое сортировка?

2. Какими способами можно отсортировать данные электронной таблицы?

3. Как отсортировать список по двум и более ключам?

4. Что такое фильтр?

5. Какие виды фильтров вы знаете?

6. В чем отличие сортировки списка от фильтрации списка?

7. Что такое расширенный фильтр?

8. Как сформировать критерий для расширенного фильтра?

9. Какими способами можно выполнить фильтрацию (выборку) данных в электронной таблице?

10. Как выполнить фильтрацию данных в электронной таблице с помощью Расширенного фильтра?

Не нашли, что искали? Воспользуйтесь поиском по сайту: