farbige Zeilen zählen
Josef Gietzen
ich benutze Windows200 und Office2000. Dazu habe ich für Excel
folgende Frage:
Ich habe eine Tabelle mit 1.824 Zeilen, von denen einige mit
einem gelbem Muster hinterlegt sind. Diese Anzahl der farbigen
Zeilen erhöht sich ständig (durch Abarbeitung der Zeilen).
Ich möchte nun gern den Arbeitsfortschritt in Prozent ermitteln
können, Anzahl farbige Zeilen dividiert durch Anzahl Gesamtzeilen.
Wie ermittle ich die Anzahl der farbigen Zeilen?
=ZählenWenn(....... oder SummeWenn(......
Ich sage jetzt schon einmal Danke für euere Mithilfe.
Josef
Frank Kabel
das geht nur mit VBA:
Du kannst unten stehenden Code benutzten (kopiere diesen einfach in ein
Modul Deiner Arbeitsmappe).
Diese Funktion ColorIndex kannst du jetzt z.B.: wie folgt nutzen:
=SUMMENPRODUKT(--(ColorIndex(A1:A100)=3))
um alle roten Zellen im Bereich A1:A100 zu zählen
Wenn du alle Zellen, die ene rote SCHRIFT enthalten zählen möchtest,
nutze
=SUMMENPRODUKT(--(ColorIndex(A1:A100;WAHR)=3))
Wenn du den Farbcode einer Zelle wissen möchtest, gibt einfach
=ColorIndex(A1)
für die Zelle A1 ein.
Hoffe, das hilft Dir weiter
Frank
------
Und hier kommt der Code
'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant
'---------------------------------------------------------------------
' Function: Returns the colorindex of the supplied range
' Synopsis:
' Author: Bob Phillips/Harlan Grove
'
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant
If rng.Areas.Count > 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If
iWhite = WhiteColorindex(rng.Worksheet.Parent)
iBlack = BlackColorindex(rng.Worksheet.Parent)
If rng.Cells.Count = 1 Then
If text Then
aryColours = DecodeColorIndex(rng, True, iBlack)
Else
aryColours = DecodeColorIndex(rng, False, iWhite)
End If
Else
aryColours = rng.Value
i = 0
For Each row In rng.Rows
i = i + 1
j = 0
For Each cell In row.Cells
j = j + 1
If text Then
aryColours(i, j) = DecodeColorIndex(cell, True, _
iBlack)
Else
aryColours(i, j) = DecodeColorIndex(cell, False, _
iWhite)
End If
Next cell
Next row
End If
ColorIndex = aryColours
End Function
Private Function WhiteColorindex(oWB As Workbook)
Dim iPalette As Long
WhiteColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &HFFFFFF Then
WhiteColorindex = iPalette
Exit Function
End If
Next iPalette
End Function
Private Function BlackColorindex(oWB As Workbook)
Dim iPalette As Long
BlackColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &H0 Then
BlackColorindex = iPalette
Exit Function
End If
Next iPalette
End Function
Private Function DecodeColorIndex(rng As Range, text As Boolean, idx As
Long)
Dim iColor As Long
If text Then
iColor = rng.font.ColorIndex
Else
iColor = rng.Interior.ColorIndex
End If
If iColor < 0 Then
iColor = idx
End If
DecodeColorIndex = iColor
End Function
--
Regards
Frank Kabel
Frankfurt, Germany
"Josef Gietzen" <jgie...@t-online.de> schrieb im Newsbeitrag
news:c3rp5m$5gl$03$1...@news.t-online.com...
Josef Gietzen
Frank Kabel schrieb:
> Hi Josef
> das geht nur mit VBA:
Ja, klar - aber ich habe schon überall gestörbert, jedoch nichts
gefunden zum Zählen ganzer ZEILEN.
Auch dein Code verstehe ich so, dass er die Anzahl aller eingefärbten
ZELLEN ermittelt. Da ich aber in jeder ZEILE 40 SPALTEN belege, würde
ich eine falsche Anzahl der mit Füllfarbe versehen ZEILEN erhalten, oder?
Ich färbe die bearbeiteten Zeilen mit Klick auf die Zeilen-Nummer und
Auswahl der Hintergrundfarbe (Also werden 255 Zellen gefärbt).
schon mal Danke, Josef
Frank Kabel
"Josef Gietzen" <jgie...@t-online.de> schrieb im Newsbeitrag
news:c3rqrb$9ib$07$1...@news.t-online.com...
> Hallo Frank,
>
> Frank Kabel schrieb:
>
> > Hi Josef
> > das geht nur mit VBA:
> Ja, klar - aber ich habe schon überall gestörbert, jedoch nichts
> gefunden zum Zählen ganzer ZEILEN.
> Auch dein Code verstehe ich so, dass er die Anzahl aller eingefärbten
> ZELLEN ermittelt. Da ich aber in jeder ZEILE 40 SPALTEN belege, würde
> ich eine falsche Anzahl der mit Füllfarbe versehen ZEILEN erhalten,
oder?
> Ich färbe die bearbeiteten Zeilen mit Klick auf die Zeilen-Nummer und
> Auswahl der Hintergrundfarbe (Also werden 255 Zellen gefärbt).
>
> schon mal Danke, Josef
du kannst die Funktion dann z.B.: wie folgt nutzen:
- füge eine Hilfsspalte ein
- gib in dieser pro Zeile die Formel:
=WENN(SUMMENPRODUKT((--(ColorIndex(A1:X1)=3)))=26;1;0)
Zähle diese Hilfsspalte durch
Frank
Josef Gietzen
Frank Kabel schrieb:
>>>das geht nur mit VBA:
>>
>>Ja, klar - aber ich habe schon überall gestörbert, jedoch nichts
>>gefunden zum Zählen ganzer ZEILEN.
>>Auch dein Code verstehe ich so, dass er die Anzahl aller eingefärbten
>>ZELLEN ermittelt. Da ich aber in jeder ZEILE 40 SPALTEN belege, würde
>>ich eine falsche Anzahl der mit Füllfarbe versehen ZEILEN erhalten,
>
> oder?
>
>>Ich färbe die bearbeiteten Zeilen mit Klick auf die Zeilen-Nummer und
>>Auswahl der Hintergrundfarbe (Also werden 255 Zellen gefärbt).
>>
>>schon mal Danke, Josef
>
>
> du kannst die Funktion dann z.B.: wie folgt nutzen:
> - füge eine Hilfsspalte ein
> - gib in dieser pro Zeile die Formel:
> =WENN(SUMMENPRODUKT((--(ColorIndex(A1:X1)=3)))=26;1;0)
>
> Zähle diese Hilfsspalte durch
>
Ich habe den Code aus deiner 1. mail mit Rechtsklick auf
"Blattname - Code anzeigen" in das Modul des Tabellenblattes hineinkopiert.
Dann habe ich eine Hilfsspalte vorn (A) eingefügt und in Zelle A1
diese Formel =WENN(SUMMENPRODUKT((--(ColorIndex(A1:X1)=3)))=26;1;0)
hineingeschrieben.
Als Ergebnis erscheint immer (auch in die nach unten kopierten Zellen)
die Fehlermeldung #NAME? egal mit welcher Farbe ich den
Zeilenhintergrund eingefärbt habe.
Klicke ich "Spur zum Fehler" an, so erscheint folgende Fehlermeldung:
Der Fehlerdetektiv hat Spurpfeile oder Zirkelbezüge gefunden."
In der Zelle A1 erscheint dann über der fehlermeldung #NAME? ein kleiner
roter Punkt.
Im übrigen fangen die eventuell eingefärbten Zeilen erst mit Zeile 3 an,
da die ersten beiden Zeilen Spaltenbeschriftungen enthalten)
danke, Josef
Frank Kabel
[snip]
> Ich habe den Code aus deiner 1. mail mit Rechtsklick auf
> "Blattname - Code anzeigen" in das Modul des Tabellenblattes
hineinkopiert.
> Dann habe ich eine Hilfsspalte vorn (A) eingefügt und in Zelle A1
> diese Formel =WENN(SUMMENPRODUKT((--(ColorIndex(A1:X1)=3)))=26;1;0)
> hineingeschrieben.
> Als Ergebnis erscheint immer (auch in die nach unten kopierten
Zellen)
> die Fehlermeldung #NAME? egal mit welcher Farbe ich den
> Zeilenhintergrund eingefärbt habe.
Hi Josef
da gehört der code nicht hin. Er gehört in ein Standardmodul (du hast
ihn in ein Blattmodul kopiert) :-)
Gehe wie folgt vor:
- rufe den VBA Editor auf (mit ALT + F11)
- im linken Projekt-Explorer füge ein neues Modul ein
- kopiere den Code in dieses neue Modul
Siehe dazu auch:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Im übrigen fangen die eventuell eingefärbten Zeilen erst mit Zeile 3
an,
> da die ersten beiden Zeilen Spaltenbeschriftungen enthalten)
Da kannst du die formel einfach entsprechend ändern
Frank
Josef Gietzen
ich hatte zwischenzeitlich auch mal was anderes zu tun, als vor
dem Compi zu sitzen. Daher melde ich mich erst jetzt wieder.
Frank Kabel schrieb:
> da gehört der code nicht hin. Er gehört in ein Standardmodul (du hast
> ihn in ein Blattmodul kopiert) :-)
> Gehe wie folgt vor:
> - rufe den VBA Editor auf (mit ALT + F11)
> - im linken Projekt-Explorer füge ein neues Modul ein
> - kopiere den Code in dieses neue Modul
habe ich gemacht!
Makro läuft jetzt ohne Fehler. Aber das Ergebnis funzt glaube ich nicht.
Gleich welche Farbe die Zeile hat, in der Hilfsspalte wird immer eine
"0" geschrieben.
Kannst Du mir erläutern was die einzelnen Teile der Formel bewirken?
=WENN(SUMMENPRODUKT((--(ColorIndex(A1:X1)=3)))=26;1;0)
^ ^ ^ ^ ^ ^
| | | | | das andere
| | | | | das eine oder
| | | | ????
| | | Farbcode 3 = Rot ?
| | Zellbereich ??
| ?????
Die relevanten Spalten sind A - AJ
tschüss, Josef
Frank Kabel
erste Frage:
- färbst Du immer die ganze Zeile ein oder nur die Spalten A-AJ (im
ersteren Fall funktioniert es natürlich nicht)
Zur Erläuterung der Formel:
- SUMMENPRODUKT: durchläuft den angegeben Zellenbereich und summiert
alle Wahrheitswerte auf
- '--' wandelt einen Wahrheitswert in eine Zahl um (WAHR: 1 / FALSCH:
0)
- A1X1 wahr meine Annahme für Deinen Zellenbereich, passe das auf
A1:AJ1 für die erste Zeile an
- =26: Anzahl der farbigen Zellen in Deinem bereich. Sollten also
entsprechend Deiner Spaltenanzahl zwischen A-AJ angepasst werden (also
bei Dir 36).
- Nur wenn 26 (36) Zellen in der Spalte eingefärbt sind, gibt es eine
1, ansonsten immer eine 0
--
Regards
Frank Kabel
Frankfurt, Germany
Josef Gietzen
Frank Kabel schrieb:
Vorab, es funzt.
> erste Frage:
> - färbst Du immer die ganze Zeile ein oder nur die Spalten A-AJ (im
> ersteren Fall funktioniert es natürlich nicht)
Die ganze Zeile war eingefärbt, weil es einfacher war durch Klick auf
Zeilennummer diese zu markieren.
> Zur Erläuterung der Formel:
> - SUMMENPRODUKT: durchläuft den angegeben Zellenbereich und summiert
> alle Wahrheitswerte auf
> - '--' wandelt einen Wahrheitswert in eine Zahl um (WAHR: 1 / FALSCH:
> 0)
> - A1X1 wahr meine Annahme für Deinen Zellenbereich, passe das auf
> A1:AJ1 für die erste Zeile an
> - =26: Anzahl der farbigen Zellen in Deinem bereich. Sollten also
> entsprechend Deiner Spaltenanzahl zwischen A-AJ angepasst werden (also
> bei Dir 36).
> - Nur wenn 26 (36) Zellen in der Spalte eingefärbt sind, gibt es eine
> 1, ansonsten immer eine 0
Allerdings kann ich die Spalte A (Hilfsspalte) nicht miteinbeziehen, da
ich dann eine Fehlermeldung bekomme wegen Zirkelbezug. Außerdem muß ich
die Formel nach der Einfärbung dorthin kopieren. Wenn sie vorher schon
in der Hilfsspalte steht, ändert sich der Wert nicht.
Ich hatte es vorher schon versucht (bei Färbung der ganzen Zeile) und
das SUMMENPRODUKT auf 254 gesetzt aber es funktionierte nicht
Vielen Dank, Josef
Frank Kabel
klasse, das es funktioniert :-)
--
Regards
Frank Kabel
Frankfurt, Germany
Josef Gietzen
Frank Kabel schrieb:
Vorab, es funzt.
Aber nur auf dem Notebook unter Office2002
Auf dem Hauptrechner mit Office2000 bekomme ich immer eine
Fehlermeldung, dass die Makro in diesem Projekt deaktiviert
sind. Ich solle sie aktivieren, aber ich weiß nicht wie und wo?
Der angebliche Menüpunkt unter EXTRAS-MAKRO-Sicherheit gibt es
bei mir nicht.
Josef
> erste Frage:
> - färbst Du immer die ganze Zeile ein oder nur die Spalten A-AJ (im
> ersteren Fall funktioniert es natürlich nicht)
Die ganze Zeile war eingefärbt, weil es einfacher war durch Klick auf
Zeilennummer diese zu markieren.
> Zur Erläuterung der Formel:
> - SUMMENPRODUKT: durchläuft den angegeben Zellenbereich und summiert
> alle Wahrheitswerte auf
> - '--' wandelt einen Wahrheitswert in eine Zahl um (WAHR: 1 / FALSCH:
> 0)
> - A1X1 wahr meine Annahme für Deinen Zellenbereich, passe das auf
> A1:AJ1 für die erste Zeile an
> - =26: Anzahl der farbigen Zellen in Deinem bereich. Sollten also
> entsprechend Deiner Spaltenanzahl zwischen A-AJ angepasst werden (also
> bei Dir 36).
> - Nur wenn 26 (36) Zellen in der Spalte eingefärbt sind, gibt es eine
> 1, ansonsten immer eine 0
Allerdings kann ich die Spalte A (Hilfsspalte) nicht miteinbeziehen, da
Frank Kabel
fragt Excel Dich beim Öffnen, ob Du die Makros aktivieren sollts?
--
Regards
Frank Kabel
Frankfurt, Germany
Josef Gietzen
Frank Kabel schrieb:
> fragt Excel Dich beim Öffnen, ob Du die Makros aktivieren sollts?
>
Nein, tut es nicht
Josef
Frank Kabel
habe leider kein Excel 2000 zur Hand. Welche Einträge hast Du denn im
Menu 'Extras - Makros'. da müsste eiegntlich auch etwas zur
Sicherheitseinstellung sein.
--
Regards
Frank Kabel
Frankfurt, Germany
Josef Gietzen
Frank Kabel schrieb:
> Hi Josef
> habe leider kein Excel 2000 zur Hand. Welche Einträge hast Du denn im
> Menu 'Extras - Makros'. da müsste eiegntlich auch etwas zur
> Sicherheitseinstellung sein.
Das ist das Problem.
Unter Excel habe ich die Punkte:
Makros
Aufzeichnen
Visual Basic-Editor
Unter Word aber:
Makros
Aufzeichnen
Sicherheit
Visual Basic-Editor
und auf dem Notebook werde ich bei Start von Excel gefragt,
ob ich Makros aktivieren oder deaktivieren soll.
Josef
Frank Kabel
nur eine Vermutung: Ist Excel 2000 evtl. auf einem Firmenrechner, bei
dem Makros deaktiviert sind
--
Regards
Frank Kabel
Frankfurt, Germany
Josef Gietzen
Frank Kabel schrieb:
> Hi Josef
> nur eine Vermutung: Ist Excel 2000 evtl. auf einem Firmenrechner, bei
> dem Makros deaktiviert sind
>
Nein, Tower und Notebook sind zwar vernetzt, aber nur um hin- und wieder
beide Compi zu aktualisieren (für unterwegs 1x pro Woche).
Josef
Frank Kabel
kannst du denn in Deiner Excel 2000 Version überhaupt Makros eingeben
und Starten?
--
Regards
Frank Kabel
Frankfurt, Germany
Josef Gietzen
Frank Kabel schrieb:
> Hi Josef
> kannst du denn in Deiner Excel 2000 Version überhaupt Makros eingeben
> und Starten?
Ja, das kann ich. Ich kann Makros schreiben oder aufzeichnen, in
Personl.xls oder in der aktuellen Mappe speichern und anwenden.
Josef
Frank Kabel
funktioniert die Funktion denn, wenn Du sie auf Deinem Hauptrechner
manuell einfügst und dann unter Excel 2000 speicherst?
Ansonsten fällt mir nur noch ein, ob Du evtl. den Menueitrag entfernt
hast. Gehe mal in das Menu 'Extras - anpassen' und schaue, ob Du dort
den Befehl 'Sicherheit' findest
--
Regards
Frank Kabel
Frankfurt, Germany
Melanie Breden
Josef Gietzen schrieb:
> Hallo Frank,
>
> Frank Kabel schrieb:
>
>> Hi Josef
>> habe leider kein Excel 2000 zur Hand. Welche Einträge hast Du denn im
>> Menu 'Extras - Makros'. da müsste eiegntlich auch etwas zur
>> Sicherheitseinstellung sein.
> Das ist das Problem.
>
> Unter Excel habe ich die Punkte:
> Makros
> Aufzeichnen
> Visual Basic-Editor
>
> Unter Word aber:
> Makros
> Aufzeichnen
> Sicherheit
> Visual Basic-Editor
in Excel2000 findest du normalerweise den Dialog 'Sicherheitseinstellungen'
im Menü Extras | Makro | Sicherheit.
Falls er dort nicht ist, versuche ihn über den Anpassen-Dialog neu zuzuordnen:
Klicke im Menü Ansicht auf Symbolleisten | Anpassen.
Wähle den Register 'Befehle' und markiere die Kategorie 'Extras'.
Scrolle in der Liste der Befehle nach unten bis zum Eintrag 'Sicherheit'
Klicke diesen mit der linken Maustaste an und ziehe ihn auf das Menü 'Extras'
der Menüleiste und weiter zum Menü 'Makro' und lasse die Maustaste los,
wenn der Cursor sich unter dem Eintrag 'Aufzeichnen' befindet.
Schließe den Anpassen-Dialog wieder.
Jetzt solltest du die Sicherheitseinstellungen ändern können.
--
Mit freundlichen Grüssen
Melanie Breden
- Microsoft MVP für Excel -
http://excel.codebooks.de (Das Excel-VBA Codebook)
#Excel-Auftragsprogrammierung#
Josef Gietzen
Frank Kabel schrieb:
> Ansonsten fällt mir nur noch ein, ob Du evtl. den Menueitrag entfernt
> hast. Gehe mal in das Menu 'Extras - anpassen' und schaue, ob Du dort
> den Befehl 'Sicherheit' findest
Das war des Pudels Kern!
Es ist mir nicht bewußt, das ich den Menüpunkt mal gelöscht hätte, aber
jetzt ist er da!
Ich bedanke mich sehr für deinen geduldigen Beistand!
Josef
Frank Kabel
hauptsache es funktioniert jetzt!
--
Regards
Frank Kabel
Frankfurt, Germany