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 e presentare tutti i risultati corrispondenti in una colonna, 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. Qui esploriamo due metodi efficienti.
Metodo 1: Utilizzo della funzione TEXTJOIN (Excel365 / 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) o 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.
- IF(..., $C$2:$C$21, "") restituisce il nome del prodotto se la riga corrisponde; altrimenti, restituisce un valore vuoto.
- TEXTJOIN(", ", TRUE, ...) combina tutti i nomi dei prodotti non vuoti in una cella, separati da ", ".
Metodo 2: Utilizzo di Kutools per Excel
Kutools per Excel offre una soluzione potente e semplice, consentendo 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, clicca su Kutools > Unisci e Dividi > Unione Avanzata Righe, vedi screenshot:
- Nella finestra di dialogo Unione Avanzata Righe, configura le seguenti opzioni:
- Scegli le intestazioni di colonna che contengono i tuoi criteri di corrispondenza (ad esempio, Venditore e Mese). Per ogni colonna selezionata, clicca su Chiave Primaria per definirle come condizioni di ricerca.
- Clicca sull'intestazione della colonna dove desideri i risultati combinati (ad esempio, Prodotto). Nella sezione Combina, seleziona il delimitatore preferito (ad esempio, virgola, spazio o separatore personalizzato).
- Infine, clicca sul pulsante OK.
Risultato: Kutools unirà immediatamente 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 un formato di colonna verticale, Excel offre diverse soluzioni potenti.
Metodo 1: Utilizzo di 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 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 nella schermata sotto:
- $A$2:$A$18=$E$2: Controlla se il Venditore corrisponde al valore nella cella E2.
- $B$2:$B$18=$F$2: Controlla se il Mese corrisponde al valore nella cella F2.
- * è un operatore logico AND (entrambe le condizioni devono essere vere).
- ROW($C$2:$C$18)-ROW($C$2)+1: Genera un numero di riga relativo per ciascun prodotto.
- SMALL(..., ROW(1:1)): Recupera l'n-esimo numero di riga corrispondente più piccolo (man mano che la formula viene trascinata verso il basso).
- INDEX(...): Restituisce il prodotto dalla riga corrispondente.
- IFERROR(..., ""): Restituisce una cella vuota se non ci sono altre corrispondenze.
Metodo 2: Utilizzo della funzione FILTRO (Excel365 / 2021)
Se stai usando 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 versare dinamicamente i risultati senza formule matrice complesse.
Copia o inserisci la seguente formula in una cella vuota, quindi premi il tasto Invio, tutti i record corrispondenti sono restituiti in base ai criteri multipli.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FILTER(...) restituisce tutti i valori da C2:C18 dove entrambe le condizioni sono soddisfatte.
- (A2:A18=E2)*(B2:B18=F2): Array logico che verifica corrispondenze per venditore e mese.
- "No match": 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 in cui lo spazio verticale è limitato. In questa sezione, esploreremo due metodi potenti.
Metodo 1: Utilizzo di 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), regoliamo la formula per restituire i risultati in una riga.
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), COLUMN(A1))), "")2. Premi 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: Controlla se il Venditore corrisponde.
- $B$2:$B$18=$F$2: Controlla se il Mese corrisponde.
- *: Logico AND—entrambe le condizioni devono essere vere.
- ROW($C$2:$C$18)-ROW($C$2)+1: Crea numeri di riga relativi.
- COLUMN(A1): Regola quale corrispondenza restituire, a seconda di quanto la formula è stata trascinata verso destra.
- IFERROR(...): Impedisce errori una volta esaurite le corrispondenze.
Metodo 2: Utilizzo della funzione FILTRO (Excel365 / 2021)
Copia o inserisci la seguente formula in una cella vuota, quindi premi il tasto Invio, tutti i valori corrispondenti sono estratti e posizionati in una riga. Vedi screenshot:
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FILTER(...): Recupera i valori corrispondenti dalla colonna C in base alle due condizioni.
- (A2:A18=E2)*(B2:B18=F2): Entrambe le condizioni devono essere vere.
- TRANSPOSE(...): Converte l'array verticale restituito da FILTER 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, una riga o all'interno di una singola cella.
- Per gli utenti di 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 in modo efficiente e accurato le ricerche basate su criteri multipli. Se sei interessato a esplorare ulteriori suggerimenti e trucchi di Excel, il nostro sito web offre migliaia di tutorial per aiutarti a padroneggiare Excel.
Altri articoli correlati:
- Restituisci Più Valori Corrispondenti In Una Singola Cella Separati 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 virgole, trattini, ecc… in una singola cella come mostrato nello screenshot seguente. Come possiamo ottenere e restituire più valori corrispondenti in una singola cella separata da virgole in Excel?
- Cerca Verticale E Restituisci Più Valori Corrispondenti In Google Sheets
- La normale funzione CERCA.VERT in Google Sheets può aiutarti a trovare e restituire il primo valore corrispondente trovato in base a un dato dato. Ma a volte, potresti aver bisogno di cercare e restituire tutti i valori corrispondenti come mostrato nello screenshot seguente. Hai qualche buon e facile modo per risolvere questo problema in Google Sheets?
- Cerca Verticale E Restituisci Più Valori Da Elenco A Discesa
- In Excel, come puoi cercare 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 illustrerò 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 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 e restituire il valore corrispondente tra due valori come mostrato nello screenshot seguente; come potresti affrontare questa sfida 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 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