Hoe werkt het sjabloon RelatieVolgnummer.xls ?

Vrij taaie kost. Het sjabloon beschrijft een manier om zonder matrixformules en vba een volgnummer van elementen binnen een categorie - al dan niet op basis van verschillende sorteringscriteria - te berekenen. Een wijziging in één of meerdere van deze criteria zal direct in de lijst en het volgnummer zichtbaar worden gemaakt.

In het sjabloon is zowel een eenvoudig als een ingewikkeld voorbeeld opgenomen. Deze handleiding beperkt zich tot de uitleg van het eenvoudige voorbeeld.

Noodzakelijk voor het bepalen van een volgnummer van een element binnen een categorie zijn:

• Vaste lengte categorie
• Unieke identificatie van een element met een vaste lengte
• Een (hulp) kolom met de concatenatie van de betreffende substrings, gescheiden door een speciaal teken.

N.B. De vaste lengte kan natuurlijk ook als tussenbewerking in Excel worden bewerkstelligd.

De formule voor het berekenen van een volgnummer maakt gebruik van de Excel-functie "=AANTAL.ALS". Daarnaast wordt geen moeite gedaan om de categorien te bepalen (relatie), maar wordt er gerekend met elementen binnen de TOTALE lijst. Te berekenen kenmerken zijn:

1. Hoeveel elementen komen in de ongesorteerde lijst (alfabetisch gezien) NA het beginelement van een categorie.
2. Hoeveel elementen komen in de ongesorteerde lijst (alfabetisch gezien) NA een element.
3. Hoe bereken ik dan de positie (volgnummer) van het element BINNEN de categorie.

ad 1. AANTAL.ALS(E:E;">"&B5&"|AAAAAAA")

Hoe bepaal je hoeveel elementen ten opzichte van de volledige lijst na het EERSTE element van een categorie liggen. Hiervoor is het noodzakelijk een stringwaarde samen te stellen met een substring die de betreffende categorie (relatie) bevat en een substring waarvan met zekerheid kan worden gezegd dat er - alfabetisch gezien - geen kleinere waarde in de lijst voorkomt.

1. "E:E" is de kolom met de hulpstring waarin de categorie met de unieke identificatie van het element is geconcateneerd. Deze kolom is noodzakelijk voor de plaatsbepaling van een element binnen de totale lijst.
2. "B5" bevat de categorie waarbinnen het element valt.

ad 2. AANTAL.ALS(E:E;">"&E5)

Hoe bepaal je hoeveel elementen ten opzichte van de volledige lijst na het HUIDIGE element binnen een categorie liggen.

1. "E:E" is de kolom met de hulpstring waarin de categorie met de unieke identificatie van het element is geconcateneerd. Deze kolom is noodzakelijk voor de plaatsbepaling van een element binnen de totale lijst.
2. "E5" bevat de concateneerde string van categorie (relatie) een unieke identificatie van het element (inclusief scheidingsteken).

Ad 3. Het verschil tussen ad 1 en ad 2.

=AANTAL.ALS(E:E;">"&B5&"|AAAAAAA") - AANTAL.ALS(E:E;">"&E5)



Het volgnummer van het element binnen een categorie (relatie) is gelijk aan het verschil van de uitkomsten zoals berekend in ad 1 en ad 2.

Dezelfde formules zijn van toepassing voor het complexe voorbeeld. De berekeningen gelden voor zowel een gesorteerde als een ongesorteerde lijst. De berekening kent meerdere toepassingen.


Veel plezier met het sjabloon.


Naar boven . . .