Formel/Makro für erweiterbare Tabelle

Question

Hallo zusammen,

ich habe eine Excel-Tabelle in der verschiedene Produkte angeboten werden. Linker Hand steht eine Beschreibung mit Bild und rechter Hand sind dann verschiedene Preise (Miete für paar Jahre bzw für Sofortkauf). Zum Schluss kommt dann eine Gesamtsumme in Jahren zum Mieten und für den Sofortkauf aller angebotenen Produkte.
Nachdem aber immer wieder Produkte wegfallen/hinzukommen muss ich die Formel für die Gesamtsumme ganz unten immer wieder erneuern.
Heißt im Klartext dass von Spalte A-K nur Beschreibungen zu finden sind und in Spalte L und M die Beträge stehen.

Als Beispiel:

In den Spalten M20:M80 stehen meine Beträge die ich summieren will.
Jetzt kommen zwei Produkte dazu und die Summe bezieht sich auf M20:M90. Um nicht immer 6 Summen (für die Mieten und den Sofortkauf) erneuern zu müssen würde ich gerne ein Makro oder eine "dehnbare" Formel haben.

Hat jemand eine zündende Idee für mich?

vielen Dank im Voraus

Elbryan

p.s. Formel/Makro sollte auch bei 200 Produkten einsetzbar sein

Answer 1

Hallo Elbryan,

angenommen Du willst die Werte von M2 bis M20 summieren, wobei
die Formel in M21 stehen soll, dann geht das mit

=SUMME(BEREICH.VERSCHIEBEN(M$1;1;;ZEILE()-2;))

Du kannst jetzt zwischen M2 und der Formelzelle beliebig viele Zeilen
einfügen, die Formel brauchst Du nicht mehr ändern.

Gruß
Rainer

Answer 2

Hallo,

wenn die Summen nicht in der Spalte L oder M stehen, geht es für Spalte M mit

=Summe(M:M)

sonst

=SUMME(INDIREKT(ADRESSE(1;Spalte())):INDIREKT(ADRESSE(ZEILE()-1;Spalte())))

Gruß

Helmut

Answer 3

Kleine Korrektur meinerseits:

Die Summen beziehen sich auf die einzelenen Spalten/Zellen und nicht wie gerade gesagt auf eine komplette Spalte....


hier einmal eine Beispieltabelle damit es etwas deutlicher wird.

Wie gesagt, danke im Voraus für die Ideen :)

Gruß

Maurice

Answer 4

Hallo,

seh dir mal diese Lösung an

rapidshare.com/files/253020443/test.xls.html

mit Summewenn()

Gruß

Helmut

Answer 5

Hallo Elbryan,

anbei eine Lösung mit dynamischen Formeln, denn Du willst ja im
oberen Teil Zeilen einfügen oder löschen.

Dazu ist erforderlich, dass in M198 der Text "Ende" steht.

Über dieser Zeile kannst Du dann beliebig einfügen oder löschen, die
Formeln brauchst Du nicht zu ändern.

Hier
die Datei

Gruß
Rainer

Answer 6

Hallo,

wenn bei meiner Tabelle irgendwo zwischen der 1. und 198. Zeile zusätzliche Spalten eingefugt werden funktioniert die Formel trotzdem. Nur Zeile 1 und 198 müssen erste und letzte bleinben

Gruß

Helmut

Answer 7

Hallo Helmut,

wie meinst du diesen Satz?

Nur Zeile 1 und 198 müssen erste und letzte bleinben

Heißt das, dass ich Zeile 1 und 198 nicht verschieben darf?


Gruß

Maurice

Answer 8

Hallo,

es bedeutet, dass du nur nach der Zeile 1 oder vor der jetzigen Zeile 198 neue Zeilen einfügen darfst, da sonst diese bei der Berechnung nicht mitgenommen werden.

Die jetzige Zeile 198 ist die letzte Zeile vor deinen Zusammenstellungen. Wenn du nun 10 Zeilen einfügst , ist die letzte Zeile dann 108 oder wenn du welche löschst dann rutsch diese Zeile nach oben.

In Zelle M200 Steht jetzt

=SUMMEWENN(B$1:$D198;"Monatliche Miete " & $M199 & "*";K$1:$M198)

die fetten Werte verändern sich wenn du Zeilen eifügst oder löschst.

Gruß

Helmut

Answer 9

Vielen Dank erstma für eure Antworten,

nun gibt es allerdings ein bisschen verwirrung meinerseits.
Sind die Formeln schon fertig sodass sie in die Tabelle gesetzt werden können oder muss ich dort noch ein paar individuelle veränderungen vornehmen?
Tut mir leid falls diese Frage doof klingt aber in Excel bin ich momentan noch eher bei den Anfängern einzuordnen.
Denn prinzipiell funktioniert die Formel in dem Sinn, dass sie mir Summen anzeigt allerdings weicht eure Summe imens von meiner ab und genau dies ist es, was vor allem beim Kunden nicht soo übermäßig toll ankommt.

Gruß

Maurice

p.s. falls noch etwas unklar ist, welcher wert wo summiert werden soll dann fragt bitte. dann kann ich gezielt antworten ;)

Answer 10

Hallo,

Bei der Summenformel düefen keine Texte vorkommen die nicht eindeutig sind.

Ich habe z.B. die Zeile "Kaufpreis" in "Kaufpreis mit Service" und in "Kaufpreis ohne Service" geändert, da er alle Positionen in denen "Kaufpreis" vorkommt zusammenrechnet. Die Bezeichnungen müssen eindeutig und in allen Zeilen gleichgeschrieben sein, sonst funktioniert es nicht.

Gruß

Helmut