Office: Alle Elemente einer Matrix mit Anzahl auflisten

Hallo zusammen *Smilie

Ich suche schon seit längerer Zeit nach einer passenden Formel für mein Problem, konnte aber leider bis jetzt nicht fündig werden.

Das Problem ist wie folgt:

Ich habe eine Matrix mit X-Zeilen und Y-Spalten. Dort drin sind Texteinträge in beliebiger Form (einfach und mehrfach).
Jetzt möchte ich daraus eine Liste generieren, welche alle vorkommenden Einträge auflistet und im besten Falle mir noch anzeigt, wie oft die einzelnen Einträge vorkommen.

Meine Suche ergab schon die wildesten Formeln mit SVERWEIS, INDEX und VERGLEICH, aber ich konnte keine davon zum Laufen bringen.

Anbei ein Bild, welches das Problem hoffentlich gut darstellt:




Es würde mich freuen, wenn mir hier unter den Experten jemand helfen könnte*Smilie

Vielen Dank

Mit freundlichen Grüßen
Evox

edit:
Ich nutze übrigens das Office 365

:)

 

Hi,


[*]Spezialfilter kopieren

[*]Zählenwenn

Bei Bedarf lässt sich das sogar noch mit nem Makrorekorder aufzeichnen.

 

Hi,

meinst du so:


Tabelle1

	B	C	D	E	F	G	H	I	J	K	L
1							Ergebnis:				Ball
2							Eintrag	Anzahl			Katze
3							Ball	2			Hund
4	Matrix:						Katze	3			Kabel
5	Ball	Katze	Hund	Kabel			Hund	2			Tür
6	Kabel	Tür	Katze	Blume			Kabel	2			Katze
7	Hund	Rakete	Ball	Giraffe			Tür	1			Hund
8	Stift	Dübel	Magnet	Katze			Rakete	1			Rakete
9							Stift	1			Ball
10							Dübel	1			Stift
11							Magnet	1			Dübel
12											Magnet

verwendete Formeln
Zelle	Formel	Bereich	N/A
H3	{=INDEX(L:L;VERGLEICH(WAHR;L1:L99<>"";0))}	$H
H4:H12	{=WENNFEHLER(INDEX(L:L;VERGLEICH(1;(ZÄHLENWENN(H:H3;L:L)=0)*(L:L<>"");0));"")}	$H
I3:I12	=WENN(H3<>"";ZÄHLENWENN($B:$E;H3);"")
L1:L12	=INDEX(B:E;(ZEILE(A5)-5)/3+5;REST(ZEILE(A5)-5;3)+1)

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

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip

http://Hajo-Excel.de/tools.htm

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

Add-In-Version 25.20 einschl. 64 Bit

Spalte L ist eine Hilfsspalte um alle Inhalte der Matrix in einer gemeinsamen Spalte aufzulisten und kann ausgeblendet werden. Diese Spalte dient als Grundlage für die jeweils einmalige Auflistung der Daten in Spalte H.

 

Hallo,

Ich schlage meine Funktionen sbReShape und sbMiniPivot vor:
http://www.sulprobil.com/Get_it_done...minipivot.html

Viele Grüße,
Bernd P

 

Moin,
ganz ohne Formel, ganz ohne VBA/Makro => Daten | Abrufen und transformieren aka Power Query, mit ein paar Mausklicks.

 

Zu Karin,

Sag mir wo die Blume ist.
Wo ist sie geblieben?

*wink.gif* *wink.gif* *wink.gif*

 

In Excel 365 (evtl. nur Insider?) funktioniert auch folgendes:

Die Matrix wird ab A1 in 4 Spalten bis D "wortweise" gefüllt, also von links nach rechts und dann oben nach unten. 18 Einträge enden also in B5. Lücken sind nicht erlaubt.

F1: Köpfchen
(als notwendiger Feldbezeichner; ich wähle so ein blödes Wort, damit man bei der Instruktion sieht, dass es von mir kommt)

F2: =INDEX(A:D;(
ZEILE(INDIREKT("1:"&ANZAHL2(A:D)))+3)/4;REST(
ZEILE(INDIREKT("1:"&ANZAHL2(A:D)))-1;4)+1)

(Feld F2 enthält die implizite Matrix des neuen Excels (seit Oktober 2018). Mit einer Formelerweiterung wäre nicht nur A:D in der Zeilenanzahl, sondern sogar A1 in Zeilenanzahl und Spaltenanzahl dynamisch. Übrigens ist das ganze nicht Strg-L-fähig, da das eine Umwandlung in festen Text nach sich zöge. Natürlich kann man die Formel auch wie früher statisch als {} abschließen. Dann geht es auch vor xl365.)

Pivottabelle-Quelle:
F1:F999
(eine statt dessen dynamische Benennung wie =BEREICH.VERSCHIEBEN($F$1;;;1+ANZAHL2($A:$D)), die dann als dynamische Pivottabellen-Quelle funktionieren könnte, ist mir heute morgen noch nicht gelungen.)

Pivottabelle-Felder:
Zeilen: Köpfchen
Werte: Köpfchen-Anzahl
Zeilenbeschriftungen - Beschriftungsfilter - ist nicht gleich - enthält nicht <leerlassen!>
Zeilenbeschriftungen - Weitere Sortieroptionen - A-Z

(leider muss die Pivottabelle immer noch manuell aktualisiert werden, wenn man kein entsprechendes Ereignis einbaut/einbauen möchte.)

 

Hallo zusammen *Smilie

Vielen dank für die vielen Antworten.

@Mase Wie genau stelle ich den Spezialfilter ein? Ich weiß nicht so recht, was in das Kriterienfeld muss, sodass er mir alle Matrixelemente ohne Duplikate als Liste aufzählt.

@Beverly (Karin)
Ich hab die Formeln mal probiert, leider weiß ich nicht genau was die verschiedenen Parameter machen und habe scheinbar auch irgendwo noch einen Fehler drin:



Zumindest fehlt mir die 4. Spalte. Vermutlich muss eine (oder mehrere) der 3en zu einer 4 werden.

@Sulprobil
Danke für die Antwort, aber das schaut recht komplex aus, ich würde es glaube erstmal mit einer Anfänger freundlicheren Lösung versuchen *Smilie

@Drogist
Von Power Query hab ich bis jetzt noch nichts gehört, werde es mir mal angucken.
Kann ich damit auch ein, für andere nutzbares, Template erstellen oder benötigt jeder für das Nutzen dann Power Query?

@lupo1
So ganz hab ich nicht verstanden was ich machen soll. Habe aber auch noch nie mit Pivot-Tabellen gearbeitet...


Bin weiterhin dankbar für Tipps und Hilfe *Smilie

Viele Grüße
Evox

 

Moin,

wenn Du magst kannst ne Mappe hochladen -> geht schneller *Smilie

 

Hallo,

anbei meine Arbeitsmappe *Smilie

 

Nette Homepage *Smilie
Harvay Mackay -> CIT -> bemerkenswerte Informationsgewinnung *Smilie

 

Ja...

 

Hi,

Das ist korrekt - da es nicht 3 sondern 4 Spalten sind, müsste die Formel für mein Beispiel so aussehen:

=INDEX(B:E;(ZEILE(A5)-5)/4+5;REST(ZEILE(A5)-5;4)+1)


Und für deine hochgeladene Mappe müsste die Formel für die komplette Auflistung so aussehen:

=INDEX(A: D;(ZEILE(A1)-1))/4+1;REST(ZEILE(A1))-1);4)+1)

Die Rot markierten Ziffern geben die Startzeile und die Grün markierten Ziffern gegeben die Spaltenanzahl an.

 

Das eigentliche Problem ist doch die Datenstrukturierung. Wer dort im Sinne von Bearbeitungstechnik schlampt, muss dann mehr nacharbeiten, sieht man ja an Hilfsspalten, Unpivot per Power Query, VBA-Feuerwerk & Co.

Wenn Du Deine Texteinträge gleich in eine Spalte legst statt irgendwo verteilt im Raum, kannst Du dann ein einfaches Pivot direkt verwenden.

Wenn man es sich mit einfachen Dingen schon so kompliziert macht (und persönlich scheitert?), wie will man dann richtige Aufgaben lösen?

 

Da stimme nicht nur ich Eberhard ausdrücklich zu!

Benötigt man die Vier-Spalten-Matrix trotzdem (z.B. zum Ausdrucken), stellt man die aus der Pivot-Quelle (= 1 Spalte) ganz einfach mit INDEX(), ZEILE() und SPALTE() her.

Die Vier-Spalten-Darstellung ist immer nur eine "Ableitung" der zugrundeliegenden Daten. Das gewünschte Ergebnis ist - als eine Zusammenfassung - ebenfalls nur eine Ableitung.

Man geht aber - schon aufgrund des Aufwandes - möglichst nicht direkt von "Ableitung" zu "Ableitung", sondern immer über die Ausgangsdaten (hier eben 1 Spalte).