[[20191106130936]]　『串刺し集計で集計するシートの範囲を可変にする方』（かさぶた）　ページの最後に飛ぶ

『串刺し集計で集計するシートの範囲を可変にする方法』（かさぶた）

最大4月から翌年3月までの連続した業績集計シートを串刺し集計するのですが、単に存在するシートを全て串刺し集計するのではなく、例えば、8月から11月、10月から1月など、範囲を指定してワンタッチで串刺し集計する方法があったら教えていただきたいのです。よろしくお願いいたします。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 「START」のシートを新規作成
 「END」のシートを新規作成

 この2つのシートの間に希望する月度のシートを挟んで串刺し計算

 例
 =SUM(START!A1:END!A1)
（GobGob） 2019/11/06(水) 13:34

 一応。

 ※各月度のシート、A1セルを串差し計算
 ※各月度のシート、A1セルに「1」が入力されている。
 ※月度は飛び飛びで計算しない。（必ず連続）

 をルールとして。

	A	B	C	D
1	FROM	4	月	TOTAL
2	TO	12	月	9
3

 B1に開始月、B2に終了月

 D2 =SUMPRODUCT(N(INDIRECT(MOD(B1+ROW(INDIRECT("1:"&MOD(B2+8,12)-MOD(B1+8,12)+1))-2,12)+1&"月!A1")))

 ※単月計算の場合はB1とB2 両方同じ月を入力
（GobGob） 2019/11/06(水) 13:50

 一回目の回答

 > =SUM(START!A1:END!A1)

 =SUM(START:END!A1)

 でした。
（GobGob） 2019/11/06(水) 14:48

ありがとうございます。これから試みてみます。結果は改めてご報告します。
（かさぶた） 2019/11/07(木) 10:48

ご報告が大変遅くなりましたが、教えていただいた方法で見事に対応できました。ありがとうございました。
しかしながら、初心者のため、この算式のロジックを理解しようとしてみたのですが、どうしてもわかりませんでした。わからないなりに、求めた結果は得られているのでいいといえばいいのですが、ちゃんと理解したいのです。解説いただけたら、大変ありがたいです。
よろしくお願いいたします。

 D2 =SUMPRODUCT(N(INDIRECT(MOD(B1+ROW(INDIRECT("1:"&MOD(B2+8,12)-MOD(B1+8,12)+1))-2,12)+1&"月!A1")))

（かさぶた） 2019/12/05(木) 11:14

 B1に4、B2に3 、各シート A1に1 として

 ●●●●● 「対象とするシート数」を算出 ●●●●●

 (1) MOD(B2+8,12)-MOD(B1+8,12)+1

 MODは除算した結果の「余り」を出す関数。

 MOD(3+8,12)-MOD(4+8,12)+1 = MOD(11,12)-MOD(12,12)+1 = 11-0+1 = 12

 ●●●●● 「対象とする『シート名』」を算出し、セル参照する ●●●●●

 (2) INDIRECT("1:"&(1)の数式)

 INDIRECTは文字列で指定されたセル参照を返す関数

 INDIRECT("1:"&(1)の結果) = INDIRECT("1:"&12) = INDIRECT("1:12")

 (3) ROW((2)の数式)-2

 ROWは指定されたセルの行番号を返す。

 ROW((2)の結果)-2 = {1;2;3;4;5;6;7;8;9;10;11;12} -2 = {-1;0;1;2;3;4;5;6;7;8;9;10}

 (4) B1+(3)の結果

 B1+{-1;2;3;4;5;6;7;8;9;10} = 4+{-1;0;1;2;3;4;5;6;7;8;9;10} = {3;4;5;6;7;8;9;10;11;12;13;14}

 (5) MOD((4)の結果,12)+1

 MOD({3;4;5;6;7;8;9;10;11;12;13;14},12)+1 = {3;4;5;6;7;8;9;10;11;0;1;2}+1 = {4;5;6;7;8;9;10;11;12;1;2;3}

 (6) INDIRECT((5)の結果&"月!A1")

 INDIRECT({4;5;6;7;8;9;10;11;12;1;2;3}&"月!A1") = INDIRECT({"4月!A1";"5月!A1";"6月!A1";"7月!A1";"8月!A1";"9月!A1";"10月!A1";"11月!A1";"12月!A1";"1月!A1";"2月!A1";"3月!A1"})

 ●●●●● セル参照の配列 を 数値の配列に置換 ●●●●●

 (7) N((6)の結果)

 Nは引数データを数値に変換する関数。セル参照は配列処理できないのでN関数で数値化する。

 N(INDIRECT({"4月!A1";"5月!A1";"6月!A1";"7月!A1";"8月!A1";"9月!A1";"10月!A1";"11月!A1";"12月!A1";"1月!A1";"2月!A1";"3月!A1"})
=N({1;1;1;1;1;1;1;1;1;1;1;1})

 ●●●●● 最後に足し算 ●●●●●

 (8) SUMPRODUCT((7)の結果)

 SUMPRODUCTは配列で足し算する関数

 SUMPRODUCT(N({1;1;1;1;1;1;1;1;1;1;1;1})) = 12

 ※MODを2回ネストするのは「4月～3月で一年」対応のため。
（GobGob） 2019/12/05(木) 12:35 → 12:44 少し見直し

コメント返信：

[ 一覧(最新更新順) ]

[[20191106130936]] 『串刺し集計で集計するシートの範囲を可変にする方』（かさぶた） ページの最後に飛ぶ

[[20191106130936]]　『串刺し集計で集計するシートの範囲を可変にする方』（かさぶた）　ページの最後に飛ぶ