Office: Mehrere Zeichen ersetzen nach "Vorgabe-Matrix" (kein VBA!)

Hallo Community,

ich habe eine kleine Herausforderung, und hoffe, dass ich hier Hilfe finde.
Gleich vorweg: Ich weiß, dass ich das alles mit einer VBA Programmierung lösen könnte, aber darum geht es mir hier nicht. Ich möchte wissen, ob es eine Möglichkeit gibt, das Ganze mit Excel-Formeln zu lösen.

Nun zur Herausforderung:
In Zelle A1 steht eine Zeichenkette. Die Zeichen dieser Zeichenkette sollen durch andere Zeichen ersetzt werden. Der Schlüssel dafür ist in den Spalten D (Urpsrungszeichen) und E (Ersetzung) gegeben. Mit SVerweis() und Teil() könnte ich ja einzelne Zeichen ansprechen und die Entsprechung finden, aber wie funktioniert das für eine beliebig lange Zeichenkette? Und auch Wechseln() wäre ganz nett, wenn es nicht gerade nur eine Begrenzung für 9 Vorgänge hätte.
Habt ihr eine Idee, wie man das ohne aufwendige Zwischenschritte (also zusätzliche Zellen) lösen kann?

Und dann käme noch eine kleine zusätzliche Frage. Angenommen ich würde alle Zeichen durch Zahlen ersetzen (Spalte F) wollen, und diese dann summieren, könnte ich das ähnlich realisieren?

Die Ergebnisse der Zeichenersetzung bzw. Summenbildung stehen in Spalte B.

Die Ersetzungsmatrix ist meist deutlich größer (30-60 Zeichen) und die Zahlen für die Summen sind dementsprechend auch nicht nur einstellig!

Und hier zur Verdeutlichung die Tabelle.

	A	B	C	D	E	F
1	ABCdef123	xyz456!?_		A	x	1
2		45		B	y	2
3				C	z	3
4				d	4	4
5				e	5	5
6				f	6	6
7				1	!	7
8				2	?	8
9				3	_	9

Excel Tabellen im Web darstellen >>

:)

 

aufwendig, aber ich bin nicht der Formelmensch.

Tabelle5

	A	B	C	D	E	F	G
1	ABCdef123	xyz456!?_	xyz456!?_		A	x	1
2		123456789	45		B	y	2
3	Quersumme	45			C	z	3
4	HTTP://WWW.excelformeln.de/formeln.html?welcher=112				d	4	4
5					e	5	5
6					f	6	6
7					1	!	7
8					2	?	8
9					3	_	9

verbundene Zellen

A4: D4

verwendete Formeln
Zelle	Formel	Bereich	N/A
B1	=WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSEL N($A;$E;$F);$E;$F);$E;$F);$E;$F);$E;$F);$E;$F);$E;$F );$E;$F);$E;$F)
B2	=WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSEL N(A1;E7;G7);E8;G8);E9;G9);E1;G1);E2;G2);E3;G3);E4;G4);E5;G5);E6;G6)
B3	{=SUMME(TEIL(B2;ZEILE(INDIREKT("1:"&LÄNGE(B2)));1)*1)}	$B

{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.01 einschl. 64 Bit

 

xl2003-: 8 Funktionsverschachtelungen in einer Zelle
xl2007+: 64 Funktionsverschachtelungen in einer Zelle (ohne Gewähr)

In beiden "Welten" unbegrenzt" (ohne Gewähr) mit Hilfszellen oder mit kaskadierenden benannten Formeln.

Willst Du A in 1 wechseln und 1 in 14? Das ist einerseits ein Reihenfolgeproblem: Erst 1 tauschen, dann A - weil A wieder 1 wird.

Andererseits ist die 14 als Ersatz höchst gefährlich, da ein späterer Ersatz von 4 sie wieder zerstört.

Es braucht also einen sorgfältig designten Übergang - falls Du nicht NUR von Buchstaben NUR in Buchstaben wechseln möchtest.

 

Hallo,

danke für eure Antworten. Sie haben mich dazu gebracht nochmal etwas genauer nachzudenken.

@Hajo_Zi:
Du baust eine Wechseln() Formel auf. Für den aktuellen Fall ist das ganz nett, aber wenn ich evtl. 32 oder sogar bis zu 64 Ersetzungen habe, dann wird es sehr aufwändig das zu verformeln.
Leider habe ich in dem "Summen"-Beispiel nur einstellige Zahlen. Was aber, wenn die Ersetzungen mit Zahlen >9 arbeiten? Dann funktioniert die Matrix-Formel mit der Summe leider nicht mehr.

@lupo1:
Du hast ein gutes Beispiel gebracht. Wenn A durch B ersetzt würde und B durch C, dann würde A durch C ersetzt werden, wenn es nacheinander durchgeführt wird... das stellt aber leider ein Problem bei jeder Ersetzung dar, nicht wahr?!

Deshalb dachte ich daran, dass es vielleicht "sequentiell" möglich ist zu ersetzen via SVerweis oder so!? Sprich, die "Formel" beginnt beim ersten Zeichen, ersetzt es, und geht zum nächsten, ersetzt es, und geht zum nächsten... mit VB wäre das ja möglich, aber wie gesagt, das wollte ich eigentlich vermeiden.

Scheint aber nicht anders zu funktioneren?! Oder?

Viele Grüße
Dr.A.Colian

 

Eine Formellösung ist recht schwierig.

Wie wär es mit einer UDF?

Ist zwar auch eine VBA-Lösung, die aber über Excel aufgerufen wird.

Bsp-Aufruf in einer Zell:

Für Umwandlung: =Convert_xlph($A1;$D$1:$F$9;2;0;0)
Für Summenbildung: =Convert_xlph($A1;$D$1:$F$9;3;1;0)

Für mehr Speed den Bereich ($D$1:$F$9) nach Spalte 1 aufsteigend sortieren.
(Hilfreich bei vielen Aufrufen)

Und folgende Aufrufe verwenden:

Für Umwandlung: =Convert_xlph($A1;$D$1:$F$9;2;0;1)
Für Summenbildung: =Convert_xlph($A1;$D$1:$F$9;3;1;1)

Code:

Wird bei der Umwandlung ein Zeichen nicht gefunden, wird anstelle des Zeichens "(?)" ausgegeben.
Wird bei der Summenbildung ein Zeichen nicht gefunden, wird #NV zurückgegeben.

 

Hallo,

die UDF-Lösung im oberen Beitrag (#5) ist mangelhaft.

VlookUp (Sverweis) unterscheidet nicht zw. Groß- und Kleinschreibung.

Hiermit funktioniert es aber:

Code:

Bsp.-Aufruf in einer Zelle für Umwandlung:
=Convert_xlph($A1;$D$1:$D$9;$E$1:$E$9)

Bsp.-Aufruf in einer Zelle für Summenbildung:
=Convert_xlph($A1;$D$1:$D$9;$F$1:$F$9;WAHR)

 

Oder:
Code:

=convert_hsv($A1;$D$1:$D$9;$E$1:$E$9)
oder:
=convert_hsv($A1;$D$1:$D$9;$F$1:$F$9)

 

\@HS(V)

Warum benutzt du Find in einer UDF?

Für den 1. Fund ist Find recht schnell; aber das Evaluate() bremst.

 

\@xlph,

Hemmt etwas, aber nicht spürbar hier..