Excel 2016: KKLEINSTE verschachteln

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
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. :freak:
 
Zuletzt bearbeitet von einem Moderator: (zweite Formel korrigiert (Namen verwendet))
…und ich habe den ganzen Tag was anderes getan…

Benutze Hilfsspalten und ein weiteres Arbeits-Blatt auf dem auf Hilfswerte referenziert wird über die dann KKLEINSTE geworfen werden kann.
Oder statt ganzer Spalten nur bestimmte Hilfszellen je Haupt-Blatt die schon Vorberechnungen weitergeben.

XL ist keine Präsentation die schön aussehen soll sondern eine Datenwüste die quasi ein Endergebnis abliefert.

CN8
 
Hallo Diegori,

um ehrlich zu sein, würde ich hier auf VBA zurückgreifen.

Soll das ganze nur mit Formeln gelöst werden, kann ich Dir nur eine Lösung über ein Hilfs-Arbeitsblatt bieten:

*EDIT*: sorry formel in M2 ff. war falsch
 

Anhänge

  • Kopie von kkleinste verschachteln.xlsx
    12,1 KB · Aufrufe: 446
  • excel.png
    excel.png
    34,2 KB · Aufrufe: 480
Zuletzt bearbeitet:
Danke für eure Antworten.

@cumulonimbus8: Könntest du deinen Vorschlag bitte konkretisieren? Was würdest du denn in eine Hilfsspalte auslagern?

@Majestro1337: Danke für deine Mühe. Deine Formeln funktionieren gut, decken allerdings nur die Hälfte der gewünschten Bedingungen ab. Habe diese Bedingungen nicht in Worte gefasst, daher hole ich es hier nach:

Es sollen alle Datumsangaben als Verlauf dargestellt werden, bei denen die Paarung der gesuchten Kategorien vorkommt. Also egal ob GH, AB oder AB, GH.

Wie müsste ich deine Formel dafür verändern?

Diegori
 
Ich kenne die genaue Aufgabe nicht - vielleicht würde ich auf jedem (Daten-)Blatt KKLEINSTE in einer Hilfsspalte oder eher noch Hilfszelle ausführen. Diese Hilfszellen kann ich dann auf einem Hilfsblatt (oder auf einem bestimmten Datenblatt an ruhiger Stelle) sammeln und darüber die finale Berechnung laufen lassen.

Jedenfalls würde ich solche Monsterformeln auf verschiedene Weise kürzen, teilen, oder eben durch Inhalte von besagten Hilfszellen übersichtlicher gestalten.

CN8
 
Hallo!

im Prinzip multiplizierst du in der Datumsauflistung zusätzlich noch mit der 2ten bedingung und die anzahl hits mit 2 kriterien erhälst du mit zählenwenns statt zählenwenn.

Datei hängt an.

BG
 

Anhänge

  • Kopie von kkleinste verschachteln.xlsx
    12,1 KB · Aufrufe: 454
Danke euch beiden für eure Mühe.

Die Lösung ist nun gefunden. Letzlich habe ich es irgendwie so gemacht, wie cumulonimbus8 es geschrieben hat (soweit ich es verstanden habe). Pro Jahr habe ich die kleinsten Datumsangaben in einer Hilfsspalte berechnet und dann von diesen Berechnungen nochmal in der Auswertung das kleinste Datum.

Ich glaube, dass ich die ganze Sache zu kompliziert angegangen bin. Liegt wohl an den vielen verschachtelten Formeln, die sonst so in der Statistik zu finden sind (und alle funktionieren).

Schönen Abend allen Lesern.
 
Von tatsächlichen Beschränkungen abgesehen kann eine Formel durchaus komplex werden - nur wird sie dadurch unübersichtlich, unhandlich.

Nehmen wir mal was Heißgeliebtes: a²+2ab+b². In einer einzigen Superformel müsste ich alles was a ist komplett zweimal einflechten. Nutze ich Hilfszellen muss ich nur (zweimal) auf die Hilfszelle zurückgreifen.

In dem Sinn eine Vorbereitung die primitiv scheint (weil - man kann scheinbar keine komplexen Formeln bauen) aber sehr effizient ist. Ich nenne das vergleichsweise die Maximierung der Faulheit.
Auch beim Makroschreiben erlebe ich diesen Zwiespalt zwischen Flexibilität und Kompaktheit, Lesbarkeit. Hilfszellen können einmal Gold sein, dann nur umständlich. Immer ein Kommt-Drauf-An.

CN8
 
Zurück
Oben