|
每天一点小技能 职场打怪不得怂  编按：在面对Excel常见的查询问题时，我们首先会想到的就是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函数的特点，查找到价格最低的机型。 好啦，今天和大家分享的就是这些了！ |
|
|
