Trasforma la tabella in stile matrix a 3 colonne in Excel

Ho un tavolo di matrix in excel where B1: Z1 sono intestazioni di colonna e A2: A99 sono intestazioni di row. Vorrei convertire questa tabella in una tabella a tre colonne (intestazione di colonna, intestazione di row, valore della cella). Non import in quale ordine il nuovo tavolo è.

ABCDABCABC 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 

Ho giocato con la function OFFSET per creare l'integer tavolo ma mi sembra una combinazione di TRANSPOSE e V / HLOOKUP.

Grazie

EDIT

Sono riuscito a trovare le formule corrette. Se i dati sono in Sheet1 come nel mio esempio precedente, le formule vanno in 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)))),"") 

Le formule sono limitate a B1: Z1 per le intestazioni e A2: A99 per le righe (queste possono essere aumentate ai loro massimi se necessario). La formula COUNTA () restituisce il numero di celle che hanno effettivamente valori, il che limita il numero di righe restituite alle righe delle voci *. Altrimenti le formule potrebbero andare avanti per l'infinito a causa della function MOD.

Mi piace questa domanda 🙂

provare queste funzioni. inserirli in h0, r0, v0 e trascinarli

  hrv 0 (a) (b) (c) 1 | | | | | | 2 \ / \ / \ / 3 vvv 

where:
h = etichetta di intestazione
r = etichetta di row
v = etichetta di valore

(a) = OFFSET ($ A $ 1, INT (H2 / 99) + 1, MOD (H2,99) +1,1,1)
(b) = OFFSET ($ A $ 1, INT (H2 / 99) +1,0,1,1)
(c) = OFFSET ($ A $ 1,0, MOD (H2,99) +1,1,)

where H2 è una colonna con valori: 0,1,2,3 …

Ho visto una versione leggermente più generalizzata della soluzione fornita da @Dick Kusleika a StackOverflow poche settimane fa: Convertire la row con colonne di dati nella colonna con più righe in Excel 2007 . Sembra non avere riferimenti a singole righe (usando ROW() invece) ma richiede ancora che il numero di colonne sia codificato.