当月まで累計計算 特定のセルを除外

Question

下記のような表があり、特定の月までの累計が計算される式を作成したいのですが、途中に上期合計数(E列)が入っています。

上期合計の数値を除外して累計を計算する方法はありますでしょうか。

特定の月は別シートに入力するため、例えば（表!T5）にあるとします。

【例】１０月まで累計を計算したい場合

■シート１

| A列 | B列 | C列 | | | 受注累計① | 受注残 | | --- | --- | --- | | A店 | 5 | 5 |

■シート2

	A列	B列	C列	D列	E列	F列	G列	H列

	受注・見込	4月	5月	6月～9月	上期合計	10月	11月	12月
A店	台数	1	0	3	4	1	3	2

シート１のB2セルに、シート2の4月～10月の累計を入力したいですが、上期合計のE2セルがあるためうまく累計が計算されません。

特定の月は別シートに該当月を入力し、自動的にその月に合わせた累計が入力されるようにしたいです。

また該当月以降の台数の累計も計算したいです。

(例)10月までの場合・・・4～10月までの累計、11～3月までの累計

宜しくお願い致します。

Answer 1

表!T5の部分は Adeyemi Olayemi Samuel さんの回答にあるように MONTH 関数を使用すれば良いと思います。

Answer 2

ご解答ありがとうございます。

該当月を「2023/10/1」と入力しているため、うまく計算されませんでした。。

※ほかのセルで「10」と入力してみたら、正しい数値が計算されました。

IF(表!T5<=3,表!T5+9,表!T5-3)の部分を日付入力で計算される関数に置き換えることは可能でしょうか。

むずかしければ別のセルに数字のみに置き換える関数を入力し、そこから引っ張ってこようと思います。

恐れ入りますが、今一度ご教授いただければ幸いです。

宜しくお願い致します。

Answer 3

TSUTSUMI Akina (堤 亜希奈)さま

Adeyemi Olayemi Samuelさまの回答もありますが、10 月までの累積合計を計算する場合 で「4月から9月までの累積合計」を求めていますが、10月分を含む場合の例がないので、別のやり方を考えてみました。

まず、質問のシート2のD列に6月～9月のデータが格納されているようですが、これだと集計が難しくなるので下記のような列で計算式を作成しています。

A列 支店

B列 項目名

C列 4月

D列 5月

E列 6月

F列 7月

G列 8月

H列 9月

I列 上期合計

J列 10月

K列 11月

L列 12月

M列 1月

N列 2月

O列 3月

P列 下期合計

Q列 年合計

Officeのバージョンが不明なのでとりあえず、Microsoft 365 を前提にします。

【Microsoft 365の場合】

Microsoft 365では下記の関数が追加されているので、わかりやすく記述できます。

HSTACK 関数 - Microsoft サポート

　　配列を横並びで結合する。（４月から３月まで連続した列にする）

TAKE 関数 - Microsoft サポート

　　指定数分要素を取り出す。

DROP 関数 - Microsoft サポート

　　指定数分要素を除外する。

≪受注累計①≫

=SUM(TAKE(HSTACK(シート2!$C2:$H2,シート2!$J2:$O2),1,IF(表!T5<=3,表!T5+9,表!T5-3)))

≪受注残≫

=IF(表!T5=3,0,SUM(DROP(HSTACK(シート2!$C2:$H2,シート2!$J2:$O2),0,IF(表!T5<=3,表!T5+9,表!T5-3))))

Microsoft 365でない場合は下記のようにすると集計できます。

対象の列だけを集計する。 

≪受注累計①≫

=SUMPRODUCT((COLUMN(シート2!$C2:$O2)<>9)*(COLUMN(シート2!$C2:$O2)<=IF(表!T5<=3,表!T5+12,IF(表!T5<=9,表!T5-1,表!T5)))*シート2!$C2:$O2)

≪受注残≫

=SUMPRODUCT((COLUMN(シート2!$C2:$O2)<>9)*(COLUMN(シート2!$C2:$O2)>IF(表!T5<=3,表!T5+12,IF(表!T5<=9,表!T5-1,表!T5)))*シート2!$C2:$O2)

≪受注残≫は実際は下記のようにして計算した方が簡単です。

=SUM(シート2!$C2:$H2)+SUM(シート2!$J2:$O2)-シート１!B2

以上、参考になれば幸いです。

Answer 4

その場合 シート2で、各月の横に、月が目的の範囲内にあるかどうかを確認する新しい列を作成します。たとえば、4 月から 10 月までを計算する場合は、ヘルパー列で次のような数式を使用できます。

エクセル =IF(AND(MONTH(DATE)>=4, MONTH(date)<=10), 1, 0)

Replace `date` with the cell that contains the date for that row. This formula will return `1` for months from April to October and `0` otherwise.

Then Calculate the cumulative total, In the cell where you want to display the cumulative total (B2 in Sheet 1), use the `SUMPRODUCT` function to calculate the cumulative total:

```excel
=SUMPRODUCT((Sheet2!$B$2:$H$2)\*(Sheet2!$I$2:$O$2))

Replace $B$2:$H$2 with the range of cells that contains the order numbers and $I$2:$O$2 with the range of cells in the helper column. This formula multiplies each order number with the corresponding value in the helper column (either 1 or 0) and then sums up these products.

This method allows you to calculate the cumulative total for a specific range of months while excluding certain cells⁶. Please replace date, $B$2:$H$2, and $I$2:$O$2 with your actual cell ranges.

Remember, Excel formulas are sensitive to changes in worksheet names, cell references, and ranges. So, make sure to adjust them according to your actual data layout.

Regards Adeyemi

この回答は自動翻訳されています。文法や表現の誤りが発生した場合はご容赦ください。

Answer 5

こんにちは

私はAdeyemiです、そして私はあなたの質問を喜んでお手伝いします。

私はあなたの要件を理解しています。上半期の合計を除いた特定の月までの累計(E列)を計算し、関連月以降の工数の累計も計算します。

これを行う方法は次のとおりです。

1.特定の月までの累積合計(上半期の合計を除く):絶対セル参照と相対セル参照の組み合わせで「SUM」関数を使用できます。たとえば、データがセル B2 から始まり、10 月までの累積合計を計算する場合 (たとえば、10 月がセル G2 にあるとします)、セル G3 に次の数式を入力できます: '=SUM($B$3:F3)'。これにより、4月から9月までの累積合計が得られます。

2.特定の月以降の累積合計:同様のアプローチを使用できますが、今回は10月の翌月から開始します。11 月がセル H2 にある場合は、セル H3 に次の数式を入力できます: '=SUM(H3:$M$3)'。これにより、11 月以降の累計が表示されます。

実際のデータレイアウトに従ってセル参照を調整してください。これらの数式には、列Eの「上半期の合計」は含まれないことに注意してください。

これがお役に立てば幸いです

コミュニティに還元する。この返信で問題が解決したかどうかを示すことで、この問題を抱えている次の人を助けます。下の [はい] または [いいえ] をクリックします

よろしく アデエミ

この回答は自動翻訳されています。文法や表現の誤りが発生した場合はご容赦ください。