Как скопировать правило в excel в другую ячейку

Обновлено: 07.01.2025

При попытке скопировать условное форматирование (с условием в виде формулы использующую другие ячейки) одной ячейки, значение формулы для условного форматирования просто копируется, а не изменяется.

Есть два столбца с формулами.
Условное форматирование ячейки второго столбца зависит от значения равного суммам ячеек обеих столбцов в этой строке.

т.е. для ячейки b1 - если а1+b1 меньше 5, тогда ячейка b1 должна окрасится в зеленый цвет. На этом этапе все хорошо.
Далее, я с помощью формата по образцу пытаюсь перенести это форматирование на соседние ячейки.
Формула условного форматирования должна, по идее быть следующая
для b2 - если а2+b2 меньше 5, тогда ячейка b1 должна окрасится в зеленый цвет.
для b3 - если а3+b3 меньше 5, тогда ячейка b1 должна окрасится в зеленый цвет.

На деле, же для всех ячеек формула условного форматирования как была, так и осталась а1+b1.

Вопрос - как скопировать условное форматирование не с абсолютными, а относительными ссылками?

На деле, же для всех ячеек формула условного форматирования как была, так и осталась а1+b1.

Вопрос - как скопировать условное форматирование не с абсолютными, а относительными ссылками?

На деле, же для всех ячеек формула условного форматирования как была, так и осталась а1+b1.

Вопрос - как скопировать условное форматирование не с абсолютными, а относительными ссылками?

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

Основы условного форматирования в Excel

Используя условное форматирование, мы можем:

закрашивать значения цветом
менять шрифт
задавать формат границ

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

Где находится условное форматирование в Эксель?

Как сделать условное форматирование в Excel?

При применении условного форматирования системе необходимо задать две настройки:

Каким ячейкам вы хотите задать формат;
По каким условиям будет присвоен формат.

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

В таблице с данными выделим диапазон, для которого мы хотим применить выделение цветом:

Также, доступны следующие условия:

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

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

Как создать правило

Если пред настроенные условия не подходят, вы можете создавать свои правила. Для настройки проделаем следующие шаги:

Условное форматирование по значению другой ячейки

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

Для создания условия по значению другой ячейки выполним следующие шаги:

На скриншоте ниже цветом выделены данные, в которых курс валюты стал ниже к предыдущему периоду:

Как применить несколько правил условного форматирования к одной ячейке

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

Для применения нескольких условий к одной ячейке выполним следующие действия:

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

Для редактирования присвоенного правила выполните следующие шаги:

Как копировать правило условного форматирования

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

Как удалить условное форматирование

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

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

Скопируйте правила условного форматирования на другой лист / книгу с помощью Format Painter

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

1. Выберите диапазон, условное форматирование которого вы скопируете, и нажмите Главная > Формат Painter.

2. Перейдите к диапазону назначения и перетащите кисть. в этом диапазоне.

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

3. Продолжайте выбирать диапазон назначения и нажмите Главная > Условное форматирование > Управление правилами. Смотрите скриншот:

4. В диалоговом окне «Диспетчер правил условного форматирования» дважды щелкните линейку, которая не работает в целевом диапазоне, чтобы открыть диалоговое окно «Изменить правило форматирования». Смотрите скриншот:

5. А затем в Формат значений, где эта формула истинна поле измените ссылки на ячейки в зависимости от диапазона назначения.

Внимание: В нашем случае мы меняем формулу как = СЧЁТЕСЛИ ($ B $ 23: $ B $ 46, $ B23)> 1 (23 млрд долларов: 46 млрд долларов - второй столбец в целевом диапазоне, а $ B23 - первая ячейка во втором столбце).

6. Нажмите OK > OK кнопки для сохранения изменений.

И тогда вы увидите, что условное форматирование точно скопировано и вставлено в целевой диапазон. Смотрите скриншот:

Ноты: Если диапазон, из которого вы копируете условное форматирование, и диапазон, в который вы вставляете условное форматирование, находятся в разных книгах, перед копированием необходимо открыть обе книги.

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

Если вам нужно скопировать условное форматирование из указанного диапазона и часто вставлять в разные книги, мы рекомендуем вам сохранить указанный диапазон с условным форматированием как запись автотекста Kutools for Excel's Авто Текст Утилита, а затем вы можете скопировать правило условного форматирования, не открывая соответствующую книгу.

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

1. Нажмите Kutools > Вставить > Авто Текст , чтобы активировать панель автотекста.

2. Выберите диапазон с условным форматированием, щелкните значок Добавить кнопка в области автотекста, затем в открывшемся диалоговом окне «Новый автотекст» введите имя для этой новой записи автотекста и укажите группу для его сохранения, а затем щелкните значок Добавить кнопка. Смотрите скриншот:

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

3. Просто щелкните запись «Автотекст» на панели «Автотекст», этот диапазон с условным форматированием будет вставлен в ваш рабочий лист. И тогда вы можете следовать те же шаги, которые мы вводим выше для копирования условного форматирования.

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Пример условного форматирования всей строки на листе Excel в зависимости от содержимого одной ячейки в этой строке.

Условие примера
1. Заливка строки зеленым фоном, если в третьей ячейке (столбец «C») этой строки содержится значение «Зеленый».
2. Заливка строки голубым фоном, если в третьей ячейке (столбец «C») этой строки содержится значение «Голубой».
Решение примера

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

2. Нажимаем кнопку «Условное форматирование» на ленте инструментов «Главная» и выбираем ссылку «Создать правило…»:

3. В окне «Создание правила форматирования» выбираем строку «Использовать формулу для определения форматируемых ячеек»:

4. В поле «Форматировать значения, для которых следующая формула является истинной» вставляем условие =$C1="Зеленый" . Далее, нажав кнопку «Формат…», на вкладке «Заливка» выбираем зеленый цвет и нажимаем кнопку «OK»:

5. После выбора заливки и возврата к форме «Создание правила форматирования» нажимаем кнопку «OK».

6. Повторяем шаги 1-5, только на 4 шаге в поле «Форматировать значения, для которых следующая формула является истинной» вставляем условие =$C1="Голубой" , и на вкладке «Заливка» выбираем голубой цвет:

7. Нажимаем кнопку «Условное форматирование» на ленте инструментов «Главная» и выбираем ссылку «Управление правилами…»:

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

9. Вводим в ячейки столбца «C» наименования цветов и смотрим результаты условного форматирования всей строки:

Форматирование части строки в Excel

Пример условного форматирования части строки на листе Excel в зависимости от содержимого одной или двух ячеек в этой строке.

Условие примера
1. Заливка строки желтым фоном, если в третьей ячейке (столбец «C») этой строки содержится значение «Да».
2. Заливка строки серым фоном, если в четвертой ячейке (столбец «D») этой строки содержится значение «Нет».
3. Заливка строки красным фоном, если в третьей ячейке (столбец «C») этой строки содержится значение «Да», а в четвертой ячейке (столбец «D») – значение «Нет».
4. Заливка применяется к 5 первым ячейкам любой строки.
Решение примера

1. Выделяем первые 5 столбцов, чтобы задать диапазон, к которому будут применяться создаваемые правила условного форматирования:

2. Создаем первое правило: условие – =$C1="Да" , цвет заливки – желтый:

3. Создаем второе правило: условие – =$D1="Нет" , цвет заливки – серый:

4. Создаем третье правило: условие – =И($C1="Да";$D1="Нет") , цвет заливки – красный:

5. Проверяем созданные правила в «Диспетчере правил условного форматирования». Видим, что диапазоны, к которым применяются правила, отобразились верно:

6. Заполняем ячейки столбцов «C» и «D» словами «Да» и «Нет» и смотрим результаты условного форматирования части строки:

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

Читайте также: