Название: Обработка табличной информации - учебное пособие (Бычков М.И.)

Жанр: Информатика

Просмотров: 987


6.   работа со списками

 

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

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

На список накладываются более строгие ограничения, чем на обычную таблицу Excel. Основными из них являются:

первая строка списка рассматривается как заголовок

таблицы;

столбец списка должен содержать однотипную инфор-

мацию;

данные в строке должны быть взаимосвязаны и характеризовать отдельный объект;

от других данных список отделяется пустыми строками и столбцами;

ссылки на ячейки вне списка могут быть только абсолютными.

В меню Данные содержатся команды, выполнение которых обеспечивает следующие операции над списками:

ввод и удаление записей;

изменение порядка строк в списке (сортировка);

подведение промежуточных итогов;

выборочное отображение данных на экране по условию,

заданному пользователем (фильтрация);

форматирование фрагментов списка;

создание отчетной ведомости в виде сводной таблицы.

Рекомендации по созданию списка:

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

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

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

не допускать пробелов в начале и конце ячеек;

в одном столбце размещать только однотипные данные;

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

важные данные располагать сверху или снизу списка;

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

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

Способы работы со списками

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

 

Рис. 4

 

Действия над списком осуществляются установкой указателя на любую ячейку списка и выполнением одной из команд в меню Данные.

Форма – это диалоговое окно для ввода и отображения одной строки списка (рис. 4.). Она может также использоваться для поиска и удаления записей.

Чтобы использовать форму нужно установить указатель на любую ячейку списка и выполнить команду Данные/Форма. Вверху каждого столбца списка должен быть заголовок. Форма может одновременно отображать до 32 полей (столбцов) списка.

Фильтрация записей

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

Фильтрация записей в списке с помощью автофильтра 

Использование автофильтра не требует особых пояснений. Достаточно установить указатель на любую ячейку списка и дать команду Данные/ Фильтр/Автофильтр. Ее выполнение приведет к появлению пиктограмм со стрелками рядом с названиями столбцов списка (рис. 5). Щелчок на пиктограмме позволяет высветить перечень значений соответствующего столбца списка. Если теперь выбрать одно из этих значений, то в списке останутся только те элементы, значения которых совпадают с выбранным. Пиктограмма и номера элементов списка при этом меняют цвет. По этому признаку можно определить, что список был профильтрован по данному полю.

 

 

Рис. 5

 

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

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

Элемент Условие… позволяет отобразить только те данные списка, которые удовлетворяют заданным условиям.

Для отмены фильтра требуется повторно выполнить команду Данные/ Фильтр/Автофильтр.

Фильтрация записей в списке с помощью расширенного фильтра

Для фильтрации по критериям, не предусмотренным в автофильтре, применяется Расширенный фильтр.

До использования Расширенного фильтра нужно над или под списком задать диапазон критериев, отделив его от списка пустой строкой (рис. 6.).

 

Рис. 6

 

Диапазон критериев должен содержать как минимум две строки, первая из которых должна включать названия выбранных полей списка, а другая (или другие) – критерии фильтрации. Критерии фильтрации подразделяются на текстовые, числовые и вычисляемые. Текстовые используют сравнение со строкой с помощью операторов =, >, >=,<, <=, <>. Числовые, используют сравнение с числами в соответствующих столбцах таблицы с помощью перечисленных выше операторов. Вычисляемые основываются на одном или нескольких расчетах. Используемые элементы условий отбора приведены в приложении 10.

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

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

Ключ Только уникальные записи в диалоговом окне позволяет при отсутствии диапазона критериев убрать из списка повторяющиеся записи.

Результат использования расширенного фильтра для списка, приведенного на рис. 6, показан на рис. 7.

 

 

Рис. 7

 

Отказаться от Расширенного фильтра можно, выполнив команду Данные/ Фильтр/Отобразить все.

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

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

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

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

В качестве примера можно привести формулу =G5>СРЗНАЧ($E$5:$E$14), результаты выполнения которой позволят отобразить только те строки списка, которые содержат в столбце G значения, большие чем среднее значение ячеек диапазона E5:E14. Здесь G5 – это относительная ссылка на название столбца, по которому будет произведена фильтрация. При этом ячейка для заголовка условия отбора, расположенная над ячейкой, содержащей формулу, может быть пустой.

Следует подчеркнуть, что ссылка на подпись столбца или на соответствующее поле в первой записи списка должна быть относительной, а все остальные ссылки в формуле должны быть абсолютными. Формула должна возвращать результат ИСТИНА или ЛОЖЬ.