Транспонировать таблицу в матричном стиле в 3 столбца в Excel

Question

У меня есть таблица в виде матрицы в Excel, где B1:Z1 - заголовки столбцов, а A2:A99 - заголовки строк. Я хотел бы преобразовать эту таблицу в таблицу из 3 столбцов (заголовок столбца, заголовок строки, значение ячейки). Не имеет значения, в каком порядке находится новая таблица.

    A   B   C   D            A   B   C            A   B   C   
 1      H1  H2  H3        1  H1  R1  V1        1  H1  R1  V1
 2   R1 V1  V2  V3   =>   2  H1  R2  V4   or   2  H2  R1  V2
 3   R2 V4  V5  V6        3  H1  R3  V7        3  H3  R1  V3
 4   R3 V7  V8  V9        4  H2  R1  V2        4  H1  R2  V4
                          5  H2  R2  V5        5  H2  R2  V5
                          6  H2  R3  V8        6  H3  R2  V6
                          7  H3  R1  V3        7  H1  R3  V7
                          8  H3  R2  V6        8  H2  R3  V8
                          9  H3  R3  V9        9  H3  R3  V8

Я играл с функцией OFFSET, чтобы создать весь стол, но я чувствую, что требуется комбинация TRANSPOSE и V/HLOOKUP.

Спасибо

РЕДАКТИРОВАТЬ

Мне удалось придумать правильные формулы. Если данные в Sheet1, как в моем примере выше, формулы идут в Sheet2:

[A1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99), OFFSET(Sheet1!$A$1,0,IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"")

[B1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),0),"")

[C1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"")

Формулы ограничены значениями B1:Z1 для заголовков и A2:A99 для строк (при необходимости их можно увеличить до максимальных значений). Формула COUNTA () возвращает количество ячеек, которые действительно имеют значения, что ограничивает число строк, возвращаемых заголовками * строками. В противном случае формулы могли бы продолжаться бесконечно из-за функции MOD.

Answer 1

Мне нравится этот вопрос :)

попробуйте эти функции. вставьте их в h0, r0, v0 и перетащите их вниз

   h   r   v
0 (a) (b) (c)
1 | | | | | |
2 \ / \ / \ /
3  v   v   v

где:
h = метка заголовка
r = метка строки
v = метка значения

(a) = OFFSET($ A $ 1, INT(H2/99)+1, MOD(H2,99)+1,1,1)
(b) = СМЕЩЕНИЕ ($ A $ 1, INT(H2/99)+1,0,1,1)
(c) = СМЕЩЕНИЕ ($ A $ 1,0, MOD(H2,99)+1,1,)

где H2 - столбец со значениями: 0,1,2,3 ...

Answer 2

Несколько недель назад я увидел несколько более обобщенную версию решения, предоставленного @Dick Kusleika в StackOverflow: преобразовать строку с столбцами данных в столбец с несколькими строками в Excel 2007. Кажется, он не имеет ссылок на отдельные строки (вместо этого используется ROW() ), но все же требует, чтобы количество столбцов было жестко закодировано.