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

Cum se returnează mai multe valori de potrivire bazate pe unul sau mai multe criterii în Excel?

În mod normal, căutarea unei valori specifice și returnarea articolului care se potrivește este ușor pentru majoritatea dintre noi utilizând funcția VLOOKUP. Dar, ați încercat vreodată să returnați mai multe valori potrivite pe baza unuia sau mai multor criterii, după cum se arată în următoarea captură de ecran? În acest articol, voi introduce câteva formule pentru rezolvarea acestei sarcini complexe în Excel.

Returnează mai multe valori de potrivire bazate pe unul sau mai multe criterii cu formule matrice


Returnează mai multe valori de potrivire bazate pe unul sau mai multe criterii cu formule matrice

De exemplu, vreau să extrag toate numele a căror vârstă este de 28 de ani și provin din Statele Unite, vă rugăm să aplicați următoarea formulă:

1. Copiați sau introduceți formula de mai jos într-o celulă goală unde doriți să localizați rezultatul:

=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))

notițe: În formula de mai sus, B2: B11 este coloana din care se returnează valoarea potrivită; F2, C2: C11 sunt prima condiție și datele coloanei care conține prima condiție; G2, D2: D11 sunt a doua condiție și datele coloanei care conțin această condiție, vă rugăm să le modificați în funcție de nevoile dvs.

2. Apoi, apăsați Ctrl + Shift + Enter tastele pentru a obține primul rezultat de potrivire, apoi selectați prima celulă de formulă și trageți mânerul de umplere în jos până la celule până când este afișată valoarea erorii, acum toate valorile de potrivire sunt returnate așa cum se arată în imaginea de mai jos:

sfaturi: Dacă trebuie doar să returnați toate valorile potrivite pe baza unei condiții, aplicați formula matricei de mai jos:

=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))),"" )


Mai multe articole relative:

  • Returnează mai multe valori de căutare într-o singură celulă separată prin virgulă
  • În Excel, putem aplica funcția VLOOKUP pentru a returna prima valoare potrivită dintr-o celulă de tabel, dar, uneori, trebuie să extragem toate valorile potrivite și apoi să le separăm printr-un delimitator specific, cum ar fi virgulă, liniuță etc. într-o singură celulă după cum se arată în următoarea captură de ecran. Cum am putea obține și returna mai multe valori de căutare într-o singură celulă separată prin virgule în Excel?
  • Vizualizați și returnați mai multe valori de potrivire simultan în foaia Google
  • Funcția normală Vlookup din foaia Google vă poate ajuta să găsiți și să returnați prima valoare potrivită pe baza unor date date. Dar, uneori, poate fi necesar să căutați și să returnați toate valorile potrivite, după cum se arată în următoarea captură de ecran. Aveți vreo modalitate bună și ușoară de a rezolva această sarcină în foaia Google?
  • Vizualizați și returnați mai multe valori din lista derulantă
  • În Excel, cum ați putea să căutați și să returnați mai multe valori corespunzătoare dintr-o listă derulantă, ceea ce înseamnă că atunci când alegeți un articol din lista derulantă, toate valorile sale relative sunt afișate simultan, după cum se arată în următoarea captură de ecran. În acest articol, voi introduce soluția pas cu pas.
  • Vizualizați și returnați mai multe valori pe verticală în Excel
  • În mod normal, puteți utiliza funcția Vlookup pentru a obține prima valoare corespunzătoare, dar, uneori, doriți să returnați toate înregistrările de potrivire pe baza unui criteriu specific. În acest articol, voi vorbi despre cum să vizualizați și să returnați toate valorile potrivite pe verticală, orizontală sau într-o singură celulă.
  • Vlookup și returnează datele de potrivire între două valori în Excel
  • În Excel, putem aplica funcția normală Vlookup pentru a obține valoarea corespunzătoare pe baza datelor date. Dar, uneori, dorim să căutăm și să returnăm valoarea potrivită între două valori așa cum se arată în următoarea captură de ecran, cum ați putea face față acestei sarcini în Excel?

 


  • Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
  • Merge celule / rânduri / coloane și păstrarea datelor; Conținut de celule divizate; Combinați rânduri duplicate și sumă / medie... Prevenirea celulelor duplicate; Comparați gamele...
  • Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
  • Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
  • Formule favorite și inserare rapidă, Gama, Diagrame și Imagini; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
  • Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
  • Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
  • Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
  • Gruparea tabelului pivot după numărul săptămânii, ziua săptămânii și multe altele ... Afișați celulele deblocate, blocate prin diferite culori; Evidențiați celulele care au formulă / nume...
fila kte 201905
  • Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
  • Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
  • Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
fundul officetab

 

Comentarii (25)
Încă nu există evaluări. Fii primul care evaluează!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Am încercat exact aceeași formulă; copiat 100%. Singurul lucru pe care l-am schimbat a fost că datele sunt potrivite și returnate. Când folosesc această formulă, Excel spune „Ați introdus prea multe argumente pentru această funcție).=INDEX('2020 Volume Report'!$B$3:$B$100,SMALL(IF(COUNTIF($A$1,'2020 Volume) Raport'!$A$3:$A$100)*COUNTIF($A$3,'2020 Volume Report'!$D$3:$D$100),ROW('2020 Volume Report'!$A$3:$G$100)- MIN(RÂND('Raport volum 2020'!$A$3:$G$100))+1,"0"), RÂND(A1), COLONA (A1))
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, Ați putea să dați erorile de date și formule ca o captură de ecran aici?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună ziua, Cum îl pot folosi pentru starea orizontală.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Care este „0” după +1 din formulă? Asta nu este în exemplul.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Salut, am incercat aceeasi formula. Primesc un rezultat, dar când dau CSE, nu oferă răspunsuri multiple
Acest comentariu a fost redus la minimum de moderatorul de pe site

Acest comentariu a fost redus la minimum de moderatorul de pe site
În ceea ce privește returnarea mai multor valori de potrivire pe baza unuia sau a mai multor criterii cu formule matrice: de ce dacă am datele în altă parte, cu excepția începând cu A1, nu funcționează, deși actualizez toate referințele de celule din formulă?
Acest comentariu a fost redus la minimum de moderatorul de pe site
În primul exemplu, ce modificare a formulei ar fi necesară pentru a returna toți cei care aveau mai puțin de 28 de ani?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună,

Mă întrebam dacă este deloc posibil să introduc un al doilea criteriu, dar din același interval ca și primul criteriu,

De exemplu, cu exemplul folosit de mai sus, aș dori să caut numele persoanelor atât din America, cât și din Franța. Astfel, celula F3 ar avea Franța, Scarlett și Andrew vor popula și în lista din coloana G.

Vă mulțumim anticipat pentru ajutor.
Acest comentariu a fost redus la minimum de moderatorul de pe site
Salut Nick,

Bucuros sa ajut. Dacă doriți să obțineți numele persoanelor atât din America, cât și din Franța, vă sfătuiesc să folosiți formula noastră de două ori pentru a obține rezultatul. Vă rugăm să vedeți captura de ecran, În F2 și G2 sunt valorile „Statele Unite” și „Franța”. Aplicați 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))),"" ) pentru a obține rezultatele pentru America. Și aplicați 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))),"" ) pentru a obține rezultatele pentru Franța. E simplu. Vă rugăm să încercați.

Cu sinceritate,
Mandy
Acest comentariu a fost redus la minimum de moderatorul de pe site
Când folosesc a doua formulă și trag în jos, nu apare nimic. Rezultatul formulei (fx) spune că ar trebui să returneze ceva, dar este necompletat. Cum corectez asta?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Buna Alysia,

Bucuros sa ajut. Am încercat a doua formulă din articol și trageți formula în jos, restul rezultatelor au fost returnate. Cred că pot fi două motive pentru problema ta. În primul rând, poate uitați să apăsați tastele Ctrl + Shift + Enter pentru a introduce formula. În al doilea rând, rezultatul potrivit este unul singur, deci nu se returnează niciun alt rezultat. Vă rog să faceți un cec.

Cu sinceritate,
Mandy
Acest comentariu a fost redus la minimum de moderatorul de pe site
Salut,
Am încercat să folosesc formula și fie generează o valoare de 0, fie imaginea atașată
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, Milku
Captura de ecran a arătat software-ul WPS cu versiunea MAC, așa că nu sunt sigur dacă formula noastră este disponibilă.
Am încărcat un fișier Excel aici, puteți încerca să vedeți dacă se poate calcula corect în mediul dumneavoastră.
Mulțumesc!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Buna,
ce ar fi necesar pentru a extinde prima formulă în următorul caz:
Unele ID-uri sunt goale (de exemplu, celula A5 este goală) și aș dori o condiție suplimentară de ieșire a liniilor numai atunci când ID-urile nu sunt goale. (Deci rezultatul ar trebui să fie James și Abdul.
Multumesc!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, Jo,
Pentru a vă rezolva problema, vă rugăm să aplicați formula de mai jos:
=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))

Vă rugăm să faceți o încercare, sper că vă poate ajuta!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună,

dacă în celula H1 scriu „Nume” și aș vrea să le leg cu formula, cum ar funcționa?
Apoi aș putea scrie „ID” în celula H1 și aș obține automat ca rezultat: AA1004; DD1009; PP1023 (pentru prima formulă)

Va multumesc anticipat!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, Marie
Îmi pare rău, nu pot înțelege rostul primei probleme, ați putea explica problema mai clar și mai detaliat? Sau puteți introduce o captură de ecran aici pentru a vă descrie problema.
În ceea ce privește a doua întrebare, trebuie doar să schimbați referința celulei astfel:
=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))

Amintiți-vă să apăsați Ctrl + Shift + Enter cheile împreună.
Vă rugăm să încercați, sper că vă poate ajuta!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Heyi, mulțumesc pentru formulă. A funcționat pentru valori "fixe" / text ca criterii. Cu toate acestea, unul dintre criteriile pe care încerc să le folosesc este o condiție (valori <>0), dar nu funcționează formula descrisă. Știți băieți ce ar trebui să schimb pentru a adapta formula astfel încât să am o condiție ca unul dintre criterii, vă rog?

Cel mai bun,

Ioan
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, Marcus
Pentru a vă rezolva problema, vă rugăm să consultați acest articol:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Există câteva explicații detaliate ale acestei sarcini. Trebuie doar să schimbi criteira cu propria ta.
Mulțumesc!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună,

În primul rând, mulțumesc pentru împărtășire!

Puteți oferi o soluție la cazul de mai jos:

Am 3 coloane (A: Conțin informații de referință, B: Conțin informații care trebuie căutate, C: Rezultatul căutării)

Adresa URL a imaginii este furnizată mai jos

https://ibb.co/VHCd09K

Coloana A-------------------------Coloana B------------ Coloana C
Nume fișier-------------------------Nume---------------- Nume fișier, Nume document, Nume element, nume
Element schimbat-----------------Element-------------Element modificat, Nume element, ID element
Locația coloanei
numele documentului
Nume element
Nume si Prenume
Categorii
Garanție
Pantă
ID element

Ceea ce am nevoie este să caut în coloana A orice potrivire parțială cu celula B2 (Nume) sau B3 (Element) și să obțin rezultatul într-o singură celulă,

Mulțumesc, Behzad
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, Behzad
Poate că funcția definită de utilizator de mai jos vă poate ajuta.
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


După ce copiați și lipiți acest cod, apoi utilizați această formulă:=ConcatPartLookUp(B2,$A$2:$A$8) pentru a obține rezultatul de care aveți nevoie.
Vă rugăm să încercați, sper că vă poate ajuta!
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună,

Mulțumesc pentru postarea acestor exemple.
Încerc să implementez acest lucru în propria mea foaie, dar nu îl fac să funcționeze (poate pentru că folosesc o versiune de excel pentru Europa)?

Vreau să obțin datele zilelor în care am avut turele sau că am lucrat „câteva” (>0) ore pentru un client.

Deci în I3 este numele și în J3 luna. K3 și L3 sunt schimburile (1 este lucrat) și orele (nu știu cum să setez asta, ar trebui să fie mai mult de zero)

Rezultatele mele așteptate sunt în:
Ture: I7 și I8
ore: J7

Așa că am lucrat mai mult de 0 ore pentru „persoana 2” în octombrie pe 3-10-2022
a avut ture pentru persoana 2 pe „10-10-2022” și 28-10-2022

Când adaug „=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))' în foaia mea Excel, nu permite virgulă între diferitele părți ale formulei.
Așa că trebuie să le schimb în „;”.
Dar când îl încerc, întotdeauna spune: „#NUME?”

Deci ma poate ajuta cineva cu asta?

Cu stima,

Bas
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, dacă există valori duplicate (de exemplu, doi adams), cum mă asigur că returnează doar 1 adam și nu 2?
Acest comentariu a fost redus la minimum de moderatorul de pe site
Bună, Bobby,
Pentru a extrage numai valori unice de potrivire, ar trebui să aplicați formula de mai jos:
După ce lipiți formula, apăsați Ctrl + Shift + Enter tastele împreună pentru a obține rezultatul corect.
=IFEROARE(INDEX($B$2:$B$5, MATCH(0, COUNTIF(H1:$H$1, $B$2:$B$5))+IF($D$2:$D$5<>$G$2, 1) , 0)+IF($C$2:$C$5<>$F$2, 1, 0), 0)), "")

Vă rugăm să încercați, sper că vă poate ajuta!
Nu există comentarii postate aici încă
Lăsa comentarii
Postare ca invitat
×
Evaluează această postare:
0   Caractere
Locații sugerate

Urmărește-ne

Copyright © 2009 - www.extendoffice.com. | Toate drepturile rezervate. Cu sprijinul ExtendOffice. | Harta site-ului
Microsoft și sigla Office sunt mărci comerciale sau mărci comerciale înregistrate ale Microsoft Corporation în Statele Unite și / sau în alte țări.
Protejat de Sectigo SSL