Momwe mungapangire mndandanda wotsitsa mu Excel

Mndandanda wotsitsa ndi chida chothandiza kwambiri chomwe chingathandize kuti ntchito ndi zidziwitso zikhale zomasuka. Zimapangitsa kukhala ndi zikhalidwe zingapo mu cell nthawi imodzi, zomwe mungagwiritse ntchito, monga zina zilizonse. Kuti musankhe yomwe mukufuna, ingodinani pazithunzi za mivi, pambuyo pake mndandanda wazinthu zauXNUMXbuXNUMXbis ukuwonetsedwa. Mukasankha imodzi, selo imadzazidwa ndi izo, ndipo mafomuwa amawerengedwanso potengera izo.

Excel imapereka njira zosiyanasiyana zopangira menyu otsika, komanso, imakupatsani mwayi wosintha mwamakonda. Tiyeni tipende njirazi mwatsatanetsatane.

Ndondomeko yopanga mndandanda

Kuti mupange ma pop-up menyu, dinani pazinthu zomwe zili m'mphepete mwa njira "Data" - "Kutsimikizika kwa data". Bokosi la zokambirana lidzatsegulidwa pomwe muyenera kupeza tabu ya "Parameters" ndikudina ngati silinatsegulidwe kale. Ili ndi makonda ambiri, koma chinthu cha "Data Type" ndichofunika kwa ife. Mwa matanthauzo onse, “Mndandanda” ndi wolondola.

Chiwerengero cha njira zomwe zidziwitso zimalowetsedwa pamndandanda wa pop-up ndizochulukirapo.

1. Chizindikiro chodziyimira pawokha cha mndandanda wazinthu zosiyanitsidwa ndi semicolon mugawo la "Source" lomwe lili pagawo lomwelo la bokosi lomwelo.
   

   

2. Chisonyezero choyambirira cha makhalidwe abwino. The Source field ili ndi mitundu yomwe chidziwitso chofunikira chikupezeka.
   

   

3. Kutchula mtunda wotchulidwa. Njira yomwe imabwereza yapitayi, koma ndikofunikira kuti mutchule mtunduwo.
   

   

Iliyonse mwa njirazi idzatulutsa zotsatira zomwe mukufuna. Tiyeni tiwone njira zopangira mindandanda yotsikira m'mikhalidwe yeniyeni.

Kutengera zomwe zachokera pamndandanda

Tiyerekeze kuti tili ndi tebulo lofotokoza mitundu ya zipatso zosiyanasiyana.

Kuti mupange mndandanda wazinthu zotsikira-pansi kutengera chidziwitso ichi, muyenera kuchita izi:

1. Sankhani selo lomwe lasungidwa pamndandanda wamtsogolo.
2. Pezani tabu ya Data pa riboni. Pamenepo timadina "Verify data".
   

   

3. Pezani chinthucho "mtundu wa data" ndikusintha mtengo kukhala "List".
   

   

4. M'munda wosonyeza njira ya "Source", lowetsani zomwe mukufuna. Chonde dziwani kuti maumboni athunthu ayenera kufotokozedwa kotero kuti pokopera mndandandawo, chidziwitsocho chisasunthe.
   

Kuphatikiza apo, pali ntchito yopangira mindandanda nthawi imodzi mumaselo opitilira umodzi. Kuti muchite izi, muyenera kusankha zonse, ndikuchita zomwe tafotokozazi. Apanso, muyenera kuwonetsetsa kuti maumboni athunthu alembedwa. Ngati adilesi ilibe chizindikiro cha dola pafupi ndi mzere ndi mayina a mzere, ndiye kuti muyenera kuwawonjezera mwa kukanikiza fungulo la F4 mpaka chizindikiro cha $ chili pafupi ndi mzere ndi mayina a mzere.

Ndi zolemba pamanja deta

M'mikhalidwe yomwe ili pamwambayi, mndandandawo unalembedwa ndikuwonetsa mndandanda wofunikira. Iyi ndi njira yabwino, koma nthawi zina m'pofunika kulemba deta pamanja. Izi zipangitsa kuti zitheke kupewa kubwereza zomwe zili m'buku lantchito.

Tiyerekeze kuti tikuyang'anizana ndi ntchito yopanga mndandanda wokhala ndi zosankha ziwiri: inde ndi ayi. Kuti mugwire ntchitoyo, ndikofunikira:

1. Dinani pa selo kuti mupeze mndandanda.
2. Tsegulani "Data" ndipo pezani gawo la "Data Check" lodziwika kwa ife.
   

   

3. Apanso, sankhani mtundu wa "List".
   

   

4. Apa muyenera kulowa “Inde; Ayi” monga gwero. Tikuwona kuti zambiri zimalowetsedwa pamanja pogwiritsa ntchito semicolon powerengera.

Pambuyo kuwonekera OK, tili ndi zotsatirazi.

Kenako, pulogalamuyo imangopanga menyu yotsitsa mu cell yoyenera. Zidziwitso zonse zomwe wosuta adazifotokoza ngati zinthu zomwe zili pamndandanda wa pop-up. Malamulo opangira mndandanda m'maselo angapo ndi ofanana ndi akale, kupatulapo kuti muyenera kufotokoza zambiri pamanja pogwiritsa ntchito semicolon.

Kupanga mndandanda wotsitsa pogwiritsa ntchito ntchito ya OFFSET

Kuphatikiza pa njira yachikale, ndizotheka kugwiritsa ntchito ntchitoyi Kutayakupanga menyu otsika.

Tiyeni titsegule pepala.

Kuti mugwiritse ntchito mndandanda wotsitsa, muyenera kuchita izi:

1. Sankhani selo lachidwi komwe mukufuna kuyika mndandanda wamtsogolo.
2. Tsegulani tabu "Data" ndi zenera la "Data Validation" motsatizana.
   

   

3. Khazikitsani "List". Izi zimachitika mofanana ndi zitsanzo zam'mbuyomu. Pomaliza, njira yotsatirayi imagwiritsidwa ntchito: =OFFSET(A$2$;0;0;5). Timalowetsamo momwe maselo omwe adzagwiritsidwe ntchito ngati mkangano amatchulidwa.

Ndiye pulogalamu adzalenga menyu ndi mndandanda wa zipatso.

Syntax ya izi ndi:

=OFFSET(reference,line_offset,column_offset,[kutalika],[width])

Tikuwona kuti ntchitoyi ili ndi mikangano 5. Choyamba, adilesi yoyamba ya cell yomwe iyenera kuchotsedwa imaperekedwa. Zotsutsana ziwiri zotsatira zimafotokoza mizere ingati ndi mizati yoti muchepetse. Kunena za ife, mkangano wa Kutalika ndi 5 chifukwa umayimira kutalika kwa mndandanda. 

Mndandanda wotsikira mu Excel ndikusintha kwa data (+ pogwiritsa ntchito ntchito ya OFFSET)

Pankhani yoperekedwa Kutaya amaloledwa kupanga pop-up menyu yomwe ili mumtundu wokhazikika. Choyipa cha njirayi ndikuti mutawonjezera chinthucho, muyenera kusintha nokha fomula.

Kuti mupange mndandanda wosinthika wokhala ndi chithandizo cholowetsa zidziwitso zatsopano, muyenera:

1. Sankhani selo lokonda.
2. Wonjezerani "Data" tabu ndikudina "Kutsimikizira Data".
3. Pazenera lomwe limatsegulidwa, sankhaninso chinthu cha "Mndandanda" ndikutchulanso fomula ili ngati gwero la data: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Dinani OK.

Izi zili ndi ntchito COUNTIF, kuti mudziwe mwamsanga kuchuluka kwa maselo omwe amadzazidwa (ngakhale ali ndi chiwerengero chochulukirapo cha ntchito, timangolemba apa ndi cholinga china).

Kuti fomuyo igwire bwino ntchito, ndikofunikira kufufuza ngati pali maselo opanda kanthu panjira ya chilinganizocho. Iwo sayenera kukhala.

Mndandanda wotsitsa wokhala ndi data kuchokera patsamba lina kapena fayilo ya Excel

Njira yachikale siyigwira ntchito ngati mukufuna kudziwa zambiri kuchokera ku chikalata china kapena pepala lomwe lili mufayilo yomweyo. Kwa izi, ntchitoyo imagwiritsidwa ntchito KUMALO, zomwe zimakulolani kuti mulowe mumtundu wolondola ulalo ku selo yomwe ili mu pepala lina kapena kawirikawiri - fayilo. Muyenera kuchita izi:

1. Yambitsani selo pomwe timayika mndandanda.
2. Timatsegula zenera lomwe tikudziwa kale. Pamalo omwewo pomwe tidawonetsa kale magwero amitundu ina, fomula imawonetsedwa mumpangidwewo = INDIRECT(“[List1.xlsx]Sheet1!$A$1:$A$9”). Mwachilengedwe, m'malo mwa List1 ndi Sheet1, mutha kuyika mayina abuku lanu ndi mapepala, motsatana. 

Chenjerani! Dzina lafayilo limaperekedwa m'mabulaketi apakati. Pankhaniyi, Excel sangathe kugwiritsa ntchito fayilo yomwe yatsekedwa pano ngati gwero lachidziwitso.

Tiyeneranso kukumbukira kuti dzina la fayilo palokha limakhala lomveka ngati chikalata chofunikira chili mufoda yomweyi ndi yomwe mndandandawo udzalowetsedwa. Ngati sichoncho, muyenera kufotokoza adiresi ya chikalatachi mokwanira.

Kupanga Zotsitsa Zodalira

Mndandanda wodalira ndi womwe zomwe zili mkati mwake zimakhudzidwa ndi kusankha kwa wogwiritsa ntchito pamndandanda wina. Tiyerekeze kuti tili ndi tebulo lotsegulidwa kutsogolo kwathu lomwe lili ndi mizere itatu, iliyonse yomwe yapatsidwa dzina.

Muyenera kutsatira izi kuti mupange mindandanda yomwe zotsatira zake zimakhudzidwa ndi zomwe zasankhidwa pamndandanda wina.

1. Pangani mndandanda woyamba wokhala ndi mayina osiyanasiyana.
   

   

2. Pamalo olowera magwero, zizindikiro zofunika zimawonetsedwa chimodzi ndi chimodzi.
   

   

3. Pangani mndandanda wachiwiri kutengera mtundu wa chomera chomwe munthu wasankha. Kapenanso, ngati mutchula mitengo pamndandanda woyamba, ndiye kuti zomwe zili mumndandanda wachiwiri zidzakhala "oak, hornbeam, chestnut" ndi kupitirira. Ndikofunikira kulemba chilinganizocho m'malo mwa kuyika kwa gwero la data = INDIRECT(E3). E3 - selo lomwe lili ndi dzina lamitundu 1.=INDIRECT(E3). E3 - cell yokhala ndi dzina la mndandanda 1.

Tsopano zonse zakonzeka.

Momwe mungasankhire ma values ​​angapo pamndandanda wotsitsa?

Nthawi zina sizingatheke kupereka zokonda pamtengo umodzi wokha, kotero kuti zambiri ziyenera kusankhidwa. Kenako muyenera kuwonjezera macro ku code yatsamba. Kugwiritsa ntchito kiyi kuphatikiza Alt + F11 kumatsegula Visual Basic Editor. Ndipo code imayikidwa pamenepo.

Private Sub Worksheet_Change (ByVal Target As Range)

    Pa Zolakwika Zowonjezera Zotsatira

    Ngati Sichidutsana(Chandamale, Mtundu(«Е2:Е9»)) Palibe Ndipo Target.Cells.Count = 1 Ndiye

        Application.EnableEvents = Zabodza

        Ngati Len (Target.Offset (0, 1)) = 0 Ndiye

            Target.Offset (0, 1) = Cholinga

        china

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

        Kutha Ngati

        Cholinga.Chotsani Zamkatimu

        Application.EnableEvents = Zoona

    Kutha Ngati

mapeto Sub 

Kuti zomwe zili m'maselo ziwonetsedwe pansipa, timayika kachidindo kameneka mu mkonzi.

Private Sub Worksheet_Change (ByVal Target As Range)

    Pa Zolakwika Zowonjezera Zotsatira

    If Not Intersect(Chandanda, Range(«Н2:К2»)) Si Kanthu Ndipo Target.Cells.Count = 1 Ndiye

        Application.EnableEvents = Zabodza

        Ngati Len (Target.Offset (1, 0)) = 0 Ndiye

            Target.Offset (1, 0) = Cholinga

        china

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

        Kutha Ngati

        Cholinga.Chotsani Zamkatimu

        Application.EnableEvents = Zoona

    Kutha Ngati

mapeto Sub

Ndipo potsiriza, code iyi imagwiritsidwa ntchito kulemba mu selo limodzi.

Private Sub Worksheet_Change (ByVal Target As Range)

    Pa Zolakwika Zowonjezera Zotsatira

    If Not Intersect(Target, Range(«C2:C5»)) Is nothing And Target.Cells.Count = 1 Ndiye

        Application.EnableEvents = Zabodza

        newVal = Cholinga

        Ntchito.Bwezerani

        oldval = Cholinga

        Ngati Len (oldval) <> 0 Ndi oldval <> newVal Ndiye

            Cholinga = Cholinga & «,» & newVal

        china

            Target = newVal

        Kutha Ngati

        Ngati Len (newVal) = 0 Ndiye Target.ClearContents

        Application.EnableEvents = Zoona

    Kutha Ngati

mapeto Sub

Masanjidwe amatha kusintha.

Momwe mungapangire mndandanda wotsitsa ndikusaka?

Pankhaniyi, choyamba muyenera kugwiritsa ntchito mtundu wina wa mndandanda. Tsamba la "Developer" limatsegulidwa, pambuyo pake muyenera kudina kapena kudina (ngati chinsalu chakhudza) pa "Insert" - "ActiveX". Ili ndi bokosi la combo. Mudzafunsidwa kuti mujambule mndandandawu, pambuyo pake udzawonjezedwa ku chikalatacho.

Kupitilira apo, imakonzedwa kudzera mu katundu, pomwe mitundu imatchulidwa mu ListFillRange njira. Selo yomwe mtengo wotchulidwa ndi wogwiritsa ntchito umakonzedwa pogwiritsa ntchito njira ya LinkedCell. Kenako, mungofunika kulemba zilembo zoyambirira, chifukwa pulogalamuyo imangowonetsa zomwe zingatheke.

Mndandanda wotsitsa wokhala ndi zosintha za data zokha

Palinso ntchito yomwe deta imalowetsedwa m'malo mwake itatha kuwonjezeredwa pamtundu. Ndi zophweka kuchita izi:

1. Pangani gulu lamagulu amndandanda wamtsogolo. Kwa ife, ichi ndi mtundu wa mitundu. Timasankha izo.
   

   

2. Kenako, iyenera kusinthidwa ngati tebulo. Muyenera dinani batani la dzina lomwelo ndikusankha kalembedwe ka tebulo.
   

   

   

Kenako, muyenera kutsimikizira izi mwa kukanikiza batani "Chabwino".

Timasankha tebulo lotsatira ndikulipatsa dzina kudzera mugawo lolowetsa lomwe lili pamwamba pa ndime A.

Ndizo zonse, pali tebulo, ndipo lingagwiritsidwe ntchito ngati maziko a mndandanda wotsitsa, womwe mukufunikira:

1. Sankhani selo pomwe mndandanda uli.
2. Tsegulani dialog ya Data Validation.
   

   

3. Timayika mtundu wa data ku "List", ndipo monga mfundo timapatsa dzina la tebulo kudzera pa = chizindikiro.
   

   

   

Chilichonse, selo yakonzeka, ndipo mayina amitundu akuwonetsedwa mmenemo, monga momwe tinkafunira poyamba. Tsopano mutha kuwonjezera malo atsopano pongowalemba mu cell yomwe ili pansi pang'ono pambuyo pa yomaliza.

Uwu ndi mwayi wa tebulo, kuti kuchuluka kwake kumangowonjezereka pamene deta yatsopano ikuwonjezedwa. Chifukwa chake, iyi ndi njira yabwino kwambiri yowonjezerera mndandanda.

Kodi mungakopere bwanji mndandanda wotsitsa?

Kukopera, ndikokwanira kugwiritsa ntchito kiyi kuphatikiza Ctrl + C ndi Ctrl + V. Kotero mndandanda wotsitsa udzakopera pamodzi ndi masanjidwewo. Kuti muchotse masanjidwe, muyenera kugwiritsa ntchito phala lapadera (mumenyu yankhaniyo, njirayi ikuwoneka mutakopera mndandanda), pomwe njira ya "mikhalidwe pamitengo" imayikidwa.

Sankhani maselo onse omwe ali ndi mndandanda wotsitsa

Kuti mukwaniritse ntchitoyi, muyenera kugwiritsa ntchito "Sankhani gulu la maselo" mu gulu la "Pezani ndi Sankhani".

Pambuyo pake, bokosi la zokambirana lidzatsegulidwa, pomwe muyenera kusankha zinthu "Zonse" ndi "Zomwezi" mu "Kutsimikizira Data" menyu. Chinthu choyamba chimasankha mndandanda wonse, ndipo chachiwiri chimasankha okhawo omwe ali ofanana ndi ena.