Potresti non sapere come utilizzare la funzione OFFSET in Excel

Sapevi che la funzione OFFSET in Microsoft Excel può aiutarti a fare molte altre cose, non solo i riferimenti di cella? Di seguito sono riportati i modi unici per utilizzare OFFSET in Excel che potresti non conoscere.

La funzione OFFSET di Microsoft Excel è uno strumento flessibile e potente. A causa della sua complessità e del numero di funzioni simili, purtroppo questa funzione di Excel è sottoutilizzata.

Questa funzione relativamente semplice può aiutare gli utenti a risparmiare tempo e fatica. Semplifica l'ottenimento di punti dati accurati da un set di dati master ben ordinato. Consente inoltre la creazione semplice di report dettagliati, visualizzazioni interattive e dashboard complessi.

Crea un set di dati di dimensioni personalizzate

Uno degli usi più comuni della funzione OFFSET in Excel è raccogliere set di dati ridimensionabili. Con un set di dati di grandi dimensioni, è possibile prendere in considerazione solo una piccola parte dei dati in un dato momento.

Ad esempio, per fornire un report automatizzato sui totali delle vendite dei dipendenti, è necessario ottenere il totale per una o più settimane specifiche.

Scrivere una funzione SOMMA diversa per ciascun totale può essere abbastanza semplice. Tuttavia, l'uso della funzione OFFSET risulterà più facile da usare e più veloce da riutilizzare.

Innanzitutto, inserisci la data con cui vuoi iniziare nella cella B6. Quindi, inserisci il numero di giorni per cui desideri eseguire il report in B7. Successivamente, utilizza le semplici funzioni SOMMA e MEDIA per ottenere più dati per ciascun dipendente nel periodo selezionato:

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

Modificare la data o l'ora di inizio è semplice come modificare una cella, ma puoi trascinare e ridimensionare gruppi di dati molto più facilmente.

Consente di creare report personalizzati

Utilizza lo stesso metodo sopra descritto per creare un report completo personalizzando le variabili abbastanza facilmente. Puoi fare in modo che il primo argomento nella funzione OFFSET faccia riferimento a un altro foglio. Ciò consente di separare l'immissione dei dati e il reporting.

L'azione di cui sopra consente anche di trascinare pezzi di dati selezionati da un foglio di lavoro all'altro. Inoltre, consente di visualizzare solo le informazioni rilevanti per il report corrente. Impostare un report che mostri i totali giornalieri utilizzando gli stessi dati di prima è abbastanza semplice. Puoi farlo senza recuperare i dati per date non correlate.

La seguente funzione di offset inserita nella cella B5 recupererà il numero di giorni specificati in B2. Inizia dalla settimana selezionata in B1 e mostra solo i dati del periodo in questione:

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

È possibile limitare le voci in B1 e B2 e creare un elenco a discesa in Excel con le settimane disponibili utilizzando la convalida dei dati.

Impaginazione del foglio di calcolo

La funzione OFFSET può anche rendere possibile la semplice impaginazione di un foglio di calcolo, un'attività spesso associata all'utilizzo della scheda Sviluppatore di Excel. Ciò può rendere l'elaborazione di grandi gruppi di dati più semplice ed efficace.

Quando si lavora con fogli di calcolo contenenti fino a 30.000 righe di dati, può essere difficile esaminare tutti i dati contemporaneamente. Crea un menu a discesa che accetta un intervallo di numeri da 1 a 300, consentendoti di separare rapidamente i dati per pagina con 100 risultati per pagina.

La funzione OFFSET ha sempre il suo punto di ancoraggio posizionato nella cella dati superiore. Tuttavia, il secondo argomento è l'offset verticale. Questo valore è uguale al numero di pagina selezionato meno 100. L'offset è orizzontale, il terzo argomento può essere impostato su 0.

Il quarto argomento è impostato su 100, il quinto argomento è il numero di colonne nel set di dati. Questo metodo rende estremamente semplice la suddivisione e la gestione delle tabelle di dati.

Di seguito è riportato un esempio di dati impaginati. Comprende 500 informazioni registrate da un sensore in un foglio e un report impaginato nell'altro foglio.

Il report utilizza la convalida dei dati nella cella B1 per creare un menu a discesa con pagine al suo interno. La seguente espressione nella cella A3 crea il corpo del report:

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

Sopra sono riportati alcuni modi per utilizzare la funzione offset in Excel che pochi conoscono ma che sono davvero utili, soprattutto quando è necessario elaborare grandi quantità di dati.

Spero che l'articolo ti sia utile.