Excelでのweeknumまたはweekdayごとの値の合計

このチュートリアルでは、Excelで指定された週番号または平日に基づいて、特定の範囲の値を合計するのに役立つ数式を段階的に示します。

Excelで週番号で値を合計する方法は？
Excelで平日ごとに値を合計する方法は？

Excelで週番号で値を合計する方法は？

以下のスクリーンショットに示すように、特定の週番号が該当する日付に基づいて[金額]列の値を合計するために、XNUMXつの数式を提供します。

一般的な式

=SUMIFS( value_range, helper_column_range, week_number)

=SUMPRODUCT(--(WEEKNUM(date_range+0,1)=week_number),value_range)

Arguments

値の範囲：週番号に基づいて合計する値の範囲。

Helper_column_range：日付から抽出された週番号の範囲。

週番号：値を合計するための基準として使用される週番号。

この式の使い方は？

SUMIFS式とヘルパー列の使用

使用するには SUMIFS 式、最初に、あなたは適用する必要があります ウィークナム ヘルパー列を作成して、日付列の日付に対応する週番号を計算する関数。

=WEEKNUM (serial_num, [return_type])

WEEKNUM関数は、XNUMX年の指定された日付の週番号を返します。

1.この場合、[金額]列の最初の値の横にあるセル（F5）を選択します。

2.以下の式を入力し、を押します。 入力します 最初の日付の週番号を取得するためのキー。結果セルを選択し、そのオートフィルハンドルを下にドラッグして、他の週の数値を取得します。スクリーンショットを参照してください：

=WEEKNUM(C5)

3.合計金額を出力するセルを選択し、以下の数式を入力して、を押します。 入力します 鍵。この結果セルを選択し、オートフィルハンドルを下にドラッグして、他の結果を取得します。

=SUMIFS($E$5:$E$14, $F$5:$F$14, H6)

SUMPRODUCT式の使用

この問題を解決するためにSUMPROFUCT式を使用する場合、ヘルパー列を作成する必要はありません。

1.空白のセルを選択し、以下の数式を入力して、を押します。 入力します キー。

=SUMPRODUCT(--(WEEKNUM($C$5:$C$14+0,1)=G6),$E$5:$E$14)

2.結果セルを選択し、ドラッグします オートフィルハンドル 必要に応じて他の結果を取得します。

式の説明

=SUMIFS($E$5:$E$14, $F$5:$F$14, H6)

WEEKNUM関数を使用して週番号を計算した後、ヘルパー列F5：F14の週番号がH5の指定された週と一致する場合、SUMIFS関数は範囲E14：E6の値を合計します。

=SUMPRODUCT(--(WEEKNUM($C$5:$C$14+0,1)=G6),$E$5:$E$14)

WEEKNUM（$ C $ 5：$ C $ 14 + 0,1）：WEEKNUM関数は、C5：C14の範囲の日付の各週番号を計算し、次のような配列を返します。{1; 1; 5; 6; 6; 9; 11; 11; 13; 14};

{1;1;5;6;6;9;11;11;13;14}=G6：ここでは、配列内の各週番号をG6内の指定された週番号と比較し、新しい配列を返します。{TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE};

-{TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}：これらの1つのマイナス記号は、「TRUE」と「FALSE」を0と1に変換します。ここでは、新しい配列を{1; 0; 0; 0; 0; 0; 0; 0; 0; XNUMX};として取得します。

{1;1;0;0;0;0;0;0;0;0},{1428;2010;2288;1831;2069;1728;1325;1469;1023;1100}：配列内のエントリの各ペアは乗算され、{1428; 2010; 0; 0; 0; 0; 0; 0; 0; 0};を返します。

SUMPRODUCT({1428;2010;0;0;0;0;0;0;0;0})：SUMPRODUCT関数は、配列内のすべての数値を合計し、最終結果1428 + 2010 = 3438を返します。

Excelで平日ごとに値を合計する方法は？

以下のスクリーンショットに示すように、Excelの特定の平日までに[金額]列の値を合計するには、SUMPRODUCT関数をWEEKDAY関数と一緒に適用して実行できます。

この場合、各平日の合計金額を合計する必要があるため、数式を適用する前に、月曜日から日曜日を個別に示す1から7までの数値のヘルパー列を作成します。

一般的な式

=SUMPRODUCT((WEEKDAY(date_range,2)=weekday_number)*value_range)

Arguments

日付範囲：特定の平日を評価する日付範囲。

値の範囲：平日に基づいて合計する値の範囲。

2：番号2は、番号1（月曜日）から7（日曜日）を表します。

曜日番号：値を合計するための基準として使用される平日番号。セル参照または1から7までの数値にすることができます。

この式の使い方は？

1.番号1のセルの横にあるセルを選択します。ここではI6を選択します。

2.以下の数式をコピーまたは入力して、を押します。 入力します 鍵。結果セルを選択し、オートフィルハンドルを下にドラッグして、他の平日の結果を取得します。

=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=H6)*$E$5:$E$14)

注：金曜日など、指定された5つの平日に基づいて値を合計する必要がある場合は、次のように数式に数値XNUMXを直接入力できます。

=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=5)*$E$5:$E$14)

式の説明

=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=H6)*$E$5:$E$14)

平日（$ C $ 5：$ C $ 14,2）：WEEKDAY関数は、1から7までの整数を返し、月曜日から日曜日までの曜日をC5：C14の範囲で表します。{5; 7; 1; 3; 6; 7; 1; 5; 5; 6};

{5;7;1;3;6;7;1;5;5;6}=H6：ここでは、配列内の各曜日をH6内の指定された曜日番号と比較し、新しい配列を返します。{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE};

{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE} * $ E $ 5：$ E $ 14：ここで、TRUEとFalseは1と0に変換され、E5：E14の値と乗算されて、配列が返されます。{0; 0; 2288; 0; 0; 0; 1325; 0; 0; 0};

SUMPRODUCT{0;0;2288;0;0;0;1325;0;0;0}：SUMPRODUCT関数は、配列内のすべての数値を合計し、最終結果2288 + 1325 = 3613を返します。