Ergebnis 1 bis 1 von 1

Thema: String oder .csv mit Trennzeichen per Formel auf Zellen aufteilen

  1. #1
    Office-Hilfe.com - Durchblicker
    Registriert seit
    30.12.2016
    Beiträge
    265

    String oder .csv mit Trennzeichen per Formel auf Zellen aufteilen


    Einleitung und Problematik


    Irgendwann ca. 2012 (so bei Excelformeln.de in der 210, 301, 326 und 455 dokumentiert, aber eigentlich noch vorher) störte es mich, dass man mit langen (und pro Feld auch noch wechselnden) Funktionen wie SUCHEN und mit vielfacher Erwähnung des Aufteilstrings diesen umständlich auseinanderpflücken muss. Ich überlegte, welche Funktion man hier besser einsetzen könnte.

    Idee und Lösung

    Was, wenn man die Trenner aufspreizt, und in gleichgroßem Abstand nach Inhalten fischt? Die rutschen in dem "Intervall" dann nur immer mehr nach rechts. Ich illustriere mal wie folgt:

    A1: |Schere Stein Papier|

    Das sind ungefähr 20 Buchstaben (ohne Trennzeichen, nicht genau gezählt). Um meine Formel wiedererkennbar zu machen, nehme ich als Aufspreizungsfaktor Zahlen mit 9 in der Einerstelle, hier also 19.

    B1: |Schere_____________|_____Stein_________|_________ _Papier____|

    =WECHSELN($A1;" ";WIEDERHOLEN(" ";19)) sorgt dafür. Wichtig ist, dass die 19 nicht kleiner ist, als die Addition aller Zeichen abzüglich der Trenner. Häufiger wird man also eine 99, eine 299 oder gar 999 hier finden. Für dieses Beispiel reicht die 19.

    Nun wollen wir aber an den senkrechten Strichen auch tatsächlich teilen:

    B1[:D1]: =TEIL(WECHSELN($A1;" ";WIEDERHOLEN(" ";19));SPALTE(A1)*19-18;19)

    schreibt

    B1: |Schere_____________|
    C1: |_____Stein_________|
    D1: |_________Papier____|

    in die Zellen.

    Bitte beachte, dass sich die Zahl 19 insgesamt 4 Mal wiederfindet, davon einmal um 1 auf 18 reduziert!

    Die Leerzeichen, hier dargestellt durch den Unterstrich, müssen noch weg:

    B1[:D1]: =GLÄTTEN(TEIL(WECHSELN($A1;" ";WIEDERHOLEN(" ";19));SPALTE(A1)*19-18;19))

    ergibt endgültig

    B1: Schere
    C1: Stein
    D1: Papier

    Wir haben unser Ziel erreicht!

    Vorteil 1: Auf $A1 wird nur ein einziges Mal Bezug genommen! Das bedeutet, dass man dafür auch beliebig lange Ausdrücke einsetzen kann, ohne dass man Hilfszellen verwalten müsste (weil der Ausdruck in der Formel nämlich nur einmal rechnen muss).

    Vorteil 2: Die Formel ist in alle Zellen kopierbar. Jeder Modellentwickler weiß das zu schätzen!

    Vorteil 3: Die Formel ist relativ kurz, und sie besitzt Wiedererkennungswert. Die alten SUCHEN()-Konstrukte waren hingegen immer höchst individuell und überhaupt nicht "zu lesen".

    Vorteil 4: Nimmt man statt $A1 ein A$1, und ersetzt SPALTE(A1) durch ZEILE(A1), hat man die Ergebnisse untereinander, statt nebeneinander.

    Mit Parametern wird die gerade genannte Formel vielleicht noch etwas deutlicher:

    B1: =GLÄTTEN(TEIL(WECHSELN($A1;" ";WIEDERHOLEN(" ";
    LängeDesStringsOhneTrenner));AbWievieltemWort*
    LängeDesStringsOhneTrenner-(
    LängeDesStringsOhneTrenner-1);
    LängeDesStringsOhneTrenner*
    WievieleWörterZusammenInEinerZelleTrotzTrenner))


    Die Länge eines Formelergebnisses darf seit xl2007 32k Zeichen betragen. Dies ist nun das Produkt aus zulässiger verketteter Aufteilstringlänge (ohne Trenner) und Anzahl der Wörter darin, welches nicht überschritten werden darf.

    Anwendungsfeld

    Ich habe in den Foren bestimmt schon 100 Mal mit der Formel geantwortet. Aus dem Beispiel oben kann man den Standardfall erkennen. Aber man kann damit auch Zellinhalte rechnerisch auswerten:

    A1: 2 -3 3,4 Saft -1,2% -,3 Milch 1001 -0,00001 ergibt 1003,08799 mit

    B1: {=SUMME(WENNFEHLER(--TEIL(WECHSELN(A1;" ";WIEDERHOLEN(" ";99));ZEILE($1:$19)*99-98;99);0))}

    (diese Formel bitte nur ohne {} eingeben, und statt mit EINGABE erweitert mit STRG-UMSCHALT-EINGABE abschließen!)

    Wir sehen daran:

    1. Eingestreute Texte in A1 sind unschädlich.

    2. -,3 statt -0,3 ist als "Summand" möglich. Ebenso ein angehängtes %-Zeichen für /100 ... usw. (jedoch nicht wie bei Formeln mehrere % hintereinander).

    3. In der Formel B1 kann bei Zahlenauswertung auf GLÄTTEN verzichtet werden, weil Excel die Leerzeichen beim Umwandlungsversuch mit dem Doppelminus von selbst wegnimmt.

    In Verbindung mit meiner WENNNULLODERNEGATIV-Funktion kann man beispielsweise nur die positiven Zahlen zu 1006,4 addieren:

    {=SUMME(WENNFEHLER(EXP(LN(TEIL(WECHSELN(A1;" ";WIEDERHOLEN(" ";99));ZEILE($1:$19)*99-98;99)));""))}

    Der Mittelwert der nur negativen ergibt sich zu -0,8280025 durch zwei eingefügte Spiegelungen um Null herum aus

    {=MITTELWERT(WENNFEHLER(-EXP(LN(-TEIL(WECHSELN(A1;" ";WIEDERHOLEN(" ";99));ZEILE($1:$19)*99-98;99)));""))}

    Wichtig dabei: Bei den beiden letzten Auswertungen mit Bedingung muss statt 0 das "" als 2. WENNFEHLER-Argument verwendet werden, da die 0 sonst in den MITTELWERT mit eingeht!

    Im Konstruktionsmakro unten findet man auch noch die Formel für Einträge von rechts.

    Fazit

    Die o.g. Formel löst in den meisten Fällen das Problem der Stringaufteilung (etwa bei Textimporten) mit einer aufgrund einer schmutzigen Idee standardisierten Formel. Außerdem nimmt sie den eifrigen "Zahlen-in-einer-Zelle"-Sammlern, die es immer noch gibt, die Arbeit ab, diese Zahlen z.B. mit SUMME() auszuwerten.

    Die Formel ist jedoch nicht unbegrenzt anwendbar, da sie drei Limitationen hat: Stringlänge einer Zelle, Anzahl Aufteilungen und maximale Länge eines Teils. https://www.ms-office-forum.net/foru...27#post1873227 schafft hier Abhilfe:

    Schere-Stein-Papier-Beispiel: B1[:D1]: =TEIL(A$1;
    FINDEN("#";WECHSELN(" "&A$1&"#";" ";"#";SPALTE(A1)+0));
    FINDEN("#";WECHSELN(" "&A$1&"#";" ";"#";SPALTE(A1)+1))-
    FINDEN("#";WECHSELN(" "&A$1&"#";" ";"#";SPALTE(A1)+0)))


    Baue Dir die Datei selbst

    Sub ZellaufteilungsformelAehnlichSplit()
    Workbooks.Add xlWorksheet
    Kern = "MID(SUBSTITUTE(R1C1,"" "",REPT("" "",99))," 'Kernteil der Formel
    Eintrag = "ROW(R1:R19)*99-98,99)" 'einzelner Eintrag
    Anzahl = "LEN(R1C1)-LEN(SUBSTITUTE(R1C1,"" "",))+1" 'Anzahl Verkettungseintraege
    Quelle = "http://www.office-hilfe.com/support/showthread.php/" 'Ort der Doku
    [A1] = "2 -3 3,4 Saft -1,2% -,3 Milch 1001 -0,00001"
    [A3] = "Spaltenaufteilung:"
    [A4:J4].FormulaR1C1 = "=TRIM(" & Kern & "COLUMN(RC)*99-98,99))"
    [A6] = "Viert- und drittletzter Eintrag aus A1:"
    [A7:B7].FormulaR1C1 = "=TRIM(" & Kern & "(" & Anzahl & "-4+COLUMN(RC))*99-98,99))"
    [A9] = "Summe der Zahlen in der Zelle A1:"
    [A10].FormulaArray = "=SUM(IFERROR(--" & Kern & Eintrag & ",0))"
    [A12] = "beschränkt auf die positiven Zahlen:"
    [A13].FormulaArray = "=SUM(IFERROR(EXP(LN(" & Kern & Eintrag & ")),""""))"
    [A15] = "Mittelwert nur der negativen Zahlen in der Zelle A1:"
    [A16].FormulaArray = "=AVERAGE(IFERROR(-EXP(LN(-" & Kern & Eintrag & ")),""""))"
    [A18] = "Quelle Konstruktionsmakro:": [A19] = Quelle & 22254
    [A21] = "Quelle WENNNULLODERNEGATIV:": [A22] = Quelle & 21725
    End Sub


    Beispiele der Formel in verschiedenen Foren

    2017.10.03 Mit den Referenzdaten a) einer Spalte oder b) einer anderen einzelnen Zelle übereinstimmende Zahlen einer Zelle addieren
    2017.10.04 Alle Zahlen in einer Zelle (mit Leerzeichen und CR) addieren
    2017.10.04 Die Zahlen einer Zelle, die zu einem bestimmten Namen gehören, für mehrere solche Zellen namensbezogen addieren
    2018.04.27 Nur bei 3 Leerzeichen aufteilen, nicht bei weniger!
    2018.09.24 Mit + getrennte Zahlen in einer Text-Zelle - und dies für mehrere Zellen zusammen - addieren
    Geändert von lupo1 (24.09.2018 um 17:35 Uhr)

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  
Excel Ticker - News, Tipps und Tricks zu Microsoft Excel