Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
19.04.2024 16:34:52
gemeinsamer Filter über drei Spalten
19.04.2024 15:39:42
Exceltabelle daten per combobox zu word Datei
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1748to1752
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
1748to1752
1748to1752
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Summenprodukt Teilergebnis Variabeler Bezug

Summenprodukt Teilergebnis Variabeler Bezug
03.04.2020 15:44:10
Berny
Hallo Excelprofis,
ich brauche eine Variable Formel mit Namensbezug:
In einer Mappe sind mehrere Tabellen.
In der Tabelle 1 werden Daten (Zeilenweise) hinzugefügt und entfernt. Dadurch kann sich der Bereich verschieben, oder auch vergrößern bzw. verkleinern. Deshalb habe ich dem Bereich Namen gegeben.
In einer weiteren Tabelle(2) werden verschiedene Auswertungen gefahren.
Bisher war das kein Problem, da das Ergebnis unabhängig von der Filterung war.
Nun soll aber das Ergebnis als Teilergebnis, je nach dem wie die Tabelle1 gefiltert ist geliefert werden.
Nach meiner bisherigen recherche geht das mit Summenprodukt. Ich habe mich auch schon daran probiert, aber ich bekomme den Variablen Bezug mit Namen nicht hin.
Hier ist mein letzter kläglicher Versuch.
=SUMMENPRODUKT(TEILERGEBNIS(109;INDIREKT(ADRESSE(ZEILE(Sum_1);SPALTE(Sum_1))&":"&ADRESSE(ZEILE(Sum_1) +ZEILEN(Sum_1);SPALTE(Sum_1)))))*(SP_1="A")
siehe auch Anhang.
https://www.herber.de/bbs/user/136380.xlsx
Wie müsste die Formel dafür aussehen?
M.f.G.
Berny

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

Betreff
Datum
Anwender
Anzeige
AW: Summenprodukt Teilergebnis Variabeler Bezug
03.04.2020 15:57:56
Daniel
HI
wenn bei Bedingter Summierung die Filterung eine Rolle spielen soll, würde ich so vorgehen:
füge in der Tabelle eine Hilfsspalte ein mit der Formel: =Teilergebnis(103;A2)
wobei A2 eine Zelle in der gleichen Zeile sein sollte, welche immer einen Wert erhält.
Das Ergebnis dieser Formel ist ist 1, wenn die Zeile sichtbar und 0 wenn sie ausgeblendet ist.
die Hilfsspalte kannst du dann als zusätzliche Bedingung verwenden und damit einfach mit SummeWenns oder zählenWenns auswerten, ohne das komplexe Formeln benötigt werden.
Gruß Daniel
AW: ergänzend hierzu der Hinweis ...
03.04.2020 17:35:43
neopa
Hallo Berny,
... ich halte Deine vielen Bereichsnamensdefinitionen zumindest für zu unübersichtlich, wenn nicht gar für problematisch. Denn wenn Du z.B. eine Zeile zwischen Zeile 9 und 10 einfügst, gehört diese dann weder Deinem Bereich "SP_0" noch "SP_1" an
Mein Vorschlag wäre, lösche Deine benannten Bereichsnamen, füge eine weitere (Hilfs-)Spalte hinzu in dem Du ein eindeutige Bereichskennzeichen zuweist und wandle auch Deine gesamte Datenliste in eine "intelligente" durch "Als Tabelle formatiere". Bei Datenergänzung oder Einfügung hast Du zumindest visuell leicht den Überblick ob und wo Du evtl. eine entsprechende neue Kennung vornehmen musst.
Diese angestrebte Auswertung dabei bleibt im Prinzip wie von Daniel vorgeschlagen.
Gruß Werner
.. , - ...
Anzeige
AW: ergänzend hierzu der Hinweis ...
03.04.2020 20:41:51
Berny
Hallo Daniel; Hallo Werner,
Ich habe im Bsp. natürlich eine ganz vereinfachte Darstellung gegeben, weil ich sonst jeden erschlagen würde.
Meine Datei hat tatsächlich 50 Datenbereiche die Abgrenzung der Bereiche habe ich geschützt, so dass niemand etwas zwischen den Bereichen eingeben kann. Das funktioniert in der alten Version schon seit mehr als 10 Jahren fehlerfrei. Die Buchstaben in der Spalte 1 welche ich im Bsp. nur mit "a" "b" angegeben habe können bis zu 200 verschiedene Kombinationen haben. Das heißt in der Auswertungstabelle habe ich unter umständen 10.000 Formeln dieser art. (Kein Aprilscherz!)
Deshalb will ich diese neue Anforderung "Auswertung nach Filter" in einer Formel haben.
Ich war eigentlich recht zuversichtlich das hinzubekommen. Ich bin mir auch sicher, dass es geht, aber ich bin wohl zu däm….
M.f.G.
Berny
Anzeige
AW: ergänzend hierzu der Hinweis ...
03.04.2020 21:21:58
Daniel
den Filterzustand direkt in den Auswerteformeln zu berücksichtigen ist ziemlich kompliziert und verursacht einen hohen, eigentlich unnötigen Rechenaufwand.
Das liegt daran, dass du Indirekt verwenden musst und das ist volatil, was bedeutet, dass eine Formel, die diese Funktion enthält immer neu berechnet wird, wenn sich irgendwo in irgendeiner geöffneten Exceldatei was ändert - normale Formlen werden nur dann neu berechnet, wenn sich in einem Zellbereich, den die Formel auch tatsächlich verwendet eine Änderung ergeben hat.
von daher rate ich dir zu der von mir beschriebenen Hilfsspaltenlösung.
Du musst dann deine eigentlichen Auswertungen nur noch um die Abfrage erweitern, ob in der Hilfsspalte eine 1 steht oder nicht.
Gruß Daniel
Anzeige
AW: ergänzend hierzu der Hinweis ...
04.04.2020 09:49:27
Berny
Hallo Daniel,
OK, das habe ich verstanden. Das würde mir meine Mappe langsam machen.
Gestatte mir aber noch eine Rückfrage.
du schlägst eine Hilfsspalte vor: "füge in der Tabelle eine Hilfsspalte ein mit der Formel: =Teilergebnis(103;A2)"
Hier steht doch dann Teilergebnis drinnen. Bremst das dann nicht genauso?
M.f.G.
Berny
AW: ergänzend hierzu der Hinweis ...
04.04.2020 10:47:10
Daniel
Hi
Ein kleines bisschen vielleicht.
Aber viel weniger, als in einer Matrixformel.
Außerdem erfolgt hier die Neuberechnung nur dann, wenn tatsächlich erforderlich und einmal berechnet, kann das Ergebnis dann in beliebig vielen weiteren Formeln verwendet werden. Ohne diese zusätzliche Hilfsspalte müsste jede Formel dieses Ergebnis selbst berechnen, anstatt auf einen bestehenden Wert zugreifen zu können.
Wichtig für die Reduzierung des Rechenaufwandes ist auch, dass du so das INDIREKT nicht mehr benötigt, welches auf Grund seiner Volatilität problematisch ist.
Gruß Daniel
Anzeige
AW: ergänzend hierzu der Hinweis ...
04.04.2020 10:48:25
Berny
Hallo,
ich habe mir das mit der Hilfsspalte angeschaut. Im Prinzip scheint das eine gute Lösung, aber jetzt habe ich ein neues Problem. Wenn ich in der Tabelle 1 (bisher ohne Formel) die Hilfsspalte, bei der in jeder Zeile die "Teilergebnis"- Formel stehen muss einfüge, wird die Formel nicht automatisch ergänzt, wenn ich Zeilen einfüge. Oder habe ich etwas falsch verstanden?
M.f.G.
Berny
AW: ergänzend hierzu der Hinweis ...
04.04.2020 11:05:59
Daniel
Also das ist ne ganz normale Formel, die du einfach deiner Tabelle hinzu fügen musst.
Ob du dabei alles richtig gemacht hast, kann ich für hier per ferndiagnose nicht sagen, dazu müsstest du mir schon ganz genau zeigen, wie die Tabelle bei dir aussieht und was du ganz genau gemacht hast.
Anzeige
AW: ergänzend hierzu der Hinweis ...
04.04.2020 19:02:44
Berny
Hallo Daniel,
vielen Dank für deine Hilfestellung. Ich werde die Hilfsspalte verwenden. Die Originaldatei ist sowieso eine ".XLSM". Da werde ich Ereignisgesteuert die Spalte automatisch mit der Formel ausfüllen lassen.
Schönes Wochenende
M.f.G.
Berny

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige