編按：

哈嘍，大家好！遇到不規(guī)范的excel表格該怎么辦呢？下文分享了7條函數(shù)公式，可以快速把不規(guī)范的表格變規(guī)范，也可以反過來把規(guī)范的表格變得符合領(lǐng)導(dǎo)閱讀習(xí)慣的不那么規(guī)范的表格。不規(guī)范、規(guī)范，左右互搏。趕緊來看看吧！

【前言】

來了，高手又要出招了，難道這次就是江湖傳聞的大招“左右互博”之術(shù)？

作者E圖表述在以前的文章中寫過一篇關(guān)于使用EXCEL對(duì)于數(shù)據(jù)源要求的文章——《函數(shù)技巧千千萬，如何制表才關(guān)鍵！》，但是現(xiàn)實(shí)工作中，還是有很多的同學(xué)把表建設(shè)的不規(guī)范。不規(guī)范又能怎么樣呢，反正沒有人可憐你統(tǒng)計(jì)過程有多復(fù)雜，最后你要給出結(jié)果就對(duì)了，那么我們今天就以三種典型“不規(guī)范案例”的左右互轉(zhuǎn)，來介紹幾個(gè)“難且必會(huì)的”函數(shù)操作。

【正文】

●左右互博第一式

案例如下：

左博右（不包含空格的數(shù)據(jù)）

解法：

在F2單元格輸入函數(shù)：

=INDEX($A$2:$C$4,INT(ROW(A3)/3),MOD(ROW(A3),3)+1)

函數(shù)解析：

INT(ROW(A3)/3)：因?yàn)槲覀兇藭r(shí)的函數(shù)是寫在F2單元格的，此時(shí)ROW(A3)=3，3/3=1，再用INT函數(shù)取整，還是1；

MOD(ROW(A3),3)+1：還是因?yàn)槲覀兇藭r(shí)的函數(shù)是寫在F2單元格，ROW(A3)=3，MOD函數(shù)求3/3的余數(shù)再加1，就等于1；

之所以作者E圖表述一直強(qiáng)調(diào)“函數(shù)是寫在F2單元格”，是因?yàn)槿绻覀冊(cè)谙吕畛浜瘮?shù)之后，就是下表的對(duì)應(yīng)值：

再使用INDEX函數(shù)分別按照對(duì)應(yīng)區(qū)域$A$2:$C$4的行列號(hào)，引出值即可。

右博左（不包含空格的數(shù)據(jù)）

解法：

在D2單元格輸入函數(shù)：

=INDEX($A$2:$A$10,COLUMN(A1)+3*ROW(A1)-3)

函數(shù)解析：

和左博右“異曲同工”的效果，都是利用了INDEX函數(shù)和數(shù)學(xué)思維，只不過上例INDEX的數(shù)據(jù)區(qū)域是二維的，而本例是一維的，只需要一個(gè)維度即可，可是數(shù)學(xué)思維的運(yùn)算更加的復(fù)雜。

●左右互博第二式

案例如下：

左博右（包含空格的數(shù)據(jù)）

解法：

在G2單元格輸入函數(shù)：

{=INDIRECT(TEXT(SMALL(IF($A$2:$D$6<>"",ROW($2:$6)*1000+COLUMN(A:D),9^9),ROW(A1)),"R0c000"),0)}

函數(shù)解析：

相信很多同學(xué)對(duì)于這個(gè)函數(shù)，理解起來比較吃力，不過仔細(xì)看，這其實(shí)就是典型的“萬金油”數(shù)組函數(shù)的用法。

第一步：如果A2:D6區(qū)域的值不等于空值，則返回單元格的行號(hào)乘以1000，再加上列號(hào)，否則返回9^9（一個(gè)足夠大的數(shù)值），使用“公式求值”功能我們看到返回如下數(shù)列；

第二步：通過G2單元格下拉填充函數(shù)后，ROW（A1）函數(shù)返回的內(nèi)容就是1，2，3，4，5……，再通過SMALL函數(shù)返回對(duì)應(yīng)大小的值：2001，2002，2003，2004，3001，3002，3004，4001，……；

第三步：使用TEXT函數(shù)，將這組數(shù)列轉(zhuǎn)換成R0C000的格式，就形成了類似R1C1單元格引用的格式R2C001、R2C002、……、R4C001、……；

第四步：使用INDIRECT函數(shù)將這些R1C1格式單元格文本，形成引用，就得到了我們的結(jié)果。這里有必要說一下，INDIRECT函數(shù)大家應(yīng)該不陌生，但是一定要記得，此函數(shù)是由兩個(gè)參數(shù)構(gòu)成的，第2個(gè)參數(shù)，就是對(duì)單元格地址格式的限定，如下圖所示：

我們函數(shù)中使用了INDIRECT（…，0）的結(jié)構(gòu)，（0是FALSE，1是TRUE，我們以前有講過）就是對(duì)R1C1單元格引用的參數(shù)。

還是左博右（包含空格的數(shù)據(jù)）

高手過招就要出其不備，還是左博右，沒想到吧！哈哈哈哈哈……

但是出招的方式變了，上例我們按照逐行引出內(nèi)容，現(xiàn)在我們要逐列引出內(nèi)容。

解法：

在G2單元格輸入函數(shù)：

{=IFERROR(INDIRECT(TEXT(MOD(SMALL(IF(A$2:D$6<>"",COLUMN(A:D)*(10^6+1)+ROW($2:$6)*100),ROW(A1)),10^6),"r0c00"),0),"")}

函數(shù)解析：

其實(shí)和上例的思路是一樣的，依然利用了“經(jīng)典的萬金油”函數(shù)。

第一步：使用IF函數(shù)，判斷當(dāng)A2:D6單元格區(qū)域中的單元格不為空的時(shí)候，列號(hào)*10^6再加1再加行號(hào)乘100，得到下面的數(shù)列；

就是這么巧妙，我們拿出其中一個(gè)數(shù)值來分析，后面的函數(shù)原理就迎刃而解了。

例如：30000203，如果我們?nèi)サ?span>30000，那么就是203，按照上例的思路，我們可以理解為2代表“第2行”，3代表“第3列”。之所以我們使用10^6，是為了讓這個(gè)數(shù)字2到第1位數(shù)值之間有足夠多的0，便于數(shù)據(jù)區(qū)域更大的時(shí)候依然可以使用。

第二步：通過G2單元格下拉填充函數(shù)后，ROW（A1）函數(shù)返回的內(nèi)容就是1，2，3，4，5……，原理同上一個(gè)案例；

但是這個(gè)函數(shù)之所以能夠按列索引，也是這步起到的作用，將二維的數(shù)列，按照數(shù)值的從小到大排列，得到了1000201，1000301，1000401，2000202，2000302，2000402，2000502，2000602，……，這樣的數(shù)列。

第三步：用MOD（…，10^6）函數(shù)去掉代表行號(hào)位之前的數(shù)據(jù)，如圖所示：

因?yàn)椤肮角笾怠敝酗@示不出MOD函數(shù)的數(shù)組效果，同學(xué)們可以“腦補(bǔ)”畫面，形成了201、202、203、204、…、304、…這樣的數(shù)列。

第四步：使用TEXT函數(shù)將值轉(zhuǎn)換成R0C00的表達(dá)格式；

第五步：使用INDIRECT（…，0）函數(shù)引出每一個(gè)單元格的值，完工。

第六步：IFFEROR函數(shù)的容錯(cuò)功能。

話外音：第二式中都是左表變右表，一定有小伙伴會(huì)問“如何從右表變成左表呢？”，其實(shí)我們案例中的數(shù)據(jù)源是不能轉(zhuǎn)變的，是因?yàn)楸疚闹械陌咐龥]有可以參考的“分列依據(jù)”。但是作者E圖表述絕不能讓大家失望，后期一定會(huì)安排一期“專門的一維表轉(zhuǎn)二維表的N種方法”，統(tǒng)一教給大家，敬請(qǐng)期待。

●左右互博第三式

案例如下：

左博右

同學(xué)們你的表中是否也有這樣的操作環(huán)境呢？是不是也曾為這樣的數(shù)據(jù)統(tǒng)計(jì)而頭疼，下面作者：E圖表述帶你破解這個(gè)難題。

解法：

在F2單元格輸入函數(shù)：

{=TRIM(MID(SUBSTITUTE(PHONETIC(B$2:B$7),"、",REPT(" ",199)),ROW(A1)*199-198,199))}

在E2單元格輸入函數(shù)：

=VLOOKUP("*"&F2&"*",IF({1,0},$B$2:$B$6,$A$2:$A$6),2,0)

函數(shù)解析：

我們先看F列單元格的函數(shù)。

第一步：PHONETIC函數(shù)，將B2:B7單元格區(qū)域的內(nèi)容，“連成一串”！

注意：B2:B7單元格區(qū)域中，每一個(gè)單元格中的每一個(gè)元素都需要有一個(gè)同樣的符號(hào)間隔，我們的案例中使用的（、）號(hào)，如果實(shí)際數(shù)據(jù)中的符號(hào)不統(tǒng)一，或者每個(gè)單元格結(jié)尾沒有（、）號(hào)，可以使用替換功能、&連詞功能，調(diào)整成案例中的格式。

第二步：使用SUBSTITUTE函數(shù)，將（、）號(hào)替換成199個(gè)空格。

第三步：使用MID函數(shù)“截取”這個(gè)超長的字符串，這個(gè)函數(shù)中最巧妙的就是ROW(A1)*199-198部分，又是“數(shù)學(xué)問題大于EXCEL問題”的思路，隨著ROW函數(shù)的下拉，每次都從第n個(gè)199減198的位置作為截取點(diǎn)，截取199個(gè)字符。這樣就把PHONETIC形成的大串字符“截成一段一段的”，而且每一段中都會(huì)包含一個(gè)字符元素。

第四步：再通過TRIM函數(shù)，去除截取好的字符串中的空格。及此完成F列的操作。

E列中的函數(shù)【=VLOOKUP("*"&F2&"*",IF({1,0},$B$2:$B$6,$A$2:$A$6),2,0)】就好理解了，使用了VLOOKUP函數(shù)反向查詢的功能，再配合通配符的使用，我們這里就不浪費(fèi)篇幅講解了。不熟悉的同學(xué)，可以查看一下往期教程《VLOOKUP&LOOKUP雙雄戰(zhàn)（四）：在橫向和逆向查詢上的血拼！》。

右博左

左手畫圓，右手畫方。上例反轉(zhuǎn)，看作者E圖表述繼續(xù)破解。（太巧妙了，必學(xué)思路）

解法：

首先復(fù)制A列，將其粘貼到E列，點(diǎn)擊工具欄中“刪除重復(fù)項(xiàng)”功能按鈕，得到E列內(nèi)容：

在F2單元格輸入函數(shù)：

{=MID(SUBSTITUTE(PHONETIC(OFFSET(A$1,MATCH(E2,A$2:A$24,0),,COUNTIF(A$2:A$24,E2),2)),E2,"、"),2,99)}

函數(shù)解析：

第一步：使用MATCH函數(shù)，找到E列值在A列區(qū)域中第一次出現(xiàn)的序號(hào)；使用COUNTIF函數(shù)確定A列中有幾個(gè)E列對(duì)應(yīng)的值。用“平面設(shè)計(jì)”這個(gè)字段來看，在A2:A24區(qū)域，第一次出現(xiàn)的位置是1，一共有3個(gè)值；

第二步：使用OFFSET函數(shù)，以A1單元格為基礎(chǔ)單元格，向下偏移1個(gè)位置，向右不偏移，擴(kuò)展出一個(gè)3行2列的新區(qū)域。用“平面設(shè)計(jì)”這個(gè)字段來看就是A2:B4區(qū)域；

第三步：使用PHONETIC函數(shù)，將OFFSET函數(shù)形成的區(qū)域引用，連接成一個(gè)字符串；

第四步：使用SUBSTITUTE函數(shù)，將E列對(duì)應(yīng)的值替換成（、）號(hào)。以“平面設(shè)計(jì)”字段為例，就是將上圖PHONETIC函數(shù)形成的字符串中所有的“平面設(shè)計(jì)”字符串替換成（、）號(hào)；

第五步：因?yàn)榈谝粋€(gè)是（、）號(hào)，所以我們使用MID函數(shù)，從第2位開始截取之后的99個(gè)字符。如果我們的元素內(nèi)容較多，可以調(diào)大99這個(gè)值。這里也利用了MID函數(shù)的一個(gè)“BUG”，當(dāng)數(shù)據(jù)不足99位時(shí)，會(huì)自動(dòng)截取到最后一位，不報(bào)錯(cuò)！

【編后語】

“左右互博”講完了，基本上涵蓋了所有清洗不規(guī)范數(shù)據(jù)的EXCEL函數(shù)思路。注意，我說的是思路，而不是解法。大家學(xué)習(xí)一定要記得，“知其然，更要知其所以然！”這樣才能真的學(xué)到知識(shí)，否則看再多的教程也沒有用，換個(gè)樣子一樣不會(huì)。喜歡作者的話，“留言”、“再看”、“分享朋友圈”，一波走起！

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

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

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

相關(guān)推薦：

連接字符串的方法《連接字符串的神奇妙用，讓你輕松實(shí)現(xiàn)高效率辦公！》

substitute函數(shù)的應(yīng)用《Excel函數(shù)經(jīng)典案例：substitute函數(shù)應(yīng)用》

OFFSET函數(shù)的應(yīng)用《Excel進(jìn)階之路必學(xué)函數(shù)：動(dòng)態(tài)統(tǒng)計(jì)之王——OFFSET（上篇）》

MATCH函數(shù)的應(yīng)用《MATCH：函數(shù)哲學(xué)家，找巨人做伴。新出道必學(xué)！》