エクセル関数式のセル列を一定数とばして参照する方法

少し質問が長くなるのですがエクセル関数に関する質問です。
sheet1,A列には品名がB列には数量が入りこれを「あ群1番地」とします。続いて「い群1番地」の品名がC列、数量がD列と続き「う・え・お群」とさらに続きます。
次の「あ群2番地」が現れるのはK列とL列です。
Sheet2で各群の集計表を作成したいのですが「あ群」集計をA列で品名、B列,C列,D列・・・の列で番地ごとの品名数量を集計する為に名前はSheet１からコピーペーストでA列に貼り付けし、
品名の番地ごとの数量集計をB2には=IF(A2="","",SUMIF(sheet1!$A$2::$A$100,A2,sheet1!$B$2：$B$100))とB3には=IF(A2="","",SUMIF(sheet1!$K$2:$K$100,A2,sheet1!$L$2：$L$100))さらにB4には6列跳んだセル番地が入ることになるのですが、
sheet1でこの番地が今後増えた場合、sheet2集計表の関数式を毎回入力修正するのではなくセルB2の関数式をオートフィル機能で簡素化する方法がありましたらご教授願えないでしょうか。
宜しくお願い致します。

No.1 回答者： ngkkkkk 回答日時： 2017/07/25 06:55

INDIRECT、ADDRESS、ROW、COLUMN、OFFSET関数を組み合わせるとできると思います。

複雑なので関数を分解した考え方だけとりあえず
=SUMIF(①,A2,Ⅰ)　ー他シート参照範囲の合計値
①…OFFSET(②,0,0,1,100)　ー他シートの参照範囲(品名)
Ⅰ…OFFSET(Ⅱ,0,0,1,100)　ー他シートの参照範囲(数量)
②…INDIRECT("Sheet1!"&③)　ー他シートの参照(品名)
Ⅱ…INDIRECT("Sheet1!"&Ⅲ)　ー他シートの参照(数量)
③…ADDRESS(1,((COLUMN()-2)*10)+1)　ー他シートのセル番地(品名列先頭)
Ⅲ…ADDRESS(1,((COLUMN()-2)*10)+2)　ー他シートのセル番地(数量列先頭)

机上なので若干間違いあると思いますが、こんな感じで考えていけばできると思われ…ます。

この回答へのお礼

遅くなりました。ありがとうございます。早速試してみます。

お礼日時：2017/07/25 12:08

No.2 回答者： ngkkkkk 回答日時： 2017/07/25 06:57

すみません、INDIRECT関数は要らないですね

No.3 回答者： d-q-t-p 回答日時： 2017/07/25 16:00

正直なところ 元の表を見直した方がいいと思いますが

=IF(A$2="","",SUMIF(INDEX(Sheet1!$2:$100,,(ROW(C2)-2)*10+1),A$2,INDEX(Sheet1!$2:$100,,(ROW(C2)-2)*10+2)))

この回答へのお礼

ありがとうございます。
職場の書類の一部であり表書式変更は現実的には困難な状況です。
年一回の改善提案に出案しても賛同をどの程度えらっれるか・・・。
書式を崩さず自分なりにアレンジして使用しておりますのでご提案を試してみたいと思います。
ありがとうございました。

お礼日時：2017/07/26 09:51