Thema: Problem mit Excel (nur bestimmte Felder zählen)

[Timberwere]

Ich habe ein Problem mit Excel (bzw. eigentlich Open Office, aber das ist ja so gut wie dasselbe in dem Fall^^), das ich nicht knacken kann.

Und zwar habe ich eine Datei mit 3 Spalten: Datum, Name eines Testfalles und Status ("passed", "failed" oder "pending").
Die Testfälle identischen Namens kommen zu unterschiedlichen Daten vor und können alle jeden Status haben.
Ich möchte jetzt von jedem Testfall nur den vom neuesten Datum zählen und dann am Ende unten eine Gesamtanzahl für jeden Status ausgegeben bekommen.

Dazu reichen meine Formelkenntnisse leider nicht aus... ich bekomme ja gerade mal eine einfache "Wenn-Bedingung" hin, wenn es hochkommt, aber ich habe z.B. keine Ahnung, wie ich das mit dem neuesten Datum einbauen soll. (Übrigens muss die Tabelle auch nicht immer unbedingt nach Datum oder Namen sortiert sein, sondern alles kann querbeet und kreuz und quer erscheinen).

Meine Excel-Datei hänge ich mal gezippt an, aber hier ist das, was ich meine, auch im Posting.

Datum     Testfall       Status
23.06.09   Andreas        failed
01.08.09   Andreas   passed
01.10.09   Andreas   failed
05.11.09   Andreas   failed   (dies soll gezählt werden)
05.07.09   Gabi   pending
01.10.09   Gabi   passed
22.12.09   Gabi   failed
01.01.10   Gabi   passed   (dies soll gezählt werden)
03.07.09   Julia   passed
01.01.10   Julia   failed    (dies soll gezählt werden)
10.04.09   Paul   pending
18.06.09   Paul   failed
23.10.09   Paul   passed
20.11.09   Paul   passed
20.12.09   Paul   failed   (dies soll gezählt werden)
05.08.09   Petra   failed
20.11.09   Petra   pending  (dies soll gezählt werden)
30.11.09   Sabine   failed  (dies soll gezählt werden)
05.07.09   Timon   pending
30.08.09   Timon   pending
01.10.09   Timon   passed   (dies soll gezählt werden)

Das Endergebnis, das ich möchte (und das Excel mir berechnen soll), müsste das hier sein:
passed:   2
failed:   4
pending:   1

Kann mir jemand helfen? Ich wäre euch sehr dankbar!

[gelöscht durch Administrator]

[Karl Lauer]

Ich hab mir das nur mal kurz durch den Kopf gehen lassen. Ein so doller Excelkünstler bin ich ja nicht, aber ich denke man könnte es mit ein paar "Nebenrechnungen" hin bekommen.

Wäre das in Ordnung? Oder sollte es mit einer einzigen Formel (jeweils) funktionieren, die dann eben die Anzahl auswirft?

Da könnte ich dann nicht helfen. Müsste dann wohl eine Matrix/Array Funktion sein

[Timberwere]

Danke schon mal für's drüber Nachdenken, Karl!

Es darf gerne auch eine etwas kompliziertere Rechnung sein, Hauptsache, unten wird das richtige Ergebnis ausgespuckt.

[Karl Lauer]

Ich habs mal angehängt wie ich es machen würde.

Dabei ermittle ich in einer Nebenrechnung für jeden "Testfall" das höchste Datum und den dazugehörigen Status.

Danach zähle ich nur noch bei der Nebenrechnung durch wie oft jeder Status vor kommt.

[gelöscht durch Administrator]

[Timberwere]

1000 Dank schonmal!

*angucken geh*

Ich melde mich wieder, ob ich es verstanden habe...

[Timberwere]

So, ich habs mir angesehen, vielen Dank!

Dass das richtige Ergebnis herauskommt, ist schon mal sehr genial. Die DBMAX- und DBAUSZUG-Funktion kannte ich noch gar nicht. Interessant!

Allerdings... ein kleines Problem habe ich da noch. Und zwar ist diese Datei ja nur eine kleine Beispieldatei, die verdeutlichen sollte, was ich will. Die echte Datei ist viel größer. Da können Dutzende, vielleicht über die Zeit Hunderte, von Testfällen reinkommen. Und diese Testfälle können teils recht lange und komplizierte Namen haben. Und es können ziemlich häufig neue dazukommen.
Das heißt, es ist nicht sehr praktikabel, jeden einzelnen Testfall von Hand in die Nebenrechnung einzutragen, wie du das hier getan hast.
Gibt es nicht vielleicht eine Funktion, mit der man das Ganze automatisieren könnte? Dass Excel von selbst jeden Namen aus der "Testfall"-Spalte genau einmal zieht und daraus dann den Rest berechnet?

[der.hobbit]

Zählen abhängig von mehreren Spalten geht in Excel 2007 über eine Formel (ZÄHLENWENNS), aber nicht in alten Excels. Aber man kann mit SUMMENPRODUKTEN arbeiten.

=SUMMENPRODUKT((A2:A22=MAX(A2:A22))*(C2:C22=E3)) zählt die insgesamt aktuellsten Ergebnisse; wie man das persönlich aktuellste Ergebnis herausfindet, schaue ich mir evtl. morgen an (falls mir niemand zuvor kommt). Man müsste den MAX Teil entsprechend anpassen. Gibt's auch eine Namensliste mit allen Namen irgendwo? Das würde helfen ...

[Timberwere]

Auch dir schon mal vielen Dank, Hobbit!

Ob ich an die komplette Datei herankomme - bzw. wie vertraulich die Daten und/oder Testfallnamen sind - muss ich nächste Woche sehen. Ein Kollege hatte mir das Problem geschildert, aber ich die eigentliche Datei bisher noch nicht gesehen.
Falls sie postbar ist, hänge ich sie am nächsten Dienstag hier an, vorher sehe ich den Kollegen nicht.

[Timberwere]

Sooooo. Jetzt habe ich sowohl die Datei in den Händen als auch die Erlaubnis meines Bekannten, sie hier anzuhängen, weil sie keine vertraulichen Informationen enthält.

Wichtig sind Spalte A (das Datum), Spalte B (der Name des Testfalles) und Spalte G (der Status). Farben spielen keinerlei Rolle.
Wie weiter oben im Eingangspost schon erwähnt, soll, wenn ein Testfall mehr als einmal vorkommt, nur der Status dieses Testfalles am neuesten Datum gezählt werden, und wenn es ihn nur einmal gibt, eben der Status an diesem einen Datum.

Beispiele:

R-TRANS-SDD-CORE-002-A existiert 2x unterschiedlich in der Datei. Es soll nur das "OK" vom 15.09.2009 gezählt werden.
R-TR-SDD-B2B-171-A gibt es 2x identisch in der Datei. Es soll nur das neuere "NOK" vom 22.09.2009 gezählt werden.
R-TR-SDD-CORE-006 ist 3x unterschiedlich vorhanden. Es soll nur das neueste "OK" vom 25.09.2009 gezählt werden.
SCT-OUT-DO-GEN-034 gibt es nur 1x in der Liste. Genau dieses eine "pending" vom 02.09.2009 soll gezählt werden.

----

Ich hoffe, die "echte" Datei hilft euch ein wenig weiter.
Und ich würde mich sehr freuen (und mein Bekannter bestimmt noch viel mehr), wenn ihr eine Lösung finden könntet.

[gelöscht durch Administrator]

[der.hobbit]

Habe hier kein altes Excel, und außerdem berechnet dein Sheet überhaupt keine Formeln (so was!) aber so wie ich das sehe, sind alle Daten in aufsteigender Reihenfolge, oder? D.h. dann auch, dass jeweils die letzte Zeile in der der jeweilige Testfall steht der aktuellste ist? Denn dann könnte man das vielleicht darüber lösen, dass man den jeweils letzten Testfall (versteckt) markiert:
Füge eine Spalte hinter Test case no. ein.
In die erste Zelle kommt folgende Formel: =WENN(ZÄHLENWENN(4B44:B$4000;B44)=1;"NEUSTER";"")
diese Formel lässt du nach unten hin ausfüllen - jetzt sollte hinter dem jeweils aktuellsten Eintrag 'NEUSTER' stehen, hinter allen anderen nichts.
Danach fügst du deine unterschiedlichen Stati ein, wie gesagt exakt gleich geschrieben wie in der Zelle selbst, und setzt hinter jeden folgende Formel:
=SUMMENPRODUKT(($C$44:$C$4000="NEUSTER")*($G$4:$G$44=[REFERENZ AUF LABEL]))
Dabei ist [REFERENZ AUF LABEL] die Zelle, in der du den Status eingetragen hast. Also, wenn du deinen Zähler in den Feldern A3 bis B7 willst, dann schreibst du in A3 OK und in B3 =SUMMENPRODUKT(($C$44:$C$4000="NEUSTER")*($G$4:$G$44=A3))

Das sollte theoretisch funktionieren - ich habe es jetzt nicht getestet weil mir dein Sheet wie gesagt keine Formeln auswertet. Die hässliche zusätzliche Spalte verschwindet übrigens schnell nach einem Rechts-Klick auf den Spalten-Header (also das C) und einem Klick auf "hide" (bzw. "verstecken"?)

Das Ganze funktioniert für Werte bis in Zeile 4000, falls ihr es für mehr braucht, könnt ihr die 4000 in den entsprechenden Formeln anpassen (dann auch wieder die Formeln nach unten ausfüllen).

[Timberwere]

Hmmm... das Problem dabei, die neueste Version jedes Testfalles händisch als "NEUESTER" zu markieren, sehe ich darin, dass die Tabelle nach Datum sortiert ist, nicht nach Namen. D.h. ich muss wieder mühevoll nach allen Namen einzeln filtern, um auch ja wirklich keinen zu übersehen, der den Vermerk "NEUESTER" tragen müsste und es nachher eventuell nicht tut. Denn wenn ich einen übersehe, würde das ja wieder das Ergebnis verfälschen. Und dieser ganzen Such- und Änderungsaufwand würde jedesmal anfallen, wenn neue Testergebnisse mit neuem Datum dazukommen, und das kann völlig unregelmäßig und häufig passieren, wenn ich den Kollegen richtig verstanden habe.

Ich habe natürlich als erstes versucht, die Tabelle nach Namen statt nach Datum zu sortieren, aber noch nicht einmal das ging, weil ich dann die Fehlermeldung bekam, dass alle verbundenen Zellen dieselbe Größe haben müssten.  *schnief*

Von daher wäre eine "automatisierte" Lösung schon deutlich besser. Wenn es denn eine gibt. Was ich immer noch hoffe...

Es ist halt leider nicht meine Datei. Und auch der Bekannte bekommt sie, glaube ich, auch aus unterschiedlichen Quellen zusammengestoppelt. Kann man das "Formeln berechnen lassen" nicht irgendwo einstellen?

[der.hobbit]

Die Markierung erfolgt automatisch auf der Basis der ersten Formel - das einzige was du manuell machst, ist diese Formel einmal eintragen, dann ausreichend viele Felder markieren und Unten ausfüllen wählen - schon steht überall da letzter, wo letzter hingehört

Falls das gesamte Excel jedes mal neu berechnet wird und er nur eine geschlossene Formel hineinkopieren möchte, dann könnte er sich eine Copy'n'Paste Vorlage erstellen, d.h. die NEUSTER Formel nicht in die C-Spalte sondern als letzte Spalte, die Berechnungen in den Zeilen darüber und wenn das neue Dokument kommt kopiert er diese Spalten von der Vorlage in das neue Sheet - und sieht die Ergebnisse sofort. Wäre ungefähr gleich viel Aufwand wie das in diesem Fall notwendige hinzufügen der geschlossenen Formel.

[Timberwere]

Okay, dann versuche ich das jetzt mal.

Das mit dem Formel-Herunterziehen kenne ich natürlich, hatte das nur irgendwie so verstanden, dass man als Vorarbeit selbst bestimmen muss, welches Feld ein "Neuester" bekommt.

Ich werde jetzt einfach mal antesten, was du mir da rätst. Mein Kollege hat nämlich noch weniger Ahnung von Excel als ich; ich kann mich wenigstens halbwegs einfuchsen, wenn man mir etwas vorkaut. Wenn es klappt, hüpfe ich im Karree und gebe es meinem Bekannten so weiter. Und wenn nicht, schreie ich nochmal um Hilfe.

So oder so melde ich mich nochmal. Und sage jetzt schon einmal ganz herzlichen Dank!!

[Timberwere]

15 Minuten später.

Okay, die Spalte mit dem "Neuester" war einfach.
Was ich jetzt noch nicht hinbekomme, ist das Berechnen mit dem Summenprodukt. Da bin ich irgendwie noch zu doof dazu.

Ich stelle mir das so vor, dass es irgendwo (oben, unten, am Rand, in einem zweiten Blatt, ganz egal), eben 3 Felder gibt mit den ausgerechneten Zahlen für die 3 Stati. Das heißt ja dann, dass ich in der Zelle, wo diese Gesamtzahl ausgespuckt werden soll, die Formel reinschreibe.
In meinem Beispiel beginnt die Tabelle ja in Zeile 44, und der Status steht in Spalte G.
Sehe ich es richtig, dass meine Formel also entsprechend =SUMMENPRODUKT(($C$44:$C$4000="NEUSTER")*($G$4:$G$44=G44)) lauten müsste? Aber wenn ja, wo schreibe ich sie genau rein, und was kommt in das Feld, wo die Gesamtzahl letztendlich ausgegeben werden soll? *hat irgendwie noch ein Brett vor dem Kopf*

[der.hobbit]

Im Prinzip willst du zwei Bedingungen prüfen:
1) es ist der jeweils neuste Eintrag
Das wird durch

Code: [Auswählen]

($C$44:$C$4000="NEUSTER")
ermittelt - ein simpler check, ob in der NEUSTER Spalte auch wirklich NEUSTER steht.


2) es ist der passende Status
Dafür ist der zweite Teil zuständig:

Code: [Auswählen]

($G$4:$G$44=[REFERENZ AUF LABEL])
Damit vergleichst du den Status mit - ja was?
Du legst dir jeweils Felder an, in denen der Status steht un in deren benachbarten Felder dann der Wert kommt. Diese Felder kannst du überall auf den Sheet anlegen - z.B. über den Statuszeilen oder rechts neben ihnen (ich empfehle ersteres).
Also z.B.:

+....+...+
| OK | 7 |
+....+...+

Dafür gibst du im einen Feld OK genau so ein, wie es auch in den Statuszellen auftaucht und im zweiten Feld (bei der 7) die zweite Formel. In dieser zweiten Formel ersetzt du das letzte Feld (dort wo [REFERENZ AUF LABEL] bzw. A3 steht) durch die Position des Feldes links - also das OK.

Das Ergebnis ist dann etwas wie:


+.....+....+
|  OK |  7 |
+.....+....+
| NOK | 12 |
+.....+....+


Die Statusfelder musst du einzeln übertragen, aber die sollten ziemlich statisch sein, oder?

[der.hobbit]

Bin jetzt offline, schaue aber heute Abend noch einmal rein - viel Erfolg!

[Timberwere]

danke dir, ich gehe jetzt auch erstmal off - und beiße mir heute abend oder morgen die Zähne weiter daran aus.
Denn irgendwie bekomme ich das noch nicht gebacken *brumm*

Vielen Dank Dir schon mal!!

[der.hobbit]

Me stupid - einige der Referenzen waren noch falsch. Außerdem sind die Stati nicht einheitlich geschrieben (z.B. manchmal Ok, manchmal ok manchmal OK). Darum muss da noch ein GROSS rein.

So what. Übrigens war der automatische Filter schuld, dass meine Formeln nicht berechnet wurden, den habe ich jetzt mal ausgeschaltet und siehe da - alles wunderbar (denke ich).

Siehe Attachment.

[gelöscht durch Administrator]

[Asdrubael]

Sieht echt gut aus. Gerade bei der Datenbasis.
Gerade solche Felder mit OK, Not OK usw sollte man vielleicht mal mit einer Data Validation versehen, damit immer das gleiche eingetragen wird

[der.hobbit]

Oder auch als Auswahlfeld definieren, dann kann man sogar die Summierungslabels als Auswahl nehmen (also die oberen drei).

[Asdrubael]

Oder auch als Auswahlfeld definieren, dann kann man sogar die Summierungslabels als Auswahl nehmen (also die oberen drei).

ich definiere meine Data Validation eigentlich immer mit Auswahlfeld. Hier könnte man dann auch noch die Farbgebung mit einem Conditional Formatting machen

Wobei hier auch bei den Datümern, man mal checken könnte, ob wirklich immer ein Datum eingetragen wurde. Scheinen ab und an Text-strings zu sein und die kann man leider nicht ohne weiteres vergleichen. Die Lösung vom Hobbit geht ja "nur" in einer aufsteigend sortierten Liste.

[Timberwere]

Sooo, nach einem sehr stressigen und langen Wochenende komme ich jetzt endlich dazu, hier wieder reinzuschauen - und zu sagen: Danke, danke, danke!!!! 

Hach, genial. Genau das hatte ich mir erhofft, war aber einfach zu blöd dazu. Wenn wir uns mal bei einem GROSSEN sehen sollen, geb' ich dir blineder einen aus, Hobbit!   

Eine Frage noch: wo genau hast Du jetzt was an der Datei verändert bzw. eingetragen?
Klar, Spalte C hast du ergänzt und oben die Ergebnisfelder eingetragen, aber du meintest doch auch noch irgendwas von wegen eine weitere Formel müsse in die Nähe der Statusfelder. Die habe ich jetzt nicht gefunden, obwohl ich versucht habe alles einzublenden.

Ich würde a) gern versuchen, nachzuvollziehen, was du genau gemacht hast, und b) muss mein Kollege das jetzt ja vermutlich in einige andere Dateien übertragen, und da wäre es besser, wenn er keine Formel auslässt...

Und nochmal: vielen, vielen lieben Dank!

[der.hobbit]

genau die Felder, die du erwähnt hast: Die Formel neben den  Statusfeldern sind die Zahlen, die Statusfelder selbst sind nur die Header.
Änderungen sind also die C-Spalte und die 3x2 Felder oben, sonst nichts.

Hmmm, dann muss ich wohl doch mal zu einem Großen kommen