Excelで複数条件で重複したデータも抜出したい。

質問

はじめての質問でわかりづらいと思いますが、お願い致します。
Excel2003で建築部材の入出庫管理をしたいと考えていますが、日々の建築部材出入の記録用の入力シートにデータがあり、そこから貸出し状況シートへ（貸出に対してバラバラ返却の状況）を複数の条件にて重複したデータも抜出したい。　貸出し状況シートのABCDはなんとか抜出しましたが、EFGHIJ・・・が分かりませんのでご教授下さい。

入力シート
　　A　　　　　　B　　　　　　C　　　D　　　　　　E　　　　　　F　G　・・・・
１　部材番号　入出庫日　数量　貸出現場　出入処理
２　ABC100　　9月10日　　　50　　空陸　　　返却
２　DEF100　　9月10日　　　20　　空陸　　　返却
３　DEF100　　9月6日　　　　30　　空陸　　　返却
３　DEF100　　9月4日　　　　50　　空陸　　　返却
４　DEF100　　9月2日　　　100　　山川　　　 貸出
５　DEF100　　9月1日　　　100　　空陸　　　 貸出
６　ABC100　　9月1日　　　100　　空陸　　　貸出
・
・

貸出し状況シート
　　A　　　　　　B　　　　　　　C　　　D　　　　　E　　　　　F　　G　 　　 H　　　Ｉ　　　　　　J・・・・
１　貸出現場　部材番号　出庫日　数量　返却日1　数量1 返却日2　数量2　返却日3　数量3
２　山川 DEF100　　9月2日　　100　　　　
３　空陸　　　DEF100　　9月1日　　100　　9月4日　50　　9月6日　　30　　9月10日　20
４　空陸　　　ABC100　　9月1日　　100　　9月10日　50
５　　
・
・

回答No.3

tom04 · ベストアンサー

何度もお邪魔します。
お礼欄を読ませてもらって・・・
関数での方法をご希望だというコトですので、無理やり関数でやってみました。
↓の画像（小さくて見にくいかもしれません）のようにやはりSheet1に作業用の列を設ける方法しか思い浮かびませんでした。

Sheet1の作業列F2セルを
=IF(E2="返却",$D2&"_"&$A2,"")
としてオートフィルでずぃ～～～！っと下へコピー！

そしてSheet2のA2セル（配列数式になります）に
=IF(COUNTIF(Sheet1!$E:$E,"貸出")<ROW(A1),"",INDEX(Sheet1!$A$1:$E$1000,SMALL(IF(Sheet1!$E$1:$E$1000="貸出",ROW($A$1:$A$1000)),ROW(A1)),MATCH(A$1,Sheet1!$A$1:$E$1,0)))
としてD2セルまでコピー！
※　C2セルはエラーになりますので、C列のみ別途数式を入れます。
C2セル（これも配列数式です）は
=IF(A2="","",INDEX(Sheet1!$B$1:$B$1000,SMALL(IF(Sheet1!$E$1:$E$1000="貸出",ROW($A$1:$A$1000)),ROW(A1))))
としてセルの表示形式は「日付」にしておきます。
A2～D2セルを範囲指定　→　D2セルのフィルハンドルで下へコピー！

E2セル（配列数式・セルの表示形式は「日付」）は
=IF(COUNTIF(Sheet1!$F:$F,$A2&"_"&$B2)<COLUMN(B1)/2,"",SMALL(IF(Sheet1!$F$1:$F$1000=$A2&"_"&$B2,Sheet1!$B$1:$B$1000),COLUMN(B1)/2))

F2セル（配列数式ではありません。表示形式は「標準」）は
=IF(E2="","",SUMPRODUCT((Sheet1!$D$1:$D$1000=$A2)*(Sheet1!$A$1:$A$1000=$B2)*(Sheet1!$B$1:$B$1000=E2),Sheet1!$C$1:$C$1000))

という数式を入れ、E2・F2セルを範囲指定　→　F2セルのフィルハンドルで2列ずつ列方向にコピー！
そのまま下へオートフィルでコピーすると画像のような感じになります。

※　配列数式の場合はPCに負担を掛けますので、データ量が多い場合は作業列を多くしてでも
極力配列数式にしない方が良いと思います。

この程度で参考になりますかね？m(_ _)m

お礼

大変早い指摘をいただき心からお礼いたします。
ご指摘どおり、配列関数が実行になっていなかったり、数量のタイトルが違っていたり、操作列１のセル文字接続の記号が"_"の指示で"－"になっていたりしていました。
１時間かかりましたが完璧でした。（ドジでした。）
これを元に発展させてみます。
ありがとうございました。

補足

毎度お世話になります。
手抜きでそのままコピペで関数と配列式を移して実行してみましたが、Sheet2のA2-D2セルのみデータの表示が出ましたが貸出のデータではなさそうでした。
配列関数もまだ初心者で修正箇所がわかりませんでした。
ご教授頂けないでしょうか。
宜しく、お願い致します。

貸出現場 部材番号 入出庫日 数量 返却日1 数量1
空陸 ABC100 2011年9月10日 50
#NUM! #NUM! #NUM! #NUM!
#NUM! #NUM! #NUM! #NUM!

回答No.4

tom04

またまたお邪魔します。

補足のエラー部分をみると、数式が配列数式になっていないように思われます。

この画面から各セルにコピー＆ペーストされているというコトですので、

セルに貼り付け後
（1）数式バー内で一度クリック
（2）F2キーを押す
（3）貼り付けセルでダブルクリック

上記（1）～（3）のどの操作でもかまいませんので行ってみてください。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式が｛　｝マークで囲まれ、配列数式になります。

その後前回書いたようにセルの表示形式を「日付」・「標準」など設定しオートフィルでコピーしてみてください。

Sheet2のE2・F2セルは範囲指定し2列ずつ列方向にコピーは忘れないでください。
今こちらでExcelを開き、この画面からコピー＆ペーストするとちゃんと表示できたみたいです。

今一度頑張ってトライしてみてくださいね。m(__)m

回答No.2

tom04

No.1です！
たびたびごめんなさい。
投稿後に思ったのですが・・・

重要なのは貸出した部材がいくつ返却されていないのか？ということだと思います。

余計なお世話かもしれませんが、そちら重視で考えてみました。
貸出列・返却列は別列の方が良いと思いますので、勝手に↓のような感じの表にしてみました。
（日付は無視しています）

Sheet1に作業用の列を2列設けています。
作業列1のF2セルに
=IF(A2="","",A2&"-"&B2)
作業列2のG2セルに
=IF(A2="","",IF(COUNTIF($F$2:F2,F2)=1,ROW(),""))
という数式を入れ、F2・G2セルを範囲指定　→　G2セルのフィルハンドルでずぃ～～～！っと下へコピーしておきます。

そして、Sheet2のA2セルに
=IF(COUNT(Sheet1!$G:$G)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$G:$G,ROW(A1))))
という数式を入れ隣のB2セルまでコピー
C2セルを
=IF(A2="","",SUMIF(Sheet1!$F:$F,$A2&"-"&$B2,Sheet1!D:D))
として、隣のD2セルまでコピー
E2セルは単に
=IF(A2="","",C2-D2)

最後にSheet2のA2～E2セルを範囲指定し、E2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

これでどの「部材番号」がどこの「貸出現場」にいくつ残っているかすぐに判断できると思います。

※　最初に書いたようにご希望の方法でなかったら読み流してくださいね。m(_ _)m

お礼

配列数式での計算が必要になり必要に駆られての質問でした。　継続して返却日付のための表示数式を模索していますが、作業列を省いた配列数式にこだわっていましたが、わかりやすくしたほうが早く式の完成に近くなることを教わりました。　教えていただいたところは完璧でした。
ありがとうございます。

回答No.1

tom04

こんばんは！
途中まではできているというコトなので、余計なお世話になるかもしれませんが・・・

VBAで無理やりやってみました。
Sheet1にデータがありSheet2に表示するとします。
Sheet3を作業用のSheetとして使用していますので、Sheet3は使っていないという前提です。

画面左下にあるSheet1のSheet見出し上で右クリック　→　コードの表示　→　VBE画面がでますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j As Long
Dim ws2, ws3 As Worksheet
Set ws2 = Worksheets(2)
Set ws3 = Worksheets(3)
ws2.Cells.ClearContents
ws3.Cells.Clear
Application.ScreenUpdating = False
With ws2.Cells(1, 1)
.Value = Cells(1, 4)
.Offset(, 1) = Cells(1, 1)
.Offset(, 2) = "出庫日"
.Offset(, 3) = Cells(1, 3)
End With
i = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(2, 1), Cells(i, 5)).Copy Destination:=ws3.Cells(1, 2)
For j = 1 To ws3.Cells(Rows.Count, 2).End(xlUp).Row
ws3.Cells(j, 1) = ws3.Cells(j, 5) & ws3.Cells(j, 2)
Next j
For j = 1 To ws3.Cells(Rows.Count, 1).End(xlUp).Row
If ws3.Cells(j, 6) = "貸出" Then
With ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = ws3.Cells(j, 5)
.Offset(, 1) = ws3.Cells(j, 2)
With .Offset(, 2)
.Value = ws3.Cells(j, 3)
.NumberFormatLocal = "m月d日"
End With
.Offset(, 3) = ws3.Cells(j, 4)
End With
End If
Next j
ws3.Columns("A:F").Sort key1:=ws3.Cells(1, 3), order1:=xlAscending
For i = 2 To ws2.Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To ws3.Cells(Rows.Count, 1).End(xlUp).Row
If ws2.Cells(i, 1) & ws2.Cells(i, 2) = ws3.Cells(j, 1) And ws3.Cells(j, 6) = "返却" Then
With ws2.Cells(i, Columns.Count).End(xlToLeft).Offset(, 1)
.Value = ws3.Cells(j, 3)
.NumberFormatLocal = "m月d日"
.Offset(, 1) = ws3.Cells(j, 4)
End With
End If
Next j
Next i
j = ws2.UsedRange.Columns.Count
For i = 5 To j Step 2
With ws2.Cells(1, i)
.Value = "返却日" & (i - 3) / 2
.Offset(, 1) = "数量" & (i - 3) / 2
End With
Next i
ws2.Columns.AutoFit
ws3.Cells.Clear
Application.ScreenUpdating = True
End Sub 'この行まで

※　一旦マクロを実行すると元に戻せませんので、別BookにSheet1のデータをコピーしてマクロを試してみてください。

参考になれば良いのですが・・・m(_ _)m

お礼

お礼とお詫び
ご丁寧にご教授頂き大変恐縮ですが、エクセルは初心者ですので簡単な関数や配列数式がやっとでした。
ご教授頂いたマクロを使えるように努力します。
こちらの質問の仕方が悪く大変申し訳ございません。
今回がはじめての質問でしたので質問内容に今後教えていただく内容を具体的に記述致します。
どうか、お気を悪くされないでください。