Spis treści
Jeśli masz dużą listę posortowaną według jakiejś kolumny, dobrze byłoby automatycznie oddzielić wynikowe zestawy wierszy za pomocą oddzielających linii poziomych dla jasności:
W powyższym przykładzie są to linie między krajami, ale ogólnie między dowolnymi powtarzającymi się pozycjami w tej samej kolumnie. Przyjrzyjmy się kilku sposobom realizacji tego.
Metoda 1. Prosta
Najszybszym sposobem na zrobienie tego jest bardzo proste formatowanie warunkowe, które narysuje dolną granicę komórek, jeśli zawartość komórki w kolumnie A nie jest równa zawartości następnej komórki w tej samej kolumnie. Zaznacz wszystkie komórki w tabeli z wyjątkiem nagłówka i wybierz Głównym karta poleceń Formatowanie warunkowe – Utwórz regułę (Strona główna — Formatowanie warunkowe — Nowa reguła). Wybierz typ reguły Użyj formuły, aby określić, które komórki należy sformatować (Użyj formuły, aby określić, które komórki do sformatowania) i w polu wpisz następującą formułę:
Zwróć uwagę na dolary w adresach, aby naprawić litery kolumn, ale nie numery wierszy, ponieważ. porównujemy tylko kraje w kolumnie A. W formule nie powinno być spacji.
Naciśnij przycisk Framework (format) a w otwartym oknie na zakładce Granica (Granice) włącz linię żądanego koloru na dolnej krawędzi. Po kliknięciu OK nasza reguła zadziała i między grupami linii pojawią się poziome kreskowane linie
Metoda 2. Z obsługą filtrów dla liczb i dat
Niewielką, ale bardzo zauważalną wadą pierwszej metody jest to, że takie obramowania nie zawsze będą działać poprawnie podczas filtrowania listy według innych kolumn. Na przykład, jeśli przefiltrujemy naszą tabelę według dat (tylko styczeń), to linie nie będą już widoczne między wszystkimi krajami, tak jak poprzednio:
W takim przypadku możesz wyjść za pomocą funkcji SUMY CZĘŚCIOWE (SUMA CZĘŚCIOWA), który może wykonywać różne operacje matematyczne (suma, średnia, liczba itp.), ale „widzi” tylko przefiltrowane komórki. Na przykład posortujmy naszą tabelę według ostatniej kolumny z datą i narysujmy linię podziału między dniami. W formatowaniu warunkowym będziesz musiał utworzyć regułę podobną do pierwszej metody, ale nie używaj bezpośrednich linków do porównywania komórek D2 i D3, ale uwzględnij je jako argumenty w funkcji SUMY.CZĘŚCIOWE:
Pierwszym argumentem funkcji (numer 109) jest opcode sumowania. Tak naprawdę nic tu nie dodajemy i wykonujemy tak naprawdę głupią operację, taką jak SUMA (D2), która oczywiście jest równa D2. Ale ta funkcja różni się od SUM właśnie tym, że wykonuje akcje tylko na widocznych komórkach, czyli porównywane będą komórki pozostałe po filtrze na ekranie, o co nam chodziło.
Metoda 3. Z obsługą filtrów dla dowolnych danych
Jak łatwo zauważyć, druga metoda ma również wadę: funkcję sumy można zastosować tylko do liczb lub dat (które są również liczbami w Excelu), ale nie do tekstu. Oznacza to, że jeśli chcemy narysować granicę między krajami, tak jak w pierwszej metodzie, ale aby po przefiltrowaniu była wyświetlana poprawnie, będziemy musieli użyć znacznie bardziej skomplikowanej ścieżki. Wybierz ponownie całą tabelę z wyjątkiem nagłówka, utwórz nową regułę na podstawie wzoru i wprowadź następującą konstrukcję w polu walidacji:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ($A$1:$A2;СТРОКА($A$1:$A2)-МИН(СТРОКА($A$1:$A2));;1));—($A$1:$A2=$A2))=1
W wersji angielskiej będzie to:
=SUMPRODUCT(SUBTOTAL(103;OFFSET($A$1:$A2;ROW($A$1:$A2)-MIN(ROW($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Klikając przycisk Framework (format) ustaw ramkę z czerwoną linią na górze i kliknij OK. Wynikowy podział według kraju będzie działał poprawnie nawet po przefiltrowaniu, na przykład według daty:
- Wyróżnij daty i godziny za pomocą formatowania warunkowego
- Jak Excel faktycznie działa z datami i godzinami
- Jak używać formatowania warunkowego do wyróżniania komórek według stanu w programie Excel?