Фильтрация

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

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

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

Автофильтр

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

Microsoft Excel обозначает отфильтрованные элементы голубым цветом

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

Расширенный фильтр

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

Примеры сложных условий отбора

Несколько условий для одного столбца

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

Продавец

Белов

Батурин

Рощин

Одно условие для нескольких столбцов

Для того чтобы найти в нескольких столбцах данные, отвечающие одному условию отбора, введите все условия отбора в одну строку диапазона условий отбора. Например, следующий диапазон условий отбора возвращает все строки, содержащие значения «Продукты» в столбце «Товар», «Белов» в столбце «Продавец» и объем продаж более 1 000 руб.

Тип

Продавец

Продажи

Фрукты

Белов

>1 000

Разные условия для разных столбцов

Для того чтобы найти данные, отвечающие одному условию, в одном столбце, или отвечающие другому условию, в другом столбце, введите условия отбора в разные строки диапазона условий отбора. Например, следующий диапазон условий отбора отображает все строки, содержащие значение «Продукты» в столбце «Товар», «Белов» в столбце «Продавец», либо объем продаж, превышающий 1 000 руб.

Тип

Продавец

Продажи

Фрукты

 

 

 

Белов

 

 

 

>1 000

Один из двух наборов условий для двух столбцов

Для того чтобы найти строки, отвечающие одному из двух наборов условий, каждый из которых содержит условия более чем для одного столбца, введите эти условия отбора в отдельные строки. Например, следующий диапазон условий отбора отображает строки, содержащие как значение «Белов» в столбце «Продавец», так и объем продаж, превышающий 3 000 руб., а также строки по продавцу Батурину с объемами продаж более 1 500 руб.

Продавец

Продажи

Белов

>3 000

Батурин

>1 500

Более двух наборов условий для одного столбца

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

Продажи

Продажи

>5 000

<8 000

<500

 

Условия, создаваемые как результат выполнения формулы

В качестве условия отбора можно использовать вычисляемое значение, являющееся результатом выполнения формулы  (Формула. Совокупность значений, ссылок на другие ячейки, именованных объектов, функций и операторов, позволяющая получить новое значение. Формула всегда начинается со знака равенства (=)) . При создании условия отбора с помощью формулы не используйте заголовок столбца в качестве заголовка столбца условий; либо оставьте условие отбора без заголовка, либо используйте заголовок, не являющийся заголовком столбца в списке. Например, следующий диапазон условий отбора отображает строки, которые содержат в столбце C значение, превышающее среднее значение ячеек диапазона C7:C10.

=C7>СРЕДНЕЕ($C$7:$C$10)

Примечания


О консолидации данных

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

Консолидировать данные в Microsoft Excel можно несколькими способами. Наиболее удобный метод заключается в создании формул, содержащих ссылки на ячейки в каждом диапазоне объединенных данных. Формулы, содержащие ссылки на несколько листов, называются трехмерными формулами.

Использование трехмерных формул

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

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

 

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

Консолидация по расположению

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

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

Консолидация по категории

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

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

Другие способы объединения данных

Можно создать отчет сводной таблицы  (Отчет сводной таблицы. Интерактивный перекрестный отчет Microsoft Excel, содержащий итоговые данные и выполняющий анализ таких данных, как записи базы данных из разных источников, в том числе внешних по отношению к Microsoft Excel.) из нескольких диапазонов консолидации. Данный метод сходен с консолидацией по категории, однако обладает большей гибкостью в отношении реорганизации категорий.


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

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

•  Если отчет создается на основе веб-запроса , запроса с параметрами, шаблона отчета , odc-файла или файла запроса, извлеките данные в книгу, а затем выделите ячейку в списке Microsoft Excel, содержащем извлекаемые данные.

Если данные извлекаются из базы данных OLAP или средства подключения к данным Microsoft Office возвращают данные в виде пустого отчета сводной таблицы, выполните шаг 6, описанный ниже.

•  Если отчет создается на основе данных, находящихся в списке или базе данных Microsoft Excel, выделите ячейку списка или базы данных.

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

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

  1. Выполните одно из следующих действий.

Создание отчета вручную

•  Из окна Список полей сводной таблицы перетащите поля с данными, которые требуется отобразить в строках, в область перетаскивания с надписью «Перетащите сюда поля строк».

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

•  Поля с данными, которые должны отображаться в столбцах, перетащите в область перетаскивания с надписью «Перетащите сюда поля столбцов».

•  Поля, содержащие данные, которые требуется обобщить, перетащите в область с надписью «Перетащите сюда элементы данных».

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

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

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

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

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

Создание отчета с помощью мастера

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

•  На шаге 3 мастера нажмите кнопку Макет .

•  Из группы кнопок полей, расположенной справа, перетащите нужные поля в области диаграммы СТРОКА и СТОЛБЕЦ.

•  Поля, содержащие данные, которые требуется обобщить, перетащите в область ДАННЫЕ.

•  Поля, которые требуется использовать как поля страниц, перетащите в область СТРАНИЦА.

Если требуется постраничное получение данных для работы с большим количеством исходных данных, дважды щелкните кнопкой мыши поле страницы, нажмите кнопку Дополнительные , выберите параметр Обновлять внешние данные при выборе каждого элемента , а затем дважды нажмите OK (этот параметр недоступен для некоторых видов исходных данных, включая базы данных OLAP и средства подключения к данным Microsoft Office).

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

•  Чтобы удалить поле, перетащите его за пределы диаграммы.

•  Получив нужный макет, нажмите кнопку OK , а затем кнопку Готово .