DBのクロス検索＋線形補間

## 概要・経緯

こんなDBから
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/93818c78-e374-a6f4-31fc-fff1148a9c59.png)

所望の値yを引っ張り出したい
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/83fe7936-0e1e-a377-0e7a-55a4e307bf8c.png)
	
普通にクロス検索するなら`INDEX`&`MATCH`、今時なら`XLOOKUP`でやれば済む話だが、
`Area_x`はある実験の実測値の出力比で、所望の値が実測値と実測値の間の値であることがほとんどで、歯抜けの部分を検索するとなると一手間増える。

これまでDB側で1%刻みで横に長々と線形補間列を作っていたが、
もっとスマートにできないものかと思い作った。


## 結果

各`v`、`x`に対応する`y`が取り出せた。
100%,80%は`Area_x`にあるが70%は存在しない。
それでもちゃんと線形補間されて取り出せている。素晴らしい。

※後述するが実測値以下の値は選べない。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/46c769ba-a134-571a-a0ac-ad1b8acc0264.png)
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/0cd430a5-df86-5567-1acd-dd59811bb28a.png"><img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/21689d30-9626-885e-5124-4ed550b3062b.png"><img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/3914297f-e45e-ad1b-42d6-ab4d9321962d.png">


（十字カーソルはRelaxToolsから）

https://www.apnari.com/entry/Excel-RelaxToolsAddin


## 関数 `Area_y`という名前を`F3`から直接呼び出せる場合

``` vb
=LET(Val_v_検索値_,[@v],
     Val_x検索値_,[@x],

     Area_x転置_,SORT(TRANSPOSE(Area_x)),

     Count_x_,COUNT(Area_x),
     Pos_xMatch_,MATCH(Val_x検索値_,Area_x転置_,1),
     Pos_vMatch_,MATCH(Val_v_検索値_,Area_v,0)-1,

     Area_x_既知,OFFSET(Area_x,0,Count_x_-Pos_xMatch_,1,2),
     Area_y_既知,OFFSET(Area_y,Pos_vMatch_,Count_x_-Pos_xMatch_,1,2),

     Ans_y_,FORECAST.LINEAR(Val_x検索値_,Area_y_既知,Area_x_既知),
Ans_y_)
```

## 関数 セルの値から`INDIRECT`で`Area_y`という名前付き範囲を呼ぶ場合

似たようなフォーマットだけどクエリでまとめるのが面倒な複数のDBで、かつ更新頻度が極めて低い場合には即効性があるかと感じた手法。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/d897ec1a-fb05-2508-4b02-c81e3cb09e53.png)


``` vb
=LET(Val_v_検索値_,[@v],
     Val_x検索値_,[@x],

     Area_x転置_,SORT(TRANSPOSE(Area_x)),
     Area_y,INDIRECT($C$1 & "_" & $D$1),

     Count_x_,COUNT(Area_x),
     Pos_xMatch_,MATCH(Val_x検索値_,Area_x転置_,1),
     Pos_vMatch_,MATCH(Val_v_検索値_,Area_v,0)-1,

     Area_x_既知,OFFSET(Area_x,0,Count_x_-Pos_xMatch_,1,2),
     Area_y_既知,OFFSET(Area_y,Pos_vMatch_,Count_x_-Pos_xMatch_,1,2),

     Ans_y_,FORECAST.LINEAR(Val_x検索値_,Area_y_既知,Area_x_既知),
Ans_y_)
```

## 解説
各関数の機能と役割をざっと。
たぶん数か月後には自分も忘れてるだろうし。。。

役割を順に追っていくと、関数名は下から順になった。

### LET
この入り組んだ式をいとも簡単に読み書きさせてくれる土台。

変数宣言と代入の1組を1行ずつ改行して書けばよく、
宣言した変数名は計算過程や出力に用いることができる。

### FORECAST.LINEAR
今回のメイン機能。線形補間する関数。
`x`,`y`それぞれ既知の範囲から所望の`x`に対する`y`を推定する、というもの。

冒頭のDBは各実験値全域で線形になる訳ではないが、各点の間を線形補完して運用している。

ただ、実験値全域を既知の範囲としてしまうと公開データと齟齬が出てしまうので計算方法は公開データと合わせて
実測値間ごとに線形補間する。

そのためには所望の`x`を挟む`x`2つの範囲を絞り込む必要がある。


ちなみに所望の`x`が実測値ズバリでも問題ないが、実測値以下の値は選べない。
`OFFSET`で絞り込む範囲で下の値が存在しないため。


### OFFSET
範囲の絞り込み用。
ある配列範囲から、左上の座標と縦横の幅をしていして絞り込む関数。

今回は、上記のとおり「所望の`x`を挟む`x`2つの範囲を絞り込む」のが役目。

`Area_y_既知,OFFSET(Area_y,Pos_vMatch_,Count_x_-Pos_xMatch_,1,2)`
の行位置の部分**`Count_x_-Pos_xMatch_`**がなぜこんな形かというと、
`Area_x`を`SORT`で逆転させているため、ここで帳尻合わせをしている。


### MATCH
絞り込んだ範囲の位置をズバリ求める係。
「所望の`x`**以上**」を探すため近似検索とする。

### SORT
`MATCH`で近似検索をかけるには昇順である必要があるので、
今回のDBの`Area_x`が降順なので`SORT`で昇順に変換する。

### TRANSPOSE
`SORT`するには縦長の配列である必要があるので`TRANSPOSE`しておく。

## 感想
結構複雑なことをやったつもりが、
戻り値行を除いて10行でかけてしまったので
マクロより楽に書けたというのが驚き。

冒頭のDBがいくつもあり、
それぞれまとめ方が異なるためどうしようかと頭を悩ませていたところ、
PowerQueryでマージする方法をアドバイス頂いたので
いずれ恒久版を作り込む際にはそちらに移行したい。

PowerQueryをちらっと触ったところ、
きちんと使えるようになれば各DBの更新にも無敵の性能を誇るイメージは沸いたが、
戦力として投入するにはまだまだ修行が必要なので
取り急ぎは慣れた名前付き範囲と関数で対処することにした。


## おまけ：変数名の付け方
### アプリケーションハンガリアン＋日本語
最近VBAで変数名の参考にさせていただいているこれをLET関数にも利用させてもらった。
正直かなり読み書きしやすい。

https://www.limecode.jp/entry/guideline/applicationhungarian

### アンダースコア("_")
LET関数内の変数末尾にアンダースコア("_")を使った元ネタはこちら。
意味合いは違うが、既存の関数に抵触しないため書きやすい。

https://tonari-it.com/excel-vba-property-let/#toc3


> プライベート変数名を小文字+アンダースコアの形式にしたのは、これが理由です。
> 
> Personクラスモジュール内で実際にデータを持つのは変数id_
他のモジュールからアクセスをするのはProperty LetプロシージャId
と使い分けをする必要があるからですね。

> 他のモジュールからアクセスさせないように、プライベート変数として宣言する場合は、小文字+アンダースコアという名前することで、上記の役割を明確化する慣習があるということです。