[2010] Summe nach Farbe

[Lisa Wilke-Thissen]

Hallo zusammen,

es geht um eine Kalkulationstabelle mit 365 Spalten (eine Spalte je
Kalendertag) und mehr als 500 Zeilen für verschiedene Produkte. Der Bereich
wird gelegentlich um weitere Produkte vergrößert.
Je Spalte bzw. Tag wird eine Summe der eingetragenen Werte gebildet. Bis
hierher kein Problem ;-).

Manuell (nicht per bedingter Formatierung) wird einigen Zellen rote oder
gelbe Schriftfarbe zugewiesen. Sowohl für die rot als auch für die gelb
gefärbten Werte sollen je Spalte separate Summen gebildet und an anderer
Stelle weiterverwendet werden.
Diese Summenbildung ist offensichtlich nur per VBA möglich. Dazu finden sich
im WWW durchaus Lösungsansätze, z. B.
http://www.excel-live.de/2010/09/zellen-mit-gleicher-schriftfarbe-addieren/

Das Problem: Makros sind in der Arbeitsmappe nicht zugelassen. Gibt es
eventuell doch eine Möglichkeit ohne VBA?
Oder fällt euch - statt der farblichen Kennzeichnung - eine Alternative ein,
die das Addieren bestimmter Zellinhalte zulässt? 365 Hilfsspalten wären
allerdings zu viel des Guten...

--
Viele Grüße
Lisa

[Claus Busch]

Hallo Lisa,

Post by Lisa Wilke-Thissen
Das Problem: Makros sind in der Arbeitsmappe nicht zugelassen. Gibt es
eventuell doch eine Möglichkeit ohne VBA?

entweder Einfärben mit bedingter Formatierung und dann diese Bedingung
in der Summewenn-Formel verwenden.
Oder aber nach Farbe filtern und mit Teilergebnis arbeiten. Das
Teilergebnis müsste aber ermittelt und direkt wieder als Wert eingefügt
werden.
Mit Excel4-Makro müsstest du die ganze Tabelle nachbilden.


Mit freundlichen Grüßen
Claus

[Lisa Wilke-Thissen]

Guten Morgen Claus,

"Claus Busch" schrieb

Post by Claus Busch

Post by Lisa Wilke-Thissen
Das Problem: Makros sind in der Arbeitsmappe nicht zugelassen.
Gibt es eventuell doch eine Möglichkeit ohne VBA?

entweder Einfärben mit bedingter Formatierung und dann
diese Bedingung in der Summewenn-Formel verwenden.

kommt leider nicht in Frage, da es keine Bedingung gibt, nach der eingefärbt
werden könnte :-(.

Post by Claus Busch
Oder aber nach Farbe filtern und mit Teilergebnis arbeiten.
Das Teilergebnis müsste aber ermittelt und direkt wieder als
Wert eingefügt werden.

Auch das bietet sich leider nicht an. Denn sobald jemand irgendwann
nachträglich einen Eintrag einfärbt oder ggf. die Formatierung wieder
entfernt, müsste das Teilergebnis erneut ermittelt und als Wert übertragen
werden...
Letztlich bestätigst Du, was ich bereits befürchtet hatte. Aber so habe ich
mehr Gewissheit und danke Dir für die Antwort :-).

[Claus Busch]

Hallo Lisa,

Post by Lisa Wilke-Thissen
kommt leider nicht in Frage, da es keine Bedingung gibt, nach der eingefärbt
werden könnte :-(.

man färbt doch Zellen nicht ein, weil man meint, dass dort dem Auge ein
Farbtupfer gut tun könnte. Es gibt irgendeinen Grund dafür. Und wenn man
diesen Grund sprachlich exakt beschreiben kann, dann sollte man ihn auch
in eine Formel packen können.


Mit freundlichen Grüßen
Claus

[Lisa Wilke-Thissen]

Hallo Claus,

"Claus Busch" schrieb

Post by Claus Busch
man färbt doch Zellen nicht ein, weil man meint, dass dort dem
Auge ein Farbtupfer gut tun könnte.

grundsätzlich stimmt das natürlich - aber keine Regel ohne Ausnahme ;-).
Beispielsweise unrealistisch erscheinender Wert, muss nachkontrolliert
werden; niedrige Produktionszahlen durch Streik; niedrige Besucherzahlen
aufgrund schlechten Wetters, ...
Ergänzend zur farblichen Kennzeichnung fügt man sinnvollerweise meist
Kommentare hinzu.

Eigentlich habe ich noch eine Frage zu der VBA-Lösung, die stelle ich
später. Muss jetzt weg.

[Lisa Wilke-Thissen]

Hallo Claus,

Eigentlich habe ich noch eine Frage zu der VBA-Lösung, ...

meine Frage hat sich erledigt.
Das Ergebnis der benutzerdefinierten Funktion muss wohl manuell (F9) oder
alternativ nach bestimmten Zeitintervallen per VBA aktualisiert werden.
Es gibt offenbar keine Lösung, das Ergebnis direkt nach Zuweisen einer Farbe
aktualisieren zu lassen.

[Claus Busch]

Hallo Lisa,

Post by Lisa Wilke-Thissen
Das Ergebnis der benutzerdefinierten Funktion muss wohl manuell (F9) oder
alternativ nach bestimmten Zeitintervallen per VBA aktualisiert werden.
Es gibt offenbar keine Lösung, das Ergebnis direkt nach Zuweisen einer Farbe
aktualisieren zu lassen.

das Ändern oder Löschen eines Formats löst leider keine Berechnung aus.
Wenn aber in dieser Spalte Zellen nur ausgewählt werden, um eine Zelle
einzufärben oder die Farbe zu löschen oder um einen Kommentar zu
schreiben oder zu löschen, könntest du die Berechnung anstossen mit:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Mit freundlichen Grüßen
Claus

[Claus Busch]

Hallo nochmal,

Post by Claus Busch
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

sorry, das war falsch gedacht. Es müsste ja berechnet werden, wenn die
Zelle verlassen wird und nicht beim Anwählen.


Mit freundlichen Grüßen
Claus

[Ulrich Möller]

Post by Claus Busch
Hallo nochmal,

Post by Claus Busch
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

sorry, das war falsch gedacht. Es müsste ja berechnet werden, wenn die
Zelle verlassen wird und nicht beim Anwählen.
Mit freundlichen Grüßen
Claus

Dieses könnte man mit einer FormatCondition und einem Ausdruck
simulieren. Als Ausdruck verwendet man einfach eine VBA Funktion, die
auf einen Farbwert prüft.

Ulrich

[Lisa Wilke-Thissen]

Hallo Ulrich,

"Ulrich Möller" schrieb

[Neuberechnung nach Änderung der Schriftfarbe]

Dieses könnte man mit einer FormatCondition und einem Ausdruck simulieren.
Als Ausdruck verwendet man einfach
eine VBA Funktion, die auf einen Farbwert prüft.

ich bin kein VBA-Freak, schon gar nicht in Sachen Excel.
Wäre die von dir angedachte Lösung weniger rechenintensiv als ein
zeitgesteuertes Aktualisieren z. B. alle 59 Sekunden?

[Claus Busch]

Hallo Lisa,

Post by Lisa Wilke-Thissen

Dieses könnte man mit einer FormatCondition und einem Ausdruck simulieren.
Als Ausdruck verwendet man einfach
eine VBA Funktion, die auf einen Farbwert prüft.

ich bin kein VBA-Freak, schon gar nicht in Sachen Excel.
Wäre die von dir angedachte Lösung weniger rechenintensiv als ein
zeitgesteuertes Aktualisieren z. B. alle 59 Sekunden?

diese Funktion müsste doch auch neu berechnet werden. Und die
Farbänderung stößt auch keine bedingte Formatierung an.
Kann der Kunde nicht mit Rechtsklick oder Doppelklick eine Neuberechnung
anstossen bei Bedarf?


Mit freundlichen Grüßen
Claus

[Lisa Wilke-Thissen]

Hallo Claus,

Post by Claus Busch
Kann der Kunde nicht mit Rechtsklick oder Doppelklick
eine Neuberechnung anstossen bei Bedarf?

theoretisch denkbar. In der Praxis sieht es aber so aus, dass Anwender
leider vergessen, F9 zu drücken oder die Neuberechnung anderweitig
anzustoßen.
Und dann stimmen die Ergebnisse halt nicht mehr.
Beim Testen habe ich festgestellt, dass selbst in einer relativ einfach
gestrickten Musterdatei mit weniger als 100 Zeilen und 100 Spalten die
Aktualisierung durchaus Ressourcen verbraucht.
Das ist nicht dramatisch. Wenn es einfacher gegangen wäre, hätte ich mich
allerdings für die einfachere Variante entschieden ;-).

[Claus Busch]

Hallo Lisa,

Post by Lisa Wilke-Thissen
theoretisch denkbar. In der Praxis sieht es aber so aus, dass Anwender
leider vergessen, F9 zu drücken oder die Neuberechnung anderweitig
anzustoßen.

F9 berechnet auch die ganze Mappe. Du könntest dich beim Berechnen doch
auf deinen Bereich mit den beiden Summen beschränken, z.B.:
ActiveSheet.Range("A501:NA502").Calculate
und eventuell die Berechnung auf Manuell stellen.


Mit freundlichen Grüßen
Claus

[Lisa Wilke-Thissen]

Hallo Claus,

Post by Claus Busch
F9 berechnet auch die ganze Mappe. Du könntest dich
beim Berechnen doch auf deinen Bereich mit den beiden
ActiveSheet.Range("A501:NA502").Calculate
und eventuell die Berechnung auf Manuell stellen.

jetzt, wo du's sagst ;-).
Danke für den Hinweis, werde ich mal testen.

[Claus Busch]

Hallo Lisa,

Post by Lisa Wilke-Thissen
Danke für den Hinweis, werde ich mal testen.

ich weiß nicht viel über diese Tabelle. Werden in diesem Blatt viele
Zellen berechnet? Wo werden die Zellen eingefärbt bzw. die Farbe
gelöscht? In den letzten 8 Tagen?
Dann könntest du die Berechnung auf "Automatisch" belassen und dann mit
dem Worksheet_SelectionChange- Ereignis die Zelle XFD1 ändern, wenn in
den letzten 8 Tagen eine Zelle angewählt wird. In XFD2 schreibst du dann
= XFD1*Jetzt(). Damit wird dann immer eine Berechnung ausgelöst und wenn
du in der Funktion für die Farbsumme Application.Volatile stehen hast,
wird diese Funktion mit berechnet.

Hast du viele berechnete Zellen in dem Blatt, wäre es sinnvoller die
Berechnung auf "Manuell" zu stellen. Auch dann sollte
Application.Volatile in die Funktion. Und über das
Worksheet_SelectionChange-Ereignis für die letzen 8 Tage könntest du
eine Prozedur "Berechnen" aufrufen, die die Formelzellen der Funktion
für die letzten 8 Tage berechnet.

Ins Codemodul des Blattes:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range(Cells(1, Date - DateSerial(Year(Date), 1, 0)
- 8), _
Cells(500, Date - DateSerial(Year(Date), 1, 0)))) Is Nothing Then
Exit Sub

Range("XFD1") = IIf(Range("XFD1") = 0, 1, 0)
Call Berechnen
End Sub

Ich bin von einem Bereich Zeile 1 bis 500 ausgegangen und 365 Spalten
für die Tage.

In ein Standardmodul:

Sub Berechnen()
ActiveSheet.Range(Cells(501, Date - DateSerial(Year(Date), 1, 0) - 8), _
Cells(502, Date - DateSerial(Year(Date), 1, 0))).Calculate
End Sub

Hier bin ich davon ausgegangen, dass die Formel in den Zeilen 501 und
502 steht für gelb und rot.

Ich bin auch davon ausgegangen, dass die Zeilen 501 und 502 gelb bzw,
rot eingefärbt sind. Dann kann man die Summe leicht errechnen mit:

Function FarbSum(Bereich As Range) As Double
Dim rngC As Range
Dim Farbe As Double

Application.Volatile
Farbe = Application.Caller.Interior.Color
For Each rngC In Bereich
If rngC.Interior.Color = Farbe Then
FarbSum = FarbSum + rngC
End If
Next

End Function

Die Funktion holt sich die Hintergrundfarbe von der aufrufenden Zelle.



Mit freundlichen Grüßen
Claus

[Lisa Wilke-Thissen]

Hallo Claus,

"Claus Busch" schrieb

Werden in diesem Blatt viele Zellen berechnet?

jede Spalte (min. 365) enthält Formeln für die tägliche Auswertung
(Ist-Zustand/Prognose).

Wo werden die Zellen eingefärbt bzw. die Farbe
gelöscht? In den letzten 8 Tagen?

Ein Zeitraum von 8 bis 14 Tagen dürfte passen.

Dann könntest du die Berechnung auf "Automatisch" belassen
und dann mit dem Worksheet_SelectionChange- Ereignis die
Zelle XFD1 ändern, wenn in den letzten 8 Tagen eine Zelle
angewählt wird. In XFD2 schreibst du dann = XFD1*Jetzt().
Damit wird dann immer eine Berechnung ausgelöst und wenn
du in der Funktion für die Farbsumme Application.Volatile
stehen hast, wird diese Funktion mit berechnet.

Aktuell verwende ich:

Function Farbsumme(Bereich As Range, Farbe As Integer) As Double
Dim Zelle As Range
Application.Volatile
For Each Zelle In Bereich
If Zelle.Font.ColorIndex = Farbe Then
Farbsumme = Farbsumme + Zelle
End If
Next
End Function

Und über das Worksheet_SelectionChange-Ereignis
für die letzen 8 Tage könntest du eine Prozedur
"Berechnen" aufrufen, die die Formelzellen der Funktion
für die letzten 8 Tage berechnet.

[Code]

Hier bin ich davon ausgegangen, dass die Formel in
den Zeilen 501 und 502 steht für gelb und rot.

Die Formeln befinden sich wegen des besseren Überblicks in den ersten
Zeilen. Aber den Code dahingehend zu ändern, ist glücklicherweise nicht gar
so schwer ;-).

Ich bin auch davon ausgegangen, dass die Zeilen
501 und 502 gelb bzw, rot eingefärbt sind. Dann kann

[Code]

Die Funktion holt sich die Hintergrundfarbe von der aufrufenden Zelle.

Auch nicht schlecht, kann ich ja anpassen.

Nochmals vielen Dank für deine Lösungen. Es ist immer erstaunlich, wie viele
Wege zum Ergebnis führen können bzw. wie viele Anregungen man durch solche
Diskussionen erhält.

[Claus Busch]

Hallo Lisa,

Post by Lisa Wilke-Thissen
Nochmals vielen Dank für deine Lösungen. Es ist immer erstaunlich, wie viele
Wege zum Ergebnis führen können bzw. wie viele Anregungen man durch solche
Diskussionen erhält.

du köntnest auch die Berechnung auf "Manuell" stellen und dann mit dem
Worksheet_SelectionChange-Ereignis die Formeln eintragen lassen. Das
würde auch eine Berechnung auslösen. Man nüsste nur sicher stellen, dass
nach dem Einfärben die Zelle mit Enter , also nach unten verlassen wird.
In den Zellen davor könnte man die Formeln zu Werten ändern, dann wäre
der Ressourcenverbrauch auch nicht so hoch bei der Neuberechnung. Und
wenn dann doch wieder in der Spalte gearbeitet würde, würden ja wieder
die Formeln eingetragen.
Eine eventuelle Neuberechnugn des Blattes wegen anderer Formel könntest
du dann mit einem Timer auslösen. Der müsste dann eventuell nicht ganz
so kurz geschaltet sein.

Eine weitere Möglichkeit wäre in dem oberen bestimmt ja fixierten
Bereich ein Button zur Neuberechnung einzufügen, der mit der
ScrollColumn wandert und so immer im Blick ist. Die Berechnung könnte
dann ganz abgeschaltet und bei Bedarf mit dem Button ausgelöst werden.


Mit freundlichen Grüßen
Claus

[Ulrich Möller]

Post by Claus Busch
Hallo Lisa,

Post by Lisa Wilke-Thissen

Dieses könnte man mit einer FormatCondition und einem Ausdruck simulieren.
Als Ausdruck verwendet man einfach
eine VBA Funktion, die auf einen Farbwert prüft.

ich bin kein VBA-Freak, schon gar nicht in Sachen Excel.
Wäre die von dir angedachte Lösung weniger rechenintensiv als ein
zeitgesteuertes Aktualisieren z. B. alle 59 Sekunden?

diese Funktion müsste doch auch neu berechnet werden. Und die
Farbänderung stößt auch keine bedingte Formatierung an.
Kann der Kunde nicht mit Rechtsklick oder Doppelklick eine Neuberechnung
anstossen bei Bedarf?
Mit freundlichen Grüßen
Claus

Mit der bedingten Formatierung kann man sehr wohl eine Farbänderung
detektieren, zumindest in Excel 2010. Um Ressourcen zu schonen, könnte
man die zu summierenden Zellenadressen zwischenspeichern und dann bei
Änderungen asynchron über einen Timer mit kurzer Zeiteinstellung eine
Funktion aufrufen, die diesen Zwischenspeicher benutzt um dann die Summe
neu zu berechnen. So müssen nicht alle Zellen durchlaufen werden.
Wichtig hierbei ist aber , daß die angegebene Funktion von der bedingten
Formatierung sehr häufig angestoßen wird. Man muß hier eventuell
Mehrfachaufrufe unterbinden, um sauber z.B. eine Farbänderung zu
detektieren. Sollte aber machbar sein.

Ulrich

[Claus Busch]

Hallo Ulrich,

Post by Ulrich Möller
Mit der bedingten Formatierung kann man sehr wohl eine Farbänderung
detektieren, zumindest in Excel 2010.

die Tabelle ist ein Arbeitsbericht. Darim werden einzelne Zellen von
Hand eingefärbt, damit dort noch einmal die Daten geprüft werden. Da ist
keine bedingte Formatierung und ich kann mit händisch gefärbten Zellen
auch keine bedingte Formatierung oder Berechnung auslösen.


Mit freundlichen Grüßen
Claus

[Ulrich Möller]

Post by Lisa Wilke-Thissen
Hallo Ulrich,

Post by Ulrich Möller
Mit der bedingten Formatierung kann man sehr wohl eine Farbänderung
detektieren, zumindest in Excel 2010.

die Tabelle ist ein Arbeitsbericht. Darim werden einzelne Zellen von
Hand eingefärbt, damit dort noch einmal die Daten geprüft werden. Da ist
keine bedingte Formatierung und ich kann mit händisch gefärbten Zellen
auch keine bedingte Formatierung oder Berechnung auslösen.
Mit freundlichen Grüßen
Claus

Hallo Claus,

weiß jetzt gerade nicht, was du mit "Arbeitsbericht" meinst, aber in
einem Worksheet greift die bedingte Formatierung hierfür definitiv, auch
wenn man händisch die Schriftfarbe für eine Zelle ändert. Das habe ich
überprüft und arbeitet wie erwartet. Der Zellinhalt muß dabei noch nicht
einmal verändert werden.

Ulrich

[Lisa Wilke-Thissen]

Hallo Ulrich,

"Ulrich Möller" schrieb

Post by Ulrich Möller
Mit der bedingten Formatierung kann man sehr wohl eine Farbänderung
detektieren, zumindest in Excel 2010.

aber in einem Worksheet greift die bedingte Formatierung
hierfür definitiv, auch wenn man händisch die Schriftfarbe für eine Zelle
ändert. Das habe ich überprüft und arbeitet wie erwartet. Der Zellinhalt
muß dabei noch nicht einmal verändert werden.

kannst du etwas konkreter werden? Wir lernen hier doch alle gern dazu...

[Ulrich Möller]

Post by Lisa Wilke-Thissen
Hallo Ulrich,
"Ulrich Möller" schrieb

Post by Ulrich Möller
Mit der bedingten Formatierung kann man sehr wohl eine Farbänderung
detektieren, zumindest in Excel 2010.

aber in einem Worksheet greift die bedingte Formatierung
hierfür definitiv, auch wenn man händisch die Schriftfarbe für eine
Zelle ändert. Das habe ich überprüft und arbeitet wie erwartet. Der
Zellinhalt muß dabei noch nicht einmal verändert werden.

kannst du etwas konkreter werden? Wir lernen hier doch alle gern dazu...

Hallo Lisa,

es ging mir um das Teilproblem, eine Farbänderung durch den Benutzer in
vba automatisch erkennen zu können, so daß der Anwender eventuell nicht
immer explizit von Hand eine Neuberechnung anstoßen muß. Hierzu habe ich
einen Lösungsansatz versucht, den ich eigentlich in Access des öfteren
anwende, indem ich die FormatConditions etwas anders anwende, als es
vielleicht normalerweise der Fall wäre.

Beispielsweise für den Bereich A2:C4 einmal die FormatCondition zuweisen:
Worksheets(1).Range("A2:C4").FormatConditions.add
xlExpression,,"=ColorCeck(A2)"

und damit verbunden in einem Standardmodul die Funktion:
Public Function ColorCheck(Bereich as Range) as Variant
Debug.Print "ColorCheck()", Bereich.Address,
Bereich.Font.ColorIndex
End Function

So wird mit jeder Änderung an einer Zelle diese Funktion aufgerufen,
auch wenn eine neue Schriftfarbe zugewiesen wird. Allerdings auch dann,
wenn andere Dinge an der Zelle verändert werden. Also müßte man nur die
Farbänderungen herausfiltern und auf die relevanten Farben prüfen,
ansonsten die Function direkt verlassen.

So weit so gut. Da man nun aber auch die einzelne Zelle als Argument
bekommt, könnte man vielleicht bei einer Farbänderung eine notwendige
Neurechnung zusätzlich auf die entsprechende Spalte beschränken.

Wie gesagt, nur eine Idee/Anregung. Da ich tatsächlich eher selten in
Excel-VBA etwas mache, fehlt mir allerdings die Übung, das jetzt für
dein Problem "rund" zu machen. Ich würde eventuell eine Summenformel
unterhalb der Spalten machen und im Argument die einzelnen Zellen
hinzufügen oder löschen und dann bei Änderungen diese neu berechnen.

Vielleicht hat aber Claus dazu noch einen Tip, wie man hier am besten
weiter vorgehen könnte. Er ist bestimmt wesentlich souveräner im Umgang
mit Excel als ich.

Grüße

Ulrich