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

點擊藍字關注【秋葉AIExcel】

發(fā)送【10】

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

本文作者：小爽

本文編輯：小蘭

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

最終登記成如下的表格：

▲ 以上為模擬數(shù)據(jù)

現(xiàn)在他想要基于所記錄的數(shù)據(jù)進行分析，比如每個人參與過哪場直播……

很明顯，想要做數(shù)據(jù)分析，第一步就是把以上表格轉(zhuǎn)化為一維表，如下圖：

怎么做呢？

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

AI 函數(shù)

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

豆包網(wǎng)址：doubao.com

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

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

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

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

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

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 寫函數(shù)公式完成的，方便是方便，不過我們編寫完公式，還得向下拉進行拖拽，直到出現(xiàn)錯誤值，才知道結尾。

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

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

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

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

課程原價 99 元

今天免費領取

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

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

報名免費領取

秋葉獨家 AI 學習資料包

6 套飛書多維表格模板

60+ 秋葉自研智能體

AI 編程

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

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

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

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

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

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

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

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

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

超贊！

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

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

其他方法

▋方法一：PQ

記住，二維表轉(zhuǎn)一維表，用PowerQuery輕松搞定！

我們將數(shù)據(jù)上傳到 PQ 編輯器中。

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

一下子就完成了~

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

WPS 用不了 PQ 怎么辦？

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

▋方法二：函數(shù)

場次列

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

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

=A2:E9<>""

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

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

最后利用 Tocol 函數(shù)將數(shù)據(jù)拉成一列。

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

忽略特殊值有如下參數(shù)可選。

0-保留所有值(默認)

1-忽略空白

2-忽略錯誤

3-忽略空白和錯誤

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

通過列掃描有如下參數(shù)可選。

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

true(1)-按列掃描

最后函數(shù)公式如下圖所示：

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

姓名列

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

=TOCOL(A2:E9,1)

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

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

如何將二維的一條條數(shù)據(jù)，轉(zhuǎn)為一維數(shù)據(jù)，方便我們進行數(shù)據(jù)分析，本文一共分享了 4 種方法。

?AI 寫函數(shù)。

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

數(shù)據(jù)源背景：數(shù)據(jù)源區(qū)域，數(shù)據(jù)源內(nèi)容，標題信息

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

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

AI 函數(shù)公式，使用的是傳統(tǒng)萬金油函數(shù)公式做法。

INDEX+MOD+SMALL+IF+ROW+COLUMN

?AI 寫 VBA 代碼。

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

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

?PQ 逆透視列一步完成。

?邏輯判斷+Tocol 函數(shù)快速搞定。

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

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

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

課程原價 99 元

現(xiàn)在僅需 0 元！

秋葉實戰(zhàn)派老師直播授課

專業(yè)助教隨時答疑

多種 AI 工具教學

長按掃碼，加班主任微信

立刻預約上課！

報名免費領取

秋葉獨家 AI 學習資料包

6 套飛書多維表格模板

60+ 秋葉自研智能體

發(fā)送【10】

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