Excel если значение то цвет ячейки

Сложение значений в зависимости от цвета ячеек в MS EXCEL

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

Функции для суммирования значений по цвету ячеек в EXCEL не существует (по крайней мере, в EXCEL 2016 и в более ранних версиях). Вероятно, подавляющему большинству пользователей это не требуется.

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

Необходимо сложить значения ячеек в зависимости от цвета фона. Основная задача: Как нам «объяснить» функции сложения, что нужно складывать значения, например, только зеленых ячеек?

Это можно сделать разными способами, приведем 3 из них: с помощью Автофильтра, Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ() и VBA.

С помощью Автофильтра (ручной метод)

  • Добавьте справа еще один столбец с заголовком Код цвета.
  • Выделите заголовки и нажмите CTRL+SHIFT+L, т.е. вызовите Автофильтр (подробнее здесь)
  • Вызовите меню Автофильтра, выберите зеленый цвет

  • Будут отображены только строки с зелеными ячейками
  • Введите напротив каждого «зеленого» значения число 1

  • Сделайте тоже для всех цветов

Введите формулу =СУММЕСЛИ(B7:B17;E7;A7:A17) как показано в файле примера (лист Фильтр) .

Для подсчета значений используйте функцию СЧЕТЕСЛИ() .

С помощью Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ()

Сразу предупрежу, что начинающему пользователю EXCEL будет сложно разобраться с этим и следующим разделом.

Идея заключается в том, чтобы автоматически вывести в соседнем столбце числовой код фона ячейки (в MS EXCEL все цвета имеют соответствующий числовой код). Для этого нам потребуется функция, которая может вернуть этот код. Ни одна обычная функция этого не умеет. Используем макрофункцию ПОЛУЧИТЬ.ЯЧЕЙКУ(), которая возвращает код цвета заливки ячейки (она может много, но нам потребуется только это ее свойство).

Примечание: Макрофункции — это набор функций к EXCEL 4-й версии, которые нельзя напрямую использовать на листе EXCEL современных версий, а можно использовать только в качестве Именованной формулы. Макрофункции — промежуточный вариант между обычными функциями и функциями VBA. Для работы с этими функциями требуется сохранить файл в формате с макросами *.xlsm

  • Сделайте активной ячейку В7(это важно, т.к. мы будем использовать относительную адресацию в формуле)
  • В Диспетчере имен введите формулу =ПОЛУЧИТЬ.ЯЧЕЙКУ(63;Макрофункция!A7)
  • Назовите ее Цвет

  • Закройте Диспетчер имен
  • Введите в ячейку В7формулу =Цвет и скопируйте ее вниз.

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

Макрофункция работает кривовато:

  • если вы измените цвет ячейки, то макрофункция не обновит значения кода (для этого нужно опять скопировать формулу из В7вниз или выделить ячейку, нажать клавишу F2 и затем ENTER)
  • функция возвращает только 56 цветов (так называемая палитра EXCEL), т.е. если цвета близки, например, зеленый и светло зеленый, то коды этих цветов могут совпасть. Подробнее об этом см. лист файла примера Colors . Как следствие, будут сложены значения из ячеек с разными цветами.

С помощью VBA

В файле примера на листе VBA приведено решение с помощью VBA. Решений может быть множество:

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

Значение функции ЕСЛИ в зависимости от цвета ячейки

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

Хочу чтоб если у ячейки Б1 был залит цвет(и желательно именно синий), истина 1 а если не залит(или залит но другой) то 0.

был бы рад помощи без макросов =)
подскажите пожалуйста.

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

Значение ячейки в зависимости от значения другой ячейки
Добрый день! Есть собственно вот такая задача: есть ячейка с цыфровым значением (предположим.

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

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

Текстовое значение ячейки в зависимости от параметрв
Доброе утро! Помогите разобраться с проблемой (файл во вложении) Задача — в зависимости от.

Первый раз вижу книгу с расположением столбцов справа налево и «лист» на иврите 🙂

Казанский,
Спасибо за ответ! Если сможешь объяснить что значит больше, ровно и два минуса «>=—»
и Зачем использовать И и ИЛИ условного форматирования?

А по поводу иврита, я ж в Израиле живу =)

Добавлено через 1 минуту
AlexM,

Спасибо так же и тебе ) если могу уточнить, что значит (B1<>«») и почему на это умножаем?

а можно вопрос по существу но не связан с темой?

скажем есть работник начал работать в 23 закончил в 10 утра. можно ли построить формулу что если из рабочей смены, хотя бы 2 часа попадают в интервал между 22:00-6:00 то эта ячейка окрашивается скажем синим.
как не писал это в если все время выходит что он принимает 2 часа как за 2 часа ночи а не просто 2 часа в целом как 120 минут.

привёл пример в формате екселя.
начало в 20:00 конец в 8:00 из 12 часов смены поскольку хотя бы 2 часа входят в интервал между 22:00-6:00 это считается ночной сменой, тогда 100% ставки будет 7 часов а остальных как по формуле. но если бы он отработал с 8:00 lj 20:00 то 100% ставки часов было бы 8 а не 7.

Закрасить ячейку по условию или формуле

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

Инструкция для Excel 2010


ВКЛЮЧИТЕ СУБТИТРЫ!

Как это сделать в Excel 2007


ВКЛЮЧИТЕ СУБТИТРЫ!
Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберем «Создать правило».

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

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

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

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

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

Обратили внимание, что статусы задаются выбором из выпадающего списка значений? Как делать такие списки, мы рассказывали в инструкции «Как в Excel сделать выпадающий список».

Читать еще:  Excel дату перевести в число

Как это сделать в Excel 2003


ВКЛЮЧИТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних.

Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон.
Но принцип действий тот же самый.
Покоряйте Excel и до новых встреч!

Комментарии:

  1. Svetlana — 27.06.2015 21:28

наконец-то узнала, как это можно сделать!

Виктор — 14.04.2016 17:23

Здравствуйте, а можно сделать условное форматирование столбца А с фразами по условию «Текст —- содержит» по нескольким словам, а лучше по столбцу В, состоящего из слов?

salam — 19.05.2016 16:24

Подскажите как подсвечивать ячеку В2 при условии если ячейка А2 не пустая?

Федя — 16.11.2016 14:39

Как задать цвет определенному значению в одной ячейки, например — вожу 5 — она будет красным цветом, вожу 4 — она станет зелёным цветом

Оля — 03.05.2017 12:12

подскажите, как заливать в гамме одного цвета с разными оттенками в столбике, если напр., если 100% — зеленый, 95- зеленый но светлее, 75 — еще светлее и т.д. заранее спасибо

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

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

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

Как изменить цвет строки на основании числового значения одной из ячеек

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

Мы хотим раскрасить различными цветами строки в зависимости от заказанного количества товара (значение в столбце Qty.), чтобы выделить самые важные заказы. Справиться с этой задачей нам поможет инструмент Excel – «Условное форматирование».

  1. Первым делом, выделим все ячейки, цвет заливки которых мы хотим изменить.
  2. Чтобы создать новое правило форматирования, нажимаем Главная >Условное форматирование >Создать правило (Home > Conditional Formatting > New rule).
  3. В появившемся диалоговом окне Создание правила форматирования (New Formatting Rule) выбираем вариант Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format), и ниже, в поле Форматировать значения, для которых следующая формула является истинной (Format values where this formula is true), вводим такое выражение:

Вместо C2 Вы можете ввести ссылку на другую ячейку Вашей таблицы, значение которой нужно использовать для проверки условия, а вместо 4 можете указать любое нужное число. Разумеется, в зависимости от поставленной задачи, Вы можете использовать операторы сравнения меньше ( =$C2
=$C2=4

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

  • Нажимаем кнопку Формат (Format) и переходим на вкладку Заливка (Fill), чтобы выбрать цвет фона ячеек. Если стандартных цветов недостаточно, нажмите кнопку Другие цвета (More Colors), выберите подходящий и дважды нажмите ОК.Таким же образом на остальных вкладках диалогового окна Формат ячеек (Format Cells) настраиваются другие параметры форматирования, такие как цвет шрифта или границы ячеек.
  • В поле Образец (Preview) показан результат выполнения созданного правила условного форматирования:
  • Если всё получилось так, как было задумано, и выбранный цвет устраивает, то жмём ОК, чтобы увидеть созданное правило в действии.Теперь, если значение в столбце Qty. больше 4, то соответствующая строка таблицы целиком станет голубой.
  • Как видите, изменять в Excel цвет целой строки на основании числового значения одной из ячеек – это совсем не сложно. Далее мы рассмотрим ещё несколько примеров формул и парочку хитростей для решения более сложных задач.

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

    В таблице из предыдущего примера, вероятно, было бы удобнее использовать разные цвета заливки, чтобы выделить строки, содержащие в столбце Qty. различные значения. К примеру, создать ещё одно правило условного форматирования для строк, содержащих значение 10 или больше, и выделить их розовым цветом. Для этого нам понадобится формула:

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

    1. На вкладке Главная (Home) в разделе Стили (Styles) нажмите Условное форматирование (Conditional Formatting) >Управление правилами (Manage Rules)
    2. В выпадающем списке Показать правила форматирования для (Show formatting rules for) выберите Этот лист (This worksheet). Если нужно изменить параметры только для правил на выделенном фрагменте, выберите вариант Текущий фрагмент (Current Selection).
    3. Выберите правило форматирования, которое должно быть применено первым, и при помощи стрелок переместите его вверх списка. Должно получиться вот так:Нажмите ОК, и строки в указанном фрагменте тут же изменят цвет, в соответствии с формулами в обоих правилах.

    Как изменить цвет строки на основании текстового значения одной из ячеек

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

    • Если срок доставки заказа находится в будущем (значение Due in X Days), то заливка таких ячеек должна быть оранжевой;
    • Если заказ доставлен (значение Delivered), то заливка таких ячеек должна быть зелёной;
    • Если срок доставки заказа находится в прошлом (значение Past Due), то заливка таких ячеек должна быть красной.

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

    С формулой для значений Delivered и Past Due всё понятно, она будет аналогичной формуле из нашего первого примера:

    =$E2=»Delivered»
    =$E2=»Past Due»

    Сложнее звучит задача для заказов, которые должны быть доставлены через Х дней (значение Due in X Days). Мы видим, что срок доставки для различных заказов составляет 1, 3, 5 или более дней, а это значит, что приведённая выше формула здесь не применима, так как она нацелена на точное значение.

    В данном случае удобно использовать функцию ПОИСК (SEARCH) и для нахождения частичного совпадения записать вот такую формулу:

    =ПОИСК(«Due in»;$E2)>0
    =SEARCH(«Due in»,$E2)>0

    В данной формуле E2 – это адрес ячейки, на основании значения которой мы применим правило условного форматирования; знак доллара $ нужен для того, чтобы применить формулу к целой строке; условие “>0” означает, что правило форматирования будет применено, если заданный текст (в нашем случае это “Due in”) будет найден.

    Подсказка: Если в формуле используется условие “>0“, то строка будет выделена цветом в каждом случае, когда в ключевой ячейке будет найден заданный текст, вне зависимости от того, где именно в ячейке он находится. В примере таблицы на рисунке ниже столбец Delivery (столбец F) может содержать текст “Urgent, Due in 6 Hours” (что в переводе означает – Срочно, доставить в течение 6 часов), и эта строка также будет окрашена.

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

    =ПОИСК(«Due in»;$E2)=1
    =SEARCH(«Due in»,$E2)=1

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

    Итак, выполнив те же шаги, что и в первом примере, мы создали три правила форматирования, и наша таблица стала выглядеть вот так:

    Как изменить цвет ячейки на основании значения другой ячейки

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

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

    Как задать несколько условий для изменения цвета строки

    Если нужно выделить строки одним и тем же цветом при появлении одного из нескольких различных значений, то вместо создания нескольких правил форматирования можно использовать функции И (AND), ИЛИ (OR) и объединить таким образом нескольких условий в одном правиле.

    Например, мы можем отметить заказы, ожидаемые в течение 1 и 3 дней, розовым цветом, а те, которые будут выполнены в течение 5 и 7 дней, жёлтым цветом. Формулы будут выглядеть так:

    =ИЛИ($F2=»Due in 1 Days»;$F2=»Due in 3 Days»)
    =OR($F2=»Due in 1 Days»,$F2=»Due in 3 Days»)

    =ИЛИ($F2=»Due in 5 Days»;$F2=»Due in 7 Days»)
    =OR($F2=»Due in 5 Days»,$F2=»Due in 7 Days»)

    Для того, чтобы выделить заказы с количеством товара не менее 5, но не более 10 (значение в столбце Qty.), запишем формулу с функцией И (AND):

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

    =ИЛИ($F2=»Due in 1 Days»;$F2=»Due in 3 Days»;$F2=»Due in 5 Days»)
    =OR($F2=»Due in 1 Days»,$F2=»Due in 3 Days»,$F2=»Due in 5 Days»)

    Подсказка: Теперь, когда Вы научились раскрашивать ячейки в разные цвета, в зависимости от содержащихся в них значений, возможно, Вы захотите узнать, сколько ячеек выделено определённым цветом, и посчитать сумму значений в этих ячейках. Хочу порадовать Вас, это действие тоже можно сделать автоматически, и решение этой задачи мы покажем в статье, посвящённой вопросу Как в Excel посчитать количество, сумму и настроить фильтр для ячеек определённого цвета.

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

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

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

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

    Автоматическое заполнение ячеек датами

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

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

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

    Автоматическое заполнение ячеек актуальными датами

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

    Как работает формула для автоматической генерации уходящих месяцев?

    На рисунке формула возвращает период уходящего времени начиная даты написания статьи: 17.09.2017. В первом аргументе в функции DATA – вложена формула, которая всегда возвращает текущий год на сегодняшнюю дату благодаря функциям: ГОД и СЕГОНЯ. Во втором аргументе указан номер месяца (-1). Отрицательное число значит, что нас интересует какой был месяц в прошлом времени. Пример условий для второго аргумента со значением:

    • 1 – значит первый месяц (январь) в году указанном в первом аргументе;
    • 0 – это 1 месяца назад;
    • -1 – это 2 мес. назад от начала текущего года (то есть: 01.10.2016).

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

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

    Как видно теперь функция ДАТА использует значение из ячейки B1 и увеличивает номер месяца на 1 по отношению к предыдущей ячейки. В результате получаем 1 – число следующего месяца.

    Теперь скопируйте эту формулу из ячейки C1 в остальные заголовки столбцов диапазона D1:L1.

    Выделите диапазон ячеек B1:L1 и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Формат ячеек» или просто нажмите комбинацию клавиш CTRL+1. В появившемся диалоговом окне, на вкладке «Число», в разделе «Числовые форматы:» выберите опцию «(все форматы)». В поле «Тип:» введите значение: МММ.ГГ (обязательно буквы в верхнем регистре). Благодаря этому мы получим укороченное отображение значения дат в заголовках регистра, что упростит визуальный анализ и сделает его более комфортным за счет лучшей читабельности.

    Обратите внимание! При наступлении января месяца (D1), формула автоматически меняет в дате год на следующий.

    Как выделить столбец цветом в Excel по условию

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

    1. Выделите диапазон ячеек B2:L15 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». А в появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулу для определения форматируемых ячеек»
    2. В поле ввода введите формулу:
    3. Щелкните на кнопку «Формат» и укажите на вкладке «Заливка» каким цветом будут выделены ячейки актуального месяца. Например – зеленый. После чего на всех окнах для подтверждения нажмите на кнопку «ОК».

    Столбец под соответствующим заголовком регистра автоматически подсвечивается зеленым цветом соответственно с нашими условиями:

    Как работает формула выделения столбца цветом по условию?

    Благодаря тому, что перед созданием правила условного форматирования мы охватили всю табличную часть для введения данных регистра, форматирование будет активно для каждой ячейки в этом диапазоне B2:L15. Смешанная ссылка в формуле B$1 (абсолютный адрес только для строк, а для столбцов – относительный) обусловливает, что формула будет всегда относиться к первой строке каждого столбца.

    Автоматическое выделение цветом столбца по условию текущего месяца

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

    Обратите внимание! В условиях этой формулы, для последнего аргумента функции ДАТА указано значение 1, так же, как и для формул в определении дат для заголовков столбцов регистра.

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

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

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

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

    1. Выделите диапазон ячеек A2:A15 (то есть список номеров клиентов) и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». А в появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулу для определения форматируемых ячеек»
    2. В этот раз в поле ввода введите формулу:
    3. Щелкните на кнопку «Формат» и укажите красный цвет на вкладке «Заливка». После чего на всех окнах нажмите «ОК».
    4. Заполоните ячейки текстовым значением «заказ» как на рисунке и посмотрите на результат:

    Номера клиентов подсвечиваются красным цветом, если в их строке нет значения «заказ» в последних трех ячейках к текущему месяцу (включительно).

    Анализ формулы для выделения цветом ячеек по условию:

    Сначала займемся средней частью нашей формулы. Функция СМЕЩ возвращает ссылку на диапазон смещенного по отношении к области базового диапазона определенной числом строк и столбцов. Возвращаемая ссылка может быть одной ячейкой или целым диапазоном ячеек. Дополнительно можно определить количество возвращаемых строк и столбцов. В нашем примере функция возвращает ссылку на диапазон ячеек для последних 3-х месяцев.

    Важная часть для нашего условия выделения цветом находиться в первом аргументе функции СМЕЩ. Он определяет, с какого месяца начать смещение. В данном примере – это ячейка D2, то есть начало года – январь. Естественно для остальных ячеек в столбце номер строки для базовой ячейки будет соответствовать номеру строки в котором она находиться. Следующие 2 аргумента функции СМЕЩ определяют на сколько строк и столбцов должно быть выполнено смещение. Так как вычисления для каждого клиента будем выполнять в той же строке, значение смещения для строк указываем –¬ 0.

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

    Последнее два аргумента для функции СМЕЩ определяют высоту (в количестве строк) и ширину (в количестве столбцов) возвращаемого диапазона. В нашем примере – это область ячеек с высотой на 1-ну строку и шириной на 4 столбца. Этот диапазон охватывает столбцы 3-х предыдущих месяцев и текущий.

    Первая функция в формуле СЧЕТЕСЛИ проверяет условия: сколько раз в возвращаемом диапазоне с помощью функции СМЕЩ встречается текстовое значение «заказ». Если функция возвращает значение 0 – значит от клиента с таким номером на протяжении 3-х месяцев не было ни одного заказа. А в соответствии с нашими условиями, ячейка с номером данного клиента выделяется красным цветом заливки.

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

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

    Похожие статьи

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