FIXER Tech Blog - Development

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

　本記事はFIXERが提供する「cloud.config Tech Blog」に掲載された「業務で使えるExcel関数テクニック − 関数を使った動的な範囲指定のコツ～」を再編集したものです。

本記事はFIXER Advent Calendar 2024（FIXER Advent Calendar 2024 ～ ルーキー編 〜）12月3日の記事です。

前書き

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

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

　実際に、MicrosoftのExcel関数（機能別）を見ると、関数の数は350を超えており、確かに全部の関数を覚えて使いこなすのは大変です。ただ、使えなくても「そのソフトを使えばできる」という知識は、非常に大きいものです。   

　そこで、私が実際に業務で活用している関数を一緒に学んでみませんか。

概要

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

・OFFSET関数
・INDIRECT関数
・COUNTA関数

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

前提と目的

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

国産くだものリスト

ID	販売名	果物の種類	甘味	酸味
1	紅玉の輝き	りんご	3	4
2	サンセットマンゴー	マンゴー	5	1
…	…	…	…	…

外国産くだものリスト

ID	販売名	果物の種類	甘味	酸味
1	Crimson Delight	りんご	4	2
2	Tropical Sunrise	Mango	5	1
…	…	…	…	…

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

国産と外国産くだものリストのピボットテーブル

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

土台を作る

　とりあえず、作業するためのシート作りから始まります。最終的なピボットテーブルを表示するシートを”PivotTable”シートとしましょう。プルダウンの対象は”pull-down”シートを作って管理するのが私は好みなのでそうします。

　”PivotTable”シートを作って、「データ」タブ→「データの入力規則」から、「入力値の種類」を「リスト」にして選択式にします。私は"pull-down"シートのB列に国産くだもの、外国産くだものとシート名を書いているため、範囲は'='pull-down'!$B:$B'にしました。

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

C2をプルダウン選択状態にした状態

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

　今のシート構成は

・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")))

・
・
・
？？？

　何かいてるのかよくわかりませんね。とりあえず、実際にこの式をセルに入力してみましょう。

セルに式を入れたらリストが表示されている

　国産くだもののリストが表示されるようになっています。プルダウンを”外国産くだもの”に切り替えてみましょう。

C2のプルダウン選択を変更したらリストが切り替わっている

　表が自動で変わりました。うまく行ってそうです。では、この式を実際にピボットテーブルに入れてみましょう。

エラーウィンドウ

　エラーが表示されました... ピボットテーブルの参照では、関数が想定通りに機能していないようです。

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

・名前：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を使用しないとできないようです。

外国産くだもののピボットテーブル

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

発展（対象の追加）

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

異世界産くだものリスト

ID	販売名	果物の種類	甘味	酸味
1	龍の息吹	りんご	1	5
2	太陽の恵み	マンゴー	5	1
…	…	…	…	…

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

異世界産くだもののピボットテーブル

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