文 / 靳偉（知乎）
老衲多年前遭遇異緣,獲得Excel五輪真經一部, 其中Excel心法共有五層, 每層除了心法之外還有招式. 老衲對Excel雖然所知甚少,但覺得只要苦練心法, 層層遞進, 直達第五層,應該就算是精通Excel了.
(不過根據老衲多年以來面試經驗,一般自稱精通Excel的, 大多都是初入江湖的小朋友, 功力一般在一二層之間. 此外, Excel是數據處理工具,用Excel打遊戲或繪畫者, 雖然可能是豪俠巨擘, 但我覺得不應該作為精通Excel這件事的參照系.)
---一切有為法分割線---
Excel五輪心法:
總綱
章一.基礎應用/Short Cut
章二.函數/公式
章三.圖表/圖形
章四.數據透視表
章五. VBA /VSTO
跋:Excel局限性和小技巧
---三法印分割線---
總綱:
先賢有言,三生萬物. 可見萬物皆數也. 雷蒙三聖雷奧茲雲, 數理昭昭, 惟精惟一. Excel心法, 以紛繁之數始, 需去其雜蕪糟粕,截之以齊; 續而密經實緯, 攢之以方; 再而形諸圖表, 文飾藻繪, 方可示人. 至於心法高處, 名為VBA, 千變萬化,言語不可盡其妙也.
下圖就是雷奧茲(RayOzzie), Excel大法開山祖師, 和比爾門,鮑爾默並稱雷蒙三聖.
翻譯一下:
Excel操作上, 第一步是對數據進行清洗, 去除不合規格的髒數據, 將數據調整成整齊合理的格式. 然後添適宜的數據輔助列,補充數據維度. 最後是將處理好的數據以美觀的圖/表形式向他人展示.最高級的功能叫做VBA
VBA的使用非常靈活強大, 不是幾句話能說清楚的。
Excel招式繁多, 先給各位施主一個直觀印象. 一般止於二級菜單. 重點功能老衲用星號標記:
章一. 基礎操作 /Shortcuts
基礎操作中的入門法門是數據整理.這個是最基本的柱礎, 腰馬合一, 力從地起. 但很不幸的,大多號稱精通Excel的少俠們尚未具備這個意識。
原始數據一般都長成這樣。
這是個糟糕的數據樣本,但是還不是最糟的. 從不同的人手裡收集原始數據的時候, 這種情況特別常見.
好的數據格式是:
世間任何功夫都是由淺入深,循序漸進,數據整理就是其中最基本最重要的入門招式。不過入門招式，往往也意味著很辛苦，別無捷徑，唯手熟心細爾。
數據整理之起式:清洗
吾宗神秀大師有雲:身是菩提樹，心如明鏡臺，時時勤拂拭，莫使有塵埃.
心需拂拭, 同理,數需清洗. 使其平熨齊整, 利於後續使用。
清洗的對象,簡稱髒數據. 一般有如下幾種情況:
1. 同名異物:例如公司裡面有兩個李明, 如果不加區別地導入數據並進行合併統計, 可能就會出問題。
2. 同物異名:例如性別, 有的人寫成男女, 有的人寫成M/F, 有的人乾脆寫成0/1。
3. 單位錯亂:例如金額, 人民幣和美元一旦混同, 那絕對是一場災難
4. 規格不合:例如身份證號為9527。
5. 格式混亂:最典型的就是日期! 例如10/6/11, 根本說不清楚是11年10月6日, 還是11年6月10日, 抑或是10年6月11日,因為美式日期, 英式日期, 中式日期各自都不相同! 老衲生平目睹過的日期慘案足足有12306樁那麼多! 另外一種是分位符.美利堅的分位符是"," 而歐羅巴諸國的分位符是"."如果是一位叫Chateaubriand的美國同事發過來一個數字"123.456", 根本說不清楚這是一百掛零, 還是十萬有餘.老衲生平目睹過的分位符慘案足足有1024樁那麼多!
假使少俠天資過人,心如明鏡, 眼疾手快, 刷刷刷刷檢出來"123.456"個髒數據, 怎麼處理呢?
數據整理之承式:規制
做數據之前,先要和其他人協商好, 各個數據都是什麼格式, 不同數據表之間的格式是否要統一, 之間是否有依賴關係.如果數據不滿足依賴關係如何處理。
例如先約定好,性別一律寫成"男/女". 如果寫成M/F的, 那麼M就當成男性, F就當成女性來處理(使用替換, 或者使用中間映射表).寫成Nan/Ny的, 直接當髒數據拋棄掉。
數據整理之轉式:分組
在數據預處理中,分組是一個很重要的手段, 例如各位少俠要面對的是本公司的工資表, 想看看整體是否失衡, 可以將資歷分為中低高三組(日企),對應人員的工資進行匯總; 但具體資歷分層的節點的把握, 則需要小心, 必要時還需要反覆嘗試.例如可分成
a.工作1年以下，
b.工作1年-3年
c.工作3年-5年
d.工作5年以上
跑出來一看,wow, 公司是大學生創業基金支援的, 全部員工都是工作1年以下.
這個時候就得按更細粒度的月來進行劃分了。
數據整理之合式:聚類
聚類則更靈活,例如最早登記報冊的只有員工的姓名工號, 亂糟糟一大把, 業餘活動組織不起來怎麼辦?
這個時候找IT要一下各人上班的時候的瀏覽網頁,從網頁記錄推算一下各人愛好, 然後按照愛好進行聚類, 變成籃球俱樂部, 羽毛球娛樂部,DOTA俱樂部這以後的工作就好開展了。
上述四種,強調意識, 不限於方法。
-
老衲一生中,對於Excel最喜歡的功能是表格格式, 不單單是因為美觀整齊, 表格格式還集成了篩選, 排序, 甚至凍結窗格的功能,叫表格格式這麼平淡的名字, 若依老衲, 應該起名叫"般若波羅蜜多"格。
在表格格式內別有乾坤:
然後說說神鬼莫測七招式:數據工具
先說說分列.這種密集大魔王造出的數據, 大家想必都很常見吧.貼到Excel裡面還依然是密密麻麻令人頭暈目眩.
只需將之選中,使用"分列"式:
Duang(此處應該有掌聲)
接下來是"速填"式,說實話, 這是個相對比較無聊的招式. 如果功力達到第二層, 使用公式函數,取而代之乃是易如反掌.
不過既然至此,老衲繼續用上面的例子:
從"代碼縮略"下面一直選到底,點擊"快速填充" . Duang:
刪除重複項實而不華,特別推薦. 非常簡單, 不贅述.
數據驗證意正身正,實用, 略繁瑣, 多人協作時很推薦. 不贅述.
合併計算和"快速填充"有點相似,如果功力遞進到第四層, 使用數據透視表來取代之簡直是易如反掌. 且便捷不止十倍.先不詳述.
模擬分析是堪稱第一層的屠龍絕技,看似厲害, 其實用處很小. 而且這個模擬分析和公式/函數關聯極大,留待第二章詳述.
關係則是一種高不成低不就的招式.不能說沒用, 但它的功能呢, 其一可以使用輔助列取代, 其二可以使用vlookup取代, 其三可以使用資料庫取代.等說到數據透視表的時候再詳述不遲.
中級法門是數據導入,早期數據清洗什麼的, 早在入庫前就已經有人替你完成了, 便當之至:
連個SQLserver看看:
能練到這一級,少俠, 你功力已然不淺了.想必SQL這種中等功夫你也有過粗練.
(沒練過的看這裡:SQL基礎教程語句匯總 )
至於MDX這種小無相功,功力也不在Excel五輪心經之下, 但修習全憑機緣,老衲先不多講了.
(好奇想練練的看這裡:MDX的基本語法及概念 )
注意事項:
Excel初級法門中有一道奇毒,名為"合併單元格", 想老衲數度往生極樂, 多緣於"合併單元格"之手. 作為原始數據, 儘量不要使用"合併單元格",這個功能在後續處理數據的時候會帶來大量的麻煩. 足以令人嘔血三升. "合併單元格"一般是在最後一步,確定數據不再修改的時候才可以使用.
另外和別的人協作處理數據的時候,最好將處理好的原始數據和呈現數據一起提交給他人,方便他人未來進一步修改.
至於哪些Shortcuts比較重要,個人覺得是單元格位置的操作, 能避免在萬千數據裡頻繁地拖動滾動條.
-色想受行識分割線-
章二.函數/公式
此二者非常容易混淆,畫張圖比較容易說清楚:
只要在上方公式區輸入的,等號之後的內容, 都屬於公式(綠色).
而函數(紅色),則是後面帶一對括號的那些內容.
這一節的初級要點是熟悉Excel現有的函數庫
其中比較普世的是以下四類:
數量不多, 經常去[該網站因為政策法規不予展示] 上搜一下, 就知道用法了。
這個常用函數裡面有一個人氣堪比AKB48的,那就是VLOOKUP (以及他的妹妹HLOOKUP)
VLOOKUP其實就是建立兩個表的關聯,將B表的內容, 自動導入到A表:
-斷見取見-
中級要點是如何將這麼多函數做成一個複雜的公式。
複雜的公式,核心就是函數的嵌套, 函數裡面套函數, 招中有招, 直至八八六十四招. 如長江大河, 滔滔不絕.函數的嵌套最多可以套64層(Excel 2013, 從前Excel 2003-2007為最多套7層). 函數的總字數長度可達恆河沙數(老衲記不清具體數量的時候就暫時說恆河沙數)。
下圖這個例子就是一個簡單的多層嵌套,主要是if邏輯上的嵌套。
不過心有五蘊,人有三昧, 簡言之就是地球人還沒有進化成三體星人的形態, 嵌套的數量一多, 就會令人心毒盛起, 頭暈目眩, 前列腺緊張,根本看不清楚自己在寫什麼.
臣子恨,何時雪?
怎麼辦?中間列!
這回老衲舉一個實用的例子,個稅計算:
正統的個稅計算算法是這樣的:
寫成公式是這樣的:
把公式摘出來給各位欣賞一下:
=IF([月工資]-3500<=0,0,IF([月工資]-3500<=1500,([月工資]-3500)*0.03,IF([月工資]-3500<=4500,([月工資]-3500)*0.1-105,IF([月工資]-3500<=9000,([月工資]-3500)*0.2-555,IF([月工資]-3500<=35000,([月工資]-3500)*0.25-1005,IF([月工資]-3500<=55000,([月工資]-3500)*0.3-2755,IF([月工資]-3500<=80000,([月工資]-3500)*0.35-5505,IF([月工資]-3500>80000,([月工資]-3500)*0.45-13505,0))))))))
看到這個公式是不是感到口乾舌燥,頭暈目眩, 前列腺緊張?
但如果使用中間列,將公式拆解, 每個子部分做成一列, 就會立刻神清氣爽:
最後將不需要的列隱藏起來,Mission complete~
然後該說說模擬運算了.
公式可以拖動,其中參考的單元格在拖動的時候位置也會變動. 下圖就是老衲正在拖數據的瞬間:
一鬆手:
這種拖數據,雖然很簡便, 但也有一個問題, 就是只能向著一個方向拖, 或上或下, 或左或右. 假設現在有一個數據要求, 有兩個變量,相當於讓你同時向下向右拖動怎麼辦?
例如不同利率, 不同年限下房貸的問題(這真是一個令人悲傷的例子, 施主請看破紅塵吧):
普通公式也可以做到,但是就是需要向右拖N次, 或者向下拖M次. 等你拖好, 妹子已經下班,和別人一起吃麻辣燙去了.
模擬運算則可以一下子把這個6*6的結果全算出來.
操作很繁瑣, 接下來的內容請點讚, 給施主增加信心:
先在左上角放一個本息合計公式:
然後選中所有的可變利率及可變年限:
然後選擇"模擬運算表"
點擊確定之後就可以Duang了:
唉,這果然是一個令人傷心的例子.
---阿耨多羅三藐三菩提---
高級要點是如何自定義一個函數.
剛才的例子,為了計算日期對應的季度, 使用了一個漫長的公式. 現在看看這個, 一個函數就直接命中靶心,賞心悅目~
這個Quarter函數,少俠的Excel裡面是找不到的, 因為這是老衲自創. 它的真實面目是這樣的(感謝 @黃老邪的提醒):
沒錯,這就是第五層心法乾坤大挪移第一級, 也就是VBA.
---六道輪迴分割線---
章三:圖/表
各位善男子善女人久等.老衲今天為各位解說五輪真經的第三層, 又稱無上正等正覺圖形圖表經. 如是我聞:
第一級:表格
主要入口在這裡:
也可以使用這個:
表格創建完成後,點擊表格中的內容, 會出現一個新的密法空間:
這些東西都是幹嘛的呢?
首先是表名稱.子曰:「名不正,則言不順; 言不順,則事不成; 事不成, 則飲西北風". 達爾文在加拉帕戈斯群島發現的奇行種生物程序猿,對名稱就非常關注, 程序猿對名稱的關注主要是認為能方便後續使用. 更直觀, 也不容易出錯。
以上圖出現過的公式距離
=VLOOKUP([對應級數],個稅速算表,4,FALSE)
"個稅速算表"就是一個表格的名字,這樣的話選擇範圍就不是一個類似於 =D30:F37 這樣很難記憶的字符串, 而變成一個非常容易理解的對象.而[對應級數]這種列名也一目了然, 如果不加命名, 就得換成=F22:F26, 還要考慮絕對地址和相對地址,非常麻煩。
起個好名字就成功了一半哦~
切片器:切片器誕生於2010年. 其實就是一種更美觀的篩選。
那篩選又是什麼:
篩選一共只有兩路18式.屬於最簡易的功夫, 但是日常防身非常實用, 希望各位有時間能多多操演, 不過沒時間的話, 老闆也會逼著你天天操演,所以這個不必多解釋.
此外再說一下表格的另外一個好處: 生成透視表特別方便.
如果是普通數據, 如果想要生成數據表, 必須全部選中:
但如果是表格的話,隨便選中表中任意一個單元格, 即可開始操作:
既然事已至此,老衲順道說一下條件格式這個惠而不費的功能, 自己使用還是給他人展示都非常美觀:
下面做個集大成的演示(哎呀呀,老衲狗眼被晃瞎了, 看來只能明日再敘了)
---阿賴耶識分割線---
圖表經 第二級圖表
圖表主練手少陽三焦經,內力不深, 招式繁多. 以老衲愚見, 這級偏向華而不實. 不過既然至此,老衲依舊為諸位善男子善女人逐一解說.
圖表共有十式, 常用者六, 不常用者四. 看起來招式並不多對吧?
但其中每一式下可能有若干變招,故而常見的總數是: 52式 (自定義式未計入)
常用圖式,可通過Excel上面的tips來理解 (將滑鼠hover在某圖式上就可以看到):
後面不太常用的四式可以稍微詳細點說說.
---諸行無常分割線---
散點圖
散點圖是展示兩變量關係強弱的圖形.
老衲舉個蒸慄(正例).一個國家的人均壽命和該國家的人均GDP有沒有關係呢?
找了一份2014年的公開數據(不一定完全正確哦)
就用它作圖(注意老衲的手法,千萬注意, 否則圖是畫不出來的):
Duang:
能看出來點規律嗎?似乎不是很容易對吧. 喝! 目下才是真正顯示手腕的時刻!
再看一遍:
看來錢可通神這四個字果然是有些道理.
剛才的例子也表明, 真正控制圖表的, 不在圖上,而是在屬性格式裡.
那老衲再舉一個例子,民主指數和人均GDP(購買力平價法)之間的關係:
咦,這個相關性就要差一些了. 有很多有錢的一點也不民主,但民主的基本還算有錢.
散點圖在老衲看來只是一個半成品,它不能顯示數據所屬的"系列", 結果就是上面這一大片圓點,你是無法直觀識別每個點是屬於誰的.
這個時候就必須配合第三方標籤工具來完成:例如 XY Chart Labeler
順道再說一句,如果施主打算自己也寫這麼個XY Chart Labeler, 功力需要達到第五層心法的第二級, 也就是VSTO. 掌握了VSTO,Excel世界的大門就算正式打開了,理論上就沒有什麼能夠阻止施主了.
泡泡圖
這是散點圖的一種變招,散點圖只能選取兩列, 而泡泡圖必須選取三列, 第三列就是用來計算泡泡麵積的, 繼續用上面的數據做例子,我們不但要看看民主指數和人均GDP的關係, 還得看看這個國家的總體量, 省得被一群小國忽悠:
這三列全都選中,然後選擇泡泡圖:
結果如何,各位不妨親自試試^_^
---四無量心分割線---
雷達圖
雷達圖主要是對兩個(多個)對象的多個屬性進行綜合比較的時候使用. 一般來說越圓, 各項指標就越均衡; 圈的面積越大,綜合實力就越高.
注意事項: 屬性值作為行(hang), 對象作為列. 這樣默認就能輸出正確的雷達圖. (樣例數據來自汽車之家,老衲不是車託)
好,出圖了:
如果數據寫成了這樣:
那默認出圖就會是這樣:
但其實也沒啥問題.這時候需要右鍵點擊圖片: 選擇數據
然後切換一下行列就OK了
選擇數據是非常基本而關鍵的知識點.萬望各位施主對此能提高重視.
-
曲面圖
曲面圖主要是表現什麼呢..老衲個人理解是 這個主要表現 z = f''( f(x),f'(y) ) 這樣的計算式 (這完全不是中文好嗎?! 老衲也知道啊, 但是就是不知道中文怎麼講啊!!)
曲面圖和"模擬運算表" 可謂是天生的一對, 地造的一雙. 什麼? 你已經忘了什麼叫"模擬運算表"? 那其實也沒啥關係,只要你不是科研/教學人員, 那曲面圖和模擬運算表在實際工作中, 遭遇到的概率小於萬分之一。
不過行文至此, 圖還是要上的(背後的數據就是使用模擬運算表得來的)。
是不是很酷炫? 還能3D旋轉哦. 但這個圖究竟能幹啥呢?讓老衲吃包辣條再思考一下..
-
股價圖
顧名思義, 就是做出股價分析的圖表. 但老衲生平從不炒股(因為沒錢),都不知道這個東西怎麼看
畫一張還是可以的:
這張圖對應的數據是這樣的:
最多5列,分別是交易量, 開盤價, 高點, 低點, 收盤價.順序不能隨便改哦.
預祝炒股的各位同學今年好收成.
--以下是乾貨分割線--
關於圖表的乾貨終於要來了!那就是怎麼把圖表變漂亮~追求美乃是人之天性, 老衲但做淺說.
請看老衲標記的部分, 再加上空白的大背景,一般而言能控制的便是這些.想要變漂亮就要從每一個標記點上做道場:
一旦開始對圖表進行操作,千萬不要觸碰快速布局和圖表樣式模板哦, 否則:
然後就要確定一個所謂美觀的範例. 如果不知道貓長什麼樣, 筆下又怎麼可能畫出一隻貓呢?不知道漂亮的Excel圖表是什麼樣子, 那即便對所有的操作無比精熟,又怎麼可能做出一張漂亮的Excel圖表呢?
(A同學默默地交給老衲一張圖: 我覺得這張圖很帥, 就要這樣的吧)
..
老衲花了半天功夫, 終於找到了一個合適的示例: 電影票房火箭飛升,雖然立意不高, 但是配色看著還挺喜慶~
老衲這裡沒有原始數據,所以繪圖不能100%復刻. 先把已有的數據列出來.
然後標記上對應的顏色(需要使用第三方取色器,老衲使用的是QQ截圖.)
來畫個八維太極圓環圖吧(餅圖的變招)
好像哪裡不對,這和原圖沒有任何相似之處啊.
切換一下行列(忘了如何切換行列的請往前翻):
好像有點意思了, 不過這也太醜了. 醜得讓老衲又開始思考苦海無邊,回頭是岸的道理了
但這就是Excel自帶模板的真面目.
好, 圖表美化經中真正的大招來了: 天罡地火 輔助列!!!
為了彌補Excel自帶模板的先天不足, 製圖時, 很可能需要添加一些數據,來實現美觀的效果.
原有數據(提前切換了行列)添加輔助列之後變成這樣:
至於輔助列裡面的數據怎麼來的,很遺憾地告訴大家, 是老衲隨意編造的.這個其實需要一些嘗試和反覆修改.
請看大圖!
似乎更醜了..
吃包辣條冷靜了一下,老衲覺得問題主要是圖中每個環都瘦比飛燕, 如果變成楊玉環想必會漂亮得多.那就來試試吧:
於是乎:
眼尖的同學是不是可以看出一點眉目了?
接下來就簡單了:
1.去掉每個環區的邊框, 使其彼此緊湊. 2. 扇區起始角度順時針移動30度.3.將輔助列的部分改成"無填充"
填上一個底色讓大家看清晰一些:
是不是有點意思了^_^
然後就是給每個環區賦上顏色.看結果吧:
然後添加文本說明(老衲只加一個示意吧,迪塞爾的光頭照請各位意會)
和原圖比較一下:
是不是感覺有幾分神似?
--四種清淨分隔線--
第三層表格/圖表的主要內容都說得差不多了.迷你圖很簡單, 一試便知. 今天剩下的時間, 就講講第三層另一個屠龍神技吧: 般若白象功PowerView.
使用這個功能, 能生成所謂的動態圖表, 怎麼個動態法呢?
就是選中某個數據系列或者篩選項時,對應的數據系列會變色/高亮.
還有就是使用一個切片器(還記得這是什麼嗎?)可以同時控制多個圖表(即所謂的聯動)
當然如果真的有這種需求存在, 那Power View應該還是有點價值的. 在老衲的生涯裡, 這種需求非但極少,而且均屬錦上添花的作用, 最後都是用VBA解決的.
另外一個略有用的功能就是, PowerView能根據地名自動綁定Bing地圖.
老衲偽造了一份美國各州人口表(使用了Randbetween函數):
將這兩列選中後, 點擊"Power View",這個時候就可以去泡咖啡了.
大概兩分鐘吧, 終於生成了一個全新的工作表(Sheet):
這個還是挺方便的.
但是老衲從前見過的地圖,一般要求畫成這樣(這個老衲當年也是用VBA解決的):
所以老衲稱之為屠龍神技, 還是有自己的道理的. 順便一句, PowerView的功能,是用silverlight實現的(可以理解成微軟家山寨flash),而silverlight已經被微軟判了死刑...春草碧色,春水淥波,送君黃泉,傷如之何, 傷如之何,阿彌陀佛...
(至於怎麼畫上面的圖, 可以參見老衲的另一個答案: excel上怎麼做數據地圖？ -靳偉的回答)
第三層真經講解完畢. 不日更新第四層.
---正理因明分割線---
章四: 數據透視表
數據透視表(pivot table)這個]翻譯比較古怪. 不過名稱不是大問題,只要理解數據透視表能做什麼即可.
數據透視表是一種簡易報表, 可以對不同的數據行列進行數據匯總.
數據透視表的入口在此:
生成了Pivottable之後的主要控制區:
開始舉例,老衲這回還得請出王二和李明來:
此時前世孽緣來了,一個自稱是老闆的人,讓你算一下李明和王二現在賣出的東西的平均價格是多少.
用公式sumif是可以實現的,但現在有更好的辦法來了.
選中這個表格, 插入數據透視表:
然後輸入一個計算欄位( 計算欄位CalculatedField是Pivot table中的重點功能, 要著重注意):
然後在右邊拖一拖:
我們用Sumif核算一下
看來沒什麼問題^_^
這時候老闆又發話了,按照顏色和銷售人員各統計一下總銷售金額. (高達八成的老闆都是這樣) 怎麼辦呢? 很簡單,再拖一下:
就是這麼方便,就是這麼任性!
老闆繼續發話,"你這麼搞完全沒有理解我身為老闆的一片苦心! 我是讓你製作兩個表, 一個統計人員, 一個統計顏色, 然後給我一個過濾表單,這樣我可以按日期看人員和顏色的變化趨勢."
這個說來很簡單,只需將Pivot table整個圈中, 複製黏貼, 然後改一下欄位即可.
過濾項呢,也是將欄位拖入到篩選器即可:
但現在有兩個pivot table, 是否有方法同時操作兩個pivot table呢?
有, 那就是切片器(從前也提起過哦)
選中一個Pivot table, 添加一個切片器。
添加切片器之後,右鍵選擇"報表連接", 繼續添加連接的pivot table, 兩個都選中:
這樣,用這個切片器, 就可以達成一個切片器來控制多個pivot table的目標:
順道說一下,"日程表"也是切片器的一種, 只不過外觀是特別優化過的罷了:
眼尖心細的少俠會發現,在PivotTable操作中, 有幾個選項一直是灰色的, 例如:
還有:
這是因為它們都需要特殊的奇門兵器和外道功夫:
老衲由於近來深研佛法三寶(合稱PPT), 一時沒有準備OLAP真經, 所以這節暫時跳過,待機緣成熟再來補完。
至於Power Pivot, 各位可以直接視之為"不服跑個分兒"版的數據透視表. 嚴格點說,它預期起到的作用是簡易的資料庫(例如Access), 而工作方式比較像數據透視表。
另外想要在Excel中突破一張表最多100萬行的限制, 也得仰仗這位的大肚能容. 在Power Pivot中,一張表的最大行數為20億行. 詳情請見: PowerPivot Capacity Specification
但老衲還是認為, 如果必須應對上億行的數據, 學習一下資料庫--例如SQL server, Oracle, MySQL--是很有必要的, 好過使用這個Power Pivot. 所以這個PowerPivot暫不深表.
數據透視圖和普通的圖表幾乎沒什麼不同, 只不過能和一個數據透視表彼此聯通, 控制表的同時, 可以影響到圖的展示內容.不作為重點.
這一章通常來說, 最常用的還是 計算欄位 和 計算項.望勤為操演.
餘者待老衲重新準備一下, 來日方長.
---眾因緣生法分割線---
章五: VBA / VSTO
老衲痛感逝者如斯, 不舍晝夜, 因而決定提前講說章五.也就是乾坤大挪移心法.
請各位注視自己的Excel, 是否能找到我神功入口?
找不到也正常,畢竟是奇門秘籍, 一般都藏之名山大川, 幽谷白猿之中. 請從這裡找尋:
"開發工具"一定要選中才行.
這個裡面常用的又是"代碼"和"控制項"
舉個慄子, 請各位看個大概:
1. 點擊錄製宏:
然後對Volume列進行排序操作:
然後點擊一下"停止錄製":
然後點擊左側的VisualBasic:
可以看到代碼了:
這就是VBA奧義最簡單的例子.
各位少俠中, 可能有不少人第一次發現這個入口. 對於編程(沒錯, 就是編程)可能也不太了解,所以有一些基本概念要澄清一下:
這些基本概念分別是: 對象, 屬性與方法, 集合.
因為老衲遁入空門前是一名光榮的人民廚師,所以就用廚師來做一下比喻.
一個廚師, 就是一個對象. 一群廚師(同類別的對象), 就是一個集合. 集合也是對象的一種.像"順峰頤和園路東口分店後廚大師傅們"就是一個集合, 也是一種對象.
我們來定義一個廚師:
Dim someone as Chef
現在在代碼世界就有了一個叫someone的廚師了.
那此Someone有哪些屬性呢? 例如身高178cm, 體重73kg, 月工資15000 (順峰店啊, 人均消費過千,大師傅沒有上萬的薪水, 你們還願意去吃嗎?
身高 = someone.Height
'這時候身高=178, 前面這個 ' 號要注意, 這個是注釋, 開頭寫上' 號的一行, 在程序眼裡是不存在的,只是方便奇行種程序猿(達爾文在加拉戈帕斯群島發現的哦)來閱讀的.
體重 = someone.Weight
'這時候體重= 73
工資 = someone.Salary
'這時候工資= 15000
那"順峰頤和園路東口分店後廚大師傅們"也可以是一個對象, 為了舉例方便,大師傅集合就叫Chefs.
Dim 順峰頤和園路東口分店後廚大師傅們 as Chefs
那順峰頤和園路東口分店後廚大師傅們 有哪些屬性呢? 例如總人數15人,每月工作時間21.75天
順峰頤和園路東口分店後廚大師傅們.count = 15 (等於號, 表示是賦值操作, 把右邊的值寫到左邊去,前提是左邊的東西可讀寫, 剛才這句話相當於為大師傅強行指派了一個總數, 當然通常來說,這個count很可能是只讀的)
順峰頤和園路東口分店後廚大師傅們.WorkDays = 21.75
好, 對象, 集合, 屬性三個都說過了, 那方法又是什麼?
方法是對象能做出的行動.
例如someone這個對象, 本質上是一名光榮的人民廚師.他能幹什麼呢?
someone.fries() '炒/炸
someone.boil() '煮
someone.stew() '燉
'英文能表達的動作太少了, 完全不適用於中式廚師啊
那"順峰頤和園路東口分店後廚大師傅們"這個對象呢?
順峰頤和園路東口分店後廚大師傅們.work()
順峰頤和園路東口分店後廚大師傅們.Dismiss()
'還有扯淡吹水等動作就不逐一列舉了.
有這些概念, 少俠基本就明白自己面對的是些什麼東西了.
然後老衲還要以廚師工作舉例, 說明一下語言(VBA)和IDE是什麼.
編程都有語言, 語言本質上就是一種人類和機器相互溝通的工具, 人類告訴機器怎麼來運作, 如何執行動作,創作出豬肉料理來. 在廚師界, VBA就是中式菜, C#就是西式菜, Java就是印尼菜. 不同的語言在思路上是很不一樣的,例如Java講的就是普適口味, 不再眾口難調; VBA講的就是快熟快上; Erlang講的就是明火多灶;但是目標都是把飯做熟.
那IDE(Integrated Development Environment)是什麼? 當然就是廚房啦.有全套廚具, 灶臺, 煙機, 還有一個寶貝哦: 全方位支持的速查菜譜.
Excel自帶一個VBA的IDE, 雖說簡陋得緊, 一副從大清朝穿越過來的樣子, 不過好歹也算功能齊全, 聊可一用.(到了VSTO可就是使用全套頂級大廚廚房了, 保證樂不思蜀~)
這個廚房分為幾個常用功能區:
菜單區:這個是控制樞紐, 各個命令的總入口都在這裡。
工程區:在這裡可以直觀地管理/組織你的代碼。
屬性區:對於在工程區選中的對象, 在屬性區可以直觀地顯示該對象的各個屬性。
代碼區:就是寫入VBA並調試執行的地方
監視窗口:對於運行中的對象, 我們想知道它在中間狀態中的各個屬性值變化, 可以通過這個窗口來觀察. 如果自己做過牛排, 或許知道探針式溫度計.這兩個道理很相近。
老衲說了這麼許多基礎知識, 各位施主恐怕已經昏昏欲睡了吧. 沒事,咱迅速炒個回鍋肉, 大家精神一下:
第一步: 起火,找一個新鍋(建一個新Module).
第二步: 寫代碼, 炒回鍋肉.寫完了之後點上面的三角箭頭
第三步: 回鍋肉裝盤亮相:
夠短平快吧?~~
從回鍋肉到全世界, 中間的困難主要在於對各個食材(對象,類庫)的熟悉程度. 再往上則是編程思維(例如編程Pattern). 因此老衲也不打算寫太多了,可以參考: Getting Started withVBA in Excel 2010
循序漸進,必至大道.
---破我執分割線---
應某位少俠的要求, 提前講解一下Excel(2013)的局限性,如是我聞:
1. Excel(2013)及之前,大概佔用普通Windows電腦內存的25%-30%, 即容易崩潰, 或出現各種不穩定症狀. 尤其是32位Windows.該數字出於老衲的經驗. 因而一個大內存是很有必要的.
2. Excel(2013)的一張工作表,可以容納1,048,576行 乘以 16,384 列. 那麼如果少俠家資豐厚, 隨便拿出兩百萬行數據怎麼辦? 請使用SQLserver express(express版本是免費的)來輔助進行數據預處理. Access理論上也可行, 但是有失簡陋,所以老衲向來不用, 從而知之甚少.
3.一個單元格能放32,767個字符.
4. 回退能退100步.但使用了VBA就不好說了.
5.一個下拉列表單裡能放10000個選項.
6.一個函數裡面最多使用255個參數.
7. 公式裡面的函數嵌套, 可以嵌套64層.(說7層的那是還沒更新到2013, 請加速更新)
8.公式的長度不能超過8192字符.
9. 一個圖表裡最多可以放255個數據系列.(一般來說放二三十個就已經糊滿了)
10. 面積圖, 不能用平滑曲線.需要使用別的方式實現.
11. 柱狀圖,不能同時兩個柱狀的數據系列分別對應主坐標軸和次坐標軸. 需要變為一個柱狀圖,一個折線圖.
（本文系作者授權「清南」發布）