☰

グループ内の各行に他の列から列を入力する

tag-icon • tag-icon microsoft-excel
グループ内の各行に他の列から列を入力する

私はデータ移行作業を行っており、10 万行を超えるシートがあるため、手動更新以外のソリューションが必要です。

この投稿では問題を単純化します。シートには3つの列（A、B、C）があり、見出し[データタイプ]は次のようになっています：名前[テキスト]、ID[数値]、作成日[日付]。データは列Aで並べ替えられており、意図的にその列の重複値に焦点を当てています。IDは一意です。日付はない個性的。

この例では、列 A の重複した値によって 3 つのグループに「グループ化」できる行が 9 つあります。したがって、行 2 と 3 の場合、列 A の値はabc、行 4、5、6 の場合、列 A の値はdef、行 7 ～ 10 の場合、列 A の値は ですghi。

最初のタスクは、各「行グループ」の最新の CreateDate を特定することです。したがって、この例では、行 2 と 3 は 5/11/1999、行 4 から 6 は 3/12/2001、行 7 から 10 は 5/11/1999 になります。これは、{=MAX(IF(A2=$A:$A,$C:$C))} 列 D の配列数式を使用して行います。

次のタスクは難しいことが分かりました。各行グループの最新日付を決定したので、その LatestDate に対応する ID を列 E (行グループの各行) に配置して、結果が以下の例の列 E のようになるようにします。ただし、これは手動で行うのではなく、数式/関数を使用して行う必要があります。これは移行プロジェクト用なので、見た目をきれいにする必要はありません。

一致する ID を同じ行の列 E に配置するのは簡単です (IF ステートメント) - 行 3、6、9 を参照してください - しかし、これが解決策であるとは主張しません。しかし、各グループの他の行に同じ ID を設定する方法がわかりません。添付の例では、行 2、4 と 5、7 と 8、10 になります。列 C の値は一意ではないため、参照することはできません。さまざまな数式で無駄な試みをした後、途方に暮れています。私の推測では、列 C (一致する値) と、などの関数、または VBA に頼る必要があると思いINDEXます MATCH。

     +------+------+------------+------------+-----------------+
     |  A   |   B  |     C      |     D      |        E        |
+----+------+------+------------+------------+-----------------+
|  1 | Name |  ID  | CreateDate | LatestDate | CorrespondingID |
|  2 | abc  |   1  | 4/12/1998  | 5/11/1999  |         2       |
|  3 | abc  |   2  | 5/11/1999  | 5/11/1999  |         2       |
|  4 | def  |   3  | 1/12/1999  | 3/12/2001  |         5       |
|  5 | def  |   4  | 5/11/1999  | 3/12/2001  |         5       |
|  6 | def  |   5  | 3/12/2001  | 3/12/2001  |         5       |
|  7 | ghi  |  17  | 1/17/1999  | 5/11/1999  |        55       |
|  8 | ghi  |  42  | 2/4/1999   | 5/11/1999  |        55       |
|  9 | ghi  |  55  | 5/11/1999  | 5/11/1999  |        55       |
| 10 | ghi  |  83  | 3/28/1999  | 5/11/1999  |        55       |
+----+------+------+------------+------------+-----------------+

(より短い例は画像としてご利用いただけます。)

答え1

私の参考文献はここTLDR: インデックス一致式の非配列バージョンを使用します。

E2に以下を入力します:

=INDEX(B:B,MATCH(1,INDEX((A2=A:A)*(D2=C:C),0,1),0))

アイデア: 内部の index() は、名前と日付の両方の条件に一致する 0 と 1 のリストを生成します。次に、外部の index() を使用して、A:A から名前を「ロード」します。

答え2

このソリューションでは、オフセット マッチ メソッドを使用します。マッチを使用して日付を検索し、日付に基づいてオフセットして対応する ID を検索します。

=OFFSET(C2,MATCH(D2,C2:C,0)-1,-1)

これをE2セルにドロップし、E全体にドラッグします。

答え3

B特定の行（例えば行2）について、行から ID（列）を見つけたいとします。 ん(つまり、セル ) で、名前 ( ) は現在の行 ( ) の名前に等しく、CreatedDate ( ) は現在の行 ( ) の LatestDate に等しくなります。行 BnAnA2CnD2んIDが一意であるため、の” 行は最大行です。論理的な答えは、列の数式を少し拡張することです D。

=MAX(IF(AND(A2=$A:$A,D2=$C:$C), $B:$B))

残念ながら、AND配列数式では機能しないようです。そこで、標準的なトリックを使用します。 TRUE = 1（またはゼロ以外の値）と FALSE = 0 を使用すると、AND乗算でシミュレートできます（AND(TRUE,TRUE) = TRUE は 1 × 1 = 1 と同じ、  = は  0 × と同じ AND(FALSE,anything)FALSE何でも = 0）。したがって、上記を次のように変更します。

=MAX(IF((A2=$A:$A)*(D2=$C:$C), $B:$B))

(もちろん、Ctrl+ Shift+を使用した配列数式として入力します):Enter

Aこれは、一意でない CreatedDates でも​​機能することに注意してください。上記の例 (データが列と で並べ替えられている) のように、CreatedDates が ID と同じ順序になっていない場合でも機能します B。

関連情報