Kusankha Kogwirizanitsa

Muli ndi chowunikira chachikulu, koma matebulo omwe mumagwira nawo ntchito ndi okulirapo. Ndipo, kuyang'ana pa chinsalu kufunafuna zofunikira, nthawi zonse pamakhala mwayi "wozembera" maso anu pamzere wotsatira ndikuyang'ana njira yolakwika. Ndikudziwanso anthu omwe, pazochitika zotere, nthawi zonse amasunga wolamulira wamatabwa pafupi ndi iwo kuti amangirire pamzere wowunikira. Tekinoloje zam'tsogolo! 

Ndipo ngati mzere wamakono ndi ndime zikuwonetsedwa pamene selo yogwira imayenda kudutsa pepalalo? Kusankha kophatikizana kotere:

Kuposa wolamulira, sichoncho?

Pali njira zingapo zosinthira zovuta kuti zikwaniritse izi. Njira iliyonse ili ndi ubwino ndi kuipa kwake. Tiyeni tione mwatsatanetsatane.

Njira 1. Zoonekeratu. Macro yomwe ikuwonetsa mzere ndi mzere wapano

Njira yodziwikiratu yothetsera vuto lathu "pamphumi" - timafunikira macro omwe angayang'anire kusintha kwa kusankha pa pepala ndikusankha mzere wonse ndi mzere wa selo lamakono. Ndizofunikanso kuti zitheke ndikuletsa ntchitoyi ngati kuli kofunikira, kotero kuti kusankhidwa kofanana ndi mtanda kusatilepheretse kulowa, mwachitsanzo, mafomu, koma kumangogwira ntchito tikayang'ana mndandandawo kufunafuna zofunikira. zambiri. Izi zimatifikitsa ku macros atatu (sankhani, yambitsani, ndi kuletsa) omwe adzafunika kuwonjezeredwa ku gawo la pepala.

Tsegulani pepala lokhala ndi tebulo momwe mukufuna kupeza masankhidwe ogwirizana. Dinani kumanja pa pepala tabu ndikusankha lamulo kuchokera pamenyu yankhani Mawu oyambira (Source kodi).Zenera la Visual Basic Editor liyenera kutsegulidwa. Lembani mawu awa a macros atatuwa mmenemo:

Dim Coord_Selection Monga Boolean 'Kusintha kwapadziko lonse kwa kusankha pa/kuzimitsa Sub Selection_On() 'Macro pa kusankha Coord_Selection = True End Sub Selection_Off() 'Macro off kusankha Coord_Selection = Mapeto Olakwika Sub 'Njira yaikulu yomwe imasankha Private Sub Worksheet_SelectionChange(ByVal Target Range) Dim WorkRange Monga Range Ngati Target.Cells.Count > 1 Ndiye Tulukani Sub 'ngati cell yopitilira 1 yasankhidwa, tulukani Ngati Coord_Selection = Zabodza Kenako Tulukani Sub 'ngati kusankha kuzimitsa, tulukani Application.ScreenUpdating = False Set WorkRange = Range ("A6:N300") 'adilesi yamitundu yogwirira ntchito yomwe kusankha kumawonekera

Sinthani adilesi yamtundu wogwirira ntchito kukhala yanu - ndi mkati mwamtunduwu pomwe kusankha kwathu kudzagwira ntchito. Kenako tsekani Visual Basic Editor ndikubwerera ku Excel.

Dinani njira yachidule ya kiyibodi ALT + F8kuti mutsegule zenera ndi mndandanda wa macros omwe alipo. Macro Selection_On, monga momwe mungaganizire, imaphatikizapo kusankha kogwirizanitsa pa pepala lamakono, ndi macro Selection_Off - kuzimitsa. Mu zenera lomwelo, mwa kuwonekera batani magawo (Zosankha) Mutha kugawa njira zazifupi za kiyibodi ku ma macros kuti muyambitse mosavuta.

Ubwino wa njirayi:

  • mosavuta kukhazikitsa
  • kusankha - ntchitoyo ndi yopanda vuto ndipo sisintha zomwe zili kapena mawonekedwe amtundu uliwonse, zonse zimakhalabe momwe zilili.

Kuipa kwa njira iyi:

  • kusankha koteroko sikugwira ntchito bwino ngati pali maselo ophatikizidwa papepala - mizere yonse ndi mizati yomwe ili mu mgwirizano imasankhidwa nthawi imodzi.
  • ngati mwangokanikiza batani la Chotsani, ndiye kuti si cell yogwira yokha yomwe idzachotsedwe, koma malo onse osankhidwa, mwachitsanzo, chotsani deta pamzere wonse ndi mzere.

Njira 2. Choyambirira. CELL + Conditional Formatting Function

Njirayi, ngakhale ili ndi zovuta zingapo, zikuwoneka kwa ine zokongola kwambiri. Kuti mugwiritse ntchito china chake pogwiritsa ntchito zida za Excel zokha, kulowa pang'onopang'ono mu VBA ndi aerobatics 😉

Njirayi imachokera ku ntchito ya CELL, yomwe ingapereke zambiri zosiyana pa selo lopatsidwa - kutalika, m'lifupi, nambala ya mzere wa mzere, chiwerengero cha nambala, ndi zina zotero. Ntchitoyi ili ndi zifukwa ziwiri:

  • mawu achinsinsi a parameter, monga "gawo" kapena "mzere"
  • adilesi ya cell yomwe tikufuna kudziwa mtengo wa parameter iyi

Chinyengo ndi chakuti mkangano wachiwiri ndi wosankha. Ngati sichinatchulidwe, ndiye kuti selo lomwe likugwira ntchito likutengedwa.

Chigawo chachiwiri cha njirayi ndi kusanjika kokhazikika. Ntchito yothandiza kwambiri iyi ya Excel imakupatsani mwayi wopanga ma cell ngati akwaniritsa zomwe mwasankha. Ngati tiphatikiza malingaliro awiriwa kukhala amodzi, timapeza ma aligorivimu otsatirawa kuti tigwiritse ntchito masanjidwe athu ogwirizana pogwiritsa ntchito masanjidwe okhazikika:

  1. Timasankha tebulo lathu, mwachitsanzo, maselo omwe kusankha kogwirizanitsa kuyenera kuwonetsedwa m'tsogolomu.
  2. Mu Excel 2003 ndi kupitilira apo, tsegulani menyu Format - Conditional Formating - Fomula (Mapangidwe - Mapangidwe Okhazikika - Fomula). Mu Excel 2007 ndi zatsopano - dinani pa tabu Kunyumba (Kunyumba)batani Mapangidwe Okhazikika - Pangani Lamulo (Mapangidwe Okhazikika - Pangani Lamulo) ndikusankha mtundu wa malamulo Gwiritsani ntchito fomula kuti mudziwe kuti ndi maselo ati omwe angasanjidwe (Gwiritsani ntchito fomula)
  3. Lowetsani fomula ya kusankha kwathu kogwirizanitsa:

    =OR(CELL(“row”)=ROW(A2),CELL(“colum”)=COLUMN(A2))

    =OR(LULO(«mzere»)=ROW(A1),CELL(«gawo»)=COLUMN(A1))

    Fomulayi imayang'ana kuti awone ngati nambala ya gawo lililonse patebulo ili yofanana ndi nambala yagawo la cell yomwe ilipo. Momwemonso ndi mizati. Chifukwa chake, ma cell okhawo omwe ali ndi nambala yazachidutswa kapena nambala ya mzere wofanana ndi cell yomwe ilipo ndi omwe adzadzazidwa. Ndipo iyi ndiye kusankha kolumikizira komwe tikufuna kukwaniritsa.

  4. atolankhani Makhalidwe (Mtundu) ndikuyika mtundu wodzaza.

Chilichonse chatsala pang'ono kukonzekera, koma pali nuance imodzi. Chowonadi ndi chakuti Excel sichiwona kusintha kosankhidwa ngati kusintha kwa data papepala. Ndipo, chifukwa chake, siziyambitsa kuwerengetseranso ma formula ndikusinthanso mitundu yamapangidwe okhazikika pokhapokha malo a cell yogwira asintha. Chifukwa chake, tiyeni tiwonjezere macro osavuta pagawo la pepala lomwe lingachite izi. Dinani kumanja pa pepala tabu ndikusankha lamulo kuchokera pamenyu yankhani Mawu oyambira (Source kodi).Zenera la Visual Basic Editor liyenera kutsegulidwa. Lembani mawu awa a macro osavuta awa:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveCell.Yerekezerani Mapeto a Sub

Tsopano, kusankha kukasintha, njira yowerengeranso fomula ndi ntchitoyo idzayambika Cell m'mapangidwe okhazikika ndikusefukira mzere ndi mzere wapano.

Ubwino wa njirayi:

  • Kupanga kovomerezeka sikuphwanya makonzedwe a tebulo
  • Kusankha kumeneku kumagwira ntchito bwino ndi ma cell ophatikizidwa.
  • Palibe chiwopsezo chochotsa mzere wonse ndi mzere wa data pakudina mwangozi Chotsani.
  • Macros amagwiritsidwa ntchito pang'ono

Kuipa kwa njira iyi:

  • Fomula yamapangidwe okhazikika iyenera kulembedwa pamanja.
  • Palibe njira yachangu yothandizira / kuletsa masanjidwe otere - imayatsidwa nthawi zonse mpaka lamulolo lichotsedwe.

Njira 3. Mulingo woyenera. Mapangidwe Okhazikika + Macros

Amatanthauza golide. Timagwiritsa ntchito njira yotsatirira zomwe zasankhidwa papepala pogwiritsa ntchito macros kuchokera ku njira-1 ndikuwonjezera kuwunikira kotetezeka pogwiritsa ntchito mawonekedwe okhazikika kuchokera ku njira-2.

Tsegulani pepala lokhala ndi tebulo momwe mukufuna kupeza masankhidwe ogwirizana. Dinani kumanja pa pepala tabu ndikusankha lamulo kuchokera pamenyu yankhani Mawu oyambira (Source kodi).Zenera la Visual Basic Editor liyenera kutsegulidwa. Lembani mawu awa a macros atatuwa mmenemo:

Dim Coord_Selection Monga Boolean Sub Selection_On() Coord_Selection = True End Sub Sub Selection_Off() Coord_Selection = False End Sub Private Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRange Monga Range, CrossRange Monga Range Set7 WorkRange300"A1 = N 'адрес рабочего диапазона с таблицей Ngati Target.Count > 1 Ndiye Tulukani Sub Ngati Coord_Selection = False Ndiye WorkRange.FormatConditions.Delete Exit Sub End Ngati Application.ScreenUpdating = Zonama Ngati Sizidutsana(Target, WorkRange = Interset) WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(33).Interior.ColorIndex = XNUMX Target.Format. .Delete End If End Sub

Musaiwale kusintha adilesi yomwe ikugwira ntchito kukhala adilesi yanu ya tebulo. Tsekani Visual Basic Editor ndikubwerera ku Excel. Kuti mugwiritse ntchito macros owonjezera, dinani njira yachidule ya kiyibodi ALT + F8  ndikuchita chimodzimodzi monga njira 1. 

Njira 4. Wokongola. TsatiraniCellPointer yowonjezera

MVP ya Excel Jan Karel Pieterse waku Netherlands amapereka zowonjezera zaulere patsamba lake TsatiraniCellPointer(36Kb), yomwe imathetsa vuto lomwelo pojambula mizere yojambula zithunzi pogwiritsa ntchito macros kuwunikira mzere ndi gawo lomwe lilipo:

 

Yankho labwino. Osati popanda glitches m'malo, koma ndithudi ofunika tiyese. Tsitsani zosungidwazo, zitulutseni ku diski ndikuyika zowonjezera:

  • mu Excel 2003 ndi achikulire - kudzera menyu Utumiki - Zowonjezera - mwachidule (Zida - Zowonjezera - Sakatulani)
  • mu Excel 2007 ndi kenako, kudzera Fayilo - Zosankha - Zowonjezera - Pitani - Sakatulani (Fayilo - Zosankha za Excel - Zowonjezera - Pitani ku - Sakatulani)

  • Kodi macros ndi chiyani, komwe mungayike macro code mu Visual Basic

 

Siyani Mumakonda