Preskoči na glavno vsebino

Kako ustvariti lastno iskalno polje v Excelu?

Razen funkcije iskanja v Excelu lahko dejansko ustvarite svoje iskalno polje za enostavno iskanje potrebnih vrednosti. Ta članek vam podrobno prikazuje dva načina za ustvarjanje lastnega iskalnega polja v Excelu.

Ustvarite svoje iskalno polje s pogojnim oblikovanjem, da označite vse iskane rezultate
Ustvarite svoje iskalno polje s formulami, v katerem bodo navedeni vsi rezultati iskanja


Ustvarite svoje iskalno polje s pogojnim oblikovanjem, da označite vse iskane rezultate

S pomočjo funkcije pogojnega oblikovanja v Excelu lahko ustvarite lastno iskalno polje na naslednji način.

1. V iskalnem polju izberite obseg s podatki, ki jih želite iskati, in kliknite Pogojno oblikovanje > Novo pravilo pod HOME zavihek. Oglejte si posnetek zaslona:

2. V Ljubljani Novo pravilo oblikovanja pogovorno okno, morate:

2.1) Izberite S formulo določite, katere celice želite formatirati možnost v Izberite vrsto pravila škatla;

2.2) Vnesite formulo = ŠTEVILO (ISKANJE ($ B $ 2, A5)) v Oblikujte vrednosti, kjer je ta formula resnična škatla;

2.3) Kliknite oblikovana gumb, da določite označeno barvo iskane vrednosti;

2.4) Kliknite OK gumb.

Opombe:

1. V formuli je $ B $ 2 prazna celica, ki jo morate uporabiti kot iskalno polje, A5 pa prva celica izbranega obsega, v kateri morate iskati vrednosti. Prosimo, spremenite jih po potrebi.

2. Formula ne razlikuje med velikimi in malimi črkami.

Zdaj je iskalno polje ustvarjeno, ko vtipkate iskalna merila v iskalno polje B2 in pritisnete tipko Enter, se vse iskane vrednosti v določenem obsegu poiščejo in takoj označijo, kot je prikazano spodaj.


Ustvarite svoje iskalno polje s formulami, v katerem bodo navedeni vsi rezultati iskanja

Recimo, da imate v obsegu E4: E23 seznam podatkov, ki ga morate iskati, če želite po iskalnem polju z lastnim iskalnim poljem v drugem stolpcu našteti vse ujemajoče se vrednosti, lahko poskusite s spodnjo metodo.

1. Izberite prazno celico, ki meji na celico E4, tu izberem celico D4, nato vnesem formulo = IFERROR (ISKANJE ($ B $ 2, E4) + RED () / 100000, "") v vrstico s formulami in pritisnite tipko Vnesite tipko. Oglejte si posnetek zaslona:

Opombe: V formuli je $ B $ 2 celica, ki jo želite uporabiti kot iskalno polje, E4 je prva celica seznama podatkov, ki ga morate iskati. Lahko jih spremenite po potrebi.

2. Nadaljujte z izbiro celice E4, nato povlecite ročico za polnjenje navzdol do celice D23. Oglejte si posnetek zaslona:

3. Zdaj izberite celico C4, vnesite formulo = IFERROR (RANG (D4, $ D $ 4: $ D $ 23,1), "") v vrstico formule in pritisnite Vnesite tipko. Izberite celico C4, nato povlecite ročico za polnjenje navzdol do C23. Oglejte si posnetek zaslona:

4. Zdaj morate obseg A4: A23 izpolniti s serijsko številko, ki se poveča za 1 z 1 na 20, kot je prikazano spodaj:

5. Izberite prazno celico, ki jo potrebujete za prikaz iskanega rezultata, vnesite formulo = IFERROR (VLOOKUP (A4, 4 C $: 23,3 E $, LAŽNO), "") v vrstico formule in pritisnite Vnesite tipko. Nadaljujte z izbiro celice B4, povlecite ročico za polnjenje navzdol do B23, kot je prikazano spodaj.

Od zdaj naprej bodo pri vnosu podatkov v iskalno polje B2 vse ujemajoče se vrednosti navedene v območju B4: B23, kot je prikazano spodaj.

Opombe: ta metoda ne razlikuje med velikimi in malimi črkami.

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

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!
Comments (29)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
me pueden apoyar en cual es la formula para solo buscar
porfa
This comment was minimized by the moderator on the site
Hi, for the second part: "Create Your Own Search Box With Formulas To List All Searched Results", it doesn't say what to do with the search box, my search box has no formula in it. I am trying to do it with multiple columns as well, does it work too?
This comment was minimized by the moderator on the site
Insert data from example. Copy formula in indicated cell, but delete space from formula. Easy!
This comment was minimized by the moderator on the site
i have tried using this but is dose not wont to highlight the box I am searching for why is this
This comment was minimized by the moderator on the site
Can you create a formula that captures two cells worth of information in retrospect i am using a set up that captures user names and badge data so i need it to when it filters that it carries both cells of information not just one
This comment was minimized by the moderator on the site
hi! I used the basic highlight search bar, but am having a couple of issues. it is predicting my search and finding it with no issues... however, it always highlights the cell a couple below or above the searched one. Are you able to help me with this please?
This comment was minimized by the moderator on the site
Hi! Is this applicable to Google Sheets? I'm trying to make a search box with formulas to list all searched results. I followed every steps listed above but the results shows nothing. There is an error tho, it says "change Iterative calculation". But whatever I do, it doesn't show anything - just blank. Can you help me with this, please... Thank you!
This comment was minimized by the moderator on the site
Good day,
This is only applicable to Microsoft Excel application. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Hi! Is this applicable to Google Sheets? I'm trying to make a search box with formulas to list all searched results. I followed every steps listed above but the results shows nothing. There is an error tho, it says "change Iterative calculation". But whatever I do, it doesn't show anything - just blank. Can you help me with this, please... Thank you!
This comment was minimized by the moderator on the site
Hello, I am using to search between my vendors (one column) and brands (another column). With this setup, there are brands (like Microsoft) that can have more vendors (vendor A, vendor B, vendor C,...). I would like to search for the brand (microsoft) name and would like to see all possible vendors (A, B & C as well). But now the result is only the first vendor and that's all. How can I change/fix that?

Many thanks!
This comment was minimized by the moderator on the site
Hi Tomas,
Maybe you can rearrange your data and create a dynamic drop down list to solve the problem. You can browse the below article for more details.
https://www.extendoffice.com/documents/excel/1350-excel-create-dynamic-drop-down-list.html
This comment was minimized by the moderator on the site
i followed the resulted search method and it worked perfectly however the results are hyperlinked and it shows me the result without the hyperlink is there a way i can make it show me the result with the link connection?
This comment was minimized by the moderator on the site
After entering the formula =ISNUMBER(SEARCH($B$1,A4)) for conditional formatting, if the cell I used for the search function is blank, all the cells that are searched (A4:C368) are highlighted. But once a string is entered for the search criteria the cells containing the search criteria are highlighted correctly. Is there a way to tweak the formula to not highlight until search criteria is entered? Or did I do something wrong?

Also, using the formula in step 5 on another sheet within the workbook isn't working. What I'm trying to do is perform a search and show results of that search on one sheet named Search & Results while having the information to be searched on a sheet named Index. The formula I'm using is =IFERROR(VLOOKUP(A3,Index!$A$4:$C$368,5,FALSE),"") where A3 is the beginning of my numbered cells on the Search & Results sheet and the search is taking place throughout cells A4:C368 on the Index sheet.
This comment was minimized by the moderator on the site
same had been having the same issue with the formula,try this in your conditional formatting rule "=AND($I$1<>"",ISNUMBER(SEARCH($I$1,$B4)))", it works for me
I1 is my search box, B4 is first cell of selected range
This comment was minimized by the moderator on the site
Hi Colby. Your vlookup formula=IFERROR(VLOOKUP(A3,Index!$A$4:$C$368,5,FALSE),"") refers to range from A4 to C368 in Sheet name index which have only 3 column but you are entering 5 right after $C$368 which tells the vlookup formula to display value of column 5 which does not exists in your selected range. Fix this and I believe that your problem will be solved.
This comment was minimized by the moderator on the site
I have the same problem. Did you get an answer?
This comment was minimized by the moderator on the site
also me. i have the same problem
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations