如何在B列的公式中加入IF

hellenlxy

请教各位老师 ，

如何在出货清单一个月结束后自动显示本月合计和累计的金额,即在B列的公式是加入IF(若单元格的月份大于上一个单元格的月份时为零)
=IF($C4="本月合计","",IFERROR(INDEX(出货!D:D,SMALL(IF(出货!$Q$2:$Q$9280=$J$3,ROW($2:$3987)),ROW(B1))),""))

橒♂蝣

=IFERROR(REDUCE("",UNIQUE(出货!C2:C218),LAMBDA(m,n,LET(
k,HSTACK("客户:",n," ","联系人："," "," ","电话: "," ","对帐单号",n),
j,REDUCE({"日期","货  号","名  称","规  格","数量","单价","金额","单号","每单金额","收款金额"},UNIQUE(MONTH(出货!B8:B69)),LAMBDA(x,y,LET(
      i,FILTER(HSTACK(出货!B2:B218,出货!D2:D218,出货!E2:K218,出货!O2:O218),(MONTH(出货!B2:B218)=y)*(出货!C2:C218=n),""),
      VSTACK(x,i,HSTACK("","","本月合计","",IFERROR(SUM(INDEX(i,,5)),0),"",IFERROR(SUM(INDEX(i,,7)),0)))))),
VSTACK(m,VSTACK(k,j,HSTACK("","","累计","",IFERROR(SUM(INDEX(j,,5))/2,0),"",IFERROR(SUM(INDEX(j,,7))/2,0))))))),"")

hellenlxy

橒♂蝣 发表于 2024-3-13 00:07
=IFERROR(REDUCE("",UNIQUE(出货!C2:C218),LAMBDA(m,n,LET(
k,HSTACK("客户:",n," ","联系人："," "," "," ...

能帮忙上个附件吗？公式理解不了

yellowhawk

这个操作用VBA比较好一点，你的表格计算好像有点问题，或许是某些没有对齐 吧

hellenlxy

yellowhawk 发表于 2024-3-13 08:48
这个操作用VBA比较好一点，你的表格计算好像有点问题，或许是某些没有对齐 吧

有哪些没有对齐？对帐单表的A4列以后才有用，以上可以用页眉处理，不用公式

mykitami

A4单元格公式如下：（自动溢出，这种问题如果用公式的话，比较适合用REDUCE函数解决）

=LET(w,REDUCE(HSTACK(出货!B1,出货!D1:K1,出货!O1),SORT(UNIQUE(MONTH(出货!B2:B218))),LAMBDA(x,y,LET(a,FILTER(HSTACK(出货!B2:B21800,出货!D2:K21800,出货!O2:O21800),(出货!C2:C21800='想要的结果 (2)'!J3)*(MONTH(出货!B2:B21800)=y)),b,FILTER(HSTACK(出货!B2:B21800,出货!D2:K21800,出货!O2:O21800),(出货!C2:C21800='想要的结果 (2)'!J3)*(MONTH(出货!B2:B21800)<=y)),c,HSTACK("","","本月合计","",SUM(INDEX(a,,5)),"",SUM(INDEX(a,,7)),"","",""),d,HSTACK("","","累计","",SUM(INDEX(b,,5)),"",SUM(INDEX(b,,7)),"","",""),VSTACK(x,VSTACK(a,c,d))))),VSTACK(w,{"公司经办人","","","核对人","","","","公司——财务部   ",0,""}))

hellenlxy

mykitami 发表于 2024-3-13 11:06
A4单元格公式如下：（自动溢出，这种问题如果用公式的话，比较适合用REDUCE函数解决）

=LET(w,REDUCE( ...

看不懂公式

橒♂蝣

hellenlxy 发表于 2024-3-13 07:33
能帮忙上个附件吗？公式理解不了

请参考附件，的确是比较复杂

yellowhawk

程序只能执行一次，如果要执行第二次，则需要删除程序增加的行！
Sub 宏1()
Dim a1, b1, r1, r2
r1 = Cells(Rows.Count, "B").End(xlUp).Row
For i = 5 To r1
   If Cells(i, 1) = "" Then GoTo 10
  If Cells(i + 1, 1) = "" Then
  Rows(i + 1).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
   Cells(i + 1, 3) = "本月合计"
   Cells(i + 1, 5).Formula = "=SUM(e" & a1 & ":e" & i & ")"
   Cells(i + 1, 7).Formula = "=SUM(g" & a1 & ":g" & i & ")"
      Cells(i + 2, 3) = "累计"
   Cells(i + 2, 5) = Cells(i + 1, 5) + Cells(a1 - 1, 5)
   Cells(i + 2, 7) = Cells(i + 1, 7) + Cells(a1 - 1, 7)
   i = i + 3
  End If
  If Cells(i, 1) = "" Then GoTo 10
  If Month(Range("A" & i)) <> Month(Range("A" & i + 1)) Then
  Rows(i + 1).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
   Cells(i + 1, 3) = "本月合计"
   Cells(i + 1, 5).Formula = "=SUM(e5:e" & i & ")"
   Cells(i + 1, 7).Formula = "=SUM(g5:g" & i & ")"
      Cells(i + 2, 3) = "累计"
   Cells(i + 2, 5) = Cells(i + 1, 5)
   Cells(i + 2, 7) = Cells(i + 1, 7)
   i = i + 3
a1 = i

End If
Next
'
10
End Sub

hellenlxy

感谢各位的帮忙,只可惜都不是我想要的结果!