エクセル グループを作って集計

質問

ご教示ください。
エクセルのあるシートに、(1)～(10)の、10個の同じレイアウトの表が、縦に並んでいます。

その中から、5個選んだグループを作り、グループの合計の表を作りたいです。(表全体をそのまま重ねて串刺しのように合計するイメージです)

今後グループ分けを変更する可能性があり、それにも対応できるようにしたいと思っています。

同じフォーマットの表が縦に同じように並んでいるので、抽出するだけなら、OFFSET関数でできました。
たとえば、OFFSET値を、(1)は100、(2)は200、…などと指定しておけば、値の参照先が指定できます。

ただ、これを単純に合計することを考えた時、表のマス目それぞれに
=OFFSET() +OFFSET() +OFFSET() +OFFSET() +OFFSET()
となり長くなります。
もっとシンプルにできる方法はありますでしょうか？
あるいは根本から違う方法がよいのでしょうか。

どうぞよろしくお願い致します。

回答No.9

tsubu-yuki

> 他からデータを入れ込むシステムがすでに構築されており
> (これはプロの手を借りた模様です)

うん、なんだか大変なご様子ですね。
私も５番さんと同意見、とてもプロの仕事とは思えない構成です。
※私はノンプロなので、プロの標準はよくわかりませんが(笑)。

ま、その構成（仕様）はすぐには変えられないとのこと。
一時ファイルを作り、集計に必要な分だけそちらにコピー、
そこに串刺し、というのが（多分）最も楽な方法です。
ただ、数が多くなると手間もその分食いますから、
コレを解消すべく、私も個人的にはＶＢＡを推したいところです。
機会があればお勉強なさっても損は無いと思いますよ。

> 実際に使用する人は私以上に使い慣れていない人
であればなおのこと、邪魔な列が増えたとしても
読み解きやすい関数で半ば無理やりにでもまとめたほうが
良いのかもしれませんね。
作った後はメンテナンスが発生しますから。

回答No.8

tsubu-yuki

複雑な関数式を考えるのが苦手な６です。

失礼しました。
> つまり集計したくない表については、作業列に値が出ないようにする
を忘れておりました。

仰る通り、作業列にＩＦ関数などで表示しない方式が一つ。
もう一つは、作業列を増やして集計対象（もしくは対象外）にフラグを立て、
ＳＵＭＩＦＳ関数で複数条件に対応する、という手段が考えられます。

条件（グループ分け？）が変わる可能性があるのであれば
作業列２の方が後々で解り良いのかなぁ、とぼんやり思います。
もちろん、ＩＦ関数で作業列１でも対応できます。

多分、わかりやすい式が一番ですよ。
と、強がってみます(笑)。

回答No.7

Nouble

お邪魔します

因みに、
=SUMPRODUT(OFFSET(B1,{1,2,3},1,1,1)
=SUMPRODUT(OFFSET(INDIRECT("B "&{1,2,3}),1,1,1,1)
とかも、
OFFSETを
PF9で、評価すると
ちゃんと、値か
複数、出てくるのに

例え
INDEXを、挟んでも
駄目…　ですよね？

下の式、なんて
OFFSETの
サポートヘルプにも
同類の、もの
載ってるのに！！
ですよ？

でも、駄目て
とほほ

出来なくて
ご免なさい

回答No.6

tsubu-yuki

んー、ＯＦＦＳＥＴ関数で「規則的に」探せるのであれば、
こんなやり方でももしかしたらいけるかもしれません。

作業列を使います（添付図をなんとなくご参照ください）。
今、Ａ列に値が入った、同じレイアウトの表が二つあります。
データ部はそれぞれ、２行目と１２行目～８行目と１８行目にあります。
なので、１の位の数字で分類できそうです。
そこで作業列（Ｂ列）を設け、ＲＯＷ関数・ＭＯＤ関数を使います。
　Ｂ２セル：=MOD(ROW(),10)
　※以下、行方向にコピー
これで、行番号を１０で割ったときの余りが作業列に用意されました。
あとはお気付きでしょう。
実際の集計部分（Ｄ列）にはＳＵＭＩＦ関数を使います。
　Ｄ２セル：=SUMIF(B:B,ROW(),A:A)
　※以下、行方向にコピー
これで集計が完了しました。

ただし、条件として
・同じレイアウトの表が
・規則的に並んでいる
ことが必須です。

作業列が入る分、見た目にシンプルとはいきませんが・・
式はかなり簡略化されるものと思われます。

お礼

tsubu-yuki様
再度のご回答、ありがとうございます。
なるほど…頭の体操のようですね。
規則的に探すのは問題ないです。グループに入っていない、つまり集計したくない表については、作業列に値が出ないようにする、などの対応になるわけですね。

回答No.5

imogasi

Ｅｘｃｅｌには同形式のシートの間のセルごと集計は、串刺し演算＝３Ｄ集計があり、だれもこれを使おうと思うと思います。
そういう設計にしなかったのは片手落ちです。
もうひとつ、ピボットに持ち込む方法が、そのあとの、やりたいことをするうえで優れています。関数などでやろうとするのは、ちゃんちゃらおかしい。
「できるビジネスマン」はピボットテーブルを使っていると思いますが。
参考　http://tonari-it.com/stop-growth/
ＶＢＡができれば、今の見出し部分や表題を省いて、「テーブル」形式や「リスト」形式（一般の用語ではなく、エクセルの術語です）にする方法もありそうです。
すなわち、別の１シートに、２個目の表の塊分は、データだけ移す、ために
（Ａ）望みのセル範囲（グループ）を割り出す手がかり
わざと今から、適当な行（最終行のあと、開始行の前行など）に簡単な目印になる文字列などを入れる手もある。
（Ｂ）ピボットでは、利用には、余分になる列見出し、次の表間の空白部、を行削除する。
見出し行数は、どの表（グループ）も同じですか。

お礼

imogasi様
ご指摘ごもっともです。しかしながら、このExcelを作った人間はまた別の人で、他からデータを入れ込むシステムがすでに構築されており(これはプロの手を借りた模様です)、すぐには仕様を変えることができないという事情がございます。
そのため、無理やりに、ちゃんちゃらおかしい方法で対応するしかございません。
質問内容からもご推察されるかと思いますが、私も調べながらやっと簡単な関数を理解する程度でして、ピボットテーブルもVBAも使う機会がほとんどないという人間です。更に言うと、このExcelを実際に使用する人は私以上に使い慣れていない人です。
求めているのは、小手先でも良いので、求めている結果が素人にもわかりやすく表される方法といった感じなのです。すみません。

回答No.4

mt2015

例えば、A列にデータがあるとして、単純な合計なら以下の様に書く事が出来ます。
＃面倒なのでOFFSET値が100と200の2つのグループの合計の例です。

=SUM(OFFSET(INDEX(A:A,100),,,10,))+SUM(OFFSET(INDEX(A:A,200),,,10,))

グループを変更する可能性があるのでしたらOFFSET値を外出しにした方が良いでしょう。

お礼

mt2015様
ありがとうございます。そうなんです、グループを変える可能性があります。参考にして、また少し試してみます。出を動かさないとなかなかすぐに理解できず、すみません。

回答No.3

tsubu-yuki

お言葉をお借りするなら・・
「根本的に」使い方を見誤っているような気がします。
（間違っている、とは言いませんが。）

> あるいは根本から違う方法がよいのでしょうか。
表ごとにシートを分けて、串刺し計算させるのが
もっともシンプルだと思いますよ。
その場合、集計対象にする「５個のシート」を連続させて、例えば
　　集計シートのＢ２セル：=SUM(Sheet1:Sheet5!B2)
　　　　　　　　　　　　　※シート名は任意
としてやると良いかと思われます。

とりあえず、参考までに。

お礼

tsubu-yuki様
ご回答ありがとうございます。
やはり串刺しがシンプルな方法のようですね。
補足いたしますと、すでに大量のシート、データが詰め込まれたExcelブックでして、Excelの扱いに(私以上に)慣れていない人が扱っています。
そのため、シートを分けて(増やして)串刺し演算する以外の方法を探しておりました。

回答No.2

bunjii

>もっとシンプルにできる方法はありますでしょうか？
INDEX関数の方が簡単になるかも知れません。
各表にdata1、data2、data3、・・・・等の名前を付ける。
=INDEX(data1+data2+data3+data4+data4,ROW(A1),COLUMN(A1))
但し、計算対象範囲（矩形の範囲）に文字列のデータが有るとエラーにななります。

>あるいは根本から違う方法がよいのでしょうか。
串刺し集計を行う場合は同じ配列のSheetを使うべきです。
データ入力の都合が有るのでしたら集計用のSheetを用意して活用すれば良いでしょう。
=SUM(Data1:Data5!A1)

お礼

bunjii様
お礼を書き込んだつもりが反映されておらず、失礼いたしました。名前の定義と、INDEX関数ですと、おっしゃるとおりだいぶ簡単になりました。
ありがとうございます。

回答No.1

Nouble

昔は、出来た
の、ですが

例えば、
=SUMPRODUCT(INDIRECT("C"&{2,3,4}))
で、すら
駄目で

幾ら、
様々に、試しても
参照関数から、集計関数への、
関数間、値受け渡しが
実現、しません

以前、
INDEXの、挙動が
突然、変わった
時点が、あった
の、ですが

其の、辺りで
一緒に

仕様
と、いう名の
バグに
墜ちた
ので、しょうか？

此、
回避、できる
ので、しょうか？

=SUMPRODUCT(
OFFSET($B$1,
SMALL(
INDEX(
(
($E$2=$B$2:$B$40)+
($F$2=$B$2:$B$40)+
($G$2=$B$2:$B$40)+
($H$2=$B$2:$B$40)+
($I$2=$B$2:$B$40)
)*
ROW($B$1:$B$39),,),
39+
1-
ROW(
INDIRECT("A1:A"&SUMPRODUCT(
($E$2=$B$2:$B$40)+
($F$2=$B$2:$B$40)+
($G$2=$B$2:$B$40)+
($H$2=$B$2:$B$40)+
($I$2=$B$2:$B$40)
)
)
)
),
1,
1,
1
)
)

参考URL：

https://1drv.ms/f/s!AjviygfJDgV_kWuw4Zkzve3qvu3m

お礼

Nouble様
ありがとうございます。仕様が変わることもあるのですね。あまり関数に詳しくなくて…すみません、後ほど読み解いてみたいと思います。取り急ぎ御礼まで。