Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
SummeWENN aus Text
12.11.2021 08:21:57
Andre´
Hallo alle zusammen,
ich möchte gern die Summe aus einen Bereich ermitteln ohne Hilfsspalte.
Die Daten liegen wie folgt vor und die Zahlen habe ich rot dargestellt die summiert werden sollen.
In A9 wo ich eine Formel benötige habe ich manuell die Zahlen summiert.
Userbild
Hier noch die Bsp. Datei https://www.herber.de/bbs/user/149093.xlsx
Gibt es dafür eine Formel-Lösung?
Vielen Dank im Voraus
MFG Andre

29
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
PQ und CUBEWERT()
12.11.2021 09:06:49
ChrisL
Hi Andre
Die XL-Version ist für die nachfolgende Lösung ziemlich relevant.
https://www.herber.de/bbs/user/149096.xlsx
Für die Transformation bietet sich Power-Query an. Die bereinigte Tabelle wird ins Datenmodell geladen und ein Summen-Measure hinzugefügt. Die Resultatausgabe kann über folgende Formel erfolgen:
=CUBEWERT("ThisWorkbookDataModel";CUBEELEMENT("ThisWorkbookDataModel"; "[Measures].[Sum of Spalte1.1]"))
Bei Interesse bitte Power-Query Intro Video schauen und mit Power-Pivot Measure auseinandersetzen. Bei Bedarf nachfragen.
Sollte die Lösung unpassend sein (z.B. alte XL-Version), bitte die Frage wieder als offen kennzeichnen.
cu
Chris
Anzeige
AW: sorry Chris, hab ich erst jetzt gelesen.
12.11.2021 09:16:46
neopa
Gruß Werner
.. , - ...
AW: warum Auswertung über eine CUBE-Fkt? ...
12.11.2021 17:55:22
neopa
Hallo Chris,
... dies bedarf es doch nicht wirklich. Ich hab einfach in PQ eine temporäre Hilfsspalte mit einem beliebigen Textwert ergänzt und nach diesem gruppiert und so die Summe ermittelt.
Gruß Werner
.. , - ...
AW: warum Auswertung über eine CUBE-Fkt? ...
12.11.2021 19:27:50
ChrisL
Hi Werner
Wieso? Wieso nicht?
Du hast natürlich recht, Cubewert braucht es nicht zwingend. Kann man, muss man aber nicht.
Mir gefällt es, weil das Resultat ohne Titelzeile und Tabellen-Format ausgegeben werden kann. Die Formel lässt sich leicht verschieben, kopieren usw. Aber ich zeige auch einfach gerne die weniger bekannte Technik auf bzw. beschäftige mich damit.
Darauf, dass die Lösung "ziemlich" (ziemlich stark) von der Excel-Version abhängt, habe ich hingewiesen.
Ein starkes Argument gegen Cubewert wäre natürlich die Aktualisierungsdauer. Aber bisher konnte ich diesbezüglich keinen spürbaren Unterschied ausmachen. Die Laufzeit wird hauptsächlich durch die Abfrage selber und durch das Laden ins Datenmodell bestimmt. Danach sieht man ganz kurz einen Formelfehler (den Zeitpunkt interpretiere ich als Update vom Modell) und anschliessend ist auch schon das neue Resultat da.
Vielleicht muss ich es mal noch etwas genauer testen, aber bisher basiere ich auf der Annahme, dass Measure und Cube-Funktionen im Rahmen von üblichen Anwendungen keine Performance-Fresser sind. Bei sehr grossen Datenmengen müsste man natürlich genauer hinschauen, aber das Format der Inputdaten deutet nicht auf "Big Data" hin ;)
PS: Fällt mir ein, die für eine normale Formellösung lästigen Zeilenumbrüche könnte man loswerden, wenn man die Datei einmal als TXT abspeichert.
cu
Chris
Anzeige
AW: zur Performance mit Cubefunktion ...
12.11.2021 19:53:45
neopa
Hallo Chris,
... wenn ich in Deiner eingestellten Datei einen Datenwert ändere und danach aktualisiere, kann ich auf meinem PC den "Kreisel" geschätzte 2 sec zuschauen. In meiner Datei mit der reinen PQ-Lösung ist das nicht der Fall. Dabei hab ich darin sogar 3 PQ-Schritte mehr. Das kann doch dann nur an der CUBE-Funktion liegen, oder?
Gruß Werner
.. , - ...
AW: zur Performance mit Cubefunktion ...
12.11.2021 20:27:37
ChrisL
Hi Werner
Ich werde es nächste Woche mal testen. Bisher ist mir der Unterschied nicht aufgefallen.
cu
Chris
AW: zur Performance mit Cubefunktion ...
15.11.2021 13:02:06
ChrisL
Hi Werner
Ich habe die folgenden 3 Varianten verglichen:
1 reine PQ Lösung mit Ausgabe als Abfrageresultat
2 PQ-Lösung ins Datenmodell geladen, ohne Resultat Ausgabe
3 PQ-Lösung ins Datenmodell geladen, mit Cube-Funktion
Variante 1 scheint tatsächlich deutlich schneller wie die anderen Lösungswege.
Zwischen V2 und V3 konnte ich allerdings keinen grossen Unterschied mehr ausmachen. Mir scheint es, als ob das Laden ins Datenmodell die Laufzeit frisst und nicht die Cube-Funktion.
Repräsentativ (grössere Anzahl Stichproben, Datenmengen, präzise Zeitmessung) analysiert habe ich nicht, aber deine Beobachtung (Unterschied V1 zu V3) kann ich bestätigen.
cu
Chris
Anzeige
AW: danke für Deine vergleichende Untersuchung ...
15.11.2021 13:15:18
neopa
Hallo Chris,
... ja, die Performance ist zumindest in der Beispieldatei mit Variante 1 um eine Größenordnung besser.
Gruß Werner
.. , - ...
AW: PQ und CUBEWERT()
15.11.2021 13:03:30
ChrisL
Die Cube-Funktion lässt sich übrigens wie folgt kürzen:

=CUBEWERT("ThisWorkbookDataModel";"[Measures].[Sum of Spalte1.1]")

AW: ein wichtiger Hinweis zumindest für XL2016 ...
15.11.2021 13:26:34
neopa
Hallo Chris,
... Du hattest in Deiner Beispieldatei die CUBE-Auswertungsformel unmittelbar unterhalb des letzten Datenwertes eingesetzt, was dazu führt, dass (zumindest in meiner XL2016er Version), dass das Ergebnis mit in die formatierte Datentabelle integriert wird und somit das Formelergebnis auch als (geänderter) Datenwert interpretiert wird. Dadurch wird mit jeder vorgenommen Aktualisierung auch ohne Datenänderung das Ergebnis immer verdoppelt.
Es sollte also zwingend eine Trennung zwischen Datentabelle und Auswertungszelle gesichert sein, um solches zu verhindern.
Gruß Werner
.. , - ...
Anzeige
AW: ein wichtiger Hinweis zumindest für XL2016 ...
15.11.2021 14:21:50
ChrisL
Danke, das ist gut zu wissen. In XL365 ging es auch so, also anscheinend Versionenabhängig.
AW: wäre bedingt möglich, aber ...
12.11.2021 09:15:29
neopa
Hallo André,
... wegen Deinen Zahlenwerten in Texten mit Zeilenumbrüchen nur mit unverhältnismäßig hohen Aufwand bzw. Hilfsspalten oder mit Hilfe von Power Query (ab XL Version 2013)
Gruß Werner
.. , - ...
AW: ... wenn nur max ein Zeilenumbruch ...
12.11.2021 09:58:01
neopa
Hallo,
... dann mit folgender Formel:

=SUMMENPRODUKT(LINKS(A1:A8;SUCHEN(" ";A1:A8&"0 ")-1)+LINKS(TEIL(A1:A8&ZEICHEN(10)&"0"; SUCHEN(ZEICHEN(10);A1:A8&ZEICHEN(10)&"0 ")+1;99);SUCHEN(" ";TEIL(A1:A8&ZEICHEN(10)&"0 "; SUCHEN(ZEICHEN(10);A1:A8&ZEICHEN(10)&"0 ")+1;99))))

Gruß Werner
.. , - ...
Anzeige
Hatte ähnliche Idee, ...
12.11.2021 12:00:05
Luc:-?
…Werner,
mit genausoviel Fktsaufrufen wie Deine Fml, darunter allerdings 1× 1 UDF, aber für mehrere feste Zeilen pro Zelle:
{=SUMME(--LINKS(VSplit(A1:A8&WIEDERHOLEN(ZEICHEN(10)&0;MAX(LÄNGE(A1:A8)-LÄNGE(WECHSELN(A1:A8;ZEICHEN(10);"")))-(LÄNGE(A1:A8)-LÄNGE(WECHSELN(A1:A8;ZEICHEN(10);""))));ZEICHEN(10));2))}
SUMMENPRODUKT würde hier allerdings die MxFmlForm nicht überflüssig machen.
Gruß + schöWE, Luc :-?
Variante UDF
12.11.2021 09:27:49
ChrisL
Die erste Lösung gefällt mir zwar besser, aber hier noch eine benutzerdefinierte Funktion.
Ins Standardmodul kopieren:

Function SumSpez(rng As Range) As Long
Dim r As Range
Dim v1 As Variant, v2 As Variant
Dim i As Integer
For Each r In rng.Cells
v1 = Split(r, Chr(10))
For i = LBound(v1) To UBound(v1)
v2 = Split(v1(i), " ")
If IsNumeric(v2(0)) Then SumSpez = SumSpez + v2(0)
Next i
Next r
End Function
Formel:

=sumspez(A1:A8)

Anzeige
AW: Variante UDF
12.11.2021 09:40:42
Andre
Hallo alle zusammen,
vielen Dank für die Lösungsansätze!
Da wohl eine Formellösung doch mit viel Aufwand verbunden ist, werde ich dies mit einer Hilfsspalte umsetzen...
MFG Andre
AW: ohne Hilfsspalte s.o. bedingt möglich owT
12.11.2021 10:00:06
neopa
Gruß Werner
.. , - ...
AW: SummeWENN aus Text
12.11.2021 10:12:19
Daniel
HI
wenn du maximal zwei Zeilen in einer Zeile hast, könnte es folgende Matrxiformel tun (eingabe immer mit STRG+SHIFT+ENTER abschließen:

=SUMME(WENNFEHLER(--LINKS(A1:A8;FINDEN(" ";A1:A8));0))
+SUMME(WENNFEHLER(--TEIL(WECHSELN(A1:A8;" ";"          ");FINDEN(ZEICHEN(10);WECHSELN(A1:A8;" ";"          "))+1;8);0))
die erste Summe addiert die Zahlenwerte am Anfang, die zweite Summe die Zahlenwerte nach dem ersten Zeilenumbruch.
ab der dritten Zeile wirds dann kompliziert.
Am einfachsten wäre es, wenn du es schaffst, dass es nur noch eine Zeile pro Excelzelle gibt.
dann würde die erste Summe reichen.
Gruß Daniel
Anzeige
Die Formelvorschläge sind sehr gut...
12.11.2021 10:38:41
Andre
Hallo alle zusammen,
vielen lieben Dank für die Formellösungen, diese funktionieren wunderbar ;-)
Jetzt kann ich auf die Hilfsspalten verzichten!
MFG Andre
AW: Die Formelvorschläge sind sehr gut...
12.11.2021 11:56:12
Luschi
Hallo Andre,
alle Formel-Vorschläge sind eine Katastrophe!
- Excel ab 2016 bietet da bessere Varianten
- Du aber vertraust auf Altbewährtes wie zu Zeiten von Excel 2010
- die Variante von ChrisL: PQ und CUBEWERT()
- ist die Gegenwart mit Zukunftspotential
- alles andere ist rückwärtsgewandt!
Gruß von Luschi
aus klein-Paris
Da stand 'xlVersion ohne Relevanz', ...
12.11.2021 12:21:00
Luc:-?
…Luschi,
folglich liefern die Fmln etwas, was mehr oder weniger immer fktioniert, meine ab Xl9/2k.
Ist ja nett, dass Du Dich so für den Fortschritt engagierst, allerdings hatte µS ihn, auf dem sanften Ruhekissen seiner Marktmacht, auch bis 2007 um mindestens 10 Jahre verpennt. Bis dato waren dann unter unzufriedenen Xl-Usern schon etliche Hilfsmittel entstanden. Und jetzt sollen alle mit wehenden Fahnen zu Abonnement-Versionen, -Fktt und womöglich Cloud Computing überlaufen (was REvil & Co nebst Mossad & NSA und sonst noch werweißwen freuen dürfte)…‽
Wenn das die Zukunft sein soll, quasi eine weitere Version „blühender Landschaften“, …
Man könnte natürlich auch warten bis die selbstlernende KI alles erledigt. Dann muss man nur noch Daten und Bedingungen vorgeben und rein gar nichts mehr wissen…
Gruß, Luc :-?
Anzeige
AW: Da stand 'xlVersion ohne Relevanz', ...
12.11.2021 12:56:52
Luschi
Hallo Luc,
ich bin mir sicher, daß der Fragesteller weder die Variante
- PQ mit CUBEWERT()
- noch die angebotenen Formellösungen versteht
Sicher wird er auf 1 Formellösung setzen und auch einsetzen, aber ein Verständnis dafür hat er sicherlich NICHT und ist auch nicht gewillt, hier mehr Detailwissen zu erlangen.
Gruß von Luschi
aus klein-Paris
AW: Da stand 'xlVersion ohne Relevanz', ...
12.11.2021 13:13:14
Andre
Hallo Luschi,
PQ wende ich bereits an, allerdings nicht mit CUBWERT() das ist schon interessant und dies werde ich auch weiter verfolgen und ausgiebig testen.
PQ kann ich hier nicht einsetzen, weil die Daten mitunter in einer Zeile stehen, wo ich halt eine Formellösung einsetzen möchte.
Bezüglich Formellösung: habe ich auch gemerkt, dass der Zellinhalt einen gewissen Aufbau haben muss, sonst wird evtl. etwas anderes ausgegeben....
da das bei mir der Fall ist, reicht dies absolut aus.
Vielen Dank noch mal an ALLE!
MFG Andre
Anzeige
OT: Was Detailwissenserwerb anbelangt, ...
13.11.2021 01:43:01
Luc:-?
…magst Du recht haben, Luschi,
allerdings sieht's jetzt so aus, als ob André eher tiefstapeln würde…
Ansonsten bereite ich zZ eine etwas andere Symbiose von Xl und VBA vor, die schon zu neuem Detailwissen geführt hat. ;-)
Demnächst auch in diesem Theater
Morhn, Luc :-?
AW: ist so mE nicht voll zutreffend ...
12.11.2021 17:07:02
neopa
Hallo Luschi,
... abgesehen davon, dass der TE seine genutzte Excelversion nicht explizit angegeben hat, auf welches auch schon Luc verwiesen hatte, trifft die Aussage bzgl. der CUBE-Funktionen zumindest für meine XL2016er Version nicht zu. Diese sind da nicht vorhanden bzw. aktivierbar.
Gruß Werner
.. , - ...
AW: eben noch herausgefunden ...
12.11.2021 18:00:16
neopa
Hallo Luschi,
... das in meiner XL2016er Version die CUBE-Funktionen doch vorhanden und aktivierbar sind.
Allerdings musste ich feststellen, dass im vom Chris vorliegenden Beispiel die Auswertung sehr langsam ist. Auf jeden Fall ist eine reine PQ-Auswertung schneller und bei dieser kleinen auszuwertenden Datenmenge jede Formellösung schneller.
Gruß Werner
.. , - ...
AW: evtl. Korrektur der Formelvorschläge für ..
12.11.2021 16:58:47
neopa
Hallo André
... im Fall, dass im auszuwertenden Bereich Leerzellen oder ="" vorhanden sind, berücksichtigt dies meine Formel noch nicht richtig.
Wenn das notwendig sein sollte, müsste in meiner Formel im ersten Formelteil noch zwei kleine Ergänzungen (nachfolgend fett dargestellt) vorgenommen werden und zwar so:
=SUMMENPRODUKT(LINKS(0&A1:A8;SUCHEN(" ";0&A1:A8&"0 ")-1)+LINKS(....))
Daniels Formel berücksichtigt zwar schon die Leerzellen aber noch keine reine Zahlenwerte in den Auswertungszellen.
Wenn das auch notwendig sein sollte, dann im ersten Formelteil folgende Ergänzungen vornehmen:
{=SUMME(WENNFEHLER(--LINKS(0&A1:A8;FINDEN(" ";0&A1:A8&" "));0))+SUMME(....)}
Gruß Werner
.. , - ...
AW: evtl. Korrektur der Formelvorschläge für ..
12.11.2021 23:03:54
Andre´
Hallo Werner,
vielen Dank für die Formel-Erweiterung auch das funktioniert prima ;-)
Jetzt würde kein #WERT! erscheinen falls nichts in einer Zelle stehen sollte... aber wie bereits erwähnt ist das nicht der Fall.
Ich wünsche ein schönes WE
MFG Andre
AW: wünsche ich auch owT
13.11.2021 09:16:40
neopa
Gruß Werner
.. , - ...

315 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige