InterVal

Interval-sjabloon_7


Wat illustreert het sjabloon InterVal ?


Het kan voorkomen dat we een lijst met elementen willen onderverdelen naar categorien. In dit sjabloon en in deze handleiding staan een aantal oplossingen uitgewerkt op welke wijze dit op basis van numerieke waarden is te realiseren.



a. Oplossing 1 Vast interval

De eerste oplossing gaat uit van het categoriseren van onderstaande lijst op basis van een vast interval.


Interval-sjabloon_4


Voordat voor de lijst op basis van de omzet de categorien kunnen worden bepaald is het nodig om aan te geven in hoeveel categorien de onderverdeling moet plaatsvinden en wat de ondergrens en de bovengrens is. Deze Hulpwaarden kunnen in het sjabloon worden ingegeven. Worden de ondergrens en de bovengrens leeggelaten, dan wordt uitgegaan van de kleinste en de grootste omzet in de lijst. Op basis van de ingevoerde gegevens worden de categorien bepaald en vindt de berekening van de categorie plaats. In het voorbeeld is gekozen voor een onderverdeling in 5 categorien met een ondergrens en bovengrens van resp. 10.000 en 100.000 EUR.


Interval-sjabloon_2

De formule waarmee de categorie bij een filiaal wordt bepaald ziet er als volgt uit:


=AFRONDEN.NAAR.BOVEN((<Omzet>-<Minimale ondergrens>)/<Intervalstapgrootte>;0)


Op basis van de ingegeven parameters worden de intervallen berekend. Zie hiervoor de afbeelding hieronder.


Interval-sjabloon_3


Het is mogelijk in het sjabloon andere grenswaarden en aantal categorien in te vullen. De verschillende waarden worden herberekend. De voor het sjabloon ingesteld maximale aantal categorien bedraagt 50.

b. Oplossing 2 Ongelijke intervallen

Er kan zich een situatie voordoen waarbij het gewenst is de intervalgrootte variabel te kiezen. Hiervan is een voorbeeld uitgewerkt in het 2e werkblad van het sjabloon (On)gelijke intervallen. Het voorbeeld gaat uit van eenzelfde lijst als de voorgaande oplossing.


Interval-sjabloon_6


Omdat in de oplossing wordt uitgegaan van intervalgroottes die niet even groot zijn is het nodig handmatig de intervallen te definieren. Dit in tegenstelling van het voorgaande voorbeeld waar de intervallen automatisch werden berekend. Voor het invoeren van de intervallen van verschillende omvang is het alleen nodig om de ondergrenzen te definieren. Daarnaast is er voor de beschreven oplossing nog de kolommen Interval en hulp opgenomen.


Interval-sjabloon_5



b.1. Met gebruikmaking van hulpkolommen

In de kolom Berekend Interval (Hulpkolom) wordt het interval berekend met gebruikmaking van de gedefinieerde ondergrenzen, de formule =SOM.ALS() en de kolom Hulp. In de formule wordt de "hulpteller" voor elke categorie gesommeerd waarvoor geldt dat de omzet van het betreffende filiaal groter is dan de ondergrens van het interval. Op deze manier kan de categorie worden bepaald.
De gebruikte formule voor het bepalen van de categorie (interval) ziet er hierbij als volgt uit:


=SOM.ALS(<Grenswaardenbereik>;"<="&<Omzet filiaal>;<Hulpkolombereik>)

Voorbeeld formule zoals in het sjabloon is opgenomen:


=SOM.ALS($O$8:$O$12;"<="&G8;$Q$8:$Q$12)



b.2. Met gebruikmaking van matrixformules

In de kolom Berekend interval (matrixformules) wordt het interval berekend met gebruikmaking van matrixformules. Hierbij wordt ook gebruik gemaakt van de gedefinieerde ondergrenzen en van de formules =MAX(), =VERGELIJKEN() en ALS(). Met behulp van deze formules wordt voor alle intervallen waarvoor geldt dat de omzet groter of gelijk is aan het betreffende ondergrens van het interval het maximum (ondergrens) bepaald. Met deze waarde wordt (relatief) gezocht in het bereik waarbij het gevonden rijnummer rechtstreeks wordt overgenomen als categorie (interval). In pseudocode ziet de formule er als volgt uit (Denk eraan dat voor een matrixformule de formule wordt afgesloten met ctrl-shift-enter!).


{=VERGELIJKEN(MAX(ALS( Omzet>=Ondergrensbereik;Ondergrensbereik;0));Ondergrensbereik;0)}

Voorbeeld formule zoals in het sjabloon is opgenomen:

{=VERGELIJKEN(MAX(ALS(G8>=$O$8:$O$12;$O$8:$O$12;0));$O$8:$O$12;0)}



c. Overige oplossingen:

Er zijn nog meer wegen die naar Rome leiden. Naast de in het sjabloon uitgewerkte oplossingen kan gebruik worden gemaakt van VBA om een eigen door de gebruiker gedefinieerde functie (formule) te schrijven. Ook kan in het geval van een beperkt aantal intervallen de betreffende intervallen in de formule zelf hardgecodeerd worden opgenomen.

c.1 VBA User defined function (UDF)
Hieronder is een voorbeeld uitgewerkt van een functie (op te nemen in een VBA module) die dezelfde functionaliteit bevat als oplossing 1.


Function BepaalCategorie(Waarde As Double, Ondergrens As Double, _
BovenGrens As Double, Aantalcategorien As Double) As Integer

Dim Categoriegrootte As Double

Categoriegrootte = ((BovenGrens - Ondergrens) / Aantalcategorien)
BepaalCategorie = Int((Waarde - Ondergrens) / Categoriegrootte) + 1

End Function



c.2 Hardgecodeerde, boolean

Een boolean (waar, onwaar) heeft de eigenschap bij WAAR de waarde "1" te hebben en bij ONWAAR de waarde "0", deze eigenschap kan in combinatie met hardgecodeerde grenswaarden van het interval worden gebruikt om het interval te berekenen.
De formule ziet er dan als volgt uit:


=1*(EN(Omzet>0; Omzet<=10000))+2*(EN(Omzet>10000;Omzet<=20000)+....+10*(EN(Omzet>90000;Omzet<=100000)


Hierbij staat de constante 1, 2, 3, ... 10 voor het interval. Valt een omzet niet in de categorie, dan wordt deze vermenigvuldigd met 0, valt de omzet in de categorie, dan wordt deze vermenigvuldigd met 1. Een voorwaarde hierbij is dat de omzet slechts in een categorie mag vallen.

Hierboven zijn een aantal oplossingen gegeven waarmee binnen Excel met de problematiek van intervallen kan worden omgegaan. Ongetwijfeld zijn er nog andere manieren om hiermee om te gaan.


Heb je nog vragen of suggesties, neem dan gerust contact met ons op.

Inloggen

Inloggen

Mrah Development BV

MRAHDevelopment

Mrah Development BV
Voor uw Excel vragen,
Leuke gratis Excel toepassingen.

ExcelFIX

links_partners_5

ExcelFIX
ExcelFIX herstelt beschadigde
en corrupte spreadsheets die
gemaakt zijn in Microsoft Excel.

Infotron BV


linkspartners_5

www.infotron.nl
Zelf al goed met Excel, maar wilt u bestanden beter begrijpen, valideren en verbeteren?

Exhelp.be

Links_partners_20

ExcelFIX
Dé Vlaamse Excel blog