Excel VBA質問箱 IV

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

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


4793 / 13646 ツリー ←次へ | 前へ→

【53995】データの抽出 ポン太 08/2/19(火) 17:55 質問[未読]
【53997】Re:データの抽出 maka 08/2/19(火) 20:24 発言[未読]
【54001】Re:データの抽出 neptune 08/2/19(火) 22:07 回答[未読]
【54008】Re:データの抽出 VBWASURETA 08/2/20(水) 0:40 回答[未読]
【54009】Re:データの抽出 VBWASURETA 08/2/20(水) 0:49 回答[未読]
【54032】Re:データの抽出 カウボーイズ 08/2/20(水) 18:58 発言[未読]
【54043】Re:データの抽出 ポン太 08/2/21(木) 14:08 質問[未読]
【54045】Re:データの抽出 ポン太 08/2/21(木) 14:53 発言[未読]
【54046】Re:データの抽出 VBWASURETA 08/2/21(木) 17:05 回答[未読]
【54048】Re:データの抽出 ポン太 08/2/21(木) 17:18 発言[未読]
【54050】Re:データの抽出 わさび 08/2/21(木) 18:17 発言[未読]
【54047】Re:データの抽出 ポン太 08/2/21(木) 17:15 質問[未読]
【54058】Re:データの抽出 今日も暇人 08/2/22(金) 11:42 発言[未読]
【54120】Re:データの抽出 ポン太 08/2/25(月) 17:10 質問[未読]
【54121】Re:データの抽出 VBWASURETA 08/2/25(月) 17:40 回答[未読]
【54122】Re:データの抽出 VBWASURETA 08/2/25(月) 18:00 発言[未読]
【54194】Re:データの抽出 ポン太 08/2/28(木) 23:28 発言[未読]
【54137】Re:データの抽出 今日も暇人 08/2/26(火) 8:46 発言[未読]
【54193】Re:データの抽出 ポン太 08/2/28(木) 23:26 回答[未読]
【54202】Re:データの抽出 VBWASURETA 08/2/29(金) 13:09 質問[未読]
【54204】Re:データの抽出 VBWASURETA 08/2/29(金) 14:17 発言[未読]
【54207】Re:データの抽出 VBWASURETA 08/2/29(金) 15:22 発言[未読]
【54288】Re:データの抽出 ポン太 08/3/3(月) 13:39 発言[未読]
【54413】Re:データの抽出 ポン太 08/3/12(水) 12:03 発言[未読]
【54415】Re:データの抽出 VBWASURETA 08/3/12(水) 13:26 質問[未読]
【54418】Re:データの抽出 ポン太 08/3/12(水) 13:45 発言[未読]
【54428】Re:データの抽出 今日も暇人 08/3/12(水) 15:20 発言[未読]
【54429】Re:データの抽出 VBWASURETA 08/3/12(水) 15:40 質問[未読]
【54430】Re:データの抽出 VBWASURETA 08/3/12(水) 16:11 発言[未読]
【54436】Re:データの抽出 VBWASURETA 08/3/12(水) 22:48 発言[未読]
【54443】Re:データの抽出 ポン太 08/3/13(木) 11:02 お礼[未読]

【53995】データの抽出
質問  ポン太  - 08/2/19(火) 17:55 -

引用なし
パスワード
   コンボで「AprWk1」と選択すると、1.シートのデータを2.シートに抽出する物を作成したいです。

1.1.シートには品目と数値が入っている。
 2.シートはそれをまとめるためのシートである。
2.1.シートと2.シートで一致する品目の数値を2.シートに反映させたいのだが、
 1.シートのAJ列に【Adapter】【Book】【Castle】等々書いてあり、
 【Apple】の場合にのみ2.シートのC列に数値を反映させたい。
上記条件があるのだが、それを下記に書いてみたものの、
どうしても動きません。
何がおかしいのでしょうか?
インデックスが有効範囲にありませんと言われます。

 Select Case (コンボ)
  Case "AprWk1"
  Dim ws1 As Worksheet, ws2 As Worksheet, Rmax As Long, i As Long, T As String
  '処理対象のシートを明確にするために変数にセット
  Set ws1 = ThisWorkbook.Worksheets("1.")
  Set ws2 = ThisWorkbook.Worksheets("2.")
  '集計対象シートの最下行:キーになるF列で判定
  Rmax = ws2.Range("F65536").End(xlUp).Row

  'SUMIFで合計する
  T = "Apple"
  i = 7
  With ActiveSheet
  Do Until .Cells(i, 1).Value = ""
  If ws2.Range("AJ" & "i-5").String = T Then
  ws1.Range("C" & i).Value = Application.WorksheetFunction.SumIf _
  (ws2.Range("E1:E" & Rmax), ws1.Range("A" & i).Value, ws2.Range("G1:G" & Rmax))
      i = i + 1
  End If
  Loop

  End With

【53997】Re:データの抽出
発言  maka  - 08/2/19(火) 20:24 -

引用なし
パスワード
   はじめまして。
ここに回答できる能力は無いのですが
下記のことが書いてあったので↓

>2.1.シートと2.シートで一致する品目の数値を2.シートに反映させたいのだが、
> 1.シートのAJ列に【Adapter】【Book】【Castle】等々書いてあり、
> 【Apple】の場合にのみ2.シートのC列に数値を反映させたい。
>上記条件があるのだが、それを下記に書いてみたものの、


>【Apple】の場合にのみ2.シートのC列に数値を反映させたい。
とあったので、1.シートにオートフィルターを設定して抽出してコピーして2.シートに貼り付けはダメなのでしょうか?

私の職場では、発注などのとき業者ごとにこの方法で発注内容を抽出して発注シートにコピーしています。

的外れだったら、すみません。

【54001】Re:データの抽出
回答  neptune  - 08/2/19(火) 22:07 -

引用なし
パスワード
   ▼ポン太 さん:
こんにちは

パッと見て気が付くのが
>If ws2.Range("AJ" & "i-5").String = T Then
の "i-5"
もしiは変数のはずなのに文字列扱い
インデックスが有効範囲にありません
のエラーが出るかも?

【54008】Re:データの抽出
回答  VBWASURETA  - 08/2/20(水) 0:40 -

引用なし
パスワード
   こんばんは。

たぶん、neptuneさんので解決しそうですがもうひとつ
気になるのが以下の二つです。
 
  Set ws1 = ThisWorkbook.Worksheets("1.")
  Set ws2 = ThisWorkbook.Worksheets("2.")

シート名はあってますか?

  'シート1
  Set ws1 = ThisWorkbook.Worksheets(1)
  'シート2
  Set ws2 = ThisWorkbook.Worksheets(2)

シート名でも間違えた場合同じエラーが発生します。

【54009】Re:データの抽出
回答  VBWASURETA  - 08/2/20(水) 0:49 -

引用なし
パスワード
   すみませんもう1点気になったところがありました。

 If ws2.Range("AJ" & "i-5").String = T Then

neptuneさんと同じ個所なのですが、

RangeメソッドにStringプロパティってありました??
Excel2000にはありませんでした。

Excel2000では

 If ws2.Range("AJ" & "i-5").Value = T Then

と指定しますが。

【54032】Re:データの抽出
発言  カウボーイズ  - 08/2/20(水) 18:58 -

引用なし
パスワード
   ▼ポン太 さん:

他の人からも色々出てますが、自分も気付いた部分をば

>  Do Until .Cells(i, 1).Value = ""
>  If ws2.Range("AJ" & "i-5").String = T Then
>  ws1.Range("C" & i).Value = Application.WorksheetFunction.SumIf _
>  (ws2.Range("E1:E" & Rmax), ws1.Range("A" & i).Value, ws2.Range("G1:G" & Rmax))
>      i = i + 1
>  End If
>  Loop
これif文の条件式に当てはまらない時、
i = i + 1
が発生しないので無限ループになりませんか?

【54043】Re:データの抽出
質問  ポン太  - 08/2/21(木) 14:08 -

引用なし
パスワード
   皆様からのご意見を参考に変更してみました。
しかし、今度は型が一致しませんと出ます。

#今回は一旦全部SUMIFで2.シートに反映させた後、
 1.シートの特定列(AJ列)に【Apple】と入っていない場合のみ
 空白を返すようにしているつもりです。
 オートコンフィグしないとダメですかね。

Private Sub コンボ_Change()

 Select Case (コンボ)
  Case "AprWk1"

  Dim ws1 As Worksheet, ws2 As Worksheet, Rmax As Long, i As Long
 
  '処理対象のシートを明確にするために変数にセット
  Set ws1 = ThisWorkbook.Worksheets("2")
  Set ws2 = ThisWorkbook.Worksheets("1")
  '集計対象シートの最下行:キーになるF列で判定
  Rmax = ws2.Range("E65536").End(xlUp).Row

  'SUMIFで合計する
  i = 7
  With ActiveSheet
  Do Until .Cells(i, 1).Value = ""
  ws1.Range("C" & i).Value = Application.WorksheetFunction.SumIf _
  (ws2.Range("E1:E" & Rmax), ws1.Range("A" & i).Value, ws2.Range("G1:G" & Rmax))
      i = i + 1
  Loop

  End With
  
  i = 7
  With ActiveSheet
  Do Until .Cells(i, 1).Value = ""
  If ws2.Range("AJ1:AJ" & Rmax) = "<>Apple" Then
  ws1.Range("C" & i).Value = Application.WorksheetFunction.SumIf _
  (ws2.Range("AJ1:AJ" & Rmax), ws1.Range("A" & i).Value, "")
      i = i + 1
  End If
  Loop

  End With
  End Select

End Sub

【54045】Re:データの抽出
発言  ポン太  - 08/2/21(木) 14:53 -

引用なし
パスワード
   自分の書いた式だと、
望んだ結果にはならないことが判明。

地道にやってみます。。。

【54046】Re:データの抽出
回答  VBWASURETA  - 08/2/21(木) 17:05 -

引用なし
パスワード
   ▼ポン太 さん:
こんにちは。

見て気になったところだけ書きます。

>If ws2.Range("AJ1:AJ" & Rmax) = "<>Apple" Then

範囲指定した場合比較演算は出来ません。
1セル単位での比較にしないとValueプロパティが見えませんから。
後、比較の"<>Apple"ですが、比較演算子も文字列ですが
これはこれで正しいのでしょうか?

【54047】Re:データの抽出
質問  ポン太  - 08/2/21(木) 17:15 -

引用なし
パスワード
   可視セルからSUMIF関数を利用してデータを引っ張ってこようとしています。

 Select Case (コンボ)
  Case "AprWk1"

  Dim ws1 As Worksheet, ws2 As Worksheet, Rmax As Long, i As Long
 
  '処理対象のシートを明確にするために変数にセット
  Set ws1 = ThisWorkbook.Worksheets("2")
  Set ws2 = ThisWorkbook.Worksheets("1")

  ws2.Select
  ws2.Range("A1").Select
  Selection.AutoFilter
  Selection.AutoFilter Field:=36, Criteria1:="Apple"
  Selection.SpecialCells(xlCellTypeVisible).Select
  '集計対象シートの最下行:キーになるE列で判定
  Rmax = ws2.Range("E65536").End(xlUp).Row
  ws1.Select


  'SUMIFで合計する
  i = 7
  With ActiveSheet
  Do Until .Cells(i, 1).Value = ""
  ws1.Range("C" & i).Value = Application.WorksheetFunction.SumIf _
  (ws2.Range("E2:E" & Rmax), ws1.Range("A" & i).Value, ws2.Range("G2:G" & Rmax))
      i = i + 1
  Loop

  End With
  End Select
 End Sub

この☆部分から下が違う気がします。
なぜならば、可視セルからだけSUMIF関数を利用したいのですが、
上記式だと見えていないセルからも数字を引っ張ってきているからです。

可視セルのみ引っ張ってくる方法をご存知の方!
どうか教えてください。

【54048】Re:データの抽出
発言  ポン太  - 08/2/21(木) 17:18 -

引用なし
パスワード
   >VBWASURETA さん
度々のご回答ありがとうございます。

>範囲指定した場合比較演算は出来ません。
>1セル単位での比較にしないとValueプロパティが見えませんから。
そうなのですね。
ということは、この式以外で考えなくては。

>後、比較の"<>Apple"ですが、比較演算子も文字列ですが
>これはこれで正しいのでしょうか?
んー。違う方法を考えます。
この列で【Apple】と入っている行の数値は抜かしたかったのですが。
逆に複雑に考えすぎて、混乱してきました。
ただ、記入した式のような考え方をしたいのです。

【54050】Re:データの抽出
発言  わさび  - 08/2/21(木) 18:17 -

引用なし
パスワード
   ▼ポン太 さん:
横からすみません。
ちょっとこの部分だけ、コメントを…

>>後、比較の"<>Apple"ですが、比較演算子も文字列ですが
>>これはこれで正しいのでしょうか?
>んー。違う方法を考えます。
>この列で【Apple】と入っている行の数値は抜かしたかったのですが。
>逆に複雑に考えすぎて、混乱してきました。
>ただ、記入した式のような考え方をしたいのです。

VBWASURETAさんが言いたかったのは、
「<>」も「"」で囲まれてしまってますよ、ということかと。
つまり、「Apple」ではなく「<>Apple」という文字列になってます。
「<> "Apple"」の誤記かと思いますが。

【54058】Re:データの抽出
発言  今日も暇人  - 08/2/22(金) 11:42 -

引用なし
パスワード
   みなさん今日は
一番最初は、これがやりたかったのかな?

>コンボで「AprWk1」と選択すると、1.シートのデータを2.シートに抽出する物を作成したいです。
>
>1.1.シートには品目と数値が入っている。
> 2.シートはそれをまとめるためのシートである。
>2.1.シートと2.シートで一致する品目の数値を2.シートに反映させたいのだが、
> 1.シートのAJ列に【Adapter】【Book】【Castle】等々書いてあり、
> 【Apple】の場合にのみ2.シートのC列に数値を反映させたい。
>上記条件があるのだが、それを下記に書いてみたものの、
>どうしても動きません。
>何がおかしいのでしょうか?
>インデックスが有効範囲にありませんと言われます。
>
> Select Case (コンボ)
>  Case "AprWk1"


Dim ws1  As Worksheet
Dim ws2  As Worksheet
Dim Ws1_Lr As Single
Dim Rmax  As Long
Dim i   As Long
Dim App   As Range
Dim Ans  As Long
Dim T   As String
 
  Set ws1 = ThisWorkbook.Worksheets(1) '処理対象のシートを明確にするために変数にセット
  Set ws2 = ThisWorkbook.Worksheets(2)
 
  Ws1_Lr = ws1.Range("A65536").End(xlUp).Row '集計シート最下行*キーになるA列で判定
  Rmax = ws2.Range("F65536").End(xlUp).Row '集計対象シート最下行*キーになるF列で判定
  T = "Apple" 'SUMIFで合計する
  i = 7    '開始行
  Ans = 0   '合計収納
  With ws1
    For i = 7 To Ws1_Lr     'ws1のA7〜A最終行まで
      For Each App In ws2.Range("AJ1:AJ" & Rmax) 'ws2のAJ1〜AJ最終行まで
        If App.Value = T Then  'AJ* が Apple だったとき
          'ws2の E* がws1の A* だったとき
          If .Range("A" & i).Value = ws2.Range("E" & App.Row).Value Then
            Ans = Ans + ws2.Range("G" & App.Row).Value 'Ansに加算
          End If
        End If
      Next
      .Range("C" & i).Value = Ans '合計書き込み
    Next i

違ってたらすみません (~o~)

【54120】Re:データの抽出
質問  ポン太  - 08/2/25(月) 17:10 -

引用なし
パスワード
   >今日も暇人 さん

ご回答ありがとうございます。

踏まえて、再度作成致しましたが、
型番の不一致と出てしまいます。

不一致箇所ってありますか?
どこが不一致なのか分かりません。

−−−
Private Sub コンボ_Change()

 Select Case (コンボ)
  Case "AprWk1"

  Dim ws1  As Worksheet, wsa  As Worksheet
  Dim Rmax  As Long, Lr   As Single
  Dim i   As Long, T   As String
  Dim App  As Range, Ans  As Long
  
 
  '処理対象のシートを明確にするために変数にセット
  Set ws1 = ThisWorkbook.Worksheets("1")
  Set wsa = ThisWorkbook.Worksheets("2")
  '集計対象シートの最下行:キーになるE列で判定
  Lr = ws1.Range("A65536").End(xlUp).Row
  Rmax = wsa.Range("E65536").End(xlUp).Row

  'SUMIFで合計する
  T = "Apple"
  i = 7
  Ans = 0

  With ws1
    For i = 7 To Lr
      For Each App In wsa.Range("AJ2:AJ" & Rmax)
        If App.Value = T Then
          Ans = Application.WorksheetFunction.SumIf _
            (wsa.Range("E2:E" & Rmax), ws1.Range("A" & i).Value, wsa.Range("G2:G" & Rmax))
          Else
          Ans = ""
        End If
      Next
      .Range("C" & i).Value = Ans
    Next i
  End With

 End Select
End Sub

【54121】Re:データの抽出
回答  VBWASURETA  - 08/2/25(月) 17:40 -

引用なし
パスワード
   こんにちは。

ぱっと見ですが。

>          Ans = ""

これですよね?
変数の型をもう少し覚えた方が良いような気がします^^;

Ans はLong型の数値です。
「""」は文字列です。それに入れようとするとエラーになりますよね?

【54122】Re:データの抽出
発言  VBWASURETA  - 08/2/25(月) 18:00 -

引用なし
パスワード
   とりあえず毎回指摘箇所を書くのもあれなんで
以下の参照して覚えてみては?


変数型一覧
//excelvba.pc-users.net/fol5/5_2.html


neptuneさんお勧めデバッグ方法HP
//members.jcom.home.ne.jp/rex-uchida/vba110.htm


エラー箇所がわからないといわれてますが
実はエラー箇所は示されてます。
デバッグ方法HPを見ていただければわかると思いますよ。

【54137】Re:データの抽出
発言  今日も暇人  - 08/2/26(火) 8:46 -

引用なし
パスワード
   ▼ポン太 さん:
私のコードではダメでしたか?
まんまコードのつもりだったんですが…。
ざんねん!

<Else
<Ans = ""

は不必要です。0を表示したくなかったら一般機能で対応するとかしたほうがググット
&単にSUMIF では希望の計算が出来ないだったんじゃないでしょうか?
作業を設けてそこに、品目&AJ列 と一つの文字列にしちゃってからじゃナイト

【54193】Re:データの抽出
回答  ポン太  - 08/2/28(木) 23:26 -

引用なし
パスワード
   >今日も暇人 さん

SUMIFで期待する数値が出なかった訳ではないです。
式自体が動かなかったのです。
文章で説明するのは難しいですね。

前回の物から Ans = "" を削除してみましたが、
条件が全く生きてこなくなってしまいました。

  '処理対象のシートを明確にするために変数にセット
  Set ws1 = ThisWorkbook.Worksheets("1")
  Set wsa = ThisWorkbook.Worksheets("2")
  '集計対象シートの最下行:キーになるE列で判定
  Lr = ws1.Range("A65536").End(xlUp).Row
  Rmax = wsa.Range("E65536").End(xlUp).Row

  T = "Apple"
  i = 7
  Ans = 0

  With ws1
    For i = 7 To Lr
      For Each App In wsa.Range("AJ2:AJ" & Rmax)
        If App.Value = T Then
          Ans = Application.WorksheetFunction.SumIf _
            (wsa.Range("E2:E" & Rmax), ws1.Range("A" & i).Value, wsa.Range("G2:G" & Rmax))
        End If
      Next
      .Range("C" & i).Value = Ans
    Next i
  End With

1シートにあるApple欄に、
2シートからAppleやBookやCastleなど沢山品目がある中で、
Appleと入っている行の個数を引っ張ってきたいのです。
のでSUMIFでないと出来ないのです。

なぜ条件が生きてこないのか分かられますか?

【54194】Re:データの抽出
発言  ポン太  - 08/2/28(木) 23:28 -

引用なし
パスワード
   >VBWASURETA さん:
毎々すみません。
文字列は基本は入らないのですね。
だから変数を使用していたはずですのに。
教えて頂いたページで学んできます。

【54202】Re:データの抽出
質問  VBWASURETA  - 08/2/29(金) 13:09 -

引用なし
パスワード
   ▼ポン太 さん、今日も暇人 さん

こんにちは。

以下のソースあまりきっちり見てませんが、
気になったので質問します。


>  '処理対象のシートを明確にするために変数にセット
>  Set ws1 = ThisWorkbook.Worksheets("1")
>  Set wsa = ThisWorkbook.Worksheets("2")
>  '集計対象シートの最下行:キーになるE列で判定
>  Lr = ws1.Range("A65536").End(xlUp).Row
>  Rmax = wsa.Range("E65536").End(xlUp).Row
>
>  T = "Apple"
>  i = 7
>  Ans = 0
>
>  With ws1
>    For i = 7 To Lr
>      For Each App In wsa.Range("AJ2:AJ" & Rmax)
>        If App.Value = T Then
>          Ans = Application.WorksheetFunction.SumIf _
>            (wsa.Range("E2:E" & Rmax), ws1.Range("A" & i).Value, wsa.Range("G2:G" & Rmax))
>        End If
>      Next
>      .Range("C" & i).Value = Ans
>    Next i
>  End With


ソースのうち


>    For i = 7 To Lr
>      For Each App In wsa.Range("AJ2:AJ" & Rmax)
>        If App.Value = T Then
>          Ans = Application.WorksheetFunction.SumIf _
>            (wsa.Range("E2:E" & Rmax), ws1.Range("A" & i).Value, wsa.Range("G2:G" & Rmax))
>        End If
>      Next
>      .Range("C" & i).Value = Ans
>    Next i


の部分ですが、何故Forループを入れ子にしているのでしょう?
SumIf関数で、シート1のA列7行目以降を元に集計しているのがわかるのですが。
中のループが終らない限り、i値が変わらないので
「If App.Value = T Then」の条件に入ったとしても
次のループ(For Each App In wsa.Range("AJ2:AJ" & Rmax))で
また「If App.Value = T Then」に入る条件があると「Ans」の
変数の値が上書きされるのは正しいのでしょうか??

質問者でないのに質問してすみません。

【54204】Re:データの抽出
発言  VBWASURETA  - 08/2/29(金) 14:17 -

引用なし
パスワード
   ▼ポン太 さん、今日も暇人 さん

ちょっと、今日も暇人さんのソースから見て
標準関数で作ってみました。


関数例:
=IF(COUNTIF(Sheet2!$AJ$2:$AJ$50,"Apple")>1,SUMIF(Sheet2!$E$2:$E50,Sheet1!A7,Sheet2!$G$2:$G$50),"")


この関数をセルC7に入れてセルC50までコピーすると
同じ動きになるかと思います。

【54207】Re:データの抽出
発言  VBWASURETA  - 08/2/29(金) 15:22 -

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

訂正です。

ソースはポン太さんが変更されたのでしたね^^;

一応生きてこない回答になってそうなので、これで原因わかるでしょうか?

【54288】Re:データの抽出
発言  ポン太  - 08/3/3(月) 13:39 -

引用なし
パスワード
   >VBWASURETA さん
毎々ご回答ありがとうございます。

>中のループが終らない限り、i値が変わらないので
>「If App.Value = T Then」の条件に入ったとしても
>次のループ(For Each App In wsa.Range("AJ2:AJ" & Rmax))で
>また「If App.Value = T Then」に入る条件があると「Ans」の
>変数の値が上書きされるのは正しいのでしょうか??

…正しくありません。
ただ、シート1のA列とシート2のE列が一致していたら、
シート2のG列の値をシート1のC列に返すのですが、
シート2のAJ列に【Apple】がある場合のみ返す、となると、
このようになってしまいました。
これはVBWASURETAさんの言うように上書きされてるから、ということですよね?

【54413】Re:データの抽出
発言  ポン太  - 08/3/12(水) 12:03 -

引用なし
パスワード
   未だ出来ず。
上書きされないように
Exit Forを入れてみたものの、
全然効果ナシでした。

  With ws1
    For i = 7 To Lr
      For Each App In wsa.Range("AJ2:AJ" & Rmax)
        If App.Value = T Then
          Ans = Application.WorksheetFunction.SumIf _
            (wsa.Range("E2:E" & Rmax), ws1.Range("A" & i).Value, wsa.Range("G2:G" & Rmax))
    Exit For

        End If
      Next
      .Range("C" & i).Value = Ans
    Next i
  End With

こういう抽出はマクロでは出来ないのですかねー。
あったら便利なのに。

【54415】Re:データの抽出
質問  VBWASURETA  - 08/3/12(水) 13:26 -

引用なし
パスワード
   ▼ポン太 さん:
こんにちは。

ちょっと、条件を理解していないのですが。

>      For Each App In wsa.Range("AJ2:AJ" & Rmax)
>        If App.Value = T Then
>          Ans = Application.WorksheetFunction.SumIf _
>            (wsa.Range("E2:E" & Rmax), ws1.Range("A" & i).Value, wsa.Range("G2:G" & Rmax))
>    Exit For
>
>        End If
>      Next

この中のループは意味がないです^^;
Sumif関数の範囲は常に固定範囲ですし、wsa.Range("AJ2:AJ" & Rmax)の
範囲に"Apple"が存在するかの1回見るだけで良いはずなので、
ループを入れると不要な動きをしてそうです。

後、気になったのが"Apple"が1件も上記の範囲になかった場合、
セルに全く関数が入らない条件になってますがこれはこれで正しいのでしょうか?

【54418】Re:データの抽出
発言  ポン太  - 08/3/12(水) 13:45 -

引用なし
パスワード
   >VBWASURETAさん
ご回答ありがとうございます。

>この中のループは意味がないです^^;
>Sumif関数の範囲は常に固定範囲ですし、wsa.Range("AJ2:AJ" & Rmax)の
>範囲に"Apple"が存在するかの1回見るだけで良いはずなので、
>ループを入れると不要な動きをしてそうです。
そうなのですね。
AJ列の中に"Apple"という文字列が入っているのかを見て、
入っている場合は入っている行だけの中からSUMIFを行なってほしいのです。
"Book"や"Castle"の行からはSUMIFで引っ張って欲しくないです。


>後、気になったのが"Apple"が1件も上記の範囲になかった場合、
>セルに全く関数が入らない条件になってますがこれはこれで正しいのでしょうか?
はい。"Apple"用のシート(シート1)にデータを写しているので構いません。

<シート1(Apple用)>
行\列  A  B  C  D  § AI  AJ
1    いも    10            
2    うり    0
3    えび    2
4    おけ
5    かに
6    きも
7    くき
8    けし

<シート2>
行\列  A B C D E  F  G  H§ AI  AJ
1          いも    10       Apple
2          うり    5       Castle
3          えび    2       Apple
4          いも    8       Castle
5          かに    20       Book
6          いも    6       Book
7          うり    1       Castle
8          えび    9       Book

のような感じです。今更ですが。。。

ループをはずす方法を考えます。

【54428】Re:データの抽出
発言  今日も暇人  - 08/3/12(水) 15:20 -

引用なし
パスワード
   ポン太 さん、こんにちは
以下コードを試しに動かしてください。
*SUMIFではないです。
Sub a()

Dim ws1  As Worksheet
Dim ws2  As Worksheet
Dim Ws1_Lr As Single
Dim Rmax  As Long
Dim i   As Long
Dim App   As Range
Dim Ans  As Single
Dim T   As String
       
Set ws1 = ThisWorkbook.Worksheets(1) '処理対象のシートを明確にするために変数にセット
Set ws2 = ThisWorkbook.Worksheets(2)
      
Ws1_Lr = ws1.Range("A65536").End(xlUp).Row '集計シート最下行*キーになるA列で判定
Rmax = ws2.Range("F65536").End(xlUp).Row '集計対象シート最下行*キーになるF列で判定
T = "Apple" 'SUMIFで合計する
i = 1    '開始行
With ws1
  For i = 1 To Ws1_Lr     'ws1のA3〜A最終行まで
  Ans = 0   '合計収納
    For Each App In ws2.Range("AJ2:AJ" & Rmax) 'ws2のAJ1〜AJ最終行まで
      If App.Value = T Then  'AJ* が Apple だったとき
        'ws2の E* がws1の A* だったとき
        If .Range("A" & i).Value = ws2.Range("E" & App.Row).Value Then
          Ans = Ans + CLng(ws2.Range("G" & App.Row).Value) 'Ansに加算
        End If
       End If
     Next
    .Range("C" & i).Value = Ans '合計書き込み
  Next i
End With
End Sub

【54429】Re:データの抽出
質問  VBWASURETA  - 08/3/12(水) 15:40 -

引用なし
パスワード
   ちょっと見ていてまさかとは思いますが何となくですが

> <シート1(Apple用)>
> 行\列  A  B  C  D  § AI  AJ
> 1    いも    10            
> 2    うり    0
> 3    えび    2
> 4    おけ
> 5    かに
> 6    きも
> 7    くき
> 8    けし


シート1は入力用で、Aに入っている品に対して、


> <シート2>
> 行\列  A B C D E  F  G  H§ AI  AJ
> 1          いも    10       Apple
> 2          うり    5       Castle
> 3          えび    2       Apple
> 4          いも    8       Castle
> 5          かに    20       Book
> 6          いも    6       Book
> 7          うり    1       Castle
> 8          えび    9       Book

シート2の参照マスタデータの品とそれに対する数値(金額?)を
抽出する条件プラスとして"Apple"の入った条件の場合のみ
該当数値(金額?)を抜き出す。

式としては
シート1のA列(品) = シート2の見つかった同じ品名E列 And シート2のAJ列(見つかった同じ品名の同一行) = "Apple"

<結果>
真:シート2のG列数値(見つかった同一行の数値)
偽:次の同一品の検索に入る


まさかこれではないですよね?^^;

【54430】Re:データの抽出
発言  VBWASURETA  - 08/3/12(水) 16:11 -

引用なし
パスワード
   さっきの追記です。

もしさっきので正解だったら SUMPRODUCT関数 使えば出来ますよ。

例:SUMPRODUCT((シート2の品名範囲=検索品名)*(シート2のApple条件範囲=検索Apple)*(合計範囲))
※間の「*」は積ですがandと思ってください。

=SUMPRODUCT((Sheet2!$A$1:$A$8=Sheet1!A1)*(Sheet2!AJ1:AJ8="Apple")*(Sheet2!F1:F8))

【54436】Re:データの抽出
発言  VBWASURETA  - 08/3/12(水) 22:48 -

引用なし
パスワード
   一応、ポン太さんのソースからSumProduct関数で使用する
サンプルを置いておきます。

Sub main()
  Dim ws1       As Worksheet
  Dim ws2       As Worksheet
  Dim DataMaxRow   As Long
  Dim MstMaxRow    As Long
  Dim i        As Long
  Dim ret       As Long
  Dim strSumprdct   As String
  Dim strSearch    As String
  Dim strSumSearch  As String
  
  Set ws1 = ThisWorkbook.Worksheets(1)  '処理対象のシートを明確にするために変数にセット
  Set ws2 = ThisWorkbook.Worksheets(2)
  
  DataMaxRow = ws1.Range("A65536").End(xlUp).Row
  
  '↓このシート2の最大行数が何故F列なのかわからないのでデータがあるE列にしました。
  ' 多分、今日も暇人さんのソースもここをE列にすると動きます。
  MstMaxRow = ws2.Range("E65536").End(xlUp).Row

  strSumSearch = "Apple" '合計条件
  
  With ws1
    For i = 7 To DataMaxRow 'ws1のA7〜A最終行まで
      'シート1のA列条件
      strSearch = ws1.Cells(i, 1).Value
      
      'Sumproduct関数の文字列
      strSumprdct = "=SUMPRODUCT((" & ws2.Name & "!E1:E" & MstMaxRow & " = """ & strSearch & """)*"
      strSumprdct = strSumprdct & "(" & ws2.Name & "!AJ1:AJ" & MstMaxRow & " = """ & strSumSearch & """)*"
      strSumprdct = strSumprdct & "(" & ws2.Name & "!G1:G" & MstMaxRow & "))"
            
      'シート関数の実行
      ret = Application.Evaluate(strSumprdct)
      
      If (ret > 0) Then
        'シート1のB列に結果を書く
        ws1.Cells(i, 2) = ret
      End If
    Next i
  End With
  
  '最後にシートオブジェクトのインスタンス解放は必ずしましょう。
  Set ws1 = Nothing
  Set ws2 = Nothing
End Sub

【54443】Re:データの抽出
お礼  ポン太  - 08/3/13(木) 11:02 -

引用なし
パスワード
   >VBWASURETAさん
>今日も暇人さん

お2人のご協力により、
動くようになりました!
本当にありがとうございます。

SUMPRODUCTという関数も初めて見ましたし、
デバックについても教えて頂きました。

本当にありがとうございます。

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