Как сделать заливку в excel?

Применение и удаление заливки ячеек

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

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

Заливка ячеек сплошными цветами

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

На вкладке Главная в группе Шрифт выполните одно из указанных ниже действий.

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

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

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

Примечание: Microsoft Excel сохраняет 10 самых последних выбранных настраиваемых цветов. Чтобы быстро применить один из этих цветов, щелкните стрелку рядом с кнопкой Цвет заливки , а затем выберите нужный цвет в разделе Последние цвета.

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

Заполнение ячеек узором

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

На вкладке Главная в группе Шрифт нажмите кнопку вызова диалогового окна » Формат ячеек «.

Сочетание клавиш. Кроме того, можно нажать клавиши CTRL + SHIFT + F.

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

Выполните одно из следующих действий.

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

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

Проверка параметров печати для печати заливки ячеек в цвете

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

На вкладке Разметка страницы в группе Параметры страницы нажмите кнопку вызова диалогового окна » Параметры страницы «.

Убедитесь, что на вкладке лист в группе Печатьустановлены флажки черно-белый и Черновое качество .

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

Удаление заливки ячеек

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

На вкладке Главная в группе Шрифт щелкните стрелку рядом с кнопкой Цвет заливки, а затем выберите пункт Нет заливки.

Установка цвета заливки по умолчанию для всех ячеек листа

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

Создайте новый пустой лист.

Нажмите кнопку » выделить все «, чтобы выделить весь лист.

На вкладке Главная в группе Шрифт щелкните стрелку рядом с кнопкой Цвет заливки , а затем выберите нужный цвет.

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

На вкладке Файл выберите команду Сохранить как.

В поле Имя файла введите имя шаблона.

В поле Тип файла выберите пункт шаблон Excel, нажмите кнопку сохранить, а затем закройте лист.

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

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

На вкладке Файл нажмите кнопку Создать.

В разделе Доступные шаблоныщелкните Мои шаблоны.

В диалоговом окне Создание в разделе личные шаблоныщелкните только что созданный шаблон.

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

Как сделать заливку в Excel

У вас есть таблица в Экселе, которую не очень неудобно читать из-за одномерного белого фона?

Вам хотелось бы раскрасить таблицу так, чтобы чётные и нечётные строки визуально выделялись, например, все чётные — зеленым цветом?

Читать еще:  Как сделать рассылку в excel?

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

Итак, приготовьте вашу собственную таблицу и поехали!

Для заливки ячеек мы воспользуемся универсальной формулой Экселя — условное форматирование. Она есть у всех версий Excel.

Пример будет на основе Excel 2007. Если у вас версия выше — делайте по аналогии, суть та же самая.

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

На вкладке Главная нажимаем Условное форматирование, и затем Создать правило…

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

Затем переходите к полю ввода и вводите (или скопируйте/вставьте) следующую формулу:

=ОСТАТ(СТРОКА(A2);2)=0

где функция СТРОКА берет самую первую ячейку выделения и вычисляет ее номер, а функция ОСТАТ берет результат и делит его на 2 (для конкретно нашего примера). Если после деления имеется остаток, то ячейка пропускается, а если остаток равен нулю, то ячейка форматируется по выбранным нами условиям.

Если хотите выделить каждую 3ю или 7ю ячейку, то в формулу вместо двух, подставляете либо 3 либо 7. То есть, =ОСТАТ(СТРОКА(A2);3)=0 или =ОСТАТ(СТРОКА(A2);7)=0

Примечание: для тех, у кого английская версия Офиса, формула будет такая:

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

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

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

И вот оно, наше «полосатое» счастье;)

Как видите, не так уж сложно сделать таблицу полосатой и удобной для чтения. Достаточно один раз разобраться, как это делать. И с каждым разом вы будет делать это быстрее и достигнете автоматизма. А ваши друзья и коллеги будут в шоке, увидев как вы играючи управляетесь с Экселем;)

Удалось ли вам раскрасить таблицу?

Хочу еще почитать:

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

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

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

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

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

Для выбора функции заливки нужно определить тип таблицы и ее содержание:

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

Определившись с типом таблицы, выбираем функцию:

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

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

Справка! В статье мы работаем в Эксель версии 2016. Для версий 2007, 2010 и 2013 описываемые действия будут аналогичными, возможно незначительное отличие в названиях опций.

Работа с функцией «Условное форматирование»

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

Закрасим ячейки с цифрами:

  • 39 и больше красным;
  • 36,5 и меньше зеленым.

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

Шаг 2. Во вкладке «Главная» ищем группу «Стили».

Шаг 3. Нажимаем левой кнопкой мышки по иконке «Условное форматирование».

Шаг 4. В раскрывшемся меню щелкаем по строке с названием «Создать правило».

Шаг 5. Выделим левым кликом мышки функцию «Форматировать только ячейки…».

Шаг 6. В части окна под названием «Измените описание правила» выставляем нужные параметры, согласно описанным выше условиям:

    графу «Значение ячейки» оставляем по умолчанию;

Шаг 7. Нажимаем по кнопке «Формат».

Шаг 8. В открытом окне «Формат ячеек» активируем щелчком мышки вкладку «Заливка», если она не открылась по умолчанию.

Шаг 9. Левой кнопкой мышки нажимаем по подходящему цвету в палитре. В блоке «Образец» появится выбранный цвет.

Шаг 10. Если цвет устраивает, кликаем по «ОК».

Шаг 11. Проверяем выставленные параметры, кликаем по «ОК», чтобы настройки отобразились в таблице.

Шаг 12. Создаем другое правило, согласно второму условию, перейдя в «Условное форматирование».

Шаг 13. Выбираем второй тип правила.

Шаг 14. В графе «между» выставляем «меньше или равно», вводим цифру 36,5.

Шаг 15. Жмем по кнопке «Формат».

Шаг 16. Выбираем зеленый цвет, кликаем по «ОК».

Шаг 17. Проверяем все настройки, жмем по «ОК».

Таким образом мы создали в одной таблице два правила с разной заливкой.

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

Применим функцию заливки одним цветом разных оттенков.

    С помощью левой клавиши мышки выделим все данные.

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

Работа с функцией «Найти и выделить»

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

    Выделим ячейки с доходом.

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

В итоге данные в таблице окрашены в разные цвета согласно значениям.

Читать еще:  Как текст в excel сделать гиперссылкой?

Инструкция по закрашиванию пустых ячеек и с ошибками

Для закрашивания особых ячеек мы применим настройки функции «Условное форматирование». Следуем ниже описанным действиям:

    Выделяем все строки с данными.

Пустые ячейки закрасятся в обозначенный цвет.

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

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

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

    Щелкните по иконке с названием «Условное форматирование».

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

Заключение

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

Видео — Как изменить цвет в ячейках Excel в зависимости от значения

Понравилась статья?
Сохраните, чтобы не потерять!

Как сделать так, чтобы цвет ячейки Excel менялся в зависимости от значения

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

Цвет заливки меняется вместе со значением

Для примера мы потренируемся на том, чтобы ячейка меняла цвет в данной таблице при определенном условии. Да ни одна, а все со значением в диапазоне от 60 до 90. Для этого мы воспользуемся функцией «Условное форматирование».

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

Далее находим на вкладке «Главная» кнопку «Условное форматирование» и в списке выбираем «Создать правило».

У нас открылось окно «Создание правил форматирования». В этом окне выбираем тип правила: «Форматировать только ячейки, которые содержат».

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

В нашем случае необходимо поставить следующие: «значения ячейки» и «между». Так же мы обозначаем диапазон, что при условии значения от 60 до 90 будет применена заливка. Посмотрите на скриншоте, как это сделал я.

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

Если вы заполнили, то не спешите кликать по кнопке «ОК». Прежде необходимо нажать на кнопку «Формат», как на скриншоте, и перейти к настройке заливки.

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

Посмотрите на свой результат. Думаю, у вас все получилось. У меня точно получилось. Взгляните на скриншот:

Окрасим ячейку в определенный цвет, если она равна чему-то

Давайте вернемся к нашей таблице в изначальном виде. И теперь мы поменяем цвет там, где содержится цифра 40 на красный цвет, а с цифрой 50 на желтый. Конечно, для этого дела можно воспользоваться первым способом, но мы же хотим знать больше возможностей Excel.

В этот раз мы воспользуемся функцией «Найти и заменить».

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

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

Можно же и горячими клавишами пользоваться: CTRL + F

В поле «Найти» мы указываем то, что ищем. В данном случае пишем «40», а затем жмем кнопку «Найти все».

Теперь, когда ниже были показаны результаты поиска, выберите одно из них и нажмите на сочетание CTRL + A, чтобы выбрать их все сразу. А затем нажмите на «Закрыть», чтобы убрать окно «Найти и заменить».

Когда у нас выбраны все, содержащие цифру 40, на вкладке «Главная» в разделе «Шрифт» выберите окраску ячейки. У нас это красный. И, как вы видите у себя на экране, так и у меня на скриншоте, они окрасились в красный.

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

У вас получилось? А посмотрите, что вышло у меня.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Далее перейдите в ячейку 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