Office: Auswertung ohne Duplikate

Hallo,

ich bitte um Unterstützung bei einer Auswertung, bei der Personen nur 1 x gezählt werden sollen (Excel2010):

In der beigefügten Mustertabelle sind PIDs (=Personen) aufgelistet, die pro Monat Leistungen (=1) erhalten. Die Leistungen werden zwei Produkt-Nummern zugeordnet (s. Sp. N), entweder 80995 oder 80996. Einige PIDs erhalten mehrere Leistungen pro Monat (s. z.B. PID 79404). Die Ergebnisse ab Sp. Q. für Jan - März sind per Hand eingefügt.

Die Auswertung soll nun pro Monat pro o.g. Produkt-Nr. die Anzahl der PIDs mit "1" jedoch die Person nur 1 x gezählt berechnen. So würde z.B. PID 79404 (hat 4 x eine 1 im Januar, mit je 2 x einer Zuordnung zu 80995 und 80996) für Jan. jeweils 1 x für 80995 und 80996 gezählt werden.

Meine Versuche, dies mit Pivot anzugehen scheiterten bisher, da ich nicht weiß, wie ich die mehrfach vorkommenden PIDs "herausrechne" - evt. gibt es hierzu ja auch eine "Formel-Lösung", die Mehrfach-PIDs nur 1 x zählt.

Danke schon mal vorab für die Unterstützung.

Gruß Zorraldo

:)

 

Hallo Zorraldo,

es gibt wohl mehrere Möglichkeiten, um das gewünschte Ergebnis zu erzielen. Ich habe es mit Power Query realisiert. Das Ergebnis siehst du ab P25. Dabei komme ich im Jan. für Produkt 80996 allerdings nur auf 9.

 

Hallo Zorraldo,

ich habe die Auswertung nun noch um die Zählung "Gesamt" erweitert.

Im übrigen lassen sich auch einige seiner anderen Fragestellungen gut mit Power Query realisieren.

 

Hallo WS-53,

danke zunächst einmal für die Beschäftigung mit dem Thema und die Lösungsvorschläge.

Der Hinweis mit der 9 im Jan. im P. 80996 ist korrekt - da war wohl meine "Handzählung" falsch - sorry ...

Der Lösungsvorschlag per PQ ist leider hier mit meiner Excel-Umgebung (2010) nicht ganz kompatibel (zumindest bekomme ich eine PQ-Kompatibilitätswarnung); außerdem würde ich gerne auf diese Herangehensweise verzichten, da die originale Datenquelle deutlich umfangreicher ist und diejenigen, die außer mir hiermit arbeiten müssen mit PQ nicht vertraut sind und dort PQ evt. nicht als Add-ON installiert ist.

Könnte es auch eine formelbasierte Lösung geben? Ich vermute, dass es mit SUMMENPRODUKT/ VERGLEICH/ ZEILE funktionieren könnte, habe jedoch keine Schimmer, wie das zusammengehören würde*frown.gif*

Gruß Zorraldo

 

Hallo Zorraldo,

seit der Version 2016 ist Power Query fester Bestandteil von Excel. Ab Excel 2013 auf jeden Fall und für verschiedene Versionen von Excel 2016 kann dieses Add-In kostenlos installiert werden. Und sofern du als Controller demnächst Grafiken und Dashboards mit Power BI erstellst, solltest du wissen, dass bei Power BI der Import über Power Query erfolgt. Insofern macht es Sinn, Power Query nicht so weit weg zu schieben. Und wenn du dich erst einmal an das Konzept von Power Query gewöhnt hast, wirst du feststellen, dass sich damit sehr viele Aufgaben recht einfach realisieren lassen, für die sonst Formelmonster oder VBA-Programmierung notwendig gewesen wäre. Genau wie VBA hat es den Vorteil, dass die Ergebnisse formelfrei sind und somit gegenüber sehr rechenintensiven Formeln, die Antwortzeit nicht negativ belastet. Abgesehen davon, dass VBA sehr leistungsfähig und berechtigt ist, lernt ein normaler Anwender eher Power Query als VBA. Witerhin würde ich mir die Frage stellen, warum mich mein Arbeitgeber im Jahr 2020 immer noch mit einer 10 Jahre alten Excelversion arbeiten lässt.

Aber vielleicht findet sich ein Spezialist, der die die Ergebnisse mit einer Formel ermitteln kann.

 

Q6: =ANZAHL(EINDEUTIG($A$6:$A$26*B$6:B$26*($N$6:$N$26=$P6)))-1 kostenlos

 

Hallo,

zunächst einmal an WS-53: danke für die ausführliche Einschätzung meines derzeitigen "Excel-Kompetenz-Profils". Der Verweis auf PQ und BI ist berechtigt. Jedoch: mein Arbeitgeber ist eine große öffentliche Verwaltung. Jeden (Arbeits-)Tag, wenn ich ihn verlasse, verlass ich einen "Digitalisierungsgrad", der jenem in der Außenwelt um ca. 10-15 Jahre hinterher hinkt. Wir sind gerade dabei die virensichere Kreide abzuschaffen und unsere Restbestände an Tafelschwämmen werden zu Mundschutzmasken umgearbeitet. Insofern kann ich ja schon froh sein, mit Excel 2010 arbeiten zu dürfen ... Ich hoffe, ich erzeuge maximales Mitleid *frown.gif* In meiner privaten Excel-Umgebung bin ich bereits etwas weiter (2016), fange an, mich mit PQ etc. zu beschäftigen, kann damit aber noch nicht locker jonglieren ...

@ lupo1: Danke für die Formel - "Eindeutig" kannte ich bisher nicht. Leider erzeugt das Ergebnis (s. beigefügte Datei in C35) "-1"

Ich habe selbst noch einen Versuch mit mit SUMMENPRODUKT/ VERGLEICH/ ZEILE gestartet (s. Formel in C31) - gegenüber meiner "Handzählung" stimmen aber die Ergebnisse nicht und ich finde den Fehler nicht ...

Gruß Zorraldo

 

Deine Version hatte ich mit "kostenlos" berücksichtigt.

 

Hi

hier als Makro:
Auch kostenlos

Code:

Die Koordinaten der ersten Zelle oben eintragen.
Der Bereich unter der Tabelle wird mit dem Ergebnis überschrieben.

Vor dem Einsatz bitte testen.

LGB

 

Auch Barbara hat mein "kostenlos" nicht verstanden. Es geht darum, wie JEDER neueste Funktionen ohne das neueste Excel trotzdem verwenden kann. Übrigens auch in GDocs.

 

\@ lupo1: Das mit dem "kostenlos" bringt mich hinsichtlich meiner Fragestellung nicht weiter, da hier nicht operativ nutzbar bzw. weiß ich nicht, was ich "sehen" soll (?). Könntest du bitte nochmals hinsichtlich der von dir erstellten Formel schauen, weshalb die sich im Tabellenblatt meiner letzten Datei nicht umsetzen lässt - merci ...

@Barbara: Danke für deine Mühe - Makros sind jedoch inzw. aus "Sicherheitsgründen" in unserer Arbeitsumgebung nicht mehr einsetzbar - insofern bin ich auf eine Umsetzung per Formel angewiesen - sorry ...

Gruß
Zorraldo

 

Hallo Zorraldo,

bei mir ermittelt Lupos Formel die richtigen Werte. Bei dir wohl nicht, da die Funktion Eindeutig in der 2010er Version noch nicht verfügbar ist. Deshalb auch der Link "kostenlos " damit du testen kannst, wie es gehen könnte, wenn dein Arbeitgeber nicht so weit in der Vergangenheit leben würde.

 

Danke schön, WS-53!

 

Guten Vormittag!

Danke für die Aufklärung zur Zukunftsträchtigkeit der "Eindeutig-Funktion" und dem Hinweis, dass es dort funktioniert - nur was mache ich nun damit in der Gegenwart, da ich eigentlich nicht gewillt bin, ca. 5 Jahre zu warten, bis mir in meiner Excel-Version diese Funktion zur Verfügung steht ...?

Vielleicht findet sich hier im Forum ja jemand, dem zu meinem (offenbar fehlerbehafteten) Formelversuch noch etwas einfällt ...

Grüße Zorraldo

 

Hallo Zorraldo,

kannst du bitte mal erläutern, wie die "Einsen" ín die Tabelle kommen, die ja im Original vermutlich viel umfangreicher ist und wohl auch mehr Produktnummern enthält.

Eventuell ergibt sich mit den Hintergrundinfos ein alternativer Lösungsansatz.

Ansonsten kann ich nur feststellen, wenn du die Zählergenisse haben möchtest, weil es dich persönlich interessiert, dann ist es dein Problem. Sollst du diese Ergebnisse aber liefern, dann hat dein Arbeitgeber ein Problem und du bist fein raus, da du dich ja redlich um eine Lösung bemüht hast, du aber 3 funktionierende Lösungen nicht verwenden kannst.

Und da ja bekanntlich alle guten Dinge 3 sind, wird es u.U. schwer werden, noch eine 4. Lösung zu finden, die auch bei dir funktioniert.

Wobei die 4. Variante könnte sein, es bei dir im Home Office zu realiseren, da ja auf deinem privaten Rechner ein morderneres Office installiert ist.