Coordinate xaiv

Koj muaj lub monitor loj, tab sis cov ntxhuav koj ua haujlwm nrog tseem loj dua. Thiab, saib ntawm qhov screen hauv kev tshawb nrhiav cov ntaub ntawv tsim nyog, yeej ib txwm muaj txoj hauv kev "nplaim" koj ob lub qhov muag mus rau kab tom ntej thiab saib tsis ncaj ncees lawm. Kuv txawm paub cov neeg uas, rau lub sijhawm zoo li no, ib txwm khaws ib tus ntoo ntoo nyob ze rau lawv kom txuas rau kab ntawm lub monitor. Technology ntawm lub neej yav tom ntej! 

Thiab yog hais tias kab thiab kab tam sim no tau qhia meej thaum lub xov tooj ntawm tes txav mus thoob plaws hauv daim ntawv? Ib hom kev xaiv kev sib koom ua ke zoo li no:

Zoo dua tus kav, txoj cai?

Muaj ntau txoj hauv kev sib txawv ntawm qhov nyuaj los ua qhov no. Txhua txoj kev muaj nws qhov zoo thiab qhov tsis zoo. Cia peb saib lawv hauv kev nthuav dav.

Txoj Kev 1. Pom meej. Macro uas qhia txog kab thiab kab tam sim no

Txoj kev pom tseeb tshaj plaws los daws peb cov teeb meem "ntawm lub hauv pliaj" - peb xav tau ib qho macro uas yuav taug qab qhov kev hloov pauv hauv kev xaiv ntawm daim ntawv thiab xaiv tag nrho cov kab thiab kab rau lub xov tooj ntawm tes tam sim no. Nws kuj tseem tsim nyog kom muaj peev xwm ua kom muaj peev xwm thiab lov tes taw ua haujlwm no yog tias tsim nyog, yog li kev xaiv tus ntoo khaub lig zoo li tsis tiv thaiv peb los ntawm kev nkag mus, piv txwv li, cov qauv, tab sis tsuas yog ua haujlwm thaum peb saib los ntawm cov npe hauv kev tshawb nrhiav qhov tsim nyog. cov ntaub ntawv. Qhov no coj peb mus rau peb macros (xaiv, pab, thiab lov tes taw) uas yuav tsum tau ntxiv rau daim ntawv module.

Qhib ib daim ntawv nrog ib lub rooj uas koj xav tau xws li kev sib koom tes xaiv. Right-click rau ntawm daim ntawv tab thiab xaiv cov lus txib los ntawm cov ntawv qhia zaub mov Cov ntawv nyeem (Source Code).Lub qhov rais Visual Basic Editor yuav tsum qhib. Luam cov ntawv no ntawm peb macros rau hauv nws:

Dim Coord_Selection Raws li Boolean 'Kev hloov pauv thoob ntiaj teb rau kev xaiv rau / tawm Sub Selection_On() 'Macro ntawm kev xaiv Coord_Selection = True End Sub Selection_Off() 'Macro tawm xaiv Coord_Selection = False End Sub 'Cov txheej txheem tseem ceeb uas ua haujlwm xaiv Private Sub Worksheet_SelectionChange (ByV) Range) Dim WorkRange As Range Yog Target.Cells.Count > 1 Ces Exit Sub 'yog tias ntau tshaj 1 cell raug xaiv, tawm Yog Coord_Selection = False Ces Tawm Sub 'yog xaiv tsis raug, tawm Application.ScreenUpdating = False Set WorkRange = Range ("A6:N300") 'chaw nyob ntawm qhov chaw ua haujlwm nyob rau hauv qhov kev xaiv pom

Hloov chaw nyob ntawm qhov chaw ua haujlwm rau koj tus kheej - nws yog nyob rau hauv qhov no uas peb xaiv yuav ua haujlwm. Tom qab ntawd kaw Visual Basic Editor thiab rov qab mus rau Excel.

Nias lub keyboard shortcut ALT + F8qhib lub qhov rais nrog ib daim ntawv teev cov macros muaj. Macro Xaiv_On, raws li koj tuaj yeem twv, suav nrog kev sib koom ua ke ntawm cov ntawv tam sim no, thiab macro Xaiv_Off - tig nws tawm. Nyob rau hauv tib lub qhov rais, nias lub khawm tsis (Kev xaiv) Koj tuaj yeem muab cov keyboard shortcuts rau cov macro no kom yooj yim tso tawm.

Qhov zoo ntawm txoj kev no:

  • txheeb ze yooj yim ntawm kev siv
  • xaiv - kev ua haujlwm tsis muaj teeb meem thiab tsis hloov cov ntsiab lus lossis formatting ntawm daim ntawv cell nyob rau hauv ib txoj kev, txhua yam tseem zoo li nws yog

Cons ntawm txoj kev no:

  • xws li kev xaiv tsis ua haujlwm raug yog tias muaj cov hlwb sib koom ua ke ntawm daim ntawv - tag nrho cov kab thiab kab nrog rau hauv lub koomhaum raug xaiv ib zaug
  • Yog hais tias koj yuam kev nias tus yuam sij Delete, ces tsis tsuas yog lub active cell yuav raug tshem tawm, tab sis tag nrho cov xaiv cheeb tsam, piv txwv li rho tawm cov ntaub ntawv los ntawm tag nrho cov kab thiab kab.

Txoj Kev 2. Thawj. CELL + Conditional Formatting Function

Txoj kev no, txawm hais tias nws muaj ob peb qhov tsis zoo, zoo li kuv zoo nkauj heev. Txhawm rau siv qee yam siv cov cuab yeej hauv Excel nkaus xwb, tsawg kawg nkag mus rau hauv kev ua haujlwm hauv VBA yog aerobatics 😉

Cov txheej txheem yog ua raws li kev siv CELL muaj nuj nqi, uas tuaj yeem muab ntau cov ntaub ntawv sib txawv ntawm ib lub xov tooj ntawm tes - qhov siab, dav, kab-kem tus lej, tus lej hom, thiab lwm yam. Cov haujlwm no muaj ob qhov kev sib cav:

  • ib lo lus code rau parameter, xws li "kem" lossis "kab"
  • qhov chaw nyob ntawm tes uas peb xav kom txiav txim siab tus nqi ntawm qhov parameter no

Qhov ua kom yuam kev yog qhov kev sib cav thib ob yog xaiv tau. Yog hais tias nws tsis tau teev tseg, ces tam sim no active cell raug coj.

Qhov thib ob feem ntawm txoj kev no yog conditional formatting. Qhov no muaj txiaj ntsig zoo heev Excel tso cai rau koj tuaj yeem hloov kho cov hlwb yog tias lawv ua tau raws li qhov xwm txheej. Yog tias peb muab ob lub tswv yim no rau hauv ib qho, peb tau txais cov algorithm hauv qab no rau kev siv peb cov kev xaiv kev sib koom tes los ntawm kev ua kom haum xeeb:

  1. Peb xaiv peb lub rooj, piv txwv li cov hlwb uas qhov kev xaiv xaiv yuav tsum tau muab tso rau yav tom ntej.
  2. Hauv Excel 2003 thiab laus dua, qhib cov ntawv qhia zaub mov Format – Conditional Formatting – Formula (Format — Conditional Formatting — Formula). Hauv Excel 2007 thiab tshiab dua - nyem rau ntawm tab Tsev (Tsev)khawm Conditional Formatting – Tsim Txoj Cai (Conditional Formatting - Tsim Txoj Cai) thiab xaiv txoj cai hom Siv tus qauv los txiav txim seb lub cell twg los tsim (Siv cov qauv)
  3. Nkag mus rau tus qauv rau peb qhov kev xaiv ua haujlwm:

    =OR(CELL("row")=ROW(A2),CELL("column")=COLUMN(A2))

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

    Cov qauv no kuaj xyuas seb kab zauv ntawm txhua lub xov tooj ntawm tes hauv lub rooj puas zoo ib yam li kab zauv ntawm lub xov tooj tam sim no. Ib yam li nrog kab. Yog li, tsuas yog cov hlwb uas muaj tus lej kab lossis kab zauv uas phim rau lub xovtooj tam sim no yuav raug sau rau hauv.

  4. xovxwm moj khaum (Hom ntawv) thiab teem caij sau xim.

Txhua yam yog yuav luag npaj, tab sis muaj ib tug nuance. Qhov tseeb yog tias Excel tsis xav txog kev hloov pauv hauv kev xaiv raws li kev hloov pauv ntawm cov ntaub ntawv ntawm daim ntawv. Thiab, raws li qhov tshwm sim, nws tsis ua rau rov suav cov qauv thiab rov ua cov txheej txheem hloov kho tsuas yog thaum txoj haujlwm ntawm lub cell hloov pauv. Yog li ntawd, cia peb ntxiv ib qho yooj yim macro rau daim ntawv module uas yuav ua qhov no. Right-click rau ntawm daim ntawv tab thiab xaiv cov lus txib los ntawm cov ntawv qhia zaub mov Cov ntawv nyeem (Source Code).Lub qhov rais Visual Basic Editor yuav tsum qhib. Luam cov ntawv no ntawm no yooj yim macro rau hauv nws:

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

Tam sim no, thaum xaiv hloov pauv, cov txheej txheem ntawm kev suav cov qauv nrog cov haujlwm yuav raug tso tawm CELL nyob rau hauv conditional formatting thiab dej nyab cov kab thiab kab tam sim no.

Qhov zoo ntawm txoj kev no:

  • Conditional formatting tsis ua txhaum kev cai rooj formatting
  • Qhov kev xaiv xaiv no ua haujlwm raug nrog cov hlwb sib koom ua ke.
  • Tsis muaj kev pheej hmoo ntawm kev rho tawm tag nrho kab thiab kab ntawm cov ntaub ntawv ntawm kev sib tsoo Rho tawm.
  • Macros siv tsawg kawg nkaus

Cons ntawm txoj kev no:

  • Cov mis rau kev kho kom haum xeeb yuav tsum tau nkag mus manually.
  • Tsis muaj txoj hauv kev sai los pab / lov tes taw xws li formatting - nws yeej ib txwm qhib kom txog thaum txoj cai raug tshem tawm.

Txoj Kev 3. Zoo. Conditional Formatting + Macros

Golden txhais tau tias. Peb siv cov txheej txheem rau kev taug qab cov kev xaiv ntawm daim ntawv uas siv macros los ntawm txoj kev-1 thiab ntxiv kev nyab xeeb tseem ceeb rau nws siv cov kev cai formatting los ntawm txoj kev-2.

Qhib ib daim ntawv nrog ib lub rooj uas koj xav tau xws li kev sib koom tes xaiv. Right-click rau ntawm daim ntawv tab thiab xaiv cov lus txib los ntawm cov ntawv qhia zaub mov Cov ntawv nyeem (Source Code).Lub qhov rais Visual Basic Editor yuav tsum qhib. Luam cov ntawv no ntawm peb macros rau hauv nws:

Dim Coord_Selection As Boolean Sub Selection_On() Coord_Selection = True End Sub Sub Selection_Off() Coord_Selection = False End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRange As Range, CrossRange As Range Set WorkRange"7A" = Range(300) 'адрес рабочего диапазона с таблицей Yog Target.Count > 1 Ces Exit Sub Yog Coord_Selection = False Then WorkRange.FormatConditions.Delete Exit Sub End If Application.ScreenUpdating Is = False If Not IntersectReang(Tar) WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(1).Interior.ColorIndex.Formations = 33. .Delete End Yog End Sub

Tsis txhob hnov ​​​​qab hloov qhov chaw nyob ua haujlwm rau koj lub rooj chaw nyob. Kaw Visual Basic Editor thiab rov qab mus rau Excel. Txhawm rau siv cov macro ntxiv, nias lub keyboard shortcut ALT + F8  thiab ua raws li txoj kev 1. 

Txoj Kev 4. Zoo nkauj. FollowCellPointer add-on

Excel MVP Jan Karel Pieterse los ntawm Netherlands muab pub dawb ntxiv rau ntawm nws lub vev xaib FollowCellPointer(36Kb), uas daws qhov teeb meem tib yam los ntawm kev kos duab kos duab kab siv macros los qhia txog kab thiab kab tam sim no:

 

Zoo tshuaj. Tsis yog tsis muaj glitches hauv qhov chaw, tab sis twv yuav raug hu tsim nyog sim. Download tau lub archive, unpack nws rau disk thiab nruab qhov add-on:

  • hauv Excel 2003 thiab laus dua - los ntawm cov ntawv qhia zaub mov Kev Pabcuam - Ntxiv-ons - Txheej txheem cej luam (Cov cuab yeej - Ntxiv-Ins - Xauj)
  • hauv Excel 2007 thiab tom qab ntawd, dhau los Cov ntaub ntawv – Kev xaiv – Ntxiv-ons – Mus – Xauj (Cov ntaub ntawv - Excel Options - Ntxiv-Ins - Mus rau - Xauj)

  • Dab tsi yog macro, qhov twg los ntxig macro code hauv Visual Basic

 

Sau ntawv cia Ncua