用Excel來進行數據分析

2021-02-24 律網工作坊

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」的總和作為值。現在你準備好使用數據透視表了,它顯示地區和產品保費總和。您還可以使用計數、平均值、最小值、最大值和其他匯總度量。。

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

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

數據清理

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

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

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

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

選擇範圍A1:A6轉到「數據」功能區–>「文本到列」

在上方,我們有兩個選項「分隔符號」和「固定寬度」。我選擇分隔符號的原因是因為值由分隔符(;)分隔。如果我們想根據寬度拆分數據(例如,前四個字符為第一列,第五個字符到第十個字符為第二列),則可以選擇固定寬度。單擊下一步–>標記「半冒號」複選框,然後單擊下一步並完成。

基本鍵盤快捷鍵

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

Ctrl + [向下|向上箭頭]:移動到當前列的頂部或底部單元格,並結合ctrl+[左|右]箭頭鍵,移動到當前行中最左側或最右側的單元格Ctrl + Shift +向下/向上箭頭:選擇當前單元格上方或下方的所有單元格Ctrl +主頁:移動到單元格A1Ctrl + End:移動到包含數據的最後一個單元格Alt + F1:基於所選數據集創建一個圖表。Ctrl + Shift + L:激活對數據表的自動篩選Alt +向下箭頭:打開自動篩選的下拉菜單。使用此快捷方式:Alt + D + S:對數據集進行排序Ctrl + O:打開一個新工作簿Ctrl + N:創建一個新工作簿F4:選擇範圍並按F4鍵,它將參考值更改為絕對值,混合值和相對值。注意:這不是一個完全的快捷鍵列表。你可以在下面的評論部分中隨意分享你在Excel中最喜歡的鍵盤快捷鍵。實際上,我使用這些快捷方式基本上完成了80%的excel任務。

結語

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

相關焦點

  • 使用簡單而強大的Excel來進行數據分析
    使用Excel進行數據分析是使用R或Python進行數據科學的先驅我們應該要學習用於分析數據的基本的Excel函數介紹我一直都很佩服Excel強大的數據分析能力。這款軟體不僅能夠進行基本的數據計算,還可以使用它來進行數據分析。它被廣泛用於許多的領域內,包括財務建模和業務規劃等。對於數據分析領域的新手來說,Excel它可以成為一個很好的跳板。甚至來說在學習R或Python之前,最好先了解一下Excel。將Excel添加到你的技能庫中沒有什麼壞處。
  • 別人都在用數據分析軟體,你還在用excel做數據分析?
    之前聽朋友吐槽過,他們是上千人的企業,但做數據分析居然還是靠手動上傳數據,而且還是用的excel做的。但其實excel並不是企業做數據分析的好工具。數據分析是指用適當的統計分析方法對收集來的大量數據進行分析,提取有用信息和形成結論而對數據加以詳細研究和概括總結的過程。
  • 像Excel一樣使用python進行數據分析
    Excel是數據分析中最常用的工具,本篇文章通過python與excel的功能對比介紹如何使用python通過函數式編程完成excel中的數據處理及分析工作。df=pd.DataFrame(pd.read_csv('name.csv',header=1))df=pd.DataFrame(pd.read_excel('name.xlsx'))創建數據表另一種方法是通過直接寫入數據來生成數據表,excel中直接在單元格中輸入數據就可以,python中通過下面的代碼來實現
  • 別人都在用數據分析軟體了,不要再用excel做數據分析了
    之前聽朋友吐槽過,他們是上千人的企業,但做數據分析居然還是靠手動上傳數據,而且還是用的excel做的。但其實excel並不是企業做數據分析的好工具。 數據分析是指用適當的統計分析方法對收集來的大量數據進行分析,提取有用信息和形成結論而對數據加以詳細研究和概括總結的過程。
  • 像Excel一樣使用R進行數據分析(3)
    Excel是數據分析中最常用的工具,本篇文章通過R與excel的功能對比介紹如何使用R通過函數式編程完成excel中的數據處理及分析工作。
  • 像Excel一樣使用R進行數據分析(1)
    Excel是數據分析中最常用的工具,本篇文章通過R與excel的功能對比介紹如何使用R通過函數式編程完成excel中的數據處理及分析工作。
  • 用Excel進行數據的Weibull數據分析
    很久前我是覺得數據分析對數學的要求很高,自認為數學不好所以搞不定。後來是工作需要沒辦法,只要硬著頭皮上。今天和大家一起來一步步看看用Excel進行數據的Weibull分析怎麼做,不進行編程,只是利用Excel自身的功能來進行。
  • 如何用SPSS進行數據分析?
    1.1 SPSS的核心功能1.2 數據編輯功能可以通過SPSS的數據編輯功能,對數據進行增刪改等處理,還可以根據需要對數據進行拆分、加權、排序、聚合等處理。1.6 統計功能CDA數據分析師認為SPSS統計功能是進行數據分析要重點掌握的模塊,通過此功能可以完成絕大部分數理統計模型分析,其中包括:回歸分析、列聯表分析、聚類分析、因子分析、相關分析、對應分析、時間序列分析、判別分析等。
  • Maxdiff系列(六)MaxDiff的數據分析(用Excel進行logit分析)
    當然,上面這種數據還不能直接進行分析,必須進行進一步的編碼。這裡插一句,在數據分析中,編碼(coding)是相當重要的環節。我們每個任務都被編碼了兩次,用於分析的實際任務總數也就由6個變成了12個。把所有任務按照以上編碼方式編碼,會得到下圖的數據(這種編碼也是大多數軟體進行條件邏輯分析時可以直接調用的數據格式)
  • 如何用Excel進行預測分析?
    請使用Excel進行分析。【分析思路】第1日(次日)留存用戶數=第1日新增用戶數*次日留存率第2日活躍用戶數=第2日新增用戶數+第1日留存用戶數第3日活躍用戶數=第3日新增用戶數+第2日留存用戶數(第2日新增用戶數*第2日留存率)+第1日留存用戶數...
  • 數據分析9 - 數據分析常用excel函數筆記整理
    在數據分析中,excel可以方便快速的分析小樣本數據,熟練運用excel函數也很重要。「不會因為會python成為好的數據分析師,而是能用任何工具解決問題」,其實數據分析師重要的是解決問題,發現問題,一起學起來吧
  • 5個步驟,用SPSS進行數據分析
    SPSS是一款非常強大的數據處理軟體,那麼該如何用SPSS進行數據分析呢? 統計功能 小編認為SPSS統計功能是進行數據分析要重點掌握的模塊,通過此功能可以完成絕大部分數理統計模型分析,其中包括:回歸分析、列聯表分析、聚類分析、因子分析、相關分析、對應分析、時間序列分析、判別分析等。 如何用SPSS進行數據分析?
  • 數據分析案例:用excel實現最低總成本的決策分析
    1、首先依據題目給定的數據建立數據模型2、繪製不同單位訂貨費下的年總成本曲線簇建立以一次訂貨費D5(30、40、50、60和當前假設的訂貨費50)為行自變量,以每次訂貨量D9(1-14、當前假設的訂貨量D7-0.001、D7、40)為列自變量,全年總成本D15
  • Excel如何通過函數來對數據進行比對?
    數據的比對可以通過函數來進行,下面來看看函數如何進行數據比對吧。1.首先打開excel表格,要將標價和實收金額兩組數據進行比對。3.然後選擇兩個單元格的數據中間用逗號隔開。5.true代表數據一致的意思,false 就是數據不一致的意思。
  • 你連Excel都用不好,怎麼證明你的數據分析能力?
    對於很多競價員和優化師的工作內容而言,我們可以用一個字「表」來概括。處理EXCEL表是我們必要的一項工作,而靈活使用是我們必要掌握的一項能力。毫不誇張地說,精通excel可以幫助我們提升70%的工作效率。那麼今天我們一起來學習下:幾個Excel高效處理數據技巧。
  • Excel可視化圖表製作數據分析工具?
    Excel直連資料庫,不用在各種平臺導數據,數據分析不求人。直接使用Excel的計算公式,Excel的圖形,Excel透視表來分析和展現數據。您的Excel技能在雲時代更有含金量。內網數據全面上雲將企業內網的ERP/OA/CRM等資料庫,通過智分析的本地數據網關接入雲端,讓員工分析數據不受地理位置束縛。支持各類雲端資料庫,本地excel數據導入。
  • excel高手不加班的神器:用數據透視表輕鬆做匯總分析!
    課程信息卡課程:《Excel天天訓練營》2.0圖文版章節:第1章-提高效率內容:excel數據透視表如果你諮詢過一些excel高手,會發現他們會經常提及數據透視表。在excel裡面,數據透視表確實可以算作一個神器,因為用它不需要會寫函數公式,也可以非常輕鬆的做數據匯總和數據分析。現在,我們就來講下excel高手不加班的神器:用數據透視表輕鬆做匯總分析!
  • 用Excel進行數據分析:描述性統計分析
  • excel中利用Ctrl加T生成的超級表進行數據分析與處理
    大家好,在excel操作中,ctrl+T是容易被忽略卻又十分強大的一個功能。簡單地說,ctrl+T是基於普通表格生成的「超級表」,是普通表格的功能的延伸擴展,在某些方面擁有了數據透視表的功能。對於普通表格,直接按下ctrl+T就可能生成」超級表「,並且會在主選項卡生成一個設計的表格工具,我們可以利用設計中的功能進行圖片美化、刪除重複值等操作(如下圖),也可利用切片器和數據透視表等分析圖表。
  • Excel和Python常用功能的操作(數據分析)
    Excel與Python都是數據分析中常用的工具,本文介紹這兩種工具是如何實現數據的讀取、生成、計算、修改、統計、抽樣、查找、可視化、存儲等數據處理中的常用操作!數據保存Excel:左上角點擊保存Python:data.to_excel("name.xlsx")4.