Excel срез использовать настраиваемые списки при сортировке
Столкнулся с проблемой - не получается без "плясок с бубном" отсортировать "Срез" Сводной таблицы, содержащий названия месяцев, в правильном (хронологическом) порядке. Excel упорно сортирует исключительно в алфавитном порядке и даже выбор "Как в источнике данных" не спасает.
При этом, в Сводных Таблицах, построенных на "обычных" данных (без использования модели данных PowerPivot), такой проблемы не возникает, там Excel использует настраиваемые списки и названия месяцев сортируются в привычном для всех виде.
Возможно ли получить "правильно" отсортированный список с Месяцами в отчете PowerPivot без каких-либо "обходных" вариантов, один из которых я привел в примере?
Подробно примеры и вся суть проблемы расписана в приложенном фале.
Столкнулся с проблемой - не получается без "плясок с бубном" отсортировать "Срез" Сводной таблицы, содержащий названия месяцев, в правильном (хронологическом) порядке. Excel упорно сортирует исключительно в алфавитном порядке и даже выбор "Как в источнике данных" не спасает.
При этом, в Сводных Таблицах, построенных на "обычных" данных (без использования модели данных PowerPivot), такой проблемы не возникает, там Excel использует настраиваемые списки и названия месяцев сортируются в привычном для всех виде.
Возможно ли получить "правильно" отсортированный список с Месяцами в отчете PowerPivot без каких-либо "обходных" вариантов, один из которых я привел в примере?
Подробно примеры и вся суть проблемы расписана в приложенном фале. vbolotin
Столкнулся с проблемой - не получается без "плясок с бубном" отсортировать "Срез" Сводной таблицы, содержащий названия месяцев, в правильном (хронологическом) порядке. Excel упорно сортирует исключительно в алфавитном порядке и даже выбор "Как в источнике данных" не спасает.
При этом, в Сводных Таблицах, построенных на "обычных" данных (без использования модели данных PowerPivot), такой проблемы не возникает, там Excel использует настраиваемые списки и названия месяцев сортируются в привычном для всех виде.
Возможно ли получить "правильно" отсортированный список с Месяцами в отчете PowerPivot без каких-либо "обходных" вариантов, один из которых я привел в примере?
Подробно примеры и вся суть проблемы расписана в приложенном фале. Автор - vbolotin
Дата добавления - 02.06.2014 в 10:44
Срезы предоставляют кнопки, которые можно использовать для фильтрации таблицили срезов. Помимо быстрой фильтрации срезы также указывают текущее состояние фильтрации, что позволяет легко понять, что именно отображается в данный момент.
С помощью среза можно с легкостью фильтровать данные в таблице или таблице.
Создание среза для фильтрации данных
Щелкните в любом месте таблицы или таблицы.
На вкладке "Главная" перейдите к срезу>".
В диалоговом окне "Вставка срезов" выберите флажки для полей, которые вы хотите отобразить, а затем выберите "ОК".
Для каждого выбранного поля будет создан срез. Если нажать любую из кнопок среза, фильтр будет автоматически применяться к связанной таблице или таблице.
Чтобы выбрать несколько элементов, нажмите клавишу CTRL и, удерживая ее нажатой, щелкните каждый из элементов, которые нужно отобразить.
Вы можете настроить параметры среза на вкладке "Срез" (в более новых версиях Excel) или на вкладке "Конструктор" (в Excel 2016 и более старых версиях) на ленте.
Примечание: Щелкните и удерживайте угол среза, чтобы настроить и изменить его размер.
Если вы хотите подключить срез к одной или несколько срезов, перейдите в > "Подключения к отчетам> проверьте срез, который нужно включить в срез, и выберите "ОК".
Примечание: Срезы можно связывать только со срезами, которые имеют один и тот же источник данных.
Компоненты среза
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
Щелкните в любом месте сводной таблицы, для которой хотите создать срез.
Откроется вкладка Анализ сводной таблицы.
На вкладке Анализ сводной таблицы нажмите кнопку Вставить срез.
В диалоговом окне Вставка срезов установите флажки для полей сводной таблицы, для которых нужно создать срез.
Для каждого выбранного поля будет отображен срез.
В каждом срезе выберите элементы, которые нужно отфильтровать.
Чтобы выбрать более одного элемента, нажмите клавишу COMMAND и, удерживая ее, щелкните каждый из элементов, которые нужно отфильтровать.
Щелкните в любом месте таблицы, для которой хотите создать срез.
Откроется вкладка Таблица.
На вкладке Таблица нажмите кнопку Вставить срез.
В диалоговом окне Вставка срезов установите флажки рядом с полями (столбцами), для которых нужно создать срез.
Для каждого выбранного поля (столбца) будет отображен срез.
В каждом срезе выберите элементы, которые нужно отфильтровать.
Чтобы выбрать более одного элемента, нажмите клавишу COMMAND и, удерживая ее, щелкните каждый из элементов, которые нужно отфильтровать.
Щелкните срез, который хотите отформатировать.
Откроется вкладка Срез.
На вкладке Срез щелкните цветной стиль, который хотите выбрать.
Доступ к срезу для использования в другой сводной таблицеЕсли для сводной таблицы уже есть срез, вы можете использовать его для фильтрации другой сводной таблицы. Обратите внимание: вы можете использовать эту возможность, только если для этих сводных таблиц используется один источник данных.
Сначала создайте сводную таблицу на основе того же источника данных, что использовался для сводной таблицы со срезом, который вы хотите повторно использовать.
Щелкните срез, который хотите использовать в другой сводной таблице.
Откроется вкладка Срез.
На вкладке Срез нажмите кнопку Подключения к отчетам.
В диалоговом окне установите флажки рядом со сводными таблицами, в которых должен быть доступен срез.
Щелкните в любом месте сводной таблицы, для которой хотите отключить срез.
Откроется вкладка Анализ сводной таблицы.
Перейдите на вкладку Анализ сводной таблицы и нажмите кнопку Подключения к фильтрам.
В диалоговом окне снимите флажки рядом со всеми полями сводной таблицы, для которых вы хотите отключить срез.
Выполните одно из указанных ниже действий.
Щелкните срез и нажмите клавишу DELETE.
Щелкните срез, удерживая нажатой клавишу CONTROL, и выберите команду Удалить <имя среза>.
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
Примечание: Excel в Интернете позволяет использовать срезы, которые были созданы в классических версиях Excel, но не поддерживают создание срезов, редактирование срезов и выбор нескольких значений в срезе.
Выполните одно из указанных ниже действий.
Щелкните срез и нажмите клавишу DELETE.
Щелкните срез, удерживая нажатой клавишу CONTROL, и выберите команду Удалить <имя среза>.
Срез обычно отображает указанные ниже компоненты.
1. Заголовок среза указывает категорию элементов в срезе.
2. Ненажатая кнопка фильтрации показывает, что элемент не включен в фильтр.
3. Нажатая кнопка фильтрации показывает, что элемент включен в фильтр.
4. Кнопка Очистить фильтр удаляет фильтр, выбирая все элементы в срезе.
5. Полоса прокрутки позволяет прокручивать срез, если в нем помещаются не все элементы.
6. С помощью элементов управления для перемещения границ и изменения размеров можно настроить размеры и расположение среза.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Для сортировки или заполнения значений в пользовательском порядке можно применять настраиваемые списки. В Excel есть встроенные списки дней недели и месяцев года, но вы можете создавать и свои настраиваемые списки.
Чтобы понять, что представляют собой настраиваемые списки, полезно ознакомиться с принципами их работы и хранения на компьютере.
Сравнение встроенных и настраиваемых списков
В Excel есть указанные ниже встроенные списки дней недели и месяцев года.
Встроенные списки
Пн, Вт, Ср, Чт, Пт, Сб, Вс
Понедельник, Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье
янв, фев, мар, апр, май, июн, июл, авг, сен, окт, ноя, дек
Январь, Февраль, Март, Апрель, Май, Июнь, Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь
Примечание: Изменить или удалить встроенный список невозможно.
Вы также можете создать свой настраиваемый список и использовать его для сортировки или заполнения. Например, чтобы отсортировать или заполнить значения по приведенным ниже спискам, нужен настраиваемый список, так как соответствующего естественного порядка значений не существует.
Настраиваемые списки
Высокое, Среднее, Низкое
Большое, Среднее, Малое
Север, Юг, Восток, Запад
Старший менеджер по продажам, Региональный менеджер по продажам, Руководитель отдела продаж, Торговый представитель
Настраиваемый список может соответствовать диапазону ячеек, или его можно ввести в диалоговом окне Списки.
Примечание: Настраиваемый список может содержать только текст или текст с числами. Чтобы создать настраиваемый список, содержащий только числа, например от 0 до 100, нужно сначала создать список чисел в текстовом формате.
Создать настраиваемый список можно двумя способами. Если список короткий, можно ввести его значения прямо во всплывающем окне. Если список длинный, можно импортировать значения из диапазона ячеек.
Введение значений напрямую
Чтобы создать настраиваемый список этим способом, выполните указанные ниже действия.
В Excel 2010 и более поздних версиях выберите пункты Файл > Параметры > Дополнительно > Общие > Изменить списки.
В Excel 2007 нажмите кнопку Microsoft Office и выберите пункты Параметры Excel > Популярные > Основные параметры работы с Excel > Изменить списки.
Выберите в поле Списки пункт НОВЫЙ СПИСОК и введите данные в поле Элементы списка, начиная с первого элемента.
После ввода каждого элемента нажимайте клавишу ВВОД.
Завершив создание списка, нажмите кнопку Добавить.
На панели Списки появятся введенные вами элементы.
Нажмите два раза кнопку ОК.
Создание настраиваемого списка на основе диапазона ячеек
Выполните указанные ниже действия.
В диапазоне ячеек введите сверху вниз значения, по которым нужно выполнить сортировку или заполнение. Выделите этот диапазон и, следуя инструкциям выше, откройте всплывающее окно "Списки".
Убедитесь, что ссылка на выделенные значения отображается в окне Списки в поле Импорт списка из ячеек, и нажмите кнопку Импорт.
На панели Списки появятся выбранные вами элементы.
Два раза нажмите кнопку ОК.
Примечание: Настраиваемый список можно создать только на основе значений, таких как текст, числа, даты и время. На основе формата, например значков, цвета ячейки или цвета шрифта, создать настраиваемый список нельзя.
Выполните указанные ниже действия.
По приведенным выше инструкциям откройте диалоговое окно "Списки".
Выделите список, который нужно удалить, в поле Списки и нажмите кнопку Удалить.
Настраиваемые списки добавляются в реестр компьютера, чтобы их можно было использовать в других книгах. Если вы используете настраиваемый список при сортировке данных, он также сохраняется вместе с книгой, поэтому его можно использовать на других компьютерах, в том числе на серверах с Службы Excel, для которых может быть опубликована ваша книга.
Однако при открытии книги на другом компьютере или сервере такой список, сохраненный в файле книги, не отображается во всплывающем окне Списки в параметрах Excel: его можно выбрать только в столбце Порядок диалогового окна Сортировка. Настраиваемый список, сохраненный в файле книги, также недоступен непосредственно для команды Заполнить.
При необходимости можно добавить такой список в реестр компьютера или сервера, чтобы он был доступен в Параметрах Excel во всплывающем окне Списки. Для этого выберите во всплывающем окне Сортировка в столбце Порядок пункт Настраиваемый список, чтобы отобразить всплывающее окно Списки, а затем выделите настраиваемый список и нажмите кнопку Добавить.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Это продолжение перевода книги Зак Барресс и Кевин Джонс. Таблицы Excel: Полное руководство для создания, использования и автоматизации списков и таблиц (Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables by Zack Barresse and Kevin Jones. Published by: Holy Macro! Books. First printing: July 2014. – 161 p.).
Таблицы сортируются с помощью элементов управления Автофильтра. Когда Таблица сортируется, Excel автоматически ограничивает область сортировки диапазоном тела данных Таблицы, и не включает в сортировку строки, лежащие ниже Таблицы.
Рис. 6.1. Меню сортировки Таблиц
С точки зрения доступных вариантов сортировки, нет никакой разницы между сортировкой стандартного диапазона ячеек и сортировкой Таблицы. Существует три основных типа сортировки: по возрастанию, по убыванию, по цвету.
При сортировке по цвету Excel отображает список всех цветов заливки ячеек, используемых в выбранном столбце. В Excel перечислены только цвета заливки ячеек, примененные вручную или с условным форматированием (но не цвета стиля Таблицы). Если цвет заливки ячейки, примененный вручную, переопределяется условно примененным цветом заливки ячейки, то базовый цвет заливки ячейки, примененный вручную, не учитывается при построении списка или сортировке списка.
Вы можете просмотреть параметры сортировки, щелкнув раскрывающийся элемент Автофильтр в правой части каждой ячейки заголовка. На рис. 6.1 показано меню сортировки и фильтрации, которое появляется при нажатии раскрывающегося списка столбца Item.
Существует два вида раскрывающихся элементов управления, связанных с ячейками: список проверки данных и Автофильтр. Они выглядят одинаково, но расположены по разные стороны правой границы ячейки:
Рис. 6.2. Автофильтр и список проверки данных
Кроме того, раскрывающиеся элементы управления Автофильтром отображаются всегда, независимо от того, выбрана ячейка или нет. Элементы управления списком проверки отображаются только тогда, когда ячейка выбрана и активна.
Вы также можете получить доступ к командам сортировки, пройдя по меню Главная –> Редактирование –> Сортировка и фильтр.
Пользовательская сортировка
В нижней части меню Сортировать по цвету находится команда Пользовательская сортировка (рис. 6.3). К ней также можно получить доступ, пройдя по меню Данные –> Сортировка.
Рис. 6.3. Вызов пользовательской сортировки
При выборе этой команды Excel открывает диалоговое окно Сортировка, которое позволяет выполнять сортировку по нескольким столбцам и условиям:
Рис. 6.4. Диалоговое окно Сортировка для пользовательской сортировки; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Если столбец отсортирован значок Автофильтра изменяется, чтобы показать стрелку вверх (по возрастанию / по алфавиту) или вниз (по убыванию):
Рис. 6.5. Значок Автофильтра после сортировки
Фильтры
Фильтрация Таблиц работает так же, как и фильтрация стандартных диапазонов рабочего листа. Excel допускает только один автофильтр для каждого листа, но таблицы имеют свой собственный автофильтр в дополнение к автофильтру листа. Поэтому можно иметь автофильтр для каждой таблицы плюс один дополнительный автофильтр для рабочего листа.
Excel позволяет выполнять фильтрацию несколькими способами: по цвету, по тексту/числу/дате.
Команда Фильтр по цвету доступна только в том случае, если в столбце есть ячейки с цветами заливки, отличными от тех, что применяются в стиле Таблицы.
Если столбец содержит в основном текстовые значения, Excel отображает меню Текстовые фильтры с разнообразными параметрами фильтрации:
Рис. 6.6. Текстовые фильтры
Если столбец содержит в основном числовые значения, Excel отображает меню Числовые фильтры:
Рис. 6.7. Числовые фильтры
Если столбец содержит в основном значения дат, Excel отображает меню Фильтры по дате:
Рис. 6.8. Фильтры по дате
В нижней части меню Автофильтра находится список всех уникальных значений этого столбца. Если столбец содержит в основном значения даты или времени, то список организован иерархически, начиная с года на самом высоком уровне, затем месяцы, дни и так далее. Вы можете выбрать любую комбинацию значений. Вы можете использовать поле поиска в средней части окна, чтобы быстро сузить список значений до определенного набора, соответствующего введенному значению поиска.
Когда Таблица отфильтрована, номера строк в левой части окна рабочего листа изменяются с черного на синий:
Рис. 6.9. Номера строк отфильтрованной Таблицы приобретают синий цвет
Еще один способ узнать, применен ли фильтр (и какие столбцы фильтруются) – посмотреть на значки Автофильтра. Когда столбец фильтруется, значок изменяется на знак воронки (см. рис. 6.9).
При применении фильтра к Таблице, если скрыть строку заголовка, Excel удаляет фильтр и отключает управление кнопкой фильтра. И обходного пути нет.
Чтобы удалить фильтр из столбца, кликните стрелку Автофильтра и выберите пункт Удалить фильтр из столбца… Если столбец не фильтруется, эта команда отключена.
Срезы
Срезы были впервые представлены в Excel 2010 только для сводных таблиц. Начиная с Excel 2013 они доступны и для Таблиц. Срез – это визуальный интерактивный элемент управления фильтрацией. Срезы выводят уникальные значения одного столбца в виде кнопок. По умолчанию при нажатии кнопки Excel включает это значение и исключает все остальные. Если нажать клавишу Ctrl и одну из кнопок, а затем отпустить Ctrl, выберутся все кнопки, кроме нажатой. Если нажать и удерживать клавишу Shift (или Ctrl), и нажать несколько кнопок, то все, относящиеся к ним значения, будут показаны.
Чтобы добавить срез на лист Excel, кликните любую ячейку Таблицы и пройдите по меню Конструктор –> Вставить срез. Откроется диалоговое окно вставка срезов:
Рис. 6.10. Диалоговое окно Вставка срезов
Вы можете выбрать столбец(ы), для которого вы хотите вывести срез, а затем нажать ОК. Excel создает по одному срезу для каждого выбранного столбца. После создания среза Excel помещает его на рабочий лист. Если кликнуть по срезу, появится вкладка Параметры:
Рис. 6.11. Вкладка Параметры для настройки среза
Вы можете добавить дублирующие срезы для одного и того же столбца. При этом срезы по умолчанию связаны с одним и тем же источником данных, и изменить эту связь невозможно. Вы не можете фильтровать значения столбца, если они неактивны из-за фильтра в других столбцах. Вы можете указать Excel, что делать со значениями, которые недоступны, установив один из трех флажков в диалоговом окне Настройка среза: Скрыть элементы без данных, Выделить пустые элементы (при этом они будут неактивны и иметь более бледный тон) и Отображать пустые элементы последними (если галочку отжать, то все элементы, и активные, и неактивные, будут отсортированы по алфавиту / по возрастанию).
Рис. 6.12. Настройка среза
Вкладка ленты Инструменты для среза –> Параметры позволяет настроить визуальный стиль, количество столбцов, размеры среза, кнопок и др.
Срезы Таблиц аналогичны срезам сводных таблиц и поэтому имеют много общего в пользовательском интерфейсе. Например, контекстное меню среза Таблицы совпадает с контекстным меню среза сводной таблицы. Отличие заключается в том, что команды Обновить и Подключения к отчетам отключены для Таблиц, поскольку эти функции нужны только для сводных таблиц.
Рис. 6.13. Контекстное меню среза Таблицы
Если строка заголовка Таблицы отключена, вы не можете использовать срезы. Все срезы, связанные с Таблицей отображаются как устаревшие (рис. 6.14). Вы можете нажать кнопку Обновить, чтобы включить строку заголовка таблицы и сделать срезы активными.
Рис. 6.14. Неактивные срезы после отключения заголовка Таблицы
Временная шкала – это вариант среза, который специально предназначен для дат. Временные шкалы доступны только для сводных таблиц, но не для Таблиц Excel.
Читайте также: