3.12. Обработка списков

3.12.1. Формирование списка

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

Столбцы в списке являются полями записи в базе данных.

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

Каждая строка в списке является записью в базе данных. Необходимо избегать пустых строк и столбцов внутри списка. На рис. 3. 34 приведен список из семи столбцов. ГП ж "чмт ssssasisfs® : -.-?"іг-ш,''' І if \ ^МІЯМІТ Страна производнтапь V Аспирин 38 000,00 р. | ЗО 12 Июнь, 1998 РБ И Аскаф Т 64 200,00 р. [ 42 25 Май, 1980 США 1 1 Эфферолган і 54 ёсаоо р.Г "50 29 Ноябй^ОТО США Ноогропил 104 0DQ.CI0 р і 60 5 Декабрь. 1997 Польша г Панадол 52 000,00 p.'j 100 7 Октябрь, 2001 'Фракция * Смекга , 5 000,00 р., 75 25 Декабрь, 1997 Франция • Цугтромон | 5 700,00 pi ОТ б Июнь, 1999 РБ Парацетамол 53 500.00 p. j so, 17 Апрель 2000 РБ Бромергон 175 000,00 g.| ' 10 27 Сентябрь, 2001 Польша Рис. 3.34. В первой строке списка содержатся имена столбцов, каждый из которых содержит однородную информацию

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

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

Выполните команду Закрепить области из меню Окно.

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

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

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

Для добавления новой строки (записи) в список щелкните кнопку Добавить. Excel выведет пустую форму, в поля которой можно вводить значения новой строки. Для возврата на рабочий лист щелкните кнопку Закрыть.

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

3.12.2. Сортировка списков и других интервалов

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

При сортировке обращайте внимание на ячейки с формулами.

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

Чтобы избежать проблем с сортировкой списков и интервалов, содержащих формулы, соблюдайте следующие правила: •

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

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

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

Сортировка по одному столбцу

Для сортировки списка сначала выделите в нем какую-нибудь ячейку (не интервал) и выполните команду Сортировка из меню Данные.

Рис. 3.36. Диалоговое окно Сортировка диапазона используется для задания параметров сортировки

В раскрывающемся списке поля Сортировать по выберите поле, по которому нужно сортировать список. Для выбора порядка сортировки установите переключатель по возрастанию или по убыванию. Проверьте, установлен ли в разделе Идентифицировать поля но переключатель Подписям (первая строка диапазона) (для того, чтобы заголовки столбцов не сортировались и остались в начале списка) и щелкните ОК.

Сразу же проверьте результат сортировки и если он Вас не устроит, то воспользуйтесь командой Отменить из меню Правка и восстановите предыдущий порядок строк в списке.

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

Сортировка по нескольким столбцам

Excel дает возможность отсортировать данные на рабочем листе за один раз не более чем по трем столбцам (рис. 3.37). Рис. 3.37. В раскрывающихся списках Сортировать по и Затем по и В последнюю очередь по выбраны заголовки полей для сортировки

по трем столбцам

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

Сортировка части списка

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

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

Сортировка по столбцам

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

Сортировка в Excel не ограничивается стандартным упорядочением по возрастанию или по убыванию. Если нужно отсортировать список в особом; порядке, то можно определить соответствующую последовательность сортировки.

Для создания новой пользовательской последовательности сортировки выполните .следующие действия: 1.

Выберите команду Параметры из меню Сервис и щелкните вкладку Списки. 2.

В разделе Списки выберите Новый список. 3.

В разделе Элементы списка напечатайте элементы списка в нужном порядке через запятую или столбиком и щелкните ОК.

Для удаления пользовательского списка выберите его на вкладке Списки и щелкните кнопку Удалить.

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

3.12.3. Анализ списка с помощью фильтров

Отфильтровать список - это значит скрыть все строки кроме тех, которые удовлетворяют заданным критериям. Excel предоставляет две команды фильтрации: Автофильтр - для простых критериев и Расширенный фильтр - для более сложных критериев.

Команда Автофильтр (AutoFilter)

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

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

1 Рис. 3.38. Строки, относящиеся к стране производителю РБ отфильтрованы по команде Автофилы р

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

Автофильтр по нескольким столбцам

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

Настройка пользовательского автофильтра для более сложных критериев

Критерий заданного на рис. 3.40 фильтра состоит всего из одного условия со знаком равенства. Можно создать пользовательский автофильтр с более сложным критерием, состоящим из одного или двух условий с любыми знаками сравнения. Такие критерии позволяют отфильтровать значения, находящиеся в некотором интервале. Чтобы создать пользовательский автофильтр, раскройте список критериев для требуемого столбца и выберите элемент Условие. Откроется диалоговое окно Пользовательский автофильтр (рис. 3.39).

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

Предположим, что в списке (рис. 3.34) нужно просмотреть только записи со значениями столбца Срок хранения до, большими 1 января 1998 г. Для этого нужно заполнить диалоговое окно, как показано на рис. 3.40.

Поиск текстовых значений в определенном алфавитном интервале

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

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

Символы шаблона в пользовательских критериях

При создании критериев можно использовать два символа

шаблона: •

звездочка (*) для представления любой последовательности символов; •

вопросительный знак (?) для представления любого отдельного символа.

Допустимы любые комбинации символов шаблона.

Удаление автофильтров

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

Команда Расширенный фильтр

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

» Создавать критерии с условиями по нескольким столбцам, связанным по правилу ИЛИ (OR). •

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

« Создавать вычисляемые критерии.

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

Интервал критериев

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

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

Пример критерия с двумя условиями, объединенными по правилу ИЛИ. Пусть из списка студентов, приведенного на рис. 3.34, нужно выделить как лекарства, производимые в США, так и лекарства, имеющие стоимость менее 80 ООО р. Для создания такого фильтра выполните следующие действия: 1.

Вставьте несколько строк для интервала критериев выше списка на рабочем листе (выберите команду Строка из меню Вставка). 2.

Создайте интервал критериев, как показано на рис. 3.41.

nj І.ША.1 ЧЦй" ~" ? j .-"".7 ^.VI n~'~:"Lrr~~L и [ІіІЩЩ ."7іТ—. ip", X - "Я Страна производителе Цена США |<80000 Наименование Ціна Количество Срок хранения до Страна производите. ІАспирин 38 000,00 р. 30 12 Июнь, 1998 РБ Аскаф 54 200,00 р. 42 25 Май, 1990 США Эфферолган 54 600J)0JJ. 50 29 Ноябрь, 2000 США ІНоотропил 104 000,00 р. Г во 5 Декабрь. 1997 Польша ІПанадол 52 000,00 а, 100 7 Октябрь^ 2001 Франция [Смекга 5 000,00 р. 75 25Двкабрь,1997 Франция Цитроном 5 7OO]0Dp 20 І в Июнь, 1999 ГРБ [Парацетамол 53 SOCyiO р. 80 < 7 Апрель, 2000 РБ [Бромергон 175 000,00 jj. , 10 27 Сентябрь, 2001 Польша Рис. 3.41. Интервал А1:ВЗ содержит критерии для выделения из списка лекарств, производимых в США, и имеющих стоимость менее 80 ООО р. 3.

Выберите команду Расширенный фильтр из подменю Фильтр меню Данные и введите в соответствии с рис. 3.42 данные в диалоговом окне Расширенный фильтр. 4.

Убедитесь, что установлен переключатель Фильтровать список на месте и щелкните ОК. Рис. 3.42. Диалоговое окно Расширенный фильтр задает положение списка и интервала критериев

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

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

Условия в одной строке объединяются по правилу И. •

Условия на отдельных строках объединяются по правилу ИЛИ.

Пример критерия с тремя условиями по одному столбцу, объединенными по правилу ИЛИ. Предположим теперь, что в списке на рис. 3.34 нужно выделить лекарства, начинающиеся с букв А, П или С. В интервал критериев следует включить заголовок столбца Наименование и ввести буквы А, П и С в три ячейки под этим заголовком. Открыв по одноименной команде диалоговое окно Расширенный фильтр и задав ссылки на список и интервал критериев, Вы получите результат, показанный на рис.3.43.

Пример критерия с условиями, объединяемыми по правилам И и ИЛИ одновременно. Пусть необходимо найти лекарства, которые начинаются с букв А, С и которых на складе имеется больше 35. Для этого следует создать интервал критериев, показанный на рис. 3.44. ІЩіІНаиценованиа

?НА

вДс' ;. Количество >35 >36 ^S Наименование Цена Количество Срок хранения до Страна производите ЯШАскаф

ЖЩСмекта "1 54 200,00 р. 42 25 Май, 1990 США 5 00gj>0j). 75 25 Декабрь, 1997 Франций Рис. 3.44. Список лекарств, полученный с помощью интервала критериев А1:ВЗ

Текстовые критерии

Обработка текстовых критериев выполняется в Excel по следующим правилам: •

Если задана одна буква, то по равенству (=) будут найдены все начинающиеся на эту букву значения. •

По условиям больше (>) или меньше (<) будут найдены значения, которые располагаются в алфавитном порядке соответственно после или до заданного значения. •

По критерию ="=текст" выделяются значения, точно совпадающие с заданным выражением текст. Например, для поиска записей с фамилией Иванов, следует задать ="=Иванов". Если задать не формулу, а просто Иванов, то будут выделены записи с фамилиями Иванов, Иванова, Ивановский и т. п. •

Символы шаблона обрабатываются так же, как и в автофильтре.

Вычисляемые критерии

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

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

Ссылки на ячейки вне списка должны быть абсолютными. •

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

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

При создании такого фильтра в ячейку Е1 вне списка была введена формула

=СРЗНАЧ(В7 :В 15)

Затем в ячейку А2 было введено условие вычисляемого критерия с абсолютной ссылкой на эту "внешнюю" ячейку:

=В7>$Е$1 . 1 швшшят шшшшшшш шявек ЯІІЗЖШІ JL а лекарства больше средней цены J ЛОЖЬ ! '; "" " ! Средняя ценз j [ 60 222,22 р JL в. ; j "" "j 1 ~ ! - і і 4 Наименование j Цена Количество Срок хранения доСтрана пронзве ffiffi гро пил 1104 000,00 р.І 60 5 Декабрь, 1997;Польша ЖУ мергон 1175 000.00 p.I 10' L 27 Сентябрь, 2001': Польша ill .г."":- ".і" ..".:: Рис. 3.45. Усиленный фильтр с вычисляемым критерием в ячейке А2

Рассмотренный пример иллюстрирует следующее: •

Заголовок интервала критериев не совпадает ни с одним заголовком в списке, иначе фильтр работал бы неправильно. •

В формуле критерия только ячейка В7 сравнивается с ячейкой Е1, но при обработке фильтра сравнение выполняется последовательно для всех ячеек столбца В, начиная с ячейки В7 и до конца списка. •

Ссылка на ячейку Е1 - абсолютная. Если в ячейке А2 задать формулу =В7>Е1 с относительной ссылкой, то ячейка В7 сравнивалась бы с ячейкой Е1. ячейка В8 - с ячейкой Е2 и т. д., что не правильно.

• На значение, возвращаемое формулой критерия в ячейке А2, можно не обращать внимания. Значение ИСТИНА соответствует тому, что цена первого лекарства в списке больше среднего, значение ЛОЖЬ - что цена первого лекарства в списке меньше среднего.

Исключение из правила. На рис. 3.46, так же как и на рис. 3.45, приведен отфильтрованный список лекарств с ценой, больше средней цены. Копнчествої Срок хранания до Страна производитель

Польша^ Польша

SJei^ftJW

27Свнтяб^2С101 Рис. 3.46. Пример использования абсолютных ссылок на ячейки внутри списка вместо обычных относительных ссылок

Но в данном случае используется формула критерия

=В7>СРЗНАЧ($В$7:$В$ 15),

которая содержит ссылку на столбец Цена, а не внешнюю ячейку. Согласно выше изложенным правилам, ссылки на ячейки внутри списка должны быть относительными, однако здесь ссылка на интервал В7:В15 - абсолютная, и это связано с тем, что на каждом шаге фильтрации нужно сравнивать содержимое текущей ячейки столбца В со средним значением из фиксированного интервала ячеек В7:В15. Если использовать относительную адресацию, то уже на втором шаге ячейка В8 будет сравниваться со средним значением интервала В8:В16.

Копирование отфильтрованных строк в другое место рабочего л иста

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

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

Флажок Только уникальные записи диалогового окна Расширенный фильтр действует только в режиме Скопировать результат » другое место и устраняет дублирование строк.

Применение формы данных для поиска информации в списке

Фильтры являются эффективным, но не единственным средством поиска данных в списке. Выделить строки, удовлетворяющие заданным критериям, можно также по команде Форма. Для этого: 1.

Выделите любую ячейку в списке. 2.

Выберите команду Форма из меню Данные и в открывающемся диалоговом окне щелкните кнопку Критерии . 3.

Заполните поля ввода так же, как при создании интервчла критериев.

Рис. 3.47 иллюстрирует заполнение формы для просмотра данных о лекарствах, произведенных в РБ. 4.

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

Пролистайте выделенные записи, используя кнопку Далее, вперед, или кнопку Назад - назад. 6.

Нажмите кнопу Закрыть для выхода из режима данных.