學不完．教不停．用不盡

在進行大數據分析前，必須要做資料清理。而在 Power BI 或 Excel 的 Power Query 編輯器中，對於資料的格式可以做的處理如下圖。使用工具方便、輕鬆，但是如果想要以 Excel 的公式來處理呢？

這些功能，如何在 Excel 的工作表中以公式呈現來完成？

1. 小寫

所有文字轉換成大寫字，使用UPPER函數，儲存格B2：=UPPER(A2) 。

2. 大寫

所有文字轉換成小寫字，使用LOWER函數，儲存格B2：=LOWER(A2)。

3. 每個單字大寫

所有文字轉換成首字大寫，使用PROPER函數，儲存格B2：=PROPER(A2) 。

4. 修剪(移除開頭和尾端的空白字元)

刪除儲存格內容頭尾空白，使用TRIM函數，儲存格B2：=TRIM(A2)。

5. 清除(移除不可列印字元)

刪除儲存格內不可視字元，使用CLEAN函數，儲存格B2：=CLEAN(A2)。

結果只清除了不可列印字元，仍有一些不可視字元未能刪除。

註：根據微軟提供的資料，CLEAN 函數是設計來從文字中移除 7 位元 ASCII 碼的前 32 個非列印字元 (值 0 至 31)。 在 Unicode 字元集中，有額外的非列印字元 (127、129、141、143、144 及 157 的值)。 單獨使用 CLEAN 函數不會移除這些額外的非列印字元。

6. 新增首碼

在儲存格內容之前新增文字，使用「&」運算子，儲存格B2：="Microsoft "&A2。

7. 新增尾碼

在儲存格內容之後新增文字，使用「&」運算子，儲存格B2：=A2&"文件"。

網友根據這一篇：Excel-移除表格中重覆的資料，詢問如何刪除重覆欄？

這一篇文章主要是以表格功能裡的「移除重覆項」來操作，但是其主要是刪除重覆的「列」，如果是要刪除「欄」，該如何處理？

我好像找不到適用的工具，可以直接刪除重覆欄。但是，可以使用以下的間接方式。

本例假設要刪除「甲」的重覆項目（項四-A）。

1. 將原表格的欄和列轉置。

如果如下：

2. 將資料範圍轉換為表格

3. 移除重覆項。

勾選欄位：甲

結果如下：

4.將表格轉換為「範圍」。

5. 將原表格的欄和列轉置，任務完成。

網友想要根據一個 Excel 的資料表（如下圖左），在輸入一個代碼後查得資料表中的價格，該如何處理？

主要是要根據代碼中的「a」和「b」來查詢價格。

【公式設計與解析】

首先，要將「b欄位」的資料重建在二欄之中（如下圖）。

(1) 公式：=SUBSTITUTE(F2,"-",REPT(" ",20))

結果：XYZ         2       61       5       10

將儲存格F2中的「-」字元以20個空格取代。

(2) 公式：=SUBSTITUTE(MID(SUBSTITUTE(F2,"-",REPT(" ",20)),20,20)," ","")*1

結果：2

利用 MID 函數由第20個字取20個字元，再將空格以空字串取代。

「*1」作用乃為將MID函數傳回的文字數字轉換為數值數字。

(3) 公式：=SUBSTITUTE(MID(SUBSTITUTE(F2,"-",REPT(" ",20)),40,20)," ","")*1

結果：61

利用 MID 函數由第40個字取20個字元，再將空格以空字串取代。

「*1」作用乃為將MID函數傳回的文字數字轉換為數值數字。

(4) 公式：

=SUMPRODUCT((B11=A2:A5)*(B13>=B2:B5)*(B13<=C2:C5)*ROW(D2:D5))-1

結果：4

在 SUMPRODUCT 函數中利用三個條件取出完全符合者的列號：

(B11=A2:A5)*(B13>=B2:B5)*(B13<=C2:C5)

其中「*」運算，相當於執行邏輯AND運算。