[Excel] Min,Max,Mittelwert in Abhängigkeit zur Kategorie

A

amn.ssy

Erfahrenes Mitglied
Hallo,
ich hoffe, daß sich auch über die Osterfeiertage jemand in dieses Forum schaut und vieleicht eine Idee hat wie ich folgendens Problem lösen kann:

Auf Tabellenblatt1 gibt es in D2:E2, F2:G2, usw. (D2:E2 wg. Span) bis AP2:AQ2 Überschriften wie Global to Global, Global to Local, Local to Local.
In E5:E15, G5:G15, I5:I15, usw. habe ich Daten die zu einer der o.g Kategorie gehören.
Nun benötige ich auf Tabellenblatt2, in Abhängikeit zur Kategorie für jede Zeile Min, Max und Mittelwert. N Value als letzte Berechnung gibt die Anzahl der Werte an die größer 0 sind (auch hier bezogen auf Kategorie und Zeile).
Hab die Datei mal angehängt und in der Rabelle 2 mit Kommentaren versehen, die das beschriebene Problem nochmal verdeutlichen sollen.

Anhang anzeigen 63136

Vielen Dank und frohe Ostern

LG
opiwahn
 
Hallo opiWahn,

was ist denn genau deine Frage bzw. dein Problem?

Für Min gibt es die =MIN Funktion.
Für Max gibt es die =MAX Funktion.
Für den Mittelwert gibt es die =MITTELWERT Funktion.

Dein "N Value" hat mich persönlich sehr interessiert!
Beim Suchen im Internet bin ich auf folgende Seiten gestoßen:

  1. http://spreadsheets.about.com/od/excelformulas/tp/Excel-Array-Formula-Tutorials.htm
  2. http://spreadsheets.about.com/od/2010statisticalfunctions/qt/2010-04-26-excel-2010-min-function.htm
  3. http://spreadsheets.about.com/od/ex...010-multi-cell-array-formula-sbs-tutorial.htm

Hinter dem zweiten Link versteckt sich genau eine Anleitung, wie man solche Array-Formeln in Exceln umsetzt.

Ich habe es auch mal mit deinem Anwendungsfall ausprobiert und es klappt wunderbar.
Um das allgemeine Prinzip einfacher zu beschreiben, habe ich eine neue Excelmappe angelegt.

Als Erstes fügt man nun die Daten ein:
Schritt1.PNG

Nun wollen wir in Spalte D die entsprechenden Formeln einfügen, so wie sie in Spalte C beschrieben sind.
Die Formeln für D1 bis D3 sind ziemlich einfach:


Hier die "rohe" Formel, um die Anzahl der Werte > 0 zu berechnen. Wichtig: "Roh" daher, weil man nach der Eingabe der Formel innerhalb der Zelle (bevor man wegklickt!) Folgendes drücken muss: [Strg]+[Umschalt]+[Enter].

Code: 
=SUMME( WENN(A1:A6>0; 1; 0) )
Wie funktioniert das nun?

  • Mit der speziellen Tastenkombination am Ende erreichen wir, dass die Range-Angabe A1:A6 wirklich durchlaufen wird.
  • WENN(A1:A6>0; 1; 0): Wir prüfen jeden Zellwert, ob er > 0 ist. Ist er dies, geben wir 1 zurück, ansonsten 0.
  • =SUMME( WENN(A1:A6>0; 1; 0) ): Wenn wir nun die zurückgegebenen Einsen und Nullen addieren, erhalten wir in der Tat die Anzahl der Zellen, dessen Zellwerte > 0 sind.

Dies schaut nun so aus:
Schritt2.PNG

Frohe Ostern!
 
Hallo ComFreak,

ich hasse zusammengesetze Funktionen ... aber wenns funktioniert ;-)

Visual Basic: 
'*** Min (MAX und AVG das gleiche)
{=MIN(WENN('Data Base Historical-  EPO'!$D$2:$AR$2=$A$1;'Data Base Historical-  EPO'!$E5:$AS5))}
'*** N Value
{=SUMME(WENN(('Data Base Historical-  EPO'!$D$2:$AR$2=$A$1)*('Data Base Historical-  EPO'!$E5:$AS5>=0,1); 1))}

Soviel zu meiner Lösung.

Trotzdem Danke für deine Mühe und Gruß
opiwahn
 
Mahlzeit,

nachdem ich letzte Woche schon dachte die Sache wäre vom Tisch, kam Cheffe an: Ähm - ich hätte noch gerne ...
Letztlich ging es darum neue Abschnitte einfügen zu können und sich die Formeln anpassen.
Gelöst habe ich das nun selbständig :) mit eine Combobox, die gefüllt wird in dem eine Funktion über die Zeile (2) rauscht und sich alle "Überschriften" wie Global to Global etc. einsammelt (ohne doppelte).
Bei Auswählen eines Items wird dann hinter dem Bereich ein Neuer angelegt, der genauso heißt wie der Vorgänger.
Damit sich die Formeln auch ja weiter aktualisieren hab ich als allerletzten Bereich ein Template angelegt.
Bei Auswahl von Template wird dann der neue Bereich unmittelbar davor angelegt.
Grundsätzlich funktioniert das alles, jedoch mit 2 "kleinen" Einschränkungen:
Zum einen kann ich das 1. angezeigte Item (hier Global to Global) nicht auswählen bzw. es reagiert nicht.
Wähle ich das 2. oder eins der folgenden funktioniert das ganze bzw. danach funktioniert auch das 1.
Ein unmittelbares hintereinander Auswählen des gleichen Item funktioniert auch nicht.
Warum?
Click- und Change Ereignisse habe ich beide wechselseitig ausprobiert, leider ohne Erfolg.
Beide zusammen führen interessanterweise zu einem doppelten Einfügen.

Das Zweite wäre das Aktualisieren der Combobox, das immer dann stattfinden solte, wenn ein Bereich hinzugefügt wurde.
Wie und Wohin?
Hier mal mein bisheriges Werk:

(Arbeitsmappe)
Visual Basic: 
Private Sub Workbook_Open()
    FillCombo
End Sub

(Tabelle2)
Visual Basic: 
'Private Sub AddNewColumn_Click()
'    sItem = ActiveSheet.AddNewColumn.Value
'    InsertCol (sItem)
'End Sub

Private Sub AddNewColumn_Change()
    sItem = ActiveSheet.AddNewColumn.Value
    InsertCol (sItem)
End Sub

(Modul1)
Visual Basic: 
Function FillCombo()
    Dim rItems As Object
    LCol = gLCol
    area = Range("D2:" & LCol & "2").Value
    Set rItems = CreateObject("scripting.dictionary")
    For i = 1 To UBound(area, 2)
        On Error Resume Next
        If Len(area(1, i)) > 0 Then rItems.Add area(1, i), 0
    Next
    With ActiveSheet.AddNewColumn
        .List = rItems.keys
        .ListIndex = 0
    End With
    Set rItems = Nothing
End Function

Function InsertCol(sItem)
    LEntN = gLEnt(sItem)
    LEntA = gColA(LEntN)
    IEntN = LEntN + 2
    IEntA = gColA(IEntN)
    cCol1 = LEntA
    cCol2 = gColA(LEntN + 1)
    dCol1 = IEntA
    dCol2 = gColA(IEntN + 1)
    If sItem = "Template" Then
        IEntN = IEntN - 2
        IEntA = gColA(IEntN)
        cCol1 = gColA(IEntN + 2)
        cCol2 = gColA(IEntN + 3)
        dCol1 = LEntA
        dCol2 = gColA(LEntN + 1)
    End If
    Application.ScreenUpdating = False
    ActiveSheet.Cells(1, IEntN).Resize(1, 2).EntireColumn.Insert
    With ActiveSheet
        .Range(cCol1 & "1:" & cCol2 & "36").Copy
        .Range(dCol1 & "1").PasteSpecial Paste:=xlPasteFormats
        .Range(cCol2 & "5:" & cCol2 & "16").Copy
        .Range(dCol2 & "5").PasteSpecial Paste:=xlPasteFormulas
        .Range(cCol1 & "3:" & cCol2 & "3").Copy
        .Range(dCol1 & "3").PasteSpecial Paste:=xlValues
        .Range(cCol1 & "17").Copy
        .Range(dCol1 & "17").PasteSpecial Paste:=xlValues
        .Range(dCol1 & "2").Value = sItem
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
        .Range(dCol1 & "1").Select
    End With
End Function

Function gLEnt(sItem)
  For i = Cells(2, Columns.Count).End(xlToLeft).Column To 1 Step -1
    If Cells(2, i) = sItem Then
      CN = i
      Exit For
    End If
  Next
  gLEnt = CN
End Function

Function gLCol()
    CN = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column
    gLCol = gColA(CN)
End Function

Function gColA(CN)
    gColA = Left(WorksheetFunction.Substitute(Cells(1, CN).Address, "$", ""), _
            Len(WorksheetFunction.Substitute(Cells(1, CN).Address, "$", "")) - 1)
End Function

Grüße
opiwahn
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück