4 metodi efficaci per utilizzare riferimenti strutturati in Microsoft Excel

L’uso di Excel spesso si concentra sull’identificazione delle relazioni tra diversi set di dati. Tuttavia, quando si inseriscono formule complesse, l’uso eccessivo di riferimenti di cella espliciti sia relativi che assoluti, come “B7” e le sue varianti, può portare a una barra delle formule disordinata e caotica.

L’utilizzo di riferimenti strutturati in Excel può migliorare questo processo consentendo di assegnare nomi alle tabelle e alle loro intestazioni. Questi nomi designati servono come riferimenti di cella impliciti, consentendo a Excel di recuperare e calcolare automaticamente i dati strutturati.

Di seguito sono riportati diversi metodi efficaci per utilizzare riferimenti strutturati in Excel.

1. Esecuzione di calcoli all’interno delle tabelle

I riferimenti strutturati funzionano esclusivamente con le tabelle, quindi il metodo ottimale per utilizzarli è all’interno di tali tipologie di tabelle.

Ad esempio, crea una tabella semplice che va da B2 a F8 contenente informazioni sulle vendite per un negozio. Assicurati di etichettare la tabella come “Sales” (controlla il “Table Name” nell’angolo in alto a sinistra).

Successivamente calcoleremo il totale per ogni singola vendita:

Fase 1: Seleziona la cella F2 (assicurandoti di non cliccare sull’icona a discesa). Vai su “Home”, quindi su “Inserisci” e scegli “Inserisci colonne tabella a destra” per includere automaticamente una nuova colonna nella tabella.

Passaggio 2: designare l’intestazione della nuova colonna G come “Totale”.

Fase 3: Nella cella G3, immettere =[@PricePerUnit]*[@Quantity]e premere Invio. Regolare la formattazione della cella come necessario.

I riferimenti “[@PricePerUnit]” e “[@Quantity]” corrispondono ai rispettivi campi all’interno delle colonne. Il simbolo “@” che precede i nomi delle colonne indica che ogni cella di risultato trarrà i suoi valori dalla stessa riga della tabella.

Per chiarire, la formula =[@PricePerUnit]*[@Quantity]in G3 corrisponde effettivamente a =$C3*$D3.

2. Recupero di un intervallo oltre la tabella

Quando intendi utilizzare un riferimento strutturato in una cella che si trova all’esterno della tabella, devi iniziare il riferimento con il nome della tabella. Ad esempio, nel nostro esempio precedente, l’impiego di “Sales[Total]” recupererà l’intero intervallo di valori nella colonna “Total” dalla tabella “Sales”. Ciò fornisce un array di valori multipli per ulteriori manipolazioni.

Ecco come appare in Excel, in particolare nella cella I3, dove c’è ampio spazio per espandere l’intervallo verso il basso.

Recupero di un intervallo esterno alla tabella

3. Sommare e sommare parzialmente una colonna

Per una somma rapida di un’intera colonna, fai leva sulla casella di controllo “Total Row” che si trova nelle impostazioni “Table Design” (in “Table Style Options”). Ecco come ottenere i totali per entrambe le colonne “Quantity” e “Total”.

Sebbene la riga “Totale” non possa essere spostata e venga sempre posizionata alla fine della tabella (consentendo l’inserimento di voci aggiuntive), è possibile duplicarne il risultato altrove:

Per calcolare il totale di ogni voce nella colonna “Totale”, utilizzare =SUM(Sales[Total]).
Per ottenere la somma delle sole voci visibili, come quelle che compaiono dopo aver filtrato la tabella, applica =SUBTOTAL(109,Sales[Total]). Questa formula rispecchia ciò che l’opzione “Total Row” in Table Format fa per la sua riga.

Puoi anche calcolare una somma parziale basata su un criterio specifico trovato nella tabella senza doverla formattare. Ad esempio:

Per trovare le vendite totali attribuite a Mike, usa =SUMIF(Sales[Seller]," Mike" ,Sales[Total]). Qui, “Mike” è una stringa inserita manualmente.
Per sommare tutti i prodotti rappresentati dall’ID prodotto “41230”, dovresti usare =SUMIF(Sales[ProductID],41230,Sales[Total]). Nota che poiché la colonna ProductID è formattata come “Generale”, puoi immettere direttamente il numero.

4. Implementazione della convalida dei dati da una tabella con INDIRETTO

Supponendo che tu stia lavorando con la tabella Sales menzionata in precedenza, puoi stabilire impostazioni di Data Validation personalizzate per facilitare la ricerca nei dati. Creiamo una tabella più piccola che consenta selezioni per Product ID, Date o Sellers, consentendoti di scegliere un singolo articolo da questi set per visualizzare il subtotale.

Passaggio 1: nella cella B13, imposta la convalida dei dati (scheda Dati > Strumenti dati > Convalida dati).

Validazione dei dati da una tabella tramite INDIRETTO 1

Fase 2: Nella finestra popup che appare, seleziona “List” dal menu a discesa “Allow” e inserisci manualmente i valori della colonna nel campo “Source”, separati da virgole. Per questo esempio, abbiamo inserito “ProductID, Seller, Date”.

Validazione dei dati da una tabella tramite INDIRETTO 2

Fase 3: Nella cella C13, stabilisci un’altra istanza di Data Validation. Di nuovo, seleziona “List”. Per “Source”, includi la formula: =INDIRECT("Sales["&B13&"]").