Note: The other languages of the website are Google-translated. Back to English

Come estrarre valori univoci in base a criteri in Excel?

Supponiamo che tu abbia l'intervallo di dati sinistro che desideri elencare solo i nomi univoci della colonna B in base a un criterio specifico della colonna A per ottenere il risultato come mostrato nell'immagine sottostante. Come potresti gestire questa attività in Excel in modo rapido e semplice?

Estrai valori univoci in base a criteri con formula di matrice

Estrai valori univoci in base a più criteri con la formula di matrice

Estrai valori univoci da un elenco di celle con una funzione utile

 

Estrai valori univoci in base a criteri con formula di matrice

Per risolvere questo lavoro, puoi applicare una formula di matrice complessa, per favore fai come segue:

1. Immettere la formula seguente in una cella vuota in cui si desidera elencare il risultato dell'estrazione, in questo esempio, lo inserirò nella cella E2, quindi premere Maiusc + Ctrl + Invio chiavi per ottenere il primo valore univoco.

=IFERROR(INDEX($B$2:$B$15, MATCH(0, IF($D$2=$A$2:$A$15, COUNTIF($E$1:$E1, $B$2:$B$15), ""), 0)),"")

2. Quindi, trascina il quadratino di riempimento verso il basso sulle celle finché non vengono visualizzate celle vuote e ora sono stati elencati tutti i valori univoci basati sul criterio specifico, vedi screenshot:

NOTA: Nella formula precedente: B2: B15 è l'intervallo di colonne che contiene i valori univoci da cui si desidera estrarre, A2: A15 è la colonna che contiene il criterio su cui ti sei basato, D2 indica il criterio su cui si desidera elencare i valori univoci in base a, e E1 è la cella sopra la formula inserita.

Estrai valori univoci in base a più criteri con la formula di matrice

Se vuoi estrarre i valori univoci in base a due condizioni, ecco un'altra formula di matrice che può farti un favore, per favore fai come segue:

1. Immettere la formula seguente in una cella vuota in cui si desidera elencare i valori univoci, in questo esempio, la inserirò nella cella G2, quindi premere Maiusc + Ctrl + Invio chiavi per ottenere il primo valore univoco.

=IFERROR(INDEX($C$2:$C$15,MATCH(0,COUNTIF(G1:$G$1,$C$2:$C$15)+IF($A$2:$A$15<>$E$2,1,0)+IF($B$2:$B$15<>$F$2,1,0),0)),"")

2. Quindi, trascina il quadratino di riempimento verso il basso sulle celle finché non vengono visualizzate celle vuote e ora sono stati elencati tutti i valori univoci basati sulle due condizioni specifiche, vedi screenshot:

NOTA: Nella formula precedente: C2: C15 è l'intervallo di colonne che contiene i valori univoci da cui si desidera estrarre, A2: A15 e E2 sono il primo intervallo con i criteri su cui desideri estrarre valori univoci in base, B2: B15 e F2 sono il secondo intervallo con i criteri su cui si desidera estrarre valori univoci basati su e G1 è la cella sopra la formula inserita.

Estrai valori univoci da un elenco di celle con una funzione utile

A volte, vuoi solo estrarre i valori univoci da un elenco di celle, qui, ti consiglierò uno strumento utile-Kutools for Excel, Con la sua Estrai celle con valori univoci (includi il primo duplicato) utility, puoi estrarre rapidamente i valori univoci.

NOTA:Per applicare questo Estrai celle con valori univoci (includi il primo duplicato), in primo luogo, dovresti scaricare il file Kutools for Excele quindi applica la funzione in modo rapido e semplice.

Dopo l'installazione Kutools for Excel, per favore fai come questo:

1. Fare clic su una cella in cui si desidera visualizzare il risultato. (Note:: Non fare clic su una cella nella prima riga.)

2. Quindi fare clic Kutools > formula Helper > formula Helper, vedi screenshot:

3. Nel Formule Helper finestra di dialogo, eseguire le seguenti operazioni:

  • Seleziona Testo opzione dal Formula Tipologia menu `A tendina;
  • Quindi scegli Estrai celle con valori univoci (includi il primo duplicato) dal Scegli una fromula casella di riepilogo;
  • In destra Input di argomenti sezione, seleziona un elenco di celle da cui desideri estrarre valori univoci.

4. Quindi fare clic Ok pulsante, il primo risultato viene visualizzato nella cella, quindi seleziona la cella e trascina il quadratino di riempimento sulle celle in cui desideri elencare tutti i valori univoci finché non vengono visualizzate celle vuote, vedi screenshot:

Scarica subito Kutools per Excel!


Articoli più relativi:

  • Contare il numero di valori univoci e distinti da un elenco
  • Supponendo che tu abbia un lungo elenco di valori con alcuni elementi duplicati, ora, vuoi contare il numero di valori univoci (i valori che compaiono nell'elenco solo una volta) o valori distinti (tutti i diversi valori nell'elenco, significa unico valori + 1 ° valore duplicato) in una colonna come mostrato nell'immagine a sinistra. In questo articolo, parlerò di come affrontare questo lavoro in Excel.
  • Sommare i valori univoci in base ai criteri in Excel
  • Ad esempio, ho un intervallo di dati che contiene le colonne Nome e Ordine, ora, per sommare solo valori univoci nella colonna Ordine in base alla colonna Nome come mostrato nella seguente schermata. Come risolvere questa attività rapidamente e facilmente in Excel?
  • Valori univoci concatenati in Excel
  • Se ho un lungo elenco di valori che è stato popolato con alcuni dati duplicati, ora voglio trovare solo i valori univoci e quindi concatenarli in una singola cella. Come posso affrontare questo problema rapidamente e facilmente in Excel?

I migliori strumenti per la produttività in ufficio

Kutools per Excel risolve la maggior parte dei tuoi problemi e aumenta la tua produttività dell'80%

  • Riutilizzo: Inserisci rapidamente formule complesse, grafici e tutto ciò che hai usato prima; Crittografa celle con password; Crea mailing list e invia email ...
  • Bar Super Formula (modifica facilmente più righe di testo e formula); Layout di lettura (leggi e modifica facilmente un gran numero di celle); Incolla su intervallo filtrato...
  • Unisci celle / righe / colonne senza perdere dati; Contenuto delle celle divise; Combina righe / colonne duplicate... Impedisci celle duplicate; Confronta intervalli...
  • Seleziona Duplica o Unico Righe; Seleziona Righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molte cartelle di lavoro; Selezione casuale ...
  • Copia esatta Più celle senza modificare il riferimento della formula; Riferimenti di creazione automatica a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
  • Estrai testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare totali parziali di paging; Converti contenuto e commenti tra celle...
  • Super filtro (salva e applica schemi di filtri ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale in grassetto, corsivo ...
  • Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle in base a colonne chiave; Suddividi i dati in più fogli; Conversione in batch xls, xlsx e PDF...
  • Più di 300 potenti funzionalità. Supporta Office/Excel 2007-2021 e 365. Supporta tutte le lingue. Facile implementazione nella tua azienda o organizzazione. Funzionalità complete Prova gratuita di 30 giorni. Garanzia di rimborso di 60 giorni.
scheda kte 201905

Scheda Office porta l'interfaccia a schede a Office e semplifica notevolmente il lavoro

  • 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 della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
fondo officetab
Commenti (40)
Ancora nessuna valutazione. Puoi essere il primo a votare!
Questo commento è stato fatto dal moderatore sul sito
Ciao, grazie per questo tutorial, funziona perfettamente. Sto cercando di modificarlo per funzionare con una condizione OR, ma non sembra funzionare: è possibile? es. =INDICE($B$2:$B$17, CONFRONTA(0, SE(OR($D$2=$A$2:$A$17,$D$2=$B$2:$B$17), COUNTIF($E$1 :$E1, $B$2:$B$17), ""), 0))
Questo commento è stato fatto dal moderatore sul sito
Grazie per questo tutorial! Sto anche cercando di modificare la formula, come il commentatore sopra, ma con una condizione AND in modo che soddisfi un altro criterio condizionale (ad esempio, per questo esempio, mi piacerebbe vedere solo cose al di sopra di una certa soglia). Potete per favore consigliare? Grazie!
Questo commento è stato fatto dal moderatore sul sito
Ehi, un modo per farlo: sostituisci la formula if con sumproduct((condition1=rng1)+(condition2=rng2))*countif(... Ha funzionato per me. Buona fortuna! Sostituendo il + con un * puoi rendilo una condizione OR, ma prenditi cura delle parentesi!
Questo commento è stato fatto dal moderatore sul sito
Grazie, questo è fantastico!
Questo commento è stato fatto dal moderatore sul sito
Grazie per questo ho provato questo e sembra funzionare bene a intermittenza. Il problema che continua a ripetersi è che a volte viene restituito solo il primo valore corrispondente e viene quindi duplicato quando si trascina verso il basso per restituire tutti i valori corrispondenti. Come posso prevenire questo? Eventuali suggerimenti?
Questo commento è stato fatto dal moderatore sul sito
Funziona davvero bene, ma ogni volta che il valore che sta inserendo viene duplicato, inserisce il valore solo una volta. Ad esempio, se la tua lista conteneva due Lucy, porta solo una Lucy al nuovo tavolo. C'è un modo per risolvere questo problema?
Questo commento è stato fatto dal moderatore sul sito
Quando si utilizza questa formula continua a ripetere il primo valore, come si fa a interrompere e fornire l'elenco di valori che è uguale al prodotto in D2?
Questo commento è stato fatto dal moderatore sul sito
Ciao, per impedire che il primo valore si ripeta mentre trascini verso il basso devi CONTA.SE la cella SOPRA la cella in cui stai inserendo la formula. Ad esempio, se la formula sta andando in E2 devi digitare countif($E$1:$E1...
Questo commento è stato fatto dal moderatore sul sito
Ciao Ryan. Le formule funzionano alla grande, tuttavia quando si trascina verso il basso il primo valore continua a ripetersi. Mi sono assicurato che COUNTIF faccia riferimento alla cella SOPRA la cella con la formula, ma ripeta ancora il primo valore durante il trascinamento verso il basso? (ad es. se la formula dell'array è in C2, COUNTIF punta alla cella $C$1:$C$1)
Questo commento è stato fatto dal moderatore sul sito
Ciao Ryan. Le formule funzionano alla grande, tuttavia quando si trascina verso il basso il primo valore continua a ripetersi. Mi sono assicurato che COUNTIF faccia riferimento alla cella SOPRA la cella con la formula, ma ripeta ancora il primo valore durante il trascinamento verso il basso? (ad es. se la formula dell'array è in C2, COUNTIF punta alla cella $C$1:$C$1)
Questo commento è stato fatto dal moderatore sul sito
Probabilmente non funziona perché hai bloccato le celle - Prova a sostituire $C$1:$C$1 con $C$1:$C1
Questo commento è stato fatto dal moderatore sul sito
questo è stato di grande aiuto, ma continuo a ricevere il doppio di tutti i nomi come questo:
Dai, Jane
Dai, Jane
Aspirapolvere, Tom
Aspirapolvere, Tom

Come posso fermarlo?
Questo commento è stato fatto dal moderatore sul sito
Ciao, ricevo l'errore "#N/A" in "Funzione Match", puoi per favore guidare?
Questo commento è stato fatto dal moderatore sul sito
=IF(AL2="AP","AP",IF(AK2="AD","AD",IF(Z2>500000,"Yes","No"))) Voglio che "tutte le condizioni" siano soddisfatte per dire di sì... excel riflettendo l'errore in questa formula... si prega di consigliare
Questo commento è stato fatto dal moderatore sul sito
in realtà voglio che la cella rifletta "SÌ" se (AL2="AP" e AK2="AD" e Z2>500000)
Questo commento è stato fatto dal moderatore sul sito
Ricevo un errore #N/D nella funzione Abbina con questa formula. Puoi aiutarmi per favore?
Questo commento è stato fatto dal moderatore sul sito
Ciao, ricevo l'errore "#N/A" in "Funzione Match", puoi per favore guidare?
Questo commento è stato fatto dal moderatore sul sito
Se ricevi l'errore #N/D, vai alla tua formula e usa Ctrl + Maiusc + Invio invece di Invio.
Questo commento è stato fatto dal moderatore sul sito
Ottengo 0 invece dei risultati previsti, la formula funziona benissimo per i dati nello stesso foglio, hai qualche soluzione per i dati in un foglio diverso?

questa è la mia formula

=IFERROR(INDEX('Switching Data'!$B$7:$B$204,MATCH(0,IF($A$2='Switching Data'!$A$7:$A$204,COUNTIF($A$4:A4,'Switching Data'!$B$7:$B$204),""),0)),0)
Questo commento è stato fatto dal moderatore sul sito
Ciao, Gon,
Dopo aver inserito la formula, dovresti premere insieme i tasti Ctrl + Maiusc + Invio, non solo il tasto Invio.
Si prega di provare, grazie!
Questo commento è stato fatto dal moderatore sul sito
Ciao Gon, spero che tu stia bene. Mi chiedo se puoi risolvere questo problema. Ricevo lo stesso errore quando la formula proviene da un foglio diverso. Apprezzerò condividere la soluzione se l'hai ottenuta.
Questo commento è stato fatto dal moderatore sul sito
Grazie!
Questo commento è stato fatto dal moderatore sul sito
Come potrei ottenere questa formula per restituire ciascuno dei duplicati anziché uno di ciascuno dei nomi? Ad esempio, nell'esempio sopra, come potrei ottenere la colonna dei risultati (B:B) per restituire Lucy, Ruby, Anny, Jose, Lucy, Anny, Tom? Lo sto usando come strumento di budget che estrae riepiloghi di account specifici da una contabilità generale. Tuttavia, molti degli importi e delle descrizioni delle transazioni sono duplicati nella contabilità generale. Una volta estratto il primo dei valori duplicati, non ne vengono più estratti.
Questo commento è stato fatto dal moderatore sul sito
Ciao, Gio,
Per estrarre tutti i valori corrispondenti in base a criteri di cella specifici, la seguente formula di matrice può aiutarti, vedi screenshot:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

Dopo aver inserito la formula, premere insieme i tasti Maiusc + Ctrl + Invio per ottenere il risultato corretto, quindi trascinare il quadratino di riempimento verso il basso per ottenere tutti i valori.
Spero che questo possa aiutarti, grazie!
Questo commento è stato fatto dal moderatore sul sito
Fin qui tutto bene. Sono in grado di duplicare i risultati nel foglio di prova, apportare modifiche all'array e quindi correggere la formula per tenere conto delle modifiche apportate. Ho intenzione di spostarlo nel master sheet oggi e vedere come funziona. Grazie per l'aiuto!
Questo commento è stato fatto dal moderatore sul sito
Ok, quindi funziona nella cartella di lavoro principale. C'è un'eccezione di cui non sono stato in grado di determinare la causa: se l'array (nel mio caso, la contabilità generale che avevo a partire dalla riga 3) non inizia nella riga 1, i valori restituiti non sono corretti. Cosa causa questo problema e quale termine nella formula lo risolve? Grazie ancora per il tuo aiuto con questo!
Questo commento è stato fatto dal moderatore sul sito
Ultima domanda: se voglio che la colonna dei risultati restituisca tutti i valori non associati a KTE o KTO (quindi, D:D sarebbe Tom, Nocol, Lily, Angelina, Genna), come lo farei?
Questo commento è stato fatto dal moderatore sul sito
Per me la formula non funziona. Premo ctrl shift invio e ricevo ancora un errore N/D. Vorrei aggiungere che ho preparato esattamente gli stessi dati del tutorial. Qual è il motivo per cui non funziona?
Questo commento è stato fatto dal moderatore sul sito
Questo ha funzionato alla grande per me con un valore di ricerca specifico. Tuttavia, se volessi utilizzare un carattere jolly per cercare valori parziali, come lo farei? Ad esempio, se volessi cercare tutti i nomi associati a KT?

Sto usando questa funzione per cercare celle che contengono più testo. Ad esempio se ogni prodotto avesse anche un sottoprodotto all'interno della stessa cella ma stavo solo cercando i nomi associati al sottoprodotto "elfo".

KTE - elfo
KTE-palla
KTE - pianoforte
KTO - elfo
KTO-palla
KTO - pianoforte
Questo commento è stato fatto dal moderatore sul sito
C'è un modo per farlo funzionare mentre CONSENTE valori duplicati? Ad esempio, voglio che tutte le istanze di Lucy siano elencate nei risultati.
Questo commento è stato fatto dal moderatore sul sito
Ciao, Costantino,
Per estrarre tutti i valori corrispondenti inclusi i duplicati in base a criteri di cella specifici, la seguente formula di matrice può aiutarti, vedi screenshot:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

Dopo aver inserito la formula, premere insieme i tasti Maiusc + Ctrl + Invio per ottenere il risultato corretto, quindi trascinare il quadratino di riempimento verso il basso per ottenere tutti i valori.
Spero che questo possa aiutarti, grazie!
Non ci sono ancora commenti pubblicati qui
Carica Altre
Lasciate i vostri commenti
Pubblicazione come ospite
×
Valuta questo post:
0   Personaggi
Posizioni suggerite

Seguici

Copyright © 2009 - www.extendoffice.com. | Tutti i diritti riservati. Offerto da ExtendOffice, | Mappa del sito
Microsoft e il logo Office sono marchi o marchi registrati di Microsoft Corporation negli Stati Uniti e / o in altri paesi.
Protetto da Sectigo SSL