贊助廠商

學不完.教不停.用不盡文章列表

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

Excel-利用公式執行資料清理

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

1. 小寫

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

Excel-利用公式執行資料清理


2. 大寫

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

Excel-利用公式執行資料清理


3. 每個單字大寫

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

Excel-利用公式執行資料清理


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

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

Excel-利用公式執行資料清理


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

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

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

Excel-利用公式執行資料清理

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


6. 新增首碼

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

Excel-利用公式執行資料清理


7. 新增尾碼

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

Excel-利用公式執行資料清理

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(2) 人氣()

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

Excel-移除表格中「欄」的重覆資料

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

Excel-移除表格中「欄」的重覆資料

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

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

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

Excel-移除表格中「欄」的重覆資料

如果如下:

Excel-移除表格中「欄」的重覆資料

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

3. 移除重覆項。

Excel-移除表格中「欄」的重覆資料

勾選欄位:甲

Excel-移除表格中「欄」的重覆資料

結果如下:

Excel-移除表格中「欄」的重覆資料

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

Excel-移除表格中「欄」的重覆資料

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

Excel-移除表格中「欄」的重覆資料

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

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

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

Excel-同時利用二個代碼查詢(SUBSTITUTE,MID,SUMPRODUCT)

 

【公式設計與解析】

首先,要將「b欄位」的資料重建在二欄之中(如下圖)。

Excel-同時利用二個代碼查詢(SUBSTITUTE,MID,SUMPRODUCT)

 

(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運算。

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼