大家好，我是研究數(shù)據(jù)轉(zhuǎn)換的小爽~

小李是我的同事，他之前設(shè)計(jì)過一個(gè)座位表，當(dāng)需要查找姓名對應(yīng)的座位號時(shí)，遇到了困難。

小李在查找匹配的數(shù)據(jù)時(shí)，造成難度增加的主要原因是：

設(shè)計(jì)的表是一個(gè)數(shù)據(jù)展示表，而不是一個(gè)標(biāo)準(zhǔn)數(shù)據(jù)結(jié)構(gòu)表。

如果是個(gè)規(guī)范的數(shù)據(jù)源，查找數(shù)據(jù)將不再是難題。

比如可以直接使用 Vlookup 函數(shù)進(jìn)行查找。(我相信這肯定難不倒我們秋葉 Excel 的粉絲們~)

所以，小爽今天側(cè)重介紹：如何將這個(gè)座位數(shù)據(jù)表，轉(zhuǎn)化為一維表？

文章主要介紹三個(gè)方法，一起來看看吧。（最后一招狠簡單?。?/p>

利用 Power Query

使用 PQ 的第一步，自然是需要先將數(shù)據(jù)導(dǎo)入到 PQ 編輯器中。

全選數(shù)據(jù)源區(qū)域-在【數(shù)據(jù)】選項(xiàng)卡下，選擇【來自表格/區(qū)域】。

由于沒有標(biāo)題，所以取消勾選【表包含標(biāo)題】，單擊【確定】按鈕。

此時(shí)數(shù)據(jù)源已經(jīng)導(dǎo)入到 PQ 編輯器中。

單擊 fx 新增步驟。

由于數(shù)據(jù)是每三行為一組數(shù)據(jù)，所以我們將表利用?Table.Split?函數(shù)進(jìn)行拆分。

PS. Table.Split 函數(shù)能夠?qū)⒅付ǖ男袛?shù)拆分為多個(gè)表。

公式欄中，輸入公式:

• =?Table.Split(源,3)
  

利用?Table.Transpose 函數(shù)將每一個(gè)表進(jìn)行轉(zhuǎn)置操作。
公式欄公式如下 :

• = List.Transform(

• ?Table.Split(源,3),

• ?each Table.Transpose(_)

• )
  

到這里，差不多已經(jīng)是我們想要的效果了，現(xiàn)在只需要利用?Table.Combine 函數(shù)把表進(jìn)行合并處理即可。
公式欄公式如下：

• = Table.Combine( ?

• List.Transform(Table.Split(源,3),each Table.Transpose(_))

• )
  

將多余的列刪除，鼠標(biāo)右鍵需要?jiǎng)h除的列-選擇【刪除】選項(xiàng)。

到這里，數(shù)據(jù)轉(zhuǎn)換效果已經(jīng)完成了。是不是很簡單鴨~

最后，將表加載到 Excel 中。搞定~

利用 PQ 做法，簡單是簡單，但是它在導(dǎo)入數(shù)據(jù)的時(shí)候，破壞了原本表格的展示效果。

好丑 !!! 絕對不能忍 !

那有沒有不破壞表格結(jié)構(gòu)，對數(shù)據(jù)進(jìn)行整理的做法？

接下來，我們介紹函數(shù)的做法。

函數(shù)整理法

利用函數(shù)來做，確定位置時(shí)，我們需要用到一點(diǎn)點(diǎn)數(shù)學(xué)知識~

?? 確定行的規(guī)律

觀察表中數(shù)據(jù)，可以發(fā)現(xiàn)，姓名與姓名之間依次間隔 3 行, 也就是：1 , 4 , 7 , 10 , 13 , 16。
——由于有 16 列，所以每個(gè)數(shù)依次重復(fù) 16 次。

U2 單元格輸入公式 :

• =INT((ROW(A1)-1)/16+1)*3-2
  

?? 確定列的規(guī)律

觀察表中數(shù)據(jù)，可以發(fā)現(xiàn)，每一行有 16 列,由于一共有 6 排，所以 1 到 16，需要重復(fù) 6 次。
V2 單元格輸入公式 :

• =MOD(ROW(A1)-1,16)+1
  

?? 索引姓名數(shù)據(jù)

在前面，我們已經(jīng)分別確定每一個(gè)姓名的行列相對位置。

所以，我們使用 index 函數(shù)，返回對應(yīng)區(qū)域的行列數(shù)就可以啦。

index 函數(shù)的用法，通俗上講：

=index ( 區(qū)域 , 第幾行，第幾列 )

比如下圖，要查找第一行，第一列：

=index(區(qū)域,1,1)

也就是小爽。

回到案例中。
W2 單元格直接編寫公式：

• =INDEX($D$5:S$21,U2,V2)
  

?? 索引座位數(shù)據(jù)

因?yàn)樽辉谛彰南蛳乱恍?，所以行需要再加?1。

X2 單元格輸入公式：

• =INDEX($D$5:$S$21,U2+1,V2)

  
  

利用傳統(tǒng)函數(shù)做法，簡單是簡單，但是每一次做的時(shí)候，都需要事先利用數(shù)學(xué)知識找規(guī)律，再進(jìn)一步編寫函數(shù)。好麻煩~

像這種有規(guī)律性的結(jié)構(gòu)表設(shè)計(jì)，在工作中，我們經(jīng)常見到，比如說制作標(biāo)簽，座位安排等等。
杜絕這種問題主要就是，在設(shè)計(jì)表格之初，盡量避免。

不過，工作要求，我們不得不設(shè)計(jì)這種表。

那么對于這種，有結(jié)構(gòu)性規(guī)律的重復(fù)表格，轉(zhuǎn)換為一維表，我們能不能把它弄為一個(gè)自定義函數(shù)呢？

我們 Excel 主講老師——拉登老師就有這個(gè)想法??，于是他制作了一個(gè)?P-index?函數(shù)，就是專門用來解決這個(gè)問題的。

PS. 獲取公式 PLUS 的方法見文末。

前面的函數(shù)做法，我們用了 n 個(gè)函數(shù)，對吧？利用?P_INDEX?函數(shù)，我們只需要一個(gè)函數(shù)就可以搞定。走起~

?? 得到姓名

如下圖, 在V2 單元格輸入公式：

• =P_INDEX($D$5,$E$5,$D$8,16,100,U2)
  

?? 得到座位

W2 單元格輸入公式 ：

• =P_INDEX($D$5,$E$5,$D$8,16,100,U2,1)
  

簡單解釋一下 ：

座位列的公式跟姓名列一樣，只不過多了一個(gè)偏移的參數(shù)，最后的 1 表示向下偏移一個(gè)單元格。而座位就在姓名的向下一個(gè)單元格處。

• 姓名列

• =P_INDEX($D$5,$E$5,$D$8,16,100,U2)

• 座位列

• =P_INDEX($D$5,$E$5,$D$8,16,100,U2,1)
  

敲黑板 ?? P_INDEX 函數(shù)基礎(chǔ)語法

參數(shù) 1：第一個(gè)單元格

參數(shù)2：第二個(gè)單元格

參數(shù) 3：第三個(gè)單元格

參數(shù) 4：列標(biāo)簽有幾個(gè)。

數(shù)一數(shù)我們知道為 16。

參數(shù) 5：行標(biāo)簽有幾個(gè)。

數(shù)一數(shù)知道有 6 個(gè)，但是我們不知道有多少個(gè)的情況下，可以寫大一點(diǎn)，比如我這里寫了 100。（寫大于等于 6 的數(shù)就可以）

參數(shù) 6：獲取第幾個(gè)值，序號中就是 123……，直接引用就可以。

參數(shù) 7：以起始單元格向下偏移幾行（座位在姓名的向下一個(gè)單元格，所以如果要獲取座位的話，寫 1）

參數(shù) 8：以起始單元格向右偏移幾行。

不需要找規(guī)律，一個(gè) P_INDEX 函數(shù)一下子就搞定這個(gè)結(jié)構(gòu)轉(zhuǎn)換的問題。

最后的話

本文深入講解了同事小李遇到的表格查找問題。查找的主要難點(diǎn)在于表格數(shù)據(jù)不規(guī)范，造成需要寫個(gè)長公式才能解決，極大的增加了使用難度。

借著這個(gè)問題，小爽寫了三種轉(zhuǎn)換一維表的方法。

方法一：利用 pq，涉及三個(gè)基礎(chǔ) M 函數(shù)

拆：利用 Table.Split 函數(shù)拆表；

轉(zhuǎn)：利用 Table.Transpose 函數(shù)轉(zhuǎn)置；

合：利用 Table.Combine 合并表格；

刪：刪除不需要的列。

方法二：傳統(tǒng)函數(shù)定位法

利用 int 和 row 函數(shù)構(gòu)造規(guī)律的行數(shù)；

利用 mod 和 row 函數(shù)構(gòu)造規(guī)律的列數(shù)；

通過 index 索引區(qū)域行列數(shù)，獲取姓名和座位。

方法三：公式 PLUS(P_INDEX)

第一到第三參數(shù)，確定位置

第四五參數(shù)，確定列和行標(biāo)簽個(gè)數(shù)

第六，返回第幾個(gè)

第七八參數(shù)，確定行列偏移數(shù)

當(dāng)然 P_INDEX 函數(shù)除了快速解決上述的表格結(jié)構(gòu)轉(zhuǎn)換，還可以處理其他有規(guī)律性結(jié)構(gòu)。后面有機(jī)會，我們再聊聊。

做個(gè)小調(diào)查，上面三種轉(zhuǎn)換一維表的方法，你認(rèn)為哪個(gè)最好用呢？

大家關(guān)于工作中，遇到過哪些奇葩表？留言區(qū)與我一起聊聊。

對了，如果你想系統(tǒng)性學(xué)習(xí) Excel，掌握更多Excel 技能，提升工作效率。

正好，我們家的《秋葉 Excel 3 天集訓(xùn)營》專為職場人準(zhǔn)備，全部基于職場真實(shí)表格案例設(shè)計(jì)，還有很多超實(shí)用 Excel 技巧教學(xué)。

從日常的功能出發(fā)，全程演示，一課一練，夯實(shí)進(jìn)階每一步。

報(bào)名即送 ?【35 個(gè)常用函數(shù)說明】??????

*廣告