Содержание:
Если у вас большой список, отсортированный по какому-то столбцу, то для наглядности было бы неплохо автоматически разделить полученные наборы строк горизонтальными линиями:
В приведенном выше примере это строки между странами, но в целом между любыми повторяющимися элементами в одном столбце. Давайте рассмотрим несколько способов реализовать это.
Способ 1. Простой
Самый быстрый способ сделать это — использовать условное форматирование, при котором нижняя граница ячеек будет рисоваться, если содержимое ячейки в столбце A не равно содержимому следующей ячейки в том же столбце. Выделите все ячейки таблицы, кроме заголовка, и выберите Основной вкладка команды Условное форматирование – Создать правило (Главная страница – Условное форматирование – Новое правило). Выберите тип правила Используйте формулу, чтобы определить, какие ячейки следует форматировать. (Используйте формулу, чтобы определить, какие ячейки форматировать) и введите в поле следующую формулу:
Обратите внимание на доллары в адресах, чтобы исправить буквы столбцов, а не номера строк, т.к. мы сравниваем страны только в столбце А. В формуле не должно быть пробелов.
Нажмите кнопку Рамки (Формат) и в открывшемся окне на вкладке Граница (Границы) включите линию нужного цвета на нижней границе. После нажатия на OK наше правило сработает и между группами линий появятся горизонтальные пунктирные линии
Способ 2. С поддержкой фильтров по числам и датам.
Небольшой, но очень заметный недостаток первого способа — такие границы не всегда будут корректно работать при фильтрации списка по другим столбцам. Так, например, если мы отфильтруем нашу таблицу по датам (только январь), то строки между всеми странами уже не будут видны, как раньше:
В этом случае выйти можно с помощью функции ПРОМЕЖУТОЧНЫЕ ИТОГИ (ПРОМЕЖУТОЧНЫЙ ИТОГ), который может выполнять различные математические операции (суммировать, усреднять, считать и т. д.), но «видит» только отфильтрованные ячейки. Например, отсортируем нашу таблицу по последнему столбцу с датой и проведем разделительную линию между днями. При условном форматировании вам придется создать правило, аналогичное первому способу, но не использовать прямые ссылки при сравнении ячеек D2 и D3, а заключить их в качестве аргументов в функцию ПРОМЕЖУТОЧНЫЙ ИТОГ:
Первый аргумент функции (номер 109) — это код операции суммирования. По сути, мы здесь ничего не добавляем и делаем, по сути, дурацкую операцию типа СУММ(D2), которая, естественно, равна D2. Но эта функция отличается от СУММ именно тем, что она выполняет действия только над видимыми ячейками, т.е. и будут сравниваться ячейки, оставшиеся после фильтра на экране, чего мы и хотели.
Способ 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