Материал: 64

31

–в списке нельзя объединять ячейки;

–заголовки полей списка должны быть уникальными;

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

Сортировка данных в списке

При сортировке данных в списке следует руководствоваться сформулированным ранее правилом – каждая запись списка характеризует конкретный объект. Отсюда следует, что при правильном выполнении сортировки должен изменяться порядок следования записей, а не значений данных в отдельных полях.

Для выполнения сортировки записей списка по значениям данных в одном поле следует поставить указатель в любую ячейку этого поля, далее нажать кнопку прямого или обратного порядка сортировки в группе Сортировка и фильтр на вкладке ленты Данные. Если выбран прямой порядок сортировки, в поле, по которому выполняется сортировка, текстовые данные будут расставлены по алфавиту, числа – от меньшего к большему, даты – от более ранней к более поздней. При обратном порядке сортировки порядок расположения данных будет противоположным. Не рекомендуется перед выполнением сортировки выделять данные в поле – при недостаточно аккуратной работе список может быть испорчен.

Если планируется сортировка по нескольким полям, в группе Сортировка и фильтр следует нажать кнопку Сортировка для вывода диалогового окна, в котором следует определить параметры сортировки (рисунок 14).

Рисунок 14 – Диалоговое окно для создания многоуровневой сортировки записей списка

Параметры, заданные в диалоговом окне, представленном на рисунке 14, позволяют выполнить двухуровневую сортировку записей списка. Вначале запи-

32

си будут рассортированы по районам расположения квартир (по алфавиту), затем внутри этих групп – по датам приватизации квартир (в хронологическом порядке).

MS Excel позволяет создать с помощью пользовательского списка порядок сортировки, отличающийся от стандартного. Предположим, записи списка должны сортироваться в следующем порядке: Южный, Восточный, Северный районы. Для реализации возможности такой сортировки необходимо в диалоговом окне Сортировка для поля Район открыть список в поле Порядок (рисунок 14) и выполнить команду Настраиваемый список…. На экране появится диалоговое окно Списки, инструменты которого позволяют сформировать новый пользовательский список. В дальнейшем этот список можно будет выбирать в поле Порядок диалогового окна Сортировка.

Выборка данных из списка с помощью автофильтра

Автофильтр позволяет выбирать из списка записи, удовлетворяющие некоторым условиям отбора. Остальные записи при этом на рабочем листе не отображаются.

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

В правом нижнем углу каждой ячейки с заголовком поля появится кнопка со стрелкой. Щелчок мышью на кнопке, расположенной в заголовке некоторого поля, открывает список уникальных значений данных, имеющихся в этом поле. Для выборки требуемых сведений из списка при решении простых задач достаточно включить флажки только возле нужных значений данных.

Более широкие возможности для создания условий отбора записей из списка предоставляют появляющиеся также после нажатия кнопки со стрелкой ко-

манды Текстовые фильтры, Числовые фильтры, Фильтры по дате (вид ко-

манды зависит от типа данных в соответствующем поле списка). После выполнения любой из этих команд появляется меню, позволяющее задавать критерии отбора непосредственно (например, Выше среднего, Сегодня, С начала года)

или открывать диалоговые окна для их ввода (например, равно…, между…, на-

чинается с…). Универсальной является команда Настраиваемый фильтр….

На рисунке 15 изображено окно, открытое с помощью этой команды, в котором заданы условия выборки из списка сведений о квартирах с жилой площадью в диапазоне от 50 до 75 квадратных метров.

33

Рисунок 15 – Диалоговое окно автофильтра с заданными условиями отбора для жилой площади квартир

В условиях отбора для полей с текстовыми данными можно использовать подстановочные знаки * (последовательность любых символов) и ? (один символ). Например, если ввести для поля Адрес таблицы, изображённой на рисунке 13, условие отбора в виде равно М*, из списка будут выбраны квартиры, расположенные на улицах Мира и Майской. Критерий содержит е?о, созданный в поле Адрес, позволяет найти квартиры, расположенные на улицах Чехова и Седова.

С помощью автофильтра из списка можно выбирать заданное количество максимальных или минимальных значений числовых данных. Для реализации этой возможности выполняются команды Числовые фильтры и Первые 10…, затем в диалоговом окне Наложение условия по списку вводятся необходимые критерии (рисунок 16).

Рисунок 16 – Диалоговое окно для выбора из списка двух квартир с наименьшей общей площадью (условие отбора определено для поля списка

Общая площадь)

34

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

После создания и применения критериев для выборки данных размеры списка уменьшаются – в нём остаются только записи, удовлетворяющие условиям отбора. Критерии можно вводить друг за другом в нескольких полях списка, в результате в списке останутся только те записи, которые удовлетворяют всем введённым условиям отбора.

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

Для выключения автофильтра нужно нажать кнопку Фильтр в группе

Сортировка и фильтр на вкладке ленты Данные.

Расширенный фильтр

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

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

Этап 1. Создаются заголовки диапазона условий – в пустые ячейки рабочего листа копируются заголовки полей списка (напоминаем, что ячейки с любыми данными на рабочем листе должны отделяться от списка пустыми строками или столбцами). Можно скопировать заголовки всех полей списка или только тех полей, для которых планируется создавать условия отбора. Ниже заголовков диапазона условий необходимо оставить несколько пустых строк.

Этап 2. Под заголовками диапазона условий вводятся критерии для выборки нужных данных (правила их формирования будут рассмотрены далее).

Этап 3. Выполняется процедура фильтрации. Для этого следует нажать кнопку Дополнительно в группе Сортировка и фильтр вкладки ленты Дан-

ные, затем в диалоговом окне Расширенный фильтр (рисунок 17) установить параметры фильтрации.

35

Рисунок 17 – Диалоговое окно для ввода параметров фильтрации расширенного фильтра

Спомощью переключателей группы Обработка определяется, куда будут выводиться результаты фильтрации. Выбор переключателя фильтровать список на месте приведёт к ситуации, знакомой по работе с автофильтром – размеры списка уменьшатся и в нём остаются только записи, удовлетворяющие условиям отбора. При выполнении следующего запроса полученные результаты будут утрачены, если они не были своевременно скопированы в другое место рабочего листа. Поэтому более рациональным представляется выбор переключа-

теля скопировать результат в другое место.

В поле Исходный диапазон: указывается (например, выделяется мышью) диапазон ячеек, в котором находится список. При этом обязательно должны быть выделены заголовки полей списка. Если в списке заранее был установлен указатель, исходный диапазон будет выделен MS Excel автоматически.

В поле Диапазон условий: указывается диапазон ячеек, в который введены критерии для выборки данных. При этом необходимо выделить заголовки диапазона условий. Допускается выделять весь диапазон условий или только те его столбцы, в которых заданы критерии.

Спомощью поля Поместить результат в диапазон: должен быть определён диапазон ячеек, куда будут выведены результаты фильтрации. Лучше указать только одну ячейку – левый верхний угол планируемого целевого диапазона. В этом случае MS Excel автоматически сформирует диапазон нужных размеров.

Если результаты фильтрации предполагается получить только для отдельных полей списка, предварительно следует скопировать заголовки этих полей в