Frage von felixschaefer92, 72

SummenProdukt Formel Frage: Kann mir jemand helfen?

Ich habe eine Datentabelle mit Montage Start, Montage Ende, einem Titel und einer Anzahl an montierbaren Teilen. Nun möchte ich ein Balkendiagramm erstellen welches mir sagt, in welchem Monat wie viele Teile montiert werden müssen.

Derzeit hab ich eine lange Tabelle mit vielen Montagestart und Montageende Daten und vielen gleichen Titeln. Ich möchte nun eine Tabelle in der zu jedem Titel gesagt wird, in welchem Monat wie viele Teile montiert werden müssen.

=SUMMENPRODUKT((ODER((UND(E2:E41<=K10;F2:F41>K10));UND((E2:E41>=K10);(E2:E41<L10))))(D2:D41)(A2:A41=H9))

Ist die derzeitige Formel die ich verwende.

A2:A41 Titel
D2:D41 Anzahl

E2:E41 Montagestart - Daten

F2:F41 Montageende -Daten

K10 erster Monat meiner Achse, in diesem Fall Januar 2016

 H9 Titel in neuer Tabelle ohne Duplikate.

Die Formel spuckt derzeit zwar einen Wert aus, dieser ist aber um Meilen verkehrt. Über Hilfe würde ich mich sehr freuen, Liebe Grüße, Felix

Antwort
von Funfroc, 20

Hallo,

auch nach den Kommentaren bin ich noch nicht so richtig schlau.

In deinem Fall wäre es ganz sinnvoll eine Datei zu sehen, um den Aufbau vor Augen zu haben und in der Datei operieren zu können.

Dafür ist dieses Forum hier meiner Ansicht nach nicht geeignet. Dafür gibt es explizite Excel-Foren, wo man dann halt auch seine Datei hochladen kann. Als Beispiel mal genannt das Herber-Forum oder auch Office-Lösung.

LG, Chris

Antwort
von k3ltis, 47

Ich würde die Tabelle horizontal für jeden Monat aufstellen und am Ende jeder Spalte eine Summe pro Monat eintragen.

     JAN                    FEB                    ...
Start Ende Name Teile Start Ende Name Teile
+--------------------+ +--------------------+

+--------------+-----+ +--------------+-----+
Summe + xyz + xyz

Und dann über der Summenzeile die Statistik bilden. Einfacher, als wenn du alles untereinander hast.

Kommentar von felixschaefer92 ,

Danke erstmal für deine schnelle Antwort. Bei dieser vorgehensweise müsste ich aber jede Zeile einzeln durchgehen und alles händisch notieren. Da dieses Bespiel nicht das einzige Bespiel ist und ich umfangreiche Datenlisten auswerten muss, hilft mir ein händisches Einfüllen der Tabelle nicht.

Die Tabelle war aber eben auch mit einer horizontalen Achse für die Monate gedacht und als Y-Achse sind die Titel gewählt. Das Diagramm wird nach Erstellung der Tabelle dann in gestapelten Balken dargestellt.

Die oben gezeigte Formel zeigt nur den Inhalt eines Elements der Tabelle, in diesem Fall für alle summierten Werte mit den Titel H9 im ersten Monat

Kommentar von k3ltis ,

In etwa so ?


JAN FEB

Teile Teile
Titel1 14 23
Titel2 8 1
Titel3 12 9

Kommentar von felixschaefer92 ,

Ja so war es gemeint

Antwort
von Jackie251, 34

was steht denn in L10 ?

Kommentar von felixschaefer92 ,

Sorry, in L10 steht der nächste Monat, also Februar 2016

Kommentar von Jackie251 ,

Dann verstehe ich zwar nicht, warum die Titelliste ohne Duplikate in zeile 9 und nicht in Zeile 11 Beginnt, aber gut.

Eigenartig ist die Überprüfung ob die Eingabe korrekt ist, innerhalb der Auswertungsformel.
Die Eingabelogik (also ist das Enddatum >= dem Anfangsdatum) sollte viel früher geprüft werden.
Denn etwas anderes macht ja deine Und formel nicht.
Das erste UND prüft ob die Montage vor bis zum Stichttag begonnen wurde UND ob das Ende nach dem Stichtag liegt.
Wenn das Ende immer größer sein muss als der Anfang, dann reicht einer Prüfung völlig aus. Umso eigenartiger ist, das Teil 2 der Oderprüfung dann falsch herum eingetragene Montagen wieder zulässt.

Die Oderfunktion ist dabei ein unverständlicher Filter.
Es liefert dann Wahr, wenn die Montage vor bis zum Stichtag begonnen wurde oder wenn die Montag vor dem nächstem Stichtag endet (und nach dem aktuellem Stichtag begonnen wurde)

Projekte die über mehrer Monate laufen, werden in jedem Monat mit voller Teilezahl berücksichtigt.

Kardinalfehler ist natürlich das zu gleichzeitig alle Zeilen in Und/Oder Funktionen pakts - jedoch eine Matrixauswertung benötigst.

So liefert E2:E41<=K10 zwar korrekt für jede Zeile die Rückmeldung ob das Startdatum vor dem Stichtag liegt, durch die Zusammenfassung mit (UND(E2:E41<=K10;....))
wird daraus jedoch nur ein einziger Wahrheitswert.

Kommentar von felixschaefer92 ,

"Das erste UND prüft ob die Montage vor bis zum Stichttag begonnen wurde UND ob das Ende nach dem Stichtag liegt.
Wenn das Ende immer größer sein muss als der Anfang, dann reicht einer Prüfung völlig aus. Umso eigenartiger ist, das Teil 2 der Oderprüfung dann falsch herum eingetragene Montagen wieder zulässt."

Meiner Meinung nach sollte eine Prüfung, ob der Montage davor startet nicht ausreichen, weil man muss ja auch Überprüfen, ob noch nicht das Montageende erreicht ist, dehalb muss ich,trotz der der Tatsache, dass das Ende immer größer als Anfang sein muss.

Der zweite Teil  der ODER Prüfung war so gemeint, dass auch wenn mein Montagestart nach meinem ersten Stichtag, z.B. dem 1. Januar ist, kann es trotzdem noch im Januar beginnen. Also wird geprüft aber der Montagestart zwischen dem 1. Januar und dem 1. Februar liegt.

"Projekte die über mehrer Monate laufen, werden in jedem Monat mit voller Teilezahl berücksichtigt."
"

Das die Projekte über mehrere Monate laufen und in jedem Monat mit voller Teilezahl berücksichtigt werden ist mir bewusst, stellt aber auch kein Problem dar.

"Kardinalfehler ist natürlich das zu gleichzeitig alle Zeilen in Und/Oder Funktionen pakts - jedoch eine Matrixauswertung benötigst.

So liefert E2:E41<=K10 zwar korrekt für jede Zeile die Rückmeldung ob das Startdatum vor dem Stichtag liegt, durch die Zusammenfassung mit (UND(E2:E41<=K10;....))
wird daraus jedoch nur ein einziger Wahrheitswert."

Ich hatte mir das so gedacht, dass meine Summenformel entweder einen Wahr wert( also 1?) ausspuckt oder einen falsch (also 0). Bei meinem Summenprodukt würde das ja dann zb so ausschauen:

20 (Anzahl) * 0 weil nicht gegebenem Zeitraum, oder 20 * 1 da meine UND/ODER Schleifen erfüllt sind. Habe aber leider bisher noch nicht sehr viel Erfahrung was Excel betrifft, kann also in diesem Punkt durchaus von einer besseren Lösung überzeugt werden :)

"

Kommentar von Jackie251 ,

Grundsätzlich scheint mit die Funktion Summewenns für dein Ziel sinnvoller als Summenprodukt.

Kommentar von Jackie251 ,

Im Grunde reichen doch 2 Prüfungen aus ob ein Projekt im aktuellem Monat liegt:

Wurde das Projekt vor dem ersten Tag des nächsten Monats begonnen?
Endet das Projekt nicht vor dem Anfang dieses Monats?
Kann man beides mit JA beantworten, wird das Projekt im betrachtetem Monat bearbeitet

In einer Summewenns Funktion sähe das so aus:

=SUMMEWENNS($D$2:$D$41;$E$2:$E$41;"<"&L$10;$F$2:$F$41;">="&K$10;$A$2:$A$41;"="&$H9)
Kommentar von Jackie251 ,

wobei nach meinem Verständnis die Titel ohne Dopplungen erst in H11 und nicht in H9 beginnen dürften.
ggf den letzten Wert daher anpassen..

Kommentar von felixschaefer92 ,

Die Formel sieht schonmal gut aus! Danke dafür.

Fällt bei einem der Projekte der Start bespielsweise auf den 2.1 und endet am 31.1 so würde das Projekt weder im Januar, noch im Februar auftauchen...

Ich schätze mal eine Oder Schreibweise kann ich nicht verwenden in meiner Matrixauswertung? 

Kommentar von Jackie251 ,

Wieso sollte das Projekt dann nicht auftrauchen?

Januar bedeutet Stichtag 01.01.2016
Stichtag des Folgemonats 01.02.2016
Tag vor dem Stichtag dieses Monats 31.12.2015

Beginnt das Projekt vor dem Stichtag des nächsten Monats?
JA denn der 02.01.2016 liegt vor dem 1.02.

Endet das Projekt nicht vor dem Stichtag dieses Monats?
Ja, denn der 31.01.2016 kommt erst nach dem 31.12.2015

Beide Fragen mit Ja beantwortet => Projekt wird im Januar berücksichtigt.

Für Februar sehe das ganze dann so aus:

Beginnt das Projekt vor dem Stichtag des nächsten Monats?

JA denn der 02.01.2016 liegt vor dem 1.03.

Endet das Projekt nicht vor dem Stichtag dieses Monats?
Nein, denn der 31.01.2016 liegt vor dem 01.02.2016 

Es wurden nicht beide Fragen mit JA beantwortet => im Februar wird das Projekt nicht berücksichtigt.

Kommentar von Jackie251 ,

Du könntest das ganze auch in Matrixschreibweise machen und Summenprodukt verwenden, dann aber ohne die Und/Oder Funktionen.
Du müsstest die die Wirkung von Und/Oder quasi nachbauen.

Statt Und(Beding1;Beding2) sieht das dann so aus
(Beding1+Beding2)=2
Bzw bei einen Oder (Beding1;Beding2)

(Beding1+Beding2)>1

Dadurch wird das Prüfungsergebnis für jede Zeile erhalten.

Allerdings ist die Formel dann - wegen deiner 7 Wahrheitsprüfungen - sehr unübersichtlich.

Kommentar von felixschaefer92 ,

Stimmt sorry hab iwie bei der ersten Überprüfung immer den aktuellen Monat genommen anstatt den des nächsten, was natürlich viel sinnvoller ist. Danke dir vielmals!!!!!!

Kommentar von Iamiam ,

Da hast Du Dich aber schwer reinvertieft: wenn das keine Hilfreichste gibt...

Keine passende Antwort gefunden?

Fragen Sie die Community