大數據時代Excel的基本應用概述

馬道京 陳有源 宋海波

摘 要：本文概述了在大數據背景條件下，Excel的基本應用技巧、查詢方式及數據透視表的使用，介紹了大數據平臺架構與Excel業務分析如何實現直連的解決方案。

關鍵詞：大數據;Excel;應用

大數據是指無法在一定時間范圍內用常規軟件工具進行捕捉、管理和處理的數據集合，是需要新處理模式才能具有更強的決策力、洞察發現力和流程優化能力的海量、高增長率和多樣化的信息資產。

具體多大的數據才能稱之為“大”，并未有一個普遍和具體的量度，但其數據規模如此龐大，以至于不能用G或T來衡量，處理這些數據更好地是選擇專業化的大數據處理工具，如果這些大量的數據用Excel來分析的話，連載入內存都不可能。但是，對于我們普通的用戶而言，這些工具顯得過于專業，如果想要了解大數據處理的原理方法或是完成一些簡單的業務分析，在數據規模適量的情況下，Excel作為一個入門級工具，也何嘗不是一個快速分析數據的理想工具，熟練使用它，可以加快數據分析的時間，提高查詢效率，也能創建供內部使用的數據圖，也可以與其他大數據處理工具直連配合使用，具有一定的實際價值。

一、Excel批量數據操作技巧

（一）基本技巧

（1）“&”連接符實現批量數據的合并。具體操作：選擇單元格或英文狀態下雙引號括起來的字符串，填入“&”符號，后緊跟合并單元個或字符串。

（2）區域快速求和。具體操作：選擇計算區域，然后按下快捷鍵“ALT+=”。

（3）數據批量填充。具體操作：使用F8擴展區域方式選連續的多單元格：先選第1個有公式的單元格，然后按一下F8，再點擊滾動條找到要填的最后一個單元格，選擇它，按組合鍵CTRL+D。

（4）快速刪除空白行。具體操作：選取區域數據，使用“Ctrl+G”定位空格所在行，然后區域右鍵選取刪除整行。

（5）重復上一次操作。具體操作：完成上一步操作后，直接按下F4即可。

（6）快速選取數據。具體操作：按住快捷鍵[Ctrl+Shift+方向鍵]，能夠快速地將指定的Excel數據選取上。

（7）刪除重復值。具體操作：依次點擊[數據]-[刪除重復值]，選擇需要篩選的行列，然后直接刪除即可。

（二）Ctrl+E運用

Ctrl+E快捷鍵是通過比對字符串間的關系，來給出最符合用戶需要的一種填充規則。可參考的對應關系越多，判斷越準確。運用它可實現如連接單元格、拆分單元格內容、替換字符、去除空格和符號、自動換行、提取身份證信息、分離文本和數字等。版本限制為2013版以上。

例：有如下表，要求從身份證號中提取出出生日期。

第一步：首先在第一個單元格手動輸入身份證對應的出生日期數據。

第二步：按下Ctrl+E，Excel會自動識別填充規則，并進行填充。結果如下。

二、Excel批量數據查詢

（一）VLOOKUP+MATCH+INDEX函數匹配

VLOOKUP是一個查找函數，給定一個查找的目標，它就能從指定的查找區域中查找返回想要查找到的值。語法結構=VLOOKUP（查找值，查找區域，返回的列數，查找類型）。查找為0則精確匹配，為1則近似匹配。

MATCH函數指返回要查找的數據在區域中的相對位置。語法結構=MATCH（查找值，查找區域，查找類型）。查找類型為0則精確查找，為1則查找小于或等于查找值的最大值，為-1則查找大于或等于查找值的最小值且查找區域中參數必須按降序排列。

INDEX函數是返回表或區域中的值或值的引用。函數INDEX（）有兩種形式：數組形式和引用形式。數組形式通常返回數值或數值數組;引用形式通常返回引用[1]。

通過上述三個函數的相互匹配，可以實現多條件的批量數據查找和填充。

（二）VBA應用

VBA（Visual Basic for Applications）是Visual Basic的一種宏語言，是在其桌面應用程序中執行通用的自動化（OLE）任務的編程語言。主要能用來擴展Windows的應用程序功能，特別是Microsoft Office軟件。它也可說是一種應用程式視覺化的Basic腳本，語法簡單易學。在Excel中，我們可以使用VBA進行編程，充分利用編程語言的邏輯、數組、Find函數等實現批量數據的各種復雜的查詢工作。

三、Excel數據透視表

數據透視表是Excel中一個強大的數據處理分析工具，通過數據透視表可以快速分類匯總、比較大量的數據，并且可以根據用戶的業務需求，快速變換統計分析維度來查看統計結果，特別是大數據時代的來臨，用戶需要處理的數據體量也越來越大，如何高效地完成統計分析，數據透視表無疑將成為一把利器。數據透視表不僅綜合了數據排序、篩選、組合及分類匯總等數據分析方法的優點，而且匯總的方式更靈活多變，并能以不同方式顯現數據。一張“數據透視表”僅靠鼠標指針移動字段所處位置，即可變換出各種報表，以滿足廣大你的工作需求。同時數據透視表也是解決Excel函數公式速度“瓶頸”的重要手段之一[2]。

數據透視表的數據源可以來自Excel數據列表清單、外部數據源（文本、SQL Server、Microsoft Access數據庫、Microsoft OLAP多維數據集等）、多個獨立的Excel數據列表以及其他的數據透視表。其結構包含篩選區域、行區域、列區域以及值區域四個部分，可通過軟件的插入菜單，選擇數據透視表工具選項按步驟進行創建。

四、Excel大數據分析直連方案

隨著互聯網和新媒體技術的飛速發展，企業擁有的數據量呈現出幾何倍數增長，傳統數倉和OLAP技術已經無法滿足業務的場景需求。這就需要提供一個統一的大數據分析平臺，能夠在亞秒級分析超大規模的數據，提供統一的業務語義定義能力，并能夠直連Excel，幫助不少企業實現了數據平臺架構升級，釋放業務的大數據分析能力。例如，Kyligence公司為用戶打造的基于Apache Kylin的智能分析平臺及產品，可以在業務端輕松支持上千名分析師并發使用Excel進行分析，使用Excel透視表直連Kyligence進行大數據分析以及使用Excel實現交互式分析大數據。其實現架構圖如下：

五、結語

綜上所述，掌握Excel的基本操作技能，熟練運用Excel進行業務數據的分析，在大數據時代，通過大數據的智能分析平臺，實現直連交互，幫助企業高效升級到現代化架構，透明加速業務用戶使用Excel的分析體驗。

參考文獻：

[1]馮注龍.Excel之光：高效工作的Excel完全手冊：電子工業出版社，2019.5.

[2]Excel Home.Excel 2016數據透視表應用大全：北京大學出版社，2018.11.

作者簡介：馬道京（1984—），男，漢族，貴州都勻人，碩士，副教授，研究方向：信息系統。

*通訊作者：陳有源（1968—），男，漢族，貴州都勻人，本科，副教授，研究方向：計算機基礎教育。