Excelで線形補間計算する方法について徹底解説

2024年1月21日2024年5月18日

URLをコピーしました！

線形補間計算をしたいんだけど…
どうやって計算すればいいか分からない
Excelで効率よく計算する方法を教えてほしい！！

建設現場の測量において、実施することが多い線形補間計算。2点の座標（x, y）を直線で結んだ際に、間にある任意のx座標に対応するyを計算する方法です。例えば、線形図から任意の場所の計画高を計算する時に使用します。測量のために計画高を計算するのが大変で困っている方も多いはず。

私自身、新卒でゼネコンに入社して半年くらい経った時に、橋梁上部工の計画高の計算を任されました。線形図を読み取りながら膨大な量の計画高を計算する日々。少しでも残業時間を減らすために試行錯誤した結果、今ではExcelで簡単に線形補間計算できるようになりました。

そこで本記事では、若手現場監督が線形補間計算で苦労しないように、Excelで効率よく計算する方法を分かりやすく解説します。この記事を読めば、「誰でも線形補間計算を効率化すること」ができます。

私が何度も試行錯誤することで手に入れたノウハウをまとめました。測量業務の生産性を向上させたい方は、ぜひ最後まで読んでください。

線形補間計算の原理

線形補間計算の計算式は、以下の通りです。

y = y0 + (y1 – y0) / (x1 – x0) * (x – x0)

ここで、

y0 は、x = x0 におけるyの値
y1 は、x = x1 におけるyの値
x は、補間する値

となります。

例えば、次の表があるとします。

X	1	3	5
Y	4	9	6

この表から、x = 2 のyを求めたい場合、次のようになります。

y = 4 + (9 – 4) / (3 – 1) * (2 – 1) = 6.5

したがって、x = 2 のyは 6.5 となります。

x = 4 のyを求めたい場合も、同様の計算を行います。

y = 9 + (6 – 9) / (5 – 3) * (4 – 3) = 7.5

したがって、x = ４のyは 7.5 となります。

ツッキー

傾きが正でも負でも同じ計算式で計算できることが分かります。

Excelを用いた線形補間計算の方法

問題条件

本記事では、写真のグラフを例題として扱います。既知点のXY座標は以下の通りです。

スクロールできます

X	0	5	10	15	20	25	30	35	40
Y	5	8	14	19	21	19	25	18	10

任意の1点を求める場合

x = 8 のyを求めたい場合、上記の写真のように算出します。F3に入力した数式と各セルの数値が以下の式に対応していることが分かります。

y = 8 + (14 – 8) / (10 – 5) * (8 – 5) = 11.6

x = 38 のyを求めたい場合も同様です。F4に入力した数式と各セルの数値が以下の式に対応しています。

y = 18 + (10 – 18) / (40 – 35) * (38 – 35) = 13.2

任意の複数点を求める場合

任意の複数点を求める場合は、関数を使用しましょう。一度数式を入力すれば、後はコピペで線形補間計算をすることができるからです。写真の例の場合、セルF3には以下の数式を入力します。

=VLOOKUP(E3,$B$3:$C$11,2,1)+(INDEX($B$3:$C$11,MATCH(E3,$B$3:$B$11,1)+1,2)–VLOOKUP(E3,$B$3:$C$11,2,1))/(INDEX($B$3:$C$11,MATCH(E3,$B$3:$B$11,1)+1,1)–VLOOKUP(E3,$B$3:$C$11,1,1))*(E3-VLOOKUP(E3,$B$3:$C$11,1,1))

線形補間計算の式と比較すると、以下の通り対応していることが分かります。

= y0 + (y1 – y0) / (x1 – x0) * (x – x0)

y0 = VLOOKUP(E3,$B$3:$C$11,2,1)
y1 = INDEX($B$3:$C$11,MATCH(E3,$B$3:$B$11,1)+1,2)
x0 = VLOOKUP(E3,$B$3:$C$11,1,1)
x1 = INDEX($B$3:$C$11,MATCH(E3,$B$3:$B$11,1)+1,1)

ツッキー

セルF3をコピーしてF4以下の列にペーストすればyの値を求められます。

使用する関数について

VLOOKUP関数

VLOOKUP関数を使用して、x0およびy0の値を表から計算しました。検索の型を近似一致であるTRUE(1)にしているのがポイントです。近似一致にすることにより、求めたい座標のxの値に最も近いx未満の値を検索範囲から探すことができます。

VLOOKUP関数

引数(範囲)の先頭列を縦方向に検索し、引数(検索値)に一致する値を調べる。その値のセルと同じ行で、指定した引数(列番号)に当たるセルの値を取り出す。引数(検索の型)は、調べる値が完全一致の場合は「0」、近似一致の場合は「1」を指定。

＝VLOOKUP(検索値,範囲,列番号,検索の型)

「〜以上〜未満」の検索は近似一致であるTRUE(1)で

完全一致は、引数[検索値]と同じデータしか検索できませんが、近似一致は、一致する値がなくても、最も近い値(検索値未満の近似値)を検索します。

できるYOUTUBER式 EXCEL現場の教科書長内孝平

ツッキー

近似一致は、検索値と一致する値がなくても、検索値より小さい値かつ最も近い値を探してくれます。
よって、検索範囲のxの値は昇順に並べておく必要があります。

INDEX関数

INDEX関数を使用して、x1およびy1の値を表から計算できます。検索範囲の中から、指定した行番号と列番号の位置にある値を求める関数です。後述するMATCH関数と組み合わせることで、目的のセルの値を返すことができます。

INDEX関数

INDEX(範囲,行番号,列番号)

引数[範囲]の中の、指定した引数[行番号]と引数[列番号]の位置にある値を求める。

(中略)

実務では、INDEX関数を単体で使うことはほぼありません。なぜなら、手打ちで引数[行番号]と引数[列番号]を指定するのは手間がかかる上に、ミスをしやすいからです。そこで、目的のセルが何個目にあるかを求められるMATCH関数の出番です。

できるYOUTUBER式 EXCEL現場の教科書長内孝平

MATCH関数

MATCH関数を用いて、INDEX関数の行番号の指定を行いました。VLOOK関数と同様、検索の型をTRUE(1)にしています。近似一致により、検索値より小さいかつ最も近い値が検索範囲の何行目にあるかが分かります。MATCH関数にで返された値に1を足すことによって、x1やy1に該当する行番号を指定可能です。

MATCH関数

MATCH(検索値,検査範囲,照合の種類)

引数[検索値]が引数[検査範囲]の中の何番目のセルにあるかを求める。引数[検査範囲]における先頭のセルの位置を1として数えた値が返される。完全一致のデータを検索する場合、引数[照合の種類]は[0]または省略する。

できるYOUTUBER式 EXCEL現場の教科書長内孝平

ツッキー

本記事で紹介した3つの関数は、慣れると業務で頻繁に使用します。
ぜひ使ってみてください。