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

Kako vrniti več ujemajočih se vrednosti na podlagi enega ali več meril v Excelu?

Običajno je iskanje določene vrednosti in vrnitev ustreznega predmeta za večino od nas enostavno s funkcijo VLOOKUP. Ali ste že kdaj poskusili vrniti več ujemajočih se vrednosti na podlagi enega ali več meril, kot je prikazano na sliki spodaj? V tem članku bom predstavil nekaj formul za reševanje te zapletene naloge v Excelu.

Vrni več ujemajočih se vrednosti na podlagi enega ali več meril s formulami matrike


Vrni več ujemajočih se vrednosti na podlagi enega ali več meril s formulami matrike

Na primer, želim izvleči vsa imena, katerih starost je 28 let, in prihajajo iz Združenih držav, uporabite naslednjo formulo:

1. Kopirajte ali vnesite spodnjo formulo v prazno celico, kjer želite poiskati rezultat:

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

Opombe: V zgornji formuli, B2: B11 je stolpec, iz katerega se vrne ujemajoča se vrednost; F2, C2: C11 so prvi pogoj in podatki stolpca, ki vsebuje prvi pogoj; G2, D2: D11 so drugi pogoj in podatki stolpcev, ki vsebujejo ta pogoj, jih spremenite po potrebi.

2. Nato pritisnite Ctrl + Shift + Enter tipke, da dobite prvi ujemajoč se rezultat, nato pa izberite prvo celico formule in povlecite ročico za polnjenje navzdol do celic, dokler se ne prikaže vrednost napake.

nasveti: Če morate vrniti vse ujemajoče se vrednosti na podlagi enega pogoja, uporabite spodnjo formulo matrike:

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


Več relativnih člankov:

  • Vrni več iskalnih vrednosti v eno ločeno celico
  • V Excelu lahko uporabimo funkcijo VLOOKUP za vrnitev prve ujemajoče se vrednosti iz celic tabele, vendar moramo včasih izločiti vse ujemajoče se vrednosti in jih nato ločiti s posebnim ločilnikom, kot so vejica, pomišljaj itd. celica, kot je prikazano na sliki spodaj. Kako lahko dobimo in vrnemo več iskalnih vrednosti v eni celici, ločeni z vejico v Excelu?
  • Iskanje in vrnitev več ujemajočih se vrednosti naenkrat v Google Sheet
  • Običajna funkcija Vlookup v Googlovem listu vam lahko pomaga najti in vrniti prvo ujemajočo se vrednost na podlagi danih podatkov. Včasih pa boste morda morali vlookup in vrniti vse ujemajoče se vrednosti, kot je prikazano na sliki spodaj. Ali imate na Googlovem listu kakšne dobre in enostavne načine za reševanje te naloge?
  • Iskanje in vrnitev več vrednosti s spustnega seznama
  • Kako lahko v Excelu iščete in vrnete več ustreznih vrednosti s spustnega seznama, kar pomeni, da ko izberete en element s spustnega seznama, se naenkrat prikažejo vse njegove relativne vrednosti, kot je prikazano na sliki spodaj. V tem članku bom predstavil rešitev korak za korakom.
  • Vlookup in vrnitev več vrednosti navpično v Excelu
  • Običajno lahko s funkcijo Vlookup dobite prvo ustrezno vrednost, včasih pa želite vrniti vse ujemajoče se zapise na podlagi določenega merila. V tem članku bom govoril o tem, kako vlookup in vrnem vse ujemajoče se vrednosti navpično, vodoravno ali v eno samo celico.
  • Vlookup in vrnitev ujemajočih se podatkov med dvema vrednostma v Excelu
  • V Excelu lahko uporabimo običajno funkcijo Vlookup, da dobimo ustrezno vrednost na podlagi danih podatkov. Toda včasih želimo vlookup in vrniti ujemajočo se vrednost med dvema vrednostma, kot je prikazano na spodnjem posnetku zaslona, ​​kako bi se lahko spoprijeli s to nalogo v Excelu?

 


  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce in vodenje podatkov; Vsebina razdeljenih celic; Združite podvojene vrstice in vsoto / povprečje... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Priljubljene in hitro vstavite formule, Obsegi, grafikoni in slike; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Razvrščanje vrtilne tabele po številka tedna, dan v tednu in še več ... Prikaži odklenjene, zaklenjene celice po različnih barvah; Označite celice s formulo / imenom...
zavihek kte 201905
  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
dno pisarniške mize

 

Komentarji (25)
Ocene še ni. Bodite prvi in ​​ocenite!
Ta komentar je moderator na spletnem mestu minimiziral
Preizkusil sem popolnoma isto formulo; kopirano 100%. Edino, kar sem spremenil, so bili podatki, ki so se ujemali in vrnili. Ko uporabljam to formulo, Excel pravi "Vnesli ste preveč argumentov za to funkcijo).=INDEX('2020 Volume Report'!$B$3:$B$100,SMALL(IF(COUNTIF($A$1,'2020 Volume) Poročilo'!$A$3:$A$100)*COUNTIF($A$3,'Poročilo o obsegu 2020'!$D$3:$D$100),ROW('Poročilo o obsegu 2020'!$A$3:$G$100)- MIN(ROW('Poročilo o obsegu 2020'!$A$3:$G$100))+1,"0"),ROW(A1),COLUMN(A1))
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, ali lahko tukaj navedete svoje podatke in napako formule kot posnetek zaslona?
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni, kako ga lahko uporabim za vodoravno stanje.
Ta komentar je moderator na spletnem mestu minimiziral
Kaj je "0" za +1 v formuli? Tega v primeru ni.
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, poskusil sem z isto formulo. dobim rezultat, vendar ko dam CSE, ne zagotavlja več odgovorov
Ta komentar je moderator na spletnem mestu minimiziral

Ta komentar je moderator na spletnem mestu minimiziral
Glede vrnjenih več ujemajočih se vrednosti, ki temeljijo na enem ali več merilih s formulami matrike: Zakaj, če imam podatke kjer koli drugje, razen na začetku v A1, ne delujejo, čeprav posodobim vse reference celic v formuli?
Ta komentar je moderator na spletnem mestu minimiziral
Kakšna sprememba formule bi bila v prvem primeru potrebna za vrnitev vseh, ki so bili mlajši od 28 let?
Ta komentar je moderator na spletnem mestu minimiziral
Hi,

Zanimalo me je, ali je sploh mogoče vnesti 2. kriterij, vendar iz istega obsega kot 1. kriterij,

Na primer z zgornjim uporabljenim primerom bi rad poiskal imena ljudi iz Amerike in Francije, Tako da bi celica F3 imela Francijo, Scarlett & Andrew bi prav tako izpolnila seznam v stolpcu G

Hvala za pomoč vnaprej.
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni Nick,

Z veseljem pomagam. Če želite dobiti imena ljudi iz Amerike in Francije, vam svetujem, da dvakrat uporabite našo formulo, da dobite rezultat. Oglejte si posnetek zaslona, ​​v F2 in G2 sta vrednosti "Združene države" in "Francija". Uporabi formulo =IFERROR(INDEX($B$2:$B$11, SMALL($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1) ), ROW(1:1))),"" ), da dobite rezultate za Ameriko. In uporabite formulo =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))),"" ), da dobite rezultate za Francijo. To je preprosto. Prosimo, poskusite.

S spoštovanjem,
Mandy
Ta komentar je moderator na spletnem mestu minimiziral
Ko uporabim drugo formulo in povlečem navzdol, se nič ne prikaže. Rezultat formule (fx) pravi, da bi moral nekaj vrniti, vendar je prazen. Kako naj to popravim?
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljena Alysia,

z veseljem pomagam. Poskusil sem drugo formulo v članku in povlekel formulo navzdol, preostali rezultati so bili vrnjeni. Mislim, da sta lahko dva razloga za vaš problem. Prvič, morda ste pozabili pritisniti tipki Ctrl + Shift + Enter za vnos formule. Drugič, ujemajoči se rezultat je samo en, zato se drugi rezultati ne vrnejo. Prosim za ček.

S spoštovanjem,
Mandy
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni,
Poskusil sem uporabiti formulo in ustvari vrednost 0 ali priloženo sliko
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, Milku
Vaš posnetek zaslona je pokazal programsko opremo WPS različice MAC, zato nisem prepričan, ali je naša formula na voljo.
Tukaj sem naložil Excelovo datoteko, poskusite preveriti, ali lahko pravilno izračuna v vašem okolju.
Hvala!
Ta komentar je moderator na spletnem mestu minimiziral
Zdravo,
kaj bi bilo potrebno za razširitev prve formule v naslednjem primeru:
Nekateri ID-ji so prazni (npr. celica A5 je prazna) in želel bi dodaten pogoj, ki izpisuje vrstice le, če ID-ji niso prazni. (Izhod bi torej moral biti James in Abdul.
Hvala!
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, Jo,
Za rešitev težave uporabite spodnjo formulo:
=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))

Prosim, pokličite, upam, da vam lahko pomaga!
Ta komentar je moderator na spletnem mestu minimiziral
Hi,

če bi v celico H1 napisal "Ime" in želel to povezati s formulo, kako bi to delovalo?
Potem bi lahko v celico H1 napisal "ID" in samodejno dobil kot rezultat: AA1004; DD1009; PP1023 (za prvo formulo)

Hvala vnaprej!
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, Marie
Oprostite, ne razumem bistva vaše prve težave. Ali lahko svojo težavo pojasnite bolj jasno in podrobno? Lahko pa tukaj vstavite posnetek zaslona, ​​da opišete svojo težavo.
Kar zadeva drugo vprašanje, morate le spremeniti sklic na celico takole:
=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))

Ne pozabite pritisniti Ctrl + Shift + Enter skupaj.
Prosim poskusite, upam, da vam lahko pomaga!
Ta komentar je moderator na spletnem mestu minimiziral
Heyi, hvala za formulo. Delovalo je za "fiksne" vrednosti / besedilo kot merila. Vendar je eno od meril, ki jih poskušam uporabiti, pogoj (vrednosti <>0), vendar ne deluje z opisano formulo. Ali veste, kaj naj spremenim, da prilagodim formulo, da bom lahko imel pogoj kot eno od meril, prosim?

Lep pozdrav,

John
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, Marcus
Če želite rešiti svojo težavo, si oglejte ta članek:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Obstaja nekaj podrobnih razlag te naloge. Samo spremeniti morate kriterije na svoje.
Hvala!
Ta komentar je moderator na spletnem mestu minimiziral
Hi,

Najprej hvala za skupno rabo!

Ali lahko ponudite rešitev za spodnji primer:

Imam 3 stolpce (A: Vsebuje referenčne informacije, B: Vsebuje informacije za iskanje, C: Rezultat iskanja)

URL slike je naveden spodaj

https://ibb.co/VHCd09K

Stolpec A------------------------Stolpec B------------Stolpec C
Ime datoteke------------------------Ime----------------Ime datoteke, Ime dokumenta, Ime elementa, Ime
Spremenjen element-----------------Element--------------Spremenjen element, Ime elementa, ID elementa
Lokacija stolpca
Ime dokumenta
Ime elementa
Ime
Kategorija
garancija
Naklon
ID elementa

V stolpcu A moram poiskati katero koli delno ujemanje s celico B2 (ime) ali B3 (element) in dobiti rezultat v eni celici,

Hvala, Behzad
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljen, Behzad
Morda vam lahko pomaga spodnja uporabniško definirana funkcija.
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


Ko kopirate in prilepite to kodo, uporabite to formulo:=ConcatPartLookUp(B2,$A$2:$A$8) da dobite rezultat, ki ga potrebujete.
Prosimo, poskusite, upam, da vam bo pomagalo!
Ta komentar je moderator na spletnem mestu minimiziral
Hi,

Hvala za objavo teh primerov.
To poskušam implementirati v svoj list, vendar ne deluje (morda zato, ker uporabljam evropsko različico excela)?

Želim pridobiti datume dni, ko sem imel svoje izmene ali ko sem delal 'nekaj' (>0) ur za stranko.

Torej je v I3 ime, v J3 pa mesec. K3 in L3 sta izmeni (1 je opravljeno) in ure (ne vem, kako to nastaviti, mora biti več kot nič)

Moji pričakovani rezultati so:
Prestave: I7 in I8
ure: J7

Tako sem oktobra 0-2-3 delal več kot 10 ur za 'osebo 2022'
imel izmene za osebo 2 na '10-10-2022' in 28-10-2022

Ko dodam '=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))' v moji Excelovi tabeli ne dovoljuje vejica med različnimi deli formule.
Zato jih moram spremeniti v ';'.
Toda ko poskusim, vedno piše: '#NAME?'

Torej mi lahko kdo pomaga pri tem?

Lep pozdrav,

Bas
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, če obstajajo podvojene vrednosti (npr. dva adam), kako naj zagotovim, da vrne samo 1 adam in ne 2?
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, Bobby,
Če želite pridobiti samo edinstvene ujemajoče se vrednosti, morate uporabiti spodnjo formulo:
Ko prilepite formulo, pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite pravilen rezultat.
=IFERROR(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)), "")

Prosimo, poskusite, upam, da vam bo pomagalo!
Tu še ni objavljenih komentarjev
Pustite vaše komentarje
Objava kot gost
×
Ocenite to objavo:
0   Znaki
Predlagane lokacije

Sledi nam

Copyright © 2009 - www.extendoffice.com. | Vse pravice pridržane. Poganja ga ExtendOffice. | Kazalo
Microsoft in logotip Office sta blagovni znamki ali registrirani blagovni znamki družbe Microsoft Corporation v ZDA in / ali drugih državah.
Zaščiteno s Sectigo SSL