Liste aller vorkommenden Werte erzeugen
Jens Müller
gegeben ist eine Tabelle, die (leider) nur aus einer Spalte besteht:
A
C
C
A
D
E
A
C
B
usw.
jetzt m�chte ich herausfinden, wie oft jeder Wert vorkommt. Das w�re ja
mit Z�HLENWENN kein Problem, ich brauche allerdings eine Liste mit den
vorkommenden Werten, da die Spalte ca. 750 Werte umfasst. Gibt es daf�r
eine Formel?
Gru�
Jens
--
"The only proof that intelligent life exists in the universe
is that it hasn't tried to contact us yet." - Calvin
Andreas Killer
> gegeben ist eine Tabelle, die (leider) nur aus einer Spalte besteht:
...
> jetzt möchte ich herausfinden, wie oft jeder Wert vorkommt. Das wäre ja
> mit ZÄHLENWENN kein Problem, ich brauche allerdings eine Liste mit den
> vorkommenden Werten, da die Spalte ca. 750 Werte umfasst. Gibt es dafür
> eine Formel?
Nein, aber unter Daten\Filter einen Spezialfilter mit dem Du die
Dublikate ausfiltern kannst. (Dieser setzt zwingend voraus das die
Daten eine Überschrift haben.)
Andreas.
Jens Müller
> On 5 Aug., 09:06, Jens M�ller <craw...@web.de> wrote:
>
>> gegeben ist eine Tabelle, die (leider) nur aus einer Spalte besteht:
> ...
>> vorkommenden Werten, da die Spalte ca. 750 Werte umfasst. Gibt es daf�r
>> eine Formel?
> Nein, aber unter Daten\Filter einen Spezialfilter mit dem Du die
> Dublikate ausfiltern kannst. (Dieser setzt zwingend voraus das die
Autsch! Und ich suche 'Stunden' nach einer Formel...
1000 Dank & Gru�
Alexander Wolff
> On 5 Aug., 09:06, Jens M�ller <craw...@web.de> wrote:
>
>> gegeben ist eine Tabelle, die (leider) nur aus einer Spalte besteht:
>> w�re ja mit Z�HLENWENN kein Problem, ich brauche allerdings eine
>> Liste mit den vorkommenden Werten, da die Spalte ca. 750 Werte
> Nein,
Doch. http://excelformeln.de/formeln.html?welcher=194. Eine der am
h�ufigsten zitierten L�sungen.
Wobei Spezialfilter schneller geht, das stimmt.
Noch schneller: Pivottabelle - in Spalten dann einfach Deine Spalte. Dann
die Vorspalte f�r Z�hlenwenn kopieren - oder gleich Pivot daf�r verwenden.
--
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2
Bernd P
Ich empfehle meine Funktion Pfreq:
http://sulprobil.com/html/listfreq.html
Wer wissen will, warum ich andere Ansätze für weniger geeignet halte:
http://sulprobil.com/html/excel_don_ts.html
(3. Eintrag)
und
http://sulprobil.com/html/count_unique.html
Viele Grüße,
Bernd
Andreas Killer
> Ich empfehle meine Funktion Pfreq:
> http://sulprobil.com/html/listfreq.html
Da f�llt mir wieder eine Frage ein, unabh�ngig ob ich nun Deine oder
Alexanders L�sung nehme:
Beide L�sungen m�ssen ja als Matrix-Formel in ein Tabellenblatt
eingegeben werden und das hat einen kleinen Haken, gerade auch dann
wenn sich der Quell-Datenbereich �ndert.
(Ich hatte letztens auch diese M�glichkeit in Erw�gung gezogen, sie
aber dann wegen dieses Problems verworfen:)
Also simples Beispiel:
Function Matrix(Bereich As Range) As Variant
Dim M(), X As Long, Y As Long, R As Range
ReDim M(1 To Bereich.Rows.Count, 1 To Bereich.Columns.Count)
For Y = 1 To Bereich.Rows.Count
For X = 1 To Bereich.Columns.Count
M(Y, X) = Bereich(Y, X)
Next
Next
Matrix = M
End Function
Jetzt gebe ich in in A1 bis A4 Daten ein und in A6 bis A9 die
Matrix-Formel {=Matrix(A1:A4)} ein und erhalte wunderbar quasi eine
Kopie der Daten.
L�sche ich nun eine Zeile, dann kann ich im Bereich der Matrixformel
#NV sehen. F�ge ich aber eine Zeile hinzu, dann sehe ich dies nicht im
Matrixbereich und erhalte auch keine Meldung, die Daten fehlen einfach.
Gibt es eine M�glichkeit zu pr�fen ob der Bereich in den die
Matrixformel eingegeben wurde ausreicht?
Anders gefragt: Wie kann ich sicher sein das meine Matrixformel einen
ausreichenden Bereich abdeckt?
Ich hab schon mal
Dim V As Variant
V = Application.Caller
probiert um an den Eingabebereich zu kommen, aber das ist anscheinend
nicht erlaubt, denn entweder gibt die function nun einfach Bl�dsinn
zur�ck, oder ich erhalte eine Fehlermeldung wegen Zirkelbezugs.
Andreas.
Bernd P
Den Ausgabebereich kann man größenmäßig via VBA testen. Z. B. zum
Ablaufzeitpunkt, siehe
http://sulprobil.com/html/reshape.html
oder auch beim Start des Makros, siehe
http://sulprobil.com/html/uniqrandint.html
Aber auch ohne Makros kann man Matrixformeln einfach für
Zwischenergebnisse nutzen und die Standardfehlerwerte (falls der
Ausgabebereich zu groß ist) später (in einem Endausgabebereich)
abfangen / einfach nicht zeigen. Hier ist nur meine These, dass
Matrixformeln für ernsthafte Anwendungen eher untauglich sind ...
Viele Grüße,
Bernd
Andreas Killer
> Den Ausgabebereich kann man größenmäßig via VBA testen. Z. B. zum
> Ablaufzeitpunkt, siehehttp://sulprobil.com/html/reshape.html
Hmm, dann muss ich ja irgendwas falsch gemacht haben das das bei mir
nicht ging...
BTW, schicke Function das ReShape. Ich brauchte heute sowas in der Art
um einen einspaltigen Bereich in einen zweizeiligen umzuwandeln.
Allerdings macht ReShape das nur zeilenweise, ich brauchte aber
spaltenweise. Da hab noch mal ein paar Zeilen dazugetackert,
vielleicht findest Du es ja brauchbar.
> abfangen / einfach nicht zeigen. Hier ist nur meine These, dass
> Matrixformeln für ernsthafte Anwendungen eher untauglich sind ...
Da kann ich Dir nur zustimmen.
Andreas.
Function ReShape(v As Variant, _
Optional ByRows As Boolean = False) As Variant
'Algorithm by: http://Reverse("moc.liborplus.www")
'PB 1-Aug-2009 V0.2
Dim vR As Variant, vI As Variant
Dim i As Long, j As Long
With Application.Caller
ReDim vR(1 To .Rows.Count, 1 To .Columns.Count)
i = 1
j = 1
If TypeOf v Is Range Then
If v.Rows.Count * v.Columns.Count > _
.Rows.Count * .Columns.Count Then
vR(i, j) = "Formula input field is too small"
ReShape = vR
Exit Function
End If
End If
For Each vI In v
vR(i, j) = vI
If ByRows Then
i = i + 1
If i > .Rows.Count Then
i = 1
j = j + 1
If j > .Columns.Count Then
ReShape = vR
Exit Function
End If
End If
Else
j = j + 1
If j > .Columns.Count Then
j = 1
i = i + 1
If i > .Rows.Count Then
ReShape = vR
Exit Function
End If
End If
End If
Next vI
fillcverrval:
vR(i, j) = CVErr(xlErrNA)
If ByRows Then
i = i + 1
If i > .Rows.Count Then
i = 1
j = j + 1
If j > .Columns.Count Then
ReShape = vR
Exit Function
End If
End If
Else
j = j + 1
If j > .Columns.Count Then
j = 1
i = i + 1
If i > .Rows.Count Then
ReShape = vR
Exit Function
End If
End If
End If
GoTo fillcverrval
End With
End Function
Bernd P
hab gerade wenig Zeit, aber da fehlte doch lediglich ein Transpose
(Transposition) vor und/oder nach der Verarbeitung, nicht wahr?
Das mache ich üblicherweise im Worksheet (Tabellenblatt).
Deine Erweiterung sieht aber gut aus. Nur: Ich gebe am liebsten keine
Text-Fehlermeldungen aus, sondern harte Fehlercodes. Diese pflanzen
sich dann fort in alle abhängigen Zellen und der Anwender kann nicht
unabsichtlich "gute" Ergebnisse erhalten.
Viele Grüße,
Bernd
Andreas Killer
> hab gerade wenig Zeit, aber da fehlte doch lediglich ein Transpose
> (Transposition) vor und/oder nach der Verarbeitung, nicht wahr?
Tja naja, ein MTRANS(ReShape) wäre wohl möglich gewesen?
Aber da ich gerade mit Matrixformeln immer ziemlich ahnungslos im
Nebel rumstochere war's mir so viel viel einfacher. :-)
Andreas.
Bernd P
OT: Ich habe die Option bByRow in mein Reshape eingebaut. Durch
Vertauschung der Indizes bleibt der Code kurz (geht etwas zu Lasten
der Übersichtlichkeit).
Viele Grüße,
Bernd