EXCEL材料运费表格的设计思路及函数使用技巧

原创 kaixinit  2018-11-25 14:51  阅读 1,514 次
运输单位编码 发货单位编码 物资名称编码 运输单位 运输车号 运输起止期 发货单位 物资名称 运输车数(车) 运输数量(吨) 单价(元/吨) 运输金额(元) 应付运费小计 加公司柴油 用公司配件 实付运费 付款金额(元) 欠款金额(元)

1.运输单位、发货单位、物资名称(编码):=OFFSET('数据源 (运费)'!$H$2:$H$50,,,COUNTA('数据源 (运费)'!$H$2:$H$50),)

备注:数据验证选择序列,然后输入公式,这样做的目的是防止有空白格下拉选项

2.运输单位、车号、发货单位:=IFERROR(VLOOKUP($A2,'数据源 (运费)'!$H$2:$L$50,2,0),"")

备注:

返回函数文本值,忽略N/A错误:

=IFERROR(VLOOKUP($A2,'数据源 (运费)'!$H$2:$L$50,2,0),"")

返回函数数值,忽略N/A错误,如果没有数值返回内容为数值0:(如果源数据为空白,返回值为0,解决方法为:源数据值 空格键空格一次即可解决)

=IFERROR(VLOOKUP($A2,'数据源 (运费)'!$H$2:$L$50,2,0),"0")

3.运输车数(计数):=SUMPRODUCT(('003'!$D:$D>=F2)*1,('003'!$D:$D<=G2)*1,('003'!$F:$F=I2)*1,('003'!$O:$O=E2)*1)

4.运输数量、金额(合计):=SUMPRODUCT(('003'!$D:$D>=F2)*1,('003'!$F:$F=I2)*1,('003'!$D:$D<=G2)*1,('003'!$O:$O=E2)*1,('003'!$H:$H))

5.如果单元格为0的显示为空白,且小数点保留后两位,选中区域,右键设置单元格格式,选择自定义,将其改为 [=0]"";#,##0.00,函数公式设置还会显示0,如果这样可以这样写入:=IFERROR(VLOOKUP($C2*1,数据源!$H$2:$L$50,5,0),"0"*1)  或者直接输入=IFERROR(VLOOKUP($C2*1,数据源!$H$2:$L$50,5,0),0),带引号"0"代表文本值0,不带引号0代表数值0。

6.文本替换的技巧,=H3*1*Q3*1,=IFERROR(VLOOKUP($C3*1,数据源!$H$2:$L$50,2,0),""),*1的目的是为了取绝对值,方便替换!

7.数据透视后的数据表针对平均单价问题,因为透视的问题,进行计算字段,金额/数量,除了车数,其他计算项单元格式设置为: [=0]"";#,##0.00 ,这样单元为0的不显示(欠款金额和采购数量、运输数量不建议使用,采取标准的数值显示方式)。

8.条件格式设置行和列颜色

一、条件格式中包含某一文字,整行标注颜色

选定需要标注范围的列和行,然后开始-条件格式-新建规则-使用公式确定-输入公式: =COUNTIF($G1,"*暂估*"),这个表明G列内包含暂估文字的内容,格式,选择颜色进行标注即可

二、条件格式中特定文字,整行标注颜色

选定需要标注范围的列和行,然后开始-条件格式-新建规则-使用公式确定-输入公式: =$G1="暂估",这个表明G列内绝对值:暂估,文字的内容,格式,选择颜色进行标注即可

三、使用格式刷让整个条件格式快捷应用到其他表内

开始-格式化-选定需要应用的列和行如A-J列,点击格式刷,选择其他表,选择A-J列即可完成。

参考:http://www.kaixinit.com/info/office/1269.html

 

本文地址:http://www.kaixinit.com/info/office/1184.html
版权声明:本文为原创文章,版权归 kaixinit 所有,欢迎分享本文,转载请保留出处!

发表评论


表情