Чергування кольору рядків і стовпців в excel (чергуються рядки і стовпці)
Це керівництво пояснює, як налаштувати чергування кольору заливки рядків і автоматично виділяти кожну другий рядок або стовпець на робочому аркуші. Ви навчитеся створювати чергуються рядки і стовпці в Excel, а також дізнаєтеся кілька цікавих формул, що дозволяють чергувати колір рядків в залежності від містяться в них значень.
Виділення рядків або стовпців в Excel чергуються квітами заливки - це поширений спосіб зробити вміст листа зрозумілішим. У невеликій таблиці виділити рядки можна і вручну, але завдання значно ускладнюється зі збільшенням розміру таблиці. Було б дуже зручно, якби колір рядка або стовпця змінювався автоматично. У даній статті я покажу швидке вирішення такого завдання.
Чергуючи колір рядків в Excel
Коли в Excel потрібно виділити кольором кожну другий рядок, більшість фахівців тут же згадують про умовне форматуванні і, поколдовав деякий час над створенням хитромудрого поєднання функцій ОСТАТ (MOD) і СТРОКА (ROW), досягають потрібного результату.
Якщо ж Ви не з тих, хто любить колоти горіхи кувалдою, тобто не хочете витрачати багато часу і натхнення на таку дрібницю, як розфарбовування в смужку таблиць Excel, рекомендую використовувати більш швидке рішення - вбудовані стилі таблиць.
Виділяємо кольором кожну другий рядок за допомогою стилів таблиць (чергуються рядки в Excel)
Найбільш швидкий і простий спосіб налаштувати забарвлення рядків в Excel - використовувати готові стилі таблиць. Поряд з іншими перевагами (наприклад, автоматичний фільтр), до рядків таблиці також застосовується чергування квітів. Щоб перетворити діапазон даних в таблицю:
- Виділіть діапазон комірок, в якому потрібно налаштувати чергування квітів рядків.
- На вкладці Вставка (Insert) клікніть Таблиця (Table) або натисніть Ctrl + T.
Якщо всі переваги таблиці не потрібні, і досить залишити тільки чергуються забарвлення рядків, то таблиця легко перетворюється назад в звичайний діапазон. Для цього клікніть правою кнопкою по будь-якому осередку таблиці і в контекстному меню натисніть Таблиця> Перетворити в діапазон (Table> Convert to Range).
Зауваження: Якщо вирішите перетворити таблицю в діапазон, то в подальшому при додаванні до діапазону нових рядків чергування квітів не буде змінюватися автоматично. Є ще один недолік: при сортуванні даних, тобто при переміщенні комірки чи рядки в межах вибраного діапазону, кольорові смуги будуть переміщатися разом з осередками, і акуратна смугаста розфарбування повністю перемішається.
Як бачите, перетворення діапазону в таблицю - це дуже простий і швидкий спосіб виділення чергуються рядків в Excel. Але що якщо хочеться трохи більшого?
Як вибрати власні кольори для смуг
Якщо стандартна синьо-біла палітра таблиці Excel не викликає у Вас захоплення, то на вибір пропонується безліч шаблонів і квітів. Просто виділіть таблицю, або будь-який осередок цієї таблиці, а потім на вкладці Конструктор (Design) в розділі Стилі таблиць (Table Styles) виберіть відповідний колір.
Перегортати колекцію стилів можна за допомогою стрілок або клікнути на кнопку Додаткові параметри (More), щоб показати всі стилі. При наведенні покажчика миші на будь-який стиль, він негайно приміряється до таблиці, і можна відразу побачити, як при цьому будуть виглядати чергуються рядки.
Як виділяти різну кількість рядків в смугах таблиці
Якщо в кожній з кольорових смуг потрібно виділити різну кількість рядків листа Excel, наприклад, два рядки забарвити одним кольором, а три наступні - іншим кольором, то потрібно створити власний стиль таблиці. Припустимо, що ми вже перетворили діапазон в таблицю, і виконаємо наступні кроки:
- Відкриємо вкладку Конструктор (Design), кликнемо правою кнопкою по вподобаному стилю таблиці і в меню натиснемо Дублювати (Duplicate).
Зауваження: Користувальницькі стилі таблиць зберігаються тільки в поточній робочій книзі, тобто в інших книгах вони доступні не будуть. Щоб використовувати власний стиль за замовчуванням для всіх створюваних таблиць в поточній робочій книзі, при створенні або зміні стилю в діалоговому вікні Зміна стилю таблиці (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. але перевага умовного форматування полягає в тому, що воно працює як з таблицями, так і з простими діапазонами даних. А це означає, що при сортуванні, додаванні або видаленні рядків в діапазоні, розфарбування не перемішується.
Створимо правило умовного форматування ось таким чином:
- Виділіть комірки, для яких потрібно змінити колір. Якщо розфарбувати рядки необхідно на всьому аркуші, то натисніть на сірий трикутник в лівому верхньому куті аркуша - так лист буде виділено повністю.
У діалоговому вікні Створення правила форматування (New Formatting Rule) виберіть варіант Використовувати формулу для визначення форматується осередків (Use formula to determine which cells to format) і введіть таку формулу:
Якщо замість чергування з білими рядками Ви хочете розфарбувати рядки в два різних кольору, то створіть ще одне правило умовного форматування з такою формулою:
Тепер парні і непарні рядки виділені різними кольорами:
Просто, правда? Зараз я хочу коротко пояснити синтаксис функції ОСТАТ (MOD), оскільки далі ми будемо використовувати її в трохи більш складних прикладах.
Функція ОСТАТ (MOD) - повертає залишок від ділення і має наступний синтаксис:
Наприклад, результатом обчислення формули
буде 0, тому що 4 ділиться на 2 без залишку.
Тепер подивимося докладніше, що саме робить створена нами в попередньому прикладі функція ОСТАТ (MOD). Ми використовували ось таку комбінацію функцій ОСТАТ (MOD) і СТРОКА (ROW):
Синтаксис простий і нехитрий: функція СТРОКА (ROW) повертає номер рядка, потім функція ОСТАТ (MOD) ділить його на 2 і повертає залишок від ділення. У застосуванні до нашої таблиці формула повертає ось такі результати:
Побачили закономірність? Для парних рядків результат завжди дорівнює 0. а для непарних - 1. Далі ми створюємо правило умовного форматування, яке вказує Excel забарвити непарні рядки (результат дорівнює 1) в один колір, а парні рядки (результат дорівнює 0) - в інший колір.
Тепер, коли з основами розібралися, давайте займемося більш складними прикладами.
Як налаштувати чергування груп рядків різного кольору
Наступні формули можна використовувати для розмальовки заданого числа рядків, незалежно від їх вмісту:
Розфарбовуємо непарні групи рядків. тобто виділяємо кольором першу групу і далі через одну:
Розфарбовуємо парні групи рядків. тобто другу і далі через одну:
Тут НомерСтрокі - це номер рядка першого осередку з даними, а N - кількість рядків в кожній розфарбованої групі.
Порада: Якщо потрібно виділити і парні, і непарні групи рядків, тоді доведеться створити два правила умовного форматування - для кожної з показаних вище формул.
У наступній таблиці представлені кілька прикладів використання формул і результат форматування.
Розфарбувати кожні 2 рядки, починаючи з 1 групи. Дані починаються в рядку 2.
