tdots

6 лет назад

Перекрестное выделение активной ячейки

Если Вам приходится работать с большими таблицами, то наверняка у Вас часто возникала проблема с тем, чтобы определить, в каком именно столбце шапки и в какой строке первой колонки находится активная ячейка. Адрес ячейки, который мы всегда можем увидеть, в большинстве случаев нам ни о чем не говорит. Было бы здорово, если бы при выделении ячейки выделялся столбец и строка, в которых она расположена. Примерно как на гифке ниже.

В этой статье мы научим Вас создавать такое перекрестное выделение на нужном листе Excel.

Итак, начнём.

У нас имеется файл с большой таблицей, которая начинается с ячейки C5 (значения больше 400 000 выделены в таблице красным цветом с помощью условного форматирования). Шапка и первый столбец закреплены, но определять к какому месяцу, году и товару относится выделенная в середине таблицы ячейка всё равно неудобно.

Создание правила условного форматирования

Добавим в таблицу перекрестное выделение (то есть подсветку строки и столбца активной ячейки). Для этого нам понадобится настроить правило условного форматирования.

Для начала выделите весь диапазон, в котором должно срабатывать перекрестное выделение, начиная с левой верхней ячейки (обязательно обратите внимание на адрес этой ячейки, он нам пригодится).

Далее создайте новое правило условного форматирования:

"Главная" - "Условное форматирование" - "Создать правило"

В открывшемся окне выберите "Использовать формулу для определения форматируемых ячеек"

Теперь самая интересная часть. Нужно ввести формулу, которая будет отвечать за подсветку строки и столбца активной ячейки. Для этого нам понадобится функция ЯЧЕЙКА(). Это крайне полезная функция из категории "Информация", которая умеет возвращать некоторые сведения о ячейках или целом файле. В нашем случае нам понадобится получить сведения о номере строки и столбца активной ячейки. Для этого нужно указать в качестве ЕДИНСТВЕННОГО аргумента функции слово "строка" или "столбец" соответственно.

Чтобы подсветить строку, в которой находится активная ячейка, нужно прописать формулу:

=ЯЧЕЙКА("строка")=СТРОКА(D6)

Обратите внимание на аргумент функции СТРОКА. Это ячейка D6, то есть та ячейка, с которой мы начинали выделение диапазона для применения правила условного форматирования. Если Вы начинаете выделение диапазона с другой ячейки, то должны указать ее. И обязательно без закрепления (знака $), чтобы правило работало на весь диапазон.

Как Вы уже догадались, формула выше сравнивает номер строки активной ячейки и номер строки ячейки из выделенного диапазона и закрашивает ее, если номера совпадают. Условное форматирование работает так, что формулу мы вводим как бы для одной ячейки (той, с которой начинаем выделение диапазона), но вводим ее так, как будто бы собирались копировать ее на весь диапазон (копировать ее будет уже сам Excel).

Если мы хотим, чтобы выделялась вся строка, но активная ячейка не подсвечивалась (как в первой гифке), то нужно добавить условие:

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

В этой формуле функция И объединяет два условия: заливка выполняется, только если строка ячейки совпадает со строкой активной ячейки, а столбец не совпадает (совпадение столбца будет только в самой активной ячейке). Таким образом будет залита вся строка, кроме активной ячейки.

Так как нам нужно выделять и строку, и столбец активной ячейки, сделаем аналогичную формулу для подсветки столбца:

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

Осталось объединить эти формулы в одно условие с помощью функции ИЛИ (не запутайтесь в скобках).

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

Именно эту формулу и нужно вставить в строку формулы правила условного форматирования.

Теперь нам нужно выбрать, как именно будет оформлено "перекрестное" выделение. Для этого нажмите кнопку "Формат..." под строкой ввода формулы и выберите нужное форматирование. Мы выбрали простую заливку голубым цветом. Когда формат будет выбран, в поле "Образец" появится пример оформления ячейки, которая подходит под правило.

Теперь жмите ОК

Настройка автопересчета правила

Вы создали правило, но это еще не все. В Вашей таблице выделилась строка и столбец ячейки левого верхнего угла, но при выделении другой ячейки заливка не перемещается. Это происходит из-за того, что для изменения выделения правило должно пересчитаться. Те, кто любит горячие клавиши, знает, что пересчет активного листа запускается командой Shift+F9. Но не будем же мы всякий раз при выделении другой ячейки кликать эту комбинацию? Конечно, нет. Мы напишем коротенький макрос.

Кликните на ярлыке Вашего листа с таблицей правой кнопкой мыши и выберите команду "Исходный код" или "Просмотреть код"

Перед Вами откроется редактор VBE с окном кода указанного листа. Здесь Вам нужно выбрать в выпадающем списке слева вверху "Worksheet", а в выпадающем списке справа вверху - "SelectionChange".

В окне кода появится следующая конструкция:

Это процедура, которая будет запускать каждый раз, когда на указанном листе будет происходить изменение активной ячейки. А так как нам нужен пересчет листа в этот момент, то мы должны ввести между строками Private Sub и End Sub строчку:

ActiveSheet.Calculate

Должна получиться вот такая конструкция:

Теперь можете закрыть окно редактора VBE на крестик в правом верхнем углу. Поздравляем! Вы создали код обработки события! Сохраните файл на всякий случай (сохранять теперь придется в формате .xlsm или .xlsb, чтобы работали макросы).

Как видите, теперь при изменении активной ячейки изменяется и перекрестное выделение.

Создание выключателя перекрестного выделения

В принципе, с задачей мы справились. Но есть небольшая проблема. Перекрестное выделение не так то просто отключить. Чтобы оно перестало работать, придется удалять правило условного форматирования. А если мы снова захотим его включить? Правило придется заново создавать? Это не выход. Давайте лучше добавим на листа выключатель правила.

Итак, выделите какую-то пустую ячейку в пределах видимости. Перейдите на вкладку "Данные" - "Проверка данных" - "Список" и введите в строку "Источник" слова "Да" и "Нет" через точку с запятой.

Теперь в ячейке появился выпадающий список с пунктами "Да" и "Нет" (можете для удобства добавить к ним условное форматирование, например, "Да" заливать зеленым, а "Нет" - красным цветом).

Мы хотим, чтобы при выборе в ячейке слова "Да" перекрестное выделение работало, а при слове "Нет" - отключалось.

Для этого нужно добавить в наше правило, созданное в предыдущем пункте, функцию И с еще одним условием. Принцип такой:

=И($C$3="Да";"Наше правило")

То есть, правило будет срабатывать только если выполняются все условия, описанные в предыдущем пункте, и если в ячейке $C$3 значение "Да".

Ячейка $C$3 может быть заменена на любую другую, но обязательно должна быть полностью закреплена двумя знаками $

Теперь формула для перекрестного выделения примет вид:

=И($C$3="Да";ИЛИ(И(ЯЧЕЙКА("строка")=СТРОКА(D6);ЯЧЕЙКА("столбец")<>СТОЛБЕЦ(D6));И(ЯЧЕЙКА("строка")<>СТРОКА(D6);ЯЧЕЙКА("столбец")=СТОЛБЕЦ(D6))))

Готово! Теперь перекрестное выделение включается, если в ячейке выбрано "Да" и выключается, если выбрано "Нет"

Управление перекрестным выделением

Напоследок пару нюансов управления перекрестным выделением.

Чтобы перекрестное выделение не перезатирало другие правила условного форматирования, опустите его в списке правил в самый низ. Выберите "Главная" - "Условное форматирование" - "Управление правилами", кликните нужное правило и с помощью стрелки "Вниз" опустите его в конец очереди;

Чтобы расширить/сузить диапазон действия перекрестного выделения, достаточно в диспетчере правил (рисунок выше) изменить диапазон действия в поле "Применяется к:" (но ячейка, указанная в тексте правила (D6 в нашем случае) должна оставаться первой в указываемом диапазоне);
Перекрестное выделение создается только для одного листа. Чтобы создать его на другом листе или в другом файле придется повторить все шаги заново.

Надеемся, подобный прием пригодится Вам в работе. Файл-пример из данной статьи можете скачать вот по этой ссылке.

Поддержать наш проект и его дальнейшее развитие можно вот здесь.

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru