怎樣才算精通 Excel？

11-25

姐妹問題：
怎樣才算精通PPT？ - Microsoft Office
怎樣才算精通word？ - Microsoft Word
怎樣才算精通Illustrator？ - Adobe Illustrator
怎樣才算精通Photoshop？ - 設計

怎樣才算是精通Excel?

老衲多年前遭遇異緣, 獲得Excel五輪真經一部, 其中Excel心法共有五層, 每層除了心法之外還有招式. 老衲對Excel雖然所知甚少, 但覺得只要苦練心法, 層層遞進, 直達第五層, 應該就算是精通Excel了.

(不過根據老衲多年以來面試經驗, 一般自稱精通Excel的, 大多都是初入江湖的小朋友, 功力一般在一二層之間. 此外, Excel是數據處理工具, 用Excel打遊戲或繪畫者, 雖然可能是豪俠巨擘, 但我覺得不應該作為精通Excel這件事的參照系. )

-------------------一切有為法分割線-----------------------

Excel五輪心法:
總綱
章一. 基礎應用/Short Cut
章二. 函數/公式
章三. 圖表/圖形
章四. 數據透視表
章五. VBA / VSTO
跋: Excel局限性和小技巧

-------------------三法印分割線-----------------------

總綱:
先賢有言, 三生萬物. 可見萬物皆數也. 雷蒙三聖雷奧茲雲, 數理昭昭,
惟精惟一. Excel心法, 以紛繁之數始, 需去其雜蕪糟粕, 截之以齊; 續而密經實緯, 攢之以方; 再而形諸圖表, 文飾藻繪, 方可示人.
至於心法高處, 名為VBA, 千變萬化, 言語不可盡其妙也.

下圖就是雷奧茲(Ray Ozzie), 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取代, 其三可以使用資料庫取代. 等說到數據透視表的時候再詳述不遲.

中級法門是數據導入, 早期數據清洗什麼的, 早在入庫前就已經有人替你完成了, 便當之至:

連個SQL server看看:

能練到這一級, 少俠, 你功力已然不淺了. 想必SQL這種中等功夫你也有過粗練.
(沒練過的看這裡: SQL基礎教程語句匯總 )
至於MDX這種小無相功, 功力也不在Excel五輪心經之下, 但修習全憑機緣, 老衲先不多講了.
(好奇想練練的看這裡: MDX的基本語法及概念 )

注意事項:
Excel初級法門中有一道奇毒, 名為"合併單元格", 想老衲數度往生極樂, 多緣於"合併單元格"之手. 作為原始數據, 盡量不要使用"合併單元格", 這個功能在後續處理數據的時候會帶來大量的麻煩. 足以令人嘔血三升. "合併單元格"一般是在最後一步, 確定數據不再修改的時候才可以使用.

另外和別的人協作處理數據的時候, 最好將處理好的原始數據和呈現數據一起提交給他人, 方便他人未來進一步修改.

Shortcuts是用來省時間的, 相比來說只能算是小技巧. 最好的參考資料就是微軟的官方說明書:
Keyboard shortcuts in 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 (下載地址:The XY Chart Labeler Add-in）

順道再說一句, 如果施主打算自己也寫這麼個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.將輔助列的部分改成"無填充"

填上一個底色讓大家看清晰一些:

是不是有點意思了^_^

然後就是給每個環區賦上顏色. 看結果吧:

然後添加文本說明(老衲只加一個示意吧, 迪塞爾的光頭照請各位意會)

和原圖比較一下:

是不是感覺有幾分神似?

老衲的手機不幸撲街, 今日要開水陸道場, 為之超度往生, 所以今天暫時更新到這裡.

------------------乾達婆城分割線-----------------

老衲又找到一個例子: 這是經濟學人典型的紅藍配. 藍色走漸變, 紅色是點綴, 左上一枝紅杏出牆, 待老衲做將來:

先分析一下要點:

可見這張圖雖然很小巧緊湊, 乍看也無驚艷之處, 其實作者非常嚴謹, 功力至少達到了第四層.

現在編造一點數據準備復刻, 出圖啦:

這個不用DNA鑒定了, 肯定不是同一個人生的, 呵呵.

首先要把進口數字乘以-1, 然後再繪圖:

這回就順眼多了, 以下就是以此修改剛才標出來的元素, 例如標題, 副標題, 圖例等等.

現在有幾分姿色了吧~

繼續調整各項細節:
1. 移動標題位置, 更新字體
2. 插入矩形對象, 填充紅色, 去除邊框
3. 插入文本框, 輸入文本, 生成副標題
4. 調整圖例位置, 寬度及字體字型大小
5. 將左邊數據軸移除
6. 在Balance上添加次數據軸, 並調整上限下限和間隔單位. 右側次坐標軸字體字型大小調整
7. 調整柱狀圖的數據系列的分類間距(219% -&> 70%)
8. 在源數據上改動年份寫法
9. 添加數據來源, 數據備註.
10. 添加一條線, 置為紅色, 拖到0的位置上.

好, 各位施主請看!

各位可以和原圖比較一下, 看看是否相似.

圖表美化的價值, 在老衲看來並不很大. 因為小公司一般不要求美化效果, 大公司反而有專人(美工)支持. 故而此事上, 知曉大概, 不求甚解就好了.

當然少俠對老衲這種敷衍態度必然是不滿的, 所以老衲另有秘籍推薦: 《Excel圖表之道:如何製作專業有效的商務圖表(彩)》劉萬祥【摘要書評試讀】圖書

----------------------四種清凈分隔線------------------------

第三層表格/圖表的主要內容都說得差不多了. 迷你圖很簡單, 一試便知. 今天剩下的時間, 就講講第三層另一個屠龍神技吧: 般若白象功 Power View.

使用這個功能, 能生成所謂的動態圖表, 怎麼個動態法呢?
就是選中某個數據系列或者篩選項時, 對應的數據系列會變色/高亮.
還有就是使用一個切片器(還記得這是什麼嗎?)可以同時控制多個圖表(即所謂的聯動)
當然如果真的有這種需求存在, 那Power View應該還是有點價值的. 在老衲的生涯里, 這種需求非但極少, 而且均屬錦上添花的作用, 最後都是用VBA解決的......

另外一個略有用的功能就是, Power View能根據地名自動綁定Bing地圖.

老衲偽造了一份美國各州人口表(使用了Randbetween函數):

將這兩列選中後, 點擊"Power View", 這個時候就可以去泡咖啡了.

.............

大概兩分鐘吧, 終於生成了一個全新的工作表(Sheet):

這個還是挺方便的.

但是老衲從前見過的地圖, 一般要求畫成這樣(這個老衲當年也是用VBA解決的):

所以老衲稱之為屠龍神技, 還是有自己的道理的. 順便一句, PowerView的功能, 是用silverlight實現的(可以理解成微軟家山寨flash), 而silverlight已經被微軟判了死刑........春草碧色,春水淥波,送君黃泉,傷如之何, 傷如之何, 阿彌陀佛........

(至於怎麼畫上面的圖, 可以參見老衲的另一個答案: excel上怎麼做數據地圖？ - 靳偉的回答)

第三層真經講解完畢. 不日更新第四層.

---------------正理因明分割線------------------

第四層: 數據透視表

數據透視表(pivot table)這個]翻譯比較古怪. 不過名稱不是大問題, 只要理解數據透視表能做什麼即可.
數據透視表是一種簡易報表, 可以對不同的數據行列進行數據匯總.

數據透視表的入口在此:

生成了Pivot table之後的主要控制區:

開始舉例, 老衲這回還得請出王二和李明來:

此時前世孽緣來了, 一個自稱是老闆的人, 讓你算一下李明和王二現在賣出的東西的平均價格是多少.
用公式sumif是可以實現的, 但現在有更好的辦法來了.

選中這個表格, 插入數據透視表:

然後輸入一個計算欄位( 計算欄位Calculated Field是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. 所以這個Power Pivot暫不深表.

數據透視圖和普通的圖表幾乎沒什麼不同, 只不過能和一個數據透視表彼此聯通, 控制表的同時, 可以影響到圖的展示內容. 不作為重點.

這一章通常來說, 最常用的還是 計算欄位 和 計算項. 望勤為操演.
余者待老衲重新準備一下, 來日方長.

---------------眾因緣生法分割線----------------

章五: VBA / VSTO

老衲痛感逝者如斯, 不舍晝夜, 因而決定提前講說章五. 也就是乾坤大挪移心法.

請各位注視自己的Excel, 是否能找到我神功入口?

找不到也正常, 畢竟是奇門秘籍, 一般都藏之名山大川, 幽谷白猿之中. 請從這裡找尋:

"開發工具"一定要選中才行.

這個裡面常用的又是"代碼"和"控制項"

舉個栗子, 請各位看個大概:

1. 點擊錄製宏:

然後對Volume列進行排序操作:

然後點擊一下"停止錄製":

然後點擊左側的Visual Basic:

可以看到代碼了:

Sub 宏4() " ActiveWorkbook.Worksheets("K線圖").ListObjects("表13").Sort.SortFields.Clear ActiveWorkbook.Worksheets("K線圖").ListObjects("表13").Sort.SortFields.Add Key:= _ Range("表13[[#All],[Volume]]"), SortOn:=xlSortOnValues, Order:=xlDescending _ , DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("K線圖").ListObjects("表13").Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub

這就是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 with VBA in Excel 2010
循序漸進, 必至大道.

---------------一切智智分割線------------------

VSTO (Visual Studio Tools for Office) 是VBA的升華版. 顧名思義, 這個VSTO是在Visual Studio裡面使用的(也就是老衲剛才說的全套頂級大廚廚房).

Visual Studio作為微軟諸神齊心協力創造的大神級IDE, 在易用性上是無與倫比的.

不巧的是, 老衲的機器上沒有安裝Visual Studio( C盤空間不足, 殘念*1024.......主要原因是, 老衲並非開發人員.......)

好消息是, 如果少俠跟隨老衲已經走到了這裡, VSTO也不會是什麼難事.
可以從網上找些公開資料參考, 例如: VSTO學習筆記（一）VSTO概述

---------------破我執分割線-------------------
應某位少俠的要求, 提前講解一下Excel(2013) 的局限性, 如是我聞:
1. Excel(2013)及之前, 大概佔用普通Windows電腦內存的25%-30%, 即容易崩潰, 或出現各種不穩定癥狀. 尤其是32位Windows. 該數字出於老衲的經驗. 因而一個大內存是很有必要的.
2. Excel(2013)的一張工作表, 可以容納1,048,576行乘以 16,384 列. 那麼如果少俠家資豐厚, 隨便拿出兩百萬行數據怎麼辦? 請使用SQL server express(express版本是免費的)來輔助進行數據預處理. Access理論上也可行, 但是有失簡陋, 所以老衲向來不用, 從而知之甚少.
3. 一個單元格能放32,767個字元.
4. 回退能退100步. 但使用了VBA就不好說了.
5. 一個下拉列表單里能放10000個選項.
6. 一個函數裡面最多使用255個參數.
7. 公式裡面的函數嵌套, 可以嵌套64層. (說7層的那是還沒更新到2013, 請加速更新)
8. 公式的長度不能超過8192字元.
9. 一個圖表裡最多可以放255個數據系列. (一般來說放二三十個就已經糊滿了)
10. 面積圖, 不能用平滑曲線. 需要使用別的方式實現.
11. 柱狀圖, 不能同時兩個柱狀的數據系列分別對應主坐標軸和次坐標軸. 需要變為一個柱狀圖, 一個折線圖.

如果想了解更多, 官方提供了一個詳細列表: Excel specifications and limits

----------------人法兩無我分割線------------------

感謝信:
該答案連續編輯了十餘日, 老衲基本已經寫完, 一愚之得, 不敢自專, 望各位看過之後, 或有裨益. 余心安矣.
在這十多天里, 雖然更新答案比較辛苦, 但是感覺此過程中, 老衲才是最大的受益人, 從頭到尾梳理了一下自己對Excel的認識. 查漏補缺, 對Excel的理解又明徹了幾分.
感謝各位青眼有加, 紛紛點贊, 以資鼓勵. 非君等大力支持, 老衲未必支撐得下來.
祝各位在未來生活愉快, 工作順利.

PS: 老衲其他的答案也很有趣哦~~ 歡迎關注~~~

或者來老衲專欄逛逛: 從點滴開始做更好的自己

(全文完)

--------------苟日新日日新分割線-----------------

4月23日更新:

章一內容補充: 數據整理方法;

章二內容補充: 函數VLOOKUP用法.中間列示例.

4月24日更新:

章三: 表格

4月26日更新:

章三: 圖表概覽, 散點圖, XY Chart Labeler, 氣泡圖

4月27日更新:

章三: 雷達圖, 曲面圖, 股價圖.

4月28日更新:

章三: 如何美化圖表實例一

4月29日更新:

章三: 如何美化圖表實例二, Power View

補充: Excel的局限

4月30日更新:

章四: Pivot table基礎介紹

5月2日更新:

章四: Pivot table, Power Pivot介紹

5月3日更新:

VBA初步

5月4日更新:

VBA初步, VSTO簡介, 早期挖坑補全. 感謝信

其他內容補充: 秀一下老衲的吉祥物:

彩蛋來啦: 如何做好 PPT？ - 靳偉的回答

多送一個彩蛋: 有哪些學習寫作的必讀書目？ - 靳偉的回答

Word大法連載進行中: 怎樣才算精通 Word？ - 靳偉的回答

以上精彩答案多是從技能來闡述如何精通Excel，本文提供一個新視角：從使用場景來闡述怎樣精通Excel。因為我的學習理論是：單純從學習技能出發效率和動力太小，只有從實戰和解決問題出發，才能培養出興趣和長久堅持。

以下各方面有並行的關係也有部分進階的關係，精通Excel的幾大維度如下：

數據處理及分析
可視化
軟體/App
控制台/Console
奇巧淫技

一、數據處理及分析
99%以上的人使用Excel是為了數據處理及分析，然而程度卻可以從low到爆進階為屌炸天。我就親眼目睹一高大上公司的精英僱員，把數字輸入到Excel，然後在計算器上手工完成計算，最後把結果鍵入到Excel。當然，如果水平足夠進階，也可以做出炫目的財務模型。

以及利用Excel自帶的各種數據清洗（排序、篩選、根據統一分隔符來分隔數據等）和統計分析工具（ANOVA、樣本檢驗及多元線性回歸等等一個都不少）。

這種Low到土裡到炫目中間有很多階梯，有興趣的同學可以慢慢學習、慢慢進階。

二、可視化
Excel另一大功用就是繪製圖表，作為視覺動物的人類越來越需要利用圖表來理解和表達。在文章（RIO是如何席捲大江南北的？ - 數據冰山 - 知乎專欄）中，為了表達雞尾酒消費意願的逐年增長，抽取數據後需要利用可視化來表達。下圖左方是利用系統默認配置繪製出的圖表，是不是濃濃的廣場舞風格？而要實現下圖右方的財經雜誌風格，那就只好一個個參數的優化：優化圖表類型，配置Title、標註及數據源，添加趨勢線，優化字體等等。為了讓圖表更好看，各種招和參數都用了一遍，對於Excel的圖表工具，自然就掌握了。這兩者直接就隔著無數的進階空間。

如果還不滿意，可以把平時收集的大牌財經雜誌中的各類經典表格（比如：華爾街日報、經濟學人等等）分別實現一遍。這樣精通Excel的步伐又往上面走了數個階梯。

三、軟體/App
這一項門檻略高，需要涉及到用VBA編程。但是起步並沒有那麼可怕，可通過錄製宏和改參數的方式來實現，基本可以不涉及到編程。所以一些簡單的重複工作（列印、設置列印屬性、大批量刪除和增加行列等等），都可以如此山寨得來製作最簡單的軟體或者App。

當然向上的階梯無窮的，可以拿Excel來做生成GIF的軟體，出現在回答中（黃燜雞米飯是怎麼火起來的？ - 何明科的回答）。

也可以通過下圖的Word+VBA+Access製作一個八股文生成軟體，該軟體不僅完成界面交互還連接計算程序和協調計算過程。在通過簡單的交互獲取主要信息後，在後台完成計算並將主要信息填寫入八股文的word模版，最終完成報告，同時將結構化的信息存入Access資料庫，便於日後的統計分析。

四、控制台/Console
這一項門檻更高，涉及到Excel之外的各種軟體甚至是OS。到了這個層級，Excel已經可以成為一個中控平台。Excel所存儲的數據特別結構化和格式化，因此很適合以Excel為數據中心和操作平台，把數據輸入到其他軟體中完成可視化及最終成果的輸出。比如之前提到的列印問題，最簡單可以通過錄製宏及改參數就可以完成。然而，隨著列印問題不斷地複雜，程序也隨之升級。這款包含VBA程序的Excel成為列印的控制台，解決如下問題：

如何防止卡紙？
如何協調多台印表機來提高速度？
如果在偌大的辦公室中自動選取最近的印表機？
……

另外一個例子就是在Excel製作各種流程圖來監控某產品的進度，並且根據用戶的需求按照Owner/部門/任務的屬性/任務的狀態等多個維度來做成各式各樣的表格，然後Excel調用Powerpoint的程序，把這些Excel中的表格成批量的輸出到PPT。然後根據任務的進展情況，讓Excel與Powerpoint保持准實時同步，使得PPT文件始終展現最新的各種表格。這時候Excel已經成為強大的數據中心和中控平台。

五、奇巧淫技
這一項門檻超高，還涉及到各種奇思妙想。但卻可以用來把妹撩漢。

比如有人用Excel做出了三國殺遊戲。

比如我拿Excel來給女神做像素級的油畫。

……更多文章請到數據冰山 - 知乎專欄
……更多回答請看何明科的主頁

謝邀，這個問題坑太大了，略答

在我看來，EXCEL就是一堆數據沒事做在那兒聊天，東一堆西一堆的，還完全沒組織性紀律性，完全無視我天朝的種種律法，你，做為一個先進性思想的代表，一個人類的靈魂工程師以及一個吃飽了撐得慌的老闆以及其它人的小跟班，得去收拾收拾這幫數據。

於是，你親切的來到了人民數據之中，和無數的人民數據暢談理想與人生規劃，人民對你提出了心中最渴望的想法，每個數據都想做一個獨一無二的自己。你回到了家中，想整理出來和領導彙報，才發現領導只告訴了一件事「我才不管狗屁人民數據的想法，我只要他們老實，聽話，和諧，別跟我添亂，至於每個人的獨一無二，哈哈哈」

為了保住你自己的職位，你只能痛苦的回到人民數據之中，它們渴望的眼神看著你，你沉思了一下，站在了高台上，想像中前面就是一個偉大的交響樂團，你，你就是一個神一樣的指揮，來吧，來吧，揮動你的手，來吧，來吧，揮動你的細細的指揮棒。

剛開始的時候，你只能一個一個的指揮數據，設置格式，拖來拖去，學會了弄個著色來標識個別不聽話的，也許學會了一些快捷方式，忽然，你在角落裡面發現了一本售價三百大毛的秘籍「EXCEL三天就會」，於是，你苦練一番，你發現自己已經批量設置格式，順便弄個透視表，不少的架勢已經學會，數據已經被你收拾的差不多，雖然看上去高低不平，個性不一，萬千著色啥玩意都有，你覺得自己已經精通了，不過如此嘛。

你悄然的來到了一個大街上，驕傲的對著HR說「
我精通EXCEL」，
「哦，那你肯定認識EXCEL的函數啦，他們現在怎麼樣啊？過的還好么？好久沒和他們聯繫了」
「什麼是函數？」
「就是你說的那個數據交響樂團裡面那個XX嘛」
「有，有，有這個玩意么？」

你回到數據之中，看著那幾個數據後面躲著的幾個小小的玩意，就這個玩意啊，不就是幾個變戲法的傢伙么，有什麼好認識的嘛，HR還覺得他們有多好多好，突然，群眾大亂，無數的數據過來，你已經無法用數量來形容他們，他們沖著你抱怨，當年你答應別人的獨一無二無法實現，天啦，怎麼辦，怎麼辦，那幾個變戲法的傢伙過來了，偶爾一個出去，間或幾個重疊，後面還有一個叫「數組」和一個叫「數據透視表」的傢伙在微笑，揮揮手，每個數據都實現了其獨一無二的想法，只是轉眼之間，數據回到了和諧共存的時代，整齊而又實現個性，你輕輕的嘆了口氣，對HR說「我會EXCEL」

於是，你在江湖上成名，大家都知道你的戲法變的不錯，爭先恐後的要求你變，有一天，有個傢伙提出一個戲法讓你實現，你想破腦袋，也不能用手頭上的工具來實現，無論是如何去組合，但你又聽說江湖上有人實現過，你苦苦的思索，突然之間，有天看見一個函數用一個玩意把自己拆開又重新組合起來，眼睛一閃，你知道了有個叫VBA的玩意，他能實現自定義函數，自定義各種自動過程，後面你才知道，他是EXCEL的幕後者，所有的數據都是他的化身，你跪服在他手下，而且幾年後當你出關的時候，感慨的對HR說「我會一點EXCEL」

十多年過去了，江湖上你已經行走了大半，你輕輕一揮手，萬千數據隨便走，在江湖上，你撞上了VBA的媽媽VB，遇到了他的兄弟SQL，找到他的朋友們，還認識到他另外的家庭成員，在一個冷清而月光皎潔的晚上，在大內之顛，在華山之峰，你輕輕的對陪伴了你多年的HR說「我只是懂一點EXCEL而已」，山下遠遠的傳來一個年輕而驕傲的聲音「我精通EXCEL！」
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
EXCEL只是你的一個工具，對於你的工作來說，可以使用並達到你的目的就叫熟練，至於精通，能稱的上這二個字的人都不認為自己是精通的，略通而已。

聽故事，學 Excel

作者：盧子，清華暢銷書作者，《 Excel 效率手冊早做完，不加班》系列叢書

學 Excel 不久，越來越覺得不過如此，Excel 不就是加了邊框的 Word，會打字就會用 Excel。

今朝正準備放棄學習 Excel，轉學 PS，到書店購買了一本《PS 從入門到精通》，打算學習。厚厚的一本書籍，當枕頭剛好。翻看十幾頁，發覺並不難，還有模有樣的跟著操作起來。

凡事如果都按計划走，就沒有了現在的今朝。忽然有一天，無聊打開領導的表格，發現一件讓今朝很驚訝的事情，在品名列的單元格，出現了一條很長很長的公式，對於當時的我而言。

這是什麼？

更改了番號，品名居然自動更改，真的很神奇！跟領導打聽，才知道這是日本總公司

那邊設置的公式，不愧是日本人。

有句老話叫「好奇害死貓」，今朝就是被這條公式害死了，從此走上 Excel 不歸路。

一下班就搗鼓著 IF，試了三天，貌似懂得一點點名堂。

今天溫度已超過 35℃，如果沒有風扇（當然條件好的話有空調更佳），晚上要睡覺純屬

痴心妄想。用 IF 可以這麼表示：

=IF(A1="風扇","睡得著","睡不著")

IF 函數大概知道語法，可是 VLOOKUP 函數始終看不懂，參數好多，四個讓人怎麼記住

呢？愁死今朝了。

如果能學會這個函數，以後就省事了，可以不用每次都找領導輸入日語品名。今朝這人有一缺點，就是不善於溝通，有的時候寧願多走點彎路，也不想一步到位。

N 年以後才知道，原來當初走的彎路實際上卻是捷徑。下圖來自微博：

百度搜索了幾次「輸入番號，自動生成命名」，終於找到了一條公式。

=VLOOKUP(D4,Sheet1!$B$2:$E$5059,2,0)

什麼情況？怎麼這一段可以不用寫："C:DOCUME~1OwnerLOCALS~1Temp[検収報告書.xls]

後來今朝不知從哪裡得知，這是路徑，只有跨工作薄才會出現。知道這一點後，今朝鬆了一口氣，公式現在縮短了一大半。又經過了幾天的摸索，今朝對這個函數的基本用法已經了解。

在茫茫人海中，想查找到一個叫「我是菜鳥」的人的性別，已知人海的第一列為姓名，第二列為性別。

=VLOOKUP(A1,人海,2,0)

VLOOKUP 的查找，90%的情況下為精確查找，也就是第 4 參數為 0，如果第 4 參數為 1

或者省略即為模糊查找。

思考題：

1、如果設備為空調或者風扇，就可以睡得著，否則睡不著，怎麼設置公式？

2、如何根據姓名簡稱，獲取性別？

勇敢踏出第一步

今朝雖然學了兩個函數，但工作依舊沒有任何變化。但每次製作「出貨檢查不良報告」的時候，總是無意識地對品名列多看了幾眼。來日企兩年了，到現在今朝都沒學會日語輸寫，想想也挺可笑的。報告每次做完 99%，就剩品名讓領導輸入，時間久了領導也就習以為常。不過今朝卻在想解決之策，怎麼將 VLOOKUP 函數運用到這裡。

時間一天天地過去了，但事情依然沒進展。現在難點就是找到一份產品清單，裡面列舉所有產品返回對應的品名。今朝向領導打聽，部門內是否有我們公司所有產品清單？領導的答覆讓今朝的心情一下子陷入低谷，曾經菜頭試圖找這一份清單，翻遍了各台電腦依舊沒有發現，後來得知只有日本總公司才有。沒有清單，會 VLOOKUP 也沒用。此事就告一段落。

忽然有一天，今朝收到日本的 Excel 檔成績書，裡面有十多款產品。今朝看到這些成績書，突然腦袋裡閃出這樣的念頭：自己製作產品清單。當然這個清單只是隆成這邊而已，全部供應商的產品清單想都不用想，幾萬款產品。

有想法就得嘗試，今朝將隆成的所有產品番號一一羅列出來，將成績書上有的命名複製黏貼到產品清單里，這樣就完成了一小半。還好今朝想到了自己的老鄉會寫日語，就麻煩她將剩餘的命名輸入進去。經過半天的時間，終於完成了產品清單，太難能可貴了。

不會日語的人真傷不起！

有了這份清單，設置品名查找公式就變得輕而易舉。根據以前的表格，依樣畫葫蘆。

這個公式會將查找不到的值顯示成#N/A，在這裡本來是沒多大影響的。

後來有一天學到了一個新函數 IFERROR，有人也許會問，這個是幹嘛用的，跟前面又有何關係？

函數語法：

=IFERROR(錯誤值,顯示值)

就是讓錯誤值顯示成你想顯示的任意值，不是錯誤值顯示本身。

最終，今朝將公式改成：

=IFERROR(VLOOKUP(B10,產品清單!B:C,2,0),"")

雖然在這裡進行容錯處理意義不大，但起碼能將所學第一時間用上。另一種解釋就是顯示錯誤值不好看，顯示成空白好看點。

在不良報告設置公式，雖然對今朝而言沒什麼，但對領導而言卻是一種解脫，讓領導省去無數次輸入品名的麻煩。後來這個模板在公司內部悄悄地流行起來，今朝也因此受到領導小小的表揚。

原來統計並非手工活

今朝雖然學了三兩函數，但依然還是菜鳥。其實，嚴格來說只能算路上菜，也就是走在通過菜鳥的路上。

Ricell 公司每年都有組織十個左右上日本學習，郭小姐也申請去日本。她一去日本，今

朝就得頂替她的工作。今朝這人最不擅長溝通，所以對於經常需要跟供應商溝通的組長並不感冒，正所謂無官一身輕。今朝本想推脫，但這一職位今朝是最合適的人選，除了他沒人熟悉中山那邊的情況。

既來之，則安之。還是用點心事在交接的事兒上面才是正道，要不然郭小姐一走，今朝就只有哭的份兒。

常用表格交接

只見郭小姐熟練的打開《隆成每天不良記錄表》，這是中山隆成的不良記錄表，每天都要記錄下每一款產品的不良數量，有顏色填充部分是事先設置好的公式，會自動幫你統計。

今朝點擊了不良數列的單元格，發現真的存在一條公式。不良數故名思議就是對每天的不良數量進行匯總，其實叫總不良數更合適，SUM 應該就是求和的意思。

其他兩個公式為：不良率

=IF(C5="","",C5/$DU$30)

次數

=COUNTA(I5:DU5)

除了 IF 外，雖然知道其他兩個函數的大概意思，但怎麼使用函數不太清楚，就像郭小姐請教。

郭小姐說 SUM 函數使用很簡單，點擊 C5 單元格→「公式」→「自動求和」，最後用滑鼠選擇求和區域就行。

這個 COUNTA 函數就藏得深一點，「公式」→「其他函數」→「統計」，瀏覽到 COUNTA，

這個函數的用法跟 SUM 一樣。

今朝跟著試了一遍，基本就記住了。以前一直用計算器，統計數據非常慢，有了這兩個函數，統計又上了一個台階。

郭小姐看今朝明白了，就打開了第二張表《隆成月度統計表》，這裡面有填充色的已經設置好公式，還有最小、最大、合計、平均這些都設置好公式，你只要填入一些基礎數據就行。

今朝粗略看了下，也沒問什麼。

郭小姐就繼續說了一些工作上注意事項、生活注意事項，說了好久。不過今朝基本上也沒記住她說什麼，就如讀書的時候：左耳進，右耳出，管老師說得天花亂墜。

不過對這兩張表格，今朝卻很感興趣，利用下班的時候，又看了幾次。無意間點開了「自

動求和」的下拉按鈕，真是坑爹，居然還有這麼多有關的函數。估計很多人都跟今朝一樣，看到這些無限感慨。以為這裡就只有 SUM 函數而已。

思考題

1、文中郭小姐向今朝介紹用 COUNTA 統計數字次數，這種統計方法是否正確？如果不正確，請說出理由。

思考題釋疑

OR 與 AND

1、如果設備為空調或者風扇，就可以睡得著，否則睡不著，怎麼設置公式？

邏輯函數的使用頻率很高，雖然簡單，但必須掌握好。先看 OR，函數語法：

OR(條件 1,條件 2,條件 n)：只要滿足其中一個條件就顯示 TRUE。

=IF(OR(A2="風扇",A2="空調"),"睡得著","睡不著")

這個相對簡單，就不做多餘的解釋。跟 OR 很像的一個函數 AND，函數語法：

AND(條件 1,條件 2,條件 n)：只有滿足全部條件才顯示 TRUE。

以現在 Excel 當選 MVP 來說明，最基本的要求就是：精通 Excel 某方面的內容（如函數與公式）、無私幫助別人，只有這兩個條件滿足才能擁有成為 MVP 的最基本條件，只要其中

一個不滿足永遠當不了 MVP。

=IF(AND(精通 Excel 某方面的內容,無私幫助別人),成為 MVP 的基礎條件,什麼都不是)

說得通俗點，OR 就是或者，AND 就是並且。

模糊匹配≠通配符查找

2、如何根據姓名簡稱，獲取性別？

對於這題，有些人用模糊匹配的方法獲取性別，但今朝的模糊匹配不一定是笑看今朝，模糊匹配一般都是用在區間的判斷上。

根據右邊的成績判定表獲取等級

=VLOOKUP(A2,D:F,3)

當 VLOOKUP 第四參數省略，也就是模糊查找時，查找不到對應值，返回小於查找值的最大值，如 50 查找不到對應值，就返回 0 的對應值；67 返回 60 的對應值，60 就是小於 67

的最大值。

回到今朝跟笑看今朝上，漢字是按拼音排序的，J（今）&>X（笑），明顯的不成立，也就是說用今朝查找笑看今朝將得到錯誤的答案。

="今朝">="笑看今朝"，返回 FALSE，即不成立。

既然模糊匹配返回不到正確值，應該用什麼才可以查找到對應值？

先來了解下 Excel 中的通配符：？代表一個字元，*代表所有字元。當字元個數確定的時候就用？，比如知道區域中都是兩個字元，第二個字元為朝，就可以用公式：

=VLOOKUP("?朝",{"今朝";"老鄉"},1,0)

而數據源很明顯的字元個數不確定，所以用：

=VLOOKUP("*"D2"*",A:B,2,0)

忠告：在沒弄明白參數的作用時，切忌不可亂用！

COUNT、COUNTA、COUNTBLANK

3、文中郭小姐向今朝介紹用 COUNTA 統計數字次數，這種統計方法是否正確？如果不正確，

請說出理由。

先來看下面三個函數的作用：

COUNT：統計區域中數字的個數

COUNTA：統計區域中非空單元格的個數

COUNTBLANK：統計區域中空單元格的個數

看到這裡可以肯定地斷定郭小姐的做法是錯誤的，在數據區域中錄入文本可能性很小，

但錄入空格的可能性很大，一不小心就錄入一兩個空格。

全面掃盲

四則運算

今朝一想到以後就要負責中山隆成那邊，就發愁起來了。以前需要處理的數據很少，用

計算器還勉強能應付過去，但現在需要處理的數據越來越多，不靠 Excel 根本做不好。考慮了半天，今朝打算豁出去了，買本《函數手冊》來學習。

對於英語水平超級爛的今朝來說，學函數實在是一種挑戰。今朝思索著：我能學好函數

嗎？顧慮歸顧慮，但難得第一次買 Excel 書籍，怎麼也要花點心思來學習。

這段時間一直出差，一下班女同事就看電視，今朝又對電視不感興趣，基本上很少跟她

們一起看。在這邊屬於斷網狀態，那時候還沒有微博，手機能做的事情實在有限，上會 QQ

後就不知幹嘛。突然，今朝想起了前段時間購買的《函數手冊》，所以掏出來準備學習。為了不打擊自己的自信心，今朝選擇了以前接觸過的函數，開始看起來。有很多知識，如果學過後沒有重新複習的話，很容易就忘記了，重複看有助於記憶。就像很多人考試前一樣，都會花點心思重新翻開書籍複習，這樣以前的知識點又重新被牢記於心。

這樣的日子應該持續有兩個月吧，今朝每天抽出一點時間翻開書籍，對有興趣的知識點先了解，不感興趣的不看，如工程函數。沒想到今朝還學會了很多函數的基本用法，為此今朝興奮了一小段時間。

還因此得出結論：不會英語也可以學函數！

學習的目的是什麼？不就是為了能學以致用嗎？為此，今朝開始嘗試自己製作模板，雖然很粗糙，但也是一種進步。

經常出差，有時關心的僅僅是每回可以拿多少補助費而已，人有時就是這麼現實，不要見笑。今朝還算有點基礎，不一會兒就製作出《出差登記表》，這樣一來，每一回出差就知道住宿今天，能獲取多少補助。

怎麼獲取住宿天數呢？首先來了解下日期知識點，日期其實就是一組序列，1900-1-1 就

是日期的起點，也就是序列 1。同理 1900-1-2，就是序列 2。如果將日期的單元格格式設置為常規，就是日期的本身序列號。

我們知道數字是可以直接進行四則運算的，那麼日期也可以。所以，住宿天數就是結束日期減去開始日期。默認情況下，相減單元格會自定變成日期格式，當你看到日期格式的時候不要慌，只要明白日期其實也就是普通的數字，設置為常規格式就變回來了。

補助小計的公式為：

=E5*F5

思考題：

1、日期直接相減會自動顯示成日期格式，如果不設置單元格格式，怎麼自動獲取兩個日期

的相差天數，比如第一個就是 4？

2、假如日補助金額格式改變，如圖所示，怎麼獲取補助小計？

比較運算符

以前沒有出差等級表的時候，今朝也沒去在意補助金額，反正領導給多少就是多少。現在有了這份表格，就知道領導給得補助知否準確。

話說領導是怎麼算補助的：掰手指算天數，7-26,7-27……8-1，一般持續十天以上的出差

很少，所以手指還夠用，統計好天數後，比如 5 天就敲計算器=5×30，逐個統計。這樣算其實也沒什麼不好，就是偶爾會出一點點小差錯而已，如果能夠核對兩次以上估計沒問題。

領導的做法今朝不敢幹預，也沒有領導那麼有時間，所以能夠藉助 Excel 統計的東西，

還是盡量藉助 Excel。

接著一起看 Excel 怎麼表示區間。

作為質檢人員，接觸最多的是 AQL 抽樣基準表，在某個範圍內的出貨數量要抽檢多少數量。

那區間該怎麼表示呢？在數學中經常用 1&<=X&<=500，這種表示方法，但 Excel 真的表示方法是否一樣？今朝嘗試了，卻是不成立的。

很多時候，理想跟實現就是存在差距。在 Excel 首先執行 1&<=30 的判斷，這個明顯成立，

返回 TRUE。接著用 TRUE&<=500，邏輯值大於數字，得到 FALSE。 Excel 中數據的排序依據：錯誤值&>邏輯值&>文本&>數字。

既然這樣不行，正確的做法應該怎麼做呢？出於某個區間也就是大於等於最小值，小於等於最大值，只有滿足這兩個條件才能成立。

=AND(1&<=30,30&<=500)

前面說過四則運算，其實也可以用在這個地方。

=(1&<=30)*(30&<=500)

滿足現實 TRUE，不滿足現實 FALSE，然後兩者相乘。在這裡 TRUE 可以看做是 1，FALSE

可以看做是 0，只要都滿足就顯示 1，否則就是 0。

TRUE*TRUE=1

TRUE*FALSE=0

FALSE*FALSE=0

相對引用、絕對引用、混合引用

這一天今朝沒事在辦公室，回頭看到覃美女在做報表。今朝仔細一樣，發現覃美女現在懂得用電腦處理數據，而沒用計算器，心上暗自誇獎：不錯，有進步。可是過了幾秒中，發生了一件讓今朝大跌眼鏡的事情，如果今朝有眼鏡的話早就碎了一地。

覃美女，輸入=，接著用滑鼠點擊 G6，輸入/，接著再用滑鼠點擊 F3。看到這裡還算正常，接著直接她一回車，到 H7 單元格，又重複上一操作。

啊？這樣也行？

今朝半調侃問：怎麼不用計算器算呢？

覃美女倒也回答得爽快：用 Excel 算比較准。

今朝：Excel 確實挺適合計算，但你為什麼不用絕對引用？

覃美女：這個以前讀書時看過，不過太久沒用了，都不知道怎麼用。

今朝：你輸入第一條公式後，別急著回車，用滑鼠選擇 F3，然後按【F4】鍵。注意觀察

編輯欄的變化，自動添加了兩美元（$），最後向下填充公式。

覃美女：還真的是這樣，這樣就快捷多了。原來是按【F4】鍵，這回我得記牢它。知識補充：

1、通過【F4】鍵可以不斷切換各種引用方式。

2、相對引用：就是行列都不給美元，這樣公式複製到哪裡，哪裡就跟著變。

3、絕對引用：行列都給美元，不管怎麼複製公式，就是不會變。

4、混合引用：只給行或者列美元，給行美元，行不變；給列美元，列不變。

加入第一幫派——Excel Home

天下武功出少林，天下 Excel 技巧出 Excel Home。在我的印象中，每一個高手都曾經是

EH 會員，如果你是高手但不是 EH 會員的，請告訴我。

說句實話，現在的書籍很多都是枯燥無味的，今朝利用兩個月的時間還是沒法整本書看

完。有一天晚上，今朝在一朋友的空間發現了 EH 這個論壇，出於好奇，點擊打開論壇。在論壇的首頁提供了好多的視頻，今朝如獲至寶，下載了一個晚上。

第二天開始就嘗試聽這些視頻，不聽還沒什麼，一聽覺得自己什麼都不懂。不過還是勉強聽了一些，在視頻結束的時候都有提到可以在論壇交流之類的話。為此今朝註冊了一個號：

盧子。盧子→爐子，感覺這個比較容易記。註冊後就在論壇到處閑逛，無意間發現有一些精華的帖子可以下載，這一發現可以說浪費了今朝無數個晚上的時間。今朝變成了瘋狂的下載者，這裡下載，那裡下載，電腦的磁碟每日劇增。資料這麼多，今朝拈拈自喜了好一段時間。以為有這些，以後就是高手，其實擁有這些你依然什麼都不是，下載≠學習。有一天，今朝在逛論壇的時候，發現一個帖子《從瘋狂的下載者變成真正的學習者》，一帖說到了今朝的

心。下載一萬個附件，還不如用心看好一個帖子，今朝一狠心將電腦上所有跟 Excel 有關的資料全部刪除，開始靜下心來。

今朝開始在 EH 潛水學習，這一潛就是兩三個月，這期間功力大增。但獨學而無友是一件很痛苦的事情，今朝嘗試了在 EH 發表了一個帖，感嘆沒有朋友這件事。沒想到很快就得

到 EH 站長的回復，鼓勵今朝多跟大家交流，交流多了自然就會有朋友。看到老大的回復，心裡很受鼓舞，從此走上另一種學習方式——助人式學習。

在論壇嘗試了回答一些自己會的問題，逐漸的懂得越來越多。也就是在這一年了，加入

了很多 Excel 交流群。

憑藉 Vlookup 解疑難

截止到目前，今朝懂得不少函數，但真正熟練的就只有 Vlookup。Vlookup 是一個很神奇的函數，有了它查找數據變得異常輕鬆。

EH 的函數版每天的熱鬧非凡，提問者一個接一個，也正因為如此，才給今朝提供一個

一展身手的機會。其實，準確點應該叫班門弄斧。

在這眾多問題中，今朝只關注一類題：查找。只要標題包含關鍵字：查找，今朝就會打開鏈接，嘗試解讀。一個晚上解答三五個疑難也屬於正常的事，隨著解讀問題的增多，今朝

也越來越了解 Vlookup。在 10 年的某一天，今朝總結了一個帖子《Vlookup 函數問題彙集》，裡面涉及到成 9 個方面，常見的基本上都有。現在挑選其中部分內容進行說明。

VLOOKUP 在表格數組的首列查找指定的值，並由此返回表格數組當前行中其他列的值。

語法：

VLOOKUP(查找值,區域,返回區域第幾列,查找模式)

模糊查找與精確查找

模糊查找必須對數據源進行升序，否則出錯。

精確查找數據源即使不排序也不會有影響。

查找的結果返回多列對應值

俗稱是區域中第 2 列，正常的話就設置公式為：

=VLOOKUP($A15,$A$2:$F$11,2,0)

接著是 3,4,5,6 列的公式，依次修改第三參數為：

=VLOOKUP($A15,$A$2:$F$11,3,0)

=VLOOKUP($A15,$A$2:$F$11,4,0)

=VLOOKUP($A15,$A$2:$F$11,5,0)

=VLOOKUP($A15,$A$2:$F$11,6,0)

只有 5 列修改也不是很久的事，但假如是 50 列呢，直接就暈菜了。有沒有更好的辦法呢？答案是肯定的。

函數中有兩個函數，ROW 跟 COLUMN，一個產生行號，一個產生列號。這裡以 COLUMN

進行說明。這個函數有兩種用法，一是省略參數，比如在 A 列輸入就返回 A 的對應值 1。二

是輸入參數，如果要返回對應值 2 的話，可以參數寫 B1。今朝喜歡使用第二種。說到這裡，公式就可以改善成這樣：

=VLOOKUP($A15,$A$2:$F$11,COLUMN(B1),0)

剩餘的暫時不涉及，直接上傳附件，有興趣可以去了解。

溫馨提示：當你覺得很繁瑣的時候，請停下來思考，也許 Excel 本身就有提供相應的解決方案。

Lookup 的二分法是個傳說

在《VLookup 函數問題彙集》這個帖子發出去當天，帖子上升為 24 人氣榜首。昨天看了，下載量超過 1000 次。這對於名人而言，或許沒有什麼，但對於初出茅廬的今朝而言，卻是莫大的鼓舞。帖子的回復中有一個人叫周義坤，一看資料是老鄉，所以留了點心。

今朝又像往常一樣回答有關查找的問題，依然使用 Vlookup 函數解決，而當今朝再次查

看帖子的時候，後面總有一個叫周義坤的人用 Lookup 函數提供另一種答案。連續好多題都是這樣，也許這就是緣分吧。

周義坤首先在論壇加今朝為好友，今朝同意並用 QQ 添加他為好友。兩人開始在 QQ 聊

起關於查找的問題，他反覆強調 Lookup 好用，讓今朝一定要去學習，並推薦一個帖子

《Vlookup 與 Lookup 一一過招》。其實，今朝與周義坤不就是在上演一場真實版的兩函數過招嗎？

今朝抽空看了這個帖子，很受啟發，同時查找了很多有關 Lookup 函數的資料，開始學習起來。不過在這些帖子中提到了二分法，哎，這個說法不知道嚇怕了多少初學者。今朝也是其中一員，所以在很長的一段時間，對這個函數依然不太了解，甚至有意逃避它。至到有

一天跟提出這個二分法的神人 gouweicao78 聊天，才知道這個二分法可以不掌握，這只不過是個傳說，即使不會照樣可以學好 Lookup。聊天的時候還涉及到 Lookup 的另一種方法，經典查找模式，這個實在太好用了，有了它，查找就是這麼簡單。

gouweicao78 是誰？有函神之稱（Excel 函數之神），跟另一個人山菊花，兩人合稱花草，

EH 的兩大高手。gouweicao78 的帖子富有邏輯性，山菊花的帖子幽默有趣，各有其優點。他

們兩人的精華帖，今朝基本上都用心看了多次。

扯遠了，繼續 Lookup 的話題。gouweicao78 就是靠著 Lookup 函數而一舉成名的。學

Lookup 看他的帖子最好。不要迷戀二分法，二分法只是一個傳說。拋開這個傳說，一起來見證 Vlookup 的神奇。

正常的話，每發一個帖子，都有有回復，現在想看最後回復的人員是誰？

=LOOKUP("座",B:B)

幫助提到：如果 LOOKUP 找不到 lookup_value 的值，它會使用數組中小於或等於

lookup_value 的最大值。

也就是返回最後一個對應值。

要返回最後一個對應值，只有一個辦法就是找到一個比所有人員都大的值。漢字的排序

是以首字母進行排序的，如果要選擇最大的一定要選擇首字母包含 Z 的漢字。座是一個很大的漢字，正常情況下的漢字都比它小，當然他並不是最大的。最大的字是生僻字，說句實話，今朝也寫不出來。以後如果要返回最後一個文本，就用座，寶座的座。

有人看到這裡也許會問，那最大的數字是什麼？

Excel 中最大的數字是 9E307，也就是 9*10^307，除非搞科研，否則的話，10 位數的數

字已經夠大了。

現在一起來認識 Lookup 的經典查找模式：

Lookup(1,0/((條件 1)*(條件 2)*(條件 n)),返回值)

Vlookup 函數在處理逆向查找跟多條件查找是個難題，既然這樣，那就單獨以這兩個例

子來說明 Lookup 查找之方便。

根據訂單號，逆向查找番號

只需套上模式即可：

=LOOKUP(1,0/($C$2:$C$8=A12),$A$2:$A$8)

在 Lookup 的字典中，沒有逆向這個詞。管你什麼方向，對 Lookup 通通都一樣。

0/($C$2:$C$8=A12)的作用就是將符合條件的值轉換成 0，其他轉換成錯誤值。在這裡認

識下有獨孤九劍之稱的【F9】鍵。

哪裡不懂抹哪裡，公式理解 so easy。

=LOOKUP(1,0/($C$2:$C$8=A12),$A$2:$A$8)

現在括弧內，不理解，直接在編輯欄抹黑，就看到原來這部分是訂單號的逐一比較，如

果滿足就顯示 TRUE，否則顯示 FALSE。

=LOOKUP(1,0/{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},$A$2:$A$8)

了解後，記得按【Ctrl+Z】返回，否則公式就變了。

=LOOKUP(1,0/($C$2:$C$8=A12),$A$2:$A$8)

一次看完 0/(條件)，得到的是由 0 跟錯誤值轉成的數組。

=LOOKUP(1,{0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},$A$2:$A$8)

Lookup 喜歡以大欺小，用 1 查找 0，就是返回最後一個 0 的對應值。前提是有多個對應

值，沒有的話就直接返回 0 的對應值。

如果還不理解，沒關係，先記住這個模式！以後用多了自然會理解，就如書讀百遍，其義自現。

最後看看 Lookup 怎麼進行多條件查找？

根據俗稱跟訂單號，查找訂單數

套用前面的勞動成果，一步到位。

=LOOKUP(1,0/(($A$2:$A$10=A14)*($B$2:$B$10=B14)),$C$2:$C$10)

以後需要查找就直接套上，查找再也無煩惱！

七夕閑聊

明天就是七夕了，這個時候除了愛情的事兒，聊別的都感覺不合時宜。

今朝從來不相信一見鍾情，所謂的一見鍾情不過就是一見鍾臉罷了，而日久生情更符合實際情況。感情這事兒挺奇妙的，有人因為感情毀了一生，有人因為感情成就了一生。每個成功男人的背後都有一個默默無聞的女人，可見女人對男人影響很大！當然我現在並不能算男人，只能說是個男孩，呵呵。

據說每個 Excel 學得好的人，都有一段不為人知的往事，而今朝的往事卻是因為感情。以前讀書時只顧讀書，儼然一書獃子，讀了十多年書居然沒談過戀愛，挺悲劇的。直到工作兩年的時候，才談了一場戀愛。這段感情的時間不算很長，但卻深深的改變了今朝。

那女孩叫 F，在她還沒開始上班的時候，老鄉就跟今朝說這人挺可愛的，不知道是有意還是無意這麼說。不管怎麼樣，今朝把這話記在心中。等到 F 來上班的時候，今朝特意多看了兩眼，感覺也沒什麼吸引人的。可能人得對比才能看得出來，就像電影《唐伯虎戲秋香》中的秋香，唐伯虎在沒見到秋香之前，別人都跟他說她是美女，當第一次見到時，感覺很一般。最後他的朋友，告訴了他：你需要跟其他女的一起看。其他女的長得實在不敢恭維，這

時很普通的秋香就變成一美女。繼續說 F 的事兒，跟 F 一起來上班還有 Z，兩個一對比，優缺點就擺在眼前。這事今朝本來也就沒太當回事，可是令人沒想到的是，往後連續幾天下班

後的巧遇。真是無巧不成書，正因為這幾次巧遇，讓今朝跟 F 兩人越來越熟。

今朝嘗試第一次約 F 出來，沒想到沒有任何推託的話語，真的是太順利。然後開始逛街，聊一些可有可無的話兒，有人說過，正因為有這些廢話存在，才大大增進人的感情。有了第一次就有第二次，第三次……，這每一次約會今朝都挺珍惜。事情來得太順也許並不是好事，在某一天，公司內部舉行聚餐，發生了一件不愉快的事兒。也正因為這事，今朝傷心地提前

離開，關掉手機，不讓任何人聯繫到。當第二天打開手機看到 F 很多簡訊，今朝回了一句：我們分手吧。今朝回了這句，心都在滴血。

……

為了忘掉這段感情，今朝一有空閑就到處遊玩，在短短的一段時間內，今朝玩了大半個

東莞。不過這樣終究不是辦法，人總得積極點。也正因為如此，今朝把業餘的 99%的時間用

在了學習 Excel 上。每天不斷學習，不斷交流，直到把這段感情深藏內心深處。初戀不管怎麼樣，都是無法忘掉的。

聊了那麼多，突然有點點傷感。還是不說，回到 Excel 中。

人最難忘記的往往是第一次跟最後一次，如果在 Excel 中，怎麼分別獲取人員呢？

如果找不到正確的方法，此題很難。但如果了解了 Vlookup 跟 Lookup 兩個函數，這題輕而易舉。當有多個對應值，Vlookup 是查找首次出現的對應值，Lookup 是查找最後一次出現的對應值，利用這個特點，可以將公式設置為：

=VLOOKUP("喜歡",A2:B7,2,0) =LOOKUP(1,0/(A2:A7="喜歡"),B2:B7)

隨便 Vs 隨機

隨便這個詞我們會經常聽到，比如你問朋友想吃什麼，她往往回答：隨便。又如，你問她想看什麼電影，她回答：隨便。

有一些人就會由隨便聯想到隨機，但它們倆真的不是一回事！隨機很多時候體現的是一

種專業。今朝從事品質檢查，經常會利用 AQL 對產品進行抽樣，也就是根據一個標準從總體中抽取一小部分進行檢查。這時你不能隨便，那樣犯錯誤的可能性非常大。有經驗的人，會進行隨機抽樣，比如每個卡板的產品從各個方向抽取部分產品。又或每過一個時間段，從生產的產品中挑選部分進行檢驗。

在 Excel 中有兩個隨機數：RANDBETWEEN 和 RAND。在你猶豫的時候，可以讓它們幫你做決定。如果現在有十個產品，編號為 1 到 10，你想隨機挑選一個的話，就可以用

RANDBETWEEN，這個函數返回位於兩個指定數之間的一個隨機整數，語法：

=RANDBETWEEN(起始值,終點值)

因為是 1 到 10，所以可以設置為：

=RANDBETWEEN(1,10)

前面提到【F9】鍵可以解讀公式，其實它還可以讓公式重新計算。輕點幾下【F9】鍵，從中選擇一個產品作為抽樣的對象。

如果要現在知道產品的名稱為 A,B,C,D,E,F,G,H,I,J，想從中挑選三個呢？RANDBETWEEN 只

能產生隨機整數，即使可以產生字母，也有可能出現重複。因為是隨機數，不可能保證在這麼小的範圍內，所有的數字都不重複。

有句話叫做：正難則反。正常的辦法很難做到，我們就可以變通下。先把產品名稱羅列

在 A 列，然後在 B 列利用 RAND 生成隨機數，最後進行排序，從中選擇前三個即可。RAND

這個函數沒有參數，作用就是產生 0-1 之間的隨機數。

有人也許會問，如果產品名為 A-Z，一共 26 個字母，手工輸入很麻煩，有沒更快捷的方

法？答案是肯定的，不過先不急，先來看三個數字密碼。52946 45230 50403，如果把這三個數字分開放，也許沒人知道含義，但在情人節這一天說的，即使是傻瓜都能大概猜到意思。如果你想從諧音上獲取含義，根本是破解不了。

一起來認識 CODE 跟 CHAR 兩兄弟，兩者如影隨形，有其中一個就會有另外一個。每一

個字元都有唯一的數字碼，而每個數字碼都能組成唯一的字元。52946 45230 50403，這三個就是數字碼，通過這些可以找到由這三個數字碼組成的字元。

數字碼通過 CHAR 可以轉換成字元，那字元就可以通過 CODE 轉換成數字碼。通過驗證，知道 A 的數字碼為 65，Z 的數字碼為 90，每個字母之間的數字碼都是遞增的，步長值為 1。

前面已經提過 ROW 可以產生行號，COLUMN 可以產生列號。在這裡就派上用途了。利用 ROW 可以產生 65 到 90 的數字碼。

=CHAR(ROW(A65))

有的人喜歡用 ALT+數字鍵輸入一些特殊符號，如√。其實數字鍵的 41240 就是√對應的數字碼。

經典的 Index+Match 組合

疑難彙集

提取括弧內數字

昨天在群內回答了一個問題，裡面涉及到的知識點很容易被人忽略，

所以拿出來聊聊。

說明：特殊字元在論壇顯示亂碼，不懂設置。

人生：請教下高手，

=IF(AND(B12=10.1,(FIND("?",C12)=2)),(ABS(SUBSTITUTE(C12,"?"

,""))))與 =IF(AND(B12=10.1,(FIND("R",C12)=2)),ABS(SUBSTITUTE(C12,"R",

"")))兩個公式如何合併在一起？將三種取值方法合併到一個公式里。

今朝：提取數字？

人生：對

今朝：只有兩個特殊字元？

人生：對，但是括弧內數字的位數是不固定的哈

今朝：=TEXT(-SUBSTITUTE(SUBSTITUTE(C12,"?",),"R",),"0.000")

————————————————分割線———————————————

1、了解負數的另一種表示方法

負數等同於帶括弧的數字

2、文本數字轉換是數值

通過負運算可以將文本數字轉換成數值

3、替換掉無用字元

? 跟 R 是非數字，用技巧的話可以用查找替換，在公式中的替換用

SUBSTITUTE =SUBSTITUTE(SUBSTITUTE(C12,"?",),"R",)

完整公式應該這麼寫

=SUBSTITUTE(SUBSTITUTE(C12,"?",""),"R","")

這裡有一個專業名詞叫預設，也就是默認的意思，但預設不同於省略，而是有

用逗號佔位。最常見的是 VLOOKUP 函數的用法

=VLOOKUP(A1,B:C,2,)精確查找（用逗號佔位就表示，默認這裡是 0）=VLOOKUP(A1,B:C,2)模糊查找（省略逗號就表示這裡什麼都沒，只有三個參數

到這裡就得到帶括弧數字，如果不懂轉換請重新看 1、2 點。TEXT 在這裡的作用等同於自定義單元格格式效果，將小數點設置成 3 位。

四捨五入,今朝已奔三

網友：用 ROUND 對數據進行四捨五入，怎麼統一位數，即 12 顯示 12.0？

今朝：可以用 TEXT 取代 ROUND。 =TEXT(A2,"0.0")

TEXT 其實跟自定義單元格格式有點相似，先用設置單元格格式，設置為小數點後 1 位，

查看自定義格式代碼，這時顯示 0.0_。

這個下劃線」_」有什麼作用呢？其實是顯示一個跟下劃線一樣長度的空白，當然也可以直接用空白代替。看到這裡知道這個「_」在這裡沒有作用，可以去掉。也就是最終的自定義

格式代碼為 0.0。

為什麼不使用自定義單元格格式而用 TEXT 代替呢？自定義單元格格式只是欺騙我們的眼睛罷了，實際數據的性質並沒有改變。TEXT 得到的是真正的數據。這就像化妝（自定義單

元格格式）跟整容（TEXT），化妝後看起來很好看，卸妝後依然還是本來的相貌，沒有任何改變。整容後是本質已發生改變。

聊到四捨五入，突然想起了一個問題，明天就是今朝的生日，也就是說今朝已 26 周歲了，四捨五入已奔三。歲月不饒人啊！

有人也許會說，這樣也行？

Excel 可以這麼四捨五入，當然現實也可以這麼用。

ROUND 的第二參數，不僅可以是正數，也可以是零，甚至是負數。-1 就是個位進行四

舍五入後進位。6 大於 5，也就是升 1。如果換成-2 的話，今朝就返老回童，重新走進娘胎里。

數字舍入，存在即合理

說起 ROUND，不得不說他的兩兄弟，ROUNDUP 跟 ROUNDDOWN。既然是三兄弟，那語法也就一樣，只是作用稍有差別而已。UP 就是向上的意思，DOWN 就是向下的意思，也就

是說 ROUNDUP 就是向上舍入數字，ROUNDDOWN 就是向下舍入數字。

李版用下面的公式測試今朝的年齡，如果實際這麼多歲，今朝已成仙。

=ROUNDUP(A1,-2)

-2 也就是用十位上的數字進行判斷，UP 不管什麼數只進位，不捨去。也就是說今朝已

成百歲仙人。

同理，如果用 DOWN，不管什麼數只舍不進，今朝也就還沒出生。

=ROUNDDOWN(A1,-2)

這兩個函數有什麼用呢？

比如公司最近生意好，老闆在發工資的時候就大方點，2120 就發 2200。百元以下通通

進位，這時 ROUNDUP 就派上用場，只需把第二參數改成-2 就行。又再如別人在買東西，126

就算 120，零頭不要，這時就設置 ROUNDDOWN 的第二參數為-1。

接著看另外一種舍入，在很多時候，稱重量並不需要非常準確，尾數小於 0.25 就算 0，

大於等於 0.75 算 1，其餘算 0.5。也就是說，最小精確度為半斤，不足半斤按數量的大小進行舍入。

粗略這麼一看，上面的三兄弟都派不上用場，難道還存在第 4 個這樣的函數？今朝學 E

這麼多年了，至今沒發現過，也許在未來的某個版本就會出現也說不定。

仔細觀察，0.25 跟 0.75 兩個數字乘以 2 尾數都是 0.5，這樣就可以用 ROUND 進行四捨五入。當然有借有還，現在借了*2，自然要/2，這樣才能回到原來。

思考題這道題是前幾天一粉絲在微博私信今朝的：大於 10000 按百位數進行四捨五入，大於

5000 按十位數進行舍入，大於 100 按個位數進行舍入。如果是你會怎麼做呢？

註：不考慮小於 100 這種情況。

重溫 Lookup，認識 Match

思考題解疑

這道題是前幾天一粉絲在微博私信今朝的：大於 10000 按百位數進行四捨五入，大於

5000 按十位數進行舍入，大於 100 按個位數進行舍入。如果是你會怎麼做呢？

註：不考慮小於 100 這種情況。

粗略看了題目，大部分人都會使用 IF 函數，包括今朝在內。但今朝有一習慣，就是 IF

函數超過兩個就放棄使用，改用別的函數取代。

稍加思考，立即可以看到，這裡一共有三個區間，100-4999,5000-9999,10000 以上，而

每個區間都會對應一個值。從小到大，依次是個十百，ROUND 函數的第二參數設置為-1，-

2，-3 就是根據個十百舍入。現在我們可以根據上面的條件模擬一個對應表。

按某區間查找對應值可以利用 Vlookup 的模糊查找，當然用 Lookup 會更好。

為什麼要搞這個對應表呢？因為這樣看起來會清晰很多，特別是初學者。公式引用後如

果覺得對應表在這裡會佔地方，這時可以對公式進行一些小修改。對引用的區域依次按【F9】

鍵抹黑，修改後按回車。

最終公式為：

=ROUND(A2,LOOKUP(A2,{100;5000;10000},{-1;-2;-3}))

這時就可以把對應表刪除，因為這時的公式已經不依靠對應表存在。以前今朝就是經常採用這種變形，不過當你越來越熟練這種形式後，就不能老依靠對應表。這時對應表是在心中，而不在表格中，然後按心中所想設置公式。

在這以字元長度論英雄的時代，這個公式實在太長了，必須再精簡。將相同部分提取出來，如下

=ROUND(A2,-LOOKUP(A2,{1;50;100}*100,{1;2;3}))

這個是今朝最初的公式，但僅僅是得到這個公式今朝並不滿足。玉不琢不成器，公式不

琢磨不成經典。Lookup 的對應值是 1,2,3，好有規律好不好？如果換成排位會怎樣？一起來了解下 Match，這個函數是獲取查找值在區域中的排位，語法為：

MATCH(查找值,區域,查找模式)

查找模式有三種：

0 就是精確查找，數據源不做要求

1 就是模糊查找，數據源升序

-1 也是模糊查找，數據源降序

一般情況下，參數為-1 可以不用，效率很低下。現在來看前兩種，重點放在模糊查找上。

=MATCH(3,{1,3,5},0)，用 3 在區域中精確查找，剛好是第二個，也就是得到排位 2。=MATCH(2,{1,3,5},0)，用 2 在區間查找對應值，沒有對應值就返回錯誤。

=MATCH(2,{1,3,5})，用 2 在區域中模糊查找，因為找不到對應值，就查找小於 2 的最大值 1

的位置，也就是得到排位為 1。

說明：第三參數省略也是模糊查找。

=MATCH(2,{1;50;100})，排位為 1 =MATCH(51,{1;50;100})，排位為 2 =MATCH(100,{1;50;100})，排位為 3

到這裡可以知道，Match 可以取代 Lookup 獲取 1,2,3，所以最終公式為：

=ROUND(A2,-MATCH(A2,{1;50;100}*100))

溫馨提示：學習階段要多從各個角度思考問題，爭取找到最合適的方法。但工作的時候，要以自己做擅長的方式來思考，只要能達到效果就行，字元長度這些都是浮雲。

初識 Index

既然提到 Match，就得說一下他的搭檔 Index。 Index 是返回區域中行列交叉引用的單元格，語法為

INDEX(區域,行號,列號)

當區域為一行一列的時候，可以允許為兩個參數

INDEX(區域,行號或列號)

一起回到表格中來理解下用法。

現在要獲取區域中第 3 行，第 2 列的對應值，就可以用，得到 3.4

=INDEX(A1:C10,3,2)

想知道 A 列第 3 行的對應水果，就可以用，得到香蕉

=INDEX(A1:A10,3)

第 1 行第 2 列的對應值，就可以用，得到單價

=INDEX(A1:C1,2)

但大部分情況下行列號都是不知道的，而是通過別的函數獲取。早上有網友問今朝：怎

么獲取最後 5 條記錄？

一般情況下記錄都是隨時增加的，你不可能去數下哪幾條是最後的，數到的數據即使現

在可以，過幾天就行不通了。最後一條非空記錄的行號，可以用 COUNTA，這個就是統計非空單元格的個數。

=COUNTA(A:A)

現在知道非空一共有 10 條，那最後 5 條記錄就是，10,9,8,7,6。一般情況下連續數字首

先考慮到的就是 ROW。

=COUNTA(A:A)-ROW(A1)

這樣就可以得到 9-6 這 4 條記錄，但缺少第 1 條記錄，也就是說在第 1 行的時候只能減

去 0。

=COUNTA(A:A)-ROW(A1)+1

現在已經知道行號，只需嵌套個 Index 就可以。

但有的人覺得還是按原來順序排序好，記錄顯示是 6-10。也就是第 1 行減去 4，第 2 行

減去 3……這時只需做小小的變形就行

=COUNTA(A:A)-5+ROW(A1)

現在就得到 6-10，再重新嵌套 Index

=INDEX(A:A,COUNTA(A:A)-5+ROW(A1))

如果想要改成引用 A:C 這種區域的形式，可以用

=INDEX($A:$C,COUNTA(A:A)-5+ROW(A1),COLUMN(A1))

以上這些都是比較常用的形式，接著看對行列進行求和。

如果想對第 2 行數據求和可以用

=SUM(A3:D3)

但這是理想狀態，如果行數可以變動，總不能每次都去修改區域吧。Index 的列號設置

為 0，就是忽略列號，直接引用整行數據，這樣就可以獲取動態行號，最後再嵌套 SUM 就

行。

=SUM(INDEX(A2:D10,F2,0))

同理，行號為 0，就是對整列數據的引用。

=SUM(INDEX(A2:D10,0,F2))

其實，Index 還有個通式：

INDEX(區域,MATCH,MATCH)

通過跟 Match 配合查找到各種對應值，這個今朝很少用，畢竟有 Vlookup 跟 Lookup 的

存在。這裡就舉一個簡單的例子進行說明，亂序的情況下返回多列對應值。

先根據番號獲取尾數的對應值，利用 Match 獲取返回的排位，然後返回 D 列的對應值。

=INDEX($D$1:$D$10,MATCH(A14,$A$1:$A$10,0))

同理，尾數也可以在原來的區域中，獲取排位

=MATCH(B13,A1:E1,0)

兩者合併

=INDEX($A$1:$E$10,MATCH($A14,$A$1:$A$10,0),MATCH(B$13,$A$1:$E$1,0))

Index 的基本用法就到此告一段落，這個函數的經典在於數組公式部分，耐心等待，在

不久的將來就會提到，那時你會愛上這個函數跟其他函數的組合！

Offset 之今朝訪友

今朝有三個朋友：無言、安安、胖紙，朋友間就得互相來往。每個朋友的距離都挺遠，今朝住在潮州，無言離今朝最近，在汕頭那邊，安安次之在深圳，胖紙最遠在東莞。同屬廣東，坐車半天內都能到。

假如 Offset 就是今朝的車，要如何去到每個朋友哪裡？

有人說過 Offset 會輕功，那速度當然不比車子慢。先來看看語法：

OFFSET(起點,偏移行,偏移列,行高,列寬)

註：行高、列寬為可選參數。

今朝要去無言那邊，只需向右坐 2 站就到

=OFFSET(A1,0,2)

如果要去安安那邊，只需向下坐 5 站才能到

=OFFSET(A1,5,0)

去胖紙那邊就稍微麻煩點，要向下坐 6 站，再向右坐 1 站才能到

=OFFSET(A1,6,1)

看到這裡大概知道 Offset 是幹嘛用的，如果偏移的行為正數就是向下偏移，偏移的列為正數就是向右偏移。相反，如果偏移的行為負數就是向上偏移，偏移的列為負數就是向左偏移。

假如今朝現在在胖紙家，想要回到自己的家。就得向上坐 6 站，就是-6，向左坐 1 站，

也就是-1。

=OFFSET(A1,-6,-1)

既然知道怎麼去，就得知道怎麼回，今朝還不至於路痴到忘記回來的路。

安安跟胖紙離得很近，今朝想知道她們兩家合併的範圍有多大，也就是深圳跟東莞的范

圍。今朝就得先到安安這裡，然後將這裡的行高設置為 2，列寬設置為 2，這樣就知道這兩地的範圍。

=OFFSET(A1,5,0,2,2)

但這個只是劃分個範圍，沒有統計，統計可以用 COUNTA，得到這兩地的範圍為 4。

=COUNTA(OFFSET(A1,5,0,2,2))

Offset 可以做各種偏移，所以常用於數據的變形，如將一列的數據轉換成多行多列。這

種題目只要找到規律其實很簡單。C 列就是偏移 1-4 的對應值，D 列就是偏移 5-8 的對應值，

E 列就是偏移 9-12 的對應值。也就是說往右一列就加 4。行列號可以通過 ROW 跟 COLUMN

獲取，所以可以將公式設置為：

=OFFSET($A$1,ROW(A1)+(COLUMN(A1)-1)*4,0)

思考題

如何將多行多列轉換成一列

讓人心寒的一組數據

截止到現在，汕頭因水災經濟損失了 30 億。很多人的家都變成「水上樂園」，有的地方

水深接近 3 米。轉眼已一周，看看收到多少捐款？

金額較大的幾筆捐款共計 2180 萬元

=SUM(C2:C8)

物資為 680 萬元

=SUMIF(B:B,"物資",C:C)

外省好心人捐款為「鴨蛋」，好大一個！

=SUMIF(D:D,"外省",C:C)

Sum 家族

函數中最受歡迎的有兩大家族，一個是以 Vlookup 為首的查找引用家族，一個是以 Sum

為首的求和家族。根據二八定律，學好這兩大家族的函數，就能完成 80%的工作。一起來看

看 Sum 家族怎麼完成各種匯總？

1、現在要知道各種產品的銷售數量

=SUM(C2:C8)

Sum 這個估計大家都很熟，就是對區域的數據進行求和。需要注意的是，如果區域存在

文本，將自動被忽略。

如現在 C2 單元格的值為：零，直接用 Sum 求和不會有任何影響。

2、如果想知道電腦的銷售數量

=SUMIF(B2:B8,"電腦",C2:C8)

Sumif→Sum+If，If 就是如果的意思，也就是如果滿足條件就對區域中的數據進行求和。

語法：

SUMIF(條件區域,條件,求和區域)

但這個語法並不是硬性規定的，允許只存在兩個參數。求數量大於 50 的和

=SUMIF(C2:C8,">50",C2:C8)

縮寫

=SUMIF(C2:C8,">50")

也就是說當條件區域跟求和區域一樣的時候，求和區域可以不用寫。

Sumif 函數有時可以取代 Vlookup 進行查找，當返回值為數值的時候用 Sumif 更好，即

使查找不到對應值也不會顯示錯誤值。Vlookup 查找要求很嚴，需要格式一樣才行，2012-1-

10 為日期格式，如果現在用文本格式卻查不到，而用 Sumif 卻可以，在這裡也能體現出 Sumif

的便利。

=VLOOKUP("2012-1-10",A2:C8,3,0)返回錯誤值

正確寫法：

=VLOOKUP(--"2012-1-10",A2:C8,3,0) =SUMIF(A2:A8,"2012-1-10",C2:C8)

繼續回到求和的問題上。

3、對 2013-1-16 之前的電腦的銷售數量進行求和

=SUMIFS(C2:C8,A2:A8,"&<2013-1-16",B2:B8,"電腦")

Sumif 是條件求和，Sumifs 就是多條件求和。語法：

SUMIFS(求和區域,條件區域 1,條件 1,條件區域 2,條件 2,條件區域 n,條件 n)

有了 Sumifs 以後再多條件都能夠搞定。這個函數是 07 版開始才有的函數，如果在使用

03 版的朋友，看到這裡估計得感嘆，多條件高版本有 Sumifs，我能用什麼？

雖然 03 版即將退出歷史舞台，但並不能忽視現在使用人群的龐大，十年前微軟就是靠 03 版爭取到 90%的用戶。

神秘的 D 字頭函數，Dsum 閃亮登場！D 字頭函數使用者很少，但並不因為少就忽略了他們，他們是非常的強大。

當你看到這個公式，你會不會懷疑自己的眼睛是不是看錯了，居然這麼簡單，不太可能啊！但事實就擺在眼前，不容你質疑。

Dsum 的語法：

Dsum(數據源,返回列號,條件區域)

說明：數據源跟條件區域必須包含標題。

這個函數有個好處就是，不管多少個條件，都一樣處理，想出錯都難。

Dsum 之三條件求和

Sum 顯神威

Dsum 函數雖然很好用，但也存在一些缺陷，如多條件求和時，向下複製公式會出現問

題。不能單獨匯總，而是累計匯總。這是個大問題，莫非 Dsum 因這問題而變成垃圾函數？

一個函數的力量是有限的，適當的時候要藉助別人之力。既然是累計匯總，H2 就是等

於本身，H3 也就多了 H2 的銷售額，H4 多了 H2+H3 的銷售額，只要能將這多出來的去掉就行。

也就是說分別對 H2,H2:H3,H2:H4 這三個區域求和，第一個區域只有一個單元格，變成區域的形式可以這樣寫 H2:H2。仔細觀察第一個 H2 始終不變，第二個 H2 會逐漸改變，也就是說這採用了混合引用。為了加深理解，先看下面的例子，對 A 列的數據進行累加。

=SUM($A$2:A2)

固定第一個 A2，也就是給點美元，這樣當公式下拉的時候，只改變第二個 A2 的值，到

B6 的時候，變成 A2:A6，從而實現累加。

所以，多條件求和可以用

=DSUM($A$1:$D$20,4,$F$1:G2)-SUM($H$1:H1)

Sum 忽略文本，所以可以直接引用第一個單元格。

其實都是低版本惹的禍，高版本這個會很簡單。

=SUMIFS(D:D,A:A,F2,B:B,G2)

只能說每個函數都有他的優缺點，要好好利用他的優點。

Sum 有一種特殊的表示方法，可以快速對多個表格進行匯總，這個很好用。

對匯總外的所有表格的 A1 進行匯總

=SUM("*"!A1)

*是通配符，代表所有字元，用在這裡的意思就是代表除了公式所在表格（匯總）的其

他所有表格。

如果現在只需要包含月的所有表格的 A1 進行匯總

=SUM("*月"!A1)

思考題怎麼統計所有表格的第二個字元為 T 的 A1 的和，也就是紅色填充色的表格

Sum 顯神威續

有很多人都喜歡將每月的數據分別存放在各個表格，然後再匯總。就如下圖，有 6 個月的數據，表格格式相同。

現在想在匯總表依次獲取每月的銷售量，該怎麼做呢？

有人也許會說，這還不簡單，直接 Sum 然後分別引用表格不就可以

=SUM("1 月"!C:C) =SUM("2 月"!C:C)

……

=SUM("6 月"!C:C)

當然這也是種辦法，不過用 Excel 就得考慮擴展性，假如是 12 個月？或者是按銷售人員分表，那估計就得幾百個表格，這樣還能繼續更改公式嗎？

當然你時間比較充足，要這麼做也無可厚非。但對於懶惰成性的今朝而言，這種事情是

絕不允許發生的。現在每個表格名已知，區域也已知，只要引用起來不就完事 OK。

=A2"!C:C"

這樣就可以獲得 1-6 月 C 列區域，那是不是這樣就能直接求和呢？

=SUM("A2""!C:C")

得到的是#VALUE!，警告你出錯了，原因是裡面的區域僅僅是文本，而不是真正的區域，那該怎麼辦呢？

別急，先看下 Indirect 函數，這個函數可以對文本進行引用，一般叫間接引用。

有間接引用自然有直接引用。比如現在有三個人，分別叫甲、乙、丙，現在甲要知道丙的事情，可以直接去問丙，也可以通過乙間接去了解丙的事情。也就是說直接引用就是直接

輸入區域就行，不通過第三者，我們正常的引用都是直接引用，如區域 C:C。間接引用就是

通過第三者才能獲得的，如 INDIRECT(C:C)。也就是說，只要嵌套個 Indirect 就可以。

=SUM(INDIRECT(A2"!C:C"))

再來看一個例子，加深理解。現在要得到 A4 跟 B5 的值，我們可以利用 Indirect 間接引

用。

其實個人經驗來說
對EXCEL的要求各個崗位公司都不同。高級一點的要VBA，低級的只用會輸數據就行了
而在數據分析師這個崗位里EXCEL的要求要高的多。
來點乾貨
Excel 2010基礎教程.zip_免費高速下載
Excel 2010函數應用視頻教程.zip_免費高速下載
Excel 2010數據透視表教程.zip_免費高速下載
Excel 2010VBA教程.zip_免費高速下載

各位看官點個讚唄

前言

看到這個問題很久了，一直想斗膽談談本人對「精通Excel的理解「，今天終於成文，大家看完了如果同意本人觀點，就點個贊，不同意，歡迎指正討論，但請勿拍磚，很痛的。

本人不是高手，只是一個用Excel多年的大表哥，充其量只是掌握了20%功能的半桶水而已，套用《「偷懶」技術：打造財務Excel達人》合著者錢勇在後記中的一句話「本人一直在努力地抬頭仰望，卻發現視線所及之處，才僅僅是Excel的半山腰「。

正文

怎麼才算精通Excel？能編製多層嵌套非常複雜的公式，就是精通Excel嗎？能編寫一段VBA代碼，用代碼實現Excel沒有的功能，就是精通Excel嗎？

首先，我們先來看三張喝飲料的圖片，看看什麼人是Excel菜鳥，什麼人是普通的Excel用戶，什麼人是精通Excel的人。

然後，我們再來看二個例子，

第一個：

請統計下表中《「偷懶」的技術：打造財務Excel達人》銷售數量、金額的合計。

凡對Excel的脾氣有點了解的人都知道，用於計算的數字不能和文字混搭，否則會造成Excel計算的腦梗塞。但是當沒摸清Excel脾氣的新手們，在「沒有困難製造困難也要上」提出這樣的問題時，一些Excel高手不是指導規勸新手們從規範做起，而是列出諸如下面的公式，我們能說這些高手們是真的高手嗎？

=SUMPRODUCT(SUBSTITUTE(D4:D8,"元","")*1)

第二個：

又比如，表哥龍逸凡要對銷售員的銷售情況做一個登記台賬，他做出的銷售台賬如下圖：

由於表格設計不規範，如果要統計某產品的銷售數量銷售金額，不能使用SUMIF函數，龍逸凡編製的求和公式是複雜的數組公式，如下：

=SUM((LOOKUP(ROW(4:22),ROW(4:22)/(A4:A22&>0),A4:A22)=K3)*D4:F22)

此公式很複雜，思路也很巧妙，但是，我們能說龍逸凡精通Excel嗎？

如果將上面的表格做成規範的清單式表格，那麼用一個簡單的條件求和函數SUMIF就能統計出來：

從上面的二個例子出發，談談我對「怎麼才算精通Excel」的理解。

竊以為，我們至少要做到以下四點，才算得上精通Excel：

規範。要有規範的數據處理理念和操作；
廣度。要全面掌握Excel的常用功能；
深度，深入研究過各種常用功能，對常用功能會拓展應用。
靈活，對已掌握的知識能融會貫通，會靈活運用。

下面分別說明。

一、規範：

要輕鬆駕馭Excel，把Excel發揮到極致，首先要做到規範。

不要以為Excel是任我們擺弄的玩偶，殊不知，它也是有脾氣的。什麼數據用什麼格式、什麼功能用什麼表格布局，都要順著它的性子，以從根源上簡化數據的處理。只有讓Excel順心了，它才能充分發揮其洪荒之力為我們所用。如果沒有正確的數據處理理念、做不到表格規範、數據規範，而是完全靠霸王硬上弓的粗暴技術編製高超精巧的公式來完成相關運算，其實是一種基本功不紮實的表現。

真正精通Excel的高手懂得「上醫治未病，中醫治欲病，下醫治已病」的道理。只有將「規範」的理念貫穿始終，才能把表格玩得行雲流水天馬行空。障礙應消滅於萌芽之處，而不是任由其變異膨脹再想辦法消滅。

孫子曰：「古之所以謂善戰者，無智名，無勇功，勝於易勝也「。Excel，道理亦然。

二、廣度：

Excel博大精深，不能玩轉個十七八般武藝，好意思說自己是高手嗎？咱們來自我評估一下吧：

基本功：

不但精通應用各種高級篩選、分類匯總、合併計算、數據透視表等常規的基本功能，對那些不怎麼常用的功能如單變數求解、模擬運算表及分析工具庫等載入項也能熟練應用。

函數技：

Excel中400多個函數除了那些專業函數沒用過，其他的函數都能信手拈來。別說普通的函數嵌套組合應用，那怕是讓普通用戶感覺燒腦無比的複雜數組公式也能一次性成功編寫出來。

圖表訣：

不但熟練掌握常用的柱形圖、折線圖，餅圖，能將這些圖表的各種元素玩出各種花樣，組合出新穎而又貼切的圖表，還能使用熟練應用不太常用的散點圖、曲面圖等，並能利用散點圖作為輔助工具，繪製出複雜的圖表。

VBA：

不但精通常見的VBA屬性對象方法，還能熟練應用SQL、字典、ADO、API、類模塊。什麼？VBA落伍了？沒什麼，VSTO也已熟練掌握。

精通Excel的高手，知識面一定是廣博的。遇到問題，能馬上給出解決方法。精通Excel的高手，既要知道條條大路通羅馬，更要知道在什麼背景下選哪條路才更高效便捷。

三、深度

做為精通Excel的高手，Excel知識掌握得既精又深才是應有的標準。Excel很多看似「膚淺」的功能，其實都蘊藏著更為深邃的技法。

比如查找替換功能，只要使用過Excel的人，都會認為自己會使用此功能，但實際上，查找功能的一些選項或功能很多人都沒用過，比如查找結果能排序。作為Excel高手應該能掌握常見功能的方方面面，並能將其放揮出別樣的用途。

案例：下圖的表格，工作表已保護，請批量清除C2:H23單元格區域的數字，而保留有灰色底色單元格的公式

由於表格已保護，無法使用定位功能，這時我們可以使用查找功能的通配符及查找結果排序功能，間接實現定位的定位常量、數字、公式等功能。利用查找功能批量選定常量（數字）單元格，從而批量將其清除。由於知乎不能傳動圖，具體操作演示，參見網易雲課堂免費課程《查找替換居然還能這樣用》

Excel偷懶的技術：查找替換居然還能這樣用

做為精通Excel的高手，對函數來講，要深入掌握各類函數的知識點，要知其然也知其所以然。比如深入理解SUMIF第三參數的定位原理，LOOKUP的查找原理。

SUMIF第三參數的定位原理：

SUMIF函數第三參數單元格區域起作用的就是左上角那個單元格，此單元格的作用是定位定點，只要有此定位點，SUMIF會自動以此單元格為原點，按照第一參數區域符合條件的單元格的坐標，找到同樣坐標位置的單元格，並對其數值求和。

我們可以利用此特點，完成看似難以完成的任務

SUMIF函數應用案例1：錯行求和

SUMIF函數應用案例2：錯行求和

SUMIF函數應用案例3：錯列求和

LOOKUP函數查找原理

LOOKUP函數是運用二分法，在已經升序排列的數組裡查找目標值，如果數組的中間位置的值且小於等於查找值，它會繼續按二分法進行查找，直到查找到相等的數或查找完「按規則應該查找的位置」（不是查找完所有的值），如果還找不到相等的數，就返回結束查找前最近一個符合條件的值。如果有多個符合條件，則返回最後一個符合條件的值。

關於LOOKUP查找原理與方法，就不舉例說明了，大家可參見龍逸凡的博文《深入理解LOOKUP：LOOKUP函數的查找原理》查看詳細解釋及實例，鏈接：

深入理解LOOKUP：LOOKUP函數的查找原理

案例：又比如，要統計下表某片區1－6月的銷售額，該用什麼公式呢？

網上的文章給的幾個公式是這樣的：

高手公式：

=SUMPRODUCT(SUMIF(OFFSET(A2:A9,,ROW(2:7)*0),A12,OFFSET(A2:A9,,ROW(2:7))))

高高手的公式

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C1:H1,ROW(1:8),))*(A2:A9=A12))

高高高手的公式

=SUMPRODUCT(MMULT(C2:H9,ROW(2:7)^0)*(A2:A9=A12))

實際上，用不著這麼複雜，只需一個SUMPRODUCT函數就可解決：

=SUMPRODUCT((A2:A9=A12)*C2:H9)

四、靈活

精通Excel的人，會靈活運用常規的功能、函數，能將常規功能發揮出別樣的用途，能將普通的函數進行組合出令人拍案叫絕的用法。

案例1：比如上文中的運用查找功能實現定位功能，

案例2：使用「填充－兩端對齊」和定位功能提取字元串中的數字

解題思路：先將A列寬度設為小於一個字元，然後選定A列，「填充－兩端對齊」，分離出A列的數字和漢字，再用定位功能刪除非數字字元的單元格，即可提取出各單格中的數字。

案例3：不使用函數，在練習題027工作表的B3:C9單元格引用sheet1表格中各人員的實際金額，要求批量操作。

本題實際上是間行引用，故不能直接下拉填充，習慣了函數公式的高手，習慣性的會使用OFFSET、INDEX等函數，實際上這道題，只需使用簡單的功能就可實現：

方法一：

在本工作表B3，B4單元格輸入字元串Sheet1!B3、Sheet1!B5，C列類同，然後下拉填充，再用查找替換將Sheet替換為＝Sheet

方法二：

在B3輸入公式=Sheet1!B3、B5輸入公式=Sheet1!B5，C列類同，下拉填充，然後定位－空值，刪除空白單元格，下面單元格上移，

方法三：

篩選，複製－選擇性粘貼（鏈接），要操作二次。

案例4：要對下圖的各部門進行小計求和，

一般的用戶只會一個部門一個部門的設置求和區域，實際上如果靈活應用sum公式，選定E2：15單元格，然後輸入下面的公式也可以批量求和。

=SUM(D2:$D$15)-SUM(E3:$E$15)

這個公式巧妙的應用了「借用」的思想－借用了後面單元格的計算結果。如果只是錄入錄入單元格，那公式計算結果是錯的。此思路非常巧妙！

案例5：批量向下求和的公式

我們知道，要批量輸入向上求和的公式，只要先使用F5快捷鍵定位空白單元格，然後Alt+=就可批量輸入向上批量求和的公式，但要批量向下求和似乎是個難以完成的任務。實際上我們使用常見的subtotal和sum函數，結合「借用」的思維，巧妙的實現了向下求和。

對真正的劍道高手來說，飛花摘葉皆可傷人，草木竹石均可為劍，甚至可以妙滲造化，劍在心中，無劍無我，無跡可尋，無堅不摧！

對真正的Excel高手來說，Excel問題的解決方案，俯拾皆是，順手拈來。手中無招，心中卻有招，無往不至！無敵天下！

註：

由於知乎無法上傳附件，需要本文章示例文件的朋友，可以加入到QQ群478304311下載。

千萬千萬別看排名第一的答案，看他的答案只會讓你再也不想見到EXCEL。

要學好 EXCEL 需要且僅需要一項能力：

把自己遇到的問題轉化為普遍問題去百度。

解決多幾次，什麼 Vlookup、Index Match、Sumif等常用函數就會用了。

能夠實現多表格樣式統一且互相鏈接，邏輯清晰，版面乾淨整潔能夠運用vba達到想要的效果
熟練使用，sumifs, countifs, vlookup, iferror, isblank, if嵌套，subtotal, indirect, find等常用函數
能夠運用table, pivot table, power pivot table處理大型數據
能夠使用condition formatting,data validation, text to column等
能夠出waterfall等圖表，會做interactive chart
熟悉各種快捷鍵，比如f4, 格式刷等ctrl+上下左右等，ctrl+f替代簡直是神器，尤其是在替換公式內部的字

精通Excel不敢說，因為我覺得Excel作為一個多面手，

在職場的很多領域都有高頻應用，綜合來講：

Excel函數與數組公式；

Excel圖表；

Excel數據透視表；

Excel數據分析工具；

ExcelVBA；

然後綜合一點兒就是維度建模與財務模型，儀錶盤與決策駕駛艙。

---------------

以上所列，答主都有所涉獵，但是很遺憾，大多都是粗淺理解，但是數據可視化方面倒是小有建樹，以下是自己以前練習和工作做過的一些東西，分享給大家：

【個人公眾號】：數據小魔方（datamofang）

【個人博客】：www.raindu.com

【知乎專欄】：R語言數據分析與可視化

---------------------------------------------------------------------------------------------------------------------

Excel的數據可視化功能非常強大，到底有多強大呢，好吧廢話不多說，直接上圖：

這是以前練習的時候做過的案例圖：

後來學的有些心得了，就開始自己找案例做

案例|全球創新國家1000強研發投入變動趨勢

圖表案例——一個小小的圖表所折射出的作圖哲學

圖表案例——雲計算背景下國際科技巨頭的戰略轉型

充分發揮想像力，Excel也可以成為黑科技！

Excel史上最好看的堆積柱形圖（以下連續三圖）

圖表案例——全球主流社交平台「網紅」收入統計分析

既能高大上、又能小清新，如何用excel來拯救你的圖表顏值！

交叉柱形圖

蛇形圖

後來感覺只用Excel有些不爽，就用Excel+PPT來作圖：

（未公開案例）

桌面文件：

未公開案例（練習信息圖之用）

桌面源文件：

未公開案例（練習信息圖之用）

桌面源文件預覽：

--------------------------

實習期間參與2017中國貿促會出口路線圖報告設計工作，實例在

國家報告（貿促會出口路線圖網站）

官網截圖：

承辦單位：

-----------------

實習了時候，老闆非要做幾個裝逼的，我說做不出來……最後還是勉強做了兩個：

（實習期間參與2017貿易大數據報告內圖表，工作內容，本人無源文件）

出處：國家信息中心：「一帶一路」貿易合作大數據報告2017

報告封面：《「一帶一路」貿易合作大數據報告2017》

-------------------------

知乎動圖效果不好，我的動態儀錶盤就不發了！

--------------------

原文出處：微信公眾號（數據小魔方），喜歡商務圖表與信息圖的小夥伴兒可以關注跟著一起學哦！

=====

Excel依然是一款強大的數據可視化利器~

學習R語言我都做了那些有趣的事情！！！

精通，是一個很嚇人的概念。

從字面上去理解，精是高度和深度，通是寬度和結合度，它涵蓋的並不只是一個平面，而是一個在3D立體世界裡馳騁縱橫的概念。

就Excel而言，要達到精通的層次，我以為這人一定是一個數學家（否則你不會使用高等數學類型的函數，下同）、統計學家、經濟學家、財務學家、美術家……對了，還必須是一個具有極強邏輯思維的全方位牛人。

為什麼？

因為Excel是一個數據領域包羅萬象的工具，其本身，並不算是一個單純的學科。除了一些通用的功能（比如排序、查找替換、篩選、定位……）外，它還為不同行業的人提供了專門的武器（從函數的類別就可以看出來）。

打個不太準確的比喻，Excel實際上是一部百科全書，而並不是「百科」里一個具體的「科」，百科全書的編撰，一般是彙集了眾多領域的專家，發揮各自特長創作出來的工具。而這些專家可能是精通天文，精通地理，精通歷史……卻幾乎沒聽說過誰是精通百科全書。對Excel的掌控達到其所有功能20%，也許在大多數人眼裡都可以算是「高手」了。

所以，如果標準嚴格點，我認為幾乎是無法達到精通Excel這個境界的。

降低點標準，能在自己的工作領域內駕馭Excel，就算差不多了。具體說來，我認為需要滿足以下幾點：

1、熟悉Excel的習性，有數據管理和表格設計的規範意識。此乃奠定高效使用Excel的基礎，稱其為Excel製表基因一點不為過（在本人參與創作的《偷懶的技術：打造Excel財務達人》一書中，對此極為重視）。

2、掌握通用的功能（比如排序、查找替換、篩選、定位、插入圖表等等）。

3、掌握足夠多的快捷操作方式。

4、掌握在自己涉足的領域裡常用的功能、函數以及VBA編程。

5、能將掌握的功能、函數、程序代碼融會貫通結合，並憑藉出色的邏輯思考能力，對其加以綜合的應用。

最後引用下本人在《偷懶的技術：打造Excel財務達人》後記中的一段話來總結下我對Excel的體會吧：

理解Excel，並不意味著就是對函數或者功能的死記硬背或者是對生僻功能的深入鑽研。只要打好了規範的基礎，其實Excel也是平易近人的，只是它需要靠用戶的想像力和主觀能動性去成就它的精彩。

精通太誇張，我自評最多也就算「能熟練使用Excel的常用技能而已。

PowerBI及系列插件，一定是近幾年Excel最偉大的發明，用excel就能處理千萬，甚至上億級別的數據，還在等什麼，快看看，我整理的近兩年國內的相關介紹文章吧，EXCEL的升級你跟不上你就OUT了

首先為什麼要學習自助式BI，我們看看高飛老師在PowerBI極客中給出大家的答案：

自助式BI，全面提升你的數據分析能力 - 知乎專欄

前言：PowerBI簡單介紹：

Power BI 是基於雲的商業數據分析和共享工具，它能將複雜的數據轉化成最簡潔的視圖。通過它，你可以快速創建豐富的可視化互動式報告，即使在外也能用手機端 APP 隨時查看。甚至檢測公司各項業務的運行狀況，只需它儀錶板的一個界面就夠了。一切，就是這麼簡單、快捷和靈活。

不管你是哪種行業，什麼職能，何種角色，只要在使用數據，Power BI 總能為你提供幫助。

《PBI重新介紹：Power BI全面簡體中文化》

《PowerBI相關組件下載安裝（附操作截圖）：-雷公子個人博客》

在開始之前我們說說學習PowerBI包括的三個核心內容吧，當然這三個核心內容在Excel中都有對應的商務智能插件，他們分別是：PowerQuery,PowerPivot,以及可視化。我們先扒一扒第一部分吧：

第一章節：PowerQuery篇

如果你還不清楚PowerQuery是什麼，能幹什麼，強烈建議您讀下由Excel120博主宗萌老師整理的文章：

《什麼是Power Query – 簡單得不像超級查詢實力派》

關於介紹的文章，我們也可以參考如下文章：

《Microsoft Power Query for Excel 簡介》

PowerQuery最新功能GET

《Power Query新特性：你給答案，我來計算 - 知乎專欄》

相信讀完這篇文章後，你肯定會對powerquery有一個比較全面的了解及認識，那麼powerquery到底能做什麼呢，這裡再給大家一些實例：

《用PowerQuery動態匯總文件夾下的多個Excel文件（支持動態增刪自動更新）-雷公子個人博客》

《數據逆透視-多維數據轉回一維 | Excel120》

《PQ：一個快速創建整數序列及字元序列的方法 | Excel120》

《PowerQuery處理典型的中國式二維表格轉一維》

《(視頻)URL分析之參數批量快速提取之Url.Parts函數 - 知乎專欄》

《利用Power Query進行採購發票校驗》

《PowerQuery語言M函數中文翻譯文檔面世 - 知乎專欄》

《查看PowerQuery函數的幾種方法》

PowerQuery技巧類文章：

《同一Excel文檔中多個智能表格如何批量添加到PowerQuery - 知乎專欄》

《PowerQuery實現動態查詢（與文件的路徑無關） | Excel120》

《PowerQuery技巧篇--詞根串聯（生成笛卡爾積數列） - 知乎專欄》

《PowerQuery的參數表格用法》

PowerQuery進階的一些知識：

《PQ：多行屬性合併到一個單元格 | Excel120》

《PowerQuery分表函數簡單應用Table.Partition(分表) - 知乎專欄》

《PowerQuery作為ETL系統 - 自助裝載大數據（amp;amp;>100W）最佳實踐》

第二章節：PowerPivot數據建模分析篇

學習PowerPivot離不開DAX函數的學習，在學習PowerPivot之前，我們先看看高飛在公眾號《PowerBI極客》里對DAX的介紹：

《認識DAX數據分析語言 - 知乎專欄》

DAX函數中重中之重的一個函數就是CALCULATE函數，我們先整理幾篇CALCULATE函數的介紹大家了解下：

《Power Pivot聚合函數與Calculate函數詳解 - 知乎專欄》

《PowerBI，PowerPivot中篩選函數CALCULATE的介紹 - 知乎專欄》

《DAX-CALCULATE工作原理 - 知乎專欄》

DAX其他函數介紹

《PowerBI學習第二階段函數 Divide/If/Switch/Related/Lookupvalue - 知乎專欄》

《Power BI中的ALL家族函數 - 知乎專欄》

《PowerPivot中的文本函數 - FORMAT函數(附其他日期函數)》

DAX函數高級用法

《高級DAX：USERELATIONSHIP | Excel120》

《Power Pivot 中如何執行 lookup+find操作（數據分析操作篇）》

《Power BI 多指標切片展現 - 知乎專欄》

《運用參數表提升Powerpivot實戰技能》

《如何使用DAX精確計算年齡 - 知乎專欄》

第三章節：數據可視化展現篇

《亮瞎雙眼的Power BI自定義可視化圖表 - 知乎專欄》

《數據可視化字典 - 知乎專欄》

《當Power BI遇上歐洲杯-案例-北京敏捷艾科數據技術有限公司-Power BI|Power Pivot|PowerPivot工坊》

《運營分析儀錶板-案例-北京敏捷艾科數據技術有限公司-Power BI|Power Pivot|PowerPivot工坊》

《當Power BI遇上恐怖主義-案例-北京敏捷艾科數據技術有限公司-Power BI|Power Pivot|PowerPivot工坊》

第四章節：PowerBI+行業篇

《「大數據+HR」不再是忽悠 | Excel120》

《用Power BI獲取實時股票數據並快速選股》

《案例-北京敏捷艾科數據技術有限公司-Power BI|Power Pivot|PowerPivot工坊》

《用Power BI觀察經濟與健康的關係》

第五章節：其他資源分享篇

1、PowerBI國內學習網站：

Excel120.com（宗萌老師個人博客）

pbihome.net（國內唯一PowerBI交流論壇，論壇剛起步，內容較少，但是相關這方面相關大咖均已入駐，大家學習過程中遇到什麼問題都可以發帖交流）

PowerQuery - 簡書

Power BI - 知乎專欄

Power BI 專欄 - 知乎專欄

2、PowerBI國外學習網站：

SQLBI

PowerPivotPro - Transforming your Business with Power Pivot and Power BI

Power Query Archives – The BIccountant

Chris Webbamp;amp;#x27;s BI Blog

3、PowerBI公眾號推薦：

《Powerpivot工坊》

《PowerBI極客》

《PowerBI大師》

《Excel120》

4、PowerBI學習線路圖推薦：《PBI系列學習框架地圖 | Excel120》

如果你想下載閱讀這些電子書可以直達我們PowerBIhome論壇下載《點擊直達電子書下載》

5、國內課程推薦：

1）、PowerPivot工坊文超老師系列課程

2）、劉凱老師系列課程

3）、張文洲老師網易雲課堂系列課程

4）、搜索引擎營銷行業可以了解我個人課程，其他行業忽略（《Excel數據分析必備技能》覆蓋初/中/高級 | 艾奇學院）

6、PowerBI Excel交流群：

M與DAX的恩怨糾葛 545814382（偏PowerQuery交流）

Excel120B群 125836108

微軟Power BI技術交流群2 553499910

雷友會-Excel_PowerBI交流 198086726（專註PowerBI及系列插件交流）

最新更新，歡迎大家訪問Powerbi.cc，關注知乎專欄【Power BI】

註：以上所有文章版權均歸原作者所有，如因版權問題，可聯繫刪除。

個人認為，掌握Excel的以下幾個功能基本上可以算是精通Excel了，此外，系統地學習Excel可以參考以下十個步驟：

第一階段：熟悉Excel界面

簡單來說，就是把Excel上方工具欄中的那些按鈕都點開看一下，熟悉一些都有哪些最基本的功能，以及如何使用這些功能。這些都是最基本的點擊式操作，沒有任何難度。

第二階段：學習Functions

函數不需要所有的都學習，最基本的統計求和函數要會用，最基本的查找匹配函數要會用，接下來就是函數之間的嵌套應用，這些都掌握了之後，你再可以學習一下與自身業務相關的一些函數，這都沒有什麼難度，當你把這些函數都搞定了之後，再學習數組。掌握了數組，你就很了不起了。在這裡推薦一本關於Excel函數方面的書，書中介紹了最基本的75個函數，只要掌握基本用法，後面的靈活運用就靠自己了，書名為：75個Excel函數搞定一切。

第三階段：基本的Graphs

Excel幾乎可以繪製各種各樣你所看到的圖，而且十分方便，只要你掌握這些繪圖的基本方法與原理，你同樣可以繪製出非常漂亮的圖表。當你掌握這些基本的圖表製作技巧之後，你就可以進行自我創新，創造出讓別人佩服的圖表。市面上關於Excel圖表的書籍很多，個人覺得最適合新手的是這本書：EXCEL 2016圖表達人（增強版）。

第四階段：數據透視表

當你對Excel界面十分熟悉，對函數和數組掌握到了一定程度，也對繪圖有了一定的造詣之後，你在實際工作中，可能需要提升效率，這個時候數據透視表就是最好的神器。它在好多方面都能夠大大提高你的工作效率，是Excel高手必備的技能之一。市面上很多講透視表的書都是直接上案例，而沒有對數據透視表進行最基本的講解，個人比較推薦的是：玩轉Excel數據透視表。

第五階段：錄製宏和VBA

當你想要處理重複的工作時，可以通過錄製宏，然後自定義快捷鍵來實現，當你要實現某個特殊的自定義功能時，這就需要學習VBA了，VBA的基本語法並不難，難在它是無底洞，永遠也研究不玩，就像一門武功，永遠也練不到盡頭。

第六階段：Powerquery

當你將傳統的Excel都掌握到了爐火純青的地步時，下一個你要學習的就是Powerquery，在Excel2016當中，它已經成為內置插件了，可以直接使用，它可以讓你的數據清理與處理工作變得非常的方便和快捷，起初，你還是需要了解一下它的基本界面，然後，你可能需要學習一門新的語言——M語言，目前國內會這門語言的人可能並不多。個人認為在這方面的書籍，推薦搞懂Power Query的第一本書。

第七階段：PowerPivot

當你掌握了Powerquery之後，你還需要繼續學習PowerPivot，PowerPivot就像它的名字一樣，是數據透視表的加強版，它又有點像資料庫，需要創建表之間的關係，有主鍵和外鍵，它在處理大量數據時比Excel具有更快的速度，目前也是國內商務智能領域的一些認識正在研究的內容，這裡推薦一本書：Power Pivot入門手冊。

第八階段：PowerBI

當你掌握了Powerquery和PowerPivot之後，學習PowerBI對你來說已經不是什麼問題了，它相當於Powerquery+PowerPivot+PowerView+PowerMap的組合，主要用於數據可視化，是微軟用來對抗Tableau的一款產品，目前在國內正逐步流行。

第九階段：靈活運用

當你掌握了上面的這一些基本之後，接下來的就是結合自己的需要，創造出比其他人更快、更好、更專業的東西來，就拿繪圖來說，你如何將上面所學到的東西綜合運用，打造出和別人不一樣的圖表來，就像近年來必究留下的Dashboard，這裡同樣推薦一本不錯的入門書籍：Excel 2016商業儀錶盤基礎。

第十階段：模仿學習

最後，這是本人第一次在知乎上認真寫答案，還寫了這麼多，希望大家多多支持，更多關於Excel、R、數據分析以及金融方面的問題，歡迎去本人博客查找資料：悟君居士 - Welcome to my blog

10年表哥一路用過來，不敢說精通。但是衡量你EXCEL水平很重要的一個標準是遇到一個問題你知道有哪些辦法可以解決而且能找出最優化的解決辦法。在數據量特別大的時候優化很重要。

又改啦……以前問的不是」怎樣或者如何「么……
去事務所找份處理數據的實習。
每天不僅不加班，而且可以在下班前完成當天處理數據的任務，就算是精通了。

關於大學生與office三大件：
PPT是社團和上課Presentation時搞定的，完事兒基本不用再苦練技術了。
Word是寫論文時突飛猛進的，完事兒需要再學學郵件合併、查找替換與許可權管理。
——唯有Excel，所有的提高都是在苦悶的數據工作中得到的。
提取數據、校驗數據、合併報表，太鍛煉人了。

我是一周君~~在跪著看完各路Excel大神們的詳細分析，實在佩服。

我認為：對於Excel，操作效率是否高效也是衡量精通的一個標準。

之前有小粉絲來諮詢，說自己是一個大四畢業狗，自己在公司會經常用到excel，然而學術不精，雖然大學裡學過皮毛，但是遠遠不能滿足工作上的需求，想請教一下能不能教大家一些工作中常用的小技巧。

其實這問題很簡單，想要高效處理數據，office軟體已經人性化的解決了我們很多問題。

別人還靠著滑鼠拖動鍵盤敲打勤奮賣力的複製再粘貼，而我們掌握了Excel中的小技巧後，從此讓你遠離加班的煩惱。

今天，開始分享這10個Excel常用技巧，看看你精不精通Excel~

【1】批量處理行高、列寬

單元格的距離參差不齊，有的單元格小到看不清數據，而有的卻太寬，這樣的單元格難免會影響美觀，我們要做的呢，就是把所有選中的單元格都處理為相等的間距。

1. 點擊行標或列標，選中需要統一行或者列區域

2. 滑鼠放在行標或列標之間的線上

3. 待滑鼠變化為黑色帶雙向箭頭時候拖拽行標或列標之間的線

【2】列寬自動適應內容

統一調整後的單元格，確實緩解了強迫症的壓力，但是有的單元格並沒有適應內容，那麼我們就需要進一步美化，把所有選中的單元格都處理為適應內容的寬度。

1. 在行標或列標上選中所有需要調整的行或者列

2. 滑鼠放在行標或列標之間的線上

3. 待滑鼠變化為黑色帶雙向箭頭時候雙擊一下滑鼠左鍵

【3】快速填充數值

工作期間難免會遇到老闆讓你提取單元格數據的問題，比如根據前面的身份證號，來提取身份證中間的生日以及身份證後四位數

1. 在第一行單元格中手動輸入身份證號中的出生日期

2. 滑鼠移動到本單元格右下角待滑鼠變成黑色十字架後雙擊一下

3. 點擊最下面的「自動填充選項」，選擇「快速填充」

【4】一次插入多行或多列

有的時候，我們可能需要在已有的數據區域中間插入多行或者多列，來讓我去添加更多的數據，大家都知道在行標和列表直接插入是只有一行或一列，那麼我們要插入多行或多列怎麼辦呢？

1. 在行標或列標中選擇多行或多列

2. 在選取所在的行標或列標處點擊右鍵，選擇插入

3. 在選取的左側或上面，會插入與選中行數或列數相同的區域

【5】快速移動選取數據

學會了插入空白的多行或多列，那要是我們要把選擇的數據區域插入到其他數據區域中間，要如何操作呢？

1. 選擇需要移動的數據區域

2. 滑鼠移動到選取邊框線上，使滑鼠箭頭變為黑色實心狀態

3. 按住shift鍵並點擊滑鼠左鍵拖拽，拖到正確位置後先鬆開滑鼠，然後在放開shift鍵

【6】快速定位邊緣單元格

在成百上千列的數據表中，想定位到最後一行單元格的時候，我們通常會滑動右側滾動條，或者使用滑鼠滾輪，但是，這都不是個最快捷的辦法

1. 選中數據區域任意一個單元格

2. 滑鼠放在單元格下邊框線上，使滑鼠箭頭變為實心狀態

3. 雙擊單元格下框線，可以直接跳轉到這列數據最後一行

（或者鍵盤可以使用快捷鍵，Ctrl+方向鍵）

【7】凍結窗格

有時候在眾多數據的數據表中，向下滾動下滑鼠滾輪，首行的標題行就會被滾動跑了，那麼我們應該鎖定首行或者首列，讓他們不再滾動

1. 視圖選項卡-凍結窗格-凍結首行或凍結首列

2. 如果需要同時凍結首行和首列，點擊數據區域左上角第一個可滾動的單元格

3. 選擇凍結窗格中的「凍結拆分窗格」，若取消凍結，點擊「取消凍結窗格」

【8】篩選以3和4開始的數字

老闆通常會讓我們去做許多篩選類型的數據，比如在身份證後四位中，篩選出來以3和4開頭的數字

1. 在數據-篩選-篩選搜索框中輸入「3*」後點擊確定

2. 再次打開搜索框輸入「4*」，勾選將當前所選內容添加到篩選器

3. 點擊確定後，我們所要篩選出來以3和4開頭是數字就展現出來了

【9】批量操作數字前加漢字

老闆讓列印工作表，還要去要把編號顯示在最前端，那些天天加班的人會選擇一個個去敲打複製粘貼，而那些高效率工作者卻是這樣做的

1. 選中數字單元格區域

2. 按鍵盤上的Ctrl+數字1打開單元格格式窗口

3. 選擇數字-自定義

4. 在類型中輸入代碼：「編號：」00

（注意，這裡的雙引號，必須使用英文狀態下的引號）

【10】查找重複值

工作中難免會要篩選一下重複的數據，然而在眾多數據中用肉眼去一點一點的看，是相當費力的，同時還容易出錯，那麼我們應該如何便捷的查找數據列表裡邊重複的單元格呢？

1. 選取查找數據的區域

2. 點擊開始-條件格式-突出顯示單元格規則-重複值

3. 選擇重複-淺紅色填充重複的單元格

Excel入門小技巧其實並不難，由淺入深的學習，明確自己的目標，能夠解決自己的需求就足夠了。

什麼什麼？你還想問我怎樣get更多技能？那就快來關注我嘛

-----------------------------------------

輸入

什麼是輸入呢？就是我們往Excel里輸入數據的過程，包括把那些系統導出的數據，通過分列、left/Find函數，處理成我們期望格式的過程，也叫輸入。

輸入過程又可以細分成下面幾個部分：

純手動錄入

純手動錄入，就是我們不得不一個一個敲進去的數據，像姓名、地址等等。這個輸入過程，看似沒有什麼技術含量，就是拼手速的一個過程嘛。

但是你知道這個沒有技術含量的過程，有多少問題困擾著我們嗎？

身份證號碼總是出錯
序號前面如何補0
如何添加下拉菜單
合併單元格填充序號
如何輸入對號、錯誤符號

這些反覆被問到的問題，只不過是我們做表格的第1步：輸入。

外部導入數據

系統導出的CSV文件如何導入到表格里？文本文檔的數據，如何導入的表格中？如何把網頁中的數據導入的表格中？

這些是導入外部數據問題的範疇。

數據格式轉換

這個就更常見了。

文本類的問題。如何把中英文分開，如何把文本轉成數字，數字轉成金額，數字轉文本等等。

日期類問題。如何計算周別？如何把日期、時間分開？如何計算工作日？使用符號連接日期怎麼是錯的？

退後一步去看這些知識點，你會發現這些知識我們工作中最基本的一步，把數據正確的輸入到表格中。

但是，這個過程是不產生利益的，老闆不會因為你花了很長的時間敲數據，來給你漲工資的。

咱們再來看看Excel學習的第2個部分：數據分析統計

數據分析統計

數據分析統計，就是在海量的數據裡面，找到我們想要的信息，或者把這些信息聚集在一起，方便我們查看。

這個過程，大致可以分為：

多表整合

什麼意思呢？很多時候，我們的數據分布在不同的工作表中，無論是篩選、排序、查找，這些操作的一個前提是，數據都要在同一個sheet中。

所以把多個工作表、工作簿的數據整合在一起，是數據分析統計的一個常見環節。

數據查看、查詢

數據輸入完成、整理好之後，第一個基本需求就是：看。

單位元定位類。比如在人事表裡，要找到「拉小登」的詳細信息，就要定位到這個單元格。常用的CTRL+F，篩選，ctrl+方向鍵等等，都是這個範疇的。

查詢類。這個查詢和單元格定位不太一樣。比如現在我們有一個班的姓名列表，要在全校人事檔案里，把這些人的信息快速找出來。最常用的方法就是VLOOKUP。
類似的需求還有，查找最後一條記錄，查找最大值、最小值記錄等等。

數據聚合類。按照相同部門，把信息聚集在一起，可以使用篩選。還有的需求，希望把相同部門信息的人名，放在同一個單元格里等等。這也是聚合類需求。

高亮顯示。把我們想看的信息，都用黃色標記出來。用的最多的是條件格式。你都會多少？

數據對比

兩個工作表，對比出差異，找出不同的信息。

數據統計

財務、電商部門的同學，是這一塊業務需求最大的人群。

根據條件求和。跨行、跨列求和。按條件統計個數。統計產品銷量排名等等。

梳理到這裡，估計這些需求，差不多可以佔據你Excel知識儲備的70%了吧。

但是你要知道，老闆關注的不是這些細節。他們希望看到的東西，越簡單越好。天貓是如何表現雙十一業績的？

看到了嗎？就用上面一個數字，傻子都能看懂。如果你像這樣去做表格，能不能升職加薪我不確定，但是老闆心裡肯定會記住：「哎，這小子的報表做成蠻不錯的啊」。

輸出

最後一個環節就是輸出了。把表單做的簡單、大方。為什麼客戶的表格那麼有商務范兒？那個儀錶盤的表做的真好看。

這些都是表格輸出的內容，我把它分成了下面幾個部分：

表格美化

表格美化。我在《漂亮的表格應該這麼做》里，簡單的總結了幾招，就可以讓原本刺稜稜的表格，帶上點商務范兒。除了基本的信息傳遞，漂亮的表格，會讓人感覺你很專業。

數據可視化

數據可視化。這是大部分學習者都忽略的一個部分。數據可視化不僅僅只是做個柱形圖那麼簡單，需要分析信息重點，對數據拆解分類，然後用一個連傻子都能看懂的可視化形式呈現出來。

你可以感受一下改善前後，兩個圖表的差異。

改善前

改善後

列印輸出

工作中很多表格需要列印出來填寫、簽字的，很多列印的技巧，也是需要掌握的。比如，添加頁眉頁腳，添加頁碼，列印尺寸調整等等。

當然了，如果你的表格設計的很有商務范，數據可視化也做的非常的直觀，列印出來的表格，肯定也會錦上添花。

怎麼樣，經過我這麼一梳理，你是不是對Excel的學習方向，更加明確了？表格的輸出設計，是討好老闆的必備技巧哦。

我把詳細的這個框架，整理到幕布上了，方便你隨時的翻看。

點擊下面鏈接，可以獲取更詳細的Excel知識框架內容。

https://mubu.com/doc/eGqZwD6gv

資源分享

我整理了幾個Excel學習資源，分享給大家。

免費

幾個我一直關注的Excel公眾號，搜索名稱關注即可。

基礎知識類

Excel不加班
Excel函數與公式
Excel精英培訓
Excel到PowerBI
秋葉PPT
Excelhome

數據可視化類

Excel卓越之道
Excel圖表之道
拉小登

《表格設計課》系列文章

表格設計課，是我講解表格設計理念的一個系列文章，目前寫了13期，表格設計課，專欄地址：表格設計課

付費

網易雲課堂

和秋葉一起Excel（￥169）
讓你的圖表動起來 - 動態圖表大全（￥360）

= = 推薦文章 = =

「表格設計課」第13期雙色圖的魅力和原理
史上最簡單Excel動態圖表，5秒輕鬆完成！
用Excel做出像微信報表一樣的動態圖表，你也可以！
漂亮的表格應該這麼做

還在耍快捷鍵的和VBA都是入門級別的，NB的人會意識到Excel在處理大數據時局限性，開始用另外一種語言寫計算量大的方程，並把結果編譯好做成Excel插件。而數據則儲存在專用的資料庫匯中,用SQL調入Excel。而Excel和VBA則純粹作為操作界面設計工具，這實際上也是Excel真正的強項。

我可以舉一個例子，你想想準備怎麼實現，大概就可以知道自己的水平了:
給你500支股票的過去15年每天的股價。現在要求比較過去任何一天任何兩支股票的日收益之差，和當天之前的N天相比的Z分數。（N由用戶設定，一般在7-360之間）。要求同時將所有可能的兩支股票的組合的結果同時展現出來（也就是說要在一個不是太大的屏幕上顯示一個500*500的表格），並且用戶在修改數後新的計算結果必須要立刻出現，延時不能高。文件不能超過5mb,否則打開太慢佔用內存太多。

然後做完了老闆跟你說幫我加個回測功能吧，當Z分數大於一定閾值時我們來買賣，閥值我自己看著設。有時候我也會想看看Z分數的時間序列。。。

這時你意識到你的需要處理的數據量是5000*500*500，光存下來就需要1.6GB內存。

大家可以試著拿vlookup或者VBA去做做，看看會不會累死。對了結果必須要顯示在Excel里，因為用戶喜歡拿著計算結果再去做二次處理。你要是做成一個.exe的文件人家開都不敢開。

精通問題描述，隨手會用百度

大四在學校做簡歷時，大家都會寫一句話，精通office系列辦公軟體，畢業後，工作一個月，默默的把簡歷改成，了解office辦公軟體的基本操作。

所謂精通都應該是指向性一般來說現實遇到的問題都能用其在較短時間內高效解決基本就可以算精通了
我覺得有一個尺度很好衡量
上這裡http://club.excelhome.net/forum-2-1.html
一眼掃過去覺得大部分問題都可以手到擒來基本上就可以算精通了
掃過去拿不準的
可以試試去回答100個問題有50的回答被答主採納或者部分採納基本也就算精通了
本人在EH賬號是百度不到去谷歌