Matrix Elemente zählen

[mart3n]

Hallo alle zusammen!

Nachdem ich bereits Google und die Forum-Suchfunktion bereits ohne Ergebnisse durchforstet habe und mir dieses eigentlich simple Problem das Hirn zu verknoten scheint, habe ich mir gedacht dass ich euch hier vielleicht um Rat bitten könnte.

Das Problem ist folgendermaßen: Ich möchte die verschiedenen Elemente einer Matrix zählen (siehe Attachment).
Ich habe hier nämlich einen Export, der die verschiedenen "Optionen" einer Zeile auf mehrere Spalten verteilt ausgibt.
Also in der Beispieldatei hat z.B. Zeile 1 die Optionen M5, M8 und M3, verteilt auf 3 Spalten.

Alles was ich gerne machen würde ist alle verschiedenen vorkommenden Optionen in dieser Matrix aufzulisten, damit ich sie danach zählen kann.
Pivottabellen haben mir hier bis jetzt nicht weitergeholfen... wüsstet ihr evtl. eine Lösung für das Problem?

Vielen Dank!

Martin

[WillWissen]

Hi Martin,

so ganz klar ist mir nicht, ob du das Ergebnis zeilenweise oder für den gesamten Bereich benötigst. Hier mal mein Vorschlag für die zeilenweise Option.

Tabelle1

	A	B	C	D	E	F	G	H
2		Optionen 1	Optionen 2	Optionen 3	Optionen 4	Optionen 5	Optionen 6
3	Zeile 1	M5	M8	M7				3
4	Zeile 2	M5	N4	F3	M8			4
5	Zeile 3	F3	M9	M5				3
6	Zeile 4	M5	M7	F7	F2	G4	Z3	6
7	Zeile 5	Z3	N4	M8	F2			4

Formeln der Tabelle

Zelle Formel H3 =ANZAHL2(B3:G3)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[shift-del]

Hallo Martin

Du möchtest die Kreuztabelle entpivotieren.
Importiere die Daten nach Power Query und entpivotisiere sie dort.
Und wenn man schon mal da ist dann kann man sie mit der Funktion "Zeilen zählen" gruppieren und du hast das Ergebnis.

	A	B
1	Option	Anzahl
2	M5	4
3	M8	3
4	M7	2
5	N4	2
6	F3	2
7	M9	1
8	F7	1
9	F2	2
10	G4	1
11	Z3	2

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[snb]

Matrix Formel:

PHP-Code:

=SUM(IFERROR(1/COUNTIF(B3:G7;B3:G7);0)) 


[mart3n]

Hallo alle zusammen,

vielen Dank für die raschen Antworten!

Leider kann ich keine der Vorschläge wirklich umsetzen - ich würde, um auf WillWissen's Frage zu antworten, den gesamten Bereich durchsuchen müssen.

PowerQuery kann ich leider auch nicht verwenden, da ich das Plugin hierzu installieren müsste und hier keine Berechtigung dazu hab (trotzdem vielen Dank für den Tipp, ich kannte das gar nicht).

Und zu guter Letzt - die Matrix-Formel habe ich mit Strg-Shift-Enter eingegeben, und es gibt mir ein paar Zahlen aus. Keine Ahnung jedoch was die bedeuten - wie gesagt, ich würde einfach eine Liste aller verschiedenen Elemente in der Matrix benötigen, weil ich deren Häufigkeit herausfinden will. Deren gesamte Häufigkeit in der Kompletten Matrix.

Anbei noch mein Versuch mit der Matrix-Formel. Danke trotzdem für die Antworten!

Bg Martin

[shift-del]

Es gibt noch andere Möglichkeiten eine Kreuztabelle zu entpivotisieren.

-----------------------------------------------------------------------------------------
Videos
mit VBA / mit Pivot-Tabellen-Assistent von Andrea Thehos (de)
mit Pivot-Tabellen-Assistent von Debra Dalgliesh (Contextures) (en)
mit Power Query von Miguel Escoba (The Power User) (en)
mit Pivot-Tabellen-Assistent von Mike Girvin (ExcelIsFun) (en)
mit Pivot-Tabellen-Assistent von Bill Jelen (MrExcel) (en)
mit Power Query von Ken Puls (en)
mit Power Query von Bill Jelen (MrExcel) (en)
mit Pivot-Tabellen-Assistent von Andrea Thehos (de)
mit Power Query von Mike Girvin (ExcelIsFun) (en)
mit Power Query von Mike Girvin (ExcelIsFun) (en)
mit Power Query von Mike Girvin (ExcelIsFun) (en)
mit Power Query von Mike Girvin (ExcelIsFun) (en)
-----------------------------------------------------------------------------------------
Aufgabe und Lösungen von Andreas Thehos (de)
Aufgabe
Lösung mit Power Query
Lösung mit Pivot-Tabellen-Assistent
Lösung mit WAHL()
Lösung mit VBA
-----------------------------------------------------------------------------------------
Artikel
mit INDEX() (excelformeln.de)
mit Makro oder Power Query (excel-ist-sexy.de)
How-to Convert an Existing Excel Data Set to a Pivot Table Format
Quick Ways to Unpivot Excel Data
Unpivot Excel Data With a Macro

[snb]

Code:

Sub M_snb()
   sn = [B3:G70]
   With CreateObject("scripting.dictionary")
      For Each it In sn
        If it <> "" Then .Item(it) = .Item(it) + 1
      Next
      
      Cells(1, 10).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
    End With
End Sub

oder:

Code:

Sub M_snb()
   With CreateObject("scripting.dictionary")
      For Each it In [B3:G7].SpecialCells(2)
        .Item(it.Value) = .Item(it.Value) + 1
      Next
      
      Cells(1, 10).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
    End With
End Sub

[Castor]

Mit UDFs, falls du diese einsetzen darfst und kannst, wäre das kein Problem, Martin:

	A	B	C	D	E	F	G	H	I
2	Optionen 1 Optionen 2 Optionen 3 Optionen 4 Optionen 5 Optionen 6 Option Anzahl Zeile 1 M5 M8 M7 M5 4 Zeile 2 M5 N4 F3 M8 M8 3 Zeile 3 F3 M9 M5 M7 2 Zeile 4 M5 M7 F7 F2 G4 Z3 N4 2 Zeile 5 Z3 N4 M8 F2 F3 2 M9 1 Zeile 1 77,5 77,8 77,7 F7 1 Zeile 2 77,5 78,4 70,3 77,8 F2 2 Zeile 3 70,3 77,9 77,5 G4 1 Zeile 4 77,5 77,7 70,7 70,2 71,4 90,3 Z3 2 Zeile 5 90,3 78,4 77,8 70,2 ← plurale Matrixformeln ^ B9:G13: {=WENNFEHLER(--(CODE(LINKS(B3:G7))&","&TEIL(B3:G7;2;3));"")} (gelbe Felder/blaue Schrift dienen nur der Visualisierung der entsprd Teile der Formel im grünen Feld) H3:I12: {=WAHL({1.2};VSplit(VJoin(WENN(B3:G7="";"";B3:G7);;-1);;;1);DataSet(HÄUFIGKEIT(WENNFEHLER(--(CODE(LINKS(B3:G7))&","&TEIL(B3:G7;2;3));"");WENNFEHLER(--(CODE(LINKS(B3:G7))&","&TEIL(B3:G7;2;3));""));0;-1))}
3
4
5
6
7
8
9
10
11
12
13
14
15

Die 3 verwendeten UDFs sind hier zu finden:
DataSet (Vs1.3)
VJoin (1.4) & VSplit (1.1) → BspDatei
Gruß, Castor

[Rabe]

Hi Martin,

PowerQuery kann ich leider auch nicht verwenden, da ich das Plugin hierzu installieren müsste und hier keine Berechtigung dazu hab (trotzdem vielen Dank für den Tipp, ich kannte das gar nicht).

hast Du das Installieren eines AddIns ausprobiert und es geht nicht oder vermutest Du nur, daß es nicht geht, weil Du keine Admin-Berechtigung hast?

Denn:
Das Installieren von AddIns geht auch als absolut normaler Nutzer ohne jegliche Admin-Berechtigung.

[snb]

@Rabe

Auch in XP ?