Jinsi ya kutengeneza orodha ya kushuka katika Excel

Orodha kunjuzi ni zana muhimu sana ambayo inaweza kusaidia kufanya kazi na habari kuwa nzuri zaidi. Inafanya uwezekano wa kuwa na maadili kadhaa katika seli mara moja, ambayo unaweza kufanya kazi nayo, kama na wengine wowote. Ili kuchagua ile unayohitaji, bonyeza tu kwenye ikoni ya mshale, na kisha orodha ya maadili ya uXNUMXbuXNUMXbis itaonyeshwa. Baada ya kuchagua moja maalum, kiini hujazwa kiotomatiki nayo, na fomula huhesabiwa tena kwa msingi wake.

Excel hutoa njia nyingi tofauti za kutengeneza menyu kunjuzi, na kwa kuongezea, hukuruhusu kuzibadilisha kwa urahisi. Hebu tuchambue njia hizi kwa undani zaidi.

Mchakato wa kuunda orodha

Ili kutengeneza menyu ibukizi, bofya kwenye vipengee vya menyu kwenye njia ya "Data" - "Uthibitishaji wa Data". Sanduku la mazungumzo litafungua ambapo unahitaji kupata kichupo cha "Parameters" na ubofye juu yake ikiwa haijafunguliwa hapo awali. Ina mipangilio mingi, lakini kipengee cha "Aina ya Data" ni muhimu kwetu. Kati ya maana zote, "Orodha" ndiyo sahihi.

Idadi ya njia ambazo habari huingizwa kwenye orodha ya pop-up ni kubwa sana.

1. Ashirio huru la vipengele vya orodha vilivyotenganishwa na nusu koloni katika sehemu ya "Chanzo" iliyoko kwenye kichupo sawa cha kisanduku cha mazungumzo.
   

   

2. Dalili ya awali ya maadili. Sehemu ya Chanzo ina masafa ambapo taarifa inayohitajika inapatikana.
   

   

3. Inabainisha safu iliyotajwa. Njia ambayo inarudia ya awali, lakini ni muhimu tu kutaja masafa.
   

   

Yoyote ya njia hizi itatoa matokeo yaliyohitajika. Hebu tuangalie mbinu za kutengeneza orodha kunjuzi katika hali halisi ya maisha.

Kulingana na data kutoka kwenye orodha

Wacha tuseme tunayo meza inayoelezea aina tofauti za matunda.

Ili kuunda orodha katika menyu kunjuzi kulingana na seti hii ya habari, unahitaji kufanya yafuatayo:

1. Chagua kisanduku kilichohifadhiwa kwa orodha ya baadaye.
2. Pata kichupo cha Data kwenye utepe. Huko tunabofya "Thibitisha data".
   

   

3. Pata kipengee "Aina ya Data" na ubadili thamani kwa "Orodha".
   

   

4. Katika uwanja unaoashiria chaguo la "Chanzo", ingiza safu unayotaka. Tafadhali kumbuka kuwa marejeleo kamili lazima yabainishwe ili wakati wa kunakili orodha, habari haibadiliki.
   

Kwa kuongeza, kuna chaguo la kukokotoa kutengeneza orodha mara moja katika seli zaidi ya moja. Ili kufikia hili, unapaswa kuwachagua wote, na ufanyie hatua sawa na ilivyoelezwa hapo awali. Tena, unahitaji kuhakikisha kuwa marejeleo kamili yameandikwa. Ikiwa anwani haina ishara ya dola karibu na safu na majina ya safu, basi unahitaji kuwaongeza kwa kushinikiza ufunguo wa F4 mpaka alama ya $ iko karibu na safu na majina ya safu.

Kwa kurekodi data kwa mikono

Katika hali iliyo hapo juu, orodha iliandikwa kwa kuonyesha safu inayohitajika. Hii ni njia rahisi, lakini wakati mwingine ni muhimu kurekodi data kwa mikono. Hii itafanya iwezekanavyo kuzuia kurudiwa kwa habari kwenye kitabu cha kazi.

Tuseme tunakabiliwa na kazi ya kuunda orodha iliyo na chaguzi mbili zinazowezekana: ndio na hapana. Ili kukamilisha kazi, ni muhimu:

1. Bofya kwenye seli kwa orodha.
2. Fungua "Data" na upate sehemu ya "Angalia Data" inayojulikana kwetu.
   

   

3. Tena, chagua aina ya "Orodha".
   

   

4. Hapa unahitaji kuingia "Ndiyo; Hapana" kama chanzo. Tunaona kwamba maelezo yameingizwa kwa mikono kwa kutumia nusu koloni kwa kuhesabiwa.

Baada ya kubofya OK, tuna matokeo yafuatayo.

Ifuatayo, programu itaunda kiotomati menyu ya kushuka kwenye seli inayofaa. Taarifa zote ambazo mtumiaji amebainisha kama vitu kwenye orodha ibukizi. Sheria za kuunda orodha katika seli kadhaa ni sawa na zile zilizopita, isipokuwa tu kwamba lazima ueleze habari kwa mikono kwa kutumia semicolon.

Kuunda orodha kunjuzi kwa kutumia kitendakazi cha OFFSET

Mbali na njia ya classical, inawezekana kutumia kazi KUTOLEWAkutengeneza menyu kunjuzi.

Hebu tufungue karatasi.

Ili kutumia chaguo la kukokotoa kwa orodha ya kushuka, unahitaji kufanya yafuatayo:

1. Chagua seli ya kukuvutia ambapo ungependa kuweka orodha ya siku zijazo.
2. Fungua kichupo cha "Data" na dirisha la "Uthibitishaji wa Data" kwa mlolongo.
   

   

3. Weka "Orodha". Hii inafanywa kwa njia sawa na mifano ya awali. Hatimaye, formula ifuatayo hutumiwa: =OFFSET(A$2$;0;0;5). Tunaiingiza ambapo seli ambazo zitatumika kama hoja zimebainishwa.

Kisha programu itaunda menyu na orodha ya matunda.

Syntax ya hii ni:

=OFFSET(marejeleo, msimbo_kuweka_kurekebisha, safu_wima, [urefu], [upana])

Tunaona kwamba kipengele hiki kina hoja 5. Kwanza, anwani ya seli ya kwanza ya kusawazishwa imetolewa. Hoja mbili zinazofuata zinabainisha ni safu mlalo na safu wima ngapi za kurekebisha. Tukizungumza juu yetu, hoja ya Urefu ni 5 kwa sababu inawakilisha urefu wa orodha. 

Orodha kunjuzi katika Excel na uingizwaji wa data (+ kwa kutumia kazi ya OFFSET)

Katika kesi iliyotolewa KUTOLEWA inaruhusiwa kuunda menyu ibukizi iliyo katika safu maalum. Ubaya wa njia hii ni kwamba baada ya kuongeza kipengee, itabidi uhariri fomula mwenyewe.

Ili kuunda orodha inayobadilika na usaidizi wa kuingiza habari mpya, lazima:

1. Chagua seli unayopenda.
2. Panua kichupo cha "Data" na ubofye "Uthibitishaji wa Data".
3. Katika dirisha linalofungua, chagua kipengee cha "Orodha" tena na ueleze fomula ifuatayo kama chanzo cha data: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Bofya OK.

Hii ina kipengele cha kukokotoa COUNTIF, ili kuamua mara moja ni seli ngapi zimejazwa (ingawa ina idadi kubwa zaidi ya matumizi, tunaandika tu hapa kwa madhumuni maalum).

Ili fomula ifanye kazi kwa kawaida, ni muhimu kufuatilia ikiwa kuna seli tupu kwenye njia ya fomula. Hawapaswi kuwa.

Orodha kunjuzi na data kutoka laha nyingine au faili ya Excel

Njia ya classic haifanyi kazi ikiwa unahitaji kupata taarifa kutoka kwa hati nyingine au hata karatasi iliyo kwenye faili sawa. Kwa hili, kazi hutumiwa INDIRECT, ambayo inakuwezesha kuingia katika muundo sahihi kiungo kwenye seli iko kwenye karatasi nyingine au kwa ujumla - faili. Unahitaji kufanya yafuatayo:

1. Washa seli ambapo tunaweka orodha.
2. Tunafungua dirisha ambalo tayari tunajua. Katika sehemu ile ile tulipoonyesha vyanzo vya visanduku vingine hapo awali, fomula inaonyeshwa katika umbizo =INDIRECT(“[List1.xlsx]Laha1!$A$1:$A$9”). Kwa kawaida, badala ya Orodha1 na Karatasi1, unaweza kuingiza majina yako ya kitabu na laha, mtawalia. 

Tahadhari! Jina la faili limetolewa katika mabano ya mraba. Katika kesi hii, Excel haitaweza kutumia faili ambayo imefungwa kwa sasa kama chanzo cha habari.

Inapaswa pia kuzingatiwa kuwa jina la faili yenyewe lina maana tu ikiwa hati inayohitajika iko kwenye folda sawa na ile ambayo orodha itaingizwa. Ikiwa sio, basi lazima ueleze anwani ya hati hii kwa ukamilifu.

Kuunda Kunjuzi Tegemezi

Orodha tegemezi ni ile ambayo maudhui yake yameathiriwa na chaguo la mtumiaji katika orodha nyingine. Tuseme tuna meza iliyofunguliwa mbele yetu ambayo ina safu tatu, ambayo kila moja imepewa jina.

Unahitaji kufuata hatua hizi ili kuzalisha orodha ambazo matokeo yake yameathiriwa na chaguo lililochaguliwa katika orodha nyingine.

1. Unda orodha ya 1 yenye majina ya masafa.
   

   

2. Katika hatua ya kuingia chanzo, viashiria vinavyohitajika vinaonyeshwa moja kwa moja.
   

   

3. Tengeneza orodha ya 2 kulingana na aina ya mmea ambao mtu amechagua. Vinginevyo, ikiwa unataja miti katika orodha ya kwanza, basi taarifa katika orodha ya pili itakuwa "mwaloni, hornbeam, chestnut" na zaidi. Inahitajika kuandika fomula mahali pa uingizaji wa chanzo cha data =INDIRECT(E3). E3 - seli iliyo na jina la safu 1.=INDIRECT(E3). E3 - seli iliyo na jina la orodha 1.

Sasa kila kitu kiko tayari.

Jinsi ya kuchagua maadili mengi kutoka kwa orodha ya kushuka?

Wakati mwingine haiwezekani kutoa upendeleo kwa thamani moja tu, hivyo zaidi ya moja lazima ichaguliwe. Kisha unahitaji kuongeza jumla kwenye msimbo wa ukurasa. Kutumia mchanganyiko muhimu Alt + F11 kufungua Mhariri wa Visual Basic. Na kanuni imeingizwa hapo.

Mabadiliko ya Laha Ndogo ya Kibinafsi (Lengo la ByVal Kama Masafa)

    On Hitilafu Endelea Ifwatayo

    Ikiwa Haiingiliani(Lengo, Masafa(«Е2:Е9»)) Si Kitu Na Inalengwa.Cells.Hesabu = 1 Kisha

        Application.EnableEvents = Si kweli

        Ikiwa Len (Target.Offset (0, 1)) = 0 Kisha

            Target.Offset (0, 1) = Lengo

        mwingine

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

        Kama mwisho

        Lengwa.Futa Yaliyomo

        Application.EnableEvents = Kweli

    Kama mwisho

Mwisho Sub 

Ili yaliyomo kwenye seli kuonyeshwa hapa chini, tunaingiza msimbo ufuatao kwenye kihariri.

Mabadiliko ya Laha Ndogo ya Kibinafsi (Lengo la ByVal Kama Masafa)

    On Hitilafu Endelea Ifwatayo

    Ikiwa Haiingiliani(Lengo, Masafa(«Н2:К2»)) Si Kitu Na Inalengwa.Seli.Hesabu = 1 Kisha

        Application.EnableEvents = Si kweli

        Ikiwa Len (Target.Offset (1, 0)) = 0 Kisha

            Target.Offset (1, 0) = Lengo

        mwingine

            Lengo.Mwisho (xlDown) .Offset (1, 0) = Lengo

        Kama mwisho

        Lengwa.Futa Yaliyomo

        Application.EnableEvents = Kweli

    Kama mwisho

Mwisho Sub

Na hatimaye, kanuni hii hutumiwa kuandika katika seli moja.

Mabadiliko ya Laha Ndogo ya Kibinafsi (Lengo la ByVal Kama Masafa)

    On Hitilafu Endelea Ifwatayo

    Iwapo Haijaingiliana(Lengo, Msururu(«C2:C5»)) Sio Kitu Na Inalengwa.Cells.Hesabu = 1 Kisha

        Application.EnableEvents = Si kweli

        newVal = Lengo

        Tendua

        oldval = Lengo

        Ikiwa Len (oldval) <> 0 Na oldval <> newVal Basi

            Lengo = Lengo & «,» & newVal

        mwingine

            Lengo = newVal

        Kama mwisho

        Ikiwa Len (newVal) = 0 Kisha Target.ClearContents

        Application.EnableEvents = Kweli

    Kama mwisho

Mwisho Sub

Masafa yanaweza kuhaririwa.

Jinsi ya kutengeneza orodha ya kushuka na utaftaji?

Katika kesi hii, lazima kwanza utumie aina tofauti ya orodha. Kichupo cha "Msanidi programu" kinafungua, baada ya hapo unahitaji kubofya au kugonga (ikiwa skrini inagusa) kwenye kipengele cha "Ingiza" - "ActiveX". Ina sanduku la combo. Utaulizwa kuteka orodha hii, baada ya hapo itaongezwa kwenye hati.

Zaidi ya hayo, imeundwa kupitia mali, ambapo masafa yamebainishwa katika chaguo la ListFillRange. Seli ambapo thamani iliyobainishwa na mtumiaji inaonyeshwa inasanidiwa kwa kutumia chaguo la LinkedCell. Ifuatayo, unahitaji tu kuandika wahusika wa kwanza, kwani programu itapendekeza moja kwa moja maadili iwezekanavyo.

Orodha kunjuzi na uingizwaji wa data kiotomatiki

Pia kuna chaguo za kukokotoa ambazo data hubadilishwa kiotomatiki baada ya kuongezwa kwenye masafa. Ni rahisi kufanya hivi:

1. Unda seti ya seli kwa orodha ya baadaye. Kwa upande wetu, hii ni seti ya rangi. Tunaichagua.
   

   

2. Ifuatayo, inahitaji kupangiliwa kama jedwali. Unahitaji kubofya kitufe cha jina moja na uchague mtindo wa meza.
   

   

   

Ifuatayo, unahitaji kuthibitisha safu hii kwa kushinikiza kitufe cha "OK".

Tunachagua jedwali linalotokana na kuipa jina kupitia uwanja wa kuingiza ulio juu ya safu A.

Hiyo ndiyo yote, kuna meza, na inaweza kutumika kama msingi wa orodha ya kushuka, ambayo unahitaji:

1. Chagua kiini ambapo orodha iko.
2. Fungua kidirisha cha Uthibitishaji wa Data.
   

   

3. Tunaweka aina ya data kwa "Orodha", na kama maadili tunatoa jina la jedwali kupitia = ishara.
   

   

   

Kila kitu, kiini kiko tayari, na majina ya rangi yanaonyeshwa ndani yake, kama tulivyohitaji hapo awali. Sasa unaweza kuongeza nafasi mpya kwa kuziandika kwa kisanduku kilicho chini kidogo mara baada ya cha mwisho.

Hii ndiyo faida ya jedwali, kwamba masafa huongezeka kiotomatiki data mpya inapoongezwa. Ipasavyo, hii ndiyo njia rahisi zaidi ya kuongeza orodha.

Jinsi ya kunakili orodha kunjuzi?

Ili kunakili, inatosha kutumia mchanganyiko muhimu Ctrl + C na Ctrl + V. Kwa hiyo orodha ya kushuka itanakiliwa pamoja na muundo. Ili kuondoa fomati, unahitaji kutumia kuweka maalum (katika menyu ya muktadha, chaguo hili linaonekana baada ya kunakili orodha), ambapo chaguo la "masharti juu ya maadili" limewekwa.

Chagua visanduku vyote vilivyo na orodha kunjuzi

Ili kukamilisha kazi hii, lazima utumie kazi ya "Chagua kikundi cha seli" katika kikundi cha "Pata na Chagua".

Baada ya hapo, sanduku la mazungumzo litafungua, ambapo unapaswa kuchagua vitu "Zote" na "Haya sawa" kwenye menyu ya "Uthibitishaji wa Data". Kipengee cha kwanza huchagua orodha zote, na pili huchagua tu wale wanaofanana na fulani.