Excel VBA質問箱 IV

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

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

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
654 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【79146】ファイルをまたいだ数値の取得および合算 まぐろ 17/5/24(水) 8:53 質問[未読]
【79147】Re:ファイルをまたいだ数値の取得および合算 カリーニン 17/5/24(水) 10:35 発言[未読]
【79159】Re:ファイルをまたいだ数値の取得および合算 まぐろ 17/5/26(金) 15:03 回答[未読]
【79148】Re:ファイルをまたいだ数値の取得および合算 γ 17/5/25(木) 7:19 発言[未読]
【79160】Re:ファイルをまたいだ数値の取得および合算 まぐろ 17/5/26(金) 15:06 回答[未読]
【79162】Re:ファイルをまたいだ数値の取得および合算 γ 17/5/27(土) 7:31 発言[未読]
【79214】Re:ファイルをまたいだ数値の取得および合算 まぐろ 17/6/16(金) 17:45 お礼[未読]
【79146】ファイルをまたいだ数値の取得および合算
質問  まぐろ  - 17/5/24(水) 8:53 -
引用なし
パスワード
   初投稿です、よろしくお願いします。

以下の、SHEET”部門Ａ”(元資料)から別ファイルのSHEET”○月”（最新の月。
例として６月）へ、ボタンを押すと自動で数値が流れるようにしたいです。

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

　　　　　SHEET”部門Ａ”（ファイル「作業内容」。元資料）

(上段；予算　下段；実績)
　　A1　　　　B1　　 C1　　　D1　・・・・・
作業内容　　 ４月　 ５月　　６月　・・・・・
　 A2・3　　　B2　　 C2　　　D2
　作図　　　10,000　11,000　12,000・・・・
　(製品あ)　　B3　　 C3　　　D3
　　　　　　 9,000　12,000　10,500・・・・
　 A4・5　　　B4　　 C4　　　D4
　組立　　　15,000　14,000　17,000・・・・
　　　　　　　B5　　 C5　　　D5
　　　　　　13,500　12,000　16,000・・・・
　 A6・7　　　B6　　 C6　　　D6
　要請対応　 5,000　 4,200　 4,800・・・・
　　　　　　　B7　　 C7　　　D7
　　　　　　 5,100　 4,000　 4,700・・・・
　 A8・9　　　B8　　 C8　　　D8
　作図　　　 3,000　 2,700　 2,800・・・・
　(製品い)　　B9　　 C9　　　D9
　　　　　　 2,850　 2,750　 2,900・・・・

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

　　　　　SHEET”６月”（ファイル「単月業績」）
　　
　　　　　 AV29　　　AW29　　　AX29
　　　　　　　　　　 予算　 　 実績
　　　　　 AV30　　　AW30　　　AX30
　　　　　 作図　　 14,800　　13,400→製品"あ"と"い"の合算
　　　　　 AV31　　　AW31　　　AX31
　　　　　 組立　　 17,000　　16,000
　　　　　 AV32　　　AW32　　　AX32
　　　　　要請対応　 4,800　　 4,700

　　　※AV,AWといった中途半端な位置にあるのは、SHEET
　　　　”６月”には「作業内容」以外に他の資料から
　　　　数値を引っ張ってくる為
　　　　
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
○備考
・ファイル「作業内容」には”部門Ａ”以外、部門Ｂ・Ｃ・・と他の部門のSHEET
が入っている。毎月それぞれのシートに最新月の数値を追加していく
・ファイル「単月業績」は毎月、”４月””５月”・・と毎月最新月のSHEETを作っていく

ネットで方法を調べて考えてみましたが、
引用してくる数値が月によって変わること（特定の月の数値を指定して引用する
必要がある）、引用してきた数値を合算すること、ファイル間をまたぐこと
が重なり、どこから手をつければいいかわからなくて質問致しました。
お分かりになれば教えて頂きたいです。すいませんが、よろしく
お願いします。
【79147】Re:ファイルをまたいだ数値の取得および...
発言  カリーニン  - 17/5/24(水) 10:35 -
引用なし
パスワード
   先ずは、手作業でやるならどういう手順になるかを日本語で考えてみましょう。
後は、考えた手順をコード化するだけです。
【79148】Re:ファイルをまたいだ数値の取得および...
発言  γ  - 17/5/25(木) 7:19 -
引用なし
パスワード
   疑問点。
1．単月業績ファイルでは、部門という切り口はどうなっているのですか？
2．製品は"あ"と"い"だけですか？それは固定なんですか？

集計は「作業内容」ファイルで計算式で行い、
その結果を転記する部分だけマクロにするほうが効率がよいかもしれませんね。

なお、表を書くときは、こんな風に書いた方が分かりやすいと思う。
■「作業内容」ファイルの「部門Ａ」シート（元資料）

(上段；予算　下段；実績)
　A　　　　　 B　　　 C　　　 D　・・・・・
1 作業内容　　 ４月　 ５月　　６月　・・・・・
2　作図　　　10,000　11,000　12,000・・・・
3　(製品あ)　 9,000　12,000　10,500・・・・
4　組立　　　15,000　14,000　17,000・・・・
5　　　　　　13,500　12,000　16,000・・・・
6　要請対応　 5,000　 4,200　 4,800・・・・
7　　　　　　 5,100　 4,000　 4,700・・・・
8　作図　　　 3,000　 2,700　 2,800・・・・
9 (製品い)　　2,850　 2,750　 2,900・・・・
【79159】Re:ファイルをまたいだ数値の取得および...
回答  まぐろ  - 17/5/26(金) 15:03 -
引用なし
パスワード
   ▼カリーニン さん：
>先ずは、手作業でやるならどういう手順になるかを日本語で考えてみましょう。
>後は、考えた手順をコード化するだけです。


カリーニン様

お返事ありがとうございました。

＞日本語で考える
どんなコードかを検索する際に、以下のように考えました。

1.「作業内容」ファイルでは、各SHEET（部門Ａ、Ｂ、Ｃ、Ｄ・・・）で、
数値（作業時間）が、作業内容ごとに入力されている。
このSHEETの数値を「単月業績」へ引っ張りたい。
しかし「作業内容」の各SHEETは別ファイルである

2.さらに、「作業内容」の項目と「単月業績」の項目は、
１対１の対応関係になっていない（「作業内容」の項目を集約して
計算しなければならない）

3.「単月業績」の”４月”SHEETを作る際は「作業内容」の部門SHEETの
４月の数値から、「単月業績」の”５月”を作る際は「作業内容」の５月から、
といったように、取得する数値（列）が変わっていく。


1.は、
DIM 変数 AS RANGE
SET 変数 = RANGE を使うのかなと思いましたが、

別ファイルからの取得なので、

SET 変数 = RANGE('[作業内容.XLS]部門Ａ!B2)

としてみましたが、コンパイルエラー、となってしまいました。
1.ができない為2.、3.に進めない状況です。

1つの変数については検索して出てきますが、組み合わせになると
なかなか出てこなくて難しいです。
【79160】Re:ファイルをまたいだ数値の取得および...
回答  まぐろ  - 17/5/26(金) 15:06 -
引用なし
パスワード
   ▼γ さん：
>疑問点。
>1．単月業績ファイルでは、部門という切り口はどうなっているのですか？
>2．製品は"あ"と"い"だけですか？それは固定なんですか？
>
>集計は「作業内容」ファイルで計算式で行い、
>その結果を転記する部分だけマクロにするほうが効率がよいかもしれませんね。
>
>なお、表を書くときは、こんな風に書いた方が分かりやすいと思う。
>■「作業内容」ファイルの「部門Ａ」シート（元資料）
>
>(上段；予算　下段；実績)
>　A　　　　　 B　　　 C　　　 D　・・・・・
>1 作業内容　　 ４月　 ５月　　６月　・・・・・
>2　作図　　　10,000　11,000　12,000・・・・
>3　(製品あ)　 9,000　12,000　10,500・・・・
>4　組立　　　15,000　14,000　17,000・・・・
>5　　　　　　13,500　12,000　16,000・・・・
>6　要請対応　 5,000　 4,200　 4,800・・・・
>7　　　　　　 5,100　 4,000　 4,700・・・・
>8　作図　　　 3,000　 2,700　 2,800・・・・
>9 (製品い)　　2,850　 2,750　 2,900・・・・


γ様

お返事ありがとうございました。

表の書き方は仰る通りです、自分のは見にくすぎました、すいません。

疑問点１．について

　　 AU　　AV　　　 AW　　　 AX
29　部　　　　　　予算　 　 実績
30　門　 作図　　14,800　　13,400→製品"あ"と"い"の合算
31　Ａ　 組立　　17,000　　16,000
32　　 要請対応　 4,800　　 4,700
33　部
34　門
35　Ｂ
36　・
37　・
38　・

といったように、「作業内容」ファイルでは、1部門の数値が
1SHEETでまとめられており、各部門のSHEETからそれぞれ1つの月の数値を
取得していき「単月業績」の中で1つのSHEETでまとめる、という形です。

疑問点２．について

固定ではなく、期（半年）ごとで見直しをかけて、変更します。
ついでに、”部門”の数(SHEET)の数も、見直しによって増減することもあります。

VBAだと列を指定して数値を取得するので、変更があってもそれに応じて
取ってくる行の値を変えればいいのかなと思っていました。


＞集計は「作業内容」ファイルで計算式で行い、
その結果を転記する部分だけマクロにするほうが効率がよいかもしれませんね。

その場合、「作業内容」に集計用のシートを別途作って、そこでピボットで集計、
それを転記、みたいな流れでしょうか？
【79162】Re:ファイルをまたいだ数値の取得および...
発言  γ  - 17/5/27(土) 7:31 -
引用なし
パスワード
   ・各部門のシートはフォーマット（特に数値が入っているセルの位置）が統一されているか。
　部門によっては、製品と製品の間に空行が入っているといったことがないか。
・製品の数は各部門で違うとき、
　単純に、最後の行までを対象に、製品の合計をとればよいのか、不明。
などの疑問点があります。

しかし、現時点で示されたものを前提にコメントしておきます。

> Dim 変数 As Range
> SET 変数 = RANGE を使うのかなと思いましたが、
> 別ファイルからの取得なので､
> SET 変数 = RANGE('[作業内容.XLS]部門Ａ!B2)
というところですが、
Workbooks("aaa").Worksheets("BBB").Range("...") という形式のほうが
よいでしょう。

たとえば、こんな書き方ができるかもしれません。参考例です。
Sub test()
　　Dim wbS As Workbook
　　Dim wbD As Workbook
　　Dim wsS As Worksheet
　　Dim wsD As Worksheet
　　Dim sh As Variant
　　Dim k As Long
　　　　
　　Set wbD = Workbooks("単月業績.xls")　　 'D: Destination(転記先)
　　Set wsD = wbD.Worksheets("6月")

　　Set wbS = Workbooks("作業内容.xls")　　 'S: Source（転記元)
　　For Each sh In Array("部門A", "部門B")
　　　　Set wsS = wbS.Worksheets(sh)
　　　　wsD.Cells(2 + k, "D").Value = getSum(wsS.Cells(2, 4))
　　　　wsD.Cells(2 + k, "E").Value = getSum(wsS.Cells(3, 4))
　　　　wsD.Cells(3 + k, "D").Value = getSum(wsS.Cells(4, 4))
　　　　wsD.Cells(3 + k, "E").Value = getSum(wsS.Cells(5, 4))
　　　　wsD.Cells(4 + k, "D").Value = getSum(wsS.Cells(6, 4))
　　　　wsD.Cells(4 + k, "E").Value = getSum(wsS.Cells(7, 4))
　　　　k = k + 4
　　Next
Ens Sub

合計する部分は、例えば関数にしておいて
Function getSum(r As Range) As Long
　　getSum = r.Value + r.Offset(6).Value + r.Offset(12).Value
End Function
などとします。最大３つの製品に関する数値があるという前提です。

上記コードはとっかかりに過ぎません。
月に応じて、書込先の列を変えるとか、いろいろ改善点がありえます。

なお、今後、実はこういう前提になっていました、
という変更事項がありましてもコードを修正する積もりはありません。
予め、お断りしておきます。

上記は単なるヒントですから、
それを参考にしてあなたがトライしていただきたいと思います。

なお、今後、予算と実績の比較とか色々な分析をされると思うので、
現在のフォーマットが適当なものかも不明です。
ピボットテーブルなどの利用できる形式の書式のほうがよいかもしれませんね。

それでは頑張ってください。
【79214】Re:ファイルをまたいだ数値の取得および...
お礼  まぐろ  - 17/6/16(金) 17:45 -
引用なし
パスワード
   γさま
大変お返事遅れてすいません。
頂いた式やコメントを参考に取り組み中です。
一度締め切ります。
ひょっとしたらまたご質問させて頂くかもしれませんが。

改めて、具体的な式まで書いて頂きありがとうございました。


▼γ さん：
>・各部門のシートはフォーマット（特に数値が入っているセルの位置）が統一されているか。
>　部門によっては、製品と製品の間に空行が入っているといったことがないか。
>・製品の数は各部門で違うとき、
>　単純に、最後の行までを対象に、製品の合計をとればよいのか、不明。
>などの疑問点があります。
>
>しかし、現時点で示されたものを前提にコメントしておきます。
>
>> Dim 変数 As Range
>> SET 変数 = RANGE を使うのかなと思いましたが、
>> 別ファイルからの取得なので､
>> SET 変数 = RANGE('[作業内容.XLS]部門Ａ!B2)
>というところですが、
>Workbooks("aaa").Worksheets("BBB").Range("...") という形式のほうが
>よいでしょう。
>
>たとえば、こんな書き方ができるかもしれません。参考例です。
>Sub test()
>　　Dim wbS As Workbook
>　　Dim wbD As Workbook
>　　Dim wsS As Worksheet
>　　Dim wsD As Worksheet
>　　Dim sh As Variant
>　　Dim k As Long
>　　　　
>　　Set wbD = Workbooks("単月業績.xls")　　 'D: Destination(転記先)
>　　Set wsD = wbD.Worksheets("6月")
>
>　　Set wbS = Workbooks("作業内容.xls")　　 'S: Source（転記元)
>　　For Each sh In Array("部門A", "部門B")
>　　　　Set wsS = wbS.Worksheets(sh)
>　　　　wsD.Cells(2 + k, "D").Value = getSum(wsS.Cells(2, 4))
>　　　　wsD.Cells(2 + k, "E").Value = getSum(wsS.Cells(3, 4))
>　　　　wsD.Cells(3 + k, "D").Value = getSum(wsS.Cells(4, 4))
>　　　　wsD.Cells(3 + k, "E").Value = getSum(wsS.Cells(5, 4))
>　　　　wsD.Cells(4 + k, "D").Value = getSum(wsS.Cells(6, 4))
>　　　　wsD.Cells(4 + k, "E").Value = getSum(wsS.Cells(7, 4))
>　　　　k = k + 4
>　　Next
>Ens Sub
>
>合計する部分は、例えば関数にしておいて
>Function getSum(r As Range) As Long
>　　getSum = r.Value + r.Offset(6).Value + r.Offset(12).Value
>End Function
>などとします。最大３つの製品に関する数値があるという前提です。
>
>上記コードはとっかかりに過ぎません。
>月に応じて、書込先の列を変えるとか、いろいろ改善点がありえます。
>
>なお、今後、実はこういう前提になっていました、
>という変更事項がありましてもコードを修正する積もりはありません。
>予め、お断りしておきます。
>
>上記は単なるヒントですから、
>それを参考にしてあなたがトライしていただきたいと思います。
>
>なお、今後、予算と実績の比較とか色々な分析をされると思うので、
>現在のフォーマットが適当なものかも不明です。
>ピボットテーブルなどの利用できる形式の書式のほうがよいかもしれませんね。
>
>それでは頑張ってください。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
654 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free