Arrayformel findet immer einen Wert zu wenig
12.08.2003 17:03:19
dietrich
ein Problem, von dem ich gehofft hatte, es gelöst zu haben, bereitet mir weiterhin Schwierigkeiten...
Ich hab hier einen komischen Effekt bei einer Arrayformel, den Ihr vielleicht (er)kennt:
Eine Tabellenspalte hat insgesamt 153 Zeileneinträge, die sich aus mehrfach vorkommenden Werten (Texten) zusammensetzt. In diesem Fall sind es 4 einzelne Werte, die mehrfach vorkommen. Mit der untenstehenden Formel möchte ich diese 4 Werte zur Weiterverarbeitung identifizieren. Ich habe inzwischen gelernt, dass dies mit einer Arrayformel gut machbar ist (Tips von Knut und Hajo). Ich verwende dazu die untenstehende Formel, die ich per VBA in die Nachbarspalte der zu der durchsuchenden Spalte als Arrayformel kopiere.
Da ich weiss, dass grundsätzlich nie mehr als 10 verschiedene Werte vorkommen können, begrenze ich den Kopier-Durchlauf auf max. 10.
Die Formel lautet:
For I = 2 To 10
Range("d" & I).FormulaArray = _
"=IF(SUM(IF(R1C[-1]:R" & anzzeilen & "C[-1]="""",0,1/COUNTIF(R1C[-1]:R" & anzzeilen & "C[-1],R1C[-1]:R" & anzzeilen & "C[-1]))) < ROW(),"""",INDIRECT(""c""&SMALL(IF(COUNTIF(OFFSET(R1C[-1],,,ROW(R1C[-1]:R" & anzzeilen & "C[-1])),R1C[-1]:R" & anzzeilen & "C[-1])=1,ROW(R1C[-1]:R" & anzzeilen & "C[-1])),ROW())))"
Next
In Excel ist das Ergebnis dann:
{=WENN(SUMME(WENN(C$1:C$153="";0;1/ZÄHLENWENN(C$1:C$153;C$1:C$153))) < ZEILE();"";INDIREKT("c"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(C$1;;;ZEILE(C$1:C$153));C$1:C$153)=1;ZEILE(C$1:C$153));ZEILE())))}
Ich hab eine Beispieltabelle beigefügt, falls es dort leichter zu lesen ist ;-)
Die Datei https://www.herber.de/bbs/user/510.xls wurde aus Datenschutzgründen gelöscht
Der Effekt: Er findet immer nur 3 Ergebnisse. Nie die 4, die tatsächlich enthalten sind.
Hat jemand hierzu vielleicht eine Idee?
Viele Grüße
Dietrich