Alle verschiedenartigen Einträge einer Spalte

PantherX · 30. Dezember 2008 um 10:43

Hallo die Experten,

ich habe eine tausende Zeilen lange Spalte mit verschiedenen Einträgen. Ich möchte wissen, welche Einträge das sind und wie oft sie vorkommen. Ich weiß nicht, was alles da drinsteht.
dh. zunächst bräuchte ich eine Formel, die mir jeden einzelnen unterschiedlichen Eintrag EINMAL ausgibt. Anschließend kann ich ja leicht die Anzahl bestimmen.
Aber wie kriege ich alle unterschiedlichen Einträge heraus?
Mit einem Filter, klar, da sehe ich sie, aber ich bräuchte sie ja jeweils in einer Zelle stehen, damit ich mit ihnen rechnen kann.
Any ideas?

Viele Grüße

Jerry

Reinhard_fb8cb7 · 30. Dezember 2008 um 11:12

unterschiedliche Einträge auflisten und zählen

ich habe eine tausende Zeilen lange Spalte mit verschiedenen
Einträgen. Ich möchte wissen, welche Einträge das sind und wie
oft sie vorkommen. Ich weiß nicht, was alles da drinsteht.
dh. zunächst bräuchte ich eine Formel, die mir jeden einzelnen
unterschiedlichen Eintrag EINMAL ausgibt. Anschließend kann
ich ja leicht die Anzahl bestimmen.

Hi Jerry,

den Hilfsspalten D und E kannst du ja als Schriftfarbe die Zellenfarbe zuweisen.

Tabellenblatt: [Mappe48]!Tabelle1
 │ A │ B │ C │ D │ E │
──┼───┼───┼───┼───┼────────┤
1 │ a │ a │ 3 │ a │ a │
──┼───┼───┼───┼───┼────────┤
2 │ b │ b │ 2 │ b │ b │
──┼───┼───┼───┼───┼────────┤
3 │ a │ c │ 1 │ │ c │
──┼───┼───┼───┼───┼────────┤
4 │ c │ f │ 2 │ c │ f │
──┼───┼───┼───┼───┼────────┤
5 │ f │ │ │ f │ #ZAHL! │
──┼───┼───┼───┼───┼────────┤
6 │ b │ │ │ │ #ZAHL! │
──┼───┼───┼───┼───┼────────┤
7 │ a │ │ │ │ #ZAHL! │
──┼───┼───┼───┼───┼────────┤
8 │ f │ │ │ │ #ZAHL! │
──┴───┴───┴───┴───┴────────┘
Benutzte Formeln:
B1: =WENN(ISTFEHLER(E1);"";E1)
B2: =WENN(ISTFEHLER(E2);"";E2)
B3: =WENN(ISTFEHLER(E3);"";E3)
B4: =WENN(ISTFEHLER(E4);"";E4)
B5: =WENN(ISTFEHLER(E5);"";E5)
B6: =WENN(ISTFEHLER(E6);"";E6)
B7: =WENN(ISTFEHLER(E7);"";E7)
B8: =WENN(ISTFEHLER(E8);"";E8)
C1: =WENN(B1="";"";ZÄHLENWENN(A:A;B1))
C2: =WENN(B2="";"";ZÄHLENWENN(A:A;B2))
C3: =WENN(B3="";"";ZÄHLENWENN(A:A;B3))
C4: =WENN(B4="";"";ZÄHLENWENN(A:A;B4))
C5: =WENN(B5="";"";ZÄHLENWENN(A:A;B5))
C6: =WENN(B6="";"";ZÄHLENWENN(A:A;B6))
C7: =WENN(B7="";"";ZÄHLENWENN(A:A;B7))
C8: =WENN(B8="";"";ZÄHLENWENN(A:A;B8))
D1: =WENN(ZÄHLENWENN($A$1:A1;A1)=1;A1;"")
D2: =WENN(ZÄHLENWENN($A$1:A2;A2)=1;A2;"")
D3: =WENN(ZÄHLENWENN($A$1:A3;A3)=1;A3;"")
D4: =WENN(ZÄHLENWENN($A$1:A4;A4)=1;A4;"")
D5: =WENN(ZÄHLENWENN($A$1:A5;A5)=1;A5;"")
D6: =WENN(ZÄHLENWENN($A$1:A6;A6)=1;A6;"")
D7: =WENN(ZÄHLENWENN($A$1:A7;A7)=1;A7;"")
D8: =WENN(ZÄHLENWENN($A$1:A8;A8)=1;A8;"")

Benutzte Matrixformeln:
E1: {=WENN(ZEILE(D1)\>ANZAHL2(D:smiley:);"";INDEX(D:smiley:;KKLEINSTE(WENN(D$1:smiley:$1000"";ZEILE($1:blush:1000));ZEILE(D1))))}
E2: {=WENN(ZEILE(D2)\>ANZAHL2(D:smiley:);"";INDEX(D:smiley:;KKLEINSTE(WENN(D$1:smiley:$1000"";ZEILE($1:blush:1000));ZEILE(D2))))}
E3: {=WENN(ZEILE(D3)\>ANZAHL2(D:smiley:);"";INDEX(D:smiley:;KKLEINSTE(WENN(D$1:smiley:$1000"";ZEILE($1:blush:1000));ZEILE(D3))))}
E4: {=WENN(ZEILE(D4)\>ANZAHL2(D:smiley:);"";INDEX(D:smiley:;KKLEINSTE(WENN(D$1:smiley:$1000"";ZEILE($1:blush:1000));ZEILE(D4))))}
E5: {=WENN(ZEILE(D5)\>ANZAHL2(D:smiley:);"";INDEX(D:smiley:;KKLEINSTE(WENN(D$1:smiley:$1000"";ZEILE($1:blush:1000));ZEILE(D5))))}
E6: {=WENN(ZEILE(D6)\>ANZAHL2(D:smiley:);"";INDEX(D:smiley:;KKLEINSTE(WENN(D$1:smiley:$1000"";ZEILE($1:blush:1000));ZEILE(D6))))}
E7: {=WENN(ZEILE(D7)\>ANZAHL2(D:smiley:);"";INDEX(D:smiley:;KKLEINSTE(WENN(D$1:smiley:$1000"";ZEILE($1:blush:1000));ZEILE(D7))))}
E8: {=WENN(ZEILE(D8)\>ANZAHL2(D:smiley:);"";INDEX(D:smiley:;KKLEINSTE(WENN(D$1:smiley:$1000"";ZEILE($1:blush:1000));ZEILE(D8))))}
(Matrixformeln nicht mit "Enter" sondern mit "Strg+Shift+Enter" eingeben.
Die Spezialklammern nicht manuell eingeben, sie werden von Excel erzeugt.)
A1:E8
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

PantherX · 30. Dezember 2008 um 11:48

Perfekt, funktioniert, vielen Dank! (owT)

.

Niclaus_Wurthberg · 30. Dezember 2008 um 14:48

ich habe eine tausende Zeilen lange Spalte mit verschiedenen
Einträgen. Ich möchte wissen, welche Einträge das sind und wie
oft sie vorkommen. Ich weiß nicht, was alles da drinsteht.
dh. zunächst bräuchte ich eine Formel, die mir jeden einzelnen
unterschiedlichen Eintrag EINMAL ausgibt. Anschließend kann
ich ja leicht die Anzahl bestimmen.

Benutzte Formeln:
B1: =WENN(ISTFEHLER(E1);"";E1)
C1: =WENN(B1="";"";ZÄHLENWENN(A:A;B1))
D1: =WENN(ZÄHLENWENN($A$1:A1;A1)=1;A1;"")
Benutzte Matrixformeln:
E1:
{=WENN(ZEILE(D1)>ANZAHL2(D:smiley:);"";INDEX(D:smiley:;KKLEINSTE(WENN(D$1:smiley:$1000"";ZEILE($1:blush:1000));ZEILE(D1))))}
Gruß
Reinhard

Hallo Reinhard

Wie immer: Toll!

Da Thomas scheinbar abwesend ist, sage ich, was er sicher sagen würde: Das ginge doch auch mit einer Pivot-Tabelle.

Und noch etwas: Ich habe eine Liste mit 14664 Vornamen. Wenn ich sie mit Deinen Formeln bearbeite, erhalte ich als Ergebnis 458 verschiedene Vornamen. Und wenn ich die Anzahl Einträge in Deiner Spalte C berechne, komme ich auf 10593 verschiedene Vornamen-Träger. Das müssten aber doch 14664 sein? - Sind 14664 Zeilen zu viel für Excel?

Mit Pivot komme ich auf 2301 verschiedene Vornamen und auf die richtigen 14664 verschiedenen Vornamen-Träger.

Was bei Pivot aber seltsam ist: Das Ergebnis sollte ja alphabetisch sortiert sein. Es beginnt aber mit Thu, August, Jan, dann folgt eine für mich unerklärliche Leer-Zelle mit dem Eintrag 1. Dann erst beginnt es „richtig“ alphabetisch.

Ich habe die Datei hochgeladen. Achtung, sie ist recht gross, über 6000 KB
http://www.hostarea.de/server-12/Dezember-27987aecf6…
Hier noch in gezippter Version mit 1270 KB
http://www.hostarea.de/server-12/Dezember-cb2fb846b7…

Vielen Dank für Deine Bemühungen. Und noch 34 angenehme Stunden im alten Jahr und alles Beste fürs neue Jahr.
Niclaus

Reinhard_fb8cb7 · 30. Dezember 2008 um 16:11

Gruezi Niclaus,

Da Thomas scheinbar abwesend ist, sage ich, was er sicher
sagen würde: Das ginge doch auch mit einer Pivot-Tabelle.

da wirst du Recht haben. Ich mutmaß, als die schweizer Grenzstationen aufgelöst wurden kamen jahrhunderte alte Gene bei Thomas wieder durch.
Er wird irgendwas über die Berge schmuggeln *annehm*))

Und noch etwas: Ich habe eine Liste mit 14664 Vornamen. Wenn
ich sie mit Deinen Formeln bearbeite, erhalte ich als Ergebnis
458 verschiedene Vornamen.

Du hast Recht, es sind 2301 verschiedene Vornamen, das sagt auch Vba.

Ersetze bitte die 1000 in Spalte E durch 15000 und sage mir ob dann auch 2301 verschiedene aufgelistet werden.

Ich kann das leider nicht machen, mein Excel2000 nippelt schon ab wenn ich nur 30 Matrixformeln kopiere:frowning:

Was bei Pivot aber seltsam ist: Das Ergebnis sollte ja
alphabetisch sortiert sein. Es beginnt aber mit Thu, August,
Jan, dann folgt eine für mich unerklärliche Leer-Zelle mit dem
Eintrag 1. Dann erst beginnt es „richtig“ alphabetisch.

Ich stapel nicht tief mit Pivots, ich hab da echt keine Ahnung von. Ich kann dir gern ein Bild von der Menüleiste machen, da wo man auf Pivot klickt sind nur 2-3 Kurzklickeindrücke zu sehen wenn du mit dem Rastermikroskop das Bild anschaust.

Die Leerzeile liegt daran daß da in A8 ein Leerzeichen ist, Excel sieht das als Vornamen an und da 32 kleiner ist als 65 als steht es vor dem ersten Namen mit A…
Woher da Thu, Jan, August kommen weiß ich absolut nicht.

Gruß
Reinhard

Niclaus_Wurthberg · 30. Dezember 2008 um 20:30

Hallo Reinhard
Vielen Dank

Ersetze bitte die 1000 in Spalte E durch 15000

Hätte ich auch selber drauf kommen können. Schön blöd von mir.

Was bei Pivot aber seltsam ist: Das Ergebnis sollte ja
alphabetisch sortiert sein. Es beginnt aber mit Thu, August,
Jan, dann folgt eine für mich unerklärliche Leer-Zelle mit dem
Eintrag 1. Dann erst beginnt es „richtig“ alphabetisch.

Die Leerzeile liegt daran daß da in A8 ein Leerzeichen ist,
Excel sieht das als Vornamen an und da 32 kleiner ist als 65
als steht es vor dem ersten Namen mit A…

Die Leer-Zelle habe ich gefunden. Im Original ist tatsächlich A3221 leer. Noch einmal blöd von mir.

Woher da Thu, Jan, August kommen weiß ich absolut nicht.

Ueberlassen wir das Thomas, damit er auch noch was zu „hirnen“ hat.

Noch einmal vielen Dank und viele Grüsse
Niclaus

Thomas_Ramel · 31. Dezember 2008 um 11:28

Grüezi zusammen

Was bei Pivot aber seltsam ist: Das Ergebnis sollte ja
alphabetisch sortiert sein. Es beginnt aber mit Thu, August,
Jan, dann folgt eine für mich unerklärliche Leer-Zelle mit dem
Eintrag 1. Dann erst beginnt es „richtig“ alphabetisch.

Woher da Thu, Jan, August kommen weiß ich absolut nicht.

Ueberlassen wir das Thomas, damit er auch noch was zu „hirnen“
hat.

Na, wenn ihr schon alle Arbeit macht, bin ich froh wenn ich auch noch was beitragen kann/darf

Die Mappe ist im Übrigen ein schönes Beispiel dafür, dass Pivot-Tabellen absolut ihre Daseins-Berechtigung haben.
Beim öffnen der Mappe wurde diese erstmal komplett durchgerechnet und brauchte dazu mehr als 1 Minute- wegen der vielen Matrixformeln eben…

Aber zurück zum eigentlichen Punkt.
Excel berücksichtigt beim sortieren (wenn gewünscht) benutzerdefinierte Listen die in den Optioen angelegt werden können.
Hier sind dann Datums- und Wochentags- Abkürzungen in englischer und deutscher Sprache hinterlegt, denen Vorrang vor der alphabetischen Sortierung gegeben wird - die drei Begriffe ‚Thu‘ ‚Jan‘ und ‚August‘ (in den Quelldaten sind sie echt enthalten) sind genau in dieser Reihenfolge in den benutzerdefinierten Listen aufgeführt.

In den Optionen der PT kann diese Sortier-Besonderheit dann abgestellt werden und die PT sortiert wie erwartet.

BTW:
Den Zug gen Süden habe ich noch nicht angetreten…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Niclaus_Wurthberg · 31. Dezember 2008 um 14:17

Grüezi zusammen

Woher da Thu, Jan, August kommen weiß ich absolut nicht.

Ueberlassen wir das Thomas, damit er auch noch was zu „hirnen“
hat.

Na, wenn ihr schon alle Arbeit macht, bin ich froh wenn ich
auch noch was beitragen kann/darf

Aber zurück zum eigentlichen Punkt.
Excel berücksichtigt beim sortieren (wenn gewünscht)
benutzerdefinierte Listen die in den Optioen angelegt werden
können.
Hier sind dann Datums- und Wochentags- Abkürzungen in
englischer und deutscher Sprache hinterlegt, denen Vorrang vor
der alphabetischen Sortierung gegeben wird - die drei Begriffe
‚Thu‘ ‚Jan‘ und ‚August‘ (in den Quelldaten sind sie echt
enthalten) sind genau in dieser Reihenfolge in den
benutzerdefinierten Listen aufgeführt.

In den Optionen der PT kann diese Sortier-Besonderheit dann
abgestellt werden und die PT sortiert wie erwartet.

BTW:
Den Zug gen Süden habe ich noch nicht angetreten…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Salü Thomnas
Vielen Dank.
So wird man auch in den letzten Minuten des Jahres klüger!
Es guets neus!
Niclaus

Niclaus_Wurthberg · 4. Januar 2009 um 15:44

Hallo Thomas

Ich habe doch noch eine Frage dazu. Das mit den benutzerdefinierten Listen ist mir klar. Aber folgendes:

In den Optionen der PT kann diese Sortier-Besonderheit dann
abgestellt werden und die PT sortiert wie erwartet.

Ich finde unter den PT-Optionen keine Möglichkeit, diese Sortierung ein- oder auszuschalten. Ich arbeite mit Excel 2003.

Vielen Dank für Deine Bemühungen und viele Grüsse
Niclaus

Thomas_Ramel · 4. Januar 2009 um 16:17

Grüezi Niclaus

Ich habe doch noch eine Frage dazu. Das mit den
benutzerdefinierten Listen ist mir klar. Aber folgendes:

In den Optionen der PT kann diese Sortier-Besonderheit dann
abgestellt werden und die PT sortiert wie erwartet.

Ich finde unter den PT-Optionen keine Möglichkeit, diese
Sortierung ein- oder auszuschalten. Ich arbeite mit Excel
2003.

Da musst Du dann in den Eigenschaften des einzelen Feldes nachsehen.

Rechtsklick in die betreffende Spalte
Feldeigenschaften
[Weitere…]

Hier müsste dann die Sortierung zu finden sein.

Ich hatte mich auf xl2007 bezogen, da ist diese Option unter den Tabellen-Optionen der PT untergebracht.

Mit freudlichen Grüssen

Thomas Ramel

MVP für MS-Excel -