哪位Excel高人琢磨出的這4個數據整理技巧，太好用了！

點擊藍字關注【秋葉AIExcel】

發送【10】

免費領 10 個 AI 美化圖表提示詞！

本文作者：小爽

本文編輯：小蘭

同事小李作為一個線下培訓師，每次直播觀看的人員名稱都會一一記錄。

最終登記成如下的表格：

▲ 以上為模擬數據

現在他想要基于所記錄的數據進行分析，比如每個人參與過哪場直播……

很明顯，想要做數據分析，第一步就是把以上表格轉化為一維表，如下圖：

怎么做呢？

首先讓我們的 AI 小助理來幫忙~

AI 函數

這里我使用的是豆包，簡單的表格問題它基本都能解決。

豆包網址：doubao.com

? 進入豆包后，在對話框中輸入提示詞。

提示詞編寫通用框架：數據源背景+編寫要求+返回格式。

PS：數據源數據，可以直接復制粘貼少部分數據到提示詞中。也可以通過上傳文件或者上傳表格圖片，這里采取直接復制粘貼。

思考片刻之后，豆包就給我們編寫了兩個函數公式。

? 回到 Excel，我們將對應函數公式輸入到單元格中，下拉填充即可。

G2單元格：

=INDEX($A$1:$E$1,,MOD(SMALL(IF($A$2:$E$9<>"",ROW($A$2:$E$9)*100+COLUMN($A$2:$E$9),""),ROW(A1)),100))

H2單元格：

=INDEX($A$2:$E$9,INT(SMALL(IF($A$2:$E$9<>"",ROW($A$2:$E$9)*100+COLUMN($A$2:$E$9),""),ROW(A1))/100)-1,MOD(SMALL(IF($A$2:$E$9<>"",ROW($A$2:$E$9)*100+COLUMN($A$2:$E$9),""),ROW(A1)),100))

上面是用 AI 寫函數公式完成的，方便是方便，不過我們編寫完公式，還得向下拉進行拖拽，直到出現錯誤值，才知道結尾。

要是數據太多豈不是手都得累斷，所以，我們還可以讓AI 生成 VBA代碼去解決這個問題。

好處是，代碼寫好后直接運行就可以了！

對了，如果你也想讓 AI 替你工作，卻不知道怎么讓它配合，如果你想知道更多 AI 運用場景，以簡單的操作完成復雜的工作任務~

那我推薦你加入《秋葉 AI 智能辦公 3 天實戰營》，秋葉金牌講師 @AI陳 帶你快速掌握豆包、飛書、扣子、即夢4 大工具辦公技巧，提高個人核心競爭力。

課程原價 99 元

今天免費領取

?直播授課 ?實操練習 ?助教答疑

名額有限，立即掃碼預約上課！

報名免費領取

秋葉獨家 AI 學習資料包

6 套飛書多維表格模板

60+ 秋葉自研智能體

AI 編程

只需將提示詞的函數公式改成 VBA即可。

可以看到，右邊的 AI 已經幫我們寫好了 VBA 代碼~

在【開發工具】選項卡下，單擊【Visual Basic】，進入 VBA 編輯器中。

鼠標右鍵新建模塊，將代碼復制粘貼到模塊中。

點擊運行代碼，如下圖所示 ，VBA 一下子就幫我們把數據整理好了。

當然這個代碼還不具有通用性，我們可以把整個過程變成一個函數，數據源為函數的參數。

繼續問 AI，改一下我們的要求即可。

同理，豆包的右邊直接替我們編寫好了 VBA 代碼，貼到模塊中。

我們測試一下這個VBA 自定義函數公式的效果。

超贊！

后面我們遇到同樣的需求，這個 VBA 自定義函數公式就可以復用啦~

前兩種方法都和 AI 有關，如果你用不了 AI 或者擔心數據安全，那最后這 2 種方法，你也可以了解一下。

其他方法

▋方法一：PQ

記住，二維表轉一維表，用PowerQuery輕松搞定！

我們將數據上傳到 PQ 編輯器中。

鼠標單擊【場次1】列，按住【Shift】鍵，再單擊場次 5 列，點擊鼠標右鍵，單擊【逆透視列】。

一下子就完成了~

需要改標題的話，可以直接在編輯欄中改。

WPS 用不了 PQ 怎么辦？

下面這個函數法就是專為你準備的。

▋方法二：函數

場次列

觀察數據源，我們知道場次標題需要重復的次數，是由下面非空數據區域決定。

那我們其實可以先做個判斷。

=A2:E9<>""

如果不為空，那我們就顯示對應的標題，否則我們就顯示為錯誤值。

=IF(A2:E9<>"",A1:E1,NA())

最后利用 Tocol 函數將數據拉成一列。

=TOCOL(數組,[忽略特殊值],[通過列掃描])

忽略特殊值有如下參數可選。

0-保留所有值(默認)

1-忽略空白

2-忽略錯誤

3-忽略空白和錯誤

由于我們需要忽略錯誤值，所以第二參數得寫 2 或者 3。

通過列掃描有如下參數可選。

false(0)-按行掃描(默認值)

true(1)-按列掃描

最后函數公式如下圖所示：

=TOCOL(IF(A2:E9<>"",A1:E1,NA()),3)

姓名列

姓名列就簡單了，直接利用 Tocol 函數將數據拉成一列，同時第二參數選 1，忽略空白。

=TOCOL(A2:E9,1)

如果想將兩列進行橫向拼接的話，可以使用 Hstack 函數。

=HSTACK(TOCOL(IF(A2:E9<>"",A1:E1,NA()),2),TOCOL(A2:E9,1))

如何將二維的一條條數據，轉為一維數據，方便我們進行數據分析，本文一共分享了 4 種方法。

?AI 寫函數。

對于數據清洗，常見的提示詞模板為：數據源背景+編寫要求+輸出表格格式

數據源背景：數據源區域，數據源內容，標題信息

編寫要求：轉化的需求+需要使用的是哪種方法返回(函數/VBA...)

輸出表格格式：可以使用 Markdown 格式

AI 函數公式，使用的是傳統萬金油函數公式做法。

INDEX+MOD+SMALL+IF+ROW+COLUMN

?AI 寫 VBA 代碼。

AI 編程，使用 VBA 返回，不同提示需求，返回形式會不一樣。

• 輸出過程，直接運行就可以返回結果。
• 輸出函數，將數據源作為參數，使得代碼更具有通用性，下次遇到同類的問題，可以復用自定義函數解決。

?PQ 逆透視列一步完成。

?邏輯判斷+Tocol 函數快速搞定。

解決問題的方法有很多種，選適合你的就可以~

不過如果你也想讓 AI 替你工作，如果你想知道更多 AI 運用場景，以簡單的操作完成復雜的工作任務~

那我推薦你加入《秋葉 AI 智能辦公 3 天實戰營》，秋葉金牌講師帶你快速掌握豆包、飛書、Coze、即夢4 大工具辦公技巧，提高個人核心競爭力。

課程原價 99 元

現在僅需 0 元！

秋葉實戰派老師直播授課

專業助教隨時答疑

多種 AI 工具教學

長按掃碼，加班主任微信

立刻預約上課！

報名免費領取

秋葉獨家 AI 學習資料包

6 套飛書多維表格模板

60+ 秋葉自研智能體

發送【10】

免費領 10 個AI 美化圖表提示詞！