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

関連記事

VULTRでRHEL8をインストール(1/2)

VULTRのVPSにRHEL8.3をインストールする手順を説明します。 概要 VULTRのVPSにRed Hat Enterprise Linux(RHEL)をインストールする手順を説明します。 実案 …

Excel VBAでJSONを自動生成

サンプルExcelシートの説明 Excelシート上に記載したフィールドや値をJSONに出力できます。 「配列」(I列)、「型」(J列)を変更することで、JSONへの値の出力仕様を変更できます。JSON …

ASP.NET Coreを使った独自フレームワーク開発の考察

ASP.NET CoreをベースとしたWebアプリ用フレームワークを開発するためのナレッジ置き場です。 ASP.NET Core3系のMVC + Razorを使用する前提の情報です。 まだ思い付きで書 …

開発環境のJBoss EAP7にリモートアクセス

開発中のものを他者に見せたり、問題が発生している開発者の開発物を参照するために、eclipse上で起動しているEAP7のWebアプリに別のPCからアクセスしたい場合があります。 このための手順を記載し …

ftp, ftps, sftpの違い

開発対象システムの連携先システムとして、ftpsやらftpsサーバが指定される場合がある。 私の場合、開発標準の役割を担う場合が多く、これらの仕様を把握し、動作確認や単体テスト用のダミーのサーバを用意 …