Summenprodukt soll mehrere Werte aus EINER Zelle übernehmen

[Nini]

Hallo, ich hab schon gesucht, aber für mein spezielles Problem finde ich keine Lösung. (Danke übrigens, die Beschreibung wie man über Google im Forum sucht hat immerhin die Sucherei schon mal etwas leichter gemacht, wenn auch leider ohne Ergebnis)

Ich habe folgende Formel:
=SUMMENPRODUKT(($A$49:$A$280={109;105})*$M$49:$M$280)

Diese funktioniert zwar, ABER die Werte in den geschweiften Klammern möchte ich nicht von Hand eintragen (Fehlerquelle und umfangreiche Tabelle), sondern aus der Zelle C6 auslesen. Schreibe ich {C6} oder {INDIREKT("C6")} bekomme ich den Fehler Err:512 (Soweit ich weiß heißt das wohl die Formel ist zu lang?)

Es funktioniert teilweise wenn ich die Werte aus Spalte C auf mehrere Spalten aufteile und mit + verbinde. Also in der Art: =SUMMENPRODUKT(($A$49:$A$280=C6)+($A$49:$A$280=D6)+($A$49:$A$280=E6)+...*$M$49:$M$280) Was jedoch leider nicht zuverlässig funktioniert (warum weiß ich nicht) und bei mehreren Werten eine ewig lange Formel wird und entsprechend viele zusätzliche Spalten bräuchte... Daher ist dies keine Lösung. Obwohl diese Version viel länger ist, erscheint hier kein Err:512, was ich auch nicht nachvollziehen kann.

Ich hoffe ich habe nix vergessen und alles verständlich darlegen können. Wäre ganz toll, wenn jemand da eine Lösung wüsste und mir helfen könnte. Übrigens: Wie und mit welchem Trennzeichen oder Format die Werte in C6 geschrieben kann beliebig angepasst werden, nur in einer Zelle sollen sie stehen.

[lorbass]

Diese funktioniert zwar

Ich hoffe, du weißt auch, wie sie funktioniert, insbesondere was der Term $A$49:$A$280={109;105} anrichtet…

die Werte in den geschweiften Klammern möchte ich nicht von Hand eintragen (Fehlerquelle und umfangreiche Tabelle), sondern aus der Zelle C6 auslesen.

Wenn du an der Stelle zwei Werte brauchst, musst du eben auch zwei Zellen verwenden. Wenn du unbedingt zwei Werte in eine Zelle eingeben willst, musst du dir eine Funktion schreiben, die den Inhalt der einen Zelle aufdröselt und die Werte in Matrix-Form ausgibt.

Es funktioniert teilweise wenn ich die Werte aus Spalte C auf mehrere Spalten aufteile und mit + verbinde. Also in der Art:
=SUMMENPRODUKT(($A$49:$A$280=C6)+($A$49:$A$280=D6)+($A$49:$A$280=E6)+...*$M$49:$M$280)

Hier fängt es jetzt an, so richtig unübersichtlich zu werden. Nicht nur, dass du „die Spalte C“ in der Formel offensichtlich auf C6, D6, E6, … (also zeilenförmig) aufspaltest. Hier wird aus zwei Werten {109;105} auch noch ein „ewig langer“ Vektor zusammengezimmert.

Präzisiere bitte die genaue(!) Aufgabenstellung in einer anonymisierten / verfremdeten Beispieldatei und und stelle sie uns zur Verfügung. Als angemeldeter Benutzer kannst du diese mit deinem nächsten Beitrag unmittelbar hier im Forum hochladen. Die entsprechende Eingabemöglichkeit findest du auf dem Registerblatt Dateianhang hochladen unterhalb des Texteingabefensters.

Gruß
lorbass

[Nini]

Ich hoffe, du weißt auch, wie sie funktioniert, insbesondere was der Term $A$49:$A$280={109;105} anrichtet…

Ich weiß, dass das in dem Bereich $A$49:$A$280 alle Zellen mit dem Inhalt 109 oder 105 sucht
Aber ich weiß leider nicht mehr wie ich damals beim Erstellen auf die geschweiften Klammern gekommen bin. Kann gut sein, dass das Teil des Problems ist, aber ich finds einfach nicht mehr. Weder im Hirn noch die Quelle... Kam aus irgend nem Excel Buch glaube ich.

Wenn du an der Stelle zwei Werte brauchst, musst du eben auch zwei Zellen verwenden.

Genau das habe ich ja beschrieben, wie ich es -als Beispiel- mit 3 Werten in 3 Zellen durch zusätzliche Spalten versucht habe (C6,D6,E6) aber das findest du wie du selbst im nächsten Absatz schreibst (!) unübersichtlich... und ich auch, und suche genau deswegen eine andere Lösung. (Es sind nicht immer nur 2 Werte, die Anzahl der Werte variiert von Zeile zu Zeile)

Ich versuche es nochmal: Ich suche nur eine Möglichkeit in =SUMMENPRODUKT(($A$49:$A$280={109;105})*$M$49:$M$280) die Werte in der geschweiften Klammer, also 109;105 aus einer einzelnen Zelle zu übernehmen, damit sich die Formel anpasst, wenn die Werte geändert werden. (Platz für eine Zelle je Wert ist leider nicht gegeben.)

Bzw. falls es grundsätzlich nicht möglich ist, Teile einer Formel von woanders her zu holen, wär nett wenn mir einer sagt, dass ich gar nicht weiter rumprobieren und suchen brauche...

[lorbass]

Ich hoffe, du weißt auch, wie sie funktioniert, insbesondere was der Term $A$49:$A$280={109;105} anrichtet…

Ich weiß, dass das in dem Bereich $A$49:$A$280 alle Zellen mit dem Inhalt 109 oder 105 sucht

So was ähnliches habe ich befürchtet. Tatsächlich vergleicht der Term $A$49:$A$280={109;105} die Werte der beiden Vektoren $A$49:$A$280 und {109;105} miteinander, und zwar $A$49 mit dem Literal 109 und $A$50 mit 105. Fertig!

Das Ergebnis ist ein Vektor, mit den Werten WAHR oder FALSCH für die ersten beiden Stellen. Alle anderen Dimensionen haben bleiben unbestimmt (→  #WERT!). Auch die Wirksamkeit anschließende Multiplikation bleibt auf 2-dimensionale Vektoren beschränkt.

Ich wiederhole nochmal: Bitte beschreibe dein Vorhaben genau(!) in einer Beispieldatei. Füge auch die erwarteten Ergebnisse ein. Beschränke dich auf wenige Zeilen, die man „zu Fuß“ nachrechnen kann.

Für heute habe ich allerdings keine Zeit mehr.

Gruß
lorbass

[Nini]

Ist ja schon in Arbeit... Bitteschön. Aber was du da schreibst, mit dem Vergleich der Vektoren, kann ich überhaupt nicht nachvollziehen. So wie du das beschreibst, dürfte meine Formel doch gar nicht funktionieren??? Sie funktioniert aber und auch mit 3 oder mehr Werten in der geschweiften Klammer. Jetzt bin ich vollends verwirrt und verstehe nur noch Bahnhof...

[Hiker]

Hallo,

Sie funktioniert aber und auch mit 3 oder mehr Werten in der geschweiften Klammer.

wenn funktionieren meint, daß Sie keinen Fehler auswirft, hast Du wohl recht.
Bist Du aber auch sicher, daß Sie immer das richtige Ergebnis liefert?

Deine "Übersetzung" deutet darauf hin, daß Du zunächst herausfinden willst, welche Zellen von a49 bis A280
einem der Werte 109 ODER 105 ODER etc. entsprechen.
Zur Vereinfachung möchtest Du dann alle Suchwerte in eine Zelle packen - was normalerweise nicht geht.
Für Texte habe ich mal so etwas gebastelt: Alle mögliche Ergebnisse (bei mir Warenkürzel, bei dir _109_ und _105_
mit Anfangs- und Endemarkierungen) in eine Zeichenkette gepackt. In einer Hilfsspalte ergibt SUCHEN oder / FINDEN
dann sozusagen deine Filtermatrix zur Multiplikation mit M.
Bei Zahlen würde ich wahrscheinlich die Suchmatrix explizit in einer Hilfstabelle aufbauen:
Es hilft ungemein bei der Fehlersuche, wenn man tatsächlich sehen kann was gefiltert wird...


Wenn deine Formel tatsächlich richtig "rechnet" spräche aber nichts dagegen {109,105} durch einen Bezug auf einen
anderen Tabellenbereich zu ersetzen - nötigenfalls Tabelle2.A49:A280 - nur die Variante einen Vektor in eine Zelle zu schreiben ist
nicht vorgesehen.

[Nini]

Ja die Formel funktioniert UND wirft das richtige Ergebnis aus. Das habe ich mehrfach nachgezählt. Die rechnet wirklich richtig!
Ich verstehe leider überhaupt nicht was mit Vektor gemeint ist. Ich kenn das in den geschweiften Klammern nur als Array. (Bitte um Nachsicht. Ich hab leider nicht so ein Profiwissen wie die anderen hier, nur selbst zusammengelesenes...)

Wenn deine Formel tatsächlich richtig "rechnet" spräche aber nichts dagegen {109,105} durch einen Bezug auf einen
anderen Tabellenbereich zu ersetzen - nötigenfalls Tabelle2.A49:A280 - nur die Variante einen Vektor in eine Zelle zu schreiben ist
nicht vorgesehen.

Genau das möchte ich und genau das bekomm ich nicht hin. Wann immer ich versuche den Teil {109;105} durch einen Bezug auf eine Zelle zu ersetzen bekomme ich Fehlermeldungen. Hab verschiedenste Varianten durchprobiert. Aber das müsste doch eigentlich gehen? Ich kann doch sonst auch beliebige Teile einer Formel zum Beispiel mit Indirekt aus anderen Zellen holen. Verstehe nicht warum es hier nicht klappt. Vielleicht kann das mal jemand in der Beispieltabelle nachvollziehen und findet dabei heraus wie es geht?

[mikeleb]

Hallo,
ich finde es erstmal cool, dass diese Formel funktioniert (hätte ich so nicht erwartet).
Leider findet sich in der Hilfe die Aussage:

Eine eingebettete Matrix wird in geschwungenen Klammern '{' und '}' eingeschlossen. Elemente können entweder eine Zahl (einschließlich negativer Zahlen), eine logische Konstante (WAHR, FALSCH) oder eine Zeichenkette sein. Variablen sind nicht erlaubt.

Damit entfällt die Variante die Matrix {101.102} (hier eindimensional, also ein Vektor) in der Form irgendwie zu kontruieren. Also bleibt Hikers Variante.

[lorbass]

ch verstehe leider überhaupt nicht was mit Vektor gemeint ist.

Ein Vektor in diesem Kontext ist eine einzeilige oder einspaltige Matrix. Eine Matrix wiederum ist ein rechteckiger Zellbereich einer Calc-Tabelle. Synonym kann auch der Begriff Array verwendet werden.

Der Term {101.102}, den du in deiner Formel verwendest, wird als Inline-Matrix bezeichnet. Eine Inline-Matrix ist eine Matrix, deren Werte NICHT auf Zellen des Arbeitsblattes zurückgeführt werden, sondern als Konstanten in der Formel selbst stehen.

Nun hast du in deiner Beispieldatei mit dem Term {101.102} abweichend von deiner Ausgangsfrage einen Zeilenvektor verwendet, mit dem sich sinnvolle Werte berechnen lassen. Ob die fachlich OK sind, weiß ich nicht, musst du sagen. In der Ausgangsfrage stand der Term {109;105} für einen Spaltenvektor. Das sollte der entscheidende Schritt gewesen sein, um schon mal die richtigen Werte zu generieren. Die Inline-Matrix {101.102} kannst du nun problemlos ersetzen durch einen Zeilenvektor – z.B. E16:K16 – oder einen transponierten Spaltenvektor – z.B. MTRANS(T3:T9).

Bleibt die Aufgabe, die geschlossenen Darstellungen à la „101;102;105“ automatisch auszuwerten. Dazu empfehle ich, im Bereich Makros und allgemeine Programmierung nach einer Funktion zu fragen, die den Text aufdröselt und einen entsprechenden Zeilenvektor zurückgibt. Den Funktionsaufruf kannst du dann direkt in die Formel einarbeiten.

Gruß
lorbass

[Nini]

Danke für die Erklärungen, so langsam steig ich dahinter

Der Term {101.102}, den du in deiner Formel verwendest,

Ich hab die Werte in den geschweiften Klammern aber immer mit Semikolon getrennt nicht mit Punkt. Auch in der Beispieldatei. Also das mit dem Zeilenvektor hilft mir leider nicht. Ich brauche eine Lösung, wie ich {101;102} in der Formel aus der Zelle C6 in die Formel übernehmen kann, so dass sich die Formel anpasst, wenn der Wert in C6 geändert wird. (Wie die Werte dabei in C6 geschrieben werden ist egal, ob mit Klammern oder ohne, oder welches Trennzeichen, es soll nur funktionieren.)

[lorbass]

Ich brauche eine Lösung, wie ich {101;102} in der Formel aus der Zelle C6 in die Formel übernehmen kann

Dazu empfehle ich, im Bereich Makros und allgemeine Programmierung nach einer Funktion zu fragen, die den Text aufdröselt und einen entsprechenden Zeilenvektor zurückgibt. Den Funktionsaufruf kannst du dann direkt in die Formel einarbeiten.

Gruß
lorbass

[ChristianAC]

Trau' Dich,
die Beispieldatei ist doch dann sehr gut.
Da solltest Du auch Hilfe finden,im Bereich Makros,
und den Thread hier bitte angeben (verlinken)

Gruss Chris

[Hiker]

Hallo,

ich glaube so langsam habe ich wenigstens verstanden, warum die Formel bei Ihm funktioniert.
Ich hatte zunächst angenommen daß der Term

Code: Alles auswählen

SUMMENPRODUKT(($A$49:$A$280={109.105})

tatsächlich mit den Vektoren/Arrays a49:a280 und {109.105} arbeitet und diese vergleicht -
wie es wohl auch lorbass Erklärung entspricht.

Statt dessen scheint {109;195} ein "unexpected array" im Sinne dieser Wiki-Stelle https://wiki.openoffice.org/wiki/Docume ... ing_Arrays zu sein.
Daher wird der Vektor a49:a280 zunächst mit jedem einzelnen der (skalaren) Werte 109 und 105 verglichen,
was jedesmal einen Ergebnisvektor ergibt, die per SUMMENPRODUKT kombiniert werden
so daß der entstehende Vektor aussieht, wie ein ODER Vergleich mit den Elementen von {109.105}

Den Punkt habe ich hier tatsächlich verwendet, da er in der Beispieldatei in der funktionierenden Formel steht UND
die in der Ausgangsfrage verwendete Form mit ; wie auch | nicht funktioniert - sondern evtl vom zunächst von mir
vermuteten Verhalten führen. Da bin ich noch nicht sicher.
Der Punkt als Trennzeichen wäre zu Excel konsistent, wo wohl (abhängig von der Spracheinstellung) das Komma verwendet wird.

Nach dem was ich bisher gelesen habe dürfte es nicht möglich sein den {}-Teil der Formel durch einen Bereich zu ersetzten,
weil das anscheinend einerseits dazu führt, daß die Formel so arbeitet, wie Lorbass beschrieben hat,
andererseits merhfach erwähnt wird, daß hier keine Variablen oder Bereiche möglich sind.

Ich würde eher in die Richtung arbeiten, ob man den Ergebnisvektor der Vergleiche nicht mit einem anderen Ansatz auch
zusammmenbauen kann, aber vielleicht hat hier ja noch einer eine bessere Idee...

MfG,
Jörn

PS: Versuche mit LO 4.3.6.2 durchgeführt

[Hiker]

Für die Variante ohne inline-Matrix habe ich mal die Beispieltabelle via SUCHEN ausgewertet
und die entsprechenden Vektoren in den Spalten S/T/U sichtbar zusammmengesetzt.

BeispieltabelleSuchen.ods

(27.18 KiB) 144-mal heruntergeladen

MfG, Jörn

[mikeleb]

Hallo,

anbei eine Makrolösung. Die Funktion Vektor() macht aus dem übergebenen String einen Vektor, mit dem dann gerechnet werden kann.