Okuqukethwe
Ukwakhiwa kwenkinga
Ake sithi sinetafula okufanele “sidanse” nalo nsuku zonke:
To whom the table seems small – mentally multiply it twenty times by area, adding a couple more blocks and two dozen large cities.
Umsebenzi uwukukhipha okwesikhashana emigqeni yesikrini namakholomu angadingeki emsebenzini okwamanje, okungukuthi,
- fihla imininingwane ngenyanga, ushiye amakota kuphela
- fihla ingqikithi ngezinyanga nangekota, kusale ingqikithi kuphela ingxenye yonyaka
- fihla amadolobha angadingekile okwamanje (ngisebenza eMoscow - kungani kufanele ngibone iSt. Petersburg?), njll.
Empilweni yangempela, kukhona ulwandle lwezibonelo zamatafula anjalo.
Indlela yoku-1: Ukufihla imigqa namakholomu
Indlela, ngokungagwegwesi, ingeyakudala futhi ayilula neze, kodwa amagama amabili angashiwo ngayo. Noma yimiphi imigqa noma amakholomu akhethwe ngaphambilini eshidini angafihlwa ngokuchofoza kwesokudla ikholomu noma unhlokweni nokukhetha umyalo kumenyu yokuqukethwe. Fihla (Fihla):
Ukuze ubonise ukuhlehla, khetha imigqa / amakholomu aseduze futhi, ngokuchofoza kwesokudla, khetha kumenyu, ngokulandelana, display (Veza).
Inkinga ukuthi kufanele ubhekane nekholomu ngayinye nomugqa ngamunye, okuyinto engalungile.
Indlela 2. Ukuhlanganisa
Uma ukhetha imigqa noma amakholomu amaningi bese ukhetha kumenyu Idatha - Iqembu Nesakhiwo - Iqembu (Idatha — Iqembu Nohlaka — Iqembu), khona-ke zizovalwa kubakaki abayisikwele (ziqoqwe). Ngaphezu kwalokho, amaqembu angafakwa kwelinye kwelinye (kufika kumazinga angu-8 wokuzalela avunyelwe):
Indlela elula neshesha kakhulu ukusebenzisa isinqamuleli sekhibhodi ukuze uqoqe imigqa ekhethwe ngaphambilini noma amakholomu. U-Alt+Shift+Umcibisholo Wesokudla, kanye nokukhipha amaqembu U-Alt+Shift+Umcibisholo WesokunxeleNgokulandelana.
Le ndlela yokufihla idatha engadingekile ilula kakhulu - ungachofoza inkinobho ethi "+"Noma"-“, noma ezinkinobho ezineleveli yokuqoqa izinombolo ekhoneni elingaphezulu kwesokunxele seshidi - khona-ke wonke amaqembu weleveli oyifunayo azogoqwa noma andwe ngesikhathi esisodwa.
Futhi, uma ithebula lakho liqukethe imigqa yesifinyezo noma amakholomu anomsebenzi wokufingqa amaseli angomakhelwane, okungukuthi, ithuba (hhayi iqiniso elingu-100%) le-Excel uzodala wonke amaqembu adingekayo etafuleni ngokunyakaza okukodwa - ngokusebenzisa imenyu Idatha - Iqembu Nesakhiwo - Dala Isakhiwo (Idatha — Iqembu kanye Nohlaka — Dala Uhlaka). Ngeshwa, umsebenzi onjalo usebenza ngokungalindelekile futhi ngezinye izikhathi wenza umbhedo ophelele kumatafula ayinkimbinkimbi. Kodwa ungazama.
Ku-Excel 2007 nangaphezulu, zonke lezi zinjabulo zikuthebhu Idatha (Usuku) eqenjini Ukwakheka (Uhlaka):
Indlela yesi-3. Ukufihla imigqa/amakholomu amakwe nge-macro
Le ndlela mhlawumbe iguquguquka kakhulu. Ake sengeze umugqa ongenalutho kanye nekholomu engenalutho ekuqaleni kweshidi lethu futhi siphawule nganoma yisiphi isithonjana leyo migqa namakholomu esifuna ukuwafihla:
Manje ake sivule i-Visual Basic Editor (I-ALT + F11), faka imojula entsha engenalutho encwadini yethu (imenyu Faka – Imojula) bese ukopisha umbhalo wamamakhro amabili alula lapho:
I-Sub Fihla() Dim cell Njengohlelo Lohlelo Lokusebenza.ScreenUpdating = Amanga 'Khubaza ukubuyekezwa kwesikrini ukuze usheshise Kuseli ngalinye Ku-ActiveSheet.UsedRange.Rows(1).Amaseli 'Phinda phezu kwawo wonke amaseli kumugqa wokuqala Uma iseli.Value = "x " Bese cell cell.EntireRow.Hidden = Iqiniso 'uma kuseli x - fihla umugqa Isicelo Esilandelayo.ScreenUpdating = True End Sub Show() Amakholomu.Hidden = Amanga 'khansela yonke imigqa namakholomu acashile Imigqa.Kufihliwe = Isiphetho Esingamanga Isihloko
Njengoba ungase uqagele, i-macro Fihla izifihla kanye ne-macro Bonisa - Ibonisa emuva imigqa namakholomu. Uma uthanda, ama-macros anganikezwa ama-hotkeys (I-Alt + F8 nenkinobho Amapharamitha), noma dala izinkinobho ngqo eshidini ukuze uziqalise kuthebhu Unjiniyela – Faka – Inkinobho (Unjiniyela — Faka — Inkinobho).
Indlela 4. Ukufihla imigqa/amakholomu anombala othile
Ake sithi kulesi sibonelo esingenhla, thina, ngokuphambene nalokho, sifuna ukufihla ingqikithi, okungukuthi imigqa ensomi nemnyama namakholomu aphuzi neluhlaza. Ngemuva kwalokho i-macro yethu yangaphambilini kuzodingeka iguqulwe kancane ngokungeza, esikhundleni sokubheka ubukhona buka-“x”, isheke lokufanisa umbala wokugcwalisa namaseli esampula akhethwe ngokungahleliwe:
I-Sub HideByColor() Dim cell As Range Application.ScreenUpdating = Amanga Ngeseli Ngalinye Ku-ActiveSheet.UsedRange.Rows(2).Amaseli Uma iseli.Ingaphakathi.Umbala = Ububanzi("F2").Ingaphakathi.Umbala Bese iseli.IkholomuYonke.Ifihliwe. = Iqiniso Uma iseli.Ingaphakathi.Umbala = Ububanzi("K2").Ingaphakathi.Umbala Bese kuba iseli.IkholomuYonke.Kufihliwe = Iqiniso Elilandelayo Kuseli ngalinye Ku-ActiveSheet.UsedRange.Columns(2).Amaseli Uma iseli.Ingaphakathi.Umbala = Ububanzi ("D6").Ingaphakathi.Umbala Bese kuba iseli.Umugqa Wonke.Kufihliwe = Iqiniso Uma iseli.Ingaphakathi.Umbala = Ububanzi("B11").Ingaphakathi.Umbala Bese iseli.Umugqa Wonke.Ofihliwe = Isicelo Esilandelayo Seqiniso.I-ScreenUpdating = Isiphetho Sangempela Nokho, akumelwe sikhohlwe nge-caveat eyodwa: le macro isebenza kuphela uma amaseli ethebula lomthombo egcwaliswe ngombala ngesandla, futhi engasebenzisi ukufometha okunemibandela (lokhu umkhawulo weNdawo Engaphakathi.Impahla yombala). Ngakho-ke, isibonelo, uma ugqamise ngokuzenzakalela wonke amadili kuthebula lakho lapho inombolo ingaphansi kuka-10 usebenzisa ukufometha okunemibandela:
... futhi ufuna ukuwafihla ngokunyakaza okukodwa, khona-ke i-macro yangaphambilini kuzodingeka "iqedwe". Uma une-Excel 2010-2013, ungaphuma usebenzisa esikhundleni sendawo Ingaphakathi impahla I-DisplayFormat.Ingaphakathi, ekhipha umbala weseli, kungakhathaliseki ukuthi imiswe kanjani. Imakhro yokufihla imigqa eluhlaza okwesibhakabhaka ingase ibukeke kanje:
I-Sub HideByConditionalFormattingColor() Dim cell As Range Application.ScreenUpdating = Amanga Ngeseli ngalinye Ku-ActiveSheet.UsedRange.Columns(1).Amaseli Uma iseli.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Ingaphakathi.Umbala Bese iseli .EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub Iseli G2 lithathwa njengesampula ukuze kuqhathaniswe umbala. Ngeshwa impahla I-DisplayFormat ivele ku-Excel kuphela kusukela kunguqulo ka-2010, ngakho-ke uma une-Excel 2007 noma ngaphezulu, kuzodingeka uqhamuke nezinye izindlela.
- Iyini i-macro, lapho ungafaka khona ikhodi ye-macro, indlela yokuyisebenzisa
- Ukuqoqa ngokuzenzakalelayo ohlwini lwamaleveli amaningi