Khetho ea Coordinate

U na le sebali se seholo, empa litafole tseo u sebetsang le tsona li kholo le ho feta. 'Me, ha u sheba ka holim'a skrine ho batla boitsebiso bo hlokahalang, kamehla ho na le monyetla oa ho "thella" mahlo a hau moleng o latelang ebe u sheba ka tsela e fosahetseng. Ke bile ke tseba batho bao, bakeng sa liketsahalo tse joalo, kamehla ba bolokang rulara ea lepolanka haufi le bona ho e hokela moleng oa mochine. Theknoloji ea nako e tlang! 

'Me haeba mola le kholomo ea hajoale li totobatsoa ha sele e sebetsang e tsamaea ka mose ho lakane? Khetho ea mofuta o joalo oa ho kopanya:

Ho molemo ho feta 'musi, na?

Ho na le mekhoa e mengata e fapaneng ea ho rarahana ho kenya ts'ebetsong sena. Mokhoa o mong le o mong o na le melemo le mathata. A re ke re li hlahlobe ka botlalo.

Mokhoa oa 1. Ho totobetse. Macro e totobatsang mola le kholomo ea hajoale

Tsela e hlakileng ka ho fetisisa ea ho rarolla bothata ba rona "phatleng" - re hloka macro e tla latela phetoho ea khetho ho lakane ebe o khetha mola le kholomo eohle bakeng sa sele ea hona joale. Hape ke ntho e lakatsehang ho khona ho nolofalletsa le ho tima ts'ebetso ena ha ho hlokahala, e le hore khetho e joalo e se nang sebopeho e se ke ea re thibela ho kena, mohlala, liforomo, empa e sebetsa feela ha re sheba lethathamong ho batla se hlokahalang. tlhahisoleseding. Sena se re tlisa ho li-macros tse tharo (khetha, nolofalletsa, 'me u tima) tse tla hloka ho kenyelletsoa mojuleng oa lakane.

Bula lakane e nang le tafole eo ho eona u batlang ho fumana khetho e joalo ea khokahano. Tobetsa ka ho le letona tabeng ea leqephe ebe u khetha taelo ho tsoa ho menu ea moelelo Mongolo oa mohloli (Mohloli oa Mohloli).Fesetere ea Visual Basic Editor e lokela ho buleha. Kopitsa mongolo ona oa li-macros tsena tse tharo ho eona:

Dim Coord_Selection As Boolean 'Global variable for selection on/off Sub Selection_On()' Macro on selection Coord_Selection = True End Sub Selection_Off() 'Macro off kgetho Coord_Selection = Flse End Sub 'Mokhoa o ka sehloohong o etsang khetho Private Sub Worksheet_SelectionChange(ByVal Target Range) Dim WorkRange Joalo ka Range Haeba Target.Cells.Count > 1 Ebe O Tsoa Sub 'haeba ho khethiloe sele e fetang 1, tsoa Haeba Coord_Selection = False Ebe U Tsoa Sub 'ha khetho e timme, tsoa ho Application.ScreenUpdating = False Set WorkRange = Range ("A6:N300") 'aterese ea sebaka sa ho sebetsa moo khetho e bonahalang

Fetola aterese ea sebaka sa ho sebetsa hore e be sa hau - ke ka har'a sebaka sena moo khetho ea rona e tla sebetsa. Ebe u koala Visual Basic Editor ebe u khutlela Excel.

Tobetsa khaoletso ea keyboard ALT + F8ho bula fensetere e nang le lenane la li-macro tse fumanehang. Macro Khetho_E Buletsoe, joalo ka ha u ka nahana, e kenyelletsa khetho ea khokahano leqepheng la hajoale, le macro Selection_Off – e tima. Ka fensetere e tšoanang, ka ho tobetsa konopo entsprechen (Dikgetho) O ka abela likhutšoane tsa keyboard ho li-macros tsena bakeng sa ho qala habonolo.

Melemo ea mokhoa ona:

  • boiketlo bo lekanyelitsoeng ba ho kenya tshebetsong
  • khetho - ts'ebetso ha e kotsi ebile ha e fetole litaba kapa sebopeho sa lisele tsa lakane ka tsela efe kapa efe, ntho e ngoe le e ngoe e ntse e le joalo.

Mathata a mokhoa ona:

  • khetho e joalo ha e sebetse ka nepo haeba ho na le lisele tse kopantsoeng letlapeng - mela le litšiea tsohle tse kenyellelitsoeng mokhatlong li khethoa hang-hang.
  • haeba ka phoso u tobetsa konopo ea "Delete", joale ha se sele e sebetsang feela e tla hlakoloa, empa sebaka sohle se khethiloeng, ke hore, hlakola data ho tsoa moleng le kholomong.

Mokhoa oa 2. Ea pele. CELL + Conditional Formatting Function

Mokhoa ona, le hoja o na le litšitiso tse 'maloa, ho 'na o bonahala o le motle haholo. Ho kenya ts'ebetsong ho hong ho sebelisa lisebelisoa tsa Excel tse hahelletsoeng feela, ho kenella hanyane lenaneong la VBA ke aerobatics 😉

Mokhoa o itšetlehile ka ho sebelisa mosebetsi oa CELL, o ka fanang ka boitsebiso bo bongata bo fapaneng ka sele e fanoeng - bophahamo, bophara, nomoro ea mohala oa mohala, mokhoa oa nomoro, joalo-joalo. Mosebetsi ona o na le likhang tse peli:

  • lentsoe la khoutu bakeng sa paramethara, joalo ka "kholomo" kapa "mola"
  • aterese ea sele eo re batlang ho fumana boleng ba parameter ena

Leqheka ke hore khang ea bobeli ke ea boikhethelo. Haeba e sa hlalosoa, joale sele e sebetsang ea hona joale e nkuoa.

Karolo ea bobeli ea mokhoa ona ke ho fomata ka maemo. Karolo ena ea bohlokoa haholo ea Excel e u lumella ho fomata lisele ka bo eona haeba li kopana le maemo a boletsoeng. Haeba re kopanya mehopolo ena e 'meli hore e be e le' ngoe, re fumana algorithm e latelang ea ho kenya tšebetsong khetho ea rona ea khokahano ka ho fomata ka maemo:

  1. Re khetha tafole ea rona, ke hore lisele tseo ho tsona khetho ea ho hokahanya e lokelang ho bontšoa nakong e tlang.
  2. Ho Excel 2003 le ho feta, bula menyetla Foromo – Ho Femetha ka Maemo – Foromo (Format — Conditional Formatting — Foromo). Ho Excel 2007 le ho feta - tobetsa tab Home (Lehae)konopo Ho Femetha ka Maemo - Theha Molao (Ho Femata ka Maemo - Theha Molao) ebe u khetha mofuta oa molao Sebelisa foromo ho fumana hore na ke lisele life tse lokelang ho etsoa (Sebelisa foromo)
  3. Kenya foromo ea khetho ea rona ea khokahano:

    =KAPA(SELI(“row”)=ROW(A2),CELL(“kholomo”)=COLUMN(A2))

    =KAPA(SELENG(«mola»)=ROW(A1),SEL(«kholomo»)=COLUMN(A1))

    Foromo ena e hlahloba ho bona hore na nomoro ea mohala ea sele e 'ngoe le e' ngoe e tafoleng e tšoana le ea nomoro ea sele ea hona joale. Ho joalo le ka litšiea. Kahoo, ho tla tlatsoa feela lisele tse nang le kholomo kapa nomoro ea mola e tsamaellanang le sele ea hona joale. Ena ke khetho ea coordinate e bōpehileng joaloka sefapano eo re batlang ho e finyella.

  4. ebe o tobetsa konopo Mookameli (Mofuta) ebe u beha 'mala oa ho tlatsa.

Ntho e 'ngoe le e' ngoe e se e loketse, empa ho na le nuance e le 'ngoe. Taba ke hore Excel ha e nke phetoho khethong e le phetoho ea data e leqepheng. 'Me, ka lebaka leo, ha e qale ho balloa bocha ha liforomo le ho fetola mebala ea sebopeho sa maemo ha feela boemo ba sele e sebetsang bo fetoha. Ka hona, a re kenyelletseng macro e bonolo ho module ea sheet e tla etsa sena. Tobetsa ka ho le letona tabeng ea leqephe ebe u khetha taelo ho tsoa ho menu ea moelelo Mongolo oa mohloli (Mohloli oa Mohloli).Fesetere ea Visual Basic Editor e lokela ho buleha. Kopitsa mongolo ona oa macro ena e bonolo ho eona:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveCell.Calculate End Sub

Joale, ha khetho e fetoha, mokhoa oa ho bala foromo hape ka ts'ebetso e tla qala MOLETSANE ka ho fometa ho nang le maemo le ho phalla mola le kholomo ea hajoale.

Melemo ea mokhoa ona:

  • Ho fometa ka maemo ha ho senye sebopeho sa litafole
  • Khetho ena e sebetsa ka nepo le lisele tse kopaneng.
  • Ha ho na kotsi ea ho hlakola mola le kholomo ea data ka ho tobetsa ka phoso hlakola.
  • Macro e sebelisoa hanyane

Mathata a mokhoa ona:

  • Foromo ea ho fometa ka mabaka e tlameha ho kenngoa ka letsoho.
  • Ha ho na mokhoa o potlakileng oa ho nolofalletsa / ho tima mokhoa o joalo oa ho fometa - o lula o sebetsa ho fihlela molao o hlakotsoe.

Mokhoa oa 3. E nepahetseng. Ho Femetha ka Maemo + Macros

Ho bolela khauta. Re sebelisa mochini oa ho latela khetho ho lakane re sebelisa li-macros ho tsoa ho mokhoa-1 mme re e eketsa ka mokhoa o bolokehileng re sebelisa fomate ea maemo ho tsoa ho mokhoa oa 2.

Bula lakane e nang le tafole eo ho eona u batlang ho fumana khetho e joalo ea khokahano. Tobetsa ka ho le letona tabeng ea leqephe ebe u khetha taelo ho tsoa ho menu ea moelelo Mongolo oa mohloli (Mohloli oa Mohloli).Fesetere ea Visual Basic Editor e lokela ho buleha. Kopitsa mongolo ona oa li-macros tsena tse tharo ho eona:

Dim Coord_Selection As Boolean Sub Selection_On() Coord_Selection = True End Sub Sub Selection_Off() Coord_Selection = False End Sub Private Subworksheet_SelectionChange(ByVal Target As Range) Dim WorkRange As Range, CrossRange As Range Set WorkRange7"300 = N Range 1 'адрес рабочего диапазона с таблицей If Target.Count > 1 Ebe O Tsoa Sub Haeba Coord_Selection = False Ebe WorkRange.FormatConditions.Delete Exit Sub End If Application.ScreenUpdating = False If Not Intersect(Target, Nothing Is Intersect) WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(33).Interior.ColorIndex = XNUMX Target Forma. .Delete End If End Sub

Se ke oa lebala ho fetola aterese ea sebaka se sebetsang ho aterese ea hau ea tafole. Koala Visual Basic Editor 'me u khutlele Excel. Ho sebelisa li-macro tse ekelitsoeng, tobetsa konopo ea keyboard ALT + F8  'me u tsoele pele ka tsela e ts'oanang le mokhoa oa 1. 

Mokhoa oa 4. E ntle. Keketso ea LatelaCellPointer

Excel MVP Jan Karel Pieterse oa Netherlands o fana ka tlatsetso ea mahala webosaeteng ea hae LatelaCellPointer(36Kb), e rarollang bothata bo tšoanang ka ho taka metsu ea litšoantšo e sebelisa macros ho totobatsa mola le kholomo ea hajoale:

 

Tharollo e ntle. Eseng ntle le glitches libakeng, empa ka sebele u lokela ho leka. Khoasolla li-archive, u li lokolle ho disk ebe u kenya tlatsetso:

  • ho Excel 2003 le ho feta - ka menu Tshebeletso – Dikeketso – Kakaretso (Lisebelisoa - Keketso - Batla)
  • ho Excel 2007 le hamorao, ka Faele – Dikgetho – Dikeketso – Eya – Batla (Faele - Likhetho tsa Excel - Keketso - Eya ho - Batla)

  • Li-macros ke eng, moo u ka kenyang khoutu ea macro ho Visual Basic

 

Leave a Reply