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

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

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

На рисунке изображена со срезами, расположенными справа. Каждый срез относится к определенному полю данных. В данном случае срезы представляют Федеральный округ и Численность. Сводная таблица отображает информацию о площади территории для Северо-Западного и Южного округов с численностью от 1000 до 10000 тыс. человек.

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

Добавление среза

Для того чтобы добавить срез в рабочую книгу, щелкните на любой ячейке сводной таблицы. Затем выберите Работа со сводными таблицами -> Анализ -> Фильтр -> Вставить срез.

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

Настройка срезов

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

Фильтрация срезом

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

Если вы хотите убрать фильтр с конкретного среза, нажмите на иконку Удалить фильтр, находящуюся в верхнем правом углу среза (или нажмите Alt+C, когда срез выбран).

Подключение к фильтрам

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

Для подключения среза ко второй сводной таблице, выберите любую ячейку во второй сводной таблице. Переходим по вкладкам Работа со сводными таблицами –> Анализ –> Фильтр -> Подключения к фильтрам.

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

Теперь обе сводные таблицы подключены к одному срезу. Если вы выберете пункт в срезе, обе сводные таблицы будут отфильтрованы. В примере ниже показано, как в срезе Федеральный округ выбраны Уральский, Северо-Западный и Южный и обе сводные таблицы отображают релевантную информацию.

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

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

Вы не даёте заголовки столбцам таблиц

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

Пустые столбцы и строки внутри ваших таблиц

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

На одном листе располагается несколько таблиц

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

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

Данные одного типа искусственно располагаются в разных столбцах

Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:

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

Дело в том, что данный формат содержит 2 измерения: чтобы , вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel - формул и сводных таблиц.

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

Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.

Разнесение информации по разным листам книги «для удобства»

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

Информация в комментариях

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

Бардак с форматированием

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

Объединение ячеек

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

Объединение текста и чисел в одной ячейке

Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.

Числа в виде текста в ячейке

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

Если ваша таблица будет презентоваться через LCD проектор

Выбирайте максимально контрастные комбинации цвета и фона. Хорошо выглядит на проекторе тёмный фон и светлые буквы. Самое ужасное впечатление производит красный на чёрном и наоборот. Это сочетание крайне неконтрастно выглядит на проекторе — избегайте его.

Страничный режим листа в Excel

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

Ещё больше полезной информации про Excel можно узнать на

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Привет всем!

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

Для начала посмотрим как производится стандартная фильтрация сводных таблиц

После выбора нужных позиций у нас получается такая картинка:

Минус в том, что мы не видим, какие позиции отобраны:

Срезы решают эту проблему. Чтобы их вставить, встаем на вкладку Анализ (в Excel 2010 Параметры ) и нажимаем Вставить срез

Появляется такое окно:

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

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

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

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

В зависимости от стиля кнопки делятся на выбранные/не выбранные и на присутствующие/отсутствующие . На рисунке внизу

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

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

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

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

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

«Глаза боятся, а руки делают»

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

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

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

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

Файл примера

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

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

Классика

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

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

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

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

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

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

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

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

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

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

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

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