Excel-ում բացվող ցուցակների ստեղծումը հեշտացնում է տվյալների մուտքագրումը և նվազագույնի հասցնում սխալները: Բայց ավելի մեծ տվյալների հավաքածուների դեպքում երկար ցուցակների միջով ոլորելը դառնում է դժվար: Ավելի հեշտ չի՞ լինի պարզապես մուտքագրել և արագ գտնել ձեր իրը: Ա"որոնելի բացվող ցուցակ«Առաջարկում է այս հարմարությունը: Այս ուղեցույցը կուղեկցի ձեզ Excel-ում նման ցուցակ ստեղծելու չորս եղանակներով:

• Որոնելի բացվող ցուցակ Excel 365-ում
• Ստեղծեք որոնելի բացվող ցուցակ (Excel 2019 և ավելի ուշ)
• Հեշտությամբ ստեղծեք որոնելի բացվող ցուցակ (Excel-ի բոլոր տարբերակների համար)
• Ստեղծեք որոնելի բացվող ցուցակ Combo box-ով և VBA-ով (ավելի բարդ)

Տեսանյութ

Որոնելի բացվող ցուցակ Excel 365-ում

Excel 365-ն իր տվյալների վավերացման բացվող ցուցակներում ներդրել է շատ սպասված հատկություն՝ ցանկի ներսում որոնելու հնարավորություն: Որոնվող ֆունկցիոնալությամբ օգտվողները կարող են արագ գտնել և ընտրել իրերն ավելի արդյունավետ կերպով: Բացվող ցուցակը սովորականի պես տեղադրելուց հետո պարզապես սեղմեք բացվող ցուցակով բջիջի վրա և սկսեք մուտքագրել: Ցանկը անմիջապես կզտվի՝ համապատասխանեցնելով մուտքագրված տեքստին:

Այս դեպքում ես մուտքագրում եմ San վանդակում և բացվող ցանկը զտում է քաղաքները, որոնք սկսվում են որոնման տերմինով San, Օրինակ, Սան Ֆրանցիսկո և San Diego. Այնուհետև կարող եք մկնիկի օգնությամբ ընտրել արդյունքը կամ օգտագործել սլաքների ստեղները և սեղմել Enter:

Ստեղծեք որոնելի բացվող ցուցակ (Excel 2019 և ավելի ուշ)

Եթե ​​դուք օգտագործում եք Excel 2019 կամ ավելի ուշ տարբերակները, այս բաժնի մեթոդը կարող է օգտագործվել նաև Excel-ում բացվող ցանկը որոնելի դարձնելու համար:

Ենթադրելով, որ Sheet2-ի A2 բջիջում բացվող ցուցակ եք ստեղծել (պատկերը աջ կողմում)՝ օգտագործելով Sheet2-ի A8:A1 տիրույթի տվյալները (ձախ կողմում գտնվող պատկերը), հետևեք այս քայլերին՝ ցանկը որոնելի դարձնելու համար:

Քայլ 1. Ստեղծեք օգնական սյունակ, որը թվարկում է որոնման կետերը

Այստեղ մեզ անհրաժեշտ է օգնական սյունակ՝ ձեր աղբյուրի տվյալներին համապատասխանող տարրերը թվարկելու համար: Այս դեպքում ես կստեղծեմ օգնական սյունակը սյունակ Դ of Sheet1.

1. Ընտրեք առաջին բջիջը D1 Դ սյունակում և մուտքագրեք սյունակի վերնագիրը, օրինակ՝ «Որոնման արդյունքները" այս դեպքում.
2. Մուտքագրեք հետևյալ բանաձևը D2 բջիջում և սեղմեք Մտնել.

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

   

Քայլ 2. Վերակազմավորեք բացվող ցանկը

1. Ընտրեք բացվող ցուցակի բջիջը (այս դեպքում ես ընտրում եմ Sheet2-ի A2 բջիջը), այնուհետև անցեք ընտրելու Ամսաթիվ > Տվյալների վավերացում > Տվյալների վավերացում.
   
2. Է Տվյալների վավերացում երկխոսության տուփ, դուք պետք է կազմաձևեք հետևյալ կերպ.
   1. Տակ է Կարգավորումներ ներդիրը, սեղմեք կոճակը Աղբյուր տուփ:
      
   2. The Տվյալների վավերացում Երկխոսության տուփը կվերահղվի Sheet1, ընտրեք բջիջը (օրինակ, D2) 1-ին քայլի բանաձևով, ավելացրեք # խորհրդանիշը և կտտացրեք այն փակել կոճակը:
      
   3. Կարդացեք Erգուշացման սխալ նիշք, նշեք այն Invույց տալ սխալ ազդանշանը անվավեր տվյալների մուտքագրումից հետո վանդակը, և վերջապես կտտացրեք OK փոփոխությունները փրկելու համար:
      

Արդյունք

Sheet2-ի A2 բջիջի բացվող ցանկն այժմ հնարավոր է որոնել: Մուտքագրեք տեքստը վանդակում, սեղմեք բացվող սլաքի վրա՝ բացվող ցուցակը ընդլայնելու համար, և կտեսնեք, որ ցուցակն անմիջապես զտված է՝ տպագրված տեքստին համապատասխանելու համար:

Հեշտությամբ ստեղծեք որոնելի բացվող ցուցակ (Excel-ի բոլոր տարբերակների համար)

Հաշվի առնելով վերը նշված մեթոդների տարբեր սահմանափակումները, ահա ձեզ համար շատ արդյունավետ գործիք. Excel- ի համար նախատեսված գործիքներ's Բացվող ցանկը դարձրեք որոնելի, ավտոմատ բացվողհատկանիշ. Այս հատկությունը հասանելի է Excel-ի բոլոր տարբերակներում և թույլ է տալիս հեշտությամբ որոնել ցանկալի տարրը բացվող ցանկում՝ պարզ կարգավորումների միջոցով:

Այն բանից հետո Excel-ի համար Kutools-ի ներբեռնում և տեղադրումընտրեք Կուտոլս > Բացվող ցուցակ > Բացվող ցանկը դարձրեք որոնելի, ավտոմատ բացվող այս հնարավորությունը միացնելու համար: Մեջ Բացվող ցանկը դարձրեք որոնելի երկխոսության տուփ, անհրաժեշտ է.

1. Ընտրեք բացվող ցուցակները պարունակող միջակայքը, որոնք պետք է սահմանվեն որպես որոնելի բացվող ցուցակներ:
2. Սեղմել OK պարամետրերը լրացնելու համար:
   

Արդյունք

Երբ դուք սեղմում եք բացվող ցանկի բջիջը նշված տիրույթում, աջ կողմում հայտնվում է ցուցակի վանդակ: Մուտքագրեք տեքստ՝ ցանկն անմիջապես զտելու համար, այնուհետև ընտրեք որևէ տարր կամ օգտագործեք սլաքների ստեղները և սեղմեք Մտնել այն բջիջին ավելացնելու համար:

Ստեղծեք որոնելի բացվող ցուցակ Combo box-ով և VBA-ով (ավելի բարդ)

Եթե ​​դուք պարզապես ցանկանում եք ստեղծել որոնելի բացվող ցուցակ՝ առանց նշված բացվող ցուցակի տեսակը նշելու: Այս բաժինը տրամադրում է այլընտրանքային մոտեցում՝ առաջադրանքին հասնելու համար VBA կոդով Combo տուփի օգտագործումը:

Ենթադրենք, որ դուք ունեք երկրների անունների ցանկ A սյունակում, ինչպես ցույց է տրված ստորև ներկայացված սքրինշոթում, և այժմ ցանկանում եք դրանք օգտագործել որպես որոնման բացվող ցուցակների սկզբնաղբյուր տվյալներ, կարող եք անել հետևյալը, որպեսզի այն կատարվի:

Ձեր աշխատաթերթում տվյալների վավերացման բացվող ցուցակի փոխարեն պետք է տեղադրեք Combo տուփ:

1. Եթե Երեվակիչ ներդիրը չի ցուցադրվում ժապավենի վրա, կարող եք միացնել Երեվակիչ էջանիշը հետեւյալ կերպ.
   1. Excel 2010 կամ ավելի ուշ տարբերակներում սեղմեք Ֆայլ > Ընտրանքներ. Եվ է Excel Ընտրանքներ երկխոսության վանդակում, սեղմեք Անհատականացրեք ժապավենը ձախ պատուհանում: Գնացեք Անհատականացրեք ժապավենի ցանկի վանդակը, նշեք Երեվակիչ տուփը, ապա կտտացրեք OK կոճակ Տեսեք,
      
   2. Excel 2007-ում կտտացրեք Գրասենյակ կոճակ> Excel ընտրանքներ, Մեջ Excel ընտրանքներ երկխոսության վանդակում, սեղմեք ժողովրդական ձախ պատուհանում ստուգեք Developուցադրել erրագրավորողի ներդիրը ibապավենում տուփը և վերջապես կտտացրեք այն OK կոճակը:
      
2. Ցուցադրելուց հետո Երեվակիչ էջանշանը, սեղմեք Երեվակիչ > Տեղադրել > Կոմբո տուփ.
   
3. Աշխատանքային թերթում նկարեք Combo տուփ, աջ սեղմեք դրա վրա և ընտրեք Հատկություններ աջ կտտացնելու ցանկից:
   
4. Է Հատկություններ երկխոսության տուփ, անհրաժեշտ է.
   1. ընտրել Կեղծ է AutoWordSelect- ը դաշտային;
   2. Նշեք բջիջը LinkedCell- ը դաշտային Այս դեպքում մենք մտնում ենք A12;
   3. ընտրել 2-fmMatchEntryՈչ է MatchEntry դաշտային;
   4. Տիպ DropDownList մեջ ListFillRange դաշտային;
   5. Փակեք Հատկություններ երկխոսության տուփ: Տեսեք,
      
5. Այժմ անջատեք դիզայնի ռեժիմը՝ սեղմելով Երեվակիչ > Դիզայնի ռեժիմ.
6. Ընտրեք դատարկ բջիջ, ինչպիսին է C2-ը, մուտքագրեք ստորև բերված բանաձևը և սեղմեք Մտնել. Նրանք քաշում են իր AutoFill Handle-ը դեպի C9 բջիջ՝ բջիջները նույն բանաձևով ավտոմատ լցնելու համար: Տես սքրինշոթը.

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

   
   Notes:

   1. $ A $ 12 այն բջիջն է, որը դուք նշել եք որպես LinkedCell- ը 4-րդ քայլում;
   2. Վերոնշյալ քայլերն ավարտելուց հետո այժմ կարող եք փորձարկել. մուտքագրեք C տառը համակցված վանդակում, այնուհետև կարող եք տեսնել, որ բանաձևի բջիջները, որոնք հղում են անում C նիշը պարունակող բջիջներին, լցված են 1 թվով:
7. Ընտրեք D2 բջիջը, մուտքագրեք ստորև բերված բանաձևը և սեղմեք Մտնել. Այնուհետև քաշեք դրա Autofill Handle-ը դեպի D9 բջիջ:

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

   
8. Ընտրեք E2 բջիջը, մուտքագրեք ստորև բերված բանաձևը և սեղմեք Մտնել. Այնուհետև քաշեք իր AutoFill Handle-ը մինչև E9՝ նույն բանաձևը կիրառելու համար:

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

   
9. Այժմ դուք պետք է ստեղծեք անվանական տիրույթ: Խնդրում ենք սեղմել Ֆորմուլա > Սահմանեք անունը.
   
10. Է Նոր անուն երկխոսության տուփ, տիպ DropDownList է Անուն վանդակում, մուտքագրեք ստորև բերված բանաձևը Վերաբերում է տուփը, ապա կտտացրեք OK կոճակը:

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

    
11. Այժմ միացրեք դիզայնի ռեժիմը՝ սեղմելով Երեվակիչ > Դիզայնի ռեժիմ. Այնուհետև կրկնակի սեղմեք Combo տուփի վրա՝ բացելու համար Microsoft Visual Basic հավելվածների համար պատուհան.
12. Պատճենեք և տեղադրեք ներքևում գտնվող VBA կոդը կոդի խմբագրիչում:
    
    VBA կոդ. Բացվող ցուցակը դարձրեք որոնելի

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

13. Սեղմեք ալտ + Q ստեղները փակելու համար Microsoft Visual Basic հավելվածների համար պատուհան.

Այսուհետ, երբ նիշը մուտքագրվի համակցված վանդակում, այն կկատարի մշուշոտ որոնում և այնուհետև ցուցակում համապատասխան արժեքները կցուցադրի:

Առնչվող հոդվածներ քանակը: