Finora abbiamo imparato come sommare l'intera colonna corrispondente di una tabella in Excel. Ma come si sommano i valori quando è necessario abbinare colonne e righe. In questo articolo impareremo come eseguire la somma delle righe e delle colonne corrispondenti.
Ci sono due formule per farlo, ma prima vediamo lo scenario.
Qui ho una tabella che registra le vendite effettuate dai dipendenti in diversi mesi. Il nome dei dipendenti può essere ripetuto. Vedere la figura seguente:
Dobbiamo ottenere la somma del mese di maggio in cui il venditore è Donald.
Metodo 1: sommare l'intestazione di colonna e l'intestazione di riga corrispondenti utilizzando la funzione SUMPRODUCT.
Il Funzione SUMPRODOTTO è la funzione più versatile quando si tratta di sommare e contare valori con criteri complicati. La funzione generica da sommare facendo corrispondere colonna e riga è:
| =SUMPRODUCT((colonne)*(intestazioni_colonna=intestazione_colonna)*(intestazioni_riga=intestazione_riga) |
Colonne:È l'intervallo bidimensionale delle colonne che si desidera sommare. Non deve contenere intestazioni. Nella tabella sopra è C3:N7.
intestazioni_colonne:È l'intervallo di intestazione dicolonne che vuoi sommare. Nei dati di cui sopra, è C2:N2.
intestazione_colonna: È l'intestazione che vuoi abbinare. Nell'esempio sopra, è in B13.
Senza ulteriori indugi usiamo la formula.
intestazioni_riga:È l'intervallo di intestazione dirighe che vuoi sommare. Nei dati di cui sopra, è B3: B10.
riga_intestazione: È l'intestazione che vuoi far corrispondere nelle righe. Nell'esempio sopra, è in F13 .
Senza ulteriori indugi usiamo la formula.
Scrivi questa formula nella cella D13 e lascia che Excel faccia la magia (non esiste la magia)…
| =SOMMAPRODOTTO((C3:N10)*(C2:N2=B13)*(B3:B10=E13)) |
Questo restituisce il valore:
Ora quando cambi il mese o il venditore, la somma cambierà secondo l'intestazione della riga e l'intestazione della colonna.
Come funziona?
Questa semplice logica booleana.
(C2:N2=B13): Questa istruzione restituirà un array di TRUE e FALSE. Tutti i valori corrispondenti nella colonna hanno true e altri avranno false. In questo caso, avremo solo un True poiché l'intervallo C2:N2 contiene solo un'istanza May a 5ns Posizione.
(B3:B10=E13): Funzionerà come sopra e restituirà un array VERO e FALSO. Tutti i valori corrispondenti avranno TRUE e altri avranno FALSE. In questo caso, avremo 2 TRUE poiché l'intervallo B3: B10 ha due istanze di "Donald".
(C2:N2=B13)*(B3:B10=E13): Ora stiamo moltiplicando gli array restituiti dalle istruzioni. Questo implementerà una logica e otterremo un array di 1 e 0. Ora avremo un array 2D che conterrà 2 1 e il resto 0.
(DO3:N10)*(C2:N2=B13)*(B3:B10=E13)= Infine moltiplichiamo l'array 2D con la tabella 2D. Restituirà di nuovo una matrice di 0 e i numeri che corrispondono ai criteri.
Infine, il SUMPRODOTTO La funzione riassumerà l'array che risulterà nell'output desiderato.
Metodo 2: Riassumere l'intestazione di colonna e l'intestazione di riga corrispondenti utilizzando la funzione SOMMA e SE
La formula generica per sommare la riga e la colonna corrispondenti utilizzando la funzione SOMMA e SE Excel è:
| =SOMMA(SE(intestazioni_colonna=intestazione_colonna,IF(intestazioni_riga=intestazione_riga,colonne))) |
Tutte le variabili sono le stesse del metodo sopra spiegato. Qui, devono solo essere usati in un ordine diverso.
Scrivi questa formula nella cella D13:
| =SOMMA(SE(C2:N2=B13,SE(B3:B10=E13,C3:N10))) |
Questo restituisce la risposta corretta. Guarda lo screenshot qui sotto:
Come funziona?
La logica è la stessa del primo metodo SUMPRODCUT, solo il meccanismo è diverso. Se lo spiego in breve, la funzione IF interna restituisce un array 2D della stessa dimensione della tabella. Questo array contiene il numero di due righe abbinate. Quindi la funzione IF interna corrisponde alle intestazioni a due colonne in questa matrice e restituisce la matrice 2D che contiene solo i numeri che corrispondono a entrambe, colonna e intestazione. Tutti gli altri elementi dell'array saranno FALSE.
Infine, la funzione SUM riassume questo array e otteniamo la nostra somma.
Quindi sì ragazzi, ecco come riassumere le righe e le colonne corrispondenti da una tabella in Excel. Spero di esserti stato esplicativo e utile. Se hai dei dubbi su questo argomento o hai altri dubbi relativi a Excel/VBA, chiedi nella sezione commenti qui sotto.
Come sommare la colonna in un Excel abbinando l'intestazione | Se vuoi ottenere la somma di una colonna semplicemente usando il nome della colonna, puoi farlo in 3 semplici modi in Excel. La sintassi del metodo SUMPRODUCT per sommare la colonna corrispondente è:
SOMMA.SE con 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.
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 renderanno il tuo lavoro ancora più veloce 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.