Innhold
Hvis du har en stor liste sortert etter en kolonne, ville det være fint å automatisk separere de resulterende radsettene med separate horisontale linjer for klarhetens skyld:
I eksemplet ovenfor er dette linjer mellom land, men generelt sett mellom gjentatte elementer i samme kolonne. La oss se på noen måter å implementere dette på.
Metode 1. Enkel
Den raskeste måten å gjøre dette på er veldig enkelt med betinget formatering, som vil tegne den nederste kanten av cellene hvis innholdet i cellen i kolonne A ikke er lik innholdet i neste celle i samme kolonne. Velg alle cellene i tabellen bortsett fra overskriften og velg Den viktigste kommandofanen Betinget formatering – Opprett regel (Hjem – betinget formatering – ny regel). Velg regeltype Bruk en formel for å bestemme hvilke celler som skal formateres (Bruk formel for å bestemme hvilke celler som skal formateres) og skriv inn følgende formel i feltet:
Vær oppmerksom på dollarene i adressene for å fikse kolonnebokstavene, men ikke radnumrene, fordi. vi sammenligner kun land i kolonne A. Det skal ikke være mellomrom i formelen.
Klikk på knappen Rammeverk (format) og i det åpnede vinduet på fanen Border (grenser) slå på linjen med ønsket farge på bunnkanten. Etter å ha klikket på OK regelen vår vil fungere og horisontale stiplede linjer vil vises mellom gruppene av linjer
Metode 2. Med filterstøtte for tall og datoer
En liten, men veldig merkbar ulempe med den første metoden er at slike grenser ikke alltid vil fungere riktig når du filtrerer listen etter andre kolonner. Så hvis vi for eksempel filtrerer tabellen vår etter datoer (bare i januar), vil linjene ikke lenger være synlige mellom alle land, som før:
I dette tilfellet kan du komme deg ut ved å bruke funksjonen SUBTOTALER (SUBTOTAL), som kan utføre ulike matematiske operasjoner (sum, gjennomsnitt, antall, etc.), men "se" bare filtrerte celler. La oss for eksempel sortere tabellen vår etter siste kolonne med dato og trekke en skillelinje mellom dagene. I betinget formatering må du lage en regel som ligner på den første metoden, men ikke bruk direkte lenker for å sammenligne cellene D2 og D3, men omslutt dem som argumenter i SUBTOTAL-funksjonen:
Det første argumentet til funksjonen (nummer 109) er summeringsopkoden. Faktisk legger vi ikke til noe her og gjør faktisk en dum operasjon som SUM (D2), som selvfølgelig er lik D2. Men denne funksjonen skiller seg fra SUM nettopp ved at den utfører handlinger kun på synlige celler, dvs. og cellene som er igjen etter filteret på skjermen vil bli sammenlignet, noe vi ønsket.
Metode 3. Med filterstøtte for alle data
Som du lett kan se, har den andre metoden også en ulempe: sumfunksjonen kan bare brukes på tall eller datoer (som også er tall i Excel), men ikke på tekst. Det vil si at hvis vi vil trekke en linje mellom land, som i den første metoden, men slik at den vises riktig etter filtrering, så må vi bruke en mye mer komplisert bane. Velg hele tabellen på nytt bortsett fra overskriften, lag en ny regel basert på formelen og skriv inn følgende konstruksjon i valideringsfeltet:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ($A$1:$A2;СТРОКА($A$1:$A2)-МИН(СТРОКА($A$1:$A2));;1));—($A$1:$A2=$A2))=1
I den engelske versjonen blir det:
=SUMPRODUCT(SUBTOTAL(103;OFFSET($A$1:$A2;ROW($A$1:$A2)-MIN(ROW($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Ved å klikke på knappen Rammeverk (format) sett en kantlinje med en rød linje på toppen og klikk OK. Den resulterende inndelingen etter land vil fungere riktig selv etter filtrering, for eksempel etter dato:
- Fremhev datoer og klokkeslett med betinget formatering
- Hvordan Excel faktisk fungerer med datoer og klokkeslett
- Hvordan bruke betinget formatering for å markere celler etter tilstand i Excel