[[20111022152118]]　『ユーザー定義関数で見出しを検索条件に』（stem）　ページの最後に飛ぶ

『ユーザー定義関数で見出しを検索条件に』（stem）

 お世話になります。
下記のコードで住所が「○○南山市○○」及び 年齢が70歳以上の人を
抜き出すユーザー定義関数を書きました。
 Function 該当者検索1(myr As Range) As String
　Application.Volatile
 If myr.Value Like "*南山市*" And myr.Offset(0, -1).Value >= 70 Then
    該当者検索1 = "南山市該当"
 Else
 End If
 End Function
うまく抽出できました。

 次に
上のコードでは検索する住所を直接「"*南山市*"」と指定していますが
見出しに入力されているデータ（Ｄ列ならばＤ１）で指定したくて
Function 該当者検索2(myr As Range) As String
Dim i As Long
Dim j As String
Application.Volatile
i = myr.Column
j = Cells(1, j).Value

 If myr.Value Like "*" & j & "*" And myr.Offset(0, -1).Value >= 70 Then
    該当者検索2 = j & "該当"
 Else
 End If

 End Function
Ａ列に個人名　Ｂ列に年齢　Ｃ列に住所のデータ
Ｄ１には「南山市」と入力があり、Ｃ２には「○○南山市123番地」と住所の入力があります。
Ｄ2に「=該当者検索2(C2)」
結果　#VALUE!　になりました。
ＸＰ　ｴｸｾﾙ2007です
ご教示宜しくお願い致します。

 ロジックはみていないけど、とりあえず　j = Cells(1, j).Value　は　j = Cells(1, i).Value だよね。

 追記）ちょっとだけロジックをながめた。
　　　j = Cells(1, i).Value だと　D1 じゃなく C1 を参照するけどいいの？

 (ぶらっと)

 ぶらっとさん
ご指摘ありがとうございます
ドジとバカがなおりません＞＜。。
＞j = Cells(1, i).Value だと　D1 じゃなく C1 を参照するけどいいの？
Ｄ１を参照させたいです。
Ｄ２に=該当者検索2(C2)の入力があればＤ１を参照
Ｅ２に=該当者検索2(C2)の入力があればＥ1を参照
のようはコードは可能でしょうか？ご教示願います。
※先ほど一度書きこみましたが、訂正しました
　（stem)

 コードを見たところユーザー定義関数としての基本から外れているように思います。
 組み込みの普通の関数(SUMなど)を考えてみてください。
 その関数内で用いる値は全て引数で与えます。だからVolatileメソッドも用いません。
 (Volatileメソッドを用いる事の処理速度の阻害については理解されていますか)

 で、一例として以下のようなコードにすれば良いでしょう。
 D2に =該当者検索2($C2,$B2,D$1)

 Function 該当者検索2(Address, Age, What) As String
   If InStr(Address, What) And Age >= 70 Then 該当者検索2 = What & "該当"
 End Function
 (sasa)

 Application.Caller　というプロパティを調べて見てください。
 そうすれば、とりあえず
 >Ｄ２に=該当者検索2(C2)の入力があればＤ１を参照
 >Ｅ２に=該当者検索2(C2)の入力があればＥ1を参照

 これの実現は出来ます。

 でも・・・。
 私が作るなら、このインタフェースにはまずしないと思いますよ！！

 提示したApplication.Callerを使った間接的な
 セルの取得や
 コード中のmyr.Offset(0, -1).Value

 Application.Volatile
 の記述がなければ、

 Ｄ２に=該当者検索2(C2)
 の場合、B2の値を変更しても、結果が変わりませんよね？
 変わらないので再計算を促すためのApplication.Volatileなんでしょうが・・・。
 これは、C2やB2以外のセルを変更しても、常に再実行しているということを
 考えてください。沢山使えば、処理にも影響してきます。
 第一、関数なのに引数に指定したセル以外の影響を受ける　という
 分かりづらい仕様になっています。

 Ｄ２に=該当者検索2(C2)の入力があればＤ１を参照
　これなら、 =該当者検索2(C2,b2,d1)
　という指定で結果を出す関数の方が引数がなんであるか分かりやすくないですか？
 また、このようなインターフェースであれば、Application.Volatileは、
 要りません。

 更にこのそれぞれの引数の意味とそもそもの関数の機能をきちんと
 ドキュメントとして、記述してみてください。

 これがきちんと表示できないような仕様なら、根本から練り直すことも
 検討してください。

 ichinose

 sasaさん
ichinoseさん
有難うございます
＞組み込みの普通の関数(SUMなど)を考えてみてください。
＞その関数内で用いる値は全て引数で与えます
ああ！ユーザー定義関数も関数なので...引数で...。今理解しました。

 ＞ (Volatileメソッドを用いる事の処理速度の阻害については理解されていますか)
まったく理解していませんでした。ユーザー定義関数を検索したら
このコードがあったのでユーザー定義関数では使うのが標準なのかと...
なんとなく入力して安心してました。

 御二方がおっしゃっている基本の
=該当者検索2(C2,b2,d1)を勉強します。
こんな関数が作れればと挑戦して最初の方法で意外とうまくいったので
それ以上の思考をまったくしていませんでした。
＞Application.Caller
検索してみました。
使いこなせるのはまだまだ後ですが自分の位置を取得するプロパティみたいですね。

 ユーザー定義関数に関しての支離滅裂な思考を正していただきました。
有難うございました。
　（stem)

 >御二方がおっしゃっている基本の
 >=該当者検索2(C2,b2,d1)を勉強します。
   ↑
 この関数の一例はすでに示しています。
 D2に =該当者検索2($C2,$B2,D$1)
 のように入力すると右や下方向等にドラッグして入力出来ます。
 (sasa)

 sasaさんへ
＞Function 該当者検索2(Address, Age, What) As String
使わせていただいています。
ただ理解の方はまだでしたので、あのように書かせていただきました。
InStr関数を検索してみて、先ほどやっと理解できたように思います。
有難うございました
　(stem)

コメント返信：

[ 一覧(最新更新順) ]

[[20111022152118]] 『ユーザー定義関数で見出しを検索条件に』（stem） ページの最後に飛ぶ

[[20111022152118]]　『ユーザー定義関数で見出しを検索条件に』（stem）　ページの最後に飛ぶ