PDA

Vollständige Version anzeigen : Einzigartige Werte in einem Bereich als Matrixformel?


kaffeeinsel
10.03.2009, 09:45
Hallo,

ich habe eine Liste mit Fertigungsaufträgen aus SAP gezogen, die so aufgebaut ist:

(A) (B) (C)
(1) F-Auftrag Datum F-Art
(2) 100678 05.01.09 120
(3) 100756 23.03.09 122

Nun ist es so, dass ein F-Auftrag auch mehrfach auftreten kann. Ich will nun berechnen, wieviele Aufträge in jedem Monat waren.

Ich verwende folgende Formel, um die Anzahl der einzigartigen FA-Nummern im angegeben Bereich (hier: A2:A2000) zu berechnen.

{=SUMME(WENN(HÄUFIGKEIT(A2:A2000;A2:A2000)>0;1))}

Ich verwende folgende Formel, um die Anzahl *aller* FA-Nummern zu berechnen, die im Monat X liegen und einer bestimmten Fertigungsart (hier bsplw. 120) entsprechen:

{=SUMME(N(MONAT(A2:A2000)=X))*N(C2:C2000=120))}


Ich möchte nun diese beiden Funktionen so zusammenfassen, dass ich alle einzigartigen Fertigungsaufträge in einem Monat herausbekomme. Dabei ist die Fertigungsart egal (also alle).

IngGi
10.03.2009, 10:34
Hallo kaffeeinsel,

versuch mal folgendes:

{=SUMME((HÄUFIGKEIT(A2:A2000;A2:A2000)>0)*(MONAT(A2:A2000)=X))}

Mich wundern in deiner letzten Formel die beiden N()-Funktionen. Damit verweist du doch eigentlich auf alle Fälle, die nicht in einem bestimmten Monat liegen und nicht einer bestimmten Fertigungsart entsprechen.

Gruß Ingolf

kaffeeinsel
10.03.2009, 11:29
Hallo,

danke für die Antwort. Deine Formel sieht gut aus, aber ich bekomme als Ergebnis #NV. Was die N()-Funktionen betrifft: Ich vergleiche ja Zellen auf Gleichheit, was im gewünschten Fall WAHR liefert. N() wandelt das dann in eine 1 um. Dürfte also stimmen, passt auch mit der Handrechnung überein. Ich habe erst versucht, gänzlich ohne N() zu rechnen, das lieferte aber nicht das richtige Ergebnis.

Nachtrag:
Das #NV kommt dadurch, dass die beiden Faktoren Vektoren unterschiedlicher Länge liefern. Ich habe den Bereich jetzt mal auf die Monate Januar und Februar eingeschränkt und verglichen:

{=ZEILEN(MONAT(B2:B183)=1)}

liefert 182 (so wie es sein sollte)

=ZEILEN(HÄUFIGKEIT(A2:A183;A2:A183))

liefert 183. Dadurch erhält Deine Formel im letzten Element ein {.., .., #NV}, wodurch der ganze Ausdruck #NV wird.

kaffeeinsel
10.03.2009, 13:01
Ich hab's jetzt mal als Datei angehängt.

IngGi
10.03.2009, 13:19
Hallo kaffeeinsel,

stimmt, HÄUFIGKEIT liefert immer ein Element mehr, als der als zweiter Parameter Klassen angegebene Zellbereich. Ich habe versucht, dieses zusätzliche Element mit der Funktion BEREICH.VERSCHIEBEN() abzuschneiden. Leider kann die Funktion offenbar nur auf Zellbereiche oder auf mehrdimensionale Matrizen, jedenfalls aber nicht auf Vektoren als Ergebnis einer vorangegangenen Funktion zugreifen. Die einzige Möglichkeit, die mir jetzt noch einfällt wäre, auch den zweiten Vektor um ein Element zu verlängern und sicherzustellen, dass die betreffende Zelle immer leer ist. Eine leere Zelle wird allerdings als Null und im Weiteren interessanterweise als Januar gedeutet. Das heißt, wenn auf Januar geprüft wird, müsste am Ende wieder 1 abgezogen werden.

Gruß Ingolf

kaffeeinsel
10.03.2009, 14:16
Ich habe einfach mal den Klassenvektor nur bis 182 laufen lassen und das Ergebnis stimmt. Ich vermute aber, dass das nicht die entgültige Lösung sein kann. Hast Du Dir mal die Tabelle angeschaut, die ich oben angehängt habe? Dort habe ich HÄUFIGKEIT auf einen Vektor angewendet und das Ergebnis als Vektor belassen. Dann stimmt die Zeilenanzahl! Kannst Du das erklären?

IngGi
10.03.2009, 15:14
Hallo kaffeeinsel,

du meinst die Anwendung von HÄUFIGKEIT in Spalte E? Hier hast du zwar nur 182 ausgegebene Ergebnisse, der Vektor besteht aber trotzdem aus 183 Elementen. Das letzte Element wird einfach verschluckt, da du für die Ausgabe des Vektors nur 182 Zellen in der Spalte markiert hast. Wenn du mit der Funktion in dieser Form allerdings weiterrechnen würdest, hättest du wieder das Problem mit dem überflüssigen zusätzlichen Element.

Ich sehe nun noch eine andere Lösungsmöglichkeit, die aber nur funktioniert, wenn die Datumsangaben in Spalte B wie in der Beispielmappe sortiert sind. Dann kann man jede Nummer in Spalte A nur einmal zählen, indem man diese mit der vorangehenden vergleicht und nur zählt, wenn sie nicht gleich ist. Also:

{=SUMME(($A$2:$A$2000=$A$1:$A$1999)*(MONAT($A$2:$A$2000)=X))}

Wenn es zu einer Nummer in Spalte A mehrere Datumsangaben in Spalte B gibt, wird damit allerdings nur die erste Nummer in Spalte A berücksichtigt!

Gruß Ingolf

kaffeeinsel
11.03.2009, 08:51
Hallo,

ja das hast Du natürlich recht. :redface:

Die Datumspalte zu sortieren, wäre kein Problem, aber Deine Formel gibt mir als Ergebnis #ZAHL.

Es scheint aber, als würde das Ergebnis mit dem um 1 verkürzten Vektor trotzdem richtig.

IngGi
11.03.2009, 09:09
Hallo kaffeeinsel,

sortiert werden muss natürlich nicht nach dem Datum, sondern nach der Nummer in Spalte A. Und in der Formel ist auch ein kleiner Fehler. Die muss folgender maßen aussehen:

{=SUMME(($A$2:$A$2000=$A$1:$A$1999)*(MONAT($B$2:$B$2000)=1))}

Bei mir spuckt die Formel dann für den Monat Januar das Ergebnis 1906 aus.

Gruß Ingolf

kaffeeinsel
11.03.2009, 13:20
Hmm, ja jetzt geht sie bei mir auch mit 1906 als Ergebnis. Aber es sollte ja 36 für den Januar und 19 für den Februar herauskommen ... 1906 ist viel zu viel, weil es ja nur 183 Datensätze sind. Hast Du noch eine Idee?

IngGi
11.03.2009, 13:45
Hallo kaffeeinsel,

mit kleinen Schritten tasten wir uns heran...

Neuer Versuch:

{=SUMME(($A$2:$A$2000<>$A$1:$A$1999)*(MONAT($B$2:$B$2000)=1))-($A$2000="")}

Damit komme ich für den Monat Januar auf 36 Fälle.

Gruß Ingolf

kaffeeinsel
16.03.2009, 08:16
Hallo,

super! Das funktioniert schon fast richtig. Bei mir kommt auch 36 für Januar raus, aber 18 für Februar und da müssten es 19 sein.

IngGi
16.03.2009, 09:13
Hallo kaffeeinsel,

der letzte Teil der Formel, der abgezogen wird, gilt nur für den Monat Januar. Für die anderen Monate muss dieser Teil weggelassen werden.

Gruß Ingolf