文章目录
增值税税负计算表
SUMPRODUCT、VLOOKUP函数的应用
A3=IF(B3=汇总表!C$2, MAX(A$2:A2)+1,"")
B3=MONTH(C3)
A3=IF(B3=汇总表!C$2,MAX(A$2:A2)+1,"")
C5=IFERROR(VLOOKUP(ROW(1:1),销项!A$3:E$100,5,0),0)
D5=SUMPRODUCT((销项!$B$3:$B$201=汇总表!$C$2)*(销项!$ES3:$E$201=汇总表!$C5)*(销项!G$3:G$201))
E5=SUMPRODUCT((销项!$BS3:$B$201=汇总表!$C$2)*(销项!$ES3:$E$201=汇总表!$C5)*(销项!H$3:H$201))
F5=SUMPRODUCT((销项!$B$3:$B$201=汇总表!$C$2)*(销项!$E$3:$E$201=汇总表!$C5)*(销项!I$3:I$201))
E2=IF(VLOOKUP(C2,进项留抵计算表!$AS3:SGS14,2,0)=0,"无留抵”,VLOOKUP(C2,进项留抵计算表!SAS3:$GS14,2.0))
G2=IF(VLOOKUP(C2,进项留抵计算表!SAS3:$GS14,6,0)=0,"无税金”,VLOOKUP(C2,进项留抵计算表!SAS3:SGS14,6,0))
J2=IF(VLOOKUP(C2,进项留抵计算表!SAS3:$GS14,7,0)=0,"无留抵",VLOOKUP(C2,进项留抵计算表!SAS3:$G$14,7,0))
C3=SUMPRODUCT((销项!$B$3:$B$201=A3)*销项!$I$3:$I$201)
D3=SUMPRODUCT((进项!$B$3:$B$201=A3)*进项!$I$3:$I$201)
F3=IF(C3-D3-B3+E3<0,0,C3-D3-B3+E3)
G3=-IF(C3-D3-B3+E3<0,C3-D3-B3+E3,0)
主营业务利润分析
SUMPRODUCT、VLOOKUP函数的应用
A2=IF (AND(L2>=利润分析!J$1,L2<利润分析!K$1),MAX(A$1:A1)+1,"”)
B2=IF (AND(G2>=合同额分析!J$1,G2<合同额分析!K$1),MAX(B$1:B1)+1,”"
D3=SUMPRODUCT((合同明细!G$2:G$294>=B3)*(合同明细!G$2:G$294<C3)*1)
E3=SUMPRODUCT((合同明细!G$2:G$294>=B3)*(合同明细!G$2:G$294<C3)*合同明细!G$2:G$294)
A10=IFERROR (VLOOKUP(ROW(1:1),合同明细!$B$2:$M$1000, COLUMN(B1),0),"”)
=SUMPRODUCT((合同明细!L$2:L$294>=B3)*(合同明细!L$2:L$294<C3)*合同明细!L$2:L$294)
应收账款占比分析
OFFSET函数和条件格式的应用
B2=0FFSET(应收!$A$2,ROW(1:1),7-$F$1)
B2=0FFSET(起始点,下偏移量,右偏移量)
B2=0FFSET(应收!$A$2,ROW(1:1),7-$F$1)
C2=0FFSET(收入!$A$2,ROW(1:1),7-$F$1)
B2=OFFSET(应收!$A$2,12-$F$1,ROW(1:1))
B2=0FFSET(起始点,下偏移量,右偏移量)
B2-0FFSET(应收!$A$2,12-$F$1,ROW(1:1))
C2=0FFSET(收入!$A$2,12-$F$1,ROW(1:1))
灵活的出入库统计方案
LOOKUP、SUMPRODUCT函数的应用
F7=SUMPRODUCT((明细表!A$14:A$1000>=$B$2)*(明细表!A$14:A$1000<=$B$3)*(明细表!C$14:C$1000=$E$3)*(明细表!D$14:D$1000=$E82)*明细表!$E$14:$E$1000)
H7=SUMPRODUCT((明细表!A$14:A$1000>=$B$2)*(明细表!A$14:A$1000<=$B$3)*(明细表!C$14:C$1000=$E$3)*(明细表!D$14:D$1000=SE82)*明细表!$G$14:$G$1000)
I7=SUMPRODUCT((明细表!A$14:A$1000>=$B$2)*(明细表!A$14:A$1000<=$B$3)*(明细表!C$14:C$1000=$E$3)*(明细表!D$14:D$1000=$E$2)*明细表!$H$14:$H$1000
K7=SUMPRODUCT((明细表!A$14:A$1000>=$B$2)*(明细表!A$14:A$1000<=$B$3)*(明细表!C$14:C$1000=$E83)*(明细表!D$14:D$1000=$E$2)*明细表!$J$14:$J$1000)
I7=SUMPRODUCT((明细表!A$14:A$1000>=$B$2)*(明细表!A$14:A$1000<=8B$3)*(明细表!C$14:C$1000=$E$3)*(明细表!DS14:D$1000=$E$2)*明细表!$H$14:$H$1000)
I7=SUMPRODUCT((日期>=$B$2)*(日期<=$B$3)*(卡种类=$E$3)*(面值种类-$E$2)*数据)
L7=LOOKUP(2,1/((明细表!A$2:A$1000=$B$3)*(明细表!C$2:C$1000=$E$3)*(明细表!D$2:D$1000=$E$2)),明细表!K$2:K$1000)
带有查询功能的台账
查询表
收入=SUMIF(C6:C200,"收”,D6:D200)
支出=SUMIF(C6:C200,"出”,D6:D200)
A6=IFERROR(VLOOKUP (ROW(1:1),数据表!$A$4:$G$1000,COLUMN(B1),0),””
A6=IFERROR(公式,””屏蔽错误值)
IFERROR(VLOOKUP(ROW(1:1),数据表!$A$4:$G$1000,COLUMN(B1),0),""
序号演示
A5=IF (AND(B5>=查询表!B$2,B5<=査询表!E$2),MAX(A$4:A4)+1,0)
=IF (AND(B2>=F$1,B2<=H$1),MAX (A$1:A1)+1,""
SUMIF函数
B3=SUMIF(D5:D500,"收”,E5:E500)
=SUMIF (A2:A14,"收”,B2:B14)
工程现场材料出入库管理系统
SUMPRODUCT函数的应用
H3-SUMPRODUCT((入库!C$3:C$1000=G3)*入库!$I$3:$I$1000)
H3=SUMPRODUCT((入库!供应商列=指定)*入库!金额列)
I3=SUMPRODUCT((D$3:D$1000=G3)*$ES3:$E$1000)
I3=SUMPRODUCT((供应商列=指定)*金额列)
D3=SUMPRODUCT((入库!D$3:D$1000=$B3)*(入库!E$3:E$1000=C3)*入库!$G$3:$G$1000)
E3=SUMPRODUCT((入库!D$3:D$1000=$B3)*(入库!E$3:E$1000=C3)*入库!$H$3:$H$1000
E3=SUMPRODUCT((入库!货物大类=$B3)*(入库!货物名称=C3)*入库!数据)
F3=SUMPRODUCT((出库!C$3:C$1000=B3)*(出库!D$3:D$1000=C3)*出库!$F$3:$F$1000)
G3=SUMPRODUCT((出库!C$3:C$1000=B3)*(出库!D$3:D$1000=C3)*出库!$G$3:$G$1000)
G3=SUMPRODUCT((出库!货物大类=B3)*(出库!货物名称=C3)*出库!数据)