Umständliche Array-Formel vereinfachen

[Werner Meier]

Hallo,
mit dieser etwas umständlichen Formle lasse ich in einer Personlatabelle
(Excel 2002) mir die freien Tage aufsummieren. Gibt es eine elegantere
Formel dafür?

=SUMME(WENN(I18:I65="u";1;0))+SUMME(WENN(I22:I65="z";1;0))+SUMME(WENN(I22:I6
5="s";1;0))+SUMME(WENN(I22:I65="ru";1;0))+SUMME(WENN(I22:I65="sf";1;0))+SUMM
E(WENN(I22:I65="ku";1;0))


Vielen Dank für Eure Hilfe! Türmer

[Frank Kabel]

Hi Werner
ich nehme mal an, dass auch Dein erster Bereich I22:I64 heßen muss (und
nicht I18:I65). Wenn ja versuche mal (keine Matrix Formel!)
=SUMMENPRODUKT(--(I18:I65={"u"."z"."s"."ru"."sf"."ku"}))

--
Regards
Frank Kabel
Frankfurt, Germany

Post by Werner Meier
Hallo,
mit dieser etwas umständlichen Formle lasse ich in einer

Personlatabelle

Post by Werner Meier
(Excel 2002) mir die freien Tage aufsummieren. Gibt es eine

elegantere

Post by Werner Meier
Formel dafür?

=SUMME(WENN(I18:I65="u";1;0))+SUMME(WENN(I22:I65="z";1;0))+SUMME(WENN(I
22:I6
5="s";1;0))+SUMME(WENN(I22:I65="ru";1;0))+SUMME(WENN(I22:I65="sf";1;0))
+SUMM

Post by Werner Meier
E(WENN(I22:I65="ku";1;0))
Vielen Dank für Eure Hilfe! Türmer

[BorisX]

Hi Frank,

Post by Frank Kabel
=SUMMENPRODUKT(--(I18:I65={"u"."z"."s"."ru"."sf"."ku"}))

Aus Performancegründen würde ich das jedoch in die
Funktion ZÄHLENWENN einbetten:

=SUMME(ZÄHLENWENN(I:I;{"u"."z"."s"."ru"."sf"."ku"}))

Ist im Ergebnis aber natürlich identisch.

[Frank Kabel]

Hi Boris

"BorisX" <***@discussions.microsoft.com> schrieb im Newsbeitrag news:2ec8b01c46c25$a9f67e70$***@phx.gbl...
Hi Frank,

Post by Frank Kabel
=SUMMENPRODUKT(--(I18:I65={"u"."z"."s"."ru"."sf"."ku"}))

Aus Performancegründen würde ich das jedoch in die
Funktion ZÄHLENWENN einbetten:

=SUMME(ZÄHLENWENN(I:I;{"u"."z"."s"."ru"."sf"."ku"}))

Ist im Ergebnis aber natürlich identisch.


Habe mal getestet, wie groß der Performanceunterschied ist :-)
Bei 100*50000 Zellen, die 50 mal ausgewertet wurden, lag auf meinem
System der Unterschied unterhalb der Messtoleranz (allerdings mit
Vorteilen für Deine Lösung). Also im Real-life nicht bemerkbar.

Deine Formel hat aber den entscheidenden Vorteil auch mit Bereichen wie
I1:Z100 zurechtzukommen!

Frank

[Eberhard Funke]

Hallo Frank

Post by Frank Kabel
Hi Werner
ich nehme mal an, dass auch Dein erster Bereich I22:I64 heßen muss (und
nicht I18:I65). Wenn ja versuche mal (keine Matrix Formel!)
=SUMMENPRODUKT(--(I18:I65={"u"."z"."s"."ru"."sf"."ku"}))

im Text oben hast Du's richtig, aber bei der Formel ...
=SUMMENPRODUKT(1*(I12:I65={"u"."z"."s"."ru"."sf"."ku"}))

Hat es eigentlich einen besonderen Grund, weshalb Du so ein Anhänger
des Minuszeiches bist (ist mir schon häufiger bei Deinen Formeln
augefallen), so daß Du es hier gleich zweimal (MinusMinus) verwendest?
Ich habe es oben durch >>1*<< ersetzt, das scheint mir doch etwas
weniger kompliziert zu sein.

--
Gruß Eberhard
XL 2000

[Frank Kabel]

Post by Eberhard Funke
Hallo Frank

Post by Frank Kabel
Hi Werner
ich nehme mal an, dass auch Dein erster Bereich I22:I64 heßen muss (und
nicht I18:I65). Wenn ja versuche mal (keine Matrix Formel!)
=SUMMENPRODUKT(--(I18:I65={"u"."z"."s"."ru"."sf"."ku"}))

im Text oben hast Du's richtig, aber bei der Formel ...
=SUMMENPRODUKT(1*(I12:I65={"u"."z"."s"."ru"."sf"."ku"}))
Hat es eigentlich einen besonderen Grund, weshalb Du so ein Anhänger
des Minuszeiches bist (ist mir schon häufiger bei Deinen Formeln
augefallen), so daß Du es hier gleich zweimal (MinusMinus)

verwendest?

Post by Eberhard Funke
Ich habe es oben durch >>1*<< ersetzt, das scheint mir doch etwas
weniger kompliziert zu sein.

Hi Eberhard
ja es hat einen Grund :-)

- das doppelte Minus ist (angeblich) etwas schneller als die anderen
mathematischen Operationen (wie '1*', '0+' oder '^1') Allerdings wird
das in der Realität so gut wie nie bemerkbar sein
- Der für mich entscheidende Grund ist aber Excels Operatoren
Rangfolge. '--' wird vor den anderen Operationen ausgewertet (und kann
deshalb auch weniger durch fehlenden Klammern durcheinandergebracht
werden)
- Wenn mehrere Bedingungen in Summenprodukt getestets werden wie z.B.:
SUMMENPRODUKT(--(Bereich1=Bedingung1),--(Bereich2=Bedingung2))
bevorzuge ich diese Syntax gegenüber
SUMMENPRODUKT((Bereich1=Bedingung1)*(Bereich2=Bedingung2))
das diese für mich näher an der Originalsyntax von Summenprodukt ist

Es ist aber im wesentlichen Geschmackssache - und ich persönlich finde
'--' unkomplizierter :-)

Gruß + schönes Wochenende
Frank

[Eberhard Funke]

Daanke Frank
[.....]

Post by Eberhard Funke

Post by Eberhard Funke

..... versuche mal (keine Matrix Formel!)
=SUMMENPRODUKT(--(I18:I65={"u"."z"."s"."ru"."sf"."ku"}))

im Text oben hast Du's richtig, aber bei der Formel ...
=SUMMENPRODUKT(1*(I12:I65={"u"."z"."s"."ru"."sf"."ku"}))
Hat es eigentlich einen besonderen Grund, weshalb Du so ein Anhänger
des Minuszeiches bist (ist mir schon häufiger bei Deinen Formeln
augefallen), so daß Du es hier gleich zweimal (MinusMinus)

verwendest?

Post by Eberhard Funke
Ich habe es oben durch >>1*<< ersetzt, das scheint mir doch etwas
weniger kompliziert zu sein.

Hi Eberhard
ja es hat einen Grund :-)
- das doppelte Minus ist (angeblich) etwas schneller als die anderen
mathematischen Operationen (wie '1*', '0+' oder '^1') Allerdings wird
das in der Realität so gut wie nie bemerkbar sein
- Der für mich entscheidende Grund ist aber Excels Operatoren
Rangfolge. '--' wird vor den anderen Operationen ausgewertet (und kann
deshalb auch weniger durch fehlenden Klammern durcheinandergebracht
werden)

Das leuchtet mir nicht so ganz ein. Fehlende Klammern sollte doch
immer zu einem falschen Ergebnis oder Fehler führen?

Post by Eberhard Funke
SUMMENPRODUKT(--(Bereich1=Bedingung1),--(Bereich2=Bedingung2))
bevorzuge ich diese Syntax gegenüber
SUMMENPRODUKT((Bereich1=Bedingung1)*(Bereich2=Bedingung2))
das diese für mich näher an der Originalsyntax von Summenprodukt ist
Es ist aber im wesentlichen Geschmackssache [........]

woraus ich schließe, daß Du von Grund aus "negativ" eingestellt
bist:-))

Post by Eberhard Funke
Gruß + schönes Wochenende
Frank

Ich habe mit Deiner Formel den ganze Vormittag rumgewerkelt, und so
allmählich dämmert mir, was es damit auf sich hat:

{"u"."z"."s"."ru"."sf"."ku"} ist ein Zeilenvektor (im Unterschied zum
Spaltenvektor [Semikolon] mit Punkten als Trennzeichen) . Mit den
vorstehenden Texten in beispielsweise A1:F1 könnte man Deine Formel
auch schreiben:
=SUMMENPRODUKT(--(I18:I65=A1F1))

Jetzt habe ich sozusagen "Array-Blut" geleckt, ich möchte da etwas
tiefer einsteigen, aber möglichst nicht mit zeitaufwe(ä)ndigem
Trial&Error. Git es dazu eine einschlägigere Ausführung, etwas, das
über z. B. Pearson hinausgeht?

Auch Dir ein schönes Restwochenende. Der Himmel hier über HD trifft
Vorbeitungen für das abendliche Gewitter; die Luft ist so drückend,
daß unsere Katze schon seit Stunden vor meinem Bildschierm liegt, ohne
sich zu rühren. Vielleicht grübelt sie ebenfalls über Arrays nach.
--
Gruß Eberhard
XL 2000

[Frank Kabel]

Hi Eberhard

Post by Eberhard Funke

Post by Frank Kabel
Hi Eberhard
ja es hat einen Grund :-)
- das doppelte Minus ist (angeblich) etwas schneller als die anderen
mathematischen Operationen (wie '1*', '0+' oder '^1') Allerdings wird
das in der Realität so gut wie nie bemerkbar sein
- Der für mich entscheidende Grund ist aber Excels Operatoren
Rangfolge. '--' wird vor den anderen Operationen ausgewertet (und kann
deshalb auch weniger durch fehlenden Klammern durcheinandergebracht
werden)

Das leuchtet mir nicht so ganz ein. Fehlende Klammern sollte doch
immer zu einem falschen Ergebnis oder Fehler führen?

Sorry, nicht ganz korrekt beschrieben. Mir ging es hierbei um die
Auswertung der Operanden, wenn man keine Klammern setzt. Nimm mal
folgende Formel
=1+2*3^--5

Jetzt kommt es halt auf die Auswertungsreihenfolge an. Folgende
Reihenfolge dazu aus der Excel Hilfe (unter Berechnungsoperatoren):
- (im Sinne einer Negation)
%
^
* und /
+ und -
&
= < > <= >= <> (Vergleichsoperatoren)

Wie man daran sieht, wird die Negation vor den anderen Operatoren
ausgewertet (wenn halt nicht Klammern entsprechend anderes gesetzt
werden). Wenn ich also das '--' verwende kann ich ziemlich sicher sein,
dass dieses immer zuerst ausgwertet wird. Aber wie gesagt in diesem
Zusammenhang gehen auch die anderen Operatoren (ist halt
Gewohnheitssache bei mir)

Post by Eberhard Funke

Post by Frank Kabel
Es ist aber im wesentlichen Geschmackssache [........]

woraus ich schließe, daß Du von Grund aus "negativ" eingestellt
bist:-))

:-)

Post by Eberhard Funke
Ich habe mit Deiner Formel den ganze Vormittag rumgewerkelt, und so
{"u"."z"."s"."ru"."sf"."ku"} ist ein Zeilenvektor (im Unterschied zum
Spaltenvektor [Semikolon] mit Punkten als Trennzeichen) . Mit den
vorstehenden Texten in beispielsweise A1:F1 könnte man Deine Formel
=SUMMENPRODUKT(--(I18:I65=A1F1))

leicht korrigiert :-)
=SUMMENPRODUKT(--(I18:I65=A1:F1))

Komplizierter wird es, wenn die Werte in einer Spalte, also z.B.: F1:F6
stehen. Typische lösungsmöglichkeiten dafür wären:
=SUMMENPRODUKT(--(I18:I65=MTRANS(F1:F6)))
WICHTIG: Diese Formel muss als Matrix Formel mit CTRL+SHIFT+ENTER
eingegeben werden. Eine bessere Alternative dazu wäre (weil nicht als
Matrixformel einzugeben):
=SUMMENPRODUKT(--(ISTZAHL(VERGLEICH(I18:I65;F1:F6;0))))

Post by Eberhard Funke
Jetzt habe ich sozusagen "Array-Blut" geleckt, ich möchte da etwas
tiefer einsteigen, aber möglichst nicht mit zeitaufwe(ä)ndigem
Trial&Error. Git es dazu eine einschlägigere Ausführung, etwas, das
über z. B. Pearson hinausgeht?

Zumindestens für SUMMENPRODUKT schaue Dir mal folgende Seite an:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Erläuterungen + Beispiele (z.B.: wie man NETTOARBEITSTAGE durch
SUMMENPRODUKT ersetzt und auch einfach abweichende arbeitsfreie Tage
definieren kann)

Post by Eberhard Funke
Auch Dir ein schönes Restwochenende. Der Himmel hier über HD trifft
Vorbeitungen für das abendliche Gewitter; die Luft ist so drückend,
daß unsere Katze schon seit Stunden vor meinem Bildschierm liegt, ohne
sich zu rühren. Vielleicht grübelt sie ebenfalls über Arrays nach.

also mein Kater liegt nur lethargisch auf dem Sessel und ignoriert mich
völlig :-)

Frank

[Eberhard Funke]

Danke Frank,

"Frank Kabel" <***@freenet.de> schrieb:

[.....]

Post by Frank Kabel

Post by Eberhard Funke

Post by Frank Kabel
- Der für mich entscheidende Grund ist aber Excels Operatoren
Rangfolge. '--' wird vor den anderen Operationen ausgewertet (und

kann

Post by Eberhard Funke

Post by Frank Kabel
deshalb auch weniger durch fehlenden Klammern durcheinandergebracht
werden)

Das leuchtet mir nicht so ganz ein. Fehlende Klammern sollte doch
immer zu einem falschen Ergebnis oder Fehler führen?

Sorry, nicht ganz korrekt beschrieben. Mir ging es hierbei um die
Auswertung der Operanden, wenn man keine Klammern setzt. Nimm mal
folgende Formel
=1+2*3^--5
Jetzt kommt es halt auf die Auswertungsreihenfolge an. Folgende
- (im Sinne einer Negation)
%
^
* und /
+ und -
&
= < > <= >= <> (Vergleichsoperatoren)
Wie man daran sieht, wird die Negation vor den anderen Operatoren
ausgewertet (wenn halt nicht Klammern entsprechend anderes gesetzt
werden). Wenn ich also das '--' verwende kann ich ziemlich sicher sein,
dass dieses immer zuerst ausgwertet wird. Aber wie gesagt in diesem
Zusammenhang gehen auch die anderen Operatoren (ist halt
Gewohnheitssache bei mir)

ok, verstanden.

Post by Frank Kabel

Post by Eberhard Funke
Ich habe mit Deiner Formel den ganze Vormittag rumgewerkelt, und so
{"u"."z"."s"."ru"."sf"."ku"} ist ein Zeilenvektor (im Unterschied zum
Spaltenvektor [Semikolon] mit Punkten als Trennzeichen) . Mit den
vorstehenden Texten in beispielsweise A1:F1 könnte man Deine Formel
=SUMMENPRODUKT(--(I18:I65=A1F1))

leicht korrigiert :-)
=SUMMENPRODUKT(--(I18:I65=A1:F1))

ja, ja :-((

Post by Frank Kabel
Komplizierter wird es, wenn die Werte in einer Spalte, also z.B.: F1:F6
=SUMMENPRODUKT(--(I18:I65=MTRANS(F1:F6)))
WICHTIG: Diese Formel muss als Matrix Formel mit CTRL+SHIFT+ENTER
eingegeben werden.

Seltsam:
Mit den Buchstaben in F1:F6 gibt
MTRANS(F1:F6) --> {"u"."z"."s"."ru"."sf"."ku"}, d. h. man sollte
meinen, =SUMMENPRODUKT(--(I18:I65=MTRANS(F1:F6))) sei identisch mit
der Formel 3 Zeilen weiter unten, aber sie funktioniert nur als
Matrixformel.
Andererseits
=SUMMENPRODUKT(--(I18:I65= {"u"."z"."s"."ru"."sf"."ku"}))
funktioniert auch ohne CTRL+SHIFT+ENTER

Hier stoße ich - vorläufig - an Verständnisgrenzen.

Post by Frank Kabel
Eine bessere Alternative dazu wäre (weil nicht als
=SUMMENPRODUKT(--(ISTZAHL(VERGLEICH(I18:I65;F1:F6;0))))

Auch hier Geschmacksache. Ich bin jetzt auf dem Array-Trip, mir
gefällt ...MTRANS... besser.

Post by Frank Kabel
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Tolle Seite!!
Bringt mich nur an dem Punkt, an dem ich jetzt angelangt bin, nicht
viel weiter :-((

Post by Frank Kabel
also mein Kater liegt nur lethargisch auf dem Sessel und ignoriert mich
völlig :-)

Streichle ihn mal von mir!

--
Gruß Eberhard
XL 2000