☰

Berechnen einer Summe für eine ganzzahlige Spalte, wenn eine andere Spalte das Jahreskriterium für die Berechnung enthält

tag-icon • tag-icon microsoft-excel-2010 worksheet-function
Berechnen einer Summe für eine ganzzahlige Spalte, wenn eine andere Spalte das Jahreskriterium für die Berechnung enthält

Ich habe eine Tabelle, deren Spalten folgende Werte enthalten:

Column(A)       B            C       D     E  

NameA        01/02/2010      5       4     5  
NameB        05/06/2011      4       3     2  
NameC        12/11/2010      3       5     3  
NameD        08/14/2012      4       4     4  

-------------------------------------------------  

SUMMARY            2010      8       9     8  
                   2011      4       3     2  
                   2012      4       4     4

und so weiter...

Ich möchte die Summe der Spalte C berechnen, wobei das Jahr in Spalte B = 2010 (oder 2011 oder 2012 usw.) ist, und eine Übersichtstabelle (möglicherweise in einem anderen Arbeitsblatt) zusammen mit einem Diagramm erstellen.

Ich habe mir SUMIF, SUMIFS und SUMPRODUCT angesehen.

Ich wüsste gern, wie sich diese Aufgabe am besten erledigen lässt, vorzugsweise indem die Zusammenfassung in einem separaten Arbeitsblatt festgehalten wird.

Antwort1

APivot-Tabellekönntest machen was du willst.

Hier sind Tutorials zum Gruppieren nach Datum: http://www.databison.com/index.php/pivot-table-quick-tip-group-by-year-quarter-month-and-date-field/ http://www.youtube.com/watch?v=XR8x58Le0HA

Antwort2

Hier ist definitiv eine Pivot-Tabelle die beste Lösung. Summieren Sie Spalte C, indem Sie sie in den Bereich „Werte“ ziehen, und fassen Sie nach Zeilen für Spalte B zusammen, indem Sie diese in den Bereich „Zeilen“ ziehen.

Der Trick, um ans Ziel zu kommen, besteht darin, Ihre Zeilen zu gruppieren. Da es sich um Datumsangaben handelt, bietet Excel Ihnen nützliche Optionen, beispielsweise nach Jahr, Monat usw. Klicken Sie mit der rechten Maustaste auf eine Zeilenüberschrift (also ein Datum), wählen Sie „Gruppieren“ > „Jahr auswählen“ und heben Sie die Auswahl aller anderen auf, sodass Sie am Ende nur eine Zusammenfassung nach Jahr erhalten.

Job erledigt.

Fügen Sie ein Diagramm hinzu, das den PT als Datenquelle verwendet, und Sie sollten ein einfaches Ergebnis erhalten.

Dann lernen Sie jede Menge anderer cooler Sachen, die Sie mit PTs machen können, wie z. B. Filter hinzufügen, Slicer verwenden und ähnliches.

PS: Erwägen Sie, Ihre Quelldaten in eine Tabelle umzuwandeln, wenn Sie möchten, dass das PT auch dann noch funktioniert, wenn Sie später weitere Daten hinzufügen.

Antwort3

Nehmen wir an, die Jahre sind in B97:B99. Dann setzen Sie C97auf

=SUMIFS(C$1:C$4, $B$1:$B$4, ">="&DATE($B97,1,1), $B$1:$B$4, "<"&DATE($B97+1,1,1))

…  1und  anpassen 4, um den Speicherort Ihrer Daten widerzuspiegeln. Dies bedeutet, dass Sie die Werte in der Spalte  Cfür die Zeilen addieren (summieren), bei denen der Wert in der Spalte  B≥ 1. Januar des durch die Zahl in der Zelle angegebenen Jahres  B97, aber < 1. Januar des Folgejahres ist. Ziehen/füllen Sie dann nach unten zur Zeile  99und nach außen in so viele Spalten, wie Sie benötigen. Natürlich kann dies in ein separates Arbeitsblatt eingefügt werden; verwenden Sie einfach die Sheet1!Notation.

Bearbeiten

Wenn Sie einen Durchschnitt dieser Daten wünschen, können Sie Folgendes tun:

=SUMIF(Argumente wie oben) / COUNTIF(2. bis 5. Argument)

Wenn Sie die Möglichkeit berücksichtigen möchten, dass für ein bestimmtes Jahr keine Daten vorliegen (so dass COUNT Null wäre, was zu einem Division-durch-Null-Fehler führen würde), führen Sie Folgendes aus:

=IFERROR(SUMIF/COUNTIF-Formel von oben, Anzuzeigender Wert bei Fehler)

verwandte Informationen