MENU

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

  • 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 は、補間する値

となります。

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

X135
Y496

この表から、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 = 4 のyは 7.5 となります。

ツッキー

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

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

問題条件

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

スクロールできます
X0510152025303540
Y5814192119251810

任意の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 + (y1y0) / (x1x0) * (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つの関数は、慣れると業務で頻繁に使用します。
ぜひ使ってみてください。

Excelを活用して線形補間計算を効率化しよう

この記事では、線形補間計算の原理と効率よく計算する方法について徹底解説しました。線形補間計算の計算式は、以下の通りです。

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

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

また、それぞれの値を以下の関数で代用することにより、Excelで効率よく線形補間計算できます。

  • VLOOKUP関数
  • INDEX関数
  • MATCH関数

Excelの関数は、知っているか知らないかで業務スピードに大きな差がうまれます。私自身、関数を使わずに線形補間計算をしていた頃は、なかなか業務が進まずに苦労しました。今ではコピペであっという間に計算できるので、残業時間の削減につながっています。

建設業界では、深刻な人材不足と働き方改革により、生産性の向上が求められています。この記事によって、少しでも日々の業務効率が向上すれば嬉しいです。一緒に業務のICT化をどんどん進めて建設業界を盛り上げていきましょう!

線形補間計算を実際に活用して、土木工事の計画高を計算する方法は以下の記事で解説しています。Excelシートのダウンロードも可能です。

ツッキー

私は以下の2冊を読んでExcelの機能を覚えました。
Excelを業務で使用する時は、いつでも調べられるように側に置いています。

¥1,760 (2023/10/20 06:27時点 | Amazon調べ)
\楽天ポイント5倍セール!/
楽天市場で探す
\ポイント5%還元!/
Yahooショッピングで探す
\楽天ポイント5倍セール!/
楽天市場で探す
\ポイント5%還元!/
Yahooショッピングで探す

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

ツッキーのアバター ツッキー 土木施工監督

【20代土木施工監督】
橋づくり2年目
土木施工管理の仕事で得た経験と知識を発信

目次