Microsoft Excel

Herbers Excel/VBA-Archiv

Mehrere Werte in einer Zelle Ausgeben.

Betrifft: Mehrere Werte in einer Zelle Ausgeben. von: Hans Nepomuk
Geschrieben am: 25.08.2014 11:22:16

Hallo,

aus einer Liste soll der Text in eine Tabelle übertragen werden (siehe Datei).
D. h. in Zelle B3 (AA) soll der Text „Ball“ erscheinen, in Zelle D4 (BC) der Text „Haus“ und „Baum“ usw., jeweils getrennt durch einen Zeilenumbruch.
Wie müsste die entsprechende Formel lauten?

Gruß
HN

  

Betrifft: AW: Mehrere Werte in einer Zelle Ausgeben. von: {Boris}
Geschrieben am: 25.08.2014 11:28:01

Hallo Hans,

was hast Du denn im Nachgang mit den Werten vor?
Ich frag deshalb, weil es meistens früher oder später wieder darum geht, die Werte zu ENTketten - und dann kommt das nächste Problem.

VG, Boris


  

Betrifft: AW: Mehrere Werte in einer Zelle ausgeben. von: Hans Nepomuk
Geschrieben am: 25.08.2014 12:25:56

Hallo Boris,
im Grunde genommen ist die Tabelle schon das "Endprodukt".
Zumindest soweit ich das bis jetzt einschätzen kann.
Probleme könnte es mir u.U. machen, wie und woher die Daten in die Liste kommen.
Das habe ich aber (noch?!) im Griff.

Gruß
HN


  

Betrifft: AW: Mehrere Werte in einer Zelle ausgeben. von: {Boris}
Geschrieben am: 25.08.2014 12:39:28

Hi Hans,

hier mal eine Formel bis zu 3 Treffern.
In Deiner Excelversion kannst Du das noch etwas kürzer unter Zuhilfenahme der Funktion WENNFEHLER gestalten - hier hab ich aber gerade nur Excel 2003.

B3 (und dann nach rechts und nach unten kopieren) - ARRAY(!)formel:

{=WENN(ZÄHLENWENN($H$3:$H$9;B$2&$A3);INDEX($I$3:$I$9;MIN(WENN($H$3:$H$9=B$2&$A3;ZEILE($1:$7))));"") &WENN(ZÄHLENWENN($H$3:$H$9;B$2&$A3)>1;ZEICHEN(10)&INDEX($I$3:$I$9; KKLEINSTE(WENN($H$3:$H$9=B$2&$A3;ZEILE($1:$7));2));"")&WENN(ZÄHLENWENN($H$3:$H$9;B$2&$A3)>2; ZEICHEN(10)&INDEX($I$3:$I$9;KKLEINSTE(WENN($H$3:$H$9=B$2&$A3;ZEILE($1:$7));3));"") }

Und die Zellen mit Zeilenumbruch formatieren!

VG, Boris


  

Betrifft: Könnte es sein, dass eher $A#&B$# als ... von: Luc:-?
Geschrieben am: 26.08.2014 02:28:05

…B$#&$A# gemeint ist, Boris (& Hans)?
Morrn, Luc :-?


  

Betrifft: AW: Könnte es sein, dass eher $A#&B$# als ... von: Hans Nepomuk
Geschrieben am: 26.08.2014 08:44:27

Hallo Luc :-?
stimmt, das ist mir anfangs garnicht aufgefallen, vielen Dank für den Hinweis.
Jetzt werden die Werte korrekt eingeordnet.

Gruß
HN


  

Betrifft: Versteh ich nicht... von: {Boris}
Geschrieben am: 26.08.2014 09:07:31

Hi Luc,

ich hab doch formuliert: B$2&$A3 ?!

VG, Boris


  

Betrifft: Jetzt hab ich kapiert.... von: {Boris}
Geschrieben am: 26.08.2014 09:15:04

...vergesst meine "Irritation" bitte ;-))
Ihr habt natürlich Recht.

VG, Boris


  

Betrifft: AW: Mehrere Werte in einer Zelle ausgeben. von: Hans Nepomuk
Geschrieben am: 26.08.2014 08:39:12

Hallo Boris,
für die Beispieldatei funktioniert dein Lösungsansatz tadellos (den kleinen Dreher - siehe Anmerkung von Luc:-? - habe ich korrigiert), danke dafür.
Für meine eigentliche Aufgabe kann ich diese Formel aber nur bedingt einsetzen, da es u.U. mehr als 3 Treffer geben kann.
Die Formel zu erweitern oder gar umzuschreiben ist mir bisher leider nicht geglückt.
Hier benötige ich wohl noch Hilfe.

Gruß
HN


  

Betrifft: AW: Mehrere Werte in einer Zelle ausgeben. von: {Boris}
Geschrieben am: 26.08.2014 09:43:08

Hi Hans,

wie viele Treffer können es denn maximal sein?
Ich frage deshalb, weil man mit einer Excelformel Elemente eines Arrays leider nicht verketten kann und daher für jeden möglichen Treffer dieser Formelteil angehängt werden muss:

&WENN(ZÄHLENWENN($H$3:$H$9;$A3&B$2)>2; ZEICHEN(10)&INDEX($I$3:$I$9;KKLEINSTE(WENN($H$3:$H$9=$A3&B$2;ZEILE($1:$7));3));"")
wobei die ZÄHLENWENN-Treffer-Abfrage immer um 1 erhöht werden muss (hier: von >2 auf >3) und selbiges auch für den Parameter k der Funktion KKLEINSTE gilt (hier: 3 -> erhöhen auf 4 usw.).

Und wenn das nachher zu viele Treffer sein können, wird das ne Monsterformel. Dann wäre wohl eher VBA das probate Mittel zur Lösung.

VG, Boris


  

Betrifft: Falls du dich für VBA-Einsatz entscheidest, ... von: Luc:-?
Geschrieben am: 26.08.2014 13:05:55

…Hans,
kannst du die UDF MxJoin, die du hier findest, auch in einer ZellFml verwenden, die dadurch auch wesentlich kürzer wird.
Gruß, Luc :-?


  

Betrifft: AW: Falls du dich für VBA-Einsatz entscheidest, ... von: Hans Nepomuk
Geschrieben am: 26.08.2014 13:55:29

Hallo Luc,
vielen Dank für den Hinweis.
VBA ist leider (noch) nichts für mich.

Gruß
HN


  

Betrifft: Für dein Bsp würde das dann so aussehen, ... von: Luc:-?
Geschrieben am: 26.08.2014 20:12:15

…Hans:

 KLMNOP
2 ABCDE
3ABall    
4B  Haus
Baum
  
5C    Auto
6D Hammer
Glas
Hallo
   
7Formel:L3[:P6]: {=MxJoin(WENN($H$3:$H$9=$A3&B$2;$I$3:$I$9;"");ZEICHEN(10))}
Die UDF musst du in ein allgemeines VBA-Modul (VBEditor - Rechtsklick auf Strukturbaum - Einfügen - Modul) deiner Mappe kopieren.
Gruß, Luc :-?


  

Betrifft: AW: Für dein Bsp würde das dann so aussehen, ... von: Hans Nepomuk
Geschrieben am: 28.08.2014 17:58:55

Hallo Luc,
funktioniert perfekt, auch wenn ich nicht verstehe warum und was diese neue Funktion insgesamt noch kann.
Vielen Dank.

Gruß
HN

PS: Wie hast du den Auszug des Tabellenblattes in deinen letzten Post bekommen?


  

Betrifft: Richtig vermutet, die kann noch mehr in ... von: Luc:-?
Geschrieben am: 29.08.2014 02:11:15

…dieser Richtung verarbeiten bzw erzeugen, Hans,
zB das, was im verlinkten Thread gefordert wurde. Das wirst du schon noch herausfinden…
Zur TabDarstellung:
Das ist eine HTML-Tabelle, die ich mit eigenem VBA-Pgm (alte 1.AusbauStufe) erzeugt und dann in den Beitragsentwurf hineinkopiert habe. So etwas ist bereits mehrfach entwickelt worden und mindestens 3 im W³ angebotene einschlägige Tools fktionieren auch in diesem Forum.
Morrn, Luc :-?


  

Betrifft: AW: Mehrere Werte in einer Zelle ausgeben. von: Hans Nepomuk
Geschrieben am: 26.08.2014 13:45:11

Hallo Boris,
ja, leider kann es soviele Treffer geben wie es Zeilen in der Liste gibt und die ist zu allem Überfluß variabel.
Ich habe die Formel für mögliche 6 Treffer erweitert und in der Tat, die Formel ist inzwischen ellenlang geworden.

Gruß
HN


 

Beiträge aus den Excel-Beispielen zum Thema "Mehrere Werte in einer Zelle Ausgeben."