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

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