OFFSET関数

特定の範囲で集計する場合は、OFFSET関数を使うと便利です。
指定する部分が多く、若干わかりにくい部分もありますが、予想にも活用できると思います。

OFFSET関数で範囲指定

OFFSET関数は、範囲を指定できる関数です。

=OFFSET(基準のセル,行数,列数,高さ,幅)

基準のセルは、範囲の起点となるセル、行数と列数は、起点から移動する行と列の数、高さと幅は範囲です。

指定するものが複数並んでいるので、とっつきにくい感じですが、基準のセル、行数、列数で指定する範囲の開始場所(左上)を決め、高さと幅で範囲の終了場所(右下)を決めるという形です。

以下の表は、OFFSET関数とCOUNTIF関数を併用したミニの集計で、色付き部分が集計範囲になります。

第6回から第10回までのミニの集計

 ABCDEFGH
1回号番号百位十位一位 数字個数
21191191 02
32988988 10
43194194 21
54105105 32
65592592 43
76792792 50
87708708 60
98234234 70
109243243 81
1110340340 91

集計方法

1.G2からG11に集計する数字を入力。

2.H2に「=COUNTIF(OFFSET($A$2,5,3,5,2),G2)」を入力。

3.H11までコピー。

数式内のOFFSET関数は「OFFSET(起点となる回号のセル,5行下まで移動,3列右に移動,5行,2列),集計する数字の指定)」のような感じの意味になっています。

H2に「=COUNTIF($D$7:$E$11,G2)」を入力してH11までコピーすることでも同様の結果が得られるため、集計範囲を固定するのであれば、無理にOFFSETを使う必要はありません。

しかし、範囲を変えて集計したり、抽せん結果の追加と連動させたりする場合は、OFFSET関数を併用した方が楽です。

以下の表は、先ほどの表に集計する回号の指定を追加したもので、開始と終了に入力する回号と集計範囲が連動するようになっています。

回号を指定して集計

 ABCDEFGH
1回号番号百位十位一位 数字個数
21191191 02
32988988 10
43194194 21
54105105 32
65592592 43
76792792 50
87708708 60
98234234 70
109243243 81
1110340340 91
12      開始6
13      終了10

集計用のH列は、H2に「=COUNTIF(OFFSET($A$2,$H$12-1,3,$H$13-$H$12+1,2),G2)」に変更し、H11までコピーします。

また、任意の回号から指定した回数分遡って集計することもできます。例えば、回号を前回、行数を5とした場合、前回から過去5回のミニを集計します。

終了回号と行数(回数)を指定して集計

 ABCDEFGH
1回号番号百位十位一位 数字個数
21191191 02
32988988 10
43194194 21
54105105 32
65592592 43
76792792 50
87708708 60
98234234 70
109243243 81
1110340340 91
12      終了10
13      行数5

集計用のH列は、H2に「=COUNTIF(OFFSET($A$3,$AE$12-$AE$13,3,$AE$13,2),AA3)」に変更し、H11までコピーします。