sos verschachtelte formel

Bild

Betrifft: sos verschachtelte formel
von: gunther
Geschrieben am: 24.02.2005 08:31:40
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

Bild

Betrifft: Ich hab deine Anfrage in unserem...
von: Boris
Geschrieben am: 24.02.2005 08:45:57
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
Bild

Betrifft: hier das gewünschte Ergebnis
von: gunther
Geschrieben am: 24.02.2005 09:11:09
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
Bild

Betrifft: Summe mit Bedingungen in gefilterter Liste
von: Boris
Geschrieben am: 24.02.2005 10:53:15
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:C16
Das 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:C16
Also: Für jede Abfrage die neue gefilterte Matrix mit eigener WENN-Bedingung vorschalten!
Alles klar?
Grüße Boris
Bild

Betrifft: Uups - Formeln abgeschnitten...
von: Boris
Geschrieben am: 24.02.2005 10:55:59
Hier die richtigen Formeln:
{=SUMME(WENN(TEILERGEBNIS(3;INDIREKT("A"&ZEILE(2:16)));(C2:C16>KKLEINSTE(WENN(TEILERGEBNIS(3;INDIREKT("A"&ZEILE(2:16)));C2:C16);2))*(C2:C16<KGRÖSSTE(WENN(TEILERGEBNIS(3;INDIREKT("A"&ZEILE(2:16)));C2:C16);2))*C2:C16))}
und mit Namen:
{=SUMME(WENN(x;(C2:C16>KKLEINSTE(WENN(x;C2:C16);2))*(C2:C16<KGRÖSSTE(WENN(x;C2:C16);2))*C2:C16))}
Grüße Boris
Bild

Betrifft: AW: Uups - Formeln abgeschnitten...
von: gunther
Geschrieben am: 24.02.2005 12:40:47
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
Bild

Betrifft: Damit es auch "Klick" macht...
von: Boris
Geschrieben am: 24.02.2005 13:29:11
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
Bild

Betrifft: Damit es auch bei mir "Klick" macht...
von: gunther
Geschrieben am: 24.02.2005 15:30:17
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
Bild

Betrifft: Klick klack...
von: Boris
Geschrieben am: 24.02.2005 15:58:06
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
Bild

Betrifft: AW: Klick klack...
von: gunther
Geschrieben am: 24.02.2005 16:08:16
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
Bild

Betrifft: AW: Klick klack...
von: gunther
Geschrieben am: 25.02.2005 22:58:28
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
 Bild

Beiträge aus den Excel-Beispielen zum Thema "sos verschachtelte formel"