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

Условная химическая формула топлива — Справочник химика 21

    Расчет можно проводить на основе условной химической формулы топлива или стехиометрических уравнений. В том случае, когда состав топлива и продуктов сгорания сложен, лучше использовать условную химическую формулу. Рассмотрим конкретные примеры. [c.22]

    УСЛОВНАЯ ХИМИЧЕСКАЯ ФОРМУЛА ТОПЛИВА [c.19]

    Здесь А, В, С — элементы, из которых состоит топливо (иногда элементы обозначают их химическими символами С — углерод, Н — водород, О — кислород и т. д.) а, с1, с — число атомов элементов. Если условная химическая формула записывается длч одного моля вещества (топлива), то ее называют молекулярной химической формулой. 0(на удобна для количественных расчетов. Если известен массовый состав элементов топлива (в %), а молекулярная масса и химическая формула не известны, то условную химическую формулу можно рассчитать по так называемой условной молекулярной массе [c.23]

    Из-за отсутствия достоверных данных за Я твердого топлива условно принималась поверхность угольной пыли. Как видно из построения формул, первый показатель Ri учитывает только химическую природу топлива и присадки, показатель A2 —также и реакционную поверхность нейтрализующих SO3 частиц.  [c.285]

    Химическая формула нефтяных топлив — условная. При расчете процессов горения в двигателях часто требуется знать хим. ф-лу топлива. В том случае, когда в качестве топлива применяется химически однородное вещество, такая формула будет известна, [c.708]

    Однако в воде, имеющей химическую формулу НаО, содержится некоторое, хотя и очень малое (всего 0,015 ат. %), количество ВгО — тяжелой воды, о которой можно сказать мал золотник, да дорог. Прежде всего, тяжелая вода применяется в качестве замедлителя нейтронов в атомных реакторах. Кроме того, и это очень важно, дейтерий относится к числу легких элементов, которые могут быть использованы в термоядерных реакторах. Когда будет решена проблема управляемого термоядерного синтеза, то только за счет дейтерия, содержащегося в морской воде, можно будет получать гигантское количество энергии — около 10 млрд. Q (где — количество энергии, которое может быть получено из 40 млрд. т условного топлива). Для сравнения укажем, что за период между 1851 и 1960 гг. человечество потребило 1 Q энергии. 

[c.88]

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

[c.20]

    Отсюда следует, что условной формулой называют запись, перечисляющую все химические элементы, входящие в состав топлива с указанием числа их грамм-атомов и показывающую, в какие продукты сгорания распределяются эти элементы с учетом количества их грамм-молекул. [c.182]

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

[c.19]

    Для конкретного варианта состава топлива производится отбор номеров атомов A Az, валентностей v , атомных весов ii всех химических элементов, присутствующих в горючем, окислителе и их примесях. Эта информация используется для образования условных формул с молекулярным весом ц,= 1000 как чистых компонентов топлива, так и с учетом примесей. Расчет условных формул, плотности и энтальпии компонентов топлива, величины (если последняя пе задана) производится по формулам части первой Справочника. [c.107]

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

[c.150]

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

Определение условной удельной поверхности — Справочник химика 21

    Метод определения условной удельной поверхности основан па измерении оптической плотности сажевой суспензии, приготовленной в специальных стандартных условиях. Для подсчета величины удельной поверхности применяются эмпирические формулы, отображающие математическую зависимость между оптической плотностью сажевых суспензий и средним радиусом сажевых частичек.  [c.220]
    ОПРЕДЕЛЕНИЕ УСЛОВНОЙ УДЕЛЬНОЙ ПОВЕРХНОСТИ [c.263]

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

[c.5]

    Прибор ПСХ-2 и другие приборы для определения внешней удельной поверхности по газопроницаемости, включая работающие под давлением менее 133 Па (1 мм рт. ст.), можно применять при условии независимости вычисленного значения удельной поверхности от пористости слоя порошка. Несмотря на известную условность значении внешней удельной поверхности, измеренных помощью прибора ПСХ-2, он обладает преимуществами но сравнению с другими приборами — быстротой измерений и простотой конструкции. [c.154]

    Условная удельная поверхность 8— это техническая константа, характеризующая удельную поверхность менее активных сортов сажи и определяемая через оптическую плотность водно-сажевой суспензии, приготовленной в стандартных условиях. Так как между оптической плотностью суспензии и величиной суспендированной частицы имеется определенная зависимость, то Е. Н. Быстровым и другими исследователями выведены уравнения, связывающие удельную поверхность с оптической плотностью. [c.262]

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

[c.104]

    Абсолютной удельной поверхностью условно названа удельная поверхность, определенная методом БЭТ. [c.55]

    Следует, однако, отметить, что определенная методом электронной микроскопии удельная поверхность сажи является геометрической удельной

Условная химическая формул — Справочник химика 21

    Пример. В состав керосина входит 86,0% С и 14% Н, найдем его условную химическую формулу. Если [c.24]

    Условная химическая формула смеси находится по уравне ню (21)  [c.37]

    В Справочнике приведены результаты расчетов для керосина с содержанием углерода 85,9% и водорода 14,1% (условная химическая формула СН1,95б). Производные параметров продуктов сгорания по составу керосина. найдены по относительному изменению отношения С/Н на 0,01. 

[c.15]

    Условная химическая формула смеси находится иа соотношение [c.17]

    Представить условными химическими формулами строение мицелл коллоидных растворов следующих веществ и указать коллоидные катионы и анионы  [c.129]

    Керосины получаются из нефти и являются смесью различных углеводородов нескольких гомологических рядов в них присутствует лишь небольшое количество азот-, серо- и кислородосодержащих гетероорганических соединений. Содержание отдельных составных частей зависит от способа получения керосина и месторождения нефти. В справочнике приведены результаты расчетов для керосина с содержанием углерода 85,9% и водорода 14,1% (условная химическая формула СН1,95б). Принято, что при температуре 25° С плотность керосина равна 0,830 г/см и низшая теплота сгорания составляет 10 300 ккал/кг. 

[c.20]

    Здесь А, В, С — элементы, из которых состоит топливо (иногда элементы обозначают их химическими символами С — углерод, Н — водород, О — кислород и т. д.) а, с1, с — число атомов элементов. Если условная химическая формула записывается длч одного моля вещества (топлива), то ее называют молекулярной химической формулой. 0(на удобна для количественных расчетов. Если известен массовый состав элементов топлива (в %), а молекулярная масса и химическая формула не известны, то условную химическую формулу можно рассчитать по так называемой условной молекулярной массе 

[c.23]

    УСЛОВНАЯ ХИМИЧЕСКАЯ ФОРМУЛА ТОПЛИВА [c.19]

    Для многокомпонентных смесей горючих гааов и паров расчет флегматизирующих концентраций инертных газов начинают с определения условной химической формулы и стандартной теплоты образования смеси горючих веществ. [c.17]

    Препарат ОС-20, условная химическая формула [c.216]

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

[c.22]

    В Справочнике приведены результаты расчетов для керосина с содержанием углерода 85,9% и водорода 14,1% (условная химическая формула керосина СНь95б). [c.15]

    Тогда условная химическая формула керосина может быть записана в виде С7,15Н1з 0Оо,о625  [c.24]

    Частица дисперсной фазы в коллоидном растворе называется мицеллой. Она состоит из коллоидно-дисперсной фазы, в данном случае (AsaSs) , и ионного стабилизатора HjS Н -f HS» условная химическая формула мицеллы [c.128]

    Если даииое топливо или окислитель представляет собой смесь различных индивидуальных химических веществ, то их элементарный весовой состав может быть вычислен или по условной химической формуле, которая составляется для многокомпонентных смесей, или по весовым долям индивидуальных веществ, входящих в состав топлива, либо окислителя расчет ведется по формуле [c.150]

    Условную химическую формулу для основной горючей связки асфальта — битума (по массе в асфальте было 20% битума и 80% песка) — можно определить по известной средней массовой доле химических элементов в битуме (86% С, 11% Н, 0,44% Н, 0,26% О и 2,3% 5). Приближенная формула при массовых долях 88 7о Си 12% Ив битуме имеет вид СгНз и соответствует углеводородам

Вычислительные методы определения удельных расходов условного топлива ТЭЦ на отпущенную электрическую и тепловую энергию в режиме комбинированной выработки. Раздел 3. Новый метод расчета удельных расходов условного топлива | Статьи

Статья опубликована в научном журнале «Наука и образование» 5 февраля 2016 года, PDF версия (0.8 MB).

Рабочее название статьи.

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

Раздел 1. Постановка задачи
Раздел 2. Обзор методов
Раздел 3. Новый метод расчета удельных расходов условного топлива
Раздел 4. Сравнение методов
Список литературы

Оглавление раздела

3. Метод расчета удельных расходов топлива, основанный на применении линеаризованной расходной характеристики паровой турбины
3.1. Линеаризованная расходная характеристика паровой турбины
3.2. Описание метода
3.3. Пример расчета

3. Метод расчета удельных расходов топлива, основанный на применении линеаризованной расходной характеристики паровой турбины

3.1.Линеаризованная расходная характеристика паровой турбины

Линеаризованная расходная характеристика паровой турбины g имеет вид [16]

(62)

где g ∈ [1:G] — номер паровой турбины; α_j^g — постоянные коэффициенты уравнения расходной характеристики; ε^g — ошибка линеаризации. Алгоритм определения коэффициентов α_j^g предложен в работе [16]. Средняя ошибка линеаризации различных типов паровых турбины составляет 0,6%. Подробный анализ ошибки линеаризации представлен в статье [16].

Общий расход пара высокого давления турбиной g разделим на три составляющие следующим образом:

(63)

Здесь Q₀^g(N), Q₀^g(Q_П), Q₀^g(Q_Т) — расходы пара высокого давления на выработку электроэнергии, промышленного пара, тепла соответственно, МВт·ч.

Определим расход пара высокого давления на выработку турбиной электроэнергии на основании расходной характеристики (62):

(64)

Величину α₀^g называют условный расход на холостой ход и относят на выработку электроэнергии [17].

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

(65)

(66)

3.2. Описание метода

Метод относится к термодинамическим методам расчета удельных расходов условного топлива. Метод основан на уравнении пара высокого давления (63).

Дополнительными исходными данными являются коэффициенты линеаризованных расходных характеристик паровых турбин ТЭЦ [16].

Решение задачи данным методом состоит из трех шагов.

1) Определение расходов пара высокого давления на выработку продуктов ТЭЦ

Для совокупности паровых турбин ТЭЦ расход пара высокого давления на выработку электроэнергии равен

(67)

где Q₀(N) — суммарный расход пара высокого давления на выработку электроэнергии N.

Аналогично определим расходы пара высокого давления на выработку промышленного пара и тепла ТЭЦ:

(68)

(69)

2) Разнесение общего расхода условного топлива на продукты ТЭЦ

Расход условного топлива на выработку электроэнергии имеет вид

(70)

Аналогично, расходы условного топлива на выработку промышленного пара и тепла определяют выражения

(71)

(72)

3) Вычисление удельных расходов условного топлива

Удельные расходы условного топлива вычисляют по формулам (1) – (3).

3.3. Пример расчета

В рассматриваемой ТЭЦ комбинированное производство осуществляется турбиной ПТ-135. Принципиальная схема ТЭЦ приведена в [4], а также в приложении к настоящей статье /sites/default/files/Chuchueva-Fuel-Rate.xlsx.

Исходные данные:

• выработка электроэнергии N = 80 МВт·ч, собственные нужды по электроэнергии N^СН = 3 МВт·ч;
• выработка промышленного пара Q_П = 144,2 МВт·ч, собственные нужды по промышленному пару Q_П^СН = 0 МВт·ч;
• выработка тепла Q_П = 193 МВт·ч, собственные нужды по теплу Q_Т^СН = 0 МВт·ч;
• общий расход условного топлива B = 48,4 т.у.т.;
• коэффициенты линеаризации паровой турбины ПТ-135 α_N = 1.8492, α_П = 0.7146, α_Т = 0.2820, α₀ = 95.6873.

1) Определение расходов пара высокого давления на выработку продуктов ТЭЦ

Расход пара высокого давления на выработку электроэнергии равен

(73)

Расходы пара высокого давления на выработку промышленного пара и тепла равны соответственно

(74)

(75)

Тогда общий расход пара высокого давления определяет выражение

(76)

2) Разнесение общего расхода условного топлива на продукты ТЭЦ

Расход условного топлива на выработку электроэнергии имеет вид

(77)

Расходы условного топлива на выработку промышленного пара и тепла равны

(78)

(79)

3) Вычисление удельных расходов условного топлива

Удельные расходы условного топлива на отпуск электроэнергии, промышленного пара и тепла ТЭЦ равны соответственно

(80)

(81)

(82)

Дополнительные расчеты приведены в приложении /sites/default/files/Chuchueva-Fuel-Rate.xlsx.

Раздел 1. Постановка задачи
Раздел 2. Обзор методов
Раздел 3. Новый метод расчета удельных расходов условного топлива
Раздел 4. Сравнение методов
Список литературы

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

Уголь по бассейнам и месторождениям
Алтайский уголь, тонна	0,782
Башкирский уголь, тонна	0,565
Воркутинский уголь, тонна	0,822
Грузинский уголь, тонна	0,589
Донецкий уголь, тонна	0,876
Интинский уголь, тонна	0,649
Казахский уголь, тонна	0,674
Камчатский уголь, тонна	0,323
Канско-Ачинский уголь, тонна	0,516
Карагандинский уголь, тонна	0,726
Кизеловский уголь, тонна	0,684
Киргизский уголь, тонна	0,570
Кузнецкий уголь, тонна	0,867
Львовско-Волынский уголь, тонна	0,764
Магаданский уголь, тонна	0,701
Подмосковный уголь, тонна	0,335
Приморский уголь, тонна	0,506
Сахалинский уголь, тонна	0,729
Свердловский уголь, тонна	0,585
Силезский уголь, тонна	0,800
Ставропольский уголь, тонна	0,669
Таджикский уголь, тонна	0,553
Тувинский уголь, тонна	0,906
Тунгусский уголь, тонна	0,754
Узбекский уголь, тонна	0,530
Украинский бурый уголь, тонна	0,398
Хакасский уголь, тонна	0,727
Челябинский уголь, тонна	0,552
Читинский уголь, тонна	0,483
Экибастузский уголь, тонна	0,628
Якутский уголь, тонна	0,751
Древесный уголь, складской м³	0,93
Эстонские сланцы, тонна	0,324
Ленинградские сланцы, тонна	0,300
Торф
Фрезерный торф (при условной влажности 40%), тонна	0,34
Кусковой торф (при условной влажности 33%), тонна	0,41
Торфяная крошка (при условной влажности 40%), тонна	0,37
Кокс металлургический сухой 25 мм и выше, тонна	0,99
Коксик 10-25 мм в пересчете на сухой вес, тонна	0,93
Коксовая мелочь , тонна	0,90
Брикеты топливные (при условной влажности 16%), тонна	0,60
Газ
Газ нефтепереработки сухой, тонна	1,50
Газ горючий природный, тыс. м³	1,15
Газ горючий попутный, тыс. м³	1,3
Газ сжиженный, тонна	1,57
Мазут
Мазут топочный, тонна	1,37
Мазут флотский, тонна	1,43
Нефть, включая газовый конденсат, тонна	1,43
Отработанные масла, тонна	1,30
Топливо для тихоходных дизелей (моторное), тонна	1,43
Топливо дизельное, тонна	1,45
Топливо печное бытовое, тонна	1,45
Бензин автомобильный, тонна	1,49
Бензин авиационный, тонна	1,49
Керосин для технических целей (тракторный), тонна	1,47
Керосин осветительный, тонна	1,47
Топливо для реактивных двигателей (керосин авиационный), тонна	1,47
Дрова
Дрова для отопления, плотный м³	0,266
Древесные обрезки, стружки, опилки, тонна	0,36
Древесные опилки, складской м³	0,11
Сучья, хвоя, щепа, складской м³	0,05
Пни, складской м³	0,12
Бревна разобранных старых зданий, пришедшие в негодность шпалы, столбы связи, рудничная стойка, плотный м³	0,266
Кора, тонна	0,42
Отходы сельскохозяйственного производства, тонна	0,50

теплота сгорания; условное топливо; удельный расход условного топлива — Студопедия

Энергетические ресурсы поступают на электростанции в виде топлива.

Топливо – это любое вещество способное при горении (окислении) выделять значительное количество энергии виде теплоты. Менделеев Д.И. называет топливом горючее вещество «умышленно» сжигаемое для получения теплоты.

Различают «рабочую массу»: С^Р + Н^Р + О^Р + N^Р +S^Р + А^Р + W^Р = 100%, где слева элементы рабочего топлива в процентах от общей массы топлива.

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

Различают «горючую массу»: С^R + Н^R + O^R +N^R +S^{R= 100% , где верхний индекс показывает, что процентный состав отдельных элементов отнесен к горючей массе}

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

Воздух является окислителем, поэтому необходим для горения,. Для полного сгорания 1 кг топлива требуется примерно 10-15 кг воздуха.

Вода. ТЭС потребляют огромное количество воды. Например, один энергоблок мощностью 300 МВт за 1 сек использует около 10 м^3воды

Основная характеристика любого вида топлива – это теплота сгоранияQ. Содержание горючей массы в рабочей определяет теплоту сгорания. Теплотой сгорания твердого и жидкого топлива называют количество теплоты (кДж) выделяемое при его полном сгорании Q_СГ [кДж/кг] или в системе МКГСС [ ккал/кг]. Теплоту сгорания газообразного топлива относят к 1 м^3..

Наибольший практический интерес представляет теплота сгорания рабочей массы топлива . Поскольку в продуктах сгорания топлива, содержащего водород и влагу, будет содержаться водяной пар Н₂ О, вводится понятие высшей теплоты сгорания.

Высшей теплотой сгорания рабочего топлива называют теплоту, выделяемую при полном сгорании 1 кг топлива, считая, что образующийся при сгорании водяные пары конденсируются.

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

Для сравнения качества работы различных ТЭС вводят понятие «условного топлива»^{(у.т.) Q_уТ.}

Условным называется такое топливо, теплота сгорания 1 кг или 1 м³ которого равна 29330 кДж/кг или 7000 ккал/кг.

Для перевода действительного топлива в условное топливо пользуются соотношением

Э_к = (в системе МКГСС Э_к = ),

где Э_к – калорийный эквивалент указывающий, какая часть теплоты сгорания условного топлива соответствует низшей теплоте сгорания рассматриваемого топлива.

Расход условного топлива

В_УС = ,

где В – расход рассматриваемого натурального топлива; — его теплота сгорания.

Например, ТЭС сожгла 1000 т бурого угля = 3500 ккал/кг, это значит, что станция израсходовала 500 т у.т.

== 500 т у.т.

Таким образом, «условное топливо» — это единица учета органического топлива, применяемое для сопоставления эффективности различных видов топлива и суммарного их учета

.

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

b_У = .

Например, на электростанции сожгли 100 т топлива с теплотой сгорания

Q = 3500 ккал/кг, т.е. использовали В_У.Т. = 50 т и при этом отпущено в сеть

Э = 160 000 кВт ч электрической энергии. Следовательно, удельный расход условного топлива составил b_У = = 312 г/кВт.ч

Между КПД станции и удельным расходом существует связь b_У = , поэтому в нашем случае η_ТЭС = = = 0,395.

Контрольные вопросы к первой лекции 2013г(БАЭ-12)

1.Что такое энергия и мощность? В каких единицах измеряется энергия и мощность?

2.Перечислите основные возобновляемые и невозобновляемые энергетические ресурсы.

3. Что такое топливно-энергетический комплекс?

4. Перечислите составные части ТЭК и дайте им расшифровку.

5. Электроэнергетическая система и ее особенности?

6. Что такое топливо и его основная характеристика?

7. Что такое условное топливо и для чего это понятие введено?

8. Как определяется удельный расход условного топлива7

9. Перечислите виды электростанций традиционной электроэнергетики.

10. Раскройте понятие электроэнергетика?

11. Какие ресурсы используются для выработки электрической и тепловой энергии на тепловых электростанциях?

12. Какие виды энергоресурсов используются на электростанциях нетрадиционной электроэнергетики?

13. Что такое энергосистема?

14. Перечислите виды массы топлива.

15. Влияние тепловых электростанций на окружающую среду.

.

Условное форматирование с формулами (10 примеров)

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

Excel поставляется с большим количеством «предустановок», которые позволяют легко создавать новые правила без формул. Однако вы также можете создавать правила со своими собственными формулами.Используя собственную формулу, вы берете на себя условие, которое запускает правило, и можете применять именно ту логику, которая вам нужна. Формулы дают вам максимальную мощность и гибкость.

Например, с помощью предустановки «Равно» легко выделить ячейки, равные «яблоку».

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

Вот результат применения правила к диапазону B4: F8 в этой таблице:

Вот точная формула:

 
 = ИЛИ (B4 = «яблоко», B4 = «киви», B4 = «лайм») 

Быстрый запуск

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

1.Выделите ячейки, которые хотите отформатировать.

2. Создайте правило условного форматирования и выберите опцию Формула

3. Введите формулу, которая возвращает ИСТИНА или ЛОЖЬ.

4. Задайте параметры форматирования и сохраните правило.

Функция ISODD возвращает TRUE только для нечетных чисел, срабатывая правило:

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

Логика формул

Формулы, применяющие условное форматирование, должны возвращать ИСТИНА или ЛОЖЬ или их числовые эквиваленты.Вот несколько примеров:

Все приведенные выше формулы возвращают ИСТИНА или ЛОЖЬ, поэтому они отлично работают в качестве триггера для условного форматирования.

Когда условное форматирование применяется к диапазону ячеек, введите ссылки на ячейки относительно первой строки и столбца в выделенном фрагменте (то есть в верхней левой ячейке). Уловка для понимания того, как работают формулы условного форматирования, заключается в том, чтобы визуализировать одну и ту же формулу, применяемую к каждой ячейке в выделенном , при этом ссылки на ячейки обновляются как обычно.Представьте, что вы ввели формулу в левую верхнюю ячейку выделенного фрагмента, а затем скопировали формулу по всему выделенному фрагменту. Если вы боретесь с этим, см. Раздел «Формулы-пустышки» ниже.

Примеры формул

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

Выделить заказы из Техаса

Чтобы выделить строки, которые представляют заказы из Техаса (сокращенно TX), используйте формулу, которая блокирует ссылку на столбец F:

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

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

Выделите даты в ближайшие 30 дней

Чтобы выделить даты, наступающие в ближайшие 30 дней, нам нужна формула, которая (1) гарантирует, что даты находятся в будущем, и (2) гарантирует, что даты находятся в 30 днях или меньше от сегодняшнего дня. Один из способов сделать это — использовать функцию И вместе с функцией СЕЙЧАС следующим образом:

Для текущей даты 18 августа 2016 года условное форматирование выделяет даты следующим образом:

Функция СЕЙЧАС возвращает текущую дату и время.Подробнее о том, как работает эта формула, см. В этой статье: Выделение дат в ближайшие N дней.

Выделить различия столбцов

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

См. Также: версия этой формулы, в которой функция ТОЧНОСТЬ используется для сравнения с учетом регистра.

Выделить отсутствующие значения

Чтобы выделить значения в одном списке, которые отсутствуют в другом, можно использовать формулу, основанную на функции СЧЁТЕСЛИ:

Эта формула просто проверяет каждое значение в списке Список A на соответствие значениям в именованном диапазоне «список» (D5: D10).Когда счетчик равен нулю, формула возвращает ИСТИНА и запускает правило, которое выделяет значения в списке , список A, , которые отсутствуют в списке , список B .

Видео: как найти пропущенные значения с помощью COUNTIF

Выделите недвижимость с 3+ спальнями до $ 350 000

Чтобы найти в этом списке объекты недвижимости с как минимум 3 спальнями, но стоимостью менее 300 000 долларов США, вы можете использовать формулу, основанную на функции И:

Знак доллара ($) блокирует ссылку на столбцы C и D, а функция AND используется для проверки ИСТИНА обоих условий.В строках, где функция И возвращает ИСТИНА, применяется условное форматирование:

Выделить верхние значения (динамический пример)

Хотя в Excel есть предустановки для «верхних значений», в этом примере показано, как сделать то же самое с формулой и как формулы могут быть более гибкими. Используя формулу, мы можем сделать рабочий лист интерактивным — когда значение в F2 обновляется, правило мгновенно реагирует и выделяет новые значения.

Формула, используемая для этого правила:

Где «данные» — это именованный диапазон B4: G11, а «вход» — именованный диапазон F2.На этой странице есть подробности и полное объяснение.

Диаграммы Ганта

Хотите верьте, хотите нет, но вы можете даже использовать формулы для создания простых диаграмм Ганта с условным форматированием, например:

В этом листе используются два правила: одно для полосок, а другое — для затенения выходных дней:

 
 = AND (D $ 4> = $ B5, D $ 4 <= $ C5) // бары
= WEEKDAY (D $ 4,2)> 5 // выходные 

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

Простое окно поиска

С условным форматированием можно сделать один интересный трюк — создать простое окно поиска. В этом примере правило выделяет ячейки в столбце B, содержащие текст, введенный в ячейку F2:

.

Используемая формула:

Для получения дополнительной информации и полного объяснения см .:

Устранение неисправностей

Если правила условного форматирования не срабатывают правильно, скорее всего, проблема связана с формулой.Во-первых, убедитесь, что вы начали формулу со знака равенства (=). Если вы забудете этот шаг, Excel незаметно преобразует всю формулу в текст, делая ее бесполезной. Чтобы исправить это, просто удалите двойные кавычки, добавленные Excel с обеих сторон, и убедитесь, что формула начинается с равно (=).

Если формула введена правильно, но правило не срабатывает, возможно, вам придется копнуть немного глубже. Обычно вы можете использовать клавишу F9 для проверки результатов в формуле или использовать функцию Evaluate для пошагового выполнения формулы.К сожалению, вы не можете использовать эти инструменты с формулами условного форматирования, но вы можете использовать технику, называемую «фиктивными формулами».

Формулы-пустышки

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

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

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

Ограничения

Есть некоторые ограничения, связанные с условным форматированием на основе формул:

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

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

Дополнительные ресурсы по формуле CF

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

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

Объектив

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

Так как это Excel, есть много способов выполнить любую задачу. Один из способов определить открытые счета-фактуры — просто отсортировать список по столбцу «Статус», чтобы открытые счета-фактуры отображались в группе.Другой способ — отфильтровать список, чтобы отображались только открытые счета. Эти методы довольно просты, поэтому давайте рассмотрим другой метод. Мы выделим строки транзакции с помощью форматирования ячеек… или, точнее, формулы условного форматирования.

Условное форматирование

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

• На главную> Условное форматирование> Правила ячеек> Равно

В диалоговом окне «Равно» мы можем ввести слово «Открыть», выбрать желаемое форматирование и нажать «ОК». Затем Excel применит форматирование к ячейкам в столбце «Состояние», которые имеют значение «Открыть». Хотя этот метод прост, он не отвечает нашей цели — выделить всю строку транзакции, а не только столбец состояния.

Чтобы выделить всю строку транзакции, нам необходимо отформатировать ячейку на основе значения в другой ячейке. То есть мы хотим отформатировать столбцы TID, Date, Status, CustID и Amount на основе значения в столбце Status. Рассматривая на мгновение одну ячейку, мы хотим отформатировать B7 на основе значения в D7. Это означает, что мы хотим отформатировать ячейку B7 на основе значения в другой ячейке D7. Распространяя это на всю строку, мы хотим отформатировать B7: F7 на основе значения в D7.

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

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

• На главную> Условное форматирование> Новое правило

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

Формула форматирования должна быть настроена так, чтобы она возвращала истинное или ложное значение. Если формула возвращает истину, применяется желаемое форматирование. Если формула возвращает false, форматирование не применяется.

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

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

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

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

 = $ D7 = "Открыть" 

Эта простая формула сравнения возвращает истину, когда D7 равно Open, и, таким образом, будет применен желаемый формат.Давайте на мгновение взглянем на ссылку на ячейку. Мы использовали смешанную ссылку на ячейку, $ D7, где часть столбца (D) является абсолютной, а часть строки (7) — относительной. Вот почему. Помните, мы хотим сделать вид, что записываем формулу в активную ячейку, в данном случае B7. Если бы мы форматировали только B7, мы могли бы использовать относительную ссылку D7, или абсолютную ссылку $ D $ 7, или смешанную ссылку. Стиль ссылки не имеет значения, потому что формула использовалась только в одной ячейке и нигде не заполнялась.

Однако в тот момент, когда мы заполняем формулу вниз или вправо, мы должны быть осторожны, чтобы использовать правильные стили ссылки на ячейки. В нашем случае, поскольку формула форматирования заполнена правильно, мы не хотим, чтобы ссылка на столбец D изменялась. То есть для всех ячеек мы хотим сослаться на столбец «Состояние», столбец D. Чтобы Excel не мог изменить ссылку на столбец, когда формула находится справа, мы фиксируем ее знаком доллара, что приводит к $ D. Поскольку формула форматирования заполняется по всему выбранному диапазону, мы хотим убедиться, что ссылка на строку обновляется соответствующим образом.При форматировании B7 мы хотим смотреть на столбец состояния в той же строке, или на D7. Однако, когда формула заполняется до строки 8, мы хотим отформатировать B8 на основе значения в D8. Поскольку мы хотим, чтобы Excel обновлял ссылку на строку, оставим ее относительной и не будем использовать знак доллара для ее блокировки.

Вот скриншот диалогового окна форматирования с формулой:

После ввода формулы вы просто используете кнопку «Формат», чтобы указать желаемый формат.

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

Бам… мы получили!

Прочие соображения

В приведенной выше формуле мы жестко запрограммировали значение «Открыть», однако мы могли бы легко поместить это значение в ячейку, а затем ссылаться на ячейку либо по имени, либо в нотации в стиле A1. Кроме того, помимо простого определения того, равно ли значение ячейки значению, поддерживаются другие операторы сравнения, например, больше (>) и меньше (<).Мы могли бы выделить все строки, в которых значение превышает 5000 долларов США, используя следующую формулу сравнения:

 = $ F7> 5000 

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

Несколько условий

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

 = И ($ D7 = "Открыть", $ F7> 5000) 

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

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

Заключение

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

Надеюсь, это поможет, и помните, правила Excel!

Пример файла

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

Формула форматирования

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

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

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

Формулы Excel для условного форматирования на основе значения ячейки

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

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

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

Как создать правило условного форматирования с помощью формулы

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

1. Выберите ячейки, которые нужно отформатировать.Вы можете выбрать один столбец, несколько столбцов или всю таблицу, если хотите применить условный формат к строкам. Наконечник. Если вы планируете добавить больше данных в будущем и хотите, чтобы правило условного форматирования применялось к новым записям автоматически, вы можете:
   • Преобразование диапазона ячеек в таблицу ( Вставка вкладки> Таблица ). В этом случае условное форматирование будет автоматически применяться ко всем новым строкам.
   • Выберите несколько пустых строк под данными, скажем, 100 пустых строк.
2. На вкладке Home в группе Стили щелкните Условное форматирование> Новое правило…
   
3. В окне Новое правило форматирования выберите Используйте формулу, чтобы определить, какие ячейки нужно отформатировать .
4. Введите формулу в соответствующее поле.
5. Нажмите кнопку Формат… , чтобы выбрать собственный формат.
   
6. Переключение между шрифтом Font , Border и Заполните вкладки и поиграйте с различными параметрами, такими как стиль шрифта, цвет узора и эффекты заливки, чтобы настроить формат, который лучше всего подходит для вас.Если стандартной палитры недостаточно, щелкните Другие цвета… и выберите любой цвет RGB или HSL по своему вкусу. Когда закончите, нажмите кнопку OK .
   
7. Убедитесь, что в разделе Preview отображается нужный формат, и если это так, нажмите кнопку OK , чтобы сохранить правило. Если вас не совсем устраивает предварительный просмотр формата, нажмите кнопку Format… еще раз и внесите изменения.
   

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

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

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

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

Формулы для сравнения значений (числа и текст)

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

Состояние	Пример формулы
Равно	= B2 = 10
Не равно	= $ B2 <> 10
Больше	= $ B2> 10
Больше или равно	= $ B2> = 10
Менее	= B2 <10
Меньше или равно	= $ B2 <= 10
Между	= И ($ B2> 5, $ B2 <10)

На снимке экрана ниже показан пример формулы Больше, чем , которая выделяет названия продуктов в столбце A, если количество товаров на складе (столбец C) больше 0.Обратите внимание, что формула применима только к столбцу A ($ A $ 2: $ A $ 8). Но если вы выберете всю таблицу (в нашем случае $ A $ 2: $ E $ 8), это выделит целые строки на основе значения в столбце C.

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

= $ A2 <$ B2 - форматировать ячейки или строки, если значение в столбце A меньше, чем соответствующее значение в столбце B.

= $ A2 = $ B2 - форматировать ячейки или строки, если значения в столбцах A и B совпадают.

= $ A2 <> $ B2 - форматировать ячейки или строки, если значение в столбце A не такое же, как в столбце B.

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

Формулы И и ИЛИ

Если вы хотите отформатировать таблицу Excel на основе 2 или более условий, используйте функцию = И или = ИЛИ:

Состояние	Формула	Описание
Если оба условия соблюдены	= И ($ B2 <$ C2, $ C2 <$ D2)	Форматирует ячейки, если значение в столбце B меньше, чем в столбце C, и , если значение в столбце C меньше, чем в столбце D.
При выполнении одного из условий	= ИЛИ ($ B2 <$ C2, $ C2 <$ D2)	Форматирует ячейки, если значение в столбце B меньше, чем в столбце C, или , если значение в столбце C меньше, чем в столбце D.

На скриншоте ниже мы используем формулу = AND ($ C2> 0, $ D2 = "Worldwide") , чтобы изменить цвет фона строк, если количество товаров на складе (столбец C) больше чем 0, и если продукт доставляется по всему миру (столбец D).Обратите внимание, что формула работает с текстовыми значениями , а также с числами .

Естественно, вы можете использовать два, три или более условий в формулах = И и = ИЛИ.

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

Условное форматирование пустых и непустых ячеек

Я думаю, что все знают, как форматировать пустые, а не пустые ячейки в Excel - вы просто создаете новое правило типа « Форматировать только ячейки, которые содержат» и выбираете либо Пробелы , либо Без пробелов .

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

Формула для пробелов : = $ B2 = "" - форматирование выбранных ячеек / строк, если соответствующая ячейка в столбце B пуста.

Формула для непустых значений : = $ B2 <> "" - форматирование выбранных ячеек / строк, если соответствующая ячейка в столбце B не пуста.

Примечание. Формулы выше будут работать для ячеек, которые «визуально» пусты или не пусты. Если вы используете какую-либо функцию Excel, которая возвращает пустую строку, например = if (false, "OK", "") , и вы не хотите, чтобы такие ячейки обрабатывались как пустые, используйте вместо них следующие формулы = isblank (A1) = true или = isblank (A1) = false для форматирования пустых и непустых ячеек соответственно.

А вот пример того, как можно использовать приведенные выше формулы на практике.Предположим, у вас есть столбец (B), который представляет собой « Дата продажи », а другой столбец (C) « Поставка ». Эти 2 столбца имеют значение только в том случае, если была совершена продажа и доставлен товар. Итак, вы хотите, чтобы вся строка стала оранжевой, когда вы совершите продажу; и когда товар доставлен, соответствующая строка должна стать зеленой. Для этого вам необходимо создать 2 правила условного форматирования со следующими формулами:

• Оранжевые строки (ячейка в столбце B не пуста): = $ B2 <> ""
• Зеленые строки (ячейки в столбце B и столбце C не пусты): = AND ($ B2 <> "", $ C2 <> "")

Еще одна вещь, которую вам нужно сделать, это переместить второе правило вверх и установить флажок Остановить, если истинно рядом с этим правилом:

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

Формулы Excel для работы с текстовыми значениями

Если вы хотите отформатировать определенный столбец (столбцы), когда другая ячейка в той же строке содержит определенное слово, вы можете использовать формулу, описанную в одном из предыдущих примеров (например, = $ D2 = "Worldwide"). Однако это будет работать только для точного совпадения .

Для частичного совпадения необходимо использовать либо ПОИСК (без учета регистра), либо НАЙТИ (с учетом регистра).

Например, для форматирования выбранных ячеек или строк, если соответствующая ячейка в столбце D содержит слово « Worldwide », используйте формулу ниже. Эта формула найдет все такие ячейки, независимо от того, где в ячейке находится указанный текст, включая « Ships Worldwide », « Worldwide, за исключением… » и т. Д .:

= ПОИСК ("Весь мир", $ D2)> 0

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

= ПОИСК ("Весь мир", $ D2)> 1

Формулы Excel для выделения дубликатов

Если вашей задачей является условное форматирование ячеек с повторяющимися значениями, вы можете воспользоваться предопределенным правилом, доступным в разделе Условное форматирование> Правила выделения ячеек> Повторяющиеся значения… В следующей статье содержится подробное руководство по использованию этой функции: Как автоматически выделять дубликаты в Excel.

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

Выделить дубликаты, включая 1 ^st вхождений

= СЧЕТЕСЛИ ($ A $ 2: $ A $ 10, $ A2)> 1 - эта формула находит повторяющиеся значения в указанном диапазоне в столбце A (A2: A10 в нашем случае), включая первые вхождения.

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

Выделить дубликаты без 1 ^st вхождений

Чтобы игнорировать первое вхождение и выделять только последующие повторяющиеся значения, используйте эту формулу: = СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> 1

Выделить последовательные дубликаты в Excel

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

• Выберите столбец, в котором вы хотите выделить дубликаты, без заголовка столбца .
• Создайте правило (я) условного форматирования, используя эти простые формулы:
  Правило 1 (синий): = $ A1 = $ A2 - выделяет 2 ^{-го вхождения} и все последующие вхождения, если таковые имеются.
  Правило 2 (зеленый): = $ A2 = $ A3 - выделяет 1 ^st вхождение.

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

Важно! Для правильной работы формул важно, чтобы Правило 1, выделяющее 2 ^и и все последующие повторяющиеся вхождения, было первым правилом в списке, особенно если вы используете два разных цвета.

Выделить повторяющиеся строки

Если вы хотите применить условный формат, когда повторяющиеся значения встречаются в двух или более столбцах, вам нужно будет добавить в таблицу дополнительный столбец, в котором вы объедините значения из ключевых столбцов, используя простую формулу, подобную этой = A2 & B2 .После этого вы применяете правило, используя любой вариант формулы СЧЁТЕСЛИ для дубликатов (с 1 ^st вхождениями или без них). Естественно, вы можете скрыть дополнительный столбец после создания правила.

В качестве альтернативы можно использовать функцию СЧЁТЕСЛИМН, которая поддерживает несколько критериев в одной формуле. В этом случае вспомогательный столбец вам не понадобится.

В этом примере, чтобы выделить повторяющиеся строки с первыми вхождениями , создайте правило со следующей формулой:
= COUNTIFS ($ A $ 2: $ A $ 11, $ A2, $ B $ 2: $ B $ 11, $ B2) > 1

Чтобы выделить повторяющиеся строки без первых вхождений , используйте эту формулу:
= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2, $ B $ 2: $ B2, $ B2)> 1

Сравнить 2 столбца на наличие дубликатов

Одна из наиболее частых задач в Excel - проверка двух столбцов на наличие повторяющихся значений - i.е. найти и выделить значения, которые существуют в обоих столбцах. Для этого вам нужно будет создать правило условного форматирования Excel для каждого столбца с комбинацией функций = ISERROR () и = MATCH () :

для столбца A: = ISERROR (MATCH (A1, $ B $ 1: $ B $ 10000,0)) = FALSE

для столбца B: = ISERROR (MATCH (B1, $ A $ 1: $ A $ 10000,0)) = FALSE

Примечание. Для правильной работы таких условных формул очень важно применять правила ко всем столбцам, например.грамм. = $ A: $ A и = $ B: $ B .

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

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

Формулы для выделения значений выше или ниже среднего

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

Например, вы можете использовать формулу = $ E2 для условного форматирования строк, в которых количество продаж ниже среднего, как показано на снимке экрана ниже. Если вы ищете обратное, то есть, чтобы затенить продукты, эффективность которых выше среднего, замените "<" на ">" в формуле: = $ E2> AVERAGE ($ E $ 2: $ E $ 8) .


Как выделить ближайшее значение в Excel

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

Пример 1. Найдите ближайшее значение, включая точное совпадение

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

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

= МИН (АБС (B2: D13- (0)))

В приведенной выше формуле B2: D13 - это диапазон ячеек, а 0 - это число, для которого вы хотите найти ближайшее соответствие. Например, если вы ищете значение, наиболее близкое к 5, формула изменится на: = MIN (ABS (B2: D13- (5)))

Примечание. Это формула массива , поэтому вам нужно нажать Ctrl + Shift + Enter вместо простого ввода Enter, чтобы завершить его.

А теперь вы создаете правило условного форматирования со следующей формулой, где B3 - это верхняя правая ячейка в вашем диапазоне, а $ C $ 2 в ячейке с приведенной выше формулой массива:

= ИЛИ (B3 = 0- $ C $ 2, B3 = 0 + $ C $ 2)

Обратите внимание на использование абсолютных ссылок в адресе ячейки, содержащей формулу массива ($ C $ 2), поскольку эта ячейка является постоянной.Кроме того, вам нужно заменить 0 на число, для которого вы хотите выделить ближайшее совпадение. Например, если мы хотим выделить ближайшее к 5 значение, формула изменится на: = ИЛИ (B3 = 5- $ C $ 2, B3 = 5 + $ C $ 2)

Пример 2. Выделите значение, наиболее близкое к заданному, но НЕ точное соответствие

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

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

= МИН (АБС (B3: C13- (0)) + (10 ^ 0 * (B3: C13 = 0)))

Не забудьте нажать Ctrl + Shift + Enter после того, как вы закончите вводить формулу массива.

Формула условного форматирования такая же, как в приведенном выше примере:

= ИЛИ (B3 = 0- $ C $ 2, B3 = 0 + $ C $ 2)

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


Если вы хотите найти значение, ближайшее к какому-либо другому числу в листе Excel, просто замените «0» нужным числом как в массиве, так и в формулах условного форматирования.

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

Почему условное форматирование в Excel работает неправильно?

Если ваше правило условного форматирования не работает должным образом, хотя формула явно верна, не расстраивайтесь! Скорее всего, это не из-за какой-то странной ошибки в условном форматировании Excel, а из-за крошечной ошибки, не очевидной на первый взгляд.Попробуйте выполнить 6 простых шагов по устранению неполадок ниже, и я уверен, что ваша формула заработает:

1. Правильно используйте абсолютные и относительные адреса ячеек. Очень сложно вывести общее правило, которое будет работать в 100% случаев. Но чаще всего вы будете использовать абсолютный столбец (с $) и относительную строку (без $) в ссылках на ячейки, например = $ A1> 1 .

   Имейте в виду, что формулы = A1 = 1 , = $ A $ 1 = 1 и = A $ 1 = 1 дадут разные результаты.Если вы не уверены, какой из них правильный в вашем случае, вы можете попробовать все 🙂 Для получения дополнительной информации см. Относительные и абсолютные ссылки на ячейки в условном форматировании Excel.

2. Проверьте примененный диапазон. Проверьте, применяется ли ваше правило условного форматирования к правильному диапазону ячеек. Эмпирическое правило таково - выберите все ячейки / строки, которые вы хотите отформатировать, но не включайте заголовки столбцов.
3. Запишите формулу для верхней левой ячейки. В правилах условного форматирования ссылки на ячейки указываются относительно самой левой верхней ячейки в применяемом диапазоне.Поэтому всегда пишите формулу условного форматирования для 1-й строки с данными.

   Например, если ваши данные начинаются со строки 2, вы помещаете = A $ 2 = 10 , чтобы выделить ячейки со значениями, равными 10, в всех строках . Распространенная ошибка - всегда использовать ссылку на первую строку (например, = A $ 1 = 10 ). Помните, что вы ссылаетесь на строку 1 в формуле только в том случае, если ваша таблица не имеет заголовков и ваши данные действительно начинаются в строке 1. Наиболее очевидным признаком этого случая является то, что правило работает, но форматирует значения не в тех строках, которые должны .

4. Проверьте созданное вами правило. Еще раз проверьте правило в диспетчере правил условного форматирования. Иногда без всякой причины Microsoft Excel искажает только что созданное правило. Итак, если правило не работает, перейдите к Условное форматирование> Управление правилами и проверьте формулу и диапазон, к которому она применяется. Если вы скопировали формулу из Интернета или другого внешнего источника, убедитесь, что используются прямые кавычки .
5. Отрегулируйте ссылки на ячейки при копировании правила. Если вы копируете условное форматирование Excel с помощью Format Painter, не забудьте настроить все ссылки на ячейки в формуле.
6. Разбиение сложных формул на простые элементы. Если вы используете сложную формулу Excel, включающую несколько различных функций, разделите ее на простые элементы и проверьте каждую функцию по отдельности.

И, наконец, если вы испробовали все шаги, но ваше правило условного форматирования все еще работает неправильно, напишите мне в комментариях, и мы постараемся разобраться в этом вместе 🙂

В моей следующей статье мы рассмотрим возможности условного форматирования дат в Excel.Увидимся на следующей неделе и спасибо за чтение!

Вас также может заинтересовать

Условное форматирование даты и времени в Excel

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

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

Если у вас есть базовые знания о формулах Excel, то вы, скорее всего, знакомы с некоторыми функциями даты и времени, такими как СЕЙЧАС, СЕГОДНЯ, ДАТА, ДЕНЬ НЕДЕЛИ и т. Д. В этом руководстве мы собираемся расширить эту функциональность. в условно отформатируйте даты Excel по своему усмотрению.

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

Условное форматирование дат в Excel (встроенные правила)

Microsoft Excel предоставляет 10 вариантов форматирования выбранных ячеек на основе текущей даты.

1. Чтобы применить форматирование, просто перейдите на вкладку Home > Условное форматирование> Выделите правила для ячеек и выберите A Date Occurring .

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

4. Нажмите OK и наслаждайтесь результатом! 🙂


Однако этот быстрый и простой способ имеет два существенных ограничения: 1) он работает только для выбранных ячеек и 2) условный формат всегда применяется на основе текущей даты.

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

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

Как выделить выходные в Excel

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

При разработке календаря Excel вы можете использовать функцию = ДАТА (год, месяц, число) для отображения дней недели.Просто введите год и номер месяца где-нибудь в своей электронной таблице и укажите эти ячейки в формуле. Конечно, вы можете вводить числа прямо в формулу, но это не очень эффективный подход, потому что вам придется корректировать формулу для каждого месяца.

Снимок экрана ниже демонстрирует функцию ДАТА в действии. Я использовал формулу = ДАТА ($ B $ 2, $ B $ 1, B $ 4) , которая копируется в строке 5.


Наконечник. Если вы хотите отображать только дни недели, как показано на изображении выше, выберите ячейки с формулой (в нашем случае строка 5), щелкните правой кнопкой мыши и выберите Формат ячеек…> Число> Пользовательский .В раскрывающемся списке под Тип выберите dddd или ddd , чтобы отображать полные или сокращенные названия дней соответственно.

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

1. Вы ​​начинаете с выбора календаря Excel, в котором вы хотите добавить тени к выходным.В нашем случае это диапазон $ B $ 4: $ AE $ 10. Обязательно начинайте выбор со столбца даты 1 st - столбец B в этом примере.
2. На вкладке Домашняя страница щелкните меню «Условное форматирование »> «Новое правило ».
3. Создайте новое правило форматирования на основе формулы, как описано в разделе «Как создать правила условного форматирования Excel с помощью формулы».
4. В поле « Format values ​​where this formula is true» введите следующую формулу WEEKDAY, которая определит, какие ячейки являются субботой и воскресеньем: = WEEKDAY (B $ 5,2)> 5
5. Нажмите кнопку Format… и настройте свой собственный формат, переключаясь между вкладками Font , Border и Fill tab и играя с различными параметрами форматирования.Когда закончите, нажмите кнопку OK , чтобы просмотреть правило.


Теперь позвольте мне вкратце пояснить формулу WEEKDAY (serial_number, [return_type]) , чтобы вы могли быстро настроить ее для своих собственных таблиц.

• Параметр serial_number представляет дату, которую вы пытаетесь найти. Вы вводите ссылку на свою первую ячейку с датой, в нашем случае 5 B $.
• Параметр [return_type] определяет тип недели (квадратные скобки означают, что это необязательно).Вы вводите 2 в качестве типа возврата за неделю, начиная с понедельника (1) по воскресенье (7). Вы можете найти полный список доступных типов возврата здесь.
• Наконец, вы пишете> 5, чтобы выделить только субботу (6) и воскресенье (7).

На скриншоте ниже показан результат в Excel 2013 - выходные выделены красноватым цветом.

Советы:

• Если у вас в компании нестандартные выходные, например По пятницам и субботам вам нужно будет настроить формулу, чтобы она начинала отсчет с воскресенья (1) и выделяла дни 6 (пятница) и 7 (суббота) - WEEKDAY (5,1 B $)> 5 .
• Если вы создаете горизонтальный (альбомный) календарь, используйте относительный столбец (без $) и абсолютную строку (с $) в ссылке на ячейку, потому что вы должны заблокировать ссылку на строку - в приведенном выше примере это строка 5, поэтому мы ввели 5 бразильских долларов. Но если вы разрабатываете календарь в вертикальной ориентации, вам следует сделать наоборот, то есть использовать абсолютный столбец и относительную строку, например $ B5, как вы можете видеть на скриншоте ниже:

Как выделить праздники в Excel

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

Например, я добавил следующие праздники в столбец A (14 австралийских долларов: 17 австралийских долларов). Конечно, не все они настоящие государственные праздники, но в демонстрационных целях подойдут 🙂

Снова вы открываете Условное форматирование > Новое правило . В случае праздников вы собираетесь использовать функцию ПОИСКПОЗ или СЧЕТЕСЛИ :

• = СЧЁТЕСЛИ ($ A $ 14: $ A $ 17, B $ 5)> 0
• = ПОИСКПОЗ (5 B $, 14 A $: 17,0 A $)

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

На следующем изображении показан результат в Excel 2013:

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

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

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

1. Прежде всего, вам нужно определить код формата вашей даты. Вот несколько примеров:
   • D1: дд-ммм-гг или д-ммм-гг
   • D2: дд-ммм или д-ммм
   • D3: ммм-гг
   • D4: мм / дд / гг или м / д / гг или м / д / гг ч: мм

   Полный список кодов дат можно найти в этой статье.

2. Выберите столбец, в котором вы хотите изменить цвет ячеек или всей таблицы, если вы хотите выделить строки.
3. А теперь создайте правило условного форматирования, используя формулу, подобную этой: = ЯЧЕЙКА ("формат", $ A2) = "D1" . В формуле A - это столбец с датами, а D1 - это формат даты.

   Если ваша таблица содержит даты в двух или более форматах, используйте оператор OR, например = ИЛИ (ячейка («формат», $ A2) = «D1», ячейка («формат», $ A2) = «D2», ячейка («формат», $ A2) = «D3»)

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

Как выделить строки по определенной дате в определенном столбце

Предположим, у вас есть большая электронная таблица Excel, содержащая два столбца даты (B и C). Вы хотите выделить каждую строку с определенной датой, например 13 мая-14, в столбце C.

Чтобы применить условное форматирование Excel к определенной дате, необходимо сначала найти ее числовое значение . Как вы, наверное, знаете, Microsoft Excel хранит даты как последовательные порядковые номера, начиная с 1 января 1900 года.Итак, 1 января 1900 хранится как 1, 2 января 1900 хранится как 2… и 13 мая 14 как 41772.

Чтобы найти номер даты, щелкните ячейку правой кнопкой мыши, выберите Формат ячеек> Число и выберите формат Общий . Запишите число, которое вы видите, и нажмите Отмена , потому что вы действительно не хотите изменять формат даты.

На самом деле это была основная часть работы, и теперь вам нужно только создать правило условного форматирования для всей таблицы с помощью этой очень простой формулы: = $ C2 = 41772 .Формула подразумевает, что ваша таблица имеет заголовки, а строка 2 - это ваша первая строка с данными.

Альтернативный способ - использовать формулу DATEVALUE , которая преобразует дату в числовой формат, в котором она хранится, например = $ C2 = ДАТА ЗНАЧЕНИЕ ("13.05.2014")

Какую бы формулу вы ни использовали, она будет иметь одинаковый эффект:

Условное форматирование дат в Excel на основе текущей даты

Как вы, наверное, знаете, Microsoft Excel предоставляет функции СЕГОДНЯ () для различных вычислений на основе текущей даты.Вот лишь несколько примеров того, как вы можете использовать его для условного форматирования дат в Excel.

Пример 1. Выделите даты, равные, больше или меньше сегодняшнего дня

Для условного форматирования ячеек или целых строк на основе сегодняшней даты используйте функцию СЕГОДНЯ следующим образом:

Равно сегодня: = $ B2 = СЕГОДНЯ ()

Больше, чем сегодня: = $ B2> СЕГОДНЯ ()

Меньше, чем сегодня: = $ B2 <СЕГОДНЯ ()

Снимок экрана ниже демонстрирует приведенные выше правила в действии.Обращаем ваше внимание, что на момент написания СЕГОДНЯ было 12 июня 2014 года.


Пример 2. Условное форматирование дат в Excel на основе нескольких условий

Аналогичным образом вы можете использовать функцию СЕГОДНЯ в сочетании с другими функциями Excel для обработки более сложных сценариев. Например, вы можете захотеть, чтобы ваша формула условного форматирования даты Excel раскрашивала столбец Счет-фактура , когда Дата доставки равна или больше, чем сегодня, НО вы хотите, чтобы форматирование исчезло при вводе номера счета-фактуры.

Для этой задачи вам понадобится дополнительный столбец со следующей формулой (где E - ваш столбец Delivery , а F - столбец Invoice ):

= ЕСЛИ (E2> = СЕГОДНЯ (), ЕСЛИ (F2 = "", 1, 0), 0)

Если дата доставки больше или равна текущей дате и в столбце «Счет-фактура» нет числа, формула возвращает 1, в противном случае - 0.

После этого вы создаете простое правило условного форматирования для столбца Invoice с формулой = $ G2 = 1 , где G - ваш дополнительный столбец.Конечно, позже вы сможете скрыть этот столбец.


Пример 3. Выделите предстоящие даты и задержки

Предположим, у вас есть расписание проекта в Excel, в котором перечислены задачи, их даты начала и продолжительность. Вы хотите, чтобы дата окончания для каждой задачи рассчитывалась автоматически. Дополнительная проблема заключается в том, что формула также должна учитывать выходные. Например, если дата начала - 13 июня 2014 года, а количество рабочих дней (Продолжительность) - 2, дата окончания должна быть 17 июня 2014 года, поскольку 14 июня и 15 июня - суббота и воскресенье. .

Для этого мы будем использовать функцию РАБДЕНЬ.INTL (начальная_дата, дни, [выходные], [праздники]) , точнее = РАБДЕНЬ.INTL (B2, C2,1) .


В формуле мы вводим 1 в качестве параметра 3 rd , поскольку он указывает субботу и воскресенье как праздничные дни. Вы можете использовать другое значение, если у вас разные выходные, например, пт и сб. Полный список значений выходных дней доступен здесь. При желании вы также можете использовать 4-й параметр [праздники], который представляет собой набор дат (диапазон ячеек), которые следует исключить из календаря рабочих дней.

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

• = AND ($ D2-TODAY ()> = 0, $ D2-TODAY () <= 7) - выделите все строки, в которых дата окончания (столбец D) находится в пределах следующих 7 дней . Эта формула действительно удобна, когда дело доходит до отслеживания предстоящих дат истечения срока действия или платежей.
• = И (СЕГОДНЯ () - $ D2> = 0, СЕГОДНЯ () - $ D2 <= 7) - выделите все строки, где Дата окончания (столбец D) находится в пределах за последние 7 дней .Вы можете использовать эту формулу для отслеживания последних просроченных платежей и других задержек.

Вот еще несколько примеров формул, которые можно применить к таблице выше:

= $ D2 <СЕГОДНЯ () - выделяет все прошедшие даты (то есть даты меньше текущей даты). Может использоваться для форматирования истекших подписок, просроченных платежей и т. Д.

= $ D2> СЕГОДНЯ () - выделяет все будущие даты (т.е. даты, которые больше текущей даты). Вы можете использовать его для выделения предстоящих событий.

Конечно, в зависимости от вашей конкретной задачи может быть бесконечное количество вариантов приведенных выше формул. Например:

= $ D2-TODAY ()> = 6 - выделяет даты, которые происходят через 6 или более дней.

= $ D2 = СЕГОДНЯ () - 14 - выделяет даты, наступившие ровно две недели назад.

Как выделить даты в диапазоне дат

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

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

Формулы для выделения прошедших дат

• Более 30 дней назад: = СЕГОДНЯ () - $ A2> 30
• 30–15 дней назад включительно: = И (СЕГОДНЯ () - $ A2> = 15, СЕГОДНЯ () - $ A2 <= 30)
• Менее 15 дней назад: = И (СЕГОДНЯ () - $ A2> = 1, СЕГОДНЯ () - $ A2 <15)

Текущая дата и любые будущие даты не окрашены.


Формулы для выделения будущих дат

• Произойдет более чем через 30 дней с этого момента: = $ A2-TODAY ()> 30
• Через 30–15 дней включительно: = AND ($ A2-TODAY ()> = 15, $ A2-TODAY () <= 30)
• Менее чем за 15 дней: = AND ($ A2-TODAY ()> = 1, $ A2-TODAY () <15)

Текущая и прошедшие даты не окрашиваются.


Как заштриховать промежутки и промежутки времени

В этом последнем примере мы собираемся использовать еще одну функцию даты Excel - DATEDIF (start_date, end_date, interval) .Эта функция вычисляет разницу между двумя датами на основе указанного интервала. Он отличается от всех других функций, которые мы обсуждали в этом руководстве, тем, что позволяет игнорировать месяцы или годы и вычислять разницу только между днями или месяцами, в зависимости от того, что вы выберете.

Не понимаете, как это может сработать? Подумайте об этом по-другому ... Предположим, у вас есть список дней рождения членов вашей семьи и друзей. Хотите знать, сколько дней осталось до их следующего дня рождения? Более того, сколько ровно дней осталось до годовщины свадьбы и других событий, которые вы бы не хотели пропустить? Легко!

Нужная вам формула (где A - ваш столбец Дата ):

= РАЗНДАТ (СЕГОДНЯ (), ДАТА ((ГОД (СЕГОДНЯ ()) + 1), МЕСЯЦ ($ A2), ДЕНЬ ($ A2)), «ярд»)

Тип интервала «ярд» в конце формулы используется для игнорирования лет и вычисления разницы только между днями.Полный список доступных типов интервалов можно найти здесь.

Наконечник. Если вы забыли или потеряли эту сложную формулу, вы можете использовать вместо нее эту простую: = 365-РАЗНДАТ ($ A2, СЕГОДНЯ (), «ярд») . Он дает точно такие же результаты, просто не забудьте заменить 365 на 366 в високосные годы 🙂

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

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


«Дней до следующего дня рождения» Excel Web App

Мы создали это приложение Excel Web App, чтобы показать вам приведенную выше формулу в действии. Просто введите свои события в 1-й столбец и измените соответствующие даты во 2-м столбце, чтобы поэкспериментировать с результатом.

Если вам интересно узнать, как создавать такие интерактивные электронные таблицы Excel, ознакомьтесь с этой статьей - Перемещение электронных таблиц Excel в Интернет.

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

Вас также может заинтересовать

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

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

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

Формулы Excel для условного форматирования на основе значения ячейки

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

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

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

Как создать правило условного форматирования с помощью формулы

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

1. Выберите ячейки, которые нужно отформатировать.Вы можете выбрать один столбец, несколько столбцов или всю таблицу, если хотите применить условный формат к строкам. Наконечник. Если вы планируете добавить больше данных в будущем и хотите, чтобы правило условного форматирования применялось к новым записям автоматически, вы можете:
   • Преобразование диапазона ячеек в таблицу ( Вставка вкладки> Таблица ). В этом случае условное форматирование будет автоматически применяться ко всем новым строкам.
   • Выберите несколько пустых строк под данными, скажем, 100 пустых строк.
2. На вкладке Home в группе Стили щелкните Условное форматирование> Новое правило…
   
3. В окне Новое правило форматирования выберите Используйте формулу, чтобы определить, какие ячейки нужно отформатировать .
4. Введите формулу в соответствующее поле.
5. Нажмите кнопку Формат… , чтобы выбрать собственный формат.
   
6. Переключение между шрифтом Font , Border и Заполните вкладки и поиграйте с различными параметрами, такими как стиль шрифта, цвет узора и эффекты заливки, чтобы настроить формат, который лучше всего подходит для вас.Если стандартной палитры недостаточно, щелкните Другие цвета… и выберите любой цвет RGB или HSL по своему вкусу. Когда закончите, нажмите кнопку OK .
   
7. Убедитесь, что в разделе Preview отображается нужный формат, и если это так, нажмите кнопку OK , чтобы сохранить правило. Если вас не совсем устраивает предварительный просмотр формата, нажмите кнопку Format… еще раз и внесите изменения.
   

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

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

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

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

Формулы для сравнения значений (числа и текст)

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

Состояние	Пример формулы
Равно	= B2 = 10
Не равно	= $ B2 <> 10
Больше	= $ B2> 10
Больше или равно	= $ B2> = 10
Менее	= B2 <10
Меньше или равно	= $ B2 <= 10
Между	= И ($ B2> 5, $ B2 <10)

На снимке экрана ниже показан пример формулы Больше, чем , которая выделяет названия продуктов в столбце A, если количество товаров на складе (столбец C) больше 0.Обратите внимание, что формула применима только к столбцу A ($ A $ 2: $ A $ 8). Но если вы выберете всю таблицу (в нашем случае $ A $ 2: $ E $ 8), это выделит целые строки на основе значения в столбце C.

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

= $ A2 <$ B2 - форматировать ячейки или строки, если значение в столбце A меньше, чем соответствующее значение в столбце B.

= $ A2 = $ B2 - форматировать ячейки или строки, если значения в столбцах A и B совпадают.

= $ A2 <> $ B2 - форматировать ячейки или строки, если значение в столбце A не такое же, как в столбце B.

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

Формулы И и ИЛИ

Если вы хотите отформатировать таблицу Excel на основе 2 или более условий, используйте функцию = И или = ИЛИ:

Состояние	Формула	Описание
Если оба условия соблюдены	= И ($ B2 <$ C2, $ C2 <$ D2)	Форматирует ячейки, если значение в столбце B меньше, чем в столбце C, и , если значение в столбце C меньше, чем в столбце D.
При выполнении одного из условий	= ИЛИ ($ B2 <$ C2, $ C2 <$ D2)	Форматирует ячейки, если значение в столбце B меньше, чем в столбце C, или , если значение в столбце C меньше, чем в столбце D.

На скриншоте ниже мы используем формулу = AND ($ C2> 0, $ D2 = "Worldwide") , чтобы изменить цвет фона строк, если количество товаров на складе (столбец C) больше чем 0, и если продукт доставляется по всему миру (столбец D).Обратите внимание, что формула работает с текстовыми значениями , а также с числами .

Естественно, вы можете использовать два, три или более условий в формулах = И и = ИЛИ.

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

Условное форматирование пустых и непустых ячеек

Я думаю, что все знают, как форматировать пустые, а не пустые ячейки в Excel - вы просто создаете новое правило типа « Форматировать только ячейки, которые содержат» и выбираете либо Пробелы , либо Без пробелов .

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

Формула для пробелов : = $ B2 = "" - форматирование выбранных ячеек / строк, если соответствующая ячейка в столбце B пуста.

Формула для непустых значений : = $ B2 <> "" - форматирование выбранных ячеек / строк, если соответствующая ячейка в столбце B не пуста.

Примечание. Формулы выше будут работать для ячеек, которые «визуально» пусты или не пусты. Если вы используете какую-либо функцию Excel, которая возвращает пустую строку, например = if (false, "OK", "") , и вы не хотите, чтобы такие ячейки обрабатывались как пустые, используйте вместо них следующие формулы = isblank (A1) = true или = isblank (A1) = false для форматирования пустых и непустых ячеек соответственно.

А вот пример того, как можно использовать приведенные выше формулы на практике.Предположим, у вас есть столбец (B), который представляет собой « Дата продажи », а другой столбец (C) « Поставка ». Эти 2 столбца имеют значение только в том случае, если была совершена продажа и доставлен товар. Итак, вы хотите, чтобы вся строка стала оранжевой, когда вы совершите продажу; и когда товар доставлен, соответствующая строка должна стать зеленой. Для этого вам необходимо создать 2 правила условного форматирования со следующими формулами:

• Оранжевые строки (ячейка в столбце B не пуста): = $ B2 <> ""
• Зеленые строки (ячейки в столбце B и столбце C не пусты): = AND ($ B2 <> "", $ C2 <> "")

Еще одна вещь, которую вам нужно сделать, это переместить второе правило вверх и установить флажок Остановить, если истинно рядом с этим правилом:

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

Формулы Excel для работы с текстовыми значениями

Если вы хотите отформатировать определенный столбец (столбцы), когда другая ячейка в той же строке содержит определенное слово, вы можете использовать формулу, описанную в одном из предыдущих примеров (например, = $ D2 = "Worldwide"). Однако это будет работать только для точного совпадения .

Для частичного совпадения необходимо использовать либо ПОИСК (без учета регистра), либо НАЙТИ (с учетом регистра).

Например, для форматирования выбранных ячеек или строк, если соответствующая ячейка в столбце D содержит слово « Worldwide », используйте формулу ниже. Эта формула найдет все такие ячейки, независимо от того, где в ячейке находится указанный текст, включая « Ships Worldwide », « Worldwide, за исключением… » и т. Д .:

= ПОИСК ("Весь мир", $ D2)> 0

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

= ПОИСК ("Весь мир", $ D2)> 1

Формулы Excel для выделения дубликатов

Если вашей задачей является условное форматирование ячеек с повторяющимися значениями, вы можете воспользоваться предопределенным правилом, доступным в разделе Условное форматирование> Правила выделения ячеек> Повторяющиеся значения… В следующей статье содержится подробное руководство по использованию этой функции: Как автоматически выделять дубликаты в Excel.

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

Выделить дубликаты, включая 1 ^st вхождений

= СЧЕТЕСЛИ ($ A $ 2: $ A $ 10, $ A2)> 1 - эта формула находит повторяющиеся значения в указанном диапазоне в столбце A (A2: A10 в нашем случае), включая первые вхождения.

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

Выделить дубликаты без 1 ^st вхождений

Чтобы игнорировать первое вхождение и выделять только последующие повторяющиеся значения, используйте эту формулу: = СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> 1

Выделить последовательные дубликаты в Excel

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

• Выберите столбец, в котором вы хотите выделить дубликаты, без заголовка столбца .
• Создайте правило (я) условного форматирования, используя эти простые формулы:
  Правило 1 (синий): = $ A1 = $ A2 - выделяет 2 ^{-го вхождения} и все последующие вхождения, если таковые имеются.
  Правило 2 (зеленый): = $ A2 = $ A3 - выделяет 1 ^st вхождение.

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

Важно! Для правильной работы формул важно, чтобы Правило 1, выделяющее 2 ^и и все последующие повторяющиеся вхождения, было первым правилом в списке, особенно если вы используете два разных цвета.

Выделить повторяющиеся строки

Если вы хотите применить условный формат, когда повторяющиеся значения встречаются в двух или более столбцах, вам нужно будет добавить в таблицу дополнительный столбец, в котором вы объедините значения из ключевых столбцов, используя простую формулу, подобную этой = A2 & B2 .После этого вы применяете правило, используя любой вариант формулы СЧЁТЕСЛИ для дубликатов (с 1 ^st вхождениями или без них). Естественно, вы можете скрыть дополнительный столбец после создания правила.

В качестве альтернативы можно использовать функцию СЧЁТЕСЛИМН, которая поддерживает несколько критериев в одной формуле. В этом случае вспомогательный столбец вам не понадобится.

В этом примере, чтобы выделить повторяющиеся строки с первыми вхождениями , создайте правило со следующей формулой:
= COUNTIFS ($ A $ 2: $ A $ 11, $ A2, $ B $ 2: $ B $ 11, $ B2) > 1

Чтобы выделить повторяющиеся строки без первых вхождений , используйте эту формулу:
= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2, $ B $ 2: $ B2, $ B2)> 1

Сравнить 2 столбца на наличие дубликатов

Одна из наиболее частых задач в Excel - проверка двух столбцов на наличие повторяющихся значений - i.е. найти и выделить значения, которые существуют в обоих столбцах. Для этого вам нужно будет создать правило условного форматирования Excel для каждого столбца с комбинацией функций = ISERROR () и = MATCH () :

для столбца A: = ISERROR (MATCH (A1, $ B $ 1: $ B $ 10000,0)) = FALSE

для столбца B: = ISERROR (MATCH (B1, $ A $ 1: $ A $ 10000,0)) = FALSE

Примечание. Для правильной работы таких условных формул очень важно применять правила ко всем столбцам, например.грамм. = $ A: $ A и = $ B: $ B .

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

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

Формулы для выделения значений выше или ниже среднего

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

Например, вы можете использовать формулу = $ E2 для условного форматирования строк, в которых количество продаж ниже среднего, как показано на снимке экрана ниже. Если вы ищете обратное, то есть, чтобы затенить продукты, эффективность которых выше среднего, замените "<" на ">" в формуле: = $ E2> AVERAGE ($ E $ 2: $ E $ 8) .


Как выделить ближайшее значение в Excel

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

Пример 1. Найдите ближайшее значение, включая точное совпадение

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

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

= МИН (АБС (B2: D13- (0)))

В приведенной выше формуле B2: D13 - это диапазон ячеек, а 0 - это число, для которого вы хотите найти ближайшее соответствие. Например, если вы ищете значение, наиболее близкое к 5, формула изменится на: = MIN (ABS (B2: D13- (5)))

Примечание. Это формула массива , поэтому вам нужно нажать Ctrl + Shift + Enter вместо простого ввода Enter, чтобы завершить его.

А теперь вы создаете правило условного форматирования со следующей формулой, где B3 - это верхняя правая ячейка в вашем диапазоне, а $ C $ 2 в ячейке с приведенной выше формулой массива:

= ИЛИ (B3 = 0- $ C $ 2, B3 = 0 + $ C $ 2)

Обратите внимание на использование абсолютных ссылок в адресе ячейки, содержащей формулу массива ($ C $ 2), поскольку эта ячейка является постоянной.Кроме того, вам нужно заменить 0 на число, для которого вы хотите выделить ближайшее совпадение. Например, если мы хотим выделить ближайшее к 5 значение, формула изменится на: = ИЛИ (B3 = 5- $ C $ 2, B3 = 5 + $ C $ 2)

Пример 2. Выделите значение, наиболее близкое к заданному, но НЕ точное соответствие

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

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

= МИН (АБС (B3: C13- (0)) + (10 ^ 0 * (B3: C13 = 0)))

Не забудьте нажать Ctrl + Shift + Enter после того, как вы закончите вводить формулу массива.

Формула условного форматирования такая же, как в приведенном выше примере:

= ИЛИ (B3 = 0- $ C $ 2, B3 = 0 + $ C $ 2)

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


Если вы хотите найти значение, ближайшее к какому-либо другому числу в листе Excel, просто замените «0» нужным числом как в массиве, так и в формулах условного форматирования.

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

Почему условное форматирование в Excel работает неправильно?

Если ваше правило условного форматирования не работает должным образом, хотя формула явно верна, не расстраивайтесь! Скорее всего, это не из-за какой-то странной ошибки в условном форматировании Excel, а из-за крошечной ошибки, не очевидной на первый взгляд.Попробуйте выполнить 6 простых шагов по устранению неполадок ниже, и я уверен, что ваша формула заработает:

1. Правильно используйте абсолютные и относительные адреса ячеек. Очень сложно вывести общее правило, которое будет работать в 100% случаев. Но чаще всего вы будете использовать абсолютный столбец (с $) и относительную строку (без $) в ссылках на ячейки, например = $ A1> 1 .

   Имейте в виду, что формулы = A1 = 1 , = $ A $ 1 = 1 и = A $ 1 = 1 дадут разные результаты.Если вы не уверены, какой из них правильный в вашем случае, вы можете попробовать все 🙂 Для получения дополнительной информации см. Относительные и абсолютные ссылки на ячейки в условном форматировании Excel.

2. Проверьте примененный диапазон. Проверьте, применяется ли ваше правило условного форматирования к правильному диапазону ячеек. Эмпирическое правило таково - выберите все ячейки / строки, которые вы хотите отформатировать, но не включайте заголовки столбцов.
3. Запишите формулу для верхней левой ячейки. В правилах условного форматирования ссылки на ячейки указываются относительно самой левой верхней ячейки в применяемом диапазоне.Поэтому всегда пишите формулу условного форматирования для 1-й строки с данными.

   Например, если ваши данные начинаются со строки 2, вы помещаете = A $ 2 = 10 , чтобы выделить ячейки со значениями, равными 10, в всех строках . Распространенная ошибка - всегда использовать ссылку на первую строку (например, = A $ 1 = 10 ). Помните, что вы ссылаетесь на строку 1 в формуле только в том случае, если ваша таблица не имеет заголовков и ваши данные действительно начинаются в строке 1. Наиболее очевидным признаком этого случая является то, что правило работает, но форматирует значения не в тех строках, которые должны .

4. Проверьте созданное вами правило. Еще раз проверьте правило в диспетчере правил условного форматирования. Иногда без всякой причины Microsoft Excel искажает только что созданное правило. Итак, если правило не работает, перейдите к Условное форматирование> Управление правилами и проверьте формулу и диапазон, к которому она применяется. Если вы скопировали формулу из Интернета или другого внешнего источника, убедитесь, что используются прямые кавычки .
5. Отрегулируйте ссылки на ячейки при копировании правила. Если вы копируете условное форматирование Excel с помощью Format Painter, не забудьте настроить все ссылки на ячейки в формуле.
6. Разбиение сложных формул на простые элементы. Если вы используете сложную формулу Excel, включающую несколько различных функций, разделите ее на простые элементы и проверьте каждую функцию по отдельности.

И, наконец, если вы испробовали все шаги, но ваше правило условного форматирования все еще работает неправильно, напишите мне в комментариях, и мы постараемся разобраться в этом вместе 🙂

В моей следующей статье мы рассмотрим возможности условного форматирования дат в Excel.Увидимся на следующей неделе и спасибо за чтение!

Вас также может заинтересовать

Условное форматирование в Excel - Easy Excel Tutorial

Правила выделения ячеек | Очистить правила | Правила сверху / снизу | Условное форматирование с формулами

Условное форматирование в Excel позволяет выделять ячейки определенным цветом в зависимости от значения ячейки.

Правила выделения ячеек

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

1. Выберите диапазон A1: A10.

2. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

3. Щелкните Выделить правила ячеек, больше.

4. Введите значение 80 и выберите стиль форматирования.

5. Щелкните OK.

Результат. Excel выделяет ячейки, размер которых превышает 80.

6.Измените значение ячейки A1 на 81.

Результат. Excel автоматически изменяет формат ячейки A1.

Примечание: вы также можете использовать эту категорию (см. Шаг 3) для выделения ячеек, которые меньше значения, между двумя значениями, равными значению, ячеек, содержащих определенный текст, даты (сегодня, на прошлой неделе, в следующем месяце и т. Д.) .), повторяющиеся или уникальные значения.

Очистить правила

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

1. Выберите диапазон A1: A10.

2. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

3. Щелкните «Очистить правила», «Очистить правила из выбранных ячеек».

Верхние / нижние правила

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

1.Выберите диапазон A1: A10.

2. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

3. Щелкните Правила верхних / нижних, выше среднего.

4. Выберите стиль форматирования.

5. Щелкните OK.

Результат. Excel вычисляет среднее значение (42,5) и форматирует ячейки, превышающие это среднее значение.

Примечание: вы также можете использовать эту категорию (см. Шаг 3), чтобы выделить первые n элементов, верхние n процентов, нижние n элементов, нижние n процентов или ячейки, которые находятся ниже среднего.

Условное форматирование с формулами

Поднимите свои навыки работы с Excel на новый уровень и используйте формулу, чтобы определить, какие ячейки нужно форматировать. Формулы, применяющие условное форматирование, должны иметь значение ИСТИНА или ЛОЖЬ.

1. Выберите диапазон A1: E5.

2. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

3.Щелкните Новое правило.

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

5. Введите формулу = ISODD (A1)

6. Выберите стиль форматирования и нажмите OK.

Результат. Excel выделяет все нечетные числа.

Объяснение: всегда записывайте формулу для верхней левой ячейки в выбранном диапазоне.Excel автоматически копирует формулу в другие ячейки. Таким образом, ячейка A2 содержит формулу = ISODD (A2), ячейка A3 содержит формулу = ISODD (A3) и т. Д.

Вот еще пример.

7. Выберите диапазон A2: D7.

8. Повторите шаги 2–4 выше.

9. Введите формулу = $ C2 = "США"

10. Выберите стиль форматирования и нажмите OK.

Результат.Excel выделяет все заказы в США.

Объяснение: мы исправили ссылку на столбец C, поместив символ $ перед буквой столбца ($ C2). В результате ячейки B2, C2 и ячейка D2 также содержат формулу = $ C2 = "USA", ячейки A3, B3, C3 и D3 содержат формулу = $ C3 = "USA" и т. Д.

.

No related posts.