зміст
Якщо у вас є великий список, відсортований за деякими стовпцями, тоді було б непогано автоматично відокремити отримані набори рядків горизонтальними лініями для наочності:
У наведеному вище прикладі це рядки між країнами, але, загалом, між будь-якими повторюваними елементами в одному стовпці. Давайте розглянемо кілька способів реалізації цього.
Спосіб 1. Простий
Найшвидший спосіб зробити це дуже простий за допомогою умовного форматування, яке малює нижню межу комірок, якщо вміст комірки в стовпці A не дорівнює вмісту наступної комірки в цьому ж стовпці. Виділіть усі клітинки в таблиці, крім заголовка, і виберіть Головний вкладка команд Умовне форматування – створити правило (Домашня сторінка – Умовне форматування – Нове правило). Виберіть тип правила За допомогою формули визначте, які клітинки потрібно форматувати (Використовуйте формулу, щоб визначити, які клітинки потрібно відформатувати) і введіть наступну формулу в поле:
Зверніть увагу на долари в адресах, щоб зафіксувати літери стовпців, але не номери рядків, тому що. ми порівнюємо лише країни в колонці А. У формулі не повинно бути пробілів.
Натисніть кнопку Рамки (формат) і у відкритому вікні на вкладці Кордон (Межі) увімкніть лінію потрібного кольору на нижній межі. Після натискання на OK наше правило спрацює і між групами ліній з'являться горизонтальні штрихові лінії
Спосіб 2. З підтримкою фільтрів для чисел і дат
Невеликий, але дуже помітний недолік першого способу полягає в тому, що такі рамки не завжди будуть коректно працювати при фільтрації списку за іншими стовпцями. Так, наприклад, якщо ми відфільтруємо нашу таблицю за датами (лише січень), то рядки більше не будуть видимі між усіма країнами, як раніше:
У цьому випадку вийти можна за допомогою функції ПРОМІЖНІ ПІДСУМКИ (ПРІМІЖНИЙ СУММ), який може виконувати різноманітні математичні операції (сума, середнє, підрахунок тощо), але «бачить» лише відфільтровані клітинки. Наприклад, давайте відсортуємо нашу таблицю за останнім стовпцем із датою та проведемо роздільну лінію між днями. В умовному форматуванні вам доведеться створити правило, подібне до першого методу, але не використовувати прямі посилання для порівняння комірок D2 і D3, а додати їх як аргументи до функції SUBTOTAL:
Першим аргументом функції (номер 109) є код операції підсумовування. Насправді ми тут нічого не додаємо і робимо, по суті, дурну операцію на зразок SUM (D2), яка, звісно, дорівнює D2. Але ця функція відрізняється від SUM саме тим, що вона виконує дії тільки над видимими осередками, тобто будуть порівнюватися осередки, що залишилися після фільтра на екрані, чого ми і хотіли.
Спосіб 3. З підтримкою фільтрів будь-яких даних
Як ви можете легко побачити, другий метод також має недолік: функцію суми можна застосувати лише до чисел або дат (які також є числами в Excel), але не до тексту. Тобто, якщо ми хочемо провести межу між країнами, як у першому способі, але щоб вона відображалася правильно після фільтрації, то нам доведеться використовувати набагато складніший шлях. Знову виділіть всю таблицю, крім заголовка, створіть нове правило на основі формули та введіть таку конструкцію в поле перевірки:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ($A$1:$A2;СТРОКА($A$1:$A2)-МИН(СТРОКА($A$1:$A2));;1));—($A$1:$A2=$A2))=1
В англійській версії це буде:
=SUMPRODUCT(SUBTOTAL(103;OFFSET($A$1:$A2;ROW($A$1:$A2)-MIN(ROW($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Натиснувши на кнопку Рамки (формат) встановіть рамку з червоною лінією зверху та натисніть OK. Отриманий розподіл за країнами буде працювати коректно навіть після фільтрації, наприклад, за датою:
- Виділіть дати й час за допомогою умовного форматування
- Як Excel насправді працює з датами та часом
- Як використовувати умовне форматування для виділення клітинок за умовою в Excel