Magpasok ng data gamit ang isang drop-down na listahan. Lumikha ng isang drop-down na listahan. Pagpili ng maramihang mga halaga mula sa isang listahan ng dropdown ng Excel

ISIPIN ANG SITWASYON: Nais naming lumikha ng isang maliit na talahanayan sa Excel kung saan maaari kaming pumili ng isang bansa at ang kaukulang lungsod nito. Kasabay nito, gamit ang mga drop-down na listahan, kinakailangang limitahan ang mga opsyon na magagamit sa mga user para sa mga bansa at lungsod kung saan sila mapipili. Sa unang cell ay pipili kami ng isang bansa, at sa pangalawang cell ay magagamit lamang ang mga lungsod na kabilang sa napiling bansa. Sa tingin ko ito ay malinaw?

Kaya simulan natin ang aming simpleng halimbawa kung paano ka makakagawa ng naka-link (o umaasa) na dropdown na listahan sa Excel? Sa isang selda B1 pipiliin natin ang bansa, at sa selda B2– isang lungsod na kabilang dito, tulad ng sa halimbawa:

Una kailangan mong lumikha ng isang database. Sa pangalawang sheet, naglagay ako ng listahan ng mga bansa na gusto kong piliin ng mga user sa unang drop-down na listahan, at sa katabing column ay nagpahiwatig ako ng numeric index na tumutugma sa isa sa mga listahan ng mga lungsod. Ang mga listahan ng mga lungsod ay matatagpuan sa kanan sa mga hanay D, F At H. Kaya, halimbawa, sa tabi France ay ang index 2 , na tumutugma sa isang listahan ng mga lungsod 2 . Mamaya makikita mo kung paano gagamitin ang index na ito.

Kung nagtatrabaho ka sa Excel 2010, maaari kang lumikha ng source sheet sa isang hiwalay na workbook. Kung mayroon kang 2003 na bersyon ng Excel at plano mong gumamit ng pinangalanang hanay, ang mga halaga ay dapat nasa parehong workbook, o sa ibang sheet.

Gagamit kami ng mga pinangalanang hanay at gagawing gumagana ang mga naka-link na listahan ng dropdown na ito sa lahat ng bersyon ng Excel. Ang susunod na hakbang ay lumikha ng mga pinangalanang hanay para sa aming mga listahan. Sa tab Mga pormula(Formulas) may utos Name Manager(Tagapamahala ng Pangalan). Ang pag-click dito ay magbubukas ng isang dialog box Name Manager(Tagapamahala ng Pangalan).

I-click ang button Bago(Bago) upang magdagdag ng bagong pinangalanang hanay. Magbubukas ang isang dialog box Bagong pangalan(Paglikha ng pangalan).

Sa field Pangalan(Pangalan) maglagay ng pangalan Bansa para sa aming unang pinangalanang hanay, at sa field Tumutukoy sa(Range) piliin ang isa kung saan nakaimbak ang listahan ng mga bansa:

Sheet3!$A$3:$A$5

Ang mga saklaw na naglalaman ng mga lungsod ay maaaring pangalanan sa eksaktong parehong paraan.

Ngayon ay maaari na kaming lumikha ng mga drop-down na listahan sa mga cell kung saan namin binalak na pumili ng data. Pumili ng cell B1(sa loob nito ay pipiliin namin ang bansa), buksan ang tab Data(Data), i-click Pagpapatunay ng Data(Pagpapatunay ng Data) at pagkatapos ay pumili mula sa drop-down na menu Pagpapatunay ng Data(Pagsusuri ng data).

Magbubukas ang isang dialog box Pagpapatunay ng Data(Tinitingnan ang mga inilagay na halaga).

Nais naming bigyan ang user ng listahan ng mga pagpipiliang mapagpipilian, kaya sa field Payagan(Uri ng data) piliin Listahan(Listahan). Isaaktibo nito ang field Pinagmulan(Source), kung saan kailangan mong tukuyin ang pangalan ng hanay sa mga bansa. Ilagay ang “=Country” sa field na ito at i-click OK. Ngayon ay kailangan nating gumawa ng pangalawang dropdown upang ang mga user ay makapili ng lungsod. Ilalagay namin ang drop down na listahan na ito sa isang cell B2. At ngayon pansin - focus! Kailangan nating suriin ang mga nilalaman ng cell ng pangalan ng bansa (cell B1) upang makuha ang index na naaayon sa database ng lungsod. Kung pipiliin ng user Portugal, pagkatapos ay dapat nating i-access ang base gamit ang index 3 , na nag-iimbak ng mga pangalan ng mga lungsod sa Portugal. Gagamitin namin ang function VLOOKUP(VLOOKUP) upang makahanap ng isang halaga mula sa isang cell B1 sa isang talahanayan na may mga pangalan ng mga bansa. Kapag nalaman na ang index, pipili kami ng listahan na magiging data source para sa aming pangalawang dropdown list. Upang gawin ito, isinusulat namin ang sumusunod na formula:

PUMILI(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)
=SELECT(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)

Ano ang ginagawa ng formula na ito? Naghahanap ito ng halaga mula sa isang cell B1 sa listahan ng mga bansa at ibinabalik ang kaukulang index, na ginagamit ng function PUMILI(PUMILI) upang piliin ang 1st, 2nd, o 3rd name range.

Ito ang magiging hitsura ng aming pangalawang dropdown list:

Ang mga drop-down na listahan sa Microsoft Excel ay kailangang-kailangan kapag gumagawa ng malalaking talahanayan at nagtatrabaho sa mga database. Ano nga ba ang kaginhawahan ng tool na ito?

Bakit kailangan ang mga ganitong listahan?

Kung, kapag pinupunan ang isang talahanayan, ang ilang data ay paulit-ulit na pana-panahon, hindi na kailangang manu-manong magpasok ng pare-parehong halaga sa bawat oras - halimbawa, pangalan ng produkto, buwan, buong pangalan ng empleyado. Ito ay sapat na upang i-pin ang isang umuulit na parameter sa listahan nang isang beses. Kadalasan, ang ilang mga list cell ay protektado mula sa pagpasok ng mga extraneous na halaga, na binabawasan ang posibilidad na magkamali sa pagpapatakbo. Ang isang mesa na dinisenyo sa ganitong paraan ay mukhang maayos.

Sa artikulong sasabihin ko sa iyo paano gumawa ng drop-down list sa isang cell sa Excel e at kung paano ito gagawin.

Pagbuo ng isang drop-down na listahan

Isa sa mga karaniwang halimbawa ng paggamit ng mga drop-down na listahan ay ang mga online na tindahan, kung saan ang lahat ng produkto ay nahahati sa mga kategorya. Pinapadali ng istrukturang ito para sa mga user na maghanap sa site. Tingnan natin nang mas malapitan:

Ang lahat ng mga produktong nakalista sa talahanayan ay dapat na maiuri sa ilalim ng kategoryang "Damit". Upang lumikha ng isang drop-down na listahan para sa listahang ito, kakailanganin mong gawin ang sumusunod:

Pumili ng anumang cell kung saan gagawin ang listahan.
Pumunta sa tab na "Data", sa seksyong "Data Check".
Sa window na bubukas, piliin ang tab na "Mga Parameter", at sa listahan ng "Uri ng data", piliin ang opsyon na "Listahan".
Sa lalabas na linya, dapat mong isaad ang lahat ng available na pangalan ng listahan. Magagawa ito sa dalawang paraan: pumili ng hanay ng data sa talahanayan gamit ang mouse (sa halimbawa, mga cell A1-A7) o manu-manong ilagay ang mga pangalan, na pinaghihiwalay ng semicolon.
Piliin ang lahat ng mga cell na may nais na mga halaga, at i-right-click at piliin ang "Magtalaga ng pangalan" mula sa menu ng konteksto.
Sa linya ng "Pangalan", ipahiwatig ang pangalan ng listahan - sa kasong ito, "Damit".
Piliin ang cell kung saan ginawa ang listahan at ipasok ang nilikhang pangalan sa linyang “Source” na may “=” sign sa simula.

Paano magdagdag ng mga halaga sa isang listahan

Minsan ito ay nagiging kinakailangan upang madagdagan ang isang umiiral na listahan. Sa drop-down na listahan, ang lahat ng mga bagong item ay awtomatikong ipinapakita kapag idinagdag. Gayunpaman, upang maiugnay ang isang hanay ng mga cell sa isang bagong idinagdag na elemento, ang listahan ay dapat na naka-format bilang isang talahanayan. Upang gawin ito, kailangan mong pumili ng isang hanay ng mga halaga, hanapin ang item na "Format bilang talahanayan" sa tab na "Home" at piliin ang anumang estilo na gusto mo, halimbawa:

Kung pinupunan mo ang isang talahanayan sa Excel, at kung minsan ang data sa isang haligi ay maaaring ulitin, halimbawa, ang pangalan ng isang produkto, o ang pangalan ng isang empleyado, kung gayon upang hindi maipasok ang nais na parameter sa bawat oras, ito ay mas simple at mas madaling lumikha ng isang drop-down na listahan nang isang beses at pumili ng isang halaga mula dito.

Sa artikulong ito titingnan natin kung paano gumawa ng mga drop-down na listahan ng iba't ibang uri sa isang Excel table.

Lumikha ng isang simpleng listahan ng dropdown

Upang gawin ito, sa mga cell A1:A7 ipinasok namin ang data na ipapakita sa listahan. Ngayon piliin natin ang cell kung saan gagawa tayo ng drop-down list - B2.

Pumunta sa tab na "Data" at mag-click sa button na "Data Check".

Sa tab na "Mga Parameter," sa field na "Uri ng data," piliin ang "Listahan". Maaari kang magpasok ng mga halaga sa patlang ng Pinagmulan sa iba't ibang paraan:

1 - manu-manong ipasok ang mga halaga para sa listahan, na pinaghihiwalay ng mga semicolon;

2 - ipahiwatig ang hanay ng mga cell kung saan ipinasok ang data para sa drop-down na listahan;

3 - piliin ang mga cell na may mga pangalan, i-right-click ang mga ito at piliin ang "Magtalaga ng isang pangalan" mula sa menu.

Piliin ang cell B2 at ilagay ang “=” sa field na “Source”, pagkatapos ay isulat ang nilikhang pangalan.

Kaya gumawa kami ng simpleng dropdown list sa Excel.

Kung mayroon kang heading para sa isang column, at kailangan mong punan ang bawat row ng mga value, pagkatapos ay pumili ng hindi isang cell, ngunit isang hanay ng mga cell - B2:B9. Pagkatapos ay maaari mong piliin ang nais na halaga sa bawat cell mula sa drop-down na listahan.

Pagdaragdag ng mga halaga sa isang drop-down na listahan - dynamic na listahan

Sa kasong ito, magdaragdag kami ng mga halaga sa kinakailangang hanay, at awtomatiko silang idaragdag sa drop-down na listahan.

Piliin ang hanay ng mga cell – D1:D8, pagkatapos ay sa tab na “Home”, i-click ang “Format as table” at pumili ng anumang istilo.

Kumpirmahin ang lokasyon ng data at lagyan ng check ang kahon na "Talahanayan na may mga header."

Sa itaas isusulat namin ang pamagat ng talahanayan - "Mga Empleyado", at punan ito ng data.

Piliin ang cell kung saan makikita ang drop-down na listahan at mag-click sa pindutan ng "Data Check". Sa susunod na window, sa field na "Source", isulat ang sumusunod: =INDIRECT("Table1"). Mayroon akong isang talahanayan sa isang sheet, kaya isinusulat ko ang "Table1", kung mayroong pangalawa - "Table2", at iba pa.

Ngayon magdagdag tayo ng bagong pangalan ng empleyado sa aming listahan: Ira. Lumitaw ito sa drop-down list. Kung tatanggalin namin ang anumang pangalan mula sa talahanayan, tatanggalin din ito sa listahan.

Listahan ng dropdown na may mga halaga mula sa isa pang sheet

Kung ang talahanayan na may mga drop-down na listahan ay nasa isang sheet, at ang data para sa mga listahang ito ay nasa isa pa, kung gayon ang function na ito ay makakatulong sa amin ng malaki.

Sa Sheet 2, pumili ng isang cell o hanay ng mga cell, pagkatapos ay i-click ang "Data Validation" na button.

Pumunta sa Sheet 1, ilagay ang cursor sa field na "Source" at piliin ang gustong hanay ng mga cell.

Ngayon ay maaari kang magdagdag ng mga pangalan sa Sheet 1, idaragdag ang mga ito sa mga drop-down na listahan sa Sheet 2.

Paglikha ng mga nakadependeng drop-down na listahan

Ipagpalagay natin na mayroon tayong tatlong hanay: mga unang pangalan, apelyido, at patronymics ng mga empleyado. Para sa bawat isa, kailangan mong magtalaga ng isang pangalan. Pinipili namin ang mga cell ng hanay na ito, maaari mo ring walang laman ang mga - sa paglipas ng panahon maaari kang magdagdag ng data sa mga ito, na lilitaw sa drop-down na listahan. Mag-right-click sa mga ito at piliin ang "Magtalaga ng pangalan" mula sa listahan.

Tinatawag namin ang unang "Pangalan", ang pangalawa - "Apelyido", ang pangatlo - "Ama".

Gumawa tayo ng isa pang hanay kung saan isusulat ang mga nakatalagang pangalan. Tawagin natin itong "Mga Empleyado".

Ginagawa namin ang unang drop-down na listahan, na binubuo ng mga pangalan ng mga hanay. Piliin ang cell E1 at sa tab na “Data” piliin ang “Data Validation”.

Sa field na "Uri ng Data," piliin ang "Listahan"; sa field ng Pinagmulan, ipasok ang "=Mga Empleyado" o pumili ng hanay ng mga cell na nabigyan ng pangalan.

Nagawa na ang unang dropdown list. Ngayon sa cell F2 gagawa kami ng pangalawang listahan, na dapat depende sa una. Kung pipiliin natin ang "Pangalan" sa una, isang listahan ng mga pangalan ang ipapakita sa pangalawa; kung pipiliin natin ang "Apelyido", isang listahan ng mga apelyido ang ipapakita.

Piliin ang cell at mag-click sa pindutan ng "Data Check". Sa field na “Uri ng data,” piliin ang “Listahan”; sa field ng pinagmulan, ilagay ang sumusunod: =INDIRECT($E$1). Narito ang E1 ay ang cell na may unang drop-down na listahan.

Gamit ang prinsipyong ito, maaari kang gumawa ng mga nakadependeng drop-down na listahan.

Kung sa hinaharap, kakailanganin mong ilagay ang mga halaga sa isang hanay na binibigyan ng pangalan, halimbawa, "Apelyido". Pumunta sa tab na Mga Formula at i-click ang Name Manager. Piliin ngayon ang "Apelyido" sa pangalan ng hanay, at sa ibaba, sa halip na ang huling cell C3, isulat ang C10. I-click ang check mark. Pagkatapos nito, tataas ang hanay, at maaari kang magdagdag ng data dito, na awtomatikong lilitaw sa drop-down na listahan.

Ngayon alam mo na kung paano gumawa ng drop-down list sa Excel.

Paano lumikha ng isang drop-down na listahan na binubuo ng ilang mga cell nang sabay-sabay (halimbawa, upang ang pangalan ay may gastos)

Salamat, lahat ay gumana nang maayos.

Ang isang drop-down na listahan na may mga halaga mula sa isa pang sheet ay hindi gumagana, dahil ang window kapag ang pag-verify ng data ay bukas ay hindi pinapayagan ang pagtatrabaho sa ibang mga window, lalo na sa isa pang sheet!

Nagbibigay-daan sa iyo ang dependent na dropdown list na gumawa ng trick na madalas na pinupuri ng mga user ng Excel template. Isang trick na ginagawang mas madali at mas mabilis ang trabaho. Isang trick na gagawing komportable at kasiya-siya ang iyong mga kurba.

Halimbawa ng paggawa ng dependent na drop-down list sa isang Excel cell

Isang halimbawa ng paggamit ng isang nakadependeng drop-down na listahan upang lumikha ng isang maginhawang form para sa pagpuno ng mga dokumento kung saan nag-order ang mga nagbebenta ng mga kalakal. Mula sa buong assortment, kailangan nilang pumili ng mga produkto na kanilang ibebenta.

Ang bawat nagbebenta ay unang natukoy ang isang pangkat ng produkto, at pagkatapos ay isang partikular na produkto mula sa pangkat na ito. Dapat kasama sa form ang buong pangalan ng pangkat at ang partikular na index ng produkto. Dahil ang pag-type nito sa pamamagitan ng kamay ay magiging masyadong matagal (at nakakainis), nakaisip ako ng napakabilis at simpleng solusyon - 2 dependent dropdown.

Ang una ay isang listahan ng lahat ng mga kategorya ng produkto, ang pangalawa ay isang listahan ng lahat ng mga produkto sa napiling kategorya. Kaya gumawa ako ng dropdown list na nakadepende sa piniling ginawa sa nakaraang listahan (dito makikita mo ang materyal kung paano gumawa ng dalawang dependent dropdown list).

Gusto ng user ng template ng badyet sa bahay na makuha ang parehong resulta kung saan kailangan nila ng kategorya at subcategory ng mga gastos. Ang isang halimbawa ng data ay nasa figure sa ibaba:

Kaya, halimbawa, kung pipiliin namin ang kategorya ng Entertainment, dapat kasama sa listahan ng mga subcategory ang: Sinehan, Teatro, Pool. Isang napakabilis na solusyon kung gusto mong suriin ang mas detalyadong impormasyon sa iyong badyet sa bahay.

Listahan ng mga kategorya at subcategory sa listahan ng dropdown na umaasa sa Excel

Inaamin ko na sa bersyon ng aking iminungkahing badyet sa bahay, nililimitahan ko ang aking sarili sa isang kategorya lamang, dahil para sa akin ang gayong paghahati ng mga gastos ay sapat na (ang pangalan ng mga gastos / kita ay itinuturing bilang isang subcategory). Gayunpaman, kung kailangan mong paghiwalayin ang mga ito sa mga subcategory, kung gayon ang paraan na inilalarawan ko sa ibaba ay perpekto. Huwag mag-atubiling gamitin ito!

At ang resulta ay ganito:

Dependent na dropdown na listahan ng mga subcategory

Upang makamit ito, kailangan naming gumawa ng isang bahagyang naiibang talahanayan ng data kaysa sa kung kami ay gumagawa ng isang drop-down na listahan. Dapat ganito ang hitsura ng talahanayan (range G2:H15):

Working Excel Source Spreadsheet

Sa talahanayang ito dapat kang magpasok ng isang kategorya at ang mga subcategory nito sa tabi nito. Ang pangalan ng kategorya ay dapat na ulitin nang maraming beses hangga't mayroong mga subcategory. Napakahalaga na ang data ay pinagsunod-sunod ayon sa hanay ng Kategorya. Ito ay magiging lubhang mahalaga kapag isinulat namin ang formula sa ibang pagkakataon.

Maaari mo ring gamitin ang mga talahanayan mula sa unang larawan. Siyempre, iba ang mga formula. Minsan, kahit na natagpuan ko ang gayong solusyon sa Internet, ngunit hindi ko ito nagustuhan dahil mayroon itong isang nakapirming haba ng listahan: na nangangahulugang kung minsan ang listahan ay naglalaman ng mga walang laman na patlang, at kung minsan ay hindi ito nagpapakita ng lahat ng mga elemento. Siyempre, maiiwasan ko ang limitasyong ito, ngunit inaamin ko na mas gusto ko ang aking solusyon, kaya hindi na ako bumalik sa solusyon na iyon.

Sige. Ngayon, ilalarawan ko nang paisa-isa ang mga hakbang ng paglikha ng isang nakadependeng listahan ng dropdown.

1. Mga Pangalan ng Hanay ng Cell

Ito ay isang opsyonal na hakbang, kung wala ito maaari naming pangasiwaan ito nang walang anumang mga problema. Gayunpaman, gusto kong gumamit ng mga pangalan dahil ginagawa nilang mas madali ang formula sa parehong pagsulat at pagbabasa.

Magtalaga tayo ng mga pangalan sa dalawang hanay. Listahan ng lahat ng mga kategorya at listahan ng trabaho ng mga kategorya. Ang mga saklaw na ito ay magiging A3:A5 (listahan ng mga kategorya sa berdeng worksheet sa unang larawan) at G3:G15 (listahan ng mga umuulit na kategorya sa purple worksheet).

Upang pangalanan ang isang listahan ng mga kategorya:

Piliin ang hanay A3:A5.
Sa kahon ng Pangalan (ang kahon sa kaliwa ng formula bar), ilagay ang pangalang "Kategorya."
Kumpirmahin gamit ang Enter key.

Magsagawa ng parehong pagkilos para sa hanay ng worklist ng kategorya G3:G15, na matatawag mong "Work_List". Gagamitin namin ang hanay na ito sa formula.

2. Gumawa ng drop-down na listahan para sa isang kategorya

Ito ay magiging simple:

Piliin ang cell kung saan mo gustong ilagay ang listahan. Sa aking kaso ito ay A12.
Mula sa DATA menu, piliin ang Data Validation tool. Ang window na "Suriin ang Mga Halaga ng Input" ay lilitaw.
Piliin ang Listahan bilang uri ng data.
Bilang pinagmulan, ilagay ang: =Category (larawan sa ibaba).
Kumpirmahin gamit ang OK.

Ang resulta ay ang mga sumusunod:

Listahan ng dropdown para sa kategorya.

3. Gumawa ng dependent na dropdown list para sa isang subcategory

Ngayon ay magiging masaya. Alam namin kung paano gumawa ng mga listahan - ginawa lang namin ito para sa isang kategorya. Isang tanong lang: "Paano ko sasabihin sa Excel na piliin lamang ang mga halagang iyon na nilayon para sa isang partikular na kategorya?" Tulad ng maaari mong hulaan, gagamit ako ng isang worksheet dito at, siyempre, mga formula.

Magsimula tayo sa kung ano ang alam na natin, iyon ay, sa pamamagitan ng paggawa ng drop-down list sa cell B12. Kaya piliin ang cell na iyon at i-click ang Data/Data Validation at ang Uri ng Data ay Listahan.

Sa source ng listahan, ilagay ang sumusunod na formula:

View ng window na "Pagsusuri ng mga halaga ng input":

Pagpapatunay ng mga halaga ng input para sa isang subcategory sa isang nakadependeng listahan ng dropdown

Tulad ng nakikita mo, ang buong trick sa isang listahan ng umaasa ay ang paggamit ng OFFSET function. Okay, halos lahat. Ang MATCH at COUNTIF function ay nakakatulong sa kanya. Binibigyang-daan ka ng OFFSET function na dynamic na tukuyin ang mga saklaw. Una, tinutukoy namin ang cell kung saan dapat magsimula ang paglipat ng hanay, at sa mga kasunod na argumento ay tinukoy namin ang laki nito.

Sa aming halimbawa, lilipat ang hanay sa column na Subcategory sa worksheet (G2:H15). Magsisimula kaming lumipat mula sa cell H2, na siya ring unang argumento ng aming function. Sa formula, ang cell H2 ay nakasulat bilang isang ganap na sanggunian dahil ipinapalagay ko na gagamitin namin ang drop-down na listahan sa maraming mga cell.

Dahil pinagbukud-bukod ang worksheet ayon sa Kategorya, magsisimula ang hanay na dapat pagmulan para sa drop-down na listahan kung saan unang lilitaw ang napiling kategorya. Halimbawa, para sa kategorya ng Pagkain gusto naming ipakita ang hanay na H6:H11, para sa Transport - ang hanay na H12:H15, atbp. Pansinin na kami ay gumagalaw sa kahabaan ng H column sa lahat ng oras, at ang tanging bagay na nagbabago ay ang simula ng hanay at taas nito ( iyon ay, ang bilang ng mga elemento sa listahan).

Ang simula ng hanay ay ililipat kaugnay sa cell H2 ng kasing dami ng mga cell pababa (sa numero) bilang numero ng posisyon ng unang nagaganap na kategorya sa column na Kategorya. Magiging mas madaling maunawaan gamit ang isang halimbawa: ang hanay para sa kategorya ng Pagkain ay inilipat nang 4 na mga cell pababa kaugnay ng cell H2 (nagsisimula sa 4 na mga cell mula sa H2). Sa ika-4 na cell ng column ng Subcategory (hindi kasama ang heading, dahil pinag-uusapan natin ang tungkol sa isang hanay na tinatawag na Work_List), mayroong salitang Nutrition (unang hitsura nito). Ginagamit namin ang katotohanang ito upang aktwal na matukoy ang simula ng hanay. Ang MATCH function (ipinakilala bilang pangalawang argumento ng OFFSET function) ay magsisilbi sa amin para sa layuning ito:

Ang taas ng hanay ay tinutukoy ng COUNTIF function. Binibilang niya ang lahat ng paglitaw ng mga pag-uulit sa kategorya, iyon ay, ang salitang Nutrisyon. Ang dami ng beses na naganap ang salitang ito, ang bilang ng mga posisyon doon sa aming hanay. Ang bilang ng mga posisyon sa isang hanay ay ang taas nito. Narito ang function:

Siyempre, ang parehong mga function ay kasama na sa OFFSET function na inilarawan sa itaas. Gayundin, pansinin na sa parehong MATCH at COUNTIF function, mayroong isang reference sa isang hanay na tinatawag na WorkList. Gaya ng nabanggit ko kanina, hindi mo kailangang gumamit ng mga pangalan ng saklaw, maaari mo lamang ipasok ang $H3:$H15. Gayunpaman, ang paggamit ng mga pangalan ng saklaw sa formula ay ginagawang mas simple at mas madaling basahin.

Iyon lang:

Mag-download ng halimbawa ng nakadependeng drop-down list sa Excel

Isang formula, well, hindi gaanong simple, ngunit ginagawa nitong mas madali ang trabaho at pinoprotektahan laban sa mga error kapag naglalagay ng data!

Upang hindi mai-type sa mga titik at numero ang naunang nai-type na teksto at mga numerong halaga ng mga cell, upang mapabilis ang proseso ng pagpuno sa mga cell ng isang MS Excel sheet ng impormasyon at upang mabawasan ang mga error, kabilang ang mga typo at mga error sa spelling, ito ay maginhawang gumamit ng isang drop-down na listahan.

Mula sa drop-down na listahan, na may ilang mga pag-click ng mouse, maaari mong ipasok ang kinakailangang impormasyon sa mga itinalagang cell. Ang mga drop-down na listahan ay malawakang ginagamit kapag nagsusulat ng mga programa sa pagkalkula sa Excel.

Ang MS Excel program, na may napaka-user-friendly na interface, ay nag-aalok sa user ng ilang iba't ibang mga opsyon para sa tulong kapag naglalagay ng paulit-ulit na impormasyon sa mga cell ng worksheet.

Ipagpalagay natin na nagpapanatili tayo ng database ng mga rolled metal na resibo sa isang bodega. Sa unang hanay ipinapahiwatig namin ang uri ng pinagsamang profile.

Opsyon #0 - "Elementarya".

Kapag gumagawa ng susunod na entry sa cell A9, kapag nagta-type ng unang titik ng pangalan ng profile, halimbawa "Ш", iminumungkahi ng Excel na punan ang cell ng salitang "Channel". Matapos i-type ang "Ш", pindutin lamang ang pindutan ng "Enter" sa keyboard - at ang salita ay ipasok sa cell.

Ang "kapinsalaan" ng pagpipiliang ito ay ang pangangailangan na kung minsan ay magpasok ng ilang mga titik at ang kawalan ng kakayahang lumikha ng isang direktoryo ng mga pangalan nang maaga, na naglilimita sa kalayaan ng aktibidad ng gumagamit.

Direktang lumipat tayo sa mga opsyon para sa paggawa ng mga drop-down na listahan.

Opsyon #1 - "Ang pinakasimpleng".

Kung i-activate mo ang cell A9 gamit ang mouse at pinindot ang key combination na “Alt” “↓”, lalabas ang isang drop-down list na naglalaman ng lahat ng value na nailagay dati sa column na ito. Ang natitira na lang ay piliin ang nais na entry gamit ang mouse. Sa halip na i-type ang keyboard shortcut sa itaas, maaari kang mag-right-click upang buksan ang menu ng konteksto at piliin ang "Pumili mula sa drop-down na listahan..." mula dito. Bilang resulta, makikita natin ang parehong drop-down na listahan.

Sa bersyong ito, ang aktibong cell ay dapat na katabi sa ibaba ng hanay ng mga halaga, at ang hanay mismo ay hindi dapat maglaman ng mga walang laman na cell!

Pagpipilian #2 - "Simple".

Ang pagpipiliang ito ay nagpapahintulot sa iyo na lumikha nang maaga ng isang listahan (direktoryo) ng mga halaga kung saan maaaring piliin ng gumagamit sa ibang pagkakataon ang mga kinakailangang tala. Sa kasong ito, maaaring ilagay ang listahan saanman sa sheet (o kahit sa isa pang sheet) at maaaring itago mula sa user kung kinakailangan.

Upang lumikha ng isang drop-down na listahan sa opsyong ito, kailangan mong sundin ang isang serye ng mga sunud-sunod na hakbang.

1. Lumilikha kami ng isang listahan ng mga posibleng halaga sa pamamagitan ng pagsulat ng mga ito sa isang hanay, isa sa bawat cell. Sabihin nating ito ay isang listahan sa mga cell A2...A8.

2. Ina-activate namin ang cell kung saan gusto naming ilagay ang drop-down na listahan sa pamamagitan ng paglalagay ng cursor dito. Hayaan itong maging parehong cell A9.

3. Sa pangunahing menu, piliin ang “Data” – “Suriin...” na buton.

4. Sa window na "Pagsusuri ng mga inilagay na halaga" na lalabas, piliin ang tab na "Mga Parameter."

5. Sa field na “Uri ng data:,” mula sa drop-down na listahan (katulad ng ginagawa namin), piliin ang value na “Listahan”.

6. Sa lalabas na field na "Pinagmulan:", tukuyin ang isang hanay na naglalaman ng listahan ng mga posibleng value.

7. Lagyan ng check (kung hindi ito naka-check bilang default) ang checkbox na "Listahan ng mga wastong halaga" at i-click ang pindutang "OK".

Ang listahan ng dropdown ay handa na. Maaari itong kopyahin bilang mga formula sa anumang bilang ng mga cell!

Pagpipilian #3 - "Kumplikado".

Ang pagpipiliang ito para sa paglikha ng isang drop-down na listahan, sa kabila ng pangalan nito na "Complex," ay mahalagang hindi iyon. Upang lumikha ng isang drop-down na listahan, ginagamit nito ang item ng Combo Box sa toolbar ng Forms.

Gumawa tayo ng drop-down list gamit ang paraang ito.

1. Lumilikha kami ng isang listahan ng sanggunian sa mga cell A2...A8.

2. Sa pangunahing menu, piliin ang pindutang "Tingnan" - "Mga Toolbar" - "Mga Form".

3. Sa panel na "Mga Form" na lalabas, piliin ang "Combo Box" at iguhit ito, halimbawa, sa cell A9.

Ang elementong "Combo Box" ay hindi inilalagay sa cell mismo, ngunit, parang, sa itaas nito!!! Ang elemento ay maaaring malaki at higit sa ilang mga cell.

4. Mag-right-click sa iginuhit na elemento at piliin ang "Format Object" sa menu ng konteksto na lilitaw.

5. Sa window na "Pag-format ng Bagay" na lilitaw, sa tab na "Kontrol", punan ang mga patlang alinsunod sa figure sa ibaba at i-click ang "OK".

6. Ang listahan ng dropdown ay handa na. Inilalabas nito ang sequence number ng listahan ng item sa nauugnay na cell B9. (Maaari kang magtalaga ng anumang cell na maginhawa para sa iyo, hindi kinakailangan B9!)

Upang ipakita ang mismong halaga mula sa listahan ng sanggunian sa anumang cell, gamitin ang function na INDEX. Sabihin nating kailangan nating magpakita ng value sa cell A9, na matatagpuan sa ilalim ng elementong “Combo Box”.

Upang gawin ito, isulat ang formula sa cell A9: =INDEX(A2:A8,B9)

Ang isang malinaw na halimbawa ay tinalakay nang detalyado sa artikulong "". Maaari mong sundan ang link at tingnan ito.

Ang drop-down list na ginawa sa ganitong paraan kasama ang paggamit ng INDEX at/o VLOOKUP function ay nagbibigay ng walang limitasyong mga posibilidad para sa user kapag kumukuha ng data mula sa iba't ibang pinagbabatayan na lookup table.

Opsyon numero 4 - "Ang pinakamahirap".

Upang lumikha ng isang drop-down na listahan sa kasong ito, ang "Combo Box" na elemento ay ginagamit din, ngunit ang "Controls" toolbar (sa MS Excel 2003). Ang mga ito ay tinatawag na ActiveX controls. Narito ang lahat ay halos kapareho sa hitsura sa opsyon No. 3, ngunit ang mga posibilidad para sa pagpapasadya at pag-format ng elemento ay mas malawak.

1. Sa pangunahing menu, piliin ang pindutang "Tingnan" - "Mga Toolbar" - "Mga Kontrol".

2. Sa panel na "Mga Kontrol" na lalabas, piliin ang "Combo Box" at iguhit ito sa cell A9. ElementoActiveXAng "combo box" ay hindi inilagay sa cell mismo, ngunit sa itaas, na sumasakop dito!!!

3. I-click ang button na "Properties" sa panel na "Controls" at sa window na "Properties" na lalabas, manu-manong ipasok ang hanay ng source data, ang address ng nauugnay na cell (ang cell kung saan ilalagay ang napiling value) at ang bilang ng mga ipinapakitang row.

4. Pagkatapos, kung gusto mo, maaari mong baguhin ang font, kulay nito, kulay ng background, at ilang iba pang mga parameter... Walang kumplikado sa paggamit ng opsyong "Pinaka-Mahirap" - tingnan para sa iyong sarili. Ang lahat ay madaling maunawaan, kahit na ang pangunahing kaalaman sa Ingles ay hindi makakasakit!

5. Pindutin ang pindutan ng "Lumabas sa Mode ng Disenyo" sa panel ng "Mga Kontrol" at suriin ang pagpapatakbo ng drop-down na listahan. Lahat ay gumagana! Ang napiling halaga ay nakasulat sa cell A9, sa aming halimbawa - sa ilalim ng elementong "Combo Box". Sa pangkalahatan, ang isang naka-link na cell ay maaaring maging anumang cell maliban sa mga cell kung saan matatagpuan ang base list.

Mga resulta.

Ang Opsyon No. 0 ay awtomatiko ang pagpuno ng mga cell sa ilang lawak, ngunit, siyempre, ay walang kinalaman sa mga drop-down na listahan at ibinibigay dito sa ilalim ng kaukulang numero bilang isang elementarya na opsyon para sa pag-automate ng pagpasok ng paulit-ulit na data.

Sa pagsasagawa, madalas akong lumikha ng mga drop-down na listahan sa Excel gamit ang mga opsyon No. 1 at No. 3, mas madalas - opsyon No. 2, at napakabihirang - opsyon No. 4, bagaman ito ay, siyempre, ang pinaka-flexible at nagbibigay ng pinakamalawak na posibilidad.

Ngunit kadalasan ang ating mga pagpipilian sa buhay ay tinutukoy ng mga panlasa, stereotype at gawi! Depende sa gawain na kailangang lutasin kapag nagtatrabaho sa Excel, dapat mong piliin ang pinakaangkop at maginhawang opsyon para sa paglikha ng mga drop-down na listahan para sa bawat partikular na kaso.

Mag-subscribe sa mga anunsyo ng artikulo sa window na matatagpuan sa dulo ng bawat artikulo o sa window sa tuktok ng pahina at huwag kalimutang kumpirmahin mag-subscribe sa pamamagitan ng pag-click sa link sa sulat na ipapadala sa iyo sa tinukoy na email address (maaaring dumating ito sa folder ng Spam - ang lahat ay depende sa iyong mga setting ng email)!!!

Kapaki-pakinabang ba ang artikulong ito o hindi para sa iyo, mahal na mga mambabasa? Isulat ang tungkol dito sa mga komento.

Ang drop-down na listahan sa Excel ay isang medyo maginhawang function na makakatulong sa iyong lumikha ng mas kumplikadong mga dokumento at gawing mas madali ang trabaho para makita ng user.

Ang ilan sa mga pinakakaraniwang uri ng drop-down na listahan na maaari mong gawin sa Excel ay:

Sa multi-select function;
Sa pagpuno;
Sa pagdaragdag ng mga bagong elemento;
May mga drop-down na larawan;
Iba pang mga uri.

Gumawa ng listahan sa Excel na may multi-select

Maaari kang lumikha ng isang listahan sa isang cell ng programa gamit ang mga built-in na tool ng control panel.

Tingnan natin ang lahat ng mga pangunahing at pinakakaraniwang uri, at ang proseso ng paglikha ng mga ito sa pagsasanay.

Payo! Ang klasikong pop-up na listahan ng programa ay may multi-select na function, iyon ay, ang user, sa pamamagitan ng pag-click sa kaukulang grupo, ay maaaring pumili ng naaangkop na opsyon para dito.

Kinakailangan ang maraming pagpili kapag kailangan mong itakda ang halaga ng ilang mga cell ng dokumento gamit ang isang listahan.

Upang lumikha ng isa, sundin ang mga tagubilin:

Pumili ng mga cell. Kung titingnan mo ang figure, kailangan mong pumili simula sa C2 at nagtatapos sa C5;
Hanapin ang tab na "Data", na matatagpuan sa pangunahing toolbar sa window ng programa. Pagkatapos ay mag-click sa data verification key tulad ng ipinapakita sa figure sa ibaba;

Sa window na bubukas, buksan ang pinakaunang tab at piliin ang elemento, tulad ng ipinapakita sa figure. Kaya, isang listahan ang gagawin sa mga lugar na ito. Sa field ng text, tukuyin ang hanay ng mga cell na pupunan sa tuwing pipili ka ng mga item.

atbpNumero ng pagpuno:

Sa ganitong paraan gagawa ka ng isang klasikong pahalang na listahan na may multi-select na functionality.

Gayunpaman, upang awtomatiko itong mapunan, kailangan mong lumikha ng isang macro na may sumusunod na source code, na ipinapakita sa figure.

Lumikha ng isang listahan sa Excel na may nilalaman

Ang isang karaniwang listahan na may nilalaman ay magbibigay-daan sa iyo na i-automate ang proseso ng pagpasok ng impormasyon. Kapag nag-click ka dito, lilitaw ang isang seleksyon ng mga posibleng halaga nito.

Maaari lamang piliin ng user ang kinakailangang halaga upang punan.