Сведение данных, фильтры, срезы и сводные диаграммы – ОфисГуру

Руководство по фильтру сводных таблиц в Excel. Здесь мы обсудим, как создать фильтр сводной таблицы в Excel вместе с примерами и шаблоном Excel.

GIF

kak-otfiltrovat-stolbets-svodnoj-tablitsy_3.gif

Чтобы увидеть видео включите в браузере GIF-Анимацию.

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

Фильтр в сводной таблице Excel

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

Для примера возьмем следующую таблицу:

Накладная.

Создадим сводную таблицу: «Вставка» – «Сводная таблица». Поместим ее на новый лист.

Отчет.

Мы добавили в сводный отчет данные по поставщикам, количеству и стоимости.

Напомним, как выглядит диалоговое окно сводного отчета:

Список.

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

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

Итоги.

Например, среднее количество заказов по каждому поставщику:

Пример.

Итоги можно менять не во всем столбце, а только в отдельной ячейке. Тогда щелкаем правой кнопкой мыши именно по этой ячейке.

Установим фильтр в сводном отчете:

  1. В перечне полей для добавления в таблицу ставим галочку напротив заголовка «Склад».Склад.
  2. Перетащим это поле в область «Фильтр отчета».Фильтр.
  3. Таблица стала трехмерной – признак «Склад» оказался вверху.

Пример1.

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

Выбор.

Например, «1»:

1.

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

Значения.

Отфильтровать отчет можно также по значениям в первом столбце.

Фильтры отчетов

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

Перетащите область из строк в фильтры в областях сводной таблицы.

Фильтры отчетов

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

Космический фильтр

Вы заметите, что

  • Значения продавца отображаются в строках.

  • Значения месяца отображаются в столбцах.

  • Фильтр регионов появляется сверху, по умолчанию выбрано ВСЕ.

  • Суммирующим значением является сумма суммы заказа.

    • Сумма суммы заказа в зависимости от продавца отображается в столбце Общая сумма.

    • Сумма суммы заказа По месяцам появляется в строке Итого.

  • Нажмите на стрелку в поле справа от области фильтра.

Значения продавца отображаются в строках.

Значения месяца отображаются в столбцах.

Фильтр регионов появляется сверху, по умолчанию выбрано ВСЕ.

Суммирующим значением является сумма суммы заказа.

Сумма суммы заказа в зависимости от продавца отображается в столбце Общая сумма.

Сумма суммы заказа По месяцам появляется в строке Итого.

Нажмите на стрелку в поле справа от области фильтра.

Появится раскрывающийся список со значениями поля Регион. Установите флажок Выбрать несколько элементов .

Выберите несколько предметов

По умолчанию все флажки отмечены. Снимите флажок ( Все ). Все коробки будут сняты.

Затем установите флажки — Юг и Запад и нажмите ОК.

Снять флажок

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

Данные, относящиеся

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

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

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

kartinka-1.png

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

В этой статье объясняется, как создавать макросы для автоматизации процесса фильтрации. Это обширное руководство по методу автофильтра в VBA.

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

Фильтрация данных в Excel

302-8706fd84bdeda9d836fe70ea347366f3.png

В Excel предусмотрено три типа фильтров:

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

Функция Срез

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

Чтобы вставить срез выполните следующие действия:

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

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

2. На вкладке Параметрыв группе Сортировка и фильтрнажмите кнопку Вставить срез.

image010_83.gifimage011_54.jpg

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

4. Нажмите кнопку ОК. Для каждого из выбранных полей будет отображен срез.

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

 

Выноска 2: Фильтр, примененный в срезеВыноска 2: Кнопка Очистить фильтрimage016_26.jpg

Чтобы выбрать несколько элементов, щелкните их по очереди, удерживая нажатой клавишу CTRL.

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

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

Управление сводными таблицами в Excel

Сводная таблица – мощный инструмент Microsoft Excel. С ее помощью пользователь анализирует большие по объему диапазоны, подводит итоги всего в несколько кликов, выводит на экран только нужную в данный момент информацию.

Об этой статье

Эту страницу просматривали 10 530 раз.

Интеллектуальное автозаполнение при вводе формул

В OfficeExcel2010 существует очень удобная возможность. Предположим, что имеется отформатированная с помощью командыФорматировать как таблицу (FormatAsTable)таблица. В нее нужно добавить еще один столбец, в каждую ячейку которого нужно ввести формулу. После ввода формулы в первую ячейку добавленного столбца и нажатия на клавишу [Enter],введенная формула автоматически копируется вниз до конца столбца таблицы.

Выноска 3: Добавленный столбецВыноска 3: Формула, введенная в первую ячейку столбцаimage019_19.jpg

ВыноскаВыноска 3: Параметры автозаполненияimage022_16.jpg

Фильтрация списка для поиска нужной информации

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

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

После того, как вы отфильтровали определенные строки, дальше допустимо работатьтолько с ними, не затрагивая остальные данные на листе. Например, можно создать график, используя отфильтрованные строки.

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

1. Щелкните на любой ячейке, содержащей данные.

2. На вкладке Данные (Data) в группе команд Сортировка и фильтр (Sort&Filter) щелкните по кнопке Фильтр (Filter).

С правой стороны каждой ячейки заголовка появляется кнопка со стрелкой.

image030_7.jpg

3. Щелкните по кнопке со стрелкой в столбце, данные в котором требуется отфильтровать.

4. Снимите флажок, щелкнув внутри квадратика, у тех данных, которые нужно скрыть.

image031_27.gif

5. Нажмите ОК.

В результате будут отображаться только те строки, содержащее указанноезначение, а вид стрелки на кнопке изменится на символ фильтра –image032_24.gif

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

1. При включенном фильтре щелкните на кнопке со стрелкой в столбце, строки в котором нужно отфильтровать с каким-либо условием.

2. В ниспадающем списке команд выберите Числовые фильтры (NumberFilters).

image033_7.jpg

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

1) РАВНО – равно определенному значению;

2) НЕ РАВНО – кроме определенного значения;

3) БОЛЬШЕ – больше определенного значения;

4) БОЛЬШЕ ИЛИ РАВНО – больше или равно определенного значения;

5) МЕНЬШЕ – меньше определенного значения;

6) МЕНЬШЕ ИЛИ РАВНО – меньше или равно определенного значения;

7) МЕЖДУ – задается промежуток между двух чисел;

8) МЕРВЫЕ 10 – первые 10 наибольших или наименьших элементов списка;

9) ВЫШЕ СРЕДНЕГО – данные выше среднего значения;

10) НИЖЕ СРЕДНЕГО – данные ниже среднего значения;

11) НАСТРАИВАЕМЫЙ ФИЛЬТР – сочетание вышеперечисленных условий.

Для удаления фильтра без отключения кнопки Фильтр (Filter) выполните следующее:

1. Щелкните по кнопке со стрелкой в столбце, данные в котором требуется отфильтровать.

2. Выберите в ниспадающем списке пункт Выделить все (All) или Снять фильтр.

3. Если фильтрация элементов проводится по нескольким столбцам, повторите шаги 1 и 2 для нужных столбцов.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...