作者 | SUNIL RAY

編譯 | CDA數據分析師

Simple Yet Powerful Excel Tricks for Analyzing Data

• Microsoft Excel是目前世界上被使用的最廣泛的數據分析工具之一
• 使用Excel進行數據分析是使用R或Python進行數據科學的先驅
• 我們應該要學習用于分析數據的基本的Excel函數

介紹

我一直都很佩服Excel強大的數據分析能力。這款軟件不僅能夠進行基本的數據計算，還可以使用它來進行數據分析。它被廣泛用于許多的領域內，包括財務建模和業務規劃等。對于數據分析領域的新手來說，Excel它可以成為一個很好的跳板。

甚至來說在學習R或Python之前，最好先了解一下Excel。將Excel添加到你的技能庫中沒有什么壞處。Excel具有非常廣泛的功能：可視化功能、數組，使你能夠迅速的通過數據產生洞察力，否則這些數據將很難看到價值。

當然Excel它也有一些缺點。比如它不能非常有效地處理大型的數據集。相信每個人都已經遇到了這個問題。當你嘗試對大約200,000個條的數據進行數據計算的時候，你會注意到excel開始出現問題。當然有一些方法可以解決這個問題并在一定程度上處理這些數據，但是Excel并不是一個處理大數據的工具。在有大型數據集的情況下，R或Python是最好的選擇。

我很幸運，我學習的旅程始于Excel。多年來，我學到了許多技巧，學會了許多可以更快處理數據的技巧。Excel具有許多功能。選擇最好的一個功能有時會會很令人困惑的。在本文中，將為大家提供一些在Excel的技巧，這樣可以節省工作或處理數據的時間。這篇文章比較適合那些熱衷于升級其數據分析技能的人們。

需要注意的是：如果你認為自己是數據科學領域的大師級人員，那么你可能覺得這篇文章對你來說不會有任何幫助。對于其他人，我建議你練習這些技巧以對它們有一個具體的了解。

常用函數

1. Vlookup（）：這個函數有助于在表中搜索值并返回相應的值。讓我們看一下下表（Policy和Customer）。在“Policy”表中，我們希望基于公共的字段“Customer_id”從Customer表中找到“City”的名稱。在這里，函數vlookup（）就可以幫助你完成這項工作。

語法：= VLOOKUP（查找的值，要查找的區域，查找區域的第幾列，是否相對匹配）

對于上述問題，我們可以在單元格“ F4”中將公式寫為= VLOOKUP（B4，$ H $ 4：$ L $ 15，5，0），這將返回所有客戶ID 1的城市名稱并將該公式復制到所有的客戶ID中。

提示：不要忘記使用“ $”符號鎖定第二個表的范圍，因為如果不鎖定的話，可能會在向下復制公式的時候出現錯誤。而這就是所謂的相對引用。

2. CONCATINATE（）：將兩個或多個單元格中的文本合并為一個單元格非常有用。例如：我們要基于主機名和請求路徑的輸入來創建URL。

語法：= Concatenate（文本1，文本2，.....文本n）

那么我們可以使用公式= concatenate（B3，C3）并向下復制公式來解決上述問題。

提示：當然我更喜歡使用“＆”符號，因為它比鍵入完整的“連接”公式要短，并且執行出來完全相同的結果。該公式也可以寫為“ = B3＆C3”。

3. LEN（） 這個函數告訴你單元格的長度，也就是包括空格和特殊字符在內的字符數。

語法：= Len（文本）

例如：= Len（B3）= 23

4. LOWER（），UPPER（）和PROPER（） –這三個函數分別幫助我們可以將文本更改為小寫，大寫和句子大小寫（每個單詞的首字母大寫）。

語法：=upper（文本）/lower（文字）/proper（文字）

在數據分析項目中，這些有助于將不同案例的類別轉換為單個情況下的類，否則將它們視為給定要素的不同類別?？聪旅娴恼掌@種，A列有五個類（標簽），其中B列只有兩個類（因為我們已將內容轉換為小寫）。

5. TRIM（）： 這是一個非常方便的函數，用于清除文本中開頭和結尾的空白。通常，當你從數據庫中獲取數據進行單獨存儲的時候，你要處理的文本中的一些可能會被填充為空白。而且，如果你不處理它們，它們也將被視為列表中的一條數據，這對你來說肯定是沒有幫助的。

語法：= Trim（文本）

6. If（）：我發現這個函數是excel中最有用價值的函數之一。它使你可以使用條件公式，當某件事為真時，它使用一種計算方式，而當某件事為假時，則進行另一種計算方式。例如，你要將每個銷售標記為“高”和“低”。如果銷售額大于或等于$ 5000，則標記為“高”，否則標記為“低”。

語法：= IF（條件，條件為真的計算，條件為假的計算）

從數據產生推理

1.數據透視表： 當你處理公司的數據時，你都在尋找問題的答案，例如“北部地區的分公司貢獻了多少收入？”或“產品A的平均客戶數量是多少？”等等。

Excel的數據透視表可幫助你輕松地回答這些問題。數據透視表是一個匯總表，可以讓你根據所選的特征進行計數，求平均值，求和并執行其他計算，即，它將數據表轉換為推理表，這有助于我們做出決策。查看下面的圖片：

在上面，你可以看到左側的表具有針對每個客戶的銷售明細，并提供了區域和產品。在右表中，我們總結了區域級別的信息，現在可以幫助我們生成一個推論，也就是南部地區的銷售額最高。

創建數據透視表的方法： 步驟1：單擊數據列表中的某處。選擇插入選項卡，然后單擊數據透視表。Excel將自動選擇包含數據（包括標題）的區域。如果沒有正確選擇區域，請在區域上拖動以手動選擇合適的區域。最好將數據透視表放在新的工作表上，因此單擊“ 新建工作表”作為位置，然后單擊“ 確定”。

步驟2：現在，你可以看到“數據透視表字段列表”的面板，其中包含列表中的字段；你需要做的就是將它們放在面板底部的框框中。完成此操作后，左側的圖表將成為數據透視表。

在上面，你可以看到我們在行中安排了“Region”，在列中安排了“Product id”，并以“Premium”的總和作為值?，F在你準備好使用數據透視表了，它顯示地區和產品保費總和。您還可以使用計數、平均值、最小值、最大值和其他匯總度量。。

2.創建圖表：在excel中創建圖表只需要選擇要繪制的數據范圍并按F11即可。這會創建一個默認圖表樣式的excel圖表，但您可以通過選擇不同的圖表樣式來更改它。如果您希望圖表與數據位于相同的工作表上，請按ALT + F1而不是按F11。

當然，無論哪種情況，一旦創建了圖表，就可以根據自己的特定需求進行自定義圖表，以傳達所需的消息。

數據清理

1.刪除重復值： Excel具有內置的功能，可以從表中刪除重復值。它根據選定的列從給定表中刪除重復的值，即，如果你選擇了兩列，則它將搜索具有兩列數據相同組合的重復值。

在上面，您可以看到A001和A002有重復的值，但是如果我們同時選擇“ID”和“Name”列，那么我們只有一個重復的值(A002, 2)。請按照以下步驟刪除重復值：選擇數據–>轉到數據功能區–>刪除重復項

2.文本分列： 假設你將數據存儲在列中，如下圖片所示。

在上面，你可以看到值之間用分號“;”分隔?，F在要將這些值拆分到不同的列中，我建議在excel中使用“ 分列 ”功能。請按照以下步驟將其轉換為不同的列：

1. 選擇范圍A1：A6
2. 轉到“數據”功能區–>“文本到列”

1. 在上方，我們有兩個選項“分隔符號”和“固定寬度”。我選擇分隔符號的原因是因為值由分隔符（;）分隔。如果我們想根據寬度拆分數據（例如，前四個字符為第一列，第五個字符到第十個字符為第二列），則可以選擇固定寬度。
2. 單擊下一步–>標記“半冒號”復選框，然后單擊下一步并完成。

基本鍵盤快捷鍵

鍵盤快捷鍵是快速瀏覽單元格或更快輸入公式的最佳方法。在下面列出了我們的經常會使用的一些快捷鍵。

1. Ctrl + [向下|向上箭頭]： 移動到當前列的頂部或底部單元格，并結合ctrl+[左|右]箭頭鍵，移動到當前行中最左側或最右側的單元格
2. Ctrl + Shift +向下/向上箭頭： 選擇當前單元格上方或下方的所有單元格
3. Ctrl +主頁： 移動到單元格A1
4. Ctrl + End： 移動到包含數據的最后一個單元格
5. Alt + F1： 基于所選數據集創建一個圖表。
6. Ctrl + Shift + L：激活對數據表的自動篩選
7. Alt +向下箭頭： 打開自動篩選的下拉菜單。使用此快捷方式：
8. Alt + D + S： 對數據集進行排序
9. Ctrl + O：打開一個新工作簿
10. Ctrl + N：創建一個新工作簿
11. F4：選擇范圍并按F4鍵，它將參考值更改為絕對值，混合值和相對值。

注意：這不是一個完全的快捷鍵列表。你可以在下面的評論部分中隨意分享你在Excel中最喜歡的鍵盤快捷鍵。實際上，我使用這些快捷方式基本上完成了80％的excel任務。

結語

Excel可以說是有史以來最好的程序之一，它一直是全球幾乎所有企業的黃金選擇標準。但是，無論你是新手還是高級用戶，總有一些東西需要學習。