Шаг четвертый – конструирование запросов и отчетов

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

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

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

Продемонстрируем сортировку на списке, приведенном на рис.1. Необходимо отсортировать список по столбцу Численность населения. Для этого выделите одну ячейку в этом списке и выполните команду Сортировка из меню Данные, откроется
показанное на рис.5 диалоговое окно.

 

Выберете поле, по которому нужно сортировать список (в нашем случае – Численность населения). Установите переключатель по убыванию. В разделе Идентифицировать поля по должен быть установлен переключатель подписям (первая строка диапазона) и щелкните ОК. Результат сортировки списка показан на рис.6. Всегда проверяйте результат сортировки. Если она Вас не устраивает, то воспользуйтесь командой Отменить из меню Правка. Если после сортировок нужно восстановить исходный порядок строк в списке, то до сортировки необходимо создать столбец с номерами строк, а затем можно отсортировать список по этому столбцу. В рассмотренном примере исходный список рис.1 был отсортирован по одному столбцу – Численность населения.

Отсортируем список по полю Название района. Выполните команду Сортировка из меню Данные. Выберите поле по которому нужно сортировать список, в нашем случае Название района. Для сортировки по возрастанию, т.е. от А до Я, установите переключатель по возрастанию.

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

Отсортируем строки внутри каждой группы по столбцу – Название административной единицы. Для этого необходимо сортировать по двум столбцам: Название района и Название административной единицы. На рис.7 показаны соответствующие установки диалогового окна Сортировка, а на рис.8 представлен список после сортировки по двум столбцам.

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

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

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

 

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

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

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

 

При обработке больших интервалов раскрывающиеся списки команды Автофильтр могут содержать не одну сотню элементов. Для быстрого поиска нужного элемента нажмите его первую букву. Обратите внимание, что на рис.9 пропущены номера строк не удовлетворяющие критериям команды Автофильтр, эти строки Excel просто скрывает. Номера отфильтрованных строк выводятся другим цветом.

 

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

 

Критерии команды Автофильтр можно задавать по любому количеству столбцов. Сначала отфильтруйте список по одному столбцу, затем полученный список отфильтруйте по другому столбцу и т.д. В нашем примере мы отфильтровали БД по двум столбцам Название района (выбрали Дальнегорский район) и Тип поселения (выбрали пгт -поселок городского типа) рис.10.

 

Каждый раскрывающийся список критериев команды Автофильтр содержит в конце элементы Пустые и Непустые.

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

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

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

 

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

Предположим, мы хотим показать населенные пункты численность населения которых больше 20тыс. человек, но меньше 50тыс. человек. Для списка на рис.1 выполним команду Автофильтр из подменю Фильтр меню Данные. Раскроем список критериев для столбца Численность населения, выберем элемент (Условие…). В диалоговом окне Пользовательский автофильтр при помощи раскрывающегося списка, в первой строке выберем условие Больше или равно, а во второй строке выберем условие Меньше или равно. В первой строке справа введем число 20, а во второй строке – число 30. Переключатель установим в положение И. Результат выполнения данных операций приведен на рис.14.

 

Для нахождения всех текстовых значений столбца, попадающих в заданный алфавитный интервал, необходимо раскрыть список критериев этого столбца и выбрать элемент (Условие…). В диалоговом окне Пользовательский автофильтр следует задать два критерия, объединенных оператором И. Например, чтобы найти все населенные пункты, названия которых начинаются с буквы Б, нужно установить фильтр по столбцу Название административной единицы, задав в диалоговом окне Пользовательский Автофильтр два условия >Б И <В. Буквы Б и В надо напечатать в соответствующих полях
диалогового окна рис.15. Результат такой фильтрации для таблицы рис.1 приведен на рис.16.

 

 

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

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

 

Например, чтобы получить результат представленный на рис.16, для списка на рис.1 выполним команду Автофильтр из подменю Фильтр меню Данные. Раскроем список критериев для столбца Название административной единицы, выберем элемент (Условие…). В диалоговом окне Пользовательский автофильтр задим критерий, как показано на рис.17.

 

 

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

 

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

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

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

• Создавать критерии с условиями по нескольким столбцам, связанным по правилу ИЛИ. Хотя для этого проще несколько раз выполнить команду Автофильтр.
• Создавать критерии с тремя и более условиями для заданного столбца, связанными по крайней мере одним союзом ИЛИ. Например, из списка населенных пунктов надо выбрать все районные центры, будь то города, поселки городского типа или села.
• Создавать вычисляемые критерии. Например,

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

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

 

Рассмотрим пример критерия с двумя условиями, объединенными по правилу ИЛИ

Пусть из списка «Мой край» приведенного на рис.1, нужно выделить как все поселки городского типа, так и административные единицы, численность населения которых больше или равна 5000 человек. Для создания такого фильтра выполните следующие действия:

• Вставьте несколько строк для интервала критериев выше списка на рабочем листе. Для этого установите курсор в ячейку A1 и пять раз выполните команду Строки из меню Вставка.
• Создайте интервал критериев, как показано на рис.18. Заголовки условий (Тип поселения,Численность населения, тыс.чел) скопируйте. На рис.18 в ячейках A1:B3 критерии содержаться в виде обыкновенных меток. Под заголовком Тип поселения введено

пгт

а под заголовком Численность населения, тыс.чел.

>=5

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

 

• Выберите команду Расширенный фильтр из подменю Фильтр меню Данные и введите в соответствии с рис.19 данные в диалоговом окне Расширенный фильтр. Перед тем как выполнять команду Расширенный фильтр, установите курсор в ячейку A6.
• 
  Убедитесь, что установлен переключатель фильтровать список на месте, и щелкните ОК. Результат фильтрации показан на рис.20.

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

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

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

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

 

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

 

 

Пример критерия с тремя условиями по одному столбцу, объединенных по правилу ИЛИ.

 

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

Пример критерия с условиями, объединенными по правилам И и ИЛИ одновременно.

 

Как выделить одновременно поселки городского типа и села, численность населения которых больше 10тыс. человек? Необходимо создать интервал критериев, показанный на рис.23. Обратите внимание, что условие >=10 присутствует в каждой строке интервала, потому что записи каждой группы типа поселения (будь-то поселок городского типа или село), должны одновременно удовлетворять и критерию численности населения, т.е. эти два условия объединяются по правилу И.

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

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

• Если задана одна буква, то равенству (=) будут найдены все начинающиеся на эту букву значения. Например, по критерию =П в столбце Название района будут найдены Партизанский, Пограничный, Пожарский районы и т.п.
• По условиям больше (>) или менше (<), будут найдены значения, которые располагаются в алфавитном порядке соответственно после или до заданного значения. Например, по критерию >К в столбце Название района будут выделены районы с названиями, начинающимися с букв от К до Я. Противоположный критерий <К позволяет отобрать районы, начинающиеся с букв от А до И.
• По критерию =”=текст” выделяются значения, точно совпадающие с заданным выражением текст. Например, чтобы найти записи относящиеся только к поселкам городского типа, не являющимися районными центрами надо задать формулу =”=пгт”, а если мы просто зададим пгт, то будут выделены все записи относящиеся к поселкам городского типа. Приведем другой пример, по критерию Ново в столбце Название административной единицы мы найдем записи с названиями Новогордеевка, Новошахтинский, Новомихайловка, Новокрещенка и т.д.
• Символы шаблона обрабатываются также как в автофильтре.

 

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

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

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

 

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

1. Вставьте несколько строк для интервала критериев выше списка на рабочем листе. Для этого установите курсор в ячейку A1 и три раза выполните команду Строки из меню Вставка.
2. В ячейку A1 введите заголовок вычисляемого критерия Плотность населения больше медианы. Перед вводом текста установите в A1 Формат ячейки Переносить по словам.
3. В ячейку D1 введите текст Медиана плотности населения. Перед вводом текста установите в D1 Формат ячейки Переносить по словам.
4. При создания вычисляемого фильтра в ячейку E1 вне списка введите формулу =МЕДИАНА(E6:E29).
   Воспользуйтесь командой Функция из меню Вставка, в диалоговом окне Мастер функций шаг 1 из 2 выберите Категория: Статистические и Выберите функцию: МЕДИАНА, щелкните ОК. В диалоговом окне Аргументы функции задайте интервал E6:E29, ОК.
5. В ячейку A2 введите условие вычисляемого критерия с абсолютной ссылкой на эту внешнюю ячейку
   =E6>$E$1
6. Перед тем как выполнять команду Расширенный фильтр, установите курсор в ячейку A5.
7. Выберите команду Расширенный фильтр из подменю Фильтр меню Данные и введите Диапазон условий $A$1:$A$2.

 

В строке состояния на рис.25 видно, что по этому фильтру выделено 12 из 24 исходных записей. Как и следовало ожидать, не больше половины всех районов имеют плотность населения выше медианы.

 

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

• Заголовок интервала не совпадает ни с одним заголовком в списке, иначе фильтр работал бы неправильно. Заголовок может быть пустым, но и в этом случае его надо включать в интервал критерия в диалоговом окне Расширенный фильтр.
• В формуле критерия только ячейка E6 сравнивается с ячейкой E1, но при обработке фильтра сравнение выполняется последовательно для всех ячеек столбца E, начиная с ячейки E6 и до конца списка.
• Ссылка на ячейку E1 – абсолютная. Если в ячейке A2 задать формулу =E6>E2 с относительной ссылкой, то ячейка E6 сравнивалась бы с ячейкой E1, ячейка E7 – с ячейкой E2 и т.д., что, очевидно, не правильно.
• На значение, возвращаемое формулой критерия в ячейке A2, можно не обращать внимания. В данном случае значение ЛОЖЬ соответствует тому, что плотность населения Анученского района меньше медианы.

 

Пример ссылки на ячейки внутри списка

 

Если в таблице на рис.24 отсутствовал бы столбец Плотность населения, то мы могли бы применить вычисляемый фильтр, для поиска районов с заданной плотностью населения. Например, мы хотим найти районы, где плотность населения меньше чем 5 человек на кв.км. Тогда в формуле критерия
=C6/D6<5
используются относительные ссылки, так как вычисления выполняются с данными из ячеек только текущей строки: C6 делится на D6, C7 делится на D7 и т.д.
Результат фильтрации с вычисляемым критерием и относительными ссылками, приведен на рис.26.