Maandelijks archief: januari 2015

Aantallen turven

De afgelopen weken kreeg ik in diverse situaties vragen over het tellen van aantallen.

Wat simpel lijkt, wil in geautomatiseerde systemen nog wel eens ingewikkelder uitpakken.
Of het nu het tellen is van het aantal regels in een Excel-bestand, het aantal lege velden in een Excel-kolom, het aantal records in een Access-tabel of het aantal niet-lege namen in een Access-record: het blijft goed opletten wat en hoe er geteld moet worden.

ToepenAantallen turven:
toen ik dit intikte, gingen mijn gedachten weer terug naar de vele gezellige avonden, die we lang geleden met een groepje hebben doorgebracht met het kaartspel Toepen. Bij dit spel worden de strafpunten op een bier- (of sinas-?)viltje geturfd door het zetten van streepjes.

Voor de liefhebbers volgt hieronder, na wat uitleg over het turven van aantallen in Excel en Access, een “geautomatiseerd bierviltje”.
Ik heb daarbij ook een weinig bekende manier van kopiëren gebruikt.

Excel

AantallenIn het Voorbeeldbestand staat in het tabblad Aantal vanaf cel  D15 een overzicht van de omzet per maand en regio van een kleine witgoedhandel (ze verkopen alleen stofzuigers, strijkijzers en broodroosters).

Uiteraard willen we de totale omzet weten (dat lossen we simpel op via een Som-formule);  maar vandaag zijn we meer geïnteresseerd in Aantallen.

AantallenExcel kent diverse standaard-functies om aantallen te berekenen.
De meest simpele (en meest gebruikte) functie is Aantal. Maar als we die toepassen op het bereik D15:D158 (de maanden) dan krijgen we als resultaat 0; idem voor Regio en Product. Pas het turven van aantallen in de kolom Bedrag levert een aantal groter dan nul op, namelijk 142. De functie Aantal telt alleen maar de inhoud van numerieke cellen!

NB een datum is voor Excel ook een getal; dus de functie Aantal turft ook datums (zie ook de cellen L18:M21 in het Voorbeeldbestand).

De functie Aantalarg voldoet al beter aan de verwachtingen (zie het overzicht hierboven): deze functie telt hoeveel cellen er gevuld zijn, onafhankelijk van de inhoud.

Mbv de functie Aantal.lege.cellen (denk aan de puntjes!) kunnen we analyseren of en hoeveel lege cellen er in een cellenbereik voorkomen.

Voor de volledigheid heb ik ook nog enkele voorbeelden van de functies Aantal.als en Aantallen.als opgenomen; zie voor nadere uitleg het artikel Tellen met voorwaarden.

Turf-alternatief

Om snel te kunnen achterhalen in welke regel er gegevens ontbreken, heb ik achter iedere regel, in kolom H, mbv de functie Aantalarg geteld hoeveel velden er gevuld zijn.
AantallenDoor cellen, die NIET 4 opleveren, via Voorwaardelijke opmaak te kleuren zien we direct in  welke regels het fout gaat
(in één van de komende artikelen zal ik op Voorwaardelijke opmaak terugkomen).

Voor de liefhebbers nog een andere manier van tellen waarbij gebruik wordt gemaakt van zogenaamde Array- of Matrix-formules.
Wanneer we in Excel in een cel intypen =D15<>”” (we kijken dus of cel D15 NIET leeg is) dan levert dit de waarde WAAR op (als die cel gevuld is tenminste!). Maar intern vertaalt Excel dit naar een 1 (en ONWAAR naar een 0; het blijft een computerprogramma!). Daar kunnen we gebruik van maken:
typ in een cel =1*(D15<>””) en we krijgen als resultaat 1.
NB denk aan de haakjes rond de voorwaarde.

We zouden in cel I15 kunnen intikken =1*(D15<>””)+1*(E15<>””)+1*(F15<>””)+1*(G15<>””) om te achterhalen hoeveel cellen er niet leeg zijn, maar dat kan makkelijker:

  1. tik in cel I15 in =Som(1*(
  2. selecteer de cellen D15 t/m G15
  3. en tik dan in (allemaal in cel I15) <>””))
  4. niet op Enter drukken, maar Ctrl-Shift-Enter

Excel zal nu voor alle cellen in het bereik D15:G15 beoordelen of die leeg is; vermenigvuldigd met 1 levert dit een serie 0’en en 1’en op, die door de functie Som bij elkaar worden opgeteld.

Door de combinatie Ctrl-Shift-Enter wordt er een Array- of Matrix-formule ingevoerd; in de formulebalk wordt dit weergegeven door {} rond de formule.

NB alleen op bovenstaande manier kun je een array-formule invoeren; het plaatsen van accolades rond een formule werkt niet!

Kopieer de array-formule naar beneden (via de vulgreep rechtsonder in cel I15 natuurlijk; zie het artikel Doorvoeren en sorteren).

Access

Binnen dit programma moeten we het doen met veel minder (functie-)mogelijkheden. Voor het tellen van het aantal records in een tabel kunnen we een Totaal-query maken en dan per veld in de regel Totaal: aangeven dat we het Aantal willen zien.
Aantal AccessHet resultaat hiervan komt overeen met het gebruik van de functie Aantalarg in Excel; dus het aantal niet-lege velden wordt geturfd voor ieder type veld.

Willen we het aantal niet-lege velden binnen een record weten (net als de berekening in de cellen H15 en I15 hiervoor in Excel) dan zullen we zelf een formule moeten intypen in de query.
Aantal Access

Optie 1:
Gevuld: IIf([Maand]<>””;1;0) + IIf([Regio]<>””;1;0) + IIf([Product]<>””;1;0) + IIf([Bedrag]<>0;1;0)

De eerste als-voorwaarde (in Access Iif) levert een 1 als Maand niet leeg is, anders 0; de tweede voorwaarde hetzelfde voor Regio enz. Door de 4 resultaten van de Iif’s op te tellen zien we per record hoeveel velden er gevuld zijn.

NB Bedrag is in Access een numeriek veld; de inhoud kunnen we dan niet vergelijken met een lege tekst, dus we controleren of de waarde niet gelijk is aan 0.

Optie 2:
Gevuld2: IIf(Not IsNull([Maand]);1;0) + IIf(Not IsNull([Regio]);1;0) + IIf(Not IsNull([Product]);1;0) + IIf(Not IsNull([Bedrag]);1;0)

Vergelijkbaar met Optie 1, maar we controleren mbv een Access-functie IsNull of een veld leeg is.

Optie 3:
Gevuld3: -(Not IsNull([Maand])) – (Not IsNull([Regio])) – (Not IsNull([Product])) – (Not IsNull([Bedrag]))

Dit is iets korter dan optie 2, waarbij we gebruik maken van de eigenschap dat de waarde WAAR in Access door -1 wordt voorgesteld (en ONWAAR door 0; dit laatste is WEL hetzelfde als in Excel!?).

NB denk aan de juiste plaats en het aantal van de haakjes.

Toepen

Zoals beloofd een “Excel-bierviltje”; zie het Voorbeeldbestand.
De (straf)puntentelling bij het kaartspel Toepen gebeurt van oudsher door het zetten van streepjes, waarbij een vijftal door een dwarsstreep wordt aangegeven. Heb je 15 strafpunten, dan lig je er uit; 14 punten is ook een bijzondere situatie: de tegenstanders krijgen bij verlies 2 strafpunten (of ze passen voor 1 punt). In het Brabants wordt dit “Pulleke” genoemd, op het “scorebord” aangeduid door een P.

In het tabblad ToepBasis worden alle benodigdheden voor het scorebord klaar gezet:

  1. Toepenin het blok B2:C4 staan de gebruikte symbolen, waarbij de cellen C2 t/m C4 een overeenkomende naam hebben gekregen (zie het Naamvak, linksboven)
  2. in de cellen B8:D12 staan de spelersnamen en hun scores: de stand binnen het lopende ‘potje’ en het aantal gewonnen ‘potjes’.
  3. ToepenF6:H12 is het scorebord/Excel-bierviltje.
    In kolom F worden de deelnemers gecombineerd met hun resultaten door cellen en teksten met behulp van het &-teken te koppelen; in cel F8 staat bijvoorbeeld =B8&”   (“&C8&”/”&D8&”)”
  4. kolom G is de meest interessante: hier wordt de score uit kolom C (de cellen C8:C12 hebben samen de naam Score gekregen) omgezet in de juiste streepjes:
    =ALS(Score>=15;Vijftal&Vijftal&Vijftal;ALS(Score=14;Vijftal&Vijftal&Paal;HERHALING(Vijftal;INTEGER(Score/5))&HERHALING(Streepje;REST(Score;5))))
    * als de Score groter of gelijk aan 15 is, dan wordt 3x de tekst uit de cel met de naam Vijftal geplaatst
    * bij een Score van 14, twee vijftallen en het teken voor het “Pulleke”
    * anders via de functie HERHALING zoveel vijftallen als we krijgen wanneer we Score door 5 delen (INTEGER betekent geheel getal) en zoveel streepjes als de REST is bij het delen van de Score door 5.
  5. kolom H bevat een simpelere vorm van de formule uit kolom G
  6. nog wat Voorwaardelijke opmaak toevoegen (‘kapot’: rode achtergrond, ‘Pulleke’: oranje achtergrond, meer dan 9 punten: oranje letters) en het Scorebord is klaar!

LET OP: de naam Score bevat de cellen C8 t/m C12. Wanneer we in kolommen rechts hiervan in een formule verwijzen naar Score, dan neemt Excel de met de rij overeenkomende waarde uit deze reeks. Dus in cel G8 wijst Score naar de waarde in cel C8, in G9 naar cel C9 etc.

ToepenHet “Excel-bierviltje” vinden we terug op het tabblad ToepScore.
Om het turven nog makkelijker te maken bevat het tabblad diverse Besturingselementen, een 10-tal zogenaamde Kringvelden Kringveld. Hiermee is het mogelijk om de scores snel te verhogen en te verlagen. De Kringvelden zijn gekoppeld aan de diverse scores in het vorige tabblad; klik met de rechtermuisknop op een Kringveld en bekijk de instellingen onder de optie Besturingselement opmaken …
Kringvelden
(en andere besturingselementen) vinden we onder de menu-tab Ontwikkelaars.

Het scoreformulier zelf is GEEN serie cellen maar een Excel-afbeelding; wanneer je er op klikt, zie je in de hoeken en in het midden van de randen plaatsen waar je met de cursor de afbeelding kunt vergroten en verkleinen.
En niet zo maar een statisch plaatje; nee, een zogenaamde Gekoppelde afbeelding. De inhoud van de afbeelding verandert met de inhoud van de cellen, waaraan deze gekoppeld is! Mooier kunnen we het niet maken….

Hoe maken we een gekoppelde afbeelding?

  1. selecteer de cellen, die in de afbeelding moeten worden weergegeven (voor ons scorebord de cellen F6:H12 in het tabblad ToepBasis)
  2. klik rechts in de selectie en kies Kopiëren
  3. Toepenga naar de cel waar de afbeelding moet komen, klik rechts en kies de optie Plakken speciaal ….
    In het submenu kies je onder Andere plakopties de 4e mogelijkheid, Gekoppelde afbeelding
  4. pas via de grepen aan de zijkant van de afbeelding de grootte aan en/of verplaats het plaatje naar de gewenste locatie.

NB Gekoppelde afbeeldingen kunnen een belangrijke rol spelen in rapportages. Wanneer je daar meerdere overzichten onder elkaar wilt weergeven, zal het meestal zo zijn, dat de gewenste kolom-breedtes van die overzichten niet met elkaar overeenkomen; maar Excel kan standaard maar één breedte weergeven.
Zet  de overzichten naast elkaar op een tabblad (of nog beter op verschillende tabbladen) en maak van deze overzichten Gekoppelde afbeeldingen; deze kun je dan onder elkaar zetten, zonder dat de kolombreedtes elkaar nog beïnvloeden.

LET OP Gekoppelde afbeeldingen vergen veel van Excel en van je PC! Sla je werk regelmatig op en gebruik niet teveel dynamische afbeeldingen in één werkmap.


Kalender 2015

Ik dacht het nieuwe jaar te beginnen met het maken van een Excel-kalender en die te beschrijven in een G-Info-artikel.
kalenderChandooMaar ‘helaas’ te laat: de Indiase  Excel-guru Chandoo heeft vorige week een heel mooie kalender gepubliceerd; die wil ik u niet onthouden.
Ik heb deze omgezet in het Nederlands en de basiskalender iets anders ingericht, zodat de opbouw waarschijnlijk begrijpelijker is (hoewel de Chandoo-routine voor de liefhebbers echt kicken is!).

Deze keer geen uitleg van het Voorbeeldbestand; ‘kijk’ zelf rond en ontdek hoe allerlei handige opties van Excel (zoals Voorwaardelijke opmaak) gebruikt kunnen worden om een mooie Excel-toepassing te creëren.
Maar begrijp je een optie niet: schroom niet om G-Info om uitleg te vragen!

Ben je geïnteresseerd in de oorspronkelijke kalender (en meer van het werk van Chandoo) en zijn achtergrondartikelen: kijk dan op Chandoo.org.

De aangepaste, Nederlandstalige versie kun je hier downloaden.


Doorvoeren en sorteren

De titel klinkt als een term uit de breiwereld, maar het heeft toch echt alles met Excel te maken.

Een paar weken geleden, tijdens een Excel-cursus die ik gaf, bleek weer eens hoe weinig mensen de handige optie ‘Doorvoeren’ van Excel ten volle kennen; laat staan gebruiken.
En bijna niemand weet dat je je eigen reeksen kunt maken, die op dezelfde manier gebruikt kunnen worden; en dat deze reeksen ook voor sortering gebruikt kunnen worden.

Doorvoeren

Laten we eerst even kijken wat doorvoeren eigenlijk is:
Doorvoereneen cel,  gevuld met een waarde (dat kan een getal, datum of tekst zijn) kun je snel kopiëren door de zogenaamde vulgreep rechtsonder in de cel met de muis naar beneden of naar rechts te verslepen.

Stel dat cel B1 het getal 1 bevat (download  het Voorbeeldbestand);  wanneer we dan de vulgreep ‘naar beneden trekken’ tot in rij 10 dan zal Excel alle tussenliggende cellen met een 1 vullen.
DoorvoerenMaar let op: rechtsonder komt een kleine button tevoorschijn: de Opties voor Automatisch doorvoeren.
Deze opties zijn context-gevoelig, dat wil zeggen dat het aantal opties en de mogelijkheden afhankelijk zijn van de inhoud van de gekopieerde cel.
DoorvoerenIn dit geval (we hebben het cijfer 1 gekopieerd) zal Excel ons 4 opties tonen wanneer we op de button klikken: Cellen kopiëren (in dit geval de standaardactie), Reeks doorvoeren, Alleen opmaak doorvoeren en Doorvoeren zonder opmaak.
De laatste 2 opties spreken voor zich. De tweede (Reeks doorvoeren) is het meest interessante: wanneer je daar nu op klikt zal Excel niet de 1 uit cel B1 kopiëren, maar de volgende rijen vullen met 2, 3 etcetera!

NB in iedere regel van de Opties voor Automatisch doorvoeren is een letter onderstreept; dat betekent dat je niet met de muis op zo’n optie hoeft te klikken, je kunt ook de betreffende onderstreepte letter op het toetsenbord intikken.

Laten we dat ook eens met een datum doen: in het Voorbeeldbestand is cel C1 gevuld met 1-1-2015.
DoorvoerenTrek de vulgreep van deze cel naar rij 10 en de cellen worden gevuld met de eerste 10 dagen van het jaar!
DoorvoerenKlik op de optie-button rechtsonder en we zien de diverse mogelijkheden. Blijkbaar gebruikt Excel bij een datum doorvoeren standaard de 2e optie (Reeks); dit in tegenstelling tot getallen, waar standaard de kopieeractie wordt uitgevoerd.
De eerste vier opties snappen we nu, de vijfde is exact gelijk aan de tweede, maar de zesde is om van te watertanden! En de zevende en de achtste zijn ook uiterst handig.
Probeer de mogelijkheden uit. Trek de reeks verder door dan 31 dagen en daarna ook voorbij februari: Excel weet precies hoeveel dagen er in een maand zitten.

Nog even terug naar de getallen: wanneer we de cellen D1 en D2 vullen met respectievelijk 1 en 2, we selecteren de TWEE cellen Doorvoerenen slepen de vulgreep naar beneden, dan kiest Excel wel als standaard de optie Reeks doorvoeren (en krijgen we dus de getallen 1 t/m 10); cellen kopiëren levert in dit geval allemaal blokjes van de getallen 1 en 2 op.

Stel we vullen de cellen E1 en E2 met de getallen 1 en 3; doorvoeren van deze 2 cellen levert een mooie reeks van 1, 3, 5, 7 etc.
LET OP:  met een reeks als 1, 4, 9 (de volgende zal dan toch wel 16 zijn?) kan Excel niet overweg.

Dit opent perspectieven: willen we een overzicht van alle maandagen in 2015 dan vullen we een cel met de eerste maandag (5-1-2015) en de cel daaronder met de tweede (12-1-2015), we selecteren deze twee cellen en slepen de vulgreep!

Teksten doorvoeren

Hiervoor hebben we gezien welke mogelijkheden Excel biedt voor het doorvoeren van getallen (voor Excel zijn datums gewoon getallen maar met een speciale opmaak).
Welke mogelijkheden zijn er voor teksten?

In het Voorbeeldbestand is cel F1 gevuld met de tekst een. Voer deze cel door en Excel vult alle cellen met dezelfde tekst. Klikken op de button Opties voor Automatisch doorvoeren laat zien dat er maar drie mogelijkheden zijn: Kopiëren (de standaardactie) en wel of niet de opmaak doorvoeren.

Zijn er twee cellen gevuld (G1 en G2 met een en twee), worden deze geselecteerd en doorgevoerd, dan ontstaat er een opeenvolging van deze twee tekstcellen.

Verrassing!?
DoorvoerenCel H1 is gevuld met het woord ma.
Wanneer we deze cel doorvoeren, ontstaat er de bekende reeks ma, di , wo, do, vr, za, zo!
Is de doorvoering groter dan 7 rijen of kolommen, dan wordt de reeks herhaald.

En ook hier zit onder de button Opties voor Automatisch doorvoeren de handige optie om alleen Weekdagen door te voeren.

Wat de mogelijkheden zijn, wanneer we een cel met het woord maandag, jan of januari doorvoeren, zal nu wel niet meer verbazen.

NB wanneer de eerste cel met een hoofdletter begint (of alles is in hoofdletters) dan zal Excel bij het doorvoeren dat automatisch overnemen.

Snel doorvoeren

Stel dat we van een grote reeks getallen het kwadraat zouden willen weten (en wie wil dat niet?) dan kunnen we diverse, benodigde handelingen snel uitvoeren (zie ook het tabblad SnelDoorv in het Voorbeeldbestand):

  1. in de cellen B1 en B2 zetten we de getallen 1 respectievelijk 2
  2. selecteer deze 2 cellen en voer met behulp van de vulgreep deze selectie naar beneden door voor het aantal getallen, waarvan je het kwadraat wilt weten
  3. in cel C1 zetten we de tekst in het kwadraat =
  4. dubbelklik op de vulgreep van cel C1 en de tekst wordt automatisch zo vaak naar beneden gekopieerd als er in kolom B getallen staan!
  5. in cel D1 komt de formule =B1^2
    Het caret-teken wordt in Excel gebruikt om een machtsverheffing aan te duiden (zoals Wikipedia zegt: Not to be confused with Carrot or Carat).
  6. dubbelklikken op de vulgreep van cel D1 en ook deze formule is tig-keer gekopieerd

LET OP: dubbelklikken heeft alleen effect als de kolom links of rechts van de door te voeren cel gevuld is.

Sorteren

De reeksen voor de dagen en maanden kunnen ook gebruikt worden om een logische sortering door te voeren.

DoorvoerenIn het Voorbeeldbestand heb ik ook een tabblad Sorteren opgenomen, waarin een overzicht staat van de omzet van een heel kleine witgoedhandel: per verkocht apparaat staat vermeld in welke maand voor welk bedrag dit is verkocht.

Om dit schema wat overzichtelijker te maken is het handig om te sorteren op de maand: selecteer een cel in de maand-kolom en sorteer in de volgorde A-Z (met behulp van de button Steekproef binnen de menuoptie Gegevens of door rechts te klikken en dan Sorteren te kiezen).
Het resultaat is waarschijnlijk niet wat je verwacht (of gehoopt) had: Excel zal standaard de gewone alfabetische volgorde aanhouden.

DoorvoerenMaar daar kunnen we wat aan doen:

  1. klik rechts op een cel in de maand-kolom
  2. kies Sorteren en dan de optie Aangepast sorteren
  3. klik in het vervolgscherm onder Volgorde op het keuzedriehoekje Doorvoeren
  4. klik op Aangepaste lijst en kies de lijst met maanden
  5. nog 2x op OK klikken en de nieuwe sortering is klaar!

De nieuw ‘geleerde’ volgorde blijft actief totdat deze op de voorgaande manier wordt aangepast.

Eigen sortering/reeks

We kunnen onze omzet ook op product sorteren: we krijgen dan de volgorde Koelkast, Magnetron, Strijkijzer.

Wanneer we (om een of andere reden) de keukenapparaten achteraan willen hebben, is dat standaard niet mogelijk, maar …..

  1. open het tabblad Sorteren van het Voorbeeldbestand
  2. klik rechts op een cel in de product-kolom
  3. kies Sorteren, Aangepast sorteren, keuzedriehoekje bij Volgorde en dan Aangepaste lijst
  4. Doorvoerenin het blok Gegevens in lijst zetten we de drie apparaten in de gewenste volgorde (gescheiden door Enter)
  5. klik op Toevoegen
  6. 2x OK en de nieuwe sortering is klaar!

NB deze aangepaste lijst zal door Excel voortaan ook gebruikt worden bij het Doorvoeren. Tik in een cel Strijkijzer in en voer de cel met behulp van de vulgreep door: de volgende cel wordt Magnetron, dan Koelkast, opnieuw Strijkijzer etc.

NB2 de aangepaste lijsten worden NIET in het betreffende werkblad vastgelegd, waarin ze zijn gemaakt, maar vormen een aanpassing op de Excel-omgeving. Ook in andere werkbladen kunnen de nieuwe reeksen dus meteen toegepast worden.

Dergelijke aangepaste lijsten zijn zeer bruikbaar: bij een bedrijf-eigen productindeling, bij waarderingscodes (denk aan de financiële waardering AAA, AA+, AA, AA-, A, BBB …) etcetera