INDIRECT関数の「動的参照」の世界
山本式和音番号シリーズも、いよいよシステムの心臓部、数式構築のフェーズへと突入します。
これまでの記事で、その膨大な和音体系を格納するための「音階データベース」と「入力データベース」の設計図を完成させました。
↓↓↓最初の記事です。まだの方は是非↓↓↓
和音変換システムの「心臓部」を理解する
今回の目的は、入力された山本式和音番号の各要素を基に、Excelが自動的に対応する「音階表」を判別し、そこから和音の構成要素を正確に引き出す仕組みを構築することです。
この「音階表を動的に切り替える」という高度な参照こそが、今回の記事の主役となります。
そして、その中心的な役割を担うのが、Excelの中でも特に強力でありながら、その複雑さゆえに敬遠されがちなINDIRECT関数です。
この関数を理解し、INDEX関数と組み合わせることで、和音変換システムは、適切なデータを自在に操れるようになります。
この論理的な仕組みを、一つずつ丁寧に解き明かしていきましょう。
STEP1：INDIRECT関数の基本を理解する 「参照文字列」という概念
まず、今回の最重要関数であるINDIRECTが、どのような能力を持っているのか、その基本性能から確認します。
文字列がセル参照に変わる
INDIRECT(参照文字列)
INDIRECT関数は、その名の通り「間接的に」セルを参照する関数です。
この関数の最もユニークな特性は、引数として渡された「文字列」を、Excelが認識可能な「セル参照」や「範囲名」に変換する点にあります。
例えば、セルA1に B5という文字列が入力されているとします。
この時、別のセルに =INDIRECT(A1) と入力すると、ExcelはまずA1セルの中身である “B5” という文字列を読み取ります。
次に、その文字列を実際のセル参照、つまりB5セルそのものへと変換し、最終的にB5セルの値を表示します。
この機能こそが、数式の中で「参照先」を柔軟に、かつ動的に変更することを可能にする、重要な要素なのです。
INDIRECT関数は「大文字・小文字」を区別しない
ここで、INDIRECT関数の実用上、非常に便利な特性を一つ紹介します。
それは、参照する範囲名やシート名において、アルファベットの「大文字と小文字を区別しない」という点です。
例えば、過去の記事で作成した音階表の範囲に「C_dur」という名前を付けました。
この範囲を、INDIRECT(“c_dur“) と参照するのはもちろん、
INDIRECT(“c_dUR“) や INDIRECT(“C_DUR“) と参照しても、
Excelはすべて同じ「C_dur」という名前の範囲として正しく認識してくれます。
文字列の大文字・小文字が異なっても、問題なく動作するこの特性が、前回作成した各音階表の名前（例: G_dur, cis_mollなど）と、
今回数式内で生成する文字列の大文字・小文字が異なっても、問題なく動作する理由です。
これにより、命名の柔軟性が増し、数式の利便性が向上します。
STEP2：INDIRECT関数で「音階表」を動的に切り替える
基本を理解したところで、いよいよ今回のシステムの核となる、参照すべき音階表の名前を動的に生成するロジックを解説します。
和音の「調」に応じて参照先を変更する
C2とJ2を組み合わせた「動的な範囲名生成」
今回のシステムの心臓部となるのが、参照すべき音階表の名前（名前付き範囲）を、入力されたデータに応じて動的に生成するロジックです。
具体的には、DB_山本式和音シートのC列（キー名）と、J列（借用フラグ）の値を参照します。
まず、INDIRECT関数に渡す文字列を生成する部分の数式を見てみましょう。
C2&IF(J2="","_dur","_moll")この数式は、2つのパーツから成り立っています。
C2: まず、C2セルから現在の行のドイツ式キー名（例: C, G, aなど）を取得します。
IF(J2=””,”_dur”,”_moll”): 次に、IF関数でJ2セル（借用フラグが0で入力される列）を判定し、”_dur“または”_moll“という文字列を返します。
J2セルが空欄（借用和音ではない）の場合は”_dur”を、J2セルに0が入力されている（借用和音である）場合は”_moll“を、キー名に結合します。
これにより、例えばC durの文脈で借用和音（例: G7(♭9)）が使われる際に、その構成音を導出するために、自動的にmollの音階表（例: c_moll）を参照させるという、精緻な仕組みが実現します。
最終的に、この数式が生成した文字列（例: C_dur、a_moll）をINDIRECT関数に渡すことで、過去の記事で作成・命名した、適切な音階表の範囲を正確に特定できるのです。
STEP3：INDEX関数で「ルート音」を導出する IF関数で参照を柔軟に制御
参照すべき正しい「音階表」が特定できました。
次はその表の中から、目的の「音」をピンポイントで抜き出すためのロジックを構築します。
ここで活躍するのがINDEX関数です。
和音の「骨格」を正確に特定する
それでは、DB_山本式和音シートのQ2セル（C：コードネームの最初の音、つまりルート音を表示する最初のセル）に、以下の数式を入力します。
=INDEX(INDIRECT(C2&IF(J2=””,”_dur”,”_moll”)),F2+IF(I2=8,2),IF(E2=””,1,E2))
この数式全体がどのようにルート音を導出するのか、その論理構造を3つの引数に分解して徹底的に解説します。
引数1: INDIRECT(…)（参照範囲の指定）
INDEX関数の最初の引数は、どの「範囲」からデータを取得するかを指定します。
ここはSTEP2で解説した「動的に音階表を切り替える部分」です。
この引数によって、INDEX関数は、C_durの音階表を見るべきか、ges_mollの音階表を見るべきか、といった判断を正確に行います。
引数2: F2+IF(I2=8,2)（行番号の指定）
次に、INDEX関数の2番目の引数である「行番号」の導出ロジックです。
この部分が、和音の度数と根音省略を解釈します。
F2: [度]列（例: 1, 5など）の値を参照します。これが参照の基本となる行番号です。
IF(I2=8,2): ここが「根音省略による参照位置の調整」の鍵です。[根省]列であるI2セルが8（根音省略）の場合、行番号に2を加算します。8でなければ、Excelの挙動により0として計算されます。
なぜ+2なのでしょうか？
これは、根音省略された和音の事実上の最低音が「第3音」になる、という山本式和音番号のルールに対応します。
音階表が度数順に並んでいるため、行番号を意図的に2つずらすことで、INDEX関数が抜き出す音を、本来の根音の「2つ先の音」、つまり第3音にシフトさせるという、極めて巧妙な仕組みなのです。
引数3: IF(E2=””,1,E2)（列番号の指定）
最後に、INDEX関数の3番目の引数である「列番号」の導出ロジックです。
この部分が、セカンダリードミナントなどを解釈します。
E2: [調]列を参照します。ここにはセカンダリードミナントのターゲットとなる度数（例: V/IIなら2）が入ります。
IF(E2=””,1,E2): E2セルが空白（通常のダイアトニックコード）の場合は1（音階表の1列目）を、E2セルに数値が入っている場合はその数値を、そのまま列番号として返します。
このように、INDEX関数の引数にIF関数を用いることで、参照する列を動的に切り替えるという、非常に柔軟な制御が可能になります。
まとめ：Excelが和音の「核心」を見抜き、ルート音を導き出す！
今回の記事では、INDIRECT関数とINDEX関数という、Excelの強力な機能を組み合わせ、山本式和音番号の各要素から和音の「ルート音」を自動導出する、システムの根幹となる数式を構築しました。
INDIRECTによる動的な音階表の切り替え、IF関数による行・列の巧妙な制御、そして「根音省略」や「借用和音」といった山本式和音番号の哲学が、いかにして一つの数式の中に精緻に反映されているか、その論理の美しさを感じていただけたのではないでしょうか。
VLOOKUPやINDEX&MATCHが実務の常識である中で、あえてOFFSETやINDIRECTといった関数を使いこなすことの価値を、ご理解いただけたら幸いです。
さて、これで和音の「骨格」となるルート音を導き出す準備が整いました。
次回の記事では、いよいよ、導出したこのルート音を基に、和音の構成音（3rd, 5th, 7th, 9thなど）を導出し、最終的なコードネームを完全に組み立てていきます。
さらに複雑で精緻な数式が登場しますが、今回の学びがあれば必ず乗り越えられるはずです。
お楽しみに。
