☰

欠落している反復 ID 番号をテキストまたは Excel のシリーズに追加する必要があります。コーディングなしでこれを実行するにはどうすればよいですか?

tag-icon • tag-icon microsoft-excel regex
欠落している反復 ID 番号をテキストまたは Excel のシリーズに追加する必要があります。コーディングなしでこれを実行するにはどうすればよいですか?

次のようなテキスト、CSV、またはExcelファイルがあります

||--ID-----||--Name--||--Date of birth--||
    1            Jo          1/1/11
    32           Mo          2/2/12
    3382         Ro          3/3/10
    21,252       Do          4/4/09

実際のセットには、合計 1,000,000 行のうち 280,000 行が含まれています。不足している ID 番号 (すべて連続 +1 反復) をすべて何らかの方法で追加し、名前と生年月日のフィールドのみを空にする必要があります。その結果は次のようになります。

||--ID-----||--Name--||--Date of birth--||
    1,           "Jo",     "1/1/11"
    2,           "",         ""
    3,           "",         ""
    4,           "",         ""

32 まで進み、すべての整数が揃うまで繰り返します。これを行う簡単な方法はありますか? ループでコード ブロックを記述せずに? Excel、何らかのアプリ、または Windows テキスト エディターのトリックから何かあれば、ありがたいです。

編集: カンマや引用符などは無視してください。欠落している ID 番号だけが重要です。

答え1

一つのアプローチは次のようになります。

  1. CSV をシート 1 として Excel にインポートします。
  2. シート 2 で、a2: =a1+1 などの数式を使用して ID 番号の完全なリストを作成し、それを下方向にコピーします。
  3. 他の 2 つの列で vlookup 数式を使用して、シート 1 のテーブルを参照します。たとえば、b2:=VLOOKUP(A2,Sheet1!A2:C13,2,false)および c2:などです。=VLOOKUP(A2,Sheet1!A2:C13,3,false)または、要求に正確に一致するように、これを IFNA ステートメントにラップして、値がない場合に "" を返します。b2:=IFNA(VLOOKUP(A2,Sheet1!A2:C13,2,FALSE),"")および c2: =IFNA(VLOOKUP(A2,Sheet1!A2:C13,3,FALSE),"")(これらの数式は、列にコピーできるようになりました。)
  4. 入力したら、シート 2 を CSV として保存します。

注: これが正しく機能するには、シート 1 のテーブルを列 1 で並べ替える必要があります。提供されている情報からはそのように見えますが、そうでない場合は最初の列で情報を並べ替えます。

100 万行あると、多くのコンピューターでは非常に遅くなりますが、数式を書く前にそのことに気付きませんでした。大規模なデータ セットで同様の操作を 1 回行ったことがあります。自動再計算をオフにしてすべての数式を配置し、その後手動で再計算しました。数時間はかかりましたが、正しく完了しました。

答え2

Power Query アドインを使用してこれを解決します。

表示またはダウンロードできるプロトタイプを作成しました。One Drive の「Power Query デモ - 欠落している ID 番号をシリーズに追加.xlsx」です。

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

そこに到達するには、いくつかの手順と、Power Query 言語 (M) で List.Numbers 関数を呼び出すためのコーディングが少し必要です (Power Query UI では公開されていません)。それでも、これは 1 行のシンプルなコードにすぎません。残りの部分は、Power Query 内でクリックして構築できます。

基本的に私の手法は、List.Numbers を使用して ID 番号のテーブルを生成し、次に Merge を追加して入力データ (ID 番号が存在する場所) から列を取得するというものでした。

List.Numbers のドキュメントはここにあります:

http://office.microsoft.com/en-au/excel-help/list-numbers-HA104111648.aspx?CTT=5&origin=HA104122363

「設定」テクニックを教えてくれた Matt Masson に心から感謝します。

http://www.mattmasson.com/2014/04/defining-configurable-settings-for-your-queries/

注: Power Query は CSV ファイルから直接読み取ることができます。これは、「入力データ」クエリのソースとして使用されます。おそらく最も簡単な方法は、そのクエリを削除し、CSV ファイルから新しいクエリを作成して「入力データ」という名前を付けることです。リソースを節約するには、[ワークシートに読み込む] オプションをオフにします。

関連情報