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

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

А если текущая строка и столбец подсвечиваются при перемещении активной ячейки по листу? Своеобразный выбор координат такой:

Лучше, чем линейка, да?

Для реализации этого существует несколько способов различной сложности. У каждого метода есть свои плюсы и минусы. Давайте рассмотрим их подробно.

Способ 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открыть окно со списком доступных макросов. Макрос Выбор_Вкл., как нетрудно догадаться, включает в себя выбор координат на текущем листе, а макрос Выбор_Выкл. – выключает его. В этом же окне, нажав кнопку параметры (Параметры) Вы можете назначить этим макросам сочетания клавиш для облегчения запуска.

Преимущества этого метода:

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

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

  • такой выбор работает некорректно, если на листе есть объединенные ячейки – сразу выбираются все строки и столбцы, входящие в объединение
  • если вы случайно нажмете клавишу «Delete», то очистится не только активная ячейка, но и вся выделенная область, т.е. будут удалены данные со всей строки и столбца

Способ 2. Оригинальный. ЯЧЕЙКА + функция условного форматирования

Этот метод, хотя и имеет пару недостатков, мне кажется очень элегантным. Реализовать что-то, используя только встроенные инструменты Excel, минимально вникать в программирование на VBA - это высший пилотаж 😉

Метод основан на использовании функции ЯЧЕЙКА, которая может выдавать много различной информации по данной ячейке – высоту, ширину, номер строки-столбца, формат числа и т.д. Эта функция имеет два аргумента:

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

Хитрость в том, что второй аргумент является необязательным. Если он не указан, то берется текущая активная ячейка.

Второй компонент этого метода — условное форматирование. Эта чрезвычайно полезная функция Excel позволяет автоматически форматировать ячейки, если они соответствуют заданным условиям. Если объединить эти две идеи в одну, то получим следующий алгоритм реализации нашего выбора координат посредством условного форматирования:

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

    =ИЛИ(ЯЧЕЙКА("строка")=СТРОКА(A2),ЯЧКА("столбец")=СТОЛБЕЦ(A2))

    =ИЛИ(ЯЧЕЙКА(«строка»)=СТРОКА(A1),ЯЧКА(«столбец»)=СТОЛБЕЦ(A1))

    Эта формула проверяет, совпадает ли номер столбца каждой ячейки таблицы с номером столбца текущей ячейки. Аналогично и со столбцами. Таким образом, будут заполнены только те ячейки, которые имеют либо номер столбца, либо номер строки, соответствующий текущей ячейке. И это крестообразное выделение координат, которого мы хотим добиться.

  4. Нажмите кнопку Рамки (Формат) и установите цвет заливки.

Все почти готово, но есть один нюанс. Дело в том, что Excel не считает изменение выборки изменением данных на листе. И, как следствие, не вызывает пересчет формул и перекрашивание условного форматирования только при изменении положения активной ячейки. Поэтому давайте добавим в модуль листа простой макрос, который будет это делать. Щелкните правой кнопкой мыши вкладку листа и выберите команду из контекстного меню. Исходный текст (Исходный код).Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этого простого макроса:

Private Sub Worksheet_SelectionChange (Цель ByVal как диапазон) 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") 'адрес рабочей таблицы с таблицей Если Target.Count > 1, то выйти из Sub, если Coord_Selection = False, тогда WorkRange.FormatConditions.Delete Exit Sub End, если Application.ScreenUpdating = False, если Not Intersect(Target, WorkRange) ничего не значит, Set CrossRange = Intersect( WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Добавить тип:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(1).Interior.ColorIndex = 33 Target.FormatConditions .Удалить End If End Sub

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

Способ 4. Красиво. Дополнение FollowCellPointer

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

 

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

  • в Excel 2003 и старше – через меню Сервис – Дополнения – Обзор (Инструменты — Надстройки — Обзор)
  • в Excel 2007 и более поздних версиях, через Файл – Параметры – Дополнения – Перейти – Обзор. (Файл — Параметры Excel — Надстройки — Перейти — Обзор)

  • Что такое макросы, куда вставлять код макроса в Visual Basic

 

Оставьте комментарий