Excel Zellen zählen wenn keine Hintergrundfarbe?
Hallo liebe Community,
ich habe ein Problem, das ich leider nicht lösen kann:
Ich bin gerade dabei, einen Stundenzettel für meine Arbeit zu entwerfen, der so viel wie möglich gleich ausrechnet.
Er soll auch automatisch die Arbeitstage ausrechen. Allerdings brauche ich dazu die Formel, dass er nur die Zellen mit Datum zählt, die weiß und nicht grau hinterlegt sind (siehe Foto).
Kann mir jemand helfen? Ich bin fast am Verzweifeln :/
3 Antworten
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")
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"})
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.
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
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?
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
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
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.
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)
Super. Ich danke dir vielmals. Ich habe dir ne Freundschaftsanfrage mit Mail gestellt. Die Anfrage musst du nicht annhemen ;)
Ausgehend von Ostersonntag lassen sich nahezu alle beweglichen Feiertage berechnen. So erstellt man dann eine Liste aller Feiertage, die sich für jedes Jahr automatisch anpasst. Sehr gut erklärt wird das auf http://www.excelformeln.de/formeln.html?welcher=3
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?
Nein, die habe ich nicht erstellt. Ich habe dir empfohlen mit NETTOARBEITSTAG zu arbeiten und die Feiertage in eine Liste zu schreiben.
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."
Bei VBA habe ich nur das gefunden: https://www.extendoffice.com/documents/excel/german-excel/2927-excel-count-sum-cells-by-color.html
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.
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
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)
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.
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.