Excelの問題で困っております。

今、以下のようなデータが、A列に5000ほど並んでおります。

-------------



100
101
102
103


101
102
201
202
203



-------------

A列はそれぞれ番号であり、重複してはいけません。
なので、重複はないかを調べるため、「=IF(COUNTIF($A$1:$A$5000,A1)>=2,"×","○")」という関数を、適当な列に入力し、オートフィルで5000行まで(「=IF(COUNTIF($A$1:$A$5000,A5000)>=2,"×","○")」)割り当てました。
これで、重複が出た時は「×」印を出してくれるようにしました。

例えば上記の例においては、重複した「102」に、×印が出ます。
ところが、「違いが半角か全角」でしかない「101」「101」は、違うものとして扱われ、どちらも「〇」印が出てしまうのです(>_<)

どうにかして、半角も全角も区別せずに、「101」と「101」があれば×印を出すような関数およびマクロを、教えていただけないでしょうか?
よろしくお願いします<m(__)m>

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2010/10/03 11:34:03
  • 終了:2010/10/06 04:35:25

ベストアンサー

id:Mook No.2

Mook回答回数1312ベストアンサー獲得回数3912010/10/03 13:30:42

ポイント100pt

こんな形でどうでしょうか。

全角は半角としてから処理し、空白も取り除いた形でチェックしています。


重複しているA列に色を塗るのと同時に、作業列(B列)に最初にある位置のデータを書くようにしてみました。

Sub checkA()
'// 重複していた時の塗りつぶし色
    Const DColor = 38 '// ピンク
    Dim objDic
    Set objDic = CreateObject("Scripting.Dictionary")

    Dim lastRow As Integer
    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    Range("A1:A" & lastRow).Interior.ColorIndex = xlNone
    Columns("B").Insert
    
    Dim s As String
    For r = 1 To lastRow
'//  半角、空白を除去してから確認
        s = Trim(StrConv(CStr(Cells(r, "A").Value), vbNarrow))
        If objDic.Exists(s) = False Then
            objDic.Add s, r
        Else
            If Range("A" & objDic.Item(s)).Interior.ColorIndex = xlNone Then
                Range("A" & objDic.Item(s)).Interior.ColorIndex = DColor
            End If
            Range("A" & r).Interior.ColorIndex = DColor
            Range("B" & r).Value = "A" & objDic.Item(s)
        End If
    Next
End Sub
id:moon-fondu

すごいです、うまくいきました!

ありがとうございます(^_^;)

2010/10/06 04:34:53

その他の回答(4件)

id:doxas No.1

doxas回答回数13ベストアンサー獲得回数42010/10/03 11:44:22

ポイント20pt

ひと手間踏んでもよければ、ASC関数を使うか、あるいはJIS関数ですね。

ASCは全角でも半角でも表記できる文字を全て半角に変換した結果を返します。

JISはその逆です。どちらかに統一するという意味であればどちらでもいいでしょう。

どこか適当な列にASCなどの関数を用いて半角に変換したデータをまずは抜き出し、そのデータに対してCOUNTIFの数式を当て込めれば、問題は解決すると思います。

これはマクロを使うまでも無いでしょう。

id:moon-fondu

なるほどです、その手があったんですね!

2010/10/05 02:42:58
id:Mook No.2

Mook回答回数1312ベストアンサー獲得回数3912010/10/03 13:30:42ここでベストアンサー

ポイント100pt

こんな形でどうでしょうか。

全角は半角としてから処理し、空白も取り除いた形でチェックしています。


重複しているA列に色を塗るのと同時に、作業列(B列)に最初にある位置のデータを書くようにしてみました。

Sub checkA()
'// 重複していた時の塗りつぶし色
    Const DColor = 38 '// ピンク
    Dim objDic
    Set objDic = CreateObject("Scripting.Dictionary")

    Dim lastRow As Integer
    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    Range("A1:A" & lastRow).Interior.ColorIndex = xlNone
    Columns("B").Insert
    
    Dim s As String
    For r = 1 To lastRow
'//  半角、空白を除去してから確認
        s = Trim(StrConv(CStr(Cells(r, "A").Value), vbNarrow))
        If objDic.Exists(s) = False Then
            objDic.Add s, r
        Else
            If Range("A" & objDic.Item(s)).Interior.ColorIndex = xlNone Then
                Range("A" & objDic.Item(s)).Interior.ColorIndex = DColor
            End If
            Range("A" & r).Interior.ColorIndex = DColor
            Range("B" & r).Value = "A" & objDic.Item(s)
        End If
    Next
End Sub
id:moon-fondu

すごいです、うまくいきました!

ありがとうございます(^_^;)

2010/10/06 04:34:53
id:takashi_m17 No.3

たか回答回数104ベストアンサー獲得回数122010/10/03 15:43:22

ポイント15pt

=ASC(A1)という関数をB1に入れて5000行コピーします。

ASC(文字列)関数は2バイト文字を1バイト文字に変換します。

よって全角101を半角101に変えてくれます。


ちなみに逆の半角→全角変換はJIS(文字列)関数です。

id:moon-fondu

ありがとうございます!

2010/10/05 02:43:09
id:nepia11 No.4

nepia11回答回数256ベストアンサー獲得回数332010/10/03 21:34:36

ポイント15pt

テキストを半角に変換する「ASC関数」もしくは、

逆に全角にする「JIS関数」がいいと思います。


B列に「=ASC(A1)」でオートフィルで5000列目まで。

その後に、C列に

=IF(COUNTIF($B$1:$B$5000,B1)>=2,"×","○")でオートフィルで5000列目まで。

これでいいと思います。

id:moon-fondu

ありがとうございます、気づきませんでした(>_<)

2010/10/05 02:43:30
id:Grossauge No.5

Grossauge回答回数25ベストアンサー獲得回数42010/10/04 05:11:19

ポイント15pt

日本版のエクセルをお使いならば、一列追加して、ASC関数で全角を半角に変換して、その列に対して同じcountifの関数を適用しみてはどうでしょうか?

http://pc.nikkeibp.co.jp/pc21/tech/excel36/28/

id:moon-fondu

JIS(ジス)関数もASC(アスキー)関数も初めて知りました、ありがとうございます!

2010/10/05 02:44:10
  • id:Silvanus
    ご質問の状況を再現しようと思いましたが、当方では
    全角・半角の区別無くいけましたけど…。
    もしかして、アラビア数字の前後に空白が入っていたりとか?
    画面写真 http://rct3jp.info/hatena/101003-01.png
    その様な場合も含めて対処したいということですか?
  • id:Mook
    回答中空白セルを書き忘れました。

    途中に空白がなければ問題ありませんが、もしある場合には For 文の中を
    For ....
     s = ...
     If s <> "" Then '// 追加
      : ' 回答の部分 
     End If '// 追加
    Next
    のようにしてください。
  • id:Silvanus
    前後に空白を挿入しても式の値には変化無し。再現できないのでギブアップ…。
  • id:moon-fondu
    >Silvanusさん
    いえ、空白は入っていませんでした。でも、Silvanusさんの添付画像では、「105」と「105」は同じものして扱ってるんで「×」になってるんですか・・・私の場合ではそうならずに、区別して「〇」になってしまいましたからね・・・どうしてでしょう・・・
    >Mookさん
    ありがとございます!さっそく明日、Mookさんが教えてくださったマクロ、試してみます(^_^;)
  • id:Silvanus
    >> moon-fonduさん
    問題となっているMS-Excelのワークブックファイルそのものを
    どこかへアップロードしていただいて、回答しようとしている私達が
    それを直接確認できるようにしていただければ嬉しいですね!
    それと、Excelに限りませんが、こういうアプリはバージョン間で
    全ての機能が完全互換ではありませんので、質問される際は
    ご使用になっているアプリのバージョンを明記された方が良いと思います。
  • id:Silvanus
    追記
    度々済みません。もしMS-Officeアプリで作成したファイルをアップする場合は
    (MSO2007であれば)[Officeボタン]-[配布準備]-[ドキュメント検査] を選択して
    個人情報(作成者名等)が含まれていないかどうかチェックしてからアップして下さいね。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません