Как сделать подсвечивание в excel?

Подсветка строки или столбца цветом в Excel для активной ячейки

Время от времени бывает удобно иметь возможность выделить (подсветить) активную строку или столбец. Решение в очень простом методе достижения этого эффекта. Мы будем использовать условное форматирование и лишь пару строк кода VBA.

Подсветка цветом строки активной ячейки в Excel

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

Выберите диапазон ячеек B2:K23 так, чтобы ячейка B2 оставалась активной.

Затем выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для форматируемых ячее». В поле «Форматировать значения, для которых следующая формула является истинной:» введите следующую формулу.

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

Запись СТРОКА(B2) = АктивнаяСтрока означает то, что ячейка выбранного диапазона будет отформатирована, если номер строки этой ячейки равен значению, хранящемуся в созданном имени диапазона АктивнаяСтрока.

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

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

Запустите редактор VBA (Alt+F11) и для листа (Например, «Пример2»), в котором вы хотите подсвечивать любые строки и где создано условное форматирование , введите код:

Private Sub Worksheet_SelectionChange( ByVal Target As Range)
ActiveWorkbook.Names( «АктивнаяСтрока» ).RefersTo = «=» & ActiveCell.Row
End Sub

Если вы бы хотели, чтобы строки подсвечивались цветом только при перемещении в пределах определенного диапазона, вы можете немного изменить код, который мог бы выглядеть так (для нашего Примера 1):

Private Sub Worksheet_SelectionChange( ByVal Target As Range)
If Not Intersect(Target, Range( «B2:K23» )) Is Nothing Then
ActiveWorkbook.Names( «АктивнаяСтрока» ).RefersTo = «=» & ActiveCell.Column
End If
End Sub

Окончательный результат выглядит так:

Туда где перемещается курсор автоматически подсвечивается цветом целая строка диапазона в месте нахождения активной ячейки.

Подсветка цветом столбца в Excel

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

  1. В условном форматировании заменить функцию СТРОКА на СТОЛБЕЦ.
  2. Изменить в коде VBA-макроса свойство ActiveCell.Row на ActiveCell.Column.

Примечание. Имя исходного диапазона ячеек «АктивнаяСтрока» можно не изменять и оставить прежним как есть.

Решение изменения 1: Выберите инструмент «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»:

В появившемся окне «Диспетчер правил условного форматирования» выберите правило и нажмите на кнопку «Изменить правило»:

Изменяем функцию, вносим желаемые стили оформления формата, которые доступны нам по кнопке «Формат»:

И нажимаем ОК на всех открытых окнах.

Решение изменения 2: Открываем редактор VBA-кода и изменяем только лишь одно свойство для объекта ActiveCell с Row на Column:

Наслаждаемся готовым желаемым результатом:

Аналогичные действия следует выполнить и для примера 2, если там есть необходимость подсвечивать цветом столбец, а не строку на целом листе Excel.

Подсветка дат и сроков

Простой способ

Выделите диапазон с датами на листе и выберите на вкладке Главная – Условное форматирование – Правила выделения ячеек – Дата ( Home – Conditional Formatting – Highlight Cell Rules – Date Occuring) . В открывшемся окне выберите из выпадающего списка нужный вариант подсветки:

Сложный, но красивый способ

Теперь давайте разберем задачку посложнее и поинтереснее. Предположим, что у нас есть большая таблица поставок каких-либо товаров:

Обратите внимание на дату отгрузки. Если она в прошлом, то товар уже поставлен – можно не волноваться. Если она в будущем – значит мы должны держать вопрос на контроле и не забыть организовать поставку к указанному сроку. И, наконец, если дата отгрузки совпадает с сегодняшней, то надо бросать все дела и заниматься именно этой партией в данный момент (наивысший приоритет).

Для наглядности, можно настроить три правила условного форматирования, чтобы автоматически заливать всю строку с данными по партии в разные цвета в зависимости от даты отгрузки. Для этого выделим всю таблицу (без шапки) и выберем на вкладке Главная – Условное форматирование – Создать правило (Home – Conditional Formatting – Create Rule) . В открывшемся окне зададим последний тип правила Использовать формулу для определения форматируемых ячеек (Use formula to determine which cell to format) и введем в поле следующую формулу:

Читать еще:  Как сделать из документа pdf документ excel?

Эта формула берет последовательно содержимое ячеек E5, E6, E7… из столбца с датой отгрузки и сравнивает эту дату с сегодняшней датой из ячейки C2. Если дата отгрузки раньше, чем сегодняшняя, то отгрузка уже была. Обратите внимание на знаки доллара, используемые для закрепления ссылок. Ссылка на $C$2 должна быть абсолютной – с двумя знаками доллара. Ссылка на первую ячейку столбца с датой отгрузки должна быть с закреплением только столбца, но не строки, т.е. $E5.

После ввода формулы можно задать цвет заливки и шрифта, нажав на кнопку Формат (Format) и применить потом наше правило, нажав на кнопку ОК. Затем повторить всю процедуру для проверки будущих поставок и поставок на текущий день. Для отгруженных партий можно, например, выбрать серый цвет, для будущих заказов – зеленый и для сегодняшних – срочный красный:

Вместо текущей даты можно вставить в ячейку С2 функцию СЕГОДНЯ (TODAY) , которая будет обновлять дату каждый раз при открытии файла, что будет приводить к автоматическому обновлению цветов в таблице.

Если подобная подсветка нужна не всегда, а только на определенное время работы с таблицей, то можно добавить к уже сделанному еще и, своего рода, выключатель. Для этого откройте вкладку Разработчик (Developer) . Если ее не видно, то сначала включите ее через Файл – Параметры – Настроить ленту и нажмите кнопку Вставить (Insert) :

В открывшемся списке инструментов выберите Флажок (Checkbox) из верхнего набора Элементы управления формы и щелкните по тому месту листа, где хотите его разместить. Затем можно задать размеры надписи и поменять ее текст (правой кнопкой мыши – Изменить текст):

Теперь, чтобы использовать флажок для включения-выключения подсветки, нужно связать его с любой ячейкой на листе. Щелкните правой кнопкой мыши по нарисованному флажку и выберите в контекстном меню команду Формат объекта (Format Object) и затем в открывшемся окне задайте любую подходящую ячейку в поле Связь с ячейкой (Cell Link) :

Проверьте как все работает. В связанную ячейку Е2 должно выводиться значение ИСТИНА, когда флажок включен или ЛОЖЬ, когда он выключен.

Теперь осталось добавить одно правило в условное форматирование, чтобы наш флажок включал-выключал подсветку дат. Выделите всю нашу таблицу (кроме шапки) и откройте на вкладке Главная — Условное форматирование — Управление правилами (Home — Conditional Formatting — Manage Rules) . В открывшемся окне должны быть хорошо видны созданные нами ранее правила для подсветки прошлых, будущих и настоящих дат разными цветами:

Жмем кнопку Создать правило (New Rule) , выбираем последний тип правила Использовать формулу для определения форматируемых ячеек (Use formula to determine which cell to format) и вводим в поле следующую формулу:

Формат не задаем и нажимаем ОК. Созданное правило должно добавится к общему списку. Теперь необходимо поднять его на первую строчку стрелками (если оно не еще не там) и включить напротив него справа флажок Остановить если истина (Stop If True) :

Параметр с малопонятным названием Остановить, если истина делает простую вещь: если правило, напротив которого он стоит, выполняется (т.е. наш флаг Подсветка сроков на листе выключен), то Microsoft Excel останавливает дальнейшую обработку правил, т.е. не переходит к следующим правилам в списке условного форматирования и не заливает таблицу. Что и требуется.

Таблица контроля сроков с подсветкой

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

Применений можно придумать уйму. Зависит от конкретной таблицы в Экселе и ее назначения.

Начиная от простого допустив если надо чтоб подсвечивало отрицательные ячейки одним цветом шрифта и /или заливки а положительные другим и заканчивая.. ну не знаю чем угодно.

Сделал дочке таблицу с распорядком дня — уроки, кружки всякие — много всего. Сверху шапка из дней недели первый столбец с разбивкой дн я на часы. И подсвечивает текущий день недели и время суток — чтоб видеть что сейчас. Ну и другие вариации графиковрасписаний/планов с подсветкой дат и событий в зависимости от цели. В работе надо иногда заполнять данные некоторые и к ним в соседних ячейках нужно вводить дополнительные уточнения, чтоб не забывать это сделал правило чтоб делало красной заливкой пустые ячейки дополнительных данных, если заполнена основная ячейка.

Автор, а почему не пользуешься приложениями? Ajoll task попробуй в play markete

А логику включить? Например, вам надо сделать что-то по заказчику «рога и копыта». Записываете в телефон дату и заказчика голосовым набором, и всё. А работаете потом со своей базой на компе.

Если проще — данные для работы остабтся в экселе, а контроль сроков — на телефоне. Удобнее же

Пните меня завтра, я тоже магию покажу. Пока что для затравки

(для редактирования — копируйте себе), всё происходит во вкладке «крафт»

Чукча обещал, чукча сделяль.

У чукчи ссылки рабочие. Можешь по профилю проверить (рейтинг нулевой, прав как у раба, исправляться не думаю — пока что писать не о чем. Разве что немного про unreal engine рассказать могу, или про программирование вообще, но пока что темы для одного поста не нашёл)

Читать еще:  Как сделать числа в процентах в excel?

Это что-то вроде таблицы для расчета крафта для ДнД или чего-то подобного?

правда мне непонятен смысл графы софт/хард/металл и единицы в которых измеряются данные в одноименных листах(правда я особо в формулы не вникал).

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

Спасибо. Полезная статья. Сохронил.

Ты не так уж далёк от истины ))

Ага 1970 сохранений и 561 плюсов.

Эм, а чем не подходят црм различного рода?

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

Кроме того црм просто безумное количество, каждый программно хочет урвать свой кусок пирога. Некоторые не удачные, некоторые чрезмерно сложные. Как выбрать? Откуда узнать? Когда прекратится поддержка? Выбрал простую но потом захотел усложнить, получиться ли?

А Эксель многие многие знают на базовом уровне, к тому же форумулы работают и в Эксель и в опен офисе и похожий на 90% синтаксис в гугл документах. Эти три инструмента покрывают практически весь «рынок» пользователей таблиц. Они знакомы всем менее или более. Сетевой faq и комьюнити просто поражают своими объемами.

Да, согласен, Эксель несколько вычурный метод, но он имеет реально много плюсов, с этим вряд ли можно поспорить.

А приложения googleapps?

Судя по минусам любит у нас народ ездить на самоделках из говна и палок вместо серийного авто.

Может кто подскажет. У меня есть 12 таблиц на 1 листе (на каждый месяц) где есть заказы и их стоимость. Можно ли какой-то формулой посчитать сумму определенных заказов, например по ключевому слову?

Да, даже с нескольких книг можно.

Лучше, конечно, сделать плоскую таблицу, как написал @CuzMich — например, написать макрос, который будет из «удобного вида» делать такую табличку. А после одной ВПР тащить что хошь.

Недавно коллега, умеющий в вба, показал индекс поиск позиций. Я сначала не мог понять, зачем такие сложности,впр же проще и быстрее написать . но со временем чуть получше изучил вопрос. Со смарт таблицами вообще чудеса творит. Попробуйте почитать о этой формуле на досуге.как минимум то, что не надо заморачиваться с переносом данных в первый столбец, уже облегчает жизнь)

Из 12 таблиц сделай одну с дополнительным столбцом «Месяц». Далее используй функцию СУММЕСЛИ. Или сводные таблицы

Если переводить 12 таблиц в 1, теряется наглядность и простота. Наверняка можно написать какой-то запрос, который будет парсить с таблиц по ключевым словам

А про обощи поподробнее можно?))

Excel гораздо удобнее в некоторых вещах.

Работаю с документами, нужно отслеживать срок исполнения той или иной задачи. Да Todolist может отобразить мои задачи, но не может сделать это для некоторого количества сотрудников. Excel может отобразить их за определённый период, исполнено/не исполнено, с разбивкой по сотрудникам, посчитать количество за период и т.д.

Всякие task manager ы так не могут.

Не заметил ошибку)

Привет. Подскажи пожалуйста, если в курсе. Есть исходная таблица Excel в которой более 1000 строк. В этой таблице есть повторяющиеся строки по наименованию, но разные значения количества, цена, стоимость и т.п.

Что я делаю : ставлю фильтр и отфильтровывая каждое наименование вычисляю количество, среднюю цену, сумму и все данные заношу в одну строку лишние строки удаляю. И так по всем наименованиям. По времени это ппц, но в конечном итоге,образно, документ из 60 и более листов сокращается до 15 — 20 листов. Можно ли как то этот процесс автоматизировать ? С чего начать ?

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

За сводные таблицы вам уже рассказали.но если у вас много строк ,но для работы с ними сначала нужно таблицу правильно подготовить.

Если в таблице бардак и сводная не создается,можно почитать про нечеткий поиск и плагин для него от самого майкрософта.fuzzylookup

PivotTables решают это на раз-два

макросы для такой ситуации сложновато. сводные таблицы сполна решат проблему.

Если однотипная работа по форматированию и не-аналитическими операциям с данными повторяется еженедельно/ежемесячно, то макрос vs. сводные сокращает количество времени до нуля практически. Да, повозишься на первых порах, может даже месяц утопишь, но потом будешь нажимать 2 кнопки.

не отрицаю. в таких случая макрос реально выручает

расскажите, как перенести данные копированием?

Создать таблицу в access, выбрать и скопировать данные в excel + 1 ряд сверху (названия столбцов).

Перейти в access, щелкнуть правой кнопкой под заголовком «щелкните для добавления» и выбрать пункт «вставить как поля». Соглашаться на все.

Если в excel есть первый столбец — номер строки, то в access можно просто нажать звездочку слева от столбца «код» и вставить Ctrl+V. но тогда первый столбец в excel станет будет соотнесен со столбцом Код в таблице Access.

Читать еще:  Связать таблицы в excel как сделать

Если из excel нужно перенести весь лист, то можно выбрать его (треугольник слева-сверху между номерами столбцов и строк), скопировать (прим. Ctrl+C), нажать на такой же треугольник на таблице Access и вставить (прим. Ctrrl+V).

Обращайтесь, чем смогу.

можете написать мне на polymike@yandex

Access же должен в экспорт/импорт уметь, как минимум в csv

Умеет. Но когда работаю с Excel, мне проще скопировать, так как не всегда нужно делать вычисления по всем данным.

Ну несколько строчек можно и скопировать. Но речь, как я поняла, о большой таблице.

Ну, для меня access ценен именно простотой взаимодействия с excel. Я признал его за инструмент имеющий право на жизнь, когда нужно было оперативно обработать несколько excel таблиц с однотипными параметрами и различающимися значениями — примерно 1300 строк в каждой (таблицы конфигурации оборудования). Не очень большая выборка данных, но так как итоговая (сводная) таблица расхождений в конфигурациях тоже должна была быть в excel — было прям очень удобно, и именно потому что не приходилось импортировать куда-то, а потом экспортировать.

С другой стороны, для более специфических задач — конечный результат которых предполагает вывод в excel — импорт-экспорт, безусловно, нужен.

Но опять же, для различных задач есть свои инструменты. Если таблица более-менее большая (в несколько сотен тысяч строк), то лично мне проще выгрузить ее в нормальную СУБД (хотя access для людей не знакомых с SQL тоже подойдет), а для ускорения — предварительно тестировать SQL запросы на некоторой выборке строк.

Если вычисления сложно представимые SQL запросами, но задача на один раз, то мне проще загнать это считаться Python, если задача предполагает регулярность — в С++.

Но это не отменяет того, что access удобен в своей сфере применения, а часть того что можно было бы сделать в MySQL/PostgreSQL описав таблицы и импортировав данные, тут можно сделать в 2 клика вообще не понимая механизмов по которым оно происходит эти он, по своему, крут: типы полей — автоопределяться, названия — подставятся, типовые функции выдадут результат через мастер запросов. Bingo! полученный результат можно выделить и скопировать обратно в excel для построения красивого графика или выделения записей цветами.

Подсветить ячейку в Excel

Добрый день уважаемый пользователь Microsoft Excel!

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

Статья будет полезна тем, кому важны сроки окончания действий договоров, для их перезаключения, проведения тендеров, тут вообще необходимо жёсткое соответствие срокам, отслеживания дат отгрузок товаров согласно соглашениям, ведение строгой и простой отчётности и т.п… Как видите, сфера применения очень обширна и данная возможность пригодится многим юристам, работникам кадровых служб, экономистам и бухгалтерам.

Давайте рассмотрим на практике два способа применения подсветки с помощью условного форматирования:

  1. С помощью правил выделения ячеек;
  2. С помощью правил условного форматирования и элементов управления форм.

Подсветка дат и сроков с помощью правил выделения ячеек

Это самый простой способ, который возможно осуществить с помощью нескольких кликов. Для начала необходимо выделить нужный диапазон с вашими датами, на панели управления во вкладке «Главная», выбрать выпадающее меню «Условное форматирование», выбрать в списке «Правила выделения ячеек», далее выбрать тип «Дата…».

Следующим шагом вы в диалоговом окне «Дата» в выпадающем списке указываем условия выделения, например, выбрать все даты «На текущей неделе» и выберем для них красный цвет.

Подсветка дат в ячейках с помощью условного форматирования и элементов управления форм

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

Создадим таблицу отгрузки товаров:

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

Для наибольшей наглядности настроим несколько правил для работы условного форматирования, чтобы происходила заливка строки таблицы в определённые цвета в зависимости от указанных дат поставки.

Для начала выделим созданную таблицу и на вкладке «Главная» выбираем в выпадающем списке «Условное форматирование» пункт «Создать правило». В открывшемся диалоговом окне выбираете правило «Использовать формулу для определения форматируемых ячеек» и вводим формулу: =$D5 ИСТИНА.

Формат указывать не надо, так как таблица должна выглядеть обыкновенной, нажимаем кнопочку «ОК» добавляем новое правило в общий список. В случае если новое правило не возглавило список, то необходимо стрелочками его туда пропихнуть и установить флажок в столбике «Остановить если истина».

Указание последнего параметра необходимо для того, чтобы, когда ваше правило выполнялось (выключен флажок «Включить индикатор строк»), Excel автоматически отключал все нижестоящие правила условного форматирования и таблица остается, девственно чиста от заливок, чего, собственно вы и добивались!

Ссылка на основную публикацию
Adblock
detector