Excel 自作関数から特定のセルを参照して分岐

質問

以下のように、C列を参照して判断し、それに適した値を表示させる関数を作っています。

質問１：以下、どこが間違ってますでしょうか？

質問２：以下では、Columns(3)として参照していますが、列に名前を付けておき、それを関数から参照させることはできますでしょうか？

Function test()
　Select Case Worksheets("Sheet1").Columns(3).Value
　　Case "りんご"
　　　test = "100円"
　　Case "みかん"
　　　test = "150円"
　　Case "いちご"
　　　test = "300円"
　　Case "すいか2"
　　　test = "200円"
　End Select
End Function

回答No.1

keithin · ベストアンサー

＞Worksheets("Sheet1").Columns(3)

これではシート１のＣ列のセル範囲（C1からC65536までの65536個のセル）です。その中の具体的に「どの１個のセル」の値を調べて判定したいのか，特定できていません。

変更前：
Worksheets("Sheet1").Columns(3).Value

変更後：あなたがやりたかったのはこういうことでしょうか
Worksheets("Sheet1").cells(application.caller.row, "C").Value

それとも？C列の上から下まで全セルを全て調べた結果を，(65536個のデータの) 配列として返す関数を作りたかったのでしょうか。

＞列に名前を付けておき、それを関数から参照させる

可能と言えば可能ですが，「列に名前を付ける」事は出来ません。名前を付けられるのは「その列のセル(セル範囲)」です。
名前は，ワークシート画面では挿入メニューの名前の定義で作成できます。勿論マクロから作成や操作も出来ますが，先にエクセルを手で動かして名前定義についてちゃんと理解してから，それからマクロで利用してください。

変更後の例のその２：
Function test()
　Select Case range("私のＣ列").cells(application.caller.row).Value
　　Case "りんご"
　　　test = "100円"
　　Case "みかん"
　　　test = "150円"
　　Case "いちご"
　　　test = "300円"
　　Case "すいか2"
　　　test = "200円"
　End Select
End Function

お礼

ありがとうございます。できました。

この書き方が知りたかったのです。助かりました。
range("私のＣ列").cells(application.caller.row).Value

回答No.3

chie65536（@chie65535）

訂正

Function test(Hinmei as Atring) as String
は
Function test(Hinmei as String) as String
の間違いでした（A⇒S）

回答No.2

chie65536（@chie65535）

＞質問１：以下、どこが間違ってますでしょうか？

「関数で最適値を返す」と言う考え方そのものが間違い。

通常、こういう「名前から価格を求める」など「ある値に対応した最適値を得る場合」には「価格表を別シートで用意し、VLOOKUP関数で求める」のが普通。

Sheet2に添付画像のような表を用意し

D7セル：=VLOOKUP(C7,Sheet2!$A:$B,2,FALSE)
D8セル：=VLOOKUP(C8,Sheet2!$A:$B,2,FALSE)
D9セル：=VLOOKUP(C9,Sheet2!$A:$B,2,FALSE)
D10セル：=VLOOKUP(C10,Sheet2!$A:$B,2,FALSE)

のような式を書きましょう。

商品や価格が変わったり、商品が増えた場合、Sheet2の表を追加変更するだけで済み、自作関数を書き変えたり修正する必要は無くなります。

＞質問２：以下では、Columns(3)として参照していますが、列に名前を付けておき、それを関数から参照させることはできますでしょうか？

Columns(3)は「C1～C65536セル」を現わします。

この関数の本来の目的は「ある値を与えると、対応した値を返す」のですから、引数として「ある値」を受け渡すべきです。

関数を以下のように書き直して下さい。

Function test(Hinmei as Atring) as String
　Select Case Hinmei
　　Case "りんご"
　　　test = "100円"
　　Case "みかん"
　　　test = "150円"
　　Case "いちご"
　　　test = "300円"
　　Case "すいか2"
　　　test = "200円"
　End Select
End Function

呼び出す部分は、
D7セル：=test(C7)
D8セル：=test(C8)
D9セル：=test(C9)
D10セル：=test(C10)
とします。

とゆ～か、別表を用意してVLOOKUPを使えば、自作関数そのものが不要で、しかも自作関数よりも利便性が高いので「自作関数で処理する」と言う馬鹿な事は辞めましょう。