エクセルのテクニック教えてください

質問

データの整理をしているのですが、エクセルが初心者同様に苦手なもので知恵をお借りしたいです。

例えばシート１でＡ１～１０００までのセルにデータが入っていたとして、Ａ１～１００、Ａ１０１～２００、Ａ２０１～３００・・・と、各列１００個ずつ平均を出していきたいのですが、一回一回１００個ずつのセルを選んでいくしかないのでしょうか？
平均はシート２に出したいと考えています。

データの量が膨大でして、実際にはＡ１０～２８００→ＤＬ１０～２８００までびっしり入っているので、データを１００個ずつに横に並び替えるとかはできそうにありません。。。
一回法則を作ってドラッグでバーっとやる方法があれば是非教えていただきたいのですが。。

分かりにくい質問の仕方で申し訳ありません。意味不明でしたら聞いていただけると助かります。

回答No.6

imogasi

初心者ということなので、よくわからないかも知れませんが、
配列数式という仕組みを使うと簡単にできます
A1：A２０に
1
1
1
1
1
2
2
2
2
2
3
3
3
3
3
4
4
4
4
4
とします。わざと簡単にしてあります。
希望はSheet2らしいが簡単のためSheet1の、どこでも良い空き列に（いまG列とする）
=AVERAGE(IF(INT((ROW(A$1:A$100)-1)/5)=ROW()-1,A$1:A$100,""))
と入れて、SHIFT+CTRL+ENTERの３つのキーを同時押しする。
これをG4まで式を複写する（＋ハンドルを引っ張る。
G1:G4に
1
2
3
4
が出ます。
質問例の１００ごとの場合は５－＞１００に変え、A$1：A$１00をA$1：A$2000に変える。１０から始まるときは
A$1:A$100)-1をA$1:A$100)-10に、ROW()-1をROW()-10に変えてください。
上記式の意味は、行の番号－１を１００で割って、０(すなわち１－１００）の時は
その行の数字を対象にし、それ以外は除外して（””）、平均を計算しろということです。０はG列第１行のケース、第２行は行番号２－１＝１は、１０１：２００－１を１００で割った行を対象にするように、ずれて行きます。

回答No.5

chukenkenkou

#2回答者です。

「マクロ」について誤解があるようです。
マクロの登録には、以下のような方法があります。

(1)一連の作業を登録
(a)「ツール」→「マクロ」→「新しいマクロの記録」
(b)マクロ名を必要により変更し、OK
(c)一連の操作を行う
　　→一連の作業を、一度、行う必要があります
(d)「ツール」→「マクロ」→「記録終了」

(2)(1)で登録した内容を元に、マクロを修正(VBAで記述）

(3)一からVBAで作成

一連の作業の内、一部分を行い、それ以降も行えるようにするには、VBAという言語でプログラミングしなければなりません。

式を一行だけ入力し、コピー＆ペーストするだけで自動的に100行ずつ計算できる式は、#1、#4回答者さんの回答で行えます。
何も考えず、式をそのままコピーしてみてください。

↓この式を、何も考えずSheet2に貼り付けてみてください。Sheet1の部分が、シート名です。

=AVERAGE(OFFSET(INDIRECT("Sheet1!A"&((ROW(A1)-1)*100+1)),0,0,100,1))

回答No.4

mshr1962

=AVERAGE(OFFSET(INDIRECT("シート1!A"&((COLUMN(A1)-1)*100+1)),0,0,100,1))
で横にドラッグでコピー
縦方向ならCOLUMN関数の代わりにROW関数を使用してください。

縦横に各列を100行単位で平均なら
=AVERAGE(OFFSET(INDIRECT(ADDRESS((ROW(A1)-1)*100+1,COLUMN(A1),,,"シート1")),0,0,100,1))

回答No.3

BLUEPIXY

=AVERAGE(OFFSET($A$1,100*(ROW()-1),0,100,1))
みたいな感じでやればいいです。
=AVERAGE(OFFSET(基準の位置,相対的な範囲の始まり縦位置,相対的な範囲の横位置,縦範囲サイズ,横範囲サイズ))

回答No.2

chukenkenkou

>シート１でＡ１～１０００までのセルにデータが入っていたとして、Ａ１～１００、Ａ１０１～２００、Ａ２０１～３００・・・と、各列１００個ずつ平均を出していきたい
>平均はシート２に出したい

シート２の平均値を入れたいセルに、以下のような式を入れればOKです。

●A1～A100の平均
=AVERAGE(Sheet1!A1:A100)

●A101～A200の平均
=AVERAGE(Sheet1!A101:A200)

>データを１００個ずつに横に並び替えるとかはできそうにありません

何のために、そういう作業が必要なのでしょうか？

>一回法則を作ってドラッグでバーっとやる方法があれば是非教えていただきたいのですが

手順が決まった作業なら、一連の作業を「マクロ登録」することで、自動化は可能です。

お礼

解答ありがとうございます！

chukenkenkouさんのやりかたが一番自分なりに解釈できそうなのですが、マクロ登録というのが良く分かりません。

「１００個のセルずつ平均するのを繰り返す」とかっていう登録ができるということですよね？
その登録方法というのは難しいのでしょうか？
よろしければ教えていただきたいのですが。

補足

補足なのですが、A1～A100の平均、A101～A200の平均を自分でだしたあと、この二つを選択した状態から下にドラッグしてもA201～A300ではなくA102～A201の平均が求まってしまうのです。
なので、よろしければ100ずつの平均にできるようなマクロ登録（？を教えて頂きたいのです。
無知でもうしわけありません、よろしくお願いします。

回答No.1

mu2011

例えば、以下の方法で如何でしょうか。
シート２の任意列１行目に=AVERAGE(INDIRECT("sheet1!a"&(ROW()-ROW(Sheet1!$A$1))*100+1):INDIRECT("sheet1!a"&ROW()*100))として下方向にコピーして下さい。