Как сделать срезы в excel?

Excel 2013. Срезы сводных таблиц; создание временной шкалы

В Excel 2010 появился новый инструмент анализа данных – срезы сводных таблиц. [1] С помощью срезов можно фильтровать сводные таблицы подобно тому, как это происходит с помощью области фильтра списка полей сводной таблицы. Различие заключается в том, что срезы обеспечивают дружественный интерфейс, позволяющий просматривать текущее состояние фильтра.

Чтобы создать срез поместите указатель мыши в область сводной таблицы, выберите контекстную вкладку ленты Анализ и щелкните на кнопке Вставить срез (рис. 1). Также, встав на сводную таблицу, можно перейти на вкладку Вставка и в области Фильтры выбрать команду Срез.

Рис. 1. Создание среза: шаг 1 – команда Вставить срез

Скачать заметку в формате Word или pdf, примеры в формате Excel

Появится диалоговое окно Вставка срезов (рис. 2). Устанавливаемые в этом окне флажки определяют критерии фильтрации. В рассматриваемом случае выполняется фильтрация по срезу Категория оборудования. Можно задать одновременно несколько срезов.

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

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

Рис. 3. Настройка фильтра с помощью среза

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

Рис. 4. Подключение одного фильтра к нескольким сводным таблицам

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

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

Заметьте, что показанный на рис. 4 список сводных таблиц выглядит довольно абстрактно (СводнаяТаблица2, СводнаяТаблица3). Эти обобщенные имена автоматически присваиваются сводным таблицам и применяются для идентификации. На самом деле довольно сложно распознать нужную вам сводную таблицу на основе подобных обобщенных названий. Поэтому лучше выбирать более осмысленные названия, облегчающие идентификацию сводных таблиц. Чтобы изменить название сводной таблицы, установите указатель мыши в произвольном ее месте, выберите контекстную вкладку Анализ и введите понятное имя в поле Имя сводной таблицы, находящемся в левой части ленты (рис. 5).

Рис. 5. Присвоение сводной таблицы «говорящего» имени

Создание временной шкалы

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

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

Чтобы создать временную шкалу, поместите указатель мыши в область сводной таблицы, выберите вкладку ленты Анализ и щелкните на значке Вставить временную шкалу (рис. 6). Также, встав на сводную таблицу, можно перейти на вкладку Вставка и в области Фильтры выбрать команду Временная шкала.

Рис. 6. Создание временной шкалы – шаг 1 – Вставить временную шкалу

На экране появится диалоговое окно Вставка временных шкал (рис. 7). В этом окне отображаются все поля дат, доступные в выбранной сводной таблицы. Выберите поля, для которых будут созданы временные шкалы.

Рис. 7. Создание временной шкалы – шаг 2 – выбор полей, для которых будут созданы временные шкалы

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

Рис. 8. Щелкните на выбранной дате для фильтрации сводной таблицы

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

Если вам нужно отфильтровать сводную таблицу по кварталам, щелкните на раскрывающемся списке временного периода и выберите параметр Кварталы. В случае необходимости можно также выбрать параметр Годы или Дни (рис. 9).

Рис. 9. Быстрое переключение между параметрами Годы, Кварталы, Месяцы или Дни

Временным шкалам не присуща обратная совместимость, поэтому они могут использоваться только в Excel 2013. Если открыть соответствующую рабочую книгу в Excel 2010 либо в более ранней версии Excel, временные шкалы будут отключены.

Читать еще:  Как формулу в excel сделать числом?

Временным шкалам не присуща обратная совместимость, поэтому они могут использоваться только в Excel 2013. Если открыть соответствующую рабочую книгу в Excel 2010 либо в более ранней версии Excel, временные шкалы будут отключены.

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

Рис. 10. Форматирование срезов

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

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

Переключение рисунков в отчетах с помощью срезов и сводных таблиц

Продолжаем готовить для вас решения по отчетности — на этот раз в Excel. С помощью сводных таблиц и диаграмм, срезов, формул СМЕЩ , СЧЁТЗ и ПОИСКПОЗ , а также связанных рисунков, сделали для вас отчет, в котором можно переключать изображения и данные в диаграммах. Круто же?! Рисунки вы легко можете заменить на фото менеджеров или товаров.

Кстати, переключать не только рисунки, но и визуальные представления – таблицы и диаграммы, можно и с помощью smart-таблиц (умных таблиц). Этот прием используется в примере ниже, чтобы показать численность населения по крупным городам России и его рост с 1926 по 2018 годы.

Срезы для переключения рисунков в Excel

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

Кроме срезов, в Excel переключение изображений можно сделать еще несколькими способами. Например, с помощью элементов управления формы (вкладка Разработчик → Вставить → Элементы управления формы). Хотя эти элементы и могут добавить дополнительную информативность отчету, они «пришли» из старых версий Excel и в их работе есть свои нюансы. Один из самых досадных – они не поддерживаются в онлайн-представлении, в отличие от срезов, которые применяются широко.

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

Настройка отчета

Для настройки отчета в первом файле мы использовали следующие инструменты Excel:

  • форматированные («умные») smart-таблицы
    (вкладка Главная → Форматировать как таблицу)
  • сводные таблицы
    (если вы не знаете, что такое сводные таблицы, читайте здесь)
  • вычисляемые поля сводных таблиц
    (выделить сводную таблицу, вкладка Анализ → Поля, элементы и наборы → Вычисляемое поле)
  • сводные диаграммы
    (вкладка Вставка → Сводная диаграмма)
  • срезы
    (выделить сводную таблицу, вкладка Анализ → Вставить срез)
  • диспетчер имен
    (вкладка Формулы → Диспетчер имен)
  • формулы СМЕЩ , СЧЁТЗ , ПОИСКПОЗ
  • связанные рисунки
  • линии и фигуры
    (вкладка Вставка → Фигуры)

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

  1. Прежде всего, создадим сводную таблицу с названиями товаров. В ней всего одно поле – названия товаров в области строк. Кстати, вместо наших рисунков вы можете добавить фото менеджеров или своих товаров. В файле таблица находится на листе «1».
  2. Добавим срез: выделяем любую ячейку в созданной сводной таблице, вкладка Анализ → Вставить срез, ставьте галочку рядом с товарами. Кстати, если щелкнуть по срезу и перейти на вкладку Параметры, можно задать вид среза и его подключение к таблицам.
  3. А теперь расскажем о приеме, с помощью которого переключаются рисунки. Приготовьтесь )
    Чтобы рисунки красиво отображались в вашем отчете, разместите их в ячейки Excel одинакового размера (в нашем примере это ячейки в столбце «фото» на листе «товары»). На вкладке Формулы откройте Диспетчер имен. Задайте новое имя – Экран и запишите в области «Диапазон» такую формулу:

= СМЕЩ ( товары!$C$1; ЕСЛИ ( СЧЁТЗ ( ‘1’!$B:$B ) > 2; 2; ПОИСКПОЗ ( ‘1’!$B$3; товары!$B:$B; 0)-1); 0; 1; 1)

Эта формула ищет нужное изображение на листе «товары». А если на срезе будет выбрано больше одного товара, то перенаправит на ячейку с подсказкой.

  1. На листе «товары» скопируйте любой рисунок. Затем перейдите Главная → Вставить → Связанный рисунок. В строке формул напишите =Экран.
  1. Готово! Настройте в вашем отчете подключение среза к таблицам и диаграммам по вкусу.

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

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

В этом курсе:

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

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

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

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

Читать еще:  Как сделать дни недели в 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 User Voice.

Как сделать срезы в excel?

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

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

Читать еще:  Как файл excel сделать легче?

Так вот, в 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 не смогли.

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

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