Zählenwenn ( Farbe)

mars_bf1f60 · 10. November 2019 um 21:35

Hallo,

gibt es in Excel 2007 eine Funktion oder Möglichkeit eine Zählennwenn Funktion einzurichten, die zählt wie viele Zeilen in Spalte B die Fabe gelb haben. Beispiel: Von B1 bis B10 haben 2 Zeilen (B5,B8) eine gelbe Formatierung. also müsste bei Zählenwenn das Ergebnis „2“ sein. Geht das irgendwie?
Danke

Niclaus_Wurthberg · 10. November 2019 um 21:35

Hallo Mars
Eine Möglichkeit gibt es mit einem Excel4-Makro-Befehl. Ich glaube, das ist hier auch schon beschrieben worden. In Excel 2003 geht das so:
Menü Einfügen / Namen / Definieren …
Als Namen eingeben z. B. „Zellfarbe“. Und unten unter „Bezieht sich auf“ folgende Formel einfügen:
=ZELLE.ZUORDNEN(63;INDIREKT(„ZS(-1)“)

Die Formel wird folgendermassen angewendet: Rechts neben der eingefärbten Zelle die Formel „=Zellfarbe“ eingeben. Für gelb erscheint dann z. B. die Farb-Zahl 6. Du musst also eine Hilfsspalte einfügen. - Mit ZÄHLENWENN über diese Spalte kommst Du dann zu dem von Dir gesuchten Ergebnis.
Aenderungen der Farbe werden erst nach Neuberechnen angezeigt.
Bei bedingten Formatierungen klappt das ganze nicht.

Es gibt dafür sicher auch Lösungen mit VBA. Ich kenne leider keine.
Grüsse Niclaus

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Reinhard_fb8cb7 · 10. November 2019 um 21:35

Grüezie Niclaus

Aenderungen der Farbe werden erst nach Neuberechnen angezeigt.

ja, da hakt es, auch wenn du die BDF von unten nimmst.

Bei bedingten Formatierungen klappt das ganze nicht.

Ja, egal Excelformeln oder Vba, man müßte in einer Nachbarzelle die Bdingungen der bed. Formatierung nachbauen und auswerten.

Zumindest bis vor 2007, da gibts ja Neuerungen in 2007, vielleicht auch in dem Punkt…

Es gibt dafür sicher auch Lösungen mit VBA. Ich kenne leider
keine.

Dann kenntste die jetzt :

Function Zellfarbe()
Zellfarbe = Application.Caller.Offset(0, -1).Interior.ColorIndex
End Function

Anwendung in einer Zelle:
=Zellfarbe()

Okay, viel Sinn macht das m.E. nicht, wenn man eh schon Vba benutzt kann man auch auf die Hilfsspalte verzichten.

Gruß
Reinhard

Reinhard_fb8cb7 · 10. November 2019 um 21:35

OT BDF = BenutzerDefinierte Funktion o.w.T.

Niclaus_Wurthberg · 10. November 2019 um 21:36

Salü Reinhard
Du lebst ja noch!!!

Grüezie Niclaus

Grüezi = männliche Form des Schweizer-Grusses
Grüezie = weibliche Form - Gruss an oder von Grazien )

Function Zellfarbe()
Zellfarbe = Application.Caller.Offset(0,-1).Interior.ColorIndex
End Function

Danke für die Function. Caller kannte ich nicht.
Grüsse Niclaus

Thomas_Ramel · 10. November 2019 um 21:36

Grüezi Mars

gibt es in Excel 2007 eine Funktion oder Möglichkeit eine
Zählennwenn Funktion einzurichten, die zählt wie viele Zeilen
in Spalte B die Fabe gelb haben. Beispiel: Von B1 bis B10
haben 2 Zeilen (B5,B8) eine gelbe Formatierung. also müsste
bei Zählenwenn das Ergebnis „2“ sein. Geht das irgendwie?

Berechnungen nach Farben als Bedingung sind in Excel nur Krücken - Du solltest dir da etwas anderes ausdenken.

Aber dennoch könntest Du das Ganze z.B. mit der folgenden Funktion angehen - die Unsicherheit mit der Aktualisierung der Formel bleiben aber selbstredend bestehen):

Function ZählenWennFarbe(Bereich As Range, \_
 SuchFarbe As Variant, \_
 Optional bolFont As Boolean = False) As Double

'Idee von Melanie Breden, © [email protected] / 13.10.2004 / 11.12.2004
'Funktion zur Anwendung von ZÄHLENWENN mit Hintergrundfarbe
'oder Schriftfarbe als Kriterium
'
'Die Parametereingabe erfolgt in derselben Reihenfolge wie in der Funktion
'ZÄHLENWENN():
' - Der erste Parameter erwartet den Suchbereich
' - Der zweite Parameter erwartet einen Zellbezug
' (Hintergrund/Schriftfarbe) oder Farbindex (Zahl)
' Farbindex '0' zählt Zellen ohne farbigen
' Hintergrund/Standard-Schriftfarbe
' - Der dritte Parameter erwartet Wahr/Falsch für die Festlegung
' ob nach Hintergrund- oder Schriftfarbe gezählt werden soll
'
' Bsp =ZählenWennFarbe(A1:A10;A1;0) =ZählenWennFarbe(A1;A1:A10;1)
' =ZählenWennFarbe(A1:A10;3;0) =ZählenWennFarbe(3;A1:A10;1)

'Zur automatischen Aktualisierung im Tabellenblatt den folgenden Term
'anhängen: +(0\*JETZT()) und F9 drücken
'Also z.B. wie folgt: =ZählenWennFarbe(A1:A10;A1)+(0\*JETZT())

Dim intColor As Integer
Dim rngCell As Range

 If bolFont Then
 If IsObject(SuchFarbe) Then
 intColor = SuchFarbe(1).Font.ColorIndex
 Else
 intColor = SuchFarbe
 End If

 For Each rngCell In Bereich
 If rngCell.Font.ColorIndex = intColor Then
 ZählenWennFarbe = ZählenWennFarbe + 1
 End If
 Next

 Else
 If IsObject(SuchFarbe) Then
 intColor = SuchFarbe(1).Interior.ColorIndex
 Else
 intColor = SuchFarbe
 End If

 For Each rngCell In Bereich
 If rngCell.Interior.ColorIndex = intColor Then
 ZählenWennFarbe = ZählenWennFarbe + 1
 End If
 Next

 End If
End Function

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Reinhard_fb8cb7 · 10. November 2019 um 21:36

Grüezi Niclaus,

Du lebst ja noch!!!

*rumtast* scheint so War mal drei Wochen nicht hier.

Grüezie Niclaus

Grüezi = männliche Form des Schweizer-Grusses
Grüezie = weibliche Form - Gruss an oder von Grazien )

Aha, so ist das, danke für die Information.

Danke für die Function. Caller kannte ich nicht.

Ich „kenne“ im Sinne von Wissen Caller auch nicht.

Probier mal in beliebigen Functions, Subs aus:

Msgbox Application.Caller.Name
Msgbox Application.Caller.Address
u.ä.

dann weißt du zu Caller soviel wie ich *gg*

Gruß
Reinhard

Niclaus_Wurthberg · 10. November 2019 um 21:36

Grüezi Thomas

SUUUUPPPPPPEEEERRRRR!!!

Eine Frage nur - damit ich mein Weiterbildungssoll für diese Woche erfüllen kann:

Zur automatischen Aktualisierung im Tabellenblatt den
folgenden Term
anhängen: +(0*JETZT()) und F9 drücken

Das Anhängsel „+(0*JETZT())“: Was bewirkt das genau?
Eines habe ich festgestellt: Ohne das Anhängsel muss ich zur Aktualisierung CTRL-ALT-F9 drücken. Mit Anhängsel nur noch F9.
Ich habe aber nicht durchschaut, wie es dazu kommt.

Kannst Du mich aufklären? - Vielen Dank und viele Grüsse
Niclaus

Reinhard_fb8cb7 · 10. November 2019 um 21:36

Grüezi Thomas,
Grüezi Niclaus,
Grüezie Melanie,

Zur automatischen Aktualisierung im Tabellenblatt den
folgenden Term
anhängen: +(0*JETZT()) und F9 drücken

Das Anhängsel „+(0*JETZT())“: Was bewirkt das genau?

Naja, Thomas und Melanie übertreiben da, wahrscheinlich wollten sie nur ein Buch mit mehr Klammerzeichen vollkriegen

+0*JETZT()
reicht da vollkommen.

Was „Jetzt“ bewirkt, IN Jetzt() steht permanent im quasi Sekundentakt ein neuer Zeitwert. Es wird also systemintern permanent aktualisiert.

Theoretisch müßte dann auch eine Formel wo steht
=B1 + 0*JETZT()
permanent aktualisiert sein.

Das klappt mal, mal klappt es nicht. Je nachdem wo du das Jetzt() einbaust, in die Formel eines namens, in eine Zellenformel.

So richtig schlau bin ich da noch nicht geworden, warum manchmal F9 langt um das Blatt zu aktualisieren, mal muß man die Zelle anwählen, dann F2 und Enter.

Was du aber auch nicht außer Acht lassen solltest bei dem Jetzt()-Einbau, ich hab ja mit Excel nix am Hut und teste nur mit kleinen Beispieltabellen.
Nimm mal an, du hast da 5.000.000 Zellen mit Formeln und alle werden neu berechnet wenn du in einer was eingibst.
Kann dauern…

Auch Volatile in BDFs ist da nicht ganz ohne.

Was ich mit all dem meine kann dir sicher Thomas oder Melanie genauer erläutern.

Gruß
Reinhard

Eines habe ich festgestellt: Ohne das Anhängsel muss ich zur
Aktualisierung CTRL-ALT-F9 drücken. Mit Anhängsel nur noch F9.
Ich habe aber nicht durchschaut, wie es dazu kommt.

Kannst Du mich aufklären? - Vielen Dank und viele Grüsse
Niclaus

Thomas_Ramel · 10. November 2019 um 21:37

Grüezi Niclaus

SUUUUPPPPPPEEEERRRRR!!!

Gell

(obschon ich nach wie vor die Ansicht vertrete, dass Berechnungen aufgrund von Farben und Formaten in Excel reine Krücken sind und es bessere Techniken für die Berechnung nach Bedingungen gibt).

Eine Frage nur - damit ich mein Weiterbildungssoll für diese
Woche erfüllen kann:

Zur automatischen Aktualisierung im Tabellenblatt den
folgenden Term
anhängen: +(0*JETZT()) und F9 drücken

Das Anhängsel „+(0*JETZT())“: Was bewirkt das genau?

JETZT() ist eine flüchtige Funktion, die sich immer (auch) neu berehcnet wenn die Mappe berechnet wird.

Eines habe ich festgestellt: Ohne das Anhängsel muss ich zur
Aktualisierung CTRL-ALT-F9 drücken. Mit Anhängsel nur noch F9.
Ich habe aber nicht durchschaut, wie es dazu kommt.

Mit F9 löst Du eine neuberechnung aus, was sich dann auf alle flüchtigen Funktionen auswirkt, daher wird bei der Verwendung dieses ‚Anhängels‘ (und daher mag ich es eben komplett geklammert, umd das zu kennzeichnen, gell Reinhard) die beterffende Zelle ebenfalls neu berechnet.

Im Grunde genommen ist das sehr ähnlich (wenn nicht sogar identisch) mit ‚Application.Volatile‘ im Code der Funktion drin.
Den Vorteil des Terms in der Zelle sehe ich darin, dass dann beim Betrachten dr Formel gleich auffällt, dass da was besonderes ist, im Gegensatz zu ‚application.Volatile‘ im Code selbst.

CTRL-ALT-F9 erzwingt die Neuberechnung der kompletten Mappe, egal ob es sich um flüchtige Funktionen handelt oder nicht.

Kannst Du mich aufklären?

‚Mein Sohn nimm Platz, wir müssen ein Gespräch unter Männern führen‘…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Niclaus_Wurthberg · 10. November 2019 um 21:37

Vielen Dank!
Niclaus

Kannst Du mich aufklären?
‚Mein Sohn nimm Platz, wir müssen ein Gespräch unter Männern
führen‘…

Können wir das nächste Woche machen? Ich geh zu meiner Freundin. Ihre Eltern sind 3 Tage weg.

Marion_L_we · 10. November 2019 um 21:37

Hallo Reinhard,

willkommen, ich freu mich, dass Du wieder da bist.

GuK Marion