请教EXCEL高手

vcd123

主要目的是为了检查每次shipment 供应商invoice里面每一个产品的价格和对应的purchase order 确认的价格是否一样。有很多的PO A,B,C,D.....而每个PO里面都有很多产品，但是每个PO里面产品是不重复，假设都有a,b,c,d.....不同PO有可能有相同的产品，但相同产品的价格在不同PO里面价格可能会不一样。

请问各位高手，EXCEL 里面有什么方法可以自动抓取并检查每张invoice里显示的PO对应的产品价格跟当初PO确认的是一样的？ 因为每张invoice有很多东西，而且有很多PO#。如果每次自己找PO~然后vlookup 来检查很耗时。

求高人指点！ 谢谢啦！

有个简单的例子让大家参考，下图中Invoice，PO 1 , PO2 分别属于3个不同的EXCEL file。 想检查的是invoice里面的价格跟相对应PO里面的价格是否一致。请大家帮忙！

floodp

这种东西不举例子没人看得懂啊。

感觉是先做表，表做的合理往后就好弄。

leyan

据我理解 你就是要确认现在PO 每个产品和原来发给别人INVOICE时的价格一样？

nali

excel估计比较难，我也不会，如果你把它存成csv 文件，我倒是可以想办法的。

你挑些数据上传上来，我给你方法。

狼狼

you should have a product dababase with pricing list, and check product price with the set up price list, not check based on PO or invocie

vcd123

狼狼 发表于 2013-11-24 20:11
you should have a product dababase with pricing list, and check product price with the set up price  ...

The purchase price for those products is changing every month, we will buy them if the profit margin meets the target no matter the price is increasing or decreasing. So setting up the price list for those products will not help as the price will never be the same.

狼狼

vcd123 发表于 2013-11-24 20:36
The purchase price for those products is changing every month, we will buy them if the profit marg ...

Ｉdon't know detail so can not comment, but pricipal is to check one variable with one fixed list, if both variable then you can not check, or set up a price range, if the movement is within 95% of the price range then you say it is ok.

vcd123

nali 发表于 2013-11-24 18:31
excel估计比较难，我也不会，如果你把它存成csv 文件，我倒是可以想办法的。

你挑些数据上传上来，我给你 ...

您好，我贴了一张图，里面是简化的例子，请指教！

CDCD

不懂你说的invoice里面的价格是怎么样的格式。
但是如果invoice里面有列出PO number和product number，然后你的系统也可以生成Purchase order number 和product number。那么你可以两个spreadsheet里面vlookup PO number & product number。

如果要用formula的话就是两个spreadsheet里面都增加一个column用CONCATENATE(Purchase order number,Product number)生成一个值，然后再用这个值做为vlookup的key。

vcd123

leyan 发表于 2013-11-24 18:25
据我理解 你就是要确认现在PO 每个产品和原来发给别人INVOICE时的价格一样？ ...

你好，其实是想确认供应商的shipment invoice 里面对应PO的产品价格与之前单独确认的各个PO里产品价格是否相同。贴了一副图作为例子，请多多指教~

leyan

vcd123 发表于 2013-11-24 22:39
你好，其实是想确认供应商的shipment invoice 里面对应PO的产品价格与之前单独确认的各个PO里产品价格是 ...

因为被查询的表格是一直在变的 我目前想不出什么方法用EXCEL 能很快的检查
唯一能快一点的是做一个MASTER FILE 写一个VLOOKUP 的VBA。
需要检查的时候 把PO 和 INVOICE 复制进去 RUN VLOOKUP  可能会快一点
等等高手 看有什么解决方法

nali

1. Invoice.csv
   
2. 
   
3. Invoice,,
   
4. Product,Price,PO#
   
5. A,2.5,1
   
6. B,1.5,2
   
7. C,3.5,1
   

复制代码

1. PO-1.csv
   
2. 
   
3. PO # 1,
   
4. Product,Price
   
5. A,2.5
   
6. B,2
   
7. C,3.5
   

复制代码

1. PO-2.csv
   
2. 
   
3. PO # 2,
   
4. Product,Price
   
5. A,3
   
6. B,1.5
   

复制代码

1. awk -F , '{if (FILENAME=="Invoice.csv")
   
2.               { if (FNR<=2) print
   
3.                 else {  if ($2==b[$3 FS $1]) print $0 ",same"
   
4.                         else {print $0 ",Not same"}
   
5.                      }
   
6.               }
   
7.            else { if (FNR==1&&/PO #/)
   
8.                       {split($1,a," ")}
   
9.                   else {po=a[3];b[po FS $1]=$2}
   
10.                 }
    
11.           }' PO*.csv Invoice.csv
    
12. 
    
13. Invoice,,
    
14. Product,Price,PO#
    
15. A,2.5,1,same
    
16. B,1.5,2,same
    
17. C,3.5,1,same
    

复制代码

nali

因为windows 系统里没有awk这个命令，你需要在你的PC里安装CYGWIN (http://cygwin.com/install.html), 点那个setup-x86.exe后安装就可以了. 当然如果你有linux系统，直接就可以运行，但多半没有。

同时,你的文件要有下面的要求:
1. PO文件名存为 PO-号码.csv， invoice的文件名只能是Invoice.csv， PO 单子的文件数量没有限制。
2. Invoice.csv 和 PO 文件的格式不要变，比如都是三行后开始时数据。

不明白的，再提问。awk 的效率很高，上万条记录的话，得到结果也就1~2秒的时间。

dtmr17

Set up tables with pre-set formulas, copy and paste invoice data as values.
Lookup or Index&Match could be used.

albert_sun

很简单,用excel的vlookup就可以阿

nali

楼上好几个都说用vlookup, 我看了这个excel里的函数。 确实可以找到对应的数据。但是如果有成千上百张PO的话，难不成要一个个建vlookup 吗？

还有，后面的判断该如何处理？如果找到对应的PO价格，如何和实际的比较？

floodp

如果PO数量太多，不如建个Access数据库，一劳永逸。

chainray

这种东西用数据库就方便多了

后端用sql server

前端可以用report builder

cotta

很简单.
table 1里面都是invoice, 你先把数据清理一下,只留下PO, Price, Product. 因为你要对比price, 只用在每条line前面再加一个unique ID.
Table 1
Unique ID                                      | Product | Price | PO |
  =trim("PO") & trim("product")     | A            |  2.5    |   1  |   
这里的UID就变成"1A"

Table 2                                          |PO1|
Unique ID                                      | Product |  price |
  =trim("PO1") & trim("product")     | A            |    1  |   
这里的UID就变成"1A"
Table 3                                           |PO2|
Unique ID                                      | Product |  price|
  =trim("PO2") & trim("product")     | A            |    2  |   
这里的UID就变成"2A"

然后在table 1 里面加入vlookup price

Unique ID                                      | Product | Price | PO | vlookup price in table 2 | vlookup price in table 3
  =trim("PO") & trim("product")     | A            |  2.5    |   1  |   =vlookup(trim("PO") & trim("product"), table 2, 2, false) |  =vlookup(trim("PO") & trim("product"), table 3, 2, false)

新的table 1 就有你table 2跟table 3 的price了. 然后自行确定到底是那个price.

Unique ID  | Product | Price | PO | vlookup price in table 2 | vlookup price in table 3
1A             | A           | 2.5    | 1    |   1                                  | #N/A

nali

cotta 发表于 2013-11-25 22:16
很简单.
table 1里面都是invoice, 你先把数据清理一下,只留下PO, Price, Product. 因为你要对比price, 只用 ...

能否做个简单的excel 上传上来，就一个invoice，两个PO, 这样理解起来方便些。