Меню

Как ячейки таблицы придать цвет



Таблицы и стили

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

Цвет фона ячеек

Цвет фона одновременно всех ячеек таблицы устанавливается через свойство background , которое применяется к селектору TABLE . При этом следует помнить о правилах использования стилей, в частности, наследовании свойств элементов. Хотя свойство background не наследуется, для ячеек значением фона по умолчанию выступает transparent , т.е. прозрачность, поэтому эффект заливки фона получается и у ячеек. Если одновременно с TABLE задать цвет у селектора TD или TH , то этот цвет будет установлен в качестве фона ячейки (пример 2.3).

Пример 2.3. Цвет фона

XHTML 1.0 CSS 2.1 IE Cr Op Sa Fx

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

) и красный у заголовка (тег

). Это связано с тем, что стиль для селектора TH не определен, поэтому «просвечивается» фон родителя, в данном случае, селектора TABLE . А цвет для селектора TD указан явно, вот ячейки и «заливаются» синим цветом.

Результат данного примера показан на рис. 2.4.

Рис. 2.4. Изменение цвета фона

Поля внутри ячеек

Полем называется расстояние между краем содержимого ячейки и её границей. Обычно для этой цели применяется атрибут cellpadding тега

. Он определяет значение поля в пикселах со всех сторон ячейки. Допускается использовать стилевое свойство padding , добавляя его к селектору TD , как показано в примере 2.4.

Пример 2.4. Поля в таблицах

XHTML 1.0 CSS 2.1 IE Cr Op Sa Fx

В данном примере с помощью группирования селекторов поля установлены одновременно для селектора TD и TH . Результат примера показан на рис. 2.5.

Рис. 2.5. Поля в ячейках

Если применяется стилевое свойство padding для ячеек таблицы, то действие атрибута cellpadding тега

игнорируется.

Расстояние между ячейками

Для изменения расстояния между ячейками применяется атрибут cellspacing тега

. Влияние этого атрибута хорошо заметно при использовании границ вокруг ячеек или при заливке ячеек цветом, который выделяется на фоне страницы. В качестве замены cellspacing выступает стилевое свойство border-spacing , оно устанавливает расстояние между границами ячеек. Одно значение устанавливает одновременно расстояние по вертикали и горизонтали между границами ячеек. Если значений у этого свойства два, то первое определяет горизонтальное расстояние (т.е. слева и справа от ячейки), а второе — вертикальное (сверху и снизу).

Свойство border-spacing действует только в том случае, если для селектора TABLE не задано свойство border-collapse со значением collapse (пример 2.5).

Пример 2.5. Расстояние между границами ячеек

XHTML 1.0 CSS 2.1 IE 7 IE 8+ Cr Op Sa Fx

Результат данного примера показан на рис. 2.6.

Рис. 2.6. Вид таблицы при использовании border-spacing

Браузер Internet Explorer до седьмой версии включительно не поддерживает свойство border-spacing , поэтому в этом браузере для таблиц будет применяться значение cellspacing заданное по умолчанию (обычно оно равно 2px).

При добавлении к селектору TABLE свойства border-collapse со значением collapse , атрибут cellspacing игнорируется, а значение border-spacing обнуляется.

Границы и рамки

По умолчанию границы в таблице изначально нет, а её добавление происходит с помощью атрибута border тега

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

Использование атрибута cellspacing

Известно, что атрибут cellspacing тега

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

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

Применение свойства border

Стилевое свойство border одновременно устанавливает цвет границы, её стиль и толщину вокруг элемента. Когда требуется создать отдельные линии на разных сторонах, лучше использовать производные — border-left , border-right , border-top и border-bottom , эти свойства соответственно определяют границу слева, справа, сверху и снизу.

Применяя свойство border к селектору TABLE , мы добавляем рамку вокруг таблицы в целом, а к селектору TD или TH — рамку вокруг ячеек (пример 2.6).

Пример 2.6. Добавление двойной рамки

XHTML 1.0 CSS 2.1 IE Cr Op Sa Fx

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

Рис. 2.7. Граница вокруг таблицы и ячеек

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

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

, то получим не двойные, а одинарные линии, но удвоенной толщины. Для изменения указанной особенности применяется стилевое свойство border-collapse со значением collapse , которое добавляется к селектору TABLE (пример 2.7).

Пример 2.7. Создание одинарной рамки

XHTML 1.0 CSS 2.1 IE Cr Op Sa Fx

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

Рис. 2.8. Граница вокруг таблицы

Выравнивание содержимого ячеек

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

, он определяет заголовок, в котором выравнивание происходит по центру. Чтобы изменить способ выравнивания применяется стилевое свойство text-align (пример 2.8).

Пример 2.8. Выравнивание содержимого ячеек по горизонтали

XHTML 1.0 CSS 2.1 IE Cr Op Sa Fx

В данном примере содержимое тега

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

— по центру. Результат примера показан ниже (рис. 2.9).

Рис. 2.9. Выравнивание текста в ячейках

Выравнивание по вертикали в ячейке всегда происходит по её центру, если это не оговорено особо. Это не всегда удобно, особенно для таблиц, у которых содержимое ячеек различается по высоте. В таком случае выравнивание устанавливают по верхнему краю ячейки с помощью свойства vertical-align , как показано в примере 2.9.

Пример 2.9. Выравнивание содержимого ячеек по вертикали

XHTML 1.0 CSS 2.1 IE Cr Op Sa Fx

В данном примере устанавливается высота заголовка

как 40 пикселов и выравнивание текста происходит по нижнему краю. Результат примера показан на рис. 2.10.

Рис. 2.10. Выравнивание текста в ячейках

Пустые ячейки

Браузеры иначе отображают ячейку, внутри которой ничего нет. «Ничего» в данном случае означает, что внутрь ячейки не добавили ни рисунок, ни текст, причём пробел в расчёт не принимается. Естественно, вид ячеек различается только в том случае, если вокруг них установлена граница. При использовании невидимой рамки, вид ячеек, независимо от того, есть в них что-нибудь или нет, совпадает.

Старые браузеры не отображали цвет фона пустых ячеек вида , поэтому в том случае, когда требовалось оставить ячейку без содержимого, но отобразить цвет фона, внутрь ячейки добавляли неразделяемый пробел ( ). Пробел не всегда подходит, особенно когда нужно установить высоту ячейки 1–2 пиксела, из-за чего широкое распространение получил однопиксельный прозрачный рисунок. Действительно, такой рисунок можно масштабировать на свое усмотрение, но он на веб-странице никак не отображается.

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

Для управления видом пустых ячеек используется свойство empty-cells , при значении hide граница и фон в пустых ячейках не отображается. Если все ячейки в строке пустые, то строка прячется целиком. Ячейка считается пустой в следующих случаях:

  • нет вообще никаких символов;
  • в ячейке содержится только перевод строки, символ табуляции или пробел;
  • значение visibility установлено как hidden .

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

Пример 2.10. Пустые ячейки

XHTML 1.0 CSS 2.1 IE Cr Op Sa Fx

Вид таблицы в браузере Safari показан на рис. 2.11а. Та же таблица в браузере IE7 продемонстрирована на рис. 2.11б.

а. В браузере Safari, Firefox, Opera, IE8, IE9

Источник

Как изменить цвет ячейки в зависимости от значения в Excel?

В этой статье вы найдете 13 примеров как изменить цвет фона ячеек в зависимости от значения в Excel 2016, 2013 и 2010. Кроме того, вы узнаете, как использовать формулы Excel для изменения цвета пустых ячеек или ячеек с ошибками в формулах.

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

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

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

Цветовые шкалы и гистограммы.

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

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

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

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

Как можно использовать значки?

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

Итак, вновь переходим в меню. Думаю, мы это уже умеем делать 😊. Далее выбираем первый пункт (1) – форматировать на основании значений. Далее в меню «Стиль формата» (2) ищем пункт «Наборы значков». Выберите тот набор, который вам больше нравится. (3) Далее устанавливаем правила для каждого значка. Думаю, здесь для вас ничего сложного не будет. Я установил границы в 25,50 и 75 процентов (4). Здесь же вы можете выбрать индивидуальное оформление каждого значка.

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

Как выделить с ячейки с текстом?

Если нам нужно изменить цвет ячейки в зависимости от того, содержится в ней текст или число? Действуем по прежней схеме.

Выделяем A1:B10, затем щелкнем на вкладке Главная — – Создать правило – Использовать формулу.

Она будет выглядеть так:

Функция ЕТЕКСТ проверяет на наличие именно текста. Если условие выполнено, клетка будет закрашена.

Просто, не правда ли?

Изменяем цвет ячеек с числами.

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

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

Содержимое A1 подставляется в качестве условия в формулу СЧЕТЕСЛИ. Результатом может быть только ноль или единица. А единица соответствует логическому ИСТИНА. Вот тогда-то и применяется установленный нами формат (в нашем случае – белый шрифт на синем фоне). И так перебираем все содержимое нашего диапазона.

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

Изменение цвета ячейки по начальным буквам.

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

Определим, где мы будем выделять найденное – в A3:F19. Далее в правиле условного форматирования используем выражение:

Поясним, как это работает. В A1 находится условие поиска – первые несколько букв слова. При помощи ДЛСТР($A$1) определяем длину этой строки, то есть сколько букв в ней. Затем при помощи функции ЛЕВСИМВ в каждой из ячеек нашего диапазона берем рассчитанное ранее количество букв начиная с первой слева, и сравниваем с A1. То есть, «отрезаем» слева кусочек такой же длины, как А1, и сравниваем с ним. Если есть совпадение, тогда выделяем их.

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

Выделяем ячейки, содержащие более 1 слова.

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

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

Разберем, как это работает. Логика здесь не сложная:

  1. Между словами всегда есть пробелы.
  2. Но, поскольку пробелы в начале и в конце текста нам не нужны, избавляемся от них при помощи функции СЖПРОБЕЛЫ(А1).
  3. Подсчитаем количество символов в получившемся в п.2 тексте при помощи ДЛСТР. Запишем ДЛСТР(СЖПРОБЕЛЫ(A1)).
  4. Удалим все пробелы из текста при помощи функции ПОДСТАВИТЬ, которая заменит пробел на пустой символ “”, то есть фактически удалит его: ПОДСТАВИТЬ(A1;» «;»»)
  5. Считаем количество символов в тексте, получившемся в п.4, используя функцию ДЛСТР: ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)).
  6. Сравниваем результаты, получившиеся в п.3 и п.5 путем их вычитания. Если результатом будет ноль, значит текст состоял из одного слова. Если получится число больше нуля, значит, были пробелы между словами. И, следовательно, текст состоял более чем из одного слова.

Что нам и было необходимо. Их и закрасим.

Выделение лишних пробелов.

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

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

Поскольку проверять мы будем в D4,D6, D8 и D10, выделим их и правило форматирования запишем в виде формулы

Функция СЖПРОБЕЛЫ убирает из текста начальные и конечные пробелы. И если после такого «сжатия» оказалось, что текст «до» не совпадает с текстом «после», значит, имеются лишние пробелы в начале или в конце. Такие данные и будут соответствующим образом помечены.

Достаточно просто, но весьма полезно.

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

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

Начнем с простого способа.

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

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

Итак, обратите внимание на дату отгрузки.

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

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

  1. Сегодняшняя или завтрашняя дата (1):

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

Ну а с датами, которые уже прошли, мы просто ничего не делаем. Они остаются «естественного» цвета.

И еще один пример. Выделим выходные дни.

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

Давайте разбираться, как это работает.

Если клетка пустая, то возвращаем ЛОЖЬ и не меняем ее вид. В этом нам поможет функция ЕПУСТО, которая станет условием в функции ЕСЛИ.

=ЕСЛИ(ЕПУСТО(A1);ЛОЖЬ;[условие если не пустая])

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

ДЕНЬНЕД(A1;2)=6 и ДЕНЬНЕД(A1;2)=7

Функция ДЕННЕД с аргументом 2 помогает нам определить номер дня недели по дате. 6 и 7 день – выходные в привычном для нас исчислении (суббота и воскресенье).

Объединяем эти два выражения при помощи ИЛИ, что означает, что нам достаточно выполнения хотя бы одного из них. Получается

ИЛИ(ДЕНЬНЕД(A1;2)=6;ДЕНЬНЕД(A1;2)=7)

В итоге, выходные дни будут выделены так, как мы определим при помощи кнопки Формат.

Чтобы перепроверить себя, давайте используем столбец В. В B1 запишите формулу =А1 и скопируйте ее вниз для всех дат. Первоначально получится копия столбца А.

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

Выделите их и нажмите комбинацию клавиш CTRL и 1. В появившемся окне выберите «Все форматы» и в поле «Тип» впишите 4 латинских буквы d (как на рисунке). И вот у вас дата преобразована в день недели без всяких формул!

И, как можете сами убедиться, что нами выделены именно субботы и воскресенья.

Как скрыть содержимое ячейки по условию?

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

Разберем небольшой пример:

Итог появляется только тогда, когда заполнены все клетки. И вот как сделана строка итогов:

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

Для диапазона B6:C6 установите желаемый фон ячеек, а цвет шрифта установите на «Авто» либо принудительно установите белый.

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

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

Подсветка ячеек с формулами.

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

Проверим наличие формулы в ячейке при помощи функции ЕФОРМУЛА (ISFORMULA).

Обратите внимание, что абсолютные ссылки здесь не нужны.

В результате клетки, содержащие формулы, будут выделены.

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

Используем функцию НЕ (NOT), которая изменит логический результат на противоположный:

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

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

Выделение пустых ячеек либо с ошибками.

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

Выделяем всю таблицу, и затем вновь используем формулу в правиле условного форматирования. Нам будут нужны две функции: ЕОШИБКА (IsError) и ЕПУСТО (IsBlank).

Можно создать два условия:

Либо объединить их в одно при помощи ИЛИ:

Естественно, диапазоны здесь вы указываете свои.

Нажмите кнопку «Формат» и выберите подходящий для вас вариант оформления.

Подсветка недопустимых значений.

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

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

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

Итак, на втором листе City есть список допустимых названий.

Мы должны изменить цвет D5 на красный, если выполняется 2 условия:

1. значение не совпадает с допустимыми;

2. она не пустая.

Чтобы проверить первое условие, в правиле условного форматирования можно использовать функцию СЧЕТЕСЛИ (COUNTIF).

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

Чтобы проверить второе условие, используем функцию ЕПУСТО (ISBLASNK), которая проверяет, является ли ячейка пустой, либо там что-то записано.

Но это проверка на то, что она пустая. Чтобы сделать проверку на то, что непустая, используем функцию НЕ (NOT). Она меняет результат выполнения логической функции на противоположное: ИСТИНА на ЛОЖЬ и наоборот. Выглядит проверка «от обратного» так:

и чтобы оба эти условия выполнялись, объединим их функцией И (AND)

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

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

Меняем вид ячейки в зависимости от прочих ячеек.

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

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

Обратите внимание: в первую строку мы вводим именно даты, а не пишем руками название месяца и года. Затем применяем к B1:M1 специальный формат представления даты.

Для этого выделите этот диапазон при помощи мыши и затем в меню Главная – Число – Формат откройте выпадающий список с всевозможными форматами. Выберите последний пункт «Другие числовые форматы». Откроется окно, как показано на картинке. Или же просто нажмите комбинацию клавиш CTRL+1.

Слева в окошке «Числовые форматы» выберите в самом низу «все форматы». И затем в поле «Тип» впишите:

mmmm yyyy

Нажимаем ОК и получаем новый формат даты.

Теперь давайте выделим текущий месяц.

Выбираем все столбцы нашей таблицы с датами. В данном случае это диапазон B2:M6. Затем открываем уже знакомое нам меню функции «Условное форматирование».

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

Кстати, текущий месяц на момент написания этого материала – декабрь, то есть 12-й.

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

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

Посмотрите, как это будет выглядеть в декабре.

Изменять заливку мы будем в A2:A6. Но проверять выполнение условия будем в диапазоне B2:M6.

Итак, выделяем мышью A2:A6 и открываем меню – Создать правило – Использовать формулу.

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

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

В качестве условия используем “>0”, поскольку, если заказ был, то какая-то сумма должна быть по нему указана. Кстати, не забудьте это условие заключить в двойные кавычки, чтобы не было ошибки.

Теперь разберемся с первым условием. Для наглядности запишем его здесь:

Чтобы возвратить диапазон данных, используем функцию СМЕЩ. Схема ее работы показана на рисунке выше. Перво-наперво нужно указать начальную точку, с которой мы начнем все действия. Пусть это будет $A2. Вновь мы делаем абсолютную ссылку на столбец, чтобы при проверке условия следующей была A3, затем A4 и так далее.

Далее мы должны указать, на сколько строк вниз нужно опуститься. Поскольку нас интересует текущая строка, то ставим 0.

Теперь нужно переместиться вправо на какое-то количество столбцов, где и будет начало (верхний левый угол) нашего диапазона. Поскольку кроме текущего месяца нас интересуют заказы еще в двух предыдущих (так всего будет 3 месяца), то смещаемся вправо на количество столбцов, равное номеру текущего месяца минус 2. Если у нас сейчас 12-й месяц, то делаем 10 шагов вправо и попадаем на октябрь, то есть столбец К. Это и есть начало нашего диапазона данных.

Следующее – указываем высоту диапазона. Поскольку интересует только текущая строка, то ставим 1.

Ну и, наконец, нужно определить, на сколько столбцов вправо будет продолжаться наш подсчет заказов. Ответ следует из нашего условия – на 3 месяца, то есть берем К, L, M. С октября по декабрь включительно – будет K2:M2. Поскольку заказов нет, то СЧЕТЕСЛИ возвратит 0. В результате условие сработает, и А2 окрасится красным. А в третьей строке в K3:M3 есть данные, поэтому А3 останется в прежнем виде.

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

Итак, мы с вами разобрали, как в Экселе менять цвет ячейки в зависимости от значения. Если остались вопросы — пишите их в комментариях. Постараюсь ответить.

Источник

Читайте также:  Как понимают язык цвета

Слово "Цвет" и его описание © 2021
Внимание! Информация, опубликованная на сайте, носит исключительно ознакомительный характер и не является рекомендацией к применению. Обязательно проконсультируйтесь с вашим лечащим врачом!