☰

Excel での時刻/日付の理解

tag-icon • tag-icon microsoft-excel google-spreadsheets
Excel での時刻/日付の理解

Excel にこの簡単なデータ テーブルがあり、今日の合計金額を求める数式が必要です。つまり、TODAY() の日付の 00:00 から 23:59 までのすべての金額の合計になります。どうすればいいでしょうか?

少し複雑になるかもしれませんが、ソース スプレッドシート (下図) に列を追加することはできません。すべての計算は別のシートで行われます。そのため、各行に別の列を追加して、補助数値や中間ステップを追加することはできません。理想的には、すべてを 1 つの大きな数式にして、別のシートの 1 つのセルに入力できる必要があります。

サンプルデータ:

ここに画像の説明を入力してください

答え1

まず最初に、日付がテキストなのか、それとも Excel で日付として表示するようにフォーマットされた数値なのかを判断します。これは非常に重要な区別であり、後者の方が Excel に組み込まれている数式をより多く使用できるため望ましいです。考えられるテストには次のようなものがあります。

  • =ISTEXT(A2)
  • =ISNUMBER(A2)
  • セルの書式設定を「一般」に変更して、表示される内容が変わるかどうかを確認します。

このソリューションでは、日付は数値であると想定されます。数値でない場合は、テキスト日付を数値/Excel 形式に変換する方法に関する質問と回答が多数あります。

Excel での時刻/日付の理解

Excel は日付を整数として保存します。これは 1900 年 1 月 0 日からの日数です。したがって、1 は 1900 年 1 月 1 日です (基準年は 1905 年だと信じていますが、間違っている可能性があります)。したがって、Excel のすべての日付は、私たちが見慣れている方法で表示するために特別な書式が適用された単なる整数です。一方、時間は 1 日の % または 1 日の小数で保存されます。0.5 は半日または昼食を表します。したがって、混合時刻の日付から日付を見つける必要がある場合は、整数部分だけを削除する必要があります。逆に、時間だけが必要な場合は、小数部分だけを削除する必要があります。

オプション 1 SUMPRODUCT

SUMPRODUCTは、配列のような計算を実行する通常の関数です。そのため、範囲参照を大まかにデータに制限し、完全な列/行参照は使用しないでください。後者を実行すると、空のセルに対する無駄な計算が過剰になり、マシンが遅くなる可能性があります。

=SUMPRODUCT((INT($A$2:$A$12)=TODAY())*$B$2:$B$12)

注記: はTODAY()揮発性関数です。つまり、関数に影響がない場合でも、シート上の何かが変更されるたびに再計算されます。非揮発性関数 (ほとんどの通常の関数) は、関数に影響する変更が発生した場合にのみ再計算されます。結果として、揮発性関数を多数のセルにコピーすると、シート上の何かが変更/入力されるたびにコンピューターが遅くなる可能性があります。

オプション 2 - SUMIFS

SUMIFSは、心配することなく完全な列参照を使用できる通常の数式であり、この状況に対処する方法として私が好むオプションです。この数式は次の形式になります。

SUMIFS(Range to be summed, range for condition 1, Condition 1 check, ..., range for condition n, Condition n check)

したがって、あなたの場合、一日の始まりと終わりをチェックしたいので、数式は条件チェックを行う必要があり、最終的には次のようになります。

=SUMIFS($B$2:$B$12,$A$2:$A$12,">="&TODAY(),$A$2:$A$12,"<"&TODAY()+1)

関連情報