Question

On google pages, 我有一个 p方案,更新4栏,每天有97列数据。在第5栏中,我有一个用于跟踪销售的公式,但我不得不人工冲积或点击公式,以便在新的数据行中实现自动化。

I have tried using the ArrayFormula() function but since I am using Vlookup to search the name of the location in each individual row it is not working since the cell reference is not being dragged down with the formula. Is there another alternative that could be made in order to automatically fill in the extra rows without having to open the google sheet?

Vlookup Program: =IF(B2<VLOOKUP(A2,A3:D194, 2, FALSE), 0, B2-VLOOKUP(A2,A3:D194, 2, FALSE)

轨道设计: = ArrayFormula(IF(B2:B<VLOOKUP(A2:A, A3:D194, 2, FALSE), 0, B2:B-VLOOKUP(A2:A,A3:D194, 2, FALSE)

前线数据将照此办理,并重复不同地点的名称。

Location Name	Quantity	Date	Time	Daily Sales
Springville	38	27/07/2023	19:12	1
Willow Creek	37	27/07/2023	19:12	4
Pineview	33	27/07/2023	19:12	3
Lakeside	30	27/07/2023	19:12	1
Clearwater	29	27/07/2023	19:12	0
Oakville	29	27/07/2023	19:12	1
Meadowbrook	28	27/07/2023	19:12	1
Cedar Ridge	27	27/07/2023	19:12	0
Maplewood	27	27/07/2023	19:12	0
Riverdale	27	27/07/2023	19:12	1
Forest Hills	26	27/07/2023	19:12	0
Sunnyside	26	27/07/2023	19:12	0
Greenwood	26	27/07/2023	19:12	1
Lakeview	25	27/07/2023	19:12	0
Sunset Heights	25	27/07/2023	19:12	2
Laurel Hill	24	27/07/2023	19:12	1
Briarwood	23	27/07/2023	19:12	1
Oakwood	22	27/07/2023	19:12	1
Cedar Heights	21	27/07/2023	19:12	16
Pinecrest	21	27/07/2023	19:12	0
Mountain View	21	27/07/2023	19:12	2
Willowbrook	19	27/07/2023	19:12	0
Maple Valley	19	27/07/2023	19:12	0
Riverside	19	27/07/2023	19:12	1
Lakefront	19	27/07/2023	19:12	1

Answer 1

You can use MAP and the LAMBDA function.

=MAP(A2:A, B2:B, LAMBDA(a, b, 
  IF(b<VLOOKUP(a,A3:D194,2,FALSE),0,
     b-VLOOKUP(a,A3:D194,2,FALSE))))

Answer 2

既然你们有两条价值观,你可以尝试使用地中海行动计划:

=MAP(A2:A,B2:B,LAMBDA(aval,bval,IF(aval="","",IF(bval<VLOOKUP(aval, A3:D194, 2, FALSE), 0,bval-VLOOKUP(aval, A3:D194, 2, FALSE)))))

您还可以在“彩虹”的帮助下简化综合框架声明:

=MAP(A2:A,B2:B,LAMBDA(aval,bval,IF(aval="","",MAX(0,bval-VLOOKUP(aval, A3:D194, 2, FALSE)))))

Answer 3

在采用阵列公式时,可以提出一系列论点,即如果违约期望有范围,则不会使其转移到下行。这里,你可以尝试的是:

=MAP(A2:A,B2:B,LAMBDA(α,b,IF(α="",,LET(vl,VLOOKUP(α,OFFSET(α,1,,9^9,2),2,0),IF(b<vl,0,b-vl)))))

友情链接