エクセルで重複していないデータ抽出方法

質問

エクセル2007を使用しています。

シート1に在庫ID、シート2に出荷予定IDを入力しています。
そこでシート2の一部分に出荷予定IDを引いた在庫IDを表示させたい
のですが、アドバイスお願いいたします。
例
シート1（在庫ID）
　　　A　　 B　　 C
1 　 234 　567 　487
2 　 687　 258　 764
3 　 258　 486 　756

シート2（出荷予定ID）
　　 　A
1 　 258
2 　 486
3 　 234

15　 687,567,487...(出荷IDを引いた在庫ID)

A15のセルに全IDは入らないと思うのでセルの結合などをする予定です。
フィルタなどいろいろ試したのですが、どうもうまくいかず、お手上げです。
よろしくお願いいたします。

回答No.4

_Kyle · ベストアンサー

#2です。

>出荷後有高が#NAME?と出てしまいました。

「#NAME?」エラーが出るのは、
定義した関数について、存在自体がExcelに認識されていない状態ですね。

●可能性１．マクロのセキュリティ設定に問題がある

　(通常のマクロに較べて)特別な設定がいるわけではないので、
　初歩的なものであれマクロが動く状態であれば問題ないはずですが…。

●可能性２．コードを記述する場所が違う

　SUBで始まる通常のマクロと同様に、
　[標準モジュール]を挿入して記述してください。

●可能性３．数式が違う

　=Sample(～,～)　とすべきところが　=Sanple(～,～)
　になってる…なんてことはありませんか？
-----------------------------------------------
なお、申し訳ないのですが「VBAの設置や起動それ自体」については
回答中で詳しくは説明しないことにしています。

マクロの使用にはセキュリティや配布・保守・引継の問題に加えて、
(職場内での)権限・裁量の問題も絡んでくるので
　１．キーを右に回す
　２．ブレーキを踏みながらレバーをDに動かす
　３．ハンドブレーキを戻す
　４．ブレーキを戻してアクセルを踏む
みたいな説明をするのは不誠実に思われるからです。

今回のコードは「ユーザー定義関数」と呼ばれるもので、
設置も使用方法も(コード自体も)基礎的なものです。
これを機会に参考書やWEBサイト等で体系的に勉強されてみてはいかがでしょう？

■Excelでお仕事!
http://www.asahi-net.or.jp/~ef2o-inue/top01.html
●『やさしくわかるExcelVBAプログラミング』
http://www.amazon.co.jp/gp/product/479734279X/
-----------------------------------------------
ついでなので、VBAを使わず組込み関数で
処理する場合についても説明しておきます。

※説明の便宜のため同一シートで処理していますが、
　作業枠の部分はカット＆ペーストで別シートに移動して構いません。
※#2と同様、在庫IDに258が2つ、出荷予定IDに258が1つあるような場合は、
　2-1=1で1つ残す仕様です。

1.在庫ID
　仮に、C5:E10 の6行3列を在庫ID枠とします。
2.出荷ID
　仮に、C16:C20 の5行1列を出荷ID枠とします。
3.列位置・行位置
　　K5セル　：　1
　　L5セル　：　1
　　K6セル　：　=IF(L6=1,K5+1,K5)
　　L6セル　：　=IF(L5=6,1,L5+1)
　として、K6:L6セルを在庫ID枠の枠数分(3-6が返るまで)下方にフィル
　※L6の数式の「6」は在庫ID枠の行数を指定します。
5.在庫ID,出荷後ID,作業列
　　M5セル　：　=INDEX($C$5:$E$10,L5,K5)&""
　　N5セル　：　=IF(COUNTIF(M$5:M5,M5)>COUNTIF(C$16:C$20,M5),M5,"")
　　O5セル　：　=O4&IF(N5<>"",","&N5,"")
　として、M5:O5を下方にフィル
6.出荷後有高
　　C23セル　：　=RIGHT(O22,LEN(O22)-1-LEN(O4))
　※O22の部分は8でフィルしたO列の最終セルを指定します。

※在庫がない、あるいは足りない場合もエラー表示はしません。
※在庫IDに258が2つ、出荷予定IDに258が1つあるような場合に、
　258をすべて削除するという場合は、
　　N5セル　：　=IF(COUNTIF(C$16:C$20,M5),"",M5)
　としてください。

ご参考まで。

お礼

_Kyles様ご丁寧な説明ありがとうございます。m(__)m
VBAでもできそうな気がしてきました（^^；
教えていただいたWebサイトもとても参考になりそうです。
もうちょっと基本を勉強してからチャレンジしてみたいと思います。
本当にありがとうございました。

回答No.3

KURUMITO

シート1でもよいのですがシート2の適当な列に作業列を設けて行ってはどうでしょう。
シート1ではA,B,Cの列に在庫のＩＤが入っているとします。
例えばシート2のＨ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(ROW(A1)<=COUNT(Sheet1!$A$1:$A$100),Sheet1!A1, IF(ROW(A1)<=COUNT(Sheet1!$A$1:$B$100),INDIRECT("Sheet1!B"&ROW(A1)-COUNT(Sheet1!$A$1:$A$100)), IF(ROW(A1)<=COUNT(Sheet1!$A$1:$C$100),INDIRECT("Sheet1!C"&ROW(A1)-COUNT(Sheet1!$A$1:$B$100)),"")))
Ｉ2セルには次の式を入力し下方にオートフィルドラッグします。
=IF(H2="","",IF(COUNTIF(A$1:A$3,H2)=0,MAX(I$1:I1)+1,""))
答えとなるＡ10セルには次の式を入力します。
=IF(COUNTIF(I:I,1)=0,"",INDIRECT("H"&MATCH(1,I:I,0)))&IF(COUNTIF(I:I,2)=0,"",","&INDIRECT("H"&MATCH(2,I:I,0)))&IF(COUNTIF(I:I,3)=0,"",","&INDIRECT("H"&MATCH(3,I:I,0)))&IF(COUNTIF(I:I,4)=0,"",","&INDIRECT("H"&MATCH(4,I:I,0)))&IF(COUNTIF(I:I,5)=0,"",","&INDIRECT("H"&MATCH(5,I:I,0)))&IF(COUNTIF(I:I,6)=0,"",","&INDIRECT("H"&MATCH(6,I:I,0)))&IF(COUNTIF(I:I,7)=0,"",","&INDIRECT("H"&MATCH(7,I:I,0)))&IF(COUNTIF(I:I,8)=0,"",","&INDIRECT("H"&MATCH(8,I:I,0)))&IF(COUNTIF(I:I,9)=0,"",","&INDIRECT("H"&MATCH(9,I:I,0)))
これは該当するＩＤを9個まで表示する式です。適宜修正して入力してください。
答えは次のように表示されます。
687,567,487,764,756
なお、作業列が気になるようでしたら作業列を非表示にして対応すればよいでしょう。このような複雑なケースでは作業列を使って対応することが分かり易く最も効率のよい方法です。

お礼

KURUMITO様ありがとうございます。
お返事遅くなり申し訳ございません。
マクロ使わなくてもできてしまうものなんですね（^^;
理解するのに時間かかりそうですが、とても勉強になります。
ありがとうございます。m(__)m

回答No.2

_Kyle

不定個数のデータを結合して単一のセルに返すのは、
一般機能では困難ですが、もしVBA(マクロ)が選択肢に入るなら
下記のようなユーザー定義関数で可能です。

●動作の概要

　Sample(元範囲,除去範囲)

　[元範囲](在庫ID)の各データから
　[除去範囲](出荷予定ID)にあるデータを除去して
　残ったデータをカンマで連結した文字列を返す。

　※元範囲、除去範囲とも複数列対応です。
　　元範囲の縦方向⇒横方向の並びで書き出します。

　※除去範囲のデータが、元範囲に無い場合
　　(在庫にないIDの出荷を予定している場合)はエラーを表示します。

　※あるIDについて、元範囲にある数より除去範囲にある数の方が多い場合
　　(二重(超過)出荷の場合)もエラーを表示します。

　※「出荷IDに258とあれば在庫IDの258を【全て】出す」という場合は、
　　下から6行目を以下のように変更してください。

　　Substitute(rstStr, elmAry(i, j) & ",", "", 1)
　　↓
　　Substitute(rstStr, elmAry(i, j) & ",", "")

'========================↓　ココカラ　↓========================
Function Sample( _
　ByVal orgRng As Range, ByVal elmRng As Range _
　) As String
　Dim orgAry As Variant
　Dim elmAry As Variant
　Dim rstStr As String
　Dim i　　　As Long
　Dim j　　　As Long
　orgAry = orgRng.Value
　elmAry = elmRng.Value
　rstStr = ","
　For j = 1 To UBound(orgAry, 2)
　　For i = 1 To UBound(orgAry, 1)
　　　If orgAry(i, j) <> "" Then
　　　　rstStr = rstStr & orgAry(i, j) & ","
　　　End If
　　Next i
　Next j
　For i = 1 To UBound(elmAry, 1)
　　For j = 1 To UBound(elmAry, 2)
　　　If elmAry(i, j) <> "" Then
　　　　If InStr(1, rstStr, "," & elmAry(i, j) & ",") = 0 Then
　　　　　Sample = "#えら～! 在庫ナシor二重出荷：" & elmAry(i, j)
　　　　　Exit Function
　　　　End If
　　　　rstStr = Application.WorksheetFunction. _
　　　　　Substitute(rstStr, elmAry(i, j) & ",", "", 1)
　　　End If
　　Next j
　Next i
　Sample = Mid(rstStr, 2, Len(rstStr) - 2)
End Function
'========================↑　ココマデ　↑========================

Excel2007で動作確認。

全体を文字列にして処理してるあたり、いかにも泥臭い感じですが、
速度面での不利は配列処理でカバー(というより相殺？)して
二重出荷や書き出し順等の仕様変更に備えた方が良さそうという判断です。

---------------------------------
なお、VBA不可の場合ですが、
「作業シートにあらかじめ十分な枠を用意して数式で埋めておく」
という手もなくはないです。

実データの規模
　・元範囲・除去範囲の行数／列数
実データの状態
　・決まったサイズの枠があるのか、個数に応じて枠を伸縮するのか
を補足していただければ検討してみます。

以上ご参考まで。長乱文・乱コード陳謝。

お礼

いろいろ試行錯誤し、画像UPして下さったので
同じように表を作りテストしましたが、出荷後有高が#NAME?と出て
しまいました。
何か手順を間違えてるのでしょうか？
よろしくお願いいたします。m(__)m

補足

_Kyle様ありがとうございます。
お返事遅くなり申し訳ございません。
マクロは、ほとんど知識がなく初歩的なものしか使ったことがありません。
ただ興味があり、うまくできればマクロでやってみたいので初心者でもわかるように教えていただけないでしょうか？
よろしくお願いいたします。m(__)m

回答No.1

tom04

こんばんは！
本来であれば数量も考慮に入れなくてはならないと思いますが、
今回は数量は無視してもいいと言うことで・・・

↓の画像のように勝手に表を作らせてもらいましが。
Sheet1をA～C列ではなく、すべて1列にしての回答になりますが

Sheet1のC列を作業列とさせてもらって、
C2セル　=IF(ISERROR(INDEX(Sheet2!$A$2:$A$4,MATCH(A2,Sheet2!$A$2:$A$4,0))),ROW()-1,"")
としてオートフィルで下へコピーしています。

この作業列を元に
Sheet2のA15セルに
=IF(COUNT(Sheet1!$C$2:$C$10)>=COLUMN(),INDEX(Sheet1!$A$2:$A$10,SMALL(Sheet1!$C$2:$C$10,COLUMN())),"")
として、右にオートフィルでコピーしています。

なんか無理矢理って感じの表と関数ですが
参考になれば幸いです。

尚、的外れの回答ならごめんなさいね。m(__)m

補足

tom04様ありがとうございます。
とても勉強になります＾＾；
sheet2のA15（最新在庫ID）なんですが、列幅の都合上どうしても
1セル内に収めたいのです。すべてのIDが見れるようにセルの統合などしようと思っています。そして各IDを区別するためにカンマ区切りや、スペースなどを入れればいいなぁと思っています。
例
687,567,487..
687 567 487..
何か良いアイデアがありましたらご教授お願いいたします。m(__)m