Excel (VBA)

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

 
(指定なし : 指定なし)
データの検索
投稿日時: 18/05/16 21:01:53
投稿者: mattuwan44

┌───┬────────────┬─┬────────┬───┬─┬──┬─┐
│      │                        │費│                │材料規│単│    │取│
│年月日│工種規格                │目│名称            │格    │位│数量│引│
│      │                        │  │                │      │  │    │先│
├───┼────────────┼─┼────────┼───┼─┼──┼─┤
│      │巻出し・敷均し・締固め(│外│巻出し・敷均し・│      │m│    │  │
│ 43235│FRS工法)               │注│締固め(FRS工法 │      │3 │  24│  │
│      │                        │費│)              │      │  │    │  │
├───┼────────────┼─┼────────┼───┼─┼──┼─┤
│      │壁面材組立・設置(FRS工 │外│壁面材組立・設置│      │m│    │  │
│ 43235│法)                    │注│(FRS工法)     │      │2 │10.8│  │
│      │                        │費│                │      │  │    │  │
├───┼────────────┼─┼────────┼───┼─┼──┼─┤
│      │                        │外│補強材敷設(FRS │      │m│    │  │
│ 43235│補強材敷設(FRS工法)   │注│工法)          │      │2 │  18│  │
│      │                        │費│                │      │  │    │  │
├───┼────────────┼─┼────────┼───┼─┼──┼─┤
│      │                        │外│補強材敷設(UC工│      │m│    │  │
│ 43235│補強材敷設(UC工法)    │注│法)            │      │2 │67.2│  │
│      │                        │費│                │      │  │    │  │
├───┼────────────┼─┼────────┼───┼─┼──┼─┤
│      │巻出し・敷均し・締固め(│外│巻出し・敷均し・│      │m│    │  │
│ 43235│UC工法)                │注│締固め(UC工法)│      │3 │40.3│  │
│      │                        │費│                │      │  │    │  │
├───┼────────────┼─┼────────┼───┼─┼──┼─┤
│      │壁面材組立・設置(UC工法 │外│壁面材組立・設置│      │m│    │  │
│ 43235│)                      │注│(UC工法)       │      │2 │  18│  │
│      │                        │費│                │      │  │    │  │
├───┼────────────┼─┼────────┼───┼─┼──┼─┤
│      │                        │外│                │      │  │    │  │
│ 43235│暗渠排水管              │注│暗渠排水管      │      │m│  12│  │
│      │                        │費│                │      │  │    │  │
├───┼────────────┼─┼────────┼───┼─┼──┼─┤
│      │壁面材組立・設置(UC工法 │材│                │      │セ│    │  │
│ 43230│)                      │料│鋼製ユニット    │UC-60P│ッ│ 127│  │
│      │                        │費│                │      │ト│    │  │
├───┼────────────┼─┼────────┼───┼─┼──┼─┤
│      │壁面材組立・設置(UC工法 │材│補強拘束ネット付│      │m│    │  │
│ 43230│)                      │料│植生シート      │      │2 │ 125│  │
│      │                        │費│                │      │  │    │  │
├───┼────────────┼─┼────────┼───┼─┼──┼─┤
│      │                        │材│                │ジオフ│m│    │  │
│ 43230│補強材敷設(UC工法)    │料│層厚管理材      │リース│2 │ 150│  │
│      │                        │費│                │SP    │  │    │  │
├───┼────────────┼─┼────────┼───┼─┼──┼─┤
│      │                        │材│                │      │  │    │  │
│ 43230│補強材敷設(UC工法)    │料│水平排水材      │EF-3  │m│ 150│  │
│      │                        │費│                │      │  │    │  │
├───┼────────────┼─┼────────┼───┼─┼──┼─┤
│      │                        │材│                │      │  │    │  │
│ 43230│補強材敷設(UC工法)    │料│固定ピン        │      │本│ 900│  │
│      │                        │費│                │      │  │    │  │
└───┴────────────┴─┴────────┴───┴─┴──┴─┘

 
上記のような複数項目あるデータを
一覧表の中で探しその一覧表の同じデータに数量を合計した数量を書き足したいとき、
どのように検索してますか?
 
ぱっと思いつくところでは、2次元配列に吐き出してひたすらループかなと思いますが、
他に何か案があれば参考にさせてください。
 

投稿日時: 18/05/16 22:27:37
投稿者: mattuwan44

そかぁ、
各項目をAndで繋いでチェックする手があるのかぁ。。。

回答
投稿日時: 18/05/17 09:35:46
投稿者: sk

引用:
上記のような複数項目あるデータ一覧表の中で探し

この両者は同じワークシート/表を指しているのか、
異なるワークシート/表を指しているのか、どちらなのでしょうか。
 
引用:
その一覧表の同じデータに数量を合計した数量を書き足したいとき、
どのように検索してますか?

引用:
ぱっと思いつくところでは、2次元配列に吐き出してひたすらループかなと思いますが、
他に何か案があれば参考にさせてください。

SQL が書けるなら ADO を使って集計してもよさそうですが。

投稿日時: 18/05/17 09:55:54
投稿者: mattuwan44

>この両者は同じワークシート/表を指しているのか、
>異なるワークシート/表を指しているのか、どちらなのでしょうか。

異なる表です。
 
全体の予定数量の一覧があるんですが、
別途、日々の毎日の出来た数量を入力・累計し、
残りの数量を計算したいと考えています。
 
 

回答
投稿日時: 18/05/17 10:13:09
投稿者: Suzu

処理の内容がイメージできていないので聞かせて下さい。
 

引用:
上記のような複数項目あるデータを 一覧表の中で探し
その一覧表の同じデータに数量を合計した数量を書き足したい

 
一覧表の中で、複数の列に対し、それぞれ ある値が【含まれているか】或いは【一致】するかを検索し
一致すれば。。。と言う意味でしょうか。
 
ここは、オートフィルターで良さそうです
 
 
引用:
同じデータに数量を合計した数量を書き足したい

どこに「書き足す」のでしょうか。
既存の「数量」の セルの値 に 「__合計値」の様にしたいのでしょうか。
 
 
skさんの書込みを見て。。
検索を行う一覧が提示のデータで、別表で被検索対象のデータがあるのか。。
 
だとすると、SQLが使えれば確かに楽ですね。
特に、【含まれる】にて検索しなければならい列が複数ある場合
オートフィルターの様に、被検索値を選択する作業が不要になりますから。。
 
もし、
【含まれて】が一列のみ なら 含まれるかどうかの判定をする作業列を作る方法もありますね。

回答
投稿日時: 18/05/17 10:47:56
投稿者: sk

引用:
異なる表です。
  
全体の予定数量の一覧があるんですが、
別途、日々の毎日の出来た数量を入力・累計し、
残りの数量を計算したいと考えています。

それは具体的にどのようなレイアウトの表なのでしょうか。

回答
投稿日時: 18/05/17 11:12:42
投稿者: sk

Suzu さんの引用:
skさんの書込みを見て。。
検索を行う一覧が提示のデータで、別表で被検索対象のデータがあるのか。。
  
だとすると、SQLが使えれば確かに楽ですね。
特に、【含まれる】にて検索しなければならい列が複数ある場合
オートフィルターの様に、被検索値を選択する作業が不要になりますから。。

場合によっては VBA を使わなくても SUMIF 関数か SUMIFS 関数でも
使った方がいいのではないかとも思っていますので、
現時点では「そういう方法もある」程度の提案です。

投稿日時: 18/05/17 11:21:40
投稿者: mattuwan44

リソース予算シート>>
これが、元になります。
単価・金額・取引先は出せないので空白にしてます。
 

┌──────────┬─┬───────┬───┬─┬───┬─┬─┬─┬─┐
│                    │  │              │      │  │      │  │  │取│  │
│工種規格            │費│              │      │単│      │単│金│引│摘│
│                    │目│名称          │規格  │位│数量  │価│額│先│要│
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │外│              │      │m│      │  │  │  │  │
│掘削(土砂)         │注│掘削          │土砂  │3 │   794│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │外│              │      │m│      │  │  │  │  │
│路体盛土            │注│路体盛土      │      │3 │   474│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │材│              │      │m│      │  │  │  │x│
│路体盛土            │料│真砂土        │      │3 │ 616.2│  │  │  │1.│
│                    │費│              │      │  │      │  │  │  │3 │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │外│              │      │m│      │  │  │  │  │
│路体(築堤)盛土     │注│路体盛土      │      │3 │     2│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │外│              │      │m│      │  │  │  │  │
│埋戻し              │注│路体盛土      │      │3 │    51│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │材│              │      │m│      │  │  │  │x│
│埋戻し              │料│真砂土        │      │3 │  66.3│  │  │  │1.│
│                    │費│              │      │  │      │  │  │  │3 │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │外│              │      │m│      │  │  │  │  │
│路床盛土            │注│路体盛土      │      │3 │   305│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │材│              │      │m│      │  │  │  │x│
│路床盛土            │料│真砂土        │      │3 │ 396.5│  │  │  │1.│
│                    │費│              │      │  │      │  │  │  │3 │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │外│              │      │m│      │  │  │  │  │
│土砂等運搬          │注│運搬          │土砂  │3 │   794│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │外│              │      │m│      │  │  │  │  │
│整地                │注│整地          │土砂  │3 │   794│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │機│              │      │ケ│      │  │  │  │  │
│整地                │械│バックホウ    │0.7m3│月│     3│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │外│              │      │m│      │  │  │  │  │
│法面整形(切土部)  │注│法面整形      │切土部│2 │    13│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │外│              │      │m│      │  │  │  │  │
│法面整形(盛土部)    │注│法面整形      │盛土部│2 │    73│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│壁面材組立・設置(UC │外│壁面材組立・設│      │m│      │  │  │  │  │
│工法)              │注│置(UC工法)   │      │2 │ 164.7│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│壁面材組立・設置(UC │材│              │      │セ│      │  │  │  │  │
│工法)              │料│鋼製ユニット  │UC-60P│ッ│   227│  │  │  │  │
│                    │費│              │      │ト│      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│壁面材組立・設置(UC │材│              │      │  │      │  │  │  │  │
│工法)              │料│植生土のう    │      │袋│    33│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│壁面材組立・設置(UC │材│補強拘束ネット│      │m│      │  │  │  │  │
│工法)              │料│付植生シート  │      │2 │   275│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│壁面材組立・設置(UC │材│              │      │m│      │  │  │  │  │
│工法)              │料│壁面強化材    │UC-20S│2 │   108│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│壁面材組立・設置(UC │材│              │      │セ│      │  │  │  │  │
│工法)              │料│連結金具      │      │ッ│    50│  │  │  │  │
│                    │費│              │      │ト│      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│壁面材組立・設置(UC │材│              │高強度│セ│      │  │  │  │  │
│工法)              │料│連結金具      │追加用│ッ│    28│  │  │  │  │
│                    │費│              │      │ト│      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │外│補強材敷設(UC│      │m│      │  │  │  │  │
│補強材敷設(UC工法)│注│工法)        │      │2 │ 918.5│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │材│              │      │m│      │  │  │  │  │
│補強材敷設(UC工法)│料│アデム        │HG-50 │2 │   612│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │材│              │      │  │      │  │  │  │  │
│補強材敷設(UC工法)│料│アデム        │HG-60 │m2│   108│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
├──────────┼─┼───────┼───┼─┼───┼─┼─┼─┼─┤
│                    │材│              │      │  │      │  │  │  │  │
│補強材敷設(UC工法)│料│アデム        │HG-80 │m2│   144│  │  │  │  │
│                    │費│              │      │  │      │  │  │  │  │
└──────────┴─┴───────┴───┴─┴───┴─┴─┴─┴─┘

 
入力シート>>
 別途日付入力欄があります。
  本日のリソース                                                                
┌─────────────┬─┬──────────┬─┬─┬───┬─┬─┐
│                          │  │                    │  │  │      │取│  │
│工種規格                  │費│                    │規│単│      │引│摘│
│                          │目│名称                │格│位│数量  │先│要│
├─────────────┼─┼──────────┼─┼─┼───┼─┼─┤
│巻出し・敷均し・締固め(UC│材│                    │C-│m│      │  │  │
│工法)                    │料│クラッシャーラン    │40│3 │    22│  │  │
│                          │費│                    │  │  │      │  │  │
├─────────────┼─┼──────────┼─┼─┼───┼─┼─┤
│巻出し・敷均し・締固め(UC│材│                    │  │m│      │  │  │
│工法)                    │料│真砂土              │  │3 │   130│  │  │
│                          │費│                    │  │  │      │  │  │
├─────────────┼─┼──────────┼─┼─┼───┼─┼─┤
│                          │外│                    │  │m│      │  │  │
│補強材敷設(UC工法)      │注│補強材敷設(UC工法)│  │2 │ 110.8│  │  │
│                          │費│                    │  │  │      │  │  │
├─────────────┼─┼──────────┼─┼─┼───┼─┼─┤
│巻出し・敷均し・締固め(UC│外│巻出し・敷均し・締固│  │m│      │  │  │
│工法)                    │注│め(UC工法)        │  │3 │    45│  │  │
│                          │費│                    │  │  │      │  │  │
├─────────────┼─┼──────────┼─┼─┼───┼─┼─┤
│巻出し・敷均し・締固め(FR│外│巻出し・敷均し・締固│  │m│      │  │  │
│S工法)                   │注│め(FRS工法)       │  │3 │    30│  │  │
│                          │費│                    │  │  │      │  │  │
├─────────────┼─┼──────────┼─┼─┼───┼─┼─┤
│壁面材組立・設置(FRS工法 │外│壁面材組立・設置(FR│  │m│      │  │  │
│)                        │注│S工法)             │  │2 │  10.8│  │  │
│                          │費│                    │  │  │      │  │  │
├─────────────┼─┼──────────┼─┼─┼───┼─┼─┤
│                          │外│補強材敷設(FRS工法 │  │m│      │  │  │
│補強材敷設(FRS工法)     │注│)                  │  │2 │    36│  │  │
│                          │費│                    │  │  │      │  │  │
├─────────────┼─┼──────────┼─┼─┼───┼─┼─┤
│                          │諸│                    │  │  │      │  │  │
│営繕費                    │経│軽油                │  │? │    48│  │  │
│                          │費│                    │  │  │      │  │  │
└─────────────┴─┴──────────┴─┴─┴───┴─┴─┘

 
リソースシート>>
日々の入力を蓄積していくシートです。
 
┌───┬───────────┬─┬────────┬────┬─┬──┬─┐
│      │                      │費│                │        │単│    │取│
│年月日│工種規格              │目│名称            │材料規格│位│数量│引│
│      │                      │  │                │        │  │    │先│
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │巻出し・敷均し・締固め│外│巻出し・敷均し・│        │m│    │  │
│ 43235│(FRS工法)           │注│締固め(FRS工法 │        │3 │  24│  │
│      │                      │費│)              │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │壁面材組立・設置(FRS │外│壁面材組立・設置│        │m│    │  │
│ 43235│工法)                │注│(FRS工法)     │        │2 │10.8│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │                      │外│補強材敷設(FRS │        │m│    │  │
│ 43235│補強材敷設(FRS工法) │注│工法)          │        │2 │  18│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │                      │外│補強材敷設(UC工│        │m│    │  │
│ 43235│補強材敷設(UC工法)  │注│法)            │        │2 │67.2│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │巻出し・敷均し・締固め│外│巻出し・敷均し・│        │m│    │  │
│ 43235│(UC工法)            │注│締固め(UC工法)│        │3 │40.3│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │壁面材組立・設置(UC工 │外│壁面材組立・設置│        │m│    │  │
│ 43235│法)                  │注│(UC工法)       │        │2 │  18│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │                      │外│                │        │  │    │  │
│ 43235│暗渠排水管            │注│暗渠排水管      │        │m│  12│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │                      │材│                │アートラ│  │    │  │
│ 43228│準備調査費            │料│生石灰          │イム5〜0│t│   4│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │                      │材│                │#3000 │  │    │  │
│ 43217│準備調査費            │料│ブルーシート    │2kx3k│枚│  20│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │                      │諸│                │        │  │    │  │
│ 43217│準備調査費            │雑│赤スプレー      │        │本│   6│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │巻出し・敷均し・締固め│材│                │        │m│    │  │
│ 43217│(UC工法)            │料│真砂土          │        │3 │97.5│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │巻出し・敷均し・締固め│材│                │        │m│    │  │
│ 43217│(UC工法)            │料│クラッシャーラン│C-40    │3 │  55│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │巻出し・敷均し・締固め│材│                │        │m│    │  │
│ 43222│(FRS工法)           │料│クラッシャーラン│C-40    │3 │  11│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │                      │諸│                │        │  │    │  │
│ 43216│準備調査費            │雑│杭              │30x30  │本│  25│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │                      │諸│                │        │  │    │  │
│ 43216│準備調査費            │雑│野地            │60mm  │坪│   1│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │巻出し・敷均し・締固め│材│                │        │m│    │  │
│ 43229│(UC工法)            │料│真砂土          │        │3 │ 130│  │
│      │                      │費│                │        │  │    │  │
├───┼───────────┼─┼────────┼────┼─┼──┼─┤
│      │巻出し・敷均し・締固め│材│                │        │m│    │  │
│ 43229│(UC工法)            │料│クラッシャーラン│C-40    │3 │16.5│  │
│      │                      │費│                │        │  │    │  │
└───┴───────────┴─┴────────┴────┴─┴──┴─┘

 
今の構想では、リソース予算シートの表の右側に、現状の合計欄と、残数量の欄を追加して計算し、
それを、入力シートに反映出来ればいいなと考えてます。

回答
投稿日時: 18/05/17 13:54:52
投稿者: Suzu

実データを出してほしい訳ではなく、考え方を提示する為にも
 

引用:
今の構想では、リソース予算シートの表の右側に、現状の合計欄と、残数量の欄を追加して計算し、
それを、入力シートに反映出来ればいいなと考えてます。

 
この根拠と言うか計算式を考えるのに、
「どこ」と「どこ」が一致したら「ここ」に
「どこ」と「どこ」の一致した「数値」の合計を入れたい とか。
そのモデルが欲しいのです。
 
何を以て合計とするのか、残数 の計算方法が判りません。
何を以て が、一致なのか、部分一致 なのかも提示してくださいね。

回答
投稿日時: 18/05/17 14:29:48
投稿者: sk

引用:
今の構想では、リソース予算シートの表の右側に、
現状の合計欄と、残数量の欄を追加して計算し、
それを、入力シートに反映出来ればいいなと考えてます。

(標準モジュール)
-------------------------------------------------------------------------
Public Sub subCreateInputsheet()
On Error GoTo Err_subCreateInputsheet
 
    Dim ws As Excel.Worksheet
 
    Dim adoCN As ADODB.Connection
    Dim adoRS As ADODB.Recordset
     
    Dim strSQL As String
    Dim strSubSQL As String
    Dim lngCol As Long
     
    Set adoCN = New ADODB.Connection
     
    With adoCN
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Extended Properties").Value = "Excel 12.0;HDR=YES"
        .Open ThisWorkbook.FullName
    End With
     
    strSubSQL = "SELECT t2.[工種規格], t2.[費目], t2.[名称], t2.[材料規格]" & _
                ", t2.[単位], Sum(t2.[数量]) AS [数量合計], t2.[取引先]" & _
                " FROM [リソースシート$] t2" & _
                " GROUP BY t2.[工種規格], t2.[費目], t2.[名称], t2.[材料規格]" & _
                ", t2.[単位], t2.[取引先]"
 
    strSQL = "SELECT t1.[工種規格], t1.[費目], t1.[名称], t1.[規格]" & _
             ", t1.[単位], t1.[数量] AS [予定数量], q2.[数量合計]" & _
             ", IIf(t1.[数量] Is Null,0,t1.[数量]) - IIf(q2.[数量合計] Is Null,0,q2.[数量合計]) AS [残数量]" & _
             ", t1.[取引先], t1.[摘要]"
              
    strSQL = strSQL & _
             " FROM [リソース予算シート$] AS t1" & _
             " LEFT JOIN (" & strSubSQL & ") q2" & _
             " ON IIf(t1.[工種規格] Is Null,'',t1.[工種規格]) = IIf(q2.[工種規格] Is Null,'',q2.[工種規格])" & _
             " AND IIf(t1.[費目] Is Null,'',t1.[費目]) = IIf(q2.[費目] Is Null,'',q2.[費目])" & _
             " AND IIf(t1.[名称] Is Null,'',t1.[名称]) = IIf(q2.[名称] Is Null,'',q2.[名称])" & _
             " AND IIf(t1.[規格] Is Null,'',t1.[規格]) = IIf(q2.[材料規格] Is Null,'',q2.[材料規格])" & _
             " AND IIf(t1.[単位] Is Null,'',t1.[単位]) = IIf(q2.[単位] Is Null,'',q2.[単位])" & _
             " AND IIf(t1.[取引先] Is Null,'',t1.[取引先]) = IIf(q2.[取引先] Is Null,'',q2.[取引先])"
 
    strSQL = strSQL & _
             " WHERE t1.[数量] Is Not Null"
     
    Set adoRS = New ADODB.Recordset
         
    With adoRS
        Set .ActiveConnection = adoCN
        .Source = strSQL
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open
         
        With ThisWorkbook
            Set ws = .Worksheets.Add(Before:=.Worksheets(1))
        End With
         
        ws.Activate
        For lngCol = 1 To .Fields.Count
            ws.Cells(1, lngCol) = .Fields(lngCol - 1).Name
        Next
     
        ws.Cells(2, 1).CopyFromRecordset adoRS
         
    End With
     
Exit_subCreateInputsheet:
On Error Resume Next
 
    Set ws = Nothing
     
    adoRS.Close
    Set adoRS = Nothing
    adoCN.Close
    Set adoCN = Nothing
    Exit Sub
 
Err_subCreateInputsheet:
     
    Debug.Print Err.Number & ": " & Err.Description
    Resume Exit_subCreateInputsheet
 
End Sub
-------------------------------------------------------------------------
 
以上のようなコードによって生成された集計結果を
[リソース予算シート]や[入力シート]に出力する
ような形になるのでしょうか。
 
とりあえず、現時点で判らないのは以下の 4 点。
 
引用:
単価・金額・取引先は出せないので空白にしてます。

・[リソースシート]について、あるグループごとに
 [数量]の合計を求めるとして、そのグループ化の対象となるのは
 どの項目(列)なのか。
 
・[リソース予算シート]と上記の集計結果とを紐付けするとして、
 その紐付けのキーとなるのはどの項目(列)同士なのか。
 
引用:
入力シート>>
別途日付入力欄があります。

・ここでの日付はユーザーが入力するのか、
 それとも何らかのコード/関数によって
 自動的に求められるのか。
 
・上記の日付は[リソースシート]のレコードを集計するに当たって
 何の影響も及ぼさないのか。

投稿日時: 18/05/17 16:42:26
投稿者: mattuwan44

引用:
とりあえず、現時点で判らないのは以下の 4 点。
  
引用:
引用:
単価・金額・取引先は出せないので空白にしてます。

 
・[リソースシート]について、あるグループごとに
 [数量]の合計を求めるとして、そのグループ化の対象となるのは
 どの項目(列)なのか。
  
・[リソース予算シート]と上記の集計結果とを紐付けするとして、
 その紐付けのキーとなるのはどの項目(列)同士なのか。
  
引用:
引用:
入力シート>>
別途日付入力欄があります。

 
・ここでの日付はユーザーが入力するのか、
 それとも何らかのコード/関数によって
 自動的に求められるのか。
  
・上記の日付は[リソースシート]のレコードを集計するに当たって
 何の影響も及ぼさないのか。

 
グループ→数量以外全項目一致で集計します。
紐づけ→紐づけのイメージが今一つ掴めませんが、
リソース予算シートの表の1行1行がキー?だと思いますが。。。。
 
日付→ユーザーです。日付情報を追加して蓄積したのがリソースシートです。
集計にあたっての影響→う〜ん。。。意味が解りかねます。
例えば、5/5のデータを入力したら、その時点までの数量の集計で考えてます。
つまりその時点での5/6以降のデータは集計に含まない。
でもこういうことじゃないのかなぁ。。。
 
 
例えば、
リソース予算シートに↓こういうデータがあったとして、
 
巻出し・敷均し・締固め(UC工法)    材料費    真砂土        m3    806.52
 
入力シートに
5/10
巻出し・敷均し・締固め(UC工法)    材料費    真砂土        m3    97.5
と入れたり
5/12
巻出し・敷均し・締固め(UC工法)    材料費    真砂土        m3    130
といれたりしたとしたら、
 
リソースシートに
5/10    巻出し・敷均し・締固め(UC工法)    材料費    真砂土        m3    97.5
5/12    巻出し・敷均し・締固め(UC工法)    材料費    真砂土        m3    130
と蓄積し、
その結果、
5/12 時点の残りの数量、
つまり579.02を求めるために、
 
巻出し・敷均し・締固め(UC工法)    材料費    真砂土 の数量を集計したいと考えてます。
 
これで、やりたいことが解りますでしょうか?

回答
投稿日時: 18/05/17 18:37:27
投稿者: sk

引用:
グループ→数量以外全項目一致で集計します。

[日付]は[リソース予算シート]には存在しない項目なのですから
グループ化の対象には含まれないのでは。
 
引用:
紐づけ→紐づけのイメージが今一つ掴めませんが、
リソース予算シートの表の1行1行がキー?だと思いますが。。。。

そのキーがそれぞれのシートにおけるどの項目(列)であるかを
具体的に明示されることをお奨めします。
 
(ワークシート[リソース予算シート])
-------------------------------------------------------------------------
工種規格	費目	名称	規格	単位	数量	単価	金額	取引先	摘要
掘削(土砂)	外注費	掘削	土砂	m3	794.0 				
路体盛土	外注費	路体盛土		m3	474.0 				
路体盛土	材料費	真砂土		m3	616.2 				x1.3
路体(築堤)盛土	外注費	路体盛土		m3	2.0 				
埋戻し	外注費	路体盛土		m3	51.0 				
埋戻し	材料費	真砂土		m3	66.3 				x1.3
路床盛土	外注費	路体盛土		m3	305.0 				
路床盛土	材料費	真砂土		m3	396.5 				x1.3
土砂等運搬	外注費	運搬	土砂	m3	794.0 				
整地	外注費	整地	土砂	m3	794.0 				
整地	機械費	バックホウ	0.7m	3ケ月	3.0 				
法面整形(切土部)	外注費	法面整形	切土部	m2	13.0 				
法面整形(盛土部)	外注費	法面整形	盛土部	m2	73.0 				
壁面材組立・設置(UC工法)	外注費	壁面材組立・設置(UC工法)		m2	164.7 				
壁面材組立・設置(UC工法)	材料費	鋼製ユニット	UC-60P	セット	227.0 				
壁面材組立・設置(UC工法)	材料費	植生土のう		袋	33.0 				
壁面材組立・設置(UC工法)	材料費	補強拘束ネット付植生シート		m2	275.0 				
壁面材組立・設置(UC工法)	材料費	壁面強化材	UC-20S	m2	108.0 				
壁面材組立・設置(UC工法)	材料費	連結金具		セット	50.0 				
壁面材組立・設置(UC工法)	材料費	連結金具	高強度追加用	セット	28.0 				
補強材敷設(UC工法)	外注費	補強材敷設(UC工法)		m2	918.5 				
補強材敷設(UC工法)	材料費	アデム	HG-50	m2	612.0 				
補強材敷設(UC工法)	材料費	アデム	HG-60	m2	108.0 				
補強材敷設(UC工法)	材料費	アデム	HG-80	m2	144.0 				
-------------------------------------------------------------------------
 
(ワークシート[リソースシート])
-------------------------------------------------------------------------
年月日	工種規格	費目	名称	材料規格	単位	数量
2018/05/15	巻出し・敷均し・締固め(FRS工法)	外注費	巻出し・敷均し・締固め(FRS工法)		m3	24.0 
2018/05/15	壁面材組立・設置(FRS工法)	外注費	壁面材組立・設置(FRS工法)		m2	10.8 
2018/05/15	補強材敷設(FRS工法)	外注費	補強材敷設(FRS工法)		m2	18.0 
2018/05/15	補強材敷設(UC工法)	外注費	補強材敷設(UC工法)		m2	67.2 
2018/05/15	巻出し・敷均し・締固め(UC工法)	外注費	巻出し・敷均し・締固め(UC工法)		m3	40.3 
2018/05/15	壁面材組立・設置(UC工法)	外注費	壁面材組立・設置(UC工法)		m2	18.0 
2018/05/15	暗渠排水管	外注費	暗渠排水管		m	12.0 
2018/05/10	壁面材組立・設置(UC工法)	材料費	鋼製ユニット	UC-60P	セット	127.0 
2018/05/10	壁面材組立・設置(UC工法)	材料費	補強拘束ネット付植生シート		m2	125.0 
2018/05/10	補強材敷設(UC工法)	材料費	層厚管理材	ジオフリースSP	m2	150.0 
2018/05/10	補強材敷設(UC工法)	材料費	水平排水材	EF-3	m	150.0 
2018/05/10	補強材敷設(UC工法)	材料費	固定ピン		本	900.0 
-------------------------------------------------------------------------
 
1. [リソースシート]のレコードを[工種規格], [費目], [名称],
  [材料規格]([リソース予算シート]における[規格]),
   [単位], [取引先]でグループ化し、グループごとに
   [数量]の合計(以下[数量合計])を求めた結果を得る。
 
([日付]に対する条件を指定しない場合の集計結果)
-------------------------------------------------------------------------
工種規格	費目	名称	材料規格	単位	取引先	数量合計
壁面材組立・設置(FRS工法)	外注費	壁面材組立・設置(FRS工法)		m2		10.8
壁面材組立・設置(UC工法)	外注費	壁面材組立・設置(UC工法)		m2		18
壁面材組立・設置(UC工法)	材料費	補強拘束ネット付植生シート		m2		125
壁面材組立・設置(UC工法)	材料費	鋼製ユニット	UC-60P	セット		127
巻出し・敷均し・締固め(FRS工法)	外注費	巻出し・敷均し・締固め(FRS工法)		m3		24
巻出し・敷均し・締固め(UC工法)	外注費	巻出し・敷均し・締固め(UC工法)		m3		40.3
暗渠排水管	外注費	暗渠排水管		m		12
補強材敷設(FRS工法)	外注費	補強材敷設(FRS工法)		m2		18
補強材敷設(UC工法)	外注費	補強材敷設(UC工法)		m2		67.2
補強材敷設(UC工法)	材料費	固定ピン		本		900
補強材敷設(UC工法)	材料費	層厚管理材	ジオフリースSP	m2		150
補強材敷設(UC工法)	材料費	水平排水材	EF-3	m		150
-------------------------------------------------------------------------
 
2. [リソース予算シート]と上記の集計結果とを
   [工種規格], [費目], [名称], [規格]([材料規格]),
   [単位], [取引先]をキーとして照合し、
   前者の[数量]と後者の[数量合計]との差を求めた結果を得る。
 
(前者のレコードを全て表示した場合)
-------------------------------------------------------------------------
工種規格	費目	名称	規格	単位	取引先	予定数量	数量合計	残数量	摘要
掘削(土砂)	外注費	掘削	土砂	m3		794		794	
路体盛土	外注費	路体盛土		m3		474		474	
路体盛土	材料費	真砂土		m3		616.2		616.2	x1.3
路体(築堤)盛土	外注費	路体盛土		m3		2		2	
埋戻し	外注費	路体盛土		m3		51		51	
埋戻し	材料費	真砂土		m3		66.3		66.3	x1.3
路床盛土	外注費	路体盛土		m3		305		305	
路床盛土	材料費	真砂土		m3		396.5		396.5	x1.3
土砂等運搬	外注費	運搬	土砂	m3		794		794	
整地	外注費	整地	土砂	m3		794		794	
整地	機械費	バックホウ	0.7m	3ケ月		3		3	
法面整形(切土部)	外注費	法面整形	切土部	m2		13		13	
法面整形(盛土部)	外注費	法面整形	盛土部	m2		73		73	
壁面材組立・設置(UC工法)	外注費	壁面材組立・設置(UC工法)		m2		164.7	18	146.7	
壁面材組立・設置(UC工法)	材料費	鋼製ユニット	UC-60P	セット		227	127	100	
壁面材組立・設置(UC工法)	材料費	植生土のう		袋		33		33	
壁面材組立・設置(UC工法)	材料費	補強拘束ネット付植生シート		m2		275	125	150	
壁面材組立・設置(UC工法)	材料費	壁面強化材	UC-20S	m2		108		108	
壁面材組立・設置(UC工法)	材料費	連結金具		セット		50		50	
壁面材組立・設置(UC工法)	材料費	連結金具	高強度追加用	セット		28		28	
補強材敷設(UC工法)	外注費	補強材敷設(UC工法)		m2		918.5	67.2	851.3	
補強材敷設(UC工法)	材料費	アデム	HG-50	m2		612		612	
補強材敷設(UC工法)	材料費	アデム	HG-60	m2		108		108	
補強材敷設(UC工法)	材料費	アデム	HG-80	m2		144		144	
-------------------------------------------------------------------------
 
以上のような結果を得るだけなら、前述したようなコードを実行なされば
事足りるはず。
 
引用:
日付→ユーザーです。日付情報を追加して蓄積したのがリソースシートです。
集計にあたっての影響→う〜ん。。。意味が解りかねます。

何らかのタイミングで「[入力シート]に入力されたレコードを
[リソースシート]の新規レコードとして複写する」という処理を
実行されるだけのつもりなら特に問題はありません。

回答
投稿日時: 18/05/17 19:13:15
投稿者: Suzu

残数データを求める場合、リソースシートの必要性が良く分からないので考えから省きました。
また、紐づけの項目としては、「工業規格」「費目」「名称」 が一致しているデータを
集計すれば良さそうなので 必要以外の列を省くと
 
シート[リソース予算]
-----------------------------------------------------

	A	B	C	D	E	F
1	工業規格	費目	名称	数量	合計	残数
2	いろは	材料費	真砂土	806.52
3
4
5

-----------------------------------------------------
 
シート[入力シート]
-----------------------------------------------------
	A	B	C	D	E
1	日付	工業規格	費目	名称	数量
2	5/10	いろは	材料費	真砂土	97.5
3	5/12	いろは	材料費	真砂土	130
4
5

-----------------------------------------------------
こんな構成になるのかと。。
※灰色の部分は、見た目ずれていますが、コピーしてExcelに貼り付ければ 整いますので、
  そちらで確認下さい。
 
ならば、
リソース予算の E2には、
=SUMIFS(入力シート!$E$2:$E$10,入力シート!$B$2:$B$10,"="&A2,入力シート!$C$2:$C$10,"="&B2,入力シート!D2:D10,"="&C2)
合計数 227.5 を得
 
リソース予算の F2には、
=C2-E2
残数 579.02 が得られます。
 
 
ただし、入力シートの値は、計算済みの日付の値が入っているなら
その日付より後のデータのみを計算対象となる様、計算式をいじります。
=SUMIFS(入力シート!$E$2:$E$10,入力シート!$B$2:$B$10,"="&A2,入力シート!$C$2:$C$10,"="&B2,入力シート!D2:D10,"="&C2,入力シート!A2:A10,">2018/5/11")
 
式をいちいち修正するのが面倒なのであれば、「2018/5/11」をセルの値を参照する様にすれば良いですよね。
 
VBAが必要とすれば、
入力シートから、リソースシートへ
・転記
・転記済のデータを集計対象としない
 
あたりが絡んだ場合でしょうけど、条件が明確でないので何ともですね。
 
現時点で当方の理解としては、まさに
引用:
VBA を使わなくても SUMIF 関数か SUMIFS 関数でも
使った方がいいのではないかとも思っていますので、
この通りですね。

投稿日時: 18/05/18 14:32:53
投稿者: mattuwan44

引用:
現時点で当方の理解としては、まさに
引用:
VBA を使わなくても SUMIF 関数か SUMIFS 関数でも
使った方がいいのではないかとも思っていますので、
この通りですね。

 
使ったことが無かったですが、Sumifs関数で集計出来ることを確認しました。
(空白セルがあると、うまくいかないようなので、ハイホンを必ず入力することにします。)
参照範囲をVBAで書き換えるように作ろうと思います。
 
syさん、Suzuさん、
いろいろな案を考えてくださって、ありがとうございました。

投稿日時: 18/05/18 21:53:07
投稿者: mattuwan44

解決です。