Преобразование таблицы для отражения имен столбцов в значениях строк
В следующей таблице есть рейтинги брендов (значения строк) по клиентам:
Я хотел бы преобразовать эту таблицу, чтобы представить рейтинги в виде столбцов, а бренды — в виде строк:
Всего у меня 116 клиентов, 10 брендов и рейтинги от 1 до 5. Я пробовал отменять и поворачивать таблицы, но значения в сводных таблицах показывают количество, а не названия брендов.
Как я могу преобразить свой стол?
Ответы 3
С Power Pivot это довольно просто:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
UnpivotedData = Table.UnpivotOtherColumns(Source, {"CustID"}, "Brand", "Rank"),
FilteredData = Table.SelectRows(UnpivotedData, each [Rank] <> null),
ChangeType = Table.TransformColumnTypes(FilteredData,{{"Rank", type text}}),
PivotedData = Table.Pivot(ChangeType, List.Distinct(ChangeType[Rank]), "Rank", "Brand"),
SortedColumnNames = List.Sort(List.RemoveFirstN(Table.ColumnNames(PivotedData), 1), (x) => Number.FromText(x)),
SortedColumns = Table.ReorderColumns(PivotedData, List.Combine({{"CustID"}, SortedColumnNames}))
in
SortedColumns
Хотя у вас уже есть решение с использованием Power Query, вот несколько методов с использованием формул Excel:
• Формула, используемая в ячейке A6
=LET(
_Data, Ranktbl[#All],
_Rank, DROP(_Data,1,1),
_RankN, SORT(UNIQUE(TOROW(_Rank,1),1),,,1),
_Output, MAKEARRAY(ROWS(_Data)-1,COLUMNS(_RankN),LAMBDA(r,c,
INDEX(TOCOL(IF(INDEX(_RankN,c)=_Rank,
DROP(TAKE(_Data,1),,1),NA()),2),r,))),
HSTACK(TAKE(_Data,,1),VSTACK(_RankN,_Output)))
Также, если применимо, можно использовать функцию PIVOTBY():
=LET(
_Data, Ranktbl[#All],
_Fx, LAMBDA(α,δ, TOCOL(IF(α<>"",δ,NA()),2)),
_Rank, DROP(_Data,1,1),
PIVOTBY(_Fx(_Rank,DROP(TAKE(_Data,,1),1)),
_Fx(_Rank,_Rank),
_Fx(_Rank,DROP(TAKE(_Data,1),,1)),
SINGLE,,0,,0))
Или, чтобы записать каждый шаг, чтобы его было легче читать:
=LET(
_Data, Ranktbl[#All],
_Fx, LAMBDA(α,δ, TOCOL(IF(α<>"",δ,NA()),2)),
_Rank, DROP(_Data,1,1),
_Customer, _Fx(_Rank,DROP(TAKE(_Data,,1),1)),
_Brand, _Fx(_Rank,DROP(TAKE(_Data,1),,1)),
_RankN, _Fx(_Rank,_Rank),
PIVOTBY(_Customer,_RankN,_Brand,SINGLE,,0,,0))
@BT, что ты имеешь в виду, говоря «не работает». Все приведенные формулы применяются в Excel и определенно работают. Когда вы говорите, что не работаете, следует публиковать соответствующие скриншоты, чтобы подтвердить свое обоснование, иначе это теряет смысл!
Извините за такую расплывчатость, я не могу опубликовать здесь снимок экрана, но во всплывающем окне появляется ошибка: «Первый аргумент LET должен быть допустимым именем»
@BT, братан, это вообще не проблема, причина в том, что, возможно, у вас есть какие-то определенные имена или имена листов, поскольку _Data просто измените переменные внутри LET() по своему усмотрению. Формула должна работать!
Сделаю, спасибо! Итак, _Data — это имя листа в вашей формуле?
@BT, есть ли у вас какие-либо определенные имена или имена листов в качестве какой-либо из этих определенных переменных в LET()? Нет, я задаю вам вопрос, я не знаю, какие у вас определенные имена или имена листов.
Другой вариант формулы, основанный на ваших примерных данных и описании, если количество рангов в строке всегда равно 5 (не больше и не меньше):
=LET(
tbl; Table1[#All];
rank; DROP(tbl; 1; 1);
test; ISNUMBER(rank);
unpivot; SORTBY(
TOCOL(IFS(test; DROP(TAKE(tbl; 1);; 1)); 2);
TOCOL(IFS(test; SEQUENCE(ROWS(rank))); 2); 1;
TOCOL(IFS(test; rank); 2); 1);
HSTACK(TAKE(tbl;; 1); VSTACK(SEQUENCE(1; 5); WRAPROWS(unpivot; 5)))
)
Однако, если количество рейтингов в строке не фиксировано, используйте следующую модифицированную версию:
=LET(
tbl; Table1[#All];
rank; DROP(tbl; 1; 1);
n; COLUMNS(rank);
cols; SEQUENCE(; n);
test; ISNUMBER(rank);
unpivot; SORTBY(
TOCOL(IF(test; DROP(TAKE(tbl; 1);; 1); ""));
TOCOL(IF(cols; SEQUENCE(ROWS(rank)))); 1;
TOCOL(IF(test; rank)); 1);
pivot; HSTACK(TAKE(DROP(tbl; 1);; 1); WRAPROWS(unpivot; n));
FILTER(VSTACK(HSTACK("CustID"; cols); pivot); BYCOL(pivot; LAMBDA(col; SUM(LEN(col)))))
)
При необходимости откорректируйте определение tbl, но обязательно включите строку заголовка таблицы или диапазона.
Примечание. В приведенных выше формулах я использовал разделители точка с запятой из-за вашего обсуждения в комментариях... Ошибка «Первый аргумент LET должен быть допустимым именем» может возникнуть при копировании/вставке формул, найденных в Интернете, если автор использует другой системный разделитель, чем у вас. Если разделителем по умолчанию не является точка с запятой, сначала вставьте формулы в Блокнот и используйте «Правка» > «Заменить...», чтобы заменить точки с запятой, например, запятыми (или наоборот).
Спасибо, однако пусть с моими формулами не получилось((