Coordinate Selection

Iwe une hombe yekutarisisa, asi matafura aunoshanda nawo akatokura. Uye, kutarisa pamusoro pechidzitiro mukutsvaga ruzivo rwakakosha, pane nguva dzose mukana we "kutsvedza" maziso ako kumutsara unotevera uye kutarisa nenzira isiri iyo. Ini ndinotoziva vanhu avo, nekuda kwezviitiko zvakadaro, vanogara vachichengeta mutongi wehuni padyo navo kuti vausungirire kumutsara uri pamonitor. Tekinoroji dzenguva yemberi! 

Uye kana mutsara wazvino uye koramu zvakasimbiswa kana sero rinoshanda richifamba nepakati pebepa? Rudzi rwekuronga sarudzo seizvi:

Zviri nani pane mutongi, handiti?

Kune nzira dzakawanda dzekusiyanisa kuoma kuita izvi. Imwe neimwe nzira ine zvayakanakira nezvayakaipira. Ngatizvitarisei zvakadzama.

Nzira 1. Zviri pachena. Macro inoratidzira iyo yazvino mutsara uye koramu

Nzira yakanyatsojeka yekugadzirisa dambudziko redu "pahuma" - tinoda macro iyo inocherechedza kuchinja kwekusarudza pane pepa uye sarudza mutsara wose uye mutsara wesero iripo. Izvo zvinodikanwawo kukwanisa kugonesa uye kudzima basa iri kana zvichidikanwa, kuitira kuti kusarudzwa kwakadai kwakafanana kusatitadzise kupinda, semuenzaniso, mafomula, asi inoshanda chete kana isu tikatarisa kuburikidza nerondedzero mukutsvaga zvinodiwa. ruzivo. Izvi zvinotisvitsa kune matatu macros (sarudza, gonesa, uye dzima) izvo zvinoda kuwedzerwa kune pepa module.

Vhura pepa rine tafura yaunoda kuwana sarudzo yekubatanidza yakadaro. Tinya-kurudyi pane pepa tebhu uye sarudza rairo kubva pane yemukati menyu Kwakabva mavara (Source Code).Iyo Visual Basic Edhinda hwindo rinofanira kuvhurwa. Kopa ichi chinyorwa cheaya matatu macros mukati maro:

Dim Coord_Selection SeBoolean 'Global variable for selection on/off Sub Selection_On()' Macro pakusarudza Coord_Selection = True End Sub Selection_Off() 'Macro off selection Coord_Selection = Nhema End Sub 'Main maitiro anoita sarudzo Private Sub Worksheet_SelectionChange(ByVal Target Range) Dim WorkRange SeRange Kana Target.Cells.Count > 1 Wobuda Sub 'kana sero rinopfuura 1 rasarudzwa, buda Kana Coord_Selection = Nhema Wobva wabuda Sub 'kana sarudzo yakadzimwa, buda Application.ScreenUpdating = False Set WorkRange = Range (" A6:N300") 'kero yenzvimbo yekushanda mukati umo sarudzo inoonekwa

Shandura kero yehuwandu hwekushanda kune yako - iri mukati meiyi mutsara iyo sarudzo yedu ichashanda. Wobva wavhara Visual Basic Mharidzo uye dzokera kuExcel.

Dzvanya nzira yekudimbudzira kiibhodhi ALT + F8kuvhura hwindo rine runyoro rwemacros aripo. Macro Selection_On, sezvaungafungidzira, inosanganisira kurongedza kusarudzwa pane razvino pepa, uye macro Selection_Kudzima – inodzima. Muhwindo rimwe chete, nekudzvanya bhatani Parameters (Sarudzo) Iwe unogona kugovera mapfupi ekhibhodi kune aya macros kuti utange nyore.

Zvakanakira nzira iyi:

  • kureruka kwekuita
  • kusarudzwa - kushanda hakukuvadzi uye hakuchinje zvirimo kana mafomati epepa masero neimwe nzira, zvese zvinoramba sezvazviri.

Cons yenzira iyi:

  • kusarudzwa kwakadaro hakushande nemazvo kana pane masero akabatanidzwa pabepa - mitsetse yese nemakoramu akabatanidzwa mumubatanidzwa anosarudzwa kamwechete.
  • kana ukangodzvanya kiyi yeDelete netsaona, saka kwete chete sero rinoshanda rinobviswa, asi nzvimbo yese yakasarudzwa, kureva kudzima data kubva mumutsara wese nekoramu.

Nzira 2. Yekutanga. CELL + Conditional Formatting Basa

Iyi nzira, kunyangwe iine zvipingamupinyi zvishoma, inoratidzika kwandiri yakanakisa. Kuita chimwe chinhu uchishandisa chete akavakirwa-muExcel maturusi, zvishoma kupinda muchirongwa muVBA is aerobatics 😉

Nzira yacho inobva pakushandisa CELL function, iyo inogona kupa ruzivo rwakasiyana-siyana pane imwe sero yakapiwa - urefu, hupamhi, nhamba yemutsara-mutsara, nhamba yehuwandu, nezvimwewo. Basa iri rine nharo mbiri:

  • izwi rekodhi reparameter, senge "column" kana "row"
  • kero yesero yatinoda kuona kukosha kweiyi parameter

Chinonyengera ndechekuti nharo yechipiri ndeyekusarudza. Kana iyo isina kutaurwa, saka ikozvino inoshanda sero inotorwa.

Chikamu chechipiri cheiyi nzira ndeye conditional formatting. Ichi chinonyanya kukosha cheExcel chimiro chinokutendera kuti ugadzire otomatiki maseru kana akasangana nemamiriro akatarwa. Kana tikabatanidza aya mazano maviri kuita rimwe chete, tinowana inotevera algorithm yekushandisa yedu yekuronga sarudzo kuburikidza nemamiriro ekugadzirisa:

  1. Isu tinosarudza tafura yedu, kureva iwo maseru umo kusarudzwa kunofanirwa kuratidzwa mune ramangwana.
  2. MuExcel 2003 uye vakuru, vhura menyu Format – Conditional Formatting – Formula (Format - Conditional Formatting - Fomula). MuExcel 2007 uye nyowani - tinya pane tebhu Home (Musha)bhatani Conditional Formatting - Gadzira Rule (Conditional Formatting - Gadzira Rule) uye sarudza rudzi rwekutonga Shandisa fomula kuti uone kuti ndeapi maseru ekufomatidza (Shandisa fomula)
  3. Isa iyo fomula yekusarudza kwedu kuronga:

    =OR(SERO(“mutsara”)=ROW(A2),SERE(“colum”)=COLUMN(A2))

    =OR(SERO(«row»)=ROW(A1),SERE(«column»)=COLUMN(A1))

    Iyi fomula inotarisa kuona kana nhamba yekoramu yesero rega rega mutafura yakafanana nenhamba yekoramu yesero razvino. Saizvozvowo nembiru. Saka, maseru aya chete ane nhamba yekoramu kana nhamba yemutsara inofanana nesero riripo anozozadzwa mukati. Uye iyi ndiyo sarudzo yakaumbwa nemuchinjikwa yatinoda kuita.

  4. dzvanya Framework (Format) uye isa ruvara rwekuzadza.

Zvose zvinenge zvakagadzirira, asi pane imwe nuance. Icho chokwadi ndechekuti Excel haifungi nezve shanduko mukusarudzwa seshanduko mune data pane pepa. Uye, semhedzisiro, haikonzere kuverengerwazve kwemafomula uye kudzoreredza kwemamiriro ekugadzirisa chete kana chinzvimbo chesero rinoshanda rachinja. Naizvozvo, ngatiwedzerei macro yakapusa kune sheet module ichaita izvi. Tinya-kurudyi pane pepa tebhu uye sarudza rairo kubva pane yemukati menyu Kwakabva mavara (Source Code).Iyo Visual Basic Edhinda hwindo rinofanira kuvhurwa. Kopa ichi chinyorwa cheiyi macro iri nyore mairi:

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

Ikozvino, kana sarudzo yachinja, maitiro ekudzokorora fomula nebasa achatangwa CHEMA muchimiro chechimiro uye mafashama mutsara wezvino nekoromo.

Zvakanakira nzira iyi:

  • Conditional fomati haiputsi mafomati etafura
  • Iyi sarudzo inoshanda nemazvo nemaseru akabatanidzwa.
  • Hapana njodzi yekudzima mutsara wese uye column yedata pakudzvanya netsaona Kudzima.
  • Macros anoshandiswa zvishoma

Cons yenzira iyi:

  • Iyo fomula yekumisikidza yekumisikidza inofanirwa kuiswa nemaoko.
  • Iko hakuna nzira yekukurumidza yekugonesa / kudzima fomati yakadai - inogara ichigoneswa kusvika mutemo wadzimwa.

Nzira 3. Yakakwana. Conditional Formatting + Macros

Goridhe zvinoreva. Isu tinoshandisa iyo nzira yekutevera kusarudzwa pane pepa tichishandisa macros kubva kunzira-1 uye tinowedzera yakachengeteka kujekesa kwairi tichishandisa conditional fomati kubva panzira-2.

Vhura pepa rine tafura yaunoda kuwana sarudzo yekubatanidza yakadaro. Tinya-kurudyi pane pepa tebhu uye sarudza rairo kubva pane yemukati menyu Kwakabva mavara (Source Code).Iyo Visual Basic Edhinda hwindo rinofanira kuvhurwa. Kopa ichi chinyorwa cheaya matatu macros mukati maro:

Dim Coord_Selection seBoolean Sub Selection_On() Coord_Selection = True End Sub Sub Selection_Off() Coord_Selection = Nhema Kupera Sub Yakavanzika Sub Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRange SeRange, CrossRange SeRange Set7 Basa Range"300 = N 'адрес рабочего диапазона с таблицей Kana Target.Count > 1 Wobva wabuda Sub Kana Coord_Selection = Nhema Zvadaro WorkRange.FormatConditions.Delete Exit Sub End Kana Application.ScreenUpdating = Nhema Kana Isingapindirani(Target, Nothing Is Interset) WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(1).Interior.ColorIndex = 33 Target Forma. .Delete End Kana End Sub

Usakanganwa kushandura kero yekero yekushanda kune kero yako yetafura. Vhara iyo Visual Basic Mharidzo uye dzokera kuExcel. Kuti ushandise macros akawedzerwa, tinya nzira yekudimbudzira keyboard ALT + F8  uye enderera nenzira imwechete seyo nzira 1. 

Nzira 4. Yakanaka. TeveraCellPointer yekuwedzera

Excel MVP Jan Karel Pieterse anobva kuNetherlands anopa yekuwedzera yemahara pawebhusaiti yake TeveraCellPointer(36Kb), iyo inogadzirisa dambudziko rimwe chete nekudhirowa graphic museve mitsetse uchishandisa macros kuratidza yazvino mutsara nekoramu:

 

Nice solution. Kwete pasina glitches munzvimbo, asi zvechokwadi zvakakodzera kuedza. Dhawunirodha iyo archive, ibudise kune diski uye isa iyo yekuwedzera-pa:

  • muExcel 2003 uye vakuru - kuburikidza nemenyu Sevhisi - Mawedzero - Overview (Zvishandiso - Wedzera-Ins - Bhurawuza)
  • muExcel 2007 uye gare gare, kuburikidza Faira - Sarudzo - Mawedzero - Enda - Bhurawuza (Faira - Excel Sarudzo - Wedzera-Ins - Enda ku - Bhurawuza)

  • Chii macros, kupi kwekuisa macro kodhi muVisual Basic

 

Leave a Reply