Excel怎么从直通车数据中提取商品转化率？

作者：EXCEL应用之家来源：部落窝教育发布时间：2020-12-09 17:31:26点击：3487

分享到:

收藏收藏人气：0人

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

编按：
哈喽，大家好！如何从直通车数据中快速提取商品名和各自的转化率方便后续转化率分析、顾客的购买意向分析呢？因为直通车数据并非规范的一维表，所以这个问题就是典型的从不规范表格中按需提取数据的问题。我们可以采用一对多查询的方式来解决。

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

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

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

图一

这个表非常不规范，实际是多个二维表的一次叠加。提取商品名和转化率就是一个一对多查询的经典应用。其次，我们也能运用多维引用的方法求得正确的结果。

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

方法一：一对多查询

图二

在单元格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）回车并向下拖曳即可。

函数解析：

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

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

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

l 利用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）回车并向下拖曳即可。

函数解析：

l 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倍，再把这两部分相加。

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

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

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

l 利用INDIRECT函数提取清单。

l IFERROR函数屏蔽错误。

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

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

图四