Villkorsstyrd formatering med olika färg varannan grupp?

[Mikael63]

Att formatera ett Excelblad där varannan rad har fyllnadsfärg är ju vanligt förekommande men här skulle jag vilja uppnå liknande detta:

Där är det olika färg varannan grupp. (jag ser nu att det är olika nyanser av grönt men det ska vara endast två olika färger, ex. ljusblå och ljusgrön)

I exemplet ovan är det de sex första tecknen som bestämmer tillhörighet. (I verkligheten är det, alltid, 21 tecken).

För den första färgen finns här tre likadana där de inledande tecknen är lika. Sedan när de inte är lika börjar färg nummer två och här är det två sådana innan likheten upphör och det åter ska vara färg nummer ett som även i detta läge råkar vara tre rader osv.

Är detta möjligt att ordna? 

Jo, jag får manuellt se till att de är sorterade.

[OveS]

Enklast är att skapa en hjälpkolumn som du kan dölja och skapa en formel som som visas i bifogad bildexempel. Bygg omformeln så det passar din situation.

Skapa sedan två villkorsstyrda formler med olika fyllningsfärger enligt nedan exempel.

[Mikael63]

Tack!

Fungerar bra så länge det inte infogas en rad någonstans vilket medför att det inte fungerar i praktisk användning.

Ett makro som skapar hjälpcellerna fungerar troligen men då måste det vara en makroaktiverad arbetsbok + att man måste komma ihåg att köra det makrot.

[OveS]

Se till att formattera tabellen som en tabell (klicka på en cell i tabellen och tryck därefter CTRL+T). När du vill infoga rader i tebellen så kommer formlerna att beräknas om till de nya radreferensen.

Vi använder oss av förskjutning för att hela tiden hunna ha samma radreferens i formeln och i beräkningen gör en förskjutning mot raden ovanför. Ändra formeln i första cellen i hjälpkolumnen enligt denna skärmbild:

=OM(FÖRSKJUTNING($A2;-1;0)<>"Kolumn1";OM(VÄNSTER($A2;4)=VÄNSTER(FÖRSKJUTNING($A2;-1;0);4);FÖRSKJUTNING($D2;-1;0);ICKE(FÖRSKJUTNING($D2;-1;0)));SANT)

 

Redigerad Maj 22 av OveS
Komplettering

[MH_]

****ED***

Kollade inte ditt exempel ordentligt. Samma start-bokstäver kan ju återkomma. Ignorera

***/ED***

En matris av alla vänsterdelar i A-kolumnen (fram till aktuell rad, obs att det inte är något $-tecken före ettan i sista $A1)

=VÄNSTER($A$1:$A1;4)

Lista bara unika:

=UNIK(VÄNSTER($A$1:$A1;4))

Och hur många unika finns det (t.om den här raden):

=ANTALV(UNIK(VÄNSTER($A$1:$A1;4)))

Och till sist: Är det ett jämt eller udda antal unika?

=ÄRUDDA(ANTALV(UNIK(VÄNSTER($A$1:$A2;4))))

=ÄRjämn(ANTALV(UNIK(VÄNSTER($A$1:$A2;4))))

Tyvärr kommer även tomma celler på slutet att markeras. Antingen gör man ett OM() som kollar att A är ifylld

=OCH($A1<>"";ÄRUDDA(ANTALV(UNIK(VÄNSTER($A$1:$A1;4)))))

 

Eller lägg ett villkor först i listan som stoppar om A-kolumnen är tom

 

Redigerad Maj 22 av MH_

[MH_]

Bahhh. så mycket för enkla lösningar (om du vill att den skall funka på översta raden):

=ÄRUDDA(SUMMA((VÄNSTER($A$1:$A2;4)<>VÄNSTER($A$2:$A3;4))*1)-(VÄNSTER($A2;4)<>VÄNSTER($A3;4))*1)

 

[Mikael63]

Tack båda!

Om jag greppat rätt är lösning från @MH_ att man skriver villkoret direkt i regeln?

Vidare tolkar jag att

49 minuter sedan, MH_ sade:

=ÄRUDDA(ANTALV(UNIK(VÄNSTER($A$1:$A2;4))))

=ÄRjämn(ANTALV(UNIK(VÄNSTER($A$1:$A2;4))))

är det som ska stå som villkor men att ärudda ersätts med

 

33 minuter sedan, MH_ sade:

=ÄRUDDA(SUMMA((VÄNSTER($A$1:$A2;4)<>VÄNSTER($A$2:$A3;4))*1)-(VÄNSTER($A2;4)<>VÄNSTER($A3;4))*1)

 

Hos mig skulle det då bli så här:

Resultatet blir inte okej med detta och inte heller med den tidigare ärudda.

Att tomma celler på slutet får den ena eller den andra färgen gör inget alls.

 

Tillägg:

Det kan finnas godtyckligt antal rader där de första 21 tecknen är likadan. I praktiken dock 1-5 rader som mest.

Redigerad Maj 22 av Mikael63

[Mikael63]

1 timme sedan, OveS sade:

Se till att formattera tabellen som en tabell (klicka på en cell i tabellen och tryck därefter CTRL+T).

Det ger ett oönskat utseende.

[MH_]

Nja. Min tanke var att du skulle byta ut både ÄRUDDA och ÄRJÄMN. Men jag var inte glasklar.

 

MEN Det är första raden som krånglar till det. Man vill egentligen jämföra med raden ovanför. Men det blir knas om man startar från rad1. 

Med det ser ju ut som om ditt område startar på rad 3 och då kan du förenkla allt. Se till att cell A3 är aktiv och kör formlerna:

***ED*** Skrev fel trots att jag gjorde rätt i bilden...

=ÄRUDDA(SUMMA((VÄNSTER($A$2:$A2;21)<>VÄNSTER($A$3:$A3;21))*1))

respektive

=ÄRJÄMN(SUMMA((VÄNSTER($A$2:$A2;21)<>VÄNSTER($A$3:$A3;21))*1))

***/ED*** Skrev fel trots att jag påpekade saken under bilden...

Obs att formeln skall vara skriven som om den skrevs för den översta vänstra cellen i området "gäller för". I din skärmdump börjar formlerna på A1 trots att området börjar i A3.

Redigerad Maj 22 av MH_

[Mikael63]

Stort tack!

Det blev då hos mig så här:

 

Jag fick flytta upp min "gula" regel som varnar för dubbletter.

[Mikael63]

Nä... även här skiter det sig om man infogar en rad...

[MH_]

Då får du försöka skapa en regel (med ord) som avgör vad so skall räknas som ett område.

I mitt sista exempel (och OveS om jag läser rätt) så antar man att ett nytt område påbörjas när de första tecknen i A-kolumnen skiljer sig från ovanstående. Och det gör de ju om du infogar en tom rad mitt i.

Om man vet att de 21 första tecknen aldrig återkommer kan man filtrera bort tomma celler  och köra: 
=ÄRJÄMN(ANTALV(UNIK(FILTER(VÄNSTER($A$3:$A3;21);$A$3:$A3<>""))))
=ÄRudda(ANTALV(UNIK(FILTER(VÄNSTER($A$3:$A3;21);$A$3:$A3<>""))))

Då blir regeln att den byter färg så fort det kommer nya (unika) värden i de 21:a första tecknen i A1. Men om de 21:a första tecknen återkommer i en ny grupp så missar man bytet

 

Eller också fortsätter du att krångla till formeln genom att dra bort antalet tomma rader ovanför två gånger (De ger två falska "byten". Ett när den tomma cellen jämförs med värdet ovan. Och ett när värdet jämförs med med den tomma cellen ovan):

=ÄRUDDA(SUMMA((VÄNSTER($A$2:$A2;21)<>VÄNSTER($A$3:$A3;21))*1)-ANTAL.OM($A$3:$A3;"")-ANTAL.OM($A$2:$A2;""))

=ÄRjämn(SUMMA((VÄNSTER($A$2:$A2;21)<>VÄNSTER($A$3:$A3;21))*1)-ANTAL.OM($A$3:$A3;"")-ANTAL.OM($A$2:$A2;""))

Men den kommer å andra sidan att floppa så fort du sätter in en tom rad mellan två områden...

 

Eller så här om du låter varje unik 21-serie få en färg:

=ÄRUDDA(PASSA(VÄNSTER($A3;21);UNIK(VÄNSTER(FILTER($A$3:$A$402;$A$3:$A$402<>"");21));0))

Men då har du åter risken för att de unika värdena dyker upp en gång till.

 

Så jag tror faktiskt att det är väldigt svårt att sätta upp en vettig regel. 

Ett alternativ skulle vara att alltid jämför A-värdet med senast ifyllda rad

=TA(VÄNSTER(FILTER($A$2:$A3;$A$2:$A3<>"");21);-1)

Men sen blev det jobbigt...

 

[Mikael63]

Tack, har inte läst än men problemet när man infogar en rad är inte den tomma raden, vad som visas eller ej i den. Problemet är att reglerna ändras, alltså att området som regeln ska gälla för blir annorlunda, det läggs till ytterligare regler osv.

Skulle önska att man kunde låsa dessa på något sätt?

Tillägg: Tanken är att jag "alltid" (efter att ha infogat/redigerat/raderat osv.) sorterar efter kolumn A varför de unika 21 tecknen borde sorteras tillsammans (på nytt).

Redigerad Maj 23 av Mikael63

[Mikael63]

3 timmar sedan, MH_ sade:

Eller så här om du låter varje unik 21-serie få en färg:

=ÄRUDDA(PASSA(VÄNSTER($A3;21);UNIK(VÄNSTER(FILTER($A$3:$A$402;$A$3:$A$402<>"");21));0))

Ja detta verkar inte messa till reglerna.

(Det här strulet med reglera har jag märkt fanns i andra filer där det fanns två olika regler för att kolla dubbletter)

Tillägg: Kollade i en sådan äldre fil och där kan det se ut så här:

Varför det finns två olika färger kommer från en helt annan bok där jag ville ha en färg för två förekomster och en annan för fler än två förekomster.
Här i de aktuella böckerna är det bara en varning om det finns mer än en unik förekomst.

Det som är knepigt är antalet och området de gäller för. Det ska bara vara koll i A3 och neråt och färgen ska bara påverka A3 och neråt.

OBS! Jag har löst, eller snyggat till, just den delen. Fattar inte hur det kunnat bli så här och varför det blev liknande strul med en "ärudda" och en "ärjämn" enligt tidigare inlägg?

 

Redigerad Maj 23 av Mikael63
Bild

[MH_]

Jag TROR att området förblir intakt, eller utökas logiskt om man infogar en hel rad (som Shift+mellanslag, Ctrl++)

Men så fort man infogar delar av en rad, kör klipp ut/klistra in  eller släpar något med musen så fragmenteras det. 

Tyvärr går det inte att låsa med indirekt() eller ett namngivet område eftersom "villkorsstyrd" (precis som diagram) omvandlar det till ett addres-område som förblir instabilt.

Det är väl helt enkelt så det är byggt. 

[Mikael63]

Ja infoga (eller radera) hel rad har jag snabbknappar för och när jag testade tycktes inte det påverka. Troligen är det kopiera/klipp ut/klistra in som har ställt till det. Dock tror jag att själva funktionaliteten är intakt men om man får för sig att redigera reglerna, eller kolla för att återanvända dem kommer man att fatta nada, vilket i mitt fall inte är ett helt ovanligt tillstånd...