EXCEL軟件中函數(shù)公式在數(shù)據(jù)管理中的應(yīng)用技巧

張楊

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

關(guān)鍵詞：函數(shù) 公式 征地內(nèi)業(yè)資料 應(yīng)用技巧

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1003-9082（2017）03-0004-01

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

一、SUBTOTAL函數(shù)

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

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

二、VLOOKUP函數(shù)

1.簡(jiǎn)介

該函數(shù)的語法規(guī)則：VLOOKUP（lookup_value，table_array， col_index_num，[range_lookup]）。

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

2.VLOOKUP在征地拆遷內(nèi)業(yè)資料中的應(yīng)用

2.1引用公安數(shù)據(jù)

在征地工作中我們經(jīng)常要用到農(nóng)戶的身份信息，若能很好的利用公安數(shù)據(jù)，可以節(jié)約大量時(shí)間，同時(shí)避免發(fā)生手工輸入錯(cuò)誤。在財(cái)務(wù)上我們可以利用公安數(shù)據(jù)，輕松導(dǎo)出目標(biāo)人員的身份證號(hào)，批量制作銀行存單。在農(nóng)轉(zhuǎn)非工作中我們也可以利用公安數(shù)據(jù)導(dǎo)出申請(qǐng)人的身份信息進(jìn)行公示。同理，可以制作人員安置款發(fā)放表。

2.2舉例建立臺(tái)賬

征地工作中涉及到被征地農(nóng)戶的土地綜合補(bǔ)償、花木補(bǔ)償、房屋殘值補(bǔ)償?shù)鹊?。這些補(bǔ)償費(fèi)以公示表的形式張榜出去。同一農(nóng)戶有多種補(bǔ)償費(fèi)，會(huì)出現(xiàn)在不同的公示表里。而有些農(nóng)戶只有一種或者兩種補(bǔ)償費(fèi)。但是我們最終做臺(tái)賬的時(shí)候是要將所有農(nóng)戶得到的所有費(fèi)用分門別類的匯總在同一張表中?？咳斯とヤ浫脒@些數(shù)據(jù)制作臺(tái)賬的話，工作量非常大也非常容易輸入錯(cuò)誤。我們可以利用之前制作好的各種公示表數(shù)據(jù)作為數(shù)據(jù)源，然后用VLOOKUP函數(shù)按照我們想要的排列順序去匯總這些數(shù)據(jù)。只是，在這里最好用上函數(shù)嵌套，作用是讓能查得到的就顯示查出的內(nèi)容，查不到的就顯示為0。例如IF（COUNTIF（$A$2：$E$181，I2），VLOOKUP（I2，$A$2：$E$181，2，0），0）。

同樣的方法也可以用來建立生活補(bǔ)貼、過渡費(fèi)的臺(tái)賬信息。

三、函數(shù)嵌套

人員安置資料中需要從被征地農(nóng)轉(zhuǎn)非人員的身份證號(hào)碼中提取其出生年月日信息以及計(jì)算截止到征地公告時(shí)的年齡，這就需要用到多個(gè)函數(shù)的功能，需要多個(gè)函數(shù)嵌套使用。

1.提取農(nóng)轉(zhuǎn)非人員出生年月日

例如：身份證號(hào)儲(chǔ)存在E4單元格中，就可以用以下公式算出該農(nóng)轉(zhuǎn)非人員的年齡。

=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） &""）

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

2.計(jì)算截止到征地公告時(shí)的年齡

例如：出生時(shí)間儲(chǔ)存在D4單元格中，身份證號(hào)儲(chǔ)存在E4單元格中，征地補(bǔ)償安置方案批準(zhǔn)時(shí)間在F4單元格中。就可以用以下公式算出該農(nóng)轉(zhuǎn)非人員的年齡。

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

四、總結(jié)

本文前面介紹的是SUBTOTAL函數(shù)和VLOOKUP函數(shù)應(yīng)用，后面介紹的是函數(shù)和公式聯(lián)合在數(shù)據(jù)管理中的應(yīng)用，看起來復(fù)雜難懂，熟悉掌握后會(huì)覺得用起來非常方便。這些僅僅為EXCEL函數(shù)的冰山一角，要實(shí)現(xiàn)辦公自動(dòng)化信息化，還需要掌握更多功能并在實(shí)踐的過程中多應(yīng)用。