Вибір координат

У вас великий монітор, але столи, за якими ви працюєте, ще більші. І, переглядаючи екран у пошуках потрібної інформації, завжди є шанс «скотнути» очима на наступний рядок і подивитися не туди. Я навіть знаю людей, які для таких випадків завжди тримають біля себе дерев’яну лінійку, щоб прикріпити її до рядка на моніторі. Технології майбутнього! 

А якщо поточний рядок і стовпець підсвічуються, коли активна комірка переміщується по аркушу? Такий вид вибору координат:

Краще, ніж лінійка, правда?

Для цього є кілька способів різної складності. Кожен спосіб має свої плюси і мінуси. Давайте розглянемо їх докладніше.

Спосіб 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 і новіших версіях – натисніть вкладку Головна (Головна)button Умовне форматування – створити правило (Умовне форматування — створити правило) і виберіть тип правила За допомогою формули визначте, які клітинки потрібно форматувати (Використовуйте формулу)
  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

 

залишити коментар