![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
![](http://oshiete.xgoo.jp/images/v2/common/profile/M/noimageicon_setting_09.png?e8efa67)
はじめまして。エクセル・アクセス関係を最近さわりはじめました。
下記について重複しているデータをチェックしたいのですが、いろんなサイトをみても「これ!」というのが見当たらず困っています。どのような式を作成すればよいのか、ご教授ください。
ちなみに今はMATCH関数を使いながら、データを並び替え・・とすごく面倒なことをしています。
顧客ID 請求番号 登録日
1111 0001 2010/01/01
1112 0001 2011/11/11
1113 0002 2010/02/01
1113 0003 2011/01/01
1114 0004 2011/01/01
1114 0005 null
上記の顧客データあるとします。
顧客IDや請求IDが重複しているものがあるため、これらを最終的には下記(<最終形態>)のようにしたいのですが、その前に横の列にチェック(<チェック>)をいれたいです。
<最終形態>
顧客ID 請求番号 登録日 チェック
1112 0001 2011/11/11 残し
1113 0003 2011/01/01 残し
1114 0004 2011/01/01 確認
1114 0005 null 確認
<チェック>
顧客ID 請求番号 登録日 チェック
1111 0001 2010/01/01 削除
1112 0001 2011/11/11 残し
1113 0002 2010/02/01 削除
1113 0003 2011/01/01 残し
1114 0004 2011/01/01 確認
1114 0005 null 確認
(1)請求番号が同じで顧客IDが異なり、登録日も違う場合は、登録日が新しいほうに「残し」、古いほうに「削除」を入れる
(2)顧客IDが同じで請求番号が異なり、登録日も違う場合は、登録日が新しいほうに「残し」、古いほうに「削除」を入れる
(3)顧客IDが同じで請求番号が異なる、あるいは請求番号が同じで顧客IDが異なり、登録日がnull(どちらか一方でも)の場合は、「確認」を入れる→登録日がともに同じ日付でも「確認」を入れる
※ちなみに、実際の重複するデータは上記のように連続して入っていません。
順序はばらばらです※
エクセルでもアクセスでもかまいませんが、やりやすいほうでご教授いただけると幸いです。
よろしくお願いします。
SQLはよくわかっていません。。
最初から教えていただけると幸いです。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
ANo4の続きです。
>この場合、顧客IDもしくは請求番号が重複していて、登録日が同じであった場合「確認」となるのでしょうか?
の対応ですが、遅くなってしまい、すいません。
(ちょっと悩ましかった点があったので。)
ANo.3の
>D2セルは、=if(isna(vlookup(A2,A$1:A1,1,false))=true,"残し","削除")
を
=if(isna(vlookup(A2,A$1:A1,1,false))=true,"残し",if(vlookup(A2,A$1:C1,3,false)=C2,"確認","削除"))
とすれば、"削除"となるもの(2件目以降)を"確認"にできます。
※
チェックしている行より上の行(すなわち登録日が同じか後の行)に同じ顧客IDがあったときは、
登録日の列の値を比較して同じなら"確認にする処理"。
あわせて、ANo.4の
>H2 =if(OR(D2="削除",E2="削除"),"削除",if(OR(F2="確認",G2="確認"),"確認","残し"))
は
H2 =if(OR(D2="削除",E2="削除"),"削除",if(OR(D2="確認",E2="確認",F2="確認",G2="確認"),"確認","残し"))
とします。
★この状態では、最初に出現した行が"残し"になっています。
I2 = if(isna(vlookup(A2,$A3:$H$65535,1,false))=true,"",if(vlookup(A2,$A3:$H$65535,8,false)="確認","確認",""))
J2 = if(isna(vlookup(B2,$B3:$H$65535,1,false))=true,"",if(vlookup(B2,$B3:$H$65535,7,false)="確認","確認",""))
を全行にコピー&ペーストします。
※$65535は一番下の行(Excel2003およびその互換モードでの最大行)。確認してその行の値でもOK。
対象行と同一の顧客IDもしくは請求番号の行が確認となっているかどうかを判定しています。
(上記の判定で、同一日のデータのみ"削除"ではなく"確認"になっているはず。)
K2 = if(OR(I2 ="確認",J2="確認"),"確認",H2)
としてコピー&ペーストすれば、K列は、
顧客IDもしくは請求番号が重複していて、登録日が同じであった場合「確認」となるはず。
(試してはいませんが。)
注:同一日の顧客IDがあるのだが、より新しい日付の請求番号があるときはどうなるのかな?
D2="確認",E2="削除"となるため、H2="削除"となっており、K2も"削除"とでてくるはず。
ま、試してみていただいて問題あるようでしたら、お礼なり補足なりに記載願います。
No.4
- 回答日時:
ANO.3です。
すいません。
H2 =if(OR(E2="削除",F2="削除"),"削除",if(OR(G2="確認",H2="確認"),"確認","残し"))
の式は、間違えていました。
H2 =if(OR(D2="削除",E2="削除"),"削除",if(OR(F2="確認",G2="確認"),"確認","残し"))
D2:顧客IDが同じで後の登録日がある場合"削除" さもなければ、 さもなければ、"残し"
E2:請求番号が同じで後の登録日がある場合"削除" さもなければ、"残し"
F2:顧客IDが同じで他にNullの登録日がある場合"確認" さもなければ、""
G2:請求番号が同じで他にNullの登録日がある場合"確認" さもなければ、""
と表示させようとしています。
H2は、
顧客IDが同じで後の登録日がある場合か請求番号が同じで後の登録日がある場合に"削除"
顧客IDが同じで他にNullの登録日がある場合か請求番号が同じで他にNullの登録日がある場合に"確認"
他は、"残し"と出そうとしています。
>この場合、顧客IDもしくは請求番号が重複していて、登録日が同じであった場合「確認」となるのでしょうか?
としたければ、
・・・ちょっと時間がないので後で。
ありがとうございます。
Nullだと最後「削除」となってしまったので、あれ?!と思っていたんです(^-^;
登録日同じ場合…またご連絡お待ちしております。
No.3
- 回答日時:
## SQL使えりゃ簡単ですが使えないのでEXCELでやってみる前提で。
まず、ロジックに問題があります。
(1)と(2)を以下の例に同時に適用しようと思うとどうなりますか?
顧客ID 請求番号 登録日
1111 0001 2010/01/01
1112 0001 2011/11/11
1112 0002 2011/12/11
顧客ID 請求番号 登録日 (1)のチェック (2)のチェック
1111 0001 2010/01/01 削除
1112 0001 2011/11/11 残し 削除
1112 0002 2011/12/11 残し
真ん中の行は削除か残しかどっち?
・・・この例では、削除だと目でみたら分かるのでしょうけど。
★登録日を基準に重複する顧客IDや請求番号があれば古い登録日の行に"削除"をいれる
と考えるべきでは?
もしそうならば、の作業方法例。
(1)登録日の逆順に並び替える
A B C
1 顧客ID 請求番号 登録日
2 1112 0002 2011/12/11
3 1112 0001 2011/11/11
4 1111 0001 2010/01/01
(2)登録日がNullのデータだけ別シートにコピーします。
(3)登録日のとなりのセルに、vlookup(matchでもいい)を使った式を入れる。
式は、D2セルは、=if(isna(vlookup(A2,A$1:A1,1,false))=true,"残し","削除")
D3セル以降はコピペすると
D3 =if(isna(vlookup(A3,A$1:A2,1,false))=true,"残し","削除")
・・・となります。
(直前までに同一顧客IDがあれば"削除"なければ"残し")
E列にも同様に請求番号について処理する式をいれます。
E2 =if(isna(vlookup(B2,B$1:B1,1,false))=true,"残し","削除")
次はNullチェックのための数式を入れていきます。
F列には、
F2 =if(isna(vlookup(A2,別シート!A:A,1,false))=true,"","確認")
G列には、
G2 =if(isna(vlookup(B2,別シート!B:B,1,false))=true,"","確認")
で、答えは、H列に
H2 =if(OR(E2="削除",F2="削除"),"削除",if(OR(G2="確認",H2="確認"),"確認","残し"))
A B C D
1 顧客ID 請求番号 登録日
2 1112 0002 2011/12/11
3 1112 0001 2011/11/11
4 1111 0001 2010/01/01
こんなところでどうでしょう。
ありがとうございます。確かにおっしゃるとおり!です。
登録日を第一優先で残していきます。
この場合、顧客IDもしくは請求番号が重複していて、登録日が同じであった場合「確認」となるのでしょうか?
No.2
- 回答日時:
かなり難しいご質問で下の方法で良いのかどうかわかりませんが作業列を作って対応するのがよいでしょう。
お示しの表がA列からC列の2行目から下方にあるとしてD2セルには次の式を入力して下方にドラッグコピーします。
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,MAX(D$1:D1)+1,INDEX(D$1:D1,MATCH(A2,A:A,0))))
E2セルには次の式を入力して下方にドラッグコピーします。
=IF(B2="","",IF(COUNTIF(B$2:B2,B2)=1,MAX(E$1:E1)+1,INDEX(E$1:E1,MATCH(B2,B:B,0))))
F2セルには次の式を入力して下方にドラッグコピーします。
=IF(AND(A2="",(ROW(A1)-COUNT(E:E))*100000<MAX(F$1:F1)+100000),(ROW(A1)-COUNT(E:E))*100000,IF(COUNTIF(E:E,E2)>1,E2*100000+IF(C2="null",0,C2),IF(COUNTIF(D:D,D2)>1,D2*100000+IF(C2="null",40000,C2),"")))
チェックの結果をG列に表示させるとしてG2セルには次の式を入力して下方にドラッグコピーします。
=IF(A2="","",IF(OR(MOD(F2,10000)=0,COUNTIF(F:F,ROUNDDOWN(F2,-4))>0),"確認",IF(RANK(F2,F:F,1)=RANK(ROUNDDOWN(F2,-5)+100000,F:F,1)-1,"残し","削除")))
最後に残しと確認の行だけを「フィルタ」機能を使って抽出してコピーし、別のシートに貼り付けをすればよいでしょう。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel 指定した固有番号で、複数の行を削除する方法は? 2 2022/03/30 15:18
- Yahoo!メール Yahooアカウントにログインできないです。 1 2023/05/26 09:01
- MySQL SQLで日付別のIDを生成するには 3 2022/10/09 10:34
- Access(アクセス) Access IF文でテーブルに存在しない場合の処理について 2 2022/10/10 18:09
- その他(データベース) accessについて 2 2022/05/31 16:58
- Yahoo!メール Yahooアカウントにログインできなくなって困っています。 2 2023/05/16 02:28
- 消費税 インボイスの事業者登録番号を仕入れ先から求められました? 3 2023/03/19 21:48
- Yahoo!メール ヤフーIDのログイン再設定で本人確認ができない 3 2023/05/29 08:37
- Oracle 質問です。 下記のテーブルとデータがあり、 取得想定結果のように出力したいです。 下記のsqlだと0 2 2023/05/23 19:10
- au(KDDI) auスマートパスプレミアム解約できなくて困ってます! 自分の管理不足でお恥ずかしいのですが、詳しい方 6 2023/02/27 13:37
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
データベースファイル(.db)を開...
-
Accessを開きなおすとテキスト...
-
エクセルのフィルタ抽出が固まる
-
警察はスマホに保存した動画や...
-
帳票フォームでのあるコンボボ...
-
CSVファイルでテキストの改行の...
-
最新の日付とその金額をクエリ...
-
構文エラー:演算子がありませ...
-
ACCESSのSQLで、NULLかNULLでな...
-
汎用カード型のデータベースソ...
-
Access SQL のMID関数について
-
AccessのSQLで、FROM句の構文エ...
-
QRコードとバーコードについて
-
Accessのリンクテーブルについて
-
Accessフォームで平均値の出し...
-
sql update で 抽出条件データ...
-
シートが異なるセル同士を、相...
-
エクセル 顧客管理表に担当ごと...
-
FROM句の構文エラー ACCESS SQL
-
FileMaker Pro 10 で令和を表示
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
データベースファイル(.db)を開...
-
ACCESSのSQLで、NULLかNULLでな...
-
Accessを開きなおすとテキスト...
-
警察はスマホに保存した動画や...
-
エクセルのフィルタ抽出が固まる
-
Accessのフォームへ、記録者の...
-
構文エラー:演算子がありませ...
-
最新の日付とその金額をクエリ...
-
Accessのリンクテーブルについて
-
CSVファイルでテキストの改行の...
-
更新クエリをリンクデータベー...
-
データベースの選定について 要...
-
ドリームメーカーってどうやっ...
-
シートが異なるセル同士を、相...
-
帳票フォームでのあるコンボボ...
-
20万行あるデータを動かしたい
-
Access フォーム上でコンボボッ...
-
googleフォームでインストール...
-
顧客データベースを作る場合、...
-
テキストボックスにコントロー...
おすすめ情報