エクセルで運賃を出す方法は？

質問

エクセルで0kmから3km以下は130円、3kmから6km以下は160円・・・xkmからykm以下はz円というような表示を出すVBAまたは関数は、どのようなものが考えられますか？　ちなみに暫定的に作ってみたのが、以下のような感じです。

Function alif(hikaku As Single, kyorikara As Single, kyorimade As Single) As Single
　→ single関数を使うことで、0.1km等の小数点に対応しております。

Dim yen As Integer
Dim i As Integer

For i = 1 To 100
kyorikara = Cells(i, "B")
　→ B列に、0kmから・・・等を入れています。
kyorimade = Cells(i, "D")
　→ D列に、3km以下・・・等を入れています。
yen = Cells(i, "C")
　→ C列に、運賃の情報を入れております。

If hikaku > kyorikara And hikaku <= kyorimade Then
alif = yen
Exit For
　→ 比較用のセルが、x距離より多くてy距離以下の場合、関数を入力した場所にz円を代入して For Next 文を終了します。

Else
　→ Elseは特に何も用意していない。

End If

Next

End Function

　しかし、これですと運賃関連の情報を１列移動した瞬間にデータが狂います（運賃関連のシートを別に作って、列を絶対に変更しなければ問題ないとは思いますが・・・）。どうにか列を変更しても問題なく作る方法はないでしょうか？

　また、使っていて気付いたことですが、運賃の「円」数値を変えた際に自動再計算しないのですが、これを自動計算させるにはどのような方法があるのでしょうか？（設定で手動計算になっていたのかは確かめておりませんので、これは改めて確認してみますが・・・。）

　どうか、ご教示いただければ幸いです。

回答No.1

popesyu · ベストアンサー

>どうにか列を変更しても問題なく作る方法はないでしょうか？
この前提が間違っているのではないでしょうか。
DB的なシステムを作るときに、目印になるフィールド名や列名が動的に変わることを前提に組むことはしません。
列を動かされたくないのなら、別シートにするなり、別ブックにしてロックをかけておけばよいだけですし。コード的な工夫や制限の話ではなく、これは運用上の問題です。
一応動的に変わることを前提にするなら、列に定義名を振ってそちらを目印にするか、あるいは1行目をタイトルとしてこれを固定してこのタイトル名でその都度検索して列数を探すとか。
ただこれらも定義名なり列名が変えられてしまうことを前提にするなら、同じことです。どちらにせよ何らかの目印は振らなければなりませんし、その目印が変更されてしまうことが前提になるなら、その都度コードを直接修正しなければなりません。
※上記の方法の方が、列数を決め打ちするよりは多少自由度が高くはなります。

>運賃の「円」数値を変えた際に自動再計算しない
元々運賃の「円」数値を変えた際にそのコードが動作するようにはなっていないと思われますが。設定シートの書き換えにも動的に連動させたいということならば、別シートにした上でそのシートのチェンジイベントを拾うようにするとか。
例えば運賃シートの値が変わった時は比較用の値を一旦キャッシュに保存して、クリアしてまたそのキャッシュの値を書き戻すとかすれば、その関数の結果も更新されます。
まぁこれもシート上で全て完結されるのではなく、入力用のフォームなどを別に用意するようにすれば、イベントの管理もしやすくなります。※思いも寄らぬ操作をされないように最初から制限しておくのがバグも発生しにくくなります。

あと根本的に100回ループさせるというが非常に無駄だと思います。
素直にFind系の関数を使って一発で該当の行を探すべきでしょう。またその際も一旦全て配列に取り込んで、その中で探すとした方が処理も速くなります。

またそもそも引数のhikaku As Single, kyorikara As Single, kyorimade As Single
が意味不明です。入力値は距離(hikaku)だけで良いだけでは。また運賃シートも必要なのはkyorikaraもしくはkyorimadeとその対応する運賃だけでしょう。
「10」として入力されたら、運賃シートの「距離」列から10以上の数字の入っている行数を取得して、その隣にある「運賃」の値を返すだけ。となるとそもそもワークシート関数で完結してしまいますので、自作関数すら出番がないですが。
例えば境界線上の部分を多少曖昧にしてもよい（それでも小数点を何十桁まで増やせば対応可能でしょうが）なら下記のような感じにすればLookup関数だけでも何とかなりそうな...。
距離から　運賃
0　100
3.001　500
6.001　1000
9.001　1500
こんな感じの表にして、
C1に距離数を入力したらその運賃が返ってくる関数
=VLOOKUP(C1,A1:B4,2)

お礼

Vlookup関数の存在は知っておりましたが、こんな使い方が出来るとは知りませんでした。

　無事に目的のことができました。ありがとうございます。