Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 17:41:19
Link erstellen mit Formel oder VBA?
23.04.2024 17:00:22
Summe und Ausgabe
23.04.2024 16:27:34
Excel Starthinweis - Arbeitsmappenleistung überprüfen ...
23.04.2024 16:15:16
Mittels VBA vordefinierte Kommentare in Zellen einfügen?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
572to576
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
572to576
572to576
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

sos verschachtelte formel

sos verschachtelte formel
24.02.2005 08:31:40
gunther
hallo,
ich versuche aus einer gefilterten liste den mittelwert etc. zu berechnen, wobei die größten und kleinsten werte rausgefiltert werden sollen. die liste liegt im original nicht! sortiert vor.
wer kann helfen und findet meinen denkfehler? siehe anhang:
https://www.herber.de/bbs/user/18634.xls
vielen dank
gunther

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Ich hab deine Anfrage in unserem...
Boris
Hi Gunther,
...Posteingang gelesen.
Grundsätzlich hast du einen Denkfehler in der Teilergebnis-Anwendung in Bezug auf die sichtbaren Zellen.
Allerdings wäre es hilfreich, wenn du das gewünschte Ergebnis mal händisch dazuschreiben würdest - am Besten in 2 unterschiedlichen Varianten.
Grüße Boris
hier das gewünschte Ergebnis
gunther
hallo boris,
ich bin froh über deine rückmeldung. das ergebnis soll in der beispieltabelle so aussehen:
Kappung = 0:---anzahl-----summe
ohne filter:------15---------120 % wie in spalte c
filter auf 5:------12----------99 % wie in spalte d
Kappung = 15%:
entspricht 2 "abgeschnittene" werte oben und unten (max und min)
--------------------anzahl----------summe
ohne filter:------(15-4)=11----------88 %
filter auf 5:------(12-4)=_8----------68 %
ich hoffe du verstehst wie ich das meine.
bis bald
gunther
Anzeige
Summe mit Bedingungen in gefilterter Liste
Boris
Hi Gunther,
du musst einfach für jede Abfrage eine mit Teilergerbnis(3...) ermittelte neue Matrix herstellen.
Um zum Beispiel auf die 68% zu kommen (bei Filter 5), sieht die Formel so aus (ich hab einfach mal mit KGRÖSSTE und KKLEINSTE die 2 oberen / unteren Werte rausgelassen):
{=SUMME(WENN(TEILERGEBNIS(3;INDIREKT("A"&ZEILE(2:16)));(C2:C16>KKLEINSTE(WENN(TEILERGEBNIS(3;INDIREKT("A"&ZEILE(2:16)));C2:C16);2))*(C2:C16Das Ganze wird übersichtlicher, wenn du für den Teil TEILERGEBNIS(3;INDIREKT("A"&ZEILE(2:16))) einen Namen definierst:
Name: x
Bezieht sich auf:
=TEILERGEBNIS(3;INDIREKT("A"&ZEILE(Tabelle1!$2:$16)))
Achtung: $2:$16 muss dann absolut sein!
Dann verkürzt sich die Formel auf:
{=SUMME(WENN(x;(C2:C16>KKLEINSTE(WENN(x;C2:C16);2))*(C2:C16Also: Für jede Abfrage die neue gefilterte Matrix mit eigener WENN-Bedingung vorschalten!
Alles klar?
Grüße Boris
Anzeige
Uups - Formeln abgeschnitten...
Boris
Hier die richtigen Formeln:
{=SUMME(WENN(TEILERGEBNIS(3;INDIREKT("A"&ZEILE(2:16)));(C2:C16&gtKKLEINSTE(WENN(TEILERGEBNIS(3;INDIREKT("A"&ZEILE(2:16)));C2:C16);2))*(C2:C16&ltKGRÖSSTE(WENN(TEILERGEBNIS(3;INDIREKT("A"&ZEILE(2:16)));C2:C16);2))*C2:C16))}
und mit Namen:
{=SUMME(WENN(x;(C2:C16&gtKKLEINSTE(WENN(x;C2:C16);2))*(C2:C16&ltKGRÖSSTE(WENN(x;C2:C16);2))*C2:C16))}
Grüße Boris
AW: Uups - Formeln abgeschnitten...
gunther
hallo Boris,
das ist ja ein ganz genialer tip.
auf die idee mit dem namen wäre ich nie gekommen.
ich hab auch gleich den zellbezug als namen (erg) verwendet, damit ist es dann nicht mehr unbedingt absolut oder besser gesagt flexibler anzupassen.
warum hast du bei der funktion für teilergenis die 3 gewählt? es werden auch texte etc. mitgezählt. ich hatte die idee mit der 2 bzw. summe, damit die formel robust gegen versehntliche texte (z.b. / für leerfelder) ist.?
jetzt hab ich die formel ausprobiert.
bei summe und max klappt alles super.aber bei min, mittelwert und anzahl gehts nicht? was hab ich jetzt evt. noch falsch? es macht einfach noch nicht klick bei mir.
zur sicherheit schicke ich nochmal meine tabelle mit:
https://www.herber.de/bbs/user/18670.xls
vielen dank
gunther
Anzeige
Damit es auch "Klick" macht...
Boris
Hi Gunther,
warum TEILERGEBNIS(3...):
Wie du richtig anmerkst, entspricht der Paramter 3 der Funktion ANZAHL2.
Und genau so muss es auch sein:
Mit
=TEILERGEBNIS(3;INDIREKT("A"&ZEILE($2:$16)))
wird Zeile für Zeile (2 bis 16) in Spalte A (nach der wird ja gefiltert!) ermittelt, ob der Eintrag überhaupt sichtbar ist - und das ist die einzige Aufgabe, die die Funtion TEILERGEBNIS in diesem Zusammenhang übernimmt - nix anderes!. Alle anderen Teilergebnis-Parameter sind dafür unbrauchbar.
Und dadurch entsteht eine neue Matrix, die aus WAHR und FALSCH besteht - und diese aus WAHR und FALSCH bestehende Matrix musst du für jede Funktion einfach vorschalten, da FALSCH in allen von dir gewünschten Funktionen (MIN / MAX / KGRÖSSTE / KKLEINSTE / MITTELWERT etc...) schlichtweg ignoriert wird - und das soll ja genau so sein!
Also: Die Namensdefinition muss unbedingt mit der Funktion TEILERGEBNIS(3... erfolgen.
Hat es jetzt "klick gemacht"? Falls nicht: Keine falsche Scham - einfach nochmal fragen!
Grüße Boris
Anzeige
Damit es auch bei mir "Klick" macht...
gunther
hallo boris,
den ersten teil hab ich gerafft - aber mit der namendefinition hab ich jetzt keinen blassen schimmer, worauf du hinaus willst. meinst du das jetzt für den namen "erg" in meinem beispiel ?
ich glaube wir schaffen das noch, auch wenn es bei mir bischen langsamer geht.
gruß gunther
Klick klack...
Boris
Hi Gunther,
was ich sagen wollte:
Um überhaupt funktional mit mehreren Bedingungen in gefilterten Listen arbeiten zu können, gibt es die (mir bekannte) einzige "Krücke", mit - in deinem Fall - TEILERGEBNIS(3;INDIREKT("A"&ZEILE($2:$16))) Zeile für Zeile zu prüfen, ob es sich um einen sichtbaren Eintrag handelt, um so eine neue Matrix zu generieren.
Daher kannst du auch nicht einfach einen benamten Bereich dagegenstellen, sondern musst in der Tat mit INDIREKT und ZEILE arbeiten, da sonst die Einzelprüfung der Zelle nicht gegeben wäre.
Ich hab jetzt leider erstmal nicht die Zeit, mich nochmal mit deiner Beispielmappe zu beschäftigen - frühestens heute Abend wieder.
Grüße Boris
Anzeige
AW: Klick klack...
gunther
hi boris,
ich hab es mit "erg" probiert bei der namendef. er rechnet damit auch. aber leider nicht immer richtig. aber auch bei indirekt und zeile rechnet er falsch .?
also wenn du mal zeit hast, würde mich echt freuen.
viele grüße gunther
AW: Klick klack...
gunther
hollo boris,
nun hat es auch bei meinen formeln geklappt. sie sehen jetzt so aus:
=SUMME(KKLEINSTE(WENN(x;erg);ZEILE(INDIREKT($G$15+1&":"&TEILERGEBNIS(2;erg)-$G$15))))
X: =TEILERGEBNIS(3;INDIREKT("erg"))
erg: $C2:$C16
=summe einfach austauschen gegen mittelwert, min, max, anzahl -- klappt wunderbar.
offensichtlich vertragen die anderen formeln nicht die multiplikation verschiedener bedingungen - zumindest nicht so wie bei der summen-formel möglich.
also hab recht herzlichen dank
gruß gunther
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige