Riferimenti strutturati in Excel

Come creare riferimenti strutturati in Excel?

I riferimenti strutturati iniziano con le tabelle Excel. Non appena le tabelle create in Excel crea automaticamente riferimenti strutturati per te.

Ora dai un'occhiata all'immagine qui sotto.

• Passaggio 1: avevo fornito un collegamento alla cella B3, invece di mostrare il collegamento come B2, viene visualizzato come Tabella1 [@Sales]. Qui Table1 è il nome della tabella e @Sales è la colonna a cui ci riferiamo. Tutte le celle in questa colonna sono indicate da un nome di tabella e seguite dal nome dell'intestazione di colonna.
• Passaggio 2: ora cambierò il nome della tabella in Data_Table e cambierò l'intestazione della colonna in Importo .
• Passaggio 3: per modificare il nome della tabella, posizionare un cursore all'interno della tabella> andare su Design> Nome tabella.

• Passaggio 4: menzionare il nome della tabella come Data_Table.

• Passaggio 5: ora cambia dare un riferimento alla cella B3.

Quindi abbiamo capito che il riferimento strutturato ha due parti Nome tabella e Nome colonna.

Esempi

Puoi scaricare questo modello Excel per riferimenti strutturati qui - Modello Excel per riferimenti strutturati

Esempio 1

Utilizzando riferimenti strutturati puoi rendere dinamica la tua formula. A differenza dei normali riferimenti di cella, consente alla formula di essere attiva in caso di aggiunta ed eliminazione nell'intervallo di dati.

Fammi applicare la formula SOMMA sia per l'intervallo normale che per la tabella Excel.

Formula SOMMA per intervallo normale.

Formula SOMMA per tabella Excel.

Vorrei aggiungere alcune righe ai dati sia della tabella normale che di quella di Excel. Ho aggiunto 2 elementi pubblicitari ai dati, ora vedo la differenza.

Il riferimento strutturato nella tabella Excel mostra il valore aggiornato ma l'intervallo di dati normale non mostra i valori aggiornati a meno che non si apportino manualmente alcune modifiche alla formula.

Esempio n. 2

Ora guarda un altro esempio. Ho un nome del prodotto, quantità e informazioni sul prezzo. Utilizzando queste informazioni ho bisogno di arrivare al valore di vendita.

Per ottenere il valore delle vendite, la formula è Qtà * Prezzo . Applichiamo questa formula nella tabella.

La formula dice [@QTY] * [@PRICE]. Questo è più comprensibile del normale riferimento di B2 * C2. Non otteniamo il nome della tabella se mettiamo la formula all'interno della tabella.

Problemi con i riferimenti strutturati di Excel

Durante l'utilizzo di riferimenti strutturati affrontiamo alcuni problemi che sono elencati di seguito.

Problema n. 1

Anche i riferimenti strutturati hanno i loro problemi. Conosciamo tutti l'applicazione della formula excel e la copia o il trascinamento nelle altre celle rimanenti. Questo non è lo stesso processo nei riferimenti strutturati, funziona in modo leggermente diverso.

Ora guarda l'esempio qui sotto. Ho applicato la formula SOMMA in Excel per l'intervallo normale.

Se voglio sommare il prezzo e il valore delle vendite, semplicemente copierò e incollerò o trascinerò la formula corrente nelle altre due celle e mi darà il valore SUM di Prezzo e valore di vendita.

Ora applica la stessa formula per la tabella Excel per la colonna Qtà.

Ora abbiamo la somma della colonna Qtà. Come il normale intervallo, la formula copia la formula corrente e incollala nella colonna Prezzo per ottenere il totale di Prezzo.

Oh mio Dio!!! Non mostra il totale della colonna Prezzo, ma mostra solo il totale della colonna Qtà. Quindi, non possiamo copiare e incollare questa formula nella cella adiacente o in qualsiasi altra cella per fare riferimento alla colonna o riga relativa.

Trascina la formula per modificare il riferimento

Ora che conosciamo i suoi limiti, non possiamo più fare il lavoro di copia-incolla con riferimenti strutturati. Allora come si supera questa limitazione?

La soluzione è molto semplice, basta trascinare la formula invece di copiarla. Seleziona la cella della formula e utilizza il quadratino di riempimento e trascinalo sulle due celle rimanenti per modificare il riferimento della colonna in Prezzo e Valore di vendita.

Ora abbiamo le formule aggiornate per ottenere i rispettivi totali.

Problema n. 2

Abbiamo visto un problema con i riferimenti della struttura e abbiamo trovato anche la soluzione, ma abbiamo un altro problema qui, non possiamo effettuare la chiamata come riferimento assoluto se stiamo trascinando la formula su altre celle.

Diamo ora un'occhiata all'esempio seguente. Ho una tabella delle vendite con più voci e desidero consolidare i dati utilizzando la funzione SUMIF in Excel.

Ora applicherò la funzione SOMMA.SE per ottenere i valori di vendita consolidati per ogni prodotto.

Ho applicato la formula per il mese di gennaio, poiché è un riferimento strutturato, non possiamo copiare e incollare la formula nelle restanti due colonne, non cambierà il riferimento a febbraio e marzo, quindi trascinerò la formula.

Oh!! Non ho ricevuto alcun valore nella colonna Feb & Mar. Quale sarebbe il problema ??? Guarda attentamente la formula.

Abbiamo trascinato la formula dal mese di gennaio. Nella funzione SOMMA.SE il primo argomento è Intervallo di criteri Tabella_Vendite [Prodotto]  poiché abbiamo trascinato la formula, questa ha modifiche in Tabella_Vendite [Gen].

Allora come lo affrontiamo ?? Dobbiamo rendere il primo argomento cioè la colonna Prodotto come assoluto e le altre colonne come riferimento relativo. A differenza del normale riferimento, non possiamo permetterci il lusso di utilizzare il tasto F4 per modificare il tipo di riferimento.

La soluzione è che dobbiamo duplicare la colonna di riferimento come mostrato nell'immagine sottostante.

Ora possiamo trascinare la formula su altre due colonne alesate. L'intervallo di criteri sarà costante e altri riferimenti di colonna cambieranno di conseguenza.

Suggerimento: per rendere la RIGA come riferimento assoluto dobbiamo inserire una doppia RIGA ma dobbiamo mettere il simbolo @ prima del nome della RIGA.

= Sales_Table [@ [Product]: [Product]]

Come disattivare il riferimento strutturato in Excel?

Se non sei un fan dei riferimenti strutturati, puoi disattivarli seguendo i passaggi seguenti.

• Passaggio 1: vai su FILE> Opzioni.
• Passaggio 2: formule> deseleziona Usa nomi di tabella nelle formule.

Cose da ricordare

• Per fare il riferimento assoluto nel riferimento strutturato, dobbiamo raddoppiare il nome della colonna.
• Non possiamo copiare la formula del riferimento strutturato, invece dobbiamo trascinare la formula.
• Non possiamo vedere esattamente a quale cella ci riferiamo nei riferimenti strutturati.
• Se non sei interessato ai riferimenti strutturati puoi disattivarli.