Wie kann sich die Summenformel in Excel beim einfügen einer neuen Spalte anpassen?
Hallo ich habe da folgende Frage.
- In einem Tabellenblatt ist die letzte Spalte F
- Ich habe in Zelle A1 folgende Summenformel: =sum(B1:F1)
Jetzt füge ich zwischen Spalte A und B eine neue Spalte ein.
Dann wird die Formel fälschlich zu: =sum(C1:G1) Denn ich will alle Spalten außer A natürlich in die Summe einbeziehen.
Wie geht das das die Formel dann aber so aussieht? = sum(B1:G1)
Ich Danke vielmals für Hilfe!
5 Antworten
diese Formel (vor der Erweiterung) sollte Dein Problem lösen:
=Summe(indirekt("B"&Zelle("Zeile";A1)&":"&Zelle("Adresse":F1))
Zeile(A1) in Verbindung mit Zelle("Adresse":F1) macht diese Formel auch nach unten kopierbar
Dadurch, dass B als Text festgeschrieben ist, wird beim Einfügen von Spalten nichts angepasst.
Dagegen ist die letzte Zelle der Summe durch Zelle("Adresse";F1) variabel, wenn F1 durch die zusätzliche Spalte zu G1 wird, ändert sich auch die Adresse in der Formel (obwohl die Formel vorher $F$1 und nachher $G$1 liefert.
$ bezeichnet nur, dass es dieselbe Zelle ist, obwohl sich die Position im Blatt verändert, also nicht, dass die Adresse gleich ist!
PPPS: Ein bisschen Spaß muss sein. Böses Drive korrigiert das schon von alleine :x
Beides richtig, aus der Kleinschreibung der Funktionen ersiehst Du, dass es eine "Trockenübung" war. xl hätte das auch korrigiert.
Wenn Du Deine Summenformel in F1 schreibst = Summe(A1:E1) und eine Spalte einfügst, sieht es so aus SUMME(A1:F1) und die Formel steht in G1
Warum muss die Summe in A1 stehen?
Entweder eigene Inflexibilität. Oder weil es besser aussieht, wenn in der ersten Spalte der Durchschnitt berechnet wird und das Ergebnis nicht erst in der letzten Spalte gesucht werden muss. Mit Hilfstabellenblättern, ~spalten... habe ich hier kein Ergebnis das mir gefällt.
Ich weiß nicht, wie der engl. Befehl heißt (du scheinst ja ne engl. Version zu haben?)
=SUMME(INDIREKT("B1"):H1)
Falls du die Formel nach unten kopieren willst und sie sich dabei in den Zeilen anpassen soll, muss sie noch etwas erweitert werden (Zeilennummer berechnen).
Ohne es probiert zu haben, sieht es nicht aus, als ob es funktionieren würde.
In welchem Programm hast du das getestet?
Excel 2010 und Excel 2007
und ja, es funktioniert, sonst hätte ich es nicht gepostet. Probiert hast du es ja anscheinend nicht.
Meine Formel ist die einfache Schreibweise von lamiams Formel. In lamiams Formel ist das :F1 nur in die Klammer von INDIREKT() reingezogen und daher erfordert es statt der Schreibweise
:F1
ein
&":"&Zelle("Adresse";F1)
Ja hast Recht, selbst in OO geht es. Bin wohl schon zu Drive fixiert, da funktioniert es nicht, das "freie" :H1 macht einen parse error. Bis jetzt hatte ich keine Probleme mit unterschieden, aber das verstehe ich auch nicht.
Ja, auch im uralten xl2000 funktioniert es, wenn - anders als sonst bei Summe() - kein Texteintrag im Bereich vorhanden ist.
Merkwürdig, jetzt gehts plötzlich doch, soeben gings erst nach entfernen des Texteintrags! (ich hab ihn rausgeschoben, dann gings, und jetzt wieder reingeschoben, und es geht weiterhin!???
Du kannst Zeilen und Spalten mit vorangestelltem $ absolut setzen
=SUMME($B1:F1)
Dachte ich auch erst, das funktioniert bei mir (excel 2003) aber nicht. Das hilft beim Formel-kopieren-durch-ziehen-mit-der-Maus
Das hab ich schon lange probiert, aber ihr seit von alleine draufgekommen :)
Füge vor dem Summenbereich eine Leerspalte ein und erweitere die Summe um diese Spalte. Wenn du nicht am Rand, sondern in der Mitte des Bereichs einfügst, werden die Grenzen nicht verändert.
Guter Trick, bin ich nicht drauf gekommen.
Gibt es auch eine Lösung mit indirect/concatenate...? (... in Drive, geht aber auch jede andere Lösung für ein andere Tabellenkalkulation.)
BÄM
Vielen Dank! Das isses!
Mir hatte die Zelle gefehlt! Bin ich froh das es jetzt funktioniert, wie lange ich daran getüftelt habe...!
Da sieht man mal wieder, wer über Sachen redet, von denen er Ahnung hat ;)
P.S. Nur ein kleiner Schönheitsfehler mit dem letzten Doppelpunkt, soll ein Strichpunkt sein.