Excel中有哪些能瞬間提高工作效率但是不為人知的技巧？

2020年了，Excel需要分兩部分學。第一是傳統的Excel，比如函數、公式、VBA等。另外一部分就是Excel的報表自動化工具，PowerQuery和PowerPivot。當然我更建議你學習Excel的Powerquery和PowerPivot功能，因為這是以后的趨勢。處理數據的效率是傳達Excel的十倍百倍。

甚至有人說，PowerPivot是Excel20年來最好的發明。

傳統Excel知識掌握常用的就可以了。

文末免費發送電子書啦，要看到最后哈

VLOOKUP

VLOOKUP可謂是白領必學函數。該函數簡單易學，是職場人必備的辦公技巧了。但是我們在使用該函數的時候需要學會變通，并且配合其他函數進行學習才能真正意義上提高我們的數據處理效率。函數語法如下圖；

01、查找數據列順序一致

用VLOOKUP函數查找多列數據時，如果需要查找的數據列在查找區域中是連續的。那么這時我們只需要修改VLOOKUP的第三個參數，也就是查找數據所在列數就可以完成查找。但是單獨地一個一個修改VLOOKUP函數的第三個參數，有時也是很耗時間的。如下圖，示例中只有4列也許不覺得時間很久，隨著列數的增加浪費的時間將會越多。

如果需要查找的列在原始區域的列數是遞增的，我們可以使用COLUMN函數來自動化參數的輸入。

我們可以先看看，COLUMN函數的語法：

COLUMN([reference])

我們可以看到COLUMN函數的參數是用方括號表示的，這說明該函數的參數是可以缺省的。就是說我們可以不輸入參數值。COLUMN 函數返回給定 單元格引用 的列號。例如，在B列任意單元格公式=COLUMN （）返回2，因為B列的列號是2。

那么我們將COLUMN函數向右拖動將分別生成2、3、4、5。剛好是我們VLOOKUP需要的第三個參數。

所以結合相對引用可以將上述B9中單元格的公式改為：

=VLOOKUP($A$9,$A$2:$E$6,COLUMN(),FALSE)

然后往右拖動填充公式就可以一次性完成數據查找了。是不是超快捷？

這里切換絕對引用使用了快捷鍵 F4。

02、查找數據列順序不一致

上面的例子是理想情況下的，實際情況是很多時候我們需要查找的數據是從原數據區域中挑選幾列，甚至會將原來的列順序打亂。雖然這種情況下，需要做的也是修改第三個參數的數值，但是簡單的使用COLUMN函數卻無法滿足需求，因為第三參數并不是遞增的。這時我們需要用的MATCH函數。

MATCH的英文名稱是匹配。所以顧名思義，使用 MATCH 函數在指定單元格區域內中搜索匹配的項，然后返回該項在此區域中的相對位置。

MATCH(lookup_value, lookup_array, [match_type])

例如，在B8中輸入公式 =MATCH(B8,A1:E1,0) 返回數字 2，因為物品是該區域中（標題行中）的第二項。

以此類推，可以快速返回其他標題所在的列數。返回的結果正是我們VLOOKUP需要的第三個參數。所以B9中單元格的公式可以改為：

=VLOOKUP($A$9,$A$2:$E$6,MATCH(B8,$A$1:$E$1,0))

接下來只需要拖動公式填充就可以快速查找需要的數據了。

幾個隱藏函數

今天向大家介紹 Excel 中三個實用的隱藏函數，對于 HR 和財會小伙伴來說，一定會覺得相見恨晚哦。這三個函數分別是DATEDIF、NUMBERSTRING、DATESTRING。

那么，什么是隱藏函數 ？

普通函數在輸入時都有智能填充功能，輸入部分函數以后可以按 Tab 鍵進行補齊。

而隱藏函數輸入時沒有智能提示，也無法使用 Tab 鍵補齊，需要手工輸入整個函數。

普通函數可以點擊“編輯欄”旁邊的“插入函數”按鈕，進行查找使用，隱藏函數無法找到。

隱藏函數在函數列表里是找不到，甚至連幫助文件中也沒有相關說明。

那么，又為什么隱藏這些函數呢 ？

微軟沒有官方聲明將這幾個函數隱藏的原因。對于 DATEDIF 函數，微軟為了兼容當時流行的電子表格軟件 Lotus 1-2-3 而提供了這個函數。1983 年由蓮花公司出品 Lotus 1-2-3，出現時間早于微軟的 Excel，在 1980 年代中期如日中天。但在后面的競爭中逐漸被淘汰。

貼個圖，緬懷一下電子表格軟件的先驅 Lotus 1-2-3

下面正式給大家介紹這三個函數

1、DATEDIF 函數

DATEDIF 函數用于計算兩個日期之間相隔的天數、月數或年數。其函數語法為：

DATEDIF(start_date,end_date,unit)

第一和第二個參數是日期，第三參數是計算的間隔單位，可以為 D、M、Y、YD、YM、MD。

前面兩個參數很容易理解，下面通過實際案例來理解最后一個參數。

結合 TODAY 函數，第三個參數選擇 「Y」 可以計算員工年齡：

第三個參數選擇 「D」 可以計算員工工齡（具體到天）：

身份證號碼可以提取出生日信息，所以可以直接用身份證號碼計算年齡：

使用以下公式：

=DATEDIF(--TEXT(MID(B2,7,8),"0000-00-00"),TODAY(),"Y")

HR 小伙伴們經常需要在員工生日的時候送上慰問，所以需要在員工表上設置生日提醒。將第三個參數選擇 「YD」 可以計算日期時間段中天數的差，而忽略日期中的年，進而實現生日提醒：

使用以下公式實現 10 天之內的生日提醒：

=TEXT(10-DATEDIF(C3,NOW()+10,"YD"),"0 天后生日;;今日生日")

涉及的計算員工的考勤工時，需要計算兩個時間的小時、分鐘或者秒數差，可惜的是 DATEDIF 的第三個參數沒有提供這兩個間隔上的計算參數。這時可以考慮使用 Power Pivot 的 DATEDIFF 函數。這兩個函數真不能不說是相似啊。不過后者強大很多。

參考閱讀: 這個函數微軟都藏起來，不想太早給你知道

2、NUMBERSTRING

財務人員制作報銷單、發票模板等需要將阿拉伯數字轉化為中文大寫數字。如果一個一個手工輸入的話，工作量就不可估量了。隱藏函數 NUMBERSTRING 就是實現這種轉換的函數。其基本語法為：

NUMBERSTRING(Value,Type)

第一個參數是數值，值得注意的是該參數不支持包含小數的數值。第二個參數指定返回結果的類型，有三種 1，2，3。第二個參數選擇為 2 就是我們財務上需要的中文大寫數字啦。

我們還可以使用另外兩種方法實現中文大寫數字轉換。

使用快捷鍵 「Ctrl + 1」設置單元格格式為中文大寫數字

使用 TEXT 函數也可以實現轉換，TEXT 函數基本語法：

TEXT(Value,Format_text)

將 Format_text 設置為 [DBNum2] 就可以實現轉換

3、DATESTRING

DATESTRING 用于將日期轉換為“yy 年 mm 月 dd 日”格式的文本日期?；菊Z法為：

DATESTRING(Date)

在 Excel 中輸入以下公式，將返回 20 年 2 月 23日（返回系統當天日期）

=DATESTRING(TODAY())

這個函數比較簡單，使用場景比較少。小編就不多做介紹了，在這里拋磚引玉，供需要的朋友們使用哈。

關于PowerQuery的幾個重要功能

替代VLOOKUP,一鍵查找關聯列

將兩個數據表合并以增加數據分析維度是我們使用 Excel 經常會面對的問題。過去，我們只能用 VLOOKUP函數，復雜一點就甚至需要使用 INDEX 及 MATCH 函數，然而很多人很難理解函數的使用邏輯。

參考閱讀 :

/ XLOOKUP 還沒出現時，VLOOKUP 就已經被它干掉了 /

學會 Excel 的 PQ 功能以后，點擊鼠標就可以完成復雜函數組合才能實現的數據豐富功能。我們有兩張表，一張庫存信息表，一張銷售明細。兩張表通過 SKU Number 進行關聯。

要分析每一筆交易的收入、成本就要將 Sale Price 和 Unit Cost 匹配到銷售明細表。

要分析不同產品類型、不同分機構的銷量情況就要將 Type、Brand 匹配到銷售明細表。

庫存信息表

銷售明細表

我們可以使用 PQ 的「合并查詢」功能進行匹配。

一、僅創建連接

為了使用 PQ 的「合并查詢」，需要將兩個數據表都加載進 PQ 編輯器。我們都知道將數據加載到 PQ 以后關閉并上載到 Excel 以后，會復制一份原始數據表到 Excel 的新工作表中，這樣就顯得多余。

所以我們需要將查詢僅創建成鏈接。

1、使用數據選項卡下「自表格/區域」功能，將庫存信息表加載進 PQ 編輯器。

2、庫存信息表加載到 PQ 編輯器，這時你可以對數據進行轉換和清洗，當然本例中不需要執行其他操作。

3、點擊「主頁」>「關閉并上載」下拉彈出菜單中，選擇「關閉并上載至」。

4、在導入數據窗口中選擇「僅創建連接」。

這時建立的查詢會顯示在「查詢 & 連接」中，而不會新建一個工作表。

同樣的方式將銷售明細表也加載到 PQ 編輯器中。這時兩個數據表都僅僅以鏈接的形式存在于 Excel 中，而不會重復加載。

使用僅創建鏈接的方式加載數據，可以讓我們的Excel工作簿簡約，不冗余。一般情況下，我們會將原始鏈接數據創建成鏈接，而把最終的數據清洗結果加載到工作表中。

二、單條件合并查詢

將兩個表格加載到 PQ 以后，就可以使用合并查詢功能。

1、將鼠標懸停在查詢上方，點擊鼠標右鍵彈出的菜單中選擇「合并」。

2、在彈出的窗口中，上方表格選擇為 Sales 表，下方的表格選擇為 Inventory 表。

3、鼠標點擊 Inventory 表的 SKU Number 列，同樣點擊 Sales 表的 SKU Number 列。選擇兩列作為兩個表的關聯列。

4、點擊確定就完成了數據匹配，下面只需要展開合并后的列，選擇需要的列并加載到 Excel 就可以了。

幾次鼠標點擊就替代了復雜的函數組合才能實現的功能。這就是 PQ 的強大之處。短時間的練習就可以在數據處理上取得突破性的進展。

三、多條件合并查詢

PQ 的 合并查詢功能也很簡單，只要選擇關聯字段時按一致的順序選擇好字段，點擊確定就可以實現多條件合并了。選擇多個字段按住「Ctrl」鍵點選即可。PQ 界面字段上方的小數字代表了關聯字段的順序。

四、模糊查詢

如果我們的需求是進行模糊匹配的話，最新版的 PQ 還給我們提供了模糊匹配的選項。勾選「模糊匹配執行合并」，然后設置相似性閾值，默認的閾值是0.8。閾值設置的越小，對于匹配字段相似性要求就越低。所以我們需要嘗試查找出最合適的閾值進行模糊匹配。

最后，雖然 PQ 提供了模糊匹配功能，但是這種匹配方式有時并不能準確的提供答案。因此還是必須得重視數據的規范性。

一秒合并工作簿，一鍵刷新

上文我們通過單獨給每一個文件加載成查詢，然后使用 Power Query 的「追加」功能將結構一致的文件合并，這種方法雖然避免了復制黏貼的重復工作，但是將文件加載成查詢的過程也是一個枯燥的動作。

/ 參考閱讀：Power Query 追加功能，快速合并文件的好方法 /

如果需要合并的文件再次增加，重復操作不可避免。Power Query 的「從文件夾」功能可以解決這個問題。

一、從文件夾合并

我們將上一篇文章中所有銷售數據文件都放同一文件夾Begin中，然后新建一個空白 Excel 文件，用來加載合并的文件。

從文件夾功能使用操作如下：

新建查詢 > 獲取數據 > 來自文件 > 從文件夾導航到目標文件夾，點擊確定 > 轉換數據

點擊「轉換數據」進入 Power Query 編輯界面?？梢钥吹?Begin 文件夾里面的文件清單和它們的各種屬性。比如：文件名、擴展名（文件類型）、獲取時間、文件創建時間等。

值得注意的是第一列（Content），該列存儲的就是文件夾中的二進制文件，里面就是我們需要合并的數據。而列右邊的圖標就是合并文件的按鈕。我們可以選中該列，刪除其他列。

二、合并文件

我們可以點擊 Content 列右上角的合并文件功能，實現文件合并。

操作完這個步驟以后在查詢窗口可以看到 Power Query 自動生成的查詢。其中我們需要的合并好的文件就在其他查詢 Begin 里面。

同時在 Begin 這個查詢的查詢設置窗口也能看到很多 Power Query 自動應用的步驟。其中從「篩選的隱藏文件1」到「擴展的表格列1」都是文件合并的操作。這個最后一個步驟「更改的類型」將其刪除。

三、轉換示例文件

在查詢窗口中我們可以看到有一個新建的查詢「轉換的示例文件」。這個文件是 Power Query 轉換文件時的參考文件，它是對合并時我們選擇的示例文件進行操作的查詢。對這個文件的所有操作都會應用到其它的文件中。

所以如果合并的過程中需要修改步驟的話可以在該查詢上操作，Power Query 會對其他文件執行同樣的操作。

四、一鍵刷新

隨著時間的推移，我們需要合并的文件會越來越多。而我們并不需要再次操作以上的步驟，將相應的文件放到 Begin 文件夾刷新查詢就可以自動就文件夾中所有文件合并了。

如果文件是放在子文件夾里，Power Query 同樣可以將子文件夾中數據合并。

關于PowerQuery，我將前段時間更新的 #PQ in Excel 專輯內的文章進行整理，制作成了一本小型電子書。

內容來自于之前的專輯分享 #PQ in Excel，我挑選了其中入門案例、基礎界面介紹及數據清洗十招的內容，共 52 頁，匯總為《PQ入門手冊》。

關注我，私信「PQ入門」，即可獲得下載鏈接。一起學習微軟 Excel 最新最酷技能。