Transponieren von unterschiedlichen Adressblöcken

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Guten Morgen Luc,
... ich kenne ja Deine Vorliebe für diese. Die Vorteile dieser hast Du mir schon dargelegt (Ressourcenschonender und damit schneller in der Auswertung).
Du kennst aber auch meine Argumentation gegen deren Einsatz (mit einigen Ausnahmen), wo in vielen Fällen die Nachteile dieser Formeln die Vorteile zumindest aus meiner Sicht aufheben. Das beginnt mit der mangelnden Transparenz dieser Formeln. MS bietet kein sofortiges Erkennungsmerkmal für diese im Unterschied zu den klassischen einzelligen Matrixformeln an. Darüber hinaus bedarf es einer (oft nicht wirklich möglichen) optimalen Einschätzung, wie groß der Ergebnisbereich dessen sein muss. Ansonsten muss eine nicht ganz unaufwendige Änderung vorgenommen werden.
An hiesigem Beispiel lässt sich das wie folgt aufzeigen: Angenommen es kommt auch nur für eine Person nicht nur max 3 sondern eine 4. und sogar 5. Datenzelle hinzu, muss der gesamte Formel-Ergebnisbereich gelöscht und neu definiert werden. Hinzu kommt, dass die notwendigen Formeln auch ganz schön voluminös werden können.
Meine alternative einfache einzellige Formel für Deinen ersten Lösungsansatz (mit Ident) wäre:
In D2:
=WENN(SPALTE(A1)>ZÄHLENWENN($A:$A;$C2);"";INDEX($B:$B;VERGLEICH($C2;$A:$A;)+SPALTE(A1)-1))
und Formel nach rechts und unten kopieren.
Deine Lösungsformel für Daten ohne Ident hat noch einen Haken, denn "Max Muster" wird damit "verschluckt".
Meine alternative Lösung für Daten ohne Ident wäre unter der bereits gestern dazu von mir genannten Voraussetzung (deutsche PLZ am Beginn des jeweils 3. Datensatzes einer Adressangabe) ein zweiteiliger Formelansatz. Eine einfache einzellige AGGREGAT()- Formel für die Ermittlung Personennamen und eine einzellige AGGREGAT()- Formel (die möglicherweise noch etwas optimiert werden könnte) für die diesen zugeordneten Daten:
In D11:
=WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE(B$1:B$99)/ISTZAHL(--LINKS(B$3:B$99;5));ZEILE(A1)));"") und Formel nach unten kopieren:
In E11:
=WENN(D11="";"";WENN(INDEX($B:$B;AGGREGAT(15;6;ZEILE(C$1:C$99)/ISTZAHL(--LINKS($B$4:$B$99;5)); ZEILE(B1))+SPALTE(B1))=$D12;"";INDEX($B:$B;AGGREGAT(15;6;ZEILE(C$1:C$99)/ ISTZAHL(--LINKS($B$4:$B$99;5));ZEILE(B1))+SPALTE(B1)))) und Formel nach rechts und unten kopieren.
Dir auch einen schönen 1. Adventssonntag noch.
Gruß Werner
.. , - ...

…Werner;
aber das muss jeder selber wissen und meine Variante ist auch schneller eingetragen. Im Übrigen gehe ich in diesem Fall davon aus, dass diese Herstellung einer transponierten Liste nicht dauerhaft per Fml erzeugt wdn soll, weshalb die ganze Liste anschließend auch kopiert und als Werte auf den ZellBereich gespeichert wdn könnte. Dann sind ohnehin alle Vor- bzw Nachteile irrelevant. Andererseits wäre das, falls das des Öfteren benötigt wird, auch eine Motivation für ein leicht umrüst- bzw entsprd parametrierbares VBA-Pgm, nur, dass das ähnliche Probleme mit der Aufgabe hätte wie eine reine Fml-Lösung. Beachtet wdn muss bei sehr großen Listen allerdings, dass die Xl-Fkt MTRANS (.Transpose) Limitierungen hat!
Aber im Einzelnen:
1. Mangelnde Transparenz von MatrixFmln kann ich nicht feststellen — sie liefern im pluralen StandardFall halt nur mehrere Werte in Vektor- oder MatrixForm, nicht eben selten in der Algebra. Ob die nun alle auf 1× oder indiziert wiedergegeben wdn sollen, hängt eher davon ab, ob unbedingt Einzelwerte benötigt wdn oder ein zusammenhängendes Datenfeld auch recht ist.
2. Die Länge kann man im konkreten Fall, besonders hier, vorher bestimmen. Für unübersichtlichere Fälle habe ich vor Jahren mal eine von einer EreignisRoutine unterstützte UDF geschrieben, in der festgelegt wdn kann, welcher Teil des ihr übergebenen Datenfelds (oder auch das ganze) ausgegeben wdn soll. Danach erfolgt dann die BereichsAuswahl für die in einer Zelle als Argument dieser UDF eingetragene MatrixFml, wobei gleich auch noch ZellFormatierungen, ggf sinnvoll angepasst, übertragen wdn.
3. Vergrößert kann ein ErgebnisBereich auch im NormalFall immer leicht wdn, nur das Verkleinern erfordert eine Neuanlage des Ganzen. Insofern ist das auch für zusätzliche Spalten ganz einfach, man muss hier aber entweder zeilenweise arbeiten oder nur die 1.Zeile erweitert neu berechnen und dann auf die FolgeZeilen übertragen (aufkopieren).
Ja, die alte Fml; hatte ich übersehen. Liegt daran, dass D10 jetzt nicht mehr leer und deshalb +1 auch nicht mehr erforderlich ist:
D11:G11[;D12:G15]:{=WENNFEHLER(MTRANS(INDEX(B$2:B$99;SUMME(--(D$10:G10""))):INDEX(INDEX(B$2:B$99;SUMME(--(D$10:G10""))):B$99;VERGLEICH(WAHR;ISTZAHL(--LINKS(INDEX(B$2:B$99;SUMME(-- (D$10:G10""))):B$99;5));0)+WENNFEHLER(VERGLEICH("Tel *";INDEX(B$2:B$99;SUMME(--(D$10:G10 ""))):B$99;0)-VERGLEICH(WAHR;ISTZAHL(--LINKS(INDEX(B$2:B$99;SUMME(--(D$10:G10""))):B$99; 5));0)=1;0)));"")}
Aber Deine 2-Fml-Lösung auf AGGREGAT-Basis scheint hier ohnehin günstiger zu sein, wobei sie als plurale MatrixFml evtl noch kürzer und günstiger wäre…
Schö1AdWoche, Luc :-?

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Call FormulaArrayIndex(Target) End Sub Rem Indizierg u.Elementarisierg v.ZellBereichen benannter MatrixFmln ' die nur absolute Zell- u.BereichsBezüge aufweisen dürfen. ' Vs1.0 -LSr:CyWorXxl -cd:20161128 -1pub:20161128h -lupd:20161128t Private Sub FormulaArrayIndex(ByVal Ziel As Range) Dim difSp As Long, difZl As Long, txFml$, xZ As Range On Error Resume Next With Application On .CutCopyMode GoTo ex, ex If IsArray(Ziel) Then If Ziel.Cells(1).HasFormula Then txFml = Mid(Ziel.Cells(1).FormulaArray, 2) If IsError(ActiveSheet.Names(txFml)) Then If IsError(ActiveSheet.Parent.Names(txFml)) Then Else GoTo iz Else iz: .EnableEvents = False difSp = Ziel.Column - 1: difZl = Ziel.Row - 1 Ziel.ClearContents For Each xZ In Ziel xZ.Formula = "=index(" & txFml & "," & xZ.Row - _ difZl & "," & xZ.Column - difSp & ")" Next xZ .EnableEvents = True End If End If End If ex: End With End Sub

Beliebteste Forumthreads (12 Monate)

	A	B
1	ID	Datensatz
2	1	Max Muster
3	1	Irgendwo 3
4	1	01234 Daundda
5	2	Gundel Gaukel
6	2	Herbstweg 2
7	2	01234 Daundda
8	2	Tel 0123/12389281
9	3	Max Meier
10	3	Irgendwo 8
11	3	01234 Daundda

	A
1	Max Muster
2	Irgendwo 3
3	01234 Daundda
4
5	Gundel Gaukel
6	Herbstweg 2
7	01234 Daundda
8	Tel 0123/12389281
9
10	Max Meier
11	Irgendwo 8
12	01234 Daundda
13
14	Rita Gundel
15	Sommerweg 5
16	01235 Nichtda
17	Tel 0124/123456
18
19	Peter Pan
20	Poltersteig 99
21	01255 Dingsda

Transponieren von unterschiedlichen Adressblöcken

44 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

44
Beiträge zum Forumthread

Beiträge zu diesem Forumthread