Избор на координати

Имате голям монитор, но масите, с които работите, са още по-големи. И, гледайки над екрана в търсене на необходимата информация, винаги има шанс да „приплъзнете“ очите си към следващия ред и да погледнете в грешната посока. Даже познавам хора, които за такива поводи винаги държат до себе си дървена линийка, за да я закачат на реда на монитора. Технологии на бъдещето! 

И ако текущият ред и колона са маркирани, когато активната клетка се движи през листа? Един вид избор на координати като този:

По-добре от владетел, нали?

Има няколко начина с различна сложност за изпълнение на това. Всеки метод има своите плюсове и минуси. Нека да ги разгледаме подробно.

Метод 1. Очевидно. Макрос, който подчертава текущия ред и колона

Най-очевидният начин да решим проблема си "на челото" - имаме нужда от макрос, който ще проследи промяната в селекцията на листа и ще избере целия ред и колона за текущата клетка. Също така е желателно да можете да активирате и деактивирате тази функция, ако е необходимо, така че такъв кръстообразен избор да не ни пречи да въвеждаме например формули, а да работи само когато разглеждаме списъка в търсене на необходимите информация. Това ни води до трите макроса (избор, активиране и дезактивиране), които ще трябва да бъдат добавени към модула за листове.

Отворете лист с таблица, в която искате да получите такава координатна селекция. Щракнете с десния бутон върху раздела на листа и изберете командата от контекстното меню Изходен текст (Програмен код).Прозорецът на редактора на Visual Basic трябва да се отвори. Копирайте този текст на тези три макроса в него:

Dim Coord_Selection As Boolean 'Глобална променлива за включване/изключване на избор Sub Selection_On() 'Макро при избор Coord_Selection = True End Sub Selection_Off() 'Макро изключен избор 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за да отворите прозорец със списък на наличните макроси. Макро Selection_On, както може би се досещате, включва избор на координати на текущия лист и макроса Selection_Off – изключва го. В същия прозорец, като щракнете върху бутона параметри (Настроики) Можете да присвоите клавишни комбинации на тези макроси за лесно стартиране.

Предимства на този метод:

  • относителна лекота на изпълнение
  • избор – операцията е безвредна и не променя съдържанието или форматирането на клетките на листа по никакъв начин, всичко остава както е

Минуси на този метод:

  • такава селекция не работи правилно, ако на листа има обединени клетки – всички редове и колони, включени в обединението, се избират наведнъж
  • ако случайно натиснете клавиша 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

Сега, когато изборът се промени, процесът на преизчисляване на формулата с функцията ще бъде стартиран CELL в условно форматиране и наводняване на текущия ред и колона.

Предимства на този метод:

  • Условното форматиране не нарушава персонализираното форматиране на таблицата
  • Тази опция за избор работи правилно с обединени клетки.
  • Няма риск от изтриване на цял ред и колона с данни при случайно щракване Изтрий.
  • Макросите са използвани минимално

Минуси на този метод:

  • Формулата за условно форматиране трябва да се въведе ръчно.
  • Няма бърз начин за активиране/деактивиране на такова форматиране – то винаги е активирано, докато правилото не бъде изтрито.

Метод 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 Jan Karel Pieterse от Холандия раздава безплатна добавка на своя уебсайт FollowCellPointer(36Kb), който решава същия проблем чрез изчертаване на графични линии със стрелки с помощта на макроси за маркиране на текущия ред и колона:

 

Хубаво решение. Не без проблеми на места, но определено си струва да опитате. Изтеглете архива, разопаковайте го на диск и инсталирайте добавката:

  • в Excel 2003 и по-стари – през менюто Услуга – Добавки – Общ преглед (Инструменти — Добавки — Преглед)
  • в Excel 2007 и по-нови, чрез Файл – Опции – Добавки – Отиди – Преглед (Файл — Опции на Excel — Добавки — Отидете на — Преглед)

  • Какво представляват макросите, къде да вмъкнете макро код във Visual Basic

 

Оставете коментар