Microsoft Excel

Herbers Excel/VBA-Archiv

Summenprodukt mit VERGLEICH

Betrifft: Summenprodukt mit VERGLEICH von: sungam1990
Geschrieben am: 28.10.2014 22:01:56

Hallo Boris,
hallo zusammen,

ok, ich mache das als neuen Thread auf. Ich habe nachstehende Formel im Einsatz. Ziel ist es, Neukunden pro Monat zu zählen. Taucht also eine Adresse erstmalig mit einem Bestelldatum aus, soll die Adresse in diesem Monat gezählt werden. Bei der Summenproduktformel mit VERGLEICH hatten mir Boris und neopa in einem vorherigen Thread bereits geholfen.

=SUMMENPRODUKT((($A$3="Alle")+(DATEN_AE!$D$2:$D$662=$A$3))*(TEXT(DATEN_AE!$C$2:$C$662;"MMMJJ")=A9) *(VERGLEICH(DATEN_AE!$A$2:$A$662;DATEN_AE!$A$2:$A$662;)=ZEILE(DATEN_AE!$1:$661)))

Nach einer Stichprobe war mir aufgefallen, dass die ermittelten Werte nicht in jedem Monat stimmen und habe ewig nach dem Fehler gesucht. Nachdem ich in der Datenbasis aber nach dem Bestelldatum aufsteigend sortiert habe, passen die Werte auf einmal - sie haben sich auf jeden Fall verändert.

Meine Frage ist, ist das richtig, dass die Werte chronologisch sortiert werden müssen, damit der VERGLEICH funktioniert oder habe ich die Formel falsch umgesetzt?

Ich habe das Bsp noch einmal angehängt:

https://www.herber.de/bbs/user/93429.xls

Ich danke für euren Hinweis.

Grüsse, Magnus

  

Betrifft: AW: Summenprodukt mit VERGLEICH von: Daniel
Geschrieben am: 28.10.2014 22:27:20

Hi

der Vergleich hat einen 3. Paramter.
über diesen teilst du Excel mit, wie die Daten vorliegen:

hat der 3. Parameter den Wert 0, dann gilt folgendes:
- die Liste kann unsortiert sein
- ist der Suchwert nicht vorhanden, ist das Ergebnis der #NV-Fehler

hat der 3. Parameter den Wert 1, dann gilt:
- die Liste muss aufsteigend sortiert sein.
- ist der Suchwert nicht vorhanden, wird die Zeilennummer es nächstkleineren Wertes als Ergebnis verwendet

hat der 3. Paramter den Wert -1, dann gilt:
- die Liste muss absteigend sortiert sein
- ist der Suchwert nicht vorhanden, wird die Zeilennummer des nächstgrösseren Wertes als Ergebnis verwendet.

der 3. Parameter ist also ziemlich wichtig und deswegen sollte man ihn immer angeben.
solltest du die Werte -1 oder 1 bei unsortierten Daten verwenden, so ist das Ergebnis kein Fehlerwert, sonden ein zufälliger Wert aus der Liste.

Gruß Daniel


  

Betrifft: AW: Summenprodukt mit VERGLEICH von: sungam1990
Geschrieben am: 28.10.2014 22:47:59

Hi Daniel,

Danke für Deine Antwort. Heißt das, Du würdest der Formel wie nachfolgend die 0 mitgeben und die Sortierung der Daten ist zu vernachlässigen?

=SUMMENPRODUKT((($A$3="Alle")+(DATEN_AE!$D$2:$D$662=$A$3))*(TEXT(DATEN_AE!$C$2:$C$662;"MMMJJ")=C3) *(VERGLEICH(DATEN_AE!$A$2:$A$662;DATEN_AE!$A$2:$A$662;0)=ZEILE(DATEN_AE!$1:$661)))

Auf den ersten Blick sieht das Ergebnis gut aus. In den Monaten, in denen es keine Neukunden gibt, liefert sie sauber eine 0 als Ergebnis. Oder wann könnte mich jetzt der NV Fehler einholen?

Bin ich damit auf der sicheren Seite oder täusche ich mich?

Sorry, blicke halt langsam nicht mehr richtig durch. Danke für Deine Hilfe!!
Grüsse, Maagnus


  

Betrifft: AW: Summenprodukt mit VERGLEICH von: {Boris}
Geschrieben am: 28.10.2014 23:24:39

Hi Magnis,

Daniel hat Dir nur eine allgemeine Erklärung zur VERGLEICH-Funktion geliefert.

Ich hatte VERGLEICH(Suchbegriff;Matrix;) formuliert.

Das ist das Selbe wie VERGLEICH(Suchbegriff;Matrix;0)

Bedeutet: es wird auf EXAKTE Übereinstimmung geprüft.
Die Angabe des 3. Paramter mit ;) ist identisch mit ;0)
Wichtig halt nur, dass man der Funktion mitteilt, dass der 3. Parameter vorhanden ist. Und ;) wird in JEDER Funktion als NULL oder FALSCH übersetzt.

Sag doch mal, mit welcher Sortierung Du ein anderes Ergebnis als das in der hochgeladenen Datei angegebene erhälst. Dann kann man es evtl. leichter nachvollziehen.

VG, Boris


  

Betrifft: AW: Summenprodukt mit VERGLEICH von: sungam1990
Geschrieben am: 29.10.2014 00:04:35

Hi Boris,

zu Beginn waren tatsächlich nur in 2 oder 3 Monaten die Werte falsch. Fast alle Einträge in der Datenquelle waren allerdings chronologisch. Nach der aufsteigenden Sortierung passte es dann. Das dem so war, bin ich mir ganz sicher, da ich ziemlich lange darüber gegrübelt habe, was ich falsch gemacht habe. Im Juni13 hatte ich 5 statt 7 als Ergebnis.

Das ursprüngliche (falsche) Ergebnis bekommme ich nicht wieder hergestellt in der Datei. Wenn ich aber die Sortierung komplett umdrehe, also Datum absteigend, stimmen keine Werte mehr.

Die Datei hängt noch einmal daebei. Die vorherigen Werte habe ich einfach daunter kopiert, so dass man den Unterschied erkennt.

https://www.herber.de/bbs/user/93431.xls

Natürlich kann ich mit dem vorherigen Sortieren leben. Wenn die Formel allerdings robuster aufzusetzen ist, wäre das natürlich cool. ...oder - das will ich nicht ausschließen - ich habe da ein grundsätzliches Verständnisproblem....

Danke Dir und Gruss,
Magnus


  

Betrifft: dazu hatte ich Dir auch gestern geschrieben ... von: neopa C (paneo)
Geschrieben am: 29.10.2014 08:08:19

Hallo Magnus,

... und zwar in Deinem ersten Thread, der gestern noch aktiv war aber nun heute allerdings passiv im Archiv des Forums liegt. Gestern hattest Du dort noch geschrieben, dass die Formelauswertung für Juni (=7) falsch und 5 richtig sei. Darauf hatte ich Dir sinngemäß geschrieben, dass die Formel korrekt auswertet.
In diesem Thread schreibst Du nun, dass sich die Ergebnisse nach dem Sortieren geändert hätten und Du vorher im Juni als Ergebnis eine 5 hattest. Diese nicht zutreffende Aussage hättest Du selbst nochmal überprüfen können, indem Du Deine Daten z.B. mal nach der ID etc. sortierst. Dann stellst Du fest, die Ergebnisse sich zumindest bei mir nicht ändern.

Ein #NV durch VERGLEICH() kann durch die Formel bei Deiner Bereichsdefinition Dich nur dann "einholen", wenn Du z.B. die Werte einer Datenzeile löschst. Die Nichtberücksichtigung leerer Zellen im Auswertungsbereich könnten jedoch durch eine Formelerweiterung noch abgefangen werden.


Gruß Werner
.. , - ...


  

Betrifft: AW: dazu hatte ich Dir auch gestern geschrieben ... von: sungam1990
Geschrieben am: 29.10.2014 10:28:03

Hallo Werner,

sorry, wollte Dich nicht verärgern. Habe auf Bitte von Boris den Thread neu aufgemacht. Mit dem Juniwert (5 vs. 7) habe ich mich selbst reingelegt. Nach dem Sortieren passte der Wert - und das habe ich dann hochgeladen. Mein Fehler, ich habe es nicht noch einmal kontrolliert.

Es trifft aber weiterhin zu, dass, wenn ich in den zugrundeliegenden Daten die Datumssortierung ändere, andere Werte pro Monat ermittelt werden. Habe ich versucht zu beschreiben und eben gerade noch einmal ausprobiert.

Nun wage ich trotzdem nocheinmal zu fragen. Gibt es eine Möglichkeit, auf die richtigen Werte unabhängig von der Sortierung zu kommen?

Grüsse, Magnus


  

Betrifft: das ist doch aber so korrekt ... von: neopa C (paneo)
Geschrieben am: 29.10.2014 14:40:04

Hallo Magnus,

... wenn Du die Datumsreihenfolge "auf den Kopf" stellst, muss es doch in den Monaten eine andere "Neukundenzahl" geben, denn geprüft wird ja immer die gesamte Datenliste beginnend ab C2.
Entscheidend ist, dass die Summe der "Neukunden" immer gleich bleibt, bei Dir in B6=60. Oder, was willst Du wirklich ermitteln?


Gruß Werner
.. , - ...


  

Betrifft: AW: das ist doch aber so korrekt ... von: sungam1990
Geschrieben am: 29.10.2014 23:47:32

Hallo Werner,

Danke, dass Du die Nerven mit mir behälst! Ich versuche es anders zu formulieren. Ich möchte ausweisen, wann (in welchem Monat) ein Kunde ein Neukunde geworden ist. Egal wie viele Bestellungen zu welchen Zeitpunkten auch immer vorliegen, der Kunde wird nur einmal Neukunde (in genau einem Monat). Die Anzahl der Bestellung ist hier vernachlässigt. Es geht um den Punkt, wann eine Adresse ein Neukunde ist; also erstmalig in der Chronologie mit einer Bestellung auftaucht und ob dafür eine Sortierung notwendig ist. Egal, wie ich die Bestelldaten sortiere, gibt es nur ein "erstes" Datum. Nach meinem Verständnis komme ich mit der Summierung der Neukunden auch auf die Gesamtzahl der Kunden (hier 60).

Ist das verständlicher? Sorry, wenn meine Formulierung zuvor missverständlich war.

Grüsse, Magnus




  

Betrifft: Anliegen verstanden, ist aber nicht verständl... von: neopa C (paneo)
Geschrieben am: 30.10.2014 13:51:29

Hallo Magnus,

... eine Grundvoraussetzung der Formel ist es, dass die Daten zunächst nach Datum aufwärts sortiert vorliegen. Dann erhältst Du ein korrektes Ergebnis.

Du kannst auch, wie ich bereits schrieb, anschließend z.B. nach ID etc. sortieren und das Ergebnis bleibt das gleiche.

Wie ich auch schon schrieb, ändert sich das Ergebnis jedoch, wenn Du die Daten nach Datum abwärts sortierst.

Wenn Du nun ein davon unabhängiges gleiches Ergebnis bekommen willst, kann man nicht VERGLEICH() einsetzen. Doch mir ist momentan nicht verständlich, warum Du unbedingt die Daten auch nach Datum abwärts sortiert haben möchtest? Gibt es dafür einen nachvollziehbaren Grund?


Gruß Werner
.. , - ...


  

Betrifft: AW: Anliegen verstanden, ist aber nicht verständl... von: sungam1990
Geschrieben am: 01.11.2014 20:56:07

Hallo Werner,

sorry für die späte Rückmeldung. Ich hab es jetzt verstanden. Ich muss, wenn ich eine neue Datenbasis unterschiebe ersteinmal kosequent sortieren, um auf die richtigen Ergebnisse zu kommen. Das hatte ich halt beim ersten Mal nicht gemacht und das hatte zu Irritationen geführt - und in der Unruhe hatte ich es aus Deinen Antworten nicht richtig rausgelesen. Danke für Deine Unterstützung, die Lösung ist klasse und das hat mir sehr weitergeholfen!!
Grüsse, Magnus


  

Betrifft: eine Lösung findet sich (fast) immer ... von: neopa C (paneo)
Geschrieben am: 02.11.2014 08:18:18

Hallo Magnus,

... man sollte sich aber den Weg dahin so leicht wie möglich machen. Und das vorherige Sortieren Deiner Daten nach Datum ist nicht eine eine Erleichterung für die Lösungsfindung und bietet sich in meinen Augen auch aus "darstellerischen" Gründen an. Wenn dies (aus welchen Gründen auch immer) nicht möglich gewesen wäre, würde die Auswertung trotzdem möglich sein, nur eben anders. Aber man muss ja nicht unbedingt in den 7. Stock über die Treppe laufen, wenn der Fahrstuhl fahrbereit ist ... es sei denn man macht es um fit zu bleiben ;-)

Gruß Werner
.. , - ...


 

Beiträge aus den Excel-Beispielen zum Thema "Summenprodukt mit VERGLEICH"