Maitiro ekugadzira rondedzero yekudonha muExcel

Rondedzero yekudonhedza-pasi chishandiso chinoshamisa chinobatsira chinogona kubatsira kuita kuti kushanda neruzivo kuve nyore. Izvo zvinoita kuti zvikwanise kuve neakaverengeka akati wandei musero kamwechete, iyo iwe yaunogona kushanda nayo, senge chero vamwe. Kuti usarudze iyo yaunoda, ingo dzvanya pane museve icon, mushure mezvo runyoro rwemakoshi uXNUMXbuXNUMXbis inoratidzwa. Mushure mekusarudza imwe chaiyo, sero rinongozadzwa naro, uye mafomula anoverengerwa zvakare zvichibva pairi.

Excel inopa akawanda akasiyana nzira dzekugadzira yekudonha-pasi menyu, uye nekuwedzera, inokutendera iwe kuti uzvigadzirise zviri nyore. Ngationgororei nzira idzi zvakadzama.

Kugadzira rondedzero

Kuti ugadzire pop-up menyu, tinya pane menyu zvinhu munzira "Data" - "Data Validation". Bhokisi rebhokisi rinovhura paunoda kuwana iyo "Parameters" tab uye tinya pairi kana isati yavhurwa. Iine zvakawanda zvekugadzirisa, asi "Data Type" chinhu chakakosha kwatiri. Pazvinoreva zvese, “Rondedzero” ndiyo chaiyo.

Huwandu hwenzira dzekupinzwa nadzo ruzivo mune pop-up list yakakura kwazvo.

1. Chiratidzo chakazvimiririra chezvinyorwa zvakapatsanurwa nesemicolon mundima ye "Source" iri pane imwechete tab yebhokisi rimwechete rebhokisi.
   

   

2. Chiratidzo chekutanga chetsika. Iyo Source ndima ine huwandu uko ruzivo rwunodiwa rwunowanikwa.
   

   

3. Kudoma dzinza rine zita. Nzira inodzokorora yapfuura, asi zvinongodiwa kuti utange zita renji.
   

   

Chero yeiyi nzira inoburitsa mhedzisiro inodiwa. Ngatitarisei nzira dzekugadzira zvinyorwa zvekudonhedza mumamiriro ezvinhu chaiwo.

Kubva pane data kubva pane rondedzero

Ngatitii tine tafura inotsanangura mhando dzemichero yakasiyana.

Kugadzira runyoro mune yekudonhedza-pasi menyu zvichibva pane iyi seti yeruzivo, unofanirwa kuita zvinotevera:

1. Sarudza sero rakachengeterwa runyorwa rweramangwana.
2. Tsvaga iyo Data tab pane ribhoni. Ikoko tinodzvanya pa "Verify data".
   

   

3. Tsvaga chinhu "Data Type" uye shandura kukosha ku "List".
   

   

4. Mumunda unoratidza iyo "Source" sarudzo, isa iyo yaunoda renji. Ndokumbira utarise kuti mareferenzi akakwana anofanirwa kutsanangurwa kuitira kuti kana uchikopa rondedzero, ruzivo rurege kuchinja.
   

Pamusoro pezvo, pane basa rekugadzira rondedzero kamwechete mune anopfuura sero rimwe. Kuti uite izvi, unofanirwa kuvasarudza vese, uye kuita nhanho dzakafanana sezvakatsanangurwa pakutanga. Zvakare, iwe unofanirwa kuve nechokwadi chekuti mareferenzi akakwana akanyorwa. Kana iyo kero isina chiratidzo chedhora padivi pekoromo uye mazita emutsara, saka iwe unofanirwa kuwedzera iwo nekudzvanya F4 kiyi kusvika iyo $ sign iri padivi pekoromo uye mitsara mazita.

Nemanyorero data rekodhi

Mumamiriro ezvinhu ari pamusoro apa, rondedzero yakanyorwa nekusimbisa uwandu hunodiwa. Iyi inzira iri nyore, asi dzimwe nguva zvinodikanwa kunyora nemaoko data. Izvi zvinozoita kuti zvikwanise kudzivirira kudzokororwa kweruzivo mubhuku rebasa.

Ngatitii takatarisana nebasa rekugadzira runyoro rine sarudzo mbiri dzinobvira: hongu uye kwete. Kuti uite basa racho, zvinodikanwa:

1. Dzvanya pa cell kuti uwane runyorwa.
2. Vhura "Data" uye ipapo tsvaga chikamu che "Data Check" chatinoziva kwatiri.
   

   

3. Zvakare, sarudza iyo "List" mhando.
   

   

4. Pano iwe unofanirwa kuisa "Hongu; Kwete” semabviro. Isu tinoona kuti ruzivo rwunopinzwa nemaoko uchishandisa semicolon yekuverenga.

Mushure mekudzvanya OK, isu tine mhedzisiro inotevera.

Tevere, iyo purogiramu inongogadzira yekudonha-pasi menyu musero yakakodzera. Ruzivo rwese rwunotsanangurwa nemushandisi sezvinhu zviri mupop-up list. Mitemo yekugadzira rondedzero mumaseru akati wandei yakafanana neyakapfuura, kunze kwekunge iwe unofanirwa kutsanangura ruzivo nemaoko uchishandisa semicolon.

Kugadzira rondedzero yekudonha uchishandisa iyo OFFSET basa

Mukuwedzera kune classical nzira, zvinokwanisika kushandisa basa KURASAkugadzira menyu ekudonha.

Ngativhure bepa.

Kuti ushandise basa rekudonhedza runyoro, unofanirwa kuita zvinotevera:

1. Sarudza sero rekufarira kwaunoda kuisa runyorwa rweramangwana.
2. Vhura iyo "Data" tab uye "Data Validation" hwindo mukutevedzana.
   

   

3. Seta "List". Izvi zvinoitwa nenzira yakafanana nemienzaniso yapfuura. Pakupedzisira, iyo inotevera formula inoshandiswa: =OFFSET(A$2$;0;0;5). Isu tinoipinda iyo iyo maseru achashandiswa senharo anotsanangurwa.

Zvadaro purogiramu ichagadzira menyu ine runyoro rwemichero.

Syntax yeizvi ndeiyi:

=OFFSET(reference,line_offset,column_offset,[urefu],[hupamhi])

Tinoona kuti basa iri rine 5 nharo. Chekutanga, kero yekutanga yesero inogadziriswa inopihwa. Nharo mbiri dzinotevera dzinotsanangura kuti mitsetse mingani nemakoramu ekugadzirisa. Kutaura nezvedu, iyo Height nharo ndeye 5 nekuti inomiririra hurefu hwechinyorwa. 

Dropdown list muExcel ine data substitution (+ uchishandisa OFFSET basa)

Munyaya yakapihwa KURASA inobvumirwa kugadzira pop-up menyu iri munzvimbo yakagadziriswa. Izvo zvakashata zveiyi nzira ndezvekuti mushure mekuwedzera chinhu, iwe uchafanirwa kugadzirisa iyo formula iwe pachako.

Kugadzira rondedzero ine simba nerutsigiro rwekupinda ruzivo rutsva, unofanirwa:

1. Sarudza cell yaunofarira.
2. Wedzera iyo "Data" tab uye tinya pa "Data Validation".
3. Muhwindo rinovhurwa, sarudza chinhu che "Rondedzerwa" uye tsanangura iyo inotevera fomula senzvimbo yedata: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Dzvanya OK.

Izvi zvine basa COUNTIF, kuti uone pakarepo kuti mangani masero akazadzwa (kunyangwe aine nhamba yakakura kwazvo yekushandiswa, tinongonyora pano nechinangwa chaicho).

Kuti fomura ishande zvakajairika, zvinodikanwa kutsvaga kana paine masero asina chinhu munzira yefomati. Havafaniri kudaro.

Dropdown list ine data kubva kune rimwe pepa kana Excel faira

Iyo yekare nzira haishande kana iwe uchida kuwana ruzivo kubva kune rimwe gwaro kana kunyange pepa riri mune imwecheteyo faira. Nokuda kweizvi, basa rinoshandiswa INDIRECT, iyo inokubvumira kuti upinde muhutano hwakarurama chibatanidza kune imwe sero iri mune rimwe pepa kana kuti kazhinji - faira. Iwe unofanirwa kuita zvinotevera:

1. Activate sero kwatinoisa rondedzero.
2. Isu tinovhura hwindo isu tinotoziva. Munzvimbo imwe chete yatakamboratidza kwainobva mamwe mazinga, fomula inoratidzwa mufomati =INDIRECT(“[List1.xlsx]Sheet1!$A$1:$A$9”). Nomuzvarirwo, pachinzvimbo cheChinyorwa1 uye Sheet1, unogona kuisa bhuku rako nemazita epepa, zvichiteerana. 

Attention! Zita refaira rinopihwa mumabhuraketi akaenzana. Muchiitiko ichi, Excel haizokwanisi kushandisa iyo faira iyo yakavharwa parizvino sesosi yeruzivo.

Izvo zvinofanirwa kucherechedzwa kuti iro zita refaira pacharo rine musoro chete kana gwaro rinodiwa riri mune imwechete folda neiyo iyo iyo rondedzero ichaiswa. Kana zvisina kudaro, unofanira kutsanangura kero yegwaro iri zvizere.

Kugadzira Dependent Dropdowns

Rondedzero inotsamira ndeiya ine zviri mukati zvinokanganiswa nesarudzo yemushandisi mune imwe rondedzero. Ngatitii tine tafura yakavhurwa pamberi pedu ine mitsara mitatu, imwe neimwe yakapihwa zita.

Iwe unofanirwa kutevedzera nhanho idzi kugadzira rondedzero ine mhedzisiro inokanganiswa nesarudzo yakasarudzwa mune imwe rondedzero.

1. Gadzira 1st list ine mazita emhando.
   

   

2. Panzvimbo yekupinda, zviratidzo zvinodikanwa zvinoratidzwa chimwe nechimwe.
   

   

3. Gadzira runyoro rwechipiri zvichienderana nerudzi rwechirimwa chakasarudzwa nemunhu. Neimwe nzira, kana iwe ukatsanangura miti mune yekutanga runyorwa, ipapo ruzivo rwechipiri runyorwa ruchava "oak, hornbeam, chestnut" uye mberi. Izvo zvinodikanwa kunyora pasi fomula munzvimbo yekupinza yedata sosi =INDIRECT(E3). E3 - sero rine zita remhando 1.=INDIRECT(E3). E3 - sero rine zita rerondedzero 1.

Iye zvino zvinhu zvose zvagadzirira.

Maitiro ekusarudza akawanda maitiro kubva pane inodonha pasi runyorwa?

Dzimwe nguva hazvibviri kupa sarudzo kune imwechete kukosha, saka kupfuura imwe chete inofanira kusarudzwa. Ipapo iwe unofanirwa kuwedzera macro kune peji kodhi. Uchishandisa kiyi musanganiswa Alt + F11 inovhura iyo Visual Basic Mharidzo. Uye kodhi inoiswa ipapo.

Private Sub Worksheet_Change (ByVal Target As Range)

    Pane Error Resume Zvadaro

    Kana Zvisina Kupindirana(Target, Range(«Е2:Е9»)) Hapana Uye Target.Cells.Count = 1 Zvadaro

        Application.EnableEvents = Nhema

        Kana Len (Target.Offset (0, 1)) = 0 Zvadaro

            Target.Offset (0, 1) = Chinangwa

        mumwe

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

        Endai Kana

        Target.ClearContents

        Application.EnableEvents = Chokwadi

    Endai Kana

End Sub 

Kuti zviri mukati memasero zviratidzwe pazasi, tinoisa kodhi inotevera mupepeti.

Private Sub Worksheet_Change (ByVal Target As Range)

    Pane Error Resume Zvadaro

    Kana Zvisina Kupindirana(Target, Range(«Н2:К2»)) Is Nothing And Target.Cells.Count = 1 Zvadaro

        Application.EnableEvents = Nhema

        Kana Len (Target.Offset (1, 0)) = 0 Zvadaro

            Target.Offset (1, 0) = Chinangwa

        mumwe

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

        Endai Kana

        Target.ClearContents

        Application.EnableEvents = Chokwadi

    Endai Kana

End Sub

Uye pakupedzisira, iyi kodhi inoshandiswa kunyora musero rimwe.

Private Sub Worksheet_Change (ByVal Target As Range)

    Pane Error Resume Zvadaro

    Kana Zvisina Kupindirana(Target, Range(«C2:C5»)) Hapana Uye Target.Cells.Count = 1 Zvadaro

        Application.EnableEvents = Nhema

        newVal = Chinangwa

        Application.Dzondura

        oldval = Chinangwa

        Kana Len (oldval) <> 0 Uye oldval <> newVal Ipapo

            Target = Target & «,» & newVal

        mumwe

            Target = newVal

        Endai Kana

        Kana Len (newVal) = 0 Zvadaro Target.ClearContents

        Application.EnableEvents = Chokwadi

    Endai Kana

End Sub

Ranges inogadziriswa.

Nzira yekugadzira rondedzero yekudonha nekutsvaga?

Muchiitiko ichi, unofanira kutanga kushandisa rudzi rwakasiyana rwerunyorwa. Iyo "Developer" tab inovhura, mushure mezvo iwe unofanirwa kudzvanya kana kudzvanya (kana iyo skrini yakabatwa) pane "Insert" - "ActiveX" chinhu. Iine combo box. Iwe unozokurudzirwa kudhirowa iyi rondedzero, mushure meiyo inozowedzerwa kune iyo gwaro.

Kupfuurirazve, inogadziridzwa kuburikidza nezvivakwa, uko renji inotsanangurwa muListFillRange sarudzo. Iyo sero iyo iyo mushandisi-inotsanangurwa kukosha inoratidzwa inogadziriswa uchishandisa iyo LinkedCell sarudzo. Tevere, iwe unongoda kunyora pasi mavara ekutanga, sezvo chirongwa chinozongo ratidza maitiro anogoneka.

Dropdown list ine otomatiki data substitution

Pane zvakare basa rekuti data rinotsiviwa otomatiki mushure mekunge vawedzerwa kune renji. Zviri nyore kuita izvi:

1. Gadzira seti yemasero erunyorwa rweramangwana. Muchiitiko chedu, iyi mutsara wemavara. Isu tinosarudza.
   

   

2. Tevere, inoda kuumbwa setafura. Iwe unofanirwa kudzvanya bhatani rezita rimwe chete uye sarudza iyo tafura maitiro.
   

   

   

Tevere, iwe unofanirwa kusimbisa iyi renji nekudzvanya bhatani re "OK".

Isu tinosarudza tafura inoguma toipa zita kuburikidza nemunda wekuisa uri pamusoro pekoromo A.

Ndizvozvo, pane tafura, uye inogona kushandiswa sehwaro hwechinyorwa chekudonhedza pasi, chaunoda:

1. Sarudza sero panowanikwa rondedzero.
2. Vhura iyo Data Validation dialog.
   

   

3. Isu tinoisa rudzi rwe data ku "Rondedzero", uye sehunhu tinopa zita retafura kuburikidza ne = chiratidzo.
   

   

   

Zvose, sero rakagadzirira, uye mazita emavara anoratidzwa mariri, sezvataida pakutanga. Iye zvino unogona kuwedzera zvinzvimbo zvitsva nekungonyora musero riri pazasi zvishoma mushure meyekupedzisira.

Iyi ndiyo mukana wetafura, kuti huwandu hunowedzera otomatiki kana data nyowani yawedzerwa. Naizvozvo, iyi ndiyo nzira iri nyore yekuwedzera rondedzero.

Nzira yekukopa sei pasi pasi list?

Kukopa, zvakakwana kushandisa kiyi yekubatanidza Ctrl + C uye Ctrl + V. Saka rondedzero yekudonha ichakopwa pamwe chete nefomati. Kuti ubvise fomati, iwe unofanirwa kushandisa yakakosha paste (mumenu yemukati, iyi sarudzo inooneka mushure mekukopa rondedzero), apo iyo "mamiriro ezvinhu pamitengo" sarudzo inoiswa.

Sarudza maseru ese ane donhodzo pasi

Kuti uite basa iri, unofanira kushandisa "Sarudza boka remasero" basa muboka re "Tsvaga uye Sarudza".

Mushure maizvozvi, bhokisi rebhokisi richazarura, kwaunofanira kusarudza zvinhu "Zvose" uye "Izvi zvakafanana" mu "Data Validation" menyu. Chinhu chekutanga chinosarudza mazita ose, uye chechipiri chinosarudza chete izvo zvakafanana kune mamwe.