Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


10979 / 13644 ツリー ←次へ | 前へ→

【18807】配列関数とその後の集計 VBA初心者 04/10/12(火) 18:53 質問[未読]
【18808】Re:配列関数とその後の集計 ichinose 04/10/12(火) 19:45 発言[未読]
【18814】Re:配列関数とその後の集計 VBA初心者 04/10/13(水) 8:55 発言[未読]
【18831】Re:配列関数とその後の集計 ichinose 04/10/13(水) 20:51 発言[未読]

【18807】配列関数とその後の集計
質問  VBA初心者  - 04/10/12(火) 18:53 -

引用なし
パスワード
   はじめまして
VBAをはじめて2ヶ月の初心者ですが、
悩みを聞いていただけますでしょうか?

VBAで集計する為の配列関数を挿入し、
値としてその場にペーストしました。
対象Rangeオブジェクトに以下のような流れで操作しています。
1.FormulaArrayプロパティの設定
2.Copy
3.PasteSpecial xlPasteValues)

が、その後それらの値をSUM関数などで集計しようとすると
うまくいきません。
セルを選択してF2を押しENTER確定で数値となるようなのですが
なぜでしょう?
配列関数とおもっているのでしょうか?

一括で計算できる数値とするには良い方法があるでしょうか?
もしご存知の方いましたらご教授お願いいたします。

【18808】Re:配列関数とその後の集計
発言  ichinose  - 04/10/12(火) 19:45 -

引用なし
パスワード
   ▼VBA初心者 さん:
こんばんは。
>VBAをはじめて2ヶ月の初心者ですが、
>悩みを聞いていただけますでしょうか?
>
>VBAで集計する為の配列関数を挿入し、
>値としてその場にペーストしました。
>対象Rangeオブジェクトに以下のような流れで操作しています。
>1.FormulaArrayプロパティの設定
>2.Copy
>3.PasteSpecial xlPasteValues)
>
>が、その後それらの値をSUM関数などで集計しようとすると
>うまくいきません。
↑この表現だけに留めるのは止めませんか?
どのようにうまくいかないのか具体的に記述して下さい。


どんな数式を配列として、設定したのか具体的な例を挙げて下さい。
例えば、
セルA1〜A5に
20,15,30,5,4
と入っていて、
セルA6に「=sum(if(a1:a5>=20,a1:a5))」を
配列数式として設定しました。

とか・・・。


>セルを選択してF2を押しENTER確定で数値となるようなのですが
>なぜでしょう?
>配列関数とおもっているのでしょうか?

配列数式の確定は、Ctrl+Shift+Enterキーで行います。

【18814】Re:配列関数とその後の集計
発言  VBA初心者  - 04/10/13(水) 8:55 -

引用なし
パスワード
   >どんな数式を配列として、設定したのか具体的な例を挙げて下さい。

申し訳ありません。
具体的な例を挙げようと思います。

該当するRangeオブジェクトに以下のように
プロパティを設定しています。

FormulaArray = "=VLOOKUP(E2,IF(dat!$B$3:B$" & longDatCount & "=B2,dat!$E$3:L$" & longDatCount & "),8,FALSE)"

longDatCountはデータの数です。
該当する範囲の絞り込み(第二引数)に対しVLOOKUPをかけ
E2に該当したものでかつ
datワークシートのB3〜B選択範囲とB2が該当した行の
8列目を合計しています。

>配列数式の確定は、Ctrl+Shift+Enterキーで行います。

上記でしたら、マクロで代入後うまく動いているようです。
設定セルが多い為できたら数式で値を設定したいのですが…

【18831】Re:配列関数とその後の集計
発言  ichinose  - 04/10/13(水) 20:51 -

引用なし
パスワード
   ▼VBA初心者 さん:
こんばんは。

>>どんな数式を配列として、設定したのか具体的な例を挙げて下さい。
>
>申し訳ありません。
>具体的な例を挙げようと思います。
うーん、↓これだけでは下の数式の真意がはっきりとはわかりません・・。
実例を必ず出してくださいね!!

下の数式の場合は、longDatCountが仮に12だったとき、
dat!B3:B12に例としての数値の明記
セルB2の例としての数値を明記
セルE2の例としての数値を明記
dat!E3:E12の例としての数値の明記
dat!L3:L12の例としての数値の明記

これらの数値を明確にして、結果xxxという数値がほしい
でも、「=xxxx」という数式では、「xxxx」という結果しか返ってこない・・・。

というような記述をしてほしかったのですが・・・・。

で、
>
>該当するRangeオブジェクトに以下のように
>プロパティを設定しています。
>
>FormulaArray = "=VLOOKUP(E2,IF(dat!$B$3:B$" & longDatCount & "=B2,dat!$E$3:L$" & longDatCount & "),8,FALSE)"

・仮にシート名「dat」のセルB3〜B12に1〜10という数値が入っていたとしましょう!!
・又、シート名「dat」のセルE3〜E12には、11〜20という数値が入っていたとしましょう!!
・さらにシート名「dat」のセルL3〜L12には、21〜30という数値が入っていたとしましょう!!

新規ブックのひとつのシートのシート名を「dat」というシート名を付けて、上記のようにセルに数値を入れてください。

・ここでシート名「dat」以外のシートをアクティブにして下さい。
 このシートのセルB2に「3」をセルE2に「13」を入力した後に以下のコードを
 実行してみて下さい。


'===============================
Sub test()
  longDatCount = 12
  With Range("c3")
   .FormulaArray = "=VLOOKUP(E2,IF(dat!$B$3:B$" & longDatCount & "=B2,dat!$E$3:l$" & longDatCount & "),8,FALSE)"
   End With
End Sub
セルC3には、「23」が表示されますよね?
VBA初心者 さんの記述どおりにすると、それらしい結果が出てきますけどね!!


同じ結果は、
'======================================================
Sub test2()
  longDatCount = 12
  With Range("c3")
   .Formula = "=sumproduct((dat!$B$3:B$" & longDatCount & _
         "=B2)*(dat!$e$3:e$" & longDatCount & _
         "=e2)*(dat!$l$3:l$" & longDatCount & "))"
   End With
End Sub

なんて方法でも出すことが出来ます。

VBA初心者 さんの投稿で、私に記述できるのはここまでです。
私の解釈が違っていたのなら、さらに詳細を記述して下さい。

10979 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free