Kreuztabelle serialisieren, aber nur teilweise

[ryder_hook]

Hallo Excel-Experten,

ich habe hier ein Problem, bin mir aber nicht sicher wie man das erreichen kann. Ich möchte das gerne mit Excel 2010 erreichen, wenn das nicht geht, dann auch mit 2016. Aber definitiv ohne Makro.

Ich habe folgende Kreuztabelle:

Tabellenblattname: Kreuztabelle

	A	B	C	D	E	F	G	H
1		R.1.1	R1.1.2.a	R.1.1.2.b	R.1.3	R.1.4.1	R.1.4.2	R.2.1
2	A.1.1	full-i	part-i
3	A.1.2				full-i	part-i
4	A.1.3
5	A.2.1					part-i
6	A.2.2						full-i
7	A.3.1.1						part-i	part-i
8	A.3.1.2		full-i
9	A.3.1.3						full-i

Nun möchte Teile der Kreuztabelle als Liste darstellen um weitere Daten hinzu zufügen, das würde dann so aussehen:

Tabellenblattname: Tabelle2

	A	B	C	D	E
1		Zuständig	Datum	Bemerkung	Referenz
2	A.1.1				R.1.1(f), R.1.1.2a(p)
3	A.1.2				R.1.3(f), R.1.4.1(p)
4	A.1.3
5	A.2.1				R.1.4.1(p)
6	A.2.2				R.1.4.2(f)
7	A.3.1.1				R.1.4.2(p), R.2.1(p)
8	A.3.1.2				R.1.1.2.a(f)
9	A.3.1.3				R.1.4.2(f)
10	R.1.1.2.b
11	R1.4.1				A1.2(p), A.2.1(p)
12	R.2.1				A.3.1.1(p)

Den obere Teil sollen alle Werte aus der Kreuztabelle von der ersten Spalte darstellen. Beim unteren Teil erscheinen nur Werte von der ersten Zeile der Kreuztabelle, wenn die Spalte ansonsten komplett leer ist oder wenn nur "part" drin steht.

Ich hoffe das Beispiel ist damit erklärlich, falls nicht, bitte einfach noch mal Fragen.

Danke.
ciao
ryder

[Ego]

Hallo ryder,

in Office 365 gibt es die Funktion Textverketten. Hiermit könnte eine Formel für den ersten Teil so aussehen:

PHP-Code:

{
=TEXTVERKETTEN(",";WAHR;WENN(INDEX(Tabelle1!$B$2:$H$9;E2;0)="";"";Tabelle1!$B$1:$H$1&"("&LINKS(INDEX(Tabelle1!$B$2:$H$9;E2;0);1)&")"))
} 


Achtung! Matrixfunktion. Die geschweiften Klammern werden nicht mit eingegeben, sondern die Formel mit Strg+Shift+Enter abgeschlossen.
Natürlich kann man die Ermittlung der Zeile in der Hilfsspalte E auch an den beiden Stellen der Formel einbauen.

[ryder_hook]

Hallo Ego,

vielen Dank, funktioniert. Es ist ein Wunder, was Du da gezaubert, selbst jetzt, wo ich die Lösung sehe, verstehe ich sie nicht.

Trotzdem vielen, vielen Dank.
Nun hoffe ich noch auf eine Lösung für die Spalte vorne.

ciao
ryder_hook

[ryder_hook]

Hallo,

ich habe die Anforderungen vereinfacht und dadurch für den noch offenen Teil eine Lösung gefunden.
D.h. ich verwendet MTRANS um ALLE Spalten der Überschrift der Kreuztabelle zu verlinken. Dadurch dass die Formel von Ego zu gut funktioniert, kann man ja dann die Zeilen aus dem R-Bereich filtern.
Das ist für meinen Zweck ausreichend.

Ich hoffe, ich habe die Losung damit erklärlich beschrieben, falls nicht, einfach noch mal melden.

Wenn mir dann noch jemand die Magic-Formel von Ego erklären, wäre ich zutiefst dankbar.

ciao
ryder_hook.

[ryder_hook]

Sorry, jetzt habe ich die Formeln und die XLS-Datei vergessen.

Mit MTRANS:

Code:

=MTRANS(Tabelle1!B1:H1)

STRG-SHIFT-ENTER nicht vergessen

oder mit INDIREKT:

Code:

=INDIREKT(ADRESSE(1;ZEILE()-8;;;"Tabelle1"))

Und noch die Datei.

ciao
ryder_hook

[Ego]

Hallo ryder,

zur Erklärung der Formel:

1) allgemein

A) Wenn die Bearbeitung einer Formel nachvollziehen will, kann man das zB. unter "Formeln">"Formelauswertung".
Ich bevorzuge aber das Nachvollziehen im Bearbeitungsfeld, indem ich dort einen Teil der Formel markiere und dann "F9" drücke. Da kann ich sebst spezifizieren welcher Teil der Formel als nächstes ausgewertet werden soll.
Achtung! Diese Auswertung mit "Esc" abbrechen, da sonst die Zwischenergebnisse fest in die Formel übernommen werden.

B) Beim Nachvollziehen einer Formel sollte man immer 

• von Innen nach Aussen vorgehen und 
  
• bei Matrixformeln beachten, dass Funktionen, die normalerweise eindeutige Parametern benötigen jetzt an diesen Stellen auch Listen verarbeiten.
  

2) Diese Formel (von Innen nach Aussen)

A:=INDEX(Tabelle1!$B$2:$H$9;E2;0)
Index liefert den Inhalt der über Zeile und Spalte ausgewählten Zelle. Wenn ich als Spaltennummer eine 0 eintrage bekomme ich eine Liste der Inhalte aller Zellen der ausgewählten Zeile.

B:= LINKS(A;1)
Hier wird für jedes Element der Liste (da Matrixformel) das erste Zeichen von links ermittelt.

C:=Tabelle1!$B$1:$H$1&"("&B&")"
Es werden die Elemente der Überschriften mit den Elementen von B (erstes Zeichen der Zelle) verknüpft (da Matrixformel).
Ich erhalte eine Liste in der aber noch, falls die Matrix keinen Eintrag hat, Überschriften mit leeren Klammern vorhanden sind.

D:=WENN(A="";"";C)
Für alle Zellen (da Matrixformel) der gewählten Zeile wird eine Liste erstellt. Sie erhält einen leeren Eintrag, wenn die Zelle leer ist und ansonsten die Überschrift mit dem ersten Zeichen der Zelle.

E:= TEXTVERKETTEN(",";WAHR;D)
Der zweite Parameter "WAHR" sorgt dafür, dass nach einem leeren Listeneintrag kein zusätzliches Trennzeichen gesetzt wird.

[ryder_hook]

@Ego, vielen Dank für die Erklärung, jetzt ist mir die Formel klar und Danke für den Hinweis mit Formelauswertung - kannte ich noch nicht.

Dank deiner Tips bin ich weiter gekommen. Ich habe jetzt das andere Thema auch gelöst, wenn auch nicht elegant. Nämlich mit einer Hilfstabelle.

In der Hilfsspalte hole ich mir die Überschriften, die relevant sind, die anderen Zellen bleiben leer.
Das funktioniert mit folgender Formel:

Code:

=WENN(ZÄHLENWENN(INDIREKT("Tabelle1!"&ADRESSE(1;ZEILE()-8;;;)&":"&ADRESSE(9;ZEILE()-8;;;));"full-i");"";INDIREKT(ADRESSE(1;ZEILE()-8;;;"Tabelle1")))

Als Matrix-Formel,

dann füge ich in Spalte A die Formel ein, um die Leerzeilen zu entfernen:

Code:

=WENN(ZEILE(A10)-9>ANZAHL2(M10:M20);"";INDEX(M:M;KKLEINSTE(WENN(M$10:M$20<>"";ZEILE($10:$20));ZEILE(M10)-9)))

ebenfalls als Matrixformel.

Beides in einer Formel zu vereinen ist mir nicht (vielleicht noch nicht) gelungen.
Auch die Anpassung von EGOs Tip für das andere Problem bedarf dann der Überarbeitung.

Im Anhang noch das angepasst Beispiel als XLS-Datei.

ciao
ryder_hook

[Jockel]

Hallo, die erste Formel habe ich der Datei vergebens gesucht? Um welches TabellenBlatt geht's denn. Und die zweite geht einfacher mit AGGREGAT()

okay, habe die erste Formel. Aber ich verstehe nicht was der Plan ist. Die TEXTVERKETTEN() Formel kann ich nicht testen da ich nur bis XL2013 zur Verfügung habe und in OE kann man keine Matrixformel erstellen (jedenfalls nicht mit Strg+Umsch+Enter)

Ergo: Ich bin schon wieder raus...

[ryder_hook]

Hallo Jockel,

es geht um Tabellenblatt "Tabelle2 (3)"
Durch die Hilfstabelle in Spalte M, habe ich es jetzt geschafft in Spalte A nur die R-Überschriften zu bekommen, bei denen in der Kreuztabelle nur part-i oder gar nichts steht (siehe auch erster Post).

Aber leider funktioniert damit die TEXTVERKETTEN Formel von Ego nicht mehr in gänze, die muss ja jetzt auch angepasst werden.

Und, zu guter Letzt, wäre es natürlich schön, wenn das, was ich mit Hilfstabelle gelöst habe in einer Formel funktioniert ohne Hilfstabelle. Aber das ist dann schon Luxus.

Ich hoffe, damit ist es klarer und sorry, für die Knappheit.

ciao
ryder_hook

[ryder_hook]

Hallo Ego,

noch mal eine Frage. Du hast geschrieben, dass die Funktion TEXTVERKETTEN dazu dient, dass keine zusaätzlichen Trennzeichen erscheinen.
Deshalb habe ich es mal mit der Funktion nur VERKETTEN versucht, geht aber nicht.

Habe ich da was falsch verstanden?

Muss es doch zwingend die TEXTVERKETTEN sein oder würde es auch mit der Funktion nur VERKETTEN gehen? Wenn ja, wie?

Vielen Dank.

ciao
ryder