對于數據分析師而言，數據處理的重要性不言而喻。小易今天為大家重點講解下如何用Excel做數據處理。

衆所周知，拿到的數據通常不是完美無瑕的，而是經常出現缺失值、錯誤值等，這時擺在數據分析師面前的兩座大山，便是“數據清洗”和“數據加工”，翻過這兩座大山之後才可以順暢地進行接下來的具體的數據分析、數據可視化等操作。

導入數據是數據處理的先行軍，而Excel中支持導入的數據類型可以分為三類：

文本類數據、網站類數據、數據庫類數據。

大家簡單浏覽一下，小易在這裡就不詳細介紹啦，本次的重點是數據處理~

數據處理的方法通常包含數據清洗、數據加工等，本篇文章将為大家介紹一下Excel中的數據處理技巧。

數據清洗通常包括：篩選并剔除多餘的重複值；補充缺失值；修正或删除錯誤值。

數據加工通常包括：對清洗後的數據進行合并、提取、拆分、轉換等。

01

數據清洗

數據清洗處理的數據包括：重複值、缺失值、錯誤值。

處理重複值

1 函數法

函數：COUNTIF(range,criteria)，對區域内滿足單個指定條件的單元格進行計數。

range：計數範圍；

criteria：計數條件，可以指定為數字、文本或表達式。

計數1：在D2單元格輸入=COUNTIF(C:C,C2)，計算每個訂單編号總共出現的次數。

計數2：在E2單元格輸入=COUNTIF(C$2:C2,C2)，計算當前單元格對應的訂單編号是第幾次出現。

針對“計數2”列，以E6單元格對應的C6單元格中的訂單号為例，3表示在C2~C8中該訂單号是第三次出現。

此時能夠很清晰的通過D列和E列看出重複值的具體情況：重複值共計出現多少次以及對應重複值是第幾次出現。

通過函數法完成對重複值的計數之後，接下來就要進行删除步驟了。

（1） 排序删除

針對函數法篩選出來的重複數據，可以對E列數據進行升序排序，選擇并删除大于1的數據即可。

（2） 篩選删除

同樣是針對函數法篩選出的數據，對E列數據進行篩選，取消顯示數值為1的數據後将剩餘的數據删除即可。

還可使用“數字篩選”功能，将“大于1”或“不等于1”的數據篩選出來後删除。

點擊“大于”或“不等于”後，在彈出的窗口中，輸入相應的數值進行篩選删除。

2 高級篩選法

選中準備篩選的數據，使用高級篩選功能并選中“選擇不重複的記錄”。

點擊确定之後顯示的就是不帶重複值的數據。

高級篩選法的優點是操作簡便，可以迅速得到去除重複值後的展現效果。

3 條件格式法

選中準備篩選的數據，點擊“開始-條件格式-突出顯示單元格規則-重複值”，即可對重複值進行标記。

此處還可以選擇重複值被标記的顔色。

條件格式法相對其他方法就沒有那麼便捷了，它的缺點是隻能标記出重複的數據，而當數據量較大的時候不能清晰的看出重複數據和重複次數。

4 數據工具法

使用“數據工具”中的“删除重複項”功能，可以直接删除數據中的重複值。

值得注意的是在點擊“删除重複項”後彈出來的窗口中，需要先“取消全選”，然後選擇想要删除重複項的列，再點确定。

因為彈出的窗口有時候會默認全選，此時不能直接點擊确定，否則會将自己需要保留的數據也一并删除了。

這個方法的優點也是操作簡單，十分便捷。

接下來介紹如何處理數據中的缺失值。

處理缺失值

處理缺失值通常有兩個方法：定位條件和查找替換。

1 定位條件

定位條件功能的入口：

1）快捷鍵“CTRL G”。

2）“開始”-“查找和選擇”-“定位條件”。

選擇需要處理的數據後，點擊“定位條件”。

此時選擇“空值”并确定即可标記缺失值。

圖中的缺失值對應的内容是“Express Air”，正常輸入需要填充的内容後，使用快捷鍵“CTRL Enter”填充剩餘的缺失值。

2 查找替換

查找替換功能的入口：

1）快捷鍵：“CTRL H”。

2）“開始”-“查找和選擇”-“替換”。

點擊替換之後，輸入需要查找的内容以及替換的内容，再點擊全部替換即可。由于此次查找的為缺失值，所以查找内容保持空白即可。

小易在這裡再補充幾點通常用于處理缺失值的思路：

用樣本統計量的值替換缺失值，比如樣本均值；

用統計模型計算出來的值替換缺失值。比如回歸模型、判别模型等；

删除包含缺失值的數據記錄；

保留包含缺失值的數據記錄，分析時按需排除。

以上就是關于Excel數據分析中處理缺失值的内容啦~接下來介紹常用的處理錯誤值的方法。

處理錯誤值

Excel中的常見錯誤值有8種，分别是：#DIV/0!、#REF!、#VALUE!、#NULL!、#NAME?、####、#NUM!、#N/A、。每一種錯誤值都有各自的特點，因此對應着不同的處理方法，接下來就介紹一下以上8種錯誤值都可以通過什麼辦法解決~

錯誤類型1 #DIV/0!

出現#DIV/0!的原因是數據相除運算時，除數出現了0、空格或缺失值的情況。因為除數不能為0，所以當公式運算不符合要求時，會出現#DIV/0!的錯誤值形式。

如圖所示，可以通過“公式-錯誤檢查”功能查看錯誤原因和計算步驟。圖中顯示為“被零除”錯誤，此時修改除數内容即可。

錯誤類型2 #REF!

出現#REF!的原因是通過公式引用某一單元格或單元格區域時，當引用的區域被删除了，公式無法找到引用的區域，此時會出現#REF!錯誤值。

如圖所示，删除“3月銷售額列後”，“3月總銷售額”數據顯示#REF!錯誤值，錯誤檢查功能顯示原因為移動或删除單元格導緻了無效的單元格引用。針對此類問題我們需要重新梳理公式計算邏輯，更新公式的計算區域。

錯誤類型3 #VALUE!

出現#VALUE!的原因是輸入公式時将不同數據類型的值進行計算，比如将“小組1”與“15”相加時出現#VALUE!錯誤值。

圖中為文本類型和數值類型的相加，導緻出現了#VALUE!錯誤值，此時我們需要檢查公式中計算區域的值是否為相同類型。

錯誤類型4 #NULL!

出現#NULL!錯誤值的原因是在公式中使用了不正确的區域運算符，或者在區域引用之間使用了交叉運算符（空格字符）來指定不相交的兩個區域的交集。

如圖所示，由于錯誤使用了區間運算符，将“，”替換成了“空格”，出現了#NULL!錯誤值。此時需要重新檢查公式以及公式引用的區間。

錯誤類型5 #NAME?

#NAME?出現的原因可能是公式中的名稱拼寫錯誤、公式的語法使用錯誤、公式引用了未定義的名稱中的一種。

如圖所示，将函數“SUMPRODUCT”輸入成“SUNPRODUCT”時，出現了#NAME?錯誤值。此時需要重新檢查錯誤值單元格中的公式拼寫。

錯誤類型6 ####

出現####錯誤值的原因是列不夠寬而無法顯示單元格的所有内容。

如圖所示，當列不夠寬無法顯示完整日期時會出現####錯誤值，此時調整列寬即可。

錯誤類型7 #NUM!

出現#NUM!錯誤值的原因是公式中包含無效數值。

如圖所示，由于公式中“3的6785次方“計算的數值超出了Excel限定的數值計算範圍導緻出現了#NUM!錯誤值，此時隻能将數據進行拆分或重新整理計算。

錯誤類型8 #N/A

出現#N/A錯誤值的原因是公式找不到要求查找的内容。

如圖所示，因為表格中不存在公式中的F7-“小組5”的内容，所以顯示#N/A錯誤值。此時需要重新檢查查找範圍，以及查找内容是否有誤。

處理錯誤值的補充：

小易在這裡再補充一個常用于處理錯誤值的函數：

IFERROR(value, value_if_error)

value：檢查是否存在錯誤的參數；

value_if_error：公式計算結果為錯誤時要返回的值。

以錯誤值#N/A為例，當由于公式查詢的數據不存在時出現#N/A錯誤值，可以使用該函數返回設定的備注。

以上就是處理錯誤值的内容啦~接下來小易帶大家進入數據加工的闆塊。

02

數據加工

數據加工通常包括數據合并、數據拆分和數據轉換。下面先從數據合并開始。

數據合并

數據合并通常包括字段合并和字段匹配。

1 字段合并

字段合并常用到函數：CONCAT(text1,[text2],……)和&連接符。

text1（所需的）：要連接的文本項。字符串或字符串數組，如單元格區域。

text2（可選）：要連接的其他文本項。文本項最多可以有253個文本參數，每個文本參數可以是一個字符串或字符串數組，如單元格區域。

&：适用于需要拼接的字段較少的情況。

下圖是對兩個方法的使用示例。

2 字段匹配

跨表格的字段匹配通常需要使用函數：

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

lookup_value（必需參數）：要查找的值。要查找的值必須列于在參數參數中指定的單元格table_array列中；

table_array（必需參數）：VLOOKUP 在其中搜索 lookup_value 和返回值的單元格區域。單元格區域的第一列必須包含lookup_value。單元格區域還需要包含要查找的返回值；

col_index_num（必需參數）：對于包含 (的列，列号table_array) 從 1 開始；

[range_lookup]（可選參數）：一個邏輯值，該值指定希望 VLOOKUP 查找近似匹配還是精确匹配。

表格1

表格2

如上圖所示，若要根據“訂單号”将表格2中的“運送日期”數據匹配到表格1中，可根據下圖的公式進行字段匹配。

上述兩個方法都是用于數據合并的，接下來介紹數據拆分的方法。

數據拆分

數據拆分通常指字段分列，小易總結了兩個方法來實現字段分列。

1 菜單法

功能入口：“數據-數據工具-分列”（如圖所示）

确認下一步之後，還需要在彈出的窗口中确認分隔符号，案例中使用的分隔符号是“/”。

此處點擊下一步後，可以在彈出的窗口中選擇拆分的數據對應的格式，對于案例中的數值可直接默認選擇，點擊完成。

案例中由于被拆分的數據相鄰的列已有内容，所以最後呈現的是将C列和D列數據替換後的效果。

2 函數法

函數法對應的有兩個函數：

LEFT(text,[num_chars])；RIGHT(text,[num_chars])

text（必需）：包含要提取的字符的文本字符串；

num_chars（可選）：指定要由LEFT/RIGHT提取的字符數量。

下圖以LEFT函數為例，對C列數據進行拆分。

以上是數據拆分的内容，接下來輪到數據轉換啦~

數據轉換

數據轉換通常包括行列轉換和數據類型轉換。

1 行列轉換

行列轉換比較簡單，可直接選擇需要轉換的數據範圍，進行“複制”、“粘貼”，唯一需要注意的是在粘貼時使用“粘貼選項-轉置”即可。

2 數據類型轉換

數據類型轉換涉及到文本轉數值、數值轉文本、數值日期轉日期等。可以通過函數VALUE()、TEXT()來實現。

VALUE(text)：将表示數字的文本數據轉為數字。

text：用引号括起來的文本或包含要轉換文本的單元格的引用。

TEXT(value, format_text)：數值數據轉為文本數據。

value：要轉換為文本的數值；

format_text：一個文本字符串，定義要應用于所提供值的格式。

下面以TEXT()函數為例，對F列數據進行轉換。将F列數字轉換為文本類型數據。

下圖示例中，我們還可以用VALUE()函數将H列的文本數據轉換為數字。

以上就是本次分享的全部内容~

這些内容僅僅是數據分析知識的冰山一角，想要更深入的學習數據分析相關技巧，可以關注戎易大數據~

我們接下來還會繼續分享更多和數據分析相關的内容。

整理不易，點贊、轉發幫忙點一下啦

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!