Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Index mit mehreren Bedingungen Erklärung

Index mit mehreren Bedingungen Erklärung
09.11.2016 21:55:17
Anton
Hallo Zusammen,
Werner hat mir vor einiger Zeit eine Formel entwickelt, welche ihre Dienste super erfüllt. Ich würde nun gerne verstehen wie die Formel funktionert. Für mich ist es leider etwas komplex hier durchzublicken. Habe mich zwar schon mit AGGREGAT beschäftigt aber das reicht anscheinend nicht.
Vielleicht kann mir ja der ein oder andere oder Werner :) ein paar Tipps dazu geben.
Hier der Link zum Post im Archiv: https://www.herber.de/forum/archiv/1512to1516/1515072_Index_mehrere_Bedingungen.html
Hab nur mal den für mich interessanten Teil ab AGGREGAT in den Beitrag kopiert:
=AGGREGAT(15;6;ZEILE(AD$1:AD$5000)/(AGGREGAT(14;6; (AF$1:AF$5000+ZEILE(AC$1:AC$5000)%%) /(AC$1:AC$5000=AH$20)/FINDEN(AH$21;AE$1:AE$5000);ZEILE(AC2))=AF$1:AF$5000+ZEILE(AC$1:AC$5000) %%) /(AC$1:AC$5000=AH$20)/FINDEN(AH$21;AE$1:AE$5000)
Was ist der Hintergrund das hier AF$1:AF$5000+ZEILE(AC$1:AC$5000)%% addiert wird und %% dahinter steht?
Das alles zu erklären sprengt wahrscheinlich den Rahmen. Ich bin trotzdem für jede Hilfe dankbar!
Schöne Grüße,
Anton

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
ZEILE(AC$1:AC$5000)%% bewirkt eine 2malige ...
10.11.2016 00:41:30
Luc:-?
…Division der ZeilenNrn von 1 bis 5000 durch 100, Anton,
also wird daraus 0,0001 bis 0,5 das zu den Werten aus den Zeilen der Spalte hinzuaddiert wird.
Um die Fml vollends zu verstehen, solltest du TeilFmln aus ihr isolieren und separat in Zellen eintragen und berechnen, wobei die MatrixFmlForm erforderlich ist, falls die TeilFml ein ganzes Datenfeld zurückgibt.
Gruß, Luc :-?
Besser informiert mit …
AW: ZEILE(AC$1:AC$5000)%% bewirkt eine 2malige ...
10.11.2016 09:11:18
Anton
Guten Morgen Luc,
dank Dir, jetzt wird mir schon einiges klarer :)
VG Anton
Anzeige
AW: ergänzend noch ...
10.11.2016 09:46:33
...
Hallo Anton,
.... Du musst Die Formelteile nicht unbedingt in einer separaten Zelle auswerten lassen.
Du kannst den jeweiligen Formelteil im Eingabefeld markieren und danach die [F9] Taste betätigen. Dann erhältst Du die (Teilformel-)Ergebnismatrix im Eingabefeld angezeigt. Mit Undo kannst Du diese Anzeige auch wieder aufheben und Dich dem nächsten Formelteil widmen.
Excel selbst bietet unter dem Menüpunkt Formeln die Funktionalität: Formelauswertung an und da Einzelschritt und Prozedurschnitt. Auch dies kann hilfreich sein. Wo ich persönlich die vorgenannte "Hand"Methode bevorzuge.
Bei derartigen Untersuchungen solltest Du allerdings zuvor den/die Auswertungsbereich/e auf überschaubare Größen (bereiche) abändern.
Der Kernpunkt (m)einer AGGREGAT-Formeln die ich als Matrixfunktion(alität)sformeln bezeichne, weil sie auf der Matrixversion der Funktion basiert ist das Erzeugen einer Matrix aus Einsen und Fehlerwerten (Division durch 0), die infolge des 2. Argumentes (die 6) der AGGREGAT()-Formel ignoriert werden und genau dadurch die wesentlichen Daten für das Endergebnis durch/mit AGGREGAT() liefern.
Gruß Werner
.. , - ...
Anzeige
AW: ergänzend noch ...
10.11.2016 10:03:25
Anton
Guten Morgen Werner,
danke für Deine Tipps!
VG Anton
AW: Index mit mehreren Bedingungen Erklärung
10.11.2016 10:23:54
Daniel
Hi
was das %% bewirkt, wurde dir ja inzwischen erklärt.
Warum man das macht (dh diesen kleinen Wert hinzuaddieren) könnte folgenden Hintergrund haben (da du nur eine Teilformel gezeigt hast, kann ich nicht beurteilen, ob das bei dir zutrifft)
die Werte, in der Spalte AC sind nicht eindeutig und können sich wiederholen.
trotzdem möchtest du als Ergebnis für dein KKleinste/KGröste/Aggregat(14/15) für jede Zeile ein eindeutiges Ergebnis haben, um dieses dann in er weitern Verwendung (z.B. im SVerweis) eine eindeutigen Zeile zuordnen zu können.
Diese Eindeutigkeit kann man dadurch erreichen, dass man jedem Wert einen sehr kleinen, aber individuellen Wert hinzuaddiert.
Diese Addition macht dann den Wert eindeutigt (daher verwendet man die Zeilennummer zur berechunung dieses Wertes).
Gleichzeitig sollte der hinzuaddierte Wert klein genug sein, damit er das Ergebnis nicht verfälscht.
Gruß Daniel
Anzeige
AW: Index mit mehreren Bedingungen Erklärung
10.11.2016 10:29:14
Anton
Hi Daniel,
vielen Dank für die Erklärung. Diese Frage hätte sich bei mir als nächstes gestellt.
VG Anton
AW: Index mit mehreren Bedingungen Erklärung
10.11.2016 14:46:59
Anton
Hallo Zusammen,
jetzt muss ich noch mal nerven. Hab nun selbst versucht die Top 10 Zeilen mit Aggregat rauszufinden:
https://www.herber.de/bbs/user/109321.xlsx
Allerdings werden mir nun die falschen Zeilen ausgegeben. Der erstgrößte Wert liegt eindeutig in Zeile 7, bekomme aber Zeile 43 raus.
Entschuldigung fürs erneute Nerven und danke für eure Hilfe!
Schöne Grüße,
Anton
AW: ZeilenNrn für bedingte Wertgrößen ermitteln...
10.11.2016 16:09:23
...
Hallo Anton,
... ist es anderes, als Du es mit Deiner hier aufgezeigten Formel versucht hast. Deren Ergebnis ist formal richtig, nur willst Du eben etwas anderes ermitteln.
Um Dein angestrebtes Ergebnis möglicherweise etwas nachvollziehbarer zu gestalten, nehme ich nachfolgend diesmal eine Hilfsspaltenlösung vor. In Spalte J werden die 10 größten Werte aus Spalte D für die vorgegebenen Bedingungen ermittelt und dann in Spalte K die dazugehörigen Zeilennummern.
In J2 folgende Formel:

=AGGREGAT(14;6;D$2:D$44/(A$2:A$44=H$2)/(FINDEN(H$3;C$2:$C$44));ZEILE(A1))
und diese neun Zellen nach unten kopieren.
Und in K2 ermittelst Du die dazugehörigen Zeilenummern so:
=AGGREGAT(15;6;ZEILE(D$2:D$49)/(D$2:D$49=J2)/(A$2:A$44=H$2)/(FINDEN(H$3;C$2:$C$44));ZÄHLENWENN(J$2:J2;J2)) und diese genauso weit nach unten kopieren.
Wenn man diese beiden Formeln in eine vereinigen will, kann man ZÄHLENWENN() nicht mehr einsetzen und kommt deshalb auf so eine Formel wie ursprünglich von mir aufgestellt.
Gruß Werner
.. , - ...
Anzeige
AW: ZeilenNrn für bedingte Wertgrößen ermitteln...
10.11.2016 16:43:12
Anton
Servus Werner,
danke für die Mühe. Habs nun endlich verstanden.
VG Anton

333 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige