Съдържание
Ако имате голям списък, сортиран по някаква колона, тогава би било хубаво автоматично да разделите получените набори редове с разделителни хоризонтални линии за яснота:
В примера по-горе това са редове между държави, но като цяло между всички повтарящи се елементи в една и съща колона. Нека да разгледаме няколко начина за прилагане на това.
Метод 1. Прост
Най-бързият начин да направите това е много лесно с условно форматиране, което ще начертае долната граница на клетките, ако съдържанието на клетката в колона А не е равно на съдържанието на следващата клетка в същата колона. Изберете всички клетки в таблицата с изключение на заглавката и изберете Основната команден раздел Условно форматиране – Създаване на правило (Начало – Условно форматиране – Ново правило). Изберете тип правило Използвайте формула, за да определите кои клетки да форматирате (Използвайте формула, за да определите кои клетки да форматирате) и въведете следната формула в полето:
Обърнете внимание на доларите в адресите, за да коригирате буквите на колоните, но не и номерата на редовете, т.к. сравняваме само държави в колона А. Във формулата не трябва да има интервали.
Кликнете върху бутона Рамка (формат) и в отворения прозорец на таб Граница (Граници) включете линията с желания цвят на долната граница. След като щракнете върху 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