[Office] 公務員WPS Excel常用的一些技巧方法

Eastmount發表於2018-01-05

這篇文章主要是我最近工作使用WPS Excel的一些常用技巧和方法，僅僅是一篇線上筆記。當然實際操作中，你遇到問題百度經驗或相關網站會提供對應的解決方法，而且它們寫得更好，這篇文章更多的是結合自己使用學到的技巧。作為程式設計師，寫了這麼多年的程式碼，用過的Excel屈指可數，不會這些技巧也是理所當然的，因為我們需要做的是去開發這些軟體，或者寫程式碼直接匯出各種各樣的表格，視覺化處理等等。只是現在這一年，需要學習罷了。文章沒有涉及到程式設計知識，希望大家多多海涵，這也是我這段時間工作生活的一個對映，一年的磨鍊，一年的等待，一年的成長，感謝這一年親朋好友同事的幫助，終身受益。

一.資料透視表功能
二.常用小技巧
1.設定表格統一行高
2.表格百分比計算及除法
3.自定義排序功能
4.表格篩選功能
5.固定表頭
6.設定含表頭列印
7.常見字元、函式功能
8.查詢重複項
三.vlookup函式獲取兩張表資料
四.其他高階技巧
1.設定定時儲存
2.並排查詢工作薄
3.模糊查詢
4.隔行顏色顯示
5.固定表頭
6.直方圖生成

一. 資料透視表功能

資料透視表是WPS做彙總資訊的一個很好的功能，通常用於繪製各種報表、彙總表等。現在假設存在如下“貴州5A和4A風景區”的資料，如下圖所示。
注意：資料中旅遊人數、旅遊收入、錄入年份，純屬虛構，讀者結合自己資料實驗。

第一步：選中需要的資料集，注意不能包含跨行或合併的資料行、資料列，點選“資料”->“資料透視表”->選擇“新建工作表”->“確定”。
您可以按住Shift鍵滑動滑鼠選中所有的資料。

第二步：點選確定後如下圖所示，需要在下面頁面操作生成對應的彙總表。右邊為對應的欄位，選擇所需欄位對資料進行彙總。

第三步：這裡選擇“市州”、“景點名稱”、“旅遊人數”和“旅遊收入”等欄位資訊。注意需要統計的對應類別為行，對應的值為彙總的數值。

注意，在右下角“值”部分，選中對應欄位，可以設定求和、計數等選項，最後進行簡單處理即可生成對應的彙總表，可以看到各個市州的景區情況，同時也可以透視不同年份的情況。

二. 常用小技巧

1.設定表格統一行高

在某些情況下，由於Excel行資料過多時，需要設定固定的行高，那怎麼實現呢？下面講述一個簡便的方法。選中需要設定統一行高的資料行，然後找到左邊行號的兩行間隔，拖動滑鼠則可設定固定統一的行高，如下圖所示。

也可以右鍵設定行高，如下圖所示：

同時，在設定Excel表中，通常習慣儘可能顯示一行的所有資料，讓其自適應行高，這隻需要選中所需行，點選左邊兩行之間的線條即可實現，如下圖所示。

2.表格百分比計算及除法
假設存在如下表格，現在需要統計各個市州所佔人數的百分比，怎樣實現比較迅速呢？在新的一列中，設定：
當前行資料/求和資料(27107)*100

然後點選如圖所示選中對話方塊的右下角，並雙擊滑鼠，則更新所有資料。

同時，很多時候會出現需要將單位萬元修改為億元，則直接設定其D22/10000，所有資料分別除以一萬即可。這也是一個小技巧。

3.自定義排序功能

如果是按照某一列的大小來排序還好實現，而某些表格通常會存在一些自定義的順序，那麼如何對其進行自定義排序呢？比如下面生成的透視彙總表需要按照貴州省市州固定順序排列，即：貴\遵\六\安\畢\銅\黔東南\黔南\黔西南的順序。

首先根據需求的順序，我們在新的一列中增加對應的數字，1表示新的排名是第一位，如下圖所示：

然後選中它們，點選“排序”->“自定義排序”->主要關鍵字選擇“列E”->升序確定即可。

排序成功後輸出如下所示：

4.表格篩選功能

表格篩選是一個比較常用的功能，包括顏色篩選、內容篩選和數字篩選，比如找到某種特定顏色的資料，再如找到年旅遊人數大於2000萬人的景點，這些在公務員中是常見的功能。

5.固定表頭

固定表頭功能主要是當資料較多時，當使用者滑動滑鼠瀏覽資料時，知道對應資料的欄位，方便了解資訊。其設定方法主要是開始欄目中的“凍結視窗”，也可以取消凍結或固定列。比如下圖固定前兩行資料。

如下圖所示，可以看到表頭固定情況下預覽其他資料。

6.設定含表頭列印

設定表頭列印是為了當資料很多時，能夠檢視每頁資料的表頭，而不用每次都翻到第一頁檢視。主要方法是：在“頁面佈局”中點選“列印標題”，如下圖所示。

然後選中“頂端標題行”選擇需要列印的表頭即可。

列印效果預覽如下圖所示：

同時，可以在“頁面佈局”中設定紙張大小、紙張方向、紙張邊距等。

7.常見字元、函式功能
字串函式主要講解&拼接，left獲取左邊部分、right獲取右邊部分、mid獲取中間內容。比如：

8.查詢重複項

查詢重複項如下所示，點選“高亮重複項”。

如果出現重複專案如“黃果樹瀑布”，它會標記成橙色。

更多知識比如設定A3列印、帶格式貼上等，希望讀者自己研究。

三. vlookup函式獲取兩張表資料

VLOOKUP函式是Excel中的一個縱向查詢函式，它與LOOKUP函式和HLOOKUP函式屬於一類函式，在工作中都有廣泛應用，例如可以用來核對資料，多個表格之間快速匯入資料等函式功能。下面講解vlookup()函式從Sheet1中快速匯入Sheet2的“年旅遊人數”欄位。

接下來使用vlookup函式：
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
表述就是VLOOKUP(查詢值，查詢範圍，查詢列數，精確匹配或者近似匹配），在我們的工作中，幾乎都使用精確匹配，該項的引數一定要選擇為false。否則返回值會出乎你的意料。

=VLOOKUP(A4,Sheet1!A1:H23,4,FALSE)其中A4表示對應相等欄位，通過“黃果樹瀑布”；Sheet1!A1:H23表示從該區域進行查詢；4表示第四列資料；FALSE表示精確查詢。