Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7全般 : Excel 2010)
重複のリスト化
投稿日時: 19/02/05 15:53:44
投稿者: chokobanana

重複しているものだけをリストにする方法が知りたいです。
 
重複削除の方法は見つけられたのですが逆の方法が見つけられなかったので
どなたか教えていただけないでしょうか。
 
(データ)C22〜最終行
(リスト)N22〜
(重複の数)O22〜
 
よろしくお願いいたします。
 
 

回答
投稿日時: 19/02/05 16:11:35
投稿者: WinArrow
投稿者のウェブサイトに移動

手操作になりますが、
 
C列にデータがあるとして
 
D列に次の数式を入力します。
 
=COUNTIF($C$22:$C$200,C22)
そうすると、重複している件数が表示されます。
オートフィルタを使って、1より大きいものを抽出します。
範囲を選択して、コピペすれば
重複したデータと件数が同時に複写できます。
 
※赤字の200は適宜変更してください。
 
マクロ化したいのでしたら、
上記の操作をマウロの記録でコードが作成できます。

回答
投稿日時: 19/02/05 16:23:56
投稿者: WinArrow
投稿者のウェブサイトに移動

若干訂正と追加
 
オートフィルタで抽出下データを
 
任意のセルにコピペします。
 
次に、「データ」ー「統合」を使って、
各々のデータ個数を求めます。
 
これで、重複した「値」と件数が取得できます。

投稿日時: 19/02/07 09:20:53
投稿者: chokobanana

申し訳ございません。
 
マクロで重複のリストを作成したいのです。
教えていただいた方法ではデータの件数を都度変更しないといけません。
 
件数が何件でもリストが作成できるようにしたいのですが
どんなことでも良いので教えていただけないでしょうか。
 
お願いします。
 

回答
投稿日時: 19/02/07 09:46:14
投稿者: WinArrow
投稿者のウェブサイトに移動

>教えていただいた方法ではデータの件数を都度変更しないといけません。
  
どこの事を言っているのかわかりませんが・・・・
 
 

回答
投稿日時: 19/02/07 16:37:06
投稿者: WinArrow
投稿者のウェブサイトに移動

>教えていただいた方法ではデータの件数を都度変更しないといけません。

  
若し、=COUNTIF($C$22:$C$200,C22)
の数式の「C200」の事でしょうか?
  
対応策として
=COUNTIF(OFFSET($C$22,,,COUNTA(C:C),1),C22)
 

回答
投稿日時: 19/02/07 18:41:06
投稿者: miyakodai

>マクロで重複のリストを作成したいのです。
 
マクロの記録に手を加えたものですが、こんなのはいかがでしょう
 
Sub 重複しているデータのリスト()
 
    'D列を作業列に使います
     
    Dim last As Long, i As Long
    Application.ScreenUpdating = False
    last = Cells(Rows.Count, "C").End(xlUp).Row
    Range("D22").Formula = "=COUNTIF($C$22:$C$" & last & ",C22)"
    Range("D22").AutoFill Destination:=Range("D22:D" & last), Type:=xlFillDefault
    Range("$C$22:$D$" & last).AutoFilter Field:=2, Criteria1:=">1", _
        Operator:=xlAnd
    Range("C22:D" & last).Copy Range("N22")
    Application.CutCopyMode = False
    ActiveSheet.Range("$N$22:$O$" & last).RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
    Range("C22").AutoFilter
    Columns("D:D").ClearContents
    Application.ScreenUpdating = True
End Sub

回答
投稿日時: 19/02/07 22:45:16
投稿者: simple

既に回答がありましたが、このほか、ピボットテーブルを使う方法もあると思います。
 
・要するにデータの個数をカウントできればよいので、
 それにはピボットテーブルが簡単です。
・その個数が1より大きいものを抽出すればよいので、
 それはオートフィルターやフィルタオプションなどのフィルタ系で
 抽出すればできますね。
 
そうした見通しをつけることが大切で、
まずは、それを手作業でやってみることでしょう。
 
場合によっては、さほどの負荷でもないかもしれないし、
毎日、何度も実行することでもないのであれば、手作業で十分かもしれません。
必要であれば、ご自分でマクロ記録をとって、それを修正すれば自力でマクロが
作成できると思います。
 
その他、Dictionaryを使う方法もありますが、
まずはご自分で作ることに集中されたほうがよいと思います。

投稿日時: 19/02/08 16:05:28
投稿者: chokobanana

大量のデータを何度も色んな人が行うのでマクロで行いたいと思いました。
私も含めてPCの能力が低い人が主に作業します。
 
関数で行うと消す人がいるので難しいです。
保護は禁止されているのです。
 
教えていただいたマクロでは実行できませんでした。
「結合されたセルの一部を変更することはできません」とメッセージがでて、
下記のコードが黄色くなりました。
 
Columns("DVery Happy").ClearContents
 
どうしたらよろしいのでしょうか。
それとD列を作業列とありますが、作業列とは何のことなのでしょうか。
D列には元々データが入っております。
 
データが無い領域はN列からとなっております。
 

回答
投稿日時: 19/02/08 16:23:16
投稿者: WinArrow
投稿者のウェブサイトに移動

> Columns("D:D").ClearContents
は、作業として使った場所のクリアですから
    Range("D22:D" & last).ClearContents
に変更すればよいでしょう。
 
作業列(作業範囲)とは、一時的に空いている列(セル範囲)を使用することです。
 
若し、D列にデータが入っているならば、別の空いている列を使用するように変更することです。
回答者は、ヒントとして作業列という表現しますが、
貴方のPCの画面は見えませんから、作業列をどこにするかは、質問者が考えることになります。
 
 

投稿日時: 19/02/08 16:58:00
投稿者: chokobanana

すいません。
私が能力が低いからなのか、エラーは出なくなったのですが、全部に数字が表示されただけでした。
 
リストの余白に重複しているものだけを抽出して、その右の列に件数を表示させたいのですが。。。
 
例で言うと「例リスト」の右余白に「重複結果」を表示していのです。
 
どこか修正する箇所を間違えてしまったのでしょうか??
 
(例リスト)
 本店 
 博多
 愛媛
 博多
 北海道
 本店
 本店
 
(重複結果)
本店 3
博多 2
 

回答
投稿日時: 19/02/08 16:58:29
投稿者: WinArrow
投稿者のウェブサイトに移動

続けてのレス
 
コードの提供で、意図したように動いたとしても、それでオーライではないです。
  
条件が変わったりすると、次は、自分でコードの変更をすることになります。
  
そのような観点で、いただいたコードは、
このコードは、なんのために、何をしているのか?
など、全て理解することです。
  
理解せずにリリースしてしまうと、問題発生したら、待ったなしで対応する状況になります。
 
それから、このマクロは、どのような状況で、どのように起動するのでしょうか?
つまり、操作性を効力する必要がある。
 
 
 
 
 

回答
投稿日時: 19/02/08 17:00:53
投稿者: miyakodai

>D列には元々データが入っております。
 
Sub 重複しているデータのリストその2()
 
    'データが無い列ならどこでもいいがP列を使ってみました
     
    Dim last As Long, i As Long, k As Long
    k = 21
    Application.ScreenUpdating = False
    last = Cells(Rows.Count, "C").End(xlUp).Row
    Range("P22").Formula = "=COUNTIF($C$22:$C$" & last & ",C22)"
    Range("P22").AutoFill Destination:=Range("P22:P" & last), Type:=xlFillDefault
    For i = 22 To last
        If Cells(i, "P").Value > 1 Then
            k = k + 1
            Cells(k, "N").Value = Cells(i, "C").Value
            Cells(k, "O").Value = Cells(i, "P").Value
        End If
    Next
    Range("$N$22:$O$" & last).RemoveDuplicates Columns:=Array(1, 2), Header _
        :=xlNo
    Columns("P:P").ClearContents
    Application.ScreenUpdating = True
End Sub
 

回答
投稿日時: 19/02/08 17:12:13
投稿者: ピンク

>マクロで重複のリストを作成したいのです。
どうぞ〜
Sub Test()
    Dim myDic As Object
    Dim c As Range
    Dim d As Variant
    Dim i As Long
  
    Set myDic = CreateObject("Scripting.Dictionary")
    For Each c In Range("C22", Cells(Rows.Count, "C").End(xlUp))
        myDic(c.Value) = myDic(c.Value) + 1
    Next
    For Each d In myDic.keys
        If myDic(d) > 1 Then
            Range("N22").Offset(i).Value = d
            Range("O22").Offset(i).Value = myDic(d)
            i = i + 1
        End If
    Next d
    Set myDic = Nothing
End Sub
 

投稿日時: 19/02/12 09:17:00
投稿者: chokobanana

miyakodaiさん
ピンクさん
 
ありがとうございます。
希望通りにできました。
 
教えて頂いたコードを1行づつ調べております。
お二人のコードが違うのに結果が一緒になるとはびっくりです。
 
重複は他にもやってみたいことがあるので頑張って勉強します。