過去全体や過去5回のような決まった回数で集計する場合は、COUNTIF関数を使った集計で事足りますが、変動する範囲を対象とした集計にはOFFSET関数が重宝します。
OFFSET関数で範囲指定
OFFSET関数は範囲を指定できる関数で、以下の形式で入力します。
数式内の「基準のセル」は、範囲の起点となるセル、「行数」と「列数」は、起点のセルから移動する行と列の数、そして「高さ」と「幅」は範囲です。
例えば、ナンバーズ3の第6回から第10回までの範囲でミニの数字を集計する場合は、集計対象となるミニの範囲をOFFSET関数で指定し、COUNTIF関数で集計します。以下の表の色付き部分が集計対象の範囲です。
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | 回号 | 番号 | 百 | 十 | 一 | 数字 | 個数 | |
| 2 | 1 | 191 | 1 | 9 | 1 | 0 | 2 | |
| 3 | 2 | 988 | 9 | 8 | 8 | 1 | 0 | |
| 4 | 3 | 194 | 1 | 9 | 4 | 2 | 1 | |
| 5 | 4 | 105 | 1 | 0 | 5 | 3 | 2 | |
| 6 | 5 | 592 | 5 | 9 | 2 | 4 | 3 | |
| 7 | 6 | 792 | 7 | 9 | 2 | 5 | 0 | |
| 8 | 7 | 708 | 7 | 0 | 8 | 6 | 0 | |
| 9 | 8 | 234 | 2 | 3 | 4 | 7 | 0 | |
| 10 | 9 | 243 | 2 | 4 | 3 | 8 | 1 | |
| 11 | 10 | 340 | 3 | 4 | 0 | 9 | 1 |
H列に集計表がありますが、H2には以下を入力しています。
以下のリンク先でエクセルファイルを閲覧可能です。※配布中のエクセルファイルについて
この式のOFFSET関数では起点に「$A$2」、行数に「5」、列数に「3」、高さに「5」、幅に「2」を指定しています。
起点「A2」から下に5行移動し、さらに右に3列移動したセル（D7）が指定する範囲の左端になり、そこから高さ5行、幅2列の範囲が集計対象になります。
ここではH2の内容を11行目までコピー（セルの右下をドラッグ）できるようにA2を絶対参照にしています。
COUNTIF関数では範囲がOFFSET関数で指定した範囲、検索条件がG列のセルにある値（数字）となります。
H2に以下の数式を入力してH11までコピーすることでも同じ結果が得られるため、集計範囲を固定するのであれば、COUNTIF関数だけで済みます。
ただ、集計範囲を任意の場所に変更できるようにする場合は、OFFSET関数を使った方が便利です。
始点と終点で指定
以下の表は、先ほどの集計表に回号の指定を追加したもので、始点と終点に回号を入力すると、その範囲の集計結果が表示されます。始点に6、終点に10を入力すると、先ほどの集計表と同じ結果になります。
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | 回号 | 番号 | 百 | 十 | 一 | 数字 | 個数 | |
| 2 | 1 | 191 | 1 | 9 | 1 | 0 | 2 | |
| 3 | 2 | 988 | 9 | 8 | 8 | 1 | 0 | |
| 4 | 3 | 194 | 1 | 9 | 4 | 2 | 1 | |
| 5 | 4 | 105 | 1 | 0 | 5 | 3 | 2 | |
| 6 | 5 | 592 | 5 | 9 | 2 | 4 | 3 | |
| 7 | 6 | 792 | 7 | 9 | 2 | 5 | 0 | |
| 8 | 7 | 708 | 7 | 0 | 8 | 6 | 0 | |
| 9 | 8 | 234 | 2 | 3 | 4 | 7 | 0 | |
| 10 | 9 | 243 | 2 | 4 | 3 | 8 | 1 | |
| 11 | 10 | 340 | 3 | 4 | 0 | 9 | 1 | |
| 12 | 始点 | 6 | ||||||
| 13 | 終点 | 10 |
集計表のH2には以下を入力しています。
終点と回数で指定
集計範囲の終点となる回号から指定した回数分遡って集計することもできます。
例えば、終点を前回、回数を5とした場合、前回から過去5回の範囲でミニの構成数字を集計します。終点に10、回数に5を入力すると、先ほどの集計表と同じ結果になります。
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | 回号 | 番号 | 百 | 十 | 一 | 数字 | 個数 | |
| 2 | 1 | 191 | 1 | 9 | 1 | 0 | 2 | |
| 3 | 2 | 988 | 9 | 8 | 8 | 1 | 0 | |
| 4 | 3 | 194 | 1 | 9 | 4 | 2 | 1 | |
| 5 | 4 | 105 | 1 | 0 | 5 | 3 | 2 | |
| 6 | 5 | 592 | 5 | 9 | 2 | 4 | 3 | |
| 7 | 6 | 792 | 7 | 9 | 2 | 5 | 0 | |
| 8 | 7 | 708 | 7 | 0 | 8 | 6 | 0 | |
| 9 | 8 | 234 | 2 | 3 | 4 | 7 | 0 | |
| 10 | 9 | 243 | 2 | 4 | 3 | 8 | 1 | |
| 11 | 10 | 340 | 3 | 4 | 0 | 9 | 1 | |
| 12 | 終点 | 10 | ||||||
| 13 | 回数 | 5 |
H列のH2には以下を入力しています。
今回は例としてナンバーズのミニを取り上げましたが、ロトの抽せん数字の集計にも応用できます。
また、右へ移動する数を調整して特定の列だけ集計することもできるので、ボーナス数字だけを集計する時にも便利だと思います。