Excelリストと一致する部分を削除してコピーする方法
- エクセル2010の利用方法について質問させてください。
- sheet1 A列のデータをsheet1 B列にコピーする際に、sheet2 A列に作成したリストを参照し、一致する部分を取り除いて表示させたいです。
- データとリストが膨大で、自動化する方法を知りたいです。
- ベストアンサー
Excel リストと一致する部分を削除してコピー
エクセル2010の利用方法について質問させてください。 sheet1 A列のデータをsheet1 B列にコピーします。 その際に、sheet2 A列に作成したリストを参照して sheet1 A列のデータとsheet2 A列に一致する文字列があった場合、 sheet1 A列のデータから一致する部分を取り除いてsheet1 B列に表示させたいのです。 例) ■sheet1 A列 1 腐ったミカン 2 りんご畑 3 大きな梨が食べたいな : ■sheet2 A列 1 が食べたいな 2 腐った 3 大きな 4 畑 : ■sheet1 B列 1 ミカン 2 りんご 3 梨 : データとリストが膨大(今後も随時追加予定)で、自動化することができればと考えております。 どうぞよろしくお願いいたします。
- japannetginko
- お礼率66% (2/3)
- Excel(エクセル)
- 回答数3
- ありがとう数5
- みんなの回答 (3)
- 専門家の回答
質問者が選んだベストアンサー
回答No.2です。 先程のVBAよりもこちらの方が処理速度が速いかも知れません。 Sub QNo8955469_Excel_リストと一致する部分を削除してコピー改() Dim DelStrSheet, DelStrFCell, OriDataSheet, OriDataFCell, PasteColumn, TempStr, myinfo As String Dim r, DelStrRange, OriDataRange As Range Dim DelStrRB, OriDataRB, OffsetC, myMsg As Long OriDataSheet = "Sheet14" '元データが入力されているシート OriDataFCell = "A1" '元データが入力されているセルの中で一番上にあるセル PasteColumn = "B" '元データから削除対象となる文字列を取り除いたデータを貼り付ける列 DelStrSheet = "Sheet14 (2)" '削除対象となる文字列のリストのシート DelStrFCell = "A1" '削除対象となる文字列が入力されているセルの中で一番上にあるセル '元データ列と貼り付け先の列番号の差 OffsetC = Columns(PasteColumn).Column - Range(OriDataFCell).Column myinfo = "" If IsError(Evaluate("ROW('" & OriDataSheet & "'!A1)")) Then _ myinfo = "元データが入力されているシート" & Chr(13) & Chr(13) & OriDataSheet If IsError(Evaluate("ROW('" & DelStrSheet & "'!A1)")) Then If myinfo <> "" Then myinfo = myinfo & Chr(13) & "及び" & Chr(13) & Chr(13) myinfo = myinfo & "元データから削除する文字列のリストが入力されているシート" _ & Chr(13) & Chr(13) & DelStrSheet End If If myinfo <> "" Then MsgBox myinfo & Chr(13) & Chr(13) & "が見つかりません。" & Chr(13) & _ "マクロの実行を中止します。", vbExclamation, "存在しないシート" Exit Sub End If With Sheets(OriDataSheet).Range(OriDataFCell) '元データが入力されている一番下の行 OriDataRB = Sheets(OriDataSheet).Cells(Rows.Count, .Column).End(xlUp).Row If OriDataRB > .Row Then GoTo label1 If OriDataRB <> .Row Or .Value <> "" Then GoTo label1 MsgBox "処理すべき元データが見つかりません。" & Chr(13) & _ "マクロの実行を中止します。", vbInformation, "データ無し" Exit Sub label1: Set OriDataRange = .Resize(OriDataRB - .Row + 1, 1) End With With Sheets(DelStrSheet).Range(DelStrFCell) '削除対象となる文字列が入力されている一番下の行 DelStrRB = Sheets(DelStrSheet).Cells(Rows.Count, .Column).End(xlUp).Row If DelStrRB > .Row Then GoTo label2 If DelStrRB <> .Row Or .Value <> "" Then GoTo label2 MsgBox "元データから削除する文字列のリストが見つかりません。" & Chr(13) & _ "マクロの実行を中止します。", vbInformation, "データ無し" Exit Sub label2: Set DelStrRange = .Resize(DelStrRB - .Row + 1, 1) End With myMsg = MsgBox("このマクロを実行しますと" & OriDataSheet & "の" & PasteColumn _ & "列のデータが上書きされます。" & Chr(13) & "マクロを実行しますか?" & Chr(13) _ & Chr(13) & "[OK]:マクロを実行します" & Chr(13) & "[キャンセル]:マクロを終了します" _ , vbOKCancel + vbQuestion, "確認") If myMsg = vbCancel Then Exit Sub Sheets(OriDataSheet).Range(PasteColumn & Range(OriDataFCell).Row & ":" & _ PasteColumn & Range(PasteColumn & Rows.Count).End(xlUp).Row).ClearContents OriDataRange.Offset(0, OffsetC).Value = OriDataRange.Value For Each r In DelStrRange OriDataRange.Offset(0, OffsetC).Replace What:=r.Value, Replacement:="", LookAt:=xlPart Next r End Sub
その他の回答 (2)
- kagakusuki
- ベストアンサー率51% (2610/5101)
sheet2 A列に入力されている取り除かねばならない文字列が64個まででしたら関数を使って除去する事も出来なくはないのですが、 >データとリストが膨大(今後も随時追加予定) という事情がお有りでは、その内、取り除かねばならない文字列が64個以上になって、関数だけでは対応出来なくなるという恐れがあると考えた方が良いかも知れません。 又、Sheet1やSheet2とは別のシート上に作業列を何列も設けて、関数だけでは処理できない中間的な処理を、別の列において行わせておく事で、文字列が64個以上になった場合に対応するという方法も無くは無いのですが、取り除かねばならない文字列が100個、200個と増えて行くのに従って、作業列も2列3列と増やしていかねばならず、作業列のセルに格納しなければならないデータの分だけ、データの総量が2倍3倍と増えてしまう事になりますので、「膨大(今後も随時追加予定)」と仰る度データが多いのでしたら、あまり好ましい方法とは申せません。 その様な訳で、VBAを用いて処理を行わせた方が宜しいのではないかと思います。 Sub QNo8955469_Excel_リストと一致する部分を削除してコピー() Dim DelStrSheet, DelStrFCell, OriDataSheet, OriDataFCell, PasteColumn, TempStr, myinfo As String Dim r1, r2, DelStrRange, OriDataRange As Range Dim DelStrRB, OriDataRB, OffsetC, myMsg As Long OriDataSheet = "Sheet14" '元データが入力されているシート OriDataFCell = "A1" '元データが入力されているセルの中で一番上にあるセル PasteColumn = "B" '元データから削除対象となる文字列を取り除いたデータを貼り付ける列 DelStrSheet = "Sheet14 (2)" '削除対象となる文字列のリストのシート DelStrFCell = "A1" '削除対象となる文字列が入力されているセルの中で一番上にあるセル '元データ列と貼り付け先の列番号の差 OffsetC = Columns(PasteColumn).Column - Range(OriDataFCell).Column myinfo = "" If IsError(Evaluate("ROW('" & OriDataSheet & "'!A1)")) Then _ myinfo = "元データが入力されているシート" & Chr(13) & Chr(13) & OriDataSheet If IsError(Evaluate("ROW('" & DelStrSheet & "'!A1)")) Then If myinfo <> "" Then myinfo = myinfo & Chr(13) & "及び" & Chr(13) & Chr(13) myinfo = myinfo & "元データから削除する文字列のリストが入力されているシート" _ & Chr(13) & Chr(13) & DelStrSheet End If If myinfo <> "" Then MsgBox myinfo & Chr(13) & Chr(13) & "が見つかりません。" & Chr(13) & _ "マクロの実行を中止します。", vbExclamation, "存在しないシート" Exit Sub End If With Sheets(OriDataSheet).Range(OriDataFCell) '元データが入力されている一番下の行 OriDataRB = Sheets(OriDataSheet).Cells(Rows.Count, .Column).End(xlUp).Row If OriDataRB > .Row Then GoTo label1 If OriDataRB <> .Row Or .Value <> "" Then GoTo label1 MsgBox "処理すべき元データが見つかりません。" & Chr(13) & _ "マクロの実行を中止します。", vbInformation, "データ無し" Exit Sub label1: Set OriDataRange = .Resize(OriDataRB - .Row + 1, 1) End With With Sheets(DelStrSheet).Range(DelStrFCell) '削除対象となる文字列が入力されている一番下の行 DelStrRB = Sheets(DelStrSheet).Cells(Rows.Count, .Column).End(xlUp).Row If DelStrRB > .Row Then GoTo label2 If DelStrRB <> .Row Or .Value <> "" Then GoTo label2 MsgBox "元データから削除する文字列のリストが見つかりません。" & Chr(13) & _ "マクロの実行を中止します。", vbInformation, "データ無し" Exit Sub label2: Set DelStrRange = .Resize(DelStrRB - .Row + 1, 1) End With myMsg = MsgBox("このマクロを実行しますと" & OriDataSheet & "の" & PasteColumn _ & "列のデータが上書きされます。" & Chr(13) & "マクロを実行しますか?" & Chr(13) _ & Chr(13) & "[OK]:マクロを実行します" & Chr(13) & "[キャンセル]:マクロを終了します" _ , vbOKCancel + vbQuestion, "確認") If myMsg = vbCancel Then Exit Sub Sheets(OriDataSheet).Range(PasteColumn & Range(OriDataFCell).Row & ":" & _ PasteColumn & Range(PasteColumn & Rows.Count).End(xlUp).Row).ClearContents For Each r1 In OriDataRange TempStr = r1.Value For Each r2 In DelStrRange TempStr = Replace(TempStr, r2.Value, "") Next r2 r1.Offset(0, OffsetC).Value = TempStr Next r1 End Sub
- bunjii
- ベストアンサー率43% (3589/8248)
>データとリストが膨大(今後も随時追加予定)で、自動化することができればと考えております。 関数で処理するには配列演算を使いますので膨大なデータの場合はリソース不足(メモリ不足)で動作しないかも知れません。 提示の模擬データ範囲でExcel 2013で検証しました。 Excel 2007以降のバージョンで再現できるはずです。 データの最大は10行までとしてあります。 Sheet1!A列の文字列にはSheet2!A列の文字列が最大2つまで含むものとしました。 Sheet1!B1へ次の数式を設定して下へ10行目までコピーしたものが貼付画像です。 =SUBSTITUTE(SUBSTITUTE(A1,INDEX(Sheet2!$A:$A,MAX((IFERROR((FIND(Sheet2!$A$1:$A$10,A1)>0)*(Sheet2!$A$1:$A$10<>""),0))*ROW(Sheet2!$A$1:$A$10)),1)&"",""),INDEX(Sheet2!$A:$A,MAX((IFERROR((FIND(Sheet2!$A$1:$A$10,SUBSTITUTE(A1,INDEX(Sheet2!$A:$A,MAX((IFERROR((FIND(Sheet2!$A$1:$A$10,A1)>0)*(Sheet2!$A$1:$A$10<>""),0))*ROW(Sheet2!$A$1:$A$10)),1)&"",""))>0)*(Sheet2!$A$1:$A$10<>""),0))*ROW(Sheet2!$A$1:$A$10)),1)&"","") この数式は計算過程で配列値を扱いますので、数式入力時にCtrl+Shift+Enterで確定します。
お礼
ありがとうございます。 とても素早くご回答いただけたので驚きました! SUBSTITUTEの関数は列を指定できるのですね。 単語を入れ子、入れ子、入れ子して途方にくれていました、、、。(恥) 勉強させていただきました!
関連するQ&A
- 【エクセル】リストと一致するデータを抽出し、文字に色をつけることは可能でしょうか?
エクセルで作成したファイルの【シート1:表】に様々なデータを打ち込んでおり、【シート2:リスト】にはリスト一覧を入れています。 シート1の表で、リストと該当する文字列だけに色をつけることは可能でしょうか。表はこのような感じです。 【表】 A列 B列 1 りんご apple 2 りんごを食べる eat an apple 3 みかん orange 4 みかんを買う get an orange 【リスト】 A列 B列 1 りんご apple 2 みかん orange このような表なのですが、例えば、1行目、3行目はリストと同じなので赤い文字で表示し、A2のセルの「りんごを食べる」の「りんご」とB2セルの「eat an apple」の「apple」、またA4セルの「みかんを買う」の「みかん」と「get an orange」の「orange」の文字列だけを赤字にするようなことは可能でしょうか。 少量のデータなら手作業で変更できるのですが、データが大量にあるため手作業では追いつかず困っています。どなたかご存知の方がいらっしゃいましたら、よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- エクセル AVERAGEを取るときに
平均値を取りたいのですが 範囲が少し複雑だったのでどのように式を作ったらいいのか わかりません。 シート1に A列 りんご みかん ばなな このようにデータがありそれぞれシート2にある りんご の個数の平均値をB列に求めたいのですが シート2には A列 B列 りんご 3 8 9 みかん 5 9 10 このように果物の種類の項目名が1番上にしかなく 個数はそれに対して必ず3行あるので VLOOKUPなどを使うとりんごと書かれている真横のセルしか 参照しないので常にその項目の横のセルから3行を参照したい のですが どうしたらよいでしょうか? 教えてください。よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- Excelでどのような関数を使えばいいでしょうか。
関数について教えて下さい。 Excelで、同じBook内に以下のような2シートがあるときに、 (Sheet1) A B C D 1 1 あい 12 りんご 2 うえ 34 みかん 3 おか 56 イチゴ 4 1 おか 78 イチゴ (Sheet2) A B C D 1 あい 11 りんご 2 うえ 34 みかん 3 おか 78 イチゴ 4 うえ 34 みかん Sheet2のA列にSheet1にあるデータを入れたいのです。 まず、列B,C,Dのそれぞれのデータが横に3つ一致するものを探して 横3つのデータが同じなら、Sheet2のA列に、 Sheet1のA列にあるデータをシートBにも持ってくる、 というようなイメージです。 (Sheet2) A B C D 1 あい 11 りんご 2 うえ 34 みかん 3 1 おか 78 イチゴ 4 うえ 34 みかん ↑このようにしたいのです。 A列にどのような関数を入れればいいのでしょう。 (A列にあるのは1か空欄かのどちらかです。 同じ3つの組み合わせで、1の場合と空欄の場合両方があることはありません。) また、Sheet2には、A列に1とついた行に色を付けたいのです。 例えば、上記の例ですと Sheet2のB3:D3のセルに色をつけたいのです。 関数や条件付書式などで、できますでしょうか。
- ベストアンサー
- オフィス系ソフト
- エクセルの関数について教えてください
例えば、A列に「りんご」「みかん」「ぶどう」「梨」「洋梨」とあり(空白のセルもあります)、このA列に「りんご」もしくは「梨を含む」場合に、B列に「○」を表示させたいのですが、関数でできますか? 「りんご」だけとか「梨を含む」というように単一の条件で指定する方法はわかるのですが…。 職場の管理表で使いたいのですが、どうしても解決できないので、お力を貸してください。 どうかよろしくお願いします。 ちなみに以下のような結果を求めています。 A B 1 みかん 2 りんご ○ 3 梨 ○ 4 ぶどう 5 洋梨 ○ 6 ぶどう 7 りんご ○
- ベストアンサー
- オフィス系ソフト
- 【Excel】部分一致の逆引きの方法について
初めまして。Excelの部分一致のデータ抽出について質問です。 A列 B列 C列 2 リンゴ リンゴが美味しい 1 3 バナナ おなかがすいた 0 4 パパイア ミカンの皮をむけ 1 5 ミカン バナナが好き 1 6 チェリー 草履を履く 0 7 イチゴ 遊びたい 0 上記のようにBの各行にA列のいずれかの単語が含まれていた場合にC列に「1」含まれない場合に「0」と記述する関数をご教授頂けると本当に助かります。 3時間調べたのですがどうしてもA2の単語がB列の中で何個含まれているかの関数しか見つけることができませんでした。何卒よろしくお願い致します。
- ベストアンサー
- オフィス系ソフト
- Excel 指定値だけ行コピー+値の移動
Excel 2010でSheet1に次のような表があります。 A列 B列 C列 D列 E列 りんご 111 222 333 444 みかん 555 666 いちご 777 888 999 ・ ・ ・ これをマクロを使って、次のような形にしたいと思います。 1.C列以降の「数値が入った列数分だけ」行コピーをする。(最大9列まで) (この例の場合りんごの行は3回、みかんの行は1回、いちごの行は2回) 2.次にC列以降の数値はコピーした行のB列に移動する。 マクロ実行後は次のようになります。 A列 B列 りんご 111 りんご 222 りんご 333 りんご 444 みかん 555 みかん 666 いちご 777 いちご 888 いちご 999 ・ ・ ・ 膨大な数のデータですので、マクロを使って処理したいと思っています。 どうぞよろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- エクセルでデータの検索
現在 sheet1に A列 B列 C列 あ い う え お と入力されています Sheet2には A列 B列 あ りんご い ばなな あ みかん う うめ い なし と入力されています。 sheet1のA列を検索条件として A列と一致するsheet2にあるものをその横の列に表示されるようにしたいです。 A列 B列 C列 あ りんご みかん い ばなな なし う うめ このような場合、一致するものは複数あるため VLOOKUPは使用できません。 どのようにすれば、このような検索ができるのでしょうか?
- ベストアンサー
- オフィス系ソフト
- エクセル関数 データの抽出について
エクセル2010 重複データの抽出。 重複データの抽出方法をご教示ください。 抽出先はシート1のA列A3~抽出データの分だけ リストはシート2のB2:AB32まで フィルターオプションなども使ってみたのですが どうも上手くいきません。 どなたか知恵をお貸しください。 方法はできれば関数だといいのですが(データが増えてもいいように) できなければ他の方法でもいいです。 VBAなどは全くの初心者なので出来れば避けたいのですが… シート2 A B C D~AB32 1 "" 2 いちご ばなな いちご りんご 3 みかん いちご ばなな いちご 4 りんご ばなな いちご みかん 5 みかん いちご ばなな りんご 6 ばなな ばなな りんご いちご : : 32 シート1(重複データなし) A 1 "" 2 "" 3 いちご 4 みかん 5 りんご 6 ばなな 7 : : 抽出リストのデータはシート2のB2:AB32にぎっしり入っています。 宜しくお願い致します。
- 締切済み
- オフィス系ソフト
- エクセルで新規顧客のリストを追加
前回、同じような質問して、分かったつもりだったのですが、できませんでしたので、もう一度質問させて頂きます。 シート1に、顧客(会社名)のリストがあります。 <シート1> (A列) A社 B社 C社 ・ ・ シート2に、その月に取引のあった顧客の名前(会社名、担当者が同じセル内にある)があります。 <シート2> (A列) (B列) B社 ○○さん 1000円 C社 △△さん 1000円 X社 □□さん 2000円 C社 ○△さん 1500円 G社 □△さん 1000円 B社 ◎○さん 500円 という具合です。 X社さん、G社さんは新しい顧客なので、シート1のリストに追加したいのです。 しかし、シート1のデータも膨大であり、シート2のデータも膨大なので、手作業では困難です。 そこで、シート1のリストにない顧客の名前だけ、シート2で目立つような(あるいはピックアップするような)方法がないでしょうか。 金額は関係ありません。次の列に入っていることを分かりやすくしただけです。
- ベストアンサー
- オフィス系ソフト
- EXCEL 複数列条件に一致する行を数えるには
添付画像のデータのB2:E17中で項目α~θが完全一致する都道府県の数をF列の「件数」に表示出来る関数はありますか? 添付画像だと 岩手と山梨:りんご・バナナ・みかんで一致→2 山形と千葉:りんご・みかん・みかんで一致→2 栃木と東京:バナナ・みかん・りんごで一致→2 それ以外は1という感じです。 宜しくお願いします。
- ベストアンサー
- その他MS Office製品
お礼
理想通りの処理が行えました。 処理前に確認までしていただけるなんて、、感動です! 丁寧に作成していただきありがとうございました。 私もkagakusuki様を目指して勉強したいと思います。