購入回数1がない会員の不要データを削除する方法は？

前へ 次へ
このQ&Aのポイント
  • 購入回数1がない会員のデータをピックアップし、削除する方法について教えてください。
  • 同じ会員番号で購入回数が2以上の行（会員）のみを抽出し、不要なデータを削除したいです。
  • 不要なデータを削除するには、xls上での処理またはインポート後の処理のどちらでも構いません。
回答を見る
  • ベストアンサー

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

注文日 会員番号 　　商品名 　　　　価格 　　　注文番号 　　　購入回数 2014/3/2 123345678 　　 お好み焼き　　　　　550 　201401123 　　1 上記行毎の注文データがxlsで1万件（行）あります。 （「購入回数」は日別で注文された累積件数をあらわします。1＝初回　2＝2回目　最大5迄ふられている） ＜問題＞ 同じ会員番号のデータで、購入回数[１]がないにもかかわらず、購入回数2以降～から存在する不要な データが混ざっています。 この購入回数１がないのに、同じ会員番号で購入回数が２～５いずれかをふられている行（会員）だけを ピックアップする方法はないでしょうか。xls上でもアクセスにインポートしてからの 処理でもどちらでもかまわないのですが。 いわゆるこの不要データを削除（クリーニング）したいのです。 お知恵をいただけるとありがたいですｍｍ

質問者が選んだベストアンサー

  • ベストアンサー
  • bunjii
  • ベストアンサー率43% (3589/8248)
回答No.6

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

mcdone
質問者

お礼

kkkkkmさんのもいいのですが、こちらはより集計中に容量オーバーでエラーになるリスクが少ないです。 こちらも試しています。ありがとうございます！

その他の回答 (5)

  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.5

高速優先手順： リストを購入回数の昇順で並べ替える リストを会員番号の昇順で並べ替える 会員番号をＢ列、購入回数をＦ列として =IF(VLOOKUP(B2,B:F,5)=1,"","削除対象") で削除対象を削除する 必要に応じて操作前に昇順の連番を振っておき、それで並べ替えてリストを復元する。

mcdone
質問者

補足

↑↑↑ だめです。＞＜ この数式では全てが削除対象で表示されます！

  • kkkkkm
  • ベストアンサー率65% (1618/2457)
回答No.4

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

mcdone
質問者

お礼

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

  • kkkkkm
  • ベストアンサー率65% (1618/2457)
回答No.3

すみません　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

mcdone
質問者

お礼

kkkkkmさん 素晴らしきフォローと思いやりに感嘆。 ありがとうございます。 今回、kkkkkmさんのアイデアでいままわしています。。

  • kkkkkm
  • ベストアンサー率65% (1618/2457)
回答No.2

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のある会員になります。

mcdone
質問者

お礼

ありがとうございます。こちらの方は断然早く実用出来です。 また先のですと、会員が重複して注文しているケース（同日2行あるケース）において、 後ろの行は1回目があるのに削除対象で出てしまします。 こちらで再度試してみます！

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.1

こんばんは！ 1行目は項目行でデータは2行目以降にあるとします。 A～F列までデータがあるとすると Excel2007以降の場合は G2セルに =IF(COUNTIF(B$2:B2,B2)<>F2,B2,"") という数式を入れフィルハンドルでダブルクリックしてみてください。 これで回数が合わない行にその会員番号が表示されますので、 G列でオートフィルタを掛け 「テキストフィルタ」　→　「指定の値に等しくない」　→　「結果」の欄には何も入力せずOK これで空白セル以外が表示されます。 ＞いわゆるこの不要データを削除（クリーニング）したいのです。 というコトですので、表示されているデータを行ごとすべて範囲指定　→　右クリック　→　「行の削除」　→　オートフィルタ解除　→　G列の削除 これで「購入回数」が一致しないデータはすべて消去されます。m(_ _)m

mcdone
質問者

お礼

tom04様 ありがとうございます！ が、18万行で試となかなかに重いですね。 軽くする方法あるのでしょうか。Ａｃｃｅｓｓ等になるのでしょうか。 すばらしいのですが、なにせ重い。。

mcdone
質問者

補足

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

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. Excel（エクセル）

関連するQ&A

  • ●最終系：ACCESSクエリーの件！懇願ｍｍ

    ACCESSクエリーで教えていただきたいことございます！ 以下に関連しております。大変恐縮です。 ↓ http://okwave.jp/qa/q8756016.html ●したいこと● 累積回数、LTVを導きたいです（自動算出）。 ●何から？● 以下データ群（テーブル名:table1）があります（会員番号はユニークをあらわします）。 注文日　　 会員番号　　商品名　　価格 2014/04/11 123456 パンツ　　1000 ←一番古い注文日の行（4/11開始）が先頭 2014/04/20 123456 靴下　　　3000 2014/05/23 456789 半袖　　　2000 2014/05/23 456789 靴下　　　3000 2014/05/29 456789 パンツ　　1000 2014/05/30 987654 靴下　　　3000 2014/06/09 987654 寝巻き　　5000 2014/06/15 987654 下駄　　　4000 ●SQLクエリーで導きだしたいこと● 上記データ群を、SQL（クエリー）を使用して、 以下のように表現できないでしょうか。 注文日　　 会員番号　 購入回数　 LTV　　※商品名も表示できればそのままつけたい 2014/04/11 123456 　　　　 1　　　　 0 2014/04/20 123456 　 2 9 2014/05/23 456789 　　　　1　　　 　0 2014/05/23 456789 　 1 0 2014/05/29 456789 　 2 6 2014/05/30 987654 　　　　 1 0 2014/06/09 987654 　 2 10 2014/06/15 987654 　 　 3 16 ※同じ注文日、同じ会員番号であれば、商品を複数購入しようが（複数行であっても）それは同日扱いにより購入回数は同じと数とする ※LTV：その回の注文日 - 最初(1回目)の注文日（最初の購入から見て何日経過か） ※ユニーク（会員番号）の方が、一度にいくつ購入したかは問わず、 何日おき（すべて初回を起点にした経過日とする）に来店したか、頻度と合わせて表現したい ※積み上がる累積金額の表示はなくてかまわないので、上記（購入回数、LTV）２つを表示できないでしょうか。 お知恵をいただければ幸いですｍｍ LTV算出は以下のイメージが近いです。 ↓↓↓↓↓↓↓↓↓↓↓ http://okwave.jp/qa/q8756016.html なお、LTV を求める記述の Max(Q2.注文日) を Min(Q2.注文日) とすると 表示結果は、 会員番号　 購入回数　 LTV 123456　 1　 0 123456　 2　 31 123456　 3　 61 456789　 1　 0 456789　 2　 31

  • 再 ACCESSのソートについて（必要なものだけ

    テーブルにあるデータを以下のようにクエリーでソートしたく。 会員番号をユニークIDとする ○テーブルにあるデータ 注文日　　　　　　会員番号　　購入額　　　購入回数 2014/03/05　　　12345678　　　3675　　　　　10 2014/03/05　　　12345678　　　3675　　　　　　9 2014/03/05　　　98765241　　　3675　　　　　　3 上記について会員番号が同一であれば、購入回数が 最大のものだけを行表示させる（以下）ことは 可能でしょうか。 注文日　　　　　　会員番号　　　　購入額　　　購入回数 2014/03/05　　 12345678 3675　　　　10 2014/03/05　　 98765241 3675　　　　 3 会員番号が重複している行は購入回数が最大の数の行だけ残したい。 （重複ありなし混在で1万ほどレコードがある） お手数をおかけします！

  • ACCESSを活用して累計（経過）を表現したい

    ACCESSテーブルに以下情報が格納されています。 注文日　　　　会員番号　　商品名　　価格 2014/03/23　 123456 サンプル　　500円 2014/04/23 　123456 サンプル　　500円 2014/05/23 　123456 サンプル　　500円 2014/03/21 　456789 容器　　　 1000円 2014/04/21 　456789 容器　　　 1000円 上記をクエリーを利用して、以下の抽出をする方法は ないでしょうか。 ↓ (1) 会員番号　購入回数　価格　累積価格 123456　　　　　1 500 500 123456 　　 2 500 1000 123456 　　3 500 1500 456789 　　1 1000 1000 456789 　　2 1000 2000 (2) 会員番号　購入回数　LTV 123456　　　　　 1 0 123456 　　 2 30 123456 　　 3 60 456789 　　 1 30 456789 　　2 60 ※LTV：その回の注文日 - 前の注文日 ※必要であれば先頭にAUTOナンバーをつけることも可能 ※ACCESS２０１０を利用中 お手数ですが御教授いただけるとたすかります。。

  • ACCESSデータは５０００以上ある（抽出の件）

    データは５０００件以上あるのですが、以下で対応は難しいのではないでしょうか。 ソートでできますでしょうか？↓ http://okwave.jp/qa/q8755953.html ●質問した内容● ACCESSを活用して累計（経過）を表現したい 質問者：jordan232011 投稿日時：2014/09/15 21:38 困ってます ACCESSテーブルに以下情報が格納されています。 注文日　　　　会員番号　　商品名　　価格 2014/03/23　 123456 サンプル　　500円 2014/04/23 　123456 サンプル　　500円 2014/05/23 　123456 サンプル　　500円 2014/03/21 　456789 容器　　　 1000円 2014/04/21 　456789 容器　　　 1000円 上記をクエリーを利用して、以下の抽出をする方法は ないでしょうか。 ↓ (1) 会員番号　購入回数　価格　累積価格 123456　　　　　1 500 500 123456 　　 2 500 1000 123456 　　3 500 1500 456789 　　1 1000 1000 456789 　　2 1000 2000 (2) 会員番号　購入回数　LTV 123456　　　　　 1 0 123456 　　 2 30 123456 　　 3 60 456789 　　 1 30 456789 　　2 60 ※LTV：その回の注文日 - 前の注文日 ※必要であれば先頭にAUTOナンバーをつけることも可能 ※ACCESS２０１０を利用中 お手数ですが御教授いただけるとたすかります。。

  • ACCESS　SQLクエリーの活用について

    以下データ群（テーブル名:table1）があります（会員番号はユニークをあらわします）。 注文日　　 会員番号　　商品名　　価格 2014/04/11 123456 パンツ　　1000 ←一番古い注文日の行（4/11開始）が先頭 2014/04/20 123456 靴下　　　3000 2014/05/23 456789 半袖　　　2000 2014/05/23 456789 靴下　　　3000 2014/05/29 456789 パンツ　　1000 2014/05/30 987654 靴下　　　3000 2014/06/09 987654 寝巻き　　5000 2014/06/15 987654 下駄　　　4000 そして、 SELECT Q4.注文日, Q4.会員番号, Q4.商品名, Q3.購入回数, Q3.LTV FROM (SELECT Q1.注文日, Q1.会員番号, Count(Q2.会員番号)+1 AS 購入回数, DateDiff('d', Nz(Min(Q2.注文日),Q1.注文日), Q1.注文日) AS LTV FROM (SELECT 会員番号, 注文日 FROM ★★ GROUP BY 会員番号, 注文日) AS Q1 LEFT JOIN (SELECT 会員番号, 注文日 FROM ★★ GROUP BY 会員番号, 注文日) AS Q2 ON Q1.会員番号=Q2.会員番号 AND Q1.注文日>Q2.注文日 GROUP BY Q1.会員番号, Q1.注文日) AS Q3 LEFT JOIN ★★ AS Q4 ON Q3.会員番号=Q4.会員番号 AND Q3.注文日=Q4.注文日 ORDER BY Q4.会員番号, Q4.注文日 ; 達人からアドバイスいただいていた 上記SQLクエリーからの表示は 注文日　会員番号　商品名　購入回数　LTV 2014/04/11　123456　パンツ　1　0 2014/04/20　123456　靴下　2　9 2014/05/23　456789　半袖　1　0 2014/05/23　456789　靴下　1　0 2014/05/29　456789　パンツ　2　6 2014/05/30　987654　靴下　1　0 2014/06/09　987654　寝巻き　2　10 2014/06/15　987654　下駄　3　16 と出ます（GOOD!!!）。 上記に付随して、 このクエリーが参照しているテーブルの他の列（例：氏名、都道府県）もある（集計対象ではない）場合でそれも追加で（列名：氏名、都道府県を）表現するためには、 SELECT Q4.注文日, Q4.会員番号, Q4.商品名, Q3.購入回数, Q3.LTV の構文箇所に２つの列名も手打ちで追加する必要がありますでしょうか。 （テーブル列名すべてを表示する・・といった個別に列名を指定しないで表示する方法はあるのでしょうか＜列名が多いときに手間が若干かかる）

  • ACCESSかエクセルで抽出したいｍｍ（再掲載）

    以下出荷データ(xls.)があるのですが、同じ「会員番号」で見て、購入回数別で 「商品名・区分の両方ないしはいずれか」が1回目と変わっているデータ（会員番号）は 1回目含めて除外、ないしは目印をつけたいの ですが、ＥＸＣＥＬ、ないしはＡＣＣＥＳＳで行う方法ないでしょうか。 ※浮気をしないで一貫しておなじ「商品名・区分」を貫いているお客（恋人）だけを 引っ張り出したいｍｍ 購入回数１しかない顧客は浮気対象ではない（除外しない） 以下は出荷履歴の一部を切り取ったものです（これを活用したい） ＥＸＣＥＬでできてしまうのか？ＡＣＣＥＳＳ　ＳＱＬがいいのか？ 注文日　　　　　　　会員番号　　　　商品名 　　　　 区分 　　　　購入回数 2014/03/01　　　　　12345678　　　　セレクト　　　　通常購入　　　　1 2014/04/01　　　　　98765432　　　　通常商品　　　　定期購入　　　　1 2014/04/10　 　　　 33333333 　　 通常商品 通常購入 1 2014/05/20　 　　　 77777777 　　 セレクト 通常購入 1 2014/06/20　 　　　 77777777 　　 セレクト　　　　通常購入　　　　2 2014/07/20　　　　　77777777　　　　セレクト　　　　通常購入　　　　3 2014/08/20　　　　　77777777　　　　シリーズ　　　　通常購入　　　　4 2014/09/20　　　　　77777777　　　　セレクト　　　　通常購入　　　　5 方法悩んでおりますｍｍｍｍ 商品名・区分はそれぞれ商品名が上記3種類、区分は2種類しかないので、 これを数字に変換してもかまわない（整理ができればいいので）。 さすがに難しいですよねｍｍｍｍ

  • エクセルで顧客の継続率

    顧客の継続率を調べていますが、 それぞれの顧客が、初回購入を含め、きっちり4週間ごとに購入している回数をエクセルの関数でだすには？ データはこんな感じ 顧客ID　初回注文日　購入日　　金額 1111　　2009/1/9　　2009/1/9　1000 2222　　2009/2/3　　2009/2/3　300 3333　　2009/3/2　　2009/3/2　400 1111　　2009/1/9　　2009/2/6　1000 4444　　2009/4/10　2009/5/15　200

  • エクセルVBAでのコード番号抽出について

    エクセルの売上一覧から会員番号ごとにデータを抽出して、合計金額を算出し、新しくデータを作りたいのですが、コード番号は顧客の購入分存在しますので、コード番号は人によって ５個あったり２０個あったりします 当初、forの中にIF分を記載するなど考えましたが、どうしても同じ会員番号のデータだけを取り出すということが出来ていません。 同じ会員番号のデータだけを抽出し、他のシートへそれらの合計件数、合計金額を書き出したら 抽出したデータは削除し、また次の重複データを抽出し、合計件数、合計金額を書き出し・・・ ということが実行できるコードがありましたらお教えください。

  • AccessからExcelへ保存

    はじめまして 質問させていただきます。 【処理内容】 １．Accessにて作成したテーブルデータをTEMP.xlsとして出力 ２．TEMP.xlsのデータを出力先.xlsへコピーする という処理を作っているのですが、具体的に ２－１．TEMP.xlsを1行コピー ２－２．ヘッダ用.xlsの最終行に貼り付け ２－３．TEMP.xlsの最終行まで繰り返す ２－４．ヘッダ用.xlsの名前を「出力先.xls」として保存する ２－５．ブックを閉じる と行っています。 それぞれに入るデータは TEMP.xls・・・Accessから作成されたエクセルファイル ヘッダ用.xls・・・「出力先.xls」のヘッダー 出力用.xls・・・TEMP.xlsのデータ＋ヘッダ用.xlsのデータ というつくりです。 このとき、TEMP.xlsの最終セル位置を取得させているのですが、 なぜか65536行と、最大値を取得してしまいます。 （本来のデータは130件程度） データ件数と取得する行数を一致させたいのですが、どうすればいいでしょうか？ 説明下手なうえ、開発環境が手元にない常態での質問で申し訳ないんですが、回答お願いいたします

  • （変数）を使った特定の範囲の選択について

    下記のように、行を変数として、グラフの元になるデータの範囲(A列とC列)を指定した行までとしたいのですが、なかなかうまくいきません・・・("A1:セル番号,C1:セル番号1")の行ところでエラーになってしまいます。 どなたかよい方法を教えていただけませんでしょうか？お願いします。 Dim 行 As Integer Dim セル番号 As Variant Dim セル番号1 As Variant 行 = 7 セル番号 = "A" & 行 セル番号1 = "C" & 行 ActiveSheet.ChartObjects("グラフ11").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:セル番号,C1:セル番号1"), _ PlotBy:=xlColumns ActiveWindow.Visible = False Windows("マクロ練習売上と見み.xls").Activate Range("A1").Select

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する