Anzahl unterschiedliche Werte

NoNet · 1970-01-01T00:00:00+00:00

Hallo Excel-Profis, ich hab hier ein Problem mit ZÄHLENWENN() bzw. mit der Ermittlung unterschiedlicher Werte in einem diskontinuierlichen Bereich. Für einen kontinuierlichen Bereich (z.B. A1:B10) kann man die Anzahl der unterschiedlichen Werte mit folgender MATRIX-Funktion ermitteln : {=SUMME(WENN(A1:B10="";0;1/ZÄHLENWENN(A1:B10;A1: B10)))} Definiert man für einen kontinuierlichen Bereich einen Namen (z.B. "Bereich1"), so funktioniert das entsprechend : {=SUMME(WENN(Bereich1="";0;1/ZÄHLENWENN(Bereich1;Bereich1)))} Sobald der Bereich jedoch diskontinuierlich ist (also aus zusammenhanglosen Bereichen besteht oder aus einer unterschiedlichen Anzahl Zeilen/Spalten, so dass sich bei Markierung kein rechteckiger Bereich ergibt, dann funktioniert dieses Konstrukt nicht mehr :-( Hier zwei Beispiele : Bereich1 ist kontinuierlich (orange markiert), Bereich2 ist diskontinuierlich (blauer Bereich in Spalten D:E). Für Bereich2 hätte ich gerne das Ergebnis 5, da im blauen Bereich 5 unterschiedliche Werte stehen ! A B C D E 1 2 3 4 5 6 7 8 9 10 11 12 MATRIX-Funktionen (mit Strg+Shift+ENTER bestätigen) ZelleFormel B4 {=SUMME(WENN(Bereich1="";0;1/ZÄHLENWENN(Bereich1;Bereich1))) D4 {=SUMME(WENN(Bereich2="";0;1/ZÄHLENWENN(Bereich2;Bereich2))) Die Funktion in D4 ergibt leider den Fehlerwert #WERT! Ich habe auf folgender Seite bereits einen Lösungsansatz per INDIREKT() gefunden : http://www.excelformeln.de/formeln.html?welcher=359 aber : 1.) ist mir INDIREKT() zu starr (der Bereich sollte flexibel sein - je nach Definition des Namens Bereich2 ) 2.) muss die Lösung noch in das Konstrukt SUMME(WENN(..;ZÄHLENWENN(...))) - oder ähnliches - eingebaut werden, um die Anzahl unterschieldlicher Werte zu zählen Ich möchte übrigens keine VBA-Lösung (die könnte ich mir auch selbst basteln ;-), sondern eine Lösung auf Basis von Formeln/Funktionen ! Danke für eure Hilfe, Gruß NoNet

Zelle	Formel
B4	{=SUMME(WENN(Bereich1="";0;1/ZÄHLENWENN(Bereich1;Bereich1)))
D4	{=SUMME(WENN(Bereich2="";0;1/ZÄHLENWENN(Bereich2;Bereich2)))

…NoNet;
es ginge wohl auch mit einer Maske, falls das nicht zu aufwendig wird. Irgendwie, per Hand, Textfktt oder VBA einen Text in Form einer Matrixkonstante als quasi Bereichsmaske erzeugen. Hier müssen dann zumindest alle immer irrelevanten Bereichspositionen (ifür die rechteckige Gesamt-Matrix, nicht nur die Mehrfachauswahl) als 0 (bzw -1 oder anderer abfragbarer Wert) angegeben wdn. Alles andere ist 1. Auf die übliche Weise erzeugst du eine Wertematrix des Gesamtbereichs, wobei du die Leerzellen ausschließt (kann der gleiche Wert wie in der Text-Matrixkonstanten sein). Eingetragene Nullen willst du vermutlich in die Zählung einbeziehen. Denen musst du einen anderen Wert geben, falls für die entfallenden Werte 0 vergeben wurde. Die Textmatrixkonstante wertest du in benannter Fml aus (alternativ kannst du die Text-MxKonst auch als echte MxKonst direkt in die Fml schreiben → hängt davon ab, was praktikabler ist). Dann kannst du das Datenfeld aus dem beschriebenen FmlAusdruck mit dem Namen der MaskenFml multiplizieren und du hast genau die Zellen, die du für die Zählung brauchst. Weiter habe ich das noch nicht ausprobiert, denn ZÄHLENWENN fktt ja dummerweise bei Datenfeldern nicht. Das hat der Pgmmierer wohl verschlafen, denn das wäre ja wohl kein Problem gewesen, wie Dutzende andere xlFmln und VBA beweisen. Übrigens auch so ein (nicht ganz so alter) Zopf, der endlich „abgeschnitten”, sprich korrigiert gehört. Hätte ja auch keine Negativfolgen für bestehende Fmln.
Es gäbe natürlich auch die Möglichkeit, den zuvor erwähnten linearen Vektor automatisiert, ggf auch als Text-MxKonst, zu erzeugen, aber das ist etwas komplizierter und hängt doch stärker von den jeweiligen Gegebenheiten ab. Gerade für solche Fälle hatte ich vor Jahren mal 'ne udFkt geschrieben, die so etwas (die Auswahl) bewerkstelligt und die Zellen als Objekte zurückgibt, deren Inhalt man dann mit anderer udFkt zu einem Datenfeld-Vektor (auch ohne Leerfelder) verbinden kann. Allerdings kann auch INDEX mit einer MxKonstMaske (mit Zellpositionsangaben) arbeiten, allerdings nur für Zeilen oder Spalten, nicht ganze Matrizen. Die dann zu einem Vektor zu verbinden, wäre das Problem. Aber ggf könnte man die ja auch einzeln verarbeiten.
So das waren also 2-3 Anregungen, evtl bringt dich (oder Andere) das auf die entscheidende Idee. Ich werde heute nämlich nicht viel Zeit haben und so lange willst du ja viell nicht warten… ;-)
Gruß und nochmals schöWE, Luc :-?

…NoNet,
aber bspw für Erich blieben ja noch Fragen offen… ;-)
Da hätte ich evtl noch etwas, denn mir ist es inzwischen gelungen, den benötigten Gesamtvektor auch ohne udF und Hilfszellen zu erzeugen. Ob das Ganze dann auch in dieser oder abgewandelter Form mit Texten fktt, käme auf entsprd Tests an. Wichtig war mir, einen solchen Vektor zu erzeugen, mit dem dann auch andere Fktt etwas anfangen können. Das wäre dann wie in anderen Fällen auch in Kombi von N mit INDIREKT der Fall. Übrigens die einzig sinnvolle Anwendung von N über den von Xl vorgesehenen Zweck hinaus. Mit INDEX wäre Vglbares nicht möglich. Offensichtl liefert INDIREKT einen Variant, der einen Vektor enthält, der aus quasi 0dimensionalen Vektoren besteht (gibt's in xl/VBA praktisch natürl nicht → nur 1/2-dimensional!). Das scheint gerade noch von N bewältigt wdn zu können (in der BspAbb BerVekt2).
HÄUFIGKEIT besticht hier natürl dadurch, dass gleich der benötigte Teilergebnisvektor geliefert wird, und das, obwohl die xlHilfe ausdrücklich einen zusammenhängenden Bereich als Arg1 vorschreibt. Aber siehe da, dank Sepp wissen wir, dass das auch geht! Deshalb habe ich das in der minimal benötigten Form in mein Bsp eingefügt. Die Werte und ihre Anordnung stimmen übrigens nicht mit der Originalvorgabe überein (wg Test und weil aus dem Gedächtnis an nicht-vernetztem PC entstanden).
Userbild

Übrigens ist der so entstandene Vektor der Einzelsummen ebenfalls transponier-resistent, weshalb MMULT zum Einsatz kommen musste (die unleserliche Stelle in den F/H45-ZFmln lautet 1^BerVekt…; das &"*" in irrelevanter H45-ZFml entfällt natürl normalerweise). Die Sache mit der 0 habe ich auch als irrelevant entschieden, weil in diesen Fällen die 0 durch Runden entsteht. Sie darf dann nur nicht nur als 0 eingetragen bzw per Fkt gerundet wdn. Man lässt beim Runden von Zahlen, mit denen weitergerechnet wdn soll, ohnehin besser immer eine Dezimale mehr stehen als angezeigt wird. Könnte hier natürl zum Problem wdn, falls der Originalwert zu klein ist.
Viel Erfolg jedem, der das brauchen kann!
Gruß Luc :-?

	B	C	D	E	F	G	H	I
1	1	2		3		0	0	4
2	5,5	3,1	3,5	1,1		0	1
3						1	0
4		3,6				1	1
5	4	6,6				4	0
6						4	1
7						5	1
8						6	0

Forumthread: Anzahl unterschiedliche Werte

13
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Anzahl unterschiedlicher Werte in Excel ermitteln

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)