點(diǎn)擊藍(lán)字關(guān)注【秋葉AIExcel】

發(fā)送【10】

免費(fèi)領(lǐng) 10 個(gè) AI 美化圖表提示詞！

本文作者：小爽

本文編輯：小蘭

同事小李作為一個(gè)線下培訓(xùn)師，每次直播觀看的人員名稱都會(huì)一一記錄。

最終登記成如下的表格：

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

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

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

怎么做呢？

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

AI 函數(shù)

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

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

? 進(jìn)入豆包后，在對(duì)話框中輸入提示詞。

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

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

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

? 回到 Excel，我們將對(duì)應(yīng)函數(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ù)公式完成的，方便是方便，不過我們編寫完公式，還得向下拉進(jìn)行拖拽，直到出現(xiàn)錯(cuò)誤值，才知道結(jié)尾。

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

好處是，代碼寫好后直接運(yùn)行就可以了！

對(duì)了，如果你也想讓 AI 替你工作，卻不知道怎么讓它配合，如果你想知道更多 AI 運(yùn)用場(chǎng)景，以簡(jiǎn)單的操作完成復(fù)雜的工作任務(wù)~

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

課程原價(jià) 99 元

今天免費(fèi)領(lǐng)取

?直播授課 ?實(shí)操練習(xí) ?助教答疑

名額有限，立即掃碼預(yù)約上課！

報(bào)名免費(fèi)領(lǐng)取

秋葉獨(dú)家 AI 學(xué)習(xí)資料包

6 套飛書多維表格模板

60+ 秋葉自研智能體

AI 編程

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

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

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

鼠標(biāo)右鍵新建模塊，將代碼復(fù)制粘貼到模塊中。

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

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

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

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

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

超贊！

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

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

其他方法

▋方法一：PQ

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

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

鼠標(biāo)單擊【場(chǎng)次1】列，按住【Shift】鍵，再單擊場(chǎng)次 5 列，點(diǎn)擊鼠標(biāo)右鍵，單擊【逆透視列】。

一下子就完成了~

需要改標(biāo)題的話，可以直接在編輯欄中改。

WPS 用不了 PQ 怎么辦？

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

▋方法二：函數(shù)

場(chǎng)次列

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

那我們其實(shí)可以先做個(gè)判斷。

=A2:E9<>""

如果不為空，那我們就顯示對(duì)應(yīng)的標(biāo)題，否則我們就顯示為錯(cuò)誤值。

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

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

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

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

0-保留所有值(默認(rèn))

1-忽略空白

2-忽略錯(cuò)誤

3-忽略空白和錯(cuò)誤

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

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

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

true(1)-按列掃描

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

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

姓名列

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

=TOCOL(A2:E9,1)

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

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

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

?AI 寫函數(shù)。

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

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

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

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

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

INDEX+MOD+SMALL+IF+ROW+COLUMN

?AI 寫 VBA 代碼。

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

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

?PQ 逆透視列一步完成。

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

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

不過如果你也想讓 AI 替你工作，如果你想知道更多 AI 運(yùn)用場(chǎng)景，以簡(jiǎn)單的操作完成復(fù)雜的工作任務(wù)~

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

課程原價(jià) 99 元

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

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

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

多種 AI 工具教學(xué)

長(zhǎng)按掃碼，加班主任微信

立刻預(yù)約上課！

報(bào)名免費(fèi)領(lǐng)取

秋葉獨(dú)家 AI 學(xué)習(xí)資料包

6 套飛書多維表格模板

60+ 秋葉自研智能體

發(fā)送【10】

免費(fèi)領(lǐng) 10 個(gè)AI 美化圖表提示詞！