Excel in Formel letzte Zeile angeben?

4 Antworten

Hi,

um den Bezug auf die letzte Zelle zu erhalten kannst du eine Mischung aus Bereich.Verschieben() und Vergleich() verwenden:

  • =TEILERGEBNIS(9;$Y$1:BEREICH.VERSCHIEBEN($Y$1;VERGLEICH(MIN(Y:Y)-1;Y:Y;-1)-1;))

Vergleich() mit "-1" als drittem Argument geht von einer absteigend sortierten Liste aus in der der kleinste Wert gesucht ist, der größer-gleich dem Suchkriterium ist. Da mit dem Suchkriterium Min(Y:Y)-1 garantiert ist, das alle Werte größer sind, gibt es die Position des letzten Eintrags aus und Bereich.Verschieben() verschiebt den Bezug Y1 um ebensoviele Zeilen

Danke für deine Antwort!

Es handelt sich nicht um eine sortierte Liste, sondern um einen normalen Datensatz in einer Spalte, dessen Position auch nicht geändert werden darf. Wie würde die Formel also aussehen?

1
@Anonymous3858

Bitte

Die Formel ist auch in dem Fall gleich, es reicht aus das Excel glaubt es handelt sich um eine sortierte Liste, sie muss es nicht tatsächlich sein.

1
@DanKirpan

Hmm scheint nicht zu funktionieren. In Zeile 1 steht keine Zahl, sondern der Name der Spalte. Könnte es daran liegen?

1
@Anonymous3858

:? in meiner Testtabelle ignoriert es Texte (egal wo sie stehen), also liegt es eher nicht daran

Was genau funktioniert nicht, rechnet es falsch oder kommt ein Fehler?

1
@DanKirpan

Das Ergebnis ist einfach 0, obwohl viele Werte deutlich höher als 0 sind.

Mit der normalen Formel kriege ich ein Ergebnis von 21 Mio :D

1
@Anonymous3858

Ok ja 0 klingt nach zu wenig; Sind die Zahlen eventuell Text statt Zahlen?

Das kannst du testen indem du du in eine beliebige Zelle eine 1 schreibst, diese kopierst und dann mit Rechtsklick-> Inhalte einfügen...-> Inhalte einfügen...-> Multiplizieren über der Liste einfügst

1
@DanKirpan

Hmm ich sehe schon, dass im Format Zahl ist. Die erste Zeile wo ein Wort steht ist aber auch im Zahlenformat. Ist das eventuell das Problem?

1
@Anonymous3858

Formatierungen sind soweit ich weiß nur eine rein optische Sache, beeinflussen Berechnungen also nicht :?

Könntest du mal per Schritt-für-Schritt-Auswertung (Registerkarte Formeln -> Formelauswertung) überprüfen von welchen Zellen versucht wird die Summe zu bilden? Gehe bisher davon aus, das es aus unbekannten Gründen nur vom Bereich Y1:Y1 versucht die Summe zu bilden.

1
@DanKirpan

Ich sehe gerade bevor ich die Formel überhaupt einfüge kommt ein kleines Fenster: "Es liegt mindestens ein Zirkelbezug vor, bei dem sich eine Formel direkt oder indirekt auf die eigene Zelle bezieht. Dies kann zu einer fehlerhaften Berechnung der Formel führen. Versuchen Sie, diese Bezüge zu entfernen oder zu ändern oder die Formel in eine andere Zelle zu verschieben."

Wenn ich beim letzten Schritt der Auswertung bin, steht da: "TEILERGBNIS(9;$Y$1;$Y$4416)" und als Ergebnis 0.

1
@Anonymous3858

Zeile 4416 ist vermutlich auch die tatsächliche letzte Zeile? Dann wird es am Zirkelbezug liegen und sie sollte funktionieren wenn du die Formel in einer anderen Spalte als Y schreibst.

Falls du darauf besteht sowohl Summe als auch Rohdaten in Y zu haben, müsste man die Bezüge bereits im Vorfeld so einschränken das die Summenzelle nicht selbst im Bereich ist

1
@DanKirpan

In Zeile 4416 steht dann die Formel, also bis Zeile 4415 steht die letzte Zahl, die in der Formel berechnet wird.

Die Formel MUSS leider in Spalte Y stehen :D

1
@Anonymous3858

Also steht sie immer direkt unter dem letzten Datensatz? Dann wird es schwieriger den Bezug zusammenzubauen

Wobei wenn ich es recht bedenke wird es einfacher :D

=TEILERGEBNIS(9;INDIREKT("Y1:Y"&ZEILE()-1;1))

1

Teilergebnis ist eigentlich dafür gedacht in gefilterten Tabellen nur noch über die sichtbaren summiert, etc.
Also, sorge dafür dass der Bereich automatisch 'alle' nötigen Zeilen einschließt und verwende die Autofilter um dann entsprechend nur das gewünschte Ergebnis zu sehen.

Wenn ich dich richtig verstehe, möchtest du in einem Feld eingeben, bis zu welcher Zeile Summiert werden soll. Ich würde es mit INDIREKT lösen:

=TEILERGEBNIS(9;INDIREKT("Y1:Y"&A1))

In meinem Beispiel sage ich in A1, er soll bis Zeile 4 summieren.

Woher ich das weiß:Eigene Erfahrung – Faulheit >> Neugier >> Wissen
 - (Computer, Informatik, Excel)

Nutze den Autofilter - Funktion =Summe() usw.

Sobald Du gefiltert hast, wird automatisch das Teilergebnis des gefilterten Bereiches angezeigt.

Woher ich das weiß:eigene Erfahrung

Es soll schon bei der Formel Teilergebnis bleiben :/

1

Was möchtest Du wissen?