Teilergebnis nach Kriterien (Text zu Zahlen)

Bild

Betrifft: Teilergebnis nach Kriterien (Text zu Zahlen)
von: Sebastian Risch
Geschrieben am: 11.11.2015 19:39:51

Hallo zusammen,
war lange nicht mehr hier... aber mir wurde immer bisher geholfen...
Ich habe folgendes Problem:
Eine Liste bereits gefilterter Umsatzwerte soll in einer Zeile über den Ergebnissen der Filterliste Teilergebnisse nach Kriterien ergeben.
Das Problem bei diesen Kriterien sind folgende:

  • Die Kriterien liegen als Text vor.

  • In dem Text ist eine Zahl

  • Für das Kriterium liegt diese Zahl in einem Wertebereich (Größer/Kleiner als)...

  • Die Summe aller Zahlen aus der Spalte Aktuell mit dem TextWert aus dem Wertebereich als Kriterium soll in dem TeilErgebnis addiert werden

  • SummeWenn funktioniert nicht, weil ich nichtg weiß, wie ich die Kriterien da rein bringe und außerdem reagiert SummeWenn nicht auf einen Filter... oder doch?
    Ich lade ein Bild mit Erklärungen hoch:
    Userbild
    Vielen Dank für Eure Hilfe
    MFG
    Sebastian

    Bild

    Betrifft: Da SUMMENPRODUKT keine Rücksicht auf ...
    von: Luc:-?
    Geschrieben am: 11.11.2015 20:57:13
    …ausgeblendete Zeilen nimmt, Sebastian,
    käme tatsächlich nur TEILERGEBNIS oder das neue AGGREGAT infrage. Ohne Rücksichtnahme auf deine exotische Xl-Einstellung sähe die 1.Fml dann zB so aus:
    {=TEILERGEBNIS(109;WENN((--RECHTS(C8:C33;4)>8410)*(--RECHTS(C8:C33;4)<8470);G8:G33;0))}
    Gruß, Luc :-?

    Besser informiert mit …

    Bild

    Betrifft: AW: Da SUMMENPRODUKT keine Rücksicht auf ...
    von: Sebastian Risch
    Geschrieben am: 11.11.2015 21:36:22
    Hallo Luc,
    danke erst einmal für die Prompte Antwort...
    Habe das ausprobiert... Aber irgendwie funktioniert die Formel mit den Geschweiften Klammern nicht... Sie ist eine Arrayformel richtig? Damit habe ich mich nur marginal einmal beschäftigt... (ist länger her...)
    Was muss ich machen, damit sie funzt?
    Was meinst du mit meiner "exotischen XL Einstellung?" Neugierig bin ich da schon...
    MFG
    Sebastian

    Bild

    Betrifft: AW: Da SUMMENPRODUKT keine Rücksicht auf ...
    von: Sebastian Risch
    Geschrieben am: 11.11.2015 22:36:35
    Hi Luc,
    Auch wenn ich die Formeln mit der Z1S1 Bezugsform umstelle funktionieren sie nicht.
    Irgendeinen Tipp, was ich Falsch mache? Ich bekomme nur einen #WERT Fehler...
    Hier sind die Korrekt ausgewählten Bereiche zu sehen:
    Userbild
    Hier ist die Array Funktion zu sehen:
    Userbild
    Irgendwelche Ideen?
    MFG
    Sebastian

    Bild

    Betrifft: AW: Da SUMMENPRODUKT keine Rücksicht auf ...
    von: Daniel
    Geschrieben am: 11.11.2015 23:39:49
    Hi
    ohne Hilfsspalte so:

    =SUMMENPRODUKT(G8:G33*TEILERGEBNIS(3;INDIREKT("G"&ZEILE(G8:G33)))*(--RECHTS(C8:C33;4)>=8410) *(--RECHTS(C8:C33;4)<8470)) 
    die zweite Formel kannst du dir ja daraus ableiten, da muss nur der letzte Parameter noch angepasst werden.
    Gruß Daniel

    Bild

    Betrifft: AW: Da SUMMENPRODUKT keine Rücksicht auf ...
    von: Sebastian Risch
    Geschrieben am: 12.11.2015 00:43:14
    Danke!
    probiere ich morgen aus... muss jetzt in die Heia... :-)
    MFG
    Sebastian

    Bild

    Betrifft: Nach reiflicher Überlegung komme ich zu dem ...
    von: Luc:-?
    Geschrieben am: 12.11.2015 05:55:56
    …Schluss, Sebastian,
    dass dieses Problem hilfszellenlos nur mit UDFs, erweiterter Fml oder AGGREGAT gelöst wdn kann. Daniels Idee zielt in die richtige Richtung, der Teil TEILERGEBNIS(3;INDIREKT("G"&ZEILE(G8:G33))) fktioniert so aber nur als HilfszellenFml. Außerdem müsste er hier TEILERGEBNIS(102;INDIREKT("G"&ZEILE(G8:G33))) oder TEILERGEBNIS(103;INDIREKT("C"&ZEILE(G8:G33))) lauten, um den gewünschten Effekt zu erzielen. Wollte man die INDIREKT-Konstruktion direkt in nur einer Fml einsetzen, müsste man den ErgebnisVektor erst sammeln/normieren, was hier mit der XlFkt N vorgenommen wdn könnte, nur liefert die im Ggsatz zu INDIREKT kein Objekt, weshalb das Konstrukt nicht innerhalb TEILERGEBNIS angewendet wdn kann, weil das im Ggsatz zu AGGREGAT zwingend ein BereichsObjekt benötigt. Das ist bei meiner Fml im Prinzip nicht anders, aber immerhin hat sie für nur eine Zeile noch fktioniert, weshalb mir das nicht gleich aufgefallen ist.
    Aber Daniels Fml kann etwas modifiziert das gewünschte Ergebnis liefern. Dafür muss man die Fkt N an richtiger Stelle hinzufügen:
    G3:=SUMMENPRODUKT(G8:G33;N(TEILERGEBNIS(103;INDIREKT("C"&ZEILE(G8:G33))));(RECHTS(C8:C33;4)>="8410")*(RECHTS(C8:C33;4)<"8470"))
    (Die Änderung des 3.Arguments habe ich nur vorgenommen, um Leerzellen auch ohne WENNFEHLER zu ermöglichen. Kommen keine vor, kann die ursprgl Form verwendet wdn!)
    Es könnte aber sein, dass AGGREGAT hier effektiver ist.
    Morrn, Luc :-?

    Bild

    Betrifft: Veraltete Version?
    von: Daniel
    Geschrieben am: 12.11.2015 12:26:51
    Hi Luc
    also bei mir liefern meine Formel und deine Erweiterung das gleiche Ergebnis.
    der Unterschied zwischen 3 und 103 sollte klar sein, wenn mit dem Autofilter gearbeitet wird, reicht die 3, die 103 ist dann erforderlich, wenn Zeilen auch direkt ausgeblendet werden.
    sollte bei dir das N() erforderlich sein, dann liegst wahrscheinlich an deinem etwas veraltetem Excel.
    bei mir mit Excel 2010 geht's auch ohne.
    Gruß Daniel

    Bild

    Betrifft: Dann hat MS inzwischen auch etwas an INDIREKT ...
    von: Luc:-?
    Geschrieben am: 12.11.2015 14:18:38
    …geändert (evtl ohne das mitzuteilen), Daniel,
    oder die Xl-Steuerung normiert das jetzt generell. Ansonsten findet man analoge N/T-Vorschläge quer durchs Archiv. Und natürlich habe ich zum Testen nicht erst einen Filter eingerichtet… ;-)
    Übrigens ist de facto jede XlVersion veraltet, sobald eine neue erscheint. Da MS inzwischen auch in fast jeder neuen Version in Bereiche eingreift, die zuvor nahezu jahr(zehnt)elang stabil blieben, entsteht eine unmögliche Situation, nicht nur für Entwickler, sondern auch dann, wenn in einer Firma unterschiedl XlVersionen eingesetzt wdn. Folglich muss man auch (ver-)alt(et)e Zustände (mind ab Xl12/2007) berücksichtigen. Könnte es sich um eine (neue) Marketing-Strategie handeln…?
    Aber diesen Zweig des Threads scheint Sebastian noch gar nicht zK genommen zu haben…
    Gruß, Luc :-?

    Bild

    Betrifft: AW: Dann hat MS inzwischen auch etwas an INDIREKT ...
    von: Daniel
    Geschrieben am: 12.11.2015 16:00:15
    Luc schrieb

    Und natürlich habe ich zum Testen nicht erst einen Filter eingerichtet… ;-)

    du solltest vielleicht etwas sorgfältiger vorgehen, bevor du fremde Lösungsvorschläge kommentierst.
    Gruß Daniel

    Bild

    Betrifft: Danke, mit Dank zurück...! :-] owT
    von: Luc:-?
    Geschrieben am: 12.11.2015 18:47:32
    :-?

    Bild

    Betrifft: Äh... bitte nicht hauen!
    von: Sebastian Risch
    Geschrieben am: 12.11.2015 21:25:15
    Ok ok.. leute... ich gebe zu das Problem ist kniffelig... aber ich habe noch keine Lösung.
    Daniel hat recht: Diesen Zweig hatte ich noch nicht gesehen, da ich heute weg vom Schreibtisch war.
    Aber bitte nicht hauen... das ist es nicht wert... Wissen kann helfen, sollte aber immer ein Helfer bleiben und nicht zu persönlichen Differenzen führen.
    Gibt es nun eine Formelbasierte Lösung?
    Ein anderer Ansatz wäre Ein Makro alle entsprechenden Werte pro sichbarer Zeile in eine Variable sammeln zu lassen und diese dann oben in die Zeile zu schreiben. Während dieser Aktion kann der Text in Spalte 3 geprüft und in eine Zahl umgewandelt werden. Gibt es so etwas wie ein "Filtered" Eigenschaft einer Zeile?
    Dazu müsste aber dann das Worksheet daraufhin geprüft werden, ob der Filter verändert wurde.
    Wie kann man das Prüfen?
    Ich würde dann nach jeder Filteränderung eine Schleife Pro Spalte Laufen lassen, die diese Routine ausführt...
    Das wäre zwar eine "zu Fuß" lösung... aber die praktikablere scheint mir...
    Freue mich, von euch zu hören...
    MFG
    Sebastian

    Bild

    Betrifft: AW: Äh... bitte nicht hauen!
    von: Daniel
    Geschrieben am: 12.11.2015 21:47:31

    
    Gibt es nun eine Formelbasierte Lösung?

    Ja, steht doch da.
    Gruß Daniel

    Bild

    Betrifft: Nein geht nicht.
    von: Sebastian Risch
    Geschrieben am: 13.11.2015 14:42:59
    Hallo Daniel,
    also die Formel funzt bei mir nicht.
    Ich werde das jetzt mit Makro lösen.
    Trotzdem vielen Dank.
    MFG
    Sebastian

    Bild

    Betrifft: Da scheinen Leerzellen zu stören, ...
    von: Luc:-?
    Geschrieben am: 13.11.2015 20:42:54
    …Sebastian;
    in nfolgd Form fktioniert Daniels Formel aber:
    =SUMMENPRODUKT(Z8S7:Z183S7*TEILERGEBNIS(3;INDIREKT("G"&ZEILE(Z8S7:Z183S7)))*(RECHTS(Z8S3:Z183S3;4) >="8410")*(RECHTS(Z8S3:Z183S3;4)<"8470"))
    Luc :-?

    Bild

    Betrifft: N war tatsächlich nicht erforderlich, auch ...
    von: Luc:-?
    Geschrieben am: 13.11.2015 22:39:47
    …unter Xl12 nicht, Daniel;
    das hatte nur etwas mit den Leerzellen und meinen ersten Versuchen, ein Ergebnis zu erzielen, zu tun. Danach blieb N einfach nur drin, obwohl die Normierung hier ja schon TEILERGEBNIS erledigt.
    Mit gefilterten Zeilen hast du recht. Nachträglich manuell ausgeblendete Zeilen wdn in solchen Bereichen auch nicht berücksichtigt, können aber nur über den Filter wieder eingeblendet wdn. Dieser (Neben-)Effekt des Filters wird in der Hilfe nicht extra erwähnt, die Gleichwertigkeit von Arg1<100 und >100 aber schon…
    Luc :-?

    Bild

    Betrifft: AW: Da SUMMENPRODUKT keine Rücksicht auf ...
    von: Sebastian Risch
    Geschrieben am: 12.11.2015 11:29:08
    Hallo Daniel,
    habe deine Formel ausprobiert...(die Ohne Hilfsspalte)
    Funzt als normale sowie als auch als Matrixformel nicht.
    Tja...
    Wir bleiben drann, oder...?
    Habe die Datei auch hochgeladen...
    https://www.herber.de/bbs/user/101446.xlsx
    MFG
    Sebastian

    Bild

    Betrifft: Hast du sie als MatrixFml eingegeben? owT
    von: Luc:-?
    Geschrieben am: 12.11.2015 03:15:15
    :-?

    Bild

    Betrifft: Lese gerade hast du! Eine Zeile habe ich noch ...
    von: Luc:-?
    Geschrieben am: 12.11.2015 03:19:48
    …so probiert, Sebastian,
    dein ganzes Bild übertrage ich aber nicht in ein Blatt. Das kannst gerne du tun und eine BspDatei hier hochladen.
    Morrn, Luc :-?

    Bild

    Betrifft: AW: Lese gerade hast du! Eine Zeile habe ich noch ...
    von: Sebastian Risch
    Geschrieben am: 12.11.2015 11:19:40
    Hallo Luc,
    ich habe die Datei hochgeladen... da darfst du mal experimentieren...wenn du magst.
    Danke schon mal...
    https://www.herber.de/bbs/user/101446.xlsx
    MFG
    Sebastian

    Bild

    Betrifft: Meine ursprgl Fml fktioniert definitiv nicht, ...
    von: Luc:-?
    Geschrieben am: 13.11.2015 04:06:22
    …Sebastian,
    und falls Daniels Fml bei deiner xlVersion nicht fktioniert, kannst du es ja mal mit meiner Modifikation seiner Fml versuchen. Anderenfalls kannst du dich ja auch in die neue xlFkt AGGREGAT vertiefen, evtl fktioniert's mit der (n/doch) besser.
    Ein Problem, das Ganze mit Fktt zu lösen, die unzusammenhängende ZellBereiche bilden, besteht darin, dass TextFktt wie hier RECHTS nichts mit derartigen Bereichen anfangen können und in SUMMENPRODUKT alle Faktor-Datenfelder gleichgroß sein müssen (inwiefern AGGREGAT dem rechnung tragen kann, weiß ich nicht). Das hat mich auf die Idee gebracht, eine bereits im Archiv befindliche UDF noch 1× unter diesem Gesichtspkt zu überarbeiten. Evtl wäre dann mit der eine einfachere Fml möglich, die auch bei deiner XlVersion fktio­nieren sollte bzw könnte.
    Morrn, Luc :-?

    Bild

    Betrifft: Mit AGGREGAT scheint ebenfalls keine ...
    von: Luc:-?
    Geschrieben am: 13.11.2015 20:23:23
    …Lösung möglich, Sebastian,
    weshalb du entweder auf Daniels, ggf modifizierte Lösung oder VBA angewiesen bist. Alternativ kannst du natürlich auch auf meine UDF-Erweiterung warten.
    SchöWE, Luc :-?

    Bild

    Betrifft: Habe jetzt je 3 Fmln für das Ergebnis, ...
    von: Luc:-?
    Geschrieben am: 14.11.2015 04:04:06
    …Sebastian:

     EFGHIJKLMNOP
    319 024 19 02419 024E3: {=SUMME(NoErrRange(ChooseIn(G8:G183;(RECHTS(C8:C183;4)≥"8410")*(RECHTS(C8:C183;4)<"8470");1;0);1))}
    4  9 174 G3: {=SUMME(NoErrRange(G8:G183;1;(RECHTS(C8:C183;4)≥"8410")*(RECHTS(C8:C183;4)<"8470")))}
    58 941 8 9418 941G5: {=SUMME(NoErrRange(G8:G183;1;RECHTS(C8:C183;4)>"8470"))}
    6    H3:=SUMMENPRODUKT(G8:G183;TEILERGEBNIS(3;INDIREKT("G"&ZEILE(G8:G183)));(RECHTS(C8:C183;4)≥"8410")*(RECHTS(C8:C183;4)<"8470"))
    Die Fmln in E3/5 habe ich doch noch mit meinem UDF-Bestand hinbekommen (CHooseIn ist alt und die Neufassung noch nicht fertig), die Fmln in G3/5 basieren auf der neuen, erweiterten Version der in alter Version im hiesigen Archiv zu findenden UDF NoErrRange, die in H3/5 auf dem von mir modifizierten Lösungsansatz Daniels.
    (Ggf nur bei bekundetem LeserInteresse folgt NoErrRange, Vs1.3, nach.)
    Morrn, Luc :-?

    Bild

    Betrifft: BrückenLink zu evtl potenziellem Kunden
    von: Luc:-?
    Geschrieben am: 15.11.2015 04:37:23
    Forumslink: https://www.herber.de/forum/messages/1458410.html
    ArchivLink: https://www.herber.de/forum/archiv/1456to1460/t1458362.htm#1458410
    Wenn die UDF nicht dort steht, setze ich dort ggf 'nen FolgeLink.
    Luc :-?

    Bild

    Betrifft: Einfache Lösung
    von: Daniel
    Geschrieben am: 11.11.2015 21:14:33
    Hi
    füge eine Hilfsspalte (bspw J/10) ein mit folgendender Formel ab Zeile 8

    =Teilergebnis(103;C8)*Rechts(C8;4)

    das Ergebnis bildest du dann so:
    schwarz G3:
    =SummeWenns(G8:G33;J8:J33;">=8410";J8:J33;"<8470")
    rot G5:
    =SummeWenns(G8:G33;J8:J33;">=8470")
    Gruß Daniel

    Bild

    Betrifft: AW: Einfache Lösung
    von: Sebastian Risch
    Geschrieben am: 11.11.2015 21:39:07
    Hallo Daniel,
    Eine Hilfsspalte ist hier nicht "erlaubt" und würde die Liste zu kompliziert machen.
    Ich probiere den Ansatz von Luc...
    MFG
    Sebastian

    Bild

    Betrifft: AW: Einfache Lösung
    von: Daniel
    Geschrieben am: 11.11.2015 21:51:50
    Hi
    warum ist das nicht erlaubt?
    im Prinzip kannst du dir aussuchen, ob die Liste kompliziert und die Formel einfach, oder die Liste einfach und die Formel kompliziert wird.
    Wobei dich im Hinzufügen einer einzelnen Spalte jetzt keine wirkliche Erhöhung der Komplexität sehe.
    Luc meint wahrscheinlich mit "exotisch" deine Verwendung der Z1S1-Bezugsart.
    die wird von normalen Excelanwendern selten verwendet, ist aber ansich nicht exotisch, sondern hat gegenüber der klassischen A1-Schreibweise auch ein paar Vorzüge.
    Gruß Daniel

    Bild

    Betrifft: AW: Einfache Lösung
    von: Sebastian Risch
    Geschrieben am: 11.11.2015 22:07:32
    Hi Daniel
    Mmh... Ich gebe zu, dass ich mit einer komplizierten (aber funktionierenden) Formel zufriedener wäre, als mit einer Strukturellen Veränderung der Tabelle, da die Daten aus einem Bericht kommen und dynamisch gefiltert werden sollen. Lucs Formel funktioniert allerdings bei mir derzeit noch nicht... und ich weiß nicht wieso...
    MFG
    Sebastian

    Bild

    Betrifft: AW: Einfache Lösung
    von: Werner
    Geschrieben am: 11.11.2015 22:46:41
    Hallo Sebastian,
    du hast die Formel von Luc auch als Matrix Formel abgeschlossen? Geschweifte Klammern nicht eingeben sondern die Formel mit Shift+STRG+Enter abschließen, dann werden die geschweiften Klammern erstellt.
    Gruß Werner

    Bild

    Betrifft: AW: Einfache Lösung
    von: Sebastian Risch
    Geschrieben am: 11.11.2015 23:09:29
    Hallo Werner,
    ja habe ich.
    MFG Sebastian

     Bild

    Beiträge aus den Excel-Beispielen zum Thema "Teilergebnis nach Kriterien (Text zu Zahlen)"