Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.05.2024 23:36:08
IFNA Funktion in WENNNV übertragen
16.05.2024 19:31:55
Zellenfarbe beim.Filtern ändern
16.05.2024 19:29:08
Mit INDIREKT() auf andere Tabellenblätter referenzieren
16.05.2024 19:06:22
Monatssumme / Zahlweise / Kalenderjahr
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1940to1944
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
1940to1944
1940to1944
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Dynamische Formel

Dynamische Formel
09.08.2023 12:31:06
hkoepp63
Hallo Zusammen,
ich versuche eine Formel dynamisch anzupassen. Leider trotz recherche weder mit "Indirekt" noch mit "Verketten" hinbekommen.
Wahrscheinlich sitzt der Fehler in der Formel wieder vor dem Gerät 

=summe(Alle_Aufmasse!$Q$6:$Q&"$H$3)


In H3 ist eine Formel hinterlegt die maximale Anzahl der Zellen aus der Ursprungstabelle auswirft.

Ich habe mal eine Tabelle nachgebaut, die die Originale abbilden soll.

https://www.herber.de/bbs/user/162253.xlsx

Vielen Dank im Voraus für Eure Hilfe.

Beste Grüße
Hinnerk

25
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Dynamische Formel
09.08.2023 12:36:06
SF
Hola,
vor das Q muss natürlich auch der Tabellenname.
Oder halt mit Index().
Gruß,
steve1da
AW: Dynamische Formel
09.08.2023 13:35:13
hkoepp63
Hallo Steve1da,

ich habe das mit dem Tabellennamen auch probiert... aber wie mit Index?

Grüße
Hinnerk
AW: Dynamische Formel
09.08.2023 13:37:35
SF 
ich habe das mit dem Tabellennamen auch probiert

Also hast du doch eine Lösung, warum also noch offen? 
=SUMME(Zahlen!Q6:INDEX(Zahlen!Q:Q;H3))
AW: Dynamische Formel
09.08.2023 14:10:30
hkoepp63
Hallo Steve,
das würde bedeuten, für mein Verständnis, daß ich die komplette formel mit dem langen Verweis umbauen müsste, z.Bsp.:

SUMMENPRODUKT(('Alle_Aufmasse'!$B$6:INDEX('Alle_Aufmasse'!B:B;H3)>$C3)... usw.

Ich habe den ersten Teil angepasst, da bekomme ich dann #BEZUG!

Grüße
Anzeige
AW: Dynamische Formel
09.08.2023 14:12:05
SF 
Ich habe den ersten Teil angepasst, da bekomme ich dann #BEZUG!

Poste doch mal die ganze Formel, der erste Teil bringt nämlich keinen #BEZUG. 
=SUMMENPRODUKT((Alle_Aufmasse!$B$6:INDEX(Alle_Aufmasse!B:B;H3)>$C3))
AW: warum so? Es reicht doch : =SUMME(Zahlen!Q:Q) owT
09.08.2023 12:59:42
neopa C
Gruß Werner
.. , - ...
AW: warum so? Es reicht doch : =SUMME(Zahlen!Q:Q) owT
09.08.2023 13:33:18
hkoepp63
Der dynamische Teil in dem Summenprodukt ($Q&"$H$3) ändert sich, weil da immer wieder Zeilen hinzukommen.
Darum brauche ich die Lösung und die owT Lösung reicht da leider nicht...

In H3 wird die aktuelle Zeilenzahl wiedergegeben.

AW: warum so? Es reicht doch : =SUMME(Zahlen!Q:Q) owT
09.08.2023 13:36:02
hkoepp63
Beitrag noch offen...
AW: hast Du es denn mal getestet? owT
09.08.2023 13:41:14
neopa C
Gruß Werner
.. , - ...
AW: hast Du es denn mal getestet? owT
09.08.2023 13:50:07
hkoepp63
Hallo Werner,
ich habe mit Indirekt versucht, leider ohne Erfolg.
Ich finde keine Lösung. Zumal ich ja beim summenprodukt alle Teile der Formeln anpassen muss.
Das Problem wie gesagt, ist das sich die Anzahl der Zeilen ständig nach oben hin ändert.

Hier mal die Formel aus dem Original: 


=SUMMENPRODUKT(('Alle_Aufmasse'!$B$10:$B$165>$C3)*('Alle_Aufmasse'!$B$10:$B$165=B$4)*('Alle_Aufmasse'!$V$10:$V$165>"")*'Alle_Aufmasse'!$V$10:$V$165)

Anzeige
jetzt plötzlich SUMMENPRODUKT() ??? owT
09.08.2023 13:53:13
Rudi Maintaire
AW: jetzt plötzlich SUMMENPRODUKT() ??? owT
09.08.2023 14:00:34
hkoepp63
Hallo Rudi,
das war es in der Beispieldatei die ganze Zeit...

Es geht um eine Lösung wie ich den Teil, egal ob Summe oder Summenprodukt :$B$165 dynamisch in Bezug auf $H$3 machen kann...

Grüße
Hinnerk
AW: jetzt plötzlich SUMMENPRODUKT() ??? owT
09.08.2023 14:08:52
Rudi Maintaire
Die Datei habe ich mir nicht angeschaut.

WARUM Summenprodukt?
SUMMEWENNS sollte es doch auch tun. Und das wiederum kannst du auch auf ganze Spalten oder vollkommen überdimensionierte Bereiche anwenden. Excel weiß selbst, wann Schluss ist.

Gruß
Rudi
AW: jetzt plötzlich SUMMENPRODUKT() ??? owT
09.08.2023 14:14:35
hkoepp63
=SUMMENPRODUKT(('Alle_Aufmasse'!$B$6:INDEX('Alle_Aufmasse'!B:B;H3)>$C3)*('Alle_Aufmasse'!$B$6:INDEX('Alle_Aufmasse'!B:B;H3=B$4)*('Alle_Aufmasse'!$Q$6:INDEX('Alle_Aufmasse'!Q:Q;H3>"")*'Alle_Aufmasse'!$Q$6:INDEX('Alle_Aufmasse'!Q:Q;H3)

Ich probiere das mal aus...
Anzeige
AW: jetzt plötzlich SUMMENPRODUKT() ??? owT
09.08.2023 14:15:27
hkoepp63 
=SUMMENPRODUKT(('Alle_Aufmasse'!$B$6:INDEX('Alle_Aufmasse'!B:B;H3)>$C3)*('Alle_Aufmasse'!$B$6:INDEX('Alle_Aufmasse'!B:B;H3=B$4)*('Alle_Aufmasse'!$Q$6:INDEX('Alle_Aufmasse'!Q:Q;H3>"")*'Alle_Aufmasse'!$Q$6:INDEX('Alle_Aufmasse'!Q:Q;H3)


Ich probiere das mal aus...
AW: jetzt plötzlich SUMMENPRODUKT() ??? owT
09.08.2023 14:30:10
Rudi Maintaire
ich würde für den benutzten Bereich einen Namen definieren oder die Daten in eine 'intelligente' Tabelle umwandeln.
Datentabellen sollten immer in A1 anfangen und nicht irgendwo auf dem Blatt.

Gruß
Rudi
AW: Deinen ersten Satz stimme ich voll zu ...
09.08.2023 14:43:49
neopa C
Hallo Rudi,

... Deinen zweiten Satz nicht. Aber das ist hier nicht entscheidend.
In seiner Beispieldatei gibt es kein Blatt "Alle_Aufmasse" und in seiner Zelle H3 ermittelt er eine letzte Zeilennummer, wo ein Wert steht.
Ich bleibe momentan noch immer bei meiner Erstaussage hier im thread.

Gruß Werner
.. , - ...
Anzeige
AW: Deinen ersten Satz stimme ich voll zu ...
09.08.2023 15:04:10
hkoepp63
Alter Schwede... Was ist denn heute mit Euch los...
Da hat man ja nach so vielen Jahren fast keine Lust mehr zu fragen.

Meine Tabelle fängt mit A1 an und hört mit CD385 auf. Enthält sensible Daten. Darum die abgespeckte Version.
Der Teil der für die Formel zum Testen wichtig ist, habe ich in einen Tabelle gebaut, wo auch die Abfragen an der richtigen Stelle sind.

Falls ihr einen Lösungsvorschlag habt, wäre ich Euch dankbar, schulmeisterliche Kommentare könnt ihr gern für Euch behalten. :-(
AW: hierzu ...
09.08.2023 15:13:22
neopa C
Hallo hkoepp63,

... die Daten einer Datei kann man anonymisieren (da gibt es sogar Tools für). Für Deine bisher eingestellte Datei stimmt mein Vorschlag. Wenn Dir dies und oder auch die anderen Vorschlägen nicht helfen, solltest vielleicht eine Datengrundlage bereitstellen, die Deiner Originaldatei besser entspricht.

Gruß Werner
.. , - ...
Anzeige
AW: Deinen ersten Satz stimme ich voll zu ...
09.08.2023 15:14:17
SF
Sorry, wir mussten natürlich davon ausgehen, dass Beschreibung und Datei nicht zusammenpassen und das wir raten sollen, was du denn gerne hättest.
Unser Fehler, ganz klar. Aber wie man zur Lösung kommt steht ja hier im Thread, du schaffst das.
AW: Deinen ersten Satz stimme ich voll zu ...
09.08.2023 15:26:24
Rudi Maintaire
auch eine Beispieldatei sollte von der Struktur her dem Original entsprechen.
Dynamische Formel
10.08.2023 07:58:23
hkoepp63
Hallo Zusammen,
ich habe die Tabelle(n) einmal komplett dem Original nachgestellt.
Ich hoffe, das macht verständlicher wonach ich suche.

Diese Datei wertet aus wieviel Umsatz gemacht wurde:

https://www.herber.de/bbs/user/162264.xlsx

Diese Datei ist der Datenursprung:

https://www.herber.de/bbs/user/162265.xlsx

Aus der Tabelle 162264 wird in Zelle H3 die höchste Zeilennummer aus Tabelle 162265 abgefragt.
Dieses Ergebnis möchte ich gern dynamisch in den Formeln in Tabelle 162264 darstellen, so daß wenn Zeilen hinzukommen, die Formel automatisch angepasst wird.

Wenn "Summenprodukt" der falsche Lösungsansatz ist, bin ich immer gern bereit etwas dazuzulernen.

Vielen Dank für Eure Unterstützung.

Grüße
Hinnerk
Anzeige
AW: SUMMENPRODUKT() einzusetzen ist hier nicht falsch ...
10.08.2023 09:19:18
neopa C
Hallo Hinnerk,

... weil Du eine Auswertung von Daten aus einer anderen Datei vornehmen willst. Aber ich würde die Auswertung trotzdem anders vornehmen.
Wenn Du eine reine Formellösung anstreben solltest, würde ich die relevanten Daten (in Deinem Fall die der Spalten B, G und Q mit einer einfachen INDEX() -Formel in Hilfsspalten (nachfolgend z.B. in K - Bauart, L - Datum und M die Summe, welche Du weit genug ziehend nach unten kopierst ) holen und diese dann mit SUMMEWENNS() auswerten.

Dann folgende Formel in B5:
=WENN($A5="Gesamtumsatz";SUMME(B6:B11);SUMMEWENNS('07.2023'!$M:$M;'07.2023'!$K:$K;$A5;'07.2023'!$L:$L;"="&B$4;'07.2023'!$L:$L;">"&A$4))
und diese nach rechts und unten ziehend kopieren.

Gruß Werner
.. , - ...

Anzeige
AW: SUMMENPRODUKT() einzusetzen ist hier nicht falsch ...
10.08.2023 09:57:02
hkoepp63
Hallo Werner,
vielen Dank für Deine Antwort.
Irgendwie stehe ich gerade auf dem Schlauch.
Wenn ich diese Formel nach unten ziehe ohne $$ dann ändern sich doch auch die Zahlen (Zellen)
Müsste ich das nicht nach unten so anpassen: 

=WENN($A5="Gesamtumsatz";SUMME(B$6:B11);SUMMEWENNS('07.2023'!$M:$M;'07.2023'!$K:$K;$A5;'07.2023'!$L:$L;"="&B$4;'07.2023'!$L:$L;">"&A$4))


Dann ändert sich die Formel bei B11 immer um einen Zähler.

Ich probiere das einmal aus. Ggf. Melde ich mich noch einmal.

Danke und Grüße
Hinnerk
AW: schau Dir die Formel nochmal genauer an, ...
10.08.2023 10:23:42
neopa C
Hallo Hinnerk,

... natürlich ändert sich die Zeilennummern der Bereichsangabe B6:B11 beim runter kopieren der Formel. Das ist aber nicht relevant, da dieser Formelteil ja nur in Zeile 5 ausgewertet wird.

Genaugenommen reicht als Formel im aufgezeigten Fall auch schon:
=WENN($A5="Gesamtumsatz";SUMME(B6:B11);SUMMEWENNS($M:$M;$K:$K;$A5;$L:$L;"="&B$4;$L:$L;">"&A$4))

Ein Bezug auf das aktuelle Tabellenblatt mit '07.2023'!... mit anzugeben wie getan ist ja nicht wirklich notwendig.

Gruß Werner
.. , - ...
Anzeige

Links zu Excel-Dialogen

Frame

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige