sverweis mit zählenwenn?

Bild

Betrifft: sverweis mit zählenwenn?
von: Denys
Geschrieben am: 21.10.2015 11:58:51

Hallo zusammen,
erstmal möchte ich Euch allen herzlichst danken. Die letzten Jahre hat mir dieses Forum und Eure Hilfsbereitschaft enorm weitergeholfen! Bisher konnte ich sämtliche Probleme über die Suche lösen, habe aber leider zu meinem aktuellen Problem nichts gefunden. Daher möchte ich meinen ersten Beitrag eröffnen!
Gegeben ist eine Zelle die mehrere Wörter enthält (Estoffe: z.b. E110, E120b, E140 etc.)
Dazu soll mir in einer anderen Zelle durch z.B. sverweis die dazugehörige Bedeutung des Wortes ausgegeben werden. Eine Tabelle dafür ist bereits angelegt. E110 wäre ein Farbstoff. Wenn in Zelle A1 also das Wort E110 vorkommt soll in Zelle A2 Farbstoff erscheinen. Soweit eigentlich easy. Es kann allerdings sein, dass E110 mehrfach in der Zelle enthalten ist und auch andere Estoffe bei denen es sich um einen Farbstoff handelt.
Wie kann ich also sicher stellen, dass in Zelle A2 das Wort Farbstoff nur einmal vorkommt und wie kann ich nach ca. 500 verschiedenen Estoffen filtern die alle verkettet in Zelle A1 vorkommen könnten? möglich wäre es mit Zählenwenn und oder... aber die Formel wäre 2 Kilometer lang....
ich hoffe Ihr kennt eine einfache Lösung. Ich hänge mal eine Beispieldatei an.
Vielen Dank vorab
https://www.herber.de/bbs/user/100933.xlsx

Bild

Betrifft: AW: lösbar ist das schon ... aber ...
von: ... neopa C
Geschrieben am: 21.10.2015 12:37:34
Hallo Denys,
... zwischen Deinen Angaben in der Exceldatei und Deinen verbalen Angaben hier sowie auch in Deiner Datei zwischen den Angaben in D8 und D14 (für D18:E26) bestehen teils nicht nachvollziehbare Differenzen bzw. zumindest Schreibfehler.
Hinzu kommt, dass Deine Datenstruktur nicht gerade Excel freundlich ist. Excel bietet viele Mio-Zellen aber bei Dir werden bzw. sollen verschieden Daten (Eingabe in D7 wie Ausgabe in D8) in eine Zelle gepresst werden. Wie geschrieben, auch das wäre lösbar. Ich rate aber trotzdem zu einer anderen Datenstruktur. Dann wird es viel einfacher.
Gruß Werner
.. , - ...


Bild

Betrifft: AW: sverweis mit zählenwenn?
von: Rudi Maintaire
Geschrieben am: 21.10.2015 12:38:05
Hallo,
da fällt mir nur ne UDF ein.
In ein Modul:

Function EStoffe(strStoffe As String, Daten As Range)
  Dim tmp, i As Integer, objStoffe As Object
  Set objStoffe = CreateObject("scripting.dictionary")
  tmp = Split(strStoffe)
  For i = 0 To UBound(tmp)
    objStoffe(Application.VLookup(tmp(i), Daten, 2, 0)) = 0
  Next
  EStoffe = Join(objStoffe.keys, vbLf)
End Function
In der Tabelle: D8 =estoffe(D7;D18:E25)
Gruß
Rudi

Bild

Betrifft: AW: sverweis mit zählenwenn?
von: Daniel
Geschrieben am: 21.10.2015 12:40:14
Hi
eine "einfache" Lösung gibt's da nicht, zumindest nicht mit den Standard-Excelfunktionen.
Excel ist für so eine Textverarbeitung noch nicht ausgelegt (zumindest für den mir bekannten Stand 2010)
mit VBA siehts da etwas anders aus, dh man könnte sich hier relativ einfach eine Funktion programmieren die das macht.
1. kopiere den gezeigten Code in ein allgemeines Modul deiner Datei.

Function fürDenys(Wörter As String, Datenbank As Range) As String
Dim Wort
Dim dicErgebnis As Object
Dim rng As Range
Set dicErgebnis = CreateObject("Scripting.dictionary")
For Each Wort In Split(Wörter, " ")
    Set rng = Datenbank.Columns(1).Find(what:=Wort, lookat:=xlWhole, LookIn:=xlValues)
    If Not rng Is Nothing Then dicErgebnis(rng.Offset(0, 1).Value) = 0
Next
fürDenys = Join(dicErgebnis.keys, vbLf)
End Function
dir steht dann in dieser Mappe die Funktion fürDenys zur verfügung, welche du wie eine normale Excelfunktion nutzen kannst.
dh in die Zelle D8 kommt die Formel:
=fürDenys(D7;D18:E26)

der erste Parameter ist die Zelle mit den Texten, der zweite Parameter ist der Zellbezug zur Datenbank mit allen E-Nummern und deren Text in der zweiten Spalte.
Gruß Daniel

Bild

Betrifft: AW: sverweis mit zählenwenn?
von: Daniel
Geschrieben am: 21.10.2015 12:40:15
Hi
eine "einfache" Lösung gibt's da nicht, zumindest nicht mit den Standard-Excelfunktionen.
Excel ist für so eine Textverarbeitung noch nicht ausgelegt (zumindest für den mir bekannten Stand 2010)
mit VBA siehts da etwas anders aus, dh man könnte sich hier relativ einfach eine Funktion programmieren die das macht.
1. kopiere den gezeigten Code in ein allgemeines Modul deiner Datei.

Function fürDenys(Wörter As String, Datenbank As Range) As String
Dim Wort
Dim dicErgebnis As Object
Dim rng As Range
Set dicErgebnis = CreateObject("Scripting.dictionary")
For Each Wort In Split(Wörter, " ")
    Set rng = Datenbank.Columns(1).Find(what:=Wort, lookat:=xlWhole, LookIn:=xlValues)
    If Not rng Is Nothing Then dicErgebnis(rng.Offset(0, 1).Value) = 0
Next
fürDenys = Join(dicErgebnis.keys, vbLf)
End Function
dir steht dann in dieser Mappe die Funktion fürDenys zur verfügung, welche du wie eine normale Excelfunktion nutzen kannst.
dh in die Zelle D8 kommt die Formel:
=fürDenys(D7;D18:E26)

der erste Parameter ist die Zelle mit den Texten, der zweite Parameter ist der Zellbezug zur Datenbank mit allen E-Nummern und deren Text in der zweiten Spalte.
Gruß Daniel

Bild

Betrifft: AW: sverweis mit zählenwenn?
von: Denys
Geschrieben am: 21.10.2015 12:59:56
Hey,
jap es klappt. Super vielen, vielen Dank Daniel!

Bild

Betrifft: Neben der doch ziemlich speziellen UDF ...
von: Luc:-?
Geschrieben am: 22.10.2015 03:27:46
…von Daniel, auf deren einseitigen Charakter schon ihr Name hinweist, Denys,
könntest du auch eine ZellFml mit den von mir bereits des Öfteren vorgestellten und im Archiv (→RECHERCHE) zu findenden allgemeinen UDFs VSplit und VJoin verwenden, die die beiden in Daniels UDF angewendeten vbFktt Split und Join separat verwenden und so auch in anderen Anwendungsfällen zum Einsatz gelangen können:
D8:{=VJoin(T(INDIREKT("E"&ZEILE(E17)+VERGLEICH(MTRANS(VSplit(D7));D18:D26;0)));ZEICHEN(10);-1)}
Die aktuelle Version von VJoin ist 1.4 und in mehreren hochgeladenen BspDateien zu finden.
Gruß, Luc :-?

PS-Mitteilung für Interessenten:
Habe bisher noch keine Zeit gefunden, die neue Version einer ZellAuswahl-UDF (erzeugt ZellBezüge, auch unzusammenhängend) fertigzustellen. In folgender Fml wird ihr unpublizierter Vorgänger ChooseIn und außerdem weitere UDFs verwendet, um zu zeigen, dass man dieses Problem auch etwas anders lösen könnte:
E8:{=VJoin(Compute(RinMxList(ChooseIn(E18:E26;D18:D26;D7;0);-2));ZEICHEN(10);-1)}


Besser informiert mit …

 Bild

Beiträge aus den Excel-Beispielen zum Thema "sverweis mit zählenwenn?"