[[20150823220616]]　『名前定義を複数のセルに』（ten）　ページの最後に飛ぶ

『名前定義を複数のセルに』（ten）

計算１という名前定義に
=IFERROR(EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(原紙!$AX$67,",","+"),"×","*"),"x","*")),0)
という関数を入れています。
このセルには普通に数字が入ったり文字列が入ることもあることからこうなっています。
この名前定義ではAX67のセルのみの指定になっていますが、これをAX67からCH68の複数のせるにも適用したいのですがそれにはどうしたらいいのでしょうか？
AX67:CH68とするとダメなようですが、各セルに名前定義を設定するしかないのでしょうか？
最終的にはAX67からCH68の各セルに入力された数値の合計を出すのですが各セルに名前定義が必要だとかなりの数になってしまうので・・・

いい方法がありましたらアドバイスよろしくお願いします。

＜使用 Excel：Excel2013、使用 OS：Windows8 ＞

VBAによるユーザー定義関数を使う機が熟したといえるでしょう。

http://www.liveway.net/technic/20090530_131028.html
などを参照してください。

 例えば、
 Function myCalc(v As Variant)
     Dim s As String

     s = Replace(Replace(Replace(v, ",", "+"), "×", "*"), "x", "*")
     myCalc = Application.Evaluate(s)
     If IsError(myCalc) Then
         myCalc = 0
     End If
 End Function
 を標準モジュールに書き込み、
 ワークシート上では、
 =myCalc(原紙!AX67)
 などと使います。

（γ） 2015/08/24(月) 06:21

もっとも、今の方式でも複数セルに対する式を名前定義しても可能は可能です。
戻り値は、与えた複数セル領域と同じ大きさのものとなりますから、
予めそれと同じ大きさのセル領域を選択した状態で、
=計算1
と入力して、Ctrl+Enterです。

（γ） 2015/08/24(月) 06:31

コメントありがとうございます。

上記マクロで計算できることは分かったのですがAX67からCH68の合計を出すとき
合計セルには　=myCalc(原紙!AX67)+myCalc(原紙!AY67)+ …　myCalc(原紙!CH68)と全部書かないといけないのでしょうか？
74個にもなるのでそれは回避したいのですが・・・
（ten） 2015/08/24(月) 21:16

 その対象領域が連続した矩形の領域であればγさんのコード内でループさせればいかがでしょう。
 エラーチェックは割愛しエラーがあれば #VALUE!になります。

 セル側は　=cvCalc(原紙!AX67:CH68) といったように記述します。

 標準モジュールに。

 Function CvCalc(r As Range) As Variant
    Dim c As Range
    For Each c In r
        CvCalc = CvCalc + Evaluate(Replace(Replace(Replace(c.Value, ",", "+"), "X", "*"), "x", "*"))
    Next
 End Function

（β） 2015/08/25(火) 00:01

単セル指定だと問題ないのですが、=cvCalc(原紙!AX67:CH68)のようにすると#VALUE!になってしまいますね。
数式で使用されるデータの形式が正しくないと出ています。
対象領域は結合もしていない連続したセルなんですが・・・
（ten） 2015/08/25(火) 00:51

 すでに改善提案が出されていますので、
 後出しの蛇足かもしれませんが。

 結局は一セル毎の確認が必要になるかも知れないので、
 一セルごとに結果を出してもいいんじゃないですか？

 仮に、Sheet1の同じ位置にあるセルに結果を書き出すとすると、

 (1)Sheet1のAX67に
    =myCalc(原紙!AX67)  とします。
 (2)これをAX67:CH68にコピペイストします。
 (3)合計算出セル（例えば、AX66)に
    =SUM(AX67:CH68)
 とするだけではないですか？   
 難しい話でもないと思いますが。
（γ） 2015/08/25(火) 06:00

 >>単セル指定だと問題ないのですが、=cvCalc(原紙!AX67:CH68)のようにすると#VALUE!になってしまいますね。

 コメントしましたが領域内の数式（のような）文字列が【すべて正しければ】計算結果がでるはずなんですが？
 １つでも間違った記述があれば #VALUE! になります。（空白セルも間違いセルとして扱われます）
 γさんのコードのようにエラーチェックをいれれば その間違った式文字列セルの値を 0 として計算することは可能です。
 ただ、膨大なセルで、こういった間違いがあった時に、それが発見されにくいというリスクもあります。

（β） 2015/08/25(火) 06:32

 エラーチェックを入れたコードは以下になります。

 Function CvCalc(r As Range) As Variant
    Dim c As Range
    Dim z As Variant
    For Each c In r
        z = Evaluate(Replace(Replace(Replace(c.Value, ",", "+"), "X", "*"), "x", "*"))
        CvCalc = CvCalc + IIf(IsError(z), 0, z)
    Next
 End Function

（β） 2015/08/25(火) 06:37

理解不足で申し訳ありません。

仮に、Sheet1の同じ位置にあるセルに結果を書き出すとすると、

 (1)Sheet1のAX67に
   =myCalc(原紙!AX67)  とします。
 (2)これをAX67:CH68にコピペイストします。
 (3)合計算出セル（例えば、AX66)に
    =SUM(AX67:CH68)

の部分をやってみたのですが、一度別シートに取り出して合計を出すとういう方法では出来ました。
しかし、なぜか　=myCalc(原紙!AX67:CH68)では動作しないようです。
空白のセルが悪いのかと思って、対象全部のセルに数値を入れてみましたが同様にダメでした。

そこでβさんのエラーチェックのコードで試したところ、無事　=cvCalc(原紙!AX67:CH68)でうまく計算できました。
どこに原因があるのかわからないですが自分なりにもうちょっと探ってみます。
（ten） 2015/08/25(火) 18:33

 >>しかし、なぜか　=myCalc(原紙!AX67:CH68)では動作しないようです。

 私のレスで、「その対象領域が連続した矩形の領域であればγさんのコード内でループさせればいかがでしょう」とコメントしましたよね。

 γさんのコードは、元々、単一セルを対象にしておられて、私のcvCal は、その方式をそのまま借用して
 領域を複数セルにしたものです。（そのために、領域内のセルを取り出すループをいれています）

 ですから「なぜか」ではなく、「そういう仕様」ということですが？

（β） 2015/08/25(火) 18:39

βさんのご指摘のとおりです。ありがとうございました。

myCalcは、単一セルを引数（ひきすう）にすることを念頭においています。
もともとそう言う意図で書いていますから、
複数セル領域で動かないのは当たり前です。

なお、私もエラーの時に 0 にしてしまうのはマズイと思いながら、
とりあえずコードを出してしまいました。

エラーのままにしておくべきでしょうね。
エラー解決が必要でしょう。
もっとも、取りあえずエラー部分を除外してSUMしたいということなら、
文字列(例："error")を返すようにすれば良いでしょう。

Excel4.0のマクロEvaluateを名前定義で使用するといったトリッキーなことよりも、
簡単なマクロ機能を使うことを推奨します。
鶴亀算よりも連立方程式のほうが格段に人間には優しいはずです。

（γ） 2015/08/25(火) 20:05

すいません。そういうことだったんですね。
全然理解できていませんでした。
マクロも始めたばかりの初心者でこれからもっと理解できるよう勉強していきたいと思います。

文字列と数値の計算を混同して使うのはよくないとは分かっているのですが、都合上セルを分けることができなかったためEvaluate使って計算していました。

大変どうもありがとうございました。
（ten） 2015/08/25(火) 23:59

コメント返信：

[ 一覧(最新更新順) ]

[[20150823220616]] 『名前定義を複数のセルに』（ten） ページの最後に飛ぶ

[[20150823220616]]　『名前定義を複数のセルに』（ten）　ページの最後に飛ぶ