A volte vogliamo copiare celle alternate da un intervallo o ogni terzo valore o ogni quarto valore e così via. In tutti questi casi, la copia manuale sarà un compito estenuante. Allora qual è la soluzione? Come si copia il valore da ogni ennesima riga? C'è una formula per questo?
Si C'è. Possiamo usare la funzione OFFSET di excel per ottenere valori dalle n-esime righe. Come? Vediamolo.
=OFFSET(First_Cell_in_List,(RIGA(A1)*n)-1,0)
First_Cell_in_Range: è il riferimento assoluto della prima cella nell'intervallo di elenco da cui si desidera copiare. Ad esempio, se la tua lista è in A5:A100, allora First_Cell_in_Range è $A$5. Non includere l'intestazione di colonna.
n: è di un numero di cui otterrai un valore multiplo. Ad esempio, se si desidera ottenere un valore ogni 2 dall'elenco, N è 2. Per ogni valore 3, è 3 e così via.
RIGA(A1): per ottenere multipli di N.
Ora vediamo un esempio, e poi ti spiego come funziona.
Esempio Copia ogni secondo valore da un elenco in Excel
Qui abbiamo un elenco di elementi. Dobbiamo copiare ogni secondo valore dall'elenco in un nuovo intervallo.
Allora, come lo facciamo?
Poiché abbiamo 11 elementi nell'elenco, otterremo cinque elementi alternativi (2, 4, 5, 6, 10).
Scrivi la formula seguente in E4 e copia le celle.
=OFFSET($B$4,(RIGA(A1)*2)-1,0)
Qui $ B $ 4 è la prima cella nell'intervallo dell'elenco e poiché vogliamo copiare ogni secondo valore (alternato) dall'elenco, abbiamo il nostro N come 2.
Se vuoi copiare ogni terzo elemento, usa questa formula.
=OFFSET($B$4,(RIGA(A1)*3)-1,0)
Per ogni 4:
=OFFSET($B$4,(RIGA(A1)*4)-1,0)
E così via. Puoi copiare qualsiasi ennesimo valore da a usando questa formula excel.
Come funziona
La formula svolge questo compito utilizzando due funzioni excel, OFFSET e ROW.
La funzione OFFSET restituisce il valore dall'offset di riga e colonna da una determinata cella.
La funzione RIGA restituisce il numero di riga del riferimento specificato.
Partendo dall'interno, RIGA(A1) restituirà 1. Poiché A1 appartiene alla prima riga.
(RIGA(A1)*2 diventerà 2.
(RIGA(A1)*2)-1, diventerà (1*2)-1=1.
Quando copiamo la formula, il riferimento in ROW cambierà come A2, A3, A4 e così via. Per A2, otterremo il valore finale (2*2)-1=3. Per A3, otterremo (3*3)-1=5 e così via.
Ora, la nostra formula in E4 è stata risolta in =OFFSET($B$4,1,0). Ciò restituirà il valore 1 riga sotto da B4, che è il secondo valore da B4.
In E4, la formula sarà =OFFSET($B$4,3,0), che è il quarto valore di B4. Allo stesso modo, otteniamo tutti i costi alternati.
Ora sai come copiare ogni ennesimo valore dall'elenco in Excel. Questa è la tecnica che conosco. Se conosci altri trucchi per copiare l'ennesima cella da un intervallo, sentiti libero di condividere nella sezione commenti qui sotto. In caso di domande su questa o qualsiasi altra funzione di Excel 2019, 2016, 2013 e 2010, utilizzare la sezione commenti.
Come recuperare l'ultimo prezzo in Excel
Recupero del primo valore in un elenco maggiore/minore di un valore specificato
Come recuperare l'intera riga di un valore abbinato
Articoli popolari:
La funzione CERCA.VERT in Excel
CONTA.SE in Excel 2016
Come utilizzare la funzione SOMMA.SE in Excel