Excel (VBA)

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

 
(Windows 7 Home Premium : Excel 2010)
2つのSheetからの計算方法
投稿日時: 19/07/18 00:00:50
投稿者: ダイハード

こんばんは。
よろしくお願いします。
 
2つのSheetがあります。
 
Sheet 工程マスタ
E列:部品コード F列:部品名  I列:工程コード J列:工程名   K列:手配コード   L列 :手配先名
M列:チェックボックス  N列: 段取   O列:自動  P列:手動  Q列:着脱   Z列:M列連動
・不必要と思われる列は省略してあります
・各部品コード:部品名には約5〜10工程があるためE列には同じコードが存在する。
・同じ部品名でJ列:工程名は違っていてもL列:手配先名は同じの場合あり
・手配先名は10〜15くらいあるので同じコードが存在する。
・N列:O列:P列:Q列は各工程の時間
・M列はチェックボックスとなっており ”レ” だとZ列がTRUE オフだとFALSEが連動している。
・M列のチェックボックスのON・OFFの切替えは当月の負荷状況によって見直しをかける。
・新規部品が増えれば都度マスタの更新を図る。現在8000行くらいあります。
 
Sheet 受注一覧
B列:製番  R列:部品コード S列:部品名   V列:生産数  AA列:段取回数
・不必要と思われる列は省略してあります。
・AA列以外はシステムからExcelへのエクスポート
・AA列は工数計算の必要時に段取回数を入力する。
・R列:部品コードは2つ以上ある事がある。ただしB列とR列を&でむすぶと同じものはない。
・毎月 100行〜150行くらいのデータ量になります。
 
やりたい事
・上記2つのSheetから部品ごとの手配先工数を段取:自動:手動:着脱に分けて計算をしたい。
    (Sheet 受注一覧の生産数×Sheet 工程マスタの各時間)
  (Sheet 受注一覧の段取回数×Sheet 工程マスタの段取)
・Sheet 工程マスタのZ列:FALSEの行は無視しても良い。
 
Vlookup関数だけでは無理??また8000行から当月の必要な部品コードを検索するのも大変です。現在は2つのSheetを見比ながら人力で計算しています。計算結果は別Sheetでも構いません。Sheet受注一覧のAA列以降でも構いません。なにかいい方法がないかと相談しました。
わかりにく説明かもしれませんが宜しくお願いします。

回答
投稿日時: 19/07/18 09:32:14
投稿者: Suzu

こんにちは。
 
工程マスタ

部品コード	工程コード	手配コード	段取	自動	手動	着脱
A	い	甲	1	2	3	4
A	ろ	乙	10	20	30	40
A	は	丙	60	70	80	90
B	ろ	丁	100	200	300	400
B	に	戊	600	700	800	900

 
受注一覧
製番	部品コード	生産数
1	A	10
2	A	20
3	B	30

 
があるとして、
 
結果
製番	部品コード	生産数	工程コード	工程名	段取	自動	手動	着脱
1	A	10	い	工程い	1	2	3	4
1	A	10	ろ	工程ろ	10	20	30	40
1	A	10	は	工程は	60	70	80	90
2	A	20	い	工程い	1	2	3	4
2	A	20	ろ	工程ろ	10	20	30	40
2	A	20	は	工程は	60	70	80	90
3	B	30	ろ	工程ろ	100	200	300	400
3	B	30	に	工程に	600	700	800	900

 
が欲しいのでしょうか。
 
データベースで言うところの外部結合ですね。
 
どんな更新頻度なのかにも依りますがクエリを使えば。。。
一般機能と言うかVBAを使わずに可能です。
 
【ExcelでPowerQueryを使ってデータ収集分析】
https://qiita.com/mosugi/items/71df310b35db81722d1e
 
部品コードを キー として 結合すれば良いです。

投稿日時: 19/07/19 01:04:31
投稿者: ダイハード

Suzuさん ありがとうございます。
 
ちょっと違います
 
Sheet工程マスタ
部品コード    工程コード    手配コード    チェックボックス    段取    自動    手動    着脱    M列連動
A    い    甲    ㇾ    1    2    3    4    TRUE
A    い    甲甲        6    7    8    9    FALSE
A    ろ    乙    ㇾ    10    20    30    40    TRUE
A    は    丙    ㇾ    60    70    80    90    TRUE
B    ろ    丁    ㇾ    100    200    300    400    TRUE
B    に    戊        600    700    800    900    FALSE
B    に    乙    ㇾ    5    50    500    5000    TRUE
 
Sheet受注一覧
製番    部品コード    生産数    段取り回数
1    A    10    1
2    A    20    3
3    B    30    4
 
の2つのSheetの場合
 
Sheet結果
製番    部品コード    生産数    手配コード    手配先名    段取り回数    段取    自動    手動    着脱
1    A    10    甲    手配甲    1    1    20    30    40
1    A    10    乙    手配乙    1    10    200    300    400
1    A    10    丙    手配丙    1    60    700    800    900
2    A    20    甲    手配甲    3    3    40    60    80
2    A    20    乙    手配乙    3    30    400    600    800
2    A    20    丙    手配丙    3    180    1400    1600    1800
3    B    30    丁    手配丁    4    400    6000    9000    12000
3    B    30    乙    手配乙    4    20    1500    15000    150000
 
になります。
結果の段取は
段取回数(Sheet受注一覧)×段取(Sheet工程マスタ)
となります。
またSheet工程マスタのM列連動でFALSE行は結果に反映しなくてよい
この2点が違うのかな?
 
よろしくお願いします
 
 
 

回答
投稿日時: 19/07/19 10:16:30
投稿者: Suzu

チェックボックスのTRUEのみを結果としたいなら
その様な条件のクエリにすれば良いです。
 
 
POWER Query - クエリ マージ
  上に、受注一覧、 下に 工程マスタ を指定
   結合種類 を 左外部を選択、 上下表の部品コード を選択し OK
   受注一覧の 右側の列に Table となり、列名にテーブル名が表示され
    左右に分かれる矢印がありますので、その矢印をクリックすると、
    工程マスタのフィールドが表示されますから全てにチェックを入れ OK
   チェックボックスの部分の コンボボックスから 「TRUE」を選択で
 

製番	部品コード	生産数	部品コード	工程コード	手配コード	チェックボックス	段取	自動	手動	着脱
1	A	10	A	い	甲	TRUE	1	2	3	4
1	A	10	A	は	丙	TRUE	60	70	80	90
2	A	20	A	い	甲	TRUE	1	2	3	4
2	A	20	A	は	丙	TRUE	60	70	80	90
3	B	30	B	ろ	丁	TRUE	100	200	300	400

 
が得られます。
 
 
引用:
段取回数(Sheet受注一覧)×段取(Sheet工程マスタ)

の部分は
  Excel上に表示された クエリを選択した状態で
  POWER Query - エディターの起動
    列の追加 から カラム列 にて 必要な数式を入れ必要な値を計算させましょう。
 
 あるいは
  クエリ結果からふつうに数式を入れても良いでしょう。
  
クエリを修正して

回答
投稿日時: 19/07/20 22:04:20
投稿者: simple

# PowerQueryを知らなかったのでインストールしてみました。
# 便利ですね。ありがとうございます。
 
もしマクロでやるとしたらということで、簡単な例にしてコードを作ってみました。
<工程シート>のうち不要な行は、予めフィルタオプションなりオートフィルタで
整えるのは簡単。ですので、最初から必要なものだけにしています。
 

考え方は、
  ・工程シートの部品コード別の開始行と行数をそれぞれ辞書に保持させ、
  ・これをもとに、コピーペイストを繰り返す
というものです。
掛け算するところも、乗算を使った貼付処理にしています。

コードを提示するのは、あくまでも考え方の参考にしてもらう目的です。
あなたのコード作成を代行するつもりはありませんので、
ご自分のデータに合わせてトライしてみてください。
また、必要な修正箇所があれば、そちらで対応してください。
 
■シートレイアウト(参考用ですので、そこが違うという指摘は無しにして下さい。)
 
<工程シート>
A列         B           C           D             E       F      G       H   I
部品コード  工程コード  手配コード  チェックボックス  段取    自動    手動    着脱  M列連動
A           い          甲          ㇾ            1       2       3       4  TRUE
A           ろ          乙          ㇾ           10      20      30      40  TRUE
A           は          丙          ㇾ           60      70      80      90  TRUE
B           ろ          丁          ㇾ          100     200     300     400  TRUE
B           に          乙          ㇾ            5      50     500    5000  TRUE

 
<受注一覧シート>
製番  部品コード 生産数  段取り回数
1     A          10               1
2     A          20               3
3     B          30               4

 
<結果シート>
A      B              C           D            E     F     G       H       I
製番  部品コード  生産数  手配コード  段取り回数  段取  自動    手動    着脱
1     A               10          甲           1     1    20      30      40
1     A               10          乙           1    10   200     300     400
1     A               10          丙           1    60   700     800     900
2     A               20          甲           3     1    40      60      80
2     A               20          乙           3    10   400     600     800
2     A               20          丙           3    60  1400    1600    1800
3     B               30          丁           4   100  6000    9000   12000
3     B               30          乙           4     5  1500   15000  150000

■参考コード
Sub test()
    Dim dicStart  As Object
    Dim dicCount  As Object
    Dim wsProcess As Worksheet
    Dim wsOrder   As Worksheet
    Dim wsOut     As Worksheet
    Dim s         As String
    Dim buhinCD   As String
    Dim k         As Long
    Dim p         As Long
    Dim st        As Long
    Dim kosu      As Long
    
    Set dicStart = CreateObject("Scripting.Dictionary")
    Set dicCount = CreateObject("Scripting.Dictionary")
    Set wsProcess = Worksheets("工程マスタ")
    Set wsOrder = Worksheets("受注一覧")
    Set wsOut = Worksheets("結果")
    
    '部品コード別に、工程マスタの開始行と行数を辞書に持つ
    For k = 2 To wsProcess.Cells(Rows.Count, "A").End(xlUp).Row
        s = wsProcess.Cells(k, 1).Value
        If Not dicStart.Exists(s) Then
            dicStart(s) = k     '開始行
            dicCount(s) = 1     '行数
        Else
            dicCount(s) = dicCount(s) + 1
        End If
    Next
    
    '受注ごとに、部品コードをキーにデータを増殖する
    
    p = 2   '書込先は2行目から。見出しはこの際省略
    
    For k = 2 To wsOrder.Cells(Rows.Count, "A").End(xlUp).Row
        buhinCD = wsOrder.Cells(k, 2).Value
        st = dicStart(buhinCD)
        kosu = dicCount(buhinCD)
        wsOrder.Cells(k, 1).Resize(1, 3).Copy _
            wsOut.Cells(p, "A").Resize(kosu, 3)
        
        wsProcess.Cells(st, 3).Resize(kosu, 1).Copy _
            wsOut.Cells(p, "D").Resize(kosu, 1)
        
        wsOrder.Cells(k, 4).Resize(1, 3).Copy _
            wsOut.Cells(p, "E").Resize(kosu, 1)
        
        wsProcess.Cells(st, "E").Resize(kosu, 1).Copy _
            wsOut.Cells(p, "F").Resize(kosu, 1)
        
        wsProcess.Cells(st, "F").Resize(kosu, 3).Copy _
            wsOut.Cells(p, "G").Resize(kosu, 3)
        
        wsOrder.Cells(k, 3).Copy
        wsOut.Cells(p, "G").Resize(kosu, 3).PasteSpecial _
             Paste:=xlPasteValues, Operation:=xlMultiply
        
        p = p + kosu
    Next
End Sub
# 少し冗長なコードだったので手を入れました。

投稿日時: 19/07/21 22:21:28
投稿者: ダイハード

こんばんは
 
PowerQuery をインストールして試してみました。
結果okでした。今後いろりろ試してみます。
今回はテーブル(sheet)2つだったけど3つだったら・・・など
興味があるのでAmazonで一冊本も購入しました。
Suzuさんありがとうございました。
 
simpleさん
VBAのコード教授ありがとうございました。
こちらの方はこれから試してみます。
一旦閉じますが解らないことあがあればまた投稿すするので
その時は宜しくお願いします。