Tagarchief: Aantal.als

Van rijen naar kolommen, deel 2

Excel AccessIn mijn vorige artikel (Van rijen naar kolommen) heb ik oplossingen aangedragen voor het ‘verplaatsen’ van gegevens van rijen/records naar kolommen/velden; een oplossing in Excel én eentje in Access.
Voor mijn Access-oplossing had ik het gevoel, dat het slimmer/handiger zou moeten kunnen, maar tot nu heb ik nog geen alternatief mogen ontvangen.

Wel heb ik zelf voor de Excel-routine een betere oplossing bedacht; die wil ik jullie niet onthouden.

Probleem

Iemand had mij het volgende gevraagd:

“Ik heb een bestand met leningnummers. Daar staan ook polisnummers en poliswaarden in, maar ze staan per lening onder elkaar; Rijen1ik wil voor iedere lening 1 regel met daarachter alle polisgegevens.”

Een voorbeeldje zie je hiernaast; de bedoeling is dat alle polissen en bijbehorende waardes van leningnummer 1 achter dit leningnummer komen, voor 2 idem etc.
Niet alle leningen hebben evenveel polissen.

Excel

Bij mijn vorige oplossing (zie Van rijen naar kolommen en het  Voorbeeldbestand 1) had ik enkele tussenstappen nodig om tot het juiste resultaat te komen. Ik realiseerde me later dat het makkelijker en eleganter zou moeten kunnen.

Het bovenstaande probleem is eigenlijk terug te brengen tot de vraag: als we in een bepaalde rij kijken, kunnen we dan achterhalen wat het volgnummer is van de polis bij het betreffende leningnummer?

Rijen1In het Voorbeeldbestand 2 heb ik daartoe in het tabblad Basis een nieuwe kolom (E) toegevoegd. In de eerste regel komt de formule:
=AANTAL.ALS($B$3:B3;B3)
Ik moet toegeven: dat ziet er wel wat vreemd uit. We turven daar hoe vaak de inhoud van B3 (achter de punt-komma) voorkomt in het bereik B3:B3 (voor de punt-komma). Dat is natuurlijk 1 keer.
Maar wanneer we deze formule naar beneden kopiëren, zien we het nut hiervan (dankzij het absoluut maken van de eerste B3 met behulp van de $-tekens). In E4 komt dan de formule
=AANTAL.ALS($B$3:B4;B4)
Hoe vaak komt de waarde uit B4 voor in het bereik B3:B4. Dit is precies wat we wilden weten: zo krijgt ieder polisnummer een volgnummer per lening zonder dat we het bestand hoeven te sorteren.

De gegevens (inclusief kolom E) gebruiken we als basis voor een draaitabel:

  1. zet de cursor ‘ergens’ in het gegevensblok, bijvoorbeeld cel B3
  2. Rijen2kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. Excel heeft het hele blok gegevens dan geselecteerd (in dit geval B2:E21); het enige wat we hoeven doen is op OK te klikken
  4. in een nieuw tabblad komt de lay-out van de draaitabel. Rijen3Rechts kunnen we de inhoud aanpassen: sleep LenNr naar de Rijlabels, VolgNr naar de Kolomlabels en PolisNr en Waarde naar het Waardegebied.
  5. Nog wat aanpassingen (verwijder de rij- en kolomtotalen) en het gewenste resultaat is klaar.

In het Voorbeeldbestand 2 in het tabblad Ovz is dit resultaat terug te vinden, inclusief een vergelijking met de Access-uitkomst.

De ‘moderne’ standaard-lay-out van een draaitabel is niet altijd even duidelijk (wat doen die kopjes Kolom- en Rijlabels daar?).
In het tabblad Ovz2 staat dezelfde draaitabel maar met een andere indeling:

  1. zet de cursor ergens in de draaitabel
  2. kies in de menutab Hulpmiddelen voor draaitabellen het tabblad Ontwerpen
  3. binnen Rapportindeling kiezen we dan Tabelweergave
  4. nog wat kleurtjes en lijnen et voilà:

Rijen4

Cadeau

Los van de inhoud van dit artikel: ik kwam pas op de site www.myonlinetraininghub.com terecht. Daar is een e-book met Tips & Tricks te downloaden.
Altijd interessant en het mag verder verspreid worden, dus bij deze: Excel_Tips&Tricks_e-BookV1.1


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: