Excel VBA質問箱 IV

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

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


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

【58721】複数条件での合計やカウントをするマクロを作りたい あい 08/11/5(水) 21:47 質問[未読]
【58725】Re:複数条件での合計やカウントをするマク... neptune 08/11/5(水) 22:13 発言[未読]
【58727】Re:複数条件での合計やカウントをするマク... あい 08/11/6(木) 0:01 質問[未読]
【58728】Re:複数条件での合計やカウントをするマク... n 08/11/6(木) 1:12 発言[未読]
【58729】Re:複数条件での合計やカウントをするマク... n 08/11/6(木) 1:51 発言[未読]
【58730】Re:複数条件での合計やカウントをするマク... あい 08/11/6(木) 2:47 質問[未読]
【58744】Re:複数条件での合計やカウントをするマク... n 08/11/6(木) 12:41 発言[未読]
【58749】Re:複数条件での合計やカウントをするマク... あい 08/11/6(木) 18:48 お礼[未読]

【58721】複数条件での合計やカウントをするマクロ...
質問  あい  - 08/11/5(水) 21:47 -

引用なし
パスワード
   また、宜しくお願い致します。
色々調べてみて、データベース関数だとちょっと使い勝手が悪いので、下記のような関数を作ってみました。

Sheet2!J2=COUNT(IF((Sheet1!J202:J65536=Sheet2!A2)*
(Sheet1!K202:K65536=Sheet2!B2)*
(Sheet1!L202:L65536=Sheet2!C2)*(Sheet1!P202:P65536>0)*
(-5%<Sheet1!M202:M65536)*(Sheet1!M202:M65536<=-4%)*
(-10%<Sheet1!N202:N65536)*(Sheet1!N202:N65536<=-9%),Sheet1!P202:P65536))

で、Ctrl + Shift + Enter で確定させてカウントや合計を出しています。
が、

Sheet2!K2=COUNT(IF((Sheet1!J202:J65536=Sheet2!A2)*
(Sheet1!K202:K65536=Sheet2!B2)*(Sheet1!L202:L65536=Sheet2!C2)*
(Sheet1!P202:P65536<0)*(-5%<Sheet1!M202:M65536)*
(Sheet1!M202:M65536<=-4%)*(-10%<Sheet1!N202:N65536)*
(Sheet1!N202:N65536<=-9%),Sheet1!P202:P65536))

Sheet2!L2=SUM(IF((Sheet1!J202:J65536=Sheet2!A2)*
(Sheet1!K202:K65536=Sheet2!B2)*(Sheet1!L202:L65536=Sheet2!C2)*
(Sheet1!P202:P65536>0)*(-5%<Sheet1!M202:M65536)*
(Sheet1!M202:M65536<=-4%)*(-10%<Sheet1!N202:N65536)*
(Sheet1!N202:N65536<=-9%),Sheet1!P202:P65536))

Sheet2!M2=SUM(IF((Sheet1!J202:J65536=Sheet2!A2)*
(Sheet1!K202:K65536=Sheet2!B2)*(Sheet1!L202:L65536=Sheet2!C2)*
(Sheet1!P202:P65536<0)*(-5%<Sheet1!M202:M65536)*
(Sheet1!M202:M65536<=-4%)*(-10%<Sheet1!N202:N65536)*
(Sheet1!N202:N65536<=-9%),Sheet1!P202:P65536))

Sheet2!N2=MIN(IF((Sheet1!J202:J65536=Sheet2!A2)*
(Sheet1!K202:K65536=Sheet2!B2)*(Sheet1!L202:L65536=Sheet2!C2)*
(Sheet1!P202:P65536<0)*(-5%<Sheet1!M202:M65536)*
(Sheet1!M202:M65536<=-4%)*(-10%<Sheet1!N202:N65536)*
(Sheet1!N202:N65536<=-9%),Sheet1!P202:P65536))

といった按配で、Sheet1!P列が正か負かによって他にも集計しなければならない項目があります。
更に各検索項目の検索条件毎(パーセンテージの刻みとか)に集計したいので、

Sheet2!J3=COUNT(IF((Sheet1!J202:J65536=Sheet2!A2)*
(Sheet1!K202:K65536=Sheet2!B2)*(Sheet1!L202:L65536=Sheet2!C2)*
(Sheet1!P202:P65536>0)*(-4%<Sheet1!M202:M65536)*
(Sheet1!M202:M65536<=-3%)*(-10%<Sheet1!N202:N65536)*
(Sheet1!N202:N65536<=-9%),Sheet1!P202:P65536))

Sheet2!J4=COUNT(IF((Sheet1!J202:J65536=Sheet2!A2)*
(Sheet1!K202:K65536=Sheet2!B2)*(Sheet1!L202:L65536=Sheet2!C2)*
(Sheet1!P202:P65536>0)*(-3%<Sheet1!M202:M65536)*
(Sheet1!M202:M65536<=-2%)*(-10%<Sheet1!N202:N65536)*
(Sheet1!N202:N65536<=-9%),Sheet1!P202:P65536))

Sheet2!J10=COUNT(IF((Sheet1!J202:J65536=Sheet2!A2)*
(Sheet1!K202:K65536=Sheet2!B2)*(Sheet1!L202:L65536=Sheet2!C2)*
(Sheet1!P202:P65536>0)*(-4%<Sheet1!M202:M65536)*
(Sheet1!M202:M65536<=-3%)*(-9%<Sheet1!N202:N65536)*
(Sheet1!N202:N65536<=-8%),Sheet1!P202:P65536))

Sheet2!J11=COUNT(IF((Sheet1!J202:J65536=Sheet2!A2)*
(Sheet1!K202:K65536=Sheet2!B2)*(Sheet1!L202:L65536=Sheet2!C2)*
(Sheet1!P202:P65536>0)*(-3%<Sheet1!M202:M65536)*
(Sheet1!M202:M65536<=-2%)*(-9%<Sheet1!N202:N65536)*
(Sheet1!N202:N65536<=-8%),Sheet1!P202:P65536))

という風になる上、集計したいデータもP列だけでなく、Q列もR列も…といった感じで

Sheet2!Y2=COUNT(IF((Sheet1!J202:J65536=Sheet2!A2)*
(Sheet1!K202:K65536=Sheet2!B2)*(Sheet1!L202:L65536=Sheet2!C2)*
(Sheet1!Q202:Q65536>0)*(-5%<Sheet1!M202:M65536)*
(Sheet1!M202:M65536<=-4%)*(-10%<Sheet1!N202:N65536)*
(Sheet1!N202:N65536<=-9%),Sheet1!Q202:Q65536))

Sheet2!Z2=COUNT(IF((Sheet1!J202:J65536=Sheet2!A2)*
(Sheet1!K202:K65536=Sheet2!B2)*(Sheet1!L202:L65536=Sheet2!C2)*
(Sheet1!Q202:Q65536<0)*(-5%<Sheet1!M202:M65536)*
(Sheet1!M202:M65536<=-4%)*(-10%<Sheet1!N202:N65536)*
(Sheet1!N202:N65536<=-9%),Sheet1!Q202:Q65536))

なんてのも必要になってきます。

Sheet1!J202:J65536=Sheet2!A2
Sheet1!K202:K65536=Sheet2!B2
Sheet1!L202:L65536=Sheet2!C2

この3つの検索条件に関しては、集計用のシートに予めA〜C列には検索条件を入れておき、シートを分けて集計したいと考えています。

Sheet1のデータはどんどん入れ替えて、様々なデータの集計を取りたいと思います。


一番簡単なのは、この式を全部コピペしたりフィルしたりして予めシートに書いておき、集計用のシートを作成しておく事だと思います。
といっても、1つの集計シートだけで全部の組合せで1600*40くらいあるので、手作業だと面倒というのもありますし、何より重くてお話しになりません(フリーズ汗

というワケで、計算式を予め書いておくのは諦めて、マクロを書いて計算をやらせてしまおうと思います。
もしフリーズさえしなければ、手作業でやっちゃう方が簡単なんですけれど…。


で、問題はまず、この関数のコードが分かりません。
後、全ての組合せをコードで書くのもあまり頭が良く無い気がするんですけど、何か良い方法はあるんでしょうか?

宜しくお願いします。

【58725】Re:複数条件での合計やカウントをするマ...
発言  neptune  - 08/11/5(水) 22:13 -

引用なし
パスワード
   ▼あい さん:
こんにちは

全部読む根性がないので回答ではありません。
応援メッセージみたいなものです。
>で、問題はまず、この関数のコードが分かりません。
>後、全ての組合せをコードで書くのもあまり頭が良く無い気がするんですけど、
>何か良い方法はあるんでしょうか?
ですが、あれだけの数式を書くスキル(調べ、考え、作るスキル)があるなら
VBAの入門書を1冊購入して読破すれば、「よい方法」が判ると思います。

VBAは開発言語VBのサブセットですから、やはり、ある程度基本を勉強しなければ
自分の好きなようなプログラムを書くことは出来ません。1冊文のサンプルを
実行し読破すれば、とりあえず、ご希望の事は問題なく出来ると思います。

文法などの本当の基本的な事は書いてないようですが、下記のサイト
にもVBAのことは書いてます。
ht tp://www.asahi-net.or.jp/~ef2o-inue/top01.html
ht tp://www6.plala.or.jp/MilkHouse/menu.html
ただ、文法などの基本的な事はやはり、本を購入した方が良いと思います。
文法など基本的な事は非常に大切ですから。

チラ見ですが、if文、比較演算子、変数の使い方、オブジェクト参照の
仕方、などを使えるようになると、どうって事のないような処理と思います。
ちなみに、min,sum,countなどの関数はVBAには用意されてないので、
ワークシート関数をVBAで使うか、自前で関数を書くことになります。

頑張って下さい。

【58727】Re:複数条件での合計やカウントをするマ...
質問  あい  - 08/11/6(木) 0:01 -

引用なし
パスワード
   ▼neptune さん:

本格的に初歩から勉強したいと常々思っているのですが、どうしても目先の事に追われて中々その時間が取れずにいます。

>チラ見ですが、if文、比較演算子、変数の使い方、オブジェクト参照の仕方、などを使えるようになると、どうって事のないような処理と思います。
>ちなみに、min,sum,countなどの関数はVBAには用意されてないので、ワークシート関数をVBAで使うか、自前で関数を書くことになります。


自分で書いてても気が遠くなるので(汗)、ちょっと関数を短くしてみました。

Sheet2!J2=COUNT(IF((Sheet1!J202:J65536=Sheet2!A2)*(Sheet1!P202:P65536>0)
,Sheet1!P202:P65536))

オブジェクトの参照の仕方がイマイチ分かっていないという根本的な問題があるんですけど、取り敢えず単純化して

If Range("J202")=Range("A2") And Rnage("P202")>0 Then
  Range("J2")=Range("P202")
End If

くらいの事は書けるんですが、
J202:J65536の中からA2の条件に合うモノを選び、更にその中からP202:P65536が正の数、とか、その辺が分からないんですよね。

時間が押していて、何とかこの集計を早く終えねばならない状況でして、初歩からやれば解決出来るとは思うのですけれど、教えてもらえないでしょうか。

【58728】Re:複数条件での合計やカウントをするマ...
発言  n  - 08/11/6(木) 1:12 -

引用なし
パスワード
   数式だけだと元データのイメージが湧かないんですが、
ピボットテーブルは検討されたんですかね?
もしくはオートフィルタやフィルタオプションを活用するとか。

>時間が押していて、何とかこの集計を早く終えねばならない状況でして、初歩からやれば解決出来るとは思うのですけれど
取りあえずは今までやってた手法で片づけて、次から『初歩からやれば』いいのでは?
それが現実的だと思いますけど。

【58729】Re:複数条件での合計やカウントをするマ...
発言  n  - 08/11/6(木) 1:51 -

引用なし
パスワード
   >1つの集計シートだけで全部の組合せで1600*40くらい
に耐えられるかどうかわかりませんけど、作業列に集計用のキーを追加してピボットでいけそうな気もします。
例えば『Sheet1!P列が正か負かによって』なら
=SIGN(P2)
でまとめて、
『各検索項目の検索条件毎(パーセンテージの刻みとか)に集計したい』なら
=TRUNC(M2*100)
とか
=TRUNC(N2*100)
とかでまとめて。

【58730】Re:複数条件での合計やカウントをするマ...
質問  あい  - 08/11/6(木) 2:47 -

引用なし
パスワード
   ▼n さん:

ピボットテーブルはチョッとやってみたんですけどチンプンカンプンで挫折しました。
頭がハッキリしている時にもう一度挑戦してみます。
後、メモリ不足と言われてしまいました(汗
これはどうしよう…(汗
集計結果は全て残しておきたいし、Sheet1のデータを入れ替えて複数のデータを調べたいと思っていて、その結果の中で有用と思われる一部はまた別のブックでまとめて比較する事になると思うのですが、ピボットテーブルで表示させた集計結果ってコピペしたり出来るんですか?

フィルタオプションというのは、データの抽出は出来るけどカウントしたり合計したりは出来そうに無いと思って(理解不足かも知れませんが)、検討していません。


Sheet1のJ列・K列・L列は○×とかそういう2択です。
作業列にまとめちゃうという手もあるのかなとも思っていますが、今の所は別々にやろうと思っています。
J列の○×、J列が○の時のK列の○×、J列が○の時のK列が○の時のL列の○×…といった感じで、14通りの分け方をしたいので、まとめちゃうのも如何なものかと自信が無いもので・・・

【58744】Re:複数条件での合計やカウントをするマ...
発言  n  - 08/11/6(木) 12:41 -

引用なし
パスワード
   >ピボットテーブルで表示させた集計結果ってコピペしたり出来るんですか?
はい、できます。コピー後、別シートに[値で貼り付け]すれば良いと思います。

>Sheet1のJ列・K列・L列は○×とかそういう2択です。
>作業列にまとめちゃうという手もあるのかなとも思っていますが、今の所は別々にやろうと思っています。
それはまとめる必要はないです。
まとめると書いたのはP,M,N列についてです。
集計用のキーを作ってしまえばピボットで柔軟な集計が可能です。
>J列の○×、J列が○の時のK列の○×、J列が○の時のK列が○の時のL列の○×…といった感じで
集計が一度にできます。

ピボットテーブルについては以下のサイトが解り易いと思います。
://www11.plala.or.jp/koma_Excel/pivot_menu.html

【58749】Re:複数条件での合計やカウントをするマ...
お礼  あい  - 08/11/6(木) 18:48 -

引用なし
パスワード
   ▼n さん:

ご紹介頂いたサイトを参考にチョッとやってみます。
ありがとうございます

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