PDA

Vollständige Version anzeigen : Summe von Bereich A1 bis aktuelle Zeile


magicdeep
10.07.2014, 07:50
Hallo,

ich habe eine Spalte mit mehren Zahlen untereinander. Z.Bsp. so:
2
4
6
2
3
1
4

Nun möchte ich in der Spalte daneben jeweile die Summe der Zahlen ausgeben und zwar von der ersten Zeile bis zur aktuelle Zeile.
Würde im Ergebnis also etwas so ausschauen:

2 - 2
4 - 6
6 - 12
2 - 14
3 - 17
1 - 18
4 - 22

Statt jeweils eine neue Formel zu erstellen wie
=SUMME(A1:A1)
=SUMME(A1:A2)
=SUMME(A1:A3)
etc.

würde ich gerne die aktuelle Zeile mit einbauen, welche ich über =ZEILE() bekomme.
Aber wie bekomme ich das hin?

Kann mir jemand helfen?
Danke!

Mc Santa
10.07.2014, 07:56
Hallo,

du hast zwei Möglichkeiten. Einmal durch geschicktes Setzen von $ (Dollarzeichen).
Aber sieh dir auch die Alternative an. Du berechnest das gleiche, aber mit deutlich weniger Rechenleistung!!
<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:97px;" /><col style="width:158px;" /><col style="width:158px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Zahlen</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">eine Möglichkeit</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">bessere Alternative</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">9</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">9</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">14</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">67</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">81</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">81</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">88</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">88</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">93</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">93</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">95</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">95</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">98</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">98</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>C2</td><td>=A2</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>B2:B10</td><td>=SUMME(A$2:A2)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>C3:C10</td><td>=C2+A3</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/>

Hilft dir das weiter?
VG

RPP63neu
10.07.2014, 07:57
Hallo!
<br/><b><em>Tabelle3</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:97px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">2</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">6</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">12</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">14</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">17</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">18</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">22</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>B2:B8</td><td>=SUMME(A$2:A2)</td><td>&nbsp;</td><td>&nbsp;</td></tr></table>
http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip<br/>
http://Hajo-Excel.de/tools.htm<br/>
Gruß, Ralf

magicdeep
10.07.2014, 08:07
Hallo,

vielen Dank!
Da hab ich wohl zu kompliziert gedacht...