Выбар каардынатаў

У вас вялікі манітор, але сталы, з якімі вы працуеце, яшчэ большыя. І, гледзячы па экране ў пошуках патрэбнай інфармацыі, заўсёды ёсць шанец «пераскочыць» на наступны радок і зірнуць не ў той бок. Я нават ведаю людзей, якія для такіх выпадкаў заўсёды трымаюць каля сябе драўляную лінейку, каб прымацаваць яе да радка на маніторы. Тэхналогіі будучыні! 

А калі бягучы радок і слупок вылучаюцца, калі актыўная ячэйка перамяшчаецца па аркушы? Выбар каардынат накшталт гэтага:

Лепш, чым лінейка, праўда?

Для гэтага ёсць некалькі спосабаў рознай складанасці. Кожны метад мае свае плюсы і мінусы. Давайце разгледзім іх дэталёва.

Спосаб 1. Відавочны. Макрас, які вылучае бягучы радок і слупок

Самы відавочны спосаб вырашыць нашу праблему «на лоб» - нам патрэбен макрас, які будзе адсочваць змяненне вылучэння на лісце і вылучаць усю радок і слупок для бягучай ячэйкі. Таксама пажадана мець магчымасць пры неабходнасці ўключаць і адключаць гэтую функцыю, каб такі крыжападобны выбар не перашкаджаў нам ўводзіць, напрыклад, формулы, а працаваў толькі тады, калі мы праглядаем спіс у пошуках патрэбнага інфармацыі. Гэта падводзіць нас да трох макрасаў (выбраць, уключыць і адключыць), якія трэба будзе дадаць у модуль аркуша.

Адкрыйце ліст з табліцай, у якой вы хочаце атрымаць такую ​​каардынату выбару. Пстрыкніце правай кнопкай мышы на ўкладцы ліста і абярыце каманду з кантэкстнага меню Зыходны тэкст (Зыходны код).Павінна адкрыцца акно рэдактара Visual Basic. Скапіруйце ў яго гэты тэкст гэтых трох макрасаў:

Dim Coord_Selection As Boolean 'Глабальная зменная для выбару on/off Sub Selection_On() 'Macro on selection Coord_Selection = True End Sub Selection_Off() 'Macro off selection Coord_Selection = False End Sub 'Асноўная працэдура, якая выконвае выбар Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRange As Range If Target.Cells.Count > 1 Then Exit Sub 'калі выбрана больш за 1 ячэйка, выйсці If Coord_Selection = False Then Exit Sub 'калі выбар выключаны, выйсці Application.ScreenUpdating = False Set WorkRange = Range (" A6:N300") 'адрас працоўнага дыяпазону, у якім бачны выбар

Зменіце адрас працоўнага дыяпазону на свой - менавіта ў гэтым дыяпазоне будзе працаваць наш выбар. Затым зачыніце рэдактар ​​​​Visual Basic і вярніцеся ў Excel.

Націсніце спалучэнне клавіш ALT + F8каб адкрыць акно са спісам даступных макрасаў. Макра Выбар_укл, як можна здагадацца, уключае ў сябе выбар каардынат на бягучым аркушы і макрас Выбар_выключаны – выключае. У гэтым жа акне, націснуўшы кнопку параметры (Параметры) Вы можаце прызначыць гэтым макрасам спалучэнні клавіш для зручнага запуску.

Перавагі гэтага метаду:

  • адносная прастата рэалізацыі
  • выбар – аперацыя бясшкодная і ніякім чынам не змяняе змесціва або фарматаванне вочак ліста, усё застаецца як ёсць

Мінусы гэтага метаду:

  • такое вылучэнне не працуе карэктна, калі на аркушы ёсць аб'яднаныя ячэйкі - усе радкі і слупкі, якія ўваходзяць у аб'яднанне, вылучаюцца адразу
  • калі вы выпадкова націснеце клавішу Delete, то ачысціцца не толькі актыўная вочка, але і ўся вылучаная вобласць, гэта значыць выдаліць дадзеныя з усяго радка і слупка

Спосаб 2. Арыгінальны. CELL + Функцыя ўмоўнага фарматавання

Гэты метад, хоць і мае некалькі недахопаў, здаецца мне вельмі элегантным. Рэалізаваць што-небудзь з дапамогай толькі ўбудаваных інструментаў Excel, мінімальна вывучаць праграмаванне на VBA - вышэйшы пілатаж 😉

Метад заснаваны на выкарыстанні функцыі CELL, якая можа даць шмат рознай інфармацыі аб дадзенай ячэйцы - вышыню, шырыню, нумар радка-слупка, фармат ліку і г.д. Гэтая функцыя мае два аргументу:

  • кодавае слова для параметру, напрыклад, «слупок» або «радок»
  • адрас ячэйкі, для якой мы хочам вызначыць значэнне гэтага параметру

Хітрасць у тым, што другі аргумент неабавязковы. Калі ён не пазначаны, то бярэцца бягучая актыўная ячэйка.

Другая складнік гэтага метаду - ўмоўнае фарматаванне. Гэтая надзвычай карысная функцыя Excel дазваляе аўтаматычна фарматаваць ячэйкі, калі яны адпавядаюць вызначаным умовам. Калі мы аб'яднаем гэтыя дзве ідэі ў адну, мы атрымаем наступны алгарытм для рэалізацыі нашага выбару каардынатаў праз умоўнае фарматаванне:

  1. Выбіраем нашу табліцу, гэта значыць тыя вочкі, у якіх у далейшым павінна адлюстроўвацца вылучэнне каардынат.
  2. У Excel 2003 і старэйшых адкрыйце меню Фармат – Умоўнае фарматаванне – Формула (Фармат — Умоўнае фарматаванне — Формула). У Excel 2007 і навейшых версіях - націсніце на ўкладку Галоўная (Дадому)кнопка Умоўнае фарматаванне - стварыць правіла (Умоўнае фарматаванне — Стварыць правіла) і абярыце тып правіла Выкарыстоўвайце формулу, каб вызначыць, якія вочкі трэба адфарматаваць (Выкарыстайце формулу)
  3. Увядзіце формулу для выбару нашай каардынаты:

    =АБО(ЯЧЭКА(“радок”)=РАДОК(A2),ЯЧЕТА(“слупок”)=СТАЛОБЦ(A2))

    =АБО(ЯЧЭКА(«радок»)=РАДОК(A1),ЯЧЭКА(«слупок»)=КАЛОБКА(A1))

    Гэтая формула правярае, ці супадае нумар слупка кожнай ячэйкі ў табліцы з нумарам слупка бягучай ячэйкі. Гэтак жа і з калонамі. Такім чынам, будуць запоўнены толькі тыя ячэйкі, у якіх ёсць нумар слупка або нумар радка, які адпавядае бягучай ячэйцы. І гэта крыжападобнае вылучэнне каардынат, якога мы хочам дасягнуць.

  4. націсніце кнопку Рамкі (Фармат) і ўсталяваць колер залівання.

Усё практычна гатова, але ёсць адзін нюанс. Справа ў тым, што Excel не разглядае змяненне вылучэння як змяненне дадзеных на лісце. І, як следства, не запускае пераразлік формул і перафарбоўку ўмоўнага фарматавання толькі пры змене пазіцыі актыўнай ячэйкі. Такім чынам, давайце дадамо просты макрас у модуль ліста, які зробіць гэта. Пстрыкніце правай кнопкай мышы на ўкладцы ліста і абярыце каманду з кантэкстнага меню Зыходны тэкст (Зыходны код).Павінна адкрыцца акно рэдактара Visual Basic. Скапіруйце ў яго гэты тэкст гэтага простага макраса:

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

Цяпер, калі выбар зменіцца, будзе запушчаны працэс пераразліку формулы з функцыяй КАЛІ ва ўмоўным фарматаванні і заліць бягучы радок і слупок.

Перавагі гэтага метаду:

  • Умоўнае фарматаванне не парушае карыстацкае фарматаванне табліцы
  • Гэты параметр выбару карэктна працуе з аб'яднанымі ячэйкамі.
  • Няма рызыкі выдалення цэлага радка і слупка дадзеных пры выпадковым націску Выдаляць.
  • Макрасы выкарыстоўваюцца мінімальна

Мінусы гэтага метаду:

  • Формулу для ўмоўнага фарматавання неабходна ўвесці ўручную.
  • Няма хуткага спосабу ўключыць/выключыць такое фарматаванне - яно заўсёды ўключана, пакуль правіла не будзе выдалена.

Спосаб 3. Аптымальны. Умоўнае фарматаванне + макрасы

Залатая сярэдзіна. Мы выкарыстоўваем механізм адсочвання вылучэння на аркушы з дапамогай макрасаў з метаду-1 і дадаем да яго бяспечнае вылучэнне з дапамогай умоўнага фарматавання з метаду-2.

Адкрыйце ліст з табліцай, у якой вы хочаце атрымаць такую ​​каардынату выбару. Пстрыкніце правай кнопкай мышы на ўкладцы ліста і абярыце каманду з кантэкстнага меню Зыходны тэкст (Зыходны код).Павінна адкрыцца акно рэдактара Visual Basic. Скапіруйце ў яго гэты тэкст гэтых трох макрасаў:

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 = Range("A7:N300") 'адрес рабочего диапазона с таблицей If Target.Count > 1 Then Exit Sub If Coord_Selection = False Then WorkRange.FormatConditions.Delete Exit Sub End If Application.ScreenUpdating = False If Not Intersect(Target, WorkRange) Is Nothing Then Set CrossRange = Intersect( WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(1).Interior.ColorIndex = 33 Target.FormatConditions .Delete End If End Sub

Не забудзьцеся змяніць адрас працоўнага дыяпазону на адрас табліцы. Зачыніце рэдактар ​​Visual Basic і вярніцеся ў Excel. Каб выкарыстоўваць дададзеныя макрасы, націсніце спалучэнне клавіш ALT + F8  і дзейнічайце гэтак жа, як у спосабе 1. 

Спосаб 4. Прыгожы. Надбудова FollowCellPointer

MVP Excel Ян Карэл Пітэрс з Нідэрландаў раздае на сваім вэб-сайце бясплатны дадатак FollowCellPointer(36Kb), які вырашае тую ж праблему, малюючы графічныя лініі са стрэлкамі з дапамогай макрасаў, каб вылучыць бягучы радок і слупок:

 

Прыемнае рашэнне. Не без збояў месцамі, але паспрабаваць, безумоўна, варта. Спампуйце архіў, распакуйце яго на дыск і ўсталюйце надбудову:

  • у Excel 2003 і старэй – праз меню Сэрвіс – Дапаўненні – Агляд (Інструменты — Надбудовы — Агляд)
  • у Excel 2007 і больш позняй версіі, праз Файл – Параметры – Дапаўненні – Перайсці – Агляд (Файл — Параметры Excel — Надбудовы — Перайсці — Агляд)

  • Што такое макрасы, куды ўставіць код макраса ў Visual Basic

 

Пакінуць каментар