Restituisci più valori corrispondenti in base a criteri multipli in Excel (Guida completa)
Gli utenti di Excel si trovano spesso di fronte a scenari in cui è necessario estrarre più valori che soddisfino contemporaneamente diversi criteri, presentando tutti i risultati corrispondenti in una colonna, in una riga o consolidati all'interno di una singola cella. Questa guida esplora metodi per tutte le versioni di Excel, oltre alla nuova funzione FILTRO disponibile in Excel 365 e 2021.
Restituisci più valori corrispondenti in base a criteri multipli in una singola cella
In Excel, estrarre più valori corrispondenti in base a criteri multipli all'interno di una singola cella è una sfida comune. Esploriamo qui due metodi efficienti.
Metodo 1: Utilizzando la funzione TEXTJOIN (Excel 365 / 2021, 2019)
Per ottenere tutti i valori corrispondenti in una singola cella con delimitatori, la funzione TEXTJOIN può esserti d'aiuto.
Inserisci o copia la seguente formula in una cella vuota, quindi premi il tasto Invio (Excel 2021 e Excel 365) oppure i tasti Ctrl + Maiusc + Invio in Excel 2019 per ottenere il risultato:
=TEXTJOIN(", ", TRUE, IF(($A$2:$A$18=E2)*($B$2:$B$18=F2), $C$2:$C$18, ""))
- ($A$2:$A$21=E2)*($B$2:$B$21=F2) verifica se ogni riga soddisfa entrambe le condizioni: “Venditore uguale a E2” e “Mese uguale a F2”. Se entrambe le condizioni sono soddisfatte, il risultato è 1; altrimenti, è 0. L'asterisco * significa che entrambe le condizioni devono essere vere.
- SE(..., $C$2:$C$21, "") restituisce il nome del prodotto se la riga corrisponde; altrimenti, restituisce una cella vuota.
- TEXTJOIN(", ", VERO, ...) combina tutti i nomi dei prodotti non vuoti in una cella, separati da ", ".
Metodo 2: Utilizzando Kutools per Excel
Kutools per Excel offre una soluzione potente e semplice, consentendoti di recuperare rapidamente e combinare più corrispondenze in una singola cella basata su criteri multipli senza formule complesse.
Dopo aver installato Kutools per Excel, procedi come segue:
- Seleziona l'intervallo di dati da cui desideri ottenere tutti i valori corrispondenti in base ai criteri.
- Quindi, fai clic su Kutools > Unisci & Dividi > Unione Avanzata Righe, vedi screenshot:
- Nella finestra di dialogo Unione Avanzata Righe, configura le seguenti opzioni:
- Scegli le intestazioni delle colonne che contengono i tuoi criteri di corrispondenza (ad esempio, Venditore e Mese). Per ogni colonna selezionata, fai clic su Chiave Primaria per definirle come condizioni di ricerca.
- Fai clic sull'intestazione della colonna dove desideri i risultati combinati (ad esempio, Prodotto). Dalla sezione Unisci, seleziona il delimitatore preferito (ad esempio, virgola, spazio o separatore personalizzato).
- Infine, fai clic sul pulsante OK.
Risultato: Kutools unirà istantaneamente tutti i valori corrispondenti in una singola cella per ogni combinazione unica di criteri.
Restituisci più valori corrispondenti in base a criteri multipli in una colonna
Quando hai bisogno di estrarre e visualizzare più record corrispondenti da un set di dati in base a diverse condizioni, restituendo i risultati in formato verticale a colonna, Excel offre diverse soluzioni potenti.
Metodo 1: Utilizzando una formula matrice (per tutte le versioni)
Puoi utilizzare la seguente formula matrice per restituire i risultati verticalmente in una colonna:
1. Copia o inserisci la seguente formula in una cella vuota:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), ROW(1:1))), "")2. Premi i tasti Ctrl + Maiusc + Invio per ottenere il primo risultato corrispondente, quindi seleziona la prima cella della formula e trascina la maniglia di riempimento verso il basso fino a quando non viene visualizzata una cella vuota, ora tutti i valori corrispondenti sono restituiti come mostrato nello screenshot seguente:
- $A$2:$A$18=$E$2: Verifica se il Venditore corrisponde al valore nella cella E2.
- $B$2:$B$18=$F$2: Verifica se il Mese corrisponde al valore nella cella F2.
- * è un operatore logico AND (entrambe le condizioni devono essere vere).
- RIF.RIGA($C$2:$C$18)-RIF.RIGA($C$2)+1: Genera un numero di riga relativo per ciascun prodotto.
- PICCOLO(..., RIF.RIGA(1:1)): Recupera l'n-esimo numero di riga corrispondente più piccolo (man mano che la formula viene trascinata verso il basso).
- INDICE(...): Restituisce il prodotto dalla riga corrispondente.
- SE.ERRORE(..., ""): Restituisce una cella vuota se non ci sono altre corrispondenze.
Metodo 2: Utilizzando la funzione FILTRO (Excel 365 / 2021)
Se stai utilizzando Excel 365 o Excel 2021, la funzione FILTRO è un'ottima scelta per restituire più risultati in base a criteri multipli, grazie alla sua semplicità, chiarezza e capacità di riversare dinamicamente i risultati senza formule matrice complesse.
Copia o inserisci la formula seguente in una cella vuota, quindi premi il tasto Invio, tutti i record corrispondenti verranno restituiti in base ai criteri multipli.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FILTRO(...) restituisce tutti i valori da C2:C18 dove entrambe le condizioni sono soddisfatte.
- (A2:A18=E2)*(B2:B18=F2): Array logico che verifica la corrispondenza del venditore e del mese.
- "Nessuna corrispondenza": Messaggio opzionale se non vengono trovati valori.
Restituisci più valori corrispondenti in base a criteri multipli in una riga
Gli utenti di Excel spesso hanno bisogno di estrarre più valori da un set di dati che soddisfano diverse condizioni e visualizzarli orizzontalmente (in una riga). Questo è utile per creare report dinamici, dashboard o tabelle di riepilogo dove lo spazio verticale è limitato. In questa sezione, esploreremo due metodi potenti.
Metodo 1: Utilizzando una formula matrice (per tutte le versioni)
Le tradizionali formule matrice permettono di estrarre più valori corrispondenti utilizzando le funzioni INDICE, PICCOLO, SE e COLONNA. A differenza dell'estrazione verticale (basata su colonne), adattiamo la formula per restituire i risultati in una riga.
1. Copia o inserisci la formula seguente in una cella vuota:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), COLUMN(A1))), "")2. Premi i tasti Ctrl + Maiusc + Invio per ottenere il primo risultato corrispondente, quindi seleziona la prima cella della formula e trascina la formula verso destra attraverso le colonne per recuperare tutti i risultati.
- $A$2:$A$18=$E$2: Verifica se il Venditore corrisponde.
- $B$2:$B$18=$F$2: Verifica se il Mese corrisponde.
- *: Logica AND - entrambe le condizioni devono essere vere.
- RIF.RIGA($C$2:$C$18)-RIF.RIGA($C$2)+1: Crea numeri di riga relativi.
- COLONNA(A1): Regola quale corrispondenza restituire, a seconda di quanto la formula è stata trascinata verso destra.
- SE.ERRORE(...): Impedisce errori una volta esaurite le corrispondenze.
Metodo 2: Utilizzando la funzione FILTRO (Excel 365 / 2021)
Copia o inserisci la formula seguente in una cella vuota, quindi premi il tasto Invio, tutti i valori corrispondenti vengono estratti e posizionati in una riga. Vedi screenshot:
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FILTRO(...): Recupera i valori corrispondenti dalla colonna C in base alle due condizioni.
- (A2:A18=E2)*(B2:B18=F2): Entrambe le condizioni devono essere vere.
- TRASPOSTA(...): Converte l'array verticale restituito da FILTRO in un array orizzontale.
🔚 Conclusione
Recuperare più valori corrispondenti in base a criteri multipli in Excel può essere realizzato in diversi modi, a seconda di come si desidera visualizzare i risultati - sia in una colonna, in una riga o all'interno di una singola cella.
- Per gli utenti con Excel 365 o Excel 2021, la funzione FILTRO offre una soluzione moderna, dinamica ed elegante che minimizza la complessità.
- Per coloro che utilizzano versioni precedenti, le formule matrice rimangono strumenti potenti, anche se richiedono un po' più di configurazione e attenzione.
- Inoltre, se desideri consolidare i risultati in una singola cella o preferisci una soluzione senza codice, la funzione TEXTJOIN o strumenti di terze parti come Kutools per Excel possono semplificare notevolmente il processo.
Scegli il metodo che meglio si adatta alla tua versione di Excel e al tuo layout preferito, e sarai ben equipaggiato per gestire efficacemente e accuratamente le ricerche a criteri multipli. Se sei interessato a esplorare altri suggerimenti e trucchi di Excel, il nostro sito web offre migliaia di tutorial per aiutarti a padroneggiare Excel.
Altri articoli correlati:
- Restituisci più valori di ricerca in una cella separata da virgole
- In Excel, possiamo applicare la funzione CERCA.VERT per restituire il primo valore corrispondente trovato nelle celle di una tabella, ma a volte dobbiamo estrarre tutti i valori corrispondenti e poi separarli con un delimitatore specifico, come virgola, trattino, ecc... in una singola cella come mostrato nello screenshot seguente. Come possiamo ottenere e restituire più valori di ricerca in una cella separata da virgole in Excel?
- Cerca verticale e restituisci più valori corrispondenti contemporaneamente in Google Fogli
- La normale funzione CERCA.VERT in Google Fogli può aiutarti a trovare e restituire il primo valore corrispondente in base a un dato dato. Ma, a volte, potresti aver bisogno di cercare verticalmente e restituire tutti i valori corrispondenti come mostrato nello screenshot seguente. Hai qualche buon metodo semplice per risolvere questo compito in Google Fogli?
- Cerca verticale e restituisci più valori da un elenco a discesa
- In Excel, come puoi cercare verticalmente e restituire più valori corrispondenti da un elenco a discesa, il che significa che quando scegli un elemento dall'elenco a discesa, tutti i suoi valori relativi vengono visualizzati contemporaneamente come mostrato nello screenshot seguente. In questo articolo, ti presenterò la soluzione passo dopo passo.
- Cerca verticale e restituisci più valori verticalmente in Excel
- Normalmente, puoi usare la funzione CERCA.VERT per ottenere il primo valore corrispondente, ma a volte vuoi restituire tutti i record corrispondenti in base a un criterio specifico. In questo articolo, parlerò di come cercare verticalmente e restituire tutti i valori corrispondenti verticalmente, orizzontalmente o in una singola cella.
- Cerca verticale e restituisci dati corrispondenti tra due valori in Excel
- In Excel, possiamo applicare la normale funzione CERCA.VERT per ottenere il valore corrispondente in base a un dato dato. Ma, a volte, vogliamo cercare verticalmente e restituire il valore corrispondente tra due valori come mostrato nello screenshot seguente, come potresti affrontare questo compito in Excel?
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 risparmiare tempo. Clicca qui per ottenere la funzione 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 della stessa finestra, invece che in nuove finestre.
- Aumenta la tua produttività del50% e riduce centinaia di clic del mouse ogni giorno!