Как сделать цветные строки в excel?

Применение цвета к чередующимся строкам или столбцам

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

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

Вот как это сделать:

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

На вкладке Главная выберите команду Форматировать как таблицу.

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

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

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

Применение полос к строкам или столбцам с помощью условного форматирования

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

Вот как это сделать:

На листе выполните одно из указанных ниже действий.

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

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

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

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

Чтобы применить цвет к чередующимся строкам, в поле Форматировать значения, для которых следующая формула является истинной введите формулу =ОСТАТ(СТРОКА();2)=0.

Чтобы применить цвет к чередующимся столбцам, введите формулу =ОСТАТ(СТОЛБЕЦ();2)=0.

Эти формулы определяют, является ли строка (или столбец) четной или нечетной, чтобы можно было применить соответствующий цвет.

Нажмите кнопку Формат.

В окне Формат ячеек откройте вкладку Заливка.

Выберите цвет и нажмите кнопку ОК.

Выбранный цвет показан в поле Образец. Нажмите кнопку ОК или выберите другой цвет.

Вот как можно изменить правило условного форматирования: щелкните одну из ячеек, к которым оно применяется, на вкладке Главная последовательно выберите команды Условное форматирование > Управление правилами, нажмите кнопку Изменить правило и внесите необходимые изменения.

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

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

Выделить активную строку цветом в таблице Excel

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

Подсветка строки в Excel цветом

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

Подсветка текущей строки в Excel цветом:

  1. Откройте редактор VBA для этого выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Visual Basic». Или нажмите комбинацию горячих клавиш ALT+F11.
  2. В правом окне к активному проекту добавьте новый модуль. Для того в редакторе VBA выберите опцию: «Insert»-«Module»
  3. В окне модуля вставьте этот VBA-код макроса:
  4. Не закрывая окно VBA-редактора перейдите на рабочий лист с исходной таблицей. Выделите целую вторую строку таблицы A2:H2 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:».
  5. В полю ввода введите формулу:
  6. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК.
  7. Скопируйте вторую строку таблицы.
  8. Выделите остальные строки и выберите инструмент: «ГЛАВНАЯ»-«Вставить»-«Специальная вставка»-«Форматы» (или нажмите комбинацию клавиш CTRL+ALT+V, потом отметить форматы и нажать ОК). Или воспользуйтесь инструментом: «ГЛАВНАЯ»-«Буфер обмена»-«Формат по образцу».
  9. Вернитесь к редактору VBA и в окне «Project» дважды щелкните по «Лист1». В появившемся окне вставьте следующий VBA-код макроса:
  10. Сохраните и закройте редактор VBA.
Читать еще:  Как сделать календарный график в excel 2010?

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

Полезный вопрос! А как же выделить активный столбец цветом? Аналогичным методом реализовывается с помощью макроса не только подсветка цветом активной строки, но и столбца. Для этого:

  1. В модуль где находится код подсветки строки VBA-макроса, добавим код новой функции АктивныйСтолбец:
  2. Добавим новое правило в условном форматировании для нашей таблицы в диапазоне A1:A7. Теперь в новом правиле пишем функцию АктивныйСтолбец(), а вместо функции СТРОКА – СТОЛБЕЦ. И указываем синий цвет форматирования заливки ячейки.
  3. Проверим все созданные правила для таблицы. Перейдите в ячейку A2 и ввыберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Управление правилами». Диспетчер правил условного форматирования должен выглядеть так:
  4. Скопируйте формат столбца A1:A7 в остальные столбцы таблицы диапазона B1:H7. Например, с помощью инструмента: «ГЛАВНАЯ»-«Буфер обмена»-«Формат по образцу».
  5. Результат превышает ожидания. Идеальный курсор для просмотра огромных таблиц с большим количеством данных на множестве строк и столбцов:

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

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

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

Чередующиеся строки и столбцы в Excel

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

Выделение строк или столбцов в Excel чередующимися цветами заливки – это распространённый способ сделать содержимое листа более понятным. В небольшой таблице выделить строки можно и вручную, но задача значительно усложняется с увеличением размера таблицы. Было бы очень удобно, если бы цвет строки или столбца изменялся автоматически. В данной статье я покажу быстрое решение такой задачи.

Чередуем цвет строк в Excel

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

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

Выделяем цветом каждую вторую строку при помощи стилей таблиц (Чередующиеся строки в Excel)

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

  1. Выделите диапазон ячеек, в котором нужно настроить чередование цветов строк.
  2. На вкладке Вставка (Insert) кликните Таблица (Table) или нажмите Ctrl+T.
  3. Готово! Чётные и нечётные строки созданной таблицы раскрашены в разные цвета. И, что замечательно, автоматическое чередование цветов будет сохраняться при сортировке, удалении или добавлении новых строк в таблицу.

Если все преимущества таблицы не нужны, и достаточно оставить только чередующуюся окраску строк, то таблица легко преобразуется обратно в обычный диапазон. Для этого кликните правой кнопкой по любой ячейке таблицы и в контекстном меню нажмите Таблица > Преобразовать в диапазон (Table > Convert to Range).

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

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

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

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

Если стандартная сине-белая палитра таблицы Excel не вызывает у Вас восторга, то на выбор предлагается множество шаблонов и цветов. Просто выделите таблицу, или любую ячейку этой таблицы, а затем на вкладке Конструктор (Design) в разделе Стили таблиц (Table Styles) выберите подходящий цвет.

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

Как выделять различное количество строк в полосах таблицы

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

  1. Откроем вкладку Конструктор (Design), кликнем правой кнопкой по понравившемуся стилю таблицы и в появившемся меню нажмём Дублировать (Duplicate).
  2. В поле Имя (Name) введём подходящее имя для нового стиля таблицы.
  3. Выберем элемент Первая полоса строк (First Row Stripe) и установим Размер полосы (Stripe Size) равный 2 или другому значению (по желанию).
  4. Далее выберем элемент Вторая полоса строк (Second Row Stripe) и повторим процесс.
  5. Жмём ОК, чтобы сохранить пользовательский стиль.
  6. Выбираем только что созданный стиль в галерее Стили таблиц (Table Styles). Созданные стили находятся вверху галереи в разделе Пользовательские (Custom).

Замечание: Пользовательские стили таблиц хранятся только в текущей рабочей книге, т.е. в других книгах они доступны не будут. Чтобы использовать пользовательский стиль по умолчанию для всех создаваемых таблиц в текущей рабочей книге, при создании или изменении стиля в диалоговом окне Изменение стиля таблицы (Modify Table Style) отметьте галочкой параметр Назначить стилем таблицы по умолчанию для данного документа (Set as default table style for this document).

Если созданный стиль получился не совсем таким, как хотелось, его легко можно изменить. Для этого открываем галерею стилей, находим наш пользовательский стиль, кликаем по нему правой кнопкой мыши и в контекстном меню выбираем Изменить (Modify). Вот где нужно дать волю своему творческому мышлению! Нажимаем кнопку Формат (Format), как показано на рисунке ниже, и на вкладках Шрифт (Font), Граница (Border) и Заливка (Fill) открывшегося диалогового окна нам доступны любые настройки соответствующих параметров. Можно настроить даже градиентную заливку для чередующихся строк.

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

Если чередование цветов в таблице Excel больше не требуется, удалить его можно буквально одним щелчком мыши. Выделите любую ячейку таблицы, откройте вкладку Конструктор (Design) и уберите галочку в строке параметра Чередующиеся строки (Banded rows).

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

Чередование окраски строк при помощи условного форматирования

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

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

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

Создадим правило условного форматирования вот таким образом:

  1. Выделите ячейки, для которых нужно изменить цвет. Если раскрасить строки необходимо на всём листе, то нажмите на серый треугольник в левом верхнем углу листа – так лист будет выделен полностью.
  2. На вкладке Главная (Home) в разделе Стили (Styles) нажмите кнопку Условное форматирование (Conditional Formatting) и в открывшемся меню выберите Создать правило (New Rule).
  3. В диалоговом окне Создание правила форматирования (New Formatting Rule) выберите вариант Использовать формулу для определения форматируемых ячеек (Use formula to determine which cells to format) и введите такую формулу:

  • Затем нажмите кнопку Формат (Format), в открывшемся диалоговом окне перейдите на вкладку Заливка (Fill) и выберите цвет заливки для чередующихся строк. Выбранный цвет будет показан в поле Образец (Sample). Если все устраивает – нажмите ОК.
  • В диалоговом окне Создание правила форматирования (New Formatting Rule) снова нажмите ОК, и созданное правило будет применено к каждой второй строке в выбранном диапазоне.
  • Вот что получилось у меня в Excel 2013:

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

    Теперь чётные и нечётные строки выделены разными цветами:

    Просто, правда? Сейчас я хочу кратко объяснить синтаксис функции ОСТАТ (MOD), поскольку далее мы будем использовать её в чуть более сложных примерах.

    Функция ОСТАТ (MOD) – возвращает остаток от деления и имеет следующий синтаксис:

    Например, результатом вычисления формулы

    будет 0, т.к. 4 делится на 2 без остатка.

    Теперь посмотрим подробнее, что именно делает созданная нами в предыдущем примере функция ОСТАТ (MOD). Мы использовали вот такую комбинацию функций ОСТАТ (MOD) и СТРОКА (ROW):

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

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

    Видео: Выделение строк в Excel с заданным шагом

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

    Первый способ.

    Применим функцию «Заливка ячеек в Excel».


    Здесь каждые три строки через три строки выделены серым цветом. Здесь, в маленькой таблице, мы выделили эти строки и окрасили их цветом функцией «Цвет заливки» на закладке «Главная» в разделе «Шрифт». А если таблица огромная в длину и ширину?

    Excel опять выручает нас. Есть несколько способов сделать все быстро и легко.

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

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

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


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

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


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


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

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

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

    Третий способ.

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

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

    В строке «Формат…» пишем такую формулу. =ОСТАТ(СТРОКА(A2)-3)=0 Цифра 3 в формуле говорит о том, что каждая третья строка будет окрашена. Строки считаются с первой строки листа Excel. Выбираем цвет заливки строк. Заполнили окно так.


    Нажимаем «ОК». Получилась такая таблица.


    Как настроить условное форматирование, читайте в статье «Условное форматирование в Excel». В условном форматировании можно применить разные условия. Смотрите примеры в статьях сайта.

    Как окрасить строки по дню недели, смотрите в статье «Как выделить строку в Excel по дню недели» тут. Можно выделить строки в таблице по своему условию.

    Смотрите об этом способе в статье «Выделить всю строку по условию в Excel». Как выделить ячейки с лишними пробелами, смотрите в статье «Как найти пробел в Excel».

    (Пока оценок нет)

    Ссылка на основную публикацию
    ×
    ×
    Adblock
    detector