EXCEL軟件中函數公式在數據管理中的應用技巧

張楊

摘 要： EXCEL軟件是常用微軟辦公軟件，具有很強大的數據處理能力，尤其是其中的函數，數量眾多功能強大。本文就其中的SUBTOTAL函數、VLOOKUP函數以及多個函數嵌套在征地工作數據管理中的應用進行探討。

關鍵詞：函數 公式 征地內業資料 應用技巧

中圖分類號：TP311 文獻標識碼：A 文章編號：1003-9082（2017）03-0004-01

EXCEL軟件是非常重要的一款辦公軟件，很多工作都需要依靠EXCEL進行數據處理。其中的函數也是種類繁多功能強大，學習它們有助于提高工作效率，減少或者避免計算失誤。尤其在征地拆遷工作中各個項目涉及到少則幾十人，多則上千人的數據資料，使用好EXCEL就顯得尤為重要。本文著重介紹征地拆遷工作常用到的SUBTOTAL函數、VLOOKUP函數以及多個函數之間的套用。

一、SUBTOTAL函數

該函數語法規則：SUBTOTAL（function_num，ref1，ref2，...） function_num為1到11（包含隱藏值）或101到111（忽略隱藏值）之間的數字，具體功能詳見表1。

工作中，我們使用最多的是求和功能（參數9或者109）。其與SUM函數用法接近，但其優點是如果在 ref1，ref2，…中有其他的分類匯總（嵌套分類匯總），將忽略這些嵌套分類匯總，以避免重復計算。也就是說在數據區域中有SUBTOTAL獲得的結果將被忽略。在數據量較多，而且有多個嵌套求和時，使用SUBTOTAL函數就比SUM函數方便的多。

二、VLOOKUP函數

1.簡介

該函數的語法規則：VLOOKUP（lookup_value，table_array， col_index_num，[range_lookup]）。

從表2我們可以對這個函數的4個參數有一個初步的了解。另外需要注意的是在我們的工作中，幾乎都使用精確查找，所以range_lookup的參數一般要選擇為false也就是0。

2.VLOOKUP在征地拆遷內業資料中的應用

2.1引用公安數據

在征地工作中我們經常要用到農戶的身份信息，若能很好的利用公安數據，可以節約大量時間，同時避免發生手工輸入錯誤。在財務上我們可以利用公安數據，輕松導出目標人員的身份證號，批量制作銀行存單。在農轉非工作中我們也可以利用公安數據導出申請人的身份信息進行公示。同理，可以制作人員安置款發放表。

2.2舉例建立臺賬

征地工作中涉及到被征地農戶的土地綜合補償、花木補償、房屋殘值補償等等。這些補償費以公示表的形式張榜出去。同一農戶有多種補償費，會出現在不同的公示表里。而有些農戶只有一種或者兩種補償費。但是我們最終做臺賬的時候是要將所有農戶得到的所有費用分門別類的匯總在同一張表中。靠人工去錄入這些數據制作臺賬的話，工作量非常大也非常容易輸入錯誤。我們可以利用之前制作好的各種公示表數據作為數據源，然后用VLOOKUP函數按照我們想要的排列順序去匯總這些數據。只是，在這里最好用上函數嵌套，作用是讓能查得到的就顯示查出的內容，查不到的就顯示為0。例如IF（COUNTIF（$A$2：$E$181，I2），VLOOKUP（I2，$A$2：$E$181，2，0），0）。

同樣的方法也可以用來建立生活補貼、過渡費的臺賬信息。

三、函數嵌套

人員安置資料中需要從被征地農轉非人員的身份證號碼中提取其出生年月日信息以及計算截止到征地公告時的年齡，這就需要用到多個函數的功能，需要多個函數嵌套使用。

1.提取農轉非人員出生年月日

例如：身份證號儲存在E4單元格中，就可以用以下公式算出該農轉非人員的年齡。

=IF（LEN（E4）=15，MID（E4，7，2）&"年"&MID（E4，9，2）&"月"&MID （E4，11，2）&"日"，MID（E4，7，4）&"-"&MID（E4，11，2）&"-"&MID（E4，13，2） &""）

首先判斷身份證號是不是15位的，若是就按照這組函數套用（MID（E4，7，2）&"年"&MID（E4，9，2）&"月"&MID（E4，11，2）&"日"）進行提取，若不是，就按照這組函數套用（MID（E4，7，4）&"-"&MID（E4，11，2）&"-"&MID（E4，13，2）&""）進行提取，

2.計算截止到征地公告時的年齡

例如：出生時間儲存在D4單元格中，身份證號儲存在E4單元格中，征地補償安置方案批準時間在F4單元格中。就可以用以下公式算出該農轉非人員的年齡。

=IF（MONTH（F4）MONTH（D4），YEAR（F4）-YEAR（D4），IF（DAY（F4）>=DAY（D4），YEAR（F4）-YEAR（D4），YEAR（F4）-YEAR（D4）-1）））

四、總結

本文前面介紹的是SUBTOTAL函數和VLOOKUP函數應用，后面介紹的是函數和公式聯合在數據管理中的應用，看起來復雜難懂，熟悉掌握后會覺得用起來非常方便。這些僅僅為EXCEL函數的冰山一角，要實現辦公自動化信息化，還需要掌握更多功能并在實踐的過程中多應用。