Quindi, abbiamo già imparato cos'è il riferimento 3D in Excel. Il fatto divertente è che il normale riferimento 3D di Excel non funziona con le funzioni condizionali, come la funzione SOMMA.SE. In questo articolo, impareremo come far funzionare i riferimenti 3D con la funzione SOMMA.SE.
La formula generica per SOMMA.SE con riferimento 3D in Excel
Sembra complicato ma non lo è (tanto).
| =SOMMA.PRODOTTO(SOMMA.SE(INDIRECT("'"&intervallo_nome_di_nomi_foglio&"'!" & "intervallo_criteri"),criteri,INDIRETTO("'"&intervallo_nome_di_nomi_foglio&"'!" &"intervallo_somma"))) |
"'"name_range_of_sheet_names"'":È un intervallo denominato che contiene i nomi dei fogli. Questo è molto importante.
"intervallo_criteri":È il riferimento testuale dei criteri che contengono range. (Dovrebbe essere lo stesso in tutti i fogli del lavoro di riferimento 3D.)
criteri:È semplicemente la condizione che vuoi mettere per la somma. Può essere un testo o un riferimento di cella.
"intervallo_somma":È il riferimento di testo dell'intervallo di somma. (Dovrebbe essere lo stesso in tutti i fogli del lavoro di riferimento 3D.)
Basta con la teoria, facciamo riferimento in 3D con la funzione SOMMA.SE funzionante.
Esempio: somma per regione da più fogli utilizzando i riferimenti 3D di Excel:
Stiamo prendendo gli stessi dati che abbiamo preso nel semplice esempio di riferimento 3D. In questo esempio, ho cinque fogli diversi che contengono dati simili. Ogni foglio contiene i dati di un mese. Nel foglio Master, voglio la somma delle unità e la raccolta per regione di tutti i fogli. Facciamolo prima per le unità. Le unità sono nella gamma D2:D14 in tutti i fogli.
Ora se usi il normale riferimento 3D con la funzione SOMMA.SE,
=SOMMA.SE(Gen:Apr!A2:A14,Principale!B4,Gen:Apr!D2:D14)
Restituirà #VALORE! errore. Quindi non possiamo usarlo. Useremo la formula generica sopra menzionata.
Usando la formula 3D generica di Excel che fa riferimento a SOMMA.SE sopra, scrivi questa formula nella cella C3:
| =SOMMA.PRODOTTO(SOMMA.SE(INDIRETTO("'"&Mesi&"'!" & "A2:A14"),Master!B3,INDIRETTO("'"&Mesi&"'!" &"D2:D14"))) |
Qui mesi è un intervallo denominato che contiene i nomi dei fogli. Questo è cruciale.
Quando premi invio ottieni l'output esatto.
Come funziona?
Il cuore della formula è la funzione INDIRETTA e il intervallo denominato. Qui la stringa"'"&Mesi&"'!" & "A2:A14"si traduce in un array di riferimenti di intervallo di ciascun foglio nel intervallo denominato.
| {"'Gen'!D2:D14";"'Feb'!D2:D14";"'Mar'!D2:D14";"'Apr'!D2:D14"} |
Questo array contiene i riferimento testualedi intervalli, non gli intervalli effettivi. Ora, poiché è un riferimento di testo, può essere utilizzato dalla funzione INDIRETTO per convertirli in intervalli effettivi. Questo accade per entrambe le funzioni INDIRETTO. Dopo aver risolto i testi all'interno delle funzioni INDIRETTE (tieni premuto), la formula si presenta così:
| =SOMMA.PRODOTTO(SOMMA.SE(INDIRETTO(({"'Gen'!A2:A14";"'Feb'!A2:A14";"'Mar'!A2:A14";"'Apr'!A2:A14"}) , Master!B3,INDIRETTO({"'Gen'!D2:D14";"'Feb'!D2:D14";"'Mar'!D2:D14";"'Apr'!D2:D14"}))) |
Ora entra in azione la funzione SOMMA.SE (non INDIRETTO, come avrai intuito). La condizione è abbinata al primo intervallo"'Jan'!A2:A14". Qui la funzione INDIRETTO lavora dinamicamente e converte questo testo nell'intervallo effettivo (ecco perché se provi a risolvere INDIRETTO prima usando il tasto F9, non otterrai il risultato). Avanti riassume i valori corrispondenti nell'intervallo"'Gennaio'!D2:D14".Questo accade per ogni intervallo nell'array. Infine, avremo un array restituito dalla funzione SOMMA.SE.
| =SOMMA.PRODOTTO({97;82;63;73}) |
Ora il SUMPRODUCT fa quello che sa fare meglio. Riassume questi valori e otteniamo il funzionamento della nostra funzione SOMMA.SE 3D.
Quindi sì ragazzi, ecco come potete ottenere una funzione SOMMA.SE 3D. E' un po' complesso, su questo sono d'accordo. C'è molto spazio per gli errori in questa formula 3D. Ti suggerirei di utilizzare la funzione SOMMA.SE su ogni foglio in una cella definita e quindi utilizzare il normale riferimento 3D per riassumere quei valori.
Spero di essere stato abbastanza esplicativo. In caso di dubbi su Excel che fa riferimento a qualsiasi altra query relativa a Excel/VBA, chiedere nella sezione commenti qui sotto.
Riferimento relativo e assoluto in Excel | Fare riferimento in Excel è un argomento importante per ogni principiante. Anche gli utenti esperti di Excel commettono errori di riferimento.
Riferimento al foglio di lavoro dinamico | Fornisci fogli di riferimento in modo dinamico utilizzando la funzione INDIRETTO di excel. Questo è semplice…
Espansione dei riferimenti in Excel | Il riferimento espandibile si espande quando viene copiato verso il basso o verso destra. Usiamo il segno $ prima della colonna e del numero di riga per farlo. Ecco un esempio…
Tutto sul riferimento assoluto | Il tipo di riferimento predefinito in Excel è relativo, ma se si desidera che il riferimento di celle e intervalli sia assoluto, utilizzare il segno $. Ecco tutti gli aspetti del riferimento assoluto in Excel.
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
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.
CONTA.SE in Excel 2016 | Conta i valori con le condizioni usando questa fantastica funzione. Non è necessario filtrare i dati per contare un valore specifico. 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.