この関数と同じ処理をVBAで行うにはどうしたら良いでしょうか？ これは、1列の中に同じ値が複数存在し

この関数と同じ処理をVBAで行うにはどうしたら良いでしょうか？
これは、1列の中に同じ値が複数存在しているかユニークかを判断させる処理です。
AM列中の結果をAO列に書き出してます。
=IF(COUNTIF($AM$1:AM1,$AM1)>=2,"重複","ユニーク")

数十万行あるので関数だと非常に時間がかかるため、
VBAで処理してみたいと思っています。

本当は、AM列以外にAQ列、AU列でも同様の処理を行えると嬉しいです。

No.11 回答者： めぐみん_ 回答日時： 2022/07/07 16:11

No.7です。

Rows.Count を最後の行番号としてますが使用セル個数が多い点から、
もしかしたら『Rows.CountLarge』が宜しいのかな？

あとこの質問以外に実行中に何かをさせているとか？
特に画面に何かをするって事なら、

https://xtech.nikkei.com/it/atcl/column/15/09010 …

にて

・ApplicationオブジェクトのScreenUpdatingプロパティにFalse

・処理実行

・ApplicationオブジェクトのScreenUpdatingプロパティにTrue

と一旦画面が変化しないようにしてしまい、処理後に一気にするとかかな？

No.10 回答者： ひでオンジ 回答日時： 2022/07/07 12:17

Public Sub dup()

Dim Chofuku As Object
Dim lLoop As Long
Dim lEndRow As Long
Dim sPaste1() As String
Dim sPaste2() As String
Dim vKey As Variant
Dim sKey As String

Set Chofuku = CreateObject("Scripting.Dictionary")

lEndRow = Cells(Rows.Count, "AM").End(xlUp).Row

For lLoop = 1 To lEndRow
sKey = Cells(lLoop, "AM").Value
If Chofuku.Exists(sKey) = False Then
Chofuku.Add sKey, "ユニーク"
Else
Chofuku.Item(sKey) = "重複"
End If

Next

lLoop = 0
For Each vKey In Chofuku.Keys
ReDim Preserve sPaste1(lLoop)
ReDim Preserve sPaste2(lLoop)

sPaste1(lLoop) = vKey
sPaste2(lLoop) = Chofuku.Item(vKey)

lLoop = lLoop + 1
Next

Range(Range("AN1"), Range("AN1").Offset(UBound(sPaste1), 0)).Value = WorksheetFunction.Transpose(sPaste1)
Range(Range("AO1"), Range("AO1").Offset(UBound(sPaste2), 0)).Value = WorksheetFunction.Transpose(sPaste2)

End Sub

この回答へのお礼

AM列で判定した結果を、
AN列と、AO列に書き込んでいるようですが、少し仕様を教えて頂けませんか？

テストしてみた感じだと、AN列にはAM列と同じ値を書き込んでいるようですが、ちょっと違うようで、途中から行ずれを起こしていっているようです。
AO列には重複かユニークかが書き込まれていました。
30万行での結果が出るまでの時間は速かったので完成出来たら幸いです。

お礼日時：2022/07/07 16:48

No.9 回答者： tatsumaru77 回答日時： 2022/07/07 12:17

補足要求です。

１．COUNTIFを使うと、大文字、小文字を区別せずに、重複を判定しますが、大文字、小文字は区別せずに重複判定をしたいということでしょうか。

２．あなたが提示された
=IF(COUNTIF($AM$1:AM1,$AM1)>=2,"重複","ユニーク")
をAOに記入し、下へオートフィルすると、
重複があった場合でも、１件目のデータはユニークと表示されます。
つまり、めぐみん_さんのNo5のようになります。
No4の結果を期待するなら、
=IF(COUNTIF($AM:$AM,$AM1)>=2,"重複","ユニーク")
ではないでしょうか？

３．AM列以外にAQ列、AU列でも同様の処理を行えると嬉しいです。
ということですが、これは、
①ＡＱ列の重複結果をＡＵ列へ出力する。
②ＡＱ列の重複結果を何かほかの列へ出力する。
及びＡＵ列の重複結果を何かほかの列へ出力する。
のように考えられます。
①②のどちらなのでしょうか。
②の場合は、出力先の行も提示したほうが良いかと思います。

この回答へのお礼

ありがとうございます。

> １．COUNTIFを使うと、大文字、小文字を区別せずに、重複を判定します> が、大文字、小文字は区別せずに重複判定をしたいということでしょうか。

そうです。例えば外国人の名前です。
MICHAELとMichaelは同一人物ですからそれぞれにユニークと結果が出ては困るんです。

> ２．あなたが提示された
> =IF(COUNTIF($AM$1:AM1,$AM1)>=2,"重複","ユニーク")
確かにそうですね、失礼いたしました。
私が考えていた式はこちらになります。
=IF(COUNTIF(AM:AM,AM1)>=2,"重複","ユニーク")
1セルに対して1行目～30万行目まで走査するでしょうから待ち時間多すぎです。。。

> ３．AM列以外にAQ列、AU列でも同様の処理を行えると嬉しいです。
> ということですが、これは、
頂いたアドバイスをカスタマイズして、現実の集計に活かすべきと思っていたので挙げてる列名はダミーなんですが、正確に言うと
実際にはAK列内の重複判定の書き込み先をAM列に、
AN列の判定結果をAP列に、AQ列の判定結果をAS列に、となってます。

回答者さんのソースに頼っちゃっていてお恥ずかしい限りです。

お礼日時：2022/07/07 16:19

No.8 回答者： Qchan1962 回答日時： 2022/07/07 11:05

ご質問されている

https://oshiete.goo.ne.jp/qa/13027572.html
で試された　#13様の回答コードを少し変更すれば良いのでは？

この回答へのお礼

はい、余裕が出来たらトライしてみます！

お礼日時：2022/07/07 15:35

No.7 回答者： めぐみん_ 回答日時： 2022/07/07 10:15

No.5です。

そうしますと勘違いしたと思ってたNo.4の方でしょうか？

この回答へのお礼

はい。No.4の方で今テストしています。
30万行以上あるデータなのですが、5分経っても応答なしのままです・・・
しばらく待ってみます。

ソートして上下を比較する関数の方が速いんですかね

お礼日時：2022/07/07 11:35

No.6 回答者： KenKen_SP 回答日時： 2022/07/07 09:58

こんにちは。

関数とVBAでロジックが同じなら関数の方が速いと思いますので、今の方向性で宜しいかと思います。

ご質問の本質的な問題点は、
>数十万行あるので関数だと非常に時間がかかる

と理解しました。

ポイントは重複チェック列にデータ入力があると、全ての重複チェックを行う関数において再計算が発生し、パフォーマンスを落としていると想像します。

この場合、リアルタイム再計算ではなく、設定でもVBAでも良いのですがExcelの再計算オプションを操作して、ボタンを押した時だけ再計算する仕組みにしてはどうですか？

以下余談

大きな期待はできませんが、関数の書き方で1点ポイントを提示します。

関数式の論理式
COUNTIF($AM$1:AM1,$AM1)>=2

は2つの評価（処理）がされています。

・数値は2より大きいか？
・数値は2か？

です。今回少数点以下の数値は有り得ませんので、

COUNTIF($AM$1:AM1,$AM1)>1

・数値は1より大きいか？

と同意となります。

この場合、論理式の評価回数は1回ですよね。つまり、処理回数を1つ減らせるわけです。

ループ処理や広域のセルを計算する場合は、こうした小さな点も高速化のポイントになりそうです。

この回答へのお礼

ロジックを考えるうえで貴重な情報です。
「>＝」と「>」は処理回数に置いて異なるのは本当ですか？
普通は数千件あろうが時間差が無いので考えても見ませんでした。
数十万件超えると顕著に現れますよね。まだ上記のテストはしてません。
微妙な差でも知っておくことは必要だと思ってます。とても感心しました。

お礼日時：2022/07/07 15:52

No.5 回答者： めぐみん_ 回答日時： 2022/07/07 09:06

No.4です。

ミスりました。
重複してても１回目はユニークなんですね。

修正版：

Sub try_2()
Dim AM As Range
Dim r As Range

'セル範囲を変更した変数を追加作成し下記ループをそれぞれの変数で行なう
Set AM = Range("AM1", Cells(Rows.Count, "AM").End(xlUp))

For Each r In AM

r.Offset(, 2).Value = IIf(WorksheetFunction.CountIf(Range(AM.Cells(1), r), r.Value) >= 2, "重複", "ユニーク")

Next

Set AM = Nothing
End Sub

この回答へのお礼

添付画像でいうと、aは1回目から重複を入れたいです。
・全体的に1個しか存在しない値は「ユニーク」
・2つ以上存在する場合は全て「重複」※1回目から
という規則で処理したいです。
つまり、
=IF(COUNTIF($AM$1:AM1,$AM1)>=2,"重複","ユニーク")
と全く同じ結果を得たいんです。

お礼日時：2022/07/07 09:43

No.4 回答者： めぐみん_ 回答日時： 2022/07/07 08:54

No.2です。

Sub try()
Dim AM As Range
Dim r As Range

'セル範囲を変更した変数を追加作成し下記ループをそれぞれの変数で行なう
Set AM = Range("AM1", Cells(Rows.Count, "AM").End(xlUp))

For Each r In AM

r.Offset(, 2).Value = IIf(WorksheetFunction.CountIf(AM, r.Value) > 1, "重複", "ユニーク")

Next

Set AM = Nothing
End Sub

この回答へのお礼

ありがとうございます。
こちらのソースでテストしてみたのですが20分経過後も終わらなかったので、ブック自体を強制終了して、アナログ作業で済ませました。。。
時間に余裕がある時に色々試したいと思います。
でも感謝してます。

お礼日時：2022/07/07 15:48

No.3 回答者： ひでオンジ 回答日時： 2022/07/07 08:34

重複チェックの結果は何列目に吐き出せば良いですか？

ディクショナリーを使えば可能です
dim Chofuku as object
Chofuku=CreateObject("Scripting.Dictionary")

ループ開始
if Chofuku.Exists(対象の値)=False Then
Chofuku.Add(対象の値,1)
Else
Chofuku.Item(対象の値)=Chofuku.Item(対象の値)+1
End If
ループ終了

この回答へのお礼

そのディクショナリーという方式の効率の良さに驚いています。
まだ全然理解できてませんので、ソースをそのままテストする次第です。
AM列を重複検査した時の吐き出し先は2列右です(AMの場合AO、AXだったらAZ)
回答してくださってる方々に返信していきながら、一番手っ取り早そうな方法に変えてくつもりです。

お礼日時：2022/07/07 15:47

No.2 回答者： めぐみん_ 回答日時： 2022/07/07 08:20

・セル範囲取得（ＡＭ、ＡＱ、ＡＵ列・・・最終行は同じ？）

・セル範囲に検索値が何個あるかWorksheetFunction.CountIfで調べ、１を超えるか否かで代入する値をチョイスする。（IIf関数で）
・一応実行開始すぐに画面更新を止め、全てが終わったら更新停止を解除する

とかですかね？
質問が続きならソートは出来ない条件と言う事で。

この回答へのお礼

ありがとうございます。
そしてお礼の対応が遅れてすみません。
作業しながらなので・・・

AM AQ AU列で重複判定が必要ですが、CSV内の処理で最終行は同じです。
各列毎にソートしてると待ち時間が半端ないので、そもそもソートなしでの実現案を模索しています。

ちなみに本日は、各列毎にソートして終えました。また来週作業が発生します。(レコード数が約1万件増加して・・・)

お礼日時：2022/07/07 15:45