Ukukhetha Ukudidiyela

Unemonitha enkulu, kodwa amathebula osebenza nawo makhulu nakakhulu. Futhi, ubheka esikrinini ufuna ulwazi oludingekayo, kuhlale kunethuba "lokushelela" amehlo akho emgqeni olandelayo futhi ubheke ohlangothini olungalungile. Ngazi ngisho nabantu abathi, ngezikhathi ezinjalo, bahlale begcina irula lezinkuni eduze kwabo ukuze balinamathisele emugqeni wokuqapha. Ubuchwepheshe besikhathi esizayo! 

Futhi uma umugqa wamanje nekholomu kugqanyiswa lapho iseli elisebenzayo lihamba linqamula eshidini? Uhlobo lokukhetha lokuhlanganisa olufana nalokhu:

Kungcono kunerula, akunjalo?

Kunezindlela eziningana zokuhlukahluka kobunzima bokusebenzisa lokhu. Indlela ngayinye inobuhle nebubi bayo. Ake sizibheke ngokuningiliziwe.

Indlela 1. Kusobala. Imakhro egqamisa umugqa nekholomu yamanje

Indlela esobala kakhulu yokuxazulula inkinga yethu "ebunzini" - sidinga i-macro ezolandelela ushintsho ekukhethweni eshidini bese ukhetha wonke umugqa nekholomu yeseli yamanje. Kuyafiseleka futhi ukukwazi ukunika amandla nokukhubaza lo msebenzi uma kunesidingo, ukuze ukukhetha okunjalo okumise okwesiphambano kungasivimbeli ukuthi singene, ngokwesibonelo, amafomula, kodwa kusebenza kuphela lapho sibheka uhlu sibheka okudingekayo. ulwazi. Lokhu kusiletha kumamakhro amathathu (khetha, vumela, futhi vimbela) azodinga ukungezwa kumojula yeshidi.

Vula ishidi elinetafula ofuna ukuthola kulo ukukhetha okunjalo kokudidiyela. Chofoza kwesokudla kuthebhu yeshidi bese ukhetha umyalo kumenyu yokuqukethwe Umbhalo ongumthombo (Ikhodi Yomthombo).Iwindi le-Visual Basic Editor kufanele livuleke. Kopisha lo mbhalo wala makhro amathathu kuwo:

Dim Coord_Selection As Boolean 'Global variable for selection on/off Sub Selection_On() 'Imakhro ekukhethweni Coord_Selection = True End Sub Selection_Off() 'Macro off choice Coord_Selection = Amanga End Sub 'Inqubo eyinhloko eyenza ukukhetha Private Subsheet_SelectionChange(ByVal Target Ububanzi) Fiphalisa Ububanzi Bokusebenza Njengobubanzi Uma I-Target.Cells.Bala > 1 Bese Uphuma Ku-Sub 'uma kukhethwe iseli elingaphezu kwe-1, phuma Uma i-Coord_Selection = Ingelona iqiniso Bese Uphuma Ku-sub 'uma ukukhetha kuvaliwe, phuma ku-Application.ScreenUpdating = Isetha Amanga Ububanzi Bokusebenza = Ububanzi (" A6:N300") 'ikheli lebanga lokusebenza lapho okukhethiwe kubonakala khona

Shintsha ikheli lebanga lokusebenza libe elakho - kungaphakathi kwalolu hlu lapho ukukhetha kwethu kuzosebenza khona. Bese uvala i-Visual Basic Editor bese ubuyela ku-Excel.

Cindezela isinqamuleli sekhibhodi I-ALT + F8ukuvula iwindi ngohlu lwamakhro atholakalayo. Imakhro Ukukhetha_Kuvuliwe, njengoba ungase uqagele, ihlanganisa ukukhetha kokudidiyela eshidini lamanje, kanye ne-macro Ukukhetha_Kuvaliwe – uyayicisha. Efasiteleni elifanayo, ngokuchofoza inkinobho Amapharamitha (Izinketho) Unganikeza izinqamuleli zekhibhodi kulawa makhro ukuze uqalise kalula.

Izinzuzo zale ndlela:

  • ukusebenziseka kalula okuhlobene
  • ukukhetha - ukusebenza akunangozi futhi akuguquli okuqukethwe noma ukufometha kwamaseli eshidi nganoma iyiphi indlela, yonke into ihlala injengoba injalo.

Ububi bale ndlela:

  • ukukhetha okunjalo akusebenzi kahle uma kukhona amaseli ahlanganisiwe eshidini - yonke imigqa namakholomu afakwe enhlanganweni akhethwa kanyekanye.
  • uma ucindezela ukhiye we-Delete ngephutha, ngeke kusulwe kuphela iseli esebenzayo, kodwa yonke indawo ekhethiwe, okungukuthi susa idatha kuwo wonke umugqa nekholomu.

Indlela 2. Yasekuqaleni. I-CELL + Umsebenzi Wokufometha Okunemibandela

Le ndlela, nakuba inezihibe ezimbalwa, kimi ibonakala inhle kakhulu. Ukwenza okuthile usebenzisa kuphela amathuluzi e-Excel akhelwe ngaphakathi, ukungena kancane ezinhlelweni ze-VBA kuyi-aerobatics 😉

Indlela isekelwe ekusebenziseni umsebenzi we-CELL, onganikeza ulwazi oluningi oluhlukene kuseli elinikeziwe - ubude, ububanzi, inombolo yekholomu yomugqa, ifomethi yenombolo, njll. Lo msebenzi unama-agumenti amabili:

  • igama lekhodi lepharamitha, njengokuthi “ikholomu” noma “umugqa”
  • ikheli leseli esifuna ukunquma ngalo inani lale pharamitha

Iqhinga liwukuthi i-agumenti yesibili iyakhethwa. Uma kungacacisiwe, khona-ke iseli elisebenzayo lamanje liyathathwa.

Ingxenye yesibili yale ndlela ukufometha okunemibandela. Lesi sici esiwusizo kakhulu se-Excel sikuvumela ukuthi ufomethe ngokuzenzakalela amaseli uma ehlangabezana nemibandela ethile. Uma sihlanganisa le mibono emibili ibe yinto eyodwa, sithola i-algorithm elandelayo yokusebenzisa ukukhetha kwethu kokudidiyela ngokufometha okunemibandela:

  1. Sikhetha ithebula lethu, okungukuthi lawo maseli lapho ukukhetha kokudidiyela kufanele kuboniswe ngokuzayo.
  2. Ku-Excel 2003 nangaphezulu, vula imenyu Ifomethi – Ukufometha Okunemibandela – Ifomula (Ifomethi — Ukufometha Okunemibandela — Ifomula). Ku-Excel 2007 nakamuva - chofoza ithebhu Ikhaya (Ikhaya)inkinobho Ukufometha Okunemibandela - Dala Umthetho (Ukufometha Okunemibandela — Dala Umthetho) bese ukhetha uhlobo lomthetho Sebenzisa ifomula ukuze unqume ukuthi yimaphi amaseli ozofometha (Sebenzisa ifomula)
  3. Faka ifomula yokukhetha kwethu ukuhlanganisa:

    =NOMA(ISELI(“row”)=UROW(A2),ISELI(“ikholomu”)=COLUMN(A2))

    =NOMA(ISELI(«irowu»)=ROW(A1),ISELI(«ikholomu»)=COLUMN(A1))

    Le fomula ihlola ukuthi ingabe inombolo yekholomu yeseli ngalinye kuthebula iyafana yini nenombolo yekholomu yeseli yamanje. Ngokufanayo namakholomu. Ngakho, lawo maseli kuphela anenombolo yekholomu noma inombolo yomugqa efana neseli yamanje azogcwaliswa. Futhi lokhu ukukhetha kokuxhumanisa okumise okwesiphambano esifuna ukukuzuza.

  4. Chofoza inkinobho Framework (Ifomethi) bese usetha umbala wokugcwalisa.

Konke sekuzolunga, kodwa kune-nuance eyodwa. Iqiniso liwukuthi i-Excel ayibheki ushintsho ekukhethweni njengoshintsho kudatha eseshidini. Futhi, ngenxa yalokho, akukuqalisi ukubalwa kabusha kwamafomula nokufaka umbala kabusha kokufometha okunemibandela kuphela lapho kushintsha indawo yeseli esebenzayo. Ngakho-ke, ake sengeze i-macro elula kumojuli yeshidi ezokwenza lokhu. Chofoza kwesokudla kuthebhu yeshidi bese ukhetha umyalo kumenyu yokuqukethwe Umbhalo ongumthombo (Ikhodi Yomthombo).Iwindi le-Visual Basic Editor kufanele livuleke. Kopisha lo mbhalo wale macro elula kuwo:

I-Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveCell.Bala Ukuphela Isihloko

Manje, lapho okukhethiwe kushintsha, inqubo yokubala kabusha ifomula nomsebenzi izokwethulwa ISELULEKILE ngokufometha okunemibandela futhi ugcwalise umugqa nekholomu yamanje.

Izinzuzo zale ndlela:

  • Ukufometha okunemibandela akukwephuli ukufometha kwethebula ngokwezifiso
  • Le nketho yokukhetha isebenza kahle ngamaseli ahlanganisiwe.
  • Ayikho ingozi yokususa wonke umugqa nekholomu yedatha ngokuchofoza ngephutha Susa.
  • Amamakhro asetshenziswa kancane

Ububi bale ndlela:

  • Ifomula yokufometha okunemibandela kufanele ifakwe mathupha.
  • Ayikho indlela esheshayo yokuvumela/ukukhubaza ukufometha okunjalo - kuhlala kunikwe amandla kuze kube yilapho umthetho ususuliwe.

Indlela 3. Ekahle. Ukufometha Okunemibandela + Amamakhro

Incazelo yegolide. Sisebenzisa indlela yokulandelela okukhethiwe eshidini sisebenzisa ama-macros asuka ku-method-1 futhi sengeza ukugqamisa okuphephile kuwo sisebenzisa ukufometha okunemibandela kusuka ku-2.

Vula ishidi elinetafula ofuna ukuthola kulo ukukhetha okunjalo kokudidiyela. Chofoza kwesokudla kuthebhu yeshidi bese ukhetha umyalo kumenyu yokuqukethwe Umbhalo ongumthombo (Ikhodi Yomthombo).Iwindi le-Visual Basic Editor kufanele livuleke. Kopisha lo mbhalo wala makhro amathathu kuwo:

Fiphalisa I-Coord_Selection Njenge-Boolean Sub Selection_On() Coord_Selection = True End Sub Selection_Off() Coord_Selection = I-False End Sub Private Worksheet_SelectionChange(ByVal Target As Range) Fiphaza Ububanzi Bokusebenza Njengobubanzi, I-CrossRange Njengobubanzi Isethi7 Ububanzi Bokusebenza"300 = N 'адрес рабочего диапазона с таблицей Uma I-Target.Count > 1 Bese Uphuma Ngaphansi Uma I-Coord_Selection = Ingamanga Bese Uba Ububanzi Be-Work.FormatConditions.Susa Ukuphuma Kwesiphetho Uma Isicelo.ScreenUpdating = Amanga Uma Ingaphambanisi(Target, Nothing Is Interset) Ububanzi Bokusebenza, Inyunyana(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Engeza Uhlobo:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(1).Interior.ColorIndex = 33TargetConditionsForma. .Susa Ukuphela Uma Uqeda Ingxenye

Ungakhohlwa ukushintsha ikheli lebanga lokusebenza libe ikheli lakho letafula. Vala i-Visual Basic Editor bese ubuyela ku-Excel. Ukuze usebenzise amamakhro angeziwe, cindezela isinqamuleli sekhibhodi I-ALT + F8  futhi uqhubeke ngendlela efanayo nendlela 1. 

Indlela 4. Enhle. LandelaCellPointer isengezo

I-Excel MVP u-Jan Karel Pieterse wase-Netherlands unikeza isengezo samahhala kuwebhusayithi yakhe LandelaCellPointer(36Kb), esixazulula inkinga efanayo ngokudweba imigqa yemicibisholo eyisithombe kusetshenziswa amamakhro ukugqamisa umugqa wamanje nekholomu:

 

Isixazululo esihle. Hhayi ngaphandle kwamaphutha ezindaweni, kodwa kufanelekile ukuzama. Landa ingobo yomlando, uyikhiphe kudiski bese ufaka isengezo:

  • ku-Excel 2003 nangaphezulu - ngokusebenzisa imenyu Isevisi – Izengezo – Uhlolojikelele (Amathuluzi - Izengezo - Dlulisa amehlo)
  • ku-Excel 2007 futhi kamuva, ngokusebenzisa Ifayela – Izinketho – Izengezo – Hamba – Phequlula (Ifayela — Izinketho ze-Excel — Izengezo — Yiya ku — Phequlula)

  • Ayini ama-macro, lapho ungafaka khona ikhodi enkulu ku-Visual Basic

 

shiya impendulo