Anzahl Zellen mit "0" innerhalb eines dynamischen Bereiches
#1
Hallo zusammen,

ich baue gerade an einer kleine Arbeitszeitübersicht von Personal für ein Projekt.
Hierbei werden Werte aus einem Schichtplan übernommen. Nun möchte ich wissen, wie viele Tage die Kolleg*innen innerhalb ihres Einsatzzeitraumes frei haben.

In Spalte D hätte ich das gern hinterlegt. So wie ich es jetzt habe, zählt er leider den gesamten Zeitraum - nicht den Einsatzzeitraum.

Max Mustermann hat aber seinen 1. Tag am 25.08. und den letzten Tag am 27.08. - hier möchte ich nur den Einsatzzeitraum betrachtet haben, also hätte er 0 freie Tage.
Ute Musterfrau soll 3 freie Tage in D6 angezeigt bekommen. Sie fängt am 24.08. an und hört am 09.09. auf.
Bernd Schmidt hätte entsprechend 2 Tage frei, bei einem Einsatz vom 30.08.-05.09.

Ich hoffe das ist verständlich?

Kann mir hier jemand mit einer Formel helfen?

Danke! Daniel


Angehängte Dateien
.xlsx   Test_freie Tage berechnen.xlsx (Größe: 12,09 KB / Downloads: 10)
Antworten Top
#2
Hallo Daniel ,

zB. so:

ZÄHLENWENNS(F7:W7;0;$F$3:$W$3;">="&E7)
helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.





Antworten Top
#3
Hallo Daniel,

und wo finde ich den letzten Einsatztag?

@helmut,

guter Ansatz, aber falsch.
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#4
@BoskoBiati: das müsste leider berechnet werden - sofern das möglich ist. Im Prinzip der letzte Tag in dem ein Wert drin steht.
Den ersten Tag in habe ich auch durch eine Formel anzeigen lassen - habe ich nur in dem Beispiel nicht drin gehabt: 
=INDEX(F$3:W$3;VERGLEICH(WAHR;F5:W5>0;0))
Vielleicht hast du ne Idee, wie der letzte Tag auszurechnen ist?
Antworten Top
#5
Hi,

eine Möglichkeit:

Code:
=LET(xa;F5:W5;xb;AGGREGAT(15;6;SPALTE(xa)/(xa>0);1)-5;xc;AGGREGAT(14;6;SPALTE(xa)/(xa>0);1)-5;ZÄHLENWENN(INDEX(xa;xb):INDEX(xa;xc);0))
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
[-] Folgende(r) 1 Nutzer sagt Danke an BoskoBiati für diesen Beitrag:
  • Daniel242
Antworten Top
#6
Wahnsinn - da denkt man immer, es kann doch nicht so kompliziert sein und dann sieht der Code aus, als könnte man damit Raketen zum Mars fliegen.
An Aggregat hatte ich auch schon gedacht und kam nicht weiter.
Die Formel passt perfekt - VIELEN DANK! Du hast mir sehr geholfen!
Antworten Top
#7
Hi,

die LET-Konstruktion macht die Formel im konkreten Beispiel nur länger.
Zudem ist die Nachvollziehbarkeit nicht gegeben, da man Teile der Formel nicht mit F9 auswerten kann.

Ohne LET - zudem ein wenig angepasst ohne Subtraktion von 5:

=ZÄHLENWENN(INDEX(F5:W5;AGGREGAT(15;6;SPALTE(A:R)/(F5:W5>0);1)):INDEX(F5:W5;AGGREGAT(14;6;SPALTE(A:R)/(F5:W5>0);1));0)

AGGREGAT(15;6;SPALTE(A:R)/(F5:W5>0);1)
findet die erste Spalte im Bereich F5:W5, in der der Wert größer Null ist (15 als erster Parameter = KKLEINSTE)

AGGREGAT(14;6;SPALTE(A:R)/(F5:W5>0);1)
findet die letzte Spalte im Bereich F5:W5, in der der Wert größer Null ist (14 als erster Parameter = KGRÖSSTE)

Beide Werte werden jeweils an INDEX übergeben - und aus beiden INDEX ein zusammenhängender Bereich erstellt, der mittels ZÄHLENWENN auf Null geprüft wird.

Das nur für Dich zur besseren Nachvollziehbarkeit.
Antworten Top
#8
Die Formel ist zwar 5 Zeichen länger als die von Boris, aber es geht auch mit:

Code:
=ZÄHLENWENNS(
F5:W5;"="&0;
$F$3:$W$3;">="&MINWENNS($F$3:$W$3;$F5:$W5;">"&0);
$F$3:$W$3;"<="&MAXWENNS($F$3:$W$3;$F5:$W5;">"&0))
Antworten Top
#9
Hi,

Die 5Zeichen gehen auch noch weg:

Code:
=ZÄHLENWENNS( F5:W5;0;$F$3:$W$3;">="&MINWENNS($F$3:$W$3;$F5:$W5;">0");$F$3:$W$3;"<="&MAXWENNS($F$3:$W$3;$F5:$W5;">0"))
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#10
Ich hatte mich bei einer ganz einfachen Aufgabe verrechnetz, denn die Formel war 8 anstatt 5 Zeichen länger.

Aber noch etwas weiter optimiert, ist diese nun mit einer Länge von 112 noch 6 Zeichen kürzer.

Code:
=ZÄHLENWENNS(
F5:W5;0;
F$3:W$3;">="&MINWENNS(F$3:W$3;$F5:$W5;">0");
F$3:W$3;"<="&MAXWENNS(F$3:W$3;$F5:$W5;">0"))
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste