編按：
哈嘍，大家好！如何從直通車數(shù)據(jù)中快速提取商品名和各自的轉(zhuǎn)化率方便后續(xù)轉(zhuǎn)化率分析、顧客的購(gòu)買意向分析呢？因?yàn)橹蓖ㄜ嚁?shù)據(jù)并非規(guī)范的一維表，所以這個(gè)問(wèn)題就是典型的從不規(guī)范表格中按需提取數(shù)據(jù)的問(wèn)題。我們可以采用一對(duì)多查詢的方式來(lái)解決。

雙十一剛過(guò)去，雙十二又要到啦！

相信有很多小伙伴們已經(jīng)準(zhǔn)備就緒，在雙十一沒(méi)搶到的貨，這次絕不能錯(cuò)過(guò)！不僅是買家，商家們也是萬(wàn)事俱備，只待發(fā)貨。

下面就是某寶上某家店鋪?zhàn)罱欢螘r(shí)間商品直通車的點(diǎn)擊率和轉(zhuǎn)化率的部分截圖。賣家希望提取商品的品名，并抓取每個(gè)品名下的轉(zhuǎn)化率，以便更好地分析客戶的購(gòu)買意愿和趨勢(shì)。

圖一

這個(gè)表非常不規(guī)范，實(shí)際是多個(gè)二維表的一次疊加。提取商品名和轉(zhuǎn)化率就是一個(gè)一對(duì)多查詢的經(jīng)典應(yīng)用。其次，我們也能運(yùn)用多維引用的方法求得正確的結(jié)果。

下面，我們就一起來(lái)看看詳細(xì)的介紹吧。

方法一：一對(duì)多查詢

圖二

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

函數(shù)解析：

l  B列中即含有文本，又含有數(shù)字，因此可以用ISTEXT函數(shù)來(lái)進(jìn)行判斷。

l  利用IF函數(shù)進(jìn)行判斷，對(duì)于那些是文本的單元格（ISTEXT函數(shù)返回結(jié)果為TRUE）則返回對(duì)應(yīng)的行號(hào)。

l  利用SMALL函數(shù)依次從小到大提取行號(hào)，作為INDEX函數(shù)的參數(shù)。

l  利用INDEX函數(shù)依次返回清單，并利用IFERROR函數(shù)屏蔽錯(cuò)誤。

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

當(dāng)你學(xué)會(huì)多維引用的方法后你會(huì)發(fā)現(xiàn)，之前有很多難題一下子就迎刃而解了。下面我們來(lái)看看它具體的過(guò)程。

圖三

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

函數(shù)解析：

l  IF(ISTEXT($B$2:$B$49),ROW($B$2:$B$49)/1%+COLUMN(B:B)*10001)部分，利用IF函數(shù)進(jìn)行判斷，單元格區(qū)域$B$2:$B$49中若含有文本字符串（TRUE），則把對(duì)應(yīng)的行號(hào)擴(kuò)大100倍（/1%），同時(shí)把對(duì)應(yīng)的列號(hào)也擴(kuò)大10001倍，再把這兩部分相加。

l  利用SMALL函數(shù)依次提取最小的值。

l  利用RIGHT函數(shù)提取行、列號(hào)的信息。這時(shí)返回頭去回味一下COLUMN(B:B)*10001這部分。由于列號(hào)被擴(kuò)大10001倍，提取后最后兩位數(shù)字是列號(hào)信息；行號(hào)擴(kuò)大了100倍，左側(cè)兩位是行號(hào)信息。

l  利用TEXT函數(shù)將上述行列號(hào)信息轉(zhuǎn)換為R1C1格式。

l  利用INDIRECT函數(shù)提取清單。

l  IFERROR函數(shù)屏蔽錯(cuò)誤。

請(qǐng)注意，這個(gè)公式里的COLUMN(B:B)*10001部分中“10001”是可以用“1”來(lái)替換的。原因是，本例中所涉及的列數(shù)僅僅只有一列，因此可以乘上1。如果說(shuō)數(shù)據(jù)區(qū)域中有多列，那只能使用COLUMN()*10001這樣的寫(xiě)法。

方法三：多維引用的方法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）回車并向下拖曳即可。

函數(shù)解析：/1%%部分表示擴(kuò)大10000倍

本例本質(zhì)上講也是多維引用，和上例的思路相同，這里就不再贅述了。

品名清單完成后，抓取轉(zhuǎn)化率的公式就比較簡(jiǎn)單了。如圖所示：

圖五

其實(shí)當(dāng)前任務(wù)有更簡(jiǎn)單的公式來(lái)完成。細(xì)心的話，大家可以發(fā)現(xiàn)我們需要提取的品名的行號(hào)間隔都是4、轉(zhuǎn)化率的間隔也是4。因此我們的公式可以簡(jiǎn)化為如下，然后公式下拉填充即可。

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

轉(zhuǎn)化率提?。?span>=index($b$2:$b$49,row(b1)*4)

上述的方法只適用于提取對(duì)象所在位置是有規(guī)律可循的，當(dāng)面對(duì)提取對(duì)象所在位置是沒(méi)有規(guī)律的情況時(shí)，類似的簡(jiǎn)便方法就不適用了，還是需要大家學(xué)會(huì)前面的操作方式。

到這里，提一個(gè)問(wèn)題請(qǐng)大家思考：(--($B$2:$B$49<="")這部分的含義什么？有什么作用？

好了，今天和大家分享的就是這些，祝各位在即將到來(lái)的新一輪的購(gòu)物狂歡中買得開(kāi)心，購(gòu)得愉快！

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

INDEX函數(shù):函數(shù)中的精確制導(dǎo)導(dǎo)彈，最強(qiáng)大的瘸子

Excel萬(wàn)金油公式：INDEX-SMALL-IF-ROW篩選函數(shù)公式解讀

IF函數(shù)用法：IF函數(shù)：剝洋蔥

Excel教程：5分鐘，學(xué)會(huì)文本函數(shù)之王——TEXT的常用套路

版權(quán)申明：

文本作者EXCEL應(yīng)用之家；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。