こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

回答受付中の質問

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

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

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

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

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

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

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

投稿日時 - 2017-04-17 23:24:39

QNo.9318770

すぐに回答ほしいです

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(9)

ANo.9

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

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

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


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

投稿日時 - 2017-04-20 08:42:45

ANo.8

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

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

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

条件(グループ分け?)が変わる可能性があるのであれば
作業列2の方が後々で解り良いのかなぁ、とぼんやり思います。
もちろん、IF関数で作業列1でも対応できます。

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

投稿日時 - 2017-04-19 12:55:27

ANo.7

お邪魔します

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

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

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

でも、駄目て
とほほ

出来なくて
ご免なさい

投稿日時 - 2017-04-18 20:52:42

ANo.6

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

作業列を使います(添付図をなんとなくご参照ください)。
今、A列に値が入った、同じレイアウトの表が二つあります。
データ部はそれぞれ、2行目と12行目~8行目と18行目にあります。
なので、1の位の数字で分類できそうです。
そこで作業列(B列)を設け、ROW関数・MOD関数を使います。
 B2セル:=MOD(ROW(),10)
 ※以下、行方向にコピー
これで、行番号を10で割ったときの余りが作業列に用意されました。
あとはお気付きでしょう。
実際の集計部分(D列)にはSUMIF関数を使います。
 D2セル:=SUMIF(B:B,ROW(),A:A)
 ※以下、行方向にコピー
これで集計が完了しました。

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

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

投稿日時 - 2017-04-18 17:55:03

お礼

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

投稿日時 - 2017-04-19 00:01:45

ANo.5

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

投稿日時 - 2017-04-18 15:08:15

お礼

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

投稿日時 - 2017-04-18 23:45:14

ANo.4

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

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

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

投稿日時 - 2017-04-18 09:44:37

お礼

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

投稿日時 - 2017-04-18 23:32:38

ANo.3

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

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

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

投稿日時 - 2017-04-18 08:54:48

お礼

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

投稿日時 - 2017-04-18 13:11:32

ANo.2

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

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

投稿日時 - 2017-04-18 07:45:05

お礼

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

投稿日時 - 2017-04-18 23:54:03

ANo.1

昔は、出来た
の、ですが

例えば、
=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

投稿日時 - 2017-04-18 02:06:49

お礼

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

投稿日時 - 2017-04-18 08:01:34