☰

Excel: 列は同じだが行が異なるテーブルを別のテーブルに追加する

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
Excel: 列は同じだが行が異なるテーブルを別のテーブルに追加する

Excel でテーブルを別のテーブルに追加するのに何時間も苦労しました。VLOOKUP のバリエーションを 1000 種類試しましたが、うまくいきませんでした。列 (メトリック) はすべてのテーブルで同じですが、行は異なります (メンバーがドロップされています)。新しい列を古いテーブルに追加して、すべての年を網羅した幅の広いテーブルを作成したいと考えています。

表1（2017年）

|member|revenue|sales|profit|
|   1  |  10   |  20 |  10  |
|   2  |  10   |  20 |  10  |
|   3  |  10   |  20 |  10  |
|   4  |  10   |  20 |  10  |
|   5  |  10   |  20 |  10  |
|   6  |  10   |  20 |  10  |
|   7  |  10   |  20 |  10  |

表2（2018年、一部メンバーが脱退）

|member|revenue|sales|profit|
|   1  |  40   |  60 |  80  |
|   3  |  40   |  60 |  80  |
|   4  |  40   |  60 |  80  |
|   6  |  40   |  60 |  80  |
|   7  |  40   |  60 |  80  |

結果: 表2のすべての列を表1に追加します

2017                         2018
|member|revenue|sales|profit|revenue|sales|profit|
|   1  |  10   |  20 |  10  |  40   |  60 |  80  |
|   2  |  10   |  20 |  10  |  NA   |  NA |  NA  |
|   3  |  10   |  20 |  10  |  40   |  60 |  80  |
|   4  |  10   |  20 |  10  |  40   |  60 |  80  |
|   5  |  10   |  20 |  10  |  NA   |  NA |  NA  |
|   6  |  10   |  20 |  10  |  40   |  60 |  80  |
|   7  |  10   |  20 |  10  |  40   |  60 |  80  |

答え1

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

注意

この配列数式をセルのE218右側に入力し、 を押してF2終了しますCtrl+Shift+Enter。次に、セルを下方向に入力して結果を取得します。

{=IFERROR(VLOOKUP(A218,$A$210:$D$215,{2,3,4},FALSE),"No Match")}

使い方：

  • {2,3,4}2列目から4列目を順番に読みます配列定数は、配列数式で、単一の値ではなく、複数の値を一度に操作するためによく使用されます。
  • 必要に応じて数式内のセル参照を調整します。
  • No Matchを に 置き換えることができますblanks。

答え2

@Rajesh S が提案した解決策が機能するはずです。まず、該当する 3 つのセルをすべて 1 行で選択し、数式バーに配列数式を入力します。この方法では、配列数式の {2,3,4} が水平配列の 3 列に分散されます。

配列数式に慣れていない場合や、テーブルが 2 つしかない場合は、VLOOKUP で COLUMN()-COLUMN(2 列後ろの固定参照) を操作するだけで、2、3、4 を 1 回だけ生成し、2 つのテーブルを結合することができます。

さらに多くのテーブルを上下に積み重ねる必要がある場合、たとえば将来的に 2019、2020 などを上下に追加する可能性がある場合は、以下のソリューションも検討してください。

この例では、ベース Table1 は $A$3:$D$9、Table2 は $A$11:$D$15、Table3 は $A$17:$D$21 です。

最初の行をヘルパー行として使用し、Excel の数式がセル参照を理解する方法とまったく同じように、それぞれのテーブルの Cell1:Cell2 の寸法をそこに入力してください (例: $A$11:$D$15)。

次に、E3 に次の数式を入力し、目的のセルまで下にドラッグします。

テーブルは隣り合って結合されます。将来、テーブルを追加する場合は、必要に応じてヘルパー行にそれぞれのディメンションを配置するだけです。

VLOOKUP のシーケンス 2、3、4 を生成するための列番号操作は、数式を列 E から開始する場合にのみ機能します。他の列から開始する場合は、シーケンス 2、3、4 を正しく再生成するために数式の変更が必要になります。

=IFERROR(VLOOKUP($A3,INDIRECT(E$1),IF(MOD(COLUMN(),3)=0,3,IF(MOD(COLUMN(),3)=1,4,2)),FALSE),"NA")

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

関連情報