- 締切済み
エクセル関数 データの抽出について
エクセル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にぎっしり入っています。 宜しくお願い致します。
- みんなの回答 (8)
- 専門家の回答
みんなの回答
- MarcoRossiItaly
- ベストアンサー率40% (454/1128)
いったん全データを縦 1 列に並べ替えてしまえば、後の処理が何かとラクです。 例えば AB 列のどこかに「=column()」と入力してみると分かるとおり、AB 列は左端から 28 番目の列です。また行数は、32 - 1 = 31 行ですね。さらに、どこかのセルに「=28*31」と入力してみると分かるとおり、全部で 868 個のデータがあります。 AD1 =index($A$2:$AB$32,int((row(ad1)-1)/28)+1,mod(row(ad1)-1,28)+1) AD1 をコピーし、そのままの状態でジャンプのダイアログ(F5 キー)にて「参照先」ボックスに「ad1:ad868」を入力して OK。選択された範囲に、コピーしていた AD1 を貼り付け。 これで並べ替えができました。それを別シートに値複写。 あとは「データ>データツール>重複の削除」を使うか、またはフィルタオプションで重複を削除してください。どちらの機能も、複数の列があるときは 1 列ずつの範囲における重複を見て削除してくれるので、処理前に 1 列にしておくのがいいわけです。 >>「重複行を削除」の処理 を行わなくとも、関数と作業列を用いれば、…… という件については、少なくとも回答者の皆さんにとっては分かり切ったことであるわけですが、削除前の準備については、No.2 さんが既に No.4 にて説明されているとおりです。
- mt2008
- ベストアンサー率52% (885/1701)
関数での回答は既に出ていますので、参考までにVBAでの回答も……。 Sub Sample() Dim cData As New Collection Dim oCurCell As Object Dim i On Error Resume Next For Each oCurCell In Sheets("Sheet2").Range("B2:AB32") If oCurCell.Value <> "" Then cData.Add oCurCell.Value, oCurCell.Value End If Next On Error GoTo 0 For i = 1 To cData.Count Sheets("Sheet1").Cells(2 + i, 1) = cData(i) Next i End Sub
- kagakusuki
- ベストアンサー率51% (2610/5101)
>「重複行を削除」の処理 を行わなくとも、関数と作業列を用いれば、Excelに全自動で抽出させる事が出来ます。 今仮に、Sheet3のA列を作業列として使用するものとします。 まず、Sheet3のA1セルに次の関数を入力して下さい。 =IF(INDEX(Sheet2!$B:$AB,INT((ROW()-ROW($A$1))/COLUMNS(Sheet2!$B:$AB))+1+ROW(Sheet2!$B$1),MOD(ROW()-ROW($A$1),COLUMNS(Sheet2!$B:$AB))+1)="","",INDEX(Sheet2!$B:$AB,INT((ROW()-ROW($A$1))/COLUMNS(Sheet2!$B:$AB))+1+ROW(Sheet2!$B$1),MOD(ROW()-ROW($A$1),COLUMNS(Sheet2!$B:$AB))+1)) 次に、Sheet3のB1セルに次の関数を入力して下さい。 =IF(AND($A1<>"",COUNTIF($A$1:$A1,$A1)=1),ROW(),"") 次に、Sheet3のA1~B1のセル範囲をコピーして、Sheet3のA2~B837のセル範囲に貼り付けて下さい。 次に、Sheet1のA3セルに次の関数を入力して下さい。 =IF(ROWS($3:3)>COUNT(Sheet3!$B:$B),"",INDEX(Sheet3!$A:$A,SMALL(Sheet3!$B:$B,ROWS($3:3)))) 次に、Sheet1のA3セルをコピーして、Sheet1のA4以下に貼り付けて下さい。 これで、重複の無いデータが抽出されます。 尚、作業列を1行だけで済ませるアィデアも思い付いたのですが、作業列を必要とする事には変わりがないにも関わらず、関数が複雑になりますので、今回は割愛致しました。
- KURUMITO
- ベストアンサー率42% (1835/4283)
シート2では作業列を作って対応します。 シート2のA列からAB列の32行目までデータが有るのですから、それらのデータを一つの列にまとめる操作をします。 シート2のAD1セルには次の式を入力しAD1000セルまで下方にドラッグコピーします。 =INDEX(A:AA,MOD(ROW(A1)-1,32)+1,ROUNDUP(ROW(A1)/32,0)) AE1セルは空のままでAE2セルには次の式を入力してAE1000セルまで下方にドラッグコピーします。 =IF(AND(AD2<>0,COUNTIF(AD$2:AD2,AD2)=1,COUNTIF(AD:AD,AD2)>1),MAX(AE$1:AE1)+1,"") 以上でシート2での作業は終わりです。 シート1のA3セルには次の式を入力して下方にドラッグコピーします。 =IF(ROW(A1)>MAX(Sheet2!AE:AE),"",INDEX(Sheet2!AD:AD,MATCH(ROW(A1),Sheet2!AE:AE,0))) お求めの表はシート1のA列に表示されます。 関数のみで行っていますので並べ替えなどの操作は全く必要が有りません。シート2でのデータの変化は即座にシート1の表に反映されます。
- chie65536(@chie65535)
- ベストアンサー率44% (8740/19838)
なお、シート2を元のまま残しておきたいなら、シート2をシート3に丸コピーして、シート3で作業して下さい。 シート1に、シート3に出来上がった「重複してないデータ」をコピペしたら、使い終わったシート3は不要なので、データを全クリアしましょう。 そうすれば「シート2は元のまま」で残ります。
- kybo
- ベストアンサー率53% (349/647)
単にシート2のA列だけのデータが、シート1のA列に重複なしで表示できればいいのでしょうか? シート2のA列をシート1のA列に貼り付けし、シート1のA列を範囲選択し、データ→重複の削除でそのままOKで出来ます。 これが、シート1にはB列以降のデータもあって、A列のデータだけで重複を判断したいのであれば、 シート2のデータをすべてシート1のA列以降に貼り付けし、シート1の表を範囲選択し、データ→重複の削除で、A列だけにチェックされた状態でOKします。 あえて、フィルタオプションでしたいのであれば、シート2の表を範囲選択→データ→詳細設定→リスト範囲には「$A$1:$A$32」、検索条件範囲も「$A$1:$A$32」とし、重複するレコードを無視するにチェックしてOKし、シート1に貼りつけします。
- chie65536(@chie65535)
- ベストアンサー率44% (8740/19838)
下準備として、以下の処理が必要です。 1.シート2の「A33」セルに、以下の式を入力 =OFFSET($A$2,MOD(ROW()-2,31),INT((ROW()-2)/31)) 2.A33セルをコピーして、A34~A896に貼り付け 3.A33~A896をコピーして、同じ位置に「形式を選択して貼り付け」で「値」を選択して貼り付け 4.B2~AB32は無視して良いので削除 ここまでやれば「データがA列に縦1列になっている」ので、 http://okwave.jp/qa/q7884600.html の「重複行を削除」の処理をして下さい。 なお「A2~AB32」の、縦横に複数の行と列がある表のままだと「オートフィルタ」が使えないので、重複抽出が非常に厄介になります。 なので、今回のように「一列に並べなおす」などの下準備の処理が必要です。
- chie65536(@chie65535)
- ベストアンサー率44% (8740/19838)
途中までは、以下の質問と同じ方法で抽出できます(「重複データは最初の1つ目だけ残す、の回答の方) http://okwave.jp/qa/q7884600.html 抽出後に「重複データを削除」をしないで、その代わりに「コピー&ペースト」すれば良いです。
補足
違います。シート2のB~AB列の32行までのリストの中から 重複しないデータをシート1のA列に表示していきたいのです。