Один из удобных методов анализа созданного документа — использование фильтрации данных, которая позволяет находить и отображать нужные данные в списках. Фильтрация дает возможность выводить на экран только те значения, которые отвечают заданным условиям отбора для столбцов, не внося изменений в сам документ. Возможен отбор и по нескольким столбцам, то есть можно одновременно применять несколько фильтров.
При этом каждый следующий фильтр отбирает данные, отфильтрованные предыдущим. Отфильтрованные данные по-прежнему можно изменять, редактировать, использовать для создания диаграммы, но при этом нельзя изменять и перемещать их порядок.
Арифметические функции учитывают и ячейки, которые не отображаются из-за применения фильтра. Возможны три типа фильтрации списков:
• Автофильтр — для автоматической фильтрации и создания однострочных списков;
• Стандартный фильтр — для создания фильтрации согласно параметрам по умолчанию;
• Расширенный фильтр — для создания фильтрации с более сложными условиями отбора. К одному диапазону ячеек в пределах столбца можно применить лишь один вид автофильтра.
-Для получения корректных результатов фильтрации рекомендуется не использовать в одном столбце данные разных форматов, например текст и числа, числа и даты, поскольку для каждого столбца может использоваться только один тип команды фильтра. В противном случае, если в столбце будет находиться несколько форматов, отобразится только команда для преобладающего формата.
Как применить автофильтр
1. В окне открытого листа выделите столбцы данных или весь диапазон данных.
2. Откройте меню Данные и в списке команд наведите курсор на пункт Фильтр.
3. В раскрывшемся списке щелкните по строке Автофильтр.
4. В первой строке выбранного диапазона отобразятся стрелки полей списка.
5. Щелкните по стрелке списка нужного столбца и выберите условие фильтрации:
• Все — для отображения полного списка;
• 10 первых — для отображения
• 10 верхних значений списка;
• Стандартный фильтр — для переключения в режим работы с окном Стандартный фильтр;
• Пусто — для отображения только пустых ячеек;
• Не пусто — для отображения заполненных ячеек. 6. Отобразятся только строки, соответствующие фильтру. При этом в качестве заголовка также будет отображаться первая строка диапазона ячеек.
-О наличии фильтрации строк можно судить по пропущенным номерам строк. Цвет кнопки со стрелкой в столбце, где производилась фильтрация, изменится.
Как отобразить отфильтрованные строки
В окне открытого листа раскройте список нужного столбца и выберите условие фильтрации Все.
Как применить стандартный фильтр
1. В окне открытого листа выделите столбцы данных или весь диапазон данных.
2. Откройте меню Данные и в списке команд наведите курсор на пункт Фильтр.
3. Щелкните по строке Стандартный фильтр.
4. В окне Стандартный фильтр щелкните по кнопке До полнительно и выберите критерии фильтрации:
• Учитывать регистр — для учета при фильтрации прописных и строчных букв;
• Область содержит заголовки столбцов — для включения в первую строку диапазона ячеек заголовков столбцов;
• Копировать результат в — для размещения в выбранном диапазоне результатов фильтрации. Диапазон задается после активации пункта Сохранить условия;
• Регулярное выражение — для использования в определении фильтра подстановочных знаков;
• Без повторений — для исключения из списка отфильтрованных данных повторяющихся строк.
5. Закройте окно кнопкой ОК.
Как удалить фильтр
1. В окне открытого листа выделите фильтрованный диапазон данных .
2. Откройте меню Данные и в списке команд наведите курсор на пункт Фильтр.
3. В списке щелкните по строке Удалить фильтр.
Как применить расширенный фильтр
Данный фильтр не создает раскрывающиеся списки столбцов. Для фильтрации используется диапазон условий, который располагается над списком, что позволяет задавать более сложные варианты отбора. Диапазон условий отбора (матрица фильтра) должен включать заголовок столбца с тремя (как минимум) пустыми строками.
1. В окне открытого листа скопируйте необходимые заголовки столбцов диапазонов в свободное место.
2. В строке под заголовками введите критерии для фильтрации.
-Горизонтально расположенные данные в строке будут логически связаны оператором И, а вертикально расположенные данные в столбцах — оператором ИЛИ.
3. Откройте меню Данные и в списке команд наведите курсор на пункт Фильтр.
4. В раскрывшемся списке щелкните по строке Расширенный фильтр.
5. В окне Расширенный фильтр щелкните по кнопке Дополнительно и выберите критерии фильтрации (аналогично стандартному фильтру).
6. Щелкните по кнопке Свернуть/развернуть и выделите на листе созданную матрицу фильтра.
7. Закройте окно кнопкой ОК.
-На листе останутся видны только строки, содержимое которых соответствует заданным критериям фильтрации, а остальные строки будут временно скрыты. Для их отображения откройте меню Формат, выберите пункт Строка, а затем — Показать.
Стандартный фильтр в табличном редакторе Calc в офисном приложении OpenOffice применяется в том случае, когда из исходной таблицы необходимо выбрать значения, удовлетворяющие определенным условиям. Например, значения больше, меньше или равные определенной величине.
С помощью Стандартного Фильтра можно cделать выборку как из текстовых, так и из цифровых значений. Стандартный фильтр позволяет одновременно использовать до трех различных условий.
Рассмотрим применение Стандартного фильтра на примере 15 крупнейших банков США, по данным Федеральной Резервной Системы США по состоянию на 31 марта 2012 года. Все исходные данные указаны в Таблице 1.
Решим две простые задачи для решения в табличном редакторе Calc.
ЗАДАЧА 1. В Таблице 1 с помощью Стандартного Фильтра табличного редактора Calc, необходимо найти Банки у которых значения Domestic Assets ( Mil $) (Внутренние активы, млн. долл. США) больше 1 152, 497 (млн. долл. США).
ЗАДАЧА 2. Выбрать из Таблицы 1 Банки, у которых значения Domestic Assets ( Mil $) больше 1 000 (млн. долл. США).
После открытия исходной таблицы в Calc делаем следующие последовательные шаги в соответствии с условиями Задачи 1:
1. В разделе Данные выбираем Фильтр, затем Стандартный фильтр … (Данные→Фильтр→Стандартный фильтр …)
1.1. В меню Calc выбираем Данные→Фильтр
1.2. В меню Calc выбираем Фильтр→Стандартный фильтр …
2. Появилось диалоговое окно Стандартный Фильтр табличного редактора Calc. В ячейке «Имя поля» указано название первого столбца в таблице — Bank Name / Holding Co Name.
3. В диалоговом окне Стандартный фильтр в ячейке «Имя поля» в выпадающем списке выбираем Domestic Assets ( Mil $).
4. В ячейке «Условие», также в выпадающем меню, выбираем знак «>», в ячейке «Значение», также в выпадающем меню выбираем значение 1152,497. Нажимаем ОК.
5. В результате появится таблица в которой будут банки у которых значение Domestic Assets ( Mil $) больше 1152,497.
Комментарий
В отфильтрованной таблице находятся все два банка. Остальные строки с 4 по 16 включительно скрыты. Они не удалены и данные не потеряны. Чтобы вернуть таблице первоначальный вид, т. е. чтобы были видны все ячейки, необходимо удалить Стандартный фильтр.
6. Удаление Стандартного фильтра в Calc происходит следующим образом: Данные→Фильтр→Удалить фильтр. После совершения данных действий таблица примет исходный вид.
Теперь можно продолжить задавать новые критерии выбора в Стандартном фильтре Calc в соответствии с Задачей 2.
7. Вновь откроем диалоговое окно Стандартный фильтр табличного редактора Calc и введем уже значение «> 1000». Цифра 1000 самостоятельно набирается в поле «Значение».
8. С помощью Стандартного Фильтра из исходной Таблицы были выбраны только Банки у которых Значение Domestic Assets ( Mil $) >1000.
9. Удалим фильтр, чтобы Таблица вернулась к первоначальному значению.
Идёт приём заявок
Подать заявку
Для учеников 1-11 классов и дошкольников
Лабораторная работа №11.
Сортировка данных в OpenOffice org . Calc . Фильтрация данных. Автофильтр и расширенный фильтр.
Как правило, данные, представленные в виде таблиц, имеют некую структуру. В Calc диапазон ячеек со структурированными данными может использоваться в качестве базы данных. При этом каждая строка будет соответствовать одной записи в базе данных, а каждая ячейка в строке – полю базы данных. Такой диапазон можно сортировать, группировать, производить в нем поиск и выполнять вычисления.
Для осуществления сортировки необходимо прежде всего выделить диапазон ячеек, содержащих данные для сортировки. Это можно сделать двумя способами:
вручную с помощью мыши или клавиш на клавиатуре
2) установите курсор в ячейку в диапазоне сортировки в том столбце, по которому будет осуществляться сортировка. Затем выберите команду Данные – Сортировка.
Calc выделит все смежные ячейки с данными, включая самую первую строку, которая по предположению является строкой заголовков, и на экране появится диалоговое окно Сортировка, в котором нужно задать критерии сортировки, а на вкладке Параметры при необходимости задайте уточняющие параметры условий сортировки.
Сортировка может быть выполнена в одном, двух и трех уровнях.
Закладка Параметры предоставляет следующие настраиваемые возможности сортировки:
● Учитывать регистр – сортировка сначала выполняется по прописным, затем по строчным буквам.
● Заголовки в первой строке – при сортировке первая строка или первый столбец выделенной области игнорируется. Параметр Направление в нижней части диалогового окна определяет имя и назначение этого флажка.
● Включая форматы – сохранить текущий формат ячеек .
● Поместить результат в – необходимо указать диапазон ячеек, в который будут помещены отсортированные данные.
● Порядок сортировки, определенный пользователем – в списке необходимо выбрать нестандартный порядок сортировки. Чтобы определить такой, выберите Сервис→Параметры→OpenOffice.org Calc→Списки сортировки
Для сортировки данных выделенного диапазона можно воспользоваться кнопками на панели инструментов Стандартная: Сортировка по возрастанию и Сортировка по убыванию .
Фильтр — это быстрый и легкий способ поиска подмножества данных и работы с ними в списке. В отфильтрованном списке отображаются только строки, отвечающие условиям ( Условие – ограничение, заданное для отбора записей, включаемых в результирующий набор записей запроса или фильтра) , заданным для столб ца.
В OpenOffice.org Calc доступны две команды для фильтрации списков:
Автофильтр , включая фильтр по выделенному, для простых условий отбора.
Расширенный фильтр для более сложных условий отбора.
В отличие от сортировки, фильтр не меняет порядок записей в списке. При фильтрации временно скрываются строки, которые не требуется отображать. Строки, отобранные при фильтрации в OpenOffice.org Calc , можно редактировать, форматировать, создавать на их основе диаграммы, выводить их на печать, не изменяя порядок строк и не перемещая их.
Для фильтрации списка предназначена команда Данные – Фильтр.
Автофильтр – это наиболее быстрый способ для того, чтобы выбрать доступные фильтры поиска, которые обычно используются во многих различных типах приложений.
Для выполнения фильтрации данных:
1. Выберите произвольную ячейку списка.
Замечание: Calc предполагает наличие в первой строке списка заголовков столбцов, и будет игнорировать их в процессе фильтрации.
2. Выберите Данные – Фильтр – Автофильтр, чтобы включить автоматическую фильтрацию для этого списка. В заголовках всех столбцов появится кнопка раскрытия списка .
3. Щелкните на кнопке в заголовке столбца, чтобы открыть список со всеми уникальными значениями этого столбца.
4. Из списка автофильтра выберите нужное значение. Список мгновенно будет отфильтрован.
Замечание. После фильтрации изображение стрелки на кнопке автофильтра будет иметь синий цвет для всех столбцов, которым назначен критерий фильтрации.
Кроме уникальных значений в список автофильтра включаются категории
Все – фильтрация по этому столбцу будет прекращена и список вернется в исходное состояние. Однако, если задана фильтрация по другим столбцам, то некоторые строки по-прежнему останутся скрыты.
10 первых – эта функциональная возможность предназначена для работы исключительно с числовыми столбцами. При выборе данного фильтра будут отображаться строки, соответствующие значения которых попадают в 10 наибольших элементов списка.
Стандартный фильтр. – эта категория позволяет создавать более гибкий фильтр. При выборе данного пункта открывается соответствующее диалоговое окно (рис. 2). С помощью этого диалогового окна можно создать фильтр, включающий до трех условий, связанных логическими связками И и ИЛИ.
В диалоговом окне стандартного фильтра среди уникальных значений поля имеется еще два пункта: – не пусто и – пусто. Если выбрать один из этих пунктов, будут выводиться только те строки, у которых в данном столбце расположены, соответственно, пустые или непустые ячейки.
Рис. 2. Диалоговое окно создания стандартного фильтра
Замечание. Когда необходимо фильтровать список только по одному или нескольким столбцам, а не по всем столбцам, нужно выделить соответствующие ячейки, и затем выполнять фильтрацию.
Для удаления автофильтров нужно повторно выполнить команду Данные – Фильтр – Автофильтр. Тогда флажок, установленный напротив команды Автофильтр, будет снят, и все списки для фильтрации будут уничтожены.
Использование расширенного фильтра
Расширенный фильтр предназначен для фильтрации писков тогда, когда возможностей автофильтра недостаточно. Хотя расширенный фильтр и несколько сложнее автофильтра, он имеет ряд преимуществ:
• можно сохранять критерий отбора для дальнейшего использования;
• для одного столбца можно задать более трех критериев отбора;
• между столбцами можно задать несколько критериев сравнения;
• можно показывать в отфильтрованных записях не все столбцы, а только указанные;
• в критерии можно включать формулы, что дает возможность отфильтровать строки, например, со значением в поле Размер меньшим значения выбранной ячейки;
• можно выводить только уникальные значения;
• вместо того, чтобы скрывать не соответствующие критерию строки, можно копировать строки, удовлетворяющие условию, в новый список.
Чтобы отфильтровать список с помощью расширенного фильтра, необходимо произвести некоторые подготовительные действия.
1. Проверить, чтобы столбцы списка имели заголовки.
2. Подготовить таблицу критериев отбора. Заголовки столбцов, для которых будут задаваться условия, должны в точности совпадать с заголовками списка, поэтому имеет смысл их просто скопировать из списка.
3. В следующих строках необходимо записать условия фильтрации. Эти строки будут использованы в качестве диапазона условий отбора. Для объединения критериев с помощью условного оператора И следует указать критерии в одной и той же строке, а для объединения критериев с помощью условного оператора ИЛИ следует ввести критерии в разных строках.
Когда критерии отбора данных будут сформированы, необходимо поставить курсор в любую ячейку исходного диапазона и выполнить команду Данные – Фильтр – Расширенный фильтр. На экране появится диалоговое окно расширенного фильтра:
Рис. 3. Диалоговое окно расширенного фильтра
В пустом поле следует указать диапазон ячеек, содержащих критерии отбора. По умолчанию результаты расширенного фильтра отображаются в исходном диапазоне ячеек (часть строк при этом просто не отображается).
Для отбора записей в другое место необходимо нажать кнопку «Еще» . Диалоговое окно примет вид:
Рис. 4. Диалоговое окно расширенного фильтра с параметрами
Чтобы поле ввода диапазона ячеек размещения результата стало доступно, активизируйте флажок Поместить результат в: и укажите адрес ячейки, в которой будет располагаться левый верхний угол результирующей таблицы
Чтобы снова вывести все записи таблицы, следует в меню выбрать команду Данные – Фильтр – Удалить фильтр.
Можно создать в Calc структуру, которая позволит группировать информацию по разделам, сворачивая/разворачивая ее при надобности. Структуру можно создать вручную, либо автоматически.
При ручном структурировании сначала надо определить нужные элементы – выделить диапазоны ячеек, которые должны быть сгруппированы, затем в меню Данные – Группа и структура – Группировать.
После выполнения команды выделенные строки/столбцы становятся разделом. Нажав на знак + , можно увидеть детали раздела (рис. 5, а), нажав на знак -, можно скрыть лишние детали.
Рис. 5. Структуры данных
• Если при сворачивании раздела, определенные строки/столбцы должны быть видимыми, их не нужно выделять для группировки.
• Структурирование производится автоматически при формировании промежуточных итогов.
Автоматически создать структуру можно следующим образом: в диапазоне, предназначенном для структурирования, необходимо выделить хотя бы одну ячейку, а затем выполнить команду Данные – Группа и структура – Создать структуру. В результате будет создана структура, использующая итоговые данные как уровни разделов.
Удалить структурирование можно, используя команды Данные – Группа и структура – Удалить.
Задание 1. Использование автофильтра
1. Создайте новую рабочую книгу и сохраните в своей папке. Первому листу дайте название «Фильтр»
2. Создайте следующую таблицу:
3. Установите курсор на одну из ячеек таблицы и выполните команду Данные – Фильтр – Автофильтр. Excell проанализирует список и добавит в строку заголовков полей кнопки раскрывающихся списков (кнопки автофильтра):
Рис. 6. Фрагмент таблицы с автофильтром
4. Щелкните на кнопке автофильтра в ячейке Отдел. Список раскроется и покажет все значения, содержащиеся в этом столбце.
5. Выберите наименование отдела Бухгалтерия, и OpenOffice.org Calc спрячет все строки, кроме тех, которые включают отмеченное значение. Другими словами, критерием отбора служит выбранное вами значение.
Рис. 8. Результат отбора по критерию Бухгалтерия
После фильтрации изменился цвет кнопки автофильтра , для напоминания о том, что список отфильтрован по значениям, содержащимся в этом столбце.
6. Чтобы отобразит весь список полностью, щелкните снова на кнопке автофильтра и выберите опцию Все.
7. Сохраните файл.
Задание 2. Фильтрация по нескольким критериям
1. Щелкните на кнопке автофильтра в ячейке Годовой фонд заработной платы. Выберите опцию Стандартный фильтр…. Открывшееся диалоговое окно Стандартный фильтр позволяет фильтровать списки с использованием нескольких критериев.
2. Необходимо вывести список сотрудников с годовым фондом зарплаты в диапазоне от 80 000р. до 100 000р. Для этого:
Для поля Годовой фонд заработной платы из раскрывающегося списка Условие выберите критерий больше (>), в поле Значение введите или выберите из списка значение критерия, например, 80 000р.
Установите переключатель И. Выберите соответствующее имя поля.
Для второго критерия в поле Условие выберите меньше ( Задание 3. Расширенный фильтр
1. Скопируйте исходную таблицу на Лист 5.
2. Для использования расширенного фильтра нужно сначала создать дополнительную таблицу для задания критерия отбора. Например, в диапазоне J 1: K 2 создадим таблицу:
4. В открывшемся диалоговом окне устанавите курсор в поле Взять критерий фильтра из и выделите мышкой диапазон ячеек J 1: K 2.
5. Включите переключатель Поместить результат в, установите курсор в поле справа и мышкой щелкните по ячейке А20. Нажмите кнопку Ок.
Рис. 10. Создание расширенного фильтра.
Расстояние между исходным диапазоном и диапазоном, в который нужно скопировать результат фильтрации, должно составлять минимум одну строку. В таблице критериев (> 9000) между знаком > и числом обязательно должен стоять пробел.
Задание для самостоятельной работы:
1. Выведите список всех сотрудников, принятых на работу после 2003 года в бухгалтерию.
2. Выведите список всех Иванов, работающих в цеху №2.
Задание 4. Сортировка списка
Сортировка по одному полю
1. Скопируйте лист с исходной таблицей и дайте ему название Сортировка.
2. Установите курсор в ячейку с фамилией первого сотрудника. Щелкните на кнопке сортировки Сортировка по возрастанию, расположенной на стандартной панели инструментов. Программа расположит список в алфавитном порядке.
3. Щелкните на кнопке сортировки Сортировка по убыванию, расположенной на стандартной панели инструментов. Программа расположит список в обратном алфавитном порядке.
Задание для самостоятельной работы:
1. Отсортируйте список так, чтобы сведения о сотрудниках располагались в порядке возрастания (убывания) окладов.
2. Представьте список так, чтобы сведения о сотрудниках располагались в порядке возрастания (убывания) даты приема на работу.
Сортировка по нескольким полям
1. Выполните сортировку по нескольким полям: сначала по полю Фамилия, затем по полю Годовой фонд зарплаты. Для этого:
выберите команду Данные – Сортировка.
В диалоговом окне Сортировка в списке Сортировать по выберите поле Фамилия, установите опцию по возрастанию.
В списке Затем по выберите поле Годовой фонд зарплаты, установите опцию по убыванию.
Нажмите кнопку ОК.
В результате данные столбцов будут отсортированы, но не просто «по возрастанию», а будут соблюдаться условия приоритетности:
Данные столбца «Фамилия» будут отсортированы точно по возрастанию;
Данные столбца «Годовой фонд» будут отсортированы по убыванию, но уже в зависимости от сортировки данных столбца «Фамилия».
Задание для самостоятельной работы:
1. Представьте сведения о сотрудниках, расположив наименования отделов в алфавитном порядке. Сотрудников, работающих в одном и том же отделе, отсортируйте по датам приема на работу и далее по возрастанию оклада.
Задание 5. Создание структуры
1. Скопируйте исходную таблицу на новый лист и дайте ему название «Структура».
2. Отсортируйте таблицу по фамилиям в алфавитном порядке.
3. Сгруппируйте сотрудников, фамилии которых начинаются с одной буквы. Для этого:
Для группировки фамилий на «А» выделите строки 2 и 3.
Выберите команду Данные – Группа и структура – Группировать. Слева от этих строк появиться элемент . Если нажать на кнопку , эти строки можно скрыть.
Аналогично сгруппируйте фамилии на «Б» и «К».
4. Сгруппируйте столбцы В и С, и Е и F . Должна получится таблица вида:
Рис. 11. Результат группировки строк и столбцов.
5. Для удаления структуры нужно развернуть группу, поставить курсор в одну их ее ячеек, и выбрать команду Данные – Группа и структура – Разгруппировать.
Задание для самостоятельной работы:
1. Представьте сведения о сотрудниках, расположив наименования отделов в алфавитном порядке. Сгруппируйте сотрудников, работающих в одном и том же отделе, и сведения по окладам и годовому фонду зарплаты.
Что называется фильтрацией списка?
Как используется автофильтр?
Как применить расширенный фильтр?
Как работает опция Стандартный фильтр?
Как отсортировать данные по одному полю?
Как отсортировать список по нескольким полям?
Что такое структура? Как создать структуру автоматически?