NDW

アプリ開発やトラブルシューティング等のノウハウ、キャンプや登山の紹介や体験談など。

1. システムエンジニアリング Excel/Word

Excelで大量データから重複値を検索する方法

投稿日:

100万件程の大量データの中で重複するデータを見つける方法を紹介します。

  • 前提条件
    検証で使用した環境は次の通りです。
    RAND()関数で作成した約100万件(Excelの最大行数となる1,048,576個)のデータを使用しています。

    PC AMD Ryzen 5 3400G, メモリ: 16GB, SSD: 256GB
    OS Windows 10(64ビット)
    Office Microsoft Office Professional Plus 2019
    (Microsoft® Excel® 2019 MSO (16.0.14228.20216) 32 ビット )
  • 重複検出方法の比較
    1. 単純案 ※ダメな案

      • COUNTIF関数を使って同じ値を持つセル数をカウントします。
        重複している個所は、2以上の値になります。
      • これをExcelの最大行数(約100万件)に適用すると、数分間応答がなくなります。
      • 重複個所のみを表示するようフィルタすると、応答が返ってきません。
    2. 改善案

      • 対象データを事前にソートし、一つ前の値と一致するかを確認する式を使用します。
        重複している個所は、TRUEになります。

      • ソート処理は非常に速く、約100万件のソートでも数秒で完了します。
      • 重複個所のみ表示するようフィルタすると、数十秒程かかりますが、応答は返ってきます。
  • 性能の違いの説明
    データ件数に応じた比較回数と計算量を次に示します。

    方法案 平均比較回数
    (100件想定)
    平均比較回数
    (100万件想定)
    計算量
    単純案 100 × 100 ÷ 2 1,000,000 × 1,000,000 ÷ 2 O(n2)
    改善案 100 1,000,000 O(n)






-1. システムエンジニアリング, Excel/Word

関連記事

Wildflyでリクエストをダンプする

Webアプリのデバッグや障害対応等で、Webアプリに対するリクエストやレスポンスのHTTPヘッダや内容を確認したい場合がある。 次のような場面ではクライアント側での確認ができないため、今回のようにAP …

mavenマルチモジュールプロジェクトの構成例

システム開発でよく使用するmavenマルチモジュールプロジェクトの構成サンプルを説明します。 構成方針 複数のサブシステムをもつシステム開発を想定しています。システム名はzzz、サブシステムはf10, …

ゾーン10進数の変換方法とC#のサンプル

なお、パック10進数はこちらで紹介しています。 ゾーン10進数 ゾーン10進数の変換方法 10進数の各桁を「ゾーン部(上位4ビット)+数値部(下位4ビット)」のバイトに変換して生成します。 生成したバ …

JBoss EAP7でDEBUG/TRACEログを出力

JBoss EAP7でアプリのDEBUGやTRACEのログを出力する方法を記載します。 開発の序盤でアプリのDEBUGログがeclipseのコンソールやログファイルに出力されず、困る場合があるので、メ …

Wildfly11のインストール

このサイトでWebアプリを公開したい。 仕事ではJBoss Enterprise Application Server(JBoss EAP)を使う機会が多いのでそれを使いたいがライセンス料がかかる。無 …