Office: (Office 365) Zeile-Funktion in formatierter Tabelle

Hallo liebe Forengemeinde,

ich habe eine formatierte Tabelle (weiß nicht genau, ob es so auch heißt, aber bei "Start", "Formatvorlagen" - "Als Tabelle formatieren" mit mehreren Spalten und Zeilen. Nun möchte ich in Spalte J das Delta der Stromwerte aus der Spalte "Strom" errechnen.

Bisher wollte ich das mit Index in einer kleinen Formel lösen, in der auch der Spaltenname berücksichtigt wird, allerdings erhalte ich mit

=[@Strom]-Index([Strom];Zeile()-1)

einen "#Bezug"-Fehler. Ist [Strom] in einer Index-Formel nicht gleich zusetzen mit einer Matrix wie z. B. "C:C"?
Über Tipps und Hinweise wäre ich sehr dankbar.

 

nur wenige schauen auf Deinen Rechner und sehen die Datei.
Ich möchte gerne den Fehler im Original sehen.

Ich baue keine Datei nach. Die Zeit hat schon jemand investiert.

Ein Nachbau sieht meist anders aus als das Original. <B>Darum sollte das Original verlinkt werden.</b>

Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten
Hinweis - Anonymisierung und Pseudonymisierung von Beispielmappen - MS-Office-Forum
änderrn.
Der Name einer hochgeladenen Mappe wird im Beitrag automatisch angezeigt, sodass es bei Verwendung von aussagekräftigen Namen leichter fällt, sie später im Ablageordner wiederzufinden und sie gedanklich einem bestimmten Thema zuzuordnen. Namen wie Muster*, Test*, Mappe*, Beispiel*, Fehler*, Kalender* usw. sind so allgemein, dass eine Zuordnung zu einem Thema unmöglich gemacht wird.
Es sollte ein aussagekräftiger Name sein.

Benutze hier im Forum die Funktion zum hochladen. Falls Du die nicht benutzen möchtest beachte, von unsicheren Servern wie z.B. www.file-upload.net lade ich keine Datei runter. (lt. Einschätzung meines Virenprogramms)3

Bilder lade ich mir nicht runter, da Excel damit nichts anfangen kann.

Bilder statt Datei | Excel ist sexy!

Hochgeladene Bilder können zwar als solche in Excel importiert werden, sind jedoch bei der Lösung von Problemen nicht sehr hilfreich, da man die eigentlichen Daten nicht ohne große und zeitraubende Umwege direkt in die Tabelle übertragen kann.

Das ist nur meine Meinung zu dem Thema.

 

Moin!
Du musst Dir mit einer (doppelten, daher auszublendenden) Zeile mit dem Anfangsbestand behelfen.

	A	B
1	Strom	Delta
2	123
3	123	0
4	242	119
5	385	143
6	411	26
7	489	104
8	627	138
9	743	116
10	871	128

Zelle	Formel
B2	=WENNFEHLER([@Strom]-A1;"")
B3	=WENNFEHLER([@Strom]-A2;"")
B4	=WENNFEHLER([@Strom]-A3;"")
B5	=WENNFEHLER([@Strom]-A4;"")
B6	=WENNFEHLER([@Strom]-A5;"")
B7	=WENNFEHLER([@Strom]-A5;"")
B8	=WENNFEHLER([@Strom]-A7;"")
B9	=WENNFEHLER([@Strom]-A8;"")
B10	=WENNFEHLER([@Strom]-A9;"")

Gruß Ralf

 

Hi,

die Formel ist im Prinzip korrekt (wenn sie sich auch innerhalb der strukturierten Tabelle befindet).
#BEZUG! kommt, sobald ZEILE()-1 einen (Index-)Wert liefert, den es nicht gibt.
Steht ZEILE()-1 z.B. in Zeile 7, dann liefert das ja 6 - und wenn die Tabelle aber nur 5 Zeilen hat, dann geht das nicht.
Ansonsten zeig doch mal Deine kleine Beispieldatei.

 

Entschuldige, das hatte ich ganz vergessen.
Es gibt allerdings ein "Update" zu meinem Problem. Ich habe, da ich Daten aus einer anderen Tabelle übernommen habe, die alte Tabelle aber noch nicht gelöscht hatte, zweimal die Spalte [Strom] gehabt, daher der Bezugsfehler.

Nun habe ich aber das Problem, dass das Ergebnis immer null ist, obwohl das Delta im Beispiel ja eins wäre. Demnach verwende ich die Formel scheinbar nicht korrekt.

Anbei die Tabelle.

 

Hi,

das Delta muss immer Null sein, da Du jeden Wert von sich selbst abziehst.

[@Strom] liefert zeilenweise den selben Eintrag wie INDEX([Strom];ZEILE()-1)

 

Hmm, wie kann ich denn die Zeile [@Strom] mit der Zeile darüber abziehen?
Ich dachte [Strom] wäre dann der Index und ich nehme dann ja - dachte ich zumindest - die Zeile darüber und Subtrahiere sie...

 

Hallo,

die Formel für Zelle J2 lautet: =[@Strom]-N(C1)
Diese Formel kopiert sich nach ihrer Eingabe in J2 automatisch in die restlichen Zellen der "Δ Voreintrag"-Spalte.

Anmerkungen:
Die Funktion ZEILE() ergibt die Zeilennummer der aktiven Zelle im Arbeitsblatt.
Dagegen beginnt der Bereich [Strom] in Zeile 2 des Arbeitsblattes (C2:C25)
Um die Zeile oberhalb der aktiven Zelle zu erreichen, müsstest du die folgende Formel verwenden:
=INDEX([Strom];ZEILE()-2)
Um in Spalte J die Strom-Zeilendifferenzen zu bilden, müsste man in Spalte J folgende Formel eingeben (zB. in J2): =[@Strom]-INDEX([Strom];ZEILE()-2)
Tut man dies, erhält man leider in J2 die Fehlermeldung: #ÜBERLAUF!
Als Fehlerbegründung erhält man: "Überlauf innerhalb einer Tabelle nicht möglich." In (strukturierten) Tabellen sind nämlich Array-Formeln, die einen mehrzelligen Ergebnisbereich haben, nicht erlaubt - sie produzieren somit einen #ÜBERLAUF-Fehler. Dagegen hat =[@Strom]-N(C1) nur einen einzelligen Ergebnisbereich, auch wenn sie sich nach ihrer Eingabe in alle Zellen der (strukt.)Tabellenspalte kopiert. Die Funktion N(...) liefert die Zahl, die in C1 steht. Steht dort aber Text, ergibt sie als Resultat eine 0.

 

Hallo Exl121150,
vielen Dank für die - zumindest für mich - neue Funktion "N". Diese kannte ich noch nicht.

Warum ich bei Zeile() mit 2 Substrahieren muss um auf die vorherige Zeile zu kommen, erschließt sich mir nicht. Wenn ich diese Formel in J45 stehen habe, dann ist die aktive Zeile entsprechend Zeile 45. Subtrahiere ich hiervon eine 1 kommt doch 44 raus.

Ich danke dir allerdings für die Unterstützung.
Auch allen anderen Dank ich. Das Problem ist gelöst, aber die Zeile-Funktion ist für mich etwas unschlüssiger geworden.

 

Hallo,

Das liegt an der INDEX-Funktion, die den Bereich [Strom] verwendet. Ausgehend von deinem Beispiel, der Zelle J45, in welcher folgende Formel stehe:
=[@Strom]-INDEX([Strom];ZEILE()-2)
1) Somit errechnet die Funktion ZEILE() den Wert 45.
2) Der Zellbereich [Strom] müsste sich dann in C2:C45 (oder größer) befinden
3a) Wendet man auf diesen Bereich die INDEX-Funktion an, so liefert INDEX([Strom];1) den Wert aus Zelle C2 und INDEX([Strom];43) den Wert aus Zelle C44.
3b) Der Ausdruck [@Strom] bezieht sich dann auf Zelle C45 (wegen Punkt 1)).
4) Für deine Zeilendifferenz-Rechnung benötigst du aber genau die Differenz =C45-C44, wenn die aktive Zelle in J45 steht
5) Daraus folgt, dass du von ZEILE() eine 2 subtrahieren musst, damit du die 43 erhältst, die du laut Punkt 3a) in der INDEX-Funktion benötigst.

Das ist eine Uralt-Funktion in Excel, die seit Excels Anfangszeiten enthalten ist und genau für diesen Einsatzzweck enorm praktisch ist.

 

Hi,

statt hier feste Werte von ZEILE() abzuziehen, in der Hoffnung, dass sich die Tabelle nie verschiebt, sollt man auf die Zeile der Kopfzeile zurückgreifen. Und damit das Ganze auch in der ersten Zeile der Tabelle funktioniert, rechnet man das vorher aus und nimmt den INDEX nur, wenn der berechnete Wert >0 ist.

=LET(z;ZEILE()-1-ZEILE(Tabelle916[#Kopfzeilen]);WENN(z>0;[@Strom]-INDEX([Strom];z);""))

Oder man zieht einfach die Zeile des ersten Wertes ab:

=LET(z;ZEILE()-ZEILE(INDEX([Strom];1));WENN(z>0;[@Strom]-INDEX([Strom];z);""))