Microsoft Excel

Herbers Excel/VBA-Archiv

"like" für Excel

Betrifft: "like" für Excel von: Franz W.
Geschrieben am: 19.12.2012 14:59:37

Hallo Fachleute,

in VBA gibt es mit "like" die Möglichkeit, einen Teilstring abzufragen:

If Target Like "*test*" Then

Gibt es so etwas auch für eine Excelformel:

wenn(SVERWEIS(AB19;Sonstiges;2;0) wie "*test*"

Ich hab verschiedenes ausprobiert, es geht aber nichts. Drum bitte ich um Eure Hilfe.

Danke schonmal und Grüße
Franz

  

Betrifft: AW: "like" für Excel von: ChrisL
Geschrieben am: 19.12.2012 15:05:00

Hi Franz

=WENN(ISTFEHLER(FINDEN("test";"Dein test string"));"keine Übereinstimmung";"OK")

"Dein test string" durch die Formel ersetzen.

Gruss
Chris


  

Betrifft: AW: "like" für Excel von: Franz W.
Geschrieben am: 19.12.2012 15:22:16

Hallo Chris,

vielen Dank schonmal, jetzt weiß ich's schon mal grundsätzlich.


Aber ganz krieg ich's noch nicht hin, folgende Formel müsste doch eigentlich stimmen:

=WENN(UND(NICHT(ISTNV(SVERWEIS(AB19;Sonstiges;2;0))); NICHT(ISTFEHLER(FINDEN("fra"; SVERWEIS(AB19;Sonstiges;2;0)))) );SVERWEIS(AB19;Sonstiges;2;0);"keine Übereinstimmung")


Es kommt aber "keine Ü."


Grüße
Franz


  

Betrifft: AW: "like" für Excel von: Daniel
Geschrieben am: 19.12.2012 15:14:02

Hi

klar gibts das, zumindest für die einfacheren Like-Variationen, heißt halt nur ein bisschen anders:

Wenn(IstZahl(Finden("test";SVERWEIS(AB19;Sonstiges;2;0)));...;...)
Wenn(IstZahl(Suchen("test";SVERWEIS(AB19;Sonstiges;2;0)));...;...)

wobei FINDEN bei Gross- und Kleinschreibung unterscheidet, während das bei SUCHEN egal ist.
Gruß Daniel


  

Betrifft: AW: "like" für Excel von: Franz W.
Geschrieben am: 19.12.2012 15:24:42

Hallo Daniel,

auch Dir danke! Aber auch mit Deinen Angeboten krieg ich's noch nicht hin. Ich hab folgende Formel:

=WENN(UND(NICHT(ISTNV(SVERWEIS(AB19;Sonstiges;2;0))); ISTZAHL(FINDEN("Fra";SVERWEIS(AB19; Sonstiges;2;0))) );SVERWEIS(AB19;Sonstiges;2;0);"keine Übereinstimmung")


auch hiermit findet er nichts...???


Fragende Grüße
Franz


  

Betrifft: @Chris + Daniel: Fehlerursache von: Franz W.
Geschrieben am: 19.12.2012 15:33:09

ich hab was gefunden:

bei AB19 handelt es sich um ein Datum. In der Liste "Sonstiges" gibt es an diesem Datum mehrere Einträge. Er soll genau nur den einen Eintrag raussuchen, der den String "Fra" enthält...

Kann es es sein, dass er nach dem ersten Eintrag bei diesem Datum nicht mehr weitersucht? Falls ja, gibt es weiterhin eine Möglichkeit für eine Excelformel. Denn die Liste per VBA absuchen zu lassen hab ich schon. Nun wollte ich eben nach Möglichkeit, dass dieser Eintrag ohne Makro gleich drinsteht.


Grüße
Franz


  

Betrifft: AW: @Chris + Daniel: Fehlerursache von: ChrisL
Geschrieben am: 19.12.2012 16:01:00

Hi Franz

Sverweis gibt grundsätzlich immer nur ein Ergebnis zurück.

Schau mal hier:
http://www.excelformeln.de/formeln.html
(Hinweis zu Matrix-Formeln beachten)

Vielleicht findest du etwas passendes z.B.
http://www.excelformeln.de/formeln.html?welcher=37

cu
Chris


  

Betrifft: AW: @Chris + Daniel: Fehlerursache von: Franz W.
Geschrieben am: 19.12.2012 16:11:14

Hallo Chris,

danke, werd mich mal damit beschäftigen, mal schaun......


Grüße
Franz


  

Betrifft: =ZÄHLENWENN(A1:A100;"*Test*") _oT von: NoNet
Geschrieben am: 19.12.2012 16:00:35

_oT = "ohne Text"


  

Betrifft: AW: =ZÄHLENWENN(A1:A100;"*Test*") _oT von: Franz W.
Geschrieben am: 19.12.2012 16:12:04

Hallo NoNet,

warum zählen? Wie kann mir das weiterhelfen?


Grüße
Franz


  

Betrifft: ...weil das m.diesem Like-Vglstext fktt und ... von: Luc:-?
Geschrieben am: 19.12.2012 17:50:58

…außerdem jedes Auftreten berücksichtigt (zählt), Franz!
Inwiefern dir das bei deinem eigentl Problem hilft, kannst ja nur du wissen, da wir es nicht kennen.
Gruß Luc :-?


  

Betrifft: AW: ...weil das m.diesem Like-Vglstext fktt und ... von: Franz W.
Geschrieben am: 19.12.2012 18:30:00

Hallo Luc,

danke, ich werd versuchen, ob ich dahinterkomm, wie ich das integrieren kann:

Kurz mein Problem geschildert:

- ein zweispaltiger Bereich "Sonstiges",
erste Spalte = Datum -- zweite Spalte = ein Eintrag

- jedes Datum kann mehr als 1 Mal vorkommen, jeweils mit unterschiedlichen Einträgen
Beispiel:
17.12.12 Müll und Schrott
17.12.12 Schrott und Müll
17.12.12 test und Speiseeis

- auf einem anderen Blatt ist ein Kalender, der an jedem Tag genau 1 Zelle hat, also genau 1 Eintrag zulässt. Und das soll der mit dem String "test" sein. In diesem Kalender soll also am 17.12. "test und Speiseeis" stehen.


Und genau da scheinen die beiden Vorschläge von Chris und Daniel zu scheitern, wenn ich das richtig sehe. Die hören scheinbar nach dem ersten gefundenen Eintrag am 17.12. = "Müll und Schrott" auf zu suchen und finden den richtigen gar nicht.

So, jetzt werd ich mal grübeln, wie ich da Zählenwenn reinbringen kann.


Danke erstmal und Grüße
Franz


  

Betrifft: AW: ...weil das m.diesem Like-Vglstext fktt und ... von: Franz W.
Geschrieben am: 19.12.2012 21:23:44

Hallo Fachleute,

vielleicht darf ich Euch nochmal um Eure Hilfe bitten, ich weiß gar nicht, wie ich mit den angebotenen Hilfen weiterkommen kann:


hier nochmal die Beschreibung des Problems:

- ein zweispaltiger Bereich "Sonstiges",
erste Spalte = Datum -- zweite Spalte = ein Eintrag

- jedes Datum kann mehr als 1 Mal vorkommen, jeweils mit unterschiedlichen Einträgen
Beispiel:
17.12.12 Müll und Schrott
17.12.12 Schrott und Müll
17.12.12 fra und Speiseeis

- auf einem anderen Blatt ist ein Kalender, der an jedem Tag genau 1 Zelle hat, also genau 1 Eintrag zulässt. Und das soll der mit dem String "test" sein. In diesem Kalender soll also am 17.12. "fra und Speiseeis" stehen.


ich habe folgende Formel, die nicht das gewünschte Ergebnis bringt, wenn in der Liste "Sonstiges" vor einem Eintrag mit "fra" noch ein anderer Eintrag ohne "fra" steht:

=WENN(ISTZAHL(FINDEN("Fra"; SVERWEIS(AB19;Sonstiges;2;0)));SVERWEIS(AB19;Sonstiges;2;0);"")

dazu hat mir NoNet folgende Formel angeboten, die in der Liste "Sonstiges" alle Einträge mit "fra" zählt
=ZÄHLENWENN(Sonstiges;"*fra*")
Aber ich hab einfach keine Idee, wie ich das jetzt in obiger Formel einsetzen kann, hab sowas noch nie gemacht... Drum bitte nochmal um Eure Hilfe


Grüße
Franz


  

Betrifft: AW: ...weil das m.diesem Like-Vglstext fktt und ... von: fcs
Geschrieben am: 20.12.2012 01:30:45

Hallo Namensvetter Franz,

mit folgenden Formeln kannst du den Inhalt der 1.Zeile in der das Datum und der Teiltext übereinstimmen in den Kalender übernehmen.

Diese Formeln sind sehr rechenintensiv. Wenn der Zellbereich "Sonstiges" sehr viele Zeilen hat dann wird deine Geduld schon gefordert.

Du solltest die beiden Spaltenbereiche der Daten jeweils mit einem eigenen Namen benennen, dann wird die Formel etwas übersichtlicher. Vergleiche Formeln in B3 und D3.

Gruß
Franz

Tage

 ABCD
1Excel-Versionalleab 2007alle
2DatumTestTesttest
317.12.2012test und Speiseeistest und Speiseeistest und Speiseeis

Formeln der Tabelle
ZelleFormel
B3{=WENN(ISTNV(VERGLEICH(TEXT(A3;"JJJJ-MM-TT") & "*" &B$2& "*"; TEXT(Sonstiges_Datum;"JJJJ-MM-TT") & Sonstiges_Text;0)); "";INDEX(Sonstiges_Text;VERGLEICH(TEXT(A3;"JJJJ-MM-TT") & "*" &B$2& "*"; TEXT(Sonstiges_Datum;"JJJJ-MM-TT") & Sonstiges_Text;0)))}
C3{=WENNFEHLER(INDEX(Sonstiges_Text;VERGLEICH(TEXT(A3;"JJJJ-MM-TT") & "*" &C$2& "*"; TEXT(Sonstiges_Datum;"JJJJ-MM-TT") & Sonstiges_Text;0)); "")}
D3{=WENN(ISTNV(VERGLEICH(TEXT(A3;"JJJJ-MM-TT") & "*" &D$2& "*"; TEXT(BEREICH.VERSCHIEBEN(Sonstiges;0;0;;1); "JJJJ-MM-TT") & BEREICH.VERSCHIEBEN(Sonstiges;0;1;;1); 0)); "";INDEX(Sonstiges;VERGLEICH(TEXT(A3;"JJJJ-MM-TT") & "*" &D$2& "*"; TEXT(BEREICH.VERSCHIEBEN(Sonstiges;0;0;;1); "JJJJ-MM-TT") & BEREICH.VERSCHIEBEN(Sonstiges;0;1;;1); 0); 2))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Namen in Formeln
ZelleNameBezieht sich auf
B3Sonstiges_Datum=Tabelle1!$A$2:$A$10
B3Sonstiges_Text=Tabelle1!$B$2:$B$10
C3Sonstiges_Datum=Tabelle1!$A$2:$A$10
C3Sonstiges_Text=Tabelle1!$B$2:$B$10
D3Sonstiges=Tabelle1!$A$2:$B$10
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


  

Betrifft: AW: ...weil das m.diesem Like-Vglstext fktt und ... von: Franz W.
Geschrieben am: 20.12.2012 08:52:12

Guten Morgen Franz,

vielen Dank für Deine nächtlichen Anstrengungen, das ist wirklich toll. Und vor allem, es funktioniert (hab Dich auch früher schon als Formelprofi kennengelernt).

Das mit dem Trennen der Namen war kein PRoblem, es gab für die beiden Spalten aus anderen Gründen schon eigene Namen. Den Rest werd ich erst mal verstehen müssen, da gibt es Anweisungen, die ich noch nicht benutzt habe. Und dann als Matrixformel (da bietest Du ja dankenswerterweise einen Lernlink an).

Ob ich das auch ein bisschen verstehen kann, werd ich jetzt versuchen. Denn es gibt noch eine kleine Erweiterung: Findet sich zu einem bestimmten Datum kein Eintrag im Bereich "Sonstiges", dann soll in den Kalender aus einer Feiertagsliste der jeweilige Feiertag übernommen werden. Ich hab das mit meiner vorigen SVerweis-Variante schon mal gelöst, und muss jetzt versuchen, das in die Matrixformel reinzubringen.


Danke erstmal und Grüße
Franz


  

Betrifft: Teilerfolg ... von: Franz W.
Geschrieben am: 20.12.2012 09:07:14

Hallo Franz,

der erste Schritt ist geschafft, war ja gar nicht so schlimm :-))), konnte meine alte SVERWEIS-Formel einsetzen:

{=WENN(ISTNV(VERGLEICH(TEXT(AB3;"JJJJ-MM-TT") & "*" &B$2& "*"; TEXT(SonstigesDatum;"JJJJ-MM-TT") _

 & SonstigesFakt;0)); SVERWEIS(AB3;FeiertageKomp;2;0);INDEX(SonstigesFakt;VERGLEICH(TEXT(AB3;" _
JJJJ-
MM-TT") & "*" &B$2& "*"; TEXT(SonstigesDatum;"JJJJ-MM-TT") & SonstigesFakt;0)))}

Allerdings gibt es jetzt ein "#NV", wenn weder in "Sonstiges" noch in der Feiertagsliste was gefunden wird. Ich probiere weiter.....



Grüße
Franz


  

Betrifft: Weiterer Teilerfolg ... von: Franz W.
Geschrieben am: 20.12.2012 10:34:42

Hallo Franz,

langsam komme ich weiter, auch das #NV-Ding ist gelöst:

=WENN(ISTNV(VERGLEICH(TEXT(AB19;"JJJJ-MM-TT") & "*" &$B$2& "*"; TEXT(SonstigesDatum;"JJJJ-MM-TT" _
)
 & SonstigesFakt;0));WENN(NICHT(ISTNV(SVERWEIS(AB19;FeiertageKomp;2;0)));
SVERWEIS(AB19;FeiertageKomp;2;0);"");INDEX(SonstigesFakt;VERGLEICH(TEXT(AB19;"JJJJ-MM-TT") & "*" &$B$2& "*"; TEXT(SonstigesDatum;"JJJJ-MM-TT") & SonstigesFakt;0)))

Allerdings stoß ich jetzt auf was anderes, was mich verwirrt: kann es sein, dass die Formel mit der Länge des Eintrages Schwierigkeiten hat?

Folgender Eintrag:

Dojo Gedichte
(Fra)
(wir)

... wird gefunden.



Dieser Eintrag hier:

Dojo Gedichte
(Fra)
(wir)
(dojo)
(Hann)

... wird nicht gefunden.

Dazu muss ich sagen, dass aus kosmetischen Gründen am Ende jeder Zeile eine Menge Leerzeichen stehen (die Zelle im Kalender hat die Höhe für nur 1 Zeile, es soll nur die erste Zeile lesbar sein, nicht die verschiedenen Unterscheidungsmerkmale). Kann es sein, dass es damit Probleme gibt? Das müsste ich noch lösen können, sonst kann ich das Ganze gar nicht einsetzen.



Fragende Grüße
Franz


  

Betrifft: AW: Weiterer Teilerfolg ... von: Franz W.
Geschrieben am: 20.12.2012 13:00:05

Hallo Franz,

so, das Problem konnte ich jetzt umgehen, in dem ich die Einträge in "Sonstiges" mit den entsprechenden Markierungen anders sprich kürzer gestalte.


Aber es bleibt die Frage, ob die Länge eines Eintrages Einfluss auf Deine Formel hat.


Grüße
Franz


  

Betrifft: AW: Weiterer Teilerfolg ... von: fcs
Geschrieben am: 20.12.2012 23:16:00

Hallo Franz,

diese Suche per Matrixformel funktioniert mit bis zu 255 Zeichen, je Matrixfeld.
Wenn dein gesuchter Teiltext immer innerhalb der ersten 245 Zeichen enthalten ist, dann funktioniert noch nachfolgende Formelvariante als Matrixformel. Ein paar Zeichen mehr kannst du noch erfassen, wenn du das Datumsformat auf "JJJJMMTT" oder "JJMMTT" änderst.

=WENN(ISTNV(VERGLEICH(TEXT(A3;"JJJJ-MM-TT") & "*" &B$2& "*"; LINKS(TEXT(Sonstiges_Datum; 
"JJJJ-MM-TT") & Sonstiges_Text;255);0));"";INDEX(Sonstiges_Text; 
VERGLEICH(TEXT(A3;"JJJJ-MM-TT") & "*" &B$2& "*"; LINKS(TEXT(Sonstiges_Datum;"JJJJ-MM-TT") 
& Sonstiges_Text;255);0)))

Falls der Suchbegriff auch nach dem 255. Zeichen stehen kann, dann benötigst du in der Datentabelle eine Hilfsspalte, in der geprüft wird, ob der im Kalender gesuchte Begriff in der Text-Spalte vorhanden ist.

Hierzu eine kleine Beispieldatei.
https://www.herber.de/bbs/user/83101.xls

Gruß
Franz


  

Betrifft: AW: Weiterer Teilerfolg ... von: Franz W.
Geschrieben am: 21.12.2012 06:11:55

Hallo Franz,

super, vielen Dank! Das erklärt mir das. Deine neue Formel werd ich mal einsetzen. Auf jeden Fall weiß ich jetzt, worauf ich achten muss.

Nachdem ich Deine Matrix-Formel im Einsatz habe, weiß ich auch, was Du meinst mit der Geduld, die ich haben sollte: nach jedem Eintrag in den Bereich "Sonstiges" braucht es nach dem Bestätigen mit Enter eine Weile, bis man weitermachen kann. Wovon hängt die Länge dieser Weile ab? Von der Größe des Bereichs "Sonstiges", also ob dieser von Zeile 1 - 100 geht oder von Zeile 1 - 10000? Oder hängt's von der Anzahl der Zellen ab, in denen die Matrixformel drinsteht, und die nach jeder Änderung in "Sonstiges" berechnet werden müssen?


Grüße
Franz


  

Betrifft: AW: Weiterer Teilerfolg ... von: fcs
Geschrieben am: 21.12.2012 10:18:39

Hallo Franz,

Wovon hängt die Länge dieser Weile ab?
Beides hat Einfluß. Welcher Part dominant ist kann ich dir nicht sagen. In diese Richtung hab ich noch nicht so viel experimentiert.
Wenn du in deinem Kalender ein Jahr verwaltest, dann ist die Anzahl der Berechnungsformel ja sowieso fix mit ca. 365 je Spalte.

Den Bereich "Sonstiges" solltest du nicht unnötig groß machen, denn hier schlägt jede Zeile extra auf die Rechenzeit durch. Nach meiner Erfahrung (zumindest unter Excel 2007/2010) gibt es bei komplexen Formeln irgendwo zwischen 5000 und 10000 Zeilen in Excel eine Schallmauer ab der -wegen der erforderlichen Auslagerung von Daten- Berechnungen plötzlich extrem ausgebremst werden.

Du solltest während der Dateneingabe im Bereich "Sonstiges" den Berechnungsmodus unter den Optionen von automatisch auf manuell setzen, um die Wartezeiten zu vermeiden. Die Berechnung aktualisierst du dann nur bei Bedarf (Taste F9).

Wenn die Wartezeit zu nervig wird, dann solltest du die Aktualisierung des Kalendes bei Bedarf (z.B. bei Selektion des Kalenderblattes) per Makro machen. Wenn ein Makro ordentlich programmiert ist, dann geht das auch recht flott.

Gruß
Franz


  

Betrifft: AW: Weiterer Teilerfolg ... von: Franz W.
Geschrieben am: 21.12.2012 11:05:18

Hallo Franz,

danke für die Hinweise. Der Bereich "Sonstiges" geht z. Zt. nur bis Zeile 600, da sollte es also noch keine größeren Schwierigkeiten geben. Aber ich hab mal probeweise in dem Kalender die Matrix-Formeln aus nahezu allen betroffenen Zellen raus und sie nur in 10 Feldern drin gelassen: das hat die Rechenzeit drastisch verkürzt. Scheint also eher damit zu tun zu haben.



Zur VBA-Lösung: es gibt außer dem bisher genannten Kalender, nennen wir ihn Kal1, noch einen zweiten Kalender Kal2. Vom Monat abhängig werden in Kal2 Teile von Kal1 benötigt (er ist auch den Anforderungen entsprechend etwas anders gestaltet als Kal1), die ich einfach mit "=" von Kal1 nach Kal2 übernehme (Zelle AD5: "=Kal1!AD5"). Dieser Kal2 wird deutlich häufiger gebraucht als Kal1.

Kal1 hab ich bisher mit einem Makro befüllt, das auch wunderbar und flott genug funktioniert. Wenn es aber nun neue Einträge gibt, die in Kal2 stehen sollen, muss jedes Mal vorher Kal1 aktualisiert werden, und das darf noch dazu nicht vergessen werden. Das ist der Grund, warum ich für Kal1 nach einer Formellösung gesucht hab.



Vielleicht fällt mir ne gänzlich andere Lösung ein. Oder iIch werd auf die Lösung mit Umstellen der Berechnung von automatisch auf manuell vorlieb nehmen.


Vielen Dank, Du hast mir sehr geholfen.
Viele Grüße und schöne Weihnachten
Franz


  

Betrifft: AW: Weiterer Teilerfolg ... von: fcs
Geschrieben am: 21.12.2012 11:31:34

Hallo Franz,

du kannst das Starten von Makros auch mit Ereignissen starten.
Also kannst du z.B. immer wenn das Eingabeblatt verlassen wird das Makro starten, das den Kalender1 aktualisiert. Alternativ immer dann, wenn das Kalenderblatt2 aktiviert wird.

Gruß
Franz


  

Betrifft: AW: Weiterer Teilerfolg ... von: Franz W.
Geschrieben am: 21.12.2012 12:17:25

Hallo Franz,

ja, stimmt, ist auch ne gute Lösung. Mal schaun, wie sich's in der Praxis bewährt. Wenn ich's mir jetzt so überleg, ist das wahrscheinlich die beste Lösung. Denn in "Sonstiges" gibt es ständig Austräge, Einträge, Änderungen. Das Aufrufen von Kal2 ist dagegen sehr viel seltener. Ja gut, dann werd ich wahrscheinlich bei der Makro-Lösung bleiben.


Danke und Grüße
Franz


  

Betrifft: OT: lustiger Betreff von: Klaus M.vdT.
Geschrieben am: 20.12.2012 09:04:39

Hallo Franz,
als ich deinen Betreff gelesen habe, dachte ich es geht um Facebook :-)

Grüße,
Klaus M.vdT.


  

Betrifft: hihi o.t. von: Franz W.
Geschrieben am: 20.12.2012 09:08:32

....


  

Betrifft: MATRICX-Funktion : Datum und Teileintrag suchen von: NoNet
Geschrieben am: 20.12.2012 12:44:10

Hallo Franz,

nach dem Durchlesen der Beiträge habe ich Dein Anliegen erst richtig verstanden. Schau Dir mal diese Lösung an :

ABCDEF
1
2
3
4
5
6
7
8
9
10
11
12
13
14

MATRIX-Funktionen im Tabellenblatt :
ZelleFormel
F2   {=WENN(SUMMENPRODUKT((A1:A999=E2)*ISTZAHL(FINDEN(D2;B1:B999)));INDEX(B:B;MIN(WENN((A2:A999=E2)*ISTZAHL(FINDEN(D2;B2:B999));ZEILE(A2:A999))));"ungültiger Eintrag !") 

Tabelle eingefügt mit Syntaxhighlighter 4.15

Die MATRIX-Funktion in F2 bitte mit Strg+Shift+ENTER bestätigen !

Gruß, NoNet

PS : Extra für Klaus ;-) : I like it...


  

Betrifft: AW: MATRICX-Funktion : Datum und Teileintrag suchen von: Franz W.
Geschrieben am: 20.12.2012 12:57:56

Hallo NoNet,

vielen Dank, komm aber erst später dazu, brauch bissl bis ich's kapier. Aber ich rühr mich wieder.


Danke erstmal und Grüße
Franz