如何制服「綠X表」？掌握這15個技巧讓你寫作業趕Due效率提升百倍
“學好微軟三件套，天天工作呱呱叫”，現在的職場，不管你在哪搬磚，
文｜北大小笨
From LinkedIn
微信號：LinkedIn-China
LinkedIn領英是全球知名的職業社交網站，每個《財富》500強公司均有高管加入。
說Excel是職場的必修課，未免太過誇張，但它的重要性毋庸置疑。
做人力，你得計算得了五險一金，寫得出招聘或培訓分析報告； 做財務，不會Excel函數基本寸步難行；
做行政，你要學會做好會議安排及簽到； 還有運營、倉管、文員…… 常言道，Excel用得好，天天工作沒煩惱。
今天就從資料登錄、處理到輸出等環節，給大家“傳授”一些Excel好用的招式，幫助大家提升技能，告別加班。
一個感覺舒適的工作環境會讓你心情舒暢，工作效率倍增，同樣，一個符合你工作習慣需要的Excel工作環境，
招式一：我的字體我說了算
在通常情況下，Excel表格打開時預設的字體並不是我們喜歡的，這時我們就可以根據自己的偏好靈活設置了，比如設置成微軟雅黑，設置成11號字體。設置完畢後，再打開Excel，就會自動變成我們設置的字體方案。
操作步驟：【檔】-【選項】-【常規】-【新建工作簿】，設置字體、字型大小等。
我的字體我說了算
招式二：作用儲存格移動方向隨心所欲
預設狀態下，按一下Enter鍵，當前作用儲存格就會向下移動。然而工作中，我們往往需要一行一行的輸入資料，操作過程就會顯得比較笨拙。 能不能按一下Enter鍵，當前作用儲存格就往右邊移動呢？答案當然是可以！
操作步驟：【檔】-【選項】-【高級】，將按Enter鍵後移動方向設置為向右。
作用儲存格方向我說了算
招式三：檔保存格式、保存時間順我心意！
雖然Excel版本已經更新到了2016，但通常情況下工作中最常交流使用的版本還是97-03版。 我們在使用高版本Excel，享受更多技巧和便利的同時，
操作步驟：【檔】-【選項】-【保存】，設置檔保存格式及自動保存間隔時間。
檔保存格式、保存時間隨我心意
除了上述這些設置之外，你也可以設置儲存格填充顏色、邊框線默認顏色。
同時，也可以佈置你需要的功能表列、快速訪問工具列按鈕、自訂功能區等，讓你的Excel成為你想要的樣子。
在Excel表格操作中，經常需要快速定位查看資料。
有時我們就會看到，為了定位最後一行的資料，有的人就會在那一直往下拖拖拖，表格資料一多，要拖好久才能完成。如何快速定位到表格的天涯海角呢？ 【CTRL】+【Home】目前的儲存格區域的第一個儲存格 【CTRL】+【End】目前的儲存格區域的最末儲存格 【CTRL】+【↑】、【↓】、【←】、【→】分別定位到目前的儲存格區域的最上、最下、最左、最右儲存格
如果連續操作兩次【CTRL】+【↓】，我們就能來到Excel的盡頭，最多行數會發現為65536行，這處“天涯海角”的風景，估計很多人一輩子都不曾知道吧。長見識了，有沒有！
快速定位到表格的“天涯海角”
如果要把兩行的資料換個位置，你會怎麼做？
除了好多不會做的人，還有些人會個笨辦法：先把一行資料剪切出來，然後剪切複製另一行資料，之後再把最先剪切出來的資料粘貼過去。 太沒有效率了！
操作步驟：選中一行或多行資料，按住【SHIFT】鍵，把滑鼠放置到選中資料行的邊緣位置，待滑鼠指標變化後，拖動到需要的位置，然後鬆開【SHIFT】鍵。列位置的移動操作方式同理。
便捷的交叉換位行或列位置
在工作中，我們經常會發現，當我們對Excel中的日期資料進行處理時往往會出錯。
原因卻很簡單，其他人都是根據自己的習慣隨便的輸入日期，比如2017.07.11,2017-07-11,20170711等，日期不規範就給資料處理帶來了一堆麻煩。
日期可以有兩種符號連接，一種是“-”（英文狀態下），如2017-7-11；一種是“/”（英文狀態下），如2017/7/11。這都是規範的日期格式。
而對於那些不規範的日期格式，我們是不是可以使用替換功能把連接子號諸如“.”換成“-”或“/”呢？
操作步驟：【CTRL】+【H】調出替換兌換框，在英文狀態下，將“.”或者其他符號統一替換成“-”或“/”。
巧用替換快速規範日期格式
有時，我們為了美化表格，經常會使用大量的合併儲存格，如合併相同的部門，相同的職務。但合併儲存格的存在對資料處理來說卻是致命的。
如何巧妙地解決合併儲存格的問題，讓資料表格規範呢？
操作步驟：取消合併儲存格後，按住【CTRL】+【G】或F5，調出【定位】對話方塊，定位標準為空儲存格。
不需要選擇，直接輸入“=B2”(B2為輸入公式所在儲存格同列上方的儲存格位址)，輸入完成後，直接按住【CTRL】+【Enter】，批量操作完成。
巧妙解決合併儲存格問題
注意：B2為操作示意圖案例中選取的儲存格，實際操作中要根據處理需要進行選取。
儲存格是Excel操作中的最基本單元，因此對單元格格式進行設置就成為一項必備可少的工作。
儲存格中不止可以設置資料類型、字體、字型大小、對齊方式等基本操作，我們還可以自訂自己需要的單元格格式，實現資料的快速輸入。
要定義自己需要的單元格格式，我們就得熟悉定義時需要的各種符號。這種符號有很多，先給大家展示一下：
自訂單元格格式代碼
工作中我們無需使用這麼多，但有幾個還是要記住的，比如：
#是數位預留位置，一個#代表一個數字占位元；
@是文本預留位置，代表任意文本；
“”(英文狀態下雙引號)可以表示特定內容；
0可以進行強制數位占位元；
！可以強制顯示某個符號等。
是不是有點暈？沒關係，下面的案例可以幫助大家更好的理解記憶。
招式一：手機號碼分段顯示
操作步驟：按右鍵選擇【設置單元格格式】或【CTRL】+【1】調出對話方塊，在【自訂】設置中，輸入代碼為000-0000-0000，手機號碼11位元即可以分段顯示。
手機號碼分段顯示
招式二：按照萬元進行統計
操作步驟：與上述步驟一樣，輸入代碼為0!.0000“萬元”（雙引號為英文狀態下），設置完成後資料按照萬元進行顯示。
按照萬元進行統計
像金額、數量、考核指標等數字，我們往往要進行資料比對。
通常為了快速比對，我們一般會採取函數的方法，通過兩數相減，結果為0則比對一致，否則不一致。有沒有更加快速的方法呢？
操作步驟：選中要比對的資料，按住【CTRL】+【】後，可以對選出的資料進行顏色填充顯示，顯示的資料即為核對不一致的資料，是不是超級快速。（驕傲臉
一秒搞定資料核對
資料登錄時，我們經常需要限制資料重複輸入，比如工會發新春大禮包，上報上來的名單出現重復資料就不好了。
如何進行重複輸入限制呢，教給大家兩招。
招式一：資料有效性
操作步驟：選中填表區域-【資料】-【資料有效性】-【自訂】，然後利用Countif函數進行限制，比如=Countif($C$2:$C$8,C2)=1,當重複輸入的時候，Countif函數設定的條件則不成立，就會禁止輸入。
輕鬆限制重複輸入-資料有效性
招式二：條件格式
操作步驟：選中填表區域-【開始】-【條件格式】-【突出顯示儲存格規則】-【重複值】，可以對重複值進行格式設置。當重複輸入時，資料就會很明顯的顯示出來。
輕鬆限制重複輸入-條件格式
為了更快捷地進行資料處理和分析，我們可以通過【套用表格樣式】讓我們的表格變身“超級表”。
操作步驟：【CTRL】+【A】全選表格後，點擊【套用表格樣式】，點擊確定即可。同樣你可以根據自己的偏好自訂表格樣式。
超級表-套用表格樣式，快速構建
為什麼要讓每一張表都變身超級表呢？因為超級表有很明顯的三大優勢：
優勢1：標籤行永遠顯示，查看資料非常方便
超大表格時，我們看到後面的資料往往就會忘記這些數字代表了什麼意義，只能再翻到前面去查看或者進行凍結窗格操作。超級表就完全不存在這樣的問題了，標籤行永遠都會顯示出來。
超級表優勢1：標籤行永遠顯示
優勢2：資料區域自動擴展、格式自動套用
變身超級表後，當我們輸入新的資料時，設定的資料區域就會自動擴展，格式也相應自動進行套用。 這樣，我們之前用以資料處理設置的公式或創建的樞紐分析表，就會識別新加的資料，參與計算中。
超級表優勢2：資料區域自動擴展、格式自動套用
優勢3：資料計算一鍵搞定
超級表中嵌入了非常強大的計算功能，選中超級表後，在【設計】選項卡中勾選【匯總行】，就可以對資料進行計算，包括求和、計數、平均值、方差等多種計算方式，非常方便。
超級表優勢3：資料計算一鍵搞定
根據日期進行銷量、金額等統計非常常見，那如何能在日期的基礎上進行月度、季度、年度來統計呢？
好多人可能會想到使用日期函數進行分類然後進行分類匯總。這個方法無疑耗時耗力，其實可以通過【資料透視-創建組】的方式來解決。
操作步驟：選中資料表任一儲存格，插入樞紐分析表，創建行標籤為日期的統計結果。選中樞紐分析表中任一日期，按一下滑鼠右鍵選擇創建組，按住【Shift】,選中年、季、月，完成。
不用計算也能按照年、季度、月度統計
在統計時，每個資料經常需要好幾種統計口徑，如最大最小值、平均值、求和、計算、百分比等，如何一次性統計完成，不用分別設置公式？
操作步驟：選中資料表任一儲存格，插入樞紐分析表，需要幾種統計口徑，就在樞紐分析表中【值】中拖入幾次匯總項。
在樞紐分析表選中匯總項，按一下滑鼠右鍵，通過【值欄位設置】或者【值匯總方式】或者【值顯示方式】來完成多種口徑的統計。
多樣統計一次搞定
資料演示時，領導們經常會提出各種不同的看資料的角度或指標，臨時篩選就會手忙腳亂，還容易出錯。如何快速回應這種需求？我們可以通過資料透視下的切片器來解決。
操作步驟：選中資料表任一儲存格，插入樞紐分析表。選中樞紐分析表任一資料，選擇【選項】下的插入切片器，我們可以根據需要插入多個切片器，組合不同切片器下的功能表項目。
可視篩選
對於HR來說，每月都要對公司的考勤狀況進行統計，通常情況下考勤表結構的設計及資料標籤的設定都完全一致，這時，我們就可以結合Sum函數進行跨表求和了。
操作步驟：選定第一張表，按住【Shift】，選定最後一張表，選中要統計的儲存格，公式為=SUM('1:12'!B6)
多表合併之跨表求和
對於不同部門在整年度的培訓費用統計時，我們可以使用合併計算功能。在使用合併時，我們要求不同資料表的基本結構要一致。
操作步驟一：添加合併計算區域
在顯示整年度統計結果的表格中選中任一儲存格（通常為表中的A1儲存格），然後點擊【資料】-【合併計算】，在合併計算對話方塊中，設置匯總方式（一般為求和），然後分別選中各個要計算的表格中的資料，進行添加。
合併計算1-添加資料區域
操作步驟二：設置標籤位置，呈現統計結果
添加完計算區域後，根據添加的計算區域表格特點，設置標籤位置，如勾選首行，勾選最左側，設置完成後，點擊確定，完成匯總。
合併計算2-設置標籤位置，呈現統計結果
各地分公司將全年12月份各類產品銷售資料匯總，你該如何完整合？學會多重數據透視就好啦。
步驟1：添加資料來源
選擇任一工作表的任一儲存格，先按下快速鍵： 【ALT】+【D】，再連續按兩下【P】鍵，彈出【樞紐分析表和資料透視圖】嚮導，選擇【多重合並計算資料區域】和【樞紐分析表】選項。點擊【下一步】，選擇【創建單頁欄位】，逐一添加各分公司銷售資料。
多表合併1-添加資料來源
要注意工作表的排序與添加後的順序可能不一致，一定以添加資料後形成的順序為准（一般按照拼音排序）
步驟2：樞紐分析表構建
點擊【下一步】，在【新工作表】中構建完成樞紐分析表
多表合併2-樞紐分析表構建
步驟3:修改欄位名稱
根據原始資料，對樞紐分析表中的欄位名稱進行修改，比如本例中，新的樞紐分析表共有四個欄位，分別為頁1（不同分公司），列（產品類型）、行（月份）、值（求和），修改完名稱後，我們可以根據統計需要對樞紐分析表進行重新佈局，重新進行資料計算。
多表合併3-修改欄位名稱
多表合併3-添加城市標籤
注意：將欄位拖入頁所在位置，可以快速修改名稱
同時，本例中樞紐分析表中城市順序為北京、上海、天津、重慶，與sheet表裡的順序有區別，注意這點。
檔保存格式、保存時間隨我心意
除了上述這些設置之外，你也可以設置儲存格填充顏色、邊框線默認顏色。
同時，也可以佈置你需要的功能表列、快速訪問工具列按鈕、自訂功能區等，讓你的Excel成為你想要的樣子。
在Excel表格操作中，經常需要快速定位查看資料。
有時我們就會看到，為了定位最後一行的資料，有的人就會在那一直往下拖拖拖，表格資料一多，要拖好久才能完成。如何快速定位到表格的天涯海角呢？ 【CTRL】+【Home】目前的儲存格區域的第一個儲存格 【CTRL】+【End】目前的儲存格區域的最末儲存格 【CTRL】+【↑】、【↓】、【←】、【→】分別定位到目前的儲存格區域的最上、最下、最左、最右儲存格
如果連續操作兩次【CTRL】+【↓】，我們就能來到Excel的盡頭，最多行數會發現為65536行，這處“天涯海角”的風景，估計很多人一輩子都不曾知道吧。長見識了，有沒有！
快速定位到表格的“天涯海角”
如果要把兩行的資料換個位置，你會怎麼做？
除了好多不會做的人，還有些人會個笨辦法：先把一行資料剪切出來，然後剪切複製另一行資料，之後再把最先剪切出來的資料粘貼過去。 太沒有效率了！
操作步驟：選中一行或多行資料，按住【SHIFT】鍵，把滑鼠放置到選中資料行的邊緣位置，待滑鼠指標變化後，拖動到需要的位置，然後鬆開【SHIFT】鍵。列位置的移動操作方式同理。
便捷的交叉換位行或列位置
在工作中，我們經常會發現，當我們對Excel中的日期資料進行處理時往往會出錯。
原因卻很簡單，其他人都是根據自己的習慣隨便的輸入日期，比如2017.07.11,2017-07-11,20170711等，日期不規範就給資料處理帶來了一堆麻煩。
日期可以有兩種符號連接，一種是“-”（英文狀態下），如2017-7-11；一種是“/”（英文狀態下），如2017/7/11。這都是規範的日期格式。
而對於那些不規範的日期格式，我們是不是可以使用替換功能把連接子號諸如“.”換成“-”或“/”呢？
操作步驟：【CTRL】+【H】調出替換兌換框，在英文狀態下，將“.”或者其他符號統一替換成“-”或“/”。
巧用替換快速規範日期格式
有時，我們為了美化表格，經常會使用大量的合併儲存格，如合併相同的部門，相同的職務。但合併儲存格的存在對資料處理來說卻是致命的。
如何巧妙地解決合併儲存格的問題，讓資料表格規範呢？
操作步驟：取消合併儲存格後，按住【CTRL】+【G】或F5，調出【定位】對話方塊，定位標準為空儲存格。
不需要選擇，直接輸入“=B2”(B2為輸入公式所在儲存格同列上方的儲存格位址)，輸入完成後，直接按住【CTRL】+【Enter】，批量操作完成。
巧妙解決合併儲存格問題
注意：B2為操作示意圖案例中選取的儲存格，實際操作中要根據處理需要進行選取。
儲存格是Excel操作中的最基本單元，因此對單元格格式進行設置就成為一項必備可少的工作。
儲存格中不止可以設置資料類型、字體、字型大小、對齊方式等基本操作，我們還可以自訂自己需要的單元格格式，實現資料的快速輸入。
要定義自己需要的單元格格式，我們就得熟悉定義時需要的各種符號。這種符號有很多，先給大家展示一下：
自訂單元格格式代碼
工作中我們無需使用這麼多，但有幾個還是要記住的，比如：
#是數位預留位置，一個#代表一個數字占位元；
@是文本預留位置，代表任意文本；
“”(英文狀態下雙引號)可以表示特定內容；
0可以進行強制數位占位元；
！可以強制顯示某個符號等。
是不是有點暈？沒關係，下面的案例可以幫助大家更好的理解記憶。
招式一：手機號碼分段顯示
操作步驟：按右鍵選擇【設置單元格格式】或【CTRL】+【1】調出對話方塊，在【自訂】設置中，輸入代碼為000-0000-0000，手機號碼11位元即可以分段顯示。
手機號碼分段顯示
招式二：按照萬元進行統計
操作步驟：與上述步驟一樣，輸入代碼為0!.0000“萬元”（雙引號為英文狀態下），設置完成後資料按照萬元進行顯示。
按照萬元進行統計
像金額、數量、考核指標等數字，我們往往要進行資料比對。
通常為了快速比對，我們一般會採取函數的方法，通過兩數相減，結果為0則比對一致，否則不一致。有沒有更加快速的方法呢？
操作步驟：選中要比對的資料，按住【CTRL】+【】後，可以對選出的資料進行顏色填充顯示，顯示的資料即為核對不一致的資料，是不是超級快速。（驕傲臉
一秒搞定資料核對
資料登錄時，我們經常需要限制資料重複輸入，比如工會發新春大禮包，上報上來的名單出現重復資料就不好了。
如何進行重複輸入限制呢，教給大家兩招。
招式一：資料有效性
操作步驟：選中填表區域-【資料】-【資料有效性】-【自訂】，然後利用Countif函數進行限制，比如=Countif($C$2:$C$8,C2)=1,當重複輸入的時候，Countif函數設定的條件則不成立，就會禁止輸入。
輕鬆限制重複輸入-資料有效性
招式二：條件格式
操作步驟：選中填表區域-【開始】-【條件格式】-【突出顯示儲存格規則】-【重複值】，可以對重複值進行格式設置。當重複輸入時，資料就會很明顯的顯示出來。
輕鬆限制重複輸入-條件格式
為了更快捷地進行資料處理和分析，我們可以通過【套用表格樣式】讓我們的表格變身“超級表”。
操作步驟：【CTRL】+【A】全選表格後，點擊【套用表格樣式】，點擊確定即可。同樣你可以根據自己的偏好自訂表格樣式。
超級表-套用表格樣式，快速構建
為什麼要讓每一張表都變身超級表呢？因為超級表有很明顯的三大優勢：
優勢1：標籤行永遠顯示，查看資料非常方便
超大表格時，我們看到後面的資料往往就會忘記這些數字代表了什麼意義，只能再翻到前面去查看或者進行凍結窗格操作。超級表就完全不存在這樣的問題了，標籤行永遠都會顯示出來。
超級表優勢1：標籤行永遠顯示
優勢2：資料區域自動擴展、格式自動套用
變身超級表後，當我們輸入新的資料時，設定的資料區域就會自動擴展，格式也相應自動進行套用。 這樣，我們之前用以資料處理設置的公式或創建的樞紐分析表，就會識別新加的資料，參與計算中。
超級表優勢2：資料區域自動擴展、格式自動套用
優勢3：資料計算一鍵搞定
超級表中嵌入了非常強大的計算功能，選中超級表後，在【設計】選項卡中勾選【匯總行】，就可以對資料進行計算，包括求和、計數、平均值、方差等多種計算方式，非常方便。
超級表優勢3：資料計算一鍵搞定
根據日期進行銷量、金額等統計非常常見，那如何能在日期的基礎上進行月度、季度、年度來統計呢？
好多人可能會想到使用日期函數進行分類然後進行分類匯總。這個方法無疑耗時耗力，其實可以通過【資料透視-創建組】的方式來解決。
操作步驟：選中資料表任一儲存格，插入樞紐分析表，創建行標籤為日期的統計結果。選中樞紐分析表中任一日期，按一下滑鼠右鍵選擇創建組，按住【Shift】,選中年、季、月，完成。
不用計算也能按照年、季度、月度統計
在統計時，每個資料經常需要好幾種統計口徑，如最大最小值、平均值、求和、計算、百分比等，如何一次性統計完成，不用分別設置公式？
操作步驟：選中資料表任一儲存格，插入樞紐分析表，需要幾種統計口徑，就在樞紐分析表中【值】中拖入幾次匯總項。
在樞紐分析表選中匯總項，按一下滑鼠右鍵，通過【值欄位設置】或者【值匯總方式】或者【值顯示方式】來完成多種口徑的統計。
多樣統計一次搞定
資料演示時，領導們經常會提出各種不同的看資料的角度或指標，臨時篩選就會手忙腳亂，還容易出錯。如何快速回應這種需求？我們可以通過資料透視下的切片器來解決。
操作步驟：選中資料表任一儲存格，插入樞紐分析表。選中樞紐分析表任一資料，選擇【選項】下的插入切片器，我們可以根據需要插入多個切片器，組合不同切片器下的功能表項目。
可視篩選
對於HR來說，每月都要對公司的考勤狀況進行統計，通常情況下考勤表結構的設計及資料標籤的設定都完全一致，這時，我們就可以結合Sum函數進行跨表求和了。
操作步驟：選定第一張表，按住【Shift】，選定最後一張表，選中要統計的儲存格，公式為=SUM('1:12'!B6)
多表合併之跨表求和
對於不同部門在整年度的培訓費用統計時，我們可以使用合併計算功能。在使用合併時，我們要求不同資料表的基本結構要一致。
操作步驟一：添加合併計算區域
在顯示整年度統計結果的表格中選中任一儲存格（通常為表中的A1儲存格），然後點擊【資料】-【合併計算】，在合併計算對話方塊中，設置匯總方式（一般為求和），然後分別選中各個要計算的表格中的資料，進行添加。
合併計算1-添加資料區域
操作步驟二：設置標籤位置，呈現統計結果
添加完計算區域後，根據添加的計算區域表格特點，設置標籤位置，如勾選首行，勾選最左側，設置完成後，點擊確定，完成匯總。
合併計算2-設置標籤位置，呈現統計結果
各地分公司將全年12月份各類產品銷售資料匯總，你該如何完整合？學會多重數據透視就好啦。
步驟1：添加資料來源
選擇任一工作表的任一儲存格，先按下快速鍵： 【ALT】+【D】，再連續按兩下【P】鍵，彈出【樞紐分析表和資料透視圖】嚮導，選擇【多重合並計算資料區域】和【樞紐分析表】選項。點擊【下一步】，選擇【創建單頁欄位】，逐一添加各分公司銷售資料。
多表合併1-添加資料來源
要注意工作表的排序與添加後的順序可能不一致，一定以添加資料後形成的順序為准（一般按照拼音排序）
步驟2：樞紐分析表構建
點擊【下一步】，在【新工作表】中構建完成樞紐分析表
多表合併2-樞紐分析表構建
步驟3:修改欄位名稱
根據原始資料，對樞紐分析表中的欄位名稱進行修改，比如本例中，新的樞紐分析表共有四個欄位，分別為頁1（不同分公司），列（產品類型）、行（月份）、值（求和），修改完名稱後，我們可以根據統計需要對樞紐分析表進行重新佈局，重新進行資料計算。
多表合併3-修改欄位名稱
多表合併3-添加城市標籤
注意：將欄位拖入頁所在位置，可以快速修改名稱
同時，本例中樞紐分析表中城市順序為北京、上海、天津、重慶，與sheet表裡的順序有區別，注意這點。