Mae creu cwymplenni yn Excel yn symleiddio mewnbynnu data ac yn lleihau gwallau. Ond gyda setiau data mwy, mae sgrolio trwy restrau hir yn mynd yn feichus. Oni fyddai'n haws teipio a lleoli eich eitem yn gyflym? A"gwymplen chwiliadwy" yn cynnig y cyfleustra hwn. Bydd y canllaw hwn yn eich arwain trwy bedwar dull i sefydlu rhestr o'r fath yn Excel.

• Rhestr gwympo chwiliadwy yn Excel 365
• Creu cwymplen chwiliadwy (ar gyfer Excel 2019 ac yn ddiweddarach)
• Creu cwymplen chwiliadwy yn hawdd (ar gyfer pob fersiwn Excel)
• Creu cwymplen chwiliadwy gyda blwch Combo a VBA (mwy cymhleth)

fideo

Rhestr gwympo chwiliadwy yn Excel 365

Mae Excel 365 wedi cyflwyno nodwedd y bu disgwyl mawr amdani i'w gwymplenni dilysu data: y gallu i chwilio o fewn y rhestr. Gyda'r swyddogaeth chwiliadwy, gall defnyddwyr ddod o hyd i eitemau a'u dewis yn gyflym mewn modd mwy effeithlon. Ar ôl mewnosod y gwymplen fel arfer, cliciwch ar gell gyda gwymplen a dechrau teipio. Bydd y rhestr yn hidlo ar unwaith i gyd-fynd â'r testun a deipiwyd.

Yn yr achos hwn, rwy'n teipio Sanz yn y gell ac mae'r gwymplen yn hidlo dinasoedd sy'n dechrau gyda'r term chwilio Sanz, Megis San Francisco ac San Diego. Yna gallwch ddewis canlyniad gyda'ch llygoden neu ddefnyddio'r bysellau saeth a phwyso Enter.

Creu cwymplen chwiliadwy (ar gyfer Excel 2019 ac yn ddiweddarach)

Os ydych chi'n defnyddio Excel 2019 neu fersiynau diweddarach, gellir defnyddio'r dull yn yr adran hon hefyd i wneud rhestr ostwng y gellir ei chwilio yn Excel.

Gan dybio eich bod wedi creu cwymplen yng nghell A2 o Daflen2 (delwedd ar y dde) gan ddefnyddio data yn yr ystod A2:A8 o Daflen1 (delwedd ar y chwith), dilynwch y camau hyn i wneud y rhestr yn chwiliadwy.

Cam 1. Creu colofn helpwr sy'n rhestru'r eitemau chwilio

Yma mae angen colofn cynorthwyydd arnom i restru'r eitemau sy'n cyfateb i'ch data ffynhonnell. Yn yr achos hwn, byddaf yn creu'r golofn cynorthwyydd yn colofn D of Sheet1.

1. Dewiswch y gell gyntaf D1 yng ngholofn D a rhowch bennawd y golofn, fel "Canlyniadau chwilio" yn yr achos hwn.
2. Rhowch y fformiwla ganlynol i mewn i gell D2 a gwasgwch Rhowch.

   =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")

   

Cam 2: Ail-ffurfweddu'r gwymplen

1. Dewiswch gell y gwymplen (yn yr achos hwn, rwy'n dewis y gell A2 o Sheet2), yna ewch i ddewis Dyddiad > Dilysu Data > Dilysu Data.
   
2. Yn y Dilysu Data blwch deialog, mae angen i chi ffurfweddu fel a ganlyn.
   1. O dan y Gosodiadau tab, cliciwch ar botwm yn y ffynhonnell blwch.
      
   2. Mae adroddiadau Dilysu Data Bydd y blwch deialog yn ailgyfeirio i Daflen1, dewiswch y gell (ee, D2) gyda'r fformiwla o Gam 1, ychwanegwch a # symbol, a chliciwch ar y Cau botwm.
      
   3. Ewch i'r Rhybudd Gwall tab, dadgynnwch y Dangos rhybudd gwall ar ôl mewnbynnu data annilys blwch ticio, ac yn olaf cliciwch ar y OK botwm i achub y newidiadau.
      

Canlyniad

Mae'r gwymplen yng nghell A2 Taflen 2 bellach yn chwiliadwy. Teipiwch destun yn y gell, cliciwch ar y gwymplen i ehangu'r gwymplen, a byddwch yn gweld y rhestr wedi'i hidlo ar unwaith i gyd-fynd â'r testun a deipiwyd.

Creu cwymplen chwiliadwy yn hawdd (ar gyfer pob fersiwn Excel)

O ystyried cyfyngiadau amrywiol y dulliau uchod, dyma offeryn effeithiol iawn i chi - Kutools ar gyfer Excel's Gwneud Chwiliadwy ar y Rhestr Gollwng, Awto-neidlennodwedd. Mae'r nodwedd hon ar gael ym mhob fersiwn o Excel ac mae'n caniatáu ichi chwilio'n hawdd am yr eitem a ddymunir yn y gwymplen gyda gosodiad syml.

Ar ôl lawrlwytho a gosod Kutools ar gyfer Excel, dewiswch Kutools > Rhestr Gollwng > Gwneud Chwiliadwy ar y Rhestr Gollwng, Awto-neidlen i alluogi'r nodwedd hon. Yn y Gwnewch y Rhestr Gwymp yn Chwiliadwy blwch deialog, mae angen i chi:

1. Dewiswch yr ystod sy'n cynnwys y cwymplenni y mae angen eu gosod fel cwymplenni chwiliadwy.
2. Cliciwch OK i gwblhau'r gosodiadau.
   

Canlyniad

Pan gliciwch ar gell rhestr ddisgynnol yn yr ystod benodedig, mae blwch rhestr yn ymddangos i'r dde. Teipiwch destun i hidlo'r rhestr ar unwaith, yna dewiswch eitem neu defnyddiwch bysellau saeth a tharo Rhowch i'w ychwanegu at y gell.

Creu cwymplen chwiliadwy gyda blwch Combo a VBA (mwy cymhleth)

Os ydych chi eisiau creu cwymplen chwiliadwy heb nodi math penodol o gwymplen. Mae'r adran hon yn darparu dull amgen: defnyddio blwch Combo gyda chod VBA i gyflawni'r dasg.

Tybiwch fod gennych restr o enwau gwledydd yng ngholofn A fel y dangosir yn y sgrin isod, a nawr eich bod am eu defnyddio fel data ffynhonnell y cwymplenni chwiliadale, gallwch chi wneud fel a ganlyn i'w gyflawni.

Mae angen i chi fewnosod blwch Combo yn lle cwymprestr dilysu data yn eich taflen waith.

1. Os yw'r Datblygwr Nid yw tab yn arddangos ar y rhuban, gallwch chi alluogi'r Datblygwr tab fel a ganlyn.
   1. Yn Excel 2010 neu fersiynau diweddarach, cliciwch Ffeil > Dewisiadau. Ac yn y Opsiynau Excel blwch deialog, cliciwch Rhinwedd Customize yn y cwarel chwith. Ewch i'r blwch rhestr Addasu'r Rhuban, gwiriwch y Datblygwr blwch, ac yna cliciwch ar y OK botwm. Gweler y screenshot:
      
   2. Yn Excel 2007, cliciwch Swyddfa botwm> Dewisiadau Excel. Yn y Dewisiadau Excel blwch deialog, cliciwch poblogaidd yn y cwarel chwith, gwiriwch y Dangos tab Datblygwr yn y Rhuban blwch, ac yn olaf cliciwch y OK botwm.
      
2. Ar ol dangos y Datblygwr tab, cliciwch Datblygwr > Mewnosod > Blwch combo.
   
3. Tynnwch flwch Combo yn y daflen waith, cliciwch ar y dde ac yna dewiswch Eiddo o'r ddewislen clicio ar y dde.
   
4. Yn y Eiddo blwch deialog, mae angen i chi:
   1. dewiswch Anghywir yn y AutoWordSelect maes;
   2. Nodwch gell yn y LinkedCell maes. Yn yr achos hwn, rydym yn nodi A12;
   3. dewiswch 2-fmMatchEntryDim yn y MatchEntry maes;
   4. math Rhestr DropDown i mewn i'r RhestrFillRage maes;
   5. Caewch y Eiddo blwch deialog. Gweler y screenshot:
      
5. Nawr trowch oddi ar y modd dylunio trwy glicio Datblygwr > Modd Dylunio.
6. Dewiswch gell wag fel C2, rhowch y fformiwla isod a gwasgwch Rhowch. Maent yn llusgo ei AutoFill Handle i lawr i gell C9 i lenwi'r celloedd yn awtomatig gyda'r un fformiwla. Gweler y sgrinlun:

   =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))

   
   Nodiadau:

   1. $ A $ 12 yw'r gell rydych chi wedi'i nodi fel y LinkedCell yng ngham 4;
   2. Ar ôl gorffen y camau uchod, gallwch nawr brofi: rhowch lythyren C yn y blwch combo, ac yna gallwch weld bod y celloedd fformiwla sy'n cyfeirio at y celloedd sy'n cynnwys y nod C wedi'u llenwi â'r rhif 1.
7. Dewiswch y gell D2, nodwch y fformiwla isod a gwasgwch Rhowch. Yna llusgwch ei AutoFill Handle i lawr i'r gell D9.

   =IF(C2=1,COUNTIF($C$2:C2,1),"")

   
8. Dewiswch gell E2, nodwch y fformiwla isod a gwasgwch Rhowch. Yna llusgwch ei AutoFill Handle i lawr i E9 i gymhwyso'r un fformiwla.

   =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")

   
9. Nawr mae angen i chi greu ystod enwau. Cliciwch os gwelwch yn dda Fformiwla > Diffinio Enw.
   
10. Yn y Enw Newydd blwch deialog, math Rhestr DropDown yn y Enw blwch, nodwch y fformiwla isod yn y Yn cyfeirio at blwch, ac yna cliciwch ar y OK botwm.

    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    

    
11. Nawr, trowch y modd dylunio ymlaen trwy glicio Datblygwr > Modd Dylunio. Yna cliciwch ddwywaith ar y blwch Combo i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.
12. Copïwch a gludwch y cod VBA isod i mewn i'r golygydd Cod.
    
    Cod VBA: gwneud rhestr ostwng yn chwiliadwy

    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub

13. Gwasgwch y Alt + Q allweddi i gau'r Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

O hyn ymlaen, pan fydd nod yn cael ei roi yn y blwch combo, bydd yn gwneud chwiliad niwlog ac yna'n rhestru'r gwerthoedd perthnasol yn y rhestr.

Erthyglau cysylltiedig: