2.12. Работа со списком

Если данные представлены списком, Excelпредоставляет различные возможности и функции их обработки.

Список– это упорядоченный набор данных. Обычно список состоит из строки заголовков и дополнительных строк данных, которые могут быть числовыми, текстовыми, датами и т.д.

Данные, организованные в список, часто называют Базами Данных табличного типа.

Столбцы списка называют полями. Поля имеют свои названия (заголовки). Поля бывают простые и вычисляемые. Строки списка называются записями.

К основным операциям, которые можно выполнять со списком относятся:

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

• фильтрация списков по определенным критериям, с целью выборочного отображения строк;

• сортировка списка;

• вставка формул для подведения промежуточных итогов;

• создание с помощью сводной таблицы итоговой таблицы данных списка.

Основные правила создания списка

1. Заголовки (по одному для каждого столбца) следует помещать в первую строку списка. Она называется строкой заголовков. Если заголовки длинные, необходимо использовать опциюПереноса по словам, чтобы не расширять столбец.

2. В каждом столбце должна содержаться однотипная информация. Например, не следует смешивать в одном столбце даты и обычный текст.

3. Нельзя использовать пустые строки в списке. Пустая строка означает конец списка.

4. Желательно помещать список на отдельном листе.

   

5. Используйте команду Вид - Окно – Закрепить области,

чтобы заголовки были всегда видны при прокручивании листа списка.

6. Данные в столбцах должны иметь один формат отображения (формат Дата, Общий, Числовой и т.д.).

2.12.1. Ввод, редактирование, поиск данных в списке

Данные можно ввести 3 способами: вручную, используя стандартные методы ввода данных, с помощью диалогового окна, которое вызывается командой Форма(Настройка панели быстрого доступа - Другие команды- Все команды - Форма), спомощью импорта или копирования данных из другого файла.

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

Для поиска данных следует в диалоговом окне использовать кнопку Критерии.

Перед выполнением поиска следует сделать текущей первую запись списка. Поиск идет от текущей записи вниз. Для просмотра всех записей, которые выбраны в результате поиска, необходимо использовать кнопки Далее или Назад.

При поиске можно применять символы * и ?, а также операторы сравнения: =, < >, >, <, < =, >=.

Чтобы задать несколько критериев поиска, следует их указать в различных полях. Задание нескольких критериев позволяет ограничить область поиска.

Например: в поле ФИО введено М* –, то есть фамилия начинается на букву М, в поле Год рождения – < 31.12.91.

2.12.2. Использование фильтров

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

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

Фильтрацию данных можно выполнять с помощью АвтофильтраиРасширенного фильтра. Автофильтр применяется для простых условий отбора; Расширенный фильтр – для более сложных условий отбора.

Работа с автофильтром.Первоначально следует выделить любую ячейку внутри списка. Затем в менюДанные - Сортировка и фильтрвыбрать командуФильтр.MSЕхсе1 выведет рядом с каждым заголовком столбцовкнопкиАвтофильтровв виде стрелок. После щелчка по кнопке Автофильтра откроется список команд с помощью, которых можно задать условия отбора данных.

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

Команда Условия предназначена для фильтрации списка одновременно только по двум критериям отбора, которые задаются в диалоговом окне. Для соединения условий используется логические операцииИиИЛИ. Логическая операцииИиспользуется очень часто для задания числового диапазона, например, от 10 до 100.

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

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

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

Работа с Расширенным фильтром. Расширенный фильтр предоставляет следующие возможности:

• задать условия, соединенные логическим оператором ИЛИ для одного или нескольких столбцов;

• задать вычисляемые условия,

• перемещать копии строк, отвечающих определенному критерию, в другое место от списка.

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

Порядок создания Расширенного фильтра:

• создать диапазон критериев;

  

• в меню Данныевыбрать команду;

• в диалоговом окне задать необходимые параметры (исходный диапазон, диапазон условий и другие).

Блок критериев (диапазон условий) – это специально отведенная область листа рабочей книги, отвечающая следующему требованию:

• диапазон критериев должен состоять, по крайней мере, из двух строк, первая из которых должна содержать все или некоторые заголовки полей списка.

• остальные строки должны содержать условия (критерии) фильтрации.

В диапазоне критериев можно ввести любое количество условий, MSЕхсеlих понимает в соответствии со следующими правилами:

• условия на одной строкев разных столбцах считаются соединенными логическим операторомИ;

• условия на разных строкахсчитаются соединенными логическим операторомИЛИ;

• пустая ячейка в диапазоне критериев означает любое значение для соответствующего столбца;

• в Расширенный фильтр нельзя включать пустые строки.

Типы критериев. Элементы, которые указываются в диапазоне критериев, можно разделить натекстовые или числовые критерии. При фильтрации используется сравнение с числом или строкой с помощью операторов сравнения: =, < >, >, <, >=, <=. Например, <500, > К – слова, начинающиеся на Л до Я, <> С – все слова, кроме слов, начинающихся на С. В критериях допускается использование символов * и ?. В текстовых сравнениях не различаются строчные и прописные буквы. Например, Сильный, сильный.

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

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

Правила создания блока критериев с простыми (не вычисляемыми) условиями:

• блок критериев должен состоять из заголовка и условий отбора, которые записываются под ним;

• заголовок в блоке критериев должен точно совпадать с заголовком отдельного поля в списке по всем параметрам (тексту названия, форматам и т.д.). Поэтому рекомендуется заголовок из списка копировать в блок критериев.

Правила создания блока критериев с вычисляемыми условиями:

• заголовок над вычисляемым критерием должен отличаться от любого заголовка столбца в списке;

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

• при записи вычисляемого условия ссылки на ячейки, находящиеся вне списка, должны быть абсолютными, а ссылки на ячейки внутри списка должны быть относительными;

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

• результатом сравнения должны быть значения Истина или Ложь.

Примечания:

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

2. При каждом выполнении команды Расширенный фильтр Ехсе1 просматривает полный список, а не текущее множество ранее отфильтрованных строк. Поэтому необязательно использовать команду Показать всеперед изменением фильтра.

Пример блока критериев с вычисляемым условием представлен в таблице.

Таблица 2.4.

Пример блока критериев с вычисляемым условием (ячейки А50:А51)

	А	Пояснения
50	Вывести	Заголовок над вычисляемым условием
51	=С2 > СРЗНАЧ($С $2: $C$40)	Условие отбора записей. С2 – первая ячейка столбца отбора.
	Истина или Ложь	Результат вычислений в ячейке А51

Копирование отфильтрованных строк в другую часть листа. Чтобы скопировать результат фильтрации в другое место следует в окне диалога Расширенный фильтр сначала включить кнопкуСкопировать результат в другое место, а затем в полеПоместить результат в диапазонввести адрес верхней левой ячейки, тем самым отметив начало диапазона копирования. Кроме того, можно добавить дополнительный фильтр к заданным условиям отбора с помощью флажка включенияТолько уникальные записи.Он скрывает повторяющиеся строки

Копирование отфильтрованных строк на другой лист. Чтобы поместить результат фильтрации в другой лист рабочей книги, необходимо обязательно на этом же листе создать диапазон критериев отбора. Сам список данных может находиться на любом другом из листов рабочей книги. Затем выполнить командуРасширенный фильтр – Фильтрв менюДанные. При выполнении данной команды первоначально должен быть обязательно активным (то есть отображаться на экране) тот лист, где расположен блок критериев.

Если вначале работы будет активным лист с БД, то результаты не копируются на другой лист.