Zeichenkette in anderer Zeichenkette suchen + markieren

Moderator: ModerationP

Zeichenkette in anderer Zeichenkette suchen + markieren

Beitragvon EXCEL-Noob_hoch_10 » 28. Sep 2021, 04:39

Hallo liebe Forengemeinschaft.

Ich bin leider noch nicht besonders in der Anwendung von Formeln in EXCEL bewandert, die über völlig triviale Funktionen hinausgehen. Aktuell sehe ich mich jedoch mit etwas konfrontiert, was für mich als Anfänger noch zu kompliziert wirkt, als dass ich es in der Zeit, die mir dafür zur Verfügung steht, selbst herausarbeiten kann, obwohl ich das natürlich gerne möchte.

  • Ich habe ein EXCEL-Dokument mit vier Tabellenblättern.
  • Das erste Tabellenblatt enthält eine Tabelle mir einer Spalte, in welcher eine Auflistung von Buchstaben-Zahlen-Kombinationen im Format wie zum Beispiel „ABCD012345.1“ oder „ABCD543210.2“ oder „ABCD987654.32“ aufgereiht ist.
  • In den anderen drei Tabellenblättern befindet sich eine Tabelle mit jeweils einer Spalte, welche eine riesige Anzahl solcher Buchstaben-Zahlen-Kombinationen – jedoch OHNE „.x“ bzw. „.xy“ – samt weiterer Beschriftung enthalten; also zum Beispiel „ABCD012345 / Kreativer Inhalt – usw.“ oder „ABCD543210 / Kreativer Inhalt – usw.“.
  • Jetzt muss ich mir in diesen drei Tabellenblättern alle Zellen farbig markieren lassen, in welchen eine Übereinstimmung mit einer der Buchstaben-Zahlen-Kombinationen – jedoch OHNE „.x“ bzw. „.xy“, also NUR mit den ersten 10 Zeichen – aus der Spalte des ersten Tabellenblattes erfasst wird.

In diesem Sinne habe ich schon mein Glück mit „Bedingte Formatierung“ und „SVERWEIS“ versucht, doch bin offensichtlich noch nicht erfolgreich gewesen, da ich mich damit schwertue, die Formel so anzupassen, dass nur ein Teil der Zeichenkette im einer anderen Zeichenkette gesucht wird.

Ich würde mich wirklich sehr freuen, wenn ihr Excel-Profis auch mir mit Hilfsbereitschaft begegnen würde. Vielen lieben Dank schon mal.
EXCEL-Noob_hoch_10
 

Re: Zeichenkette in anderer Zeichenkette suchen + markieren

Beitragvon steve1da » 28. Sep 2021, 06:12

Hola,

ich gehe davon aus dass in der ersten Tabelle die Werte ab A2 beginnen, ebenso in den anderen Tabellen. Also ggfls anpassen.
Als Formel in der bedingten Formatierung auf jedem der 3 Blätter:
Code: Alles auswählen
=SUMMENPRODUKT((LINKS(Tabelle1!$A$2:$A$10;10)=LINKS(A2;10))*1)
Gruß,
steve1da
Benutzeravatar
steve1da
Moderator
 
Beiträge: 31550
Registriert: 24. Sep 2003, 10:54

Re: Zeichenkette in anderer Zeichenkette suchen + markieren

Beitragvon slowboarder » 28. Sep 2021, 07:29

Hi

Bedingte Formatierung mit folgender Formel als Regel (A1 ist die aktive Zelle, bzw erste Zelle des Bereichs, für den du die bed. Form. erstellst.

=ZählenWenn(Tabelle1!$A:$A;Links(A1;10)&"*")

Oder

=Vergleich(Links(A1;10)&"*";Tabelle1!$A:$A;0)

Vom Verständnis muss man wissen, dass Excel jede Zahl ungleich Null als WAHR interpretiert.
Sollte es ein Performanceproblem geben, ist das zweite etws s schneller als das erste.

Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 28445
Registriert: 18. Apr 2009, 13:33

Re: Zeichenkette in anderer Zeichenkette suchen + markieren

Beitragvon Gast » 28. Sep 2021, 08:10

Danke schon mal für die erste Antwort.

Ich bin mir noch nicht so ganz schlüssig, warum die SUMMENPRODUKT-Formel zum Einsatz kommen soll - aber ich habe sie bisher meines Wissens nach auch noch nicht benutzt.

Noch mal zur Sicherheit:
Das erste Tabellenblatt enthält eine Tabelle mit einer Spalte, in welcher zeilenweise nur die einzelnen Buchstaben-Zahlen-Kombinationen (z.B. „ABCD543210.2“ oder „ABCD987654.32“) aufgelistet sind.
Nach diesen Buchstaben-Zahlen-Kombinationen gilt es nun in drei anderen Tabellenblättern zu suchen. Dort tauchen diese Buchstaben-Zahlen-Kombinationen allerdings nur in 10-stelliger Form auf und stehen mit Worten und anderen Zeichen gemeinsam in jeweils einer Zelle.
Wenn also im Tabellenblatt 2-4 Zellen gefunden werden, die eine der 10-stelligen Buchstaben-Zahlen-Kombinationen (z.B. „ABCD012345 / Kreativer Inhalt – usw.“ oder „ABCD543210 / Kreativer Inhalt – usw.“) aus der Tabellenspalte von Tabellenblatt 1 beinhaltet, soll diese farblich markiert werden.

Auf Tabellenblatt 1 sind die Buchstaben-Zahlen-Kombinationen von B3 bis B83 aufgelistet.
Diese sollen nun in den Zeichenketten von beispielsweise Tabellenblatt 2 in den Zellen A68 bis A7028 gesucht werden.
Die Zelle auf Tabellenblatt 2, in der eine der Buchstaben-Zahlen-Kombinationen von Tabellenblatt 1 ausfindig gemacht wurde, soll farblich markiert werden.

Dann navigiere ich also auf Tabellenblatt 2 (bzw. Tabellenblatt 3 oder 4) und wähle "Bedingte Formatierung" --> "neue Regel" --> "Formel zur Ermittlung der zu formatierenden Zellen verwenden" und schreibe den Beginn der Formel =SUMMENPRODUKT...
Ich ergänze um zwei "((" und klicke unten auf das Tabellenblatt 1 --> =SUMMENPRODUKT(('Blatt 1'!
Ich wähle auf Tabellenblatt 1 die Zellen B3 bis B83 aus, ergänze ";10", um auf die ersten zehn Stellen zu begrenzen, und schließe mit ")=" --> =SUMMENPRODUKT(('Blatt 1'!$B$3:$B$83;10)=
Nun klicke ich unten wieder auf Tabellenblatt 2 und die Formel ergänzt sich zu --> =SUMMENPRODUKT(('Blatt 1'!$B$3:$B$83;10)='Blatt 2'!
Ich öffne mit "(" und wähle auf Tabellenblatt 2 die Zellen A68 bis A7028 aus, sodass sich die Formel wie folgt ergänzt --> =SUMMENPRODUKT(('Blatt 1'!$B$3:$B$B83;10)='Blatt 2'!($A$68:$A$7028
Ich schließe mit "))" und ergänze "*1" sowie eine weitere ")" --> =SUMMENPRODUKT(('Blatt 1'!$B$3:$B$B83;10)='Blatt 2'!($A$68:$A$7028))*1)
Danach wähle ich unter "Formatieren" eine entsprechende Farbfüllung... Wähle "OK" und bekomme "Mit dieser Formel gibt es ein Problem".

Ich habe also leider das Problem mit diesem Fehler, habe leider noch nicht verstanden, warum die SUMMENPRODUKT-Formel zum Einsatz kommen soll, und habe leider die Funktion des "*1" nicht so ganz einordnen können.
Natürlich würde ich mich weiterhin über Hilfe freuen und bin sehr dankbar für eure Zeit und Mühe.
Gast
 

Re: Zeichenkette in anderer Zeichenkette suchen + markieren

Beitragvon steve1da » 28. Sep 2021, 08:27

Du könntest natürlich auch einfach das anwenden, was ich geschrieben habe. Mein ursprünglicher Vorschlag war:
Code: Alles auswählen
=SUMMENPRODUKT((LINKS(Tabelle1!$A$2:$A$10;10)=LINKS(A2;10))*1)
Du machst daraus:
Code: Alles auswählen
=SUMMENPRODUKT(('Blatt 1'!$B$3:$B$B83;10)='Blatt 2'!($A$68:$A$7028))*1)
Warum? Korrekt angewendet würde es lauten:
Code: Alles auswählen
=SUMMENPRODUKT((LINKS('Blatt 1'!B3:B33;10)=LINKS(A68;10))*1)

Du hättest einfach aus "Tabelle1" den richtigen Blattnamen machen müssen.
Benutzeravatar
steve1da
Moderator
 
Beiträge: 31550
Registriert: 24. Sep 2003, 10:54

Re: Zeichenkette in anderer Zeichenkette suchen + markieren

Beitragvon slowboarder » 28. Sep 2021, 08:27

Sumnenprodukt wird gerne als Alternative zu Zählen- oder SummeWenn(s) verwendet, weil es mehr Möglichkeiten bietet, die Bedingungsprüfung zu gestalten, während die -Wenn(s)-Funktionen da etwas limitiert sind.
Das *1 braucht man hier beim Sumnenprodukt, weil die Bedingungsprüfung LINKS(Tabelle1!$A$2:$A$10;10)=LINKS(A2;10) allein zunächsteinmal nur WAHR oder FALSCH erzeugt, also Wahrheitswerte, die Summenprodukt aber ignoriert, da es nur mit Zahlen rechnet.
Man muss also das WAHR / FALSCH erst noch in 1 und 0 wandeln und das passiert bei der Multiplikation.

Die Frage an dich wäre, warum du die LINKS-Funktion von Steve ignoriert hast.


Naja, du hast ja neben Summenprodukt auch andere Lösungsvorschläge erhalten, die du auch mal ausprobieren kannst.

Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 28445
Registriert: 18. Apr 2009, 13:33

Re: Zeichenkette in anderer Zeichenkette suchen + markieren

Beitragvon Gast » 28. Sep 2021, 08:31

Dankeschön auch für die zweite Antwort samt Hilfsbereitschaft.

=ZÄHLENWENN('Blatt 1'!$B$3:$B$B83;'Blatt 2'!($A$68:$A$7028)&"*")
Erzeugt bei mir leider auch eine Fehlermeldung.
Es tut mir leid, wenn ich hier aufgrund meiner leider vorhandenen Unkenntnis triviale Fragen stelle oder offensichtliche Fehler nicht selbst erkenne.
Gast
 

Re: Zeichenkette in anderer Zeichenkette suchen + markieren

Beitragvon Gast » 28. Sep 2021, 08:32

Ich bitte um Verzeihung. "LINKS" hatte ich als Platzhalter für den Link zum jeweiligen Tabellenblatt fehlinterpretiert.
Gast
 

Re: Zeichenkette in anderer Zeichenkette suchen + markieren

Beitragvon slowboarder » 28. Sep 2021, 08:52

Gast hat geschrieben:Dankeschön auch für die zweite Antwort samt Hilfsbereitschaft.

=ZÄHLENWENN('Blatt 1'!$B$3:$B$B83;'Blatt 2'!($A$68:$A$7028)&"*")
Erzeugt bei mir leider auch eine Fehlermeldung.
Es tut mir leid, wenn ich hier aufgrund meiner leider vorhandenen Unkenntnis triviale Fragen stelle oder offensichtliche Fehler nicht selbst erkenne.

ja, weil du dich nicht an das hälst, was dir gezeigt wird, sonderen wild irgendwelche Dinge ausprobierst.
erkläre mal bitte, warum du beim zweiten Zellbezug die Zelladresse in Klammern setzt und beim zweiten nicht.
Das hast du dir ausgedacht, daher solltest du auch erklären können, warum du das gemacht hast.

Aktzeptiere bitte, dass ein Forum nicht unbedingt das geeigneste Medium für Menschen mit kompletter Unkenntnis ist, sondern eher für Menschen mit Grundkenntnissen gedacht ist.
Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 28445
Registriert: 18. Apr 2009, 13:33

Re: Zeichenkette in anderer Zeichenkette suchen + markieren

Beitragvon Gast » 04. Okt 2021, 05:45

Ich kann mich nur nochmals für meine Unkenntnis entschuldigen, wenngleich ich mich nach wie vor über Unterstützung freuen würde.

Ich habe es natürlich mit euren Code-Zeilen versucht, die ich zuvor vom Bildschirm ein anderes Gerät abgeschrieben und dabei auch Fehler gemacht habe - zusätzlich zu Tatsache, dass ich die "LINKS"-Funktion schlicht und einfach fehlinterpretiert habe.

Jedenfalls habe ich mein Glück mit...
=ZählenWenn(Tabelle1!$A:$A;Links(A1;10)&"*") ---> angepasst zu =ZÄHLENWENN('Tabellenblatt 1'!$B1:$B83;LINKS(A1;10)&"*")
... und...
=Vergleich(Links(A1;10)&"*";Tabelle1!$A:$A;0) ---> angepasst zu =Vergleich(Links(A1;10)&"*";'Tabelle 1'!$B1:$B83;0)
... und...
=SUMMENPRODUKT((LINKS('Blatt 1'!B3:B33;10)=LINKS(A68;10))*1) ---> angepasst zu =SUMMENPRODUKT((LINKS('Tabellenblatt 1'!B1:B83;10)=LINKS(A1;10))*1)
... versucht, in dem ich Tabellenblatt 2 aufgerufen und die Formel unter "Bedingte Formatierung" eingefügt und mit der farblichen Zellfüllung kombiniert habe. Nur leider wird nichts eingefärbt.

Hier mal ein Link zu einem beispielhaften Dokument:
https://u.pcloud.link/publink/show?code ... EOVYx02yY7

Wie gesagt, ich bin euch wirklich sehr dankbar, dass ihr Hilfe anbietet. Man steht halt leider ganz schön auf dem Schlauch, wenn man damit nicht so häufig arbeitet und außer einfachen Rechnungen auch noch nicht wirklich mit den anderen Sachen bewandert ist.
Gast
 

Re: Zeichenkette in anderer Zeichenkette suchen + markieren

Beitragvon shift-del » 04. Okt 2021, 08:08

Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Benutzeravatar
shift-del
Moderator
 
Beiträge: 21675
Registriert: 25. Jan 2009, 22:54
Wohnort: Ehemaliges Fabrikgelände

Re: Zeichenkette in anderer Zeichenkette suchen + markieren

Beitragvon slowboarder » 04. Okt 2021, 08:58

Hier oder dort, das ist jetzt die Frage.
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 28445
Registriert: 18. Apr 2009, 13:33


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 22 Gäste