Microsoft Excel

Herbers Excel/VBA-Archiv

Mittelwert - mehrere Kriterien in einer Spalte

Betrifft: Mittelwert - mehrere Kriterien in einer Spalte von: Helmuth
Geschrieben am: 27.01.2016 23:16:08

In der Zelle C1 sollte der Mittelwert der Zahlen 2,3 und 10 der Spalte A1:A14 ausgegeben werden. Mit der Formel: =MITTELWERTWENNS(B1:B14;A1:A14;3) wird lediglich der Mittelwert der Zahl 3 errechnet. Wie kann ich die weiteren Kriterien 2 und 10 ebenfalls in der Formel hinzufügen?

Danke im voraus!

Helmuth

https://www.herber.de/bbs/user/103090.htm

  

Betrifft: AW: Mittelwert - mehrere Kriterien in einer Spalte von: Spenski
Geschrieben am: 28.01.2016 04:55:06

Hallo Helmuth

Ich lasse das Thema mal offen da sich im laufe des tages sicher jemand mit einer eleganteren Formel melden wird, aber evtl hilft dir das erst mal weiter

=(SUMMEWENN(A1:A14;2;B1:B14)+SUMMEWENN(A1:A14;3;B1:B14)+SUMMEWENN(A1:A14;10;B1:B14)) /SUMMENPRODUKT((A1:A14=2)+(A1:A14=3)+(A1:A14=10))

gruss
Christian


  

Betrifft: AW: Mittelwert - mehrere Kriterien in einer Spalte von: Helmuth
Geschrieben am: 28.01.2016 07:14:45

Hallo Christian,

vielen Dank, es klappt. Eigentlich wäre für diese Aufgabenstellung die Funktion MITTELWERTWENNS prädestiniert (Mittelwert mit mehreren Kriterien).
Doch aus einem nicht erkennbaren Grund sind mehrere Kriterien nur möglich, wenn sich auch der Kriterienbereich ändert, weshalb die Formel =MITTELWERTWENNS(B1:B14;A1:A14;2;A1:A14;3;A1:A14;10) zu keinem Ergebnis führt.

Helmuth


  

Betrifft: AW: MITTELWERTWENNS() nicht, weil ... von: ... neopa C
Geschrieben am: 28.01.2016 09:04:35

Hallo Helmuth,

... die Funktion für eine derartige Nutzung (leider) nicht programmiert ist.


Gruß Werner
.. , - ...


  

Betrifft: Der Grund: Die Bedingungen sind UND-verknüpft! orT von: Luc:-?
Geschrieben am: 28.01.2016 12:15:09

Leider sieht die Pgmmierung keine Angabe der Verknüpfung und damit Alternativen vor, Helmuth!
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: mit MITTELWERT(WENN(... als Matrixformel ... von: ... neopa C
Geschrieben am: 28.01.2016 09:00:48

Hallo helmuth,

... so:

 ABC
1113,667
222 
335 
444 
555 
661 
772 
883 
994 
10105 
11114 
12103 
1323 
1434 
15   

Formeln der Tabelle
ZelleFormel
C1{=MITTELWERT(WENN(A1:A14={2.3.10};B1:B14))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Gruß Werner
.. , - ...


  

Betrifft: Speziell für Werner (neopa)! von: Luc:-?
Geschrieben am: 28.01.2016 12:42:23

Hallo, Werner,
das ginge auch ganz ohne diese hässlichen {} und die damit verbundene FingerbrechÜbung … ;-))
Nämlich so:
3,667: =AggregateXk(-1;6;WENN(A1:A14={2.3.10};B1:B14;""))

Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: schön, aber dafür basiert es auf VBA ;-) owT von: ... neopa C
Geschrieben am: 28.01.2016 12:54:25

Gruß Werner
.. , - ...


  

Betrifft: AW: schön, aber dafür basiert es auf VBA ;-) owT von: Daniel
Geschrieben am: 28.01.2016 13:32:31

ausserdem enthält seine Formel ja auch die hässlichen "{}"!!!
(denn wenn der gemeint hätte, das man seine Formel nicht mit STRG+SHIFT+ENTER abschließen muss, dann hätte er das sicherlich auch geschrieben)
Gruß Daniel


  

Betrifft: Da irrst du, Daniel, denn die zeige ich dann ... von: Luc:-?
Geschrieben am: 28.01.2016 15:14:22

…auch. Meine dafür zuständige UDF liefert die immer mit. Für den Wegfall ist das negative 1.Argument zuständig.
Luc :-?


  

Betrifft: AW: Da irrst du, Daniel, denn die zeige ich dann ... von: Daniel
Geschrieben am: 28.01.2016 16:32:59

wo ist das Problem?
ich habe auch noch nie eine Matrixformel in {} gelammert eingegeben.
das ist absolut unnötig, den Excel fügt diese {} bei Matrixformeln ja automatisch hinzu.
man muss gegebenenfalls mal die Eingabe mit STRG+SHIFT+ENTER abschließen, damit Excel die Eingabe als matrixformel erkennt, aber {} muss man niemals dazu eingeben.


  

Betrifft: Problem: Sie sind hier nicht vonnöten bzw ... von: Luc:-?
Geschrieben am: 28.01.2016 17:41:59

…etwas exakter, die Fml hat keine MatrixFmlForm, weshalb die {} auch nicht in der Edit-Zeile gezeigt wdn und ich sie deshalb auch nicht angegeben hatte! Meine Fml-Darstellungs-UDF fragt so etwas ab und fügt die {} ggf hinzu, obwohl sie nicht eingegeben wdn müssen (man erkennt das ja an der WiedergabeForm {=…}!
Luc :-?
PS: Was ist „lammern“? Hat das was mit dem ggw BT-Präsidenten zu tun? ;-))


  

Betrifft: ...und das ist natürlich 'Teufels Beitrag' zu ... von: Luc:-?
Geschrieben am: 28.01.2016 15:17:07

…(MS-)Gottes Werk, Werner,
meint zumindest WF… ;-]
Luc :-?


  

Betrifft: AW: das mein ich nicht ... von: ... neopa C
Geschrieben am: 28.01.2016 15:23:59

Hallo Luc,

... aber noch (immer) versuche ich soviel als möglich Lösungen ohne dies zu finden, auch wenn es dadurch aus Sicht von VBAlern wie u.a. Dir, öfters nicht wirklich vertretbar scheint oder auch ist.

Gruß Werner
.. , - ...


  

Betrifft: Vorschlag war ja auch nicht bierernst gemeint! ;-) von: Luc:-?
Geschrieben am: 28.01.2016 17:44:05

:-?


  

Betrifft: AW: mit MITTELWERT(WENN(... als Matrixformel ... von: Helmuth
Geschrieben am: 28.01.2016 21:10:41

Wie muss ich jedoch vorgehen, damit diese Formel auch dann funktioniert, wenn beispielweise die Zelle B2 leer bleibt, also keinen Wert enthält?


  

Betrifft: AW: ausreichend ist dann ... von: ... neopa C
Geschrieben am: 29.01.2016 10:41:35

Hallo Helmuth,

... d.h. wenn die Zelle B2 leer ist und Du diese nicht als 0-Wert berücksichtigt haben willst,
folgende Formel:

{=MITTELWERT(WENN((A1:A14={2.3.10})*ISTZAHL(B1:B14);B1:B14))}

(ist eine WENN()-Funktion weniger, als in der Formel von Luc)

Wenn jedoch in B2 ="" sich ergeben sollte, dann reicht auch die bisherige Formel.

Gruß Werner
.. , - ...


  

Betrifft: AW: Mittelwert - mehrere Kriterien in einer Spalte von: Helmuth
Geschrieben am: 28.01.2016 20:56:44

Wie muss ich jedoch vorgehen, damit diese Formel auch dann funktioniert, wenn beispielweise die Zelle B2 leer bleibt, also keinen Wert enthält?


  

Betrifft: So, Hellmuth: von: Luc:-?
Geschrieben am: 29.01.2016 01:01:49

{=MITTELWERT(WENN(A11:A24={2.3.10};WENN(ISTZAHL(B11:B24);B11:B24)))}
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: ...Oder (etwas länger) so ... von: Luc:-?
Geschrieben am: 29.01.2016 02:15:51

…(diesmal auf neopas Bsp angepasst):
{=MITTELWERT(WENNFEHLER(0^((A1:A14={2.3.10})*ISTZAHL(B1:B14))+B1:B14;""))}
Oder in Analogie zu den von neopa oft bevorzugten AGGREGAT-Lösungen mit meiner zuvor gezeigten UDF (Vs1.2!*; diesmal ernsthaft gemeint!) am kürzesten als MatrixFml so …
{=AggregateXk(1;6;0^((A1:A14={2.3.10})*ISTZAHL(B1:B14))+B1:B14)}
…oder als NormalFml so:
=AggregateXk(-1;6;0^((A1:A14={2.3.10})*ISTZAHL(B1:B14))+B1:B14)
* Kürzlich im Archiv gelandet → RECHERCHE-Button im Forumskopf!
Luc :-?


  

Betrifft: Übrigens, die Version1.2 der UDF reagiert ... von: Luc:-?
Geschrieben am: 29.01.2016 03:04:54

…im NormalFml-Modus nicht auf Änderungen der Primärdaten. Um das zu erreichen, muss sie wie folgt zur Version 1.3 aufgerüstet wdn. Nach der PgmZeile If Funktion < 0 Then eine neue, zusätzliche Zeile einfügen:
Application.Volatile: AggregateXk = Now   (→Nur die 1. oder 2.Anweisung reicht nicht!)
Dadurch wird die UDF bei negativem 1.Argument volatil (sonst nicht!) und reagiert auf jedwede Daten­Änderung auf dem Blatt.
Luc :-?


  

Betrifft: Habe feststellen müssen, dass die UDF unter ... von: Luc:-?
Geschrieben am: 29.01.2016 18:37:03

…bestimmten Bedingungen nicht so fktt wie von mir erwartet, weshalb eine Initialisierung der Zähler sz und zz mit 1 erforderlich ist und das an beiden Stellen nach On Error Resume Next (die ersten beiden Auftritte dieser Anweisung). Da auch noch ein Hinweis im Vorspann dazukommt, hier nochmals der komplette PgmCode der Version 1.3, womit die Link-Kette von den 1.Versionen bis hierher abgeschlossen wäre:

Rem Aggregat f.1 Datenfeld aus Ausdruck (eXpression)
'   als Arg3 u.ggf eines als Arg4 f.Arg1>13; Umwandl
'   1es Bereichs in DatFeld; nur Input-FWert-Ignorg!
'   Arg1: <0 Evaluierg (nur 1× pro Formel möglich!),
'   0 nur Arg3-Wiedergabe, sonst wie b.Fkt AGGREGAT;
'   Arg2: 0/1/4/5 ohne Wirkg, 2/3/6/7 FWert-Auslassg
'   (--> 0|"") bzw FWt-Ersatz durch ""/1 b.Arg2=8/9;
'   Arg3: ZBer/Ausdr->DFeld; Arg4: ohne/Kl<-Arg1>13.
'   Achtung! Neg Arg1 vhindrt ext FmlText-Evaluierg!
'   Vs1.3 -LSr -cd:20160120 -1pub:20160120herber -lupd:20160129t
Function AggregateXk(ByVal Funktion As Integer, ByVal Optionen As Integer, _
                     ByVal DatenfeldAusdruck, Optional ByVal k)
    Const myName$ = "aggregatexk("
    Dim p As Long, sx As Long, sz As Long, zx As Long, zz As Long, _
        q(1) As Long, FTxt As String, erg, kw, xw, zwErg As Variant
    On Error GoTo fx
    If Funktion < 0 Then    'FmlTxtAuswert f.entsprd FmlTeil (nur 1×!)
        Application.Volatile: AggregateXk = Now
        FTxt = "(-" & Split(LCase(Application.ThisCell.Formula), myName)(1)
        For p = 1 To Len(FTxt)
            q(0) = q(0) - CInt(Mid(FTxt, p, 1) = "(")
            q(1) = q(1) - CInt(Mid(FTxt, p, 1) = ")")
            If q(1) = q(0) Then Exit For
        Next p
        FTxt = myName & Mid(FTxt, 2, p - 1 + CInt(p > Len(FTxt)))
        AggregateXk = ActiveSheet.Evaluate(FTxt): Exit Function
    End If
    With WorksheetFunction
        If TypeName(DatenfeldAusdruck) = "Range" Then
            If DatenfeldAusdruck.Cells.Count > 1 Then
                DatenfeldAusdruck = .Transpose(.Transpose(DatenfeldAusdruck))
            Else: DatenfeldAusdruck = Array(DatenfeldAusdruck)
            End If
        End If
        If Not IsMissing(k) Then
            If TypeName(k) = "Range" Then
                If k.Cells.Count > 1 Then k = .Transpose(.Transpose(k))
            End If
        End If
    End With
    On Error Resume Next: sz = 1: zz = 1
    sz = UBound(DatenfeldAusdruck, 2) + 1 - LBound(DatenfeldAusdruck, 2)
    zz = UBound(DatenfeldAusdruck, 1) + 1 - LBound(DatenfeldAusdruck, 1)
    On Error GoTo fx: ReDim zwErg(zz - 1, sz - 1)
    Select Case Optionen
        Case 0, 1, 4, 5                     'entfällt hier f.DFeld!
            zwErg = DatenfeldAusdruck
        Case 2, 3, 6, 7, 8, 9               'dito, nur FWertErsatz!
            For Each xw In DatenfeldAusdruck
                If Not IsError(xw) Then
                    zwErg(zx, sx) = xw
                ElseIf Optionen > 7 Then
                    zwErg(zx, sx) = Array("", 1)(Optionen - 8)
                End If
                zx = (zx + 1) Mod zz: sx = sx - CInt(zx = 0)
            Next xw
        Case Else: Err.Raise xlErrNA
    End Select
    With Application
        If Funktion > 13 And IsArray(k) Then
            On Error Resume Next: sz = 1: zz = 1
            sz = UBound(k, 2) + 1 - LBound(k, 2)
            zz = UBound(k, 1) + 1 - LBound(k, 1)
            On Error GoTo fx
            kw = k: zx = 0: sx = 0: ReDim erg(zz - 1, sz - 1)
            For Each k In kw
                On Error Resume Next: GoSub kf: On Error GoTo fx
                erg(zx, sx) = AggregateXk: AggregateXk = Empty
                zx = (zx + 1) Mod zz: sx = sx - CInt(zx = 0)
            Next k
            AggregateXk = erg: Exit Function
        End If
kf:     Select Case Funktion
            Case 0:  AggregateXk = zwErg
            Case 1:  AggregateXk = .Average(zwErg)
            Case 2:  AggregateXk = .Count(zwErg)
            Case 3:  AggregateXk = .CountA(zwErg)
            Case 4:  AggregateXk = .Max(zwErg)
            Case 5:  AggregateXk = .Min(zwErg)
            Case 6:  AggregateXk = .Product(zwErg)
            Case 7:  AggregateXk = .StDev_S(zwErg)
            Case 8:  AggregateXk = .StDev_P(zwErg)
            Case 9:  AggregateXk = .Sum(zwErg)
            Case 10: AggregateXk = .Var_S(zwErg)
            Case 11: AggregateXk = .Var_P(zwErg)
            Case 12: AggregateXk = .Median(zwErg)
            Case 13: AggregateXk = .Mode_Sngl(zwErg)
            Case 14: AggregateXk = .Large(zwErg, k)
            Case 15: AggregateXk = .Small(zwErg, k)
            Case 16: AggregateXk = .Percentile_Inc(zwErg, k)
            Case 17: AggregateXk = .Quartile_Inc(zwErg, k)
            Case 18: AggregateXk = .Percentile_Exc(zwErg, k)
            Case 19: AggregateXk = .Quartile_Exc(zwErg, k)
            Case Else: Err.Raise xlErrNA
        End Select
        If Not IsEmpty(kw) Then Return
    End With
fx: If CBool(Err.Number) Then AggregateXk = CVErr(Err.Number)
End Function
Luc :-?


 

Beiträge aus den Excel-Beispielen zum Thema "Mittelwert - mehrere Kriterien in einer Spalte"