Tornando all'inizio dei dati di origine in excel

C'è un modo formulaico per tornare alla parte superiore del file di origine una volta che la cella di riferimento ha raggiunto la fine (non VBA)? Per esempio:

AB 1 001 63001210100 2 001 63001210101 3 001 63001210102 4 001 63001210103 --end of source file 5 002 --should be 63001210100 again 6 002 7 002 8 002 

Sto praticamente tirando da un altro foglio di calcolo (il file di origine) e i dati sono giunti alla fine. Tuttavia, ho bisogno di ripetere i dati sin dall'inizio ora che la colonna A è cambiata. La colonna A è in formato text a proposito.

So che posso andare per andare a B5 e riferirlo a B1, ma questo rende le cose brutte perché il file di origine può cambiare in size. C'è una formula che può estrarre dai dati di origine alla fine, quindi tornare indietro all'inizio in base a se un valore in una cella differente cambia?

Il meglio che posso fare è usare una combinazione di INDEX , ROW e MOD invece di OFFSET . Probabilmente questo functionrà meglio della mia prima risposta.

 =INDEX(A:A,MOD(ROW()-1,COUNTA(A:A))+1,0) 

Prima risposta

Per fare questo con una formula, è ansible utilizzare una combinazione di CountA , Offset , Row e Mod

Questa image mostra un esempio di valori ripetuti dalla sorgente in A1: A6

ripetendo i valori

La formula in B1 è

 =MOD(ROW()-1,COUNTA(A:A)) 

Fa parte della function utilizzata in C1 (la parte che corre la row)

 =OFFSET($A$1,MOD(ROW()-1,COUNTA(A:A)),0) 

Puoi mantenere una colonna separata con un contatore (ad esempio se la tua colonna contatore è C, C1 sarà 1, C2 sarà 2 e così via)

Quindi non fai riferimento ai tuoi dati di origine copiando l'intera colonna (ad esempio =Source!B1 , Source!B2 , …, o qualsiasi formula che stai usando ora) ma utilizzando ( =INDIRECT("Source!B"&C1) , =INDIRECT("Source!B"&C2) , …)

Indiretto prende un riferimento di cella come string e lo converte in un riferimento corretto di excel. Se lasciate che il tuo contatore funzioni da 1 a max_row_number, avrai l'esatto comportmento che hai ora – viene copiata l'intera colonna di origine.

Ma la torsione è che ovviamente puoi influenzare il tuo contatore con una formula, come

(in C2) = IF(A2<>A1,1,C1+1)

In questo modo il contatore ricomincerà da 1 each volta che cambia il valore della colonna A. Naturalmente puoi modificarlo ulteriormente.