Microsoft Excel

Herbers Excel/VBA-Archiv

Anzahl unterschiedliche Werte | Herbers Excel-Forum


Betrifft: Anzahl unterschiedliche Werte von: NoNet
Geschrieben am: 10.02.2012 11:19:28

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 !

ABCDE
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))

Tabelle eingefügt mit Syntaxhighlighter 4.15

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

  

Betrifft: AW: Anzahl unterschiedliche Werte von: Markus
Geschrieben am: 10.02.2012 11:58:53

Hallo NoNet,

Hier ein mögllicher Denkansatz....

https://www.herber.de/bbs/user/78840.xlsm

lg
Markus


  

Betrifft: Nein, Danke - nicht per FARBE zählen von: NoNet
Geschrieben am: 10.02.2012 13:13:21

Hallo Markus,

Danke für das Hilfsangebot, aber ich möchte die Zellen ja nicht über die Zellfarbe zählen - diese waren nur Verdeutlichung der Zellen des benannten Bereiches eingefärbt.

Ich suche schon eine "echte"Lösung für den benannten Bereich ;-)

Gruß und schönes WE, NoNet


  

Betrifft: Da wirst du dann wohl kein Glück haben, ... von: Luc:-?
Geschrieben am: 10.02.2012 19:46:48

…NoNet,
denn dafür müsste erst mal ein linearer Wertevektor, am besten unter Auslassung der Leerfelder, erzeugt wdn. Das kann mW keine xlStandardFkt und eine FktsKombi in einer Fml, die das könnte, ist mir auch nicht bekannt. Wäre also wohl ein klassischer Fall für Hilfszellen oder 'ne UDF, falls nicht WF, Erich oder Sepp (evtl auch mir) noch was anderes einfällt.
Gruß + schöWE, Luc :-?


  

Betrifft: Eine Idee hab' ich noch, ... von: Luc:-?
Geschrieben am: 11.02.2012 02:48:28

…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 :-?


  

Betrifft: Ohne Hilfszellen wird's wohl nichts wdn, ... von: Luc:-?
Geschrieben am: 12.02.2012 20:15:04

…Thomas,
denn auch die MaskenFmln, selbst solch spaltenweise einen Gesamtvektor organisierenden wie …
{=WENN(ISTFEHL(DatSp1);0;WENN(ISTLEER(DatSp1);-0,666;DatSp1)) +WENN(ISTFEHL(DatSp2);0;WENN(ISTLEER(DatSp2);-0,666;DatSp2)) }
…für DatSp1:=INDEX(D5:D12;{1;2;4;5;8;-1;-1;-1;-1})
und DatSp2:=INDEX(E6:E11;{-1;-1;-1;-1;-1;2;3;6;7})
…müssen erst zwischengespeichert wdn, um bspw mit …
{=SUMME(--(ABS(hilfsbereich)=MTRANS(hilfsbereich));-(hilfsbereich>=0))/2}
die Anzahl voneinander verschiedener Werte (inkl eingetragener 0-Werte, die statistisch ja etwas anderes bedeuten als keine Angabe) anzeigen zu können, was wohl damit zusammenhängt, dass hier ein Variant entsteht, der aus Einzelwerte enthaltenden Vektoren besteht. In solchen Fällen wdn die Folgewerte nur über die allgemeine Xl-Matrix-Steuerung per Zellauswahl erreichbar. Mit einzelligen MxFmln ist da nichts zu machen, auch nicht per Auswahl zusätzlicher Zellen, weil das sich wohl alles nicht miteinander verträgt. Damit wäre ein 3.Typ einzelliger MatrixFmln entdeckt, der so komplex ist, dass gar nichts mehr ohne Zwischenspeicherung geht. Immerhin auch ein positives Ergebnis! ;-)
Übrigens reagieren auch manche mehrzelligen Zwischenschritte „sauer” auf bestimmte Transaktionen (→ zB lässt sich der resultierende Gesamtvektor nicht transponieren), was somit auch einem 3.Typ mehrzelliger MxFmln entspräche.
Gruß+schöWo, Luc :-?


  

Betrifft: AW: Anzahl unterschiedliche Werte von: Rudi Maintaire
Geschrieben am: 10.02.2012 13:46:28

Hallo,
imho muss eine Matrix rechteckig sein.

Gruß
Rudi


  

Betrifft: Nicht nur dM/mM, sondern generell, Rudi! ;-) orT von: Luc:-?
Geschrieben am: 10.02.2012 19:48:50

Gruß + schöWE, Luc :-?


  

Betrifft: Würgaround mit Intervallen für Zahlen von: Erich G.
Geschrieben am: 12.02.2012 10:13:28

Hi NoNet,
einen direkten Lösungsansatz habe ich nicht gefunden - weder im Kopf noch im Netz. :-(
Auf Versuche mit dem 4. INDEX-Parameter brachten nicht weiter.

Wenn es um Zahlen geht und wenn man "unterschiedlich" approximieren kann durch "in unterschiedlichen Intervallen",
könnte folgender Ansatz (hier mit Intervallbreite 1) nützlich sein:

 BCDEFGHI
112 3 004
25,53,13,51,1 01 
3     10 
4 3,6   11 
546,6   40 
6     41 
7     51 
8     60 

Formeln der Tabelle
ZelleFormel
G1=INDEX(HÄUFIGKEIT(BerX;ZEILE(G1)-1); 0)
H1=0+(INDEX(HÄUFIGKEIT(BerX;ZEILE(G1)-1); 0)<INDEX(HÄUFIGKEIT(BerX;ZEILE(G1)-0); 0))
I1=SUMME(H:H)
Namen in Formeln
ZelleNameBezieht sich auf
G1BerX=Tabelle1!$B$2:$E$2;Tabelle1!$C$4:$C$5
H1BerX=Tabelle1!$B$2:$E$2;Tabelle1!$C$4:$C$5
Namen verstehen

Spalte G braucht man dabei natürlich nicht.

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: Würgaround mit Intervallen für Zahlen von: Josef B
Geschrieben am: 12.02.2012 21:07:09

Hallo NoNet

Wenn es sich um Zahlen handelt, dann sollte das so funktionieren

=SUMME(N(HÄUFIGKEIT(Bereich2;Bereich2)>0))

Gruss Sepp


  

Betrifft: Korrektur- und Ergänzungs- und Klärungsbedarf von: Erich G.
Geschrieben am: 13.02.2012 07:33:04

Hi Sepp,
bei der Kürze deines Beitrags bleiben naturgemäß Wünsche offen:

a) Korrektur:
Der Betreff ist nicht be- sondern weitgehend unzu-treffend. :-) Lediglich "AW:" und für Zahlen" stimmen.

b) Ergänzung:
Welche Kurzformel sollte man verwenden, wenn es sich nicht nur um Zahlen handelt?

c) Frage:
Hast du dir das jetzt gestern frisch ausgedacht?

Das finde ich einfach genial. Chapeau!

Rückmeldung wäre nicht nur nett! - Grüße aus Kamp-Lintfort von Erich

P.S.:
Noch ein historischer Blick : http://www.online-excel.de/fom/fo_read.php?f=1&bzh=-1&h=6874&ao=1


  

Betrifft: AW Klärungsbedarf von: Josef B
Geschrieben am: 13.02.2012 21:56:21

Hallo Erich

Zu a)
Auch ich mache ab und zu Fehler. Erstens wollte ich NoNet und nicht dir antworten.
Zweitens habe ich leider nicht auf den Betreff geachtet, sonst hätte ich meinen ersten Fehler vielleicht noch bemerkt.

Zu b) wenn es sich nicht nur um Zahlen handelt, habe ich weder für eine Kurz- oder Langformel eine Lösung.

Zu c) Nein diese Formel kenne ich schon über 10 Jahre, ich musste diese also nur noch in einem diskontinuierlichen Bereich testen, und es funktionierte.

Gruss Sepp


  

Betrifft: Vielen Dank an ALLE Helfer - speziell an Sepp von: NoNet
Geschrieben am: 13.02.2012 12:39:41

Hallo Sepp,

KLASSE - Das ist genau die Funktion die ich suchte !

Ich hatte auch einen Verdacht, dass es in Kombination mit HÄUFIGKEIT() klappen konnte, nur kam ich nicht auf die Syntax !

Vielen Dank auch an alle anderen Helfer für die guten Denkansätze !

@Luc : Deine Vermutung stimmte :

falls nicht WF, Erich oder Sepp (evtl auch mir) noch was anderes einfällt.
@Erich :
Welche Kurzformel sollte man verwenden, wenn es sich nicht nur um Zahlen handelt?

Das werde ich mit einer Gültigkeitsprüfung sicherstellen (nur GANZZAHLIGE Werte).

Gruß und schöne Woche,
NoNet


  

Betrifft: Für dich mag das ja erledigt sein, ... von: Luc:-?
Geschrieben am: 14.02.2012 18:17:51

…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).



Ü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 :-?


Beiträge aus den Excel-Beispielen zum Thema "Anzahl unterschiedliche Werte"