Как сделать срез в excel в обычной таблице

Корпорация "Центр"

Использование срезов для фильтрации данных

В этом курсе:

  • Создание и форматирование таблиц
    Статья
  • Сортировка данных в таблице
    Статья
  • Фильтрация данных в диапазоне или таблице
    Статья
  • Данные итогов в таблице Excel
    Статья
  • Использование срезов для фильтрации данных
    Статья

Использование срезов для фильтрации данных

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

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

Создание среза для фильтрации данных

Щелкните в любом месте таблицы или таблицы.

На вкладке "Главная" перейдите к срезу >".

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

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

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

Чтобы очистить фильтры среза, выберите в "Очистить фильтр".

Вы можете настроить параметры среза на вкладке "Срез" (в более новых версиях Excel) или на вкладке "Конструктор" (в Excel 2016 и более старых версиях) на ленте.

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

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

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

Компоненты среза

Срез обычно отображает указанные ниже компоненты.

Элементы среза сводной таблицы

1. Заголовок среза указывает категорию элементов в срезе.

2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.

3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.

4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.

5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.

6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.

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

Откроется вкладка Анализ сводной таблицы.

На вкладке Анализ сводной таблицы нажмите кнопку Вставить срез.

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

Нажмите кнопку ОК.

Для каждого выбранного поля будет отображен срез.

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

Чтобы выбрать более одного элемента, нажмите клавишу COMMAND и, удерживая ее, щелкните каждый из элементов, которые нужно отфильтровать.

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

Откроется вкладка Таблица.

На вкладке Таблица нажмите кнопку Вставить срез.

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

Нажмите кнопку ОК.

Для каждого выбранного поля (столбца) будет отображен срез.

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

Чтобы выбрать более одного элемента, нажмите клавишу COMMAND и, удерживая ее, щелкните каждый из элементов, которые нужно отфильтровать.

Щелкните срез, который хотите отформатировать.

Откроется вкладка Срез.

На вкладке Срез щелкните цветной стиль, который хотите выбрать.

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

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

Щелкните срез, который хотите использовать в другой сводной таблице.

Откроется вкладка Срез.

На вкладке Срез нажмите кнопку Подключения к отчетам.

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

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

Откроется вкладка Анализ сводной таблицы.

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

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

Выполните одно из указанных ниже действий.

Щелкните срез и нажмите клавишу DELETE.

Щелкните срез, удерживая нажатой клавишу CONTROL, и выберите команду Удалить .

Корпорация "Центр"

Срез обычно отображает указанные ниже компоненты.

Элементы среза сводной таблицы

1. Заголовок среза указывает категорию элементов в срезе.

2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.

3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.

4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.

5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.

6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.

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

Выполните одно из указанных ниже действий.

Щелкните срез и нажмите клавишу DELETE.

Щелкните срез, удерживая нажатой клавишу CONTROL, и выберите команду Удалить .

Срез обычно отображает указанные ниже компоненты.

Элементы среза сводной таблицы

1. Заголовок среза указывает категорию элементов в срезе.

2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.

3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.

4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.

5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.

6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Как сделать срез в excel в обычной таблице

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

То есть — все уникальные значения некоего столбца данных выводятся в срезе в виде графических кнопок. Если нажать на кнопку, то источник данных, с котороым связан срез отфильтрует соответствующий столбец согласно выбранному значению. Это гораздо нагляднее и быстрее, чем обычный фильтр, однако занимает больше места на экране. А, учитывая такую популярную вещь, как дашборд (dashboard), то срезы и вовсе незаменимы.

Так вот, в Excel 2010 можно было ассоциировать со сводными таблицами и со сводными диаграммами. В Excel 2013 стало возможно их применять и с умными таблицами. В этой статье я расскажу, как можно задействовать срезы для работы с обычными таблицами. Вы спросите зачем? Я вам отвечу, что на практике часто бывает так, что сводная таблица не может быть тем объектом, который вы показываете пользователю в качестве результата, так как, например, то, что вы в итоге должны показать, опирается на 2 и более сводных таблицы, поэтому результаты приходится собирать из кусков данных промежуточных сводных таблиц. Тут не может быть ничего гибче обычной таблицы, которую вы можете контролировать на 100%.

С другой стороны, меня крайне расстраивает, что, как и в случае с умными таблицами, срезы совершенно не дружат с защитой листа. Невозможно, например, сделать так, чтобы и срез работал, и было невозможно модифицировать его настройки или вообще удалить с экрана. При защите листа приходится выставлять галочку " Изменение объектов ". Только с этой настройкой срез работает, как надо, но эта же настройка и означает, что пользователь властен делать с ним всё, что захочет.

Файл примера

Скачать

Исходные данные

Исходные данные, располагаются на листе Data в умной таблице tblSales .

Классика

Для начала покажем, как срезы используются для свобной таблицы. Смотрим лист примера Метод 1 — сводная таблица . Тут сделано следующее:

Добавлена сводная таблица на основе tblSales . Она элементарная — я хочу видеть продажи в разрезе регионов и модельного ряда.

На лист добавлены 2 среза, при помощи которых я собираюсь красиво, наглядно и удобно выбирать интересующий меня период времени — год(ы) и месяц(ы).

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

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

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

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

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

Теперь давайте обсудим, как нам приспособить такой полезный инструмент, как срез, к обычной таблице Excel. Смотрим на лист Метод 2 — обычная таблица . Как видите, я полностью воспроизвёл дизайн сводной таблицы, однако сердцевина таблицы состоит из формул СУММЕСЛИМН , которые через один полезный приём, о котором я собираюсь вам рассказать, получают параметры фильтрации из срезов.

Это устроено следующим образом:

На листе Ref я создал 2 вспомогательных умных таблицы tblYears и tblMonths . Они ни с чем не связаны — просто значения, которые мы хотим видеть в наших срезах для обычной таблицы.

На том же листе созданы 2 крошечные сводные таблицы ptYears и ptMonths на основе соответствующих вышеперечисленных вспомогательных таблиц.

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

Для каждой сводной таблицы был добавлен срез, который оказывается связанным с фильтром по полям Годы таблицы ptYears и Месяцы таблицы ptMonths . Выбор какого-либо года в срезе автоматически приводит к фильтрации по таблице ptYears .

Если в срезе по годам не выбрано ничего, то ячейка Ref ! F1 будет содержать значение "(Все)", если выбран конкретный год, то — значение этого года, если — несколько лет, то — значение "(Несколько элементов)". Вот этим мы и пользуемся. В Ref ! F3 я размещаю формулу, которая принимает значение 0, если в срезе не выбрано ничего, значение года, если выбран год, и значение -1 в остальных случаях (то есть случай выбора нескольких лет). Для удобства на базе F3 создаю именованный диапазон SelectedYear . Та же история и со вторым срезом.

Теперь у нас в SelectedYear и SelectedMonth содержатся либо год/месяц, либо 0 (ничего не выбрано), либо -1 (безобразие со множественным выбором). Эти именованные диапазоны мы используем в нашей таблицы, где при помощи формул ЕСЛИ (IF), И (AND) и, конечно же, СУММЕСЛИМН (SUMIFS) выдираем данные из tblSales . Формула выглядит страшновато, но на самом деле она шаблонна и просто уточняет состояние вышеуказанных ИД, чтобы применить формулу суммирования с нужными параметрами. Например, если SelectedYear = 0, а SelectedMonth >0, то никакой год не выбран, но выбран какой-то месяц, поэтому надо из формулы СУММЕСЛИМН убрать критерий для года, но оставить критерий для месяца. Вот и всё — остальное по аналогии.

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

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

Корпорация "Центр"

Ссылка на основную публикацию