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

Kako izvleči edinstvene vrednosti na podlagi meril v Excelu?

Recimo, da imate levi obseg podatkov, za katerega želite navesti samo enolična imena stolpca B na podlagi določenega merila stolpca A, da dobite rezultat, kot je prikazano spodaj. Kako bi se lahko hitro in enostavno spopadli s to nalogo v Excelu?

Izvlecite edinstvene vrednosti na podlagi meril s formulo matrike

Izvlecite edinstvene vrednosti na podlagi več meril s formulo matrike

Izvlecite edinstvene vrednosti s seznama celic s koristno funkcijo

 

Izvlecite edinstvene vrednosti na podlagi meril s formulo matrike

Če želite rešiti to nalogo, lahko uporabite kompleksno formulo matrike, naredite naslednje:

1. Vnesite spodnjo formulo v prazno celico, kjer želite navesti rezultat ekstrakcije, v tem primeru jo bom postavil v celico E2 in nato pritisnil Shift + Ctrl + Enter tipke, da dobite prvo unikatno vrednost.

=IFERROR(INDEX($B$2:$B$15, MATCH(0, IF($D$2=$A$2:$A$15, COUNTIF($E$1:$E1, $B$2:$B$15), ""), 0)),"")

2. Nato povlecite ročico za polnjenje navzdol do celic, dokler se ne prikažejo prazne celice, in zdaj so navedene vse edinstvene vrednosti na podlagi določenega merila, glejte posnetek zaslona:

Opomba: V zgornji formuli: B2: B15 obseg stolpcev vsebuje edinstvene vrednosti, iz katerih želite izvleči, A2: A15 je stolpec vsebuje merilo, na katerem temeljiš, D2 označuje merilo, na podlagi katerega želite navesti unikatne vrednosti, in E1 je celica nad vneseno formulo.

Izvlecite edinstvene vrednosti na podlagi več meril s formulo matrike

Če želite izvleči edinstvene vrednosti na podlagi dveh pogojev, je tu še ena formula matrike, ki vam lahko stori uslugo, storite tako:

1. Vnesite spodnjo formulo v prazno celico, kjer želite navesti edinstvene vrednosti, v tem primeru jo bom postavil v celico G2 in nato pritisnil Shift + Ctrl + Enter tipke, da dobite prvo unikatno vrednost.

=IFERROR(INDEX($C$2:$C$15,MATCH(0,COUNTIF(G1:$G$1,$C$2:$C$15)+IF($A$2:$A$15<>$E$2,1,0)+IF($B$2:$B$15<>$F$2,1,0),0)),"")

2. Nato povlecite ročico za polnjenje navzdol do celic, dokler se ne prikažejo prazne celice, in zdaj so navedene vse edinstvene vrednosti, ki temeljijo na določenih dveh pogojih, glejte sliko zaslona:

Opomba: V zgornji formuli: C2: C15 obseg stolpcev vsebuje edinstvene vrednosti, iz katerih želite izvleči, A2: A15 in E2 so prvo območje z merili, na podlagi katerih želite pridobiti edinstvene vrednosti, B2: B15 in F2 so drugo območje z merili, na podlagi katerih želite izvleči edinstvene vrednosti, in G1 je celica nad vneseno formulo.

Izvlecite edinstvene vrednosti s seznama celic s koristno funkcijo

Včasih želite samo izvleči edinstvene vrednosti s seznama celic, tukaj bom priporočil koristno orodje -Kutools za Excel, Z njegovim Izvleči celice z edinstvenimi vrednostmi (vključi prvi dvojnik) pripomoček, lahko hitro izvlečete edinstvene vrednosti.

Opomba:Če želite uporabiti to Izvleči celice z edinstvenimi vrednostmi (vključi prvi dvojnik), najprej bi morali prenesti Kutools za Excelin nato funkcijo hitro in enostavno uporabite.

Po namestitvi Kutools za Excel, naredite tako:

1. Kliknite celico, v katero želite izpisati rezultat. (Opombe: Ne kliknite celice v prvi vrstici.)

2. Nato kliknite Kutools > Pomočnik za formulo > Pomočnik za formulo, glej posnetek zaslona:

3. v Pomočnik za formule pogovorno okno, naredite naslednje:

  • Izberite Besedilo možnost iz Formula tip spustni seznam;
  • Potem izberite Izvleči celice z edinstvenimi vrednostmi (vključi prvi dvojnik) Iz Izberite fromula polje s seznamom;
  • Na desni Vnos argumentov v razdelku izberite seznam celic, za katere želite izvleči edinstvene vrednosti.

4. Nato kliknite Ok gumb, se prvi rezultat prikaže v celici, nato izberite celico in povlecite ročico za polnjenje do celic, v katere želite našteti vse edinstvene vrednosti, dokler niso prikazane prazne celice, glejte sliko zaslona:

Brezplačno prenesite Kutools za Excel zdaj!


Več relativnih člankov:

  • Štejte na seznamu število edinstvenih in ločenih vrednot
  • Recimo, da imate dolg seznam vrednosti z nekaj podvojenimi elementi, zdaj želite prešteti število unikatnih vrednosti (vrednosti, ki so na seznamu samo enkrat) ali ločenih vrednosti (vse različne vrednosti na seznamu pomenijo unikatne vrednosti + 1. podvojene vrednosti) v stolpcu, kot je prikazano na levi sliki zaslona. V tem članku bom govoril o tem, kako ravnati s tem delom v Excelu.
  • Vsota edinstvenih vrednosti na podlagi meril v Excelu
  • Na primer, zdaj imam nabor podatkov, ki vsebujejo stolpca Ime in Naročilo, da v stolpcu Naročilo seštejem samo enolične vrednosti na podlagi stolpca Ime, kot je prikazano na sliki spodaj. Kako hitro in enostavno rešiti to nalogo v Excelu?
  • Združi edinstvene vrednosti v Excelu
  • Če imam dolg seznam vrednosti, ki je zapolnjen z nekaj podvojenimi podatki, želim zdaj najti samo edinstvene vrednosti in jih nato združiti v eno celico. Kako sem se lahko hitro in enostavno spopadel s to težavo v Excelu?

Najboljša orodja za pisarniško produktivnost

Kutools za Excel rešuje večino vaših težav in poveča vašo produktivnost za 80%

  • Ponovna uporaba: Hitro vstavite zapletene formule, grafikoni in vse, kar ste že uporabljali; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • 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 brez izgube podatkov; Vsebina razdeljenih celic; Združi podvojene vrstice / stolpce... 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č ...
  • 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...
  • Več kot 300 zmogljivih funkcij. Podpira Office / Excel 2007-2021 in 365. Podpira vse jezike. Enostavna uvedba v vašem podjetju ali organizaciji. 30-dnevna brezplačna preizkusna različica vseh funkcij. 60-dnevna garancija vračila denarja.
zavihek kte 201905

Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • 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 (40)
Ocene še ni. Bodite prvi in ​​ocenite!
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, hvala za to vadnico, deluje odlično. Poskušam ga spremeniti, da bo deloval s pogojem ALI, vendar se zdi, da ne deluje – ali je to mogoče? npr. =INDEX($B$2:$B$17, MATCH(0, IF(ALI($D$2=$A$2:$A$17,$D$2=$B$2:$B$17), COUNTIF($E$1) :$E1, $B$2:$B$17), ""), 0))
Ta komentar je moderator na spletnem mestu minimiziral
Hvala za to vadnico! Prav tako poskušam spremeniti formulo, kot zgornji komentator, vendar s pogojem IN, tako da izpolnjuje druge pogojne kriterije (npr. za ta primer bi rad videl samo stvari nad določenim pragom). Ali lahko prosim svetujete? Hvala vam!
Ta komentar je moderator na spletnem mestu minimiziral
Hej, en način za to: zamenjajte formulo if z sumproduct((condition1=rng1)+(condition2=rng2))*countif(... Delovalo je zame. Vso srečo! Če zamenjate + z *, lahko naj bo pogoj ALI, vendar dobro pazi na nosilce!
Ta komentar je moderator na spletnem mestu minimiziral
Hvala, to je super!
Ta komentar je moderator na spletnem mestu minimiziral
Hvala za to. Preizkusil sem to in zdi se, da občasno deluje dobro. Težava, ki se nenehno ponavlja, je, da se včasih vrne samo prva ujemajoče se vrednosti in se nato podvoji, ko povlečem navzdol, da vrnem vse ujemajoče se vrednosti. Kako to preprečim? Kakšen predlog?
Ta komentar je moderator na spletnem mestu minimiziral
To deluje zelo dobro, vendar vsakič, ko se vrednost, ki jo vnese, podvoji, vrednost postavi samo enkrat. Na primer, če ima vaš seznam dve Lucy, to prinese samo eno Lucy v novo tabelo. Ali obstaja način, da to popravimo?
Ta komentar je moderator na spletnem mestu minimiziral
Ko uporabljate to formulo, se vedno znova ponavlja prva vrednost, kako to ustavite in zagotovite seznam vrednosti, ki je enak produktu v D2?
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, če želite, da se prva vrednost ne ponavlja, ko povlečete navzdol, morate COUNTIF celico NAD celico, v katero vnašate formulo. Npr., če je formula v E2, morate vnesti countif($E$1:$E1...
Ta komentar je moderator na spletnem mestu minimiziral
Živjo Ryan. Formule delujejo odlično, vendar se pri vlečenju navzdol prva vrednost ponavlja. Prepričal sem se, da se COUNTIF sklicuje na celico NAD celico s formulo, vendar še vedno ponavlja prvo vrednost pri vlečenju navzdol? (npr. če je formula matrike v C2, potem COUNTIF kaže na celico $C$1:$C$1)
Ta komentar je moderator na spletnem mestu minimiziral
Živjo Ryan. Formule delujejo odlično, vendar se pri vlečenju navzdol prva vrednost ponavlja. Prepričal sem se, da se COUNTIF sklicuje na celico NAD celico s formulo, vendar še vedno ponavlja prvo vrednost pri vlečenju navzdol? (npr. če je formula matrike v C2, potem COUNTIF kaže na celico $C$1:$C$1)
Ta komentar je moderator na spletnem mestu minimiziral
Verjetno ne deluje, ker ste zaklenili celice - poskusite zamenjati $C$1:$C$1 z $C$1:$C1
Ta komentar je moderator na spletnem mestu minimiziral
to je bilo zelo koristno, vendar vedno znova dobivam dvojnike vseh imen, kot je ta:
Doe, Jane
Doe, Jane
Hoover, Tom
Hoover, Tom

Kako lahko to ustavim?
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni, dobivam napako "#N/A" pri "funkciji ujemanja", mi lahko prosim poveste?
Ta komentar je moderator na spletnem mestu minimiziral
=IF(AL2="AP","AP",IF(AK2="AD","AD",IF(Z2>500000,"Da","Ne"))) Želim, da so izpolnjeni "vsi pogoji" reči da...excel odraža napako v tej formuli..prosim, svetujte
Ta komentar je moderator na spletnem mestu minimiziral
pravzaprav želim, da celica odraža "DA", če (AL2="AP" in AK2="AD" in Z2>500000)
Ta komentar je moderator na spletnem mestu minimiziral
Pri funkciji ujemanja s to formulo dobivam napako #N/A. Ali lahko pomagate?
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni, dobivam napako "#N/A" pri "funkciji ujemanja", mi lahko prosim poveste?
Ta komentar je moderator na spletnem mestu minimiziral
Če se prikaže napaka #N/A, pojdite na formulo in namesto Enter uporabite Control + Shift + Enter.
Ta komentar je moderator na spletnem mestu minimiziral
Dobim 0 namesto pričakovanih rezultatov, formula deluje odlično za podatke v istem listu, ali imate kakšno rešitev za podatke na drugem listu?

to je moja formula

=IFERROR(INDEX('Switching Data'!$B$7:$B$204,MATCH(0,IF($A$2='Switching Data'!$A$7:$A$204,COUNTIF($A$4:A4,'Switching Data'!$B$7:$B$204),""),0)),0)
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, Gon,
Ko vstavite formulo, morate skupaj pritisniti tipke Ctrl + Shift + Enter in ne samo tipke Enter.
Prosim, poskusite, hvala!
Ta komentar je moderator na spletnem mestu minimiziral
Živjo Gon, upam, da si dobro. Zanima me, če lahko rešite to težavo. Dobivam isto napako, ko formula prihaja iz drugega lista. Hvaležen bom, če delite rešitev, če jo dobite.
Ta komentar je moderator na spletnem mestu minimiziral
Hvala!
Ta komentar je moderator na spletnem mestu minimiziral
Kako naj ta formula vrne vsak dvojnik namesto enega od imen? Na primer, v zgornjem primeru, kako naj dosežem, da stolpec z rezultati (B:B) vrne Lucy, Ruby, Anny, Jose, Lucy, Anny, Tom? To uporabljam kot proračunsko orodje za pridobivanje posebnih povzetkov računa iz glavne knjige. Vendar pa je več zneskov in opisov transakcij podvojenih v glavni knjigi. Ko je prva od podvojenih vrednosti potegnjena, se jih ne izvleče več.
Ta komentar je moderator na spletnem mestu minimiziral
Živjo Joe,
Če želite izluščiti vse ustrezne vrednosti na podlagi določenega merila celice, vam lahko pomaga naslednja formula matrike, glejte posnetek zaslona:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

Ko vstavite formulo, pritisnite Shift + Ctrl + Enter skupaj, da dobite pravilen rezultat, nato pa povlecite ročico za polnjenje navzdol, da dobite vse vrednosti.
Upam, da vam to lahko pomaga, hvala!
Ta komentar je moderator na spletnem mestu minimiziral
Zaenkrat tako dobro. Lahko podvojim rezultate na testnem listu, spremenim matriko in nato popravim formulo, da upoštevam spremembe, ki sem jih naredil. Danes nameravam to premakniti v glavni list in videti, kako deluje. Hvala za pomoč!
Ta komentar je moderator na spletnem mestu minimiziral
Ok, torej deluje v glavnem delovnem zvezku. Obstaja ena izjema, za katero nisem uspel ugotoviti vzroka: Če se matrika (v mojem primeru glavna knjiga, ki sem jo imel na začetku v vrstici 3) ne začne v vrstici 1, so vrnjene vrednosti napačne. Kaj povzroča to težavo in kateri izraz v formuli jo odpravlja? Še enkrat hvala za vašo pomoč pri tem!
Ta komentar je moderator na spletnem mestu minimiziral
Zadnje vprašanje: Če želim, da stolpec z rezultati vrne vse vrednosti, ki niso povezane s KTE ali KTO (torej bi bil D:D Tom, Nocol, Lily, Angelina, Genna), kako naj to storim?
Ta komentar je moderator na spletnem mestu minimiziral
Zame formula ne deluje. Pritisnem ctrl shift enter in še vedno dobim napako N/A. Rad bi dodal, da sem pripravil popolnoma enake podatke kot v vadnici. Kaj je razlog, da ne deluje?
Ta komentar je moderator na spletnem mestu minimiziral
To mi je odlično delovalo s posebno vrednostjo iskanja. Če pa bi želel uporabiti nadomestni znak za iskanje delnih vrednosti, kako bi to naredil? Na primer, če bi želel poiskati vsa imena, povezana s KT?

To funkcijo uporabljam za iskanje celic, ki vsebujejo več besedila. Na primer, če bi imel vsak izdelek tudi podizdelek v isti celici, vendar sem iskal samo imena, povezana s podizdelkom "elf".

KTE - elf
KTE- žoga
KTE - klavir
KTO - elf
KTO- žoga
KTO - klavir
Ta komentar je moderator na spletnem mestu minimiziral
Ali obstaja način, da to deluje, medtem ko DOVOLJUJETE podvojene vrednosti? Na primer, želim, da so vsi primerki Lucy navedeni v rezultatih.
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni, Konstantin,
Če želite izluščiti vse ustrezne vrednosti, vključno z dvojniki na podlagi določenih meril celice, vam lahko pomaga naslednja formula matrike, glejte posnetek zaslona:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

Ko vstavite formulo, pritisnite Shift + Ctrl + Enter skupaj, da dobite pravilen rezultat, nato pa povlecite ročico za polnjenje navzdol, da dobite vse vrednosti.
Upam, da vam to lahko pomaga, hvala!
Tu še ni objavljenih komentarjev
Obremenitev Več
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