Weiterführende ProblemAnalyse
23.06.2018 05:22:37
Luc:-?
Bei der Lösung dieser Aufgabe wird man mit 2 Problemen konfrontiert, deren …
1. das Verketten der Inhalte einzelner Zellen bzw aller Zellen eines Bereichs ist. Xl bot hierzu in der Vergangenheit (wie generell bei TextBearbeitungsFktt) wenig. Dies hat sich erst in jüngster Zeit mit den Abo-Fktt TEXTKETTE (trivial) und TEXTVERKETTEN (universeller) – ab Xl2016 wohl im regulären Xl-FktsBestand – etwas verbessert. Das …
2. besteht in der fml-gesteuerten Sortierung von Texten, speziell auch nach (wie hier) vorgegebener Reihenfolge. Dafür bringt Xl nichts Direktes mit, so dass man das nur auf Umwegen lösen kann. Dabei gibt es prinzipiell 2 Möglichkeiten:
2.1 Man gibt die (neue) Reihenfolge direkt als Ziffernfolge der (alten – bezogen auf eine ursprüngliche TextReihenfolge –) Standorte der Texte vor bzw ermittelt diese aus …
2.2 Vorgabe des (neuen) Standorts pro Text (wie hier).
Beide VorgabeArten (die erste entspräche der üblichen Xl-Methodik) prädestinieren jeweils andere Verfahrensweisen. Die ersten beiden AWen benötigen eine Vorgabe lt 2.1. Da das hier aber nicht der Fall ist, muss mit VERGLEICH umgerechnet wdn. Das in der 3.AW gezeigte Iterationsverfahren benutzt zwar ein 2.2 entsprd Verfahren (weil hier naheliegend), könnte aber (mit Umrechnung) auch mit 2.1 angewendet wdn, ist also als Methode davon unabhängig.
Egal welchem Prinzip die Lösungen folgen, mit UDFs (im Fml-Verbund mit Xl-Fktt) wird es einfacher. Allerdings tritt eine weitere Lücke im Xl-FktsAngebot zutage, wenn man eine hier im Prinzip nahe liegende Lösung nach 2.2 (idR mit WECHSELN) anstrebt → WECHSELN arbeitet nach dem Oder-, nicht nach dem hier erforderlichen Und-Prinzip, d.h., die einzelnen Wechsel kumulieren nicht zu einem GesamtErgebnis! I.W. deshalb hatte ich vor Jahren die bisher unpublizierte (weil noch nicht endgülig fertiggestellt) UDF RepOpt für alle Fktt, bei denen eine derartige Kumulation sinnvoll wdn könnte, geschaffen (dazu mehr am BT-Ende).
Es folgt nun eine Auswahl möglicher LösungsFmln (singulare u.duale MatrixFmln) mit UDFs, die nach den beiden Methoden geordnet sind:
• Fmln nach Methode 2.1 - Auswahl in kontinuierlich aufsteigender Reihenfolge per Alt-Position
— singular:
{=VJoin(""&INDEX(A2:H2;VERGLEICH(SPALTE(A:H);A1:H1;0));";")}
{=VJoin(MTRANS(INDIREKT("Z2S"&VERGLEICH(SPALTE(A:H);A1:H1;0);0));";")}
— dual - vertikal: (d.h., für nur ein Ergebnis müssen 2 Zellen ausgewählt wdn!)
{=WAHL({1;2};"";VJoin(""&INDEX(A2:H2;VERGLEICH(SPALTE(A:H);A1:H1;0));";")) } LeerText über Text
{=WAHL({1;2};VJoin(""&INDEX(A2:H2;VERGLEICH(SPALTE(A:H);A1:H1;0));";");"") } LeerText unter Text
— dual - horizontal: (LeerText idR nur vor Text sinnvoll, außer in VerbundZelle!)
{=WAHL({1.2};"";VJoin(""&INDEX(A2:H2;VERGLEICH(SPALTE(A:H);A1:H1;0));";"))} EinzelZellen
{=WAHL({1.2};VJoin(""&INDEX(A2:H2;VERGLEICH(SPALTE(A:H);A1:H1;0));";");"")} VerbundZelle; oder …
{=VJoin(""&INDEX(A2:H2;VERGLEICH(SPALTE(A:H);A1:H1;0));";") } Text in beiden Zellen des Verbunds
• Fmln nach Methode 2.2 - Setzen lt Alt-Reihenfolge an angegebene Neu-Position
— Basis - Verteilungsmatrix: (mit LeerTexten → Verbinden ohne diese; alle singular)
{=VJoin(WENN(ZEILE(1:8)=A1:H1;A2:H2;"");";";-1)} wohl kürzest-mögliche Fml
{=MxJoin(MTRANS(AggregateXk(0;6;WENN(ZEILE(1:8)=A1:H1;A2:H2;#NV)));";")}
{=VJoin(T(VSplit(VSplit(VJoin(WECHSELN(ZEILE(1:8);A1:H1;A2:H2);{"|"." "};-2));"|";1));";";-1)}
{=VJoin(WENN(ISTZAHL(--WECHSELN(ZEILE(1:8);A1:H1;A2:H2));"";WECHSELN(ZEILE(1:8);A1:H1;A2:H2));";";-1) }
— Basis - ReihenfolgeNrn-MusterText: (Wechseln Zahl gg Text m.gleicher ZielPosition)
{=RepOpt("substitute";SPALTEN(A:H);;{2.3};RepOpt("substitute";SPALTEN(A:H);;3;WIEDERHOLEN("#;";7) &"#";"#";SPALTE(A:H);1);A1:H1;A2:H2) }
{=RepStitute(RepStitute(WIEDERHOLEN("#;";7)&"#";"#";SPALTE(A:H);1);A1:H1;A2:H2)}
Es gibt auf dieser Basis natürlich noch mehr Möglichkeiten, die aber wie die vorletzte Fml bisher generell unpublizierte UDFs voraussetzen, wie bspw eine textsortierende wie in folgender Fml, die damit auch eine recht elegante Möglichkeit darstellen würde:
{=WECHSELN(MaskOn(VJoin(Sort4Match(A1:H1&A2:H2);"");"alf");" ";";")}
Ansonsten sind die meisten UDFs im hiesigen Archiv zu finden, wobei die jeweilige Version nicht unbedingt die aktuellste sein mag, aber die letztpublizierte (höchste Vs-Nr) dürfte hier jeweils ausreichen. Da RepOpt nicht vorliegt und notwendigerweise massiert wiederholte WECHSEL(N)ei ein ständiges und absolutes Ärgernis ist, habe ich speziell dafür eine Repeat-Operation-UDF geschrieben → Repeat-Substitute:
Rem Wechseln von Teiltexten in Wiederholung (WWechseln) analog xlFkt WECHSELN (Substitute)
' Arg1: EinzelText (TextFelder m.INDEX-Fml im Arg1 in 1zelTexte zerlegen!); Arg2: auszu-
' tauschde/r Textteil/e; Arg3: neue/r Textteil/e; Arg4: fehlt=alle, sonst n-te/s Auftre-
' ten in Arg1 (analog Xl-Fkt); Arg5: 0=keine W-Op, fehlt - AutoErmittlg aus Argg2-4 (die
' auch als [Ko-]Vektoren gleicher Länge mögl sind), sonst Anzahl der WechselOperationen.
' Vs1.1 -LSr:CyWorXxl -cd:20180622 -1pub:20180623h -lupd:20180622t
Function RepStitute(QText, AText, NText, Optional nAuftr, Optional ByVal OpAnz)
Dim isOpCheck As Boolean, isSglMode As Boolean, lBit As Integer, OpVar As Integer, _
wx As Long, wArL, wt, wTx As Variant, wf As WorksheetFunction
On Error GoTo fx: If IsError(QText) Or IsArray(QText) Then Err.Raise xlErrRef
Set wf = WorksheetFunction: isSglMode = Not IsMissing(nAuftr)
If isSglMode And Not IsArray(nAuftr) Then isSglMode = nAuftr > 0
OpVar = Abs(IsArray(AText)) + 2 * Abs(IsArray(NText)): RepStitute = QText
isOpCheck = IsMissing(OpAnz): On Error Resume Next
If isOpCheck Then ReDim wArL(1 - CInt(isSglMode))
If IsArray(AText) Then
AText = wf.Transpose(AText)
If IsError(UBound(AText, 2)) Then
Else: AText = wf.Transpose(AText)
If IsError(UBound(AText, 2)) Then Else On Error GoTo fx: Err.Raise xlErrRef
End If
ReDim Preserve AText(UBound(AText) - LBound(AText))
If isOpCheck Then wArL(0) = UBound(AText) + 1
End If
If IsArray(NText) Then
NText = wf.Transpose(NText)
If IsError(UBound(NText, 2)) Then
Else: NText = wf.Transpose(NText)
If IsError(UBound(NText, 2)) Then Else On Error GoTo fx: Err.Raise xlErrRef
End If
ReDim Preserve NText(UBound(NText) - LBound(NText))
If isOpCheck Then wArL(1) = UBound(NText) + 1
End If
If isSglMode Then
OpVar = OpVar + 4 * Abs(IsArray(nAuftr))
If IsArray(nAuftr) Then
nAuftr = wf.Transpose(nAuftr)
If IsError(UBound(nAuftr, 2)) Then
Else: nAuftr = wf.Transpose(nAuftr)
If IsError(UBound(nAuftr, 2)) Then Else On Error GoTo fx: Err.Raise xlErrRef
End If
ReDim Preserve nAuftr(UBound(nAuftr) - LBound(nAuftr))
If isOpCheck Then wArL(2) = UBound(nAuftr) + 1
End If
End If
On Error GoTo fx: If isOpCheck And CBool(OpVar) Then OpAnz = wf.Max(wArL) Else OpAnz = 0
If CBool(OpAnz) Then
wTx = Array(AText, NText, IIf(isSglMode, nAuftr, Empty))
If IsEmpty(wTx(2)) Then ReDim Preserve wTx(1)
For Each wt In wTx
If IsArray(wt) Then
If UBound(wt) + 1 - LBound(wt) OpAnz Then Err.Raise xlErrRef
End If
Next wt
For wx = 0 To Abs(OpAnz) - 1
If isSglMode Then
If IsArray(wTx(2)) Then lBit = Abs(wTx(2)(wx) > 0) Else lBit = Abs(wTx(2) > 0)
End If
Select Case wf.Bin2Dec(wf.Dec2Bin(OpVar, 3) & lBit)
Case 0: RepStitute = wf.Substitute(RepStitute, wTx(0), wTx(1))
Case 1: RepStitute = wf.Substitute(RepStitute, wTx(0), wTx(1), wTx(2))
Case 2: RepStitute = wf.Substitute(RepStitute, wTx(0)(wx), wTx(1))
Case 3: RepStitute = wf.Substitute(RepStitute, wTx(0)(wx), wTx(1), wTx(2))
Case 4: RepStitute = wf.Substitute(RepStitute, wTx(0), wTx(1)(wx))
Case 5: RepStitute = wf.Substitute(RepStitute, wTx(0), wTx(1)(wx), wTx(2))
Case 6: RepStitute = wf.Substitute(RepStitute, wTx(0)(wx), wTx(1)(wx))
Case 7: RepStitute = wf.Substitute(RepStitute, wTx(0)(wx), wTx(1)(wx), wTx(2))
Case 9: RepStitute = wf.Substitute(RepStitute, wTx(0), wTx(1), wTx(2)(wx))
Case 11: RepStitute = wf.Substitute(RepStitute, wTx(0)(wx), wTx(1), wTx(2)(wx))
Case 13: RepStitute = wf.Substitute(RepStitute, wTx(0), wTx(1)(wx), wTx(2)(wx))
Case 15: RepStitute = wf.Substitute(RepStitute, wTx(0)(wx), wTx(1)(wx), wTx(2)( _
wx))
End Select
Next wx
End If
fx: If CBool(Err.Number) Then RepStitute = CVErr(Err.Number)
Set wf = Nothing
End Function
Morrn, Luc :-?
PS: Evtl folgen noch ein paar Anmerkungen nebst Verweis auf ein INDEX-„Phänomen“ samt Link zu anderem diesbzgl BT.