【Excel】XYデータ列の中で、対応する数値を計算する汎用的な方法【MATCH関数、INDEX関数】
皆さんは、XYデータ列に関して、Y値に対応するX値をどのように算出していますか？
散布図などのグラフデータを補間、補外する方法をご紹介します。Excel VBAを使った方法もありますが、VBAはご存じの通りひと手間かかりますが、本方法はVBAを使わなくても、Excelの関数のみで計算することができます。また、複雑な曲線データを関数で近似して、フィッティングする方法もありますが、フィッティング精度に課題があります。曲線は多数の折れ線の集まりだとみなして、その中で、ひとつの折れ線を検索してから、その中で数値計算する方法が、どのような曲線にも汎用的に対応することができます。
散布図グラフのもとになるX、Yデータ列について、Y値に対応するX値を算出します。題材としては、トランジスタ（MOSFET）を使い、ゲートのしきい値電圧を算出します。ここで、MOSFETとは、metal-oxide-semiconductor 金属酸化膜半導体電界効果トランジスタのことです。
以下のグラフの縦軸はドレイン電流(Drain current)、横軸はゲート電圧(Gate voltage)です。ここで、縦軸は対数としました。ドレイン電流１E-8A（10nA）に対応する、ゲート電圧を算出しました（しきい値電圧Vth=2.28V）。なお、このときのドレイン電流は、必要に応じて任意な値（100nAや1nA）に設定することができます。
説明のため、以下のように、グラフの横軸の範囲を2V～2.6Vと狭くしました。また、縦軸の範囲も狭くして、線形（リニア）に変更しました。
プロットのX値が2V,2.2V,2.4Vと0.2V間隔であることがわかります。2.2Vと2.4Vについては、Excelの機能であるデータの要素(x,y)を表示しています。すなわち、データ自体は0.2V間隔と粗く、拡大してみると折れ線状となっていることがわかります。ただ、しきい値の算出が0.2V間隔では誤差が大きく、もっと細かい精度（小数点の桁数）が必要です。そこで、2.2V～2.4Vの間の線分とy=1E-8の交点をもとめることで、しきい値電圧Vth＝2.28Vと、0.2Vより高い精度で算出することができます。ここで、見やすさのために便宜上少数第二桁までしか表示していません。
つぎに、具体的にExcelでの算出方法を解説します。以下の表の上部にはデータ列、下部には計算結果を示します。
また、以下に数式のテンプレートを用意しましたので、本表全体をコピーしてExcelのワークシートに貼り付け可能です。
| 入力 | 計算結果 | ||
| y ⇒ | 1.0.E-08 | y=任意のドレイン電流 | |
| 行番号⇒ | =MATCH(C108,C6:C106) | 行番号=MATCH(y,ドレイン電流のデータ列) | 62 |
| x1⇒ | =INDEX(B$6:B$106,C$109) | x1=INDEX(ゲート電圧の文字列,行番号) | 2.2 |
| y1⇒ | =INDEX(C$6:C$106,C$109) | y1=INDEX(ドレイン電流のデータ列,行番号) | 6.38E-09 |
| x2⇒ | =INDEX(B$6:B$106,C$109+1) | x2=INDEX(ゲート電圧の文字列,行番号+1) | 2.4 |
| y2⇒ | =INDEX(C$6:C$106,C$109+1) | y2=INDEX(ドレイン電流のデータ列,行番号+1) | 1.55E-08 |
| a⇒ | =(C113-C111)/(C112-C110) | a=(y2-y1)/(x2-x1) | 4.55E-08 |
| b⇒ | =C111-C114*C110 | b=y1-a*x1 | -9.38E-08 |
| x⇒ | =(C108-C115)/C114 | x=(y-b)/a | 2.2794 |
ここで、以下の表のように、セルB6～B106にはゲート電圧のデータ列、セルC6～C106にはドレイン電流のデータ列を示します。
まず、MATCH関数を使い、(x1,y1)=(2.2,6.38E-9)を検索ヒットさせます。MATCH(C108,C6:C106)として、C6:C106はドレイン電流のデータ列、C108は任意のドレイン電流(1E-8)です。
MATCH関数による、検索結果は第６２行（セルC６７）でした。なお、(x2,y2)=(2.4,1.55E-8)は１つカウントアップした、第６３行データとなります。(x1,y1)、(x2,y2)の各要素を参照するには、index関数を使います。x1を算出するには、INDEX(B$6:B$106,C$109)とし、B$6:B$106はゲート電圧のデータ列、C$109は検索結果（第６２行）です。同様に、検索行結果をもとにしたINDEX関数により、順次x2,y1,y2を求めます。
つぎに、示しました折れ線グラフで、(x1,y1)、(x2,y2)を通る直線y=ax+bの傾きa,切片bを求めます。 最終的に、x=(y-b)/aの関係式が得られました。a,bは既知ですので、y=1E-8の場合、x=2.2794と求められます。すなわち、任意のｙに対して、xを細かい少数桁数まで算出することができます。VBAを使うことなく、ワークシートのセル９行１列範囲の入力で済みましたので、簡便な方法といえます。いろいろな場面で役に立つと思いますので、ぜひ活用いただきたいと考えています。ただ、本方法は、データ列の並びが昇順、もしくは降順である必要があります。なぜなら、MATCH関数で検索する際に、検索結果を一意に定める必要があるからです。
データ列が必ずしも、昇順、降順でない場合、データ範囲を狭くするなどの回避策があります。また、forcast関数を使う手があります。また、forcast関数は非常に簡便な方法でもあります。この方法はxとyの関係が直線的であると考えられる場合に有効です。Forecast関数を使って計算するサイトを以下にご紹介します。
【Excel】エクセルにて直線補間（線形補間）を関数や数式を用いて行う方法【Forecast関数】
https://toushitsu-off8.com/excel-hokan-forecast/
また、セル参照し、グラフに文字列を入れる方法については、以下のサイトで紹介しています。
