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

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

То есть — все уникальные значения некоего столбца данных выводятся в срезе в виде графических кнопок.

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

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

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

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

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

Только с этой настройкой срез работает, как надо, но эта же настройка и означает, что пользователь властен делать с ним всё, что захочет.

Файл примера

Скачать

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

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

Классика

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

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

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

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

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

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

Я могу выбирать не обязательно один какой-то год / месяц — можно выбрать набор лет / месяцев и сводная таблица без проблем отобразит правильные результаты.

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

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

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

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

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

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

   

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

   

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

   

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

   

4. Если в срезе по годам не выбрано ничего, то ячейка Ref!F1 будет содержать значение «(Все)», если выбран конкретный год, то — значение этого года, если — несколько лет, то — значение «(Несколько элементов)». Вот этим мы и пользуемся.

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

   Та же история и со вторым срезом.

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

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

   Например, если SelectedYear = 0, а SelectedMonth >0, то никакой год не выбран, но выбран какой-то месяц, поэтому надо из формулы СУММЕСЛИМН убрать критерий для года, но оставить критерий для месяца. Вот и всё — остальное по аналогии.

   

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

   

Конечный результат:

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

Источник: http://perfect-excel.ru/publ/excel/dashboard/srez_slicer_kak_ehlement_polzovatelskogo_interfejsa/11-1-0-99

Фильтрация сводных таблиц Срезами и Шкалой

39058 22.11.2012 Скачать пример

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

Неудобства такого способа очевидны:

• При выборе нескольких элементов — их не видно, а видно текст «(несколько элементов)». Не юзерфрендли ни разу.
• Один фильтр отчета жестко привязан к одной сводной таблице. Если у нас несколько сводных таблиц (а одной обычно дело не ограничивается), то для каждой(!) придется создавать свой фильтр и для каждой придется его раскрывать, отмечать галочками нужные элементы и жать ОК. Жутко неудобно, видел даже энтузиастов, которые писали специальные макросы для этих целей.

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

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

Чтобы создать срез для текущей сводной таблицы нужно перейти на вкладку Параметры (Options) и в группе Сортировка и фильтр (Sort and filter) нажать кнопку Вставить срез (Insert slicer):

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

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

Один и тот же срез можно легко привязать к нескольким сводным таблицам и диаграммам, используя с вкладки Параметры (Options) кнопку Подключения к сводной таблице (Pivot table connections), которая открывает соответствующее диалоговое окно:

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

Не осталась забыта и дизайнерская составляющая. Для форматирования срезов на вкладке Конструктор (Design) есть несколько встроенных стилей:

…и возможность создавать свои варианты оформления:

А уж в связке «сводная таблица — сводная диаграмма — срез» все это выглядит совсем замечательно:

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/8/135/

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

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

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

Автофильтр

Включение автофильтра:

1. Выделить одну ячейку из диапазона данных.
2. На вкладке Данные [Data] найдите группу Сортировка и фильтр [Sort&Filter].
3. Щелкнуть по кнопке Фильтр [Filter] .

Фильтрация записей:

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

1. Выбрать условие фильтрации.

Варианты фильтрации данных

• Фильтр по значению – отметить флажком нужные значения из столбца данных, которые высвечиваются внизу диалогового окна.
• Фильтр по цвету – выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта или по значку ячейки (если установлено условное форматирование).
• Можно воспользоваться строкой быстрого поиска 
• Для выбора числового фильтра, текстового фильтра или фильтра по дате (в зависимости от типа данных) выбрать соответствующую строку. Появится контекстное меню с более детальными возможностями фильтрации:

1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др. 
2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит…, начинается с… и др.
3. При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.

Если данные после фильтрации были изменены, фильтрация автоматически не срабатывает, поэтому необходимо запустить процедуру вновь, нажав на кнопку Повторить [Reapply] в группе Сортировка и фильтр на вкладке Данные.

Отмена фильтрации

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

Срезы

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

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

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

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

1. Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].
2. В группе Сервис [Tools] (или на вкладке Вставка [Insert] в группе Фильтры [Filters]) выбрать кнопку Вставить срез [Insert Slicer] .

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

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

1. Выделить срез.
2. На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

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

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

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

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

Задание условий фильтрации

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

2. 

   Записать условия фильтрации. Условия, записанные в одной строке, выполняются одновременно (как условие «И»), а в разных строках — как условие выбора («ИЛИ»).

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

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

3. На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter] и выбрать команду Дополнительно [Advansed]. 

1. В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте [Filter the list, in-place] или скопировать результат в другое место [Copy to another Location].

1. Указать Исходный диапазон [List range], выделяя исходную таблицу вместе с заголовками столбцов.
2. Указать Диапазон условий [Criteria range], отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
3. Указать при необходимости место с результатами в поле  Поместить результат в диапазон [Copy to], отметив курсором ячейку диапазона для размещения результатов фильтрации.
4. Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи [Unique records only].

Источник: https://micro-solution.ru/excel/ranges/filter

Срезы в Excel и другие приемы работы с таблицами

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

Навигация по таблице

Перемещение по ячейкам таблицы почти не отличается от навигации в обычной табличной структуре Эксель. Вы, так же, можете активировать ячейку мышью, или перемещаться с помощью клавиатурных стрелок и кнопок PgUp, PgDn.

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

Перемещение таблицы

Переместить табличку можно двумя способами:

1. Перетягиванием. Выделите любую ячейку внутри таблицы и ведите мышь к любой из границ таблицы. Как только курсор примет вид четырёхнаправленной стрелки – зажимайте левую кнопку мыши и тяните таблицу в новое место
2. Вырезанием-вставкой:
   • Выделите всю таблицу любым способом. Например, мышью, или двойным нажатием Ctrl+A
   • Вырежьте таблицу с помощью команды Вырезать контекстного меню, комбинации Ctrl+X, или команды Главная – Буфер обмена – Вырезать
   • Вставьте табличку в новое место. Для этого, установите курсор в ту ячейку, где будет располагаться верхний левый угол таблицы. Выберите Вставить в контекстном меню, нажмите Ctrl+V или выполните Главная – Буфер обмена – Вставить

Благодаря этим простым действиям, вы можете перенести таблицу в любое место на листе.

Выделение элементов таблицы

Чтобы выделить всю строку полностью – наведите мышкой на левую границу первого столбца таблицы. Когда курсор станет чёрной стрелкой – жмите левую кнопку мыши, вся строка таблицы будет выделена. Или же выделите любую ячейку строки и нажмите Shift+Пробел. В принципе, правила выделения не отличаются от общепринятых в Эксель

Для выделения всего столбца – наведите мышкой на верхнюю грань таблицы (в нужном столбце). Как только курсор изменится на стрелку «Вниз» — жмите левой кнопкой мыши. Так же, можно активировать любую ячейку столбца и нажать Ctrl+Пробел. Нажмите эту комбинацию дважды, чтобы программа выделила и шапку.

Выделить всю табличку целиком – выберите любую ячейку таблицы и нажмите Ctrl+A. Для выделения диапазона вместе с шапкой – нажмите Ctrl+A еще раз.  Еще один способ – поместите стрелку мыши в верхний левый угол первого столбца шапки. Когда курсор превратится в диагональную стрелку – сделайте левый клик мышью. Чтобы выделить таблицу с шапкой – повторите такой клик.

Как добавить строку итогов

Обычно, мы вручную добавляем строку «Итого» в конец своей таблички. Если диапазон преобразован в таблицу Excel, программа добавит итоги одним нажатием мыши. Для этого, выделите любую ячейку таблицы и поставьте галочку: Конструктор – Параметры стилей и таблиц – Строка итогов

Вставка строки итогов

Итоги будут добавлены в конец таблички. Кстати, в строке итогов могут быть различные функции подсчёта:

• Нет – оставить поле пустым
• Среднее – считать среднее значение по столбцу
• Количество – считать количество значений в столбце
• Количество чисел – подсчитать количество числовых значений
• Максимум – вывести максимальное значение в столбце
• Минимум – вывести минимальное значение в столбце
• Сумма – сумма всех значений
• Смещённое отклонение – просчитать соответствующий статистический показатель
• Смещённая дисперсия – считать статистический показатель
• Другие функции – выбрать другую функцию из полного списка

Функции в строке итогов

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

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

Как удалить дубликаты из таблицы

Удаление дубликатов в Excel – будь то обычный диапазон или таблица – задание очень простое. Если в вашей табличке есть лишние, «задвоенные» данные – выполните команду: Конструктор – Сервис – Удалить дубликаты. В появившемся окне выберите столбцы, по которым нужно определять дублированные данные и нажмите ОК. Дальше программа всё сделает за вас.

Удаление дубликатов из столбца «Продавец»

Сортировка данных в таблице Excel

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

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

   Например, в таблице с рисунка нужно отсортировать по фамилии продавца, а потом сумму продаж по убыванию, чтобы увидеть в какие дни продавцы делали наибольшие продажи. Для этого, сначала отсортируем продажи по убыванию, а потом фамилии продавцов от «А» до «Я».

2. С помощью окна сортировки. Выполните команду Главная – Сортировка и фильтр – Настраиваемая сортировка.

Сортировка данных по нескольким параметрам

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

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

По умолчанию, в таблицах всегда включён автофильтр. Если его нет – установите флажок: Конструктор – Параметры стилей таблиц – Кнопка фильтра. После этого в окне автофильтра установите флажки возле тех пунктов, которые нужно оставить на экране. Можно установить фильтры в нескольких столбцах, чтобы получить сложную выборку.

Фильтрация с помощью автофильтра

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

Например, чтобы отобрать все продажи Иванова, выберем любую ячейку с текстом «Иванов» и в контекстном меню выберем Фильтр – Фильтр по значению выделенной ячейки.

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

Фильтр по активной ячейке

Чтобы отменить заданные фильтры и сортировки – выполните команду Главная – Сортировка и фильтр – Очистить.

Срезы в таблицах Эксель

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

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

Например, в таблице на рисунке, я сделал срезы по полям «Продавец» и «Форма оплаты».

Срезы в Excel

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

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

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

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

Источник: https://officelegko.com/2015/11/22/tablitsyi-excel-ch-2-rabota-s-tablitsami/

Срезы в сводных таблицах Excel

Привет всем!

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

• Для начала посмотрим как производится стандартная фильтрация сводных таблиц
• После выбора нужных позиций у нас получается такая картинка:
• 
• Минус в том, что мы не видим, какие позиции отобраны:
• Срезы решают эту проблему. Чтобы их вставить, встаем на вкладку Анализ (в Excel 2010 Параметры) и нажимаем Вставить срез
• 
• Появляется такое окно:
• 

Это список полей нашей сводной таблицы. Здесь мы должны выбрать те из них, по которым хотим делать фильтрацию. Я выберу в моём примере Магазины, Продукты и Месяц.

Теперь у меня 3 окошка:

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

1. Чтобы выбрать несколько пунктов, надо зажать CTRL и ткнуть мышкой нужные кнопки.
2. 
3. В зависимости от стиля кнопки делятся на выбранные/не выбранные и на присутствующие/отсутствующие. На рисунке внизу
4. 

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

А разные цвета разных панелей я сделал через Стили.

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

Еще один плюс — один и тот же срез можно подключить к нескольким сводным таблицам.

Для этого на нужном срезе нужно ткнуть правой кнопкой мыши и выбрать Подключения к отчетам и там выбрать нужные сводные таблицы.
Эксель Практик
«Глаза боятся, а руки делают»

P.S.

Источник: http://excelpractic.ru/srezy-v-svodnyx-tablicax-excel.html

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

      Добрый день читатель!

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

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

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

      Для наглядности срез в Excel выглядит так:

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

Использование, срезов в Excel будет более эффективно, нежели работа со стандартными фильтрами, но придётся потеснить данные на отображаемом поле. А если вы используете в своей работе дашборды, то срезы станут вообще неоценимыми помощниками.

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

     Итак, приступим к созданию нашего кнопочного фильтра:

1. На базе таблицы «База» мы формируем простую сводную таблицу в разрезе стран и проданных продуктов;
2. На рабочий лист добавляю два среза, и с помощью них я могу быстро, наглядно и удобно отбирать значения по необходимым периодам: месяцы или годы. Фильтр можно сделать как совместно, так и по одному из параметров;
3. Настраиваем отображение срезов в контекстном меню с помощью пунктов: «Размер и свойства…» и «Настройка среза…», указываем названия и свойства срезов, ширину, высоту и количество кнопок фильтра, и многое другое;
4. С помощью настроек сводной таблицы выставляем и фиксируем ее ширину, перейдя в «Параметры сводной таблицы», включив ярлык «Разметка и формат» и убираете галочку с пункта «Автоматически изменять ширину столбцов при обновлении». Это позволит нам иметь удобную и красивую таблицу, которая не изменяет свои границы каждый раз после обновления.

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

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

     Не забудьте подкинуть автору на кофе…

Умеренность — уже богатство. Цицерон 

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Источник: http://topexcel.ru/kak-sdelat-srez-v-excel/

Фильтрация данных в сводной таблице при помощи срезов

Использование срезов

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

Для начала можно добавить один или несколько срезов — удобных инструментов для быстрой и эффективной фильтрации данных.

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

Рисунок 60 – пример среза

1. Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами(рисунок 61).

Рисунок 61 – вкладка «Работа со сводными таблицами»

2. Если вы используете Excel 2016 или 2013, на вкладке Анализ нажмите кнопкуВставить срез(рисунок 62).

Рисунок 62 – инструмент «Вставить срез» в MSExcel 2013-2016

Если вы используете Excel 2010 или 2007, щелкните Параметры > Вставить срез > Вставить срез(рисунок 63).

Рисунок 62 – инструмент «Вставить срез» в MS Excel 2007-2010

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

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

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

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

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

Отключение среза

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

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

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

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

1. Выберите срез, который необходимо отформатировать.

Откроется окно Инструменты для среза со вкладкой Параметры.

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

Чтобы просмотреть все доступные стили, нажмите кнопку Дополнительные кнопки .

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

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

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

Все изменения общего среза мгновенно отражаются в сводных таблицах, связанных с этим срезом.

Например, если в сводной таблице 1 для фильтрации по определенной стране используется срез «Страна», в сводной таблице 2, которая также использует этот срез, будут отображены данные для той же страны.

Не нашли то, что искали? Воспользуйтесь поиском:

Источник: https://studopedia.ru/19_327019_filtratsiya-dannih-v-svodnoy-tablitse-pri-pomoshchi-srezov.html

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

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

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

И в том, и другом файле для отображения рисунков используются срезы – удобные кнопочные фильтры для сводных и 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. Готово! Настройте в вашем отчете подключение среза к таблицам и диаграммам по вкусу.

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

Источник: https://finalytics.pro/inform/xls-pictures-switch/

Как вставить срезы в сводные таблицы Microsoft Excel

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

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

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

Представьте, что вы используете срез для фильтрации ваших данных за определенный год. У вас есть блок срезов с каждым годом, который находится в данных, например «2016» и «2017». Чтобы увидеть данные только за 2016, вы можете кликнуть по нему в окне срезов, чтобы отфильтровать данные в сводной таблице.

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

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

Предпосылки: Что такое сводная таблица?

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

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

Что такое срезы в Excel?

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

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

Теперь давайте погрузимся в создание и настройку срезов в Excel.

Как быстро вставлять срезы в сводные таблицы Excel (смотри и учись)

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

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

Добавить свой первый срез в Excel

Чтобы начать работу со срезами, начните, кликнув внутри сводной таблицы. На ленте Excel найдите раздел Работа со сводными таблицами и нажмите Параметры.

Теперь найдите пункт меню Вставить срез. Нажмите на него, чтобы открыть новое меню и выбрать срез.

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

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

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

Вот так! Вы вставили срез полей. Теперь вы можете выбрать и кликнуть, чтобы отфильтровать ваши данные. Давайте узнаем больше о том, как использовать и настраивать фильтры.

Как использовать срезы в Excel

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

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

Я решил разделить свои данные как по «Tutorials», так и по данным в 2017 году, используя два среза, которые меняют данные в исходной сводной таблице.

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

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

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

На этом снимке экрана я выбрал несколько типов проектов, удерживая Ctrl и нажав несколько типов проектов.

И наконец, срезы складываются. После того, как вы нажмёте на кнопку для среза, вы увидите, что другие параметры срезов серые. Это означает, что первый фильтр, который вы применили, «выбил» другие параметры, что делает их недоступными для нажатия.

После того, как я отфильтровал по Accounting, вы заметите, что кнопка «2017» в срезах по году выделена серым цветом; это означает, что в 2017 году нет данных для Accounting, поэтому срез показывается так, что его нельзя выбрать.

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

Расширенные параметры срезов в Excel

Давайте узнаем ещё несколько методов, чтобы действительно улучшить работу со срезами. Чтобы работать с большинством из этих параметров, убедитесь, что у вас выбран срез и щелкните в меню Инструменты для среза > Параметры на ленте, чтобы сделать эти настройки видимыми.

Убедитесь, что вы выбрали срез и перейдите в раздел Инструменты для среза > Параметры на ленте.

Оформление срезов в Excel

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

Нажмите на другое представление, чтобы изменить Стиль срезов и изменить цвет вашего среза.

Выбор нескольких вариантов

Помните, как вы научились удерживать Ctrl на клавиатуре для многократного выбирать элементы срезов? Есть ещё один способ заставить этот вариант работать.

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

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

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

Несколько столбцов в срезе

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

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

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

Срезы Excel, подключенные к нескольким таблицам

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

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

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

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

Повторите и изучите больше инструментов данных в Excel

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

Ниже приведены некоторые дополнительные ресурсы, которые помогут вам работать со срезами и сводными таблицами в Microsoft Excel:

• • Microsoft Excel
  • Как использовать сводные таблицы для анализа данных в Excel
  • Эндрю Чилдресс
• 1. Microsoft Excel
  2. Как делать и использовать таблицы в Microsoft Excel (как «про»)
  3. Эндрю Чилдресс
• • Microsoft Excel
  • Как связать ваши данные из разных книг в Excel
  • Эндрю Чилдресс

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

Источник: https://business.tutsplus.com/ru/tutorials/insert-slicers-in-excel-pivottables—cms-29646