導讀作為一名接觸Excel有7年歷史的運營94年大叔，建議日常的大多數人只需快速系統了解Excel，以后遇到問題再針對性搜索或學習即可。E00.Excel系統知....

作為一名接觸Excel有7年歷史的運營94年大叔，建議日常的大多數人只需快速系統了解Excel，以后遇到問題再針對性搜索或學習即可。

E00.Excel系統知識大綱

本文內容較長，干貨不斷，建議小伙伴們點贊收藏后，供日后學習使用~

根據了Excel2016專家認證的考試大綱，結合自己的工作使用，就給大家從管理工作簿、自定義數據格式與驗證、Excel函數、Excel圖表、數據透視表等5大部分來一一論述。

E01.管理工作簿

這部分的內容主要是接觸整體的菜單欄、功能區的熟悉、讓我們對Excel整體框架有大概的了解以及如何保護工作簿不被別人修改等等。

E01-1.新建功能區藏著不少模板

【文件】→【新建】就有許多同學不知道的Excel模板，這些模板美觀 實用，絕對是初學者最好用的模板。

數據透視表中實用的圖文教程。

詳細清晰的圖文教程

2.日常中考勤用到的模板

計劃甘特圖

家庭貸款計算器

家庭預算表

商品庫存表

E01-3.日常處理工作簿的小技巧

1.設置快速訪問工具欄

設置保護密碼的表格

取消保護的工作表

還有更多工作簿處理小技巧就不一一論述，詳細可以看教程的視頻操作，更容易上手啦。

E02.自定義數據格式和驗證

單元格是工作表最基本的單位，單元格的格式設置尤為重要，我們主要了解到文本、數值、日期等格式的區別，了解通配符#、*號等應用就OK了。

E02-1.單元格式設置

通過選中單元格右擊，或者利用快捷鍵ctrl 1就能調出【單元格格式設置】，日常用的較多單元格格式設置主要為，日期、文本、數值、貨幣等幾種形式。

利用自定義格式還能設置出別的形式，例如在數值帶單位、重復文本等，這方面內容只需了解即可。作為基礎無需深入研究。

E02-2.多行多列的轉換

我們經常要遇到一些亂糟糟的數據要整理，多行多列轉換經常要用到滴。

雜亂無序的多行多列轉換

↓↓↓

E02-3.常用Excel快捷鍵

能熟練應用一些常用快捷鍵能極大幫助我們提高工作效率，不少小伙伴由于記快捷鍵太難而放棄使用快捷鍵，其實整理好規律記快捷鍵就能極大提高效率。

我整理日常自己用得比價多的快捷鍵，分別是從A到Z，0到9的運用，幫助大家能記住這些快鍵鍵。

E02-4.條件格式

通過常規的條件格式設置，我們可標記出重點的數據，突出顯示某部分的數據。

01.突出顯示單元格規則

點擊【開始】→【條件格式】即可選擇相關的規則，突出顯示相關的單元格。

1.查找重復值，查找班里不及格的人等等

02.最前最后的規則

利用前N項或者后N項，通過突出顯示快速找到對應的數據。

例如我們可以顯示數學成績高于平均分的同學、顯示各位同學最低的一門科目成績、班里成績最好的3位同學

03.圖標集

許多小伙伴不知道這些小圖標是來自條件格式，導致了誤以為是圖表上的。

優點：其實這些精致的小圖標設置很簡單，通過默認的設置就能得出來。 缺點：就是精度不高，只能表示大概。圖標集分為方向、形狀、標記、等級四個歸類，根據歸類我們日常主要分別應用在

方向：表示漲↑跌↓，一般用于數據對比

形狀：一般用于分類劃分的信號

標記：用于標記狀態，某件事做了與否，達成目標與否

等級：表示占比劃分

E02-5.批量重命名文件和新建文件夾

日常我們經常遇到要批量把某文件重新按照一定規律，通過bat批處理就能快速解決該問題。

1.提取當前文件夾文件名。

我們只需要把下面的代碼復制到一個新建的記事本中，然后把記事本的文件類型.txt改為.bat即可用提取當前文件夾的所有文件名。

@echo offfor /f "delims=" %%a in (dir /b/a-d/oN *.*) do echo %%a >>當前文件夾內文件名.txt

2.批量新建文件夾。

新建文件夾的代碼如下，把他們復制到bat文件里面，雙擊即可運行。

MD 北京MD 天津MD 河北MD 山西MD 內蒙古MD 遼寧

3.批量改名字。

批量改名往往結合1中的提取文件名使用，如果涉及處理數據我們可以使用Excel出來好數據然后把數據復制到txt文檔，最后修改文件類型為.bat，雙擊bat文件運行。

ren 1月.png 1月.jpgren 2月.png 2月.jpg

注意：以上批處理均是在.bat文件中執行的，有些安全防護軟件會阻止bat批處理的執行，直接退出或在彈窗時允許本次執行即可。

E02-6.設置主題顏色

日常我們可以看到許多大神能做出許多不同配色的方案，其實這種通過主題色設置即可，主題是顏色、字體和形狀三種的組合。

00配色好比穿搭

今天跟大家講一下配色，把PPT配色比喻女生穿穿搭，那么Excel就是男生的穿搭，所以Excel的風格更適合黑白灰三種保險色，其他突出的就用同一種色系點綴突出。

01.總的來說，PPT的配色可以豐富多變，但再漂亮的妹紙也要遵循一定的穿搭審美滴。

02.Excel較多為數據，最好用黑白灰三色，在圖表上的配色可以豐富點。

數據表格采用黑白灰三色

圖表可以豐富一點

或者再豐富一點

03.掌握原理之后更重要是如何執行，接下來通過三招，帶你成為圖表穿搭高手。

01白銀級配色

01.選中表格數據源，點擊【開始】【套用表格樣式】，選擇你喜歡的一個表格樣式就能直接套用。

02.例如這就是選中第一個的樣式的效果

02黃金級配色

01.不同色系表示不同數據系列懂得選擇同一飽和度數的顏色，在填充顏色的【主題顏色】，同一行的顏色代表顏色飽和度是相同的。所以我們選擇區分不同系列選擇顏色，飽和度相同的不同色系。

02.飽和度不同表示程度深淺

我們在配色的相同的列，就能選擇不同的飽和度的顏色，代表同一事物的深淺。

03白金級配色

01.懂得利用顏色主題的系統搭配顏色

點開【頁面布局】【主題顏色】就能變換多種主題顏色配置。系統原來就為我們搭配了許多配色方案，相對默認的配色。這些配色色系上還是不錯的。

注意：配色的核心是使用“主題色”，而不是采用標準色，標準色是你換任何配色方案都不會有變化的，當然自定義的顏色也跟標準色一樣，不會隨主題的改變而改變。

04鉆石級配色

01.自定義主題顏色設置

點開【頁面布局】【主題顏色】拉到最后的【自定義顏色】就能自己設置顏色了。

許多同學不會自己配色的，那么最快捷的方法就是參考別人優秀的配色方案

05尋找配色方案

01.參考專業的圖表網站：如echart

網站地址：https://www.echartsjs.com/zh/index.html

02.設計配色網站：安利這個PPT導航的網站給大家。網址：http://www.hippter.com/

c.可視化平臺等：如網易數讀

04.商業雜志配色

06配色展示

01.參考《經濟學人》雜志的配色效果

02.參考《中國色》配色網站效果

03.參考數據大屏的配色效果

E03.常用Excel函數

Excel函數是最考驗邏輯的功能，也是提極大提高我們工作效率一環。Excel函數多達472個，其實我們只要掌握核心的4個函數就OK了。他們分別是vlookup、IF，sumifs，countifs四個函數。

E03-1.IF函數

=IF(logical_test,value_if_true,value_if_false)

=IF(判斷條件,條件成立返回這里,條件不成立反饋這里)

①單個if函數使用

=IF(C2>6,"帥","一般")

式子里填寫中文需要用“”引住。

②多重if嵌套應用↓

=IF(K2>=85,"優",IF(K2>=70,"良",IF(K2>=60,"中","差")))

其實某部分多層IF嵌套，用vlookup可以更完美的解決，下面會跟大家降到vlookup的具體用法。

=VLOOKUP(K2,{0,"差";60,"中";70,"良";80,"優"},2,TRUE)

③IF/and/OR函數嵌套使用

---

E03-2.vlookup函數

表達式：=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

=VLOOKUP(查找值,查找區域,返回列數,匹配類型)

①正向查找

例子：要找班上一名叫喜洋洋的數學成績

=VLOOKUP(“喜洋洋”,“成績表區域”,返回列數,0)

正向查找，我們可以通過改變引用的方式，匹配出多個相對應的結果值。

延伸類型

①逆向查找

表達式：=vlookup(查找值，IF（{1,0}，查找值所在列，結果值所在列）,2,0）

逆向查找其實是構造出新的查找區域↑

例子：你有一個兒子叫哆啦A夢，你去到學校找兒子的，門衛要查找以為名叫哆啦A夢的學號，才能知道兒子在哪個班。

=VLOOKUP($M8,IF({1,0},B:B,A:A),2,0)

因為有數組輸入后，需要同時按下，ctrl shift enter

得：={VLOOKUP($M8,IF({1,0},B:B,A:A),2,0)}

延伸閱讀：

逆向查找在更多工作的時候，直接構造出輔助列，這樣可以沿用原來的正向查找的方法，所以我們要學會靈活多變。

②多重條件查找

查找值合并成1，構建新的查找區域↑

延伸閱讀

如果多重條件查找返回的結果是數值，可以通過sumifs函數來匹配出結果值。

如數學成績則表達為：

=SUMIFS(F:F,B:B,M14,C:C,N14)

更多的教程內容請看詳細教程《零基礎180分鐘快速入門Excel2016》

E04.可視化表達

許多小伙伴經常來問一些圖表是怎么做的，他們是分不清數據可視化的形式，有些小伙伴不知道條件格式、迷你圖、更不用說字體和函數表達滴。本人總結出數據可視化表達的常見幾種形式~

E04-2.常用的基礎圖表含義

我們日常工作中90%都是由基礎構成的，掌握這幾種基礎圖表的表達和理解，就能滿足使用圖表的90%的需求。

Excel常用的基礎圖表都在里面了

E04-3.動態圖表的原理及表達

動態圖表是Excel最吸引人的地方，不少人以為動態圖表只有數據透視表 切片器才能做，其實不是滴。本小章節帶領大家認清楚動態圖表的原理，以及各種動態圖表的基礎構成。

00動態圖表原理

因為Excel沒有時間軸，無法描述運動軌跡，只能表達圖表的最終形態。簡單來說動態圖表就是作圖數據源發生了變化。

在Excel中常見的動態圖表元素組合有：選擇器 抽數引擎 圖表=動態圖表

①選擇器常見有Excel控件，下拉菜單，單選框、復選框、調節數值器、切片器、日程表等；

②常見的抽數引擎有：透視表、常見的鏈接、引用函數如：vlookup，offset，index與match等

以下，我們根據選擇器的不同，盤點各類動態圖表：

01randbetween函數

由randbetween構造的數據源，我們刷新時，作圖數據源就能隨機變化，從而形成動態圖表。

大家看到我大多數的動態圖表，都是由randbetween函數構造的。

02下拉序列

我們通過【數據驗證】→【序列】就能構建出下拉序列，如圖：

通過下拉序列的單元格值，利用vlookup求出1-3月對應產品的銷量，利用圖表展示數據，通過拉下序列切換形成動態圖表。

03透視表 切片器

透視表和切片器是最好的分析組合，我大多數【數據看吧】作品都是利用透視表 切片器的。

作品示例：

04透視表 日程表

日程表對于日期分析非常好用，因為能自動根據日期、月、季度、年等多個日期組合。

---

Excel控件動態圖表

接下來的都是設計Excel控件的動態圖表，Excel控件是在【開發工具】→【插入】。

如果沒有【開發工具】菜單，記得在【文件】→【選項】→【自定義菜單】，把【開發工具】勾上即可。

因為Excel控件都是漂浮在單元格之上的控件，我們需要把控件的數值鏈接到單元格，所以這些控件都要設置鏈接單元格。05下拉菜單

控制關系鏈：下拉菜單→鏈接單元格→聯動作圖數據源=動態圖表

06復選框

控制關系鏈：復選框→鏈接單元格→聯動作圖數據源=動態圖表

復選框：多個選框控制多個單元格。

07單選框

控制關系鏈：下拉菜單→鏈接單元格→聯動作圖數據源=動態圖表

單選框：多個選框控制一個單元格。

08調節數值器

E05.數據透視表

終于要說到最后一個了，個人認為數據透視表在目前來說稱得上為Excel中最強大的功能，首先看看 圖片放松一下吧。

數據透視表的作用實在太多了，個人感覺再多的作用和功能都是圍繞著【數據分析】這個點，其中【快速匯總、統計】這個作用最強大與核心，之后的所有作用均圍繞著不同條件下統計匯總數據。例如按照【動態排名】、【分組】、【拆分】等都是統計的條件。

下面我就簡單過一些數據透視表有哪些功能吧↓

E01-.制作數據看板

制作數據看板是對數據透視表掌握的綜合考核，主要涉及到【切片器】【透視圖】【圖表應用】【統計匯總】等功能的。為什么最難放在前面，因為是為了想勾起你們的興趣，O(∩_∩)O哈哈~

E05-1.快速匯總數據

選中數據源，建立數據透視表后。分別拖動【城市】【月份】字段到【行】【列】區間，最后拖動【銷售價】到【值】區間，即可快速匯總統計數據。

E05-2.拆分工作表

日常你還在一個個滴拆分每個工作表分發給各子公司么，學會這個即可一鍵拆分工作表。把【城市】拖到【篩選區】，選擇數【據透視表工具】→【分析】→【選項】→【顯示篩選頁】選擇即可根據對應的每個城市拆分工作表。

E05-3.二維表轉一維表

依次按下Alt D P鍵盤（不能一起按），調出數據透視表向導。【多重合并計算】，【單頁字符】，選擇數據源，接下來就按確定即可。

建立數據透視表后，雙擊最后匯總項，從而得到一維的數據源。

E05-4.動態排序

在行標簽的數據項，【排序】【其他排序】，選擇根據【銷售額】降序從大到小排序，當數據源發生變化后，排序依舊根據銷售額大小排序。

E05-5.圖形圖表交互

這個就是一種小的交互圖表，多圖表組合就成為數據看板。

E05-6.數值分組統計

如果是數值或日期在行標簽列，可以根據組合匯總進行自動的分組。【分析】【創建組合】根據步長和起點值、終點值設置。

E05-7.返回特定數據源

只需要在透視表統計的數值雙擊，就能返回對應的數值。

E05-8.跨多表匯總統計

也是按住依次按下Alt D P鍵盤（不能一起按），調出數據透視表向導。【多重合并計算】，【自定義字節】，依次選擇【1月-6月】工作表的數據源分別添加，最后根據實際，選擇【3個字段】分別命名后，確定即可。

以上的作用是個人日常使用數據透視表用得比較多的地方，需要掌握更透徹，需要繼續學習更多知識。自己也可在日常工作中總結，改進解決問題流程或辦法，提供效率。

能看到這里的小伙伴實在太太太厲害了，2020定會愛情事業雙豐收滴，別忘了點贊哦~

作者：運營菌鏈接：https://www.zhihu.com/question/23991984/answer/902705116來源：知乎