D
Diegori
Gast
Hallo liebe Leser,
bei der Erstellung einer Statistik in Excel komme ich bei einer Sache mit der Funktion KKLEINSTE einfach nicht weiter, daher möchte ich euch um Hilfe bitten.
Im angehängten Excel-Dokument habe ich das Problem vereinfacht dargestellt. Die richtige Statistik umfasst mehrere Jahre (ein Tabellenblatt je Jahr) und ein Tabellenblatt mit der Auswertung. Weiterhin sind natürlich eine ganze Menge mehr Daten vorhanden, als in der Beispieldatei im Anhang.
Ich möchte in der Auswertung einen zeitlichen Verlauf darstellen, bei dem zwei Bedingungen erfüllt sein müssen, in der Beispieldatei sind das Kategorie 1 und Kategorie 2.
Die folgende Formel funktioniert, um nur das kleinste Datum herauszufinden (in der Datei grün markiert):
=MIN(MIN(INDEX(Jahr_1;KKLEINSTE(WENN((Jahr_1_Kategorie_1=Auswertung_Kategorie_1)*(Jahr_1_Kategorie_2=Auswertung_Kategorie_2);ZEILE($1:$3));ZEILE(A1));1);INDEX(Jahr_1;KKLEINSTE(WENN((Jahr_1_Kategorie_2=Auswertung_Kategorie_1)*(Jahr_1_Kategorie_1=Auswertung_Kategorie_2);ZEILE($1:$3));ZEILE(A1));1));MIN(INDEX(Jahr_2;KKLEINSTE(WENN((Jahr_2_Kategorie_1=Auswertung_Kategorie_1)*(Jahr_2_Kategorie_2=Auswertung_Kategorie_2);ZEILE($1:$3));ZEILE(A1));1);INDEX(Jahr_2;KKLEINSTE(WENN((Jahr_2_Kategorie_2=Auswertung_Kategorie_1)*(Jahr_2_Kategorie_1=Auswertung_Kategorie_2);ZEILE($1:$3));ZEILE(A1));1)))
Hierbei wird das kleinste Datum je Tabellenblatt und anschließend von den gefundenen Daten das kleinste Datum gesucht. Diese Formel ist so nicht für das zweitkleinste, drittkleinste (...) Datum verwendbar.
Meine Vorstellung wäre eine Formel, die in etwa so aussieht (in der Datei orange markiert):
={KKLEINSTE({KKLEINSTE({INDEX(Jahr_1;KKLEINSTE(WENN((Jahr_1_Kategorie_1=Auswertung_Kategorie_1)*(Jahr_1_Kategorie_2=Auswertung_Kategorie_2);ZEILE($1:$7));ZEILE(A1));1);INDEX(Jahr_1;KKLEINSTE(WENN((Jahr_1_Kategorie_2=Auswertung_Kategorie_1)*(Jahr_1_Kategorie_1=Auswertung_Kategorie_2);ZEILE($1:$7));ZEILE(A1));1)};ZEILE(A1));KKLEINSTE({INDEX(Jahr_2;KKLEINSTE(WENN((Jahr_2_Kategorie_1=Auswertung_Kategorie_1)*(Jahr_2_Kategorie_2=Auswertung_Kategorie_2);ZEILE($1:$7));ZEILE(A1));1);INDEX(Jahr_2;KKLEINSTE(WENN((Jahr_2_Kategorie_2=Auswertung_Kategorie_1)*(Jahr_2_Kategorie_1=Auswertung_Kategorie_2);ZEILE($1:$7));ZEILE(A1));1)};ZEILE(A1))};ZEILE(A1))}
Die Formel wird gar nicht erst von Excel akzeptiert. Das liegt vermutlich an den geschweiften Klammern innerhalb der Matrixformel, oder irre ich mich? Wie ich darauf komme, so einen Unsinn zu versuchen? Ich dachte, wenn
=KKLEINSTE({3;5};ZEILE(A1))
funktioniert, sollte das auch verschachtelt gehen.
Die Frage ist nun, wie ich die erste Formel so verändern kann, dass ich einen Verlauf hinbekomme, also mittels runterkopieren das zweitkleinste, drittkleinste (...) Datum erhalte.
Weiß jemand Rat? Oder macht ein anderer Formelaufbau vielleicht mehr Sinn?
Ich freue mich auf eure Antworten.
Diegori
Inzwischen ist es mir gelungen, die Formel ohne KKLEINSTE-Verschachtelung zu schreiben:
{=KKLEINSTE((INDEX(Jahr_1;VERGLEICH(Auswertung_Kategorie_1&Auswertung_Kategorie_2;Jahr_1_Kategorie_1&Jahr_1_Kategorie_2;0);1);INDEX(Jahr_1;VERGLEICH(Auswertung_Kategorie_2&Auswertung_Kategorie_1;Jahr_1_Kategorie_1&Jahr_1_Kategorie_2;0);1);(INDEX(Jahr_2;VERGLEICH(Auswertung_Kategorie_1&Auswertung_Kategorie_2;Jahr_2_Kategorie_1&Jahr_2_Kategorie_2;0);1);INDEX(Jahr_2;VERGLEICH(Auswertung_Kategorie_2&Auswertung_Kategorie_1;Jahr_2_Kategorie_1&Jahr_2_Kategorie_2;0);1)));ZEILE(A1))}
Jedoch klappt es immer noch nicht. In der Formelauswertung wird das Problem deutlich. Nachdem die ganzen Bedingungen abgeklappert wurden, steht folgendes in der Formelauswertung:
KKLEINSTE(('Jahr 1'!$A$5;'Jahr 1'!$A$2;'Jahr 2'!$A$2;'Jahr 2'!$A$5);ZEILE(A1))
Und als nächster Schritt:
KKLEINSTE((#WERT!);ZEILE(A1))
Sieht jemand das Problem dabei?
Folgende Formel funktioniert doch auch (vereinfachtes Beispiel mit irgendwelchen Zahlen in A1 bis B3):
=KKLEINSTE((A1;A2;A3;B1;B2;B3);ZEILE(A1))
Bitte schaltet mir ein Licht ein..
Diegori
Die Ursache liegt wohl in der Arbeitsweise von KKLEINSTE. Es werden erst alle Ergebnisse bzw. Bereiche des einen Tabellenblattes mit dem ersten Ergebnis des zweiten Tabellenblattes ausgewertet, bevor die restlichen Ergebnisse berechnet werden.
=KKLEINSTE(('Jahr 1'!$A$5;'Jahr 1'!$A$2;'Jahr 2'!$A$2;'Jahr 2'!$A$5);ZEILE(A1))
ergibt einen #WERT!-Fehler
=KKLEINSTE(('Jahr 1'!$A$5;'Jahr 1'!$A$2;'Jahr 1'!$A$4;'Jahr 1'!$A$3);ZEILE(A1))
nicht. Erkenntnis: KKLEINSTE hilft mir wohl tabellenblattübergreifend nicht weiter. Aber wie mache ich es sonst?
Diegori
PS: Ich fühle mich wie ein Alleinunterhalter bei diesem Thema.
bei der Erstellung einer Statistik in Excel komme ich bei einer Sache mit der Funktion KKLEINSTE einfach nicht weiter, daher möchte ich euch um Hilfe bitten.
Im angehängten Excel-Dokument habe ich das Problem vereinfacht dargestellt. Die richtige Statistik umfasst mehrere Jahre (ein Tabellenblatt je Jahr) und ein Tabellenblatt mit der Auswertung. Weiterhin sind natürlich eine ganze Menge mehr Daten vorhanden, als in der Beispieldatei im Anhang.
Ich möchte in der Auswertung einen zeitlichen Verlauf darstellen, bei dem zwei Bedingungen erfüllt sein müssen, in der Beispieldatei sind das Kategorie 1 und Kategorie 2.
Die folgende Formel funktioniert, um nur das kleinste Datum herauszufinden (in der Datei grün markiert):
=MIN(MIN(INDEX(Jahr_1;KKLEINSTE(WENN((Jahr_1_Kategorie_1=Auswertung_Kategorie_1)*(Jahr_1_Kategorie_2=Auswertung_Kategorie_2);ZEILE($1:$3));ZEILE(A1));1);INDEX(Jahr_1;KKLEINSTE(WENN((Jahr_1_Kategorie_2=Auswertung_Kategorie_1)*(Jahr_1_Kategorie_1=Auswertung_Kategorie_2);ZEILE($1:$3));ZEILE(A1));1));MIN(INDEX(Jahr_2;KKLEINSTE(WENN((Jahr_2_Kategorie_1=Auswertung_Kategorie_1)*(Jahr_2_Kategorie_2=Auswertung_Kategorie_2);ZEILE($1:$3));ZEILE(A1));1);INDEX(Jahr_2;KKLEINSTE(WENN((Jahr_2_Kategorie_2=Auswertung_Kategorie_1)*(Jahr_2_Kategorie_1=Auswertung_Kategorie_2);ZEILE($1:$3));ZEILE(A1));1)))
Hierbei wird das kleinste Datum je Tabellenblatt und anschließend von den gefundenen Daten das kleinste Datum gesucht. Diese Formel ist so nicht für das zweitkleinste, drittkleinste (...) Datum verwendbar.
Meine Vorstellung wäre eine Formel, die in etwa so aussieht (in der Datei orange markiert):
={KKLEINSTE({KKLEINSTE({INDEX(Jahr_1;KKLEINSTE(WENN((Jahr_1_Kategorie_1=Auswertung_Kategorie_1)*(Jahr_1_Kategorie_2=Auswertung_Kategorie_2);ZEILE($1:$7));ZEILE(A1));1);INDEX(Jahr_1;KKLEINSTE(WENN((Jahr_1_Kategorie_2=Auswertung_Kategorie_1)*(Jahr_1_Kategorie_1=Auswertung_Kategorie_2);ZEILE($1:$7));ZEILE(A1));1)};ZEILE(A1));KKLEINSTE({INDEX(Jahr_2;KKLEINSTE(WENN((Jahr_2_Kategorie_1=Auswertung_Kategorie_1)*(Jahr_2_Kategorie_2=Auswertung_Kategorie_2);ZEILE($1:$7));ZEILE(A1));1);INDEX(Jahr_2;KKLEINSTE(WENN((Jahr_2_Kategorie_2=Auswertung_Kategorie_1)*(Jahr_2_Kategorie_1=Auswertung_Kategorie_2);ZEILE($1:$7));ZEILE(A1));1)};ZEILE(A1))};ZEILE(A1))}
Die Formel wird gar nicht erst von Excel akzeptiert. Das liegt vermutlich an den geschweiften Klammern innerhalb der Matrixformel, oder irre ich mich? Wie ich darauf komme, so einen Unsinn zu versuchen? Ich dachte, wenn
=KKLEINSTE({3;5};ZEILE(A1))
funktioniert, sollte das auch verschachtelt gehen.
Die Frage ist nun, wie ich die erste Formel so verändern kann, dass ich einen Verlauf hinbekomme, also mittels runterkopieren das zweitkleinste, drittkleinste (...) Datum erhalte.
Weiß jemand Rat? Oder macht ein anderer Formelaufbau vielleicht mehr Sinn?
Ich freue mich auf eure Antworten.
Diegori
Ergänzung ()
Inzwischen ist es mir gelungen, die Formel ohne KKLEINSTE-Verschachtelung zu schreiben:
{=KKLEINSTE((INDEX(Jahr_1;VERGLEICH(Auswertung_Kategorie_1&Auswertung_Kategorie_2;Jahr_1_Kategorie_1&Jahr_1_Kategorie_2;0);1);INDEX(Jahr_1;VERGLEICH(Auswertung_Kategorie_2&Auswertung_Kategorie_1;Jahr_1_Kategorie_1&Jahr_1_Kategorie_2;0);1);(INDEX(Jahr_2;VERGLEICH(Auswertung_Kategorie_1&Auswertung_Kategorie_2;Jahr_2_Kategorie_1&Jahr_2_Kategorie_2;0);1);INDEX(Jahr_2;VERGLEICH(Auswertung_Kategorie_2&Auswertung_Kategorie_1;Jahr_2_Kategorie_1&Jahr_2_Kategorie_2;0);1)));ZEILE(A1))}
Jedoch klappt es immer noch nicht. In der Formelauswertung wird das Problem deutlich. Nachdem die ganzen Bedingungen abgeklappert wurden, steht folgendes in der Formelauswertung:
KKLEINSTE(('Jahr 1'!$A$5;'Jahr 1'!$A$2;'Jahr 2'!$A$2;'Jahr 2'!$A$5);ZEILE(A1))
Und als nächster Schritt:
KKLEINSTE((#WERT!);ZEILE(A1))
Sieht jemand das Problem dabei?
Folgende Formel funktioniert doch auch (vereinfachtes Beispiel mit irgendwelchen Zahlen in A1 bis B3):
=KKLEINSTE((A1;A2;A3;B1;B2;B3);ZEILE(A1))
Bitte schaltet mir ein Licht ein..
Diegori
Ergänzung ()
Die Ursache liegt wohl in der Arbeitsweise von KKLEINSTE. Es werden erst alle Ergebnisse bzw. Bereiche des einen Tabellenblattes mit dem ersten Ergebnis des zweiten Tabellenblattes ausgewertet, bevor die restlichen Ergebnisse berechnet werden.
=KKLEINSTE(('Jahr 1'!$A$5;'Jahr 1'!$A$2;'Jahr 2'!$A$2;'Jahr 2'!$A$5);ZEILE(A1))
ergibt einen #WERT!-Fehler
=KKLEINSTE(('Jahr 1'!$A$5;'Jahr 1'!$A$2;'Jahr 1'!$A$4;'Jahr 1'!$A$3);ZEILE(A1))
nicht. Erkenntnis: KKLEINSTE hilft mir wohl tabellenblattübergreifend nicht weiter. Aber wie mache ich es sonst?
Diegori
PS: Ich fühle mich wie ein Alleinunterhalter bei diesem Thema.
Zuletzt bearbeitet von einem Moderator:
(zweite Formel korrigiert (Namen verwendet))