In questo articolo impareremo come utilizzare la funzione OFFSET in Excel.
Cos'è Funzione OFFSET?
La funzione di offset è una funzione di recupero delle informazioni. Se si dispone di una tabella enorme contenente determinati dati e si desidera recuperare i dati da essa, il modo migliore per farlo è inserire una formula di offset per la tabella in quel foglio di lavoro o in un altro foglio nella stessa cartella di lavoro. Pertanto, una volta creata la tabella di recupero degli offset, devi semplicemente inserire i dati, ad esempio "mese" e otterrai le "vendite" o "reddito" di quel mese da quella tabella.
Passare attraverso la tabella è un'altra opzione, ma considereresti l'opzione se disponi di fogli e fogli di dati contenenti tutti una tabella di dati di 1000 colonne? Qui è dove l'offset viene combinato con altre funzioni.
Il concetto di tabella pivot deriva da offset. Una tabella pivot è una tabella generale e da quella specifica combinazione di dati e grafici si ottiene come e quando richiesto. Allo stesso modo possono essere create anche tabelle dinamiche. Per queste tabelle dovrai creare la formula offset excel
Sintassi di OFFSET
| =OFFSET(riferimento,righe, colonne, [altezza], [larghezza]) |
righe - chiedi a excel di spostare il numero di righe per ottenere le informazioni. In questo caso deve muoversi tutto intorno al tavolo e quindi lasciare semplicemente uno spazio vuoto indicato da una virgola (,). Altrimenti, per spostare una colonna in avanti dare il valore 1 e per spostare una colonna prima dare un valore -1.
colonne - questo serve per guidare la funzione al numero di colonne. Il sistema di valori è lo stesso della riga.
Altezza - l'altezza del tavolo, in questo caso A11.
Larghezza - larghezza del tavolo, in questo caso D11.
Dopo aver impostato la funzione di offset, è importante eseguire una prova per eliminare eventuali errori.
Prendiamo una tabella che contiene Nome, ID e-mail, data di nascita ed età. La tabella inizia dalla cella A1, contenente l'intestazione "Nome". I dati vengono eseguiti per dire A11. E le file corrono fino a D1. L'intera tabella esegue A1:D11. Vuoi un sistema di recupero che ti ottenga il nome quando inserisci l'ID e-mail o l'ID e-mail quando inserisci il nome insieme ad altri dettagli. Crei una formula soddisfacendo i 5 argomenti.
Il punto di riferimento è la cella da cui deve iniziare la ricerca. In questo caso dovrebbe essere A2. Questo è lo standard generale.
Esempio:
Ad esempio, si fornisce il riferimento di B4 e la riga di offset 0 e la colonna di offset 1, quindi verrà restituito il valore da C4. Confuso? Facciamo qualche altro esempio. L'ultimo esempio della funzione OFFSET dice come SOMMA dinamicamente.
Funzione OFFSET per ottenere valore da destra e sinistra di una cella
Abbiamo questa tabella.
Ora, se voglio ottenere il valore da 2 celle direttamente a B2 (che significa D2). Scriverò questa formula OFFSET:
| =OFFSET(B2,0,2) |
La funzione OFFSET sposterà 2 celle a destra della cella B4 e restituirà il suo valore. Vedi immagine sotto
Se voglio ottenere il valore da 1 cella rimasta a B2 (che significa A2). Scriverò questa formula OFFSET:
| =OFFSET(B2,0,-1) |
Il segno meno (-) indica l'offset per il movimento invertito.
Funzione OFFSET per ottenere valore dal basso e dall'alto di una cella
Quindi di nuovo nella tabella sopra, se voglio ottenere il valore da 2 celle a B3 (che significa B5). Scriverò questa formula OFFSET:
| =OFFSET(B2,2,0) |
Se voglio ottenere il valore da 1 cella rimasta a B2 (che significa A2). Scriverò questa formula OFFSET:
| =OFFSET(B2,-2,0) |
- Suggerimento professionale: Tratta un OFFSET come un puntatore grafico dove Riferimento è l'origine e riga e colonna sono gli assi x e y.
Funzione OFFSET per sommare dinamicamente l'intervallo
Vediamo questo esempio.
Nella tabella sopra Riga totale alla fine della tabella. Nel tempo aggiungerai righe a questa tabella. Quindi dovrai modificare l'intervallo di somma nella formula. Qui possiamo prendere l'aiuto della funzione OFFSET per sommare dinamicamente. Attualmente nella cella C11 abbiamo =SOMMA(C2:C10). Sostituiscilo con la formula seguente.
| =SOMMA(C2:OFFSET(C11,-1,0)) |
Questa formula prende solo la prima riga di dati e quindi somma l'intervallo sopra la riga totale.
Offset per ottenere l'array
La funzione OFFSET ha due argomenti opzionali, [height] e [width]. Non darli per scontati. Quando la funzione OFFSET viene fornita con gli argomenti [altezza] e [larghezza], restituisce un array bidimensionale. Se solo uno di essi viene passato come argomento, restituisce un array dimensionale di valori.
Se digiti questa formula in qualsiasi cella:
| =SOMMA(OFFSET(C1,1,0,9,3)) |
Somma i valori nell'intervallo a partire da C2 a 9 righe sotto e 3 colonne a destra.
OFFSET(C1,1,0,9,3) : Questo si tradurrà in {8,8,7;6,1,2;8,5,8;5,1,5;8,3,5;8,3,9;8,9,2;3,5 ,4;6,6,5}.
Quindi, in conclusione, l'offset è una funzione molto utile di excel che può aiutarti a risolvere molti thread strozzati. Fammi sapere come usi la funzione OFFSET nella tua formula e dove ti ha aiutato di più.
La funzione di ricerca è uguale all'offset ma utilizza funzioni come Match, Counta e IF per lavorare su una tabella dinamica.
L'uso dell'offset in Excel richiede abilità e una buona conoscenza delle funzioni di Excel e del modo in cui funzionano insieme. Tirarlo fuori senza alcun errore è l'ostacolo più grande.
Ecco tutte le note di osservazione sull'uso della formula.
Appunti:
- Questa formula funziona sia con testo che con numeri.
- Esistono cinque argomenti dell'offset che devono essere soddisfatti per ottenere informazioni prive di errori. Se si scrive una formula errata, si verifica un errore Ref.
Spero che tu abbia capito cos'è la funzione offset di Excel e come utilizzarla in Excel. Esplora altri articoli sulla ricerca in Excel e abbina i valori utilizzando le formule qui. Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica.
Funzione CERCA.VERT per calcolare il voto in Excel : Per calcolare i voti IF e IFS non sono le uniche funzioni che puoi utilizzare. Il VLOOKUP è più efficiente e dinamico per tali calcoli condizionali. Per calcolare i voti usando VLOOKUP possiamo usare questa formula.
17 cose su Excel VLOOKUP : CERCA.VERT è più comunemente usato per recuperare i valori corrispondenti, ma CERCA.VERT può fare molto di più. Ecco 17 cose su CERCA.VERT che dovresti sapere per usare in modo efficace.
CERCA il primo testo da un elenco in Excel : la funzione CERCA.VERT funziona correttamente con i caratteri jolly. Possiamo usarlo per estrarre il primo valore di testo da un determinato elenco in Excel. Ecco la formula generica.
CERCA data con ultimo valore nell'elenco : Per recuperare la data che contiene l'ultimo valore utilizziamo la funzione CERCA. Questa funzione verifica la cella che contiene l'ultimo valore in un vettore e quindi utilizza tale riferimento per restituire la data.
Come recuperare l'ultimo prezzo in Excel : È comune aggiornare i prezzi in qualsiasi attività commerciale ed è necessario utilizzare i prezzi più recenti per qualsiasi acquisto o vendita. Per recuperare l'ultimo prezzo da un listino in Excel utilizziamo la funzione CERCA. La funzione CERCA recupera il prezzo più recente.
Articoli popolari:
50 scorciatoie Excel per aumentare la produttività : Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
Come usare la funzione SE in Excel : l'istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è VERA o restituisce un altro valore specifico se FALSO.
Come utilizzare la funzione CERCA.VERT in Excel : questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.
