如何用Excel选择不同型号的设备来实现成本管控

作者：EXCEL应用之家来源：部落窝教育发布时间：2021-01-25 18:09:52点击：2875

分享到:

收藏收藏人气：0人

版权说明：原创作品，禁止转载。

编按：成本管控对于每一家公司来说都是至关重要的事！所以，员工如何在数据筛选中查找到最优的目标数据，实现成本控制，是领导们很欣赏的特质。在此类工作中，我们最常用的查询函数是Vlookup，但是殊不知多条件查找的最强王者还是我们的查询老大哥——Lookup函数！下面，小E给大家带来的就是它在实际工作中的精彩运用……

小伙伴们，今天和大家分享一个机械加工中选型设备的问题。

机加工领域，机加工设备的价值会分摊到每一个产出的零件中。随着使用年限的增加，分摊的成本会逐渐降低的。这具体表现在，相同规格型号的新旧两台设备在加工同样工艺的零件时，分摊到零件中的成本是不同的。

下面就是某加工厂的一部分设备清单。

工厂的技术员工会根据零件的要求选择不同型号的设备来生产。现有一批工件，需要选择起步价最低的机器来完成生产。具体的要求如下：

1. A列中含“铝”字的必须要用“机型属性”列中含“铝”字的机器

2. B列C列的规格必须小于或等于“长上限”列和“宽上限”列的规格

3. 根据A列中的材料及B列和C列中的规格，在“起步价”列中查找起步价最低的机型

在单元格D2中我们输入公式“=LOOKUP(,0/FREQUENCY(1%,I$3:I$18%%%+(H$3:H$18<>COUNTIF(A2,"<>*铝*"))%-(B2>F$3:F$18)-(C2>G$3:G$18)),J$3:J$18)”并向下拖曳即可。

函数解析：

总体上讲，这个题目是：求满足一定条件下的最小价格。限定条件有哪些呢？

- 长和宽的尺寸有限制

- 一部分设备对加工材料有限制

对于这种多条件下的查询问题，LOOKUP函数处理起来非常方便。

1. (B2>F$3:F$18)和(C2>G$3:G$18)部分，对不符合长和宽的尺寸要求的数据行做一个判断。他们返回的结果就是由TRUE和FALSE组成的数组。在后面的计算中分别减去了(B2>F$3:F$18)和(C2>G$3:G$18)，即表示将不符合尺寸要求的数据行排除在外。

2. COUNTIF(A2,"<>*铝*")部分，对材料中是否是铝合金做一个判断。是铝合金的，COUNTIF(A2,"<>*铝*")部分返回数字0；不是铝合金的，COUNTIF(A2,"<>*铝*")部分返回数字1。

3. (H$3:H$18<>COUNTIF(A2,"<>*铝*"))%部分，对机型做一个判断。如果是铝合金，则返回适合加工铝合金的机型设备；反之，则返回所有的设备。完成后，将结果缩小100分之一。

4. I$3:I$18%%%部分，将价格缩小1000000分之一。为什么是10^6次方分之一呢？首先因为我们要找的是最小的价格，在经肉眼观察出起步价最长的位数是4位后，可以得知价格要缩小足够的大小才能让价格信息出现在合并数据的最右侧。而且，(H$3:H$18<>COUNTIF(A2,"<>*铝*"))%这部分已经缩小了100分之一，所以I$3:I$18%%%部分缩小10^6分之一后才能使价格信息出现在数据的最右侧。

5. I$3:I$18%%%+(H$3:H$18<>COUNTIF(A2,"<>*铝*"))%-(B2>F$3:F$18)-(C2>G$3:G$18)这几部分合并后既有正数，也有负数。正数所对应的长和宽的尺寸符合要求的设备。其计算结果为{-0.9897;-0.9995;-0.9996;-0.9995;-0.9995;0.0106;0.0105;0.0007;0.0111;0.0013;0.0012;0.0115;0.0017;0.0016;0.0118;0.0017}。

6. 利用FREQUENCY函数对1%在上述区间内计频，在0.0105对应的位置上计频1，计算结果是{0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0}。

7. 0/FREQUENCY部分返回结果{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}。

8. 利用LOOKUP函数的特点，查找到价格最低的机型。

好啦，今天和大家分享的就是这些了！

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：