Отменить свертывание столбцов в excel
Сводную таблицу и сводную диаграмму можно развернуть и свернуть до любого уровня детализации данных; можно даже свернуть или развернуть все уровни детализации за одну операцию. Можно также развернуть или свернуть данные за следующим уровнем. Например, начиная с уровня страны или региона можно развернуть отчет до уровня городов, что приведет к развертыванию уровней областей или краев и городов. Это может сэкономить время при работе с множеством уровней детализации. Кроме того, можно развернуть или свернуть все элементы каждого поля в источнике данных OLAP.
Также можно просмотреть сведения, используемые для объединения значений в поле значений.
Сводную таблицу и сводную диаграмму можно развернуть и свернуть до любого уровня детализации данных; можно даже свернуть или развернуть все уровни детализации за одну операцию. Можно также развернуть или свернуть данные за следующим уровнем. Например, начиная с уровня страны или региона можно развернуть отчет до уровня городов, что приведет к развертыванию уровней областей или краев и городов. Это может сэкономить время при работе с множеством уровней детализации. Кроме того, можно развернуть или свернуть все элементы каждого поля в источнике данных OLAP.
Развертывание и свертывание уровней в сводной таблице
В сводной таблице выполните одно из указанных ниже действий.
Примечание: Если кнопки развертывания и свертывания не отображаются, см. раздел Отображение и скрытие кнопок развертывания и свертывания в сводной таблице в этой статье.
Дважды щелкните элемент, который нужно развернуть или свернуть.
Щелкните правой кнопкой мыши элемент, выберите команду Развернуть/свернуть и выполните одно из следующих действий.
Чтобы просмотреть сведения о текущем элементе, щелкните пункт Развернуть.
Чтобы скрыть сведения о текущем элементе, щелкните пункт Свернуть.
Чтобы скрыть сведения обо всех элементах в поле, щелкните пункт Свернуть все поле.
Чтобы просмотреть сведения обо всех элементах в поле, щелкните пункт Развернуть все поле.
Чтобы просмотреть данные за следующим уровнем детализации, щелкните пункт Развернуть до "".
Чтобы скрыть данные за следующим уровнем детализации, щелкните пункт Скрыть до "".
Развертывание и свертывание уровней в сводной диаграмме
В сводной диаграмме щелкните правой кнопкой мыши подпись категории, для которой нужно отобразить или скрыть сведения уровня, выберите команду Развернуть или свернуть и выполните одно из следующих действий.
Чтобы просмотреть сведения о текущем элементе, щелкните пункт Развернуть.
Чтобы скрыть сведения о текущем элементе, щелкните пункт Свернуть.
Чтобы скрыть сведения обо всех элементах в поле, щелкните пункт Свернуть все поле.
Чтобы просмотреть сведения обо всех элементах в поле, щелкните пункт Развернуть все поле.
Чтобы просмотреть данные за следующим уровнем детализации, щелкните пункт Развернуть до "".
Чтобы скрыть данные за следующим уровнем детализации, щелкните пункт Скрыть до "".
Отображение и скрытие кнопок развертывания и свертывания в сводной таблице
По умолчанию кнопки развертывания и свертывания отображаются, но их можно скрыть (например, при печати отчета). Чтобы можно было использовать эти кнопки для свертывания и развертывания уровней детализации отчета, они должны быть отображены.
В Excel 2016 и Excel 2013: на вкладке Анализ в группе Показать щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.
В Excel 2010: на вкладке Параметры в группе Показать щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.
В Excel 2007: на вкладке Параметры в группе Показать или скрыть щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.
Примечание: Кнопки развертывания и свертывания доступны только для полей, в которых есть данные для детализации.
Отображение и скрытие сведений для поля значений в отчете сводной таблицы
По умолчанию отображение сведений для поля значений в сводной таблице включено. Чтобы защитить эти данные от просмотра другими пользователями, их отображение можно отключить.
Отображение сведений поля значений
В сводной таблице выполните одно из указанных ниже действий.
Щелкните правой кнопкой мыши поле в области значений сводной таблицы и выберите команду Показать детали.
Дважды щелкните поле в области значений сводной таблицы.
Данные, на которых основано поле значений, будут помещены на новый лист.
Скрытие сведений поля значений
Щелкните правой кнопкой мыши ярлычок листа с данными поля значений и выберите команду Скрыть или Удалить.
Отключение и включение параметра отображения сведений поля значений
Щелкните в любом месте сводной таблицы.
На вкладке Параметры или Анализ (в зависимости от используемой версии Excel) ленты в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры сводной таблицы откройте вкладку Данные.
В разделе Данные сводной таблицы снимите или установите флажок Разрешить отображение деталей, чтобы отключить или включить этот параметр.
Примечание: Этот параметр недоступен для источника данных OLAP.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Может потребоваться отформатировыть данные ,иногда называемые выравниванием данных, чтобы поместить их в матричный формат, чтобы все похожие значения были в одном столбце. Это необходимо, например, для создания диаграммы или отчета.
При отводе распаковка пар атрибута и значения, которые представляют точку пересечения новых столбцов, и их повторное сконентировать в плоские столбцы:
Значения (синим цветом слева) неотвершяются в новый столбец (синим цветом справа).
Атрибуты (в зеленом цвете слева) будут отверждены в новый столбец (в зеленом цвете справа), а повторяющиеся атрибуты соотируются с новым столбцом Значения.
Вот пример данных, используемых в этой процедуре.
Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> Изменить. Дополнительные сведения см. в этойExcel.
Выберите один или несколько столбцов. В этом примере выберем все столбцы, кроме Country. Чтобы выбрать несколько столбцов поперемно или поперемно, нажимайте shift+щелчок или CTRL+щелчок каждого последующего столбца.
Выберите преобразовать>отобразить столбцы.
Эта команда отобирает невыделяемые столбцы. Используйте эту команду в запросе, если известны не все столбцы. Новые столбцы, добавленные во время обновления, также не будут отсвечены.
Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> Изменить. Дополнительные сведения см. в этойExcel.
Выберите столбцы, которые не нужно отобирать. Чтобы выбрать несколько столбцов поперемно или поперемно, нажимайте shift+щелчок или CTRL+щелчок каждого последующего столбца.
Выберите преобразовать> отобразить другие столбцы.
Используйте эту команду, если вы не знаете количество столбцов в источнике данных и хотите, чтобы выбранные столбцы оставались неотвеченными после обновления.
Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> Изменить. Дополнительные сведения см. в этойExcel.
Выберите столбцы, которые нужно отобирать. Чтобы выбрать несколько столбцов поперемно или поперемно, нажимайте shift+щелчок или CTRL+щелчок каждого последующего столбца.
Выберите преобразовать> отобразить только выбранные столбцы.
В Power Query столбцы можно преобразовать в пары "атрибут-значение", где столбцы становятся строками.
Схема, показывающая таблицу слева с пустым столбцом и строками, а также значения атрибутов A1, A2 и A3 в качестве заголовков столбцов. Столбец A1 содержит значения V1, V4 и V7, столбец A2 содержит значения V2, V5 и V8, а столбец A3 содержит значения V3, V6 и V9. При несводных столбцах таблица справа от диаграммы содержит пустой столбец и строки, столбец "Атрибуты" с девятью строками с повторениями A1, A2 и A3 три раза, а столбец "Значения" со значениями V1–V9.
Например, учитывая таблицу, как показано ниже, где строки страны и столбцы дат создают матрицу значений, сложно проанализировать данные масштабируемым способом.
Таблица, содержащая столбец "Страна", заданный в типе текстовых данных, и 6.01.2020, 7.01.2020 и 8.01.2020 в качестве типа данных "Целое число". Столбец Country содержит США в строке 1, Канаде в строке 2 и Панаме в строке 3.
Вместо этого можно преобразовать таблицу в таблицу с несводных столбцов, как показано на следующем рисунке. В преобразованной таблице проще использовать дату в качестве атрибута для фильтрации.
Таблица, содержащая столбец Country, заданный как тип текстовых данных, столбец Атрибута, заданный в качестве типа текстовых данных, и столбец Value, заданный как тип данных "Целое число". Столбец Country содержит США в первых трех строках, Канаде в следующих трех строках и Панаме в последних трех строках. Столбец "Атрибут" содержит 6.1.2020 в первых, вперед и седьмой строках, 7.01.2020 во втором, пятом и восьмом строках, а также 8.1.2020 в третьем, шестом и девятом строках.
Ключом этого преобразования является то, что в таблице есть набор дат, которые должны быть частью одного столбца. Соответствующее значение для каждой даты и страны должно находиться в разных столбцах, фактически создавая пару "атрибут-значение".
Power Query всегда будет создавать пару "атрибут-значение" с помощью двух столбцов:
- Атрибут: имя заголовков столбцов, которые были несведены.
- Значение: значения, которые находились под каждым из заголовков несводных столбцов.
В пользовательском интерфейсе есть несколько мест, где можно найти столбцы отмены сводных сведений. Вы можете щелкнуть правой кнопкой мыши столбцы, которые нужно отменить, или выбрать команду на вкладке "Преобразование " на ленте.
Существует три способа отмены сводных столбцов из таблицы:
- Отмена свертывания столбцов
- Отмена свораки других столбцов
- Отмена сворачений только выбранных столбцов
Отмена свертывания столбцов
Для описанного выше сценария сначала необходимо выбрать столбцы, которые требуется отменить сводные данные. При выборе нужного количества столбцов можно нажать клавиши CTRL . Для этого сценария необходимо выбрать все столбцы, кроме одной с именем Country. Выбрав столбцы, щелкните правой кнопкой мыши любой из выбранных столбцов и выберите команду "Отменить сводные столбцы".
Результат этой операции приведет к получению результата, показанного на следующем рисунке.
Таблица, содержащая столбец Country, заданный как тип текстовых данных, столбец Атрибута, заданный в качестве типа текстовых данных, и столбец Value, заданный как тип данных "Целое число". Столбец Country содержит США в первых трех строках, Канаде в следующих трех строках и Панаме в последних трех строках. Столбец "Атрибут" содержит 6.1.2020 в первых, вперед и седьмой строках, 7.01.2020 во втором, пятом и восьмом строках, а также 8.1.2020 в третьем, шестом и девятом строках. Кроме того, в области параметров запроса выделена запись "Отменить сводку столбцов", а код языка M отображается в строке формул.
Специальные рекомендации
После создания запроса на основе описанных выше шагов представьте, что исходная таблица обновляется так, чтобы она выглядела следующим образом.
Таблица с теми же исходными столбцами Country, 6.01.2020, 7.01.2020 и 8.01.2020 с добавлением столбца 9.01.2020. Столбец Country по-прежнему содержит значения США, Канады и Панамы, но также добавил Великобританию к четвертой строке, а Мексика добавила в пятую строку.
Обратите внимание, что вы добавили новый столбец даты 9.01.2020 (1 сентября 2020 г.) и две новые строки для стран Великобритании и Мексики.
При обновлении запроса вы заметите, что операция будет выполнена в обновленном столбце, но не повлияет на столбец, который не был изначально выбран (страна, в этом примере). Это означает, что любой новый столбец, добавленный в исходную таблицу, также не будет сводным.
На следующем рисунке показано, как будет выглядеть запрос после обновления с новой обновленной исходной таблицей.
Таблица со столбцами Country, Attribute и Value. Первые четыре строки столбца Country содержат США, вторая четыре строки содержит Канаду, третья четыре строки содержит Панаму, четвертое четыре строки содержит Великобританию, а пятая четыре строки содержит Мексику. Столбец "Атрибут" содержит 6.01.2020, 7.01.2020, 8.01.2020 и 9.01.2020 в первых четырех строках, повторяющихся для каждой страны.
Отмена свораки других столбцов
Кроме того, можно выбрать столбцы, которые не нужно свести и отменить сводные данные остальных столбцов в таблице. Эта операция заключается в том, что отмена сводных других столбцов вступает в игру.
Результат этой операции приведет к тому же результату, что и результат, полученный из столбцов unpivot.
Таблица, содержащая столбец Country, заданный как тип текстовых данных, столбец Атрибута, заданный в качестве типа текстовых данных, и столбец Value, заданный как тип данных "Целое число". Столбец Country содержит США в первых трех строках, Канаде в следующих трех строках и Панаме в последних трех строках. Столбец "Атрибут" содержит 6.1.2020 в первых, вперед и седьмой строках, 7.01.2020 во втором, пятом и восьмом строках, а также 8.1.2020 в третьем, шестом и девятом строках.
Это преобразование имеет решающее значение для запросов с неизвестным числом столбцов. Операция отменит сводку всех столбцов из таблицы, за исключением выбранных. Это идеальное решение, если источник данных вашего сценария получил новые столбцы даты в обновлении, так как они будут выбраны и свернуты.
Специальные рекомендации
Аналогично операции отмены сводных столбцов , если запрос обновляется и из источника данных берется больше данных, все столбцы будут не сводными, за исключением тех, которые были выбраны ранее.
Чтобы проиллюстрировать это, предположим, что у вас есть новая таблица, как на следующем рисунке.
Таблица с страной, 6.01.2020, 7.01.2020, 8.01.2020 и 9.01.2020, а все столбцы — типом текстовых данных. Столбец Country содержит от верхнего до нижнего, США, Канады, Панамы, Великобритании и Мексики.
Вы можете выбрать столбец "Страна ", а затем выбрать "Отменить сводку другого столбца", что приведет к следующему результату.
Таблица со столбцами Country, Attribute и Value. Столбцы "Страна" и "Атрибут" задаются в качестве типа текстовых данных. Столбец Value имеет тип данных "Целое значение". Первые четыре строки столбца Country содержат США, вторая четыре строки содержит Канаду, третий четыре строки содержит Панаму, четвертый четыре строки содержит Великобританию, а пятый четыре строки содержит Мексику. Столбец "Атрибут" содержит 6.01.2020, 7.01.2020, 8.01.2020 и 9.01.2020 в первых четырех строках, повторяющихся для каждой страны.
Отмена сворачений только выбранных столбцов
Последний вариант предназначен только для отмены сводки определенных столбцов из таблицы. Это важно для сценариев, когда вы работаете с неизвестным числом столбцов из источника данных, и вы хотите отменить сводку выбранных столбцов.
Чтобы выполнить эту операцию, выберите столбцы для отмены сводных данных, которые в этом примере являются всеми столбцами, кроме столбца Country . Затем щелкните правой кнопкой мыши любой из выбранных столбцов и выберите команду "Отменить сводку только выбранных столбцов".
Обратите внимание, что эта операция будет выдавать те же выходные данные, что и в предыдущих примерах.
Таблица, содержащая столбец Country, заданный как тип текстовых данных, столбец Атрибута, заданный в качестве типа текстовых данных, и столбец Value, заданный как тип данных "Целое число". Столбец Country содержит США в первых трех строках, Канаде в следующих трех строках и Панаме в последних трех строках. Столбец "Атрибут" содержит 6.1.2020 в первых, вперед и седьмой строках, 7.01.2020 во втором, пятом и восьмом строках, а также 8.1.2020 в третьем, шестом и девятом строках.
Специальные рекомендации
После обновления, если исходная таблица изменится на новый столбец 9.01.2020 и новые строки для Великобритании и Мексики, выходные данные запроса будут отличаться от предыдущих примеров. Предположим, что исходная таблица после обновления изменится на таблицу на следующем рисунке.
Выходные данные запроса будут выглядеть так, как показано на следующем рисунке.
Похоже, что операция отмены сводных была применена только к столбцам 6.01.2020, 7.01.2020 и 8.01.2020 , поэтому столбец с заголовком 9.01.2020 остается неизменным.
В предыдущем разделе был приведен универсальный алгоритм отмены свертывания столбцов таблицы с уровнями иерархии N*M. Отмена свертывания столбцов настолько полезна, что имеет смысл написать функцию. Такая функция позволит быстро обрабатывать исходные таблицы, берущие своё происхождение из сводных таблиц любой иерархии. С помощью такой функции ручное выполнение последовательности шагов объединяется в одно преобразование, которое будет пригодно для любой таблицы.
Рис. 1. Исходная таблица с иерархией 3х3
Создание запросов (списков) RowFields, ColumnFields и ValueField
Начнем с результатов предыдущего раздела. Загрузите книгу C07E01 — Solution.xlsx. Откройте её. Пройдите Данные –> Получить данные –> Запустить редактор Power Query. Откройте пустой запрос. Правой кнопкой мыши щелкните на панели Запросы, пройдите Новый запрос –> Другие источники –> Пустой запрос. Переименуйте новый запрос в RowFields и скопируйте следующий код в строку формул для создания списка полей строк:
Создайте второй пустой запрос. Переименуйте его в ColumnFields и скопируйте в строку формул следующий код для создания списка полей столбцов:
Важно именно так назвать новые запросы. Далее они будут использоваться в коде функции. Обратите внимание, что N представляет длину запроса RowFields, а буква M – длину запроса ColumnFields.
Создайте еще один пустой запрос. Переименуйте его в ValueField, и скопируйте следующий код в строку формул:
Удаление шагов Changed Type
Шаги Changed Type (Измененный тип) предназначены именно для текущего источника данных и не помогают обобщать запрос для обработки произвольной таблицы. Выберите запрос Revenues. Найдите третий шаг Changed Type. Данный шаг генерируется автоматически при импорте исходной таблицы в редактор Power Query. Если в дальнейшем будете работать с таблицами меньшего размера, в которых нет одного из перечисленных здесь столбцов, запрос не будет выполняться. Удалить этот шаг. Найдите шаг Changed Type 1. Удалите его. Найдите шаг Changed Type 2. Этот шаг был сгенерирован при разделении столбца Атрибут на три столбца. Этот шаг также можно удалить, поскольку он не будет применяться в некоторых ситуациях. Например, для преобразования отмены свертывания столбцов при двух уровнях иерархии нет смысла применять код, изменяющий типы трех столбцов. Удалите этот шаг.
Модификация шага Filled Down
Изменим формулы, которые явно ссылаются на имена столбцов, и обобщим их для формирования динамических ссылок на столбцы, исходя из значений длин запросов RowFields и ColumnFields.
Выберите запрос Revenues и шаг Filled Down. В строке формул появится следующий код:
Заполняются значениями первые N – 1 столбцы. Модифицируем код, изменив жестко закодированный список на динамический список имен первых N – 1 столбцов в текущей таблице.
Прежде всего рассмотрим, почему в этой формуле применяется шаг Revenues_DefinedName вместо шага Navigation. Посмотрев на панель Примененные шаги, заметим, что шаг Filled Down находится ниже шага Navigation. Обычно в строке формул отображается ссылка на имя таблицы, которое совпадает с именем предыдущего шага. Но в данном случае видно, что в формуле шаг Revenues_DefinedName применяется вместо шага Navigation. Чтобы понять, в чем дело, выберите команду Расширенный редактор на вкладке Главная и обратите внимание на следующую строку:
Как видите, корректным идентификатором предыдущей строки для применения преобразования Filled Down является Revenues_DefinedName (данный идентификатор применяется перед первым знаком равенства). Теперь, когда известно, как обратиться к предыдущему шагу, изменим шаг Filled Down, чтобы в общем случае заполнить N – 1 столбцов.
Вот исходная формула шага Filled Down:
Функция Table.FillDown ранее получала статический список в качестве второго аргумента. Для превращения его в динамический список сначала необходимо получить список всех имен столбцов таблицы. Это можно сделать с помощью функции Table.ColumnNames на предыдущем шаге Revenues_DefinedName:
Функция Table.ColumnNames возвращает все имена столбцов для текущей таблицы: Column1, Column2, Column3 и т. д. Для возвращения только первых N – 1 столбцов необходимо вычислить значение N – 1. В данном случае N представляет счетчик списка RowFields:
Поэтому N – 1 будет иметь следующий вид:
Теперь, когда известно, как вычислить N – 1, можно получить имена первых N – 1 столбцов, применяя функцию List.FirstN, которая возвращает список с первыми элементами исходного списка. Функция List.FirstN имеет два аргумента: список ввода и количество первых элементов для выборки. Можно сформировать список с помощью следующего кода:
Теперь эта формула применяется в качестве второго аргумента в обновленной функции Table.FillDown:
Модификация шага Merged Columns
Выберите шаг Merged Columns. Этот шаг сгенерирован при объединении столбцов. В строке формул жестко закодированная ссылка на список :
Для получения динамической ссылки на N имен столбцов исходной таблицы можно применить тот же код, что и выше, но на этот раз с N в качестве второго аргумента (вместо N – 1). Итоговая формула для шага Merged Columns:
Модификация шага Filled Down 1
Этот шаг сгенерирован ранее для второй операции заполнения. Вот его код:
Изменим жестко закодированные имена столбцов с учетом динамической версии. На этот раз, поскольку таблица транспонирована и обработаны поля столбцов, необходимо заполнить первые M – 1 столбца списка ColumnFields. Вот модифицированный код:
Модификация шага Unpivoted Other Columns
Выберите шаг Unpivoted Other Columns. В строке формул будут отображены следующие жестко закодированные столбцы:
При работе с интерфейсом эти столбцы были выделены как первые M столбцов таблицы. Для динамического получения первых M столбцов применим следующий код:
Использован запрос ValueField вместо названия столбца Value в качестве нового имени для значений. Напомним, что имя ValueField = Revenue.
Модификация шага Split Column by Delimiter
Выберите шаг Split Column by Delimiter. Ему соответствует код, который разбивает столбец Attribute на N столбцов:
Третий аргумент функции Table.SplitColumn определяет два важных элемента: число столбцов для разделения и названия разделенных столбцов. Вместо названий Attribute.X, заданных по умолчанию, можно использовать поля строк Country, State/Region, City, которые определены в запросе RowFields. Вот модифицированный код:
Модификация шага Renamed Columns
Выберите шаг Renamed Columns. Его код:
Этот код переименовывает столбцы, передавая список списков в качестве второго аргумента функции Table.RenameColumns. Каждый внутренний список представляет пару текстовых значений: первый текст — это имя существующего столбца, а второй — имя нового столбца.
Обратите внимание, что на шаге Unpivoted Other Columns столбец Revenue уже был правильно переименован. А на шаге Split Column by Delimiter были верно названы поля строк. Вот как выглядит запрос на шаге Split Column by Delimiter:
Рис. 2. Названия некоторых столбцов уже правильные; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Теперь нужно переименовать первые M столбцов. Для получения списка названий первых M столбцов выполните следующий код:
Для получения новых названий можно просто выполнить запрос ColumnFields.
Каким образом создать список списков, в котором каждый элемент из первого имени столбца M связан с полями столбцов? Для этого можно применить функцию языка M List.Zip, которая получает два списка в качестве входных данных и возвращает один список списков с парами из обоих списков. Например, следующие входные данные:
возвращают такой список:
Таким образом, применяя функцию List.Zip для получения названий первых M столбцов таблицы для полей столбцов, имеем следующий код:
и получим желаемый список списков, который используется в оригинальной формуле:
Итого обновленный код для шага Renamed Columns:
Восстановление шага Changed Type
Ранее мы удалили несколько шагов Changed Type (Измененный тип). Теперь пришло время явно изменить тип столбца Revenue на Десятичное число. Если выполнить эту операцию с помощью пользовательского интерфейса, то базовый код в строке формул получит следующий вид:
Измените жестко закодированное значение «Revenue» на ValueField:
Придание универсальности запросу
Пришло время отсоединить обобщенную последовательность отмены свертывания столбцов от ее источника. Можно заметить, что первые два шага зависят от конкретной рабочей книги и рабочего листа. Также можно обобщить эти шаги и запустить последовательность отмены свертывания столбцов из любого источника данных.
Щелкните правой кнопкой мыши на шаге Filled Down и выберите команду Извлечь предыдущий. В окне Извлечение шагов выберите Имя нового запроса – Source, нажмите Ok. Создается новый запрос, который загрузит исходную таблицу из файла Excel. Обобщенный запрос запустится как ссылка на новый запрос.
Преобразование запроса в функцию
Если кликнуть правой кнопкой мыши на запрос Source, можно найти опцию Создать функцию… Однако мы создадим функцию с помощью расширенного редактора, поскольку окно диалога Создать функцию не слишком успешно может применяться для работы со списками.
Щелкните правой кнопкой мыши на запросе Revenues, и выберите Расширенный редактор. Отобразившийся на экране код уже включает в себя модифицированную версию запроса. Для преобразования запроса в функцию введите над строкой let:
Щелкните Готово и переименуйте запрос Revenues в FnUnpivotSummarizedTable.
Обратите внимание, что FnUnpivotSummarizedTable на панели Запросы отмечена значком fx. После выбора этой функции предварительный просмотр данных больше не отображается. Вместо этого на панели предварительного просмотра отображается панель Ввод параметров, которую можно использовать для вызова функции. Однако в данном случае нас это не устраивает, поскольку нельзя заполнить функцию списками. Обратите также внимание, что в разделе Примененные шаги весь набор шагов сведен к одному шагу и нельзя изменять функцию средствами пользовательского интерфейса.
Рис. 3. Интерфейс функции
Если функция создается с помощью расширенного редактора, а не с помощью команды пользовательского интерфейса Создать функцию, то вне расширенного редактора изменить функцию невозможно.
Однако можно продублировать функцию, а в дублированном запросе можно удалить первую строку или закомментировать ее, добавив символы // в начале строки. Таким образом, удается преобразовать дублированную функцию в редактируемый запрос. После этого можно просмотреть шаги на панели Примененные шаги и внести изменения средствами пользовательского интерфейса. Завершив изменение нового запроса, можно скопировать код из расширенного редактора в исходную функцию.
Функция может быть написана как отдельный запрос, как это выполнено в этом случае, или как внутренний элемент внутри запроса. При вызове с аргументами создается новое значение. Можно вызвать функцию, перечислив параметры функции в формате «через запятую» в круглых скобках, за именем функции.
Итак, чего мы достигли к настоящему моменту. Вот аргументы, которые объявлены в функции:
- Source – таблица исходных данных;
- RowFields – список полей строк, в данном случае ;
- ColumnFields – список полей столбцов, в данном случае ;
- ValueField – имя для столбца Value, в данном случае "Revenue".
Символ «следования» => служит разделом между объявлением интерфейса функции и реализацией функции. Уже завершена часть реализации функции, когда обобщен каждый из соответствующих шагов с помощью параметров Source, RowFields, ColumnFields и ValueField.
Нужно отметить, что заголовок функции также может быть задан с явными типами, которые требуются в качестве входных аргументов, и ожидаемым типом вывода, используя элемент as, за которым следует конкретный тип. Вот пример строго типизированного заголовка функции:
Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.
Cводные таблицы очень полезны для финального представления данных. Беда в том, что получив данные в форме сводной таблицы, часто возникает потребность их последующей обработки. Допустим, сотрудник отчитывается о своих продажах на ежедневной основе, и отправляет вам данные в формате:
Рис. 6.1. Отчет в формате сводный таблицы (см. файл UnPivot.xlsx); чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
До появления Power Query не существовало автоматического метода разбора сводной таблицы в вид, пригодный для последующей обработки.
Щелкните любую ячейку в диапазоне А4:I8 и создайте новый запрос: Данные –> Из таблицы/диапазона. (рис. 6.2). Подтвердите диапазон $A$4:$I$7 (без строки итогов). Этот диапазон на исходном листе Excel будет преобразован в Таблицу.
Рис. 6.2. Создание запроса из диапазона
Данные импортируются в Power Query, и теперь их можно обработать. Цель – развернуть сводную таблицу, но прежде нужно удалить столбец с итогами. Щелкните правой кнопкой мыши столбец Total –> Удалить.
Выберите столбец 01.01.2014. Удерживая нажатой клавишу Shift, выберите столбец 01.07.2014. Щелкните правой кнопкой мыши один из выделенных столбцов –> Отменить свертывание столбцов.
Рис. 6.3. Вид запроса после отмены свертывания столбцов
- Щелкните правой кнопкой мыши столбец Атрибут –>Тип изменения –>Дата
- Щелкните правой кнопкой мыши столбец Атрибут –>Переименовать –>Дата
- Щелкните правой кнопкой мыши столбец Значение –>Переименовать –>Кол-во
- Щелкните правой кнопкой мыши столбец Значение –>Тип изменения –>Целое число
- Переименуйте запрос –>Ежедневные продажи
- Главная –>Закрыть и загрузить
Обратите внимание, что в этом примере нет необходимости применять тип изменения с использованием локали (языкового стандарта, подробнее см. Глава 2. Изменение настроек Power Query, действующих по умолчанию). Поскольку данные находятся внутри Excel, Power Query корректно распознает эти данные независимо от ваших региональных настроек и того в каких настройках был подготовлен исходный файл.
На основании импортированных данных можно заново построить сводные таблицы с нужными вам срезами.
На самом деле Power Query включает три функции отмены свертывания столбцов:
Рис. 6.4. Функции отмены свертывания столбцов
В примере выше мы выбирали столбцы с 01.01.2014 по 01.07.2014 (см. рис. 6.2), а могли выбрать столбец Sales Category, и выполнить команду Отменить свертывание других столбцов. Такой подход отлично сработает, если в Таблицу будут добавлены новые столбцы. Обновление запроса «подхватит» новые данные.
Как на самом деле работает преобразование столбцов
Исходя из названия команды (Отменить свертывание столбцов), можно было ожидать, что при записи этого шага Power Query будет жестко кодировать названия выбранных столбцов. Однако, Power Query посмотрел на все столбцы в наборе данных и определил, что есть лишь один невыбранный столбец. Вместо команды «отменить свертывание выбранных 7 столбцов», Power Query фактически записала код, который говорит «отменить свертывание всех столбцов, кроме одного невыбранного».
Хотя это изменение кажется весьма тонким, оно имеет довольно большие последствия. Трудно ошибиться и построить сценарий, который перестанет работать при добавлении новых столбцов ежедневных продаж. Если же вы хотите, чтобы новые столбцы (добавленные в Таблицу позже) не попали под команду «отменить свертывание», воспользуйтесь опцией Отменить свертывание только для выбранных столбцов.
Любопытно, что в момент написания книги третья функция отмены свертывания отсутствовала, и авторы посвящают страницу тому, как с помощью редактирования кода можно всё же реализовать такую возможность. – Прим. Багузина.
Читайте также: