☰

mm/dd/yy タイプの日付に変換せずに、Excel のシリアル日付を比較するにはどうすればよいですか?

tag-icon • tag-icon microsoft-excel worksheet-function date-time
mm/dd/yy タイプの日付に変換せずに、Excel のシリアル日付を比較するにはどうすればよいですか?

Excel のシリアル日付を表す値がいくつか含まれたテーブルがあります。フィールドを比較しようとして何度も失敗した後、現在はカレンダーの日付ではなくシリアル日付を比較するアプローチをとっています。数式を使用して、データを日別に集計しようとしています。

考慮する：

41021       some data
41021.625   some data
41021.63542 some data
41022       some data
41022.26042 some data
41022.91667 some data
41023       some data
41023.375   some data

望ましい結果:

41021       sum of 41021, 41021.625 and 41021.63542 data
41022       sum of 41022, 41022.26042 and 41022.91667 data
41023       sum of 41023 and 41023.375 data

本質的には、SerialDate.SerialTime のすべてのインスタンスについて、その日付の *.SerialTime に関係なく、SerialDate.* に関連付けられたデータ値を合計します。

追加の日付列を作成してフォーマットすることでこれを行う方法はわかりますが、=TEXT(<DateField>,"mm/dd/yyyy")数式でこの「変換」を処理できるソリューションを探しています。例：SUMIF((TEXT(<dateRange>,"yy/mm/dd"),=(TEXT(<dateField,"yy/mm/dd")),<dataRange>

意味が分かりますか？誰か何かアイデアはありますか？

ありがとう

答え1

シリアル時刻が列Aにあり、関連データが列Bにあると仮定すると、次の数式は

=IF(INT($A1)=$A1,SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

C1 に入力して列に拡張すると、列 C の最初の整数時間値の後ろに合計が表示されます。必要なのがこれだけであれば、完了です。

しかし、間に空白行を入れずに合計を上下に並べる必要がある場合は、配列数式参照ラベル用の追加列を挿入します。列 C で、データを含む行のセルを選択し (つまり、データが行 1 から 100 まで入力されている場合は、C1 から C100 を選択します)、数式バーに次の数式を挿入します (セルに直接入力しないでください)。

=IFERROR(SMALL(IF(INT($A1:$A100)=$A1:$A100,$A1:$A100,""),ROW()),"")

動作させるには、配列数式として保存する必要があることに注意してください。数式入力をCtrl+Shift+Enter(ではなくEnter、正しく入力した場合、数式は中括弧で囲まれて表示されます) で確定します。また、数式は空のセルに 0 を返すため、必要に応じて範囲を調整する必要があります。最後に、配列数式は大規模なデータセットではかなり遅くなることに注意してください。

完了したら、追加できます

=IF($C1<>"",SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

列 D に追加すると次のようになります。

配列数式の動作

答え2

日ごとにデータを要約する場合（または日別の小計を取得する場合）、この配列数式を試すことができます。数式バーにこれを入力し、Ctrl+ Shift+を押しますEnter。

=SUM((INT(datetimes)=D3)*somedata)

どこ

  • datetimesシリアル日付を、またはGeneral形式mm/dd/yyyy h:mmで入力します。
  • somedata加算する数字がいくつか含まれています（下の例ではB2:B25）

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

Excel では、日付は整数 (シリアル値) として、時間は小数 (24 時間の小数) として認識されます。たとえば、06/01/2012 3:00:00 PMは と等しくなります41061.625(セルの書式を に変更すると、このようになりますGeneral)。整数は日付を表し、 はまたはまたは を0.625表します。DATE セグメントを抽出するには、 を使用できます。3:00 pm15:0015/24INT(A1)

答え3

これを行う最も簡単な方法は、Excel (2007/2010) に組み込まれているテーブル機能とピボット テーブル機能を使用することです。

  1. データから Excel テーブルを作成します。
  2. Excel の「INT」関数を使用して日付のヘルパー列を作成します。これにより、すべての値が適切な日 (日付/時刻フィールドの整数部分) にロールダウンされます。フィールドの時刻値のヘルパー列も簡単に作成できます。
  3. データ テーブルをソースとして使用してピボット テーブルを作成します。
  4. 行ラベルをヘルパー日付として設定し、ピボット値をデータ ポイントとして設定します (列ラベルとして時間フィールドを追加することもできます)。

その後、テーブルを更新するたびに、ピボット テーブルを更新して現在の小計を表示できます。さらに、他の集計関数 (最小、最大、平均) を選択したり、データ テーブルの値に影響を与えずにピボット テーブルのラベルを従来の日付形式でフォーマットしたりすることもできます。

Excel 2003 を使用している場合は、テーブルの代わりにリスト関数を使用して同じことを実現できます。

Excel 2010 の例

エクセル2010

Excel 2003の例

エクセル2003

関連情報