wake-up-neo.com

Excel - SUM an das Ende der Liste

Ich habe eine datenbankähnliche Tabelle in Excel 2003 mit einer einzelnen Kopfzeile, die AutoFilters in Zeile 6 und Daten von Zeile 7 bis -say-Zeile 160 enthält. Die Zeilen 1 - 5 sind der Blatttitel, die Spaltengruppentitel und die Anweisungen, die ich nicht kann beseitigen, abschütteln.

Ich zeige auch die SUM(X6:X160) und SUBTOTAL(109,X6:X160) in diesem Bereich für numerische Spalten an. Ich möchte dies nicht am Ende der Tabelle anzeigen, da mehrere Benutzer dieser Tabelle häufig Zeilen hinzufügen und die Formeln regelmäßig zerstören.

Problem: Immer wenn ein Benutzer Daten am Ende der Tabelle hinzufügt, müsste man die Summen- und Zwischensummenformeln aktualisieren, um den Bereich zu erweitern, der - natürlich - meistens vergessen wird.

Ich könnte die Formeln erweitern, um alle Zeilen bis zu -say- Zeile 500 abzudecken und die verbleibenden Zeilen auszublenden, so dass ein Benutzer Zeilen "einfügen" müsste, wenn das sichtbare Ende der Tabelle erreicht ist - was wiederum die Formeln aktualisieren würde , aber ich halte das nicht für einen sehr sicheren Weg ... meine Benutzer zu kennen.

Frage: Gibt es eine Möglichkeit, SUM und SUBTOTAL von X6 " bis zum Ende von Spalte X zu erstellen, wo immer dies ist "?

FAZIT

Ich habe SUM (OFFSET (....)) verwendet und den Parameter "height" aus einem neuen Feld in der Kopfzeile übernommen, in dem die "Anzahl der Datensätze" durch eine =COUNTA($A:$A)-1 (-1 für den Text der Spaltenüberschrift, der ebenfalls gezählt wird) angezeigt wird ), da aus dem Geschäftskontext hervorgeht, dass in Spalte A ein Schlüsselwert für einen gültigen Datensatz vorhanden sein muss und keine leeren Zeilen zulässig sind (ich kann zumindest so viele Benutzer schulen) - plus dem Nutzervorteil, nicht nur das zu sehen SUMME aber jetzt auch die COUNT der Datensätze im Header-Fenster eingefroren.

10
MikeD

Die Verwendung von Offset () und Count () scheint am beliebtesten zu sein, und ich bin mir sicher, dass die Verwendung am effizientesten ist.

=SUM(OFFSET($X$6,0,0,COUNT($X$6:$X$1000)))

Persönlich neige ich dazu, Indirect () häufig für Dinge zu verwenden. Es wird wahrscheinlich langsamer laufen, aber es funktioniert. Es hilft mir, den Bereich zu sehen, der erstellt wird. Vorsicht, da ein Teil des Bereichs im Text enthalten ist, wird er nicht aktualisiert, wenn Sie die Formel verschieben. Das kann dich stolpern. Hier bist du sowieso.

=SUM(INDIRECT("$X$6:$X$" & COUNT($X$6:$X$1000)))

Sie können die Dynamikbereiche verwenden, auf die iDevlop hinweist, oder den Offset () oder Indirect () in den Sum () einfügen, wie ich es gerade getan habe.

In beiden Fällen möchten Sie vorsichtig sein, da Indirect () und Offset ()Flüchtige Funktionen sind. Was Sie generell vermeiden wollen.

Ich habe auch diesen Typen gefunden, der Index () und Match () verwendet, die keine flüchtigen Funktionen sind.

BEARBEITEN:

Wenn ich daran denke (weil ich es gerade auf einer Tabelle von mir gemacht habe) ..

Vorausgesetzt, Sie wissen, dass Ihre Daten ein vernünftiges Limit haben, z. B. 1000, können Sie einfach =SUM($X$6:$X$1000) verwenden und die Leerzeichen werden übersprungen, auch für die Zwischensummenmethode 1 oder 101 (Durchschnitt).

5
ptpaterson

Nun, ich denke, Sie können so etwas wie = SUMME (C: C) verwenden, um alle Zellen in Spalte C zu summieren, und es wird den Text automatisch ausschließen, ich habe es ausprobiert und es hat funktioniert

Sicher ! Und ich finde es viel besser, die Gesamtsumme oben zu haben, da Sie auch die erste (n) Zeile (n) einfrieren können, um diese Gesamtsummen sichtbar zu halten.
Fragen Sie einfach Google nach "Excel Dynamic Range". Hier sind ein paar Links:
http://www.ozgrid.com/Excel/DynamicRanges.htm
http://support.Microsoft.com/kb/830287

All dies geschieht mit einer Kombination aus den Funktionen Offset () und Count ().

2
Patrick Honorez

Setzen Sie diese Formel in die letzte Zeile Ihres Datenbereichs (darf keine Tabelle/Liste sein), um das Problem zu lösen

=SUM(INDIRECT(("x6:x"&ROW()-1)))  

oder wenn Sie eine Tabelle/Liste haben, können Sie diese Formel verwenden

=sum([table header name])

wie Sie die Spalte von "Betrag" in der Überschrift des Typs summieren möchten

=sum([Amount]) 

in der letzten Zeile Ihrer Liste/Tabelle

0