Matrix formule opstellen excel 2010

[nellekejeurissen]

Hallo allemaal,

Ik wil graag in excel het gemiddelde en de range bepalen van een aantal getallen. Aan de hand van een voorbeeld zal ik het uitleggen.

Ik heb een kast met boeken, die allemaal een genre hebben en een bepaalde prijs waren.
Ik heb per boek dus aangegeven in welk genre het valt en wat het mij gekost heeft

Ik wil 2 dingen berekenen: Het gemiddelde en de range per genre.

Nu heb ik gemerkt dat het met gemiddeldes prima gaat met =GEMIDDELDE.ALS(B1:B120;C1:C120;"fictie")

Maar ik wil dit dus ook met een range, en neem aan dat dit met MIN en MAX gaat.
Ik wil als dit kan het laagste getal hebben die hoger is dan 0 (want sommige boeken heb ik gratis gekregen, maar wil ik niet mee laten tellen) en dus per genre het weergeven. Dit geldt ook voor het maximum, maar dan alleen per genre.

Wie kan mij helpen?

 

[Tent84]

Hoi Nelleke,

Ik heb wat moois voor je gebouwd.
Maak een macro door ctrl+F11 te kiezen en een nieuwe module in te voegen (of gebruik een lege macro via "Opnemen macro" en overschrijf deze lege macro via ctrl+F8). Plak daar de tekst die helemaal onderaan staat. Je kunt dan MAXvar en MINvar gebruiken als gewone Excelformules. In de formule vul je eerst de zoekwaarde (bijv. "fictie"), daarna de kolomnummer van de zoektekst (bijv. 1 voor kolom A) en als laatst de kolomnummer van de prijzen (bijv. 2 voor kolom B). Je formule kan er dan zo uit zien:

=maxvar("fictie";1;2)
=minvar("fictie";1;2)



Function MAXvar(zoekvar As String, KolomZoekvar As Integer, KolomMaxWaarde As Integer) As Variant
For x = 1 To ActiveSheet.Range("A65536").End(xlUp).Row
If Cells(x, KolomZoekvar).Value = zoekvar Then
If Cells(x, KolomMaxWaarde).Value > maxZoekvar Then maxZoekvar = Cells(x, KolomMaxWaarde).Value
End If
Next x
If maxZoekvar = 0 Then maxZoekvar = ""
MAXvar = maxZoekvar
End Function
Function MINvar(zoekvar As String, KolomZoekvar As Integer, KolomMinWaarde As Integer)
For x = 1 To ActiveSheet.Range("A65536").End(xlUp).Row
If Cells(x, KolomZoekvar).Value = zoekvar And Cells(x, KolomMinWaarde).Value <> "0" Then
If minZoekvar > 0 Then
If minZoekvar > Cells(x, KolomMinWaarde).Value Then minZoekvar = Cells(x, KolomMinWaarde).Value
Else:
minZoekvar = Cells(x, KolomMinWaarde).Value
End If
End If
Next x
If minZoekvar = 0 Then minZoekvar = ""
MINvar = minZoekvar
End Function

 

[Jezet]

Hoi Nelleke,

Ik kan mij voorstellen dat je geen VBA-oplossing wilt. Het kan ook met een matrix-formule, die voer je in door Shift, Ctrl en Enter tegelijk in te drukken als je de formule hebt ingetypt.

Overigens kun je niet MIN gebruiken, anders dan met een trucje, leg ik zo uit.
MAX:
=MAX((B1:B120="fictie")*(C1:C120>Minbedrag)*(C1:C120<Maxbedrag)*(C1:C120))
MIN:
=999-MAX((B1:B120="fictie")*(C1:C120>Minbedrag)*(C1:C120<Maxbedrag)*(999-C1:C120))
GEMIDDELDE (geen matrix-formule):
=SOMPRODUCT((B1:B120="fictie")*(C1:C120>Minbedrag)*(C1:C120<Maxbedrag)*(C1:C120))/SOMPRODUCT((B1:B120="fictie")*(C1:C120>Minbedrag)*(C1:C120<Maxbedrag))
Tweede (en andere) hoogste prijs voor het maken van een lijst van duurste boeken:
=GROOTSTE((B1:B120="fictie")*(C1:C120>Minbedrag)*(C1:C120<Maxbedrag)*(C1:C120);2)

Min- en Maxbedrag zelf even invullen (mag verwijzing naar cel zijn.

Hoe de formules werken kun je het beste zien als je het bereik kleiner maakt, bijv. B1:B12.
=(B1:B12="fictie")
Druk nu op F2 en daarna op F9 voor het resultaat. Je zult zien dat het resultaat bestaat uit 12 resultaten WAAR of ONWAAR. Als in B1 fictie staat, dan is het eerste resultaat WAAR, anders ONWAAR, enz

Als je de formule uitbreidt met de min. prijs
=(B1:B12="fictie")*(C1:C12>10)
dan staat er niet meer WAAR of ONWAAR maar 1 of 0 (WAAR of ONWAAR)

Breidt je de formule nog verder uit dan zie je niet de 1 of 0, maar de prijs:
=(B1:B12="fictie")*(C1:C12>10)*(C1:C12<50)*(C1:C12)
Als de prijs van een fictieboek nu tussen 10 en 50 euro ligt, volgt uit de 1e 3 vergelijkingen een 1 die vermenigvuldigd wordt met de prijs (dat ook overigens voor een boek buiten ie range, maar dan wordt het resultaat 1*1*0*100=0).
De prijs van het fictieboek dat je niet gratis hebt gekregen is dan:
=(B1:B12="fictie")*(C1:C12>0)*(C1:C12) (je hoeft namelijk geen bovengrens in te vullen)
Van deze prjizen kun je met MAX de hoogste prijs berekenen.

Voor Min moet je een truc uithalen, immers dat boek van 100 euro, geeft als resultaat 0 en zou best wel eens de laagste prijs kunnen zijn. Daarom moet je de prijs omdraaien:
=(B1:B12="fictie")*(C1:C12>10)*(C1:C12<50)*(100-C1:C12)
De prijs die je nu ziet is het bedrag 100 minus de prijs van het boek, het boek van 20 euro geeft het resultaat 80 en het boek van 45 euro geeft het resultaat 55. Het boek dat dus het dichtste bij de ondergrens zit geeft dus het hoogste resultaat. Een boek dat buiten de range valt, blijft 0. Met MAX kun je die er weer uitfilteren, door het resultaat van de MAX-formule af te trekken van 100, krijg je de oorspronkelijke prijs. Voorwaarde is dat de laagste prijs natuurlijk lager is dan 100, vandaar dat ik in de formule 999 heb gezet (getal mag je ook verder verhogen, zolang beide maar gelijk zijn).

Ik kan mij ook voorstellen dat je een top 10 wilt kunnen weergeven, dat kun je met GROOTSTE doen.

Nu kan het ook zijn dat je in A1:A120 de aanschafdatum hebt staan, dan kun je je formules uitbreiden met de tijdsperiode waarbinnen het boek gekocht is. Of de schrijver van het boek of de uitgeverij enzovoorts. Als je begrijpt hoe de formule werkt kun je bijna oneindig selecteren.

Kortom, matrix-formules zijn erg handig voor jou.

Groet,
Jeroen

 

[nellekejeurissen]

Super bedankt allebei. Hier kan ik goed mee vooruit! :thumb:

 

[Jezet]

Nog welkom op het forum overigens!