Matrixformeln in angrenzende Zellen kopieren

Thomas_Kaehler · 12. November 2019 um 05:51

Hallo,

ich habe bei Excel 2010 das Problem, dass ich mit einer Matrixformel alle Beträge zu einer Auftragsnummer aus einer Tabelle aufsummieren und der Auftragsnummer in einer zweiten Tabelle zuordnen will.
Für eine einzelne Auftragsnummer habe ich das problemlos über folgende Matrixformel lösen können:

{=SUMME(WENN(bewertungszeilen!AP:AP=sendungen!A2;bewertungszeilen!Y:Y))}

Am Blatt „sendungen“ in Spalte A stehen die Sendungsnummer für das auszufüllende Blatt und im Blatt „bewertungszeilen“ steht in Spalte AP ebenfalls die Sendungsnummer und in Spalte Y der aufzusummierende Betrag.

Wenn ich nun aber diese Formel nach unten auf die zächste Zeile ziehen will bekomme ich einen Fehler.

Nun hättte ich schon versucht, den Teil „sendungen!A2“ durch „indirekt(„sendungen!A“&zeile())“ zu lösen, aber das verursacht auch einen Fehler.

Kann mir irgendwer einen Tipp geben, wie ich dieses Problem lösen könnte, da ich diese Matrixformel in ca. 10000 Zeilen benötige und somit ein Anpassen von Hand ausscheidet.

Ich bin mal für jeden Tip dankbar.

Gruß

Thomas

Thomas_Ramel · 12. November 2019 um 05:51

Grüezi Thomas

Für eine einzelne Auftragsnummer habe ich das problemlos über
folgende Matrixformel lösen können:

{=SUMME(WENN(bewertungszeilen!AP:AP=sendungen!A2;bewertungszeil
en!Y:Y))}

…aber warum denn eine Matrixformel - so geht das doch viel einfacher:

=SUMMEWENN(bewertungszeilen!$AP:blush:AP;sendungen!A2;bewertungszeilen!$Y:blush:Y)

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Reinhard_fb8cb7 · 12. November 2019 um 05:51

Hallo Thomas,

{=SUMME(WENN(bewertungszeilen!AP:AP=sendungen!A2;bewertungszeil
en!Y:Y))}

Wenn ich nun aber diese Formel nach unten auf die zächste
Zeile ziehen will bekomme ich einen Fehler.

welchen denn?

Nun hättte ich schon versucht, den Teil „sendungen!A2“ durch
„indirekt(„sendungen!A“&zeile())“ zu lösen, aber das
verursacht auch einen Fehler.

Welchen denn?

Kann mir irgendwer einen Tipp geben, wie ich dieses Problem
lösen könnte

Warum eigentlich eine matrixformel? Reicht das nicht:
=SUMMEWENN(bewertungszeilen!AP:AP;sendungen!A2;bewertungszeil

en!Y:Y)

bzw.
=SUMMEWENN(bewertungszeilen!AP:AP;A2;bewertungszeil

en!Y:Y)

wenn die Formel in sendungen steht.

Gruß
Reinhard

abgemeldet5 · 12. November 2019 um 05:51

Hallo Thomas,
was für Fehler sind das? Ich kann an der Formel keinen Fehler entdecken.
Die Formel lässt sich eigentlich auch kopieren.
Bekommst du auch einen Fehler wenn du „zu Fuß“ aus A2 ein A3 machst
und dann die Eingabe beendest?

Alternative Formel mit summenprodukt:
=SUMMENPRODUKT((bewertungszeilen!AP:AP=sendungen!A2)*(bewertungszeilen!Y:Y))

Kurze Bemerkung:
jede dieser Formeln durchsucht ganze Spalten,
also 1.048.576 Zeilen und summiert die Werte.
Wenn du diese Formel 10.000 mal kopieren möchtest,
könnte ich mir vorstellen das die Berechnung etwas Zeit benötigt
Ein Beitrag dazu

Gruß Holger

Thomas_Kaehler · 12. November 2019 um 05:51

Hallo Reinhard,

{=SUMME(WENN(bewertungszeilen!AP:AP=sendungen!A2;bewertungszeil
en!Y:Y))}

Wenn ich nun aber diese Formel nach unten auf die zächste
Zeile ziehen will bekomme ich einen Fehler.

welchen denn?

Teile des Arrays können nicht geändert werden.

Nun hättte ich schon versucht, den Teil „sendungen!A2“ durch
„indirekt(„sendungen!A“&zeile())“ zu lösen, aber das
verursacht auch einen Fehler.

Welchen denn?

=WERT?

Kann mir irgendwer einen Tipp geben, wie ich dieses Problem
lösen könnte

Warum eigentlich eine matrixformel? Reicht das nicht:
=SUMMEWENN(bewertungszeilen!AP:AP;sendungen!A2;bewertungszeilen!Y:Y)
bzw.
=SUMMEWENN(bewertungszeilen!AP:AP;A2;bewertungszeilen!Y:Y)
wenn die Formel in sendungen steht.

Eigentlich sollte das auch reichen. Jetzt funktioniert es auch mit dieser Formel. Heute Nachmittag hatte sich das System strikt geweigtert das zu nehmen.

Gruß

Thomas

Thomas_Kaehler · 12. November 2019 um 05:51

Hallo Thomas,

danke, das funktioniert jetzt. Ich hätte das ja am Nachmittag schon mal probiert, aber da hatte sich Excel geweigert, mit dieser Formel ein Ergebnis auszugeben.

Gruß

Thomas

Thomas_Kaehler · 12. November 2019 um 05:51

Hallo Holger,

ich hatte immer die Meldung

Teile des Arrays können nicht geändert werden

bekommen. Keine Ahnung, was ich da falsch gemacht hatte.

Gruß

Thomas

Reinhard_fb8cb7 · 12. November 2019 um 05:51

Ich kann an der Formel keinen Fehler entdecken.

hmmh, ich steh grad auf dem Schlauch, welchen Fehler habe ich denn bei der Umsetzung gemacht denn ich erhalte #ZAHL!?
http://www.uploadagent.de/show-181106-1324311768.html

Gruß
Reinhard

Reinhard_fb8cb7 · 12. November 2019 um 05:51

Wenn ich nun aber diese Formel nach unten auf die zächste
Zeile ziehen will bekomme ich einen Fehler.

welchen denn?

Teile des Arrays können nicht geändert werden.

Hallo Thomas,

Matrix/Arrayformeln mußt du anders als gewohnt kopieren.
Steht die Formel in C2, so markiere C2, Strg+c, dann gehe auf C3 bzw markiere C3:Cx und dann Strg+v.

Gruß
Reinhard

Thomas_Kaehler · 12. November 2019 um 05:51

Hallo Reinhard,

wieso stehst Du da jetzt auf dem Schlauch? Wenn ich Deine Datei herunterlade, dann erhalte ich ebenfalls dieses #Zahl? in den Matrixformeln, wenn ich dann bei der Datei auf aktivieren drücke, dann stehen da bei mir in allen Feldern die reichtigen Werte drin.
Keine Ahnung, was Excel da komisches macht.

Gruß

Thomas

Thomas_Kaehler · 12. November 2019 um 05:51

Hallo Reinhard,

ich habe gerade beim Absopeichern Deiner Musterdatei eine Fehlermeldung bekommen, dass dieses #ZAHL!? auch angezeigt werden kann, wenn Excel vor Version 2007 im Einstaz ist.

Gruß

Thomas

Reinhard_fb8cb7 · 12. November 2019 um 05:51

ich habe gerade beim Absopeichern Deiner Musterdatei eine
Fehlermeldung bekommen, dass dieses #ZAHL!? auch angezeigt
werden kann, wenn Excel vor Version 2007 im Einstaz ist.

Hallo Thomas,

interessant.
ja, die Mappe ist mit XL 2000 erstellt worden, da kommt wie gesagt #Zahl! bei den matrixformeln:

=SUMMENPRODUKT((bewertungszeilen!AP:AP=sendungen!A2)*(bewertungszeilen!Y:Y))
=SUMME(WENN(bewertungszeilen!AP:AP=sendungen!A2;bewertungszeilen!Y:Y))
=SUMMENPRODUKT((bewertungszeilen!AP:AP=sendungen!A3)*(bewertungszeilen!Y:Y))
=SUMME(WENN(bewertungszeilen!AP:AP=sendungen!A3;bewertungszeilen!Y:Y))

Aufgrund den Antworten hier habe ich die Mappe nun mit XL 2007 geöffnet. Beim/nach dem Öffnen sieht man noch ganz kurz die Fehlermeldung drinstehen dann zeigt XL 2007 dort die korrekten Ergebnisse an. Und das ohne daß ich da was aktivieren muß.

Ist Aktivieren sowas wie F9 früher? Du siehst, ich „arbeite“ selten mit XL 2007.

Ich kann aber in den Formeln nichts erkennen was XL 2000 nicht kennen sollte. Also steh ich weiter auf’m Schlauch

Egal, jetzt kommt gleich Großstadtrevier, erst danach suche ich mir
mütterliche Hilfe *gg*
Wenn ich keinen doofen eigenen Fehler gemacht habe muß die Problematik „bekannt“ sein.

Gruß
Reinhard

abgemeldet5 · 12. November 2019 um 05:51

Hallo zusammen,
Am Anfang steht dort Excel 2010

jetzt steht das die Mappe in Excel 2000 erstellt wurde.
Wenn ich mich recht erinnere erlaubt excel2000 noch keine kompletten Spalten in einer Matrixformel!

Gruß Holger

Reinhard_fb8cb7 · 12. November 2019 um 05:51

Matrixformeln 2000 2003 2007 2010
Hallo Holger,

Am Anfang steht dort Excel 2010

korrekt.

jetzt steht das die Mappe in Excel 2000 erstellt wurde.

Sorry, das kam durch meine Einmischung

Wenn ich mich recht erinnere erlaubt excel2000 noch keine
kompletten Spalten in einer Matrixformel!

Das ist/war der Schlauch, danke.
Wenn ich das abändere funktioniert es auch in XL 2000

Ich habe versucht den Betreff Archivtauglich zu machen damit Nachkommende die auch verblüfft sind daß eine matrix-Formel in altem Excel nicht klappt aber in neuem eine Chance haben die Lösung zu finden.
Naja, ganz zufrieden bin ich mit dem Betreff nicht.
Betreffverbesserungsbeiträge sind erwünscht, denn
schlechter Betreff = sinnlos die Beitragsfolge in’s Archiv zu stellen.

Selbstverständlich sind wir hier OT, streng bezogen auf die Anfangsfrage.
Aber Mitlesende/Archivaufsuchende die das gleiche Problem wie der Anfrager haben aber kein Excel nach XL 2003 haben hätten ohne meine OT-Einmischung von den genannten matrixlösungen rein gar nix gehabt.
Jetzt wissen sie daß sie nur AP:AP in AP1:AP1000 ändern müssen schon klappts.

Insofern sehe ich keinerlei Grund für die Sperrung des Beitrages von Thomas und bitte höflichst um Aufhebung der Sperrung.

Gruß
Reinhard

Thomas_Ramel · 12. November 2019 um 05:52

Grüezi zusammen

Wenn ich mich recht erinnere erlaubt excel2000 noch keine
kompletten Spalten in einer Matrixformel!

Ja, das ist korrekt - ganze Spalten sind erst ab xl2007 möglich.

…aber all das kann ja ganz ohne Matrixformeln mit SUMMEWENN() wunderbar gemacht werden…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -