大家好，我是潛伏在很多 Excel 交流群里，時(shí)不時(shí)冒個(gè)泡的小爽~

在其中一個(gè)群里，我看到這樣一個(gè)問題：如何批量整理標(biāo)題和鏈接？

我簡單整理了一下(如下表)，大致的需求就是：將左表整理成右表的形式。

如果是你，你會(huì)怎么做呢？

群內(nèi)的小伙伴各自發(fā)表了自己的看法：

用錯(cuò)位篩選？手機(jī)號(hào)碼都有 1 開頭，這個(gè)方法可行！

借助單元格引用的方法？這個(gè)方法可行！

上面的方法，都是很不錯(cuò)的方法。

唯一的缺點(diǎn)就是：一旦我們新增或者修改數(shù)據(jù)源內(nèi)容，所有操作需要再重新操作一遍。

所以本文跟大家分享兩種方法，目的是在新增或者修改數(shù)據(jù)源時(shí)，能夠動(dòng)態(tài)更新數(shù)據(jù)，下面就跟我一起看看吧~

??Index 函數(shù)索引法

??PowerQuery M 函數(shù)法

Index 函數(shù)索引法

我們先來看看具體的操作~

如下圖，在 E3 單元格輸入如下公式：

=INDEX($B$2:$B$11,ROW(A1)*2-1)

在 F3 單元格中輸入如下公式：

=INDEX($B$2:$B$11,ROW(A1)*2)

只需兩個(gè)函數(shù)公式，就搞定！

下面我們簡單說一下公式原理~

INDEX 函數(shù)說明：INDEX 函數(shù)可以返回指定的行與列交叉處的單元格引用。

=INDEX(區(qū)域,行數(shù),[列數(shù)],[區(qū)域數(shù)])

案例中公式如下：

=INDEX($B$2:$B$11,ROW(A1)*2-1)

公式中，數(shù)據(jù)區(qū)域?yàn)?B$2:$B$11，姓名列就在綠色區(qū)域內(nèi) 1,3,5，……的位置上。

所以我們需要構(gòu)造等差序列 1，3，5……，對(duì)應(yīng)的公式如下：

=ROW(A1)*2-1

ROW(A1)表示行數(shù)為 1，ROW(A1)*2-1=1*2-1=1。

ROW(A2)表示行數(shù)為 2，ROW(A2)*2-1=2*2-1=3。

ROW(A3)表示行數(shù)為 3，ROW(A3)*2-1=3*2-1=5。

同理，我們要得到手機(jī)號(hào)，需要構(gòu)造等差序列 2，4，6……

等差公式為=ROW(A1)*2，所以整個(gè)函數(shù)為：

=INDEX($B$2:$B$11,ROW(A1)*2)

?? 思路總結(jié)：

通過 Row 函數(shù)，構(gòu)造等差序列 1，3，5……，用 Index 函數(shù)索引就可以得到姓名列，通過使用 Row 函數(shù)構(gòu)造等差序列 2，4，6……，用 Index 函數(shù)索引即可得到手機(jī)號(hào)。

到此，Index 函數(shù)的方法就介紹完了，下面我們來介紹一個(gè) PowerQuery 的 M 函數(shù)法，一定要耐心看下去！

PowerQuery M 函數(shù)法

我們先來看看具體的操作步驟：

▋第一步：將數(shù)據(jù)源導(dǎo)入 PQ 編輯器中

選中表格區(qū)域-選擇【數(shù)據(jù)】選項(xiàng)卡-【自表格/區(qū)域】-出現(xiàn)創(chuàng)建表對(duì)話框，按住【確定】按鈕。

此時(shí)表格就導(dǎo)入 PQ 編輯器啦~

▋第二步：將數(shù)據(jù)列深化為列表選中數(shù)據(jù)列

鼠標(biāo)右鍵數(shù)據(jù)列-選擇【深化】或者選擇【轉(zhuǎn)換】選項(xiàng)卡下【轉(zhuǎn)化為列表】；

PS：深化和轉(zhuǎn)換為列表的目的都是：將當(dāng)前表中的某列數(shù)據(jù)提取出來形成列表，方便后續(xù)進(jìn)行操作。

此時(shí)就變成列表啦↓↓↓

▋第三步：每兩條信息拆解為一個(gè) List

單擊 ，fx 在右邊的編輯欄中添加公式，公式如下：

= List.Split(數(shù)據(jù)列,2)

此時(shí)數(shù)據(jù)就被拆分成兩個(gè)元素一條信息啦~

▋第四步：將拆分后的 List 轉(zhuǎn)化為 Table

這一步要用到 Table.FromList 函數(shù)。

公式如下：

= Table.FromList(自定義 1,each?_,{"姓名","手機(jī)號(hào)"})

操作動(dòng)圖：

▋第五步：實(shí)時(shí)更新

利用 PQ 做法，我們可以做到實(shí)時(shí)更新~

下面我們來簡單介紹一下案例中所涉及的 M 函數(shù)。

? List.Split：列表拆分

= List.Split(list,pagesize)

=List.Split(列表,每次拆幾個(gè))

Split 是分開的意思，List.Split 的意思就是將列表按照每 N 個(gè)拆開，形成單獨(dú)的 List。

我們的數(shù)據(jù)是每隔 2 個(gè)元素為一個(gè)完整的信息，所以需要用 List.Split 函數(shù)進(jìn)行拆分。

? Table.FromList：從列表轉(zhuǎn)換到表

=Table.FromList??(?list,?可選?對(duì)列表的處理方式?as?list??,??可選-對(duì)應(yīng)的標(biāo)題，可選-null?的默認(rèn)值，可選-額外的值處理方式)

拆分完后，我們需要將 List 轉(zhuǎn)為 Table，所以需要用到 Table.FromList 函數(shù)。

= Table.FromList(自定義 1,each _,{"姓名","手機(jī)號(hào)"})

第一參數(shù)：是一個(gè) List，這里的自定義 1 也就是上一步驟 List.Split 拆分后的列。

第二參數(shù)：對(duì)上一步驟的操作，由于這里沒有需要處理的，所以直接寫 each _。

第三參數(shù)：返回表后的列名，列名為{"姓名","手機(jī)號(hào)"}。

?? 思路歸納：

將表中的數(shù)據(jù)列深化為列表，是因?yàn)槊?2 個(gè)元素為一條信息，所以可以用 List.Split 函數(shù)將每 2 個(gè)元素拆分成一個(gè) List。

最后用 Table.FromList 函數(shù)將拆分后的 List 轉(zhuǎn)換為 Table。

到這里，M 函數(shù)的做法也就講完了~

總結(jié)一下

本文介紹了兩種整理錯(cuò)位數(shù)據(jù)的方法：

??Index 函數(shù)索引法最常見的做法，這是 Index 函數(shù)的經(jīng)典用法之一，通過構(gòu)造有規(guī)律的索引值進(jìn)行索引。

??PQ 的 M 函數(shù)做法，需要認(rèn)真研究。

利用深化將查詢表中的某列數(shù)據(jù)提取出來形成列表；利用 List.Split 函數(shù)對(duì)列表進(jìn)行拆分，使得每 2 個(gè)元素形成一個(gè) list；利用 Table.FromList 將 List 轉(zhuǎn)換為 Table。

這兩種方法在新增或者修改數(shù)據(jù)源時(shí)，都能夠動(dòng)態(tài)更新數(shù)據(jù)！

哪里可以跟秋葉一起學(xué) Excel？↓↓↓

限時(shí)福利——加入秋葉《3 天 Excel 集訓(xùn)體驗(yàn)營》，有大神帶你學(xué)習(xí)表格飛速排版、數(shù)據(jù)高效整理、圖表美化設(shè)計(jì)……??！和志同道合的小伙伴一起交流進(jìn)步~

秋葉《3 天 Excel 集訓(xùn)體驗(yàn)營》

課程原價(jià)?99?元?

但只要你是秋葉 Excel 的讀者

就能限時(shí)?1 元秒殺！！

僅需 3 天

你就可能成為 Excel 高手！

趕緊掃碼搶課吧！！

優(yōu)惠名額有限，先到先得！

現(xiàn)在掃碼報(bào)名

還能免費(fèi)領(lǐng)《35 個(gè)函數(shù)使用手冊(cè)》！