Ustvarjanje iskalnega polja v Excelu izboljša funkcionalnost vaših preglednic, saj olajša filtriranje in hiter dostop do določenih podatkov. Ta priročnik pokriva več načinov za implementacijo iskalnega polja, ki skrbijo za različne različice Excela. Ne glede na to, ali ste začetnik ali napreden uporabnik, vam bodo ti koraki pomagali nastaviti dinamično iskalno polje z uporabo funkcij, kot so funkcija FILTER, pogojno oblikovanje in različne formule.

• Preprosto ustvarite iskalno polje z funkcija FILTER
  (na voljo v Excelu 2019 in novejšem, Excel za Microsoft 365)
• Ustvarite iskalno polje z uporabo Pogojno oblikovanje
  (na voljo v vseh različicah Excela)
• Ustvarite iskalno polje z kombinacije formul
  (na voljo v vseh različicah Excela)

Enostavno ustvarite iskalno polje s funkcijo FILTER

• Ta funkcija samodejno posodobi izpis, ko se vaši podatki spremenijo.
• Funkcija FILTER lahko vrne poljubno število rezultatov, od ene vrstice do več tisoč, odvisno od tega, koliko vnosov v vašem naboru podatkov se ujema z merili, ki ste jih nastavili.

Tukaj vam bom pokazal, kako uporabiti funkcijo FILTER za ustvarjanje iskalnega polja v Excelu.

1. korak: Vstavite besedilno polje in konfigurirajte lastnosti

1. Pojdi na Razvojni jeziček, kliknite Vstavi > Text Box (kontrolnik ActiveX).
   Nasvet: Če je Razvojni ni prikazan na traku, ga lahko omogočite tako, da sledite navodilom v tej vadnici: Kako prikazati / prikazati zavihek razvijalca v Excelovem traku?
   
2. Kazalec se bo spremenil v križec, nato pa morate povleči kazalec, da narišete besedilno polje na mestu na delovnem listu, kamor želite postaviti besedilno polje. Ko narišete besedilno polje, spustite miško.
   
3. Z desno miškino tipko kliknite besedilno polje in izberite Nepremičnine iz kontekstnega menija.
   
4. v Nepremičnine povežite besedilno polje s celico tako, da v polje vnesete referenco celice Povezana celica polje. Na primer, vtipkate "J2« zagotavlja, da se vsi podatki, vneseni v besedilno polje, samodejno posodobijo v celici J2 in obratno.
   
5. Kliknite Način oblikovanja pod Razvojni za izhod iz načina oblikovanja.
   

Besedilno polje zdaj omogoča vnos besedila.

2. korak: Uporabite funkcijo FILTER

1. Pred uporabo funkcije FILTER kopirajte prvotno vrstico glave v novo območje. Tukaj postavim vrstico z glavo pod iskalno polje.
   Nasvet: Ta pristop omogoča uporabnikom, da jasno vidijo rezultate pod istimi naslovi stolpcev kot izvirni podatki.
   
2. Izberite celico pod prvo glavo (npr I5 v tem primeru), vanj vnesite naslednjo formulo in pritisnite Vnesite ključ, da dobite rezultat.

   =FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")

   
   Kot je prikazano na zgornjem posnetku zaslona, ​​ker besedilno polje zdaj nima vnosa, formula prikaže rezultat "Podatkov ni mogoče najti"v I5.

Rezultat: preizkusite iskalno polje

Preizkusimo zdaj iskalno polje. V tem primeru, ko v iskalno polje vnesem ime stranke, bodo ustrezni rezultati filtrirani in takoj prikazani.

Ustvarite iskalno polje s pogojnim oblikovanjem

Pogojno oblikovanje lahko uporabite za označevanje podatkov, ki se ujemajo z iskalnim izrazom, s čimer posredno ustvarite učinek iskalnega polja. Ta metoda ne filtrira podatkov, ampak vas vizualno vodi do ustreznih celic. Ta razdelek vam bo pokazal, kako ustvarite iskalno polje s pogojnim oblikovanjem v Excelu.

1. korak: Vstavite besedilno polje in konfigurirajte lastnosti

1. Pojdi na Razvojni jeziček, kliknite Vstavi > Text Box (kontrolnik ActiveX).
   Nasvet: Če je Razvojni ni prikazan na traku, ga lahko omogočite tako, da sledite navodilom v tej vadnici: Kako prikazati / prikazati zavihek razvijalca v Excelovem traku?
   
2. Kazalec se bo spremenil v križec, nato pa morate povleči kazalec, da narišete besedilno polje na mestu na delovnem listu, kamor želite postaviti besedilno polje. Ko narišete besedilno polje, spustite miško.
   
3. Z desno miškino tipko kliknite besedilno polje in izberite Nepremičnine iz kontekstnega menija.
   
4. v Nepremičnine povežite besedilno polje s celico tako, da v polje vnesete referenco celice Povezana celica polje. Na primer, vtipkate "J3« zagotavlja, da se vsi podatki, vneseni v besedilno polje, samodejno posodobijo v celici J3 in obratno.
   
5. Kliknite Način oblikovanja pod Razvojni za izhod iz načina oblikovanja.
   

Besedilno polje zdaj omogoča vnos besedila.

2. korak: Uporabite pogojno oblikovanje za iskanje podatkov

1. Izberite celoten obseg podatkov za iskanje. Tukaj izberem obseg A3:G279.
2. Pod Domov jeziček, kliknite Pogojno oblikovanje > Novo pravilo.
   
3. v Novo pravilo oblikovanja pogovorno okno:
   1. Izberite S formulo določite, katere celice želite formatirati v Izberite vrsto pravila opcije.
   2. Vnesite naslednjo formulo v Oblikujte vrednosti, kjer je ta formula resnična škatla.

      =$B3=$J$3

      Tu $ B3 predstavlja prvo celico v stolpcu, ki jo želite ujemati z iskalnimi kriteriji v izbranem obsegu, in $J$3 je celica, povezana z iskalnim poljem.
   3. Kliknite oblikovana gumb za določitev barve polnila za rezultate iskanja.
   4. Kliknite OK . Oglejte si posnetek zaslona:
      

Rezultat

Preizkusimo zdaj iskalno polje. V tem primeru, ko v iskalno polje vnesem ime stranke, bodo ustrezne vrstice, ki vsebujejo to stranko v stolpcu B, takoj označene z določeno barvo polnila.

Ustvarite iskalno polje s kombinacijami formul

Če ne uporabljate najnovejše različice Excela in ne želite samo označevati vrstic, vam bo morda v pomoč metoda, opisana v tem razdelku. S kombinacijo Excelovih formul lahko ustvarite funkcionalno iskalno polje v kateri koli različici Excela. Sledite spodnjim korakom.

1. korak: Ustvarite seznam edinstvenih vrednosti iz iskalnega stolpca

1. V tem primeru izberem in kopiram obseg B4: B281 na nov delovni list.
2. Ko prilepite obseg na nov delovni list, pustite prilepljene podatke izbrane, pojdite na datum Kartico in izberite Odstrani dvojnike.
   
3. V uvodu Odstrani dvojnike pogovorno okno, kliknite na OK gumb.
   
4. A Microsoft Excel nato se prikaže okno s pozivom, ki prikazuje, koliko dvojnikov je bilo odstranjenih. Kliknite OK.
   
5. Ko odstranite dvojnike, izberite vse edinstvene vrednosti na seznamu, razen glave, in temu obsegu dodelite ime, tako da ga vnesete v Ime škatla. Tukaj sem obseg poimenoval kot Pomoč.
   

2. korak: Vstavite kombinirano polje in konfigurirajte lastnosti

1. Vrnite se na delovni list, ki vsebuje nabor podatkov, ki ga želite iskati. Pojdi na Razvojni jeziček, kliknite Vstavi > Kombinirana škatla (nadzor ActiveX).
   Nasvet: Če je Razvojni ni prikazan na traku, ga lahko omogočite tako, da sledite navodilom v tej vadnici: Kako prikazati / prikazati zavihek razvijalca v Excelovem traku?
   
2. Kazalec se bo spremenil v križec, nato pa morate povleči kazalec, da narišete kombinirano polje na mestu na delovnem listu, kamor želite postaviti iskalno polje. Ko narišete kombinirano polje, spustite miško.
   
3. Z desno miškino tipko kliknite kombinirano polje in izberite Nepremičnine iz kontekstnega menija.
   
4. v Nepremičnine podokno:
   1. Kombinirano polje povežite s celico tako, da v polje vnesete referenco celice Povezana celica polje. Njen tip "M2".
      Namig: določitev tega polja zagotavlja, da se bodo vsi podatki, vneseni v kombinirano polje, samodejno posodobili v celici M2 in obratno.
   2. v ListFillRange polje vnesite ime območja ki ste ga določili za enolični seznam v 1. koraku.
   3. Spreminjanje MatchEntry polje v 2 – fmMatchEntryNone.
   4. Zapri Nepremičnine okno.
      
5. Kliknite Način oblikovanja pod Razvojni za izhod iz načina oblikovanja.
   

Zdaj lahko izberete poljuben element iz kombiniranega polja ali vnesete besedilo, ki ga želite poiskati.

3. korak: Uporabite formule

1. Ustvarite tri pomožne stolpce poleg izvirnega obsega podatkov. Oglejte si posnetek zaslona:
   
2. V celici (H5) pod naslovom prvega pomožnega stolpca vnesite naslednjo formulo in pritisnite Vnesite.

   =ROWS($B$5:B5)

   Tukaj B5 je celica, ki vsebuje prvo ime stranke v stolpcu za iskanje.
   
3. Dvokliknite spodnji desni kot celice formule, naslednja celica bo samodejno izpolnila isto formulo.
   
4. V celici (I5) pod glavo drugega pomožnega stolpca vnesite naslednjo formulo in pritisnite Vnesite. Nato dvokliknite spodnji desni kot celice s formulo, da samodejno zapolnite spodnje celice z isto formulo.

   =IF(ISNUMBER(SEARCH($M$2,B5)),H5,"")

   Tukaj M2 je celica, povezana s kombiniranim poljem.
   
5. V celici (J5) pod glavo tretjega pomožnega stolpca vnesite naslednjo formulo in pritisnite Vnesite. Nato dvokliknite spodnji desni kot celice s formulo, da samodejno zapolnite spodnje celice z isto formulo.

   =IFERROR(SMALL($I$5:$I$281,H5),"") 

   
6. Kopirajte prvotno vrstico glave v novo območje. Tukaj postavim vrstico z glavo pod iskalno polje.
   
7. Izberite celico pod prvo glavo (npr L5 v tem primeru), vanj vnesite naslednjo formulo in pritisnite tipko Enter.

   =IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")

   Tukaj A5: G281 je celoten obseg podatkov, ki ga želite prikazati v celici z rezultati.
   
8. Izberite to celico formule, povlecite Ročica za polnjenje v desno in nato navzdol, da uporabite formulo za ustrezne stolpce in vrstice.
   
   Opombe:

   • Ker v iskalno polje ni vnosa, bodo rezultati formule prikazali neobdelane podatke.
   • Ta metoda ne razlikuje med velikimi in malimi črkami, kar pomeni, da se bo ujemala z besedilom ne glede na to, ali vnašate velike ali male črke.

Rezultat

Preizkusimo zdaj iskalno polje. V tem primeru, ko v kombiniranem polju vnesem ali izberem ime stranke, bodo ustrezne vrstice, ki vsebujejo to ime stranke v stolpcu B, filtrirane in takoj prikazane v obsegu rezultatov.

Ustvarjanje iskalnega polja v Excelu lahko znatno izboljša vašo interakcijo s podatki, zaradi česar bodo vaše preglednice bolj dinamične in uporabniku prijazne. Ne glede na to, ali izberete preprostost funkcije FILTER, vizualno pomoč pogojnega oblikovanja ali vsestranskost kombinacij formul, vsaka metoda nudi dragocena orodja za izboljšanje vaših zmožnosti obdelave podatkov. Eksperimentirajte s temi tehnikami, da ugotovite, katera najbolje deluje za vaše posebne potrebe in podatkovne scenarije. Za tiste, ki se želijo poglobiti v zmogljivosti Excela, se naše spletno mesto ponaša s številnimi vadnicami. Tukaj odkrijte več nasvetov in trikov za Excel.

Povezani članki