Zelle.Zuordnen in Excel 2007

Bild

Betrifft: Zelle.Zuordnen in Excel 2007
von: Robert
Geschrieben am: 15.07.2015 14:29:38

Hallo Zusammen.
Ich versuche gerade eine Projektliste visuell etwas praktikabler zu gestalten.
Jede Zeile ist ein Projekt mit vielen Spaltenweisen Einträgen. Gibt es in einer Spalte ein ToDo, wird diese Zelle manuell Gelb oder Rot gefüllt.
Nun möchte ich in Spalte A den Projektstatus auf schnellen Blick einsehen können.
Die Zelle in Spalte A soll Grün (keine Todos), Gelb (ein gelbes Todo) oder Rot (rotes ToDo) anzeigen. Automatisch.
Ich brauche also eine Möglichkeit, per Formel die Hintergrundfarben der Zeile abzufragen und diese mit Wenn zu verknüpfen.
In Sprache: (=Wenn(Anzahl Rote Zellen mehr als 0 dann "Rot", Wenn Anzahl Gelbe mehr als 0 dann "Gelb", Wenn Anzahl Gelbe und Anzahl Rote Zellen = 0 dann "Grün").
Ich weiß, dass es die Funktion Zelle.Zuordnen(63) gibt, die mir die Hintergrundfarbe geben würde. Ich verwende auch eine Funktion in VBA, die mir den Colorindex ausgibt.
Leider bekomme ich es nicht hin, eine der beiden Funktionen über die Ganze Zeile (oder mehrere Zellen) laufen zu lassen. Ich hatte versucht, die VBA Funktion oder die über den Namensmanager als Kriterium für ein ZählenWennS zu verwenden, bisher ohne Erfolg.
Über den Namensmanager kann die Bedingte Formatierung nur in Abhängigkeit von der Hintergrundfarbe EINER Zelle angegeben werden.
Weiß einer da weiter?
Gibt es die Möglichkeit einer Persoal Function die mir die Option gibt, mehrere Zellen als Bezug anzugeben?
Wenn nötig, kann ich noch schnell ne Beispieldatei basteln.
Vielen Dank und Viele Grüße
Robert

Bild

Betrifft: AW: Zelle.Zuordnen in Excel 2007
von: Daniel
Geschrieben am: 15.07.2015 14:39:57
HI
ohne VBA müssest du das Zelle.Zuordnen in einer Paralleltabelle verwenden, so dass du für jede Zelle ein Zellezuordnen verwenden kannst.
Dann kannst du normal mit ZählenWenn() feststellen, ob im Parallel-Zellbereich die Farbe rot oder gelb vorkommt.
Gruß Daniel

Bild

Betrifft: Da du bereits eine UDF zum ZellFarbeFeststellen...
von: Luc:-?
Geschrieben am: 15.07.2015 15:25:47
…hast, Robert,
die offensichtlich aber (ebenso wie ZELLE.ZUORDNEN) nicht matrix(formel)tauglich ist, musst du diese halt matrixtauglich machen. Das kann man relativ einfach auch nachträglich machen, indem man eine For Each-Schleife vorschaltet, deren Laufvariable dem HptArgument der UDF entspricht. Dazu muss zuvor gefragt wdn, ob dieses HptArgument ein Array ist, und in diesem Fall das HptArgument in eine andere Variable überehmen, so dass dieser ZyklusKopf verwendet wdn kann:
For Each hptVariable In andereVariable
Innerhalb der Schleife erfolgt dann ein UP-Sprung (GoSub) zum normalen Ablauf, an dessen Ende ein bedingtes Return (wenn andereVariable nicht Nothing ist) stehen muss. Nach erfolgtem Return muss in der Schleife das Ergebnis einem Element einer entsprd dimensionierten Ergebnis­Variablen zugewiesen wdn (bei diesem SchleifenTyp müssen die Indizes separat hochgezählt wdn!). Nach Durchlauf der Schleife wird die ganze Ergebnis­Variable der UDF zugewiesen.
Alternativ zum UP-Sprung kann natürlich auch die UDF an dieser Stelle rekursiv mit der LaufVariable als HptArgument aufgerufen wdn. Das könnte man dann allerdings auch extern erledigen (die Original-UDF muss dann nicht geändert wdn!) und eine Rahmen-UDF dafür schreiben, die eine Matrix zyklisch verarbeitet und die Original-UDF dann intern mit der jeweiligen LaufVariablen als HptArgument aufruft. Dabei muss natürlich wieder eine ErgebnisMatrix gefüllt wdn, die die Rahmen-UDF als Ergebnis zurückgibt.
Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: AW: Da du bereits eine UDF zum ZellFarbeFeststellen...
von: Robert
Geschrieben am: 15.07.2015 16:10:25
Hallo Luc,
ja, auf die idee mit der for each.. schleife bin ich auch schon gekommen.
Ich sehe da die Gefahr dass es immer ewig zum berechnen braucht.
Vllt probiere ich es einfach mal aus.
eine Möglichkeit, ein Zählenwenn nach .Interior.Colorindex zu machen gibt es auch in VBA nicht?
Oder irgendeine Möglichkeit, das ohne schleife zu lösen?
Viele Grüße
Robert

Bild

Betrifft: AW: Da du bereits eine UDF zum ZellFarbeFeststellen...
von: Daniel
Geschrieben am: 15.07.2015 17:03:57
Hi
auch ein ZählenWenn enthält intern eine Schleife über alle angegebenen Zellen.
Ob du die Schleife jetzt selber programmierst oder diese in der verwendeten Funktion schon drin steckt, macht in VBA keinen allzugrossen unterschied für die Verarbeitungszeit (es gibt sogar fälle, in denen die selbstprogrammierte Schleife schneller ist, als die entsprechende Funktion).
Excel ist von seinen Entwicklern noch nicht für das Rechnen mit Formaten ausgelegt. Daher wirst du kaum Funktionen finden, die solche Auswertungen erstellen sondern musst das selber schreiben.
weitere Codebeispiele solltest du finden, wenn du mal nach "Farbsumme" oder "ZählenWenn Farbe" suchst.
beschleunigend kannst du ja einsetzen, dass du beim ersten Rot die Schleife sofort abbrechen kannst.
Gruß Daniel

Bild

Betrifft: 'For Each' ist sehr schnell, allerdings ...
von: Luc:-?
Geschrieben am: 15.07.2015 22:03:31
…besonders über Datenfelder, etwas weniger über die hier benötigten ZellBereiche, Robert;
aber das FarbenZählen benötigst du nicht unbedingt in der UDF, denn das kann idR extern erfolgen (zB mit HÄUFIGKEIT, mit ZÄHLENWENN würdest du eine/n Hilfsspalte bzw -bereich benötigen!).
Im Folgenden stelle ich mal ein Bsp vor, bei dem alle Farben ermittelt wdn. Mit HÄUFIGKEIT kann man dann feststellen, wie oft die einzelnen Farben verwendet wurden. Benötigst du nur die Häufigkeit einer Farbe, gibst du nur die als 2.Argument von HÄUFIGKEIT an (in einer 2.Zelle wird dann die Anzahl der übrigen Farben insgesamt angegeben, wenn du die Fml als MatrixFml über 2 unter­einander liegende Zellen eingibst):

 ABCDEFGHI
1  655351x 655351x 6553565535652802x 655352x 65535
2  167772152x 167772152x 1677721516777215167116802x 652802x 65280
3  2551x 2551x 255255167772153x 167772153x 16777215
4  16777215  16777215167116802x 167116802x 16711680
5  652801x 652801x 6528065280655351x 2551x 255
6Formeln: C1:C5: {=ZFarben(A1:A5;JETZT())}F1:G5: {=ZFarben(A1:B5;JETZT())}
7D1:D5: {=WENN(HÄUFIGKEIT(ZFarben(A1:A5);ZFarben(A1:A5))=0;"";HÄUFIGKEIT(ZFarben(A1:A5);ZFarben(A1:A5))&"x "&ZFarben(A1:A5))}
8E1:E5: {=WENN(TempStoRd(HÄUFIGKEIT(TempStoRd(ZFarben(A1:A5));TempStoRd()))=0;"";TempStoRd()&"x "&ZFarben(A1:A5))}
9H1:H5: {=WENN(TempStoRd(HÄUFIGKEIT(TempStoRd(ZFarben(A1:B5));TempStoRd()))=0;"";TempStoRd()&"x "&INDEX(ZFarben(A1:B5);GANZZAHL((ZEILE()-1)/2)+1;REST(ZEILE()-1;2)+1))}
10I1:I5: {=WENN(TempStoRd(HÄUFIGKEIT(TempStoRd(ZFarben(A1:B5));TempStoRd()))=0;"";TempStoRd()&"x "&MTRANS(VSplit(VJoin(MTRANS(ZFarben(A1:B5));;-2);;1)))}

Spalten C:E zeigen Ergebnisse nur für die 1.Spalte (A), F:I für beide Spalten (A:B). HÄUFIGKEIT wandelt eine Matrix in einen vertikalen Vektor, wobei zeilenweise vorgegangen wird (erst alle Spalten der 1.Zeile, dann der 2. usw). Die Bsp-UDF ZFarben¹ liefert ein Datenfeld gleicher Form und Größe wie ihr 1.Argument. Da es sich um ein Datenfeld handelt, geht VBA bei Weiter­Verarbeitung hier spalten-, nicht zeilenweise vor, was die spezielle Behandlung des reinen UDF-Ergebnisses in Spalten H:I bedingt (sonst passt es nicht zum Ergebnis von HÄUFIGKEIT).
In Spalten E;H:I habe ich außerdem eine UDF mit Merkfunktion (TempStoRd) benutzt, um die FmlLänge (auch aus Darstellungsgründen) zu verkürzen. Die musst du analog Spalte D ersetzen (ggf auch durch die Namen benannter TeilFmln → es sind 2 unter­schiedliche Teil­Fmln!).
Falls die Fml bei ZellFarbErmittlung über mehrere Spalten (lt F:G) nicht so kompliziert wdn soll, wie in Spalte H, kämen 2 weitere UDFs ins Spiel, die im Archiv vorhanden sind (Spalte I; dazu gibt's genug Hinweise in Forum und Archiv, so dass sie bei Bedarf leicht gefunden wdn können — bei VJoin Version 1.3 →Datei-DownLoad← verwenden!).
Eine Besonderheit weist diese einfache Bsp-UDF dennoch auf, sie ist volatil. Das nutzt zwar nicht viel, weil die nachträgliche Änderung von Formaten (hier: ZellFarben) auf die übliche Weise² kein Ereignis und somit auch keine Neuberechnung auslöst, aber, wenn wenig­stens einer Anwendung der UDF das optionale 2.Argument (AktDummy) mitgegeben wird (s.BspSpalten C;F:G), kann die Neu­berechnung per [F9] bzw DoppelKlick in eine beliebige Zelle ausgelöst wdn. Das macht die UDF unbhängig von anderen, nur evtl ausgelösten Berech­nungen.
¹ Da diese UDF nur ein Prinzip-Bsp sein soll und ggf durch eine andere ersetzt/ergänzt wdn kann, hat sie nicht die universelle Form, die man von vielen meiner UDFs gewohnt ist. Deshalb fehlen auch UrheberVermerk und Kurzbeschreibung/-anleitung.
² Anders sieht es aus, wenn Formate/Farben mit dem FormatPinsel aufkopiert wdn. Das löst ein Worksheet_Change-Ereignis aus!


Programm der Bsp-UDF:
Function ZFarben(Bereich As Range, Optional ByVal AktDummy)
    Dim cn As Long, cx As Long, rn As Long, rx As Long, zFrb, ber As Range
    Application.Volatile
    If IsArray(Bereich) Then
        cn = Bereich.Columns.Count: rn = Bereich.Rows.Count
        ReDim erg(rn - 1, cn - 1)
        For Each ber In Bereich
            erg(rx, cx) = ber.Interior.Color
            cx = (cx + 1) Mod cn: rx = rx - CInt(cx = 0)
        Next ber
        ZFarben = erg
    Else: ZFarben = Bereich.Interior.Color
    End If
End Function
Übrigens, .ColorIndex ist ab Xl12/2007 nicht mehr zu empfehlen. Die einzelnen Indizes sind mehrfach belegt, so dass ganz unter­schied­liche Farben zusammengezählt wdn könnten! Außerdem sieht .Color ungefärbte Zellen als Weiß an, .ColorIndex liefert dann einen nega­tiven Wert!
Gruß, Luc :-?

Bild

Betrifft: Nachtrag: Bsp-UDF nur f.DirektZellFarbe geeignet!
von: Luc:-?
Geschrieben am: 15.07.2015 22:05:30
:-?

Bild

Betrifft: AW: Nachtrag: Bsp-UDF nur f.DirektZellFarbe geeignet!
von: Robert
Geschrieben am: 16.07.2015 11:04:09
Hallo Zusammen,
Vielen Dank für die vielen Meldungen. Ich hab einiges für die Zukunft gelernt.
Mittlerweile sieht das ganze so aus:

Function ProjectStatus(r As Range, fCol As Long, lCol As Long)
Dim Yellow As Long
Dim Spalte As Long
Application.Volatile
Yellow = 0
For Spalte = fCol To lCol
If ActiveSheet.Cells(r.Row, Spalte).Interior.ColorIndex = 3 Then GoTo Red
If ActiveSheet.Cells(r.Row, Spalte).Interior.ColorIndex = 6 Then Yellow = 1
Next Spalte
If Yellow = 1 Then ProjectStatus = "Yellow" Else ProjectStatus = "Green"
Exit Function
Red:
ProjectStatus = "Red"
End Function

Funktioniert soweit wunderbar, die Berechnungszeit ist trotz Volatile nicht spürbar.
Für zukünftige Projekte werde ich nicht mehr .Colorindex benutzen, danke für den Hinweis.
Hier ist es jedoch nicht wichtig, da nur Weiß, Rot und Gelb zum einsatz kommen.
Der Vorteil der UDF gegenüber einer reinen VBA oder Macrolösung ist, dass die Projektliste nun auch von nicht so Excel versierten Personen erweitert werden kann.
Sie müssen einfach nur die Formel "runterziehen".
Vielen Dank + Grüße
Robert

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Ausgabe ohne Leerzeilen"