02 描述統計學 Lesson4 電子表格2:處理資料

文章目錄

• 02 描述統計學 Lesson4 電子表格2:處理資料
• • 2.單元格公式
  • 4.SUBSTITUTE 文本函數
  • 6.提取文本

2.單元格公式

單元格公式：用于計算單元格值的表達式。（Cell Formula：An expression, beginning with an EQUAL SIGN (=), that defines the operations which calculated the value for the cell.）

在實際使用時，我們經常在一個單元格建立公式（=開頭），在使用複制方式填充一行或一列得到相應其他的公式，進而計算出需要的一系列值。公式中可以時常量、數學運算符或函數，也可以引用單個或一系列單元格：

=5

=5+3

=$B$3

這是在上節講過的絕對位址

=FUNCTION(parm1, parm2)

這裡的FUNCTION指函數，parm指這個函數中的參數。

接下來我們講下函數，函數是 Excel 的内置功能，可以根據輸入内容計算值（Fuction：A standard-defined routine that can be used in formulas):

• 函數以某些關鍵字開頭（例如SUM求和，AVERAGE求平均數，TRIM去掉多餘空格函數）（ps：TRIM在Office中文幫助中沒有，英文幫助有）

參數是有函數規定的，計算函數值的所需輸入。可以是常量、單元格引用或區域引用或者其他函數（嵌套）。（Parameter：Value or expressions required by a function to determine a result，defined by the function.)

課程中使用的 Windows Office 2016，如果使用的是 Mac Office 2016，界面如下：

PS：超過400種的函數說明如下：Excel 函數（按類别列出）

有興趣的同學，可以學習一下Excel VBA函數。

4.SUBSTITUTE 文本函數

文本函數 SUBSTITUTE 是專門處理文本字元串的。文本字元串的定義是：Text String - String of letters, Numbers, and punctuation that is not treated numerically. 文本字元串可能包含以下内容：

• Names
• Phrases
• Cities
• Countries
• Not a date or number

同為文本處理，SUBSTITUTE 和 Find + Replace 的差別：

• 不改變原始資料
• 在新的單元格中存儲轉換的資料

6.提取文本

這一節的三個重點，課程視訊中比較清楚。以下用 Mac 示例作為補充，單元格位址也和課程不同：

• 1 FIND函數

  

• 2 LEFT函數（MIN和RIGHT函數類似）

  

• 3 函數的嵌套（把FIND嵌套到LEFT中）

  

7.練習：提取文本

這節練習是使用之前的FIND、單元格運算、MID完成。最後的整合是用一個嵌套公式完成輸出，比較長，請細心。提示如下：

PS：一個中文字元是算兩位，如果想統一需要使用MIDB處理：MID和MIDB差別

這裡練習的最後一個是吧前面的嵌套單元格做整合：

FIND(" ",A2,FIND("data",A2,1)+LEN("data")+1) - (FIND("data",A2,1)+LEN("data")+1)

大家注意到了那兩個

+1

了麼？可以去掉。最終變成：

FIND(" ",A2,FIND("data",A2,1)+LEN("data")) - (FIND("data",A2,1)+LEN("data"))

8.調整文本格式

這節介紹了5個新的函數：

• CONCATENATE 是将單元格中的文字結合的函數
• TRIM 是去掉多餘空格的函數
• PROPER、UPPER、LOWER 是修改文字大小寫的函數，對比如下：

  

10.數學函數

本節内容和下節練習針對的是：

=SUM()

和

=AVERAGE()

函數做求和和平均的計算，這是内置函數。同時另一種

=B2+B3

這種直接單元格運算的是另一種實作數學運算的方法。

12.重複行

在這一節，我們進入到了一個新的環節，如何得到幹淨的資料。（

Clean Data：Data that is free of corrupt or inaccurate data items.）幹淨的資料是資料分析的重要前提，無論使用的 Excel 電子表格，還是後面會使用的 Python 程式設計語言，這一步都是資料分析前的重點環節。

讓我麼從處理重複行開始，課程中的這個名字前面加了一個空格的情況，會在處理時造成問題，因為計算機會把

‘ Glasu’和‘Glasu’當作兩個不同的人進行處理：

或者是這樣，大小寫不一樣的（處理方法見上節中的字元串方法）：

13.練習：重複行

補充一點，Mac Excel 的删除重複行的功能在螢幕頂的菜單中，點選之後會提示有多少重複行，确認即可删除：

14.拆分列

補充一點， Mac Excel 的拆分列在螢幕頂菜單的資料中。Office365 無此功能。

17.練習：資料排序

Mac 的同學請跳過，程式會崩潰。Office365排序不會将其他單元格的内容一同移動，會産生資料錯位。

18.篩選資料

選出某個特征的資料并忽略其他資料（Filter：Method to group data by selecting characteristics of one or more columns in a data set.)

Mac的篩選器仍然需要到頂層菜單打開（導航右側的不生效）。之後會生成一個向下的小箭頭，點選箭頭可以選擇篩選的内容。注意在這裡是可以搜尋的，在内容很多時非常友善。

請注意，篩選不但可以多個一起進行，而且篩選的條件可以是一個判斷，比如後面練習中尋找名字為J開頭的球員：

最後的問題需要結合AVERAGE函數，并且先把過濾出來的數值COPY到别的位置完成。

PS：特别提示，在MAC下：

• 如果遇到篩選、排序會退出的情況。請嘗試不選中列，隻選中需要排序的單元格再做嘗試。
• 如果出現MAC軟體上面Ribon不可見，可以點選右上角的

  ^

  兩次，收起再打開完成。