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

Come restituire più valori corrispondenti in base a uno o più criteri in Excel?

Normalmente, cercare un valore specifico e restituire l'elemento corrispondente è facile per la maggior parte di noi utilizzando la funzione CERCA.VERT. Ma hai mai provato a restituire più valori corrispondenti in base a uno o più criteri come mostrato nella seguente schermata? In questo articolo, introdurrò alcune formule per risolvere questo compito complesso in Excel.

Restituisce più valori corrispondenti in base a uno o più criteri con formule di matrice


Restituisce più valori corrispondenti in base a uno o più criteri con formule di matrice

Ad esempio, voglio estrarre tutti i nomi la cui età è 28 e provengono dagli Stati Uniti, applica la seguente formula:

1. Copia o inserisci la formula seguente in una cella vuota in cui desideri individuare il risultato:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Note:: Nella formula sopra, B2: B11 è la colonna da cui viene restituito il valore corrispondente; F2, C2: C11 sono la prima condizione e i dati della colonna che contengono la prima condizione; G2, D2: D11 sono la seconda condizione e i dati della colonna che contengono questa condizione, per favore modificali secondo le tue necessità.

2. Quindi, premere CTRL + MAIUSC + INVIO chiavi per ottenere il primo risultato corrispondente, quindi selezionare la prima cella della formula e trascinare il quadratino di riempimento sulle celle fino a quando non viene visualizzato il valore di errore, ora, tutti i valori corrispondenti vengono restituiti come mostrato nell'immagine sottostante:

Suggerimenti:: Se devi solo restituire tutti i valori corrispondenti in base a una condizione, applica la seguente formula di matrice:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


Articoli più relativi:

  • Restituisce più valori di ricerca in una cella separata da virgole
  • In Excel, possiamo applicare la funzione CERCA.VERT per restituire il primo valore corrispondente dalle celle di una tabella, ma, a volte, dobbiamo estrarre tutti i valori corrispondenti e quindi separati da un delimitatore specifico, come virgola, trattino, ecc ... in un unico cella come mostrato nella seguente schermata. Come possiamo ottenere e restituire più valori di ricerca in una cella separata da virgole in Excel?
  • Vlookup e restituisci più valori corrispondenti contemporaneamente nel foglio Google
  • La normale funzione Vlookup nel foglio di Google può aiutarti a trovare e restituire il primo valore corrispondente in base a un dato dato. Ma, a volte, potrebbe essere necessario visualizzare e restituire tutti i valori corrispondenti come mostrato nella seguente schermata. Hai qualche modo semplice e buono per risolvere questo compito nel foglio di Google?
  • Vlookup e restituisce più valori dall'elenco a discesa
  • In Excel, come potresti visualizzare 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 nella seguente schermata. In questo articolo, introdurrò la soluzione passo dopo passo.
  • Vlookup e restituisce più valori verticalmente in Excel
  • Normalmente, puoi usare la funzione Vlookup 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 visualizzare e restituire tutti i valori corrispondenti verticalmente, orizzontalmente o in una singola cella.
  • Vlookup e restituisce i dati corrispondenti tra due valori in Excel
  • In Excel, possiamo applicare la normale funzione Vlookup per ottenere il valore corrispondente in base a un dato dato. Ma, a volte, vogliamo visualizzare e restituire il valore corrispondente tra due valori come mostrato nella seguente schermata, come potresti gestire questa attività in Excel?

 


  • 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 e conservazione dei dati; Contenuto delle celle divise; Combina righe duplicate e somma / media... 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 ...
  • Formule preferite e di inserimento rapido, Intervalli, grafici e immagini; Crittografa celle con password; Crea mailing list e invia email ...
  • 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...
  • Raggruppamento tabelle pivot per numero della settimana, giorno della settimana e altro ... Mostra celle sbloccate e bloccate da diversi colori; Evidenzia le celle che hanno formula / nome...
scheda kte 201905
  • 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 (25)
Ancora nessuna valutazione. Puoi essere il primo a votare!
Questo commento è stato fatto dal moderatore sul sito
Ho provato questa stessa identica formula; copiato al 100%. L'unica cosa che ho cambiato sono stati i dati che venivano abbinati e restituiti. Quando utilizzo questa formula, Excel dice "Hai inserito troppi argomenti per questa funzione).=INDEX('Rapporto volume 2020'!$B$3:$B$100,PICCOLO(IF(COUNTIF($A$1,'Volume 2020' Report'!$A$3:$A$100)*COUNTIF($A$3,'Rapporto sul volume 2020'!$D$3:$D$100),ROW('Rapporto sul volume 2020'!$A$3:$G$100)- MIN(ROW('Rapporto volume 2020'!$A$3:$G$100))+1,"0"),ROW(A1),COLONNA(A1))
Questo commento è stato fatto dal moderatore sul sito
Ciao, potresti fornire i tuoi dati e l'errore di formula come schermate qui?
Questo commento è stato fatto dal moderatore sul sito
Ciao, come posso usarlo per la condizione orizzontale.
Questo commento è stato fatto dal moderatore sul sito
Qual è lo "0" dopo il +1 nella formula? Quello non è nell'esempio.
Questo commento è stato fatto dal moderatore sul sito
Ciao avevo provato la stessa formula. sto ottenendo risultati ma quando si fornisce CSE non fornisce risposte multiple
Questo commento è stato fatto dal moderatore sul sito

Questo commento è stato fatto dal moderatore sul sito
Per quanto riguarda la restituzione di più valori corrispondenti in base a uno o più criteri con formule di matrice: perché se ho i dati altrove tranne che a partire da A1 non funziona anche se aggiorno tutti i riferimenti di cella nella formula?
Questo commento è stato fatto dal moderatore sul sito
Nel primo esempio, quale modifica alla formula servirebbe per restituire tutti coloro che hanno meno di 28 anni?
Questo commento è stato fatto dal moderatore sul sito
Ciao,

Mi chiedevo se fosse possibile inserire un 2° criterio ma dello stesso intervallo del 1° criterio,

Ad esempio con l'esempio usato sopra vorrei cercare i nomi di persone sia dall'America che dalla Francia Quindi la cella F3 avrebbe Francia, Scarlett e Andrew popolerebbero anche nell'elenco nella colonna G

Grazie per l'assistenza in anticipo.
Questo commento è stato fatto dal moderatore sul sito
Ciao Nick,

Felice di aiutare. Se vuoi ottenere i nomi di persone sia dall'America che dalla Francia, ti consiglio di utilizzare la nostra formula due volte per ottenere il risultato. Si prega di vedere lo screenshot, In F2 e G2 sono i valori "Stati Uniti" e "Francia". Applica la formula =IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1 ), ROW(1:1))),"" ) per ottenere i risultati per l'America. E applica la formula =IFERROR(INDEX($B$2:$B$11, SMALL(IF($G$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+ 1), ROW(1:1))),"" ) per ottenere i risultati per la Francia. È semplice. Per favore, prova.

Cordiali saluti,
Mandy
Questo commento è stato fatto dal moderatore sul sito
Quando uso la seconda formula e trascino verso il basso, non viene visualizzato nulla. Il risultato della formula (fx) dice che dovrebbe restituire qualcosa ma è vuoto. Come lo correggo?
Questo commento è stato fatto dal moderatore sul sito
Ciao Alisia,

Felice di aiutare. Ho provato la seconda formula nell'articolo e ho trascinato la formula verso il basso, il resto dei risultati è stato restituito. Penso che ci possano essere due ragioni per il tuo problema. Innanzitutto, forse ti dimentichi di premere i tasti Ctrl + Maiusc + Invio per inserire la formula. In secondo luogo, il risultato corrispondente è solo uno, quindi non vengono restituiti altri risultati. Si prega di avere un assegno.

Cordiali saluti,
Mandy
Questo commento è stato fatto dal moderatore sul sito
Ciao,
ho provato a usare la formula e genera un valore di 0 o l'immagine allegata
Questo commento è stato fatto dal moderatore sul sito
Ciao, Milku
Il tuo screenshot mostrava il software WPS della versione MAC, quindi non sono sicuro che la nostra formula sia disponibile.
Ho caricato un file Excel qui, puoi provare a vedere se può calcolare correttamente nel tuo ambiente.
.
Questo commento è stato fatto dal moderatore sul sito
Ciao,
cosa sarebbe necessario per espandere la prima formula nel caso seguente:
Alcuni ID sono vuoti (ad es. la cella A5 è vuota) e vorrei una condizione aggiuntiva che emette righe solo quando gli ID non sono vuoti. (Quindi l'output dovrebbe quindi essere James e Abdul.
Grazie!
Questo commento è stato fatto dal moderatore sul sito
Ciao, Jo,
Per risolvere il tuo problema, applica la seguente formula:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Per favore, provaci, spero che possa aiutarti!
Questo commento è stato fatto dal moderatore sul sito
Ciao,

se nella cella H1 scrivo "Nome" e volessi collegarlo alla formula, come funzionerebbe?
Quindi potrei scrivere "ID" nella cella H1 e ottenere automaticamente come risultato: AA1004; DD1009; PP1023 (per la prima formula)

Grazie in anticipo!
Questo commento è stato fatto dal moderatore sul sito
Ciao, Marie
Scusa, non riesco a capire il punto del tuo primo problema, potresti spiegare il tuo problema in modo più chiaro e dettagliato? Oppure puoi inserire uno screenshot qui per descrivere il tuo problema.
Per quanto riguarda la seconda domanda, devi solo cambiare il riferimento di cella in questo modo:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Ricordati di premere CTRL + MAIUSC + INVIO chiavi insieme.
Per favore, prova, spero che possa aiutarti!
Questo commento è stato fatto dal moderatore sul sito
Ehi, grazie per la formula. Ha funzionato per valori / testo "fissi" come criteri. Tuttavia, uno dei criteri che sto cercando di utilizzare è una condizione (values ​​<>0 ), ma non funziona con la formula descritta. Ragazzi, sapete cosa dovrei cambiare per adattare la formula in modo da poter avere una condizione come uno dei criteri, per favore?

Migliore,

John
Questo commento è stato fatto dal moderatore sul sito
Ciao, Marco
Per risolvere il tuo problema, consulta questo articolo:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Ci sono alcune spiegazioni dettagliate di questo compito. Devi solo cambiare i criteri con i tuoi.
.
Questo commento è stato fatto dal moderatore sul sito
Ciao,

Innanzitutto, grazie per aver condiviso!

Potete per favore fornire una soluzione al caso seguente:

Ho 3 colonne (A: contenente informazioni di riferimento, B: contenente informazioni da cercare, C: risultato della ricerca)

L'URL dell'immagine è fornito di seguito

https://ibb.co/VHCd09K

Colonna A ------------------------- Colonna B ------------ Colonna C
Nome file --------------------------Nome----------------Nome file, Nome documento, Nome elemento, Nome
Elemento modificato-----------------Elemento--------------Elemento modificato, Nome elemento, ID elemento
Posizione della colonna
Nome documento
Nome elemento
Nome
Categoria
Garanzia
pendenza
ID elemento

Quello di cui ho bisogno è cercare nella colonna A qualsiasi corrispondenza parziale con la cella B2 (Nome) o B3 (Elemento) e ottenere il risultato in una cella,

Grazie, Behzad
Questo commento è stato fatto dal moderatore sul sito
Ciao Behzad
Forse la funzione definita dall'utente di seguito può aiutarti.
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


Dopo aver copiato e incollato questo codice, quindi utilizzare questa formula:=ConcatPartLookUp(B2,$A$2:$A$8) per ottenere il risultato di cui hai bisogno.
Per favore, prova, spero che possa aiutarti!
Questo commento è stato fatto dal moderatore sul sito
Ciao,

Grazie per aver pubblicato questi esempi.
Sto cercando di implementarlo nel mio foglio, ma non riesco a farlo funzionare (forse perché sto usando una versione europea di excel)?

Voglio ottenere le date dei giorni in cui ho avuto i miei turni o in cui ho lavorato "alcune" (>0) ore per un cliente.

Quindi in I3 c'è il nome e in J3 il mese. K3 e L3 sono i turni (1 è lavorato) e le ore (non so come impostarle, dovrebbero essere più di zero)

I miei risultati attesi sono in:
Turni: I7 e I8
ore: J7

Quindi ho lavorato più di 0 ore per "persona 2" in ottobre il 3-10-2022
aveva turni per la persona 2 il '10-10-2022' e il 28-10-2022

Quando aggiungo '=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW ($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))' nel mio foglio Excel, non consente il virgola tra le diverse parti della formula.
Quindi ho bisogno di cambiarli in ';'.
Ma quando lo provo dice sempre: '#NAME?'

Quindi qualcuno può aiutarmi con questo?

Cordiali saluti,

Basso
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
Questo commento è stato fatto dal moderatore sul sito
Ciao, se ci sono valori duplicati (ad esempio due adam), come posso assicurarmi che restituisca solo 1 adam e non 2?
Questo commento è stato fatto dal moderatore sul sito
Ciao Bobby,
Per estrarre solo valori corrispondenti univoci, è necessario applicare la formula seguente:
Dopo aver incollato la formula, premere CTRL + MAIUSC + INVIO tasti insieme per ottenere il risultato corretto.
=SEERRORE(INDICE($B$2:$B$5, CONFRONTA(0, CONTA.SE(H1:$H$1, $B$2:$B$5)+SE($D$2:$D$5<>$G$2, 1 , 0)+IF($C$2:$C$5<>$F$2, 1, 0), 0)), "")

Per favore, prova, spero che possa aiutarti!
Non ci sono ancora commenti pubblicati qui
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