Come si conta il numero di valori univoci in un intervallo in base a più criteri in Excel?
In molti scenari pratici, non basta semplicemente contare i valori: occorre determinare quanti elementi univoci soddisfano determinate condizioni all’interno dei dati. Ad esempio, potresti voler sapere quanti prodotti diversi ha venduto un particolare venditore o quante ordini univoci sono stati effettuati in un determinato intervallo di tempo. Per gestire efficacemente queste attività in Excel, è fondamentale conoscere formule mirate, funzionalità avanzate come le tabelle pivot o persino soluzioni personalizzate in VBA. In questo articolo, esploreremo diversi metodi pratici per contare il numero di valori univoci in un intervallo in base a uno o più criteri, con istruzioni dettagliate e suggerimenti utili.
Conta il numero di valori univoci in un intervallo in base a un criterio
Conta il numero di valori univoci in un intervallo in base a due date specifiche
Conta il numero di valori univoci in un intervallo in base a due criteri
Conta il numero di valori univoci in un intervallo in base a tre criteri
Conta il numero di valori univoci in un intervallo con codice VBA (per casi complessi/automatizzati)
Conta il numero di valori univoci in un intervallo in base a un criterio
Consideriamo un caso comune: si vuole contare quanti prodotti diversi sono stati venduti da Tom. Questo metodo è ideale per set di dati semplici, quando si desidera valutare l’univocità in base a una singola condizione, come le vendite effettuate da una persona specifica. È un approccio diretto, ma richiede un uso attento delle formule matrice.
Per questo scenario, inserisca la seguente formula in una cella vuota (ad esempio, la cella G2):
=SUM(IF(«Tom»=$C$2:$C$20,1/(COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20)),0))
Dopo aver digitato la formula, premere Ctrl + Maiusc + Invio (non semplicemente Invio) per confermarla come formula matrice. Le parentesi graffe appariranno automaticamente attorno alla formula nella Barra delle formule e il risultato sarà immediatamente visibile, come illustrato di seguito:
Nota:
- «Tom» è la condizione che si desidera utilizzare per filtrare i risultati. Per una maggiore flessibilità, è possibile sostituire «Tom» con un riferimento a un'altra cella (ad esempio, $F$2).
- $C$2:$C$20 contiene i nomi dei venditori da valutare.
- $A$2:$A$20 è la colonna dei prodotti per cui si desidera ottenere conteggi univoci.
- Se il Suo intervallo dati cambia, ricordi di aggiornare di conseguenza i riferimenti.
Suggerimento: Se utilizzi Excel 365 o Excel 2019 e versioni successive, puoi provare a usare le funzioni UNICO e FILTRA per ottenere formule più semplici.
Se si verifica un errore #DIV/0!, ricontrolla il criterio e assicurati che gli intervalli abbiano la stessa lunghezza.
Conta il numero di valori univoci in un intervallo in base a due date specifiche
Quando si deve trovare il numero di elementi univoci in un determinato Intervallo di date, ad esempio tutti i prodotti univoci venduti tra il 1/9/2016 e il 30/9/2016, è possibile applicare questo approccio. È particolarmente utile per analizzare andamenti nei dati relativi a determinati periodi, come mensili, trimestrali o Intervallo di date personalizzati. Presti tuttavia attenzione alla formattazione delle date: deve corrispondere ai valori di data presenti nel foglio di lavoro.
Inserisca la seguente formula in una cella vuota in cui desidera visualizzare il risultato:
=SUM(IF($D$2:$D$20=DATE(2016,9,1)),1/COUNTIFS( $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&DATE(2016,9,1))),0)
Dopo aver inserito la formula, premere Ctrl + Maiusc + Invio per eseguirla come formula matrice. Lo screenshot seguente mostra il risultato:
Nota:
- 2016,9,1 e 2016,9,30 sono le date di inizio e di fine. Puoi modificarle in base alle tue esigenze oppure utilizzare riferimenti a celle per creare filtri dinamici basati sulle date.
- $D$2:$D$20 contiene le date da verificare.
- $A$2:$A$20 è nuovamente la colonna contenente gli articoli o i prodotti che si desidera contare in modo univoco.
- Assicurati che le date siano memorizzate come date valide di Excel e non come stringhe di testo. Se il risultato non corrisponde alle aspettative, controlla la formattazione delle date e gli intervalli.
Suggerimento: Usa la funzione DATA(anno, mese, giorno) per evitare problemi legati alla formattazione regionale delle date. Quando utilizzi intervalli dinamici, ti consigliamo di ricorrere a intervalli denominati per una maggiore chiarezza.
Conta il numero di valori univoci in un intervallo in base a due criteri
Immagina di voler analizzare esclusivamente i prodotti venduti da Tom nel mese di settembre, combinando il nome con un intervallo di date per ottenere un conteggio univoco. Questo scenario è frequente nelle valutazioni delle performance su base temporale o nelle analisi segmentate. All’aumentare dei criteri, la formula diventa più complessa e l’attenzione all’accuratezza dei dati risulta ancora più cruciale.
Inserisca la formula seguente in una qualsiasi cella vuota, ad esempio H2:
=SUM(IF((«Tom»=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))),1/COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&DATE(2016,9,1))),0)
Dopo aver digitato la formula, confermarla con Ctrl + Maiusc + Invio. Il conteggio univoco apparirà immediatamente; si veda l’illustrazione seguente:
Note:
- «Tom» è il criterio relativo al nome, mentre «2016,9,1» e «2016,9,30» definiscono i limiti del Suo intervallo di date. Modifichi questi valori in base alle Sue esigenze oppure li renda dinamici utilizzando riferimenti a celle.
- $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 conteggiare.
- Gli intervalli devono avere tutti la stessa durata per evitare errori.
Se si desidera utilizzare condizioni “oppure”, ad esempio contare i prodotti univoci venduti da Tom o nella regione Sud, è possibile usare la seguente formula. Ciò consente condizioni di ricerca più ampie, sebbene i risultati possano sovrapporsi se i dati soddisfano entrambi i criteri:
=SOMMA(--(FREQUENZA(SE((«Tom»=$C$2:$C$20)+(«South»=$B$2:$B$20), COUNTIF($A$2:$A$20, "0))
Non dimenticare di premere Ctrl + Maiusc + Invio. I risultati saranno visibili come mostrato di seguito:
Suggerimento: quando si applicano criteri OR, prestare attenzione al possibile conteggio duplicato qualora lo stesso record soddisfi entrambe le condizioni. Con set di dati di grandi dimensioni, le prestazioni potrebbero risentirne.
Conta il numero di valori univoci in un intervallo in base a tre criteri
A volte l’analisi potrebbe richiedere tre o più condizioni, ad esempio per determinare i prodotti unici venduti da Tom solo nel mese di settembre nella regione Nord. Questo è comune nelle analisi multidimensionali Analisi dei dati per reportistica o approfondimenti mirati. È essenziale gestire con cura i riferimenti per gestire correttamente logiche composte.
Inserire questa formula matriciale in una cella vuota (ad esempio I2):
=SUM(IF((«Tom»=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))*(«North»=$B$2:$B$20),1/COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&DATE(2016,9,1), $B$2:$B$20, "North")),0)
Premere Ctrl + Maiusc + Invio per completare. Di seguito un risultato di esempio a titolo illustrativo:
Per condizioni avanzate, assicurati sempre che tutti gli intervalli siano coerenti e che i tipi di dati—come date e testo—siano corretti: eventuali disallineamenti potrebbero causare errori o risultati fuorvianti.
Suggerimenti:
- In caso di problemi di prestazioni con set di dati di grandi dimensioni, valuti la possibilità di suddividere la formula o di utilizzare la soluzione Tabella pivot di Excel.
- Utilizzare intervalli denominati o riferimenti a celle per tutti i criteri migliora la leggibilità delle formule e riduce gli errori.
- Per un uso frequente, valuti la possibilità di salvare queste formule in riferimenti a celle denominate o in funzioni personalizzate.
Conta il numero di valori univoci in un intervallo con Tabella pivot (Conteggio valori univoci, Excel 2013+)
Per gli utenti di Excel 2013 o versioni successive, le tabelle pivot offrono un’alternativa interattiva e priva di formule al metodo “Conta il numero di valori univoci in un intervallo per uno o più criteri”. La funzione Conteggio valori univoci permette di riepilogare e filtrare rapidamente set di dati di grandi dimensioni, rendendo questo approccio ideale per ambienti dinamici e orientati alla creazione di report. Si noti tuttavia che le versioni precedenti di Excel non supportano la funzione Conteggio valori univoci all’interno delle tabelle pivot.
Come utilizzare 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 dati" e poi fai clic su OK.
- Trascina il campo di cui desideri contare i valori univoci (ad esempio, Prodotto) nell’area Valori. Verrà visualizzato automaticamente come «Conteggio di...».
- Faccia clic sul campo nell’area Valori e selezioni Impostazioni campo.
- Nella finestra di dialogo a comparsa, scorri verso il basso e seleziona Conteggio distinti(questa opzione è disponibile solo in Excel 2013 o versioni successive ed è visibile quando la tabella pivot viene creata con l’opzione «Aggiungi questi dati al modello dati» attivata).
- Aggiungi i campi criterio (ad esempio, Venditore, Area geografica, Data) alle aree Filtro o Righe/Colonne per applicare condizioni singole o multiple.
- La tua tabella pivot mostrerà ora il conteggio univoco dei valori filtrati in base ai criteri selezionati.
Vantaggi: Altamente visivo, facile da regolare senza dover modificare formule e perfetto per la reportistica interattiva.
Limitazioni: Non disponibile in Excel 2010 o versioni precedenti; per aggiungere nuovi dati, è necessario aggiornare manualmente la tabella pivot.
Suggerimento pratico: Assicurati sempre che i dati in Dati di origine non contengano duplicati all’interno dello stesso record, a meno che non siano previsti. Se l’opzione Conteggio valori univoci non è visibile, ricrea la tabella pivot e seleziona l’opzione «Aggiungi questi dati al modello dati».
Conta il numero di valori univoci in un intervallo con codice VBA (per casi complessi o automatizzati)
A volte potrebbe essere necessario contare automaticamente il numero di valori univoci in un intervallo in base a diversi criteri, soprattutto quando si lavora con set di dati molto grandi o si ripete spesso la stessa analisi. In questi casi, una macro VBA è la soluzione ideale: elabora rapidamente logiche complesse – inclusi filtri con condizioni multiple – senza richiedere ulteriore intervento manuale dopo la configurazione iniziale. Tuttavia, VBA è più avanzato delle normali funzionalità di Excel e si rivolge quindi a utenti esperti nell’uso delle macro o con esigenze analitiche ricorrenti.
Passaggi operativi:
- Premi Alt + F11 per aprire l’editor VBA. Nell’editor, seleziona Inserisci > Modulo per creare un nuovo modulo.
- Copi 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- Chiuda l’editor VBA e torni al foglio di lavoro. Premere Alt + F8, selezionare CountUniqueWithCriteria ed eseguire la macro.
- Segui le istruzioni per definire gli intervalli e i criteri in base ai tuoi dati: il risultato verrà visualizzato sia nella cella selezionata che in una finestra di messaggio.
Spiegazione dei parametri e note:
- Attualmente questa macro è configurata per un solo criterio. Per estenderla a più criteri, modifichi la logica
If ... Thenall’interno del ciclo. - Salvate sempre la cartella di lavoro prima di eseguire le macro, poiché le modifiche apportate non possono essere annullate.
- Abilita le macro nelle impostazioni di Excel se riscontri errori di esecuzione.
- Questo metodo si rivela particolarmente efficace con dati voluminosi o aggiornati di frequente, quando le formule manuali diventerebbero complesse da gestire.
Vantaggi: Estremamente personalizzabile e automatizzabile, gestisce in modo efficiente set di dati ampi e in continua evoluzione. Ideale per flussi di lavoro avanzati o ripetitivi.
Svantaggi: Richiede l'abilitazione delle macro e gli utenti meno esperti potrebbero aver bisogno di un po’ di tempo per familiarizzare con le operazioni VBA.
Quando si lavora con conteggi di valori univoci basati su criteri, verificare sempre i riferimenti agli intervalli e assicurarsi che tutte le colonne dei criteri abbiano esattamente la stessa dimensione. Intervalli non allineati sono una causa frequente di errori o risultati imprecisi. Se le formule restituiscono risultati inaspettati, controllare la presenza di problemi di formattazione nascosti o di celle vuote. In scenari in cui le prestazioni sono critiche, le tabelle pivot e VBA offrono alternative più robuste rispetto alle formule matriciali. Scegliete la soluzione più adatta al vostro livello di competenza e alla complessità del set di dati. Ricordate che Kutools per Excel mette a disposizione strumenti aggiuntivi e scorciatoie in grado di semplificare molte di queste operazioni, aumentando ulteriormente l’efficienza nei workbook complessi.
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 oltre 300 funzionalità avanzate per aumentare la produttività e Risparmia tempo.Clicca qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab Porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede all’interno della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e risparmia centinaia di clic del mouse ogni giorno!
Tutti i componenti aggiuntivi di Kutools in un unico programma di installazione.
Kutools for Office è la suite che include componenti aggiuntivi per Excel, Word, Outlook e PowerPoint, oltre a Office Tab Pro: la soluzione ideale per i team che lavorano su diverse app di Office.
- Suite completa— componenti aggiuntivi per Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Un unico programma di installazione, una sola licenza— configurazione in pochi minuti (pronto per MSI)
- Funziona meglio insieme— produttività ottimizzata tra le app di Office
- Prova gratuita di 30 giorni con tutte le funzionalità— nessuna registrazione, nessuna carta di credito
- Miglior rapporto qualità-prezzo— risparmia rispetto all’acquisto dei singoli componenti aggiuntivi