FIXER Tech Blog - Development

業務で使えるExcel関数テクニック − 関数を使った動的な範囲指定のコツ

関数式解説

　さて、ここにきてやっと本題です。ピボットテーブルを出すために書いたこの式はなんなんだ。という話です。

=OFFSET(INDIRECT("'" & PivotTable!$C$2 & "'!$A$1"), 0, 0, COUNTA(INDIRECT("'" & PivotTable!$C$2 & "'!$A:$A")), COUNTA(INDIRECT("'" & PivotTable!$C$2 & "'!$1:$1")))

　使われているのは、この3つの関数です。

・OFFSET関数
・INDIRECT関数
・COUNTA関数
　それぞれどのような関数なのか説明していきましょう。

OFFSET関数

=OFFSET(参照, 行数, 列数, [高さ], [幅])

　→(参照)から、(行数)行(列数)列オフセットしたセルを基点として、(高さ)行数(幅)列数の範囲を取得

例：'=OFFSET(A1, 1, 0, 3, 4)'

　A1セルから(参照)下に1行(行数)右に0行(列数)オフセットしたセル(A2)を基点として、3行下まで(高さ)、4列右まで(幅)を取得

→A2:D4を取得

OFFSET関数使用例

INDIRECT関数

=INDIRECT(参照文字列, [参照形式])

→文字列で指定されたセル参照 (参照文字列) を実際のセル参照として評価し、そのセルの値を取得

例：'=INDIRECT("B3")'

　文字列"B3"をセル参照として解釈し、セル B3 の値を取得
→B3セルの値、”12”が返される

INDIRECT関数使用例

COUNTA関数

=COUNTA(value1, [value2], ...)

→指定した範囲や引数内で、**空白でないセル（数値、文字列、論理値、エラー値、空文字列など）** の個数を数える

例：'=COUNTA(E1:E5)'

E1:E5内の空白でないセルの数をカウントする

→E1,E2,E3,E4,E5の5つに数値が入っているため、”5”が返される

COUNTA関数使用例

　以上を理解した上でもう一度見てみましょう。

=OFFSET(INDIRECT("'" & PivotTable!$C$2 & "'!$A$1"), 0, 0, COUNTA(INDIRECT("'" & PivotTable!$C$2 & "'!$A:$A")), COUNTA(INDIRECT("'" & PivotTable!$C$2 & "'!$1:$1")))

　結局よくわかりませんね。では、分解して考えていきましょう

=OFFSET(参照, 行数, 列数, [高さ], [幅])

・参照：INDIRECT("'" & PivotTable!$C$2 & "'!$A$1")
・行数：0
・列数：0
・高さ：COUNTA(INDIRECT("'" & PivotTable!$C$2 & "'!$A:$A"))
・幅：COUNTA(INDIRECT("'" & PivotTable!$C$2 & "'!$1:$1"))

　となる。文章化すると、

'INDIRECT("'" & PivotTable!$C$2 & "'!$A$1")'を基点として、   
高さ'COUNTA(INDIRECT("'" & PivotTable!$C$2 & "'!$A:$A"))' 、   
幅'COUNTA(INDIRECT("'" & PivotTable!$C$2 & "'!$1:$1"))'を取得する。

　このとき、'PivotTable!$C$2'はプルダウンで選択している<シート名>のため、以下を意味しています。

・参照：'INDIRECT(’＜シート名＞’!$A$1)'
・高さ：'COUNTA(INDIRECT(’＜シート名＞’!$A:$A))'
・幅：'COUNTA(INDIRECT(’＜シート名＞’!$1:$1))' 

　つまり、まとめて文章化すると
プルダウンで選択された<シート名>のA1セルを基点として、   
<シート名>に格納された表の高さと幅を取得し、A1セルからその高さと幅の分、つまり表全体を取得する。

　という意味になります。もっと簡単に言えば、「C2で選択したシート名に存在する、単一の表全体を取得している。」と言うことができますね。

あとがき

　長くなってしまいましたが、Excel、データ分析に有効活用できますよ。VBAを使わず関数だけでも実装できて、web版のExcelでも問題なく動きますよ。

　ということを伝えたいだけのブログでした。まわりの記事はプログラミングやクラウドの難しい話をしていて明らかに趣旨が異なりますが… 本ブログを通して、少しでもExcelの可能性を感じてもらえればうれしいです。

　また、機会があれば他の関数についても記事を書こうかと思います。拙く長いブログになってしまいましたが、端的に解説や説明をするむずかしさをひしひしと感じますね。
そこも段々と学んでいきたいものです。

稲永 然／FIXER
稲永 然（いななが ぜん）です。
高専専攻科の機械工学科の伝熱工学の分野から来ました。
趣味は広義の意味でのゲームで、電子からテーブルゲームまで遊んでいます。

■関連サイト

• cloud.config Tech Blogのエントリー
• cloud.config Tech Blog
• FIXER