怎么又被精准推送广告了？看看商家秒提取转化率和点击率你就明白了……

编按：来了！他又来了！新一轮的购物狂欢日又要拉开序幕了！在这个剁手的日子里，让我们看一下商家是怎么在品类繁多的Excel表中抓取商品转化率，洞悉买家的购物取向的。顺便预测一波今年双十二什么东西卖的最火爆吧~

双十一刚过去，双十二又要到啦！

相信有很多小伙伴们已经准备就绪，在双十一没抢到的货，这次绝不能错过！不仅是买家，商家们也是万事俱备，只待发货。

下面就是某宝上某家店铺最近一段时间商品的点击率和转化率的部分截图。卖家希望提取商品的品名，并抓取每个品名下的转化率，以便更好地分析客户的购买意愿和趋势。

图一

这就是一个一对多查询的经典应用。其次，我们也能运用多维引用的方法求得正确的结果。

下面，我们就一起来看看详细的介绍吧。

方法一：一对多查询

图二

在单元格E2中输入公式“=IFERROR(INDEX($B$2:$B$49,SMALL(IF(ISTEXT($B$2:$B$49),ROW($B$2:$B$49)),ROW(A1))-1),"")”，三键（CTRL+SHIFT+ENTER）回车并向下拖曳即可。

函数解析：

  • B列中即含有文本，又含有数字，因此可以用ISTEXT函数来进行判断。

  • 利用IF函数进行判断，对于那些是文本的单元格（ISTEXT函数返回结果为TRUE）则返回对应的行号。

  • 利用SMALL函数依次从小到大提取行号，作为INDEX函数的参数。

  • 利用INDEX函数依次返回清单，并利用IFERROR函数屏蔽错误。

方法二：多维引用的方法1

当你学会多维引用的方法后你会发现，之前有很多难题一下子就迎刃而解了。下面我们来看看它具体的过程。

图三

在单元格F2中输入公式“=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF(ISTEXT($B$2:$B$49),ROW($B$2:$B$49)/1%+COLUMN(B:B)*10001),ROW(A1)),4),"r0c00"),),"")”，三键（CTRL+SHIFT+ENTER）回车并向下拖曳即可。

函数解析：

  • IF(ISTEXT($B$2:$B$49),ROW($B$2:$B$49)/1%+COLUMN(B:B)*10001)部分，利用IF函数进行判断，单元格区域$B$2:$B$49中若含有文本字符串（TRUE），则把对应的行号扩大100倍（/1%），同时把对应的列号也扩大10001倍，再把这两部分相加。

  • 利用SMALL函数依次提取最小的值。

  • 利用RIGHT函数提取行、列号的信息。这时返回头去回味一下COLUMN(B:B)*10001这部分。由于列号被扩大10001倍，提取后最后两位数字是列号信息；行号扩大了100倍，左侧两位是行号信息。

  • 利用TEXT函数将上述行列号信息转换为R1C1格式。

  • 利用INDIRECT函数提取清单。

  • IFERROR函数屏蔽错误。

请注意，这个公式里的COLUMN(B:B)*10001部分中“10001”是可以用“1”来替换的。原因是，本例中所涉及的列数仅仅只有一列，因此可以乘上1。如果说数据区域中有多列，那只能使用COLUMN()*10001这样的写法。

方法三：多维引用的方法2

图四

在单元格G2中输入公式“=INDIRECT(TEXT(SMALL((--($B$2:$B$49<=""))/1%%+ROW($B$2:$B$49)/1%+COLUMN(B:B),ROW(A1)),"r0c00"),)&""”，三键（CTRL+SHIFT+ENTER）回车并向下拖曳即可。

函数解析：/1%%部分表示扩大10000倍

本例本质上讲也是多维引用，和上例的思路相同，这里就不再赘述了。

品名清单完成后，抓取转化率的公式就比较简单了。如图所示：

图五

其实当前任务有更简单的公式来完成。细心的话，大家可以发现我们需要提取的品名的行号间隔都是4、转化率的间隔也是4。因此我们的公式可以简化为如下，然后公式下拉填充即可。

品名提取：=index($b$2:$b$49,row(b1)*4-3)

转化率提取：=index($b$2:$b$49,row(b1)*4)

上述的方法只适用于提取对象所在位置是有规律可循的，当面对提取对象所在位置是没有规律的情况时，类似的简便方法就不适用了，还是需要大家学会前面的操作方式。

到这里，提一个问题请大家思考：(--($B$2:$B$49<="")这部分的含义什么？有什么作用？

好了，今天和大家分享的就是这些，祝各位在即将到来的新一轮的购物狂欢中买得开心，购得愉快！

赞 (0)

相关推荐