Excel如何用采购数据填写收据,除VLOOKUP外可使用哪些函数?
我尝试使用采购数据(purchase data)填写收据(receipt),但未能成功操作,具体情况如下:
- 采购数据

以上为采购数据及各商品的价目表,我需要用这些数据填写如下格式的收据: - 收据

目前我已经通过VLOOKUP函数根据客户编号,成功将客户姓名填充到收据中,但不知道其余字段要怎么填充,要求仅填写已售商品的相关信息,请问有什么适用的函数和操作技巧?
解决方案
以下为Excel环境下的适用函数和操作方法,可按需选择:
前提约定
为方便公式说明,先统一变量:
- 采购数据存放于
采购表工作表,数据范围为A2:F1000,列顺序依次为:客户编号、客户姓名、商品ID、商品名称、单价、购买数量 - 收据存放于
收据工作表,已填充的客户编号存放在B1单元格
1. 已购商品批量填充(仅显示该客户购买的商品,自动过滤未售商品)
- 如果你使用的是365/2021及以上版本Excel,直接用
FILTER函数,在收据的第一个商品名称单元格输入公式:=FILTER(采购表!D2:F1000,采购表!A2:A1000=收据!B1,"该客户无购买记录")
输入后按回车即可自动溢出填充该客户对应的所有商品名、单价、购买数量,无需下拉,自动过滤无购买记录的商品。 - 如果你使用的是2019及更早版本Excel,用
INDEX+SMALL+IF数组组合公式,在第一个商品名称单元格输入:=INDEX(采购表!D:D,SMALL(IF(采购表!A$2:A$1000=收据!B$1,ROW($2:$1000),99999),ROW(A1)))&""
输入后按Ctrl+Shift+Enter确认数组公式,下拉公式直到出现空白,即可得到该客户所有购买的商品名称。单价、数量列可参照该公式修改引用的列号即可。
2. 商品金额填充
对应商品行的金额单元格直接做乘法计算即可,例如商品名称在第4行B列、单价C列、数量D列,金额列E4的公式为:=C4*D4,下拉填充即可。
3. 总金额汇总
直接用SUMIF匹配客户编号求和即可,公式:=SUMIF(采购表!A:A,收据!B1,采购表!G:G)
其中采购表G列为采购表里提前计算好的单条商品金额,也可以直接对收据里的金额列求和:=SUM(E4:E20),根据你收据的行数调整范围即可。
实用技巧
可以给收据的商品填写区域设置条件格式:选中所有商品行,新建条件格式,规则为=B4=""(对应你商品名列的首个单元格),格式设置为无边框,即可让空白的商品行自动隐藏边框,打印出来更整洁。
内容的提问来源于stack exchange,提问作者momo1108
相关产品推荐
相关产品推荐

