Excel срез использовать настраиваемые списки при сортировке
Работая с большими сводными таблицами, часто приходится их принудительно упрощать, фильтруя часть информации, чтобы не утонуть в цифрах. Самый простой способ для этого - поместить некоторые поля в область фильтра (в версиях до 2007 она называлась область страниц) и выбирать из выпадающих списков только нужные значения:
Неудобства такого способа очевидны:
- При выборе нескольких элементов - их не видно, а видно текст "(несколько элементов)". Не юзерфрендли ни разу.
- Один фильтр отчета жестко привязан к одной сводной таблице. Если у нас несколько сводных таблиц (а одной обычно дело не ограничивается), то для каждой(!) придется создавать свой фильтр и для каждой придется его раскрывать, отмечать галочками нужные элементы и жать ОК. Жутко неудобно, видел даже энтузиастов, которые писали специальные макросы для этих целей.
Если у вас Excel 2010, то все это можно делать более изящно - с помощью срезов (slicers) . Срезы - это удобное кнопочное графическое представление интерактивных фильтров отчета для сводной таблицы или диаграммы:
Срез выглядит как отдельный графический объект (как диаграмма или картинка), не связан с ячейками и отображается над листом, что позволяет легко его перемещать. Чтобы создать срез для текущей сводной таблицы нужно перейти на вкладку Параметры (Options) и в группе Сортировка и фильтр (Sort and filter) нажать кнопку Вставить срез (Insert slicer) :
Теперь при выделении или снятии выделения с элементов среза (можно использовать клавиши Ctrl и Shift, а также протягивание с нажатой левой кнопкой мыши для выделения оптом) в сводной таблице будут отображаться только отфильтрованные данные по отобранным элементам. Дополнительный приятный нюанс в том, что срез разными цветами отображает не только выделенные, но еще и пустые элементы, для которых нет ни одного значения в исходной таблице:
Если вы используете несколько срезов, то это позволит быстро и наглядно отобразить взаимосвязи между элементами данных:
Один и тот же срез можно легко привязать к нескольким сводным таблицам и диаграммам, используя с вкладки Параметры (Options) кнопку Подключения к сводной таблице (Pivot table connections) , которая открывает соответствующее диалоговое окно:
Тогда выделение элементов на одном срезе будет влиять сразу на несколько таблиц и диаграмм, возможно даже, на разных листах.
Не осталась забыта и дизайнерская составляющая. Для форматирования срезов на вкладке Конструктор (Design) есть несколько встроенных стилей:
. и возможность создавать свои варианты оформления:
А уж в связке "сводная таблица - сводная диаграмма - срез" все это выглядит совсем замечательно:
Срезы предоставляют кнопки для фильтрации таблиц или срезов. Помимо быстрой фильтрации срезы также указывают текущее состояние фильтрации, что позволяет легко понять, что именно отображается в данный момент.
С помощью среза можно легко фильтровать данные в таблице или в таблице.
Создание среза для фильтрации данных
Щелкните в любом месте таблицы или таблицы.
На вкладке Главная перейдите к вкладке Вставка> срез.
В диалоговом окне Вставка срезов выберите флажки для полей, которые вы хотите отобразить, а затем выберите ОК.
Для каждого выбранного поля будет создан срез. Если нажать любую из кнопок среза, фильтр будет автоматически применяться к связанной таблице или таблице.
Чтобы выбрать несколько элементов, нажмите клавишу CTRL и, удерживая ее нажатой, щелкните каждый из элементов, которые нужно отобразить.
Вы можете настроить параметры среза на вкладке Срез (в более новых версиях Excel) или на вкладке Конструктор (Excel 2016 и более старых версиях) на ленте.
Примечание: Щелкните и удерживайте угол среза, чтобы настроить и изменить его размер.
Если вы хотите подключить срез к несколько срезов, перейдите к срезу > Подключения к отчетам > проверьте, нужно ли включать срезы, а затем выберите ОК.
Примечание: Срезы могут быть подключены только к срезам, которые имеют один и тот же источник данных.
Компоненты среза
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
Щелкните в любом месте сводной таблицы, для которой хотите создать срез.
Откроется вкладка Анализ сводной таблицы.
На вкладке Анализ сводной таблицы нажмите кнопку Вставить срез.
В диалоговом окне Вставка срезов установите флажки для полей сводной таблицы, для которых нужно создать срез.
Для каждого выбранного поля будет отображен срез.
В каждом срезе выберите элементы, которые нужно отфильтровать.
Чтобы выбрать более одного элемента, нажмите клавишу COMMAND и, удерживая ее, щелкните каждый из элементов, которые нужно отфильтровать.
Щелкните в любом месте таблицы, для которой хотите создать срез.
Откроется вкладка Таблица.
На вкладке Таблица нажмите кнопку Вставить срез.
В диалоговом окне Вставка срезов установите флажки рядом с полями (столбцами), для которых нужно создать срез.
Для каждого выбранного поля (столбца) будет отображен срез.
В каждом срезе выберите элементы, которые нужно отфильтровать.
Чтобы выбрать более одного элемента, нажмите клавишу COMMAND и, удерживая ее, щелкните каждый из элементов, которые нужно отфильтровать.
Щелкните срез, который хотите отформатировать.
Откроется вкладка Срез.
На вкладке Срез щелкните цветной стиль, который хотите выбрать.
Если для сводной таблицы уже есть срез, вы можете использовать его для фильтрации другой сводной таблицы. Обратите внимание: вы можете использовать эту возможность, только если для этих сводных таблиц используется один источник данных.
Сначала создайте сводную таблицу на основе того же источника данных, что использовался для сводной таблицы со срезом, который вы хотите повторно использовать.
Щелкните срез, который хотите использовать в другой сводной таблице.
Откроется вкладка Срез.
На вкладке Срез нажмите кнопку Подключения к отчетам.
В диалоговом окне установите флажки рядом со сводными таблицами, в которых должен быть доступен срез.
Щелкните в любом месте сводной таблицы, для которой хотите отключить срез.
Откроется вкладка Анализ сводной таблицы.
Перейдите на вкладку Анализ сводной таблицы и нажмите кнопку Подключения к фильтрам.
В диалоговом окне снимите флажки рядом со всеми полями сводной таблицы, для которых вы хотите отключить срез.
Выполните одно из указанных ниже действий.
Щелкните срез и нажмите клавишу DELETE.
Щелкните срез, удерживая нажатой клавишу CONTROL, и выберите команду Удалить .
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
С помощью среза можно легко фильтровать данные в таблице или в таблице.
Создание среза для фильтрации данных
Щелкните в любом месте таблицы или таблицы.
На вкладке Главная перейдите к вкладке Вставка> срез.
В диалоговом окне Вставка срезов выберите флажки для полей, которые вы хотите отобразить, а затем выберите ОК.
Для каждого выбранного поля будет создан срез. Если нажать любую из кнопок среза, фильтр будет автоматически применяться к связанной таблице или таблице.
Выполните одно из указанных ниже действий.
Щелкните срез и нажмите клавишу DELETE.
Щелкните срез, удерживая нажатой клавишу CONTROL, и выберите команду Удалить .
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
С помощью встроенных настраиваемых списков вы можете сортировать данные по дням недели или месяцам года. При желании вы можете создавать собственные настраиваемые списки для сортировки по любым другим параметрам, которые трудно сортировать по алфавиту (например, высокий, средний и низкий или S, M, L, XL).
Настраиваемые списки позволяют сортировать данные листа по месяцам доставки или по приоритету.
Сортировка по дням недели или месяцам года с помощью встроенного настраиваемого списка
Выполните указанные ниже действия:
Выделите столбцы для сортировки.
Примечание: Для получения наилучшего результата у каждого столбца должен быть заголовок.
На ленте выберите Данные > Сортировка.
Во всплывающем окне Сортировка в раскрывающемся списке Сортировать по выберите столбец, по которому нужно выполнить сортировку.
Например, чтобы отсортировать данные в предыдущем примере по дате доставки, в поле Сортировать по выберите доставка.
В раскрывающемся списке Порядок выберите Настраиваемый список.
В диалоговом окне Списки выберите нужный список и нажмите кнопку OK для сортировки листа.
Создание собственного настраиваемого списка
Чтобы создать собственный настраиваемый список для сортировки, выполните указанные ниже действия.
В столбце листа введите значения, по которым нужно выполнять сортировку. Расположите их нужным образом, определяющим порядок сортировки (от верхней ячейки к нижней). Например:
Выделите все ячейки в этом списке и выберите Файл > Параметры > Дополнительно.
Прокрутите страницу вниз до раздела Общие и нажмите кнопку Изменить списки.
В окне Списки нажмите кнопку Импорт.
Совет: Короткий список, который содержит значения "Высокий", "Средний" и "Низкий", возможно, проще ввести непосредственно в поле Элементы списка окна Списки.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Для сортировки или заполнения значений в пользовательском порядке можно применять настраиваемые списки. В Excel есть встроенные списки дней недели и месяцев года, но вы можете создавать и свои настраиваемые списки.
Чтобы понять, что представляют собой настраиваемые списки, полезно ознакомиться с принципами их работы и хранения на компьютере.
Сравнение встроенных и настраиваемых списков
В Excel есть указанные ниже встроенные списки дней недели и месяцев года.
Встроенные списки
Пн, Вт, Ср, Чт, Пт, Сб, Вс
Понедельник, Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье
янв, фев, мар, апр, май, июн, июл, авг, сен, окт, ноя, дек
Январь, Февраль, Март, Апрель, Май, Июнь, Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь
Примечание: Изменить или удалить встроенный список невозможно.
Вы также можете создать свой настраиваемый список и использовать его для сортировки или заполнения. Например, чтобы отсортировать или заполнить значения по приведенным ниже спискам, нужен настраиваемый список, так как соответствующего естественного порядка значений не существует.
Настраиваемые списки
Высокое, Среднее, Низкое
Большое, Среднее, Малое
Север, Юг, Восток, Запад
Старший менеджер по продажам, Региональный менеджер по продажам, Руководитель отдела продаж, Торговый представитель
Настраиваемый список может соответствовать диапазону ячеек, или его можно ввести в диалоговом окне Списки.
Примечание: Настраиваемый список может содержать только текст или текст с числами. Чтобы создать настраиваемый список, содержащий только числа, например от 0 до 100, нужно сначала создать список чисел в текстовом формате.
Создать настраиваемый список можно двумя способами. Если список короткий, можно ввести его значения прямо во всплывающем окне. Если список длинный, можно импортировать значения из диапазона ячеек.
Введение значений напрямую
Чтобы создать настраиваемый список этим способом, выполните указанные ниже действия.
В Excel 2010 и более поздних версиях выберите пункты Файл > Параметры > Дополнительно > Общие > Изменить списки.
В Excel 2007 нажмите кнопку Microsoft Office и выберите пункты Параметры Excel > Популярные > Основные параметры работы с Excel > Изменить списки.
Выберите в поле Списки пункт НОВЫЙ СПИСОК и введите данные в поле Элементы списка, начиная с первого элемента.
После ввода каждого элемента нажимайте клавишу ВВОД.
Завершив создание списка, нажмите кнопку Добавить.
На панели Списки появятся введенные вами элементы.
Нажмите два раза кнопку ОК.
Создание настраиваемого списка на основе диапазона ячеек
Выполните указанные ниже действия.
В диапазоне ячеек введите сверху вниз значения, по которым нужно выполнить сортировку или заполнение. Выделите этот диапазон и, следуя инструкциям выше, откройте всплывающее окно "Списки".
Убедитесь, что ссылка на выделенные значения отображается в окне Списки в поле Импорт списка из ячеек, и нажмите кнопку Импорт.
На панели Списки появятся выбранные вами элементы.
Два раза нажмите кнопку ОК.
Примечание: Настраиваемый список можно создать только на основе значений, таких как текст, числа, даты и время. На основе формата, например значков, цвета ячейки или цвета шрифта, создать настраиваемый список нельзя.
Выполните указанные ниже действия.
По приведенным выше инструкциям откройте диалоговое окно "Списки".
Выделите список, который нужно удалить, в поле Списки и нажмите кнопку Удалить.
Настраиваемые списки добавляются в реестр компьютера, чтобы их можно было использовать в других книгах. Если вы используете настраиваемый список при сортировке данных, он также сохраняется вместе с книгой, поэтому его можно использовать на других компьютерах, в том числе на серверах с Службы Excel, для которых может быть опубликована ваша книга.
Однако при открытии книги на другом компьютере или сервере такой список, сохраненный в файле книги, не отображается во всплывающем окне Списки в параметрах Excel: его можно выбрать только в столбце Порядок диалогового окна Сортировка. Настраиваемый список, сохраненный в файле книги, также недоступен непосредственно для команды Заполнить.
При необходимости можно добавить такой список в реестр компьютера или сервера, чтобы он был доступен в Параметрах Excel во всплывающем окне Списки. Для этого выберите во всплывающем окне Сортировка в столбце Порядок пункт Настраиваемый список, чтобы отобразить всплывающее окно Списки, а затем выделите настраиваемый список и нажмите кнопку Добавить.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Столкнулся с проблемой - не получается без "плясок с бубном" отсортировать "Срез" Сводной таблицы, содержащий названия месяцев, в правильном (хронологическом) порядке. Excel упорно сортирует исключительно в алфавитном порядке и даже выбор "Как в источнике данных" не спасает.
При этом, в Сводных Таблицах, построенных на "обычных" данных (без использования модели данных PowerPivot), такой проблемы не возникает, там Excel использует настраиваемые списки и названия месяцев сортируются в привычном для всех виде.
Возможно ли получить "правильно" отсортированный список с Месяцами в отчете PowerPivot без каких-либо "обходных" вариантов, один из которых я привел в примере?
Подробно примеры и вся суть проблемы расписана в приложенном фале.
Столкнулся с проблемой - не получается без "плясок с бубном" отсортировать "Срез" Сводной таблицы, содержащий названия месяцев, в правильном (хронологическом) порядке. Excel упорно сортирует исключительно в алфавитном порядке и даже выбор "Как в источнике данных" не спасает.
При этом, в Сводных Таблицах, построенных на "обычных" данных (без использования модели данных PowerPivot), такой проблемы не возникает, там Excel использует настраиваемые списки и названия месяцев сортируются в привычном для всех виде.
Возможно ли получить "правильно" отсортированный список с Месяцами в отчете PowerPivot без каких-либо "обходных" вариантов, один из которых я привел в примере?
Подробно примеры и вся суть проблемы расписана в приложенном фале. vbolotin
Столкнулся с проблемой - не получается без "плясок с бубном" отсортировать "Срез" Сводной таблицы, содержащий названия месяцев, в правильном (хронологическом) порядке. Excel упорно сортирует исключительно в алфавитном порядке и даже выбор "Как в источнике данных" не спасает.
При этом, в Сводных Таблицах, построенных на "обычных" данных (без использования модели данных PowerPivot), такой проблемы не возникает, там Excel использует настраиваемые списки и названия месяцев сортируются в привычном для всех виде.
Возможно ли получить "правильно" отсортированный список с Месяцами в отчете PowerPivot без каких-либо "обходных" вариантов, один из которых я привел в примере?
Подробно примеры и вся суть проблемы расписана в приложенном фале. Автор - vbolotin
Дата добавления - 02.06.2014 в 10:44
Попробуйте название месяцев писать не текстом, а датой 01/01/2014, 01/02/2014 и т.д. с использованием формата "ММММ".
Проверить не могу - офис 2010.
Попробуйте название месяцев писать не текстом, а датой 01/01/2014, 01/02/2014 и т.д. с использованием формата "ММММ".
Проверить не могу - офис 2010. Russel
Спасибо за идею.
Я тоже сначала хотел "обхитрить" Эксель таким способом, но даже с учетом того, что в самой "Модели данных" можно указать формат представления данных (т.е. в соответствующей таблице Модели, даты с форматом ММММ будут выглядеть как: Январь, Февраль. ), при создании среза по такому столбцу, в срез вытягиваются данные без учета формата отображения, т.е. в срезе мы увидим 01/01/2014, 01/02/2014.
Спасибо за идею.
Я тоже сначала хотел "обхитрить" Эксель таким способом, но даже с учетом того, что в самой "Модели данных" можно указать формат представления данных (т.е. в соответствующей таблице Модели, даты с форматом ММММ будут выглядеть как: Январь, Февраль. ), при создании среза по такому столбцу, в срез вытягиваются данные без учета формата отображения, т.е. в срезе мы увидим 01/01/2014, 01/02/2014. vbolotin
Спасибо за идею.
Я тоже сначала хотел "обхитрить" Эксель таким способом, но даже с учетом того, что в самой "Модели данных" можно указать формат представления данных (т.е. в соответствующей таблице Модели, даты с форматом ММММ будут выглядеть как: Январь, Февраль. ), при создании среза по такому столбцу, в срез вытягиваются данные без учета формата отображения, т.е. в срезе мы увидим 01/01/2014, 01/02/2014. Автор - vbolotin
Дата добавления - 02.06.2014 в 14:58
vbolotin, здравствуйте.
Решал похожую задачу в других программах, может Вам подойдёт. Попробуйте создать два дополнительных столбца. В один тяните номер года от даты с помощью функции ГОД, в другой - номер месяца с помощью функции МЕСЯЦ. Далее сортировка в два шага - сначала по номеру года, потом по номеру месяца.
vbolotin, здравствуйте.
Решал похожую задачу в других программах, может Вам подойдёт. Попробуйте создать два дополнительных столбца. В один тяните номер года от даты с помощью функции ГОД, в другой - номер месяца с помощью функции МЕСЯЦ. Далее сортировка в два шага - сначала по номеру года, потом по номеру месяца. Rioran
Решал похожую задачу в других программах, может Вам подойдёт. Попробуйте создать два дополнительных столбца. В один тяните номер года от даты с помощью функции ГОД, в другой - номер месяца с помощью функции МЕСЯЦ. Далее сортировка в два шага - сначала по номеру года, потом по номеру месяца. Автор - Rioran
Дата добавления - 02.06.2014 в 17:44
Rioran, приветствую.
Проблема в том, что на срезе мне нужно иметь название месяца, но как бы не был отсортирован источник данных (даже если в самой модели данных, месяцА упорядочены, хоть по дате, хоть по доп. полю 1. 12) срез все равно сортируется либо по возрастанию, либо по убыванию!
Странно, зачем тогда эта опция "Как в источнике данных"?
В общем, нормального решения не нашел. Остановлюсь на компромиссном решении: введу доп. поле 01.Январь,02.Февраль. 12.Декабрь. Его и буду выводить на срез.
Rioran, приветствую.
Проблема в том, что на срезе мне нужно иметь название месяца, но как бы не был отсортирован источник данных (даже если в самой модели данных, месяцА упорядочены, хоть по дате, хоть по доп. полю 1. 12) срез все равно сортируется либо по возрастанию, либо по убыванию!
Странно, зачем тогда эта опция "Как в источнике данных"?
В общем, нормального решения не нашел. Остановлюсь на компромиссном решении: введу доп. поле 01.Январь,02.Февраль. 12.Декабрь. Его и буду выводить на срез. vbolotin
Проблема в том, что на срезе мне нужно иметь название месяца, но как бы не был отсортирован источник данных (даже если в самой модели данных, месяцА упорядочены, хоть по дате, хоть по доп. полю 1. 12) срез все равно сортируется либо по возрастанию, либо по убыванию!
Странно, зачем тогда эта опция "Как в источнике данных"?
В общем, нормального решения не нашел. Остановлюсь на компромиссном решении: введу доп. поле 01.Январь,02.Февраль. 12.Декабрь. Его и буду выводить на срез. Автор - vbolotin
Дата добавления - 02.06.2014 в 21:57
Читайте также: