Paano gumawa ng dropdown list sa Excel

Ang drop-down list ay isang hindi kapani-paniwalang kapaki-pakinabang na tool na makakatulong na gawing mas komportable ang pagtatrabaho sa impormasyon. Ginagawa nitong posible na maglaman ng ilang mga halaga sa isang cell nang sabay-sabay, kung saan maaari kang magtrabaho, tulad ng iba pa. Upang piliin ang kailangan mo, mag-click lamang sa icon ng arrow, pagkatapos ay ipinapakita ang isang listahan ng mga halaga uXNUMXbuXNUMXbis. Pagkatapos pumili ng isang partikular na isa, ang cell ay awtomatikong napupunan dito, at ang mga formula ay muling kinakalkula batay dito.

Nagbibigay ang Excel ng maraming iba't ibang paraan para sa pagbuo ng drop-down na menu, at bilang karagdagan, pinapayagan ka nitong flexible na i-customize ang mga ito. Suriin natin ang mga pamamaraang ito nang mas detalyado.

Proseso ng paglikha ng listahan

Upang makabuo ng pop-up na menu, mag-click sa mga item sa menu sa kahabaan ng landas na “Data” – “Data Validation”. Magbubukas ang isang dialog box kung saan kailangan mong hanapin ang tab na "Mga Parameter" at i-click ito kung hindi pa ito nabuksan dati. Mayroon itong maraming mga setting, ngunit ang item na "Uri ng Data" ay mahalaga sa amin. Sa lahat ng kahulugan, "Listahan" ang tama.

Ang bilang ng mga pamamaraan kung saan ang impormasyon ay ipinasok sa listahan ng pop-up ay medyo malaki.

1. Independiyenteng indikasyon ng mga elemento ng listahan na pinaghihiwalay ng isang semicolon sa field na "Source" na matatagpuan sa parehong tab ng parehong dialog box.
   

   

2. Paunang indikasyon ng mga halaga. Ang field ng Pinagmulan ay naglalaman ng hanay kung saan available ang kinakailangang impormasyon.
   

   

3. Pagtukoy ng pinangalanang hanay. Isang paraan na inuulit ang nauna, ngunit kinakailangan lamang na paunang pangalanan ang hanay.
   

   

Ang alinman sa mga pamamaraang ito ay magbubunga ng ninanais na resulta. Tingnan natin ang mga pamamaraan para sa pagbuo ng mga drop-down na listahan sa totoong buhay na mga sitwasyon.

Batay sa data mula sa listahan

Sabihin nating mayroon tayong talahanayan na naglalarawan ng mga uri ng iba't ibang prutas.

Upang lumikha ng isang listahan sa isang drop-down na menu batay sa hanay ng impormasyong ito, kailangan mong gawin ang sumusunod:

1. Piliin ang cell na nakalaan para sa listahan sa hinaharap.
2. Hanapin ang tab na Data sa ribbon. Doon ay nag-click kami sa "I-verify ang data".
   

   

3. Hanapin ang item na "Uri ng Data" at ilipat ang halaga sa "Listahan".
   

   

4. Sa field na nagsasaad ng opsyong "Pinagmulan", ipasok ang nais na hanay. Pakitandaan na ang mga ganap na sanggunian ay dapat na tukuyin upang kapag kinokopya ang listahan, ang impormasyon ay hindi nagbabago.
   

Bilang karagdagan, mayroong isang function upang bumuo ng mga listahan nang sabay-sabay sa higit sa isang cell. Upang makamit ito, dapat mong piliin ang lahat ng ito, at gawin ang parehong mga hakbang tulad ng inilarawan kanina. Muli, kailangan mong tiyakin na ang mga ganap na sanggunian ay nakasulat. Kung ang address ay walang dollar sign sa tabi ng column at row name, kailangan mong idagdag ang mga ito sa pamamagitan ng pagpindot sa F4 key hanggang ang $ sign ay nasa tabi ng column at row name.

Gamit ang manu-manong pag-record ng data

Sa sitwasyon sa itaas, isinulat ang listahan sa pamamagitan ng pag-highlight sa kinakailangang hanay. Ito ay isang maginhawang paraan, ngunit kung minsan ito ay kinakailangan upang manu-manong i-record ang data. Gagawin nitong posible na maiwasan ang pagdoble ng impormasyon sa workbook.

Ipagpalagay na nahaharap tayo sa gawain ng paglikha ng isang listahan na naglalaman ng dalawang posibleng pagpipilian: oo at hindi. Upang maisagawa ang gawain, kinakailangan:

1. Mag-click sa cell para sa listahan.
2. Buksan ang "Data" at doon hanapin ang seksyong "Data Check" na pamilyar sa amin.
   

   

3. Muli, piliin ang uri ng "Listahan".
   

   

4. Dito kailangan mong ipasok ang "Oo; Hindi” bilang ang pinagmulan. Nakikita namin na ang impormasyon ay ipinasok nang manu-mano gamit ang isang semicolon para sa enumeration.

Pagkatapos i-click ang OK, mayroon kaming sumusunod na resulta.

Susunod, ang programa ay awtomatikong lilikha ng isang drop-down na menu sa naaangkop na cell. Lahat ng impormasyon na tinukoy ng user bilang mga item sa listahan ng pop-up. Ang mga patakaran para sa paglikha ng isang listahan sa ilang mga cell ay katulad ng mga nauna, na may tanging pagbubukod na dapat mong tukuyin ang impormasyon nang manu-mano gamit ang isang semicolon.

Paglikha ng isang drop-down na listahan gamit ang OFFSET function

Bilang karagdagan sa klasikal na pamamaraan, posible na gamitin ang function TANGGALINupang bumuo ng mga dropdown na menu.

Buksan natin ang sheet.

Upang magamit ang function para sa dropdown na listahan, kailangan mong gawin ang sumusunod:

1. Piliin ang cell ng interes kung saan mo gustong ilagay ang listahan sa hinaharap.
2. Buksan ang tab na “Data” at ang window ng “Data Validation” nang magkakasunod.
   

   

3. Itakda ang "Listahan". Ginagawa ito sa parehong paraan tulad ng mga nakaraang halimbawa. Sa wakas, ang sumusunod na formula ay ginagamit: =OFFSET(A$2$;0;0;5). Ipinasok namin ito kung saan tinukoy ang mga cell na gagamitin bilang argumento.

Pagkatapos ang programa ay lilikha ng isang menu na may listahan ng mga prutas.

Ang syntax para dito ay:

=OFFSET(reference,line_offset,column_offset,[taas],[lapad])

Nakita namin na ang function na ito ay may 5 argumento. Una, ibibigay ang unang cell address na ma-offset. Tinutukoy ng susunod na dalawang argumento kung gaano karaming mga row at column ang ii-offset. Speaking of us, ang Height argument ay 5 dahil kinakatawan nito ang taas ng listahan. 

Listahan ng dropdown sa Excel na may pagpapalit ng data (+ gamit ang OFFSET function)

Sa ibinigay na kaso TANGGALIN pinapayagang lumikha ng pop-up na menu na matatagpuan sa isang nakapirming saklaw. Ang kawalan ng pamamaraang ito ay pagkatapos idagdag ang item, kakailanganin mong i-edit ang formula sa iyong sarili.

Upang lumikha ng isang dynamic na listahan na may suporta para sa pagpasok ng bagong impormasyon, kailangan mong:

1. Piliin ang cell ng interes.
2. Palawakin ang tab na “Data” at mag-click sa “Data Validation”.
3. Sa window na bubukas, piliin muli ang item na "Listahan" at tukuyin ang sumusunod na formula bilang pinagmumulan ng data: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. -Click ang OK.

Naglalaman ito ng isang function COUNTIF, upang agad na matukoy kung gaano karaming mga cell ang napuno (bagaman ito ay may mas malaking bilang ng mga gamit, isinusulat lang namin ito dito para sa isang tiyak na layunin).

Upang gumana nang normal ang formula, kinakailangan upang masubaybayan kung may mga walang laman na cell sa landas ng formula. Hindi sila dapat.

Listahan ng dropdown na may data mula sa isa pang sheet o Excel file

Ang klasikong pamamaraan ay hindi gumagana kung kailangan mong kumuha ng impormasyon mula sa isa pang dokumento o kahit isang sheet na nakapaloob sa parehong file. Para dito, ginagamit ang function HINDI DIREKTA, na nagpapahintulot sa iyo na magpasok sa tamang format ng isang link sa isang cell na matatagpuan sa isa pang sheet o sa pangkalahatan - isang file. Kailangan mong gawin ang sumusunod:

1. I-activate ang cell kung saan namin inilalagay ang listahan.
2. Binuksan namin ang bintana na alam na namin. Sa parehong lugar kung saan nagpahiwatig kami dati ng mga mapagkukunan para sa iba pang mga hanay, ang isang formula ay ipinahiwatig sa format =INDIRECT(“[List1.xlsx]Sheet1!$A$1:$A$9”). Naturally, sa halip na List1 at Sheet1, maaari mong ipasok ang iyong mga pangalan ng libro at sheet, ayon sa pagkakabanggit. 

Pansin! Ang pangalan ng file ay ibinigay sa mga square bracket. Sa kasong ito, hindi magagamit ng Excel ang file na kasalukuyang nakasara bilang pinagmumulan ng impormasyon.

Dapat ding tandaan na ang pangalan ng file mismo ay may katuturan lamang kung ang kinakailangang dokumento ay matatagpuan sa parehong folder bilang isa kung saan ilalagay ang listahan. Kung hindi, dapat mong tukuyin ang address ng dokumentong ito nang buo.

Paglikha ng Mga Dependent Dropdown

Ang isang listahan ng umaasa ay isa na ang mga nilalaman ay apektado ng pagpili ng user sa isa pang listahan. Ipagpalagay na mayroon kaming isang talahanayan na nakabukas sa harap namin na naglalaman ng tatlong hanay, na ang bawat isa ay binigyan ng pangalan.

Kailangan mong sundin ang mga hakbang na ito upang makabuo ng mga listahan na ang resulta ay apektado ng opsyong pinili sa isa pang listahan.

1. Lumikha ng unang listahan na may mga pangalan ng saklaw.
   

   

2. Sa source entry point, isa-isang naka-highlight ang mga kinakailangang indicator.
   

   

3. Gumawa ng 2nd list depende sa uri ng halaman na pinili ng tao. Bilang kahalili, kung tinukoy mo ang mga puno sa unang listahan, ang impormasyon sa pangalawang listahan ay magiging "oak, hornbeam, chestnut" at higit pa. Kinakailangang isulat ang formula sa lugar ng input ng data source =INDIRECT(E3). E3 – cell na naglalaman ng pangalan ng hanay na 1.=INDIRECT(E3). E3 – cell na may pangalan ng listahan 1.

Ngayon ay handa na ang lahat.

Paano pumili ng maramihang mga halaga mula sa isang drop down na listahan?

Minsan hindi posibleng magbigay ng kagustuhan sa isang value lang, kaya higit sa isa ang dapat piliin. Pagkatapos ay kailangan mong magdagdag ng macro sa page code. Gamit ang kumbinasyon ng key na Alt + F11, magbubukas ang Visual Basic Editor. At ang code ay ipinasok doon.

Pribadong Sub Worksheet_Change (ByVal Target Bilang Saklaw)

    Sa Error Ipagpatuloy ang Susunod

    Kung Hindi Magsalubong(Target, Saklaw(«Е2:Е9»)) Ay Wala At Target.Cells.Count = 1 Pagkatapos

        Application.EnableEvents = False

        Kung Len (Target.Offset (0, 1)) = 0 Pagkatapos

            Target.Offset (0, 1) = Target

        iba

            Target.End (xlToRight) .Offset (0, 1) = Target

        Dulo Kung

        Target.ClearContents

        Application.EnableEvents = True

    Dulo Kung

End Sub 

Upang ang mga nilalaman ng mga cell ay maipakita sa ibaba, ipinasok namin ang sumusunod na code sa editor.

Pribadong Sub Worksheet_Change (ByVal Target Bilang Saklaw)

    Sa Error Ipagpatuloy ang Susunod

    Kung Hindi Magsalubong(Target, Saklaw(«Н2:К2»)) Ay Wala At Target.Cells.Count = 1 Pagkatapos

        Application.EnableEvents = False

        Kung Len (Target.Offset (1, 0)) = 0 Pagkatapos

            Target.Offset (1, 0) = Target

        iba

            Target.End (xlDown) .Offset (1, 0) = Target

        Dulo Kung

        Target.ClearContents

        Application.EnableEvents = True

    Dulo Kung

End Sub

At sa wakas, ang code na ito ay ginagamit upang magsulat sa isang cell.

Pribadong Sub Worksheet_Change (ByVal Target Bilang Saklaw)

    Sa Error Ipagpatuloy ang Susunod

    Kung Hindi Magsalubong(Target, Saklaw(«C2:C5»)) Ay Wala At Target.Cells.Count = 1 Pagkatapos

        Application.EnableEvents = False

        newVal = Target

        Application.I-undo

        oldval = Target

        Kung Len (oldval) <> 0 At oldval <> newVal Then

            Target = Target at «,» at newVal

        iba

            Target = newVal

        Dulo Kung

        Kung Len (newVal) = 0 Pagkatapos Target.ClearContents

        Application.EnableEvents = True

    Dulo Kung

End Sub

Nae-edit ang mga saklaw.

Paano gumawa ng dropdown list na may paghahanap?

Sa kasong ito, kailangan mo munang gumamit ng ibang uri ng listahan. Ang tab na "Developer" ay bubukas, pagkatapos nito kailangan mong i-click o i-tap (kung ang screen ay pindutin) sa "Insert" - "ActiveX" na elemento. Mayroon itong combo box. Ipo-prompt kang iguhit ang listahang ito, pagkatapos nito ay idaragdag sa dokumento.

Dagdag pa, ito ay na-configure sa pamamagitan ng mga katangian, kung saan ang isang saklaw ay tinukoy sa opsyon na ListFillRange. Ang cell kung saan ipinapakita ang value na tinukoy ng user ay na-configure gamit ang LinkedCell na opsyon. Susunod, kailangan mo lamang isulat ang mga unang character, dahil ang programa ay awtomatikong magmumungkahi ng mga posibleng halaga.

Listahan ng dropdown na may awtomatikong pagpapalit ng data

Mayroon ding isang function na ang data ay awtomatikong pinapalitan pagkatapos na idagdag ang mga ito sa hanay. Madaling gawin ito:

1. Gumawa ng set ng mga cell para sa listahan sa hinaharap. Sa aming kaso, ito ay isang hanay ng mga kulay. Pinipili namin ito.
   

   

2. Susunod, kailangan itong i-format bilang isang talahanayan. Kailangan mong i-click ang pindutan ng parehong pangalan at piliin ang estilo ng talahanayan.
   

   

   

Susunod, kailangan mong kumpirmahin ang saklaw na ito sa pamamagitan ng pagpindot sa pindutang "OK".

Pinipili namin ang resultang talahanayan at bigyan ito ng pangalan sa pamamagitan ng input field na matatagpuan sa tuktok ng column A.

Iyon lang, mayroong isang talahanayan, at maaari itong magamit bilang batayan para sa isang drop-down na listahan, kung saan kailangan mo:

1. Piliin ang cell kung saan matatagpuan ang listahan.
2. Buksan ang dialog ng Data Validation.
   

   

3. Itinakda namin ang uri ng data sa "Listahan", at bilang mga halaga ay ibinibigay namin ang pangalan ng talahanayan sa pamamagitan ng = sign.
   

   

   

Lahat, handa na ang cell, at ang mga pangalan ng mga kulay ay ipinapakita sa loob nito, gaya ng kailangan namin. Ngayon ay maaari kang magdagdag ng mga bagong posisyon sa pamamagitan lamang ng pagsusulat ng mga ito sa isang cell na matatagpuan nang mas mababa kaagad pagkatapos ng huli.

Ito ang bentahe ng talahanayan, na awtomatikong tumataas ang hanay kapag nagdagdag ng bagong data. Alinsunod dito, ito ang pinaka-maginhawang paraan upang magdagdag ng isang listahan.

Paano kopyahin ang drop down list?

Upang kopyahin, ito ay sapat na upang gamitin ang key na kumbinasyon Ctrl + C at Ctrl + V. Kaya ang drop-down na listahan ay makokopya kasama ang pag-format. Upang alisin ang pag-format, kailangan mong gumamit ng isang espesyal na i-paste (sa menu ng konteksto, lilitaw ang pagpipiliang ito pagkatapos kopyahin ang listahan), kung saan nakatakda ang opsyon na "mga kundisyon sa mga halaga".

Piliin ang lahat ng mga cell na naglalaman ng isang drop down na listahan

Upang magawa ang gawaing ito, dapat mong gamitin ang function na "Pumili ng isang pangkat ng mga cell" sa pangkat na "Hanapin at Piliin".

Pagkatapos nito, magbubukas ang isang dialog box, kung saan dapat mong piliin ang mga item na "Lahat" at "Ang mga ito ay pareho" sa menu na "Pagpapatunay ng Data". Pinipili ng unang item ang lahat ng listahan, at pipili lang ng pangalawa ang mga katulad ng ilang listahan.