Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1456to1460
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
Inhaltsverzeichnis

Teilergebnis nach Kriterien (Text zu Zahlen)

Teilergebnis nach Kriterien (Text zu Zahlen)
11.11.2015 19:39:51
Sebastian
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

    29
    Beiträge zum Forumthread
    Beiträge zu diesem Forumthread

    Betreff
    Datum
    Anwender
    Anzeige
    Da SUMMENPRODUKT keine Rücksicht auf ...
    11.11.2015 20:57:13
    Luc:-?
    …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) Gruß, Luc :-?
    Besser informiert mit …

    AW: Da SUMMENPRODUKT keine Rücksicht auf ...
    11.11.2015 21:36:22
    Sebastian
    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

    Anzeige
    AW: Da SUMMENPRODUKT keine Rücksicht auf ...
    11.11.2015 22:36:35
    Sebastian
    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

    AW: Da SUMMENPRODUKT keine Rücksicht auf ...
    11.11.2015 23:39:49
    Daniel
    Hi
    ohne Hilfsspalte so:
    =SUMMENPRODUKT(G8:G33*TEILERGEBNIS(3;INDIREKT("G"&ZEILE(G8:G33)))*(--RECHTS(C8:C33;4)>=8410) *(--RECHTS(C8:C33;4)
    die zweite Formel kannst du dir ja daraus ableiten, da muss nur der letzte Parameter noch angepasst werden.
    Gruß Daniel

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

    Nach reiflicher Überlegung komme ich zu dem ...
    12.11.2015 05:55:56
    Luc:-?
    …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) (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 :-?

    Anzeige
    Veraltete Version?
    12.11.2015 12:26:51
    Daniel
    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

    Dann hat MS inzwischen auch etwas an INDIREKT ...
    12.11.2015 14:18:38
    Luc:-?
    …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 :-?

    Anzeige
    AW: Dann hat MS inzwischen auch etwas an INDIREKT ...
    12.11.2015 16:00:15
    Daniel
    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

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

    Äh... bitte nicht hauen!
    12.11.2015 21:25:15
    Sebastian
    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

    Anzeige
    AW: Äh... bitte nicht hauen!
    12.11.2015 21:47:31
    Daniel
    
    Gibt es nun eine Formelbasierte Lösung?
    

    Ja, steht doch da.
    Gruß Daniel

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

    Da scheinen Leerzellen zu stören, ...
    13.11.2015 20:42:54
    Luc:-?
    …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) Luc :-?

    N war tatsächlich nicht erforderlich, auch ...
    13.11.2015 22:39:47
    Luc:-?
    …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 :-?

    Anzeige
    AW: Da SUMMENPRODUKT keine Rücksicht auf ...
    12.11.2015 11:29:08
    Sebastian
    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

    Hast du sie als MatrixFml eingegeben? owT
    12.11.2015 03:15:15
    Luc:-?
    :-?

    Lese gerade hast du! Eine Zeile habe ich noch ...
    12.11.2015 03:19:48
    Luc:-?
    …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 :-?

    AW: Lese gerade hast du! Eine Zeile habe ich noch ...
    12.11.2015 11:19:40
    Sebastian
    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

    Anzeige
    Meine ursprgl Fml fktioniert definitiv nicht, ...
    13.11.2015 04:06:22
    Luc:-?
    …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 :-?

    Anzeige
    Mit AGGREGAT scheint ebenfalls keine ...
    13.11.2015 20:23:23
    Luc:-?
    …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 :-?

    Habe jetzt je 3 Fmln für das Ergebnis, ...
    14.11.2015 04:04:06
    Luc:-?
    …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 :-?

    Anzeige
    Einfache Lösung
    11.11.2015 21:14:33
    Daniel
    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;"
    rot G5:
    =SummeWenns(G8:G33;J8:J33;">=8470")
    
    Gruß Daniel

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

    AW: Einfache Lösung
    11.11.2015 21:51:50
    Daniel
    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

    AW: Einfache Lösung
    11.11.2015 22:07:32
    Sebastian
    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

    AW: Einfache Lösung
    11.11.2015 22:46:41
    Werner
    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

    AW: Einfache Lösung
    11.11.2015 23:09:29
    Sebastian
    Hallo Werner,
    ja habe ich.
    MFG Sebastian

    300 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige