如何使用arrayformula引用以前的匹配记录? [英] How to reference to previous matching record using arrayformula?

问题描述

我有一个项目电子表格，项目经理输入每个月有活动的每个项目的当前进度状态.因此，列A B和C都已填充，我需要能够使用arrayformula找出列D. (请注意，2019年6月项目1"没有任何活动/记录)

I have a project spreadsheet, with project managers entering current progress status for each project for each month where there was some activity. So the columns A B and C are filled, I need to be able to figure out the column D using arrayformula. (note that there was no activity/record for "Project 1" in June 2019)

我想我首先需要一个帮助器列来找到该项目的上一个记录的日期，然后vlookup该项目的日期并返回该日期的进度.但是，当我试图弄清该项目以前的记录的日期时，我陷入了困境，但是我有点陷入困境.也许这里有人可以帮助我处理我应该在D2中输入的arrayformula以获得值D2:D6

I thought I would first need a helper column to find the date of the previous record for the project and then vlookup the date for the project and return the progress of that date. But I got stuck when trying to figure out the date for the previous record for the project, but I'm sort of stuck. Maybe someone here can help me with the arrayformula I should enter in D2 in order to get the values D2:D6

https://docs.google.com/spreadsheets/d/1DM8ZK6FwBFAmqUg5bv3o-aTIDtVKKMfGRXc2g9SSYgw/edit?usp = sharing

推荐答案

尝试:

=ARRAYFORMULA(IF(A2:A<>"", C2:C-IFNA(VLOOKUP(
 COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B))&B2:B, {
 COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B))+1&B2:B, C2:C}, 2, 0)), ))

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A&B2:B, {SORT(A2:A)&INDEX(SORT(A2:B, 1, 1, 2, 1),,2), 
 IF(A2:A<>"", INDEX(SORT(A2:C, 1, 1, 2, 1),,3)-IFNA(VLOOKUP(
 COUNTIFS(INDEX(SORT(A2:B, 1, 1, 2, 1),,2), INDEX(SORT(A2:B, 1, 1, 2, 1),,2), 
 ROW(B2:B), "<="&ROW(B2:B))&INDEX(SORT(A2:B, 1, 1, 2, 1),,2), {
 COUNTIFS(INDEX(SORT(A2:B, 1, 1, 2, 1),,2), INDEX(SORT(A2:B, 1, 1, 2, 1),,2), 
 ROW(B2:B), "<="&ROW(B2:B))+1&INDEX(SORT(A2:B, 1, 1, 2, 1),,2), 
 INDEX(SORT(A2:C, 1, 1, 2, 1),,3)}, 2, 0)), )}, 2, 0)))

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A&B2:B&C2:C&D2:D&E2:E; {
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;1)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5)\ IF(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;6)<>""; 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;6)-IFNA(VLOOKUP(IF(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)<>""; COUNTIFS(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); ROW(K2:K); "<="&ROW(K2:K)); )&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); {IF(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)<>""; COUNTIFS(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); ROW(K2:K); "<="&ROW(K2:K))+1; )&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5)\ 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;6)}; 2; 0)); )}; 2; 0)))

这篇关于如何使用arrayformula引用以前的匹配记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！