Summe/neue Zeilen
Felix Lapis
Zeile, so dass in der letzten Zeile jeweils die Summe aller Einträge
einer Spalte steht.
Diese Tabelle wird ständig erweitert, indem neue Zeilen hinzugefügt
werden. Leider wird dadurch die Summenfunktion nicht automatisch auch
auf die neue Zeile erweitert, sondern reicht nur bis zur (dann)
vorletzten Zeile.
Was muß ich machen, damit beim nachträglichen Einfügen von Zeilen, die
Summe automatisch (!) alle, d.h. auch die neuen, Zeilen umfasst?
Danke!
Sonja
mach doch zwischen der Zeile mit der Summe und den
Beträgen eine Leerzeile, dann wird die neu eingefügte
Zeile mitgerechnet.
Gruss Sonja
>-----Originalnachricht-----
>.
>
Felix Lapis
> mach doch zwischen der Zeile mit der Summe und den
> Beträgen eine Leerzeile, dann wird die neu eingefügte
> Zeile mitgerechnet.
Danke für den Tipp. Er funktioniert. Aber eigentlich müßte das doch
irgendwie auch ohne Leerzeile gehen, oder? Würde mich wundern, wenn eine
solche Standardfunktion Excel vor irgendwelche Probleme stellt...
Danke und ciao,
Felix
Christiane Kehrel
Hallo Felix,
die Frage ist gar nicht so dumm. Daran haben Sich schon viele die Zähne
ausgebissen. Hier eine Antwort, die ich selbst mal bekommen habe und
die funktionieren sollte:
"Dynamische Bereichsnamen lassen sich realisieren, indem Sie als Bezug
keine feste Adresse sondern eine Formel zuweisen. Als Ergebnis muss
diese Formel einen Bereich zurückgeben, dessen Größe von den
vorliegenden Tabellendaten abhängig ist.
Für diesen Zweck eignet sich die Tabellenfunktion
BEREICH.VERSCHIEBEN(), die einen versetzten Bezug zurückgibt. Die
Funktion hat folgende Syntax:
=BEREICH.VERSCHIEBEN(<Ausgangszelle>;<Zeilenversatz>;
<Spaltenversatz>;<Höhe>;<Breite>)
Einen dynamischen Bereich können Sie somit definieren, indem Sie als
<Ausgangszelle> die obere linke Eckzelle des Bereichs angeben.
<Zeilenversatz> und <Spaltenversatz> werden auf "0" gesetzt. Höhe und
Breite richten sich nach der Orientierung des zu benennenden Bereichs.
Wenn Sie den Bereich automatisch nach unten erweitern wollen, lassen
Sie die Höhe mit Hilfe der Tabellenfunktion ANZAHL2 ermitteln. Wenn Sie
neue Daten am rechten Ende des Bereichs eingeben, nutzen Sie ANZAHL2
zur Berechnung der Breite.
Die genaue Funktionsweise verdeutlicht ein praktisches Beispiel:
Angenommen, Sie haben eine Tabelle, in der Sie Tageseinnahmen
verbuchen. Datum und Betrag geben Sie in die Spalten B und C ein,
beginnend mit Zelle B5. Nun wollen Sie einen Bereichsnamen definieren,
der sich auf alle Einnahmedaten von Zelle B5 bis zum aktuellen Tag
bezieht.
Um einen Namen zu definieren, wählen Sie zuerst Einfügen-Name(n) und
dann Festlegen (Excel 97) bzw. Definieren (Excel 2000/2002) an. Im Feld
Namen in der Arbeitsmappe tragen Sie den gewünschten Bereichsnamen ein
- zum Beispiel "Einnahmen". Danach ersetzen Sie den Inhalt im Feld
Bezieht sich auf durch folgende Formel:
=BEREICH.VERSCHIEBEN($B$5;0;0;ANZAHL2($B$5:$B$65536);2)
Zum Schluss bestätigen Sie die Änderungen mit Ok.
Die Formel geht von der oberen linken Eckzelle des zu benennenden
Bereichs aus. Das soll auch die Eckzelle bleiben, weshalb Zeilen- und
Spaltenversatz "0" sind. Da sich der Bereich mit jeder Tageseinnahme
dynamisch nach unten erweitert, verwenden Sie die Funktion ANZAHL2()
zur Ermittlung der Bereichshöhe. Sie entspricht der Anzahl der Werte in
Spalte B, von Zelle B5 abwärts. Die Breite des Bereichs ist "2", weil
auch die Beträge in Spalte C eingeschlossen sein sollen.
Wichtig ist, dass Sie absolute Adressen (mit $-Zeichen) verwenden. In
der ANZAHL2-Funktion können Sie auch einen kleineren Bereich auswerten,
wenn Sie wissen, dass sich der benannte Bereich niemals bis zum
absoluten unteren Ende (Zeile 65536) eines Tabellenblatts erstrecken
wird - etwa nur den Bereich bis Zeile 1000.
Einen kleinen Haken hat die Formel noch: sie funktioniert nur, wenn der
Bereich keine leeren Zellen enthält. Das trifft auf die meisten Listen
zu. Falls Sie aber trotzdem einmal einen dynamischen Bereichsnamen
benötigen, zu dem auch leere Zellen gehören, definieren Sie den Namen
mit folgenden Formeln (wie oben wird B5 als obere linke Eckzelle
angenommen und eine Breite von 2 Spalten vorausgesetzt):
wenn der Bereich Zahlen- oder Datumswerte enthält:
=BEREICH.VERSCHIEBEN($B$5;0;0;
VERGLEICH(1E+30;$B$5:$B$65536);2)
wenn der Bereich Textwerte enthält:
=BEREICH.VERSCHIEBEN($B$5;0;0;
VERGLEICH("###";$B$5:$B$65536;-1);2)
Die VERGLEICH-Funktion liefert die letzte Position des Suchkriteriums
im potenziellen Eingabebereich der Tabelle. Dabei wird als
Suchkriterium entweder eine sehr hohe Zahl (hier: 1E+30 = 1 mit 30
Nullen) oder ein Fantasietext (hier: ###) eingesetzt. Wenn Sie
tatsächlich mit so hohen Zahlenwerten rechnen, müssen Sie die
Potenzzahl erhöhen. Oder wenn "###" ein möglicher Tabelleneintrag ist,
ersetzen Sie die Zeichenfolge durch irgendein anderes Zeichen - etwa
"*" oder "~".
Ob der Bereichsname akzeptiert wird, erkennen Sie, wenn Sie mit F5 das
Dialogfeld Gehe zu öffnen und als Bezug/Verweis den zugewiesenen Namen
eingeben. Bei korrekter Namensformel markiert Excel anschließend den
passenden Tabellenbereich."
Soweit. Hoffentlich klappt´s.
Gruß, Christiane
Martin Worm
hallo Felix,
sowas würd ich jetzt nicht unter die Standardfunktionen einreihen.
Aber so gehts(und sicherlich auch anders):
=SUMME(BEREICH.VERSCHIEBEN(C2;-ZEILE()+1;0;ZEILE()-1;1))
C2 ist die Zelle, in der die Formel steht. Also, eingefügt in B3, C2
durch B3 ersetzen :-)
Man sollte sie nicht in Zeile 1 einfügen. Dort gibt es den Fehler #Bezug
mfG
--
E-Mail von Martin Worm, Karlsruhe, Deutschland
benutze Excel2000(9.0.2812) Win ME
Antworten bitte nur in die Newsgroup
Ingrid Kutsche
vielen Dank für die ausführliche Anleitung. Ich hab hier schon oft von
dieser Formel/Funktion gelesen, aber sie nie wirklich begriffen. Ich
hoffe, nun fällt der Groschen!
Ingrid
> die Frage ist gar nicht so dumm. Daran haben Sich schon viele die Zähne
> ausgebissen. Hier eine Antwort, die ich selbst mal bekommen habe und
> die funktionieren sollte:
>
> "Dynamische Bereichsnamen lassen sich realisieren, indem Sie als Bezug
> keine feste Adresse sondern eine Formel zuweisen. Als Ergebnis muss
> diese Formel einen Bereich zurückgeben, dessen Größe von den
> vorliegenden Tabellendaten abhängig ist.
<snip>
Felix Lapis
> die Frage ist gar nicht so dumm.
Schön :-). Aber es wundert mich sehr, dass man derartige Verrenkungen
anstellen muß, um eine recht lappidare Anwendung ausführen zu können.
Aber vielen Dank für dein ausführliches Posting. Da in meinem Fall
tatsächlich Leerzellen vorkommen, werde ich mir aber wohl weiterhin mit
einer zusätzlichen Leerzeile vor der Summe am Ende behelfen - das ist
doch einiges leichter!
Danke!