Excel (VBA)

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

 
(Windows 7 Professional : Excel 2007)
別のシートへ集計セット2
投稿日時: 19/02/06 17:43:17
投稿者: FILETUBE

こんにちは。
前回はありがとうございました。
1つ教えて下さい。
  
日付 品番 カラー サイズ 受注番号 数量
  
の入力シートに日々入力していきます。
  
それを自動で集計シートに
品番、カラー、サイズで集計するように
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 25
 B 2 10 50 40
   
と入力シートで入力したら自動で集計シートが
更新され,出荷数も再セットするようにしたいのです。
 
今回のコードも載せます。
Private Sub Worksheet_Activate()
    Const adOpenKeyset = 1
    Const adLockReadOnly = 1
  
    Dim cn As Object
    Dim rs As Object
    Dim strSQL As String
      
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
    cn.Open "C:\test\TENSO.xls"
    '*****************************************************************************
    strSQL = strSQL & " SELECT 品番,カラー,サイズ,SUM(数量) AS 数量 "
    strSQL = strSQL & " FROM [入力シート$A:F" & Sheets(1).Rows.Count & "] "
    strSQL = strSQL & " GROUP BY 品番,カラー,サイズ"
    strSQL = strSQL & " ORDER BY 品番,カラー,サイズ"
    '*****************************************************************************
    rs.Open strSQL, cn, adOpenKeyset, adLockReadOnly
  
    Application.ScreenUpdating = False
    '集計シートをクリア
    Rows(ThisWorkbook.Worksheets("集計シート").Range("A2").Offset(1).Row & ":" & Rows.Count).Delete Shift:=xlUp
    '集計シートにデータを出力する
    ThisWorkbook.Worksheets("集計シート").Range("A2").CopyFromRecordset rs
    Application.ScreenUpdating = True
    '後処理
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub
  
どのようにするのか、分かる方おられましたら
教えて頂けないでしょうか。
  
宜しくお願いします。

回答
投稿日時: 19/02/06 18:13:43
投稿者: WinArrow
投稿者のウェブサイトに移動

結局、SQLで対応することにしたんですか?
 
> Rows(ThisWorkbook.Worksheets("集計シート").Range("A2").Offset(1).Row & ":" & Rows.Count).Delete Shift:=xlUp
 
↑このコードは随分乱暴なコードですね?
行削除は乱暴すぎます。
当該範囲をクリアするだけでよいのでは?
 
集計シートにも入力するんですか?
 
システム仕様がわからないから、的確なアドバイスができませんが、
集計シートに入力するのは、考え直した方が良いと思いますよ。
 
SQLで抽出したコードと、手入力下出荷数とが整合取れるとは思いません。

投稿日時: 19/02/06 18:59:26
投稿者: FILETUBE

いつもありがとうございます。
  
集計シートにも入力するという事もあり
ピボットはやめました。
 
まず削除の件は見直します。
行削除ではなく、範囲指定で削除するようにします。
 
「SQLで抽出したコードと、手入力下出荷数とが整合取れるとは思いません」
との事ですが、確かにそうかもしれません。
 
 
今、セット前にDictionaryにセット前を退避しようかと考えています。
 
 Dim dic As Object
 Dim vLst As Long
 Dim y As Long
        
 vLst = ThisWorkbook.Worksheets("集計シート").Range("A1").SpecialCells(xlLastCell).Row
 Set dic = CreateObject("Scripting.Dictionary")
 For y = 1 To vLst
        If ThisWorkbook.Worksheets("集計シート").Cells(y, 2).Value <> "" Then
           dic(ThisWorkbook.Worksheets("集計シート").Cells(y, 2).Value) = Empty
        End If
 Next
 
 
この後どのように比較し元に出荷数をセットするかです。
 
 

回答
投稿日時: 19/02/06 19:23:37
投稿者: WinArrow
投稿者のウェブサイトに移動

私だったら、
入力シート側に「出荷数」列を設け、受注番号別の出荷数を入力し、
一緒に集計しますが・・・
 
でも、受注データを入力する担当と、出荷数を入力する担当が別の人かもしれない。
という疑念があります。
 
 
 

投稿日時: 19/02/06 21:15:04
投稿者: FILETUBE

回答ありがとうございます。
入力シートの入力時はまだ生産段階で
それを集計し、集計シート上で
出荷数を入力する為、やはり出荷数は
集計シートで入力したいのです。
入力する人も違いますし。
 
1.集計と出荷数の退避
2.入力シートを集計し集計シートにセット
3.退避から品番、カラー、サイズを比較し出荷数を
  セット
 
の手順で実行したいのです。

回答
投稿日時: 19/02/06 21:49:43
投稿者: WinArrow
投稿者のウェブサイトに移動

出荷数を手で合計して、入力するのは、間違いのもとです。
 
担当が違うのであれば、別シートで入力シートをと同じようなフォーマットで入力させて、
 
SQLで、シート結合(JOIN)させれば、できるのではないでしょうか?

投稿日時: 19/02/07 06:26:01
投稿者: FILETUBE

WinArrowさん、何度もありがとうございます。
出荷数を入力するのに、入力する前に数は数えてあります。
確認というか、出荷数がおかしくないか
つまり、入力シートの数量を合計した生産数を超えていないか
チェックする為、集計シートで出荷数を入力しようと思いました。
 
入力シート、集計シート、の他に
同じようなフォーマットで出荷数入力シートを作るという事ですよね。
 
申し訳ありません、今一度お聞きしたいのですが
最初の入力シートには次々と品番を追加していきますが
 
生産数入力シートも、品番、カラー、サイズ、受注番号等も
もう一度手入力になりますか?
(実際はもう少し項目があります)
 
できれば出荷数の入力だけがいいと思ったのですが
となりますと、入力シートの内容を出荷数入力シートに転記
しなければならないかと思うのです。
 
どうぞよろしくお願いします。
 
 
 

回答
投稿日時: 19/02/07 09:38:17
投稿者: WinArrow
投稿者のウェブサイトに移動

運用を含めて業務に関するシステムが、まったくわからない状況(わかろうとするつもりもない)で
システム設計の分野まで、アドバイスするつもりはありません。
 
最初の表の説明で
受注番号の右側に「数量」と書いてあったので、
勝手に「受注数」と解釈していたが、
違うようですね。
 
(1)品番について
「品名」と「品番」は違うと考えますが・・・どうでしょうか?(回答は不要です)、
「品番」には、「カラー」「サイズ」を含んだものと推測しています。
それを前提に集計するならば、集計キーとしては、品番だけでよいのではないでしょうか?
集計キーを複数にすると、照合も集計も複雑になってきます。
 
(2)数量の管理
数量には、
@受注数
A生産数・・・・1日なのか1回なのか?
B生産数累計
C出荷数
D集荷数累計
 
データ入力時には、各々の関係をチェックする必要があると思いますから
それが可能なようにデザインします。
・・・その結果、データベースとしてExcelを使うならば、ブックやシートの持ち方が変わります。
(生産結果入力/出荷結果入力/データベース)
ブック/シートが別々になると、同一項目を重複して入力することに繋がりますから、
時系列的に前段で入力したデータを参照(入力させない)するようにします。
私だったら、データ入力用画面にユーザーフォームを使います。
 
(3)参照系
「入力用シート」は、「データベース」にデータを格納するまでのものと考え
「データベース」から、いろんな用途にデータを抽出/集計する処理が必要になってきます。
 
 
以上は、システム設計のヒントです。
それから、生産担当/出荷担当が別の人が前提ならば、
シートではなく、ブックを分けた方が安全運用できると思います。
もちろん、データベースの別ブックになります。
 
※「人間は間違える」(先入観、勘違い等)ということを念頭にシステム設計しないと、
 問題が発生してからプログラム変更すること(待ったなしの状況に陥ります)にならないよう
 頑張ってください。
 
参考になれば・・・

投稿日時: 19/02/07 15:46:10
投稿者: FILETUBE

WinArrowさん、回答ありがとうございます。
 
品番はあくまで品番で、品番、カラー、サイズで集計キーになります。
また数量は生産数と出荷数になり、生産数は随時の入力で
出荷数は最後にまとめて1回入力します。
 
出荷数入力の時、生産数入力と項目がかぶる為できれば前述のように
生産数を集計した結果に出荷数を入力しようと考えました。
 
少し質問と離れてしまい申し訳ありません。
また大変丁寧な回答ありがとうございました。

回答
投稿日時: 19/02/07 16:10:26
投稿者: WinArrow
投稿者のウェブサイトに移動

>品番はあくまで品番で、品番、カラー、サイズで集計キーになります。
 
この3つを1つのコードにするか、
文字列結合して、一意の集計キーにした方が簡単になります。
 
この辺で、私からのアドバイスは終了とします。
 
 
 

投稿日時: 19/02/07 21:06:37
投稿者: FILETUBE

WinArrowさん、色々とありがとうございました。
またよろしくお願いします。