Excel (VBA)

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

 
(Windows 7 Professional : Excel 2007)
別シートに自動反映
投稿日時: 19/02/05 16:57:42
投稿者: FILETUBE

 
こんにちは。
1つ教えて下さい。
 
日付 品番 カラー サイズ 受注番号 数量
 
とある入力用シート(sheet1)に日々入力していきます。
 
それを自動で集計用の別シート(sheet2)に
品番、カラー、サイズで集計し
自動で反映されるようにしたいのですが
VBAでプログラミングしないと無理でしょうか?
 
例えば
日付 品番 カラー サイズ 受注番号 数量
2/1 A 1 10 123 10
2/2 A 1 10 345 20
2/3 B 2 10 567 50
 
 
を集計用シート
品番 カラー サイズ 合計数量
A 1 10 30
B 2 10 50
 
と入力用シートで入力したら自動で集計用シートが
更新されるようにしたいのです。
 
どのようにするのか、分かる方おられましたら
教えて頂けないでしょうか。
 
宜しくお願いします。

回答
投稿日時: 19/02/05 17:33:52
投稿者: WinArrow
投稿者のウェブサイトに移動

VBAで自動集計
は、便利な手段と思いますが、
入力データの入力ミスによる、変更、削除などを
考えるとコードそのものが複雑になります。
 
ピボットテーブルなどを使えば、簡単に集計できます。

投稿日時: 19/02/05 18:24:03
投稿者: FILETUBE

回答ありがとうございます。
 
ミスなどを考えず入力シートで入力されている内容がそのままで
集計シートの方で集計されていればよいのですが
 
strSQL = strSQL & "SELECT 品番,カラー,サイズ,SUM(数量) AS 数量 FROM "
strSQL = strSQL & " FROM [Sheet1$A:F" & Sheets(1).Rows.Count & "] "
strSQL = strSQL & " GROUP BY 品番,カラー,サイズ"
rs.Open strSQL, cn, adOpenKeyset, adLockReadOnly
 
のようにコードで集計し貼り付ける事も考えてみましたが
できればコマンドボタンをクリックしなくて
自動で集計シートに反映されているようにしたいのです。
 
  
あまりピボットテーブルは使った事がないのですが
ピボットテーブルを集計シートに作っておくという事になるのでしょうか?

回答
投稿日時: 19/02/05 20:54:35
投稿者: WinArrow
投稿者のウェブサイトに移動

>あまりピボットテーブルは使った事がないのですが
> ピボットテーブルを集計シートに作っておくという事になるのでしょうか?
 
ピボットテーブルは予め作成する必要はありません。
 
集計値を見たい時にピボットテーブルを作成し、
印刷するなど、用が済んだら、削除します。
つまり、作業用に作成するだけです。
 
入力のたびにピボットテーブルを作成するとか、マクロを動かす必要はないのです。
なぜならば、バックグラウンドで動かすのだから、手間はないと思いがちですが、
データが増えていくにつれて、レスポンスが悪くなります。
 

投稿日時: 19/02/05 21:09:54
投稿者: FILETUBE

WinArrowさん、回答ありがとうございます。
入力は毎日あり、できればその都度のピボットの作成は
避けたいのですが。
 
予めピボットを作成してみましたが
データを変更してみても変更内容がピボットには
反映されないようですが。
 
入力シートの内容を先程の投稿では
ボタンをクリックしないで集計シートに反映と
投稿しましたが、入力終了の判断ができないのなら
ボタンクリックで集計シートに集計でも構いません。
 
何か良い方法はないでしょうか?
今一度、よろしくお願いします。

回答
投稿日時: 19/02/05 22:40:20
投稿者: WinArrow
投稿者のウェブサイトに移動

 
予め集計しておく目的は?
  
集計表を必要とする人が、必要なタイミングで、
ピボットテーブルを作成すれば良いのでは?
 
※ピボットテーブルを作成するときには、
元となるデータの範囲を指定しています。
これから入力するデータは、その指定したデータの範囲外となるため、
反映されないのです。

投稿日時: 19/02/05 23:18:36
投稿者: FILETUBE

回答ありがとうございます。
できれば実務者には、何もしなくて
受注の集計がすぐに見れるようにしたいのです。
 
シートがフォーカスを失ったとかいうイベントで
集計するようなコードを書くしかないかと。
 

投稿日時: 19/02/05 23:35:33
投稿者: FILETUBE

 仮に集計処理を起動するにあたり
 
今まではシートにコマンドボタンを配置して
クリックしたらというコードを書いていましたが
今のシートがアクティブでなくなったら集計処理開始のような
イベントプロシージャは記述可能でしょうか?

回答
投稿日時: 19/02/06 07:39:36
投稿者: simple

作成済みのピボットテーブルのデータ更新をするには、
「データソースの変更」というメニューが用意されているので、
それを利用すればよいでしょう。
マクロ記録をとれば、それをマクロにするヒントが得られます。
 
シートがアクティブで無くなったときに動作させるとのこと。
イベントプロシージャのなかにそういうものがあります。
ご自分で調べて下さい。
ヘルプでもネット上の検索でも簡単に得られます。
VBEのイベント処理を選ぶところにもそれは表示されています。
 
ただし、必要がないとき(データが変更されていないとき)にも
それが動作しますから無駄と言えば無駄になることもあります。

投稿日時: 19/02/06 08:09:24
投稿者: FILETUBE

simpleさん、的確なアドバイスありがとうございます。
 
「必要がないとき(データが変更されていないとき)にも
それが動作しますから無駄と言えば無駄になることもあります。」
とありますがそれも仕方ないかも。
 
少しコードを書いてみようかと思います。

回答
投稿日時: 19/02/06 10:42:28
投稿者: Suzu

あらかじめピボットテーブルを持っておいても良いでしょう。
 
今後、入力範囲が広がるのであれば、ピボットテーブルのデータソースは、
・広がるであろう入力範囲を予め予期しておき、データソースをシート名!A2:C1048576
  の様に、ROWの最大数を入れておく
・入力するデータ範囲を予めテーブルとして定義しておき、データソースにはテーブル名を指定
のどちらかで対応できるでしょう。
 
ピボットテーブルの再集計は
「ピボットツール」「オプション」-「更新」でどうぞ。
 
更新に関しては、わざわざマクロを作らなくとも、
「更新」をリボンに登録するなり、クイックアクセスツールバーに登録すれば済む話でしょう。
 
 
ピボットテーブルに関して FILETUBE さんが ご存じ無いからと言って
ユーザーの方が知らないとは限らないですよね。
 
むしろ、EXCELユーザーにとっては、一般機能のピボットテーブルで間に合う物を
なぜわざわざ マクロで、さらに SQLなんて判らない物を使うの? というレベルと思いますよ。

投稿日時: 19/02/06 15:14:10
投稿者: FILETUBE

回答ありがとうございます。
皆さんのおかげでとっても勉強になりました。
 
また宜しくお願いします。