Come contare i valori univoci in base a più criteri in Excel?
In molti scenari pratici, spesso non è sufficiente contare i valori, ma è necessario determinare quanti elementi univoci soddisfano determinate condizioni all'interno dei tuoi dati. Ad esempio, potresti voler scoprire il numero di prodotti diversi venduti da un particolare venditore oppure quante ordini univoci sono stati effettuati in un certo periodo di tempo. Gestire tali attività in modo efficiente in Excel richiede di familiarizzare con formule appropriate, funzionalità avanzate come le tabelle pivot o persino soluzioni personalizzate in VBA. In questo articolo, esploreremo diversi metodi pratici per contare valori univoci in base a uno o più criteri, con istruzioni dettagliate e suggerimenti.
Conta valori univoci in base a un criterio
Conta valori univoci in base a due date specifiche
Conta valori univoci in base a due criteri
Conta valori univoci in base a tre criteri
Conta valori univoci con Tabella Pivot (Conteggio Distinto, Excel 2013+)
Conta valori univoci con codice VBA (per casi complessi/automatizzati)
Conta valori univoci in base a un criterio
Consideriamo un caso comune: vuoi contare quanti prodotti diversi sono stati venduti da Tom. Questo metodo è adatto quando hai un set di dati semplice e desideri valutare l'unicità in base a una singola condizione, come i record delle vendite di una singola persona. È semplice ma richiede un uso attento delle formule matriciali.
Per questo scenario, inserisci la seguente formula in una cella vuota (ad esempio, cella G2):
=SOMMA(SE("Tom"=$C$2:$C$20;1/(CONTA.PIÙ.SE($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20));0))
Dopo aver digitato la formula, premi Ctrl + Shift + Enter (non solo Invio) per confermarla come formula matriciale. Le parentesi graffe appariranno intorno alla formula nella barra della formula e vedrai immediatamente il risultato come mostrato di seguito:
Nota:
- “Tom” è la condizione che desideri utilizzare per filtrare i risultati. Puoi sostituire "Tom" con un riferimento a un'altra cella (ad esempio, $F$2) se desideri maggiore flessibilità.
- $C$2:$C$20 contiene i nomi dei venditori da valutare.
- $A$2:$A$20 è la colonna dei prodotti per cui desideri conteggi univoci.
- Se il tuo intervallo di dati cambia, ricorda di regolare di conseguenza i riferimenti.
Suggerimento: Se utilizzi Excel 365 o Excel 2019 e versioni successive, puoi provare a usare le funzioni UNICI e FILTRO per formule più semplici.
Se incontri errori #DIV/0!, verifica nuovamente i criteri e assicurati che gli intervalli abbiano la stessa lunghezza.
Conta valori univoci in base a due date specifiche
Quando devi trovare il numero di elementi univoci all'interno di un intervallo di date specifico, ad esempio tutti i prodotti univoci venduti tra il 1/9/2016 e il 30/9/2016, puoi applicare questo approccio. Questo è particolarmente utile quando analizzi le tendenze dei dati tra periodi specifici, come mensili, trimestrali o intervalli di date personalizzati. Tuttavia, fai attenzione al formato delle date; deve corrispondere ai valori delle date del tuo foglio di lavoro.
Inserisci la seguente formula in una cella vuota dove vuoi visualizzare il risultato:
=SOMMA(SE($D$2:$D$20<=DATA(2016;9;30)*($D$2:$D$20>=DATA(2016;9;1));1/CONTA.PIÙ.SE($A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATA(2016;9;30);$D$2:$D$20; ">="&DATA(2016;9;1)));0)
Premi Ctrl + Shift + Enter dopo aver inserito la formula per eseguirla come formula matriciale. Lo screenshot qui sotto dimostra il risultato:
Nota:
- 2016,9,1 e 2016,9,30 sono i criteri di data di inizio e fine. Puoi modificarli secondo necessità, o persino utilizzare riferimenti di cella per filtri di data dinamici.
- $D$2:$D$20 contiene le voci di data da controllare.
- $A$2:$A$20 è ancora la colonna degli articoli o prodotti che desideri contare in modo univoco.
- Assicurati che le tue date siano memorizzate come date valide di Excel, non come stringhe di testo. Se il tuo risultato non appare come previsto, conferma il formato delle date e gli intervalli.
Suggerimento: Usa DATA(anno, mese, giorno) per evitare problemi con il formato delle date regionale. Quando utilizzi intervalli dinamici, considera l'uso di intervalli denominati per chiarezza.
Conta valori univoci in base a due criteri
Supponiamo che tu voglia analizzare solo i prodotti che Tom ha venduto a settembre, combinando nome e un intervallo di date nel tuo conteggio univoco. Questo scenario è comune per le revisioni delle prestazioni basate su periodi o analisi segmentate. Man mano che i tuoi criteri si espandono, la formula diventa più complessa e l'attenzione all'accuratezza dei dati diventa ancora più importante.
Inserisci la formula seguente in qualsiasi cella vuota, come H2:
=SOMMA(SE(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATA(2016;9;30)*($D$2:$D$20>=DATA(2016;9;1)));1/CONTA.PIÙ.SE($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATA(2016;9;30);$D$2:$D$20; ">="&DATA(2016;9;1)));0)
Dopo aver digitato la formula, confermala con Ctrl + Shift + Enter. Dovresti vedere immediatamente il conteggio univoco; controlla l'illustrazione seguente:
Note:
- “Tom” è il criterio di nome, mentre “2016,9,1” e “2016,9,30” sono i limiti dell'intervallo di date. Regolali secondo necessità o rendili dinamici con riferimenti di cella.
- $C$2:$C$20 è la colonna del personale (o un altro primo criterio); $D$2:$D$20 è la colonna delle date; $A$2:$A$20 contiene gli elementi univoci da contare.
- Gli intervalli devono avere tutti la stessa lunghezza per prevenire errori.
Se desideri utilizzare condizioni “OR”, come contare prodotti univoci venduti da Tom o nella regione Sud, puoi utilizzare la seguente formula. Questo consente condizioni di ricerca più ampie, anche se i risultati possono sovrapporsi se i dati soddisfano entrambi i criteri:
=SOMMA(--(FREQUENZA(SE(("Tom"=$C$2:$C$20)+("Sud"=$B$2:$B$20); CONTA.SE($A$2:$A$20; "<"&$A$2:$A$20); ""); CONTA.SE($A$2:$A$20; "<"&$A$2:$A$20))>0))
Non dimenticare di premere Ctrl + Shift + Enter. Vedrai i risultati come mostrato di seguito:
Suggerimento: Quando applichi criteri OR, tieni presente la possibile doppia conteggio se lo stesso record soddisfa entrambe le condizioni. Per set di dati di grandi dimensioni, le prestazioni potrebbero risentirne.
Conta valori univoci in base a tre criteri
A volte la tua analisi potrebbe richiedere tre o più condizioni, come determinare i prodotti univoci venduti da Tom a settembre solo nella regione Nord. Questo è comune nell'analisi di dati multidimensionali per report o approfondimenti aziendali mirati. Una gestione accurata dei riferimenti è essenziale per gestire tale logica composta.
Inserisci questa formula matriciale in una cella vuota (ad esempio, I2):
=SOMMA(SE(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATA(2016;9;30))*($D$2:$D$20>=DATA(2016;9;1))*("Nord"=$B$2:$B$20);1/CONTA.PIÙ.SE($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATA(2016;9;30); $D$2:$D$20; ">="&DATA(2016;9;1); $B$2:$B$20; "Nord"));0)
Premi Ctrl + Shift + Enter per finire. Ecco un risultato di esempio per riferimento:
Per condizioni avanzate, verifica attentamente che tutti gli intervalli siano coerenti e che i tipi di dati (ad esempio, date e testo) siano corretti. Disallineamenti possono causare errori o risultati fuorvianti.
Suggerimenti:
- Se incontri problemi di prestazioni su set di dati di grandi dimensioni, considera di suddividere la formula o di utilizzare la soluzione con Tabella Pivot di Excel.
- Gli intervalli denominati o i riferimenti di celle per tutti i criteri migliorano la leggibilità e riducono gli errori di formula.
- Per un uso frequente, considera di registrare queste formule in riferimenti di celle denominati o funzioni personalizzate.
Conta valori univoci con Tabella Pivot (Conteggio Distinto, Excel 2013+)
Per gli utenti di Excel 2013 o versioni successive, le Tabelle Pivot offrono un'alternativa più interattiva e senza formule per contare valori univoci su uno o più criteri. La funzione Conteggio Distinto ti aiuta a riassumere e filtrare grandi insiemi di dati in modo efficiente, rendendo questo metodo particolarmente adatto per ambienti dinamici basati su report. Tuttavia, tieni presente che le versioni precedenti di Excel non supportano la funzione Conteggio Distinto all'interno delle Tabelle Pivot.
Come usare questo metodo:
- Seleziona il tuo set di dati e vai su Inserisci > Tabella Pivot.
- Nella finestra di dialogo Crea Tabella Pivot, scegli dove posizionare la Tabella Pivot, seleziona la casella "Aggiungi questi dati al Modello di Dati" e quindi clicca OK.
- Trascina il campo che desideri contare in modo univoco (ad esempio, Prodotto) nell'area Valori. Di default, verrà visualizzato come "Conteggio di...".
- Clicca sul campo nell'area Valori e seleziona Impostazioni Campo Valore.
- Nella finestra di dialogo pop-up, scorri verso il basso e seleziona Conteggio Distinto (Questa opzione è disponibile solo in Excel 2013 o versioni successive ed appare quando la Tabella Pivot viene creata con l'opzione "Aggiungi questi dati al Modello di Dati" abilitata).
- Aggiungi i campi dei criteri (ad esempio, Venditore, Regione, Data) all'area Filtri o Righe/Colonne per applicare condizioni singole o multiple.
- La tua Tabella Pivot ora mostrerà il conteggio univoco dei valori filtrati dai criteri scelti.
Vantaggi: Altamente visuale, facile da regolare i filtri senza modificare le formule, e adatta per reporting interattivo.
Limitazioni: Non disponibile in Excel 2010 o versioni precedenti; aggiungere nuovi dati richiede di aggiornare manualmente la Tabella Pivot.
Suggerimento pratico: Assicurati sempre che i dati di origine non abbiano duplicati all'interno dello stesso record se non sono intenzionali. Se trovi l'opzione Conteggio Distinto mancante, ricrea la Tabella Pivot e controlla l'opzione “Aggiungi questi dati al Modello di Dati”.
Conta valori univoci con codice VBA (per casi complessi/automatizzati)
A volte, potrebbe essere necessario contare valori univoci in base a vari criteri automaticamente, specialmente quando si gestiscono set di dati molto grandi o quando si ripete frequentemente l'analisi. Una macro VBA è ideale per tali situazioni, poiché può elaborare rapidamente diverse logiche – inclusi filtri multi-criterio – senza intervento manuale dopo la configurazione. Tuttavia, VBA è più avanzato delle normali funzionalità di Excel, quindi è meglio utilizzato da utenti a proprio agio con le macro o da quelli con esigenze analitiche continue.
Passaggi operativi:
- Premi Alt + F11 per aprire l'editor VBA. Nell'editor, seleziona Inserisci > Modulo per creare un nuovo modulo.
- Copia e incolla il seguente codice VBA nel modulo:
Sub CountUniqueWithCriteria()
Dim DataRange As Range
Dim CriteriaRange As Range
Dim CriteriaValue As Variant
Dim Dict As Object
Dim i As Long
Dim UniqueCount As Long
Dim ResultCell As Range
Set Dict = CreateObject("Scripting.Dictionary")
' Prompt for range settings
Set DataRange = Application.InputBox("Select data range (items to count):", "KutoolsforExcel", Type:=8)
Set CriteriaRange = Application.InputBox("Select criteria range (e.g. Salesperson):", "KutoolsforExcel", Type:=8)
CriteriaValue = Application.InputBox("Enter criteria value:", "KutoolsforExcel", "", Type:=2)
Set ResultCell = Application.InputBox("Select cell for result output:", "KutoolsforExcel", Type:=8)
On Error Resume Next
For i = 1 To DataRange.Rows.Count
If CriteriaRange.Cells(i, 1).Value = CriteriaValue Then
If Not Dict.Exists(DataRange.Cells(i, 1).Value) Then
Dict.Add DataRange.Cells(i, 1).Value, 1
End If
End If
Next i
UniqueCount = Dict.Count
ResultCell.Value = UniqueCount
MsgBox "Unique count for '" & CriteriaValue & "': " & UniqueCount, vbInformation, "KutoolsforExcel"
End Sub- Chiudi l'editor VBA e torna al tuo foglio di lavoro. Premi Alt + F8, seleziona CountUniqueWithCriteria, ed esegui la macro.
- Segui le richieste di input per specificare gli intervalli e i criteri in base ai tuoi dati. Il risultato apparirà nella cella che scegli e anche come una finestra di messaggio.
Spiegazione dei parametri e note:
- Questa macro è attualmente configurata per un singolo criterio. Per estenderla a più criteri, modifica la logica SE ... ALLORA all'interno del ciclo.
- Salva sempre la cartella di lavoro prima di eseguire macro, poiché le modifiche non sono annullabili.
- Abilita le macro nelle impostazioni di Excel se incontri errori di esecuzione.
- Questo metodo funziona bene per dati più grandi o aggiornati frequentemente dove le formule manuali sarebbero ingombranti.
Vantaggi: Altamente personalizzabile e automatizzabile, gestisce in modo efficiente dati grandi e in cambiamento. Adatto per esigenze di workflow avanzate o ripetute.
Svantaggi: Richiede autorizzazioni per le macro, e i principianti potrebbero aver bisogno di tempo per familiarizzare con le operazioni VBA.
Quando si lavora con conteggi di valori univoci in base a criteri, conferma sempre i riferimenti degli intervalli e assicurati che tutte le colonne dei criteri siano allineate in dimensione. Gli intervalli non corrispondenti sono una causa comune di errori o risultati errati. Se le formule restituiscono risultati inaspettati, controlla eventuali problemi di formattazione nascosti o celle vuote. Per scenari critici dal punto di vista delle prestazioni, le Tabelle Pivot e VBA forniscono alternative robuste alle formule matriciali. Scegli la soluzione più adatta al tuo livello di comfort e alla complessità del tuo set di dati. Ricorda, Kutools per Excel offre ulteriori utilità e scorciatoie che possono semplificare molte di queste attività per una maggiore efficienza nei workbook complessi.
I migliori strumenti per la produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un'efficienza mai vista prima. Kutools per Excel offre oltre300 funzionalità avanzate per aumentare la produttività e farti risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta le schede su Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra invece che in nuove finestre.
- Aumenta la produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Tutti gli add-in Kutools. Un solo programma di installazione
La suite Kutools for Office include add-in per Excel, Word, Outlook & PowerPoint più Office Tab Pro, ideale per i team che lavorano su più app di Office.
- Suite tutto-in-uno — Add-in per Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un solo programma di installazione, una sola licenza — configurazione in pochi minuti (pronto per MSI)
- Funzionano meglio insieme — produttività ottimizzata su tutte le app Office
- Prova completa30 giorni — nessuna registrazione, nessuna carta di credito
- Massimo risparmio — costa meno rispetto all’acquisto singolo degli add-in