☰

從群組中的每一行的其他列填充列

tag-icon • tag-icon microsoft-excel
從群組中的每一行的其他列填充列

我正在進行資料遷移練習，並且 as/sheet 超過 100K 行，因此我需要一個手動更新之外的解決方案。

為了這篇文章，我將簡化問題：我有一個包含三列（A、B 和 C）的 as/sheet，其標題 [資料類型] 如下：名稱 [文字]、ID [數字]、CreateDate [日期] ]。資料已在 A 列上排序，以故意使該列中的重複值成為焦點。 ID是唯一的；日期是不是獨特的。

在範例中，有九行可以根據 A 列中的重複值「分組」為三組abc。列中的值是def，第 7-10 行是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 列中的陣列公式來完成此操作。

事實證明，接下來的任務很困難。現在我已經確定了每個行組的最新日期，我想將與該最新日期相對應的 ID 放入 E 列（在行組的每一行上），以便結果看起來像 E 列下面的範例。但我需要用公式/函數來完成，而不是手動。這是一個遷移項目，所以它不必很漂亮。

將 E 列中的匹配 ID 放在同一行中很容易（IF 語句） - 請參閱第 3、6 和 9 行 - 但我並不假裝這就是解決方案。但我無法弄清楚如何使用相同的 ID 填充每個組中的其他行 - 在我所附的範例中，這將是第 2、4 和 5、7 和 8 行以及 10 行。在C 欄中，因為這些值不是唯一的。經過各種公式的徒勞嘗試後，我不知所措。我的猜測是它需要依賴 C 列（匹配值）以及諸如INDEXand 之類的函數MATCH......或者可能是 VBA。

     +------+------+------------+------------+-----------------+
     |  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：

=偏移(C2,匹配(D2,C2:C,0)-1,-1)

將其放入 E2 單元格中，然後向下拖曳至整個 E

答案3

B對於給定的行（例如，第 2 行），您想要從行中 尋找 ID（列 ）n（即 Cell  ），其中名稱 ( ) 等於目前行 ( ) 中的名稱，CreatedDate ( ) 等於目前行 ( ) 中的最新日期。排 BnAnA2CnD2n是唯一的，因為 ID 是唯一的，所以“這” row 是最大行。一個合乎邏輯的答案是對 Column 公式的小擴展 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

請注意，這適用於非唯一的 CreatedDates。如果 CreatedDates 的順序與 ID 的順序不同，它甚至可以工作，如上面的範例（其中資料按 Columns A和 排序B）。

相關內容