No.5ベストアンサー
- 回答日時:
【関数と作業列を使う方法】
今仮に、6桁の数字がA列・B列に入力されているシートがSheet1であるものとし、
Sheet3のA列とB列を作業列として使用し、
Sheet2のA列に重複している数値を、全て表示し、その右方にあるセルに、重複している箇所のセル番号を表示させるものとします。
まず、Sheet3のA1セルに次の数式を入力して下さい。
=IF(AND(COUNTIF(Sheet1!$A:$B,INDEX(Sheet1!A:A,ROW()))>1,COUNTIF(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$B:$B,ROW()),INDEX(Sheet1!A:A,ROW()))-(INDEX(Sheet1!A:A,ROW())=INDEX(Sheet1!B:B,ROW()))=1),INDEX(Sheet1!A:A,ROW()),"")
次に、Sheet3のA1セルをコピーして、Sheet3のB1セルに貼り付けて下さい。
次に、Sheet3のA1~B1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。
次に、Sheet2のA2セルに次の数式を入力して下さい。
=IF(ROWS($A$2:A2)>COUNT(Sheet3!$A:$B),"",SMALL(Sheet3!$A:$B,ROWS($A$2:A2)))
次に、Sheet2のB2セルに次の数式を入力して下さい。
=IF(COLUMNS($B:B)>COUNTIF(Sheet1!$A:$B,$A2),"",IF(COLUMNS($B:B)>COUNTIF(Sheet1!$A:$A,$A2),"B","A")&(MATCH($A2,INDEX(Sheet1!$A:$B,IF(AND(COLUMNS($B:B)>1,LEFT(A2,1)=IF(COLUMNS($B:B)>COUNTIF(Sheet1!$A:$A,$A2),"B","A")),ROW(INDIRECT(A2)),0)+1,(COLUMNS($B:B)>COUNTIF(Sheet1!$A:$A,$A2))+1):INDEX(Sheet1!$A:$B,MAX(MATCH(9^99,Sheet1!$A:$A),MATCH(9^99,Sheet1!$B:$B)),(COLUMNS($B:B)>COUNTIF(Sheet1!$A:$A,$A2))+1),0)+IF(AND(COLUMNS($B:B)>1,LEFT(A2,1)=IF(COLUMNS($B:B)>COUNTIF(Sheet1!$A:$A,$A2),"B","A")),ROW(INDIRECT(A2)),0)))
次に、Sheet2のB2セルをコピーして、Sheet2のB2の右方向に位置するセルに貼り付けて下さい。
次に、Sheet2の2行目全体をコピーして、3行目以下に貼り付けて下さい。
すると、Sheet2に重複している数値と、その数値が入力されている位置が表示されます。
【条件付き書式を利用する方法(Excel2007よりも前のバージョンの場合)】
A1セルををクリック
↓
メニューの[書式]をクリック
↓
現れた選択肢の中にある[条件付き書式]をクリック
↓
現れた「条件付き書式の設定」ウィンドウの左端の欄をクリック
↓
現れた選択肢の中にある「数式が」をクリック
↓
左から3番目にある欄の欄に次の数式を入力
=COUNTIF($A:$B,A1)>1
↓
「条件付き書式の設定」ウィンドウの[書式]ボタンをクリック
↓
現れた「セルの書式設定」ウィンドウの[パターン]タグをクリック
↓
好きな色の四角形をクリック
↓
「セルの書式設定」ウィンドウの[OK]ボタンをクリック
↓
「条件付き書式の設定」ウィンドウの[OK]ボタンをクリック
↓
A1セルにカーソルを合わせて、マウスを右クリック
↓
現れた選択肢の中にある[コピー]をクリック
↓
A・B列の条件付き書式を設定したいセル範囲を、範囲選択
↓
選択範囲を示す黒い太枠の内側にカーソルを合わせて、マウスを右クリック
↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
↓
現れた「形式を選択して貼り付け」ウィンドウの中にある「書式」と記されている箇所をクリックして、チェックを入れる
↓
「形式を選択して貼り付け」ウィンドウのの[OK]ボタンをクリック
【条件付き書式を利用する方法(Excel2007以降のバージョンの場合)】
A1セルををクリック
↓
[ホーム]タグをクリック
↓
「スタイル」タグの中にある[条件付き書式] ボタンをクリック
↓
現れた選択肢の中にある[新しいルール] をクリック
↓
現れた「新しい書式ルール」ウィンドウの[数式を使用して、書式設定するセルを決定] をクリック
↓
左から3番目にある欄の欄に次の数式を入力
=COUNTIF($A:$B,A1)>1
↓
「新しい書式ルール」ウィンドウの[書式]ボタンをクリック
↓
現れた「セルの書式設定」ウィンドウの[塗りつぶし] タグをクリック
↓
好きな色の四角形をクリック
↓
「セルの書式設定」ウィンドウの[OK]ボタンをクリック
↓
「新しい書式ルール」ウィンドウの[OK]ボタンをクリック
↓
選択しているセルを変えずに、再度[条件付き書式] をクリック
↓
現れた選択肢の中にある[ルールの管理] をクリック
↓
現れた「条件付き書式ルールの管理」ウィンドウの「書式ルールの表示」欄が[現在の選択範囲]となっていることを確認
↓
「ルール(表示順で適用)」欄が「数式: =COUNTIF($A:$B,A1)>1」となっている行の「適用先」欄の内容を
=$A$1
から
=$A$1:$B$65535
に変更する(数値が入力されている可能性のあるセル範囲の全て)
↓
「条件付き書式ルールの管理」ウィンドウの[OK]ボタンをクリック
以上です。
No.7
- 回答日時:
別件で連想配列づいているもので...
重複している値のセルに色を付けます。
重複数が少なければ、数秒で終了します。(PentiumM 1.33GHz)
dictionaryは最初とっつき難いのを経験しておりますが、情報はWeb上に結構ありますので、必要ならお調べ下さい。
他のプログラミング言語では当たり前に使っていたりします。
Sub test()
Dim myDic As Object
Dim buf As Variant
Dim i As Long, j As Long
Dim myKey As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'試験データ作成
' With Range("A1:B65000")
' .Clear
' .Formula = "=int(rand()*10000000)+1"
' .Value = .Value
' End With
Set myDic = CreateObject("Scripting.Dictionary")
buf = Range("A1:B65000")
For i = 1 To UBound(buf, 1)
For j = 1 To UBound(buf, 2)
myKey = CStr(buf(i, j))
If myDic.exists(myKey) Then
Set myDic.Item(myKey) = Union(myDic.Item(myKey), Cells(i, j))
myDic.Item(myKey).Interior.ColorIndex = 4
Else
myDic.Add myKey, Cells(i, j)
End If
Next j
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
No.6
- 回答日時:
COUNTIF は複数列対象で数えられる
例
=COUNTIF(A2:C7,A2)
ーー
だからあわせて、13万行ぐらいなら(質問に合計行数ぐらい書くこと。それと第2列以後のデータのあり場所=列など)
データを隣接2列とかにくっつけられるなら(隣列に列挿入し、そこへコピー貼り付け)、COUNTIFで重複判定できるのでは。
結果が2列(各列対応)になってしまうが。
そういうことでは、都合が悪い・出来ない理由など質問に丁寧に書くべきではと思う。
思うよう(内容不明だが)にやりたいなら、VBAを使わないと出来ないのでは。
>重複しているものを見つけたいのですが
これも抽象的。見つけてどうしたいのか?件数カウントだけなら、まだ簡単だが。
No.4
- 回答日時:
こんばんは!
VBAでの一例です。
Sheet1のA・B列の1行目からデータがあるとします。
重複するデータをSheet2のA列に表示するようにしてみました。
画面左下のSheet1のSheet見出し上で右クリック → コードの表示 → VBE画面が出ますので
↓のコードをコピー&ペーストしてマクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)
Sub test() 'この行から
Dim i, j As Long
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To 2
If WorksheetFunction.CountIf(Range(ws1.Columns(1), ws1.Columns(2)), ws1.Cells(i, j)) > 1 Then
ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws1.Cells(i, 1)
End If
Next j
Next i
For i = ws2.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If WorksheetFunction.CountIf(ws2.Columns(1), ws2.Cells(i, 1)) > 1 Then
ws2.Cells(i, 1).Delete (xlUp)
End If
Next i
End Sub 'この行まで
※ データが65000行以上あるというコトなので、少々時間がかかるかもしれません。
他に良い方法があればごめんなさいね。m(_ _)m
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ゼロを表示
-
Excelの計算が合いません。 諸...
-
今まで文字化けなく開けていたc...
-
Excelの警告について
-
ExcelでASCを使って全角を半角...
-
スプレッドシートで指定された...
-
作成した数式を値として表示し...
-
EXCELの散布図で日付が1900年に...
-
Excel関数について教えてくださ...
-
Excel関数について教えてくださ...
-
マクロの処理が遅くなった
-
エクセルを使用して、円周率を...
-
エクセルでファイルの最終更新...
-
エクセルのセル内に分数などの...
-
条件付き書式設定で罫線を引き...
-
シートの情報を別のシートへま...
-
【マクロ】フォルダ内にあるPDF...
-
エクセルで曜日を入れたい
-
Microsoft 365Excelの見開きペ...
-
Excelで時間の計算の方法 7時45...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報