Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
カスタム関数の作り方について
投稿日時: 20/10/07 16:24:02
投稿者: Skit
メールを送信

カスタム関数に習熟したいと学習を始めました。
イメージとしてエクセル既設の組み込み関数と同様な扱いで以下の処理をしたいのですが作り方が分かりません、ご指導ください。
 事前設定済み 4つの異なる範囲に「名前の定義」済み
  @仮にそれぞれの範囲をA,B,Cとします、
  Aまず範囲A,B,Cに同一の計算式がはいっています。
 
 カスタム関数の目的
  *ある条件がそろったときそれぞれの範囲内の計算結果を固定化する。
    例 =if文で(条件式、固定(A),計算続行) ーーー 固定()をカスタム関数にしたい。
  カスタム関数”固定()”で引数として範囲 A〜C のいずれかが入るとします。
 
 いまいち分からないところが
   1)固定(A)として引数に 名前定義された記号が機能するか?
   2)引数の設定、 渡し方
です よろしく ご指導ください。
[/b]

回答
投稿日時: 20/10/07 17:14:33
投稿者: WinArrow
投稿者のウェブサイトに移動

>カスタム関数
 
一般的には、「ユーザー定義関数」と呼ばれています。
 
ユーザー関数、標準モジュールに記述します。
 

Function 固定(ByRef 範囲 As Range)
    固定 = 範囲.Address
End Function
 
 
セルには
=固定(範囲A)
と入力します。
 
この例は、指定された名前定義の名前を引数としています。

回答
投稿日時: 20/10/07 18:25:32
投稿者: mattuwan44

その自作の関数をシート上の数式で使うんですか?
それとも、マクロ内で使うんですか?
ちょっと、今の説明では、何をどうしたいか、
要領を得ないかなぁ。。。
 
>範囲内の計算結果を固定化する。
シート上の数式の再計算の対象から指定のセルを外すってことですか?
 
>=if文で(条件式、固定(A),計算続行)
計算続行とは?それも数式の引数にする?あるいは自作関数の引数にする?
 
>名前定義された記号が機能するか?
セル範囲に名前を付けたなら、
「A1」と同じように「文字列」で指定できます。
 
例)
Range("A").Select
 
※元々のセル番地と区別が付きにくい名前は避けましょう

回答
投稿日時: 20/10/07 20:39:34
投稿者: simple

引用:
1)固定(A)として引数に 名前定義された記号が機能するか?
2)引数の設定、渡し方は?
既にご指摘いただいているとおりです。
 
AAAという範囲名を定義してあるものとします。
ご自分で実行してみたらよいと思いますが、
Function test(r As Range) As Long
    test = r.Value
End Function

などとしておいて、
=IF(A1=1,test(AAA),A1+2)
などと書けます。
 
ただし、なんら計算することなく、単に範囲AAAの値を取得するだけであれば、
=IF(A1=1,AAA,A1+2)
と書けば良いわけで、UDFの利用は不要です。
すでにご指摘があったとおりです。

回答
投稿日時: 20/10/07 20:47:21
投稿者: simple

testプロシージャの返り値の型を仮にLongとしましたが、
実際は限定せずに、As Variantで良いと思います。

回答
投稿日時: 20/10/07 22:30:33
投稿者: WinArrow
投稿者のウェブサイトに移動

回答してから、何だけど・・・・
 
説明の中で、よくわからないところがあります。
 
(1)範囲内の計算結果を固定化する。
 この意味は?
 「固定化」を「値」にするということならば、
  なにも、ユーザー定義関数にしなくてもよいでは?
 
※数式の入っているセルを参照したときの、何を参照するかを理解したほうがよいのではないでしょうか?
 
 例
  セルC1に「=SUM(A1:B1)」という数式がはいっています。
  そして、D1セルには、「=C1*100」という数式が入っています。
  この時、D1セルでは、C1の「値」を参照しています。
つまり、
数式(関数を含む)では、セルの「値」を参照する
ということで、ユーザー定義関数を使って「値」にする必要はない・・・ということです。 

投稿日時: 20/10/08 20:25:18
投稿者: Skit
メールを送信

多くの皆様の回答参考にさせて頂きます。
 WinArrow様、mattuwan44様 simple様 ご指導ありがとう御座います。
 
 最初に私の最初の説明が不足で目的が分からないと言うご指摘がありますのでもう少し補足します。
  1)カスタム関数 →UDFを使用は UDF学習の目的があります。
  2)今回のUDF導入は 次のような機能ソフトの特徴から UDF出なくては出来ないのかもと考えた事。
   機能ソフトとは:
      全体的にはデータログの様な機能で 時系列に3段階での特定条件を設定しています。
     それぞれの段階で設定した条件に達した時計算結果を補足することを目的にしています。 各段階での言わば瞬時値を記録する(固定)の意味ですが。。。 各段階での計算式は同一で 設定した条件に達したか否かをif文で見ています。
     必要な条件に達したとき 計算式から結果を「値」にしないと 条件に達した時の値が変動してしまうため。シート側で{if(条件達成判定、計算結果の保存(=固定)、計算式を保持)} と言った形にしています。
 
    この計算結果の保存は10個ほどのセルが有り 全体をを範囲AAAの様な形で指定したい。
     実際にはif文で
       @ 条件1, 範囲AAAの固定、 計算式維持
       A 条件2、 範囲BBBの固定、 計算式維持
       B 条件3、 範囲CCCの固定、 計算式維持  としています。
     
     範囲AAAの固定 の部分をUDFで「値」にするという意味です。
 
 皆様のご指摘を学習させて頂きます。

回答
投稿日時: 20/10/08 20:56:15
投稿者: simple

ずいぶん高度なことを目指されているようですが、いまひとつ理解が追いつきません。
そのIF文はどこに、どのような形で書かれるのですか?
 
そのときに、AAAという単純参照ではダメな理由が今ひとつわかりません。
3段階のIF文になるとしても、それぞれの部分では単純参照で良いのではないですか?
もう少しお願いします。

回答
投稿日時: 20/10/08 22:25:01
投稿者: WinArrow
投稿者のウェブサイトに移動

Skit さんへ
 
長々と書いていただいたが、
全く理解できません。
 
>10個ほどのセルが有り 全体をを範囲AAAの様な形で指定したい
ということは、AAAは複数のセルということかか?
>固定
という意味もわかりませんし・・・・
 
もう少し、他人に理解できるように説明したしたほうがよいですね・・・・

回答
投稿日時: 20/10/08 22:26:08
投稿者: mattuwan44

数式で、
あるセル範囲の再計算をするかしないかを、
制御したいってことですか?
 
たぶん無理なんじゃないかな?
「関数」といったら、
ある「値」渡したら、決まったルールで「値」を返す
というようなことをします。
エクセルの「操作」的なことはできません。
 
そのような場合、
再計算を手動にしておいて、
マクロで再計算を制御することになるかと思います。
 
できれば、数式をシート上に置かないことが、
間違いをなくすことになると思います。
 
エクセルの挙動を条件によって制御したい(再計算をする・しないを制御したい)なら
「マクロ」を作ることになります。

投稿日時: 20/10/09 06:45:10
投稿者: Skit
メールを送信

Simple 様
 先の機能説明に補足します。
  3段階を条件がそろったときの時刻を時系列 t1, t2, t3として、計測した結果をデータ列(範囲)AAA,BBB,CCCに保存します。 最終的にはt1〜t3で各列の値で時間推移の変動を得ることが目的です。
 従って、単一の範囲AAAだけではt1〜t3の 一点しか補足できません。
 各範囲(AAA〜BBB)のセルには同一の計算式が入っており、”固定”しないとt3に達した時各範囲のデータ列は同じ値を持つことになります。
 
  因みに設定条件に達した時 セル内の計算結果を「値」に(固定)させる方法(組み込み関数)は無いのでは?
 現状は、条件1〜条件3に達した時フラグを立て そのフラグをみて各時点で「数値コピー」をマニュアルで処理して居ますが時々その処理を忘れ アチャ! となってしまうので自動化したい。
    表にすると 範囲AAA t1時 Da1,Da2,・・・・・・・Da9
            BBB t2時 Db1,Db2,・・・・・・・Db9
                        CCC t3時 Dc1,Dc2,・・・・・・・Dc9 を生成
    最終的に 各列のデータ 例:Da1〜Dc1で時間推移のグラフを作成します。
 
 今 構築したいUDFは各範囲の値を 値コピー して完了とする機能です。
 
 引き続き、ご指導下さい。
 
  はじめから 表の形で説明しておけば分かりやすかったかな と反省してます。

投稿日時: 20/10/09 06:52:40
投稿者: Skit
メールを送信

mattuwan44様 WinArrow様
 
 昨夜Simple様のレスを見てレスを書いており ご両人のレスを見落として居ました。今朝方気づきました。
 今夕 熟読させて頂きます。

回答
投稿日時: 20/10/09 08:34:18
投稿者: simple

だいぶ理解が進みました。
 
それで、
>今 構築したいUDFは各範囲の値を 値コピー して完了とする機能です。
つまり、AAA,BBB,CCCなどとは異なるセルにUDFを設定して、
そのUDFの中で、AAA,BBB,CCCたちを値コピーできないか、
ということですね。
 
残念ながらUDFでは、そう言うことができません。
 
「Excel のユーザー定義関数の制限について 」
https://support.microsoft.com/ja-jp/help/170787/description-of-limitations-of-custom-functions-in-excel
という記事をマイクロソフト社が纏めています。再掲すると、

引用:
ワークシートのセル内の数式から呼び出されるユーザー定義関数では、
Microsoft Excel の環境を変更できません。
つまり、このような関数では次の処理が実行できません。
・スプレッドシートでのセルの挿入、削除、または書式の設定。
・別のセルの値の変更。
・ブックでのシートの移動、名前の変更、削除、または追加。
・計算方法や画面表示など、環境オプションの変更。
・ブックへの名前の追加。
・プロパティの設定およびほとんどのメソッドの実行。

この2番目にあるように、UDFを書いたセルとは別のセルの値(数式を含む)を変更することは、
できないのです。
これは仕様ですから、致し方がありません。
 
マクロを使うことを検討してください。

回答
投稿日時: 20/10/09 09:56:43
投稿者: WinArrow
投稿者のウェブサイトに移動

 
 
 
投稿日時: 20/10/09 06:45:10
のレスをによると
 セル範囲、AAA,BBB,CCCは、
 各々複数個(説明では9個)のセルがあるということですね?
  
セルに入力する
=IF(条件,固定(AAA),計算実行)
の数式の中、「固定(AAA)」部分について述べますと。
まず、セルには、1つの値しか入りません。ことを理解してください。
AAAは複数のセルですから、勿論、複数のセル値を取得することはできません。
 説明からすると、個々のセル値を取得するものと思います。
 個々のデータを取得するには、
 固定(AAA,列)のように引数を与える必要があります。
  
   
 >最終的に 各列のデータ 例:Da1〜Dc1で時間推移のグラフを作成します。
この説明は、横方向ではなく、縦方向のデータを取得したいということですか?
  
配列数式を使えば、1つの数式で複数のセル値を取得することが可能です。
  
例えば、
セルH2:H4に、B2:B4の値を取得する場合。
セルH2:H4を選択しておいて、
=B2:B4を入力して、[Ctrl]+[Shift]+[Enter]を押します。
※範囲AAAは、横方向ですから、使用できません。
  
全体がZZZという名前定義であるならば
=INDEX(ZZZ,,1) 入力して、[Ctrl]+[Shift]+[Enter]を押します。
※「1」は範囲の中の1番目の列という意味です。
  
ユーザー定義関数でなくても可能だと思います。

回答
投稿日時: 20/10/09 14:34:12
投稿者: Suzu

少なくとも、ユーザー定義関数を使って ユーザー定義関数が入力されている自セル
または、他のセルの値を変更する事は出来ません。
この辺は simpleさんが既に提示くださっています。
 
 
値を保存する処理を行う契機をユーザー定義関数は「再計算」
マクロはではどのタイミングにするかさえきちんと定義できれば
マクロを使用しても、開発の手間としては変わりませんよね。
 
 
どうしてもマクロが使用できないのであれば

引用:
はじめから 表の形で説明しておけば分かりやすかったかな と反省してます。

おっしゃる様に、今からでも、表形式に変更する事をお勧めします。
 
 
範囲 A、B、C のセル個数は、同じで、内容も同じ。時系列が違うのですよね。
 
縦方向に時系列的に全時系列のデータを累積的に持ち
 
見せる為の表を別シート(または別ブック)に用意し、VLOOKUP関数やMACHI関数/INDEX関数等を使用し
見せる為の表に値を表示する様にしましょう。

投稿日時: 20/10/09 20:09:39
投稿者: Skit
メールを送信

各 投降者様
 
 UDFの適用制限が理解できました。基本的に 私のUDFに対する幻想が間違って居たため 各投降者様の理解を簡単に得られなかったのだろう と思います。いわゆる会話が成立しない・・
 
 UDFの制限に関してSimpleさんの指摘で初めて初めて正しく認識しました。 「UDFではエクセルの環境を変更出来ない」 と言う事を認識した上でもう一度各投降者様の記事を読み納得出来るようになりました。
 
 
 今後はマクロで現状の課題を解決しようと思います。
PS
 WinArrow様のコメントで 配列数式、 Index等はまだ体験した事が無いので今後学習します。
 
 とりあえず 今回はUDFの制限を理解したので解決済みとさせて頂きます。
皆様 どうもありがとうございました。