第2章

數字金融人才必備辦公技能1目錄CONTENT201

Excel在金融中的應用PowerBI在金融中的應用02學習目標：知識目標：（1）理解Excel的基本操作和外部數據獲取方法；（2）掌握金融統計分析的常用公式和常見函數；（3）掌握金融數據的統計與管理方法以及可視化方法；（4）理解PowerBI數據連接與數據清洗方法。技能目標：（1）能夠運用Excel進行基本編輯；（2）能夠運用Excel進行金融數據的分析和可視化；（3）能夠運用PowerBI快速創建數據可視化報告。32.1Excel在金融中的應用2.1.1Excel基本操作在Windows10操作系統中，打開【開始】菜單，找到Excel2016的圖標并單擊，或者雙擊桌面上的Excel2016圖標啟動Excel2016，打開的Excel2016用戶界面如圖2-1所示。單擊窗口控制按鈕中的【關閉】按鈕，如圖2-2所示，按組合鍵【Alt+F4】也可關閉Excel2016。1．Excel的啟動和關閉圖2-1

Excel2016用戶界面圖2-2

Excel2016【關閉】按鈕52．Excel用戶界面介紹圖2-3

Excel2016用戶界面組成63．Excel工作簿、工作表和單元格的基本操作（1）打開和關閉Excel工作簿（2）保護Excel工作簿（3）工作表的基本操作（4）凍結工作表窗格（5）保護工作表（6）選中單元格（7）選中單元格區域（8）插入與刪除單元格（行、列）（9）合并單元格與取消單元格合并72.1Excel在金融中的應用2.1.2外部數據的獲取獲取TXT文本數據選擇要導入數據的TXT文件。在【導入數據】對話框中，選擇要導入數據的TXT文件，如圖2-19所示，單擊【導入】按鈕。獲取CSV文本數據選擇要導入數據的CSV文件。在【導入文本文件】對話框中，選擇要導入數據的CSV文件。獲取MySQL數據庫中的數據打開【ODBC數據源管理程序(64位)】對話框。單擊【開始】→【Windows管理工具】→【ODBC數據源(64位)】，在打開的【ODBC數據源管理程序(64位)】對話框中單擊【添加】按鈕，

設置參數。1232.1Excel在金融中的應用2.1.3使用公式和函數處理數據1．公式公式是工作表中用于對單元格數據進行各種運算的等式，它必須以等號“=”開頭。一個完整的公式通常由運算符和操作數組成。圖2-4

輸入公式2．函數函數類型函數作用語法文本處理函數TRIM除了單詞之間的單個空格之外，移除文本中的空格TRIM(text)CONCATENATE將兩個或多個文本字符串聯接為一個字符串CONCATENATE(text1,[text2],...)REPLACE將特定位置的字符串替換為不同的文本字符REPLACE(old_text,start_num,num_chars,new_text)SUBSTITUE在某一文本字符串中替換指定的文本SUBSTITUTE(text,old_text,new_text,[instance_num])LEFT從文本字符串左側第一個字符開始返回指定個數的字符LEFT(text,[num_chars])RIGHT從文本字符串右側第一個字符開始返回指定個數的字符RIGHT(text,[num_chars])MID從指定位置開始提取特定數目的字符MID(text,start_num,num_chars)EXACT比較兩個文本字符串，如果它們完全相同，則返回TRUE，否則返回FALSEEXACT(text1,text2)LEN返回文本中字符的個數，一般和其他函數配合使用LEN(text)FIND查找一個字符串在另一個字符串中出現的起始位置FIND(find_text,within_text,start_num)SEARCH查找并返回該字符串的起始位置的編號SEARCH(find_text,within_text,start_num)10函數類型函數作用語法信息函數ISBLANK可檢驗是否引用了空單元格，返回TRUE或FALSEISBLANK(value)查找與引用函數VLOOKUP在表格區域中按行查找對應內容VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])HLOOKUP在單元格區域中按列查找對應內容HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])INDEX返回表格或區域中的值或值的引用INDEX(array,row_num,[column_num])MATCH在范圍單元格中搜索特定的項，然后返回該項在此區域中的相對位置MATCH(lookup_value,lookup_array,[match_type])邏輯運算函數IF對值和期待值進行邏輯比較IF(logical_test,value_if_true,[value_if_false])IFERROR如果公式的計算結果錯誤，則返回您指定的值；否則返回公式的結果IFERROR(value,value_if_error)IFNA如果公式返回錯誤值

#N/A，則結果返回您指定的值；否則返回公式的結果IFNA(value,value_if_na)AND對多個邏輯值進行邏輯與計算AND(logical1,logical2,…)OR對多個邏輯值進行邏輯或計算OR(logical1,logical2,…)函數類型函數作用語法數學統計函數SUM求和SUM(number1,[number2],...)SUMIF對符合條件的值求和SUMIF(range,criteria,[sum_range])COUNT計算包含數字的單元格個數以及參數列表中數字的個數COUNT(value1,[value2],...)COUNTIF統計滿足某個條件的單元格的數量COUNTIF(range,criteria)MAX/MIN計算選中區域的最大值/最小值。MAX(number1,[number2],...)，MIN(number1,[number2],...)LARGE返回數據組中第k個最大值LARGE（array,k）SMALL返回數據組中第k個最小值SMALL（array,k）RANK返回某數字在一列數字中的排位RANK(number,ref,[order])AVERAGE返回參數的平均值AVERAGE(number1,[number2],...)PRODUCT返回所有參數的乘積PRODUCT(number1,[number2],...)QUOTIENT返回除法的整數部分QUOTIENT（numerator,denominator）ROUND將數值四舍五入到指定的位數ROUND（number,num_digits）FREQUENCY返回一個垂直數組，統計在某個區域內的出現頻率FREQUENCY(data_array,bins_array)函數類型函數作用語法日期時間函數DATEDIF計算兩個日期間隔的年數、月數、天數DATEDIF(start_date,end_date,unit)NETWORKDAYS返回兩個日期之間的工作日個數NETWORKDAYS(start_date,end_date,[holidays])NOW返回當前的日期和時間NOW()TODAY返回當前日期TODAY()WEEKDAY返回對應日期為一周中第幾天WEEKDAY(serial_number,[return_type])WEEKNUM返回對應日期在一年中的第幾周WEEKNUM(serial_number,[return_type])DATE將三個獨立的值合并為一個日期DATE(year,month,day)格式顯示函數TEXT根據指定數值格式將數字轉成文本TEXT(value,format_text)UPPER/LOWER分別將text以大寫和小寫字母的形式輸出UPPER(text)、LOWER(text)ROUD將數字四舍五入到指定的位數ROUND(number,num_digits)132.1Excel在金融中的應用2.1.4金融數據的統計與管理1．排序

排序的方法包括根據單個關鍵字排序、根據多個關鍵字排序和自定義排序等。

在“訂單信息”工作表中先根據會員名進行升序排列，再將相同會員名的訂單根據店鋪名進行降序排列。接著，根據店鋪所在地進行自定義排序。

圖2-5

排序參數設置圖2-6

【排序】對話框的參數設置142.1.4金融數據的統計與管理1．排序在打開的【自定義序列】對話框的【輸入序列】列表框中輸入“珠海”“深圳”“佛山”“廣州”，如圖2-7所示，單擊【添加】按鈕。圖2-7

自定義序列152.1.4金融數據的統計與管理2．篩選

在Excel中，篩選是一種可以快速查找出目標數據的方法，因此在面對大量數據時，可以通過篩選找出所需的數據。篩選的方法主要有簡單篩選和高級篩選。

在“訂單信息”工作表中篩選出店鋪所在地為“珠海”的數據。圖2-8篩選參數設置162.1.4金融數據的統計與管理2．篩選

在打開的【自定義自動篩選】對話框中，根據圖2-9所示進行參數設置，單擊【確定】按鈕即可得到結果。在打開的【自定義自動篩選】對話框中，根據圖2-10所示進行參數設置，單擊【確定】按鈕即可得到結果。圖2-9選擇【自定義篩選】選項圖2-10

自定義自動篩選參數設置172.1.4金融數據的統計與管理2．篩選

新建一個工作表并輸入篩選條件，如圖2-11所示。

選中“訂單信息”表的任一非空單元格，在【數據】選項卡的【排序和篩選】命令組中，單擊【高級】按鈕。在打開的【高級篩選】對話框中，在【列表區域】文本框中輸入“$A$1:$G$942”，在【條件區域】文本框中輸入“篩選條件!$A$1:$B$2”，如圖2-12所示。

圖2-12【高級篩選】對話框中的參數設置圖2-11

輸入篩選條件182.1.4金融數據的統計與管理2．篩選

新建一個工作表并輸入篩選條件，如圖2-11所示。

選中“訂單信息”表的任一非空單元格，在【數據】選項卡的【排序和篩選】命令組中，單擊【高級】按鈕。在打開的【高級篩選】對話框中，在【列表區域】文本框中輸入“$A$1:$G$942”，在【條件區域】文本框中輸入“篩選條件!$A$1:$B$2”，如圖2-12所示。

圖2-12【高級篩選】對話框中的參數設置圖2-11

輸入篩選條件192.1.4金融數據的統計與管理3．分類匯總

分類匯總可按照設定的字段對數據進行分組，并在此基礎上統計其他需要求和、求平均值和計數等計算操作的字段。分類匯總的方法主要有簡單分類匯總、高級分類匯總和嵌套分類匯總。在“訂單信息”工作表中完成：統計各會員消費金額的總和；統計各會員消費金額的平均值；先對會員名進行簡單分類匯總，再對店鋪名進行分類匯總。具體操作如下。

圖2-13【分類匯總】對話框中的參數設置202.1.4金融數據的統計與管理4．數據透視表

在Excel中，數據透視表可以轉換行和列，用于展示源數據的不同匯總結果，也可以用于展示不同頁面篩選的數據，還可以根據用戶的需求展示數據區域中的數據。在“訂單信息”工作表中創建數據透視表、編輯透視表并操縱透視表中的數據。具體操作如下。

圖2-14【數據透視表字段】對話框參數設置2.1Excel在金融中的應用2.1.5數據分析與可視化1．條形圖

條形圖以寬度相等但長度有差異的條形來區別統計數值。在條形圖中，通常縱坐標軸用于展示類別，橫坐標軸用于展示數值。

在“部門員工信息表”工作表中根據各部門員工性別分布情況繪制簇狀條形圖。具體結果如下。

圖2-15各部門員工性別分布的簇狀條形圖2．折線圖

折線圖常用于展示數據隨時間或有序類別變化而變化的趨勢。折線圖是點、線連在一起的圖表，可反映事物的發展趨勢和分布情況，適合在不需要突出單個數據點的情況下展示變化趨勢、增長幅度。

在“季度銷售任務完成情況表”工作表中分析已購買客戶數量和銷售額之間的關系。具體結果如下。圖2-16

折線圖最終效果3．餅圖

餅圖以一個完整的圓來表示數據對象的全體，其中的扇形表示各個組成部分。餅圖常用于展示百分比的構成，其中每一個扇形都代表一類數據所占的百分比。在“省份利潤”工作表中分析利潤在各省的分布。具體結果如下。圖2-17

餅圖最終效果2.2PowerBI在金融中的應用2.2.1PowerBI概述

本節使用PowerBI2.119.986.064-bit版本，下載并完成安裝后，雙擊桌面圖標即可打開PowerBI軟件。PowerBI軟件界面包括標題欄、導航欄、報表畫布、報表編輯器和狀態欄這5個部分，如圖2-18所示。1．PowerBI軟件界面圖2-18

PowerBI軟件界面2.2.1PowerBI概述2．PowerBI的3種視圖PowerBI有3種視圖，分別是報表視圖、數據視圖和關系視圖，如圖2-19所示。視圖切換按鈕在界面的最左側，單擊即可完成切換。圖2-19

PowerBI視圖2．PowerBI的3種視圖（1）報表視圖

在報表視圖中，用戶可以借助【可視化】窗格中的可視化對象，在若干報表頁面上創建可視化內容，制作可視化報表。在此視圖下，用戶還可以對可視化對象和報表頁面進行復制、粘貼等操作。（2）數據視圖

在數據視圖中，用戶可以對所需的數據進行處理和檢查。數據視圖由導航欄、公式欄、數據網格、數據列表這4個部分組成，如圖2-20所示。圖2-20

數據視圖2．PowerBI的3種視圖（3）關系視圖

關系視圖用于對模型中的關系進行管理。當模型中有很多表格且各表格關系復雜時，可借助關系視圖對表格進行管理，如圖2-21所示。圖2-21

關系視圖2.2PowerBI在金融中的應用2.2.2數據連接與數據清洗

PowerBI提供了多種不同類型的數據源的連接，用戶可單擊【主頁】選項卡的【獲取數據】下方的下拉按鈕，在打開的下拉列表中選擇連接不同類型的數據，如圖2-22所示。1．數據連接圖2-22

【獲取數據】下拉列表2．數據清洗

數據清洗是指發現并糾正數據文件中可識別的錯誤，包括檢查數據一致性、處理無效值和缺失值等。①設置標題行。②修改數據類型。③刪除空值。④日期提取。⑤透視與逆透視。2.2PowerBI在金融中的應用2.2.3可視化圖表及視覺對象

折線圖可以顯示隨時間變化而變化的連續數據，非常適用于顯示在相同時間間隔下的數據趨勢。①打開PowerBI，將“8～10月銷售明細匯總表.xlsx”加載到PowerBI中，再通過查詢編輯器進行數據預處理。1.以折線圖為例講解可視化圖表及視覺對象圖2-23

未設置的可視化對象折線圖2.2.3可視化圖表及視覺對象1.以折線圖為例講解可視化圖表及視覺對象

②將【數據】窗格中的字段拖入【可視化】窗格中的【X軸】和【Y軸】中，具體參數設置如圖2-24所示。圖2-24【可視化】窗格中的參數設置2.2.3可視化圖表及視覺對象1.以折線圖為例講解可視化圖表及視覺對象

③優化可視化對象的格式。在【可視化】窗格中單擊【設置視覺對象格式】按鈕，如圖2-138所示，切換到【設置視覺對象格式】界面，在其中即可對對象的X軸、Y軸、數據顏色、數據標簽、標題和背景等進行格式化設置，最終效果如圖2-25所示。圖2-25

【設置視覺對象格式】按鈕圖2-26

折線圖效果2.2PowerBI在金融中的應用2.2.4可視化報表1．報表頁的基本操作

想要生成一張合適的報表，前期準備是很有必要的。如果把報表看成一本書，那么不同頁面之間的關聯，以及每一頁要呈現什么內容都需要設計清楚。①新建報表頁。②移動和復制報表。③刪除報表。

2．添加視覺對象

①

添加簡單對象。在PowerBI的【插入】選項卡中單擊【文本框】按鈕，報表畫布中將會呈現默認的文本框樣式。與此同時，在文本框的旁邊會出現文本框編輯工具欄，使用其中的文本編輯工具，可以進行字體、字號、文字顏色、加粗、傾斜、下畫線和文本對齊方式等操作。在【格式】窗格中可以對文本框進行設置，如圖2-27所示。圖2-27

文本框設置2．添加視覺對象

②

添加可視化視覺對象。導入實驗素材“訪客數據.xlsx”和“流量數據.xlsx”在報表畫布正中，選擇訪客數、收藏人數和加購人數、成交轉化率和成交訂單數，分別以折線圖、簇狀柱形圖、折線和堆積柱形圖和瀑布圖來呈現。將4個可視化視覺對象分上下兩行布局，第一行呈現訪客數等人數變化情況，第二行呈現成交轉化率和成交訂單數情況。結果如圖2-28所示。圖2-28

添加可視化視覺對象效果2．添加視覺對象

②

添加可視化視覺對象。導入實驗素材“訪客數據.xlsx”和“流量數據.xlsx”在報表畫布正中，選擇訪客數、收藏人數和加購人數、成交轉化率和成交訂單數，分別以折線圖、簇狀柱形圖、折線和堆積柱形圖和瀑布圖來呈現。將4個可視化視覺對象分上下兩行布局，第一行呈現訪客數等人數變化情況，第二行呈現成交轉化率和成交訂單數情況。結果如圖2-28所示。圖2-28

添加可視化視覺對象效果3．添加篩選器和切片器

①

篩選器使用。針對“各流量來源成交轉化率”可視化對象，在【篩選器】窗格中設置參數，如圖2-29所示，便可在該視覺對象上應用篩選功能。圖2-29【篩選器】窗格參數設置3．添加篩選器和切片器

②

創建切片器。單擊【可視化】窗格中的【切片器】按鈕，將【數據】窗格中的“日期”拖入【可視化】窗格的【字段】中，如圖2-30所示。將切片器對象放置在文本框區域，并