Frage Verhindert, dass Excel beim Hinzufügen einer neuen Zeile die Formel der letzten Tabellenzeile ändert


Ich habe eine Tabelle mit den Spalten "date", "cost" und "daily". "Datum" ist ein Datum, "Kosten" ist eine Zahl und "täglich" ist eine Formel, die die Gesamtkosten jedes Tages berechnet. Die Formel sieht so aus:

= IF (A2 = A3, "", SUMME (A $ 2: A2, A2, B $ 2: B $ 2)

Dadurch wird überprüft, ob sich das Datum der aktuellen Zeile von dem der nächsten Zeile unterscheidet. Wenn dies der Fall ist, wird die Summe der Kosten der Zeilen mit dem aktuellen Datum zurückgegeben.

Das Problem tritt auf, wenn ich neue Daten zur Tabelle hinzufüge. Angenommen, es gibt 10 Zeilen. Dann wird die Zelle C10 die Formel haben:

= WENN (A10 = A11, "", SUMME (A $ 2: A10, A10, B $ 2: B $ 2)

Wenn ich jedoch eine neue Zeile hinzufüge (z. B. durch Drücken der Tabulatortaste, während die Zelle C10 ausgewählt ist), wird die Formel von C10 automatisch geändert in:

= IF (A10 =A12, "", SUMME (A $ 2:A11, A10, B $ 2: B $ 2)

Darüber hinaus wird der Stil der Zelle zurückgesetzt, so dass es kein fett gedruckter Text mehr ist, und ich bekomme eine Warnung neben der Zelle, die anzeigt, dass "diese Zelle nicht mit der Spaltenformel übereinstimmt".

Wie verhindere ich dieses Verhalten?


1
2018-02-28 14:58


Ursprung


Sie fügen eine Zeile bei C10 ein, die den aktuellen C10 nach C11 verschiebt. Wenn Sie an C11 einfügen, wird dies nicht passieren. Sind diese Dinge richtig? - Raystafarian
Ist das eine echte Tabelle mit strukturierten Referenzen? Sie können wahrscheinlich die OFFSET-Funktion für den Gleichheitstest verwenden. - Ron Rosenfeld
Es ist eine echte Tabelle wie in einer Excel-Tabelle. Ich habe versucht mit OFFSET und es hat nicht funktioniert. - felamaslen
1. Was ist mit der mittleren A10? Ändert sich das nicht auch zu A11? 2. Wie fügen Sie Zeilen mit Tab-Taste ein? Wenn ich Tab drücke, wird die ausgewählte Zelle geändert, aber keine Zeile eingefügt. - ZygD


Antworten:


Sie können stattdessen offset () verwenden. (:

So wirst du Formel:

=IF(A2=offset(A2,1,0),"",SUMIF(A$2:offset(A2,0,0),A2,B$2:B$2)

In meinem Test-Dataset funktioniert es, wo das Hinzufügen von Zeilen die Formel nicht ändert, indem die Abhängigkeiten entfernt werden. Bitte teile es mit, falls dies nicht der Fall ist.

p / s: Dinge (Zellreferenzänderung) passiert auf diese Weise, weil die von Ihnen definierte Formel von den A11-Werten abhängt. Und die Aktion "Zeilen einfügen" behält die ursprüngliche Zelle, die in der C10-Formel referenziert ist, bei. Diese wurde aufgrund der Zeileneinfügung nach A12 verschoben.


0
2017-08-06 20:42