[[20071221173052]]　『マクロ記述の仕方』（KI）　ページの最後に飛ぶ

『マクロ記述の仕方』（KI）

 いつもお世話になります。
 マクロで数式をセルに貼り付けて、実行するとエラーになります。

 手入力で数式を入力した場合には正しい結果が得られているので
 数式に間違いはありません。

 実際の記述は↓のようなものです。

 Range("AO4").FormulaArray = "=SUM(IF(ISNUMBER(--LEFT($E4:$AI4,1)),--MID($E4:$AI4,1,FIND(""/"",$E4:$AI4)-1),""""))+SUM(IF(LEFT($E4:$AI4,2)=""出荷"",--MID(SUBSTITUTE($E4:$AI4,""出荷"",""""),1,FIND(""/"",SUBSTITUTE($E4:$AI4,""入荷"",""""))-1),""""))+SUM(IF(LEFT($E4:$AI4,2)=""在庫"",--MID(SUBSTITUTE($E4:$AI4,""在庫"",""""),1,FIND(""/"",SUBSTITUTE($E4:$AI4,""在庫"",""""))-1),""""))"

 このように FormulaArray で、配列数式の確定をさせているのですがエラーになります。
 ちなみに、FormulaArray を取ってやると AO4セル にうまく数式が入ります。
 それを、 Ctrl + Shift + Enter で確定すると正しい計算結果が得られます。
 このような、長い数式は FormulaArray を使用するのは無理なんでしょうか？
 自分はマクロの知識は自動記録したものをチョコっと触る程度の超初心者です。
 宜しくお願い致します。　Windows2000  Excel2000
   (KI)

 試してないので、違ってたらすみません。
 FormulaArray プロパティのヘルプに以下のことが書いてあります。
「解説」
このプロパティを使って、配列数式として入力するときは、数式には R1C1 形式を使う必要があります。
A1 形式は使えません。詳細は使用例の 2 番目を参照してください。
次の使用例は、シート 1 のセル範囲 E1:E3 に、=SUM(R1C1:R3C3) という配列数式を入力します。

 Worksheets("Sheet1").Range("E1:E3").FormulaArray = _
    "=Sum(R1C1:R3C3)"

 （川野鮎太郎）

 川野様
 早速のアドバイス有難うございます。
 R1C1 形式で入力してみました。
 しかし結果は同じくエラーが帰ってきます。
 通常の$E4:$AI4などの形式でも FormulaArray を使って
 式が短い場合はうまくいってるのですが....不思議です？
    (KI)

 記述として「文字数に制限がある」というのは見あたらないのですが
 こちらで試して見た所、確かに御提示の式は上手く入らないですね。
（私のエクセルは、記録すらさせてくれません。）
 ただし、パート毎なら入力出来る様なので作業セルを３つ使って
 AO4セルにはその３セルの合計を出すようにするのでは駄目なのですかね？

 あとは、式に無駄がありそうなのでその辺を減らせば入力出来るようになるかもしれません。
（短くならないかもしれませんし
　短くなったとしても、入力出来る程にはならない可能性もありますが。）

 (HANA)

 おはようございます。。。♪

 式を名前定義する方法はいかがでしようか。。。

 3つの式を
 それぞれ、名前定義して　たとえば　=式1 =式2 =式3 とします。

 その上で

 Range("AO4").Formula ="=式1+式2+式3"

 とすれば、大丈夫と思います。

 ただ、2番目の式の

 SUM(IF(LEFT($E4:$AI4,2)=""出荷"",--MID(SUBSTITUTE($E4:$AI4,""出荷"",""""),1,
FIND(""/"",SUBSTITUTE($E4:$AI4,""入荷"",""""))-1),""""))

 は、合っていますか？計算出来ない様な気がするんですが。。。。?
 カン違いならゴメンナサイ！！

 ご参考にどうぞ。。。

 。。。。Ms.Rin～♪♪

 (HANA)様、Ms.Rin様有難うございます。

 (HANA)様の言われるように3っつに分割する方法、思いつきませんでした。
 試してみたいと思います。が、表のレイアウトの関係上、出来れば同一セルで
 と考えています。

 Ms.Rin様、ご指摘の通り2番目の式間違っていました。"入荷"ではなく"出荷"
 というのが、正しいです。転記ミスでした。

 実はこの式、表見出し行として E3：AI3  には 1～31 の日付が入ります。
 そして、その下の4行目以降は書式設定が文字列に設定してあります。
 そして例えば、AA4 には"在庫2/2"、AC4 には"1/4"、AE4 には"出荷2.5/1"
 といった具合に文字列としてデータが入ります、また空白の場合もあります。
 今回問題の、AO4 セルの長～い式でやろうとしている事は、"/" の左側の数字を数値化
 して合計することでした。
 わざわざ、このような面倒な数式にせず "/" の右側と左側をそれぞれ行を変えて
 入力するとかして、別のセルに入力すれば、何の問題もないことなのですが、
 すでに出来上がっている既存の表レイアウトを極力変えたくなかったものですから、
 今回のような苦肉の策を取らざるを得ませんでした。

 数式自体は、思いつきの "行き当たりばったり" なので簡素化出来る要素は
 たぶんにあることは、自分自身でも感じているのですが
 手動で Ctrl+Shift+Enter で正しい答えが帰ってきているのに、
 FormulaArray で、なぜ出来ないのかという疑問から質問させていただきました。

  >>3つの式をそれぞれ、名前定義してたとえば　=式1 =式2 =式3 とします。
  試させていただきたいと思います。"名前定義"とはどういった方法なのでしょう？

      (KI)

 こんばんは～♪

 まだ、解決していない様ですので。。。。

 名前定義より、式を作ってみました。。。
 入力する文字列の情報が、不足している気がするのですが。。。

 こんな表の場合です。。

 	 E         F     G       H            I
[4]	在庫2/2         1/4    出荷2.5/1   在庫10/20 ・・・・・答え 15.5

 式は。。。

 FormulaArray なら

 =SUM((0&SUBSTITUTE(SUBSTITUTE(LEFT($E4:$AI4,FIND("/",$E4:$AI4&"/")-1),"在庫",),"出荷",))*1)

  Formula なら
 =SUMPRODUCT((0&SUBSTITUTE(SUBSTITUTE(LEFT($E4:$AI4,FIND("/",$E4:$AI4&"/")-1),"在庫",),"出荷",))*1)

 や
 =SUM(INDEX((0&SUBSTITUTE(SUBSTITUTE(LEFT($E4:$AI4,FIND("/",$E4:$AI4&"/")-1),"在庫",),"出荷",))*1,))

 でいかがでしょうか。。。

 ご参考にどうぞ。。。

 。。。Ms.Rin～♪♪

 > 手動で Ctrl+Shift+Enter で正しい答えが帰ってきているのに、
 > FormulaArray で、なぜ出来ないのかという疑問から質問させていただきました。

  いろいろ実験してみますと、VBAで式を設定する場合、式の文字数は「256」が
 限界のようです。

  ※　つまり、手動入力の場合とは文字数の制限が異なる、と云うことだと思います。
  　　VBAの使い手は、そんなに長い数式入力に頼るハズがない、と云う割り切りかも
 　　 知れません。真相不分明。

 (半平太)

 私が「簡素化出来る」と思った部分について書いてみます。

 MID関数は
 　MID(文字列,開始位置,文字数)
 　　●文字列の任意の位置から指定された文字数の文字を返します。
 と言う関数です。

 たとえば「在庫2/2」と言う文字から/の前の2を取り出したい時は
 最初の２文字は「在庫」と言う文字で要らないので
 　開始位置は「3」
 /の位置は、最初から数えて4番目です。
 　この数字は、「在庫」と言う2文字+欲しい文字数+「/」と言う1文字
 　なので、欲しい文字数は  4（/の位置）-2（在庫）-1（/）
 と言う事で
 --MID("在庫2/2",3,4-3)　で取り出すことが出来ます。

 /の位置は、FIND関数で求められますよね。
 つまり、3文字目から「/」の一つ前までの数値を取り出したければ
 --MID($E4:$AI4,3,FIND(""/"",$E4:$AI4)-3)
 この式で実行する事が出来ます。
 例えば↓の部分の式の事です。
 --MID(SUBSTITUTE($E4:$AI4,""在庫"",""""),1,FIND(""/"",SUBSTITUTE($E4:$AI4,""在庫"",""""))-1)

 この式を実行したいのは
 LEFT($E4:$AI4,2)=""在庫"" もしくは　LEFT($E4:$AI4,2)=""出荷""
 の時なので、IF関数に入れて、更に合計が必要なのでSUM関数にも入れます
 SUM(IF((LEFT($E4:$AI4,2)=""在庫"")+(LEFT($E4:$AI4,2)=""出荷""),--MID($E4:$AI4,3,FIND(""/"",$E4:$AI4)-3),""""))

 ISNUMBER(--LEFT($E4:$AI4,1))の時の計算と足し算しても
"=SUM(IF(ISNUMBER(--LEFT($E4:$AI4,1)),--MID($E4:$AI4,1,FIND(""/"",$E4:$AI4)-1),""""))
+SUM(IF((LEFT($E4:$AI4,2)=""在庫"")+(LEFT($E4:$AI4,2)=""出荷""),--MID($E4:$AI4,3,FIND(""/"",$E4:$AI4)-3),""""))"
 ですので、確定出来る様になると思いますよ。
（↑は改行してあるので、お試しの際は改行を無くしてください。）

 (HANA)

 Ms.Rinさん、色々な数式有難うございます。
 大変参考になりました。
 HANAさん、数式の丁寧な解説とてもよく解りました。有難うございます。
 半平太さん、やはり文字数制限だったんですね。
 そんな気はしてたんですが、配列数式ではない場合など256文字以上の式
 でも、いけてたので大丈夫かなと思っていました。
 皆様有難うございました、大変参考になりました。
     (KI)

コメント返信：

[ 一覧(最新更新順) ]

[[20071221173052]] 『マクロ記述の仕方』（KI） ページの最後に飛ぶ

[[20071221173052]]　『マクロ記述の仕方』（KI）　ページの最後に飛ぶ