Разделительная линия между наборами строк

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

separate-line-between-rows2.png

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

Способ 1. Простой

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

separate-line-between-rows1.png

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

Нажмите на кнопку Формат (Format) и в открывшемся окне на вкладке Граница (Borders) включите линию нужного цвета на нижней границе. После нажатия на ОК наше правило заработает и между группами строк появятся горизонтальные отчеркивающие линии

Способ 2. С поддержкой фильтра для чисел и дат

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

separate-line-between-rows3.png

Выкрутиться в таком случае можно, используя функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL), которая умеет выполнять различные математические операции (сумму, среднее, количество и т.д.), но "видеть" при этом только отфильтрованные ячейки. Для примера, отсортируем нашу таблицу по последнему столбцу с датой и проведем разделительную линию между днями. В условном форматировании придется создать похожее на первый способ правило, но использовать при этом не прямые ссылки в сравнении ячеек D2 и D3, а заключить их как аргументы в функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ:

separate-line-between-rows4.png

Первый аргумент функции (число 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

Нажав на кнопку Формат (Format) задайте обрамление красной линией сверху и нажмите ОК. Получившееся разделение по странам будет корректно работать даже после фильтрации, например, по дате:

separate-line-between-rows5.png

Ссылки по теме

 



11.11.2014 11:18:31
Николай, большое спасибо за статью!
Третий способ действительно универсальный и может очень полезным для таблиц с данными. Правда, если в таблице будет слишком много записей (к примеру, десятки тысяч), то такое форматирование существенно замедлит работу с файлом
Небольшое уточнение для способа № 3 – в английской версии следует писать SUBTOTAL вместо SUBTOTALS
11.11.2014 12:23:41
Да, конечно, спасибо - сейчас исправлю опечатку.
Насчет торможения на большом файле вы совершенно правы - третий способ, по сути, является формулой массива, хоть и вводится без фигурных скобок. Но, с другой стороны, кому нужны такие линии в файле со 100 тыс. строк? Кто-то будет его глазами просматривать? Обычно такого размера таблицы являются источником данных для анализа (сводных и т.п.) и редко кто с ними вручную работает.
19.10.2015 11:30:00
А как сделать хотя бы на 16 тысяч строк, а то 256 как-то совсем мало(((?
Если такая опция есть в PLEX то подскажите где ее активировать
25.02.2015 19:12:55
Спасибо!
Я попробовал универсальный способ в Вашем примере. Отсортировал (на вкладке универсально) по дате от старых к новым. Увидел, что ни в одной колонке разделители не стоят как задумано :(


Спасибо
24.04.2015 12:52:21
а я писала макрос для такого задания, может кому-то пригодиться...

Sub Borders()
Dim r1 As Integer, r2 As Integer, Col As Integer
r1 = 1
r2 = 1
col1 = Val(InputBox("Сколько столбцов с данными форматировать?";))
Col = 1
Range(Cells(r1, Col), Cells(Rows.Count, col1).End(xlDown)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Application.DisplayAlerts = False
Do
If Cells(r1, Col) <> Cells(r2 + 1, Col) Then
If r1 <> r2 Then
Range(Cells(r1, Col), Cells(r2, col1)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
     .LineStyle = xlDash
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
     .LineStyle = xlDash
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlThin
    End With
    Else
Range(Cells(r1, Col), Cells(r1, col1)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
     .LineStyle = xlContinuous
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
     .LineStyle = xlDash
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
     .LineStyle = xlDash
     .ColorIndex = 0
     .TintAndShade = 0
     .Weight = xlThin
    End With
    End If
r1 = r2 + 1
End If
r2 = r2 + 1
Loop Until Cells(r2, Col) = ""
Application.DisplayAlerts = True
End Sub
 
 
16.06.2015 18:23:18
Добрый день!

Спасибо!

Бывает, что необходимо сделать подобное разделение по данным НЕСКОЛЬКИХ столбцов.
Для варианта 1 это делается очень просто. Формула будет такой =СЦЕПИТЬ($A2;$B2)<>СЦЕПИТЬ($A3;$B3)

Николай,
для варианта 2 и 3 подобное возможно?  
13.01.2017 17:13:29
Добрый вечер!
Подскажите пож-та, как сделать что бы группы заливались цветом. (чередовались цвета).
1-ая Серая
2-ая Белая
3-ая Серая
4-ая Белая

.....
09.03.2017 17:56:52
А вот я даже статью про это написал
17.12.2018 18:32:04
Спасибо за отличную статью, вот только не хватило более детального пояснения алгоритма 3-го способа, чтобы его можно было под себя адаптировать для немного отличающейся ситуации. Я так и не смог для себя решить следующую задачу - в столбце "B" мне необходимо было закрасить верхнюю границу ячейки красным, при условии, что в тексте содержится "ТЕР*". Когда без фильтра всё работает, а вот как сделать, чтобы и без фильтра работало я так и не понял. Буду признателен если кто подскажет как реализовать.
Благодарю.
Наверх