PDA

Vollständige Version anzeigen : Summen-Formel funktioniert nicht mehr ...


zorraldo
08.07.2014, 13:26
Hallo,

mit Unterstützung aus diesem Forum hatte ich mir für das letzte Jahr eine Summenformel erstellt, die monatsweise Maximal- bzw. Minimalwerte aus einer Produktkostentabelle ermittelt. Für das aktuelle Jahr - in dem ein auszuwertender Monat hinzugekommen ist - funktioniert die Summenformel der Maximalwerte nicht mehr - und ich finde den Fehler nicht :mad: (s. C107 im Tabellenblatt "Min-Max 2014" in der beigefügten Datei - Excel 2003).

Könnte sich das bitte mal jemand ansehen, wo es da "klemmt"?

Danke schon mal vorab.

Gruß
Zorraldo

chris-kaiser
09.07.2014, 08:15
Hi,

das -1 habe ich entfernt,
und ich habe mir nur die Matrix angeschaut die MMULT erzeugt....
bei 2014 ist da -6, bzw-7 in der Matrix.
Aber eine Erklärung habe ich jetzt dafür nicht...

Superingo2
09.07.2014, 09:48
Hallo Zorraldo,

das ist eine sehr interessante Formel.
Auf die Schnelle habe ich schon bemerkt, dass Dein Problem nicht am Jahreswechsel an sich liegt, denn die Formel hat einen Fehler. Sowohl in 2013 wie auch in 2014. Sie funktioniert halt zufälligerweise in 2013 richtig, da überall Einträge stehen.
Ich studiere die Formel noch ein wenig, und melde mich dann mit der Korrektur der Formel.

LG Ingo

chris-kaiser
09.07.2014, 11:09
Hallo Ingo

habe inzwischen die Quelle gefunden:
http://www.excelformeln.de/formeln.html?welcher=495

Superingo2
09.07.2014, 11:35
Hallo Zorraldo,

so ich glaube ich habe es (und hoffentlich jetzt keinen Flüchtigkeitsfehler eingebaut).
Also der erste Fehler liegt daran, dass die Formel so nur für Minimum entwickelt worden ist, und das einfache umdrehen von ">" auf "<" nicht funktionieren kann, wenn es Spalten gibt in den noch keine Werte stehen (und nur deshalb funktioniert dies in 2013 und in 2014 dann nicht mehr).

Zweitens wurde das "-1" in 2013 dazugefummelt, da es eine Zeile mit Nullwerten gibt (Zeile47). Das macht in 2014 dann aber keinen Sinn mehr.

Ich habe die Formeln diesbezüglich angepasst.

Übrigens: Falls es mehrere gleich hohe oder niedrige Werte gibt, die das Maximum bzw. Minimum der Zeile darstellen (siehe Zeile19 in 2013), dann werden die auch jedesmal mitgerechnet. Daher gibt es in 2013 bei 95 Datenzeilen abzüglich einer Nullzeile nicht 94 Maxima und 94 Minima, sondern 94 Minima und 96 Maxima!

LG Ingo

@Chris: Danke für die Info, ich wunderte mich auch schon über die Genialität der Formel. Die kann nur ein SUPERPROFI entwickelt haben. Da reicht kein SUPERINGO für ;)

chris-kaiser
09.07.2014, 11:45
Hi,

das schaffst du schon :)
Aber siehe mal in die Datei.

komische Sache mit dem Konstrukt...

ich würde es einfach mit zwei Hilfsspalten lösen^^
(zweite Datei mit Hilfe)

Superingo2
09.07.2014, 12:16
Hallo Chris,

Deine Lösung gefällt mir, weil sie viel einfacher verständlich ist und somit auch leichter anpassbar wird.
Was (zufällig?) damit auch besser läuft ist, wenn es mehrere gleich große Minima oder Maxima gibt, wird jeweils nur das linkeste davon gezählt! Schön. Sehr schön.
Allerdings wird das Problem einer Nullerzeile (siehe Zeile47/2013) noch nicht gelöst. Genauso wie das Problem, dass bei fehlender Zeileneingabe "#nv" erscheint. Beide Probleme sind aber sehr leicht (das schafft der TS selber?) anzupassen.

@Zorraldo: Anbei nochmal meine Datei, in der ich einen kleinen Bug behoben habe (Dank dem Hinweis von Chris).

LG Ingo

zorraldo
09.07.2014, 12:32
Hallo Chris, hallo Ingo,

Danke für die Mühe, die Lösung(en) und die "Aufklärung" zu den Formelmysterien ...

Der Einfachheit halber hätte ich ja auch den Link hier aus dem Forum posten können, als damals die Formel entwickelt wurde - finde den aber gerade auf Anhieb nicht -sorry ...

Danke an Ingo für den Hinweis zu der minus 1 in der alten Formel. D.h., falls es wieder einmal zu Null-Werten kommen sollte muss ich die (alte) Formel entsprechend anpassen (?). Außerdem hilfreich der Hinweis zur Zählung bei gleich hohen oder niedrigen Werten - da muss ich nochmals nachdenken, was ich da eigentlich als Ergebnis haben möchte ...

Danke an Chris für die alternative Lösung. Mit Hilfe der Excel-Hilfe habe ich versucht die Funktion SPALTE nachzuvollziehen. Dennoch: Weshalb in

=SUMMENPRODUKT(--($L$8:$L$102=SPALTE(A1)))

A1 als Bezug stehen muss und was das für zwei komische "Minusse" vor der zweiten Klammer sind - ziemlich "abgefahrenes" Formelkonstrukt ...:boah:

Wünsche noch einen nicht zu heißen Nachmittag!

Gruß
Zorraldo

Nachtrag: Während des Schreibens kam die #7 - Kann ich mir erst später ansehen ...

chris-kaiser
09.07.2014, 12:50
Hi,

spalte(A1) liefert einfach 1

vom Prinzip her: in welcher Spalte liegt die Zelle A1

wird das nach rechts kopiert steht
=spalte(b1)

das liefert jetzt 2

usw.

die Vergleichfunktion gibt die Position in der Matrix von min und Max zurück
1 =erste Spalte usw..

Das -- ist nur dazu da um TRUE, FALSE in 1 und 0 umzuwandeln
da könnte auch +0 oder *1 verwendet werden, ich nehme gerne --

Es würde aber auch einfach =ZÄHLENWENN($L8:$L102;SPALTE(A1))
in Zelle C106 reichen

hier noch ohne "Nuller" ^^, geht aber nur dann wenn nicht -1, +1 also -werte und +Werte sich aufheben würden.
Aber wie ich aus deinem Beispiel ableiten kann, sind nur positive Werte vorhanden.

Superingo2
09.07.2014, 13:24
Hallo Zorraldo,

das Nullzeilenproblem habe ich mit dem Formelstück
-(C8:C102=0)
gelöst. Somit brauchst Du nie mehr "-1" oder so zu ergänzen (allerdings kann somit auch die Null in nur einer Zelle nie als Maximum oder Minimum gezählt werden).

@Chris: Ich habe Deine Förmelken ein bischen optimiert.
Spalte(A1) in Spalte() und dafür die Vergleichmatrix ab Spalte A beginnen lassen.
Und damit auch Minuswerte richtig erfasst werden, habe ich SUMME(C87:K87)<>0) in SUMME(C87:K87)<>MAX(C87:K87)) geändert.

LG Ingo

zorraldo
09.07.2014, 19:36
Hallo Ingo,

Danke für die Optimierung der Formel - inzwischen doch eine Superingo-Formel :grins: - sowie die Erläuterungen ...

Noch eine Frage zur Formel z.B. in L87:

Wäre es nicht korrekter statt

=(SUMME(C87:K87)<>MAX(C87:K87))*VERGLEICH(MIN(C87:K87);A87:K87;0)

erst ab C87 zu vergleichen:

=(SUMME(C87:K87)<>MAX(C87:K87))*VERGLEICH(MIN(C87:K87);C87:K87;0)

Gruß und schönen Abend
Zorraldo

Superingo2
10.07.2014, 07:50
Hallo Zorraldo,

Danke für Dein Feedback.

Spalte(A1) in Spalte() und dafür die Vergleichmatrix ab Spalte A beginnen lassen.


Den Vergleichsbereich in Spalte A beginnen zu lassen (A87:K87), ist ja gerade der Trick, damit man in der sich darauf beziehenden weiterverarbeitenden Formel nicht mehr mit Spalte(A1) arbeiten muss (was durch den relativen Bezug immer "aktuelle Spalte minus 2" bedeutet).
Die Vergleich-Formel kann zwar nie einen Treffer in Spalte A oder B erzielen und somit auch nie als Ergebnis 1 oder 2 liefern, aber dafür gibt sie nun auch mit dem Ergebnis (z.B.: 3) die tatsächliche Spalte als Nummer aus (z.B.: 3, also C).
Aber wenn Du die Lösung von Chris besser verstehst, ist diese genauso korrekt.

Mein Vorschlag "Summe ungleich Max" ist auch nicht wirklich optimal für alle mathematisch möglichen Zahlenkombinationen, sondern nur auf die Schnelle etwas besser, als die von Chris. Wenn eh keine Minuswerte vorkommen können, reicht Chris´s "Summe ungleich Null" völlig aus. Mein Vorschlag würde noch so etwas wie "0;-3;0;1;2;0;0" abfangen, aber leider nicht "0;0;0;3;0;0;0". Eigentlich muss da noch Min mit rein in die Formel, aber ich vermute mal, dass dies alles so nicht vorkommen kann, dann nimm nur "Summe ungleich Null"

Nun alles klar?

LG Ingo

zorraldo
10.07.2014, 15:09
... Ja, alles klar!

Formel kommt mit den Erläuterungen ins Excel-Schatzkästen und danke nochmals für euer Engagement!

Gruß
Zorraldo