Ungawenza kanjani uhlu lokudonsela phansi ku-Excel

Uhlu lokudonsela phansi luyithuluzi elisebenziseka ngendlela emangalisayo elingasiza ukwenza ukusebenza ngolwazi kukhululeke kakhudlwana. Kwenza kube nokwenzeka ukuqukatha amanani amaningana kuseli ngesikhathi esisodwa, ongasebenza ngawo, njengawo wonke amanye. Ukuze ukhethe oyidingayo, vele uchofoze kusithonjana somcibisholo, ngemuva kwalokho kuvezwe uhlu lwamanani uXNUMXbuXNUMXbis. Ngemva kokukhetha ethize, iseli ligcwaliswa ngalo ngokuzenzakalelayo, futhi amafomula abalwa kabusha ngokusekelwe kuyo.

I-Excel ihlinzeka ngezindlela eziningi ezahlukahlukene zokukhiqiza imenyu yokudonsela phansi, futhi ngaphezu kwalokho, ikuvumela ukuthi uzenze ngendlela oyifisayo kalula. Ake sihlaziye lezi zindlela ngokuningiliziwe.

Inqubo yokudala uhlu

Ukuze ukhiqize imenyu ye-pop-up, chofoza izinto zemenyu endleleni ethi "Idatha" - "Ukuqinisekiswa Kwedatha". Ibhokisi lengxoxo lizovuleka lapho udinga khona ukuthola ithebhu ethi “Parameters” bese uchofoza kuyo uma ingakaze ivulwe ngaphambilini. Inezilungiselelo eziningi, kodwa into ethi "Uhlobo Lwedatha" ibalulekile kithi. Kuzo zonke izincazelo, “Uhlu” iyona elungile.

Inani lezindlela ulwazi olufakwa ngazo ohlwini lwe-pop-up likhulu impela.

1. Isibonakaliso esizimele sezinto zohlu ezihlukaniswe isemikholoni kunkambu ethi “Umthombo” etholakala kuthebhu efanayo yebhokisi lengxoxo efanayo.
   

   

2. Inkomba yokuqala yamanani. Inkambu yomthombo iqukethe ububanzi lapho ulwazi oludingekayo lutholakala khona.
   

   

3. Icacisa ububanzi obuqanjwe igama. Indlela ephinda eyedlule, kodwa kudingekile kuphela ukusho igama lobubanzi.
   

   

Noma iyiphi yalezi zindlela izoveza umphumela oyifunayo. Ake sibheke izindlela zokwenza uhlu lokudonsela phansi ezimweni ezingokoqobo.

Ngokusekelwe kudatha evela kuhlu

Ake sithi sinetafula elichaza izinhlobo zezithelo ezahlukene.

Ukuze udale uhlu kumenyu yokudonsela phansi ngokusekelwe kule sethi yolwazi, udinga ukwenza lokhu okulandelayo:

1. Khetha iseli ebekelwe uhlu oluzayo.
2. Thola ithebhu Yedatha kuribhoni. Lapho sichofoza "Qinisekisa idatha".
   

   

3. Thola into ethi “Uhlobo Lwedatha” bese ushintshela inani elithi “Uhlu”.
   

   

4. Enkambini ekhomba inketho ethi "Umthombo", faka ububanzi obufisayo. Sicela uqaphele ukuthi izinkomba eziphelele kufanele zicaciswe ukuze lapho ukopisha uhlu, imininingwane ingashintshi.
   

Ngaphezu kwalokho, kunomsebenzi wokukhiqiza uhlu ngesikhathi esisodwa kumaseli angaphezu kwelilodwa. Ukufeza lokhu, kufanele uzikhethe zonke, futhi wenze izinyathelo ezifanayo njengoba kuchazwe ekuqaleni. Futhi, udinga ukwenza isiqiniseko sokuthi izinkomba eziphelele zibhaliwe. Uma ikheli lingenalo uphawu lwedola eduze kwekholomu namagama emigqa, udinga ukuwengeza ngokucindezela inkinobho ethi F4 kuze kube uphawu lwe-$ luseduze kwekholomu namagama omugqa.

Ngokuqoshwa kwedatha ngesandla

Esimeni esingenhla, uhlu lwabhalwa ngokugqamisa ububanzi obudingekayo. Lena indlela elula, kodwa ngezinye izikhathi kuyadingeka ukurekhoda idatha mathupha. Lokhu kuzokwenza ukuthi kube nokugwema ukuphindaphindeka kolwazi encwadini yokusebenzela.

Ake sithi sibhekene nomsebenzi wokwenza uhlu oluqukethe izinto ezimbili esingakhetha kuzo: yebo nocha. Ukuze ufeze lo msebenzi, kuyadingeka:

1. Chofoza iseli ukuze uthole uhlu.
2. Vula "Idatha" futhi uthole lapho isigaba "Ukuhlola Idatha" esisijwayele.
   

   

3. Futhi, khetha uhlobo "Uhlu".
   

   

4. Lapha udinga ukufaka “Yebo; Cha” njengomthombo. Siyabona ukuthi ulwazi lufakwa ngesandla kusetshenziswa isemicolon ukuze kubalwe.

Ngemva kokuchofoza KULUNGILE, sinomphumela olandelayo.

Okulandelayo, uhlelo luzodala ngokuzenzakalelayo imenyu yokudonsela phansi kuseli elifanele. Lonke ulwazi olucaciswe umsebenzisi njengezinto ezisohlwini oluzivelelayo. Imithetho yokudala uhlu kumaseli amaningana iyafana neyangaphambilini, ngaphandle kokuthi kufanele ucacise ulwazi mathupha usebenzisa isemicolon.

Ukudala uhlu lokudonsela phansi usebenzisa umsebenzi we-OFFSET

Ngaphezu kwendlela ye-classical, kungenzeka ukusebenzisa umsebenzi UKULAHLWAukukhiqiza amamenyu okwehlayo.

Asivule ishidi.

Ukuze usebenzise umsebenzi wohlu lokudonsela phansi, udinga ukwenza okulandelayo:

1. Khetha iseli olithakaselayo lapho ofuna ukubeka khona uhlu oluzayo.
2. Vula ithebhu "Idatha" kanye nefasitela elithi "Ukuqinisekiswa Kwedatha" ngokulandelana.
   

   

3. Setha “Uhlu”. Lokhu kwenziwa ngendlela efanayo nezibonelo ezedlule. Ekugcineni, kusetshenziswa ifomula elandelayo: =OFFSET(A$2$;0;0;5). Siyifaka lapho kucaciswe khona amaseli azosetshenziswa njenge-agumenti.

Khona-ke uhlelo luzokwenza imenyu enohlu lwezithelo.

I-syntax yalokhu ithi:

=OFFSET(inkomba,umugqa_ukwehliswa,ukususwa_kwekholomu,[ubude],[ububanzi])

Siyabona ukuthi lo msebenzi unezimpikiswano ezi-5. Okokuqala, ikheli leseli lokuqala elizosuswa liyanikezwa. Izimpikiswano ezimbili ezilandelayo zicacisa ukuthi mingaki imigqa namakholomu okufanele kususwe. Uma sikhuluma ngathi, i-Height argument ingu-5 ngoba imele ukuphakama kohlu. 

Uhlu olwehliswayo ku-Excel ngokushintshwa kwedatha (+ kusetshenziswa umsebenzi we-OFFSET)

Endabeni enikeziwe UKULAHLWA kuvunyelwe ukudala imenyu ye-pop-up etholakala ebangeni elingashintshi. Ububi bale ndlela ukuthi ngemva kokwengeza into, kuzodingeka uhlele ifomula ngokwakho.

Ukuze udale uhlu oluguquguqukayo olunosekelo lokufaka ulwazi olusha, kufanele:

1. Khetha iseli olithakaselayo.
2. Nweba ithebhu "Idatha" bese uchofoza "Ukuqinisekiswa Kwedatha".
3. Ewindini elivulayo, khetha into ethi "Faka" futhi futhi ucacise ifomula elandelayo njengomthombo wedatha: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Chofoza OK.

Lokhu kuqukethe umsebenzi COUNTIF, ukunquma ngokushesha ukuthi mangaki amaseli agcwalisiwe (nakuba inenani elikhulu kakhulu lokusetshenziswa, sivele siyibhale lapha ngenjongo ethile).

Ukuze ifomula isebenze ngokujwayelekile, kuyadingeka ukulandelela ukuthi akhona yini amaseli angenalutho endleleni yefomula. Akufanele.

Uhlu olwehliswayo olunedatha esuka kwelinye ishidi noma ifayela le-Excel

Indlela yakudala ayisebenzi uma udinga ukuthola ulwazi kwenye idokhumenti noma ngisho neshidi eliqukethwe kufayela elifanayo. Kulokhu, umsebenzi usetshenziswa INDALO, okuvumela ukuthi ufake ngefomethi efanele isixhumanisi esitokisini esikwelinye ishidi noma ngokujwayelekile - ifayela. Udinga ukwenza lokhu okulandelayo:

1. Yenza kusebenze iseli lapho sibeka khona uhlu.
2. Sivula iwindi esivele silazi. Endaweni efanayo lapho ngaphambilini sibonisa khona imithombo yobunye ububanzi, ifomula iboniswa ngefomethi =INDIRECT(“[Uhlu1.xlsx]Ishidi1!$A$1:$A$9”). Ngokwemvelo, esikhundleni sika-List1 kanye ne-Sheet1, ungafaka amagama ebhuku lakho kanye neshidi, ngokulandelana. 

Qaphela! Igama lefayela linikezwe kubakaki abayisikwele. Kulokhu, i-Excel ngeke ikwazi ukusebenzisa ifayela elisavaliwe njengamanje njengomthombo wolwazi.

Kufanele futhi kuqashelwe ukuthi igama lefayela ngokwalo linengqondo kuphela uma idokhumenti edingekayo itholakala kufolda efanayo naleyo lapho uhlu luzofakwa khona. Uma kungenjalo, kufanele ucacise ikheli lalo mbhalo ngokugcwele.

Ukudala Okwehliswayo Okuncike

Uhlu oluncikile yilolo okuqukethwe kwalo kuthintwa ukukhetha komsebenzisi kolunye uhlu. Ake sithi sinetafula elivuliwe phambi kwethu eliqukethe ububanzi obuthathu, ngalinye linikezwe igama.

Udinga ukulandela lezi zinyathelo ukuze ukhiqize uhlu umphumela walo uthintwa inketho ekhethwe kolunye uhlu.

1. Dala uhlu lokuqala olunamagama obubanzi.
   

   

2. Endaweni yokungena komthombo, izinkomba ezidingekayo zigqanyiswa ngayinye ngayinye.
   

   

3. Dala uhlu lwesibili kuye ngohlobo lwesitshalo umuntu asikhethile. Ngaphandle kwalokho, uma ucacisa izihlahla ohlwini lokuqala, khona-ke ulwazi ohlwini lwesibili luzoba "i-oki, i-hornbeam, i-chestnut" nangale kwalokho. Kuyadingeka ukuthi ubhale phansi ifomula endaweni yokufaka umthombo wedatha =I-INDIRECT(E3). E3 – iseli eliqukethe igama lobubanzi 1.=INDIRECT(E3). E3 – iseli elinegama lohlu 1.

Manje konke sekumi ngomumo.

Ungawakhetha kanjani amanani amaningi ohlwini lokudonsela phansi?

Ngezinye izikhathi akwenzeki ukunikeza okuthandwayo kunani elilodwa kuphela, ngakho-ke kufanele kukhethwe okungaphezu kweyodwa. Ngemuva kwalokho udinga ukwengeza i-macro kukhodi yekhasi. Ukusebenzisa inhlanganisela yokhiye i-Alt + F11 ivula i-Visual Basic Editor. Futhi ikhodi ifakwe lapho.

I-Private Sub Worksheet_Change (ByVal Target As Range)

    Kuphutha Qalisa kabusha Okulandelayo

    Uma Ingaphambanisi(Ithagethi, Ibanga(«Е2:Е9»)) Ayilutho Futhi Ithagethi.Amaseli.Bala = 1 Bese

        Application.EnableEvents = Amanga

        Uma Len (Target.Offset (0, 1)) = 0 Bese

            I-Target.Offset (0, 1) = Ithagethi

        futhi

            I-Target.End (xlToRight) .Offset (0, 1) = Ithagethi

        Qeda Uma

        Ithagethi.SulaOkuqukethwe

        Application.EnableEvents = Iqiniso

    Qeda Uma

Qeda i-Sub 

Ukuze okuqukethwe kwamaseli kuboniswe ngezansi, sifaka ikhodi elandelayo kusihleli.

I-Private Sub Worksheet_Change (ByVal Target As Range)

    Kuphutha Qalisa kabusha Okulandelayo

    Uma Ingaphambanisi(Ithagethi, Ibanga(«Н2:К2»)) Ayilutho Futhi Ithagethi.Amaseli.Bala = 1 Bese

        Application.EnableEvents = Amanga

        Uma Len (Target.Offset (1, 0)) = 0 Bese

            I-Target.Offset (1, 0) = Ithagethi

        futhi

            Ithagethi.Isiphelo (xlDown) .Offset (1, 0) = Ithagethi

        Qeda Uma

        Ithagethi.SulaOkuqukethwe

        Application.EnableEvents = Iqiniso

    Qeda Uma

Qeda i-Sub

Futhi ekugcineni, le khodi isetshenziselwa ukubhala kuseli eyodwa.

I-Private Sub Worksheet_Change (ByVal Target As Range)

    Kuphutha Qalisa kabusha Okulandelayo

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

        Application.EnableEvents = Amanga

        newVal = Ithagethi

        Isicelo.Hlehlisa

        oldval = Ithagethi

        Uma Len (oldval) <> 0 Futhi oldval <> newVal Bese

            Ithagethi = Ithagethi & «,» & newVal

        futhi

            Okuhlosiwe = okushaVal

        Qeda Uma

        Uma Len (newVal) = 0 Bese Target.ClearContents

        Application.EnableEvents = Iqiniso

    Qeda Uma

Qeda i-Sub

Ububanzi buyahleleka.

Indlela yokwenza uhlu lokudonsela phansi ngosesho?

Kulokhu, kufanele uqale usebenzise uhlobo oluhlukile lohlu. Ithebhu ethi "Unjiniyela" iyavula, ngemuva kwalokho udinga ukuchofoza noma ukuthepha (uma isikrini sithintwa) entweni ethi "Faka" - "ActiveX". Inebhokisi le-combo. Uzocelwa ukuthi udwebe lolu hlu, ngemva kwalokho luzongezwa kudokhumenti.

Ngaphezu kwalokho, ilungiswa ngezakhiwo, lapho ububanzi bucaciswe kunketho ye-ListFillRange. Iseli lapho inani elichazwe umsebenzisi liboniswa khona lilungiswa kusetshenziswa inketho ye-LinkedCell. Okulandelayo, udinga nje ukubhala phansi izinhlamvu zokuqala, njengoba uhlelo luzophakamisa ngokuzenzakalelayo amanani angenzeka.

Uhlu olwehliswayo olunokushintshwa kwedatha okuzenzakalelayo

Kuphinde kube nomsebenzi lapho idatha ishintshwa khona ngokuzenzakalelayo ngemva kokuba yengezwe ebangeni. Kulula ukwenza lokhu:

1. Dala isethi yamaseli ohlu oluzayo. Esimweni sethu, lena iqoqo lemibala. Siyayikhetha.
   

   

2. Okulandelayo, idinga ukufomethwa njengethebula. Udinga ukuchofoza inkinobho yegama elifanayo bese ukhetha isitayela sethebula.
   

   

   

Okulandelayo, udinga ukuqinisekisa lobu bubanzi ngokucindezela inkinobho ethi "KULUNGILE".

Sikhetha ithebula eliwumphumela futhi silinikeze igama ngenkambu yokufaka ephezu kwekholomu A.

Yilokho kuphela, kukhona itafula, futhi lingasetshenziswa njengesisekelo sohlu lokudonsela phansi, olidingayo:

1. Khetha iseli lapho uhlu lukhona.
2. Vula ibhokisi Lokuqinisekisa Idatha.
   

   

3. Setha uhlobo lwedatha ku- "Uhlu", futhi njengamagugu sinikeza igama letafula ngophawu =.
   

   

   

Konke, ingqamuzana selilungile, futhi amagama emibala abonisiwe kulo, njengoba sasikudinga ekuqaleni. Manje ungakwazi ukwengeza izindawo ezintsha ngokumane uzibhale kuseli etholakala ngaphansi kancane ngemva kweyokugcina.

Lokhu kuyinzuzo yethebula, ukuthi ububanzi bukhuphuka ngokuzenzakalelayo lapho idatha entsha yengezwa. Ngokufanelekile, lena indlela elula kakhulu yokwengeza uhlu.

Ungakopisha kanjani uhlu lokudonsela phansi?

Ukuze ukopishe, kwanele ukusebenzisa inhlanganisela yokhiye u-Ctrl + C no-Ctrl + V. Ngakho uhlu lokudonsela phansi luzokopishwa kanye nokufometha. Ukuze ususe ukufometha, udinga ukusebenzisa ukunamathisela okukhethekile (kwimenyu yokuqukethwe, le nketho ivela ngemva kokukopisha uhlu), lapho inketho ethi "imibandela kumanani" isethiwe.

Khetha wonke amaseli aqukethe uhlu lokudonsela phansi

Ukuze ufeze lo msebenzi, kufanele usebenzise umsebenzi othi "Khetha iqembu lamaseli" eqenjini elithi "Thola futhi Khetha".

Ngemva kwalokho, ibhokisi lengxoxo lizovuleka, lapho kufanele ukhethe khona izinto “Konke” futhi “Lokhu kufana” kumenyu ethi “Ukuqinisekiswa Kwedatha”. Into yokuqala ikhetha zonke izinhlu, futhi eyesibili ikhetha kuphela lezo ezifana nezithize.