Ich habe diese einfache Datentabelle in Excel und brauche eine Formel, um den Gesamtbetrag für heute zu ermitteln. Es wäre also eine Summe aller Beträge, die zwischen 00:00 und 23:59 Uhr für das Datum HEUTE() liegen. Wie kann ich das machen?
Um die Sache (möglicherweise) etwas komplizierter zu machen, kann ich der Quelltabelle (siehe Abbildung unten) keine weiteren Spalten hinzufügen. Die gesamte Mathematik wird in einem separaten Blatt durchgeführt. Ich kann also nicht jeder Zeile eine weitere Spalte hinzufügen, um einige Hilfszahlen/Zwischenschritte hinzuzufügen. Im Idealfall wäre das alles eine große Formel, die ich in eine Zelle auf dem separaten Blatt einfügen kann.
Beispieldaten:
Antwort1
Als erstes müssen Sie herausfinden, ob Ihre Daten Text sind oder ob es sich um Zahlen handelt, die von Excel so formatiert wurden, dass sie als Daten angezeigt werden. Dies ist eine sehr wichtige Unterscheidung und Sie sollten sich für Letzteres entscheiden, da Sie dadurch mehr der in Excel integrierten Formeln verwenden können. Einige mögliche Tests sind
- =ISTEXT(A2)
- =ISTZAHL(A2)
- Ändern Sie die Zellenformatierung auf allgemein und prüfen Sie, ob sich der angezeigte Inhalt ändert
Für diese Lösung wird davon ausgegangen, dass Ihre Daten Zahlen sind. Wenn dies nicht der Fall ist, gibt es zahlreiche Fragen/Antworten zur Konvertierung von Textdaten in das Zahlen-/Excel-Format.
Uhrzeit und Datum in Excel verstehen
Excel speichert das Datum als Ganzzahl. Es ist die Anzahl der Tage seit dem 0. Januar 1900. 1 ist also der 1. Januar 1900. (Ich glaube, das Referenzjahr ist 1905, aber ich könnte mich irren). Alle Daten in Excel sind also einfach Ganzzahlen mit einer speziellen Formatierung, um sie in der gewohnten Weise anzuzeigen. Die Zeit wird dagegen als Prozentsatz eines Tages oder, wenn Sie es vorziehen, als Dezimalzahl eines Tages gespeichert. 0,5 steht für einen halben Tag oder das Mittagessen. Wenn Sie also nur das Datum aus einem Datum mit gemischter Uhrzeit ermitteln müssen, müssen Sie nur den ganzzahligen Teil entfernen. Umgekehrt müssen Sie nur den Dezimalteil entfernen, wenn Sie nur die Uhrzeit benötigen.
Option 1 SUMPRODUCT
SUMPRODUCTist eine reguläre Funktion, die arrayähnliche Berechnungen durchführt. Daher möchten Sie die Bereichsreferenz auf ungefähr Ihre Daten beschränken und keine vollständigen Spalten-/Zeilenreferenzen verwenden. Wenn Sie Letzteres tun, kann Ihr Computer durch übermäßige, nutzlose Berechnungen mit leeren Zellen überlastet werden.
=SUMPRODUCT((INT($A$2:$A$12)=TODAY())*$B$2:$B$12)
Beachten Sie TODAY(), dass es sich um eine volatile Funktion handelt. Dies bedeutet, dass sie jedes Mal neu berechnet wird, wenn sich etwas auf dem Blatt ändert, auch wenn dies die Funktion nicht beeinflusst. Eine nicht-volatile Funktion (die meisten regulären Funktionen) wird nur neu berechnet, wenn eine Änderung auftritt, die sie beeinflusst. Wenn Sie eine volatile Funktion in viele Zellen kopieren, kann dies dazu führen, dass Ihr Computer jedes Mal überlastet wird, wenn etwas auf einem Blatt geändert/eingegeben wird.
Option 2 - SUMIFS
SUMIFSist eine reguläre Formel, in der Sie ohne Bedenken vollständige Spaltenverweise verwenden können, und meine bevorzugte Option, um mit dieser Situation umzugehen. Diese Formel hat die Form:
SUMIFS(Range to be summed, range for condition 1, Condition 1 check, ..., range for condition n, Condition n check)
Wenn Sie in Ihrem Fall also den Tagesanfang und das Tagesende prüfen möchten, muss Ihre Formel Bedingungsprüfungen durchführen und sollte am Ende ungefähr wie folgt aussehen.
=SUMIFS($B$2:$B$12,$A$2:$A$12,">="&TODAY(),$A$2:$A$12,"<"&TODAY()+1)