[Excel]Cellen in rij tellen, criteria per kolom

[FireAge]

Ik ben een excel sheet aan het bouwen waarin ik meetresultaten kan verwerken.
Het idee is om in de eerste kolom het serienummer te zetten, en dan in de kolommen erna de metingen inclusief groeperingen van metingen.

Vervolgens wordt er de mean/max/min van de metingen bepaald, waarbij metingen die buiten de acceptabele grenzen vallen, niet worden meegenomen.

Zie plaatje voor de layout.


Ik heb via voorwaardelijk opmaak gezorgd dat als de cellen niet binnen de grenzen vallen, dat ze dan rood worden, en anders groen. Tevens heb ik het min/max/mean gedeelte voor elkaar via dbgemiddelde() achtige functies.

Nu wil ik echter per subgroep gaan tellen hoeveel tests er zijn gefaald. Zo zou in het plaatje bijvoorbeeld "C2" 0 weer moeten geven, "C3" zou 1 moeten zijn en C4 zou 2 moeten zijn.

Ik heb reeds gezocht, en onder andere deze thread doorgenomen: [Excel] AANTAL.ALS in kolom1 én in kolom 2
Echter werkt dit niet voor mijn eisen.

In principe wil ik alles automatisch schaalbaar hebben. Als ik een kolom invoeg, dan moet het subtotaal in de subgroep die meenemen in zijn berekening.

Mijn voorwaarde voor rood in kolom D: OF(D2>D$23;D2<D$24)*(ABS((D2="")-1))

Ik zou kunnen doen:
C2= OF(D2>D$23;D2<D$24)*(ABS((D2="")-1)) + OF(E2>E$23;E2<E$24)*(ABS((E2="")-1)) + OF(F2>F$23;F2<F$24)*(ABS((F2="")-1))

Maar als ik dan een vierde meting erbij gooi in subgroep 1, dan moet ik die handmatig toe gaan voegen.
Dus ik wil eigenlijk bovenstaande realiseren met een aantal.als().

C2 = aantal.als( D2:F2 ; OF( D2>D$23 ; D2<D$24 ) * ( ABS((D2="")-1) )
Maar dan checkt hij D2, E2 en F2 op de voorwaarde van D2.
Ik kom er maar niet uit, hoe ik kan zorgen dat hij daar de juiste verwijzingen pakt.

Ik kan het wel oplossen door een verborgen tabblad te maken waarop ik elke cel check en een 1 of 0 laat schrijven op die plaatsen als het rood of groen is, maar ik wil dat liever voorkomen.

Iemand enig idee hoe ik die aantal.als formule voor elke cel die hij telt een andere kolom als voorwaarde kan laten gebruiken?

EDIT:
Ik heb het nu opgelost met een functie.
Mocht iemand een mooiere oplossing hebben, dan hou ik mij aanbevolen.

code:

Function SubgroupCounter(Search_Range As Range, Max_Row As Integer, Min_Row As Integer) As Integer

Dim cntr As Integer
Dim rRange As Range

cntr = 0

    For Each rRange In Search_Range
        If ((rRange.Value < Cells(Min_Row, rRange.Column).Value) Or (rRange.Value > Cells(Max_Row, rRange.Column).Value)) Then
            cntr = cntr + 1
        End If
    Next rRange
    
    SubgroupCounter = cntr

End Function

[ Voor 11% gewijzigd door FireAge op 28-08-2009 13:31 . Reden: Oplossing toegevoegd ]

[onkl]

Matrixformules.
Copy-paste onderstaand naar de formulebalk in C2. Sla dan niet op enter, maar op CTRL-SHIFT-ENTER.
=SOM(ALS(D2:O2="";0;ALS(D2:O2<=D$23:O$23;ALS(D2:O2>=D$24:O$24;1;0);0)))

Als je even de help op matrixformules doorleest en in bijv. rij 25 de subgroep waar de test bijhoort invoert om nog een extra conditie op te voeren, is het geheel redelijk onderhoudsvrij.

[FireAge]

Oeh, dat is wel een stukje mooier dan met een formule.
Ik zal het maandag even proberen.

Ik had al wel posts gezien over matrixformules, maar die vergeleken dan matrices met waardes en niet met andere matrices.

[FireAge]

Ok, ik ben al een stuk verder gekomen met die matrixformules.

Dit is mijn huidige resultaat:


Ik heb de cel waar het mis gaat met geel aangegeven.
De in die regel gebruikte formule is (via CTRL-SHIFT-ENTER):

=ALS(F2:F20="";"";GEMIDDELDE(ALS(F2:F20<=F23;ALS(F2:F20>=F24;F2:F20;"");"")))

Ik wil dat als de hele kolom leeg is, de gele cel leeg blijft.
Als een deel van de kolom leeg is, dat deel niet gebruiken.

Dit werkt met deze formule, behalve als de bovenste cel (F2) leeg is, want dan maakt hij de cel ook leeg terwijl er wel waarden onder staan.

EDIT: NVM, opgelost met
=ALS(EN(F2:F20="");"";GEMIDDELDE(ALS(F2:F20<=F23;ALS(F2:F20>=F24;F2:F20;"");"")))

[ Voor 9% gewijzigd door FireAge op 31-08-2009 09:55 ]