Rijkleur in tabel - Automatische opmaak zonder VBA

[NLI]

Voor een overzicht wilde ik de rijkleuren om en om donkerder en lichter maken. Echter bij het invoegen van een rij, verbergen van een rij, of filteren is de om en om combinatie niet meer in tact.

De volgende twee oplossingen zijn vrij eenvoudig en makkelijk toe te passen in ieder werkblad. Beide versies zijn zonder VBA, wat de flexibiliteit vergroot. Ik denk dat velen hier wat aan kunnen hebben en daarom deel ik het hier!
Onderstaande screenshots zijn van het voorbeelddocument wat ik evenals heb toegevoegd.

Versie 1
De 'Conditional Formatting Rules Manager' bied hierbij de eerste optie. Met de eenvoudige formule (Select a Rule Type > Use a formula to determine which cells to format) =ISODD(ROW()) kleuren de oneven rijen.



Versie 2
Echter lost dat niet het probleem op bij verborgen rijen of bij gebruik van het filter. Hiervoor heb ik in een verborgen extra kolom (A) de formule =IF(AGGREGATE(3;5;INDIRECT("A"&(ROW()-1)))=1;IF(INDIRECT("A"&(ROW()-1))=0;1;0);INDIRECT("A"&(ROW()-1))) gezet. Deze geeft als uitkomst een 0 of 1 . Afhankelijk van de uitkomst in de bovenliggende cel, wordt de huidige uitkomst bepaald. Enwel zo dat het om en om 0 en 1 word. Omdat iedere formule gelijk is en geen vaste verwijzing heeft naar een rijnummer, kan deze op een willekeurige plek (in dezelfde kolom) staan. Dit is handig als men een filter gebruikt welke de rijen, en daarmee ook de formules, door elkaar kan husselen.
Met Conditional Formatting kan nu de rijkleur worden bepaald. In onderstaand voorbeeld is dat =$A41=1. De bovenste rij is altijd 0 en heeft daarom geen kleur.



Gebruik van het filter (verberg B, E en F):


Probeer het zelf met onderstaand document of kopieer en pas het toe in je eigen documenten

Ik sta open voor verbeteringen of aanvullingen. Misschien kan het nog eenvoudiger en/of flexibeler.

 

[jkpieterse]

Start tab, Opmaken als tabel oftewel Home, Format As Table?

 

[NLI]

Opmaken als tabel is inderdaad een goed alternatief. Een nadeel ervan is wel dat formules binnen de tabel anders functioneren en Excel de tabel ook als tabel blijft zien; niet als 'normale' cellen. Soms kan dit ook een voordeel zijn natuurlijk, dat zal afhangen van de wensen van de gebruiker en applicatie. Dank voor je reactie.

 

[jkpieterse]

Ik zeg altijd: als het eruit ziet als een tabel, opmaken als tabel. De voordelen zijn zoveel groter dan de nadelen (die er inderdaad wel zijn)

 

[peter59]

Of bedoel je soms dit?
https://www.gratiscursus.be/Excel_Tips/Excel_Tip_221.html

 

[NLI]

Grappig. Dat werkt inderdaad ook best goed, echter niet met verborgen rijen.

 

[peter59]

Dan raad ik je toch aan om de raad van @jkpieterse op te volgen.

 

[NLI]

Zoals eerder besproken heeft dat (ook) voor en nadelen.
Voor mij werkt mijn eigen oplossing het beste. Zie onderstaand voorbeeld - waar het overigens de kolommen betreft.

Omdat AGGREGATE niet in kolommen werkt, heb ik het zo opgelost: =IF(LEFT(TEXTJOIN("";0;CELL("width";INDIRECT("R"&ROW()&"C"&COLUMN()-1;0)));1)<>"0";IF(INDIRECT("R"&ROW()&"C"&COLUMN()-1;0)=0;1;0);INDIRECT("R"&ROW()&"C"&COLUMN()-1;0)) Nadeel is dat het werkblad eerst ververst moet worden om de kleuren te herstellen na het verbergen van één of meerdere kolommen. In dit werkblad betreft het een matrix van werknemers en de uren. Er kan gefilterd worden op naam (andere namen worden verborgen). De 'banded columns' blijven dan tevens in tact. De extra formule staat in de rij 1 van iedere kolom.

 

[HSV]

Indien kolom C in je tabel geen lege cellen bevat.
=SUBTOTAAL(103;C$41:C41)

Vw. opmaak op =IS.EVEN(........

 

[NLI]

Beste HSV, Ik kan je niet volgen. Je formule krijg ik niet werkend. Komt =ISODD(ROW()) of =ISODD(COLUMN()) in vw. opmaak niet op hetzelfde neer? Dit voldoet overigens niet vanwege verborgen rijen of kolommen.

 

[HSV]

Voldoet niet?

Misschien iets beter je best doen.
Rij 44 is verborgen en er is zelfs gefilterd op C in de eerste kolom.

 

[NLI]

Zo'n bijlage maakt alles duidelijk. Ik snap nu ook niet waarom ik het niet begreep... Dat is een mooie eenvoudige toe te passen oplossing.
Detail: Na even spelen ging het mis toen ik een rij (rij 43) kopieerde en daaronder plakte. Dat probleem is volgens mij weer opgelost als de formule in voorwaardelijke opmaak op cel A41 in plaats van A40 wordt gezet.

 

[peter59]

Misschien een hele stomme vraag maar waarom wil je zoiets op deze manier aanpakken?
Oftewel wat is de achterliggende gedachte in deze?

 

[NLI]

@peter59
Heel simpel: Opmaak van een tabel, maar zonder dat het een tabel is. Ik wil de flexibiliteit houden van 'normale cellen'.

Ik liep hier tegen aan bij het werken aan een bestaande planning van een afdeling met horizontaal de weeknummers en de werknemers en verticaal de projecten. Ik heb de opdracht om het op te poetsen; overzichtelijker te maken. Het betreft een soort dubbele matrix. Zie eerste bijlage #8. De manier hoe Excel omgaat met tabellen is in dit geval niet wenselijk (om meerdere redenen). Daarom kwam ik op deze oplossing.

 

[peter59]

Oké, dan lijkt het mij iets voor power query.
Maar met alleen .jpg's als bijlagen gaat het natuurlijk niet lukken.

 

[jkpieterse]

Misschien is het eenvoudiger te proberen toch een tabel te gebruiken en te kijken hoe je om die beperkingen heen kunt werken? Ik zie tegenwoordig het niet gebruiken van een tabel als "bad spreadsheet practice"

 

[NLI]

Dank voor al die oprechte aangebode hulp. Mijn doel was eigenlijk alleen een tip te geven hoe het óók kan. En daar uit voortvloeiend andere ideeën die natuurlijk welkom zijn.

@peter59: Ik zie hier op Helpmij veel over power query voorbij komen. Ik ben een behoorlijk vergevorderde Excel gebruiker, al zeg ik het zelf, maar hoe meer je weet hoe duidelijker het ook wordt hoe veel er nog te leren is! Ik ben dus nog niet bekend met power query. Het ziet er veel belovend uit en zou inderdaad voor dit doel een goed alternatief kunnen zijn wat ik er nu van gezien heb.
Hoe zou je dit met power query aanvliegen?

@jkpieterse: Ik ben zelf niet de gebruiker. Als je zelf de gebruiker bent is het een ander verhaal en zou het eventueel kunnen - je kan dan rekening houden met een soort testfase of met de wetenschap van de werking van een tabel daar inderdaad omheen werken. Maar ik ben ook van mening dat het in dit geval praktischer werkt als de cellen gewoon normaal reageren op input en (tijdelijke) formules en daarom een externe formule samen met voorwaardelijke opmaak de kleurtjes kunnen bepalen zonder dat er iets (een tabel) in de weg zit.

 

[peter59]

Hoe zou je dit met power query aanvliegen?

Begin met het toevoegen van een Excel-bestand zoals het nu is, als startpunt. Vervolgens moeten er aanpassingen worden gedaan om de gewenste output te bereiken. Zorg er wel voor dat de basis is ontdaan van gevoelige informatie. Dat lijkt al het geval te zijn, aangezien je in #8 een Praktijk.jpg had geüpload.

Ik heb de opdracht om het op te poetsen; overzichtelijker te maken.

Laat dus ook zien hoe het eindresultaat er ongeveer uit zou moeten zien.
Bijna alle formules/berekeningen zullen op de achtergrond via Power Query plaatsvinden.
Dit zal het mijns inziens alles veel overzichtelijker en efficiënter maken.
Met andere woorden, in jouw geval betekent dit dat je toch even moet gaan omdenken.

 

[NLI]

In #8 had ik slechts enkele cellen leeg gemaakt voor het screenshot. Ik zal het hele werkblad eens schoonvegen en hier uploaden. Dan zie ik graag een voorzetje. Want het is lastig om te vertellen hoe ik dat zelf via power query kan proberen zonder dat je het bestand in handen hebt? Ik zal volgende week even kijken of ik ruimte heb om het bestand te ontdoen van bedrijfsinformatie.

Ik heb het eindresultaat al, het is zo goed als af . Ik heb een ruim versiebeheer, dus zal ook de eerste, nog kale, versie bijvoegen voor het verschil. Qua opmaak is dit overigens nihil, ik heb vooral een stukje VBAcode toegevoegd voor filterfuncties, dus mogelijk is het niet eens noodzakelijk om de eerste versie bij te voegen.

Dank voor het meedenken!