Qual è Excel Offset Funzione e Come si usa
Cos’è la funzione OFFSET?
La funzione 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 un altro foglio nella stessa cartella di lavoro. Pertanto, una volta creata la tabella di recupero offset, devi semplicemente inserire i dati per esempio “mese” e otterrai le “vendite” o “entrate” di quel mese da quella tabella.
L’analisi della tabella è un’altra opzione, ma considereresti l’opzione se avessi fogli e fogli di dati contenenti tutti una tabella di dati a 1000 colonne? Qui è dove l’offset è combinato con altre funzioni.
Il concetto di tabella pivot deriva dall’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 di offset excel
Sintassi di OFFSET
|
=OFFSET(reference,rows, columns, [height], [width]) |
Righe: chiedi a Excel di spostare il numero di righe per ottenere le informazioni. In questo caso deve spostarsi intorno al tavolo e quindi lasciare semplicemente uno spazio vuoto contrassegnato 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: serve per guidare la funzione al numero di colonne. Il sistema di valori è lo stesso di quello per la 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 vanno per dire A11. E le file corrono fino a D1. L’intera tabella corre A1: D11. Desideri un sistema di recupero che ti dia 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 dovrebbe iniziare la ricerca. In questo caso dovrebbe essere A2. Questo è lo standard generale.
Esempio:
Ad esempio, si fornisce un riferimento a B4 e offset riga 0 e offset colonna 1, verrà restituito il valore da C4. Confuso? Facciamo altri esempi. L’ultimo esempio di funzione OFFSET spiega come SOMMA dinamicamente.
Funzione OFFSET per ottenere valore dalla destra e dalla 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 l’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 spostarsi al contrario.
Funzione OFFSET per ottenere il valore dal basso e dal sopra 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 avanzato: * Tratta un OFFSET come un puntatore del grafico in cui Riferimento è l’origine e riga e colonna sono gli assi xey.
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 utilizzare la funzione OFFSET per sommare dinamicamente. Attualmente nella cella C11 abbiamo = SUM (C2: C10). Sostituiscilo con la formula seguente.
|
=SUM(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 la funzione OFFSET di array ha due argomenti opzionali, [altezza] e [larghezza]. Non darli per scontati. Quando viene fornita la funzione OFFSET [altezza] e [larghezza] argomenti, restituisce un array bidimensionale. Se solo uno di essi viene passato come argomento, restituisce un array dimensionale di valori.
Se digiti questa formula in una cella qualsiasi:
|
=SUM(OFFSET(C1,1,0,9,3)) |
Sommerà 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, offset è una funzione molto utile di Excel che può aiutarti a risolvere molti thread strangolati. Fammi sapere come usi la funzione OFFSET nella tua formula e dove ti ha aiutato di più.
La funzione di ricerca è la stessa dell’offset ma utilizza funzioni come Match, Counta e IF per lavorare su una tabella dinamica.
L’utilizzo dell’offset in Excel richiede abilità e una buona conoscenza delle funzioni di Excels e di come lavorano insieme. Tirarlo fuori senza errori è il più grande ostacolo.
Ecco tutte le note osservative riguardanti l’utilizzo della formula.
Note:
-
Questa formula funziona sia con testo che con numeri.
-
Ci sono cinque argomenti dell’offset che devono essere soddisfatti per ottenere informazioni prive di errori. Se scrivi una formula errata, si verifica un errore Ref.
Spero che tu abbia capito cos’è la funzione offset di Excel e come usarla in Excel. Esplora altri articoli sulla ricerca in Excel e sulla corrispondenza dei valori utilizzando le formule qui. Se ti sono piaciuti i nostri blog, condividilo con i tuoi amici su Facebook. Puoi anche seguirci su Twitter e Facebook. Ci piacerebbe sentire la tua opinione, facci sapere come possiamo migliorare, integrare o innovare il nostro lavoro e renderlo migliore per te. Scrivici a [email protected].
Articoli correlati
link: / lookup-formas-vlookup-function-to-calcola-voto-in-excel [funzione CERCA.VERT per calcolare il voto in Excel]: Per calcolare i voti IF e IFS non sono le uniche funzioni che puoi usare. CERCA.VERT è più efficiente e dinamico per tali calcoli condizionali. Per calcolare i voti utilizzando CERCA.VERT possiamo utilizzare questa formula.
link: / lookup-formas-17-things-about-excel-vlookup [17 Things About Excel VLOOKUP]: VLOOKUP è 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.
link: / lookup-formas-lookup-the-first-text-from-a-list [CERCA il primo testo da un elenco in Excel]: La funzione CERCA.VERT funziona bene con i caratteri jolly. Possiamo usarlo per estrarre il primo valore di testo da un dato elenco in Excel. Ecco la formula generica.
link: / lookup-formulas-lookup-date-with-last-value-in-list [LOOKUP date with last value in list]: Per recuperare la data che contiene l’ultimo valore usiamo la funzione LOOKUP. Questa funzione verifica la cella che contiene l’ultimo valore in un vettore e quindi utilizza quel riferimento per restituire la data.
link: / lookup-formulas-how-to-retrieve-latest-price-in-excel [How to Retrieve Latest Price in Excel]: È comune aggiornare i prezzi in qualsiasi attività commerciale e utilizzare i prezzi più recenti per qualsiasi acquisto o le vendite sono obbligatorie. Per recuperare l’ultimo prezzo da un elenco in Excel utilizziamo la funzione CERCA. La funzione CERCA recupera il prezzo più recente.
Articoli popolari:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentare-la-produttività [50 scorciatoia Excel per aumentare la produttività]: Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
link: / tips-if-condition-in-excel [Come utilizzare la funzione IF in Excel]: L’istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è TRUE o restituisce un altro valore specifico se FALSE .
link: / formule-e-funzioni-introduzione-di-vlookup-funzione [Come usare la funzione CERCA.VERT in Excel]: Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare valori da intervalli diversi e lenzuola.
link: / tips-countif-in-microsoft-excel [Come usare la funzione CONTA.SE in Excel]: Conta i valori con le condizioni usando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel]: Questa è un’altra funzione essenziale del dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.