Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1464to1468
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Summe mit Sverweis
08.01.2016 14:55:54
Levon
Hallo liebe Excel-Profis,
mich bringt gerade eine Funktion zum Verzweifeln.
Vielleicht hat jemand von euch eine Ahnung, wie man das lösen kann.
https://www.herber.de/bbs/user/102672.xlsx
In der angehängten Datei findet Ihr zwei Tabelle, Results und DBase.
In der Dbase gibt es zwei Spalten, erste mit Buchstaben, zweite mit Werten.
In der Results gibt es auch zwei Spalten, erste beinhaltet die Werte aus DBase mit dem Semikolon getrennt.
Problem:
Wie bekomme ich die Summen der mit Semikolon getrennten Werten in der Spalte C? Ich möchte dass die Werte wie mit Sverweis verbunden sind.
Für die Antwort wäre ich euch sehr dankbar.
Liebe Grüße
Levon

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: SUMMENPRODUKT() und FINDEN() ...
08.01.2016 15:23:52
...
HalloLevon,
... so: =SUMMENPRODUKT(ISTZAHL(FINDEN(DBase!B$2:B$99;B4))*DBase!C$2:C$99)

Gruß Werner
.. , - ...

AW: SUMMENPRODUKT() und FINDEN() ...
08.01.2016 15:33:37
Levon
Hallo Werner,
vielen vielen vielen herzlichen Dank dir!!!
Es hat super funktioniert.
Jetzt sehe ich, dass ich in die falsche Richtung recherchiert habe.
Nochmals vielen Dank und schönes Wochenende.
Levon

AW: Summe mit Sverweis
08.01.2016 16:25:39
Levon
Ich habe leider noch eine Frage.
wenn ich etwas komplexere Definitionen habe, wie z.B. MA-WE-0001;MA-WE-0002;MA-WE-0003;MA-WE-0004...
muss ich da etwas besonderes eingeben? Weil die Summe nur bei einstelligen Werten funktioniert :(
Nochmals Vielen Dank für die Hilfe

Anzeige
AW: Summe mit Sverweis
08.01.2016 19:03:38
Luschi
Hallo Levon,
bei mir klappt der Tipp von Werner auch bei zusammengesetzten Begriffen.

Die Datei https://www.herber.de/bbs/user/102682.xlsx wurde aus Datenschutzgründen gelöscht


Gruß von Luschi
aus klein-Paris

AW: Summe mit Sverweis
08.01.2016 23:05:32
Levon
Hallo Luschi,
vielen Dank, dass du dir die Mühe gemacht hast.
Ich habe eine neue Datei gemacht und drinnen probiert.
Es klappt immer noch nicht, zeigt ständig 0.
Vielleicht muss ich irgendwas am Format machen.
Ich versuche mal weiter :))
Vielen Dank und schönes Wochenende.
Grüße aus Stuttgart
Levon

Anzeige
AW: hast Du wirklich identische Texte? ...
09.01.2016 10:50:24
...
Hallo Leon,
... bei Einsatz der Funktion FINDEN() wird auch Groß- und Kleinschreibung berücksichtigt.
Gruß Werner
.. , - ...

AW: hast Du wirklich identische Texte? ...
09.01.2016 10:56:58
Daniel
Wenn die Gross/ Kleinschreibung abweichen darf, kann man SUCHEN statt FINDEN verwenden.
Gruß Daniel

AW: hast Du wirklich identische Texte? ...
09.01.2016 19:15:27
Levon
Vielen Dank erstmal für die Zahlreichen Kommentare.
Ich habe identische Texte. Ich habe auch schon versucht mit "Suchen" statt "finden" nutzt mir aber auch nichts. Ich bin am Verzweifeln ;((
Hier ist meine Datei: https://www.herber.de/bbs/user/102690.xlsx
Vielen Dank und herzliche Grüße
Levon

Anzeige
Schau mal...
09.01.2016 19:25:47
robert
Hi,
diese Formel an
=SUMMENPRODUKT(ISTZAHL(FINDEN(Input!B$3:B$99;B4))*Input!C$3:C$99)
und vergleiche sie mit Deiner Formel in der Datei.
Gruß
robert

AW: Schau mal...
10.01.2016 00:30:41
Levon
Ohh neeee ;)))
Es tut mir soo leid, dass ich was übersehen habe.
Vielen Vielen Dank nochmals
Alles funktioniert perfekt
Liebe Grüße
Levon

AW: hast Du wirklich identische Texte? ...
09.01.2016 19:28:12
Daniel
Hi
da sind zwei Fehler drin:
a) ein Zellbezug auf die falsche Zelle, du schreibst "C4", es sollte aber "B4" sein (die Zirkelbezugwarung hätte dir ein Zeichen sein sollen)
b) du hast vergessen, das zweite 99 auf 14 abzuändern.
in solchen Matrixformeln muss man darauf achten, dass die Zellbezüge gleich gross sind)
Richtig wäre diese Formel in C4 (deine Fehlerstellen unterstrichen):
=SUMMENPRODUKT(ISTZAHL(FINDEN(Input!B$3:B$14;B4))*Input!C$3:C$14)
gruss Daniel

Anzeige
AW: hast Du wirklich identische Texte? ...
09.01.2016 17:49:37
Luschi
Hallo Werner,
wie kommt man auf diese eigentlich verrückte Verwendung der Funktion Finden()/Suchen()?
In der Vba-Hilfe von E2010/13/16 habe ich dazu nichts gefunden.
Fragt sich Luschi
aus klein-Paris

AW: hast Du wirklich identische Texte? ...
09.01.2016 18:05:32
Daniel
Hi
ist ja auch kein VBA, sondern eine Excelformel.
und bei den Excelformeln steht in der Hilfe (die die geöffnet wird, wenn man die Formel eingibt und dann in der PopPup-Eingabehilfe auf den Funtkionsnamen klickt) folgendes als erster Hinweis:

Die Funktionen FINDEN und FINDENB berücksichtigen die Groß- und Kleinschreibung und lassen keine Platzhalterzeichen zu. Falls bei der Suche die Schreibweise nicht beachtet werden muss oder Platzhalterzeichen eingesetzt werden sollen, verwenden Sie SUCHEN bzw. SUCHENB.

Gruss Daniel

Anzeige
AW: Deine Frage war sicherlich anders gemeint ...
11.01.2016 08:50:27
...
Hallo Luschi,
... als wie sie Daniel "beantwortet" hat, oder?
Wenn ja, so kann ich dazu nur schreiben, für mich war das einfach sofort naheliegend.
Übrigens hatte ich bewusst nicht auf SUCHEN() verwiesen.
Bei derartigen ungewöhnlichen Summationen sollte man wenigstens Exaktheit der Datenvorgabe fordern. Und eine solche würde durch die Verwendung SUCHEN() "aufgeweicht" mit evtl. ungewollten Ergebnissen, die nur schwer als solche zu erkenne sind.
Gruß Werner
.. , - ...

AW: Deine Frage war sicherlich anders gemeint ...
14.01.2016 07:32:21
Luschi
Hallo Werner,
falls Du noch mal hier reinschaust, dann sieh Dir doch bitte mal diese Datei an.
Meine Frage dazu steht in Tabellenblatt.
https://www.herber.de/bbs/user/102770.xlsx
Gruß von Luschi
aus klein-Paris

Anzeige
AW: zu Deiner Zusatzfrage ...
14.01.2016 15:41:47
...
Hallo Luschi,
... wie der Text strukturiert ist, ist bei dieser Art Auswertung völlig gleich, denn die Matrix für die SUMMENPRODUKT()-Auswertung wird durch das Teilergebnis von FINDEN(DBase!B$2:B$99;B16) generiert und nicht durch den Text in dem gesucht (und evtl. gefunden) wird.
Das kannst Du nachvollziehen wenn Du mal folgende Teilformel =ISTZAHL(FINDEN(DBase!B$16:B$20;B16)) in D16 eingibst und Dir deren Inhalt betrachtest (z.B. nach Markieren und Betätigung von F9 in der Eingabezeile) betrachtest.
Übrigens genau wegen dieser Wirkungsweise bezogen auf die Formeln in C16:C18, hatte ich den Einsatz von FINDEN() anstelle von SUCHEN() empfohlen.
Gruß Werner
.. , - ...

Anzeige
Aha, jetzt habe ich verstanden, ...
14.01.2016 21:28:49
Luc:-?
…was du meinst, Werner,
die Frage nach den Trennzeichen im Text! Die sind FINDEN tatsächlich schnurz, denn danach wird ja nicht gefragt.
Bleibt die Frage, warum AGGREGAT nicht fktioniert! Die üblicherweise und so auch hier gelieferte Erklärung mit der MatrixFähigkeit bestimmter integrierter Fktt erst ab einer bestimmten ElementeAnzahl halte ich für einen neuen Xl-Mythos, denn das entbehrt jeder Logik. Entweder Xl reagiert auf MatrixFmln wie gewohnt oder diese Fktionalität ist einer Fkt inhärent. Das ist nur eine Frage der Pgmmierung! Da SUMME als MatrixFml hier fktioniert, ist nicht einzusehen, warum AGGREGAT das in keiner Form und Variante tut! So etwas habe ich auch schon bei anderen integrierten Fktt feststellen können. Folglich halte ich das für eine Schlampigkeit bei der Pgmmierung der RahmenFkt, die wahrscheinlich der von TEILERGEBNIS entspricht, nur etwas erweitert wurde. Dabei ist wohl am Wichtigsten, dass jetzt auch Datenfelder verarbeitet wdn. Meine Tests haben allerdings ergeben, das AGGREGAT hier auch mit ZellBereichen nicht zu fktionieren scheint. Übrigens fktioniert TEILBEREICH dann auch nicht, obwohl die Fml akzeptiert, also tatsächlich auf Bereiche referenziert wird.
Allerdings fktioniert AGGREGAT mit KKLEINSTE bzw KGRÖSSTE, nur muss außen noch SUMME drumherum, denn es wird ja ein Datenfeld geliefert. Das Ganze dann natürlich als MatrixFml:
{=SUMME(AGGREGAT(15;6;WENN(FINDEN(DBase!B$1:B$25;B10);DBase!C$1:C$25);ZEILE(B$1:B$12)))}
Aber nun wird's ganz verrückt! Die in der Fml angegebenen Bereiche über 25 Zeilen sind das Minimum! Verwendet man weniger, produziert AGGREGAT.14/15 den Fehler #ZAHL!, was absolut nicht einzusehen ist! Folglich läuft irgendetwas in der RahmenFkt mächtig schief, so dass entweder die integrierte Fkt kein ordentliches Datenfeld bekommt oder die Rahmenfkt das von der integrierten Fkt zurückgegebene Datenfeld nicht richtig interpretiert (die Fehler ausblendet).
Meine Schlussfolgerung lautet jedenfalls: AGGREGAT? Nein, danke!
Aber trotzdem danke ich dir, Luschi,
denn du hast mit diesem Problem ein schönes Bsp zum Nachweis der Existenz quasi-1-zelliger MatrixFmln geliefert (habe ich früher etwas anders bezeichnet!), also von MatrixFmln, bei denen nur ein Ergebniswert erwartet wird, man aber mindestens 2 Zellen auswählen muss, damit die ganze Matrix (Datenfeld) verwendet wird. Man hat dann 2× das gleiche Ergebnis und muss sich, falls es keine andere akzeptable FmlMöglichkeit gibt (hier ja schon), überlegen, was man mit dem überflüssigen Wert macht (ich empfehle da ja immer eine nicht-klassisch erzeugte VerbundZelle). Hierbei käme dann neben einer UDF auf Split-Basis auch SVERWEIS zum Einsatz:
{=SUMME(SVERWEIS(VSplit(B10;";");DBase!$B$3:$C$14;2;))}
{=SUMME(SVERWEIS(VSplit(B13;"");DBase!$B$3:$C$14;2;))}
Über 2 Zellen liefert die 1.MatrixFml 38, in nur einer 2! Bei der 2. sind's 243 bzw 221.
Gruß, Luc :-?
Besser informiert mit …

Anzeige
Übrigens, was SVERWEIS & Co betrifft ...
15.01.2016 12:49:19
Luc:-?
…(also den 2.Teil des vorstehenden BTs), ist das hier natürlich auch ohne UDF, dafür mit benannter Fml auf Basis der XLM-Fkt AUSWERTEN möglich:
{=SUMME(SVERWEIS(Quelle;DBase!$B$3:$C$14;2;))}
Quelle: =AUSWERTEN("{"""&WECHSELN(B10;";";""".""")&"""}")
Der Effekt ist derselbe!
Luc :-?

305 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige