简化公式

dsyxr903

Worksheets("销售单").[e5].Value = "=IF(SUMIFS(客户!D:D,客户!B:B,C$2,客户!C:C,K5)=0,IF(ISERROR(VLOOKUP(B5,基本资料!A:D,4,0)),0,VLOOKUP(B5,基本资料!A:D,4,0)),SUMIFS(客户!D:D,客户!B:B,C$2,客户!C:C,K5))"
Worksheets("销售单").[e6].Value = "=IF(SUMIFS(客户!D:D,客户!B:B,C$2,客户!C:C,K6)=0,IF(ISERROR(VLOOKUP(B6,基本资料!A:D,4,0)),0,VLOOKUP(B6,基本资料!A:D,4,0)),SUMIFS(客户!D:D,客户!B:B,C$2,客户!C:C,K6))"
Worksheets("销售单").[e7].Value = "=IF(SUMIFS(客户!D:D,客户!B:B,C$2,客户!C:C,K7)=0,IF(ISERROR(VLOOKUP(B7,基本资料!A:D,4,0)),0,VLOOKUP(B7,基本资料!A:D,4,0)),SUMIFS(客户!D:D,客户!B:B,C$2,客户!C:C,K7))"
Worksheets("销售单").[e8].Value = "=IF(SUMIFS(客户!D:D,客户!B:B,C$2,客户!C:C,K8)=0,IF(ISERROR(VLOOKUP(B8,基本资料!A:D,4,0)),0,VLOOKUP(B8,基本资料!A:D,4,0)),SUMIFS(客户!D:D,客户!B:B,C$2,客户!C:C,K8))"
Worksheets("销售单").[e9].Value = "=IF(SUMIFS(客户!D:D,客户!B:B,C$2,客户!C:C,K9)=0,IF(ISERROR(VLOOKUP(B9,基本资料!A:D,4,0)),0,VLOOKUP(B9,基本资料!A:D,4,0)),SUMIFS(客户!D:D,客户!B:B,C$2,客户!C:C,K9))"

求高手，用最笨的方法写了个公式，能不能简化一下，其实还有10行

dsyxr903

补充说明一下，就是E5单元格下拉后，E6,E7到E19里的边的公式

半百

Sub TEST()
For I = 5 To 19
Worksheets("销售单").Cells(I, "E").Value = "=IF(SUMIFS(客户!D:D,客户!B:B,C$2,客户!C:C,K" & I & ")=0,IF(ISERROR(VLOOKUP(B" & I & ",基本资料!A:D,4,0)),0,VLOOKUP(B" & I & ",基本资料!A:D,4,0)),SUMIFS(客户!D:D,客户!B:B,C$2,客户!C:C,K" & I & "))"
Next
End Sub

dsyxr903

半百 发表于 2019-9-20 12:34
Sub TEST()
For I = 5 To 19
Worksheets("销售单").Cells(I, "E").Value = "=IF(SUMIFS(客户!D:D,客户!B: ...

太棒了，谢谢老师