Прочитать на английском

Поделиться через


Сводные столбцы

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

Схема столбцов сводной таблицы.

Схема, показывающая таблицу слева с пустым столбцом и строками. Столбец "Атрибуты" содержит девять строк с A1, A2 и A3, повторяющихся три раза. Столбец "Значения" содержит значения версии 1 до версии 9 сверху вниз. В сводных столбцах таблица справа содержит пустой столбец и строки, значения Атрибутов A1, A2 и A3 в виде заголовков столбцов, с столбцом A1, содержащим значения V1, V4 и V7, столбец A2, содержащий значения V2, V5 и V8, а также столбец A3, содержащий значения V3, V6 и V9.

Представьте таблицу, как в следующем изображении.

Исходная таблица столбца сводной таблицы.

Таблица, содержащая столбец "Страна", в качестве типа текстовых данных, столбец Date, заданный в качестве типа данных данных, и столбец "Значение", заданный в качестве типа данных Кто числа. Столбец Country содержит США в первых трех строках, Канаде в следующих трех строках и Панаме в последних трех строках. Столбец "Дата" содержит дату в первых, четвертых и седьмой строках, вторую дату во второй, пятой и восьмой строках, а третью — в третьей, шестой и девятой строках.

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

Итоговая таблица столбца сводной таблицы.

Таблица, содержащая столбец "Страна" в типе текстовых данных, а также первые, второй и третий столбцы дат, заданные в качестве типа данных Кто числа. Столбец "Страна" содержит Канаду в строке 1, Панама в строке 2 и США в строке 3.

Примечание

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

Сводка столбца

  1. Выберите столбец, который требуется свести. В этом примере выберите столбец Date .

  2. На вкладке "Преобразование " группы "Любой столбец " выберите столбец Pivot.

    Снимок экрана: вкладка

  3. В диалоговом окне "Сводная колонка" в списке столбцов "Значение" выберите "Значение".

    Снимок экрана: диалоговое окно

    По умолчанию Power Query пытается выполнить сумму в качестве агрегирования, но вы можете выбрать параметр "Дополнительно ", чтобы просмотреть другие доступные агрегаты.

    Снимок экрана: диалоговое окно

Доступные параметры:

  • Не агрегировать
  • Count (all)
  • Число (не пустое)
  • Минимум
  • Максимум
  • Median
  • Sum
  • Среднее значение

Сводные столбцы, которые нельзя агрегировать

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

Столбец сводной таблицы не агрегирование.

Таблица со столбцом Country, содержащим США в первых трех строках, Канада в следующих трех строках и Панама в последних трех строках. Столбец "Позиция" содержит первое место в первой, четвертой и седьмой строках, второе место во втором, пятом и восьмом строках, а третий — в третьем, шестом и девятом строках.

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

Снимок экрана: таблица с выбранным столбцом

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

Снимок экрана: диалоговое окно столбца сводной таблицы с функцией агрегатного значения, заданной как

Результат этой операции дает результат, показанный на следующем рисунке.

Столбцы сводной таблицы не агрегируют результат.

Таблица, содержащая столбцы Country, First Place, Second Place и Third Place, с столбцом Country, содержащим Канаду в строке 1, Панама в строке 2 и США в строке 3.

Ошибки при использовании параметра "Не агрегировать"

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

Столбец сводной таблицы не агрегирует пример начальной таблицы ошибок.

Таблица со столбцами "Страна", "Дата" и "Значение". Столбец Country содержит США в первых трех строках, Канаде в следующих трех строках и Панаме в последних трех строках. Столбец Date содержит одну дату во всех строках. Столбец значений содержит различные целые числа от 20 до 785.

Вы хотите свести эту таблицу с помощью столбца Date , и вы хотите использовать значения из столбца "Значение ". Так как эта сводная таблица содержит только значения "Страна " в строках и "Даты " в качестве столбцов, вы получаете ошибку для каждого значения ячейки, так как для каждого сочетания стран и даты существует несколько строк. Результат этой операции столбца сводной таблицы дает результаты, показанные на следующем рисунке.

Столбец сводной таблицы не агрегирует пример конечной таблицы ошибок.

Обратите внимание, что сообщение об ошибке "Expression.Error: в перечислении было слишком много элементов, чтобы завершить операцию". Эта ошибка возникает, так как операция "Не агрегировать " ожидает только одно значение для сочетания стран и дат.