Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1500to1504
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Anzahl einmaliger Werte aus versch. Spalten/Zeilen

Anzahl einmaliger Werte aus versch. Spalten/Zeilen
08.07.2016 13:38:29
Joerschi
Hallo liebes Forum,
bei folgendem Problem komme ich nicht weiter (unter Skizze Erklärung):
Userbild
Musterdatei zu Skizze: https://www.herber.de/bbs/user/106864.xlsx
Im Beispiel gibt es eine Tabelle mit vier Wertezeilen-/spalten (A, B, C, D), welche "gespiegelt" sind (Zeile A = Spalte A ..... z. B. B23:T23 ist gleich V2:V20). Sie enthalten stets nur Zahlenwerte oder leere Felder.
Es sollen nun die Werte der kreuzweisen Daten miteinander verglichen werden und aus beiden Daten zusammen genommen (!) die Anzahl derjeniger Werte ausgegeben werden welche nicht mehrfach vorkommen.
Leerzeilen sollen ignoriert werden.
Im Beispiel oben farbig markiert der Vergleich zwischen Zeile 23 (= Reihe A) mit Spalte W (=Reihe B): da gibt es 12 nur einmalig vorkommende Werte.
Doppelte Werte werden nicht mit gerechnet (rot markiert und durchgestrichen).
Gesucht wird also eine Formelberechnung für die Zellen V23:Y26.
(meine Tabelle in der "Realität" umfasst ca. Tausend Reihen/Spalten - ein Ansatz ohne Hilfszeilen wäre daher am praktischsten falls mgl)
PS: Ich hatte bereits selbst gesucht, aber Links wie folgt haben mir nicht weitergeholfen, weil sie immer nur die Datenreihe fortlaufend betrachten (und nicht, dass die Daten von verschiedenen Orten wie Spalten/Zeilen zusammen betrachtet werden sollen):
http://www.excelformeln.de/formeln.html?welcher=42
https://support.office.com/de-de/article/Z%C3%A4hlen-der-Anzahl-eindeutiger-Werte-unter-Duplikaten-8d9a69b3-b867-490e-82e0-a929fbc1e273
Vielen Dank im Voraus für jede Hilfe.
Beste Grüße
Joerschi

54
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Anzahl einmaliger Werte aus versch. Spalten/Zeilen
08.07.2016 13:54:19
Michael
Hi Joerschi,
im Beispiel ist das so, aber sind DEFINITIV nie doppelte Werte (außer den leeren, natürlich) in je einer Spalte bzw. Zeile?
Ansonsten KANN man das formelmäßig machen, aber sehr vielen Daten würde ich fast ein Makro vorziehen: das bremst Excel nicht so aus wie derartige Formeln. Ist das genehm?
Schöne Grüße,
Michael

AW: Anzahl einmaliger Werte aus versch. Spalten/Zeilen
08.07.2016 14:03:37
Joerschi
Hi Michael,
Danke Dir wieder vorab für´s "bearbeiten".
Eine Formel wäre mir hier lieber, weil ich damit Daten (durch Weglöschung, Ergänzung etc.) laufend auf diverse Eigenschaften untersuche (kann meine Realtabelle auch auf 100 reihen/spalten kürzen, damit Excel nicht schlapp macht).
Zu den Werten:
ja. Innerhalb einer Zeile (A, B, C, ...) oder spalte selbst gibt es nie doppelte Werte.
Einige Werte werden in obiger Aufteilung erst doppelt, wenn man halt eine Reihe/Spalte gedanklich zusammenfasst und vergleicht.
Liebe Grüße
Joerschi

Anzeige
AW: Anzahl einmaliger Werte aus versch. Spalten/Zeilen
08.07.2016 14:03:38
Joerschi
Hi Michael,
Danke Dir wieder vorab für´s "bearbeiten".
Eine Formel wäre mir hier lieber, weil ich damit Daten (durch Weglöschung, Ergänzung etc.) laufend auf diverse Eigenschaften untersuche (kann meine Realtabelle auch auf 100 reihen/spalten kürzen, damit Excel nicht schlapp macht).
Zu den Werten:
ja. Innerhalb einer Zeile (A, B, C, ...) oder spalte selbst gibt es nie doppelte Werte.
Einige Werte werden in obiger Aufteilung erst doppelt, wenn man halt eine Reihe/Spalte gedanklich zusammenfasst und vergleicht.
Liebe Grüße
Joerschi

AW: Anzahl einmaliger Werte aus versch. Spalten/Zeilen
08.07.2016 14:28:19
GeorgH
Hallo Joerschi,
versuch mal in Zelle W23 folgende Formel:
=SUMMENPRODUKT(ISTNV(VERGLEICH(""&$B23:$T23;""&W$2:W$20;0))*1)+SUMMENPRODUKT(ISTNV(VERGLEICH(""&W$2:W$20;""&$B23:$T23;0))*1)
Diese Formel kann dann in die anderen Zellen V23:Y26 kopiert werden.
Viele Grüße
Georg

Anzeige
AW: Anzahl einmaliger Werte aus versch. Spalten/Zeilen
08.07.2016 14:48:07
Michael
Hi,
Georg hat's auf den Punkt gebracht.
Aber weil ich schon drangehängt bin, die umständliche Variante für W2:
{=ANZAHL(W$2:W$20)-ANZAHL(WENNFEHLER(VERGLEICH(W$2:W$20;$B23:$T23;0)/(W$2:W$20"");"x"))+
ANZAHL($B23:$T23)-ANZAHL(WENNFEHLER(VERGLEICH($B23:$T23;W$2:W$20;0)/($B23:$T23"");"x"))}
Irgenwann werd ich's auch noch lernen...
Happy Exceling,
Michael

AW: zu beachten wäre jedoch ...
08.07.2016 15:13:06
...
Hallo Joerschi,
... bei Einsatz der Formel von Georg oder meiner nachfolgend angeführten Alternative mit ZÄHLENWENN(), dass der auszuwertende Zeilen- und Spaltenbereich am Ende zumindest eine Leerzelle hat. Ansonsten ergeben sich falsche Ergebnisse.
Meine Alternativformel in V23 (nach rechts und unten kopierbar):
=SUMMENPRODUKT((ZÄHLENWENN($B23:$T23;V$2:V$20)=0)*1)+SUMMENPRODUKT((ZÄHLENWENN(V$2:V$20;$B23:$T23)=0) *1)
Gruß Werner
.. , - ...

Anzeige
Danke an Euch alle :-)
08.07.2016 15:22:28
Joerschi
Hallo Werner, Georg und Michael,
Euch allen besten Dank.
Ich experimentiere grad schon mit Georgs Vorschlag herum und sie funktioniert prima - die Alternative von Werner und "andere" Herangensweise von Michael werde ich mir jedoch auch noch anschauen.
vorab ein schönes WE und nochmals besten Dank :-)
Liebe Grüße, Joerschi

Ergänzungsfrage, wenn...
08.07.2016 16:21:49
Joerschi
... jeder Duplikat-/Mehrfachwert als "einfach" gezählt hinzuaddiert werden soll, wie müsste man das ergänzen?
(im Beispiel oben waren das 8 nicht mitgezählte Werte für den Vergleich von Reihe A und spalte B, welche hinzuaddiert würden)
(habe nach gefühlten 30 min probieren langsam das Gefühl, Tomaten auf den Augen zu haben... :- ( )
Danke vorab und liebe Grüße
Joerschi

Anzeige
AW: Duplikate als einfach dazu zählen ...
08.07.2016 16:54:52
...
Hallo Joerschi,
.. Du hattest doch geschrieben dass im jedem Zahlenbereich für sich betrachtet keine Duplikate vorkommen können, demzufolge kann es bei Vergleich zweier Bereiche auch nur Unikate oder Duplikate geben.
Teste mal in V23 folgende modifizierte Formel:

=SUMMENPRODUKT((ZÄHLENWENN($B23:$T23;V$2:V$20)
Aber Achtung bin gerade auf ein Problem beim Auswerten mit ZÄHLENWENN() gestoßen. Die Leerzellen dürfen zuvor nie einen Wert gehabt haben. Wenn ein Wert darin stand, der dann gelöscht wurde, wird der von ZÄHLENWENN() nicht als Leerzelle erkannt.
Mir fehlt es momentan jedoch an der Muse diesem Phänomen auf dem Grund zu gehen.
Gruß Werner
.. , - ...

Anzeige
OT: Sowohl Muse als auch Muße kann fehlen!
09.07.2016 00:37:37
Luc:-?
Was ist's bei dir, Werner,
evtl beides…? ;-)
Gruß & schöWE, Luc :-?

AW: OT: beides ... oder doch keins ;-) owT
09.07.2016 09:57:40
...
Gruß Werner
.. , - ...

Ich kann machen, was ich will, aber ich kann ...
09.07.2016 00:51:51
Luc:-?
…deine Aussage bzgl ZÄHLENWENN nur verifizieren, Werner,
wenn darin wenigstens noch ein TextPräfix steht.
Luc :-?

AW: ist schnell getan ...
09.07.2016 09:57:45
...
Hallo Luc,
... lade Dir nochmal Joerschis Datei: https://www.herber.de/bbs/user/106864.xlsx
Schreibe in Y23 meine Formel:
=SUMMENPRODUKT((ZÄHLENWENN($B23:$T23;Y$2:Y$20)=0)*1)+SUMMENPRODUKT((ZÄHLENWENN(Y$2:Y$20;$B23:$T23)=0) *1)
Das Ergebnis sollte dann 11 sein. Oder? Dann schreibe in die leere Zelle y19 z.B. 77. Das Ergebnis der Formel ist jetzt 12.Soweit korrekt. Nun lösche die die 77 wieder. Und, wie ist dann das Ergebnis der Formel bei Dir? Bei mir bleibt es 12
Ich mach jetzt erst einmal Schluss. Sehe dann erst Morgen, was hierzu noch eingestellt wurde.
Gruß Werner
.. , - ...

Anzeige
stimmt - Notnagel
09.07.2016 15:25:28
Michael
Hi Werner,
die 11 erscheint erst wieder, wenn ich Y20 auf Y19 kopiere...
Leerer als mit del geht doch nicht über die Tastatur?!
Weil es mich gejuckt hat, habe ich es doch mit Makro und auf dem zweiten Blatt mal mit mehr Daten getestet: https://www.herber.de/bbs/user/106884.xlsm
Das mal als Notnagel, falls die Formeln bei SEHR VIELEN Daten zu langsam werden sollten.
Das Makro springt an, wenn in einem der Bereiche eine Eingabe erfolgt und kalkuliert nicht alles, sondern nur die betroffenen Bereiche durch.
Schöne Grüße,
Michael

Anzeige
DEL und Backspace - verschiedene Wirkung?
09.07.2016 15:46:00
MB12
Hi Michael ua,
habe jetzt mehrmals vom Unterschied zwischen "leer" (zB in Formel "") und leer (z.B. noch nie etwas eingetragen) gelesen. Mir ist zwar klar, dass auch bei "Standard" die Zelle z.B. nach einmaliger Eingabe eines Datums als Datum formatiert ist (Auswirkung ua bei sverweis), aber
gibt es grundsätzlich beim Löschen eines Zellinhalts Unterschiede zwischen DEL und Backspace (klar, bei BSp bleibt die Zelle aktiv)?
Sorry fürs reingrätschen, M.

AW: DEL und Backspace - verschiedene Wirkung?
09.07.2016 17:01:58
Michael
Hi,
eine abschließende Antwort kann ICH Dir hier auch nicht geben; aber schreibe mal in J.s Tabelle _ in AA1 bis AH1:

Zelle	.value	.text	=empty	=""	.hasformula	celltype	typename

und stecke das Makro rein:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r&
Dim f5$
f5 = Target.Address(0)
'MsgBox f5
r = Range("AA" & Rows.Count).End(xlUp).Row + 1
Application.EnableEvents = False
Range("AA" & r) = f5
Range("AB" & r) = Range(f5).Value
Range("AC" & r) = Range(f5).Text
Range("AD" & r) = Range(f5) = Empty
Range("AE" & r) = Range(f5) = ""
Range("AF" & r) = Range(f5).HasFormula
Range("AG" & r) = CellType(Range(f5))
Range("AH" & r) = TypeName(Range(f5).Value)
Application.EnableEvents = True
Cancel = True
End Sub
Function CellType(Rng)
'Quelle:
'http://www.java2s.com/Code/VBA-Excel-Access-Word/Excel/Determiningacellsdatatype.htm
'    Set Rng = Rng.Range("A1") Das ist natürlich Quatsch...
Select Case True
Case IsEmpty(Rng)
CellType = "Blank"
Case WorksheetFunction.IsText(Rng)
CellType = "Text"
Case WorksheetFunction.IsLogical(Rng)
CellType = "Logical"
Case WorksheetFunction.IsErr(Rng)
CellType = "Error"
Case IsDate(Rng)
CellType = "Date"
Case InStr(1, Rng.Text, ":")  0
CellType = "Time"
Case IsNumeric(Rng)
CellType = "Value"
End Select
End Function
Wenn Du irgendeine Zelle anklickst, siehst Du, was man (ich) so mit VBA ermitteln kann...
Interessant ist auch der Bereich V16:V20.
Schöne Grüße,
Michael

Anzeige
sehr erhellend
09.07.2016 19:32:39
MB12
Hi Michael,
da gab es schon einige Überraschungen! Hab erst mal alles einige Male durchgetestet und mir Notizen gemacht. Soweit sind die Ergebnisse für mich verständlich.
Morgen nehme ich mir nochmal Zeit, um auch den VBA-Teil zu verstehen, einiges davon kommt mir inzwischen bekannt vor.
Wenn ich durch bin, habe ich bestimmt noch Fragen an dich; ist das ok?
Danke erst mal herzlich,
Margarete

AW: sehr erhellend
10.07.2016 12:03:28
Michael
Hi Margarete,
meinetwegen sowieso...
Ja, Mensch, so eine ähnliche Geschichte hatten wir erst vor ein paar Monaten, aber ich finde die Datei nicht mehr; da gab es noch irgendeinen Knackpunkt...
Viel Spaß beim Basteln,
Michael

Anzeige
So, nun des vermeintlichen Rätsels Lösung, ...
10.07.2016 13:31:44
Luc:-?
…Werner (& all);
das beobachtete Verhalten von ZÄHLENWENN ist auf das Problem mit Arg2 zurückzuführen, worüber wir schon mal diskutiert hat­ten (Michael hat sich erinnert) und trifft dann sicherlich auch auf alle anderen derartigen Fktt zu. Hinzu kommt, dass diese beiden FmlTeile, als Ergebnis­Vektoren dargestellt, nicht zwei, sondern sogar drei unter­schied­liche Lösungen liefern. Die dritte ist ein Ska­lar, der als alleinige Lösung erscheint, wenn man diese beiden Fml­Texte auszu­werten versucht, egal, ob mit XLM-Fkt oder UDFs auf Basis der vbFkt Evaluate (ich hab's mit 3 davon probiert). D.h., der Pgmmierer hat hier irgendeine Methode zum Auflösen des Arg2 verwendet, die sich nicht mit Fml­Text-Evaluierung verträgt, wohl, weil die Angabe ganzer Bereiche hierfür originär nicht vor­ge­sehen war. Dies löst die Xl-Steuerung auf, kolli­diert dabei aber mit der erwähnten speziellen Methode, wenn sie auf LeerZellen trifft (dazu unten mehr). Man kann dem abhelfen, indem man Arg2 als Datenfeld angibt, erhält dann aber generell 0en, die einzeln gezählt wdn. Man muss dann also die Anzahl der LeerZellen separat ermitteln und vom Ergebnis absetzen. Eine derartige Lösung ist dann auch löschungs­stabil (im nfolgd Bsp fett im oberen gelben Feld gezeigt).
Der mathematische Hintergrund der Aufgabe ist die Mengenlehre und zwar die Bildung einer Ver­einigungs­Menge der beiden mög­lichen Differenz­Mengen und zählen ihrer Elemente. Das hast du wohl ansatzweise erkannt, nur eben eine ungeeignete Methode verwendet. Es mag möglicher­weise Xl-Standard­Fktt geben, die so etwas direkt ermitteln können, aber ich habe im folgd Bsp mal jahrealte UDFs bemüht, die das auf Text­Basis tun (die in den unteren gelben Feldern sind unveröffentlicht, im goldenen wurde das mit im Archiv enthaltenen umgesetzt!):
   ABCDEFGHIJKLMNOPQRSTUVWXY
23A12345689101216222332      0121211
24B21345789111317222433      1201211
25C312467810111418232434      1212011
26D41235679101115192223242535   1111110
27
28Elementeanzahl der …Diffzmenge 1Diffzmenge 2Diffzmengen-Vereinigungsmenge
29 066406670121211 0121211
30 606460671201211 1201211
31 660466071212011 1212011
32 777044401111110 1111110
33I29[:L32]:=ANZAHL(VSplit(" "&DSet(VJoin($B23:$T23);VJoin(V$2:V$20));;1))
34M29[:P32]:=ANZAHL(VSplit(" "&DSet(VJoin(V$2:V$20);VJoin($B23:$T23));;1))
35Q29[:T32]:=ANZAHL(VSplit(DSet(VJoin($B23:$T23);VJoin(V$2:V$20))&" "&DSet(VJoin(V$2:V$20);VJoin($B23:$T23));;1))
36V29[:Y32]:=2*ANZAHL(VSplit(VJoin(VSplit(VJoin($B23:$T23)&" "&VJoin(V$2:V$20));;-1);;1))-ANZAHL($B23:$T23)-ANZAHL(V$2:V$20)
37V23[:Y26]:=SUMMENPRODUKT(--(ZÄHLENWENN($B23:$T23;--V$2:V$20)=0))-ZÄHLENWENN($B23:$T23;"")+
38                     SUMMENPRODUKT(--(ZÄHLENWENN(V$2:V$20;--$B23:$T23)=0))-ZÄHLENWENN(V$2:V$20;"")
DSet liefert 0, wenn die DifferenzMenge keine Elemente enthält, was ungünstig für die Weiter­verarbeitung ist. Durch Hinzu­fügen von Leer­Zeichen, das standard­mäßig als Trenner in VSplit fungiert, wird nicht 0, sondern Leer­Text ausgegeben (ursprgl wohl Empty, das je nach Kontext inter­pretiert wird), was auch im Falle von 0 zu richtigem Endergebnis führt.
Feedback nicht unerwünscht! Gruß + schöSo, Luc :-?
Besser informiert mit …

AW: sehe es jetzt anders ...
10.07.2016 19:02:13
...
Hallo @ all,
... und zwar: meine Aussage am Freitag waren falsch :-(
Die Formel von Georg war und ist hier völlig unbeeinflusst von Leerzellen egal welcher Art, wie auch immer die sein mögen. Tut mir leid Georg, wenn ich Deine Formel zu Unrecht als nicht ganz korrekt bezeichnet hatte. Dein Formelvorschlag war und ist für diese hier zuerst gestellte Aufgabe völlig korrekt und mE auch eine optimale Formellösung.
Ich hatte mich offensichtlich von meiner nicht korrekten ZÄHLENWENN()-Alternative leiten lassen, die zufälligerweise nur deshalb stimmte, weil in Joerschis Tabelle P23:I26 offensichtlich schon mal Werte standen. Meine Schlussfolgerungen beruhten lediglich auf Untersuchungen betreffs der Leerzellen Y19:Y20 und waren leider falsch.
@ Luc,
... ich weiß nicht, worauf Du Dich mit Deiner Aussage: " ...worüber wir schon mal diskutiert hat­ten..." genau beziehst. hast Du noch einen Link dazu? Ich kann mich nur erinnern, dass ich in vor nicht all zu langer Zeit mal auf spez. Probleme der ZÄHLENWENN()-Funktion in Zusammenhang mit auszuwertenden gemischten Text- und Zahlenformat vorliegenden Texten und Ziffernfolgen hingewiesen hatte.
Deine Schlussfolgerungen trage ich nicht ganz mit.
Dazu hier nur soviel, Dein korrigierter Formelvorschlag für den Einsatz von ZÄHLENWENN() in V13:Y26 ist doch völlig ausreichend, wenn auch nicht so effizient wie Georgs-Formelvorschlag. Warum aber noch UDFs ins Spiel ringen?
Gruß Werner
.. , - ...

Schön, dass du dich korrigierst, ...
11.07.2016 04:05:53
Luc:-?
…Werner,
aber du scheinst immer noch von deiner irrigen Annahme auszugehen, dass eine LeerZelle so etwas wie einen imaginären Schatten eines einst dort verorteten Eintrags beinhalten könnte. Man kennt zwar das „Gedächtnis“ der Natur und auch von bestimmten Materialien (die Homöopathie lebt vom „Gedächtnis des Wassers“, mit dem die Wirk­stoffe bis 0 verdünnt wdn) und auch gelöschte magnetische Daten­träger enthalten solche „Schatten“ (was allein mit der LöschTechnik zu tun hat), nicht aber eine Xl-Zelle (nur in der Datei-Historie könnte so etwas vermerkt wdn, aber darauf dürfte ZÄHLENWENN wohl kaum zurück­greifen!).
Die Ursache für dieses Verhalten kann nur die Angabeform des Arg2 als Text (wodurch Leer und LeerText nicht unter­scheidbar sind → darum ging's u.a. auch in der Diskussion → du erinnerst dich richtig!) sein, weil dazu normaler­weise auch VglsOperatoren gehören. Auch, wenn man = weglassen kann, wdn Zell- bzw Bereichs­Adressen als Text behandelt und das Argument dann der dafür vorge­sehenen Behandlung unter­zogen. Wdn nur Bezüge angegeben, wdn ihre Inhalte so behandelt und wohl nicht alle auf 1×, sondern einer nach dem anderen, was die Xl-Steuerung bewerk­stelligt, nicht die Fkt (die von dir genannte Nicht-Korrektur nach Löschung muss mit internen Abläufen und dabei entstehenden Unsicher­heiten zusammen­hängen, korrigiert wird erst bei Änderung durch Aufkopieren einer Leerzelle, weder bei [entf], [←] noch Inhalte löschen, obwohl eine Teil­Korrektur - in der Diagonale - eintritt → wahrscheinlich wird die Operation wg Wider­sprüchen abge­brochen). Interes­santer­weise wird diese Steuerung aber nicht wirksam, wenn die Fml als Text ausge­wertet wird, was du gern über­prüfen kannst. Ich habe das u.a. getan und folgd Tests durch­geführt:
AA2:AA20: {=ZÄHLENWENN($B23:$T23;Y$2:Y$20)}
AB2:AB20: {=ZÄHLENWENN(Y$2:Y$20;$B23:$T23)}
AC2:AC20: {=TxEval("CountIf(Y2:Y20,B23:T23)")}
AD2:AD20: {=TxEval("CountIf(B23:T23,Y2:Y20)")}
AE2:AE20: {=Test1v}
AF2:AF20: {=Test2v}
AG2:AG20: {=Test1a}
AH2:AH20: {=Test2a}
AI2:AI20: {=ZÄHLENWENN($B23:$T23;--Y$2:Y$20)}
AJ2:AJ20: {=ZÄHLENWENN(Y$2:Y$20;--$B23:$T23)}
AI23:=SUMMENPRODUKT(--(ZÄHLENWENN($B23:$T23;--Y$2:Y$20)=0))-ZÄHLENWENN($B23:$T23;"")
B40:T40: {=ZÄHLENWENN($B23:$T23;Y$2:Y$20)}
B41:T41: {=ZÄHLENWENN(Y$2:Y$20;$B23:$T23)}
B42:T42: {=TxEval("CountIf(Y2:Y20,B23:T23)")}
B43:T43: {=TxEval("CountIf(B23:T23,Y2:Y20)")}
B44:T44: {=Test1h}
B45:T45: {=Test2h}
B46:T46: {=Test1a}
B47:T47: {=Test2a}
B48:T48: {=ZÄHLENWENN($B23:$T23;--Y$2:Y$20)}
B49:T49: {=ZÄHLENWENN(Y$2:Y$20;--$B23:$T23)}
W49:=SUMMENPRODUKT(--(ZÄHLENWENN(Y$2:Y$20;--$B23:$T23)=0))-ZÄHLENWENN(Y$2:Y$20;"")
Test1v: =ZÄHLENWENN($B23:$T23;Y$2:Y$20)
Test2v: =ZÄHLENWENN(Y$2:Y$20;$B23:$T23)
Test1a: =AUSWERTEN("ZÄHLENWENN($B25:$T25;Y$2:Y$20)")
Test2a: =AUSWERTEN("ZÄHLENWENN(Y$2:Y$20;$B23:$T23)")
Test1h: =ZÄHLENWENN($B23:$T23;Y$2:Y$20)
Test2h: =ZÄHLENWENN(Y$2:Y$20;$B23:$T23)
Mit den ErgebnisVektoren habe ich dann noch diverse Aggregationen versucht.
Leider hast du den 2.Teil meiner Arbeit missverstanden, obwohl ich extra auf die mathematische Grundlage, die Mengenlehre, deren Anfänge nun an die 140 Jahre zurück­reichen, verwiesen hatte. Anhand der unteren gelben Felder demonstrierte ich diesen Zusammenhang. Da in Xl mengenbasierte Fktt zu fehlen scheinen, habe ich auf eine alte UDF (DSet) zurück­gegriffen, die die Differenzmenge der Elemente von 2 ListenTexten bildet. Dabei sind 2 verschiedene D'Mengen möglich, je nachdem, welche Differenz gebildet wird (schlechte Analogie 3-2=1 und 2-3=-1):
ΔM1=M1-M2 → {a,b,c,d}-{b,d,e,f}={a,c} und ΔM2=M2-M1 → {b,d,e,f}-{a,b,c,d}={e,f}
VM=ΔM1 U ΔM2 ⇒ {a,c,e,f}
Weil DSet mit listenartigen Texten arbeitet und nicht mit ZellBereichen, musste ich die Inhalte der Bereiche erst verbinden (VJoin), bevor ich diese Fkt einsetzen konnte. Beide Mengen wurden dann verbunden und alle (Zwischen-)Lösungen gesplittet (mit Umwandlung numerischer Werte in Zahlen), so dass ihre Elemente mit ANZAHL gezählt wdn konnten. Und siehe da, addiert man die ersten beiden Matrizen, ergibt sich die 3. (gelbe). Die goldene Matrix ist dann nur die (alternative) Umsetzung mit bereits publizierten UDFs.
Dieser (untere) Teil dient also (fast) „nur“ dem besseren Verständnis der dem ganzen zugrunde­liegenden Mathematik.
Was meinen oberen FmlVorschlag betrifft, warum soll der nicht so effizient wie Georgs sein? Georgs ist kürzer, das ist alles. Außerdem ging's mir hier nicht darum, die Lösung zu finden, sondern zu bewerk­stelligen, dass ZÄHLENWENN hier zuverlässig fktioniert. Ich dachte, das wäre klar!
Ich habe also 2 Themen behandelt, die ZÄHLENWENN-Problematik und die Demon­stration des mathema­tischen Hinter­grunds der eigentlichen Aufgabe!
Luc :-?
PS: Ich hatte hier noch ein weiteres AnwendungsBsp für WAHL mit Vereinigungs­Matrix-Erzeugung eingestellt (weiter unten ist noch eins).

AW: dazu stelle ich fest ...
11.07.2016 11:41:33
...
Hallo Luc,
... lade doch nochmal die Datei von Joerschi. Steht da was in P23:T23? Scheinbar doch nichts.
Auch gibt LÄNGE(P23) ... LÄNGE(T23) als Ergebnis 0. Jedoch ergibt =ANZAHL2(P23:T23)=5 !?!
Dies wirkt sich bei Anwendung von ZÄHLENWENN() analog aus.
Das war die Ursache für meinen Trugschluss vom Freitag. Wie das offensichtliche Phänomen nun erklärt wird, ist mir zwar nicht egal aber wohl den meisten Excelnutzern. Doch für diese sollten wir nicht nur Erklärungen aufzeigen sondern vor allem Lösungen, wie man derartiges vermeiden / umgehen kann.
Dafür hast Du eine Lösungsformel auf Basis von ZÄHLENWENN() aufgezeigt. Dies hatte ich ja auch anerkannt. Mir ging und geht es nicht um die Kürze einer Lösungsformel.
Georgs Formel war der erste Formellösungsvorschlag, der für die gestellte Aufgabe eine korrekte Lösung vorgeschlagen hat und ist und bleibt somit auch die effektivste im konkreten Fall.
Mir ging und geht es um das aufgezeigte "Phänomen", welches in dieser oder jener abgewandelten Form offensichtlich immer mal wieder auftreten kann. Eine befriedigende Erklärung ob und wie dies vom Grundsatz her zu vermeiden wäre, habe ich jedenfalls noch nicht gefunden.
Und noch ganz kurz zu Deinem Anwendungsbeispiel WAHL(). Ich hatte es gesehen aber aus Zeitgründen nicht weiter darauf reagiert. Ich sah und sehe dies für mich auch noch nicht als eine wirkliche Alternative zu anderen Lösungsmöglichkeiten an. Aber vielleicht gibt es ja die trotzdem.
Gruß Werner
.. , - ...

Ich hatte die Datei unter deinem Link benutzt, ...
11.07.2016 15:48:21
Luc:-?
…Werner,
in der der von dir genannte Effekt ebenfalls zu beobachten war, diese Zellen aber wirklich leer waren.
In Joerschis OriginalDatei sind die Zellen nicht wirklich leer, sondern enthalten vermutlich LeerTexte (0-Strings) oder unsichtbare SteuerZeichen. So etwas kann passieren, wenn Daten aus DBn bzw fremder Software wie SAP geladen wdn. Xl übersetzt die dortigen Null- bzw Leer-Elemente möglicher­weise mit 0-Strings oder SteuerZeichen bzw behält Letztere bei. Mitunter kann man das auch in Xl erreichen, wenn eine TextZelle gelöscht wird. Mir ist es schon passiert, dass bei Neueintrag einer Zahl, diese als Text betrachtet wurde. D.h., irgendetwas in der Formatierung hat das Löschen „überlebt“ (oder Xl erwartet dann hier wieder Text), auch, wenn nicht mal ein TextPräfix zu erkennen ist. Dann ist klar, das ANZAHL2 das mitzählt* (hier hilft bekanntlich ISTLEER). Das ist bei Joerschis Datei in allen LeerZellen aber nicht der Fall. Möglicher­weise gab's diese merkwürdige Fehler­Quelle schon immer, mir fällt sie aber erst jetzt bewusst auf (Xl14/2010). * Allerdings nicht gelöschte TextZellen!
Ein wahrscheinlich ähnlicher Effekt ist der, mit dem man eine rote Null erzeugen kann. Hier bleibt wohl die negative Vorzeichen-Info erhalten, obwohl die Zahl nicht in irgendeiner Dezimale von 0 verschieden ist (obwohl das außerhalb der abgebildeten durchaus möglich wäre → GleitkommaProblematik!). Deshalb vermute ich, dass im hiesigen Fall eine binäre Text-Info erhalten bleibt (bzw erzeugt wird). Insofern könnte man beides durchaus als Zell-(Format-)„Schatten“ bezeichnen, allerdings liegt der dann schatten­unüblich sehr tief in den Xl-„Einge­weiden“, deshalb viell eher so etwas wie ein binäres Echo. Mit Inhalte löschen macht man ihm (und -0) aber den Garaus (hattest du ja wohl auch getan)! ;-)
Zu allen anderen Fragen (auch Michaels), Bezügen und Themen dann später. Ich muss jetzt erst mal beenden.
Luc :-?

AW: das war aber die identische Datei, ...
11.07.2016 17:32:53
...
Hallo Luc,
... denn ich hatte da nur Joerschis Link kopiert. Und da sind die Zellen P23:I23 nicht leer.
Du meinst wahrscheinlich die leeren Zellen in Y19:Y20 (die echt leer sind) dieser Datei, auf die sich meine ursprünglichen Feststellungen bezogen. Die diesbzgl. von mir am Freitag getroffen Feststellungen waren und sind jedoch aber nur eine Folge, der nicht wirklich leeren Zellen in P23:I26.
Das hatte ich gestern aber auch schon angedeutet.
Den Effekt der roten Nullen ist mE eindeutig auf die Gleitkommaproblematik zurückzuführen, während das hier diskutierte evtl. auch einer anderen "Schublade" zugeordnet werden könnte. Obwohl, letztendlich kann es intern auch in die gleiche Schublade gesteckt werden.
Gruß Werner
.. , - ...

Du hast recht, ich hatte nur den Effekt ...
11.07.2016 18:37:09
Luc:-?
…untersucht, Werner,
der so nicht mehr auftrat, wenn ich die Zelle mit einer der anderen vermeintlich leeren überkopiert hatte, anstatt sie zu löschen. Aber eigentlich hätte ich sie löschen müssen, aber das hat dann nicht fktioniert, weil Xl möglicherweise FormatKomponenten der anderen derartigen Zellen übernommen hat, was eigentlich aufs Gleiche hätte hinauslaufen müssen (?).
Es ist jedenfalls ziemlich rätselhaft und Lotus ist diesmal auch nicht im Spiel. Kann eigentlich nur auf Fremd-Software oder DBn zurückgehen, denn es gelingt mir nicht, das zu simulieren. Möglicherweise hat sich hier auch der XML-Hintergrund negativ ausgewirkt, aber das kann ich nicht überprüfen.
Die Überprüfung auf Zeichen (auch Unicode!) hat ebenfalls nichts ergeben, so dass das wohl tatsächlich an irgendwelchen internen Marker-Bits liegen muss, die angelegt wurden. Bei negativen (Gleitkomma-)Zahlen wird mE ja ebenfalls ein Vorzeichen-Bit verwendet.
Weiteres später, muss weg!
Luc :-?

AW: ddies ist mittlerweile geklärt ...
11.07.2016 19:38:54
...
Hallo Luc,
... lies dazu mal den Beitrag von Daniel und den darauf folgenden von Joerschi.
Gruß Werner
.. , - ...

Ja, das kenne ich eigentlich und wir hatten vor …
13.07.2016 03:27:56
Luc:-?
…Monaten dazu auch mal einen Thread, an dem ich sogar beteiligt war, Werner,
worin der Fragesteller eine größere Menge derartiger unbezeichneter LeerTexte möglichst effizient loswdn wollte. Hatte jetzt aber nicht daran gedacht. War wohl zu sehr mit der ZÄHLENWENN-Problematik befasst…
Weiteres s.u. (bei Michael)!
Morrn, Luc :-?

Kleiner Exkurs hierzu
14.07.2016 19:19:11
Luc:-?
Hallo, Werner & all;
1. Rote Null:
Ich habe per TEXT-Fkt 100 Dezimalen von -0 ausgeben lassen, keine war von 0 verschieden und die Zahl blieb auch -0. Prüfung auf <0 ergab so auch WAHR. Nur, wenn man sie als Wert gespeichert hat, wurde bei erneutem DateiÖffnen eine schwarze 0 draus. Unter Xl12/2007 war die rote Null noch instabil, unter Xl14/2010 bleibt sie als FmlErgebnis stabil. Die Formatierung als Exponential­Wert ist auch negativ. Deshalb deutet alles darauf hin, dass das ein Vorzeichen-Bit-, kein Gleitkomma-Problem ist.
2. LeerText (Zeichenkette der Länge 0):
Hier ist die Situation evtl etwas komplizierter, denn "" oder ein einzelnes TextPräfix entspricht anscheinend dem Zeichen mit ASCII- und UniCode =0. Dieses kann mit der XlFkt ZEICHEN nicht dargestellt wdn, wohl aber mit vbFktt Chr und ChrW. Es lässt sich auch in Texte einfügen, deren Längen dadurch nicht verändert wdn, obwohl das Zeichen physisch präsent ist, denn man kann Texte an diesen Stellen trennen. Allerdings kann ein solcher Text in einer MsgBox nur bis zum 1. Chr(0) angezeigt wdn, der Rest wird unterdrückt.
Es ist also wohl das einzige Zeichen mit xlLänge=0 und xlZähler=0, d.h., es kann zwar in VBA gezählt wdn, hat dann auch eine Länge, aber eben nicht von ZellFmln. Es dürfte also als spezielles SteuerZeichen fungieren, bspw wie ein TextPräfix, und evtl auch ein eigenes Bit markieren.
Gruß, Luc :-?

AW: aber nur ein "Exkurs" ...
14.07.2016 19:29:16
...
Hallo Luc,
... mit Möglichkeiten zur Interpretation (Du schreibst ja auch: "es deutet darauf hin") aber ist eben noch kein Beweis. Aber es ist mE auch nicht so wichtig.
Gruß Werner
.. , - ...
Die Verwendung von Vorzeichen-Bits ist aber, ...
15.07.2016 03:12:55
Vorzeichen-Bits
…zumindest bei GleitkommaZahlen, EDV-üblich, Werner!
Morrn, Luc :-?

Unechte Leerzellen können auch dadurch entstehen..
11.07.2016 16:02:32
Daniel
......, dass man eine Formel mit dem Ergebnis Leerstring ("") kopiert und als Wert wieder einfügt.
beim "Kopieren + Als Wert einfügen" bleibt der Charakter des Formelergebnisses erhalten, dh ein Formelergebnis "1234" bleibt ein Text und wird nicht in eine Zahl gewandelt, das Formelergebnis "" bleibt ebenfalls ein Text und die Zelle wird keine echte Leerzelle.
Gruß Daniel

AW: genau so entstanden die Daten
11.07.2016 16:18:49
Joerschi
@ Daniel
ohne fachlich viel zu der Diskussion beitragen zu können: genau so sind die Daten in der Musterdatei entstanden (durch Kopieren aus anderer Datei und Als Wert einfügen). Die Leerzellen waren dabei einst Formeln, denen ein "" auf Grund keines Ergebnisses zugeordnet war.
Liebe Grüße
Joerschi

AW: ist so plausibel, danke Daniel & Joerschi owT
11.07.2016 16:26:07
...
Gruß Werner
.. , - ...

insofern würde es sich anbieten, Joerschi,
11.07.2016 16:41:02
Michael
erst mal einen Makrolauf zu machen und alle beteiligten, als leer erscheinende Zellen mit =empty zu überschreiben.
Rein interessehalber: hast Du das Makro mal ausprobiert?
Gruß,
Michael

AW: sicherlich könnte man das tun ...
11.07.2016 17:47:12
...
Hallo Michael,
...bzw. das was Daniel vorgeschlagen hat.
Doch der Normaluser wird dies (aus verschiedenen Gründen) nicht tun. Dieser wird bestenfalls bemerken, dass irgendetwas mit seinen Auswertungen nicht stimmt und dann aber nicht auf die Idee kommen, wie die Problematik behoben werden kann. Wenn er dann in Foren nachfragt, werden die meisten Anwender an derartige Eventualitäten wohl auch öfters nicht gleich denken. Zumal wohl die meisten Fragesteller auch nicht so eine gute Datengrundlage und entsprechende klare Fragestellungen bereitstellen, wie das Joerschi bisher immer getan hat.
Deshalb erscheint es günstiger, dass wir derartiges bei unseren Lösungsansätzen zumindest versuchen mit bedenken. Mit den vorliegenden korrekten Lösungsformeln ist das aber hier aber gar nicht nicht mehr notwendig und das ist dann doch eine optimale Lösung.
Gruß Werner
.. , - ...

AW: insofern würde es sich anbieten, Joerschi,
11.07.2016 21:36:24
Joerschi
Hallo Michael,
zu meiner Schande: leider noch nicht ;-(
Bislang hatte ich mich sehr auf die Formel konzentriert, weil ich diese Lösung bei meinem Problem bevorzuge (da dort auch mal Zeilen/Spalten entfernt werden und trotzdem noch alles funktionieren soll).
Makros sind auf jeden Fall nützliche Elemente, aber die Datenstruktur muss da imo schon sehr feststehend sein - was bei mir nicht der Fall ist.
Auf jeden Fall vielen Dank für Deine Mühe und Hilfe. Den Makro werde ich mir im Laufe der Woche dennoch noch anschauen, aber bislang hatte ich einen anderen Fokus.
Liebe Grüße
Joerschi

ok, danke für die Nachricht; außerdem
12.07.2016 22:23:22
Michael
Hi Joerschi,
läßt sich das Makro relativ leicht an Bereiche anpassen: ich habe das ja momentan mit Konstanten gelöst, aber im Grunde reicht es, wenn je eine Zelle "bekannt" ist, dann tut es Zellxy.currentregion auch.
Dabei muß nur sichergestellt sein, daß um beide Bereiche herum jeweils freie Zellen sind.
Das nur am Rande.
Gute Zeit & LG zurück,
Michael

AW: genau so entstanden die Daten
11.07.2016 17:00:20
Daniel
Hi
wenn man diese Unechten Leerzellen vermeiden will, dann hat man folgende Möglichkeiten:
a) per Code:
Verwende statt:
Quelle.Copy
Ziel.PasteSpecial xlpastevalues

die Anweisung
Ziel.Value = Quelle.Value

dies ist für Excel wie eine Neueingabe der Werte und die Werte werden in das umgewandelt, wonach sie aussehen, Zahl, Datum/Uhrzeit, Formel oder echte Leerzelle.
b) von Hand:
nach dem Kopieren/Einfügen mit der betroffenen Spalte die Aktion DATEN - DATENTOOLS - TEXT IN SPALTEN ausführen (hierbei die Einstellungen so wählen, dass keine neuen Spalten entstehen)
auch das interpretiert Excel als Neueingabe der Werte und führt die Typumwandlung durch.
c) von Hand:
mit dem Autofilter nach "leer" filtern (der Autofilter unterscheidet nicht zwischen echten Leerzellen und solchen mit dem Text "") und die Inhalte löschen.
Gruß Daniel

@Luc:-?, darf ich mal vorschlagen,
11.07.2016 11:54:16
Michael
daß Du Beispieldatein hochlädst?
Ich habe versucht, Datein mit Deinen UDFs zu finden (via Recherche im Archiv), aber außer Links zu Beiträgen, die sie erwähnen, in vertretbarer Zeit keinen Code gefunden. VJoin & VSplit habe ich noch vom Osterrätsel, aber TxEval nicht.
Der andere Vorteil wäre, daß ich nicht diverse Formeln einzeln kopieren müßte.
Ich hoffe, Du faßt das als konstruktive Kritik auf: die reine <table> - Konstruktion benötigt (ich habe sie in den Editor kopiert und gespeichert) 27117 Bytes (das dürfte in etwa die Größenordnung aller bislang von mir analysierten Xl2Htm- Varianten sein): da ist die Datei auch nicht größer: damit könnte ich dann gleich loslegen und außerdem sicher sein, daß ich immer die UDF-Version(en) habe, die Du auch gerade verwendest.
Bis hierher zur aktuellen Diskussion.
Prinzipiell würde ICH mir eine deutlich verschlankte X2H-Ausgabe wünschen, aber das ist natürlich Geschmackssache.
Nun gut. Wenn Du eine Datei hochlädst, sehe ich mir die Sachen im Detail an & melde mich wieder.
Schöne Grüße,
Michael

Sehr merkwürdig, ...
13.07.2016 04:22:19
Luc:-?
…Michael,
bei mir steht der fragliche Thread in der RECHERCHE gleich als erster, allerdings die, hier ausreichende, Version 1.0 (habe inzwischen 1.2)! Es hätte hier aber auch ein Einzeiler wie der folgende gereicht:
Function FTEval(FmlText As String): FTEval = Evaluate(FmlText): End Function
Das Hochladen von BspDateien erfordert immer einen gewissen MehrAufwand, da in Xl ständig meine AddIns mitlaufen. Die müsste ich erst deaktivieren und alles, was an UDFs, Enumerationen und ggf GlobalVariablen relevant ist, in die Datei kopieren und alle sonstigen fmlbasierten Daten als Werte speichern. Das mache ich deshalb nur, wenn's unbedingt erforderlich ist oder mal keinen allzugroßen Aufwand verursacht (ich muss das alles ja idR auch wieder rückgängig machen!). Normalerweise achte ich darauf, bei Publikationen die letztveröffentlichte UDF-Version zu verwenden, d.h., keine neuen Argumente jüngerer Versionen.
Was Range2HTM(L) betrifft, die Ausgabe ist recht umfangreich, aber geht schnell. Der Browser formt dann eh alles um. Das wollte ich in der teilverlorenen neuen Version nachempfinden und hatte deshalb Korrekturläufe eingebaut. Das, die RahmenProblematik u.a. hatte dann zu einem Mehrfachen an GenerierungsZeit geführt…!
So, jetzt aber ein ggf großes TrostPflaster, denn ich habe die MengenProblematik ausgeweitet und wie angekündigt eine entsprd UDF geschrieben, deren Anwendung am ggb Problem im folgd Bild-Bsp demonstriert wird:
Userbild
In den blauen und violetten ErsatzFmln habe ich wg der unmarkierten LeerTexte NoErrRange verwendet (dürfte relativ leicht per RECHERCHE auffindbar sein). Allerdings ist Georgs von mir hier modifizierte Fml und meine (violette) Weiterentwicklung derselben auf diese angewiesen (seine OriginalFml evtl nicht?).
So, und nun die neue UDF DataSet:
Public Enum cxSetType: cxSymDiff = -2: cxDiff: cxUnite: cxInterSect: cxCollect: End Enum
Public Enum xlTriState: xlTrue = -1: xlFalse: xlCTrue: End Enum

Rem Bildet 1 DatenMenge aus 2en n.5 praxisrelevanten OperatTypen
'   Arg1/2: 1./2.Menge (ZBereichsBezugs- oder Datenfeld-Vektor);
'   Arg3: 
Function DataSet(Menge1, Menge2, Optional ByVal ErgMengTyp As cxSetType, Optional _
ByVal nurUnikate As xlTriState, Optional ByVal kPaare As Boolean)
Dim ik As Integer, ix As Long, iz As Long, erg, M1, M2, tM1, tM2, xEl, _
wf As WorksheetFunction
On Error Resume Next: Set wf = WorksheetFunction
nurUnikate = nurUnikate Mod 2: ErgMengTyp = ErgMengTyp Mod 3
If nurUnikate = xlTrue And ErgMengTyp = cxCollect Then ErgMengTyp = cxUnite
kPaare = CBool(ErgMengTyp Mod 2) Or kPaare
If IsArray(Menge1) Then
If TypeName(Menge1) = "Range" Then _
M1 = wf.Transpose(wf.Transpose(Menge1)) Else M1 = Menge1
If IsError(LBound(M1, 2)) Then Else M1 = wf.Transpose(M1)
If IsError(LBound(M1, 2)) Then Else DataSet = CVErr(xlErrRef): GoTo ex
If CBool(nurUnikate) Then
ix = LBound(M1): ReDim tM1(UBound(M1) - ix): ik = Abs(ix = 0)
For Each xEl In M1
If wf.Match(xEl, M1, 0) = ix + ik Then tM1(iz) = xEl: iz = iz + 1
ix = ix + 1
Next xEl
ReDim Preserve tM1(iz - 1): M1 = tM1
End If
Else: M1 = Array(Menge1)
End If
If IsArray(Menge2) Then
If TypeName(Menge2) = "Range" Then _
M2 = wf.Transpose(wf.Transpose(Menge2)) Else M2 = Menge2
If IsError(LBound(M2, 2)) Then Else M2 = wf.Transpose(M2)
If IsError(LBound(M2, 2)) Then Else DataSet = CVErr(xlErrRef): GoTo ex
If CBool(nurUnikate) Then
ix = LBound(M2): ReDim tM1(UBound(M2) - ix): ik = Abs(ix = 0)
For Each xEl In M2
If wf.Match(xEl, M2, 0) = ix + ik Then tM2(iz) = xEl: iz = iz + 1
ix = ix + 1
Next xEl
ReDim Preserve tM2(iz - 1): M2 = tM2: ix = 0: iz = 0
End If
Else: M2 = Array(Menge2)
End If
ReDim Preserve M1(UBound(M1) - LBound(M1)), M2(UBound(M2) - LBound(M2))
If kPaare Then
ReDim erg(UBound(M1) + UBound(M2) - 1)
Else: ReDim erg(wf.Max(UBound(M1), UBound(M2)))
End If
For Each xEl In M1
Select Case ErgMengTyp
Case cxSymDiff, cxDiff
If IsError(wf.Match(xEl, M2, 0)) Then erg(ix) = xEl: ix = ix + 1
Case cxUnite, cxCollect
erg(ix) = xEl: ix = ix + 1
Case cxInterSect
If IsError(wf.Match(xEl, M2, 0)) Then Else erg(ix) = xEl: ix = ix + 1
End Select
Next xEl
If ErgMengTyp Mod 2 = 0 Then
If kPaare Then iz = ix
For Each xEl In M2
Select Case ErgMengTyp
Case cxSymDiff, cxUnite
If IsError(wf.Match(xEl, M1, 0)) Then
If kPaare Then erg(iz) = xEl Else erg(iz) = Array(erg(iz), xEl)
iz = iz + 1
End If
Case cxCollect
If kPaare Then erg(iz) = xEl Else erg(iz) = Array(erg(iz), xEl)
iz = iz + 1
End Select
Next xEl
ReDim Preserve erg(wf.Max(ix, iz) - 1)
If ix > iz Then
For iz = iz To ix
erg(iz) = Array(erg(iz), Empty)
Next iz
End If
Else: ReDim Preserve erg(ix - 1)
End If
If IsArray(erg(0)) Then
If IsEmpty(erg(0)(0)) Then DataSet = Chr(12) Else DataSet = erg
Else: If IsEmpty(erg(0)) Then DataSet = Chr(12) Else DataSet = erg
End If
ex: Set wf = Nothing
End Function
Falls du alle Variationen gründlich testen willst, wäre es mir recht, wenn du mir mitteiltest, ob es (und wenn welche) Probleme es gegeben hat.
Ansonsten viel Spaß beim Nachbau! Gruß, Luc :-?

Danke, Luc :-?
13.07.2016 11:16:47
Michael
für Deine Ausführungen.
Der ganze Thread schreit nach zu tiefergehenden Experimenten, die ich für heute erst Mal hinten anstelle.
Du hörst von mir...
Schöne Grüße,
Michael

Na, dann bis dann...! ;-) owT
13.07.2016 13:54:22
Luc:-?
:-?

Habe 1 Fehler festgestellt, ...
14.07.2016 04:18:51
Luc:-?
…Michael (& Interessenten);
in PgmZeile 20: Nach M1 = tM1 noch : iz = 0 ergänzen, sonst fktioniert Arg4 nicht richtig!
Da es in meinem Exemplar seltsamerweise sogar 2 Fehler waren, habe ich bereits auf Vs1.1 erweitert, die ein zusätzliches, nützliches 6.Argument hat. Folgt hier in Bälde.
Morrn, Luc :-?

AW: Habe 1 Fehler festgestellt, ...
14.07.2016 15:07:52
Michael
Hi Luc :-?,
danke für den Hinweis.
Ich habe mir den Thread auf alle Fälle mal runtergeladen, aber es klingt nach locker 4, 5 Stunden Beschäftigung, die ich im Moment nicht ohne schlechtes Gewissen investieren kann.
Er IST aber auch schon unübersichtlich geworden (hehe, Du hast Dich ja auch schon mokiert)...
Auf alle Fälle behalte ich's im Hinterkopf und melde ich zu gegebener Zeit.
Schöne Grüße,
Michael

So, Version1.1 ist fertig, ...
14.07.2016 18:30:44
Luc:-?
…Michael,
wobei sie ohne Arg6 genauso fktionieren sollte wie die korrigierte Vs1.0:
Public Enum cxTriState: cxAsUsed = -2: cxTrue: cxFalse: cxCTrue: End Enum

Rem Bildet 1 DatenMenge aus 2en n.5 praxisrelevanten OperatTypen
'   Arg1/2: 1./2.Menge (ZBereichsBezugs- oder Datenfeld-VEKTOR);
'   Arg3: 0), fehlt/0 keine Elemtt entfern;
'   Arg5: wahr/±1 horiz ErgebnVektor, fehlt/falsch/0 vert Vektor
'   aus ElemttPaaren bd Mengen (1 Elem ggf leer, nur Arg3=0/±2);
'   Arg6: fehlt/leer LeerMg|LeerWert=Zch12|leer (idR 0 gezeigt),
'   0 LM|LW=Ø|#NV, 1 LM|LW=LeerText m.Lg|o.Lg, -1 LM|LW=LToL|-0.
'   Achtg! Benötigt Enumeratt cxSetType, cxTriState, xlTriState!
'   Hinw ElemttANZAHL: -0 nicht vwenden, ANZ~2 zählt stets alle!
'   Vs1.1 -LSr:CyWorXxl -cd:20160711 -1pub:h20160713/14 -lupd:20160714t
Function DataSet(Menge1, Menge2, Optional ByVal ErgMengTyp As cxSetType, Optional _
ByVal nurUnikate As xlTriState, Optional ByVal kPaare As Boolean, _
Optional ByVal LeerErsatz As cxTriState = cxAsUsed)
Const ucLeerMalt1 As Long = 0, ucLeerMalt2 As Long = 65279, _
ucLeerMdef As Long = 12, ucLeerMsym As Long = 8709
Dim ik As Integer, ix As Long, iz As Long, erg, ersZ(1) As Variant, _
M1, M2, tEVgl, tM, xEl As Variant, wf As WorksheetFunction
On Error Resume Next: Set wf = WorksheetFunction
If LeerErsatz  cxAsUsed Then LeerErsatz = LeerErsatz Mod 2
ersZ(0) = ChrW(Array(ucLeerMdef, ucLeerMalt1, ucLeerMsym, ucLeerMalt2)(LeerErsatz + 2))
ersZ(1) = Array(Empty, -CDbl(0), CVErr(xlErrNA), "")(LeerErsatz + 2)
nurUnikate = nurUnikate Mod 2: ErgMengTyp = ErgMengTyp Mod 3
If nurUnikate = xlTrue And ErgMengTyp = cxCollect Then ErgMengTyp = cxUnite
kPaare = CBool(ErgMengTyp Mod 2) Or kPaare
If IsArray(Menge1) Then
If TypeName(Menge1) = "Range" Then _
M1 = wf.Transpose(wf.Transpose(Menge1)) Else M1 = Menge1
If IsError(LBound(M1, 2)) Then Else M1 = wf.Transpose(M1)
If IsError(LBound(M1, 2)) Then Else DataSet = CVErr(xlErrRef): GoTo ex
If CBool(nurUnikate) Then
ix = LBound(M1): ReDim tM(UBound(M1) - ix): ik = Abs(ix = 0)
For Each xEl In M1
If wf.Match(xEl, M1, 0) = ix + ik Then tM(iz) = xEl: iz = iz + 1
ix = ix + 1
Next xEl
ReDim Preserve tM(iz - 1): M1 = tM: iz = 0
End If
Else: M1 = Array(Menge1)
End If
If IsArray(Menge2) Then
If TypeName(Menge2) = "Range" Then _
M2 = wf.Transpose(wf.Transpose(Menge2)) Else M2 = Menge2
If IsError(LBound(M2, 2)) Then Else M2 = wf.Transpose(M2)
If IsError(LBound(M2, 2)) Then Else DataSet = CVErr(xlErrRef): GoTo ex
If CBool(nurUnikate) Then
ix = LBound(M2): ReDim tM(UBound(M2) - ix): ik = Abs(ix = 0)
For Each xEl In M2
If wf.Match(xEl, M2, 0) = ix + ik Then tM(iz) = xEl: iz = iz + 1
ix = ix + 1
Next xEl
ReDim Preserve tM(iz - 1): M2 = tM: ix = 0: iz = 0
End If
Else: M2 = Array(Menge2)
End If
ReDim Preserve M1(UBound(M1) - LBound(M1)), M2(UBound(M2) - LBound(M2))
If kPaare Then
ReDim erg(UBound(M1) + UBound(M2) - 1)
Else: ReDim erg(wf.Max(UBound(M1), UBound(M2)))
End If
For Each xEl In M1
Select Case ErgMengTyp
Case cxSymDiff, cxDiff
If IsError(wf.Match(xEl, M2, 0)) Then erg(ix) = xEl: ix = ix + 1
Case cxUnite, cxCollect
erg(ix) = xEl: ix = ix + 1
Case cxInterSect
If IsError(wf.Match(xEl, M2, 0)) Then Else erg(ix) = xEl: ix = ix + 1
End Select
Next xEl
If ErgMengTyp Mod 2 = 0 Then
If kPaare Then iz = ix
For Each xEl In M2
Select Case ErgMengTyp
Case cxSymDiff, cxUnite
If IsError(wf.Match(xEl, M1, 0)) Then
If kPaare Then erg(iz) = xEl Else erg(iz) = Array(erg(iz), xEl)
iz = iz + 1
End If
Case cxCollect
If kPaare Then erg(iz) = xEl Else erg(iz) = Array(erg(iz), xEl)
iz = iz + 1
End Select
Next xEl
If wf.Max(ix, iz) > 0 Then
ReDim Preserve erg(wf.Max(ix, iz) - 1)
Else: ReDim Preserve erg(0)
End If
If Not kPaare Then
If ix > iz Then
For iz = iz To ix - 1
erg(iz) = Array(erg(iz), ersZ(1))
Next iz
ElseIf LeerErsatz > cxAsUsed Then
If ix  cxAsUsed Then
If iz = 0 Then erg(0) = ersZ(1)
End If
ElseIf ix > 0 Then
ReDim Preserve erg(ix - 1)
Else: erg = ersZ(1)
End If
If IsArray(erg) Then
If IsArray(erg(0)) Then
If erg(0)(0) = erg(0)(1) Then
tEVgl = erg(0)(0): GoTo vx
Else: DataSet = erg
End If
Else: tEVgl = erg(0): GoTo vx
End If
Else: tEVgl = erg
vx:     Select Case LeerErsatz
Case cxAsUsed
If IsEmpty(tEVgl) Then DataSet = ersZ(0) Else DataSet = erg
Case cxTrue
If tEVgl = ersZ(1) Then DataSet = ersZ(0) Else DataSet = erg
Case cxFalse
If wf.IsNA(tEVgl) Then DataSet = ersZ(0) Else DataSet = erg
Case cxCTrue
If tEVgl = ersZ(1) Then DataSet = ersZ(0) Else DataSet = erg
End Select
End If
ex: erg = Empty: M1 = Empty: M2 = Empty: tM = Empty: Set wf = Nothing
End Function
Die beiden hier fehlenden Enumerationen bitte aus dem Posting der Vs1.0 entnehmen. Evtl bereits vorhandene gleichnamige Enumerationen, die in anderen UDFs verwendet wdn, aktualisieren und die anderen UDFs ggf entsprd ändern, denn das ist der jetzt gültige aktuelle Stand!
Luc :-?
Wegen LeerMengen nun noch Version1.2, ...
15.07.2016 02:59:38
Luc:-?
…Michael,
denn Vs1.1 hat hier Schwächen gezeigt, die nun hoffent­lich bereinigt sind, so dass das die auf abseh­bare Zeit (ggf auch nur vorläufig ;-]) letzte Version* sein dürfte. Hier muss auch nicht mehr extern MTRANS ver­wendet wdn, wenn ein erzeug­ter horizon­taler Vektor als verti­kaler aus­gegeben wdn soll (Auto­Erkennung eingebaut).
* Interessant wäre ggf noch eine eingebaute Ausgabe des Ganzen als Text in Matrix­Konstt­Form, aber das kann auch die aktuelle Version1.5 von VJoin (VSplit aktVs1.4, beide bisher unver­öffent­licht).
Die 3 verwendeten Enumerationen sind wieder den Postings der vorherigen Versionen zu entnehmen!
Rem Bildet 1 DatenMenge aus 2en n.5 praxisrelevanten OperatTypen
'   Arg1/2: 1./2.Menge (ZBereichsBezugs- oder Datenfeld-VEKTOR),
'   beide dürf LeerMengg sein, leere Elementt wdn b.Arg30), fehlt/0 keine Elemtt entfern;
'   Arg5: wahr/±1 horiz ErgebnVektor (wird ggf automat transpon,
'   wenn mehr Zeilen ausgewählt wdn), fehlt/falsch/0 vert Vektor
'   aus ElemttPaaren bd Mengen (1 Elem ggf leer, nur Arg3=0/±2);
'   Arg6: fehlt/leer LeerMg|LeerWert=Zch12|leer (idR 0 gezeigt),
'   0 LM|LW=Ø|#NV, 1 LM|LW=LeerText m.Lg|o.Lg, -1 LM|LW=LToL|-0.
'   Achtung! Vwendet intern WshFct Transpose (MTRANS, ElemttAnz-
'   Limitierung!) u.Enumeratt cxSetType, cxTriState, xlTriState!
'   Hinw ANZAHL-Ermittl: -0 nicht vwend, ANZ~2 zählt stets alle!
'   Vs1.2 -LSr:CyWorXxl -cd:20160711 -1pub:h20160713-15 -lupd:20160714n
Function DataSet(Menge1, Menge2, Optional ByVal ErgMengTyp As cxSetType, Optional _
ByVal nurUnikate As xlTriState, Optional ByVal kPaare As Boolean, _
Optional ByVal LeerErsatz As cxTriState = cxAsUsed)
Const ucLeerMalt1 As Long = 0, ucLeerMalt2 As Long = 65279, _
ucLeerMdef As Long = 12, ucLeerMsym As Long = 8709
Dim ik As Integer, ix As Long, iz As Long, erg, ersZ(1) As Variant, _
M1, M2, tEVgl, tM, xEl As Variant, wf As WorksheetFunction
On Error Resume Next: Set wf = WorksheetFunction
If LeerErsatz  cxAsUsed Then LeerErsatz = LeerErsatz Mod 2
ersZ(0) = ChrW(Array(ucLeerMdef, ucLeerMalt1, ucLeerMsym, ucLeerMalt2)(LeerErsatz + 2))
ersZ(1) = Array(Empty, -CDbl(0), CVErr(xlErrNA), "")(LeerErsatz + 2)
nurUnikate = nurUnikate Mod 2: ErgMengTyp = ErgMengTyp Mod 3
If nurUnikate = xlTrue And ErgMengTyp = cxCollect Then ErgMengTyp = cxUnite
kPaare = CBool(ErgMengTyp Mod 2) Or kPaare
If IsArray(Menge1) Then
If TypeName(Menge1) = "Range" Then _
M1 = wf.Transpose(wf.Transpose(Menge1)) Else M1 = Menge1
If IsError(LBound(M1, 2)) Then Else M1 = wf.Transpose(M1)
If IsError(LBound(M1, 2)) Then Else DataSet = CVErr(xlErrRef): GoTo xx
If CBool(nurUnikate) Then
ix = LBound(M1): ReDim tM(UBound(M1) - ix): ik = Abs(ix = 0)
For Each xEl In M1
If wf.Match(xEl, M1, 0) = ix + ik Then tM(iz) = xEl: iz = iz + 1
ix = ix + 1
Next xEl
ReDim Preserve tM(iz - (2 * iz) \ (iz + 1)): M1 = tM: iz = 0
Else: ReDim Preserve M1(UBound(M1) - LBound(M1))
End If
Else: M1 = Array(Menge1)
End If
If IsArray(Menge2) Then
If TypeName(Menge2) = "Range" Then _
M2 = wf.Transpose(wf.Transpose(Menge2)) Else M2 = Menge2
If IsError(LBound(M2, 2)) Then Else M2 = wf.Transpose(M2)
If IsError(LBound(M2, 2)) Then Else DataSet = CVErr(xlErrRef): GoTo xx
If CBool(nurUnikate) Then
ix = LBound(M2): ReDim tM(UBound(M2) - ix): ik = Abs(ix = 0)
For Each xEl In M2
If wf.Match(xEl, M2, 0) = ix + ik Then tM(iz) = xEl: iz = iz + 1
ix = ix + 1
Next xEl
ReDim Preserve tM(iz - (2 * iz) \ (iz + 1)): M2 = tM: ix = 0: iz = 0
Else: ReDim Preserve M2(UBound(M2) - LBound(M2))
End If
Else: M2 = Array(Menge2)
End If
ReDim erg(IIf(kPaare, UBound(M1) + UBound(M2) + 1, wf.Max(UBound(M1), UBound(M2))))
For Each xEl In M1
If IsEmpty(xEl) Then
If ErgMengTyp = cxCollect Then xEl = ersZ(1) Else GoTo nx
End If
Select Case ErgMengTyp
Case cxSymDiff, cxDiff
If IsError(wf.Match(xEl, M2, 0)) Then erg(ix) = xEl: ix = ix + 1
Case cxUnite, cxCollect
erg(ix) = xEl: ix = ix + 1
Case cxInterSect
If IsError(wf.Match(xEl, M2, 0)) Then Else erg(ix) = xEl: ix = ix + 1
End Select
nx: Next xEl
If ErgMengTyp Mod 2 = 0 Then
If kPaare Then iz = ix
For Each xEl In M2
If IsEmpty(xEl) Then
If ErgMengTyp = cxCollect Then xEl = ersZ(1) Else GoTo nz
End If
Select Case ErgMengTyp
Case cxSymDiff, cxUnite
If IsError(wf.Match(xEl, M1, 0)) Then
If kPaare Then erg(iz) = xEl Else erg(iz) = Array(erg(iz), xEl)
iz = iz + 1
End If
Case cxCollect
If kPaare Then erg(iz) = xEl Else erg(iz) = Array(erg(iz), xEl)
iz = iz + 1
End Select
nz:     Next xEl
If wf.Max(ix, iz) > 0 Then
ReDim Preserve erg(wf.Max(ix, iz) - 1)
Else: ReDim Preserve erg(0)
End If
If Not kPaare Then
If ix > iz Then
For iz = iz To ix - 1
erg(iz) = Array(erg(iz), ersZ(1))
Next iz
ElseIf LeerErsatz > cxAsUsed Then
If ix  cxAsUsed Then
If iz = 0 Then erg(0) = ersZ(1)
End If
ElseIf ix > 0 Then
ReDim Preserve erg(ix - 1)
Else: erg = ersZ(1)
End If
If IsArray(erg) Then
If IsArray(erg(0)) Then
If IsError(erg(0)(0) = erg(0)(1)) Then
DataSet = erg
ElseIf erg(0)(0) = erg(0)(1) Then
tEVgl = erg(0)(0): GoTo vx
Else: DataSet = erg
End If
Else: tEVgl = erg(0): GoTo vx
End If
Else: tEVgl = erg
vx:     Select Case LeerErsatz
Case cxAsUsed
If IsEmpty(tEVgl) Then DataSet = ersZ(0) Else DataSet = erg
Case cxTrue
If tEVgl = ersZ(1) Then DataSet = ersZ(0) Else DataSet = erg
Case cxFalse
If wf.IsNA(tEVgl) Then DataSet = ersZ(0) Else DataSet = erg
Case cxCTrue
If tEVgl = ersZ(1) Then DataSet = ersZ(0) Else DataSet = erg
End Select
End If
ex: If IsError(DataSet) Then GoTo xx
With Application
If kPaare And .ThisCell.HasArray Then
If .Caller.Rows.Count > 1 Then DataSet = wf.Transpose(DataSet)
End If
End With
xx: erg = Empty: M1 = Empty: M2 = Empty: tM = Empty: Set wf = Nothing
End Function
Morrn, Luc :-?

AW: ich hab einen anderen thread in Erinnerung ...
14.07.2016 19:32:16
...
Hallo Luc,
.. aber mir fehlt es an Zeit danach zu suchen. Ist ja auch nicht so wichtig.
Gruß Werner
.. , - ...

AW: nun dazu neuer Formelvorschlag ...
10.07.2016 19:09:10
...
Hallo Joerschi,
... nachdem ich mich eben (siehe meinen Beitrag oben trüber) mit der Auswertungsproblematik zu Deinem ersten Problem auseinander gesetzt habe korrigiere ich dementsprechend meinen Formelvorschlag für Deine Ergänzungsanfrage wie folgt:
In V23 (und nach rechts und unten kopieren):
=SUMMENPRODUKT(ISTNV(VERGLEICH(""&$B23:$T23;""&V$2:V$20;))+ISTZAHL(VERGLEICH(""&$B23:$T23;""&V$2:V$20;))*($B23:$T23""))+SUMMENPRODUKT(ISTNV(VERGLEICH(""&V$2:V$20;""&$B23:$T23;0))*1)
Gruß Werner
.. , - ...

Vielen Dank Werner! ...
10.07.2016 23:15:46
Joerschi
... und natürlich auch alle anderen, welche hier - auch gegenseitig - immer wieder neue (Un)tiefen vermeintlicher Excel-Bugs/Geheimnisse/Fallstricke verargumentieren.
Viele Grüße
Joerschi

Dieser neue Formelvorschlag liefert ein ...
11.07.2016 01:28:41
Luc:-?
…Ergebnis (ich kann's kaum glauben!), das keinerlei Ähnlichkeit mit Joerschis Vorgabe aufweist, Werner:
V23:Y26: ={14.20.20.21;20.14.20.21;20.20.14.21;21.21.21.17}
Die Diagonale zeigt keine 0-Werte, die anderen Angaben sind zu groß und die Fml ist deutlich länger als Georgs. Irgendwas wirst du vergessen haben (Hitze?)! Die einzigen Fmln, die richtige Ergebnisse liefern, sind meine und, nach Anpassung, Georgs (die kürzeste klassische!).
Alles Weitere oben, wo es hingehört! :-|
Gruß, Luc :-?

AW: schau Dir die Zusatzaufgabe nochmal an ....
11.07.2016 11:41:19
...
Hallo Luc,
... dann solltest Du feststellen, dass dies sehr wohl von mir auf diese zugeschnitten ist. Ganz davon abgesehen davon, das Joerschi dass offensichtlich auch so gesehen hatte. Und somit gehört mein Formelvorschlag hier her und nicht in den obigen Beitrag.
Gruß Werner
.. , - ...

Was für eine Zusatzaufgabe? Ich hasse so ein ...
11.07.2016 14:42:28
Luc:-?
…Durcheinander! :-(
Luc :-?

AW: das ist doch eindeutig erkennbar ...
11.07.2016 16:14:38
...
Hallo Luc,
... wenn Du die Formelliste öffnest, ist mein Beitrag eindeutig Joerschis Ergänzungsfrage, wenn... - von Joerschi am 08.07.2016 16:21:49 zugeordnet. Und auch Joerschi kannst Du keinen Vorwurf machen, denn er hat seine Frage einerseits sauber abgegrenzt und andererseits richtigerweise keinen neuen thread aufgemacht, weil seine Ergänzungsfrage im engen Zusammenhang zu seiner thread-Eröffnungsfrage steht.
Gruß Werner
.. , - ...

Erinnere mich, das sogar gelesen zu haben, ...
12.07.2016 04:37:28
Luc:-?
…Werner,
habe es wg der anderen Probleme aber nicht weiter beachtet und dann völlig vergessen. Das wäre dann keine Vereinigungsmenge der Differenzmengen, auch als symmetrische Differenzmenge bekannt, mehr, sondern diese + die Schnittmenge der beiden Mengen, also letztlich die Vereinigungsmenge. Das wäre mit VSplit+VJoin ganz einfach, zumal meine UDFs gg diese unechten LeerZellen immun sind. Außerdem bereite ich eine UDF vor, die direkt mit den ZellBereichen oder Datenfeldern operieren kann. Die ist auch immun.
Morrn, Luc :-?

324 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige