Сводная таблица Excel: показывать значения вместо сводок

Question

У меня есть таблица данных, как это:

ID  |Name   |Brand  |Model  |License
------------------------------------
1   |Foo    |KOM    |830 AC |A
1   |Foo    |KOM    |930 E  |B
1   |Foo    |CAT    |980 H  |B
2   |Bar    |KOM    |930 E  |A
2   |Bar    |CAT    |980 H  |A
3   |Baz    |P&H    |280 XB |B

Идентификатор и Имя связаны друг с другом (идентификатор 1 всегда является Foo), также никогда не бывает более одной лицензии для пары Имя и Модель)

Я хочу использовать сводную таблицу, чтобы превратить ее в это:

    |       |CAT    |KOM            |P&H
ID  |Name   |980 H  |830 AC |930 E  |280 XB
-------------------------------------------
1   |Foo    |B      |A      |B      |
2   |Bar    |A      |       |A      |
3   |Baz    |       |       |       |B

Это насколько я получил:

Но:

• Идентификаторы и имена находятся в разных строках
• Показывает количество (всегда 1) лицензий вместо показа значений

Есть ли способ заставить его работать так, как я хочу?

Answer 1

К сожалению, сводные таблицы не могут вернуть текст. Они объединяются.

Тем не менее, есть обходные пути. Самое простое включает колонку "Помощник" и VLOOKUP. Смотрите мой скриншот ниже.

Сначала вставьте столбец "Помощник" слева от столбца "Лицензия", в котором объединены идентификатор, имя, марка и модель.

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

Наконец, используйте VLOOKUP, как указано. Обратите особое внимание на размещения знака $ в формуле, чтобы сохранить абсолютные ссылки. На скриншоте показана формула в ячейке S13; скопируйте это снова и вниз по мере необходимости.

(В макете сводной таблицы вам также необходимо заполнить пробелы в строке "Бренд". На приведенном ниже снимке экрана в ячейке U11 есть формула: = T11).

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

Существуют и другие методы, включающие таблицы, INDEX+MATCH и формулы массивов.

Answer 2

Хорошо, как вы можете видеть из этого вопроса, вы можете переставить 1 столбец значений в другой, выполнив следующие шаги:

• Откройте таблицу в редакторе Power Query
  • превратить данные в таблицу
  • выберите стол
  • выберите данные -> из таблицы / диапазона
• Используйте Power Query, чтобы сделать транспонирование
  • выберите столбцы Subj и Course
  • Выберите вкладку Transform:
  • Выберите Pivot Column, вам, возможно, придется навести курсор, чтобы найти его
  • Столбец значений = Выбор курса
  • дополнительные параметры -> не агрегировать
• Верните его в свою таблицу
  • Вкладка «Главная» (все еще в Power Query)
  • Получить данные -> Закрыть и загрузить ..

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