初めての店舗開業を成功させよう>>

Excelでデータ抽出処理を以下のようにしたく、関数でできませんか?


1、Sheet1には以下のようなデータになっており、
  同じ取引先のデータも含んでいます。
  <取引先>  <商品名>   <数量>  <金額>  <重要フラグ>
   A社   みかん     10    23,000    0
   A社   もも      5    12,000    1
   B社   ぶどう     3    8,000      1
   C社   みかん     5    11,500    0
   C社   バナナ     5    9,000     1
   D社   すいか     8    21,500    0
   D社   メロン     15    39,000     1


2、このSheet1のデータをSheet2に需要フラグ="1"のデータのみ抽出したいです。
  Sheet2表の最下に合計行も追加した。

3、このSheet1のデータをSheet3に取引先毎に集計したデータを表示したいです。
  Sheet3表の最下に合計行も追加した。

 できれば関数でできればいいのですが、
 何かよい方法はないでしょうか?よろしくお願いします。
 
 関数でできなければ、VBAの標準モジュールでもやりたいですが、
 どなたかご教授よろしくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (11件中11~11件)

こんばんは!


関数の方法での一例です。

↓の画像(少し小さくて見づらいかもしれません)のようにSheet1に作業用の列を設けています(Sheet3に重複なしに抽出するため)
Sheet2・Sheet3の1行目の各項目は入力してあるとします。

Sheet1の作業列F2セルに
=IF(COUNTIF($A$2:A2,A2)=1,ROW(),"")
という数式を入れオートフィルでずぃ~~~!っと下へコピーしておきます。

Sheet2のA2セルに
=IF(COUNTIF(Sheet1!$E:$E,1)<ROW(A1),"",INDEX(Sheet1!A$1:A$100,SMALL(IF(Sheet1!$E$1:$E$100=1,ROW($A$1:$A$100)),ROW(A1))))
※ これは配列数式になってしまいますので、この画面からSheet2のA2セルにコピー&ペーストする場合は
A2セルに貼り付け後、数式バー内で一度クリック!編集可能になりますので
Shift+Ctrlキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。
これを列方向と行方向にオートフィルでコピーすると画像のSheet2のようになります。

続いてSheet3のA2セルに(どちらも配列数式ではありません!)
=IF(COUNT(Sheet1!F:F)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!F:F,ROW(A1))))
B2セルに
=IF(A2="","",SUMIF(Sheet1!A:A,A2,Sheet1!D:D))
としてA2・B2セルを範囲指定 → B2セルのフィルハンドルで下へコピー!
これで画像のような感じになります。

ただし・・・
最終行に合計の欄がほしいというコトですが、関数ではかなり難しいと思います。
VBAだと可能ですので、コードの一例も載せておきます。
この場合はSheet1の作業列は不要です。

Alt+F11キー → VBE画面が出ますので、画面左側の「This Workbook」をダブルクリックし、
↓のコードをコピー&ペーストしてマクロを実行してみてください。
(この場合もSheet2・Sheet3の1行目の項目はすでに入力済みだとしています。)

Alt+F8キー → マクロ → マクロ実行です。

Sub test() 'この行から
Dim i, j As Long
Dim vl As Variant
Dim ws1, ws2, ws3 As Worksheet
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
Set ws3 = Worksheets("sheet3")
i = ws2.Cells(Rows.Count, 1).End(xlUp).Row
j = ws3.Cells(Rows.Count, 1).End(xlUp).Row
If i > 1 Then
Range(ws2.Cells(2, 1), ws2.Cells(i, 5)).Clear
End If
If j > 1 Then
Range(ws3.Cells(2, 1), ws3.Cells(j, 2)).Clear
End If
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To 5
If ws1.Cells(i, 5) = 1 Then
ws2.Cells(Rows.Count, j).End(xlUp).Offset(1) = ws1.Cells(i, j)
End If
Next j
Next i
j = ws2.Cells(Rows.Count, 1).End(xlUp).Row
With ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = "合計"
.Offset(, 3) = WorksheetFunction.Sum(Range(ws2.Cells(2, 4), ws2.Cells(j, 4)))
End With
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(ws3.Columns(1), ws1.Cells(i, 1)) = 0 Then
ws3.Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws1.Cells(i, 1)
End If
Next i
For j = 2 To ws3.Cells(Rows.Count, 1).End(xlUp).Row
vl = 0
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If ws1.Cells(i, 1) = ws3.Cells(j, 1) Then
vl = vl + ws1.Cells(i, 4)
End If
Next i
ws3.Cells(j, 2) = vl
Next j
j = ws3.Cells(Rows.Count, 1).End(xlUp).Row
With ws3.Cells(j + 1, 1)
.Value = "合計"
.Offset(, 1) = WorksheetFunction.Sum(Range(ws3.Cells(2, 2), ws3.Cells(j, 2)))
End With
End Sub 'この行まで

こんな感じではどうでしょうか?
どうも長々と失礼しました。m(_ _)m
「Excelの関数処理教えてください」の回答画像1
    • good
    • 0
この回答へのお礼

tom04さん

丁念なご教授どうもありがとうございます。

kagakusukiさんの回答を参考して、
無事解決できました。

お礼日時:2011/09/15 18:16

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルで打ち込んだ数字を自動で別シートに表示したい

エクセルでセルに打ち込んだ数字を自動で別シートに表示できる方法があれば、教えてください。

例えば、シート1のC1に5を打ち込んだら、シート2のD2にシート1で打ち込んだ5が自動で表示される。

また1列すべてを自動で表示させる場合、一つのセルの時と違いがありましたら教えてください。よろしくお願いします。

Aベストアンサー

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コピー&リンク貼り付けを使うと便利です。

例)例)シート1のC1~C5に何かを入力したら、
  シート2のD2~D7にシート1で打ち込んだものが自動で表示される

  シート1にあるコピー元のセルを範囲選択して、
  シート2のD2の上で「右クリック」⇒「形式を選択して貼り付け」
  をクリックします。

  そして出てきた小さな画面の左下にある「リンク貼り付け」という
  ボタンをクリックすると完成です。
  試してみてください。。

  念のためにリンク貼り付けを図解しているURLを載せておきます。
  参考にしてみてくださいね。。
  http://www.geocities.jp/office_inoue/excel/eq21.htm

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コ...続きを読む


人気Q&Aランキング

おすすめ情報