表計算ソフト「Microsoft Excel」、会計や事務で使用されるものと思いきや、   
私たちエンジニアも、データ分析ツールとして扱える素晴らしいソフトです。

しかし、'=SUM()'や'=AVERAGE()'は使うけど…他の関数はよくわからない。という方、意外と多いのではないでしょうか。

実際に、MicrosoftのExcel関数（機能別）を見ると、   
関数の数は350を超えており、確かに全部の関数を覚えて使いこなすのは大変です。

ただ、使えなくても「そのソフトを使えばできる」という知識は、非常に大きいものです。   
そこで、私が実際に業務で活用している関数を一緒に学んでみませんか。

今回、紹介する関数は以下の3つです。

• OFFSET関数
• INDIRECT関数
• COUNTA関数

これらを用いて、ピボットテーブルの対象範囲を動的に切り替えて、データを視覚的に見やすくしてみましょう。

前提として、次のようなリストを格納したシートが存在するとします。

※今回はGaiXerの「Claude 3 Opus」を用いて、ランダムでサンプルデータを生成してもらったものを使用しています。実際の商品名等が混ざっている可能性がありますが、意図したものではありませんので、ご了承ください。

このリストの、「果物の種類」別で、甘味と酸味のピボットテーブルを作ってみます。

なんというか”それっぽい”ですね。このピボットテーブル、現在は国産・外国産それぞれに対して指定をしていますが、   
プルダウン形式で「国産くだもの」「外国産くだもの」を選んで表示してみよう。   
というのが今回の目的です。

とりあえず、作業するためのシート作りから始まります。

最終的なピボットテーブルを表示するシートを”PivotTable”シートとしましょう。

プルダウンの対象は”pull-down”シートを作って管理するのが私は好みなのでそうします。

”PivotTable”シートを作って、「データ」タブ→「データの入力規則」から、「入力値の種類」を「リスト」にして選択式にします。

私は"pull-down"シートのB列に国産くだもの、外国産くだものとシート名を書いているため、範囲は'='pull-down'!$B:$B'にしました。

※ここで、範囲をB1:B2ではなくB:Bにすることによって、今後、対象のリストを増やした際に拡張が容易になります。

土台はこれでいいでしょう。

今のシート構成は

• PivotTable - C2がプルダウンで選択可能な状態、最終的にこのシートにピボットテーブルを表示する
• pull-down - B列に「国産くだもの」と「外国産くだもの」と書かれたシート
• 国産くだもの - 国産くだものリスト
• 外国産くだもの - 外国産くだものリスト

となっています。つまり、今回は”PivotTable”シートの”C2”セルを参照して、ピボットテーブルの表示を変える必要があります。

今回はピボットテーブルの参照範囲を、”PivotTable”シートの”C2”セルを参照して切り替えるわけですから、

ここの「表または範囲の選択」の範囲を可変にする必要がありますね。

先に私の場合の答えを示しておきましょう。

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

・

・

・

？？？

何かいてるのかよくわかりませんね。

とりあえず、実際にこの式をセルに入力してみましょう。

国産くだもののリストが表示されるようになっています。

プルダウンを”外国産くだもの”に切り替えてみましょう。

表が自動で変わりました。うまく行ってそうです。

では、この式を実際にピボットテーブルに入れてみましょう。

エラーが表示されました...

ピボットテーブルの参照では、関数が想定通りに機能していないようです。

そんな時は、「数式」タブ→「名前の定義」から、この関数式に名前を付けましょう。

• 名前：PivotTableRange
• 適用先：ブック
• 参照範囲：=OFFSET(INDIRECT("'" & PivotTable!$C$2 & "'!$A$1"), 0, 0, COUNTA(INDIRECT("'" & PivotTable!$C$2 & "'!$A:$A")), COUNTA(INDIRECT("'" & PivotTable!$C$2 & "'!$1:$1")))

とすることで、関数式に「PivotTableRange」という名前を与えました。

これで、セルに'=PivotTableRange'と入力すると、先ほどと同様にリストが表示されるはずです。

では、式が直接入れられなかったピボットテーブルの参照範囲に' =PivotTableRange'と入れると。

ちゃんと表示されましたね。

プルダウンを切り替えて、ピボットテーブルを右クリック→「更新」で切り替わります。   
※ピボットテーブルの「更新」の自動化はVBAを使用しないとできないようです。

これで後は見た目を整えれば完成です。（そこは割愛）

発展（対象の追加）

リストを更に追加したい場合、例えば、以下のような「異世界産くだもの」リストを追加した場合

"異世界産くだもの"シートを作成して、リストを格納し、   
"pull-down"シートのB列に"異世界産くだもの"を入力するだけで

このように簡単にピボットテーブル切り替えの対象を増やすことができます。   
ある程度、拡張性を見越して設計すると後でうれしいのは、プログラミングなどでも同様ですね。   
（まあそれが簡単に出来たら苦労はしないんですが...）

さて、ここにきてやっと本題です。

ピボットテーブルを出すために書いたこの式はなんなんだ。という話です。

=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を取得

INDIRECT関数

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

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

例：'=INDIRECT("B3")'

文字列"B3"をセル参照として解釈し、セル B3 の値を取得

→B3セルの値、”12”が返される

COUNTA関数

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

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

例：'=COUNTA(E1:E5)'

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

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

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

=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の可能性を感じてもらえればうれしいです。

また、機会があれば他の関数についても記事を書こうかと思います。

拙く長いブログになってしまいましたが、端的に解説や説明をするむずかしさをひしひしと感じますね。

そこも段々と学んでいきたいものです。