Summe ohne Graue Zellen

Bild

Betrifft: Summe ohne Graue Zellen
von: mykey
Geschrieben am: 29.03.2005 16:44:57
Hallo Forum,
kann man eine Zelle so formatieren, dass folgendes zutrifft:
=SUMME Wenn A1 grau ist zähle nicht mit; sonst mitzählen (standardschriftfarbe schwarz) + Wenn B1 grau ist zähle nicht mit; sonst mitzählen (standardfarbe schwarz) + Wenn C1 grau ist zähle nicht mit; sonst mitzählen (standardfarbe schwarz) + Wenn D1 grau ist zähle nicht mit; sonst mitzählen (standardfarbe schwarz)
dieses ergebniss soll wiederum durch die anzahl der gezählten 'schwarzen' schrift geteilt werden, um somit den durchschnitt zu erhalten. Dazu formatiere ich noch eine zusätzliche spalte.
Wäre nett wenn mir jemand hilft und mir auch erklärt was die Befehle, wenn jemand mir die schickt, im einzelnen bedeuten, damit ich das nachvollziehen kann.
:)
Ich bin immernoch auf der suche nach kostenlosen kompendien über excel ...
Hat da wer nützliche links ???
Grüsse,
Max

Bild

Betrifft: AW: Summe ohne Graue Zellen
von: mykey
Geschrieben am: 29.03.2005 16:49:20
ist eine bedingte formaierung eventuell hilfreich ??? oder notwendig ???
MfG
max
Bild

Betrifft: AW: Summe ohne Graue Zellen
von: Boris
Geschrieben am: 29.03.2005 16:50:31
Hi Max,
da du dich ja anscheinend noch im Anfangsstadium von Excel befindest, gebe ich dir den Tipp, dass du Berechnungen NIEMALS von Formatierungen abhängig machen solltest, da Excel dafür so gut wie keine Mittel bereithält.
Arbeite in solchen Fällen lieber mit Hilfsspalten, in denen du die fraglichen Werte mit "x" oder sonstwie markierst. Darauf kannst du dann wiederum mit den Standardfunktionen wie SUMMEWENN oder ZÄHLENWENN oder... zurückgreifen.
Und wenn du doch etwas zu deiner Formatanfrage wissen möchtest, gib mal bei Google z.B. Farbsumme site:herber.de ein.
Grüße Boris
Bild

Betrifft: AW: Summe ohne Graue Zellen
von: mykey
Geschrieben am: 30.03.2005 15:05:18
hast du eventuell Links die mir erklären wie ich mit den Berechnungen zu SUMMEWENN oder ZÄHLENWENN umgehen muss ???
das mit der Farbsumme ist recht hilfreich danke schön !!! :)
Bild

Betrifft: AW: Summe ohne Graue Zellen
von: Fritz
Geschrieben am: 29.03.2005 17:16:55
Hallo Max
Ich würde das mit VBA-Mitteln lösen.
z.B. so

Sub Rechnen()
Dim I&, LetzteZeile&, Spalte&, Summe As Double
Spalte = 5  'Spalte E
LetzteZeile = Cells.SpecialCells(xlCellTypeLastCell).Row
Summe = 0
'Cells(I, 1).Interior.ColorIndex = 7
For I = 2 To LetzteZeile
    If Cells(I, Spalte).Interior.ColorIndex <> 15 Then       '15=grau
        Summe = Summe + Cells(I, Spalte)
    End If
Next
Cells(LetzteZeile + 1, Spalte) = Summe  ' Summe in die letzte Zeile Spalte 5 eintragen.
End Sub

In diesem fall werden alle Werte addiert, die nicht in einer grauen Zelle stehen.
Gruß
Fritz
Bild

Betrifft: AW: Summe ohne Graue Zellen
von: mykey
Geschrieben am: 30.03.2005 14:59:58
Hiho,
das ist schon mal ein schöner Anfang !
Hab das mal in meine Beipiseldatei eingefügt ... aber er spuckt mir null aus ...
Kannst Du mir denn mal erklären, Zeile für Zeile was die einzelnen Befehle bewirken ???
Ich habe das ganze schon mal etwas kommentiert:

Sub Rechnen()  ::: hier beginnt das Makro
Dim I&, LetzteZeile&, Spalte&, Summe As Double ::: Welche Variablen werden hier angesprochen ?
Spalte = 5  'Spalte E  ::: Hier gebe ich die Zielspalte an ???
LetzteZeile = Cells.SpecialCells(xlCellTypeLastCell).Row    ::: ?was passiert denn hier
Summe = 0   ::: Wofür steht die 0 ???
'Cells(I, 1).Interior.ColorIndex = 7    ::: Was passiert hier ?
For I = 2 To LetzteZeile    ::: Das I steht für die Zeile 2 ok.
    If Cells(I, Spalte).Interior.ColorIndex <> 15 Then       '15=grau   :::
        Summe = Summe + Cells(I, Spalte)
    End If
Next
Cells(LetzteZeile + 1, Spalte) = Summe  ' Summe in die letzte Zeile Spalte 5 eintragen.
End Sub


Danke Dir für Deine Hilfe !
=)
Bild

Betrifft: AW: Summe ohne Graue Zellen
von: Fritz Hellbach
Geschrieben am: 30.03.2005 16:34:54
Dim I&, LetzteZeile&, Spalte&, Summe As Double 'Variable Festlegen
Spalte = 5 'Spalte E
LetzteZeile = Cells.SpecialCells(xlCellTypeLastCell).Row 'hier wird die letzte Zeile ermittelt
Summe = 0 ' die Summe mit "=" initialisieren (muss nicht unbedingt sein)
'Cells(I, 1).Interior.ColorIndex = 7 ' Kommentarzeile, hab ich vergessen zu löschen,sorry
For I = 2 To LetzteZeile
If Cells(I, Spalte).Interior.ColorIndex <> 15 Then '15=grau
Summe = Summe + Cells(I, Spalte)
End If
Next
Cells(LetzteZeile + 1, Spalte) = Summe ' Summe in die letzte Zeile Spalte 5 eintragen.
End Sub

In der For-Schleife wird ab Zeile 2 bis zur letzten Zeile alle zellen auf ihren Farbindex gecheckt. Sollte er nicht 15 sein, dann wird der Inhalt zur Summe addiert.
Bei mir klappt das. Vielleicht lädst du mal eine Beispieldatei hoch
Gruß
Fritz
Bild

Betrifft: AW: Summe ohne Graue Zellen
von: Fritz Hellbach
Geschrieben am: 30.03.2005 16:55:01
Habe gedacht, die Farbe der Zelle sei gemeint.
Jetzt hier die Schriftfarbe.
In Deinem Beispiel funktionierts.
https://www.herber.de/bbs/user/20392.xls


Sub Rechnen()
Dim I&, LetzteZeile&, Spalte&, Summe As Double
Spalte = 2  'Spalte L
LetzteZeile = Cells.SpecialCells(xlCellTypeLastCell).Row
Summe = 0
'Font.ColorIndex = 15
For I = 2 To LetzteZeile
    If IsNumeric(Cells(I, Spalte)) Then
        If Cells(I, Spalte).Font.ColorIndex <> 15 Then       '15=grau
            Summe = Summe + Cells(I, Spalte)
        End If
    End If
Next
Cells(LetzteZeile + 1, Spalte) = Summe  ' Summe in die letzte Zeile Spalte 5 eintragen.
End Sub

Bild

Betrifft: AW: Summe ohne Graue Zellen
von: mykey
Geschrieben am: 31.03.2005 13:31:22
Jupp...
={)
Das funktioniert ! Danke schön !!!
Hab jetzt mal die andere Version benutzt und fand die irgendwie komfortabler.

MfG
max
Bild

Betrifft: AW: Summe ohne Graue Zellen
von: mykey
Geschrieben am: 30.03.2005 15:06:30
aYo 0o
hier die Datei
https://www.herber.de/bbs/user/20375.xls
Bild

Betrifft: AW: Summe ohne Graue Zellen
von: Martin Beck
Geschrieben am: 30.03.2005 16:50:18
Hallo,
hier mal ein paar benutzerdefinierte Funktionen:

Function Summewenn_Farbe(DataRange, ColorIndex)
Application.Volatile
Summe = 0
For Each cell In DataRange
    If cell.Font.ColorIndex = ColorIndex And IsNumeric(cell.Value) Then
        Summe = Summe + cell.Value
    End If
Next
Summewenn_Farbe = Summe
End Function


Function Summewenn_nicht_Farbe(DataRange, ColorIndex)
Application.Volatile
Summe = 0
For Each cell In DataRange
    If cell.Font.ColorIndex <> ColorIndex And IsNumeric(cell.Value) Then
        Summe = Summe + cell.Value
    End If
Next
Summewenn_nicht_Farbe = Summe
End Function


Function Mittelwertwenn_Farbe(DataRange, ColorIndex)
Application.Volatile
Summe = 0
Anzahl = 0
For Each cell In DataRange
    If cell.Font.ColorIndex = ColorIndex And IsNumeric(cell.Value) Then
        Summe = Summe + cell.Value
        Anzahl = Anzahl + 1
    End If
Next
Mittelwertwenn_Farbe = Summe / Anzahl
End Function


Function Mittelwertwenn_nicht_Farbe(DataRange, ColorIndex)
Application.Volatile
Summe = 0
Anzahl = 0
For Each cell In DataRange
    If cell.Font.ColorIndex <> ColorIndex And IsNumeric(cell.Value) Then
        Summe = Summe + cell.Value
        Anzahl = Anzahl + 1
    End If
Next
Mittelwertwenn_nicht_Farbe = Summe / Anzahl
End Function

Letztere liefert das gewünschte Ergebnis. Formeleingabe wie folgt:
=Mittelwertwenn_nicht_Farbe(B5:E5;15)

Den Code mußt Du in ein normales Modul kopieren.
Gruß
Martin Beck
Bild

Betrifft: AW: Summe ohne Graue Zellen
von: mykey
Geschrieben am: 31.03.2005 13:26:53
YESSA !!! Vielen Dank es klappt.
=]

Allerdings zählt er die Null als Wert ... was er nicht machen soll.
Er soll die Null auch als 'grauen Wert' erkennen bzw. den Mittelwert ohne Einbezug der "0" berechnen.
MfG
max
Bild

Betrifft: AW: Summe ohne Graue Zellen
von: Martin Beck
Geschrieben am: 31.03.2005 14:32:48
Hallo Max,
ungetestet:

Function Mittelwertwenn_nicht_Farbe(DataRange, ColorIndex)
Application.Volatile
Summe = 0
Anzahl = 0
For Each cell In DataRange
    If cell.Font.ColorIndex <> ColorIndex And IsNumeric(cell.Value) And cell.Value > 0 Then
        Summe = Summe + cell.Value
        Anzahl = Anzahl + 1
    End If
Next
Mittelwertwenn_nicht_Farbe = Summe / Anzahl
End Function


Bei den anderen Funktionen entsprechend.
Gruß
Martin Beck
Bild

Betrifft: AW: Summe ohne Graue Zellen
von: Andreas Ulrich
Geschrieben am: 31.03.2005 14:42:50
Huhu,
schönen Dank Martin!
die Mittelwertbildung bei bestimmter Farbe suchte ich :)
Leider funktioniert das ganze bei mir nicht:

Bei Aufruf der untenstehenden Funktion kommt aber nach "Application.Volatile" :
"Variable oder Prozedur anstelle eines Modules erwartet"
müssen Summe und Anzahl noch definiert werden?


Function Mittelwertwenn_Farbe(DataRange, ColorIndex)
Application.Volatile
Summe = 0
Anzahl = 0
For Each cell In DataRange
    If cell.Font.ColorIndex = ColorIndex And IsNumeric(cell.Value) Then
        Summe = Summe + cell.Value
        Anzahl = Anzahl + 1
    End If
Next
Mittelwertwenn_Farbe = Summe / Anzahl
End Function


End Function

gruss,
andreas
 Bild

Beiträge aus den Excel-Beispielen zum Thema "Summe ohne Graue Zellen"