Inhoud
Als je een grote lijst hebt gesorteerd op een kolom, dan zou het leuk zijn om de resulterende rijensets automatisch te scheiden met horizontale lijnen voor de duidelijkheid:
In het bovenstaande voorbeeld zijn dit regels tussen landen, maar in het algemeen tussen herhaalde items in dezelfde kolom. Laten we een paar manieren bekijken om dit te implementeren.
Methode 1. Eenvoudig
De snelste manier om dit te doen is heel eenvoudig met voorwaardelijke opmaak, waarbij de onderrand van de cellen wordt getekend als de inhoud van de cel in kolom A niet gelijk is aan de inhoud van de volgende cel in dezelfde kolom. Selecteer alle cellen in de tabel behalve de koptekst en selecteer De belangrijkste opdracht tabblad Voorwaardelijke opmaak – Regel maken (Home - Voorwaardelijke opmaak - Nieuwe regel). Selecteer regeltype Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt (Gebruik de formule om te bepalen welke cellen moeten worden opgemaakt) en voer de volgende formule in het veld in:
Let op de dollars in de adressen om de kolomletters te corrigeren, maar niet de rijnummers, want. we vergelijken alleen landen in kolom A. Er mogen geen spaties in de formule voorkomen.
Klik op de knop Achtergrond (Formaat) en in het geopende venster op het tabblad Grens (Randen) zet de lijn van de gewenste kleur op de onderrand aan. Na het klikken op OK onze regel zal werken en er verschijnen horizontale streepjeslijnen tussen de groepen lijnen
Methode 2. Met filterondersteuning voor getallen en datums
Een klein maar zeer merkbaar nadeel van de eerste methode is dat dergelijke randen niet altijd correct werken wanneer de lijst op andere kolommen wordt gefilterd. Dus als we onze tabel bijvoorbeeld filteren op datum (alleen januari), dan zijn de regels niet langer zichtbaar tussen alle landen, zoals voorheen:
In dit geval kunt u uitstappen met behulp van de functie SUBTOTALEN (SUBTOTAAL), die verschillende wiskundige bewerkingen kan uitvoeren (som, gemiddelde, telling, enz.), maar alleen gefilterde cellen "ziet". Laten we bijvoorbeeld onze tabel sorteren op de laatste kolom met de datum en een scheidslijn trekken tussen de dagen. Bij voorwaardelijke opmaak moet u een regel maken die lijkt op de eerste methode, maar geen directe koppelingen gebruiken bij het vergelijken van cellen D2 en D3, maar deze als argumenten insluiten in de functie SUBTOTAAL:
Het eerste argument van de functie (nummer 109) is de sommatie-opcode. In feite voegen we hier niets toe en doen in feite een domme operatie zoals SOM (D2), wat natuurlijk gelijk is aan D2. Maar deze functie verschilt juist van SUM doordat hij alleen acties uitvoert op zichtbare cellen, dat wil zeggen dat de cellen die achterblijven na het filter op het scherm worden vergeleken, wat we wilden.
Methode 3. Met filterondersteuning voor alle gegevens
Zoals je gemakkelijk kunt zien, heeft de tweede methode ook een nadeel: de somfunctie kan alleen worden toegepast op getallen of datums (wat ook getallen zijn in Excel), maar niet op tekst. Dat wil zeggen, als we een lijn tussen landen willen trekken, zoals bij de eerste methode, maar zodat deze correct wordt weergegeven na het filteren, dan zullen we een veel gecompliceerder pad moeten gebruiken. Selecteer opnieuw de hele tabel behalve de kop, maak een nieuwe regel op basis van de formule en voer de volgende constructie in het validatieveld in:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ($A$1:$A2;СТРОКА($A$1:$A2)-МИН(СТРОКА($A$1:$A2));;1));—($A$1:$A2=$A2))=1
In de Engelse versie wordt het:
=SUMPRODUCT(SUBTOTAL(103;OFFSET($A$1:$A2;ROW($A$1:$A2)-MIN(ROW($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Door op de knop te klikken Achtergrond (Formaat) stel een rand in met een rode lijn bovenaan en klik op OK. De resulterende indeling per land zal correct werken, zelfs na filteren, bijvoorbeeld op datum:
- Markeer datums en tijden met voorwaardelijke opmaak
- Hoe Excel eigenlijk werkt met datums en tijden
- Voorwaardelijke opmaak gebruiken om cellen op voorwaarde te markeren in Excel