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 usare questa funzione per contare i valori con più criteri OR.
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 desideri.
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.
Esempio: conta utenti se codice rivenditore o annopartite Utilizzo di 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, qui 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. Questi record vengono contati come 1. Ed è così che otteniamo 7.
Come funziona?
Quindi diamo un'occhiata a come viene risolta la formula passo dopo passo, poi parlerò di come funziona.
| =SUMPRODOTTO(--(((Codice=I3)+(Anno=K3))>0)) |
| 1=>SUMPRODOTTO(--(({VERO;FALSO;VERO;VERO;VERO;VERO;… }+{FALSO;FALSO;FALSO;VERO;VERO;… })>0)) |
| 2=>SUMPRODOTTO(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SUMPRODOTTO(--({VERO;FALSO;VERO;VERO;VERO;VERO;VERO;… }) |
| 4=>SUMPRODOTTO({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
Nella prima fase, il valore di I3 ("INKA") viene confrontato con ogni cella nell'intervallo di codici. Questo restituisce un array di VERO e FALSO. VERO per ogni partita. Per risparmiare spazio non ho mostrato tutto VERO-FALSO. Allo stesso modo, il valore di K3 (2016) viene abbinato a ciascuna cella nell'intervallo dell'anno.
Nel passaggio successivo, aggiungiamo questi due array che risultano in un nuovo array di valori numerici. Come forse saprai, TRUE viene trattato come 1 e FALSE come 0 in Excel. Quindi quando vengono aggiunti VERO e VERO otteniamo 2 e il resto lo puoi capire.
Nel passaggio successivo, controlliamo quale valore è maggiore di 0 nell'array. Questo converte nuovamente l'array in un vero array falso. Per ogni valore 0 che otteniamo, False e rest vengono convertiti come veri. Ora il numero di valori VERI nell'array è la nostra risposta. Ma come li contiamo? Ecco come.
I segni doppi 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.
Nella fase finale SUMPRODUCT riassume questo array e otteniamo la nostra risposta come 7.
Aggiunta di più o criteri per contare utilizzando SUMPRODUCT
Quindi, se hai bisogno di aggiungere più o criteri per contare, puoi semplicemente aggiungere criteri usando + 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 diciamo che vuoi avere due criteri da Codice gamma. Supponiamo che tu voglia contare "INKB". Quindi 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 le impostazioni di 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.
Questo 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 sono 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 questo errore poiché questo renderà 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.
Quindi sì amico, spero di essere stato abbastanza chiaro e che avesse senso. Spero che serva al tuo scopo di essere qui. Se questa formula non ha risolto il tuo problema, fammi sapere le tue esigenze nella sezione commenti qui sotto. Sarò più che felice di aiutarti in qualsiasi modo. Puoi menzionare qualsiasi dubbio relativo a Excel/VBA. Fino ad allora continua ad imparare, continua ad eccellere.
Come utilizzare la funzione SUMPRODUCT in Excel: restituisce la SOMMA dopo la moltiplicazione dei valori in più matrici in Excel. Questa funzione può essere utilizzata per eseguire più attività. Questa è una delle funzioni più versatili.
CONTA.PI.SE con intervallo di criteri dinamici : Per contare con l'intervallo di criteri dinamici utilizziamo semplicemente la funzione INDIRETTO. Questa funzione può
COUNTIFS con OR per più criteri : conta le celle che hanno più criteri che corrispondono utilizzando la funzione OR. Per inserire una logica OR nella funzione CONTA.PI.SE non sarà necessario utilizzare la funzione OR.
Utilizzo dell'IF con le funzioni AND/OR in Microsoft Excel : Queste funzioni logiche vengono utilizzate per eseguire calcoli di criteri multipli. Con IF le funzioni OR e AND vengono utilizzate per includere o escludere le corrispondenze.
Come utilizzare la funzione OR in Microsoft Excel : La funzione viene utilizzata per includere tutti i valori TRUE in più criteri.
Come contare le celle che contengono questo o quello in Excel in Excel :Per le celle che contengono questo o quello, possiamo usare la funzione SUMPRODUCT. Ecco come eseguire questi calcoli.
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.
Come utilizzare la funzione CERCA.VERT di Excel| Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come usare Excel Funzione CONTA.SE| Conta i valori con le condizioni usando questa fantastica funzione. Non è necessario filtrare i dati per contare valori specifici. 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.