Obsah
Pokud máte velký seznam seřazený podle nějakého sloupce, bylo by hezké pro přehlednost automaticky oddělit výsledné sady řádků oddělováním vodorovných čar:
Ve výše uvedeném příkladu se jedná o čáry mezi zeměmi, ale obecně mezi všemi opakovanými položkami ve stejném sloupci. Podívejme se na několik způsobů, jak to implementovat.
Metoda 1. Jednoduchá
Nejrychleji to uděláte velmi snadno pomocí podmíněného formátování, které vykreslí spodní okraj buněk, pokud se obsah buňky ve sloupci A nerovná obsahu další buňky ve stejném sloupci. Vyberte všechny buňky v tabulce kromě záhlaví a vyberte Hlavní příkazová karta Podmíněné formátování – vytvořit pravidlo (Domů – Podmíněné formátování – Nové pravidlo). Vyberte typ pravidla Pomocí vzorce určete, které buňky chcete formátovat (Použijte vzorec k určení, které buňky se mají formátovat) a do pole zadejte následující vzorec:
Věnujte pozornost dolarům v adresách, abyste opravili písmena sloupců, ale ne čísla řádků, protože. země porovnáváme pouze ve sloupci A. Ve vzorci by neměly být žádné mezery.
Klikněte na tlačítko Rámec (Formát) a v otevřeném okně na kartě Hranice (Hranice) zapněte čáru požadované barvy na spodním okraji. Po kliknutí na OK naše pravidlo bude fungovat a mezi skupinami čar se objeví vodorovné přerušované čáry
Metoda 2. S podporou filtrů pro čísla a data
Drobnou, ale velmi nápadnou nevýhodou prvního způsobu je, že takové ohraničení nebude vždy správně fungovat při filtrování seznamu podle jiných sloupců. Pokud tedy například filtrujeme naši tabulku podle dat (pouze leden), řádky již nebudou viditelné mezi všemi zeměmi jako dříve:
V tomto případě se můžete dostat ven pomocí funkce Mezisoučty (MEDZISOUČET), který může provádět různé matematické operace (součet, průměr, počet atd.), ale „vidí“ pouze filtrované buňky. Seřaďme například naši tabulku podle posledního sloupce s datem a nakreslete dělicí čáru mezi dny. V podmíněném formátování budete muset vytvořit pravidlo podobné první metodě, ale nepoužívejte přímé odkazy při porovnávání buněk D2 a D3, ale uzavřete je jako argumenty ve funkci SUBTOTAL:
První argument funkce (číslo 109) je operační kód součtu. Ve skutečnosti zde nic nepřidáváme a děláme ve skutečnosti hloupou operaci typu SUM (D2), která se samozřejmě rovná D2. Tato funkce se ale od SUM liší právě tím, že provádí akce pouze na viditelných buňkách, tj. a buňky zbývající po filtru na obrazovce budou porovnány, což jsme chtěli.
Metoda 3. S podporou filtru pro jakákoli data
Jak můžete snadno vidět, druhý způsob má také nevýhodu: funkci součtu lze použít pouze na čísla nebo data (což jsou v Excelu také čísla), ale ne na text. Čili pokud chceme nakreslit čáru mezi zeměmi, jako u prvního způsobu, ale tak, aby se po filtrování zobrazila správně, tak budeme muset použít mnohem složitější cestu. Vyberte znovu celou tabulku kromě záhlaví, vytvořte nové pravidlo založené na vzorci a do ověřovacího pole zadejte následující konstrukci:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ($A$1:$A2;СТРОКА($A$1:$A2)-МИН(СТРОКА($A$1:$A2));;1));—($A$1:$A2=$A2))=1
V anglické verzi to bude:
=SUMPRODUCT(SUBTOTAL(103;OFFSET($A$1:$A2;ROW($A$1:$A2)-MIN(ROW($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Kliknutím na tlačítko Rámec (Formát) nastavte okraj s červenou čárou nahoře a klikněte OK. Výsledné rozdělení podle země bude správně fungovat i po filtrování, například podle data:
- Zvýrazněte data a časy pomocí podmíněného formátování
- Jak Excel vlastně pracuje s daty a časy
- Jak používat podmíněné formátování ke zvýraznění buněk podle podmínky v Excelu