作者 | SUNIL RAY

編譯 | CDA數(shù)據(jù)分析師

Simple Yet Powerful Excel Tricks for Analyzing Data

• Microsoft Excel是目前世界上被使用的最廣泛的數(shù)據(jù)分析工具之一
• 使用Excel進行數(shù)據(jù)分析是使用R或Python進行數(shù)據(jù)科學(xué)的先驅(qū)
• 我們應(yīng)該要學(xué)習(xí)用于分析數(shù)據(jù)的基本的Excel函數(shù)

介紹

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

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

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

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

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

常用函數(shù)

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

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

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

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

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

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

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

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

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

語法：= Len（文本）

例如：= Len（B3）= 23

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

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

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

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

語法：= Trim（文本）

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

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

從數(shù)據(jù)產(chǎn)生推理

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

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

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

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

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

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

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

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

數(shù)據(jù)清理

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

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

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

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

1. 選擇范圍A1：A6
2. 轉(zhuǎn)到“數(shù)據(jù)”功能區(qū)–>“文本到列”

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

基本鍵盤快捷鍵

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

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

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

結(jié)語

Excel可以說是有史以來最好的程序之一，它一直是全球幾乎所有企業(yè)的黃金選擇標(biāo)準(zhǔn)。但是，無論你是新手還是高級用戶，總有一些東西需要學(xué)習(xí)。