Excel 怎么用 XLOOKUP 按商品编号查单价?
在 Cue Office 打开 Excel 采购单(.xlsx),用 XLOOKUP 到价目表精确匹配商品编号,再把同一行的单价填回采购单。
按商品编号回填单价
在 Purchase Order 工作表中选中 C2。这一行要查的编号在 A2。像 0042 这种开头带零的编号,两张表里都要存成文本。
先试一条时,只粘贴下方第一行公式。想一次填好 C2:C6,就复制五行公式,选中网格里的 C2 后粘贴。
=XLOOKUP(A2,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0) =XLOOKUP(A3,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0) =XLOOKUP(A4,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0) =XLOOKUP(A5,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0) =XLOOKUP(A6,'Price List'!$A$2:$A$5,'Price List'!$C$2:$C$5,"Not found",0)
公式先到 Price List!A2:A5 查编号,再从 Price List!C2:C5 取同一行的单价。两个范围覆盖价目表的相同行,所以找到第几条编号,就取第几条价格。每行只换采购单的编号引用(A2、A3……),价目表的两个范围用 $ 固定。
公式最后的 0 只接受精确匹配,找不到时显示 Not found。看到这条提示,先回价目表找这个编号。如果一边是文本 0042、另一边是数字 42,先把两边的编号统一为保留前导零的文本。若编号刚加到第 6 行,还要把查找范围和单价范围一起扩到第 6 行。
核对缺失编号与改价结果
粘贴公式后,保存工作簿并重新打开,检查 Purchase Order 工作表 C2:C6 的回填数值:编号 0042 对应的 C2 与 C5 均显示为 4.25,0047 显示为 8.50,0060 为 1.80;而 0099 由于价目表中未收录,如期显示为 Not found。
想知道改价后采购单会不会跟着变,可以另存一份副本,把 Price List!C2 从 4.25 改成 5.75。切回采购单,检查两笔 0042 是否都更新,0099 是否仍显示 Not found。
让 Cue AI 按编号补单价
收到新采购单时,可以把查价这一步交给 Cue AI。在原始工作簿的副本里,从 Home 打开 Cue AI,告诉它去哪张表找价格、把结果填到哪里。
能帮我按价目表把这张采购单的单价补上吗?如果价目表改价,采购单里也要跟着更新。请按商品编号精确匹配,保留编号开头的零。把结果填到 Purchase Order 的 C2:C6,价目表里没有的编号写成“Not found”,工作簿的其他地方保持原样。
Cue AI 写入后,保存并重开副本。点 C2,在公式栏检查它写入的公式。本例用 VLOOKUP 做精确匹配,并用 IFERROR 显示缺价提示。再确认 C4 显示 Not found,编号前导零和采购数量仍在。
再另存一份副本测试调价。把 Price List!C2 从 4.25 改成 5.75 后切回采购单。本例两笔 0042 都更新了,0099 仍显示 Not found。
用社区修缮采购单练习
展开完整案例
社区修缮小组用这两张表记录采购数量和单价。在同一工作簿建立 Purchase Order 和 Price List 两张表。粘贴前,分别点两张表的 A 列标,在 Home 的数字格式下拉菜单里选 Text,保留编号开头的零。再从两张表的 A1 分别粘贴对应的表头和数据。表名只用来区分两组数据。粘贴后确认编号仍显示为 0042、0047 等四位字符。
Purchase Order Item ID Quantity Unit price 0042 3 0047 2 0099 1 0042 4 0060 2 Price List Item ID Item Unit price 0042 Door hinge 4.25 0047 Sealant 8.50 0060 Wall anchor 1.80 0061 Brush 3.15
把查到的单价算成采购金额
可以请 Cue AI 给采购单加一列金额,把数量乘以单价,并列出还需向供应商询价的编号。先抽查第一笔 0042,3 件乘以 4.25 应是 12.75。像 0099 这样仍然缺价的行,等拿到报价再计入预算。
收到供应商的新报价后,在 Cue Office 免费打开 PDF(.pdf)核对商品编号和价格,再更新 Excel(.xlsx)价目表。要把预算交给审批人,可以用免费的编辑工具补齐 Word(.docx)申请或 PowerPoint(.pptx)汇报。 免费下载 Cue Office