☰

Mac Excel で行データを列に一括変換する方法 (転置は適していません)?

tag-icon • tag-icon microsoft-excel vba
Mac Excel で行データを列に一括変換する方法 (転置は適していません)?

次のようなデータがたくさんあります。2 つの列があります。

名前 Eメール
ボブ [メールアドレス]
ボブ [メールアドレス]
ボブ [メールアドレス]
サリー [メールアドレス]
サリー [メールアドレス]

しかし、私は次のようにする必要があります。 1 行に 1 つの名前があり、必要に応じて電子メールが追加の列として追加されます。名前ごとに電子メールの数は不明です。

名前 メール 1 メール 2 メール 3
ボブ [メールアドレス] [メールアドレス] [メールアドレス]
サリー [メールアドレス] [メールアドレス]

何千行もの巨大なスプレッドシートに対してこれを実行する必要があります。そのため、転置などを使用して各行を手動で変換することはできません。一括/バッチ/自動化ソリューションが必要です。

VBA が存在することは知っていますが、それをどのようにコーディングするかはわかりません。それが解決策であれば、コードがどのようになるかについて指示をいただけませんか?

VBA 以外のソリューションも大歓迎です。

答え1

使用ミラーこの CSV から始めます:

Name,Email
bob,[email protected]
bob,[email protected]
bob,[email protected]
sally,[email protected]
sally,[email protected]

次のように実行できます:

mlr --csv nest --ivar ";" -f Email then nest --explode --values --across-fields -f Email then unsparsify input.csv >output.csv

取得する:

名前 メールアドレス_1 メール_2 メール_3
ボブ [メールアドレス] [メールアドレス] [メールアドレス]
サリー [メールアドレス] [メールアドレス] -

答え2

例から始めると、列A: 名前、列B: メールアドレス

  1. この配列数式を =INDEX($A$2:$A$6, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) 空白セル（たとえば D2）に入力し、Shift + Ctrl + Enter キーを同時に押すと正しい結果が得られます。スクリーンショットを参照してください。

ここに画像の説明を入力してください

  1. セルを下にドラッグして、列 D のすべての値を入力します。結果は上のスクリーンショットのようになります。
  2. セル内E2メールを送信したい場所にこの数式を挿入します=IFERROR(INDEX($B$2:$B$16, MATCH(0,COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2,1,0),0)),"")
  3. セルを右に 5 ～ 10 セル (または十分と思われる量) ドラッグして入力し、行を下にドラッグして各人の電子メールを入力します。

結果： ここに画像の説明を入力してください

ソース

答え3

数式を使用して補助列を追加します。

="Mail "&COUNTIF($A$2:A2,A2)

ここに画像の説明を入力してください

Power Queryエディターを開く - 選択コラム1- に行く変身-ピボット列- 選択するEメールそして以下高度なオプション選択する集約しない:

ここに画像の説明を入力してください

答え4

新しいバージョンの Excel をお持ちの場合は、これらの数式を使用できます。

次に、次のようにします。 ここに画像の説明を入力してください 青テーブルの名前は Table1。

使用される数式:

  • E3:=UNIQUE(Table1[Name])
  • F2:N2:="Mail"&COLUMN()-COLUMN($E$2)
  • F3（下に伸ばす）：=INDEX(Table1[Mail];FILTER(TRANSPOSE(SORT(UNIQUE((ROW(Table1[Name])-ROW(Table1[[#Headers];[Name]]))*($E3=Table1[Name]))));TRANSPOSE(SORT(UNIQUE((ROW(Table1[Name])-ROW(Table1[[#Headers];[Name]]))*($E3=Table1[Name]))))>0;""))

テーブルは、新しい Excel バージョンの SPILL 関数によって入力されます。

サンプルファイル:https://1drv.ms/x/s!ArVkYVSHnABFiiqzBYLPmoubvdHY?e=p7wOeF

古いバージョンの Excel を使用している場合、ほとんどの機能は配列関数を使用してエミュレートできます。ただし、混乱がさらに大きくなります...

関連情報