Материал: 64

36

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

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

1

2

3

Рисунок 18 – Пример выборки из списка Сведения о продаваемых квартирах

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

37

Принципы создания условий отбора в расширенном фильтре

Как указывалось ранее, критерии для выборки данных вводятся в ячейки, расположенные ниже заголовков диапазона условий (рисунок 18). При создании условий отбора следует руководствоваться следующими правилами:

–условия, связанные между собой логической операцией И, вводятся в

одной строке;

–условия, связанные между собой логической операцией ИЛИ, вводятся

вразных строках.

Допустим, нужно найти в списке, изображённом на рисунке 13, все квартиры, расположенные в Южном районе, общая площадь которых не превышает 60 квадратных метров, приватизированные после 1 апреля 2002 года. Диапазон условий для этого примера показан на рисунке 19.

Рисунок 19 – Диапазон условий для критериев, связанных логической операцией И

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

Агент Район

Иванов О. И.

Северный

Рисунок 20 – Диапазон условий для критериев, связанных логической операцией ИЛИ

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

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

38

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

В формуле вычисляемого критерия используются относительные ссылки на адреса ячеек, расположенных в первой строке данных списка. Копирование критерия в другие ячейки рабочего листа не требуется. После ввода критерия и нажатия клавиши Enter в ячейке с критерием появятся слово ИСТИНА или ЛОЖЬ. Полученный результат свидетельствует только о том, выполняется ли вычисляемый критерий в первой строке данных списка или нет.

При использовании вычисляемого критерия MS Excel просматривает все записи списка (так как используются относительные ссылки на адреса ячеек) и вычисляет для каждой записи значение логической формулы. В ответ включаются записи, для которых результатом выполнения логической функции является значение ИСТИНА.

Для поиска в списке квартир, общая площадь которых превышает жилую площадь более чем на 30 квадратных метров, можно создать вычисляемый критерий, представленный на рисунке 21 (после ввода критерия и нажатия клавиши Enter в ячейке появится слово ИСТИНА, так как для первой квартиры в списке логическое условие выполняется).

=E4-D4>30

Рисунок 21 – Диапазон условий с вычисляемым критерием

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

Критерий

=D4>СРЗНАЧ($D$4:$D$15)

Рисунок 22 – Диапазон условий с вычисляемым критерием, включающим функцию MS Excel

Обратите внимание, что для фиксации диапазона ячеек D4:D15 в аргументе функции СРЗНАЧ используются абсолютные ссылки на адреса ячеек.

В вычисляемых критериях можно использовать логические функции И, ИЛИ, НЕ. Примеры критериев с логическими функциями:

39

=НЕ(B4= Южный ) – найти квартиры, за исключением расположенных в Южном районе;

=И(B4= Северный ; D4>=50; D4<=60) – найти квартиры, расположенные в Северном районе, с жилой площадью от 50 до 60 квадратных метров;

=ИЛИ(E4=30; E4=40; E4=90) – найти квартиры с общей площадью 30, 40 или 90 квадратных метров.

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

Сводные таблицы и диаграммы

Сводные таблицы используются для обобщения больших объёмов данных. Простота изменения структуры сводных таблиц и возможность получения разнообразных «срезов» данных делают сводные таблицы эффективным аналитическим инструментом решения различных прикладных задач.

Создание сводной таблицы

Для создания сводной таблицы на вкладке Вставка в группе Таблицы следует нажать кнопку Сводная Таблица.

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

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

После нажатия кнопки OK в диалоговом окне Создание сводной таблицы на экране будут отображаться инструменты для работы со сводными таблицами.

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

40

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

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

Рисунок 23 – Диалоговое окно Список полей сводной таблицы

Процесс создания сводной таблицы начинается с перетаскивания мышью названий полей исходной таблицы из верхней части диалогового окна Список полей сводной таблицы в области Фильтр отчёта, Названия столбцов, На-

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

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

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