In questo articolo impareremo a contare più intervalli con un criterio in Microsoft Excel.
Scenario:
In parole semplici, mentre si lavora con le tabelle di dati, a volte è necessario contare le celle in cui più di due intervalli soddisfano i criteri. In Excel, puoi eseguire attività come operazioni su più intervalli utilizzando la formula spiegata di seguito. I criteri possono essere applicati su testo, numero o qualsiasi corrispondenza parziale in Excel. Criteri all'interno della formula eseguiti utilizzando gli operatori. Operatori come uguale a ( = ), minore di uguale a ( <= ), più grande di ( > ) o non è uguale a ( ).
Come risolvere il problema?
Per questo problema, ci verrà richiesto di utilizzare la funzione SUMPRODUCT. Ora faremo una formula fuori dalla funzione. Qui ci vengono forniti due intervalli di dati e dobbiamo contare le righe che soddisfano 3 criteri. La funzione SUMPRODUCT restituisce la SOMMA dei corrispondenti valori VERO (come 1) e ignora i valori corrispondenti ai valori FALSO (come 0) nell'array restituito
Formula generica:
| = SUMPRODOTTO ( ( rng_1 op_1 crit_1 ) + 0 , ( rng_2 op_2 crit_1 ) + 0 , rng_2 op_2 crit_1 ) + 0 ) |
rng: gamma da cercare
crit: criteri da applicare
op : operatore criteri, condizione data come operatore tra intervallo e criteri
+0 : converte i valori booleani in binari (0 e 1).
Esempio:
Tutti questi potrebbero essere fonte di confusione da capire. Quindi, testiamo questa formula eseguendola nell'esempio mostrato di seguito. Qui dobbiamo trovare il conteggio delle righe elencate nell'intervallo con 3 condizioni. Qui abbiamo un elenco degli incontri diplomatici tenuti tra India e Stati Uniti dal 2014. La tabella mostra il Presidente / PM con l'etichetta del paese e l'anno. La tabella è anche divisa in parti che rappresentano il paese di origine e l'elenco dei paesi in visita.
Condizioni elencate di seguito:
Il presidente degli Stati Uniti "Barack Obama ha visitato l'India con problemi inferiori a 2.
Usa la formula:
| = SUMPRODOTTO ( ( C4:C10 = "Barack Obama" ) + 0 , ( F4:F10 = "India" ) + 0 , ( G4:G10 < 2 ) + 0 )) |
C4:C10="Barack Obama" : Presidente che corrisponde a "Barack Obama" nell'elenco delle visite.
F4:F10="India": paese ospitante corrispondente a "India".
G4:G10<2 : emette meno di due.
+0 : converte i valori booleani in binari (0 e 1).
Qui l'intervallo è dato come riferimento di cella. Premi Invio per ottenere il conteggio.
Come puoi vedere, solo una volta il presidente degli Stati Uniti Barack Obama ha visitato l'India nel 2015. Ciò mostra che la formula estrae il conteggio delle volte abbinate nell'array corrispondente. Poiché c'è anche una stessa volta in cui il presidente degli Stati Uniti "Barack Obama" ha visitato l'India, dove anche i problemi sono uguali a 1 che è inferiore a 2.
Con uguale a Criteri:
L'esempio sopra è stato facile. Quindi per renderlo interessante conteremo quante volte gli Stati Uniti hanno ospitato l'India a partire dal 2014 come da dati.
Condizioni elencate di seguito:
L'India ospitata negli Stati Uniti che ha problemi è pari a 2.
Usa la formula:
| =SOMMA.PRODOTTO ( ( F4:F10 = "USA" ) + 0 , ( D4:D10 = "India" ) + 0 , ( G4:G10 = 2 ) + 0 ) |
F4:F10="US" : paese ospitante corrispondente a "US".
D4:D10="India": paese in visita corrispondente a "India".
G4:G10=2 : problemi uguale a due.
+0 : converte i valori booleani in binari (0 e 1).
Qui l'intervallo è dato come riferimento di cella. Premi Invio per ottenere il conteggio.
Come puoi vedere, ci sono 2 volte in cui gli Stati Uniti hanno ospitato l'India e le emissioni sono pari a due. Ciò mostra che la formula estrae il conteggio delle volte abbinate nell'array corrispondente. Poiché ci sono 5 volte in cui gli Stati Uniti hanno ospitato l'India ma i problemi erano 1 o 3, ma qui abbiamo bisogno che i problemi siano abbinati a 2.
Con criteri maggiori di:
Qui per renderlo interessante conteremo quante volte il presidente degli Stati Uniti "Donald Trump" ha ospitato il primo ministro indiano a partire dal 2014 come da dati.
Condizioni elencate di seguito:
Il presidente degli Stati Uniti "Donald Trump" ha ospitato l'India con problemi maggiori di 1.
Usa la formula:
| =SOMMAPRODOTTO ( ( C4:C10 = "Donald Trump" ) + 0 , ( F4 : F10 = "India" ) + 0 , ( G4:G10 >1 ) + 0 ) |
F4:F10="US": presidente ospitante corrispondente a "Donald Trump".
D4:D10="India": paese in visita corrispondente a "India".
G4:G10=2 : problemi uguale a due.
+0 : converte i valori booleani in binari (0 e 1).
Qui l'intervallo è dato come riferimento di cella. Premi Invio per ottenere il conteggio.
Come puoi vedere, una volta in cui il presidente degli Stati Uniti "Donald Trump" ha ospitato l'India e problemi maggiori di due. Ciò mostra che la formula estrae il conteggio delle volte abbinate nell'array corrispondente. Poiché ci sono 2 volte in cui il presidente degli Stati Uniti "Donald Trump" ha ospitato l'India ma i problemi erano 1 o 3, ma qui abbiamo bisogno che i problemi siano maggiori di 1 che è 3 bugie nell'anno 2019.
Con problemi non considerati nei Criteri:
Qui per rendere facile e comodo la comprensione, conteremo quante volte in totale il presidente degli Stati Uniti ha visitato l'India a partire dal 2014 come da dati.
Condizioni elencate di seguito:
Il presidente degli Stati Uniti ha visitato l'India in totale dal 2014.
Usa la formula:
| =SOMMA.PRODOTTO((F4:F10="India")+0,(D4:D10="USA")+0) |
F4:F10="US" : paese ospitante corrispondente a "US".
D4:D10="India": paese in visita corrispondente a "India".
G4:G10=2 : problemi uguale a due.
+0 : converte i valori booleani in binari (0 e 1).
Qui l'intervallo è dato come riferimento di cella. Premi Invio per ottenere il conteggio.
Come puoi vedere, 2 volte in cui gli Stati Uniti hanno visitato l'India e problemi maggiori di due. Ciò mostra che la formula estrae il conteggio delle volte abbinate nell'array corrispondente. Come c'è stata una volta in cui il presidente degli Stati Uniti "Barack Obama" ha visitato l'India nel 2015 e una volta in cui il presidente degli Stati Uniti "Donald Trump" ha visitato l'India nell'anno 2020.
È inoltre possibile eseguire intervalli come criteri. Conta le celle in cui 2 intervalli soddisfano i criteri. Scopri di più su Countif con SUMPRODUCT in Excel qui.
Di seguito sono riportate alcune note osservative.
Appunti:
- La formula funziona solo con i numeri.
- Gli array nella formula devono essere di uguale lunghezza, poiché la formula restituisce un errore in caso contrario.
- La funzione SUMPRODUCT considera i valori non numerici come 0.
- La funzione SUMPRODUCT considera il valore logico TRUE come 1 e False come 0.
- L'array di argomenti deve essere della stessa dimensione, altrimenti la funzione restituisce un errore.
- La funzione SUMPRODUCT restituisce la somma dopo aver preso i singoli prodotti nella matrice corrispondente.
Spero che questo articolo su Contare più intervalli con un criterio in Microsoft Excel sia esplicativo. Trova altri articoli sul calcolo dei valori e le relative formule di Excel 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.
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.
Come utilizzare la funzione SUMPRODUCT in Excel: restituisce la SOMMA dopo la moltiplicazione dei valori in più matrici in Excel.
CONTA.PI.SE con intervallo di criteri dinamici : conta le celle selezionando i criteri dall'elenco di opzioni nella cella dei criteri in Excel utilizzando lo strumento di convalida dei dati.
COUNTIFS Corrispondenza a due criteri : più criteri corrispondono in diversi elenchi nella tabella utilizzando la funzione CONTA.SE in Excel
COUNTIFS con OR per più criteri : abbina due o più nomi nello stesso elenco utilizzando i criteri OR applicati nell'elenco in Excel.
Come utilizzare Countif in VBA in Microsoft Excel : conta le celle con criteri utilizzando il codice di Visual Basic, Applications Edition nelle macro di Excel.
Come usare i caratteri jolly in Excel : Conta le celle che corrispondono alle frasi negli elenchi di testo utilizzando i caratteri jolly ( * , ? , ~ )in excel
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.