Mini-ats102.ru

ООО “Мультилайн”
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Microsoft Excel

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

При создании таблицы (Вставка → Таблицы → Таблица) ее можно отформатировать, чтобы строки с двумя вариантами заливки чередовались. Благодаря такому форматированию, таблицы получаются более удобочитаемыми.

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

Режим “Разработчик” в Excel

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

Переходим в “Пуск — Параметры Excel” и находим в левом списке пункт “Надстройки”:

Excel - надстройка VBA

Выбираем в основном окне строчку “Пакет анализа — VBA” и жмем кнопочку “Перейти” в самом низу окна. Откроется еще одно окошко со списком доступных под Excel расширений (надстроек). Снова выбираем в этом списке “Пакет анализа — VBA” и соглашаемся, что хотим установить его, нажав кнопку “ОК”:

Excel - Пакет анализа VBA

Потребуется установочный диск с Microsoft Office на нем (или же подключение к Интернет) чтобы программа получила необходимые пакеты для инсталляции. Если установка прошла успешно, то в “Ленте” появиться пункт “Разработчик” (Excel 2010). Можно перейти в него через эту панель или же с помощью сочетания клавиш Alt + F11 .

Появиться окно, в котором выполняется написание кода на языке VBA, то есть фактически создаются пользовательские функции. Я писать их не буду, так как языка VBA не знаю и знать особого желания нет (все знать невозможно).

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

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

Читайте так же:
Как в ворде выровнять по правому краю

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

Во-первых, мы создаем первое правило, которое говорит нам, что все, что будет меньше 3,50, должно окрашиваться в красный цвет и иметь белый цвет шрифта (текста). Для этого во второй колонке выбираем значение Меньше, а в третьей ставим число 3,50.

how to make a cell change color 008

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

how to make a cell change color 009

А во вкладке Шрифт ставим нужный цвет — белый.

how to make a cell change color 010

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

how to make a cell change color 011

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

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

Читайте так же:
Как в ворде убрать два листа

Второй способ.

Рассмотрим, как выделить строки через одну в Excel.

Используем функцию «Формат таблицы Excel». Нажимаем на любую ячейку в таблице. На закладке «Главная» в разделе «Стили» нажимаем кнопку «Форматировать как таблицу». Появляется большое окно с разными видами формата таблицы.

Как в excel выделить строки через одну.

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

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

Как в excel выделить строки через одну.

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

Как в excel выделить строки через одну.

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

И фильтры будут работать на этих новых строках. И формулы в новых строках будут копироваться автоматически.

Шапка таблицы автоматически закрепляется и остается видна при прокрутке длинной таблицы. Появляется новая закладка «Работа с таблицами» -> «Конструктор» с многими функциями для работы с таблицей.

Нужно выделить повторяющиеся значения в столбце? Надо выбрать первые 5 максимальных ячеек? Необходимо сделать термальную шкалу для наглядности (цвет меняется в зависимости от увеличения/уменьшения значения ячеек)? В Excel выделение цветом ячеек по условиям можно сделать очень быстро и просто. За выделение цветом ячеек отвечает специальная функция «Условное форматирование». Настоятельно рекомендую! Подробнее читаем дальше:

Excel выделение цветом

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

Читайте так же:
Где кнопка рисование в ворде

Условное форматирование, где найти?

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

Условное 1

При нажатии откроется меню, с разными вариантами этого редактирования. Как вы видите, возможностей здесь действительно много.

Excel выделение цветом

Теперь подробнее о самых полезных:

Excel выделение цветом ячеек по условиям. Простые условия

Для этого зайдите в пункт Правила выделения ячеек. Если к примеру, вам нужно выделить все ячейки больше 100, нажмите кнопку Больше. В окне:

Excel выделение цветом 0

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

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

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

Excel выделение цветом

Далее снова появиться окошко с форматированием. Настройте как вам удобно. Можно выделить, например, только уникальные. Значения и курсивом (пользовательский формат)

Что делать если необходимо найти повторения по двум и более столбцам, например когда ФИО в разных столбцах? Сделайте еще один столбец и объедините значения формулой =СЦЕПИТЬ() , т.е. в отдельной ячейке у вас будет написано ИвановИванИваныч. По такому столбцу вы уже легко сможете выделить повторяющиеся значения. Важно понимать, что если порядок слов будет различаться, то Excel сочтет такие строки неповторяющимися (например, ИванИванычИванов).

Excel. значения повторяются

Выделение цветом первых/последних значений. Опять же условное форматирование

Excel выделение цветом 1

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

Excel выделение цветом 2

Построение термальной диаграммы и гистограммы

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

Читайте так же:
Как в ворде поставить цифру над буквой

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

Рекомендую. Для презентаций и аналитики — гистограммы в ячейках и термальные диаграммы основа простой визуализации при помощи Excel.

Excel выделение цветом 3

Выделение цветом ячеек, содержащих определенный текст

Очень часто нужно найти ячейки, которые содержат определенный набор символов, можно конечно воспользоваться функцией = ПОИСК() , но проще и быстрее применить условное форматирование, пройдите — Правила отбора ячеек — Текст содержит

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

Excel выделение цветом. Фильтр по цвету

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

Подробнее о фильтрах в этой статье .

Excel выделение цветом 4

Проверка условий форматирования

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

Excel выделение цветом 5

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

Важно! Условное форматирование при неправильном использовании зачастую является причиной сильных тормозов Excel . Происходит задвоение форматирований, для примера если вы много раз копируете ячейки с выделением цветом. Тогда у вас появится множество условий с цветом. Я сам видел более 3 тысяч условий — тормозил файл безобразно. Также файл может тормозить, когда задан диапазон как на картинке выше, лучше, указывать A:A — для всего диапазона.

Читайте так же:
Можно ли в аквапарке купаться в шортах

Подробнее о тормозах Excel и их причинах читайте здесь . Эта статья помогла не одной сотне людей 😉

Простая заливка блока

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

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

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

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

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

Как изменить или удалить правило? ​

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

По кнопке «Изменить правило» откроется меню, в котором можно отредактировать формулу, изменить параметры форматирования и т.д.

Кнопка «Удалить правило» удалит то, на которым в данный момент стоит выделение.

Также правила можно менять местами, нажимая на стрелочки в этом же меню «вверх» или «вниз». Выполняются правила снизу-вверх, т.е. то, которое сверху, перекрывает нижние (выполняется последним).

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector