Leere Zellen zählen / Bestimmter Bereich

[RPP63]

Servus Ralf,

neue Version ist in Arbeit und wird bald veröffentlicht :)

LG Gerd

*freu*

[diving_excel]

Eine letzte Frage hab ich doch noch:

Ich hab die Excel Datei nun mit der Formel versehen und soweit klappt alles gut. Was ist aber wenn ich jetzt ein zusätzliches Kriterium einführen möchte?

Also zb wenn ich alle gefüllten Date Zellen bis zum Stichtags-Datum zählen möchte, die außerdem noch ein Kriterium erfüllen?

Also:

• Gefüllte Date Zellen (mit Verspätung) bis Stichtag: Hier mein ich jede "Date" Zelle, die bis zum Stichtag nicht leer ist (gelb markiert) --> Also das wobei ihr mir bisher helfen konntet. Jetzt möchte ich eine Zusätzliche Unterscheidung. Wenn in der Rot markierten Zelle des Jeweiligen Teils der Wert positiv ist --> Dann zählt das Datum als Verspätet. Im Vorliegenden Beispiel müssten das 4 sein.
  

• Gefüllte "Date" Zelle (ohne Verspätung) bis Stichtag:  Hier mein ich jede "Date" Zelle, die bis zum Stichtag nicht leer ist (gelb markiert) --> Also das wobei ihr mir bisher helfen konntet. Jetzt noch die zusätzliche Unterscheidung: Wenn in der Rot markierten Zelle des Jeweiligen Teils der Wert negativ, null oder "A" ist --> Dann zählt das Datum als Pünktlich. Im Vorliegenden Beispiel müssten das 7 sein.
  

Ich denke wenn man die Angehängte Excel Datei anschaut versteht man was ich meine.

Danke!

[neopa]

Hallo,


dazu muss bei meine bisherige Formellösung nur entsprechend erweitert werden. Formeln könnten zwar noch bzgl. ihrer Länge zu kürzen sein aber so dürften sie einfacher nachvollziehbar sein.

	A	B	C
8	ID	20.08.2017	20.08.2017
9	123	4	7

Formeln der Tabelle

Zelle Formel B8 =B1 C8 =B1 B9 =SUMME(INDEX((N10:DK10>0)*ISTZAHL(N10:DK10)*(N$9:DK$9="EE")*(SPALTE(I10:DF10)<AGGREGAT(14;6;SPALTE(I10:DF10)/(I10:DF10<=B$8)/(I10:DF10<>"");1)+1);)) C9 =SUMME(INDEX(((N10:DK10<=0)+(N10:DK10="A"))*(N10:DK10<>"")*(N$9:DK$9="EE")*(SPALTE(I10:DF10)<AGGREGAT(14;6;SPALTE(I10:DF10)/(I10:DF10<=B$8)/(I10:DF10<>"");1)+1);))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

[Bamberg]

Servus diving,

welche Lösung hast Du denn im Einsatz?

LG Gerd

[diving_excel]

Momentan die von Neopa, da sie für mich besser nachzuvollziehen ist. Muss mich erst mal ne Weile mit VBA beschäftigen :D

Seine Lösung funktioniert auch ganz gut, nur werden da nicht die einzelnen Kriterien Addiert [Betrifft speziell Gefüllte "Date" Zelle (ohne Verspätung) bis Stichtag], also alle Zellen die vor dem Stichtag liegen und zusätzlich eines der Kriterien negativ, null oder "A" haben, sondern "nur" der jeweils größte Wert ausgegeben.

Also wenn in einer Zeile 10x "A" steht und zusätzlich einmal eine Zahl <=0 dann gibt mir die Formel nur den Wert 10 zurück anstatt 11.  

[neopa]

Hallo diving_exce,

ich kann diesmal Deine Aussage nicht nachvollziehen.

Mein zuletzt eingestellte Formel ermittelte in C9 doch exakt die 7, die Du für Dein eingestelltes Beispiel als Ergebnisvorgabe gemacht hattest. Das war doch richtig, oder?
Da wurden in der Zeile vor dem Stichtag in der jeweilige Spalte "EE" jeweils ein Wert=0 und einer <0 und 5 x "A" addierend gezählt.

Und wenn ich in Deiner Datei 10 x "A" vor dem Stichtag plus einmal ein Wert kleiner 0 in den "EE"-Spalte eingebe,  wird bei mir auch als Ergebnis 11 ermittelt.

Ist denn Dein "Wert <=0" auch wirklich ein Zahlenwert?
Du kannst das einfach überprüfen mit =ISTZAHL([Zelladresse], dann muss dafür WAHR ermittelt werden. Anderenfalls ist es keine zahl sondern ein Textwert der nur so aussieht, als sei es ein negativer Wert  oder 0.

Evtl. stelle Deine Datei ein, wo die von Dir ermittelte Ergebnis(se) mit meiner Formel nicht stimmen sollen, dann kann ich prüfen warum das so ist.

[diving_excel]

Sorry für die späte Antwort, bei mir war privat bisschen was los.

Also ich merk grade dass ich mich falsch ausgedrückt habe.
Was ich gemeint hab ist, ob es möglich ist die Formel von dir auf bestimmte Bereiche zu beschränken, abhängig von bestimmten Kriterien (Typ X oder Typ Y).
Hab zum besseren Verständins nochmal Excel Datei von mir bearbeitet.

• In Zelle A10 und B10 steht jetzt die Bezeichnung für den Typ --> "X" und "Y"
  

• Für den jeweiligen Typ X oder Y sind nicht alle Abschnitte (das sind die Kategorien 1 bis 17) relevant.Es kann aber trotzdem sein, dass ein Datum im jeweiligen Bereich steht, obwohl dieser eigentlich nicht relevant ist.
  

• Ob der jeweilige Abschnitt relevant ist, sieht man an dem (X) oder (Y) hinter dem jeweiligen Teil-Abschnitt.
  

• Die Spalten C bis F (in denen die ausgezählten Ergebnisse stehen), sollen also abhängig vom Projekttyp (X oder Y) deine Formel auf die beschränkten Bereiche anwenden.
  

Wie kann ich jetzt die speziellen Bereiche zuweißen?
Hab versucht die Teilgebiete für X oder Y mit der BEREICHE Funktion zu defineiren und in deine Formel einzubauen,
aber bekomm dann nur die Meldung "Zu viele Argumente".

• Für X waren das die Spalten: I, O, U, AA, AG, AM, AS, BK, CI, CO
  

• Für Y waren das die Spalten: AY, BE, BQ, BW, CC, CU, DA
  

Verknüpft hab ich das ganze so (Beispielhaft für eine Zelle [C10]):

Code:

=WENN(A10="X";SUMME(INDEX(((BEREICHE(I10;O10;U10;AA10;AG10;AM10;AS10;BK10;CI10;CO10))="")*(I$9:DF$9="Date")*(SPALTE((BEREICHE(I10;O10;U10;AA10;AG10;AM10;AS10;BK10;CI10;CO10)))<AGGREGAT(14;6;SPALTE((BEREICHE(I10;O10;U10;AA10;AG10;AM10;AS10;BK10;CI10;CO10)))/((BEREICHE(I10;O10;U10;AA10;AG10;AM10;AS10;BK10;CI10;CO10))<=$C$9)/((BEREICHE(I10;O10;U10;AA10;AG10;AM10;AS10;BK10;CI10;CO10))<>"");1)+1);));WENN(A10="Y";SUMME(INDEX(((BEREICHE(AY10;BE10;BQ10;BW10;CC10;CU10;DA10))="")*(I$9:DF$9="Date")*(SPALTE((BEREICHE(AY10;BE10;BQ10;BW10;CC10;CU10;DA10)))<AGGREGAT(14;6;SPALTE((BEREICHE(AY10;BE10;BQ10;BW10;CC10;CU10;DA10)))/((BEREICHE(AY10;BE10;BQ10;BW10;CC10;CU10;DA10))<=C$9)/((BEREICHE(AY10;BE10;BQ10;BW10;CC10;CU10;DA10))<>"");1)+1);))))

Nicht besonders Elegant, und ich bin mir sicher das geht geschickter.

Was mache ich falsch? Danke mal wieder!

[neopa]

Hallo diving_excel,

Du hast völlig richtig vermutet, dass man die zusätzliche Bedingung weniger aufwendig in die Formel(n) einbinden kann, als von Dir angedacht.

Nachfolgende 4 Formeln unterscheiden sich bzgl. ihrer "Vorgänger"formel(n) durch den Einschub der zusätzlichen Prüfung auf Einhaltung des "Bereichstyps" : *ISTZAHL(FINDEN(A10;I$8:DF$8))  Die Formeln können auch wieder nach unten kopiert werden.

	A	B	C	D	E	F
9	Typ	ID	20.08.17	20.08.17	20.08.17	20.08.17
10	X	123	3	6	1	0
11	Y	456	1	4	1	1

Formeln der Tabelle

Zelle Formel C10 =SUMME(INDEX((I10:DF10="")*(I$9:DF$9="Date")*ISTZAHL(FINDEN(A10;I$8:DF$8))*(SPALTE(I10:DF10)<AGGREGAT(14;6;SPALTE(I10:DF10)/(I10:DF10<=C$9)/(I10:DF10<>"");1)+1);)) D10 =SUMME(INDEX((I10:DF10<>"")*(I$9:DF$9="Date")*ISTZAHL(FINDEN(A10;I$8:DF$8))*(SPALTE(I10:DF10)<AGGREGAT(14;6;SPALTE(I10:DF10)/(I10:DF10<=C$9)/(I10:DF10<>"");1)+1);)) E10 =SUMME(INDEX((I10:DF10<>"")*(I$9:DF$9="Date")*ISTZAHL(FINDEN(A10;I$8:DF$8))*(SPALTE(I10:DF10)>AGGREGAT(14;6;SPALTE(I10:DF10)/(I10:DF10<=C$9)/(I10:DF10<>"");1)+1);)) F10 =SUMME(INDEX((I10:DF10="")*(I$9:DF$9="Date")*ISTZAHL(FINDEN(A10;I$8:DF$8))*(SPALTE(I10:DF10)>AGGREGAT(14;6;SPALTE(I10:DF10)/(I10:DF10<=C$9)/(I10:DF10<>"");1)+1);))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

[diving_excel]

jep, sieht auch deutlich besser aus !

Gruß!