【エクセル】１０回ずつ測定したデータの平均を、一覧表にする方法

質問

サンプルA～Dの4つの、データを3項目、各10個ずつ取って(下表１）、最後に、下表２のように平均の一覧表にまとめる作業をしています。
ここで、一覧の、Aの項目1のセルには例えば
「=AVERAGE(B11:B20)」と入力するとします。
ここからが、問題なのですが、次のBの項目1には
「=AVERAGE(B21:B30)」としたいのですが、
Aの項目1のセルをコピーアンドペーストすると、
「=AVERAGE(B12:B21)」となりうまくいきません。
すべて手入力でするしか方法はないのでしょうか？
もし、簡単な方法をご存知でしたら、教えてください。

(表1）
　　　項目1　　　項目2　　　項目3
A　1　xxxxx xxxxx xxxxx
A　2　xxxxx xxxxx xxxxx
A　3　xxxxx xxxxx xxxxx
:
D 10　xxxxx xxxxx xxxxx

(表2）
　　　項目1　　　項目2　　　項目3
A　　 xxxxx xxxxx xxxxx
B xxxxx xxxxx xxxxx
C xxxxx xxxxx xxxxx
D xxxxx xxxxx xxxxx

回答No.4

deecyan · ベストアンサー

計算式を入れた　セルの行は　row() 列はcols()　で求められます
後はINDIRECTを使えば　求めることが出来ますよ

仮に
表1　が sheet1 表2が　sheet2 として　表題などが無いとしたら
sheet2 の　A1 に　=AVERAGE(INDIRECT("sheet1!A" & (10*(ROW()-1)+1 & ":A"&10*(ROW()-1)+10)))
B1 に　=AVERAGE(INDIRECT("sheet1!B" & (10*(ROW()-1)+1 & ":B"&10*(ROW()-1)+10)))
C1 に　=AVERAGE(INDIRECT("sheet1!C" & (10*(ROW()-1)+1 & ":C"&10*(ROW()-1)+10)))
と入れて　後は下にコピーするだけです

いきなり　上記のを入れても分からないと思うので
sheet3 のA1　に　="sheet1!A"&(10*(ROW()-1)+1&":A"&10*(ROW()-1)+10)
って入れて　確認した方がいいですね

お礼

deecyanさん

ありがとうございました。
試したところ、うまくいきました。
完璧です。

本当に助かりました。
重ね重ね、ありがとうございました。

回答No.8

imogasi

#4のご回答とほぼ同じだが、少し簡単になる。
A列にデータがあるとして
B1に=AVERAGE(INDIRECT("A"&((ROW()-1)*10+1)&":A"&ROW()*10))
B2以下に式を複写すればよい。
小数テスト済み。

お礼

imogasiさん

確かに、少し簡単になりました。
こういった、少しでも簡単にすることというのは、
とても大事だと思います。

ありがとうございました。

回答No.7

fukkyse

＃６です。

「MIDやLEFTなどの関数を使って、A列に50種類を特定できるID"A"を作成する必要があるのですが、」

必要ありませんでした。B列、43行目で

=SUMIF($A$1:$A$41,A43,$B$1:$B$41)/COUNTIF($A$1:$A$41,A43)

A43には"A*"入力でOKでした。

回答No.6

fukkyse

使用しているのがエクセル2000なのでもっとよいやり方があるのかもしれませんが。
A列にA1と入っているのでしょうか。その場合はMIDやLEFTなどの関数を使って、A列に50種類を特定できるID"A"を作成する必要があるのですが、

SUMIF/COUNTIFでいかがでしょうか。
ちなみにC列、43行目で計算したときの数式はこんな感じです

=SUMIF($A$1:$A$41,A43,$C$1:$C$41)/COUNTIF($A$1:$A$41,A43)

A43には"A"を入力します。そのまま下にコピーしてA列に各項目を入力。以上です。

お礼

fukkyseさん

ありがとうございました。
試したところ、うまくいきました。
この方法、かなりいけますね。

ほかの回答者さんと甲乙つけがたいので、
今回は評価は、早いもの順につけさせていただきました。
ご理解願います。

重ね重ねありがとうございました。

回答No.5

deecyan

#4 です
列は　cols() でなく　COLUMN()　でした　すみません

回答No.3

Yochio

どうもです。

私の環境はExcel2003なので、バージョン違いにより見た目、
機能が変わっている場合があるかもしれません。

--前置き終わり

まず、(表1)のような表をすっぽり覆うように選択してください。
「項目1」の左2セルからD10の「項目3」までです。

そうしたらツールバーから[データ(D)][集計(B)]を選択します。
「リストまたは...」などのダイアログがでても無視です。OKです。

そうすると「集計の設定」ダイアログが出てくると思います。

「グループの基準」は各行をまとめるための基準になる列を表します。
ここではA～D(実際は50コ程度)ごとに分けるので、「(列 A)」を選択してください。
というより、すでに選択済みかと思います。

つぎに「集計の方法」ですが、今回は「平均」になりますかね。

そして「集計するフィールド」では、「項目1」「項目2」「項目3」にチェックを。

下のチェックボックスはどうでもいいです。
そしておもむろに[OK]をおすと、各A～Dの間に太字で集計結果が挿入されると思います。
ただ、このままだと集計行が離れていて使いづらいですよね。

なので、各データ行を隠して集計行のみ残しましょう。
シートの行番号のさらに左側の一番上に[1][2][3]などといったボタンが並んでいないでしょうか？
その[2]を押してみてください。
集計行のみ表示されるようになりました？

これで集計データの取得まではできると思います。
手順にすると長いですが、実際の処理は覚えるとすぐです。

で、ここからが問題なのですが、この集計データだけをふつうにコピーすると
貼り付けたときにデータ行もペーストされてしまいます。
そこで、「非表示行以外を選択」ショートカットキーを使います。
(余談ですが、このショートカットキーについてはokwebで初めて知りました。)
参考URL: http://okweb.jp/kotaeru.php3?q=1079744
データとして使いたいエリアを選択したら、Alt + ;(セミコロン)でデータ行を省くことができます。
あとはコピーするなりペーストするなりご自由に。

こんなかんじでいかがでしょうか？長文失礼いたしました。

お礼

Yochioさん

ご回答ありがとうございました。
ただ、私の技量不足で、思ったように行きませんでした。申し訳ありません。

Alt＋；　は勉強になりました。

ありがとうございました。

回答No.2

tnt

EXCELでは
セルの場所を計算で示すことができないので、
ＶＢを使ってスクリプトを組む必要がありそうですね。

あとは、一回表１の縦横を逆にしてから
（コピーして貼り付けるときに縦横逆を選択）
それの右側に合計を書いて、もう一度縦横を逆にする。
余計な表が３つできてしまいますが、これだと
いくら増えても自動処理できます。

でも、そんな面倒な事をするぐらいなら、
項１はＡを手入力、ＢＣＤはそれをコピーして手直し、
項２以後は横方向にコピー＆ペーストする方が
この場合は簡単だと思います。
Ａ～Ｄの４つしかありませんから。

お礼

tntさん

ありがとうございました。
やっぱりVBですかね。

ちなみにA～Dの４つしかないのは例だからであって、
実際は、５０くらいあります。

回答No.1

syadin

○表２のBの合計にAの合計の式をコピー「=AVERAGE(B12:B21)」
○Bにコピーされた式の「（B12：B21）」の部分を選択（マウスでドラッグ）するとB12からB21に枠が表示されるので
○続けてマウスでB21からB30を選択（マウスでドラッグ）
○enterキーで確定

○以上と同様にC、Dについても目的の合計範囲を設定
○表２の項目２、３の合計は、項目１の合計欄をコピー

で如何でしょうか。

お礼

syadinさん

ご回答ありがとうございました。

ただ、例ではA～Dの４個なのですが、
実務では、おそらく５０個程度で、
がんばれば無理でもないのですが、
かなり大変そうです。

世の中でよくありそうなシチュエーションだと思うのですが、皆さん、どうやってやられているんでしょう。

ご回答ありがとうございました。