Come ho detto in molti dei miei blog, SUMPRODUCT è una funzione molto versatile e può essere utilizzata per molteplici scopi. In questo articolo vedremo come possiamo utilizzare questa funzione per contare valori con più criteri o criteri.
Formula generica SUMPRODUCT per contare con più criteri o criteri
| =SOMMA.PRODOTTO(--(((criteri1)+(criteri2)+… )>0) |
Criteri1: questo è un criterio che restituisce una matrice di VERO e FALSO.
Criteri2: Questo è il prossimo criterio che vuoi controllare. Allo stesso modo puoi avere tutti i criteri che vuoi.
La formula generica di cui sopra viene modificata spesso per soddisfare i requisiti per contare con più criteri OR. Ma la formula base è questa. Per prima cosa vedremo come funziona attraverso un esempio e poi discuteremo altri scenari in cui sarà necessario modificare un po' questa formula.
Problema:
Il prezzo in una cella dovrebbe essere diviso accuratamente in 5 pagamenti simili. Quando si divide semplicemente il prezzo $ 88,04 per 5 e si arrotonda il risultato a due cifre decimali, si ottengono pagamenti individuali di $ 17,61. Tuttavia, ciò comporterebbe un pagamento totale di $ 88,05, che non è l'importo esatto con cui iniziamo. Pertanto, vogliamo calcolare 5 numeri simili che si sommano all'importo totale.
Soluzione:
Primo Calcola il primo pagamento dividendo il prezzo per 5 e arrotondando il risultato.
Utilizzare la funzione ROUND nella seguente formula:
=ROTONDO(B1/5,2)
Passo 2:
Calcola ogni pagamento residuo.
Utilizzare le funzioni ROUND, SUM e ROW nella seguente formula:
=ARROTO(($B$1-SOMMA($B$2:B2))/(5-RIGHE()+RIGHE($B$2)),2)
Esempio:
Contare gli utenti se il codice del rivenditore corrisponde o l'anno utilizzando SUMPRODUCT
Quindi qui abbiamo un set di dati di venditori. I dati contengono molte colonne. Quello che dobbiamo fare è contare il numero di utenti che hanno il codice "INKA" o l'anno è "2016". Assicurati che se qualcuno ha entrambi (codice come "inka" e anno 2016) dovrebbe essere contato come 1.
Quindi, ascolta, abbiamo due criteri. Usiamo la formula SUMPRODUCT sopra menzionata:
| =SOMMAPRODOTTO(--(((Codice=I3)+(Anno=K3))>0)) |
Qui, codice e anno sono intervalli denominati. Questo restituisce 7.
Nei dati abbiamo 5 record di codice INKA e 4 record dell'anno 2016. Ma 2 record hanno rispettivamente "INKA" e 2016 come codice e anno. E questi record vengono contati come 1. Ed è così che otteniamo 7.
Vediamo come funziona?
Quindi diamo un'occhiata a come viene risolta la formula passo dopo passo, poi parlerò di come funziona.
-
- =SOMMAPRODOTTO(--(((Codice=I3)+(Anno=K3))>0))
- SUMPRODOTTO(--(({VERO;FALSO;VERO;VERO;VERO;VERO;… }+{FALSO;FALSO;FALSO;VERO;VERO;… })>0))
- SUMPRODOTTO(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMPRODOTTO(--({VERO;FALSO;VERO;VERO;VERO;VERO;VERO;… })
- SUMPRODOTTO({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
I doppi segni negativi (--) vengono utilizzati per convertire i valori booleani in 1 e 0. Quindi ogni valore VERO nell'array viene convertito in 1 e FALSO in 0.
Aggiunta di più o criteri da contare utilizzando SUMPRODUCT, quindi se è necessario aggiungere più o criteri da contare, è sufficiente aggiungere criteri utilizzando + segno alla funzione.
Ad esempio, se vuoi aggiungere un altro criterio alla formula sopra in modo che aggiunga il numero di dipendenti che hanno venduto più di 5 prodotti. La formula SUMPRODUCT sarà semplicemente così:
| =SOMMA.PRODOTTO(--(((Codice=I3)+(Anno=K3)+(Vendite>5))>0)) |
Semplice! non è vero?
Ma supponiamo che tu voglia avere due criteri dall'intervallo di codici. Supponiamo che tu voglia contare "INKB". Allora come si fa? Un metodo utilizza la tecnica di cui sopra, ma sarebbe ripetitivo. Diciamo che voglio aggiungere altri 10 criteri dalla stessa gamma. In questi casi questa tecnica non è così intelligente per contare con SUMPRODUCT.
Diciamo che abbiamo dati disposti in questo modo.
I codici dei criteri sono in una riga I2:J2. La disposizione dei dati è importante qui. La formula SUMPRODUCT per l'impostazione del conteggio dei criteri 3 OR sarà:
| =SOMMA.PRODOTTO(--(((Codice=I2:J2)+(Anno=I3:J3))>0)) |
Questa è la formula SUMPRODUCT per contare con più criteri quando più criteri di un intervallo vengono scritti in una riga.
La formula sopra restituisce la risposta corretta che è 10.
Se digiti un anno in J3, la formula aggiungerà anche quel conteggio.
Viene utilizzato quando i criteri sono in una riga. Funzionerà quando i criteri in una colonna per lo stesso intervallo? No. Non lo farà.
In questo esempio abbiamo più codici da contare ma questi codici di tipo sono scritti in una colonna. Quando usiamo la formula SUMPRODUCT sopra, otteniamo l'errore ans #N/A. Non entreremo nel merito di come si verifica questo errore poiché questo rende questo articolo troppo lungo. Vediamo come possiamo farlo funzionare.
Per far funzionare questa formula, è necessario racchiudere i criteri del codice nella funzione TRANSPOSE. Questo farà funzionare la formula.
| =SOMMAPRODOTTO(--(((Codice=TRANSPOSE(H3:H4))+(Anno=TRANSPOSE(I3:I4)))>0)) |
Questa è la formula per contare con più o condizioni nello stesso intervallo quando i criteri sono elencati in una colonna.
Ecco tutte le note di osservazione sull'uso della formula.
Appunti:
- La formula funziona sia per testo che per numeri.
- I valori di ricerca possono essere forniti come riferimento di cella o direttamente utilizzando il simbolo di virgolette ( ") nella formula come argomenti.
Spero che tu abbia capito come dividere un importo in pagamenti uguali evitando errori di divisione/arrotondamento in Excel. Esplora altri articoli sul valore di ricerca di Excel e sulle funzioni di Excel 2019 qui. Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica.
Usa INDEX e MATCH per cercare il valore : Funzione INDEX & MATCH per cercare il valore come richiesto.
Intervallo SOMMA con INDICE in Excel : Usa la funzione INDEX per trovare la SOMMA dei valori come richiesto.
Come usare la funzione INDICE in Excel : Trova l'INDICE di un array usando la funzione INDEX spiegata con un esempio.
Come utilizzare la funzione CONFRONTA in Excel : Trova la CONFRONTA nell'array utilizzando il valore INDICE all'interno della funzione CONFRONTA spiegata con l'esempio.
Come usare la funzione CERCA in Excel : Trova il valore di ricerca nell'array utilizzando la funzione CERCA spiegata con un esempio.
Articoli popolari:
Come usare la funzione SE in Excel : l'istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è VERA o restituisce un altro valore specifico se FALSO.
Come utilizzare 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.
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.
Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.