Skip i'r prif gynnwys

Sut i greu eich blwch chwilio eich hun yn Excel?

Ac eithrio defnyddio'r swyddogaeth Dod o Hyd yn Excel, mewn gwirionedd gallwch greu eich blwch chwilio eich hun ar gyfer chwilio'r gwerthoedd sydd eu hangen yn hawdd. Bydd yr erthygl hon yn dangos dau ddull i chi greu eich blwch chwilio eich hun yn Excel yn fanwl.

Creu eich blwch chwilio eich hun gyda Fformatio Amodol i dynnu sylw at yr holl ganlyniadau a chwiliwyd
Creu eich blwch chwilio eich hun gyda fformwlâu i restru'r holl ganlyniadau a chwiliwyd


Creu eich blwch chwilio eich hun gyda Fformatio Amodol i dynnu sylw at yr holl ganlyniadau a chwiliwyd

Gallwch wneud fel a ganlyn i greu eich blwch chwilio eich hun trwy ddefnyddio'r swyddogaeth Fformatio Amodol yn Excel.

1. Dewiswch yr ystod gyda data y mae angen i chi chwilio yn ôl y blwch chwilio, yna cliciwch Fformatio Amodol > Rheol Newydd O dan y Hafan tab. Gweler y screenshot:

2. Yn y Rheol Fformatio Newydd blwch deialog, mae angen i chi:

2.1) Dewis Defnyddiwch fformiwla i bennu pa gelloedd i'w fformatio opsiwn yn y Dewiswch Math o Reol blwch;

2.2) Rhowch fformiwla = YNYS (CHWILIO ($ B $ 2, A5)) i mewn i'r Gwerthoedd fformat lle mae'r fformiwla hon yn wir blwch;

2.3) Cliciwch y fformat botwm i nodi lliw wedi'i amlygu ar gyfer y gwerth a chwiliwyd;

2.4) Cliciwch y OK botwm.

Nodiadau:

1. Yn y fformiwla, mae $ B $ 2 yn gell wag y mae angen i chi ei defnyddio fel blwch chwilio, ac A5 yw cell gyntaf yr ystod a ddewiswyd y mae angen i chi chwilio gwerthoedd oddi mewn iddi. Newidiwch nhw yn ôl yr angen.

2. Nid yw'r fformiwla yn sensitif i achosion.

Nawr bod y blwch chwilio wedi'i greu, wrth deipio meini prawf chwilio yn y blwch chwilio B2 a phwyso'r fysell Enter, mae'r holl werthoedd cyfatebol yn yr ystod benodol yn cael eu chwilio a'u hamlygu ar unwaith fel y dangosir isod y screenshot.


Creu eich blwch chwilio eich hun gyda fformwlâu i restru'r holl ganlyniadau a chwiliwyd

Gan dybio bod gennych chi restr ddata sy'n lleoli yn ystod E4: E23 y mae angen i chi ei chwilio, os ydych chi am restru'r holl werthoedd cyfatebol mewn colofn arall ar ôl chwilio gyda'ch blwch chwilio eich hun, gallwch roi cynnig ar y dull isod.

1. Dewiswch gell wag sy'n gyfagos i gell E4, dyma fi'n dewis cell D4, yna nodi'r fformiwla = IFERROR (CHWILIO ($ B $ 2, E4) + ROW () / 100000, "") i mewn i'r bar fformiwla, ac yna pwyswch y Rhowch allwedd. Gweler y screenshot:

Nodyn: Yn y fformiwla, $ B $ 2 yw'r gell y mae angen i chi ei defnyddio fel blwch chwilio, E4 yw cell gyntaf y rhestr ddata y mae angen i chi ei chwilio. Gallwch eu newid yn ôl yr angen.

2. Daliwch i ddewis cell E4, yna llusgwch y Llen Trin i lawr i gell D23. Gweler y screenshot:

3. Nawr dewiswch gell C4, nodwch y fformiwla = IFERROR (RANK (D4, $ D $ 4: $ D $ 23,1), "") i mewn i'r Bar Fformiwla, a gwasgwch y Rhowch allwedd. Dewiswch gell C4, yna llusgwch y Llen Trin i lawr i C23. Gweler y screenshot:

4. Nawr mae angen i chi lenwi ystod A4: A23 gyda rhif cyfres sy'n cynyddu 1 o 1 i 20 fel y nodir isod:

5. Dewiswch gell wag sydd ei hangen arnoch i arddangos y canlyniad a chwiliwyd, nodwch y fformiwla = IFERROR (VLOOKUP (A4, $ C $ 4: $ E $ 23,3, GAU), "") i mewn i'r Bar Fformiwla a gwasgwch y Rhowch allwedd. Daliwch i ddewis cell B4, llusgwch y Llenwch Trin i lawr i B23 fel y dangosir y llun isod.

O hyn ymlaen, wrth fewnbynnu data ym mlwch chwilio B2, bydd yr holl werthoedd cyfatebol yn cael eu rhestru yn ystod B4: B23 fel y dangosir isod y screenshot.

Nodyn: nid yw'r dull hwn yn sensitif i achosion.

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog    VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr    Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd    Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad


Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
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