Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 09:47:49
Daten aus mehreren Dateien untereinander importieren
25.04.2024 09:31:31
Bild "In Zelle platzieren"
25.04.2024 09:23:47
Spalten in Kreuztabelle hinzufügen/löschen je nach Vorgabe
25.04.2024 08:41:49
VBA: Probleme mit pdftk zum Zusammenfügen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1812to1816
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1812to1816
1812to1816
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Gewichte aus Text filten

Gewichte aus Text filten
15.02.2021 13:52:50
Kai
Guten Tag,
ich versuche aus einem Text die Zahl und Einheit herauszufiltern.
Ziel ist eine Gewichtsberechnung. Wer kennt eine gute Lösung?
Apero Graham Broetli 25g -Stück -70
Apero Laugengipfel 20g (B.52) -Stück -285
Apero Mais Broetli 25g -Stück -90
Apero Ruch Broetli 25g -Stück -14
AvE Sardisches Brot -Kilogramm -19,2
Leider kann ich die Datei grad nicht hochladen, Artikelnahme mit Gewicht ist eine Spalte, Stück/Kilogramm die nächste, Menge eine weitere.
Vielen Dank

22
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Gewichte aus Text filten
15.02.2021 13:59:39
SF
Hola,
da kann man nur raten: 
=WECHSELN(TEIL(A1;VERWEIS(256;FINDEN(" ";A1;ZEILE($1:$128)))+1;99);"g";"")*1
So erhältst du das Gewicht. Das kannst du dann multiplizieren.
Gruß,
steve1da
AW: UDF
15.02.2021 14:04:29
Fennek
Hallo,
wenn die Spalten so sind:

Apero Graham Broetli 	 25g 	Stück 	70	1750
Apero Laugengipfel 	 20g (B.52) 	Stück 	285	5700
Apero Mais Broetli 	 25g 	Stück 	90	2250
Apero Ruch Broetli 	 25g 	Stück 	14	350
errechnet diese UDF das Gesamtgewicht:

function Gewicht(rng) as double
gewicht = val(rng) * val(rng.offset(,2)
end function
In der Function muss die Zelle der Spalte B ausgewählt werden. (getestet mit LibrOfiice)
Die Struktur des letzen Beispiels "AvE Sardisches Brot -Kilogramm -19,2" muss getrennt bearbeitet werden.
mfg
Anzeige
AW: Gewichte aus Text filten
15.02.2021 14:12:33
Daniel
Hi
die Beispieldatei mit den Daten wäre schon sehr hilfreich.
warum kannst du die nicht hochladen?
Das ist nicht so kompliziert, man muss nur die gegebenen Hinweise beachten.
Gruß Daniel
AW: Gewichte aus Text filten
15.02.2021 14:22:47
ChrisL
Hi Kai
Eine Datei und mehr Beispiele, damit man die Muster/Pattern erkennen kann, wäre tatsächlich sehr hilfreich.
In der Annahme, dass unterschiedliche Einheiten geben wird, nachfolgend ein Ansatz mit RegEx.
Folgende Funktion in ein Standardmodul kopieren: 
Public Function myRegEx(TextInput As String, regexPattern As String) As String
Dim regEx As Object: Set regEx = CreateObject("VBscript.regexp")
Dim matches
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = regexPattern
End With
If regEx.test(TextInput) Then
Set matches = regEx.Execute(TextInput)
myRegEx = matches(0).Value
End If
End Function

Formel (Zahl und Einheit): 
=myregex(A1;"\d+(g|kg|l|ml)")
Formel (nur Zahl): 
=myregex(myregex(A1;"\d+(g|kg|l|ml)");"\d+")*1
Formel (nur Einheit): 
=myregex(myregex(A1;"\d+(g|kg|l|ml)");"(g|kg|l|ml)")
Leicht erweiterbar z.B. mit Milligramm und Tonnen für die ganz grossen Gipfel :)
cu
Chris
Anzeige
AW: Gewichte aus Text filten
15.02.2021 16:46:39
ChrisL
Hier mit optionaler Dezimalzahl (Punkt als Trennzeichen):
=myregex(A1;"\d*?\.?\d+(g|kg)")
AW: Gewichte aus Text filten
16.02.2021 11:40:32
ChrisL
Gewicht in g:
=WECHSELN(myregex(WENN(myregex(B2;"\d*\.?\d+( g| kg|g|kg)")"";myregex(B2;"\d*\.?\d+( g| kg|g|kg)"); myregex(A2;"\d*\.?\d+( g| kg|g|kg)"));"\d*\.?\d+");".";"") *1
Anzahl/Einheit:
=WENNFEHLER(myregex(myregex(B2;"\d+(Beutel| Beutel|Stück| Stück|Stk| Stk|Brotlaib| Brotlaib)");"\d+") *1;1)
RegEx in Power-Query
16.02.2021 16:32:57
ChrisL
Kai darf mich auch weiterhin ignorieren, aber vielleicht gibt es interessierte Mitleser.
Im vorliegenden Fall wäre eine RegEx-Lösung m.E. sehr praktisch, weil sich die Pattern rasch ergänzen lassen und man so flexibel auf neue Anforderungen reagieren kann (z.B. wer weiss, was nach Stück, Beutel und Brotlaib noch alles kommt).
Im Anhang eine Power-Query Lösung mit folgenden Features:
- PQ-Funktion für Webaufruf (via Java-Script wird RegEx ausgeführt)
- Die RegEx-Pattern sind in einer separaten Tabelle abgelegt und können dort angepasst werden (benannter Bereich)
https://www.herber.de/bbs/user/143971.xlsx
Nachfolgend meine Quellen:
https://gist.github.com/Hugoberry/4948d96b45d6799c47b4b9fa1b08eadf
https://exceloffthegrid.com/power-query-source-cell-value/
cu
Chris
Anzeige
AW: RegEx in Power-Query
16.02.2021 17:02:32
Kai
Hallo Chris
Ignorieren will ich dich sicher nicht, hab aber excel Grundkenntnisse angegeben.
Muss schaffen nebenbei und bis ich verstanden habe wo ich deine Vorschläge einfügen kann
(RegEx in Power-Query) muss ich noch viel lernen. Meine kenntnisse reichen da nicht aus.
aber Respekt, deine Lösung funktioniert.
Wie gesagt, es fehlt mir am Plan sie umzusetzen.
Gruss Kai
AW: RegEx in Power-Query
16.02.2021 17:16:32
ChrisL
Hi Kai
Ich gebe zu, die vorliegend Lösung ist kaum zu durchschauen. Aber du kannst in Spalte A:C einfach deine Daten ergänzen und auf "Alle aktualisieren" drücken.
Für meine ursprüngliche Lösung hätte der VBA Code nur wie erwähnt in ein Standardmodul kopiert werden müssen und danach hättest du die angegebenen Formeln verwenden können.
SF, Fennek, Daniel und Luc wären sicherlich auch bereit gewesen dir betr. ihren Lösungsvorschlägen auf die Sprünge zu helfen, aber es erfordert ein Minimum an Reaktion deinerseits (z.B. was hast du probiert und wo steckst du fest etc.).
cu
Chris
Anzeige
AW: RegEx in Power-Query
17.02.2021 11:09:25
Kai
Hallo zusammen
Zuerst ein grosses Danke an alle für die erbrachten Vorschläge und die Umsetzung.
Nach meinen ersten 72 Stunden im Forum seht mir bitte meine Unkenntniss nicht nach.
Die Lösung von Chris funktioniert sehr gut. Hat halt bei mir etwas länger gedauert.
Ein Problem hat sich noch ergeben. Es gibt auch die Option, dass in Spalte B Kilogramm oder Liter als Einheit festgelegt ist. In diesem Fall wäre der Wert in Spalte C das Gesamtgewicht. Dies kann ich sicher über Filter vom Rest trennen. Event. gibt es noch eine elegantere Lösung?
https://www.herber.de/bbs/user/143997.xlsx
Anzeige
AW: RegEx in Power-Query
17.02.2021 12:59:05
ChrisL
Hi Kai
https://www.herber.de/bbs/user/144005.xlsx
"l" und "lt" konnte ich rasch im Pattern ergänzen.
"0,5lt" fällt dann aber doch stark aus der Reihe (anderes Dezimaltrennzeichen und ohne 3 Nachkommastellen). Ich habe darum darauf verzichtet, die Pattern komplett umzubauen und habe einfach mittels Suchen/Ersetzen "0,5lt" durch "0.500lt" ersetzt. Es bedeutet, dass ich dir den Sonderfall nur ganz spezifisch auskorrigiere.
Du müsstest also noch einmal ganz genau hinschauen, ob du nicht noch andere Sonderfälle hast und ggf. müsste ich noch einmal am Pattern arbeiten.
cu
Chris
Anzeige
AW: RegEx in Power-Query
18.02.2021 10:28:47
ChrisL
verbesserte Version:
https://www.herber.de/bbs/user/144033.xlsx
Dezimalangaben werden jetzt unabhängig vom Trennzeichen und Nachkommastellen berücksichtigt.
(n.b. JS hat folgende Eigenheiten:
- Umgang mit Sonderzeichen, darum Dezimaltrennzeichen temporär durch "xxxx" ersetzt
- Backslash müssen ganz generell verdoppelt werden)
AW: RegEx in Power-Query
18.02.2021 12:15:18
Kai
Hallo,
konnte die verbesserte Version noch nicht testen.
Hab mit der letzten Version Artikel gesucht, die nicht ins Schema passen.
Eine Spalte mit allen Einheiten hab ich auch eingefügt.
Weiterhin gibt es viele Artikel, welche in der Einheit ein X aufweisen.
Dies betrifft Dosen und Getränke. 12x1.000l zum Bsp.
Die orange gefärbten Felder sind effektive Gewichte, welche ich 1:1 übernehmen kann.
Dies ist immer bei Kilogramm und Liter = Einheit der Fall.
Ich mach mich gern heute Abend ans testen der neuen Version, grosses Danke
Kai
https://www.herber.de/bbs/user/144038.xlsx
Anzeige
AW: RegEx in Power-Query
19.02.2021 09:07:29
ChrisL
Hi Kai
Hier eine weitere Version:
https://www.herber.de/bbs/user/144062.xlsx
Die Daten werden zunehmend heterogen, was die Aufgabe nicht einfach macht und auch auf die Performance schlägt.
- Ein paar Zeilen musst du selber auskorrigieren z.B. Zeile 103: 50 x 1.5g (wird auf 2g gerundet), die Box enthält aber 20kg. Ich habe nur 3 Dimensionen (Menge*Gewicht*Einheiten).
- Auch Zeile 105: 1 Liter (linke Spalte) geht ggü. den 20kg vor.
- Zeile 290 ist vermutlich ein Fehler in den Inputdaten: 0.18g (gerundet = 0) wäre eine winzig kleine Dose.
Den einen oder anderen Sonderfall könnte man noch einfangen. Aber wahrscheinlich bist du am Punkt, wo eine manuelle Nachbearbeitung effizienter ist.
cu
Chris
Anzeige
AW: RegEx in Power-Query
20.02.2021 16:42:48
Kai
Hi Chris,
grossen Dank, jetzt läuft das sehr gut.
Es macht wirklich Spass damit zu schaffen.
Um eine manuelle Nachbearbeitung komme ich sicher nicht rum, dazu sind meine Daten zu unterschiedlich.
Ich verstehe aber was funktioniert, da sehr logisch, und was ich man. rausfiltern muss.
Rundungen im Grammbereich kann ich vernachlässigen. (Ergebniss wird sich bei 7500kg/Tag einpendeln)
Ist eine grosse Hilfe!
Nur am Rand, Dosen zu 250g und 500g ergeben kein Ergebniss, der Rest läuft sauber durch.
https://www.herber.de/bbs/user/144097.xlsx
Anzeige
AW: RegEx in Power-Query
21.02.2021 07:44:13
ChrisL
Hi Kai
Danke für die Rückmeldung.
Ich kann im Moment nicht testen, vermutlich müsstest du Kg mit grossem K noch in den Pattern aufnehmen.
cu
Chris
AW: Gewichte aus Text filten
15.02.2021 16:06:01
Daniel
und wie sieht für diese Datei dein Wunschergebnis aus?
für jede Zeile und als Gesamtergebnis. Das solltest du noch dazu schreiben.
so spontan würde ich sagen, das ist so ohne weiteres nicht möglich.
dafür sind die Angaben in den einzelnen Zeilen zu unterschiedlich.
Gruß Daniel
AW: Gewichte aus Text filten
15.02.2021 17:46:20
Günther
Das sollte mit Power Query lösbar sein.
Aber da du entweder nicht in der Lage oder nicht gewillt bist, das Problem umfassend zu analysieren und zu beschreiben und trotz Aufforderung auch in deiner Antwort, auf die ich mich beziehe, eine Lösung anzugeben, mag ich nicht mehr.
Beispiel: AvE Sardisches Brot -Kilogramm -19,2 könnten 19,2kg sein, weil ja in der Spalte Einheit "Kilogramm drin steht. In der von dir eingestellten Muster-xlsx gibt es aber auch beispielsweise Bauernbrot rund 2kg-Kilogramm-1348. Aha… Und natürlich gibt es dann auch noch Produkte, wo zwar die Einheit Stück erfasst ist aber keinerlei Gewichtsangabe. Schick!
 
Günther  |  mein Excel-Blog
AW: Gewichte aus Text filten
16.02.2021 10:30:18
Kai
genau, schick!
Hab die Datei gestern geerbt, mit der Aufforderung die Gewichte zwecks CO2 Bilanz zu ermitteln.
Problem 1: es gibt Zehntausend Zeilen
Problem 2: meine Exelkentnisse sind gering, ich muss bei allem nachlesen, Power Query ist am Arbeisplatz vorhanden, ich habe aber noch nicht damit gearbeitet.
Problem 3: nicht sichten der Datei gibt es ab und zu minus Werte, wenn aber selten
Problem 4: es gibt auch Artikel ohne Gewichtsangabe, da muss ich faktisch am Karton nachschauen oder ignorieren
https://www.herber.de/bbs/user/143955.xlsx
Ich habe einen Teil manuell eingegeben, zum Verständniss, wie es aussehen könnte, lad ich hoch.
Soweit so gut...
Fehlende ME bei kg- bzw Stück-Zuordnung
15.02.2021 20:08:19
Luc:-?
Hallo, Kai;
wie im Betreff angegeben und auch von Günther u.a. moniert, sind fehlende Angaben ein Problem, wenn du keine Kompensation vorgibst. Anderenfalls musst du das manuell lösen. Spontan würde ich in solchen Fällen 1 als 1.Faktor einsetzen, da es sich ja bei der GesamtMenge um eine Multiplikation handelt.
Unter dieser Voraussetzung könnte folgende singulare MatrixFml meist* fktionieren:
{=WENNFEHLER(TEIL(A1;MIN(WENNFEHLER(FINDEN(ZEILE($1:$10);A1);""));2)*C1;C1)}
Durch das vor der Zahl stehende Leerzeichen könnte als 3.Argument von TEIL statt 2 auch 3 verwendet wdn, falls sich die Gewichtswerte zwischen 10 und 999 bewegen. Mit 2 dürfen sie zwischen 1 und 99 liegen, wobei grundsätzlich keine Dezimalen vorgesehen sind.
Falls das zu unpassend ist, kämen UDFs infrage, die eine größere Flexibilität aufweisen könnten, ggf auf der schon vorgeschlagenen Basis von regulären Ausdrücken (RegEx) oder auch anders. Die würden auch bei Nur-Zahl-Ermitteln, was am sinnvollsten wäre, zu kürzeren Fmln führen, was folgende Fml-Möglichkeiten mit UDFs nur zeigen sollen:
=WENNFEHLER(MaskOn(MaskOn(A1;"mrk()";1);"num")*C1;C1)
=WENNFEHLER(TEIL(A1;VLike(A1;"* #*";-1);3)*C1;C1)
Bei deinem angegebenen KenntnisStand verzichte ich hier mal lieber auf Links zu den benötigten Pgmm, zumal diese Fmln nur besser sind als die erstgenannte, wenn es um Dezimalstellen u/o eine größere GewichtswerteBreite geht. So wie hier gezeigt, liefern sie allerdings auch kaum mehr als die 1.Fml.
* Auf die Sichtung deiner Datei habe ich auf Grund der zT aufschlussreichen anderen Kommentare verzichtet.
Gruß, Luc :-?
PS@Steve: Deine Fml fktioniert nicht mit den zuerst angegebenen BspDaten.
„Die universelle Befähigung zur Unfähigkeit macht jede menschliche Leistung zu einem unglaublichen Wunder.“ Stapps ironisches Paradoxon

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige