Excel Hintergrundfarbe Zählen (Spalte)?
Hallo Community,
Ich mache aktuell in meinem Praktikum eine Softwareinventur und übertrage die Daten in eine Excel Tabelle. Ich strukturiere das so, dass in der Tabelle oben die Mitarbeiter stehen und links die Programme. Mit den Grün gefärbten Zellen wird dann angegeben, ob der Mitarbeiter dieses Programm besitzt/benutzt. Nun Möchte ich mir in der Spalte hinter der tabelle ausgeben lassen, wie oft dieses Programm verwendet wird. Man kann die Hintergrundfarbe Zählen allerdings funktioniert keine der bekannten Methoden.
Ein Beipiel meiner Tabelle ist im Bild zu sehen. Habt Ihr eine Lösung für mich?
5 Antworten
Hallo,
als kleine Hilfestellung:
Um die Hinterrundfarbe auszulesen, definierst du zuerst einen Namen:
Das Funktioniert so:
FORMELN ->Namens Manager -> Neu...
So, als Name gibst du z.B. Farbcode ein, der Bereich ist die Arbeitsmappe.
Den Kommentar kannst du leer lassen und in das Feld "Bezieht sich auf" kommt nun eine der folgenden Formeln (Je nach dem was du erreichen möchtest)
Formel1 [Es wird die Schriftfarbe der links nebenstehenden Zelle ausgelesen]:
=ZELLE.ZUORDNEN(24;INDIREKT("ZS(-1)";))Formel2 [Es wird die Schriftfarbe der darüber liegenden Zelle ausgelesen]
=ZELLE.ZUORDNEN(24;INDIREKT("Z(-1)S";))Formel3 [Es wird die Hintergrundfarbe der links nebenstehenden Zelle ausgelesen]:
=ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";))Formel4 [Es wird die Hintergrundfarbe der darüber liegenden Zelle ausgelesen]
=ZELLE.ZUORDNEN(63;INDIREKT("Z(-1)S";))So, dann gibst du nachher neben oder unter der Zelle, wo du den Farbcode haben möchtest, einfach =Farbcode ein.
Dann kannst du ja mit dem Wert weiter arbeiten, der Farbcode für Grün ist z.B. 10, dann kannst du ja abfragen
=WENN(Farbcode=10;"Programm vorhanden";"Nicht vorhanden")
Als Beispiel :)
Grüße,
iMPerFekTioN
Die farbige Markierung ginge über bedingte Formatierung (Zellbereich >0), dazu muss dann aber eine Zahl eingegeben werden. Die Anzahl dann z.B.mit der Formel =ANZAHL(Zellbereich) in einer Zelle anzeigen lassen. Farben kann Excel nicht zählen, du musst also einen Wert angeben.
Die Lösung, die Mitarbeiter mit Farben zu markieren ist zwar optisch gut, aber zum Auswerten keine ratsame Lösung, da man über Formeln keine Farben zählen kann. (Ja es gibt die Notlösung über Namen mit Formel, aber das benötigt zahlreiche Hilfsspalten-/zeilen und aktualisiert sich auch nicht zuverlässig)
Mein Tip wäre einfach ein X als Markierung zu nehmen, dann eine bedingte Formatierung mit Formel zu verwenden (=B2="X") und die Schriftfarbe = Zellfarbe zu wählen. Dann hast Du optisch das gleiche Ergebnis mit einer grünen Zelle, brauchst bei Änderungen nicht die Zellen umfärben, sondern einfach X setzen oder löschen. Gerade wenn Du zeilenweise weiß/blau verwendest, ist es mit dem Umfärben nervig.
Die Anzahl lässt sich dann auch über =anzahl2(B2:E2) oder =zählenwenn(B2:E2;"X") mit normalen Formeln ermitteln
Diese Formel bezieht sich nur auf die linke Zelle. Will man alle Zellen mit einer bestimmten Hintergrundfarbe zählen (z. B. mit Zählenwenn), muss man die Formel für jede Zelle der Matrix ausführen und über die ebenso groß dimensionierte Matrix ZÄHLENWENN laufen lassen.
Ich habe es jedenfalls nicht hin bekommen, einen Namen mit ZÄHLENWENN und ZELLE.ZUORDNEN zu definieren.
Wenn ich mich nicht täusche, kommt man um Hilfszellen nicht herum.
Bei mir funktioniert das alles super ohne eine Hilfsspalte :o
Und die Zellen auf die es sich bezieht kann man ja abändern, die Formel kann man dann ja bei jedem Mitarbeiter mit anbringen.
Sei so nett und gib mir die komplette Formel für den Namensmanager, die die Vorkommnisse der Hintergrundfarbe grün (10) zählt. Das interessiert mich wirklich.
Das ist kein sinnvolles Vorgehen aus meiner Sicht:
Warum unnötig Hilfsspalten oder -Zeilen verwenden?
Klar kann man statt der einen Formel natürlich auch mehrere hinterlegen und den Zellbezug "ZS(-1)", "ZS(-2)"... machen. Das kann man addieren - elegant und einfach sieht aus meiner Sicht aber anders aus.
Nicht zuletzt: Es sind da auch blau markierte (leere) Zellen. Nur nach Farbe abzufragen reicht also nicht, es spielt der Farbcode eine Rolle, was wiederum zu neuen Fehlern führen kann.
Vielleicht nur meine persönliche Sichtweise: Markieren in Excel ja, aber nicht Malen und so würde ich das Eintragen von Informationen durch Farbe sehen.
Darüber liegende Zelle auslesen:
Namensmanager:
Name: Farbcode
Bereich: Arbeitsmappe
Kommentar: -
Bezieht sich auf: =ZELLE.ZUORDNEN(63;INDIREKT("Z(-1)S";))
Linke Zelle auslesen:
Namensmanager:
Name: Farbcode
Bereich: Arbeitsmappe
Kommentar: -
Bezieht sich auf: =ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";))
Ja da geb ich dir auch vollkommen recht, ich wollte die Frage nur so beantworten das der Fragensteller 1. Sieht, welche Problematiken sein vorgehen hervorbringen kann aber das es 2. (grundsätzlich) durchaus möglich ist.
@Supoptimierer,
hab vergessen den Formelteil mit anzuhängen:
Ich habe in diesem Beispiel eine Reihe in 2er Abständen Grün gefärbt.
Gefärbte Zellen:
M9, M11, M13, M15, M17, M19, M21
Die erste Zelle wird in N5 abgefragt:
=WENN(Farbcode=10;1;0)
Dann wird in N12 diese Formel eingefügt und kopiert:
=WENN(UND(Farbcode=10;N9>=1);N9+1;N9)
Wenn man jetzt eine Zelle um färbt kommt trotzdem die Richtige Anzahl gefärbter Zellen heraus.
Wie gesagt, damit holt man nur die Farbinformation aus einer Zelle. Wie zählt man ohne Hilfszellen eine bestimmte Farbe in einem Bereich?
Och iMPerFekTioN, ich meine doch, wie man das ohne Hilfsspalte lösen kann. -.-
Du hast geschrieben:
Warum brauchst du für eine Namensdefinierung viele Hilfsspalten? Das hab ich jetzt nicht verstanden
Und an anderer Stelle
Bei mir funktioniert das alles super ohne eine Hilfsspalte :o
Deine bisherigen Ansätze kommen ohne Hilfsspalten nicht aus.
Hab's jetzt auch geschnallt :p
Hab's jetzt auf die Schnelle so gelöst:
2 Namen definiert:
1. Name Farbcode2 mit der Formel:
=WENN(ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";));1;0)
2. Name Farbcode mit der Formel:
=WENN(ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";))=10;INDIREKT("Z(-2)S";)+1;INDIREKT("Z(-2)S";))Die erste Zelle wird dann mit =Farbcode2 befüllt und die restlichen mit =Farbcode
Ninombre hat recht. Es ist ungünstig, mit Formatierungen Berechnungen anzustellen.
Dennoch sei der Vollständigkeit halber erwähnt, dass sich hierzu auch einfache Makros schreiben lassen:
Public Function ZaehleBGFarbe(rBereich As Range, rMusterZelleMitBGFarbe As Range) As Integer
Dim rZelle As Range
ZaehleBGFarbe = 0
For Each rZelle In rBereich
If rZelle.Interior.ColorIndex = rMusterZelleMitBGFarbe.Interior.ColorIndex Then ZaehleBGFarbe = ZaehleBGFarbe + 1
Next
End Function
Würde ich mich mit Makros so gut auskennen wie du, wäre meine komplette Tabelle eine einziges Makro :x Leider hab ich da mal so gar keine Ahnung von :p
Super Lösung! DH!
Wer kein Bücherwälzer ist und gerne für sich VBA entdecken will, sollte am Anfang etwas mit dem Makrorekorder spielen. Zwar ist der erzeugte Code meistens suboptimal, aber man lernt zum Beispiel Eigenschaften wie Interior.Color kennen.
Zwar ist der erzeugte Code meistens suboptimal
das kannst insb. Du dann ja im Nachgang noch verbessern ;-)
Das geht über "Bedingte Formatierung"
Ähm....ich denke da hab ich dich missverstanden.
Nun gut, die bedingte Formatierung hast du ja aufgrund eines Merkmals. Da brauchst du jetzt ja nur diese Merkmale zählen.
Die Zellen wurden aber nicht über die Bedingte Formatierung Grün gefärbt. DAs musste alles per Hand Gefärbt werden
Dann mach das über die bedingte Formatierung.
Wenn du anstatt der Farbe ein Mekrmal vergibst, dann kannst du das auch zählen. Einfach so kann Excel keine Farbe zählen.
Warum brauchst du für eine Namensdefinierung viele Hilfsspalten? Das hab ich jetzt nicht verstanden =D
Hintergrundfarbcodes kann mit folgender Namensdefinierung auslesen:
Zugegeben, das mit dem Aktualisieren stimmt, das muss man per Tastendruck machen oder die Zelle nochmal mit der Eingabetaste bestätigen.