Excel Zellen zählen wenn keine Hintergrundfarbe?

 - (Computer, Excel, makro)

3 Antworten

Vom Fragesteller als hilfreich ausgezeichnet

Passt du die Farben per Hand an? Dann geht es nur über VBA. Wenn es jedoch nur die Wochenenden sind kannst die über WOCHENTAG() zunächst bedingt formatieren.

Die Arbeitstage bekommst du ganz einfach mit =NETTOARBEITSTAGE("1.1.2018";"31.01.2018")

Aber da sind dann nicht die Feiertage rausgerechnet (1.1., ...)? Die Feiertage hätte ich per Hand grau gemacht.

Und zudem füllt es sich nur für Januar ein, nicht für andere Monate.


0
@13R41N

Du solltest hier natürlich Bezüge und nicht die festen Werte verwenden.

Deswegen bietet dir die Funktion Nettoarbeitstage ja zusätzlich Liste an in der du die Freien Tage erfassen kannst.

=NETTOARBEITSTAGE("1.1.2018";"31.01.2018";{"1.1.2018";"2.1.2018"})

1
@augsburgchris


A1: 01.01.2018

A2: DATUM(JAHR(A1);MONAT(A1)+1;1)-1

C1:C10: Liste der Feiertage

=NETTOARBEITSTAGE(A1;A2;C1:C10)

dann brauchst du auf jedem Blatt nur noch das Datum in A1 ändern.

1
@augsburgchris

Gibt es dazu noch eine Möglichkeit, dass nur der Monat berechnet wird, der sich unter C7 und E7 befindet?

Allerdings hilft mir das auch nicht wirklich, da die Tabelle ja für jeden Monat genutzt werden sollte. Wenn ich nämlich C7 Und E7 ändere, ändern sich automatisch die Daten in A

0
@13R41N

Ja klar geht das. Steht "Januar" als Text oder als formatiertes Datum drin? Wenn du für jeden Monat das selbe Blatt verwendet wo speicherst du denn dann den Übertrag weg?

1
@augsburgchris

Das wird über mehrere Arbeitsblätter geregelt (Jan-Dez). Allerdings gibt es ja dann in 2019 andere Feiertage ((Weihnachten fällt anders, Ostern, ...)


In C7 kann man den Monat per Dropdownmenü als Text auswählen und E7 steht das Jahr.

Die beiden Variablen ändern dann A16:A46

0
@augsburgchris

Ich habe jetzt für Januar einfach folgende Formel benutzt: =NETTOARBEITSTAGE(A16;A46; A16)

Das würde ich jetzt für jeden Monat machen.

Die klappt auch prima für alle festen jährlichen Feiertage (Weihnachten, ...), aber leide rnicht für flexible wie z.B. Ostern oder Himmelfahrt

0
@13R41N

Ich hab dir gerade schnell eine Datei gebaut. Wenn du mir deine Email per PN schickst lass ich dir diese zukommen. Ist zwar abgespeckt aber die Zellbezüge und die bedingte Formatierung passen zu deiner Datei.

1
@13R41N

Auch den Ostersonntag kann man per Formel ausrechen

=DATUM(E7;3;28)+REST(24-REST(E7;19)*10,63;29)-REST(KÜRZEN(E7*5/4)+REST(24-REST(E7;19)*10,63;29)+1;7)

2
@augsburgchris

Super. Ich danke dir vielmals. Ich habe dir ne Freundschaftsanfrage mit Mail gestellt. Die Anfrage musst du nicht annhemen ;)

0
@augsburgchris

Hi Chris, ich habe ein Problem: Ich weiß nicht mehr, wie die Berechnung gemacht wurde (zu lange her) und habe noch 1 Feiertag, der nicht berechnet wurde (Himmelfahrt) und dann ist ja der Weltkindertag seit diesem Jahr der Feiertag (20.09.). Wie kann ich diese beiden Tage noch einfügen?

0
@13R41N

also wenn ich richtig bin, hattest du mir diese Regel erstellt:

=ODER(WOCHENTAG(DATWERT($B7&". "&C$6&" "&Kalender!$A$1);2)>5;ZÄHLENWENN(Kalender!$C:$C;DATWERT($B7&". "&C$6&" "&Kalender!$A$1))<>0)

Ist das dort, wo ich etwas ändern muss?

0
@13R41N

Nein, die habe ich nicht erstellt. Ich habe dir empfohlen mit NETTOARBEITSTAG zu arbeiten und die Feiertage in eine Liste zu schreiben.

0
@augsburgchris

Die hattest du mir erstellt - ich kann sie dir gerne auch noch einmal per Mail zukommen lassen inklusive unserer Unterhaltung von damals ;)

Siehe oben: "@13R41N

Ich hab dir gerade schnell eine Datei gebaut. Wenn du mir deine Email per PN schickst lass ich dir diese zukommen. Ist zwar abgespeckt aber die Zellbezüge und die bedingte Formatierung passen zu deiner Datei."

0
0
@13R41N

Hab das jetzt nicht ausprobiert, aber da das Colorindex verwendet, dürften nur die alten 56 xl-Farben damit erreichbar sein- eigentlich unnötig, denn .color in Verbindung mit RGB() ist auch nicht komplizierter (s. m. AW, Kommentar)

0

Makro

SubWeisseZählen()

Dim c, WT:

WT = 0

For each c in selection

If c.interior.color = xlnone then WT = WT + 1

Next c

Range("WeisseTage").value = WT

End sub

Definiere eine Zelle als Bereich namens WeisseTage, wähle den auszuwertenden Bereich (nur EINE Spalte breit!) und starte das µ: Das Ergebnis wird nach WeisseZellen geschrieben.

Am besten dort kopieren und woanders hinterlegen, denn für den nächsten Monat wird der Bereich neu beschrieben und Deine Berechnungen sollen ja wahrscheinlich bleiben (setze WeisseZellen ins Fadenkreuz zweier eingefrorener Titelzeilen und Spalten, dann bleibs immer sichtbar!)

könnte sein, dass anstatt xlnone xlautomatic reinmuss, ich probier das heut nicht mehr aus.

Spezifischer wirds, wenn Du Dein Grau analysierst mit weitere Farben - Benutzerdefiniert. Dein Grau sei zB RGB 128;128;128 (Grau50%), dann:

if c.interior.color <> RGB(128, 128, 128) then WT = WT + 1

Das erlaubt dir auch das Einfärben anderer Zellen (beliebig ausser Grau50%) und sogar das Überlagern Deiner grauen Zellen (also der mittelgrauen xl-Zellen ;) mit einem Muster (pattern), ohne dass das die Zählung stört.

mit der zweiten Methode umschiffst du auch die Unsicherheit bzgl. xlnone/xlautomatic

Ich habs heute getestet mit für mich überraschendem Ergebnis: die volle Füllung ist offensichtlich ein Muster und "keine Farbe" wird als weiß angesehen. Richtig muss das µ also lauten:

Sub WeisseZählen() 'getestet

Dim c, WT: WT = 0

For Each c In Selection

If c.Interior.Color <> RGB(128, 128, 128) Then WT = WT + 1 'ok, bevorzugt weil flexibel

'If c.Interior.Color = RGB(255, 255, 255) Then WT = WT + 1 'ok

'If c.Interior.Color = xlAutomatic Then WT = WT + 1 'geht so nicht

'If c.Interior.Pattern = xlNone Then WT = WT + 1 'ok

Debug.Print c.Address & " _ _ " & WT

Next c

Range("WeisseTage").Value = WT

End Sub

Ich hab da gleich noch einige Lehrbeispiele reingepackt:

Ein : erlaubt das Aneinanderhängen von zwei Befehlszeilen ( : WT = 0)

Eine Wenn-Abfrage enthält normalerweise noch ein Else und ggf noch ein Else If sowie ein End If. Entfällt Else kann sie in EINER Zeile und ohne End if durchgeführt werden

' (Hochkomma) macht aus einem Befehl einen Kommentar und erlaubt, kommentierende Texte an Befehlszeilen anzuhängen. Oben ist nur die erste Wenn-Bedingung aktiviert, die anderen "auskommentiert"

Das Debug erlaubt die Schritt-weise Nachvervolgung beim Zustandekommen des Ergebnisses oder Zwischenergebnisses. Dieses findet sich dann im "Direktfenster". Kein Einfluss auf den Ablauf des µ.

Ausserdem empfehle ich dringend, ganz oben im Modul

Option Explicit reinzusetzen (kann man auch als Vorgabe machen), das meldet sofort Schreibfehler bei Variablen.

optional hätte ich auch den Inhalt der Variablen festlegen können als c As Range und WT as Integer, kann sinnvoll sein, ich spare mit das meistens, VBA toleriert das.

Ach ja: Rechtsklick auf den Blattnamen: Code anzeigen öffnet den VBA-Editor. Menü Einfügen - Modul kreiert ein neues Modulblatt. Dahinein kopierst Du das obige Makro.


0
@Iamiam

Da war ich wohl zu spät dran, aber vllt nützt es trotzdem was?

übrigens: in der bevorzugten Form mit <> RGB() müsste das µ logischerweise anders heissen, zB NichtMittelgraueZählen(). Habs nochmal getestet mit einer gelben Zelle dazwischen: auch die wird als NICHT MITTELGRAU GEZÄHLT, es stimmt also alles.

Um einen unbekannten Hintergrund zu analysieren gehst du einfach im Icon Zellen färben auf weitere Farben, dort ins Blatt Benutzerdefiniert: der RGB-Status wird dort angezeigt.

und wenn Du eine Formel brauchst, dann nach dem Muster im Link von A-Chris

0
@Iamiam

Man muss bei diesem verästelten xl immer nochmals naheliegende Fehler ausschließen: 50%Grau heißt auch ein Muster, das als .pattern mit interior.pattern.patterncolor aber nicht von interior.Color erfasst wird. Wie gesagt, Muster werden nicht als grau oder sonstiges gezählt, wohl aber als NICHT WEIß (wäre die letzte, auskommentierte Variante)

0

Habe dir ein kompliment gesendet, da ich meine Homepage hier nicht angeben darf.

Auf der Homepage findest du eine Excel "Zeiterfassung" da ist alles bereits in Formeln gefasst. Die Feiertage werden auch automatisch je nach Bundesland das du auswählst angezeit und berechnet. Fahrzeit Reine arbeitszeit beim Kunden etc. schaus dir einfach einmal an, diese Excel kannst du komplett nutzen oder nur die Formeln davon. Je nachdem was dir lieber ist.

Was möchtest Du wissen?