Herbers Excel-Forum - das Archiv

Mittelwert - mehrere Kriterien in einer Spalte

Bild

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

Bild

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

Bild

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

Bild

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
.. , - ...

Bild

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 …

Bild

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
.. , - ...

Bild

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 …

Bild

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
.. , - ...

Bild

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

Bild

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 :-?

Bild

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.

Bild

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? ;-))

Bild

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 :-?

Bild

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
.. , - ...

Bild

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

Bild

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?

Bild

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
.. , - ...

Bild

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?

Bild

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 …

Bild

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 :-?

Bild

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 :-?

Bild

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 :-?

 Bild
Excel-Beispiele zum Thema "Mittelwert - mehrere Kriterien in einer Spalte"
Mittelwert ohne Nullwerte Bedingter Mittelwert
Aus einer Reihe von 10-minütigen Messungen h-Mittelwerte auslesen Mittelwert einer Zahlenreihe bis zur Zeile mit der Formel
Berechnung von Zeitspanne und Mittelwert Mittelwert aus den 6 höchsten Werten in Spalte A ermitteln
Suche über mehrere Tabellen Mehrere Schaltflächen für ein Makro
Suchbegriff über mehrere Tabellenblätter suchen. Benutzerdefinierte SVERWEIS-Funktion über mehrere Bereiche