Excel Formel immer Bezug auf darüber liegender Zeile

[Sascha123145]

Ich habe in einer Liste folgende Formel:
=SUMMEWENN($A:$A;$A12899;$B:$B)

Diese liest nun solide das Datum aus, jedoch muss die Formel nach dem kopieren von weiteren Datumszeilen immer auf die darüber liegende Zeile verweisen. Im Beispiel ist die darüber liegende Zeile die 12899.
Nach dem einfügen bzw copy paste neuer Zeilen wird die Formel folgendermaßen automtisch kopiert:
=SUMMEWENN($A:$A;$A25908;$B:$B)

Jedoch ist die Nummer 25908 nicht die darüber liegende Zeile, kann ich die Formel so abändern, dass immer die darüber liegende Zeile genommen wird, egal wie oder wohin die Formel kopiert wird?

Also nochmal in kurz, die Formel soll sich immer auf Spalte A beziehen, aber immer auf die darüber liegenden Zeile.

 

[Fenatics]

Habe leider nur die englische Version und daher einfach mal „blind“ übersetzt.
Aber versuche es mal mit
=SUMMEWENN($A:$A;INDIREKT("A"&ZELLE("zeile")-1);$B:$B)
Achtung: Das ist ne volatile Funktion, heißt die Performance kann ggf. darunter leiden.

 

[Necrox]

Irgendwie brauch man hier mehr Input - warum liest du mit SUMMEWENN ein Datum aus?

 

[moneymaker4ever]

Du könntest das A12899 mit INDIREKT("A"&ZEILE()-1) ersetzen.

 

[Sascha123145]

Irgendwie brauch man hier mehr Input - warum liest du mit SUMMEWENN ein Datum aus?

Weil die Summe der jeweiligen Tage hier summiert wird.

Habe leider nur die englische Version und daher einfach mal „blind“ übersetzt.
Aber versuche es mal mit
=SUMMEWENN($A:$A;INDIREKT("A"&ZELLE("zeile")-1);$B:$B)
Achtung: Das ist ne volatile Funktion, heißt die Performance kann ggf. darunter leiden.

Das funktioniert danke! Allerdings scheint die Formel erst zu reagieren wenn ich einmal hineinklicke, vorher zeigt die Spalte einfach 0 an, nachdem ich kurz manuell rein klicke, zeigt es die Summe.

 

[nurmalsoamrande]

Er kopiert die Zeile (12899) relativ, was du willst, ist jedoch, die Zeile absolut zu kopieren. Demnach müsstest du eigentlich nur das $ vor die Zeilennummer setzen, also =SUMMEWENN($A:$A;$A$12899;$B:$B)

 

[steve1da]

Steht die Berechnung in den Excel Optionen evtl auf Manuell?

 

[Sascha123145]

Er kopiert die Zeile (12899) relativ, was du willst, ist jedoch, die Zeile absolut zu kopieren. Demnach müsstest du eigentlich nur das $ vor die Zeilennummer setzen, also =SUMMEWENN($A:$A;$A$12899;$B:$B)

Ne die Zeilennummer muss sich schon mit ändern.

Steht die Berechnung in den Excel Optionen evtl auf Manuell?

Ne auf auto..sehr merkwürdig.

 

[Sascha123145]

Also meine Formel funktioniert nun wie es soll:
=SUMMEWENN($A:$A;INDIREKT("A"&ZELLE("zeile")-1);$B:$B)
und
=(INDIREKT("$K"&ZELLE("zeile"))/INDIREKT("$I"&ZELLE("zeile")))

Allerdings habe ich nun ein anderes Problem damit, beide müssen nach dem kopieren von neuen Zeilen wieder erneut getriggert werden (anklicken F9). Wie ich las ist das wohl ein Nachteil der Indirekt Formel.
Meine Frage ist, gibt es entweder eine alternative Formel oder könnte ich mittels irgendeiner Funktion (gern via Makro) diese Formeln neu triggern?

 

[Fenatics]

Ich verstehe noch nicht ganz, wann du die Formel erneut "triggern" möchtest?
Nach dem Einfügen der Formel in einer Zelle, oder nach dem Editieren anderer Zellen, die sich auf das Ergebnis dieser Zelle auswirken sollen?
Aber das sollte eigentlich egal sein, denn das ist der Grund warum ich geschrieben hatte, es sei eine volatile Funktion. Das Problem bei "Indirekt" ist nämlich, dass egal welche Zelle aktualisiert wird, Excel eigentlich alle Zellen mit "Indirekt" oder anderen volatilen Funktionen neu berechnen sollte. Hintergrund ist der, dass Excel bei "Indirekt" keinen Verweis abspeichern kann, ob eine Zelle neu berechnet werden muss, oder nicht, da der inhalt dynamisch ist. Ich melde mich mal fix in der Firma an und guck, ob das bei mir auch so ist. Falls nicht, scheint es eine Einstellung in Excel zu geben.

-Edit: Falls es keine Einstellung gibt, kann ich dir auch gleich ein VBA Makro posten, das bei jeder Änderung im Arbeitsblatt die Berechnung neu anstößt.

-Edit2: Stimmt. Sobald ich die Zelle mit dem Suchkriterium ändere, springt das Ergebnis auf 0. Ändere ich dagegen einen anderen Wert in der Spalte A, wird das Ergebnis sofort korrekt angezeigt.

Hier der versprochene VBA Code.

Einfach in deinem Arbeitsblatt folgenden Code einfügen.

Private Sub Worksheet_Change(ByVal Target As Range)
  Calculate
End Sub

Dann wird bei jeder Änderung im Arbeitsbaltt, F9 ausgeführt.
Aber wie schon geschrieben, je öfter du solche volatilen Formeln im Arbeitsblatt nutzt, kann irgendwann die Performance in die Knie gehen.

Das sollte dann so aussehen:

 

[bughero]

Deine Fragestellung ist ziemlich vage.

Ich habe in einer Liste folgende Formel:
=SUMMEWENN($A:$A;$A12899;$B:$B)

Im Beispiel ist die darüber liegende Zeile die 12899.

Meiner Meinung nach wäre die darüber liegene Zeile 12898.

Probier mal:
=SUMMEWENN($A:$A;ADRESSE(ZEILE()-1;1);$B:$B)
Edit: geht nicht, entschuldige.

Ansonsten beschreibe dein Problem genauer und lade eine Beispieldatei hoch.

 

[Fenatics]

Probier mal:
=SUMMEWENN($A:$A;ADRESSE(ZEILE()-1;1);$B:$B)

Da fehlt meiner Meinung nach, das "Indirekt", da dort sonst einfach nur ein Zellenbezug als Text drinsteht. Nicht aber als Zellenbezug direkt, der ausgeführt werden kann. Zumindest funktionierte das bei mir so nicht, ich musste das ADRESSE(ZEILE()-1;1) in ein "Indirekt" setzen.

 

[bughero]

@Fenatics stimmt. Mein Fehler. Hatte es lange nicht mehr benutzt und anders in Erinnerung

 

[Fenatics]

@bughero Die Idee war auch gut und nicht komplett falsch. Ich finde es immer wieder erstaunlich, wie viele Varianten es gibt ein und dasselbe Problem zu lösen. Allein hier sind jetzt 3 Varianten vertreten, wie man die Zeile der aktiven Zelle auslesen kann

ADRESSE(ZEILE();1)
Zelle("zeile")
Zeile()

 

[bughero]

Doch, in dem Fall ist meine Lösung komplett falsch. Ich hatte es vorher nicht getestet und dachte es würde einen Bezug zurückliefern, aber es liefert eine Zeichenkette.

Vielleicht so besser:
=SUMMEWENN($A:$A;BEREICH.VERSCHIEBEN($A$1;ZEILE()-2;0);$B:$B)

 

[Sascha123145]

Ich verstehe noch nicht ganz, wann du die Formel erneut "triggern" möchtest?
Nach dem Einfügen der Formel in einer Zelle, oder nach dem Editieren anderer Zellen, die sich auf das Ergebnis dieser Zelle auswirken sollen?
Aber das sollte eigentlich egal sein, denn das ist der Grund warum ich geschrieben hatte, es sei eine volatile Funktion. Das Problem bei "Indirekt" ist nämlich, dass egal welche Zelle aktualisiert wird, Excel eigentlich alle Zellen mit "Indirekt" oder anderen volatilen Funktionen neu berechnen sollte. Hintergrund ist der, dass Excel bei "Indirekt" keinen Verweis abspeichern kann, ob eine Zelle neu berechnet werden muss, oder nicht, da der inhalt dynamisch ist. Ich melde mich mal fix in der Firma an und guck, ob das bei mir auch so ist. Falls nicht, scheint es eine Einstellung in Excel zu geben.

-Edit: Falls es keine Einstellung gibt, kann ich dir auch gleich ein VBA Makro posten, das bei jeder Änderung im Arbeitsblatt die Berechnung neu anstößt.

-Edit2: Stimmt. Sobald ich die Zelle mit dem Suchkriterium ändere, springt das Ergebnis auf 0. Ändere ich dagegen einen anderen Wert in der Spalte A, wird das Ergebnis sofort korrekt angezeigt.

Hier der versprochene VBA Code.

Einfach in deinem Arbeitsblatt folgenden Code einfügen.

Private Sub Worksheet_Change(ByVal Target As Range)
  Calculate
End Sub

Dann wird bei jeder Änderung im Arbeitsbaltt, F9 ausgeführt.
Aber wie schon geschrieben, je öfter du solche volatilen Formeln im Arbeitsblatt nutzt, kann irgendwann die Performance in die Knie gehen.

Das sollte dann so aussehen:
Anhang anzeigen 1235825

Danke euch! Das teste ich später mal sobald ich im Büro zeit finde. So wie ich das las ist genau dass das Problem mit INDIREKT.

 

[Sascha123145]

Also das funktioniert leider nur wenn Änderungen am ersten Sheet gemacht werden. Nutze ich das Makro, welches die Zeilen auto rein kopiert, rechnet es keine Summe mehr wenn in der Formel INDIREKT steht.
Diese Formel ist mehr als unpraktisch

 

[steve1da]

Diese Formel ist mehr als unpraktisch

Ich sagte im anderen Thread schon, dass es immer unglücklich ist, bei sich ändernden Zeilen die Formel ans Ende zu schreiben. Solche Formeln kommen immer oberhalb des sich ändernden Bereichs.

 

[Fenatics]

Nutze ich das Makro, welches die Zeilen auto rein kopiert ...

Gut, ich wusste nicht, dass du das Einfügen der Daten ebenfalls über ein Makro machst.
Dann schreib doch einfach ans Ende deines eh schon vorhandenen Makro den "Calculate" Befehl. Dann wird nach dem Kopieren per Makro alles neu berechnet.
Ansonsten muss ich gestehen, wird es nun langsam unübersichtlich, da wir ja auch nicht genau deine Datei kennen und uns so nur an den bereitgestellten Infos langhangeln können.
Ich habe bisher immer nur Lösungen angeboten, die zu den Beschreibungen passen, ohne zu prüfen, ob das Problem nicht eigentlich eher woanders liegt oder entsprechend anders gelöst werden kann.