Excel VBA質問箱 IV

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

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


837 / 13645 ツリー ←次へ | 前へ→

【77974】別ファイルにあるセル範囲を配列に格納したい ayu 16/2/22(月) 8:23 質問[未読]
【77975】Re:別ファイルにあるセル範囲を配列に格納... β 16/2/22(月) 9:12 発言[未読]
【77976】Re:別ファイルにあるセル範囲を配列に格納... β 16/2/22(月) 9:19 発言[未読]
【77977】Re:別ファイルにあるセル範囲を配列に格納... β 16/2/22(月) 9:35 発言[未読]
【77978】Re:別ファイルにあるセル範囲を配列に格納... β 16/2/22(月) 9:44 発言[未読]
【77979】Re:別ファイルにあるセル範囲を配列に格納... β 16/2/22(月) 13:13 発言[未読]
【77982】Re:別ファイルにあるセル範囲を配列に格納... ayu 16/2/23(火) 5:45 お礼[未読]
【78001】Re:別ファイルにあるセル範囲を配列に格納... ayu 16/3/3(木) 10:55 質問[未読]
【78002】Re:別ファイルにあるセル範囲を配列に格納... β 16/3/3(木) 11:36 発言[未読]
【78020】Re:別ファイルにあるセル範囲を配列に格納... ayu 16/3/7(月) 7:43 お礼[未読]
【78048】Re:別ファイルにあるセル範囲を配列に格納... ayu 16/3/14(月) 6:25 質問[未読]
【78049】Re:別ファイルにあるセル範囲を配列に格納... β 16/3/14(月) 9:30 発言[未読]
【78050】Re:別ファイルにあるセル範囲を配列に格納... β 16/3/14(月) 16:19 発言[未読]
【78051】Re:別ファイルにあるセル範囲を配列に格納... ayu 16/3/15(火) 9:13 お礼[未読]

【77974】別ファイルにあるセル範囲を配列に格納し...
質問  ayu  - 16/2/22(月) 8:23 -

引用なし
パスワード
   こんにちは。色々調べても自力では分からないのでこちらに質問させて頂きます。あるファイルに下記のようなデータが縦続きに数個入っています。日々の集計を取るために、2行目のメニュー名称と9行目にある各メニューの合計数がゼロ以上のものを別ファイルに該当メニューとその合計数を表示したいのですが、やり方としては下記のような事を考えています。

このファイルのメニュー名称が入っているセル範囲(A2:F2)と合計数(A9:F9)を、配列にコピーして、合計数が0以上のメニュー名とその数を表示したいと考えています。色々自分なりに調べてやってみたのですがうまく行きませんでした。
そこでお聞きしたいのは、まず私がやろうとしている事は可能でしょうか?また他に簡単な方法があればお聞きしたいです。ちなみに参照先のファイルは閉じた状態で見たいですが、かえって処理が遅くなったりするのでしょうか?ラップトップが遅いのでなるべく時間のかからないメモリの負荷が少ない処理だとありがたいです。

以上、ご教授のほどよろしくお願い致します。


    A列   B列  C列   D列  E列  F列
1行目 カットメニュー                    
2行目 カットA カットB カットC カットD カットE カットF
3行目 0    0    0    0    0    0
4行目 13    1    0    0    0    0
5行目 10    1    0    3    0    1
6行目 6    0    0    0    0    0
7行目 4    1    1    6    0    0
8行目 0    0    0    0    0    0            
9行目 33    3    1    9    0    1

【77975】Re:別ファイルにあるセル範囲を配列に格...
発言  β  - 16/2/22(月) 9:12 -

引用なし
パスワード
   ▼ayu さん:

>まず私がやろうとしている事は可能でしょうか?また他に簡単な方法があればお聞きしたいです。

はい。可能です。
データ件数にもよりますが、配列にいれずとも、直接、取り込み側シートにコピペして処理しても
処理速度にそんなに影響はないと思いますし、配列に入れることで、列単位の計算等が窮屈になります。
セル領域に取り込めば、非常に楽というか、柔軟な処理が望めます。

>ちなみに参照先のファイルは閉じた状態で見たいですが、かえって処理が遅くなったりするのでしょうか?

よく、この要望がだされます。
とくに、膨大なサイズのブックを相手にする。だから開くのが重くなるので、開かずに処理したい。
そこで、多くの人がイメージされるのが ExecuteExcel4Macro の利用。
ブックを開かずにセルのデータを取り出せる機能ですので。

最近、別の板ですけど、このあたりの検証が行われました。

www.excel.studio-kazu.jp/kw/20151023213330.html

結論としては

・膨大なブックであれば 
 ADO方式-->外部参照数式方式
 -- がく〜んと効率が悪くなって --> ExecuteExcel4Macro方式
 -- さらに効率が悪くなって --> ブックを開く方式

・通常のサイズのブックであれば、
 ADO方式-->外部参照数式方式-->ブックを開く方式
 -- がく〜んと効率が悪くなって --> ExecuteExcel4Macro方式

ブックサイズがいかほどかはわかりませんが、普通のエクセルブックであれば、ブックを開いて処理しても、
ADO方式や外部参照数式に比べて、そんなに効率には差がでません(少なくとも気になる差でなはい)

ADO方式や外部参照数式方式は確かに早いのですが、取得するためのコードが、ちょっと面倒になります。
当面のお勧めは、素直にブックを開いてセル領域をコピペで取り込んで処理 ですね。

【77976】Re:別ファイルにあるセル範囲を配列に格...
発言  β  - 16/2/22(月) 9:19 -

引用なし
パスワード
   ▼ayu さん:

質問文とレイアウトをよく見ましたら
ブックを開いて処理するなら、特段、データを取り込まなくても、直接元ブックを参照しながら
結果のみを表示するということができますね。

【77977】Re:別ファイルにあるセル範囲を配列に格...
発言  β  - 16/2/22(月) 9:35 -

引用なし
パスワード
   ▼ayu さん:

たとえば以下で試してみてください。
どうしても、遅い!! ということなら別途の方法を考えましょう。

Sub Sample()
  Dim shF As Worksheet
  Dim shT As Worksheet
  Dim x As Long
  Dim c As Range
  
  Application.ScreenUpdating = False '処理中の画面の動きを隠す
  
  Set shF = Workbooks.Open(ThisWorkbook.Path & "\元のブック.xlsx").Sheets("該当のシート名")
  Set shT = ThisWorkbook.Sheets("転記先のシート名")
  
  shT.Cells.ClearContents   '転記前にクリア
  x = 1            '転記開始行
  
  For Each c In shF.Range("A9:F9")  '元ブックの合計行のセルの取り出し
    If c.Value > 0 Then       ' 0 超なら
      shT.Cells(x, "A").Value = c.EntireColumn.Cells(2).Value   '項目名
      shT.Cells(x, "B").Value = c.Value              '合計数
      x = x + 1  '次の転記行
    End If
  Next
  
  shF.Parent.Close False '元ブックを閉じる
    
End Sub

【77978】Re:別ファイルにあるセル範囲を配列に格...
発言  β  - 16/2/22(月) 9:44 -

引用なし
パスワード
   ▼ayu さん:

↑ 元のブックはマクロブックと同じフォルダにあるという前提のコードです。

【77979】Re:別ファイルにあるセル範囲を配列に格...
発言  β  - 16/2/22(月) 13:13 -

引用なし
パスワード
   ▼ayu さん:

もう1つ。
転記レイアウトは アップ済みのものとはかえてあります。
外部参照数式を使い、ブックを開かないタイプ。
処理上もループをなくしてあります。
元ブックのフォルダは、アップ済みのもの同様、マクロブックと同じフォルダにしてあります。

Sub Sample2()
  Dim fPath As String
  Dim ref As String
  Dim shT As Worksheet
  Dim r As Range
  
  fPath = ThisWorkbook.Path
  ref = "='" & fPath & "\[元のブック.xlsx]該当のシート名'!"
  Set shT = ThisWorkbook.Sheets("転記先のシート名")
  shT.Cells.ClearContents   '転記前にクリア
  shT.Range("A1:F1").Formula = ref & "A2"
  shT.Range("A2:F2").Formula = ref & "A9"
  shT.Range("A1:F2").Value = shT.Range("A1:F2").Value
  
  With shT.Range("A2:F2")
    .Replace 0, Empty, xlWhole
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
    On Error GoTo 0
  End With
  
End Sub

【77982】Re:別ファイルにあるセル範囲を配列に格...
お礼  ayu  - 16/2/23(火) 5:45 -

引用なし
パスワード
   ▼β さん:
短時間にたくさんコメント頂き本当にありがとうございます。教えて頂いた方法でやってみて、また結果をご報告します。
取り急ぎお礼にて

>▼ayu さん:
>
>もう1つ。
>転記レイアウトは アップ済みのものとはかえてあります。
>外部参照数式を使い、ブックを開かないタイプ。
>処理上もループをなくしてあります。
>元ブックのフォルダは、アップ済みのもの同様、マクロブックと同じフォルダにしてあります。
>
>Sub Sample2()
>  Dim fPath As String
>  Dim ref As String
>  Dim shT As Worksheet
>  Dim r As Range
>  
>  fPath = ThisWorkbook.Path
>  ref = "='" & fPath & "\[元のブック.xlsx]該当のシート名'!"
>  Set shT = ThisWorkbook.Sheets("転記先のシート名")
>  shT.Cells.ClearContents   '転記前にクリア
>  shT.Range("A1:F1").Formula = ref & "A2"
>  shT.Range("A2:F2").Formula = ref & "A9"
>  shT.Range("A1:F2").Value = shT.Range("A1:F2").Value
>  
>  With shT.Range("A2:F2")
>    .Replace 0, Empty, xlWhole
>    On Error Resume Next
>    .SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
>    On Error GoTo 0
>  End With
>  
>End Sub

【78001】Re:別ファイルにあるセル範囲を配列に格...
質問  ayu  - 16/3/3(木) 10:55 -

引用なし
パスワード
   ▼β さん:

こんにちは。先日は色々教えて頂きありがとうございました。あの後実証してみる時間がなくて、最近やっと時間ができましたので、頂いたコードを動かしてみました。最初のサンプルは動作が確認できたのですが、2つ目の方はエラーは出ないのですが、転送先のシートに何も表示されません。そこで、質問があるのですが、

>  shT.Range("A1:F1").Formula = ref & "A2"
>  shT.Range("A2:F2").Formula = ref & "A9"
>  shT.Range("A1:F2").Value = shT.Range("A1:F2").Value

の部分は、左側で指定したセル範囲に右側のA2のFormulaをコピーし、
右側のセル範囲の値を左側で設定したシートのセル範囲にコピーするということであってますでしょうか?
お時間のある時にお返事頂ければ幸いです。
よろしくお願い致します。


>▼ayu さん:
>
>もう1つ。
>転記レイアウトは アップ済みのものとはかえてあります
>外部参照数式を使い、ブックを開かないタイプ。
>処理上もループをなくしてあります。
>元ブックのフォルダは、アップ済みのもの同様、マクロブックと同じフォルダにしてあります。
>
>Sub Sample2()
>  Dim fPath As String
>  Dim ref As String
>  Dim shT As Worksheet
>  Dim r As Range
>  
>  fPath = ThisWorkbook.Path
>  ref = "='" & fPath & "\[元のブック.xlsx]該当のシート名'!"
>  Set shT = ThisWorkbook.Sheets("転記先のシート名")
>  shT.Cells.ClearContents   '転記前にクリア
>  shT.Range("A1:F1").Formula = ref & "A2"
>  shT.Range("A2:F2").Formula = ref & "A9"
>  shT.Range("A1:F2").Value = shT.Range("A1:F2").Value
>  
>  With shT.Range("A2:F2")
>    .Replace 0, Empty, xlWhole
>    On Error Resume Next
>    .SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
>    On Error GoTo 0
>  End With
>  
>End Sub

【78002】Re:別ファイルにあるセル範囲を配列に格...
発言  β  - 16/3/3(木) 11:36 -

引用なし
パスワード
   ▼ayu さん:

>左側で指定したセル範囲に右側のA2のFormulaをコピーし、
>右側のセル範囲の値を左側で設定したシートのセル範囲にコピーする
>ということであってますでしょうか?

A2のFormula というのが、ちょっと変ですけど、以下のようなことです。

たとえばステップ実行はご存じでしょうか。
VBE画面で、Sample2 の任意のところをクリックしてF8 を押します。
最初は Sub Sample2() が黄色くハイライトされます。
で、そのまま F8 を押していきます。
  shT.Range("A1:F2").Value = shT.Range("A1:F2").Value
ここが黄色くなったら F8を押さず、マクロブックの "転記先のシート名" をみてください。
この A1:F2 に、元ブックを参照する参照式が入っています。

式と表示されている値を確認したら、VBE画面に戻り End Sub まで F8 を押していって下さい。

Sample がうまくいったということですから、同じ環境(フォルダやブック名やシート名)で実行すれば
シートに参照式がセットされ、そこで参照している値がシートに表示されているはずなんですが。

【78020】Re:別ファイルにあるセル範囲を配列に格...
お礼  ayu  - 16/3/7(月) 7:43 -

引用なし
パスワード
   ▼β さん:
お返事遅くなりました。恥ずかしながら、自分が指定したセル範囲が右の方にあり、画面をスクロールしないと見えませんでした。教えて頂いたコードを使って集計してみます。また詰まったら質問させて頂きます。どうもありがとうございました!
>▼ayu さん:
>
>>左側で指定したセル範囲に右側のA2のFormulaをコピーし、
>>右側のセル範囲の値を左側で設定したシートのセル範囲にコピーする
>>ということであってますでしょうか?
>
>A2のFormula というのが、ちょっと変ですけど、以下のようなことです。
>
>たとえばステップ実行はご存じでしょうか。
>VBE画面で、Sample2 の任意のところをクリックしてF8 を押します。
>最初は Sub Sample2() が黄色くハイライトされます。
>で、そのまま F8 を押していきます。
>  shT.Range("A1:F2").Value = shT.Range("A1:F2").Value
>ここが黄色くなったら F8を押さず、マクロブックの "転記先のシート名" をみてください。
>この A1:F2 に、元ブックを参照する参照式が入っています。
>
>式と表示されている値を確認したら、VBE画面に戻り End Sub まで F8 を押していって下さい。
>
>Sample がうまくいったということですから、同じ環境(フォルダやブック名やシート名)で実行すれば
>シートに参照式がセットされ、そこで参照している値がシートに表示されているはずなんですが。

【78048】Re:別ファイルにあるセル範囲を配列に格...
質問  ayu  - 16/3/14(月) 6:25 -

引用なし
パスワード
   ▼β さん:
お世話になります。この前教えて頂いた事を元にやりたい事を色々試しているのですが、詰まってしまいましたので、再度質問させて頂きます。
教えて頂いた事を基に、別シートから項目名と合計数を抽出し、転記先のシートに下記のようにするとこるまではできました。このように奇数行が項目名で、偶数行が該当項目の合計数になっています。項目数は一定しておらず下記に表示してるより多い行もあります。

    A列    B列   C列  D列   E列   F列   G列    H列
1行目 S-Trim  M-Trim  SC-S  SC-ML Under12 Point   N/A    
2行目 32    10    3    7   0    0     0    
3行目 C-Re/VS  C-S   C-M  C-Semi C-L   C-SLong  N/A    
4行目 0     1    0    0   0    0     0    
5行目 HL-Half  HL-Full  HL-Point   N/A               
6行目 0     0    0    0                
7行目 STP-Re  STP-S  STP-M STP-Semi STP-L STP-SLong STP-Bang N/A
8行目 0     0    0    0   0    0     0    0  
9行目 BL-Dry   TR   Iron  N/A                
10行目 0     0    0    0                
11行目 C-Eyeblow Beard  Iron  Rinse  Shampoo Bang   N/A  
12行目 0     0    0    0   0    3     0    
この下にも何行か続きます。

上記の表から印刷用のフォーマットに数字をコピーしたいのですが、そのフォーマットが
メニュー名|数|メニュー名|数|メニュー名|数|メニュー名|数|メニュー名|数
のようになっており、この行が9行ありまして、上記のメニューと合計セットも9つあります。
この印刷用のフォーマットに合計数が0以上のメニュー名と合計数をコピーしたいのですが、その方法が分からず詰まっています。

例えば上の例でいくと下記のように表示したいです。(合計数が0のメニューは無視)

    A列  B列  C列  D列 E列 F列 G列 H列
1行目 S-Trim  32  M-Trim 10  SC-S 3  SC-ML 7
2行目 C-S    1
3行目 Shampoo  3
4行目 Bang    3

このようにするべく色々模索しておりますが、なかなか上手くいきません。お時間のある時にでも何かヒントでも頂けたら大変ありがたいです。
何度もすみませんが、宜しくご教示お願いいたします。

>▼ayu さん:
>
>>左側で指定したセル範囲に右側のA2のFormulaをコピーし、
>>右側のセル範囲の値を左側で設定したシートのセル範囲にコピーする
>>ということであってますでしょうか?
>
>A2のFormula というのが、ちょっと変ですけど、以下のようなことです。
>
>たとえばステップ実行はご存じでしょうか。
>VBE画面で、Sample2 の任意のところをクリックしてF8 を押します。
>最初は Sub Sample2() が黄色くハイライトされます。
>で、そのまま F8 を押していきます。
>  shT.Range("A1:F2").Value = shT.Range("A1:F2").Value
>ここが黄色くなったら F8を押さず、マクロブックの "転記先のシート名" をみてください。
>この A1:F2 に、元ブックを参照する参照式が入っています。
>
>式と表示されている値を確認したら、VBE画面に戻り End Sub まで F8 を押していって下さい。
>
>Sample がうまくいったということですから、同じ環境(フォルダやブック名やシート名)で実行すれば
>シートに参照式がセットされ、そこで参照している値がシートに表示されているはずなんですが。

【78049】Re:別ファイルにあるセル範囲を配列に格...
発言  β  - 16/3/14(月) 9:30 -

引用なし
パスワード
   ▼ayu さん:

おそらく、Sample2の方式(外部参照数式方式)をもとにトライされているんですかね?
数式で処理すようとすると、同じブックにあっても、左詰め、上詰めが必要で
数式エキスパートさんなら 超難解な数式を組み立て(もしかしたら配列数式)ることも可能かとも思いますが
βには、とっても無理です。
逆に、ayuさんが、同じブックに元シートがあるとすれば、ひっぱってくる数式を書くことができるなら
それを教えてください。
その数式を使ったコードをβが書くことはできますので。

むしろ、Sample方式、つまりブックを開いて、処理する方式が、エクセルの持つ優れた機能を
使うこともできますし、コードもわかりやすいと思います。

で、とりあえず Sample方式で、コードを書きますが、時間がなかなかとれないので
明日、あるいは明後日になるかもしれません。

【78050】Re:別ファイルにあるセル範囲を配列に格...
発言  β  - 16/3/14(月) 16:19 -

引用なし
パスワード
   ▼ayu さん:

以下、試してください。

Sub Sample3()
  Dim shF As Worksheet
  Dim shT As Worksheet
  Dim v As Variant
  Dim w As Variant
  Dim x As Long
  Dim y As Long
  Dim i As Long
  Dim j As Long
  
  Application.ScreenUpdating = False '処理中の画面の動きを隠す
  Set shT = ThisWorkbook.Sheets("フォーマット")                          '★
  Set shF = Workbooks.Open(ThisWorkbook.Path & "\元のブック.xlsx").Sheets("該当のシート名")    '★
  
  With shF.Range("A1").CurrentRegion '元シートの表領域
    '転記用配列準備(厳密には、こんなに大きくなくてもいいですが)
    ReDim v(1 To .Rows.Count, 1 To .Columns.Count * 2)
    For i = 2 To .Rows.Count Step 2
      If WorksheetFunction.CountIf(.Rows(i), ">0") > 0 Then 'すべて 0 なら対象外
        y = y + 1  '転記行
        x = 0    '転記列
        For j = 1 To .Columns.Count
          If .Cells(i, j) > 0 Then
            x = x + 1
            v(y, x) = .Cells(i - 1, j).Value
            v(y, x + 1) = .Cells(i, j).Value
            x = x + 1
          End If
        Next
      End If
    Next
  End With
  
  shF.Parent.Close False '元ブックを閉じる
  '結果を一括転記
  shT.Cells.ClearContents
  shT.Range("A1").Resize(y, UBound(v, 2)) = v

End Sub

【78051】Re:別ファイルにあるセル範囲を配列に格...
お礼  ayu  - 16/3/15(火) 9:13 -

引用なし
パスワード
   ▼β さん:
こんなに早くお返事を頂けるとは思っていませんでした。
おかげ様で、きれいにデータを並び替えることができました。正直、配列準備のところはぱっと見ただけではよく分かりませんが、じっくり調べて理解したいと思います。何度も貴重なお時間を割いて頂き、本当にありがとうございました!!m(_ _)m
>▼ayu さん:
>
>以下、試してください。
>
>Sub Sample3()
>  Dim shF As Worksheet
>  Dim shT As Worksheet
>  Dim v As Variant
>  Dim w As Variant
>  Dim x As Long
>  Dim y As Long
>  Dim i As Long
>  Dim j As Long
>  
>  Application.ScreenUpdating = False '処理中の画面の動きを隠す
>  Set shT = ThisWorkbook.Sheets("フォーマット")                          '★
>  Set shF = Workbooks.Open(ThisWorkbook.Path & "\元のブック.xlsx").Sheets("該当のシート名")    '★
>  
>  With shF.Range("A1").CurrentRegion '元シートの表領域
>    '転記用配列準備(厳密には、こんなに大きくなくてもいいですが)
>    ReDim v(1 To .Rows.Count, 1 To .Columns.Count * 2)
>    For i = 2 To .Rows.Count Step 2
>      If WorksheetFunction.CountIf(.Rows(i), ">0") > 0 Then 'すべて 0 なら対象外
>        y = y + 1  '転記行
>        x = 0    '転記列
>        For j = 1 To .Columns.Count
>          If .Cells(i, j) > 0 Then
>            x = x + 1
>            v(y, x) = .Cells(i - 1, j).Value
>            v(y, x + 1) = .Cells(i, j).Value
>            x = x + 1
>          End If
>        Next
>      End If
>    Next
>  End With
>  
>  shF.Parent.Close False '元ブックを閉じる
>  '結果を一括転記
>  shT.Cells.ClearContents
>  shT.Range("A1").Resize(y, UBound(v, 2)) = v
>
>End Sub

837 / 13645 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free