歯抜けとなっている特定の会員だけを特定したい

質問

注文日 会員番号 　　商品名 　　　　価格 　　　注文番号 　　　購入回数
2014/3/2 123345678 　　 お好み焼き　　　　　550 　201401123 　　1

上記行毎の注文データがxlsで1万件（行）あります。

（「購入回数」は日別で注文された累積件数をあらわします。1＝初回　2＝2回目　最大5迄ふられている）

＜問題＞

同じ会員番号のデータで、購入回数[１]がないにもかかわらず、購入回数2以降～から存在する不要な
データが混ざっています。

この購入回数１がないのに、同じ会員番号で購入回数が２～５いずれかをふられている行（会員）だけを
ピックアップする方法はないでしょうか。xls上でもアクセスにインポートしてからの
処理でもどちらでもかまわないのですが。

いわゆるこの不要データを削除（クリーニング）したいのです。

お知恵をいただけるとありがたいですｍｍ

回答No.6

bunjii · ベストアンサー

作業用の列（G列）に次の数式をセットしてフィルターで0を選択したものを削除すれば良いでしょう。
G2=COUNTIFS(B:B,B2,F:F,1)
G2セルを下へ必要数コピーすれば準備完了です。
オートフィルでは多量のコピーは無理かと思います。
G2をコピーし、コピー先は現在の行番号（左上）へG3:G10000のようにタイプしてEnterキーの打鍵でコピー先を一発で指定できますので、その状態から貼り付けを行えば良いでしょう。
尚、COUNTIFS関数はExcel 2007以降のバージョンで使用できます。

お礼

kkkkkmさんのもいいのですが、こちらはより集計中に容量オーバーでエラーになるリスクが少ないです。

こちらも試しています。ありがとうございます！

回答No.5

keithin

高速優先手順：
リストを購入回数の昇順で並べ替える
リストを会員番号の昇順で並べ替える
会員番号をＢ列、購入回数をＦ列として
=IF(VLOOKUP(B2,B:F,5)=1,"","削除対象")
で削除対象を削除する

必要に応じて操作前に昇順の連番を振っておき、それで並べ替えてリストを復元する。

補足

↑↑↑
だめです。＞＜

この数式では全てが削除対象で表示されます！

回答No.4

kkkkkm

たびたびすみません。No2の自分へのお礼を見ないうちにNo3を投稿してしまいました。なんかNo3が嫌味っぽくなってしまい申し訳ありません。悪意はありませんのでご容赦ください。

お礼

Ａｃｃｅｓｓでやると早くなるということはあるのでしょうか。

回答No.3

kkkkkm

すみません　tom04さんの数式を試していましたら以下のような場合が出てきたのですが、2以降に歯抜けがあるとG列に1の存在する会員さんが出てきました。「同じ会員番号で購入回数が２～５いずれかをふられている」と書かれていて「いずれか」となっていたのがちょっと気になったので…決して横槍ではありませんのでtom04さんお気を悪くしないでください。

注文日　　　会員番号　　　商品名　　価格　　注文番号　　購入回数 　G列
2014/3/2　123345678　お好み焼き　550　201401123　1　　　　　
2014/3/3　123345678　お好み焼き　550　201401123　2
2014/3/4　123345678　お好み焼き　550　201401123　4　　　　　123345678
2014/3/5　123345678　お好み焼き　550　201401123　5　　　　　123345678

お礼

kkkkkmさん

素晴らしきフォローと思いやりに感嘆。
ありがとうございます。

今回、kkkkkmさんのアイデアでいままわしています。。

回答No.2

kkkkkm

1万行で速度的に現実的なものかどうか試していませんが
A列　　B列　　　　C列　　　D列　　　E列　　　　F列
注文日 会員番号　　商品名　　価格　　注文番号 　購入回数

となっていて2行目からデータがある場合
G2に
=B2&F2
と入力し
H２に
=IF(MATCH(B2&1,G:G,0)>0,,)
として
両方の列をデータのある最終行までコピーしてください。

H列で#N/Aと表示されるのが購入回数に1がない会員のデータ行になりますので。フィルター機能を利用して#N/Aの行を表示し、削除してください。H列に0と表示されるのは購入回数1のある会員になります。

お礼

ありがとうございます。こちらの方は断然早く実用出来です。

また先のですと、会員が重複して注文しているケース（同日2行あるケース）において、
後ろの行は1回目があるのに削除対象で出てしまします。

こちらで再度試してみます！

回答No.1

tom04

こんばんは！

1行目は項目行でデータは2行目以降にあるとします。
A～F列までデータがあるとすると
Excel2007以降の場合は

G2セルに
=IF(COUNTIF(B$2:B2,B2)<>F2,B2,"")
という数式を入れフィルハンドルでダブルクリックしてみてください。

これで回数が合わない行にその会員番号が表示されますので、
G列でオートフィルタを掛け
「テキストフィルタ」　→　「指定の値に等しくない」　→　「結果」の欄には何も入力せずOK
これで空白セル以外が表示されます。

＞いわゆるこの不要データを削除（クリーニング）したいのです。
というコトですので、表示されているデータを行ごとすべて範囲指定　→　右クリック　→　「行の削除」　→　オートフィルタ解除　→　G列の削除
これで「購入回数」が一致しないデータはすべて消去されます。m(_ _)m

お礼

tom04様

ありがとうございます！
が、18万行で試となかなかに重いですね。

軽くする方法あるのでしょうか。Ａｃｃｅｓｓ等になるのでしょうか。
すばらしいのですが、なにせ重い。。

補足

おもいのもそうなのですが、

会員が重複して注文しているケース（同日2行あるケース）において、
重複の後ろの行が1回目があるのに削除対象で出てしまします。

これでは厳しいですｍｍ