Как получить для каждой непустой записи данных в диапазоне вывод в трех столбцах, содержащих саму запись, заголовок столбца и заголовок строки
Я начинаю с диапазона данных (например, формулы, объединяющей заголовок столбца/строки и/или «текст» и/или пустые элементы).
первая строка над диапазоном — это заголовок столбца (число, буква или пустой).
первый столбец слева от данных представляет собой заголовок строки (числа по убыванию, буквы или пустые).
Все данные, заголовки столбцов/строк уникальны, без повторов (кроме пробелов).
В качестве вывода я хотел бы получить 3 столбца 1 | 2 | 3 данные | заголовок столбца | заголовок строки
эти данные представляют собой только непустые записи (пример формулы if (TRUE/FALSE,B1&A3,""). Я добавляю это, потому что кажется, что на вывод формулы нельзя всегда ссылаться? если B1 и A3 в качестве ввода данных, то он должен принять его в list if "" тогда он должен оценить ячейку как пустую и исключить ее из списка.
- Я создал вспомогательный столбец, содержащий все данные
- попытался использовать Filter(), чтобы удалить пустые записи в столбце. привел к #РАЗЛИВУ
- Пытался получить столбец заголовка и преуспел с помощью CONCAT (функция REPT).
- пытался получить заголовок строки, но с помощью INDEX MATCH не удалось
С помощью предложения TOCOL мне удалось использовать несколько вспомогательных столбцов: 1-я помощь: =TOCOL(NodeData,0,TRUE) получение всех меток (с 3 вместо 0 сразу у меня не сработало. LABEL без 0: =FILTER(rng,rng<) >0) 2-я помощь:=CONCAT(REPT(HelperXData,N(AJ11=NodeData))), а затем найдите помощника в столбце =HLOOKUP(AI11,colheaders,5) (это XHeader. Затем заголовок строки Y с =ИНДЕКС(YData,MATCH(AJ11,OFFSET(YData,0, MATCH(AI11,HelperXData,0)),0))
Ответы 2
Вы можете сделать это, используя формулу, VBA или Power Query. Подобные вопросы уже задавались много раз:
Отменить поворот матрицы/сводной таблицы Excel?
Как «отменить поворот» или «обратный поворот» в Excel?
Выберите свою таблицу, в меню «Данные» выберите «Из таблицы/диапазона», загрузите ее в Power Query, выберите столбцы A, B, C, D, щелкните их правой кнопкой мыши и выберите «Отменить сведение столбцов».
Автоматически сгенерированный M-код выглядит следующим образом:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}})
in
#"Sorted Rows"
Если вы хотите использовать формулу, вы можете использовать общую формулу, например (полученную по одной из ссылок выше), и настроить ее по своему вкусу и требованиям.
=LET(dataRng,Table1[[A]:[D]], rowAxis,Table1[Column1], colAxis,Table1[[#Headers],[A]:[D]],
iCol,COLUMN(INDEX(rowAxis,1,1)), amountCol,TOCOL(dataRng), totalCells,COUNTA(amountCol),
HSTACK(
INDEX(rowAxis,
INT(SEQUENCE(totalCells,1,0,1)/COLUMNS(dataRng))+1,
BYCOL(INDEX(rowAxis,1,), LAMBDA(aCol,COLUMN(aCol) -iCol +1))),
INDEX(colAxis,
SEQUENCE(1,ROWS(colAxis),1,1),
MOD(SEQUENCE(totalCells,1,0,1),COLUMNS(dataRng))+1),
amountCol))
Спасибо, но проблема в том, что мои заголовки также являются формулами и заменяются обычными данными, когда я создаю таблицу. Вот почему ответ TOCOL меня выручил.
Конкретное решение с использованием параметра игнорирования TOCOLs:
1 - Ignore blanks
2 - Ignore errors
=LET(
data, B2:E7,
col_header, A1:E1,
row_header, A1:A7,
result, VSTACK(
{"Label", "Col", "Row"},
HSTACK(
TOCOL(data, 1, TRUE),
TOCOL(IF(ISBLANK(data), NA(), INDEX(col_header, 1, COLUMN(data))), 2, TRUE),
TOCOL(IF(ISBLANK(data), NA(), INDEX(row_header, ROW(data), 1)), 2, TRUE)
)
),
result
)
Можете ли вы показать нам формулы, которые вы пробовали?