Excel: Opmaak en presentatie van gegevens

Voorwaardelijke opmaak

Gebruik in uw modellen de optie Format / Opmaak – Condititonal Formatting / Voorwaardelijke opmaak om de ingevoerde gegevens te controleren, om uitzonderingen in een analyse aan te geven of bij het presenteren van berekende gegevens. Wanneer een bepaalde celwaarde bijvoorbeeld de gespecificeerde waarde overstijgt, wordt dat met een andere lettertype / kleur / rand duidelijk aangegeven.

Voorbeeld: Stel, u heeft in de cellen F2 t/m F4 berekeningen van kosten. Als een van de waarden van die cellen groter wordt dan 12000, dan wilt u dit snel kunnen zien.  Neem eventueel deze gegevens over in een leeg werkblad. Selecteer de cellen F2 t/m F4 en ga naar de optie Format / Opmaak – Conditional Formatting / Voorwaardelijke opmaak. 

Stel eerst de voorwaarde in en klik daarna op Format / Opmaak om de opmaak aan te passen. Verander de opmaak naar keuze. Met het knopje Add / Toevoegen kunt u extra voorwaarden toevoegen. Met het knopje Delete / Verwijderen kunt u ingestelde voorwaarden verwijderen. Klik op OK als u klaar bent.

Verander de waarde in de cellen F2 t/m F4 (maak ze kleiner dan 12000). Wat gebeurt er? Voer nu gegevens groter dan 12000. Wat gebeurt er? Een andere mogelijkheid voor het gebruik van conditional formatting / Voorwaardelijk opmaak: We willen nu door middel van een formule de conditional formatting / voorwaardelijk opmaak instellen.

Stel, we hebben in een werkboek in een sheet de ondergrens en de bovengrens van de kosten per onderdeel. In een andere sheet (wel in hetzelfde werkboek) hebben we door middel van kostencalculaties de kosten per onderdeel berekend. Nu willen we door middel van Conditional formatting / Voorwaardelijke opmaak gaan kijken naar onze reeds vastgestelde kostenmarge en als de gecalculeerde kosten onder de ondergrens komen willen we ze een kleur geven, als ze boven de vastgestelde bovengrens komen dan willen wij ze een andere kleur geven. Als de gecalculeerde kosten tussen de vastgestelde grenzen blijven, dan hoeven ze geen specifieke opmaak te krijgen.

In het scherm hieronder is het probleem in Excel gezet.  De oplossing ziet er als volgt uit:

Stap 1: Geef het gebied met oorspronkelijke gegevens een naam. Noem ze bijvoorbeeld Kosten1.

Stap 2: Selecteer cel F2. Ga naar Format / Opmaak – Conditional Formatting / Voorwaardelijke opmaak. Kies in het eerste gedeelte voor Formula is en typ in het rechtergedeelte de formule zoals hieronder. In de nederlandse versie typt u de volgende formule in: =$F2Vert.zoeken(E2; kosten1; 3; onwaar) 

Stap 3: Kopieer cel F2. Selecteer cel F3 en F4 en plak met de optie Paste special / Plakken speciaal alleen de Format / Opmaak. Het werkboek ziet er nu als volgt uit: Verander de ondergrens en bovengrens in het linkergedeelte en kijk wat er gebeurt met de opmaak in de rechtergedeelte. 

Invoer valideren in Excel-sheets

Het komt vaak voor dat u tijdens het invoeren van gegevens in Excel ervoor wilt zorgen dat in een bepaalde bereik alleen items uit een voorgedefinieerde lijst ingevoerd mogen worden. Een voorbeeld:  In kolom C willen we een regio uit de lijst in kolom E kunnen kiezen. Dit doet u als volgt:

Selecteer de cellen E2 t/m E13 en geef ze een naam. Selecteer dan de hele kolom C beginnend met cel C2 ( Selecteer cel C2 en druk dan op Ctrl+ Shift+ ) Neem deze gegevens eerst in een leeg werkboek over. Ga naar de menuoptie Data / Data – Validation / Valideren.

Kies bij Settings / Toegestaan voor de optie List / Lijst. Kies bij Source / Bron voor de menuoptie Insert / Invoegen – Name / Naam – Paste / Plakken en kies hier de naam van het bereik regio’s. Typ eventueel bij Input Message / Invoerbericht een tekst in en bij Error Alert / Foutmelding een foutmelding. Klik op OK. Selecteer een cel in de kolom waar de regio’s in moeten komen.

Wat gebeurt er? Als u het goed heeft gedaan, krijgt u het volgende scherm te zien:  Tip: Als u namen gebruikt voor het bereik waar de oorspronkelijke gegevens in staan, kunt u ook verwijzen naar een andere sheet (in hetzelfde werkboek). Anders is verwijzing naar andere sheets voor het valideren van gegevens niet mogelijk. Voegt u extra gegevens in het bereik met de oorspronkelijke gegevens en past u de naam aan dan krijgt u ook gelijk in de kolom met validatie de extra items te zien.

De volgende vraag is: Kan ik met behulp van de optie Data / Data – Validation / Valideren ervoor zorgen dat in kolom A (klantnummers) alleen unieke gegevens ingevoerd mogen worden? Hiervoor gaat u als volgt te werk: Selecteer cel A4. Ga naar de menuoptie Data / Data – Validation / Valideren.

Typ de formule in zoals hieronder:  Excel zoekt eerst met de functie Vlookup / Vert.zoeken naar de net ingevoerde waarde in de cellen met bestaande (ingevoerde) klantnummers. Als de ingevoerde klantnummer niet aanwezig is (met de functie ISNA / ISNB wordt dit gecheckt) dan kunt u het invoeren. Anders krijgt u de ingestelde foutmelding.

Opmerking: Als u eerst gegevens invoert in een Excel-sheet en dan over de ingevoerde gegevens een validatie zet, dan zal Excel geen melding geven dat bepaalde bestaande gegevens misschien niet aan de gegeven validatie voldoen. Om deze gegevens uit te zoeken in uw Excel-sheet kunt u de optie Tools / Extra – Auditing / Controleren – Show auditing Toolbaar / Werkbalk controleren weergeven gebruiken. Heeft u het werkbalk Controleren aangezet, dan gebruikt u het voorlaatste knopje om ongeldige gegevens op te sporen.

Voorbeeld: In het werkblad hieronder is in kolom A een validatie ingesteld. Alleen gegevens groter dan 25 mogen ingevoerd worden. Op het moment dat de validatie ingesteld werd, waren de waarde in de cellen A1 t/m A4 al ingevoerd. Hieronder ziet u hoe Excel de ongeldige gegevens omcirkelt.