Как сделать в excel чтобы в формуле не менялась одна ячейка

Обновлено: 21.11.2024

При работе с электронными таблицами необходимо знать об относительных и абсолютных ссылках на ячейки.

Вот проблема: когда вы КОПИРУЕТЕ ФОРМУЛУ, содержащую ссылки на ячейки, что происходит со ссылками на ячейки?

Обычно ССЫЛКИ НА КЛЕТКИ ИЗМЕНЯЮТСЯ! Если скопировать формулу на 2 строки вправо, то ссылки на ячейки в формуле сместятся на 2 ячейки вправо. Если скопировать формулу на 3 строки вниз и на 1 строку влево, то ссылки на ячейки в формуле сместятся на 3 строки вниз и на 1 строку влево. Они называются относительными ссылками на ячейки, поскольку они изменяются относительно того места, куда вы копируете формулу.

Если вы не хотите, чтобы ссылки на ячейки менялись при копировании формулы, сделайте эти ссылки на ячейки абсолютными ссылками на ячейки. Поместите «$» перед буквой столбца, если вы хотите, чтобы она всегда оставалась неизменной. Поместите «$» перед номером строки, если вы хотите, чтобы он всегда оставался неизменным. Например, «$C$3» относится к ячейке C3, а «$C$3» будет работать точно так же, как «C3», если вы скопируете формулу. Примечание: при вводе формул вы можете использовать клавишу F4 сразу после ввода ссылки на ячейку для переключения между различными относительными/абсолютными версиями этого адреса ячейки.

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

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

Относительные и абсолютные ссылки на ячейки

Карин Стилл

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

Относительные ссылки на ячейки

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

=СУММ(B5:B8), как показано ниже, изменяется на =СУММ(C5:C8) при копировании в следующую ячейку.

Абсолютные ссылки на ячейки

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

В приведенном ниже примере при расчете комиссионных для торгового персонала вы не хотите, чтобы ячейка B10 изменялась при копировании формулы вниз. Вы хотите, чтобы и столбец, и строка оставались одинаковыми, чтобы ссылаться на эту точную ячейку. При использовании в формуле $B$10 ни один из них не изменяется при копировании.

Более сложный пример:

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

Проверьте формулу в ячейке E4. Сделав ссылку на первую ячейку $C4, вы предотвратите изменение столбца при копировании, но позволите строке измениться при копировании вниз, чтобы приспособиться к снижению цен на различные товары. Сделав ссылку на последнюю ячейку A$12, вы предотвратите изменение номера строки при копировании вниз, но позволите столбцу измениться и отразить скидку B при копировании. Смущенный? Посмотрите на рисунок ниже и результаты ячеек.

Теперь вы можете подумать, а почему бы просто не использовать 10% и 15% в реальных формулах? Разве это не было бы проще? Да, если вы уверены, что процент скидки никогда не изменится, что крайне маловероятно. Скорее всего, в конечном итоге эти проценты необходимо будет скорректировать. Ссылаясь на ячейки, содержащие 10% и 15%, а не на фактические числа, когда процент изменяется, все, что вам нужно сделать, это изменить процент один раз в ячейке A12 и/или B12 вместо того, чтобы перестраивать все ваши формулы. Excel автоматически обновит цены со скидками, чтобы отразить изменение процента скидки.

Краткий обзор использования абсолютных ссылок на ячейки:

$A1 Разрешает изменение ссылки на строку, но не ссылка на столбец.
A$1 Разрешает изменять ссылку на столбец, но не на строку.
$A$1 Не позволяет изменять ни столбец, ни ссылку на строку.

Существует ярлык для размещения абсолютных ссылок на ячейки в ваших формулах!

При вводе формулы после ввода ссылки на ячейку нажмите клавишу F4. Excel автоматически делает ссылку на ячейку абсолютной! Продолжая нажимать F4, Excel будет циклически перебирать все возможности абсолютной ссылки.Например, в первой формуле абсолютной ссылки на ячейку в этом руководстве, =B4*$B$10, я мог бы ввести =B4*B10, а затем нажать клавишу F4, чтобы изменить B10 на $B$10. Продолжая нажимать F4, вы получите 10 B$, затем B10 и, наконец, B10. Нажатие F4 изменяет только ссылку на ячейку непосредственно слева от точки вставки.

Я надеюсь, что этот учебник сделал эти типы ссылок на ячейки «абсолютно» понятными!

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

Обзор

Microsoft Excel позволяет изменять многие способы отображения данных в ячейке. Например, вы можете указать количество цифр справа от десятичной точки или добавить к ячейке шаблон и рамку. Вы можете получить доступ к большинству этих параметров и изменить их в диалоговом окне «Формат ячеек» (в меню «Формат» нажмите «Ячейки»).

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

Дополнительная информация

В диалоговом окне «Формат ячеек» есть шесть вкладок: «Число», «Выравнивание», «Шрифт», «Граница», «Шаблоны» и «Защита». В следующих разделах описаны настройки, доступные на каждой вкладке.

Вкладка "Число"

Автоматическое форматирование чисел

По умолчанию все ячейки рабочего листа форматируются в общем числовом формате. В общем формате все, что вы вводите в ячейку, обычно остается как есть. Например, если ввести в ячейку 36526 и нажать клавишу ВВОД, содержимое ячейки будет отображаться как 36526. Это связано с тем, что ячейка остается в общем числовом формате. Однако если вы сначала отформатируете ячейку как дату (например, д/д/гггг), а затем введете число 36526, ячейка отобразит 1/1/2000.

Есть и другие ситуации, когда Excel оставляет числовой формат в качестве общего, но содержимое ячейки не отображается точно так, как оно было введено. Например, если у вас есть узкий столбец и вы вводите длинную строку цифр, например 123456789, ячейка вместо этого может отображать что-то вроде 1.2E+08. Если вы проверите числовой формат в этой ситуации, он останется общим.

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

Вообще говоря, Excel применяет автоматическое форматирование чисел всякий раз, когда вы вводите в ячейку следующие типы данных:

  • Валюта
  • Процент
  • Дата
  • Время
  • Дробь
  • Научная

Встроенные числовые форматы

Excel имеет большой набор встроенных числовых форматов, из которых вы можете выбирать. Чтобы использовать один из этих форматов, щелкните любую из категорий в разделе «Общие», а затем выберите нужный параметр для этого формата. При выборе формата из списка Excel автоматически отображает пример вывода в поле «Образец» на вкладке «Число». Например, если вы введете в ячейку 1,23 и выберете число в списке категорий с тремя десятичными знаками, в ячейке отобразится число 1,230.

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

В следующей таблице перечислены все доступные встроенные числовые форматы:

Числовой формат Примечания
Число Параметры включают в себя: количество знаков после запятой, независимо от того, используется ли разделитель тысяч, и формат, который будет использоваться для отрицательных чисел.
Валюта Опции включают в себя: количество знаков после запятой, символ, используемый для валюты, и формат, который будет использоваться для отрицательных чисел. Этот формат используется для общих денежных значений.
Учет Параметры включают в себя: количество знаков после запятой и символ, используемый для валюты. Этот формат выравнивает символы валюты и десятичные точки в столбце данных.
Дата Выберите стиль даты в списке Тип.
Время Выберите стиль времени в списке Тип.
Проценты< /td> Умножает существующее значение ячейки на 100 и отображает результат с символом процента. Если сначала отформатировать ячейку, а затем ввести число, на 100 умножаются только числа от 0 до 1. Единственным вариантом является количество знаков после запятой.
Дробь Выберите стиль дроби в списке Тип.Если вы не форматируете ячейку как дробь перед вводом значения, возможно, вам придется ввести ноль или пробел перед дробной частью. Например, если ячейка имеет формат «Общий» и вы вводите в нее 1/4, Excel обрабатывает это как дату. Чтобы ввести его в виде дроби, введите в ячейке 0 1/4.
Научный Единственный вариант — количество знаков после запятой.
Текст Ячейки, отформатированные как текст, будут обрабатывать все, что введено в ячейку, как текст, включая числа.
Особый Выберите один из следующих вариантов в поле «Тип»: почтовый индекс, почтовый индекс + 4, номер телефона и номер социального страхования.

Пользовательские числовые форматы

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

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

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

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

Эти разделы разделены точкой с запятой.

Если у вас есть только один раздел, все числа (положительные, отрицательные и нулевые) форматируются в этом формате.

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

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

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

Условное форматирование — одна из самых простых, но мощных функций электронных таблиц Excel.

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

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

Использование условного форматирования в Excel (примеры)

В этом руководстве я покажу вам семь замечательных примеров использования условного форматирования в Excel:

1. Быстрое определение дубликатов

Условное форматирование в Excel можно использовать для выявления дубликатов в наборе данных.

Вот как это можно сделать:

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

2. Выделение ячеек со значением больше/меньше числа

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

Вот как это сделать:

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

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

3. Выделение верхних/нижних 10 (или 10%)

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

Аналогичным образом вы также можете быстро определить последние 10 элементов или последние 10 % в наборе данных.

Вот как это сделать:

Это позволит мгновенно выделить первые 10 элементов в выбранном наборе данных.Обратите внимание, что это работает только для ячеек, в которых есть числовое значение.

Кроме того, если в наборе данных менее 10 ячеек, и вы выбираете параметры для выделения первых 10 элементов/нижних 10 элементов, то все ячейки будут выделены.

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

4. Выделение ошибок/пробелов

Если вы работаете с большим количеством числовых данных и расчетов в Excel, вы знаете, как важно выявлять и обрабатывать ячейки с ошибками или пустые. Использование этих ячеек в дальнейших расчетах может привести к ошибочным результатам.

Условное форматирование в Excel может помочь вам быстро определить и выделить ячейки с ошибками или пустые.

Предположим, у нас есть набор данных, как показано ниже:

В этом наборе данных есть пустая ячейка (A4) и ошибки (A5 и A6).

Вот шаги, чтобы выделить ячейки, которые пусты или содержат ошибки:

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

Примечание. Вам не нужно использовать весь диапазон A1:A7 в формуле условного форматирования. Вышеупомянутая формула использует только A1. Когда вы применяете эту формулу ко всему диапазону, Excel проверяет одну ячейку за раз и корректирует ссылку. Например, при проверке A1 используется формула =ИЛИ(ЕПУСТО(A1),ЕОШИБКА(A1)). При проверке ячейки A2 используется формула =ИЛИ(ЕПУСТО(A2),ЕОШИБКА(A2)). Он автоматически настраивает ссылку (поскольку это относительные ссылки) в зависимости от того, какая ячейка анализируется. Таким образом, вам не нужно писать отдельную формулу для каждой ячейки. Excel достаточно умен, чтобы изменить ссылку на ячейку сам по себе 🙂

5. Создание тепловых карт

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

Как показано ниже:

Приведенный выше набор данных имеет значения от 1 до 100. Ячейки выделяются в зависимости от значения в нем. 100 — зеленый цвет, 1 — красный.

Вот шаги по созданию тепловых карт с использованием условного форматирования в Excel.

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

Примечание. Аналогичным образом можно применять наборы панелей данных и значков.

6. Выделить каждую вторую строку/столбец

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

Они называются полосами зебры и могут быть особенно полезны при печати данных.

Теперь есть два способа создать эти линии зебры. Самый быстрый способ — преобразовать ваши табличные данные в таблицу Excel. Он автоматически применял цвет к чередующимся строкам. Подробнее об этом можно прочитать здесь.

Еще один способ – условное форматирование.

Предположим, у вас есть набор данных, как показано ниже:

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

Вот оно! Альтернативные строки в наборе данных будут выделены.

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

  • Выделить чередующиеся четные строки: =ISEVEN(ROW())
  • Выделите дополнительные строки добавления: =ISODD(ROW())
  • Выделить каждую 3-ю строку: =MOD(ROW(),3)=0
7. Поиск и выделение данных с помощью условного форматирования

Это немного расширенное использование условного форматирования. Тогда вы будете выглядеть как рок-звезда Excel.

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

Вот шаги для создания этой функции поиска и выделения:

Вот оно! Теперь, когда вы вводите что-либо в ячейку C2 и нажимаете Enter, все соответствующие ячейки будут выделены.

Как это работает?

Формула, используемая в условном форматировании, оценивает все ячейки в наборе данных. Допустим, вы вводите Японию в ячейку C2. Теперь Excel будет оценивать формулу для каждой ячейки.

Формула вернет значение TRUE для ячейки при соблюдении двух условий:

  • Ячейка C2 не пуста.
  • Содержимое ячейки C2 точно соответствует содержимому ячейки в наборе данных.

Следовательно, все ячейки, содержащие текст Япония, будут выделены.

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

После применения условное форматирование остается в силе, если вы не удалите его вручную. Рекомендуется применять условное форматирование только к тем ячейкам, где оно необходимо.

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

Форматировать всю строку с условным форматированием

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

Видео: формат строки с условным форматированием

Вы можете посмотреть шаги в этом видео, а письменные инструкции находятся под видео. Полная стенограмма также доступна ниже.

Отформатировать ячейки

В этом примере мы будем окрашивать ячейки в синий цвет, если количество единиц в столбце B больше 75. С первого взгляда мы сможем увидеть, в каких строках содержится большое количество единиц.< /p>

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

  • Выберите ячейки, которые нужно отформатировать (в данном примере это A2:D8)
  • На вкладке "Главная" ленты в группе "Стили" нажмите "Условное форматирование".

  • Затем в нижней части списка нажмите «Новое правило». команда

Диалоговое окно «Новое правило форматирования»

Откроется диалоговое окно «Новое правило форматирования», в котором можно ввести сведения о правиле условного форматирования

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

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

    Теперь отображается поле, в котором вы можете ввести формулу:

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

    Любая формула условного форматирования должна быть проверкой, дающей ИСТИННЫЙ или ЛОЖНЫЙ результат.

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

    • Количество единиц в столбце B больше 75?
      • ИСТИНА или ЛОЖЬ

      Справочник по строкам и столбцам

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

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

      • В этом примере выбраны ячейки A2:D8
      • Ячейка A2 является активной.
      • Формула должна проверять значение в ячейке B2 – активной строке ячейки.
      • Для активной ячейки (A2) будет работать формула =B2>75
        • .
      • Формуле потребуется правильный тип ссылки на ячейку B2.

      Относительная или абсолютная ссылка

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

      • Абсолютный: $B$2 – и столбец, и строка заблокированы – все ячейки будут проверять значение в ячейке B2 листа.
      • Относительно: B2 -- ни столбец, ни строка не заблокированы -- все ячейки будут проверять значение в своей строке, в столбце справа от них.
      • Смешанный вариант: B$2 — столбец не заблокирован, строка заблокирована — все ячейки будут проверять значение в ячейке строки 2, в столбце справа от них
      • Смешанный: $B2 — столбец заблокирован, строка не заблокирована — все ячейки будут проверять значение в своей строке, в столбце B

      Введите формулу со смешанной ссылкой

      Для этой формулы нам нужно использовать последний вариант — смешанную ссылку — $B2. С этой ссылкой условное форматирование будет работать правильно в каждой выбранной ячейке, начиная с A2:D8

      • В поле правила введите следующую формулу:
        • =$B2>75
        • ссылка на столбец заблокирована ($B), ссылка на строку не заблокирована (2)
        • все ячейки будут проверять значение в своей строке в столбце B

        Добавить форматирование

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

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

        Выполните следующие действия, чтобы установить форматирование:

        • В диалоговом окне "Новое правило форматирования" нажмите кнопку "Формат".
        • В диалоговом окне «Формат ячеек» выберите нужные параметры форматирования.
          • В этом примере был выбран голубой цвет заливки.
          • Вы также можете выбрать форматирование "Шрифт", "Граница" и "Число".

          Проверьте настройки правила

          В Диспетчере правил условного форматирования отображаются новые настройки правила

          • Проверьте формулу и предварительный просмотр форматирования.
          • Если все выглядит правильно, нажмите кнопку "ОК", чтобы применить изменение правила.

          На листе применяется новое правило условного форматирования.

          • Строки с числом больше 75 в столбце "Единицы" выделяются синим цветом заливки.

          Абсолютная ссылка и относительная ссылка

          В формуле важно использовать абсолютную ссылку на столбец B, поставив $ перед буквой столбца -- $B

          Неправильное выделение

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

          • Вместо ссылки на столбец B каждый столбец будет ссылаться на столбец справа от него.
          • Это приведет к неправильному выделению с условным форматированием, как показано ниже
            • Ячейка C2 имеет синюю заливку, поскольку ячейка справа больше 75.
            • Ячейки B4 и B5 заполнены синим цветом, так как ячейки C4 и C5 больше 75.

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

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

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

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

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

            • Выберите ячейки, к которым было применено исходное правило условного форматирования (в этом примере A2:D8).
            • На ленте Excel откройте вкладку "Главная".
            • В группе «Стили» нажмите команду «Условное форматирование».
            • Далее в нижней части списка параметров нажмите "Управление правилами".

            Диспетчер правил условного форматирования

            Откроется Диспетчер правил условного форматирования со списком правил для выбранных ячеек (Текущий выбор).

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

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

            • Выберите правило в списке и нажмите кнопку "Изменить правило".
            • В разделе "Выберите тип правила" текущий тип правила выделен
              • На снимке экрана ниже тип правила – "Использовать формулу".
              • На снимке экрана ниже правило использует формулу: =$B2>75

              Изменить настройки правила

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

              • из старого правила с номером: =$B2>75
              • к новой формуле со ссылкой на ячейку: =$B2>$F$1

              Сначала выполните следующие действия, чтобы изменить формулу:

              • В поле формулы удалите число 75.
              • При необходимости переместите Диспетчер правил условного форматирования, чтобы увидеть ячейку F1, где хранится число
              • Нажмите на ячейку F1, чтобы добавить ссылку на эту ячейку в формулу

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

              • Нажмите кнопку "Формат".
              • В диалоговом окне "Формат ячеек" перейдите на вкладку "Заливка".
              • Нажмите на светло-зеленый цвет.
              • Нажмите "ОК", чтобы закрыть диалоговое окно "Формат ячеек".

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

              • Нажмите кнопку "ОК", чтобы применить изменение правила.

              На листе применяется новое правило условного форматирования.

              • Строки, в которых количество единиц больше, чем значение в ячейке F1, имеют зеленую заливку.

              Расшифровка видео

              Это полная стенограмма видеоролика "Окрашивание строки в Excel на основе значения одной ячейки" в верхней части этой страницы.

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

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

              В этом случае мы хотели бы выделить каждую строку в этом списке, если количество проданных единиц превышает 75.

              Для этого я выберу все строки, все столбцы в каждой строке. Поэтому я выбрал от A2 до D10.

              На ленте, на вкладке "Главная", я нажму "Условное форматирование", и ни одно из этих предустановленных правил не будет работать именно так, как я хочу. Итак, я перехожу к Новому правилу и здесь выбираю формулу.

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

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

              Я хочу в каждой строке просмотреть значение в столбце B. Поэтому я наберу =

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

              Поэтому введите $, чтобы зафиксировать это. А затем B.

              И в этом случае мы хотим, чтобы активная ячейка, которую мы видим, была белой, тогда как другие ячейки выделены синим цветом.

              Мы видим, что в поле имени появляется A2. Итак, это активная ячейка, поэтому активной строкой является 2.Поэтому я наберу здесь 2.

              Мы проверим, что находится в B2, и посмотрим, больше ли оно 75. Таков наш тест.

              И если оно больше 75, мы хотим отформатировать его. Поэтому я нажму «Формат», выберу цвет заливки, может быть, синий, и нажму «ОК», а затем снова нажму «ОК».

              Теперь в любой строке, где количество единиц больше 75, все четыре ячейки в этой строке окрашены в синий цвет.

              Получить образец файла

              Загрузите заархивированный образец файла для этого руководства по Excel. Сжатый файл имеет формат xlsx и не содержит макросов. Этот пример находится на листе с именем MultiCell.

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