Wie summiert man jede Zeile in einem benannten Bereich in Excel?

Question

Ich versuche, eine Budgetvorlage zurückzuentwickeln, damit ich sie ändern kann. Sie hat Abschnitte für verschiedene Ausgaben (Haushalt, Unterhaltung, Transport usw.). Jeder Abschnitt hat mehrere Ausgaben (Transport umfasst Autoversicherung, Benzin, Parken usw.). Jeder Abschnitt ist ein benannter Bereich, und jede Ausgabe wird in einer Zelle am rechten Ende der Zeile summiert. Die Vorlage führt jedoch in jeder Zeile eines Abschnitts dieselbe Funktion aus, und ich kann nicht herausfinden, wie das funktioniert.

Hier ist ein Bild davon mit der Funktion oben und dem Namensmanager. Wie gesagt, die hervorgehobene Funktion ist in allen Zeilensummen für „Daily Living“ genau dieselbe Funktion. https://i.stack.imgur.com/mbMij.jpg

Ich habe genau dieselben Namensgruppen (Namensmanager und alle Bereiche sind identisch) und dasselbe Layout, aber ich kann nicht dieselbe Funktion erhalten, die einen Fehler erzeugt und nicht akzeptiert wird. Das Beste, was ich tun konnte, war, SUM(Daily) einzugeben, wodurch alle Zeilen im Abschnitt summiert werden, nicht jede Zeile einzeln.

Kann jemand diese Funktion anhand des Bildes erklären =SUM(Daily[@[January]:[December]]), insbesondere das Symbol „@“, und woher Excel weiß, dass JAN (in der Spaltenüberschrift) Januar bedeutet, wenn es nicht im Namensmanager steht?

BONUS: Kann jemand auch erklären, wie die Funktionen „Abschnittssumme“ und „Gesamtsumme“ funktionieren? https://i.stack.imgur.com/ydMfz.jpg

Answer 1

Nun, es gibt viele Fragen und ich hoffe, dass ich jede einzelne beantworten kann. Hoffe.

Zuallererst: Das Datenlayout ist ein Table. Ein TableUnterschied zwischen Excel und dem Zellbereich, in dem die Daten früher erschienen. Es ist ein formales Objekt, in dem einfache Regeln befolgt werden und Excel im Gegenzug nicht alle möglichen seltsamen Dinge berücksichtigen muss, die jeder auf der Welt beim Einrichten von Dingen tun könnte (ich habe gesehen, dass Summenzellen IN Daten erscheinen, nicht darunter, darüber oder links oder rechts, und im selben Bereich, in verschiedenen Zeilen für verschiedene Spalten. Es passte auf eine schwachsinnige Weise in das Schema des Kerls, aber was zum Teufel soll Excel damit anfangen, wenn es für einen Menschen bizarr und verwirrend aussieht? Oder für zehn Menschen?

In jedem Fall heißt Ihre TabelleTäglich. Sie können es übrigens an der Art und Weise erkennen, wie die Elemente der Formel eingegeben werden, wenn auch aus keinem anderen Grund: Sehen Sie die eckigen Klammern ("[ ]")? Diese werden fast nur in Tabellen und an einigen anderen Stellen verwendet (die alle wahrscheinlich Tabellentechnologie verwenden). Wenn Sie beim Erstellen der einfachen SUM()Formel die ganze Zeile markieren, konvertiert Excel die Zelladressen, die Sie beim Markieren gesehen haben, in Tabellenreferenzen, basierend auf den Spaltenüberschriften: Januar bis Dezember, die die Ränder des Bereichs sind, den Sie hier markieren. Anstatt also eine Referenz wie D20:O20 zu sehen, erhalten Sie [Januar:Dezember].

Ein Element bleibt in dieser Formel übrig: die@. Da es sich um eine Tabelle handelt, würde Excel denken, Sie würden ALLE Zeilen summieren, wenn Sie Excel nicht mitteilen könnten, dass Sie NUR DIESE ZEILE meinen. In Excel teilt dies @Excel mit, dass „nur diese Zeile“ gemeint ist, und wird nicht nur in Tabellen und an verschiedenen anderen Stellen verwendet, sondern in GROSSER Weise bei der Verwendung von SpillFormeln. Genauer gesagt teilt dies Excel DIESE ZEILE mit, sodass Sie nur die Zeile „Trockenreinigung“ summieren und die Zahlen in allen Spalten zwischen Januar und Dezember zusammenzählen.

In vielerlei Hinsicht sind SUM()die hier und SUBTOTAL()(überall) verwendeten Werte irgendwie gleich. Es SUBTOTAL()gibt aber einen großen Unterschied: Excel ignoriert Teilergebnisse, die beim Zusammenfassen einer Spalte erhalten werden. Es ignoriert jedoch keine Teilergebnisse, die mit erstellt wurden. SUM()Wenn sie sich also in derselben Spalte wie einige Rohzahlen befinden, erhalten Sie die doppelte Summe, die Sie erhalten sollten. SUBTOTAL()Außerdem gibt es eine Reihe verschiedener Dinge zurück, wie z. B. einen Durchschnitt, der SUM()nicht allein berechnet werden kann.

Der Autor der Tabelle hat offensichtlich eine sehr standardmäßige und sinnvolle Methode zur Erstellung der Summierung gewählt. Er hat horizontale Summen erstellt, SUM()damit diese bei der anschließenden vertikalen Summierung nicht automatisch ignoriert werden, und hat sie dann SUBTOTAL()zur Erstellung der vertikalen Summierung verwendet, die Sie in der Tabelle sehen.

Keines dieser Dinge befindet sich in benannten Bereichen, da sie automatisch verfügbar sind (so automatisch, dass es manchmal schwer ist, sie zu vermeiden ...), da das Ganze eine Tabelle ist. Da es eine Tabelle ist, können Sie Spaltenüberschriften als Referenzen verwenden. Früher konnte man das immer tun, aber sie haben es vor etwa 17 Jahren abgeschafft und dann in dieser Form wieder eingeführt ... seufz ...

Manchmal ist es auch ärgerlich, dass Spaltenüberschriften automatisch Text sind und daher keine Formeln sein können. Dynamische Überschriften sind bei Tabellen also nicht möglich.

Ein Grund, warum es nicht wie eine Tabelle aussieht, ist, dass Sie die lästigen Filtersymbole rechts neben jeder Kopfzeile nicht sehen, die normalerweise über einem Teil der Kopfzeile liegen ... Es ist einfach, sie verschwinden zu lassen, und der Autor hat das eindeutig getan. Ohne diese Symbole würden die meisten eine ganze Weile NICHT an „Tabelle“ denken, aber die Form der Formelverweise ist ein todsicheres Zeichen. Ein weiterer Grund, der diese Tatsache verschleiert, ist, dass die meisten Leute an das Aussehen „grüner Streifen“ bei Tabellen gewöhnt sind (jede Farbe, aber dieses Papier war früher größtenteils grün und ich bin 845 Jahre alt, also ...). Aber auch das kann wegformatiert werden, wenn es nicht angemessen ist oder als verwerflich empfunden wird (Warum? Warum? Es macht bildschirmbreite Tabellen so viel besser lesbar!), und in diesem Fall gehört es eindeutig nicht dazu. Man könnte es in jedem Datenbereich verwenden, aber da sie sich in der Anzahl der Zeilen unterscheiden, die sie haben, wäre das Jahr für Jahr ein großer Aufwand.

Also:

1. Es ist ein Tableund so hat es einige gute Dinge für Sie zur Verfügung
2. Die Daten befinden sich in zwei Abschnitten, der linken Spalte mit den Überschriften der Abschnitte und dann der Unterabschnitte (in Ihrem Fall einzelne Zeilen). Wie „Chemische Reinigung“ und dann der Abschnitt „Sobald vorhanden“ unter den Überschriften von Januar bis Dezember.
3. In jeder Zeile wird mithilfe von SUM()Funktionen die Jahressumme berechnet.
4. In jeder Spalte wird mithilfe von Funktionen die Summe des Monats (und in der Spalte ganz rechts das Jahr) berechnet SUBTOTAL().
5. Die SUM()Funktionen arbeiten jeweils nur mit einer Zeile, da sie das @vor das erste Element in ihrer Bereichsreferenz („@January“) setzen.
6. EINZELNE Abschnitte („EINKOMMEN“, „HAUS“ usw.) werden für jeden Monat und für das Jahr summiert. Dabei werden SUBTOTAL()Funktionen verwendet, da diese bei der Summenbildung in der Gesamtspalte ignoriert werden. (Im Gegensatz dazu, SUM()wessen Ergebnisse aus jedem Abschnitt in die untere Gesamtspalte aufgenommen WÜRDEN, ergeben die Gesamtsummen doppelt so hohe Ergebnisse wie sie sein sollten.)

Sehr plausibel und nützlich angelegt, mit Aufmerksamkeit für wichtige Details. Man könnte argumentieren, dass diese Art von Dingen besser in eine einfache Tabelle oder sogar eine Tabellenversion gepackt wird ... eine einfache Tabelle, wenn Sie so wollen, und dann eine Pivot-Tabelle (ja, 845 Jahre alt, also klingt eine Kreuztabelle, sogar Kreuztabellen, für mich immer noch normal) zur Präsentation. Schließlich haben Pivot-Tabellen, oder genauer gesagt, Power Pivot-Tabellen viele Vorteile (oooooo ... Slicer ...) und ganz eigene Summierungsfähigkeiten und so weiter und so fort, aber das ist eine ganz andere Sache, die man lernen muss, und dies hat jemandes Bedürfnisse sehr gut gelöst.

Ich bin mir nicht sicher, was Sie tun müssen, daher kann ich Ihnen dabei nicht helfen. Aber wenn Sie die Arbeit irgendwie verbessern oder sie irgendwie zu Ihrer eigenen machen möchten, können Sie das nicht wirklich, so gut ist es gemacht.Sofern Sie sich nicht für die Pivot-Tabelle entscheiden. Wenn das also alles ist, ersparen Sie sich Ärger: Das ist sehr anständig und Sie müssen nicht wirklich nach Verbesserungen suchen. Wenn nur der Chef etwas erledigt haben möchte oder sonst, nun, ich verstehe Sie, das haben wir alle schon erlebt.