Excel 2007 zählenWenn / zähleWennFarbe?

muhisedl_7576ff · 7. Dezember 2011 um 11:44

Hallo ich dachte ich probiere jetzt mal wer-weiss-was aus bevor ich an der Aufgabe noch verzweifel

So ich versuche mal die Aufgabe möglichst gut wiederzugeben:

Ich habe eine Excel-Tabelle in der gibt es verschiedene Aspecte, diese können drei Ausprägungen annehmen „Yes“, „Partly“ und „No“. Diese sind farblich markiert mit grün, gelb und rot.

In diese drei Ausprägungen kommt entweder ein „X“ oder „NA“, wobei das „X“ dafür steht das entweder YES,Partly oder NO erfüllt ist. Und NA wenn ich keine Informationen habe.

Nun soll eine Formel alle „X“ zählen, wobei Yes = 1, Partly = 0 und No = -1 sein soll in der Zählung.

Am Ende soll das ganze Ergebnis der Rechnung von den Werten 1,0,-1 noch durch die Anzahl der „X“ geteilt werden.

Jetzt ist meine Frage, jemand eine Ahnung wie ich das mit einer einfachen Formel umsetzen kann?
Es wäre auch schön wenn die Werte für Yes,Partly und No aus einer Extratabelle entnommen werden, damit diese einfach geändert werden können.

Ich hätte jetzt drüber nachgedacht vielleicht so zu zählen das alle Zellen die z.B. das Argument erfüllen
Wert = X und Farbe Grün = 1 ist.

Meint ihr das wäre sinnvoll und wenn wie setzt man das am besten um oder gibt es einen einfach Weg den ich grade einfach nicht sehe?

Ich hoffe ich konnte das Problem beschreiben! Wenn noch Fragen offen sind, raus damit

Mit freundlichen Grüßen

muhisedl

abgemeldet5 · 7. Dezember 2011 um 11:54

Hallo muhisedl,
mir ist im Moment nicht klar wie deine Tabelle aufgebaut ist.
Es fehlen die konkreten Bezüge [zB. A1 bis A100]

Die beste Variante wäre wohl eine kleine Beispieldatei.xls. in der zu erkennen ist wie die Tabelle ausgefüllt und was gezählt wird.

Dann könnte man einen Vorschlag auch direkt überprüfen.

Link zum Hochladen
Download-Link hier rein kopieren

Gruß Holger

Helmut_Taunus · 7. Dezember 2011 um 20:11

Hallo,
die Farbe ist eine Formatierung der Zelle, vllt wie die Anzahl von Nachkommastellen. Damit duerfte man kaum weiterkommen.

Besser mit dem Zellinhalt eine Formel aufbauen.

Warum willst Du Text in die Zelle tun und dann mit dem Text rechnen?
Waere besser, eine Zahl in die Zellen zu tun und damit rechnen.

Statt yes also 1 in die Zelle, dann kann man die Einsen addieren und Mittelwert bilden.

Wenn eine Anzeige von yes noetig ist, dies in einer anderen Spalte darstellen, oder umgekehrt, yes eingeben und sofort in einer anderen Spalte dazu die 1 erzeugen.

Gruss Helmut

Brille1982 · 9. Dezember 2011 um 07:17

„Yes“,
„Partly“ und „No“. Diese sind farblich markiert mit grün, gelb
und rot.

In diese drei Ausprägungen kommt entweder ein „X“ oder „NA“,
wobei das „X“ dafür steht das entweder YES,Partly oder NO
erfüllt ist. Und NA wenn ich keine Informationen habe.

Nun soll eine Formel alle „X“ zählen, wobei Yes = 1, Partly =
0 und No = -1 sein soll in der Zählung.

Morgen,

bisschen unübersichtlich…wieso machst du einmal X´se und willst dafür dann doch Zahlen?
Falls ichs ansatzweise richtig verstanden hab und YES, No und PARTLY schon angegeben ist mach doch einfach eine wennformel:

Spalte A = YES, NO oder PARTLY

B1=wenn(A1=„YES“;1;wenn(A1=„NO“;-1;0))

Ansonsten bräuchte man mehr Infos!

MfG

muhisedl_7576ff · 9. Dezember 2011 um 13:23

Guten Tag,
ich hätte ja gerne was hochgeladen musste aber feststellen das die Hochladeseiten alle vom Filter geblockt sind in der Firma…

Das Ganze war doch etwas komplexer als gedacht…
Ich habe mir jetzt aus verschiedenen Quellen etwas zusammengebastelt das auf eine VB-Programmierung basiert und das klappt ganz gut.

Aber vielen Dank für die Tipps und Hinweise.

Mit freundlichen Grüßen
muhisedl

muhisedl_7576ff · 9. Dezember 2011 um 13:37

So ich poste hier einfach nochmal die Lösung, falls mal jemand sowas ähnliches sucht, vielleicht hilft das dann ja^^

Folgende Schritte sind zu tun:

Öffne mit Alt+F11 den VBA-Editor

mit Strg+R den Projekt-Explorer öffnen und aktivieren
die gewünschte Datei per Doppelklick markieren
Tastenkombination Alt+E und dann M drücken
in der rechten Fensterhälfte ist jetzt ein leeres Modul eingefügt worden
in dieses Modul muss der Code eingefügt werden
im Projektexplorer wird das Modul unter der Datei angezeigt
speichen und den VBA-Editor mit Alt+F4 schliessen
das Makro bzw. die Funktion steht dir jetzt in dieser Datei zur Verfügung

Der Code dafür ist:

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

'Idee von Melanie Breden, erweitert von Thomas Ramel / 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 zwiete 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

Wie finde ich jetzt den entsprechenden Farbcode herraus

Auf dem Reiter „Formeln“ auf „Namen definieren“ gehen.

Dort als Name z.B. „Farbe“ eingeben und als „Bezieht sich auf“ diese Formel:
„=ZELLE.ZUORDNEN(63;INDIREKT(„ZS(-1)“;FALSCH))“
Dann steht in jeder Zelle die Formel „=Farbe“ zur Verfügung, als Ergebnis wird der Farbcoder der Zelle links danaben ausgegeben (Aktualisierung nur per F9!).
Über diese Werte könnte man dann eine ZählenWenn-Formel erstellen…

So dann kommen wir zur Umsetzung in Excel

=((ZählenWennFarbe($E$5:blush:J$14;3)*Farbenwertung!$B$5)+(ZählenWennFarbe($E$5:blush:J$14;6)*Farbenwertung!$B$4)+(ZählenWennFarbe($E$5:blush:J$14;43)*Farbenwertung!$B$3))/(ZählenWennFarbe($E$5:blush:J$14;3)+ZählenWennFarbe($E$5:blush:J$14;6)+ZählenWennFarbe($E$5:blush:J$14;43))

Mit dieser Formel zähle ich nun die Farben:
=((ZählenWennFarbe($E$5:blush:J$14;3) // Zählt die Farbe Rot

*Farbenwertung!$B$5)// Multipliziert die Farbe Rot mit einem in einer anderen Tabelle definierten Wert

ZählenWennFarbe($E$5:blush:J$14;6) // Zählt die Farbe Gelb

*Farbenwertung!$B$4) // Multipliziert die Farbe Gelb mit einem in einer anderen Tabelle definierten Wert

ZählenWennFarbe($E$5:blush:J$14;43) // Zählt die Farbe Grün

)*Farbenwertung!$B$3)) //Multipliziert die Farbe Grün mit einem in einer anderen Tabelle definierten Wert

/(ZählenWennFarbe($E$5:blush:J$14;3)+ZählenWennFarbe($E$5:blush:J$14;6)+ZählenWennFarbe($E$5:blush:J$14;43)) // Nun wird die Zahl durch die Gesamteanzahl der Farben dividiert, damit ich einen Durchscnitt bekomme.

Die Farben stehen für Grün = Erfüllt, Geld = Teilweise erfüllt, Rot = nicht erfüllt.
Dadurch das die Farbwertung in einer extra Tabelle ist kann man die Werte ändern, falls die Gewichtung sich ändern sollte, aktuell steht Grün für 1, Geld für 0 und Rot für -1.
Allerdings wird Rot wahrscheinlich eine stärkere Gewichtung von -3 erhalten, da es deutlich schlechter ist, wenn etwas nicht erfüllt ist.

So das war meine Lösung, wie schon erwähnt etwas komplex, lies sich aber leider nicht anders umsetzen

Mit freundlichen Grüßen

muhisedl