Auswertungsformel

Question

Hallo alle,

ich hab mal wieder ein Problem bei dem ich nicht weiter komme, und zwar mit der Monsterformel hier:

=WENN( WENN(M9<>0;BA9>M9;BL9-$C$3;WENN(L9<>0;AV9>L9;BK9-$C$3;WENN(K9<>0;AQ9>K9;BJ9-$C$3;WENN(J9<>0;AL9>J9;BI9-$C$3;WENN(I9<>0;AG9>I9;BH9-$C$3;WENN(H9<>0;AB9>H9;BG9-$C$3;WENN(G9<>0;W9>G9;BF9-$C$3;WENN(F9<>0;R9>F9;BE9-$C$3))))))));".")

Die Benutzten Felder sind Folgendermaßen zugeordnet:
Verschickt: Zurückerwartet: Zurück:
07 - M09 BL09 BA09
06 - L09 BK09 AV09
05 - K09 BJ09 AQ09
04 - J09 BI09 AL09
03 - I09 BH09 AG09
02 - H09 BG09 AB09
01 - G09 BF09 W09
00 - F09 BE09 R09

C03 = aktuelles Datum

Jeder Wert ist ein Datum.

Beginn beim Verschicken, Zurückerwarte & Zurück ist immer 00,01,etc.

Max-Formel für die Werte ist nicht möglich da Teilweise z.B. 02 + 01 verschickt sind und die Formel immer den am Weitesten zurückliegenden Wert berechnen soll, bzw wenn möglich beide (keine Ahnung ob das möglich ist).

"." soll angezeigt werden wenn Zurück aktueller ist als Verschickt (wird über Bedingte Formatierung einfach ausgeblendet).

Die Formel soll im Prinzip auswerten wie lange eine Verschickte Sache das Rückgabedatum überzieht, bzw. wie lange derjenige noch Zeit hat es zurück zu schicken.

Hoffe ihr könnt mir helfen da ich damit so viele Fehlermeldungen bekommen hab das ich nicht mehr wusste wo ich anfangen sollte nachdem egal was ich gemacht hatte immer ein andere gekommen ist.

mfg Archangel

Answer 1

Hallo,

die Formel dürfte kein Ergebmnis liefern?

Mehr als 8 Ebenen bei wenn
Eine Formel kann bis zu sieben Ebenen einer verschaltelter Funktion enthalten.
http://www.excelformeln.de/formeln.html?welcher=128
=WENN(A1=1;1;WENN(A1=2;2;WENN(A1=3;3;WENN(A1=4;4;WENN(A1=5;5;WENN(A1=6;6;WENN(A1=7;7;)))))))+WENN(A1=8;8;WENN(A1=9;9;WENN(A1=10;10;WENN(A1=11;11;WENN(A1=12;12;)))))
von WF
Die Grenze liegt bei 1024 Zeichen für die Formel.

Wichtig ist es, die erste 7 Gruppe auf "" bei Text, dann weiter mit & oder auf 0 bei Zahl, dann weiter mit +enden zu lassen.
Hinweis Heinz Ulm

Gruß Hajo

Answer 2

Hallo,

leider etwas unverständlich und wenn die Anregung von @Hajo_Zi keine Lösung bringt, dann wär eine Beispieldatei nicht schlecht.

Die Beispieldatei könnte nam hier einstellen

rapidshare.com/index.html

Und den Link hier hinterlegen

Gruß

Helmut

Answer 3

Hallo alle,

Sorry das ich mich erst jetzt wieder melde, aber hatte ein verdammt kurzes Wochenende und gestern nur Stress.

Hab die Excel Datei jetzt hoch geladen unter:
http://www.gmx.de/mc/ZJj8HMPgccLzYYKZdDzW3XjmDu4Ne9

Nicht wundern in dem Ding sind mehr Formeln drin Verankert als mancher in seinem ganzen Leben jemals braucht.
Musste aus der Tabelle die ersten beiden Spalten und den Kopf rauslöschen, da dies Firmenbezogen ist und unter Verschwiegenheit fiel.
Hoffe ihr könnt damit was Anfangen.

mfg Archangel

Answer 4

Hallo,

habe mir mal die Tabelle Angesehen, so komme ich damit nicht klar. Es sind für mich keine Zusammenhänge herstellbar, dies gilt auch nach Einblendung der verschiedenen Spalten.

Hier wäre etwas Erläuterung erforderlich

Gruß

Helmut

Answer 5

Hallo Saarbauer/alle,

hier eine Aufschlüsslung der jeweiligen Bereiche der Excel Datei:

Spalte(n): Nutzen:

C3 Aktuelles Datum

F-M Wann versendet je Revision
N Anzeige wann aktuellste Revision versendet
O Anzeige welche die aktuellste versendete Revision ist

P-BC Aufschlüsslung
P, U, Z, AE, AJ, AO, AT, AY Wann Rücklauf eingearbeitet
Q, V, AA, AF, AK, AP, AU, AZ Wie lang die Einarbeitung überzogen zu Zurückerwartet
R, W, AB, AG, AL, AQ, AV, BA Wann Status erteilt
S, X, AC, AH, AM, AR, AW, BB Welcher Status
T, Y, AD, AI, AN, AS, AX, BC Wie lange Statusvergabe überzogen zu Zurückerwartet
BD Anzeige aktuellsten Rücklauf eingearbeitet

BE-BL Wann zurückerwartet(hier Versendet + 14 Tage)
BM Anzeige wann aktuellste Revision zurückerwartet

BN Rücklauf überfällig seit?
Diese Formel soll ersetzt werden!
=WENN( WENN(P_=0;0;MAXA(P_; U_; Z_;AE_;AJ_;AO_;AT_;AY_))>N_-1;" ";WENN(BM_=0;" ";WENN($C$3-BM_<0;" ";$C$3-BM_)))

BO Anzeige Rücklauf letzter erteilter Status
BP Anzeige letzte Statusvergabe
BQ Anzeige der Revision zu welcher Status erteilt wurde
BR Anzeige Statusvergabe überzogen

BS-CJ Irrelevant für diesen vorliegenden Fall

CK Anzeige Rücklauf ausstehend
(Berechnung über Datum ungenau)
Soll überflüssig werden durch neue Formel BN
CL Anzeige ob jemals verschickt oder nur Platzhalter
CM Vergleich ob aktuellster Versand = aktuellster Rücklauf
Soll überflüssig werden durch neue Formel BN

BN hab ich versucht durch die Formel:
=WENN( WENN(M9<>0;BA9>M9;BL9-$C$3;WENN(L9<>0;AV9>L9;BK9-$C$3;WENN(K9<>0;AQ9>K9;BJ9-$C$3;WENN(J9<>0;AL9>J9;BI9-$C$3;WENN(I9<>0;AG9>I9;BH9-$C$3;WENN(H9<>0;AB9>H9;BG9-$C$3;WENN(G9<>0;W9>G9;BF9-$C$3;WENN(F9<>0;R9>F9;BE9-$C$3))))))));".")
Zu ersetzen aber nur Fehlermeldungen bekommen verschiedenster Art.

Die Formel soll mir im Endeffekt anzeigen wie lange die am längsten zurückliegende verschickte Revision überfällig ist, da vermehrt der Fall auftritt das z.B. Revision 00 verschickt ist, aber noch kein Rücklauf davon vorhanden ist wenn Revision 01 aufgrund Änderungen verschickt wird.

Wäre natürlich klasse wenn es damit möglich wäre mehrere Revisionsrücklaufüberzüge anzuzeigen, aber so weit kenn ich mich mit Excel momentan nicht aus.

Falls nicht würde ich an diese Stelle 2 Spalten machen in der ersten die Überziehung der ältesten Revision ohne Rücklauf, in der zweiten die Überziehung der aktuellen Revision ohne Rücklauf.
Wobei es in diesem Fall so sein sollte das dann ein Abgleich mit der ersten Spalte erfolgt und wenn die Werte gleich sind (weil sie auf derselben Revision beruhen) die 2te Spalte leer bleibt.


So ich hoffe das jetzt alles drin steht was du wissen wolltest.
Falls ich noch irgendwas Relevantes vergessen hab reinzuschreiben bitte genau danach fragen.


Mit freundlichen Grüßen Archangel

Answer 6

Hallo,

habe es mir mal angesehen und die folgende Tabelle hochgeladen

rapidshare.com/files/258712744/Kopie_von_Drawing_List_2009_07_17_Rev.037.xls.html

in den gelb hinterlegten Feldern sind meine Änderungen. Da ich nicht ganz den Sinn verschiedener Formeln erkannt habe und diese gekürzt habe, kann es sein, dass einiges bei dir dann nicht so funktioniert. Trotzdem mal testen

Viel Erfolg

Helmut

Answer 7

Hallo Helmut,

hab mir gestern/heute mal deine Änderungen zu Gemüte geführt und bin zu folgenden Ergebnissen gekommen.


Spalte E

D o E ?

Formel von dir:
=WENN(ODER(D21="E";D21="DE";D21="GA");"E";WENN(ODER(D21="HS";"D";D21="VS";"D";D21="D");"D";" "))
Funktioniert bei Eingabe von E; DE & GA tadellos, aber bei HS; VS & D kamen immer Fehlermeldungen.
Hab mir die Formel dann angeschaut und den Fehler gleich gefunden, und zwar hattest du nach dem "HS";"D" & "VS";"D" das "D" zuviel drin stehen, so das es einen Falschen Bezug erzeugte und dadurch zu dem Fehler kam.
Korrigierte Formel:
=WENN(ODER(D21="E";D21="DE";D21="GA");"E";WENN(ODER(D21="HS";D21="VS";D21="D");"D";" "))

Hab die Formel nach der Korrektur jetzt ersetzt da die etwas kürzer ist als meine vorherige *Wenn*-Formel und trotzdem dasselbe Ergebnis liefert.


Spalte O

Hab mit der Funktion: Index bisher noch nie gearbeitet, kannte den zwar in der Theorie aber in der Praxis nirgends gesehen.
Übernommen.


Spalte Q + T + BP

Die Doofheit an den Beinen gepackt…
Auf die Formeln hätte ich auch selbst kommen können^^
Übernommen.


Spalte BO + BR

INDIREKT ist zwar eine interessante Funktion, aber viel zu rechenintensiv. Hier ist diese aufgrund der Zeilenmenge schon hart an der Grenze des nutzbaren auf einem alten PC(hier im Büro stehen noch PCs der Generation <2000).
Die hier ist aber nur eine kleine Tabelle. Hab hier einige mit 1000-2500 Zeilen(Mittelmaß) und welche mit >5000 und spätestens da ist diese Tabelle selbst auf einem aktuellen (Büro-)PC kaum noch nutzbar und ruckelt nur noch beim scrollen.


Spalte BQ

Absolut Falsch.
Der Bezug den du hier erstellt hattest geht auf die Verschickten zurück, nicht auf die Rückläufe die er eigentlich anzeigen sollte.
Des Weiteren hast du hier einen weiteren Bezug völlig außer acht gelassen der in der vorherigen Formel verankert ist, und zwar das dieser Wert nur ersetzt wird wenn ein *Rücklaufdatum(Datum Status)* für die jeweilige Revision vorhanden ist.
Zum Teil werden aber Statusänderungen direkt ohne *Datum Status* eingetragen(z.B. wenn ein Teil durch ein anderes Ersetzt wird) und stehen immer noch mit der vorherigen Revision drin bis der Status durch das Datum bestätigt wird(siehe Spalte BO).
BQ ist/war dieselbe Formel wie BP + den Revisionsbezugbezug als Ausgabewert.
Ist es möglich die Indexformel der Spalte O auch an die Bedingung des Revisionsbezugs zu knüpfen?


So und jetzt schließlich und endlich zur Spalte BN:
Die Formel ist zwar besser als meine bisherige, da diese auch negative Werte anzeigt, ist aber immer noch genauso *blöd* das die abfrage nur auf dem *Datum* und nicht auf *Datum+Revision* basiert.
Brauche infolge dessen immer noch die Spalten CK + CM die eigentlich überflüssig werden sollten.

mfg Archangel

Answer 8

EDIT:

Spalte O

Hab mit der Funktion: Index bisher noch nie gearbeitet, kannte den zwar in der Theorie aber in der Praxis nirgends gesehen.
Übernommen.

Muss ich leider Revidieren, da es #NV anzeigt wenn die Felder der Zugrunde liegenden Auswertung leer sind.
Diese lassen sich leider nicht ausblenden, da diese Zeilen entweder als Platzhalter dienen welche noch nicht fertig sind, oder nur für den internen Bedarf angelegt, aber trotzdem sichtbar sein müssen damit die Nummern nicht neu belegt werden.
Das Problem ist das über diese Tabellen Auswertungen die solche Anzeigen stören.
Löschen kann ich diese auch nicht aus den betreffenden Zeilen, da ich sonst bei jedem zuwachs der Tabelle kontrollieren müsste ob die Formel vorhanden ist oder nicht.
Deswegen bleib ich vorläufig bei einer alten Formel bis ich ne Lösung dazu gefunden habe.

mfg Archangel

Answer 9

Hallo,

dafür gibt es mehrere Lösungen Hier auf Zeile 8 Spalte 0 bezogen

1. =WENN(ISTLEER(F8);"";INDEX($F$6:$M$6;;VERGLEICH(N8;F8:M8)))

2. =WENN(ISTFEHLER(INDEX($F$6:$M$6;;VERGLEICH(N8;F8:M8)));"";INDEX($F$6:$M$6;;VERGLEICH(N8;F8:M8)))

in den anderen Feldern ist es ähnlich zu lösen. Da bei meiner Tabelle das Feld A8 (Drawing Number) leer ist, sonst aber aus meiner sicht bei ausgefüllter Zeile was dinstehen müsste, könnte es auch so gehen

=WENN(ISTLEER(A8);"";INDEX($F$6:$M$6;;VERGLEICH(N8;F8:M8)))


Gruß

Helmut

Answer 10

Hallo Helmut,

die ersten beiden Lösungen für Spalte O funktionieren, die 3te würde es nicht, da es Kategorieüberschriften in der Tabelle gibt die im Feld Bezeichnung stehen, folglich aber keine Nummer in Spalte A drin stehen haben.

Fehlt jetzt leider immer noch eine vollständige Lösung für die Spalte BN.
Hab mir das ganze Wochenende jetzt den Kopf zerbrochen wie es möglich ist den Abgleich sowohl über das jeweilige Datum(8 Stk.) und die Revision(auch 8Stk.) zusammen zu bekommen, krieg aber leider immer noch nichts funktionierendes Zustande.

Über weitere Anregungen wäre ich sehr erfreut.

mfg Archangel