Microsoft Excel

Herbers Excel/VBA-Archiv

Wenn Wortteil aus liste dann Wort aus anderer List

Betrifft: Wenn Wortteil aus liste dann Wort aus anderer List von: Georg
Geschrieben am: 24.09.2014 16:50:37

Guten Abend,

Ich habe ein kleines Anliegen. In einer Tabelle werden diverse Nagetiere aufgeführt und ich möchte diesen in einer neuen Spalte die Spezies zu ordnen lassen.

Kann ich dafür eine Formel erstellen die die Liste der Nagetiere mit einer anderen Liste abgleicht und falls Treffer vorliegen die entsprechende Spezies einfügen?

Spalte A soll durchsucht werden ob Wörter aus Spalte (Liste) E vorkommen und wenn ja soll die entsprechende Spezies aus Spalte D in Spalte B eingefügt werden

  

Betrifft: AW: Wenn Wortteil aus liste dann Wort aus anderer List von: Georg
Geschrieben am: 24.09.2014 16:56:26

https://www.herber.de/bbs/user/92798.xls


  

Betrifft: Oder verwende Dropdowns in Abhängigkeit von: Matze Matthias
Geschrieben am: 24.09.2014 17:33:14

Hallo Georg,

hier mal deine Datei etwas anders angeordnet.
Die Dropdowns kann man egal wo anlegen, nur der Aufbau der Datentabelle darf sich nicht ändern.
Momentan wird dies über Namen geregelt und der Formel Indirekt.
https://www.herber.de/bbs/user/92799.xls
Gruß Matze


  

Betrifft: aber etwas anders ... von: neopa C (paneo)
Geschrieben am: 24.09.2014 17:48:42

Hallo Matze,

... bei der Namensdefinition für Spezies ist Dir ein Fehler unterlaufen. Außerdem halte ich diese Art von "dynamische" Dropdowns für nicht besonders geeignet.

Hab jetzt allerdings einen Termin. Im Laufe des morgigen Tages zeig ich Dir eine günstigere Lösung.
Notfalls erinnere mich noch mal daran.

Gruß Werner
.. , - ...



  

Betrifft: Hi Werner, danke... von: Matze Matthias
Geschrieben am: 24.09.2014 19:33:20

für den Fehlerhinweis.
da war ich wohl ein bissel zu schnell mit der Maus um den Bereich richtig zu markieren.

Vermutlich willst du mich darauf hinweisen:
Indirekt sollte nur sehr sparsam angewendet werden und nicht bei rechenintensiven Formeln.
Diese Funktion hat nämlich den großen Nachtteil, dass sie volatil ist, d.h. sie wird immer neuberechntet, nach jeder Eingabe. Selbst dann, wenn sie in einer anderen geöffneten Arbeitsmappe steht!

Wie gesagt, danke.
Gruß Matze


  

Betrifft: für dyn. Dropdown reichen zwei Bereichsnamen ... von: neopa C (paneo)
Geschrieben am: 25.09.2014 16:25:40

Hallo Matze,

... zunächst muss und will ich aber voranstellen, dass nach meiner Interpretation Georgs Fragestellung auf eine andere Lösung zielte. So wie er geschrieben hatte, benötigt er keine Dropdowns.

Und noch eins, nein, ich meinte nicht (nur) Deine Anwendung von INDIREKT().

Da Du aber eine "dyn. Dropdownliste" ins Spiel gebracht hast, konnte ich gestern nicht umhin, meinen Senf dazu zugeben und ich versprach Dir etwas. Und versprochenes halte ich, wenn ich mich denn daran erinnere ;-)

Schon seit längerer Zeit argumentiere ich gegen die meist viel zu statischen und vor allem mE auch zu aufwendig zu erstellenden "dyn. Dropdowns" auf Basis von x-Bereichsnamen und führe dagegen eine mE günstigere Lösungsmöglichkeit ins Feld. Dazu hatte ich bereits in Online Excel Forum und auch hier schon des öfteren geschrieben und auch entsprechende Beispiele aufgezeigt (bin jetzt nur zu bequem diese zu suchen und darauf zu verlinken, zumal diese teils nocht etwas komplizierter waren, wenn ich mich richtig erinnere).

Hier kann und will ich noch einmal aufzeigen, was und wie ich es für derartige Fälle vorschlage. Die drei entscheidenden Formeln sind so aufgestellt, dass ohne notwendige Formeländerungen
- erstens die Dropdownzellen nach kopiert werden können,
- zweitens die "Spezies" in der Datentabelle nach rechts erweitert werden können
(im Beispiel bis Spalte Z)
- drittens eine unterschiedliche Anzahl von Unterspezies möglich sind und diese auch erweitert werden können.

Durch eine zusätzliche bedingte Formatierung fange ich visuell ab, wenn nachträgliche ungewollte Löschungen oder Änderungen bereits getroffener Auswahl von (hier) "Spezies" getroffen wurde.

Auswahl

 CDEF
2    
3 Spezie auswählenUnterspezie 
4 RatteStreifenhörnchen 
5 MausHausmaus 
6  Streifenhörnchen 
7 HörnchenSpitzhörnchen 
8 Hörnchen  
9    

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
D4Liste =Spezies 
E4Liste =UnterSpezie 
Namen in Formeln
ZelleNameBezieht sich auf
D4Spezies=Daten!$B$2:INDEX(Daten!$2:$2;ANZAHL2(Daten!$2:$2)+1)
E4UnterSpezie=INDEX(Daten!$3:$3;VERGLEICH(Auswahl!D4;Daten!$2:$2;)):INDEX(Daten!$A:$Z;SUMME((Daten!$A$3:$Z$99>0)*(Daten!$A$2:$Z$2=Auswahl!D4))+2;VERGLEICH(Auswahl!D4;Daten!$2:$2;))
Namen verstehen


Daten

 ABCDE
1     
2 MausRatteHörnchen 
3 SpitzmausHausratteSpitzhörnchen 
4 Hausmausnorwegische RatteStreifenhörnchen 
5 Haselmaus A-Hörnchen 
6   B-Hörnchen 
7     


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Gruß Werner
.. , - ...


  

Betrifft: AW: aber etwas anders ... von: Matze Matthias
Geschrieben am: 25.09.2014 17:37:29

Hi Werner,
nun erst einmal ein rechtherzliches Danke.

Kann dir kaum widersprechen,..lol werd ich auch nicht, dafür seit ihr die erfahreneren Excelaner.
Es kam mir halt nur schnell in den Sinn dies mit Dropdown umzugestalten.
Und je nach Aufgabenstellung (bei dem bissel was dort angeboten war) reicht doch das Indirekt.

Index und Vergleich ist hochflexibel und habe es in fast allen Arbeitsmappen wo es nötig ist.
Also nochmals, danke für deine Kurzfassung.

Gruß Matze
Ps.: Schade das sich der Fragende zur eigenen Aufgabe nicht zurückmeldet ob ihm die Lösungswege passen.


  

Betrifft: es gibt bereits einen zweiten Thread ... von: neopa C (paneo)
Geschrieben am: 25.09.2014 17:57:42

Hallo Matze,

... und zwar hier: https://www.herber.de/forum/messages/1383346.html

Und nochmal zu INDIREKT() ... wehre den Anfängen ... ;-)

Gruß Werner
.. , - ...


  

Betrifft: z.B. mit VERWEIS() und VERGLEICH() ... von: neopa C (paneo)
Geschrieben am: 24.09.2014 17:27:16

Hallo Georg,

... bei einer leicht veränderten Datenstruktur, wäre es einfacher, als wie nachfolgend aufgezeigt.
Aber ich hab einfach mal eine Lösung exakt für Deine Vorgabe gebastelt.

Schreibe in B2: =VERWEIS(9;1/(D$1:INDEX(D:D;VERGLEICH(A2;E:E;))<>"");D:D) und kopiere diese Formel nach unten.
Wenn ein Nagetier keiner Spezies zuordenbar ist, ergibt sich als Ergebnis ein #NV (könnte man auch etwas abfangen abfangen). Diese Fehlermeldung tritt natürlich auch auf wenn ein Schreibfehler vorliegt.


Gruß Werner
.. , - ...


 

Beiträge aus den Excel-Beispielen zum Thema "Wenn Wortteil aus liste dann Wort aus anderer List"