原創申明
本文原創作者為劉誠燃，歡迎轉發，謝絕轉載。群友一直想讓筆者教大家如何寫一些excle公式以輔助填報1104台賬。像sumifs，vlookup這些常用公式我想大家一定并不陌生，今天就教一個更很簡單又非常适用的公式。
1104報表中，剩餘期限計算是一件很讓人頭大的問題，比如，G21裡91日至1年，這1年到底是360天，還是1年？還有G33報表裡，1.5年到底是多少天？G18裡是含下不含上，其他報表又是含上不含下?如何利用2個日期直接得出剩餘期限？本文一一歸納整理，是一個非常有用的實用貼。
一、G21中一年到底是如何區分的？
筆者在1104企業微信群裡先做了一個調研，結果發現G21關于1年還真是差别挺大的，有的是按360天，有的是對年對月對日，有的是365天，如果再設個366天估計也會有人填。
的确，G21填報說明裡沒有明說，但我們可以通過後面關于活期存款的案例來做一個推斷G21裡的1年實際是指360天。
本行的F列[1年至5年]根據過去12個月最低的活期存款額填報，反映活期存款中較為穩定的部分；A-E列為自動計算項目，反映其餘部分的活期存款平均列入一年以内的各剩餘期限區間的情況(以各時間段期限占比進行配置設定)。例如，某銀行在2009年3月底的活期存款(不含财政性存款)為150億元，在過去12個月中最低活期存款額(不含财政性存款)是80億元，那麼該銀行3月末活期存款(不含财政性存款)中較為穩定的部分就是80億元，應在“一年以上”項目下填入80億元；其餘部分即(150-80)億元，要平均列入一年以内的各剩餘期限内(以各時間段期限占比進行配置設定)，如在“2日至7日”項目下應填入((150-80)/360)*(7-2+1)億元。
G21可以統計次日、2日至7日、8日至30日、31日至90日、91日至1年、1年至5年、5年至10年、10年以上各個時段的流動性缺口和流動性缺口率。報表雖然展現的是各個時段，銀行自身在算缺口率時應該精确到每一日。系統不支援的話，做流動性管理的司庫至少手工要能算出1年内每一日的流動性缺口情況。
在統計逾期時間段我們經常用到也是30天、60天、90天、180天、270天和360天這些時段，是以360天是一個非常重要的時間節點。我們在計算利息時1年換算成日利率也是用的是360天。
二、G0104中原始期限1年是多少天？
G0104中，存款或貸款的原始期限包括活期、三個月以内、三個月至六個月、六個月至一年、一年至兩年、兩年至三年、三年至五年、五年以上、逾期這幾種分類方式。
本表中時間劃分區間均為含上限時間，不含下限時間。如“一年至兩年”則為含兩年不含一年。月、年均按自然月年統計。實際統計時，應按存貸款原始(合同)期限分類填列，
總結而言就是含上不含下，如果正好是1年則六個月至一年這個時段内。按照對年對月對日的原則，一年糾結是多少天呢？
答案是可能是365天也可能是366天。比如2018年11月24日發放的一年期貸款，到期日為2019年11月24日，兩個日期直接相減，得到的是365天。如果2019年11月24日發放的一年期貸款，到期日為2020年11月24日，兩個日期直接相減得到的卻又是366天。因為2020年是閏年，2月有29号。
那麼如果是2020年2月29日發放的1年期貸款，到期日是哪一天呢？2021年沒有2月29日，年和月對上了，日對不上，就要往後推1天，也就是2022年3月1日才是1年。兩個日期相減依然是366天。
三、G3301中剩餘期限1.5年是怎麼統計的？
如果我們知道除G21中1年是指360天外，其他報表剩餘期限1年或n年都是對年對月對日的話，那麼就可以得出G3301中剩餘期限1.5年實際是指1年零6個月，依然是對年對月對日。
比如，2019年6月30日統計剩餘期限區間為1年-1.5年(含)的貸款，2020年12月30日正好是1.5年，落在這一區間之中，2020年12月31日則要統計到1.5年-2年(含)這一區間了。
四、先算剩餘天數，再算剩餘期限
開始這一問題也讓我困擾了很久，首先想到的是2個日期相減得到剩餘天數，然後用if嵌套公式來計算。
=IF(T5<0,"未定期限",IF(AND(T5>=0,T5<=1),"次日",IF(AND(T5>1,T5<=7),"2日至7日",IF(AND(T5>7,T5<=30),"8日至30日",IF(AND(T5>30,T5<=90),"31日至90日",IF(AND(T5>90,T5<=360),"91日至1年",IF(AND(T5>360,T5<=1826),"1年至5年",IF(AND(T5>1826,T5<=3652),"5年至10年",IF(T5>3652,"10年以上")))))))))
注T5是兩個日期直接相減
但這麼算無法避開閏年的問題，比如剩餘期限10年，在不同的時間點計算，可能是3652天(中間有2個閏年)，也可能是3653天(中間有3個閏年)。
這樣算出來的，對年對月對日可能對不上，比如2019年6月30日至2029年6月30日相差3653天，2020年6月30日至2030年6月30日則是相差3652天。這個公式在剩餘期限每期都要做一些更新，而且在計算原始期限根本就沒法用。
五、直接用2個日期相減的excle公式
萬能的Excel肯定是有辦法的，我記得有個函數是可以計算2個日期間相差多少年、多少月、多少日的。
對，就是DATEDIF函數，DATEDIF(A，B，“d”)計算的是2個日期間的天數差；DATEDIF(A，B，“m”)計算的是2個日期間的月數差；DATEDIF(A，B，“y”)計算的是2個日期間的年數差；DATEDIF(A，B，“md”)計算的是2個日期間的日的差；DATEDIF(A，B，“ym”)計算的是2個日期間的月的差。
我們用後面3個函數先建構2個日期間相差多少年多少月多少日，對年對月對日就是1年0月0日就對上了，整好1年。
用公式去試驗2020年2月29日至2020年2月28日是11個月30天，不到1年；到2021年3月1日才是1年。
我們再插入1列，對計算出來的年、月、日3列做一些技術性處理，年×1+月×0.01+日×0.0001，這樣如果算出來是1，就是正好1年，算出來是1.06，就是1.5年。
這時候我們相當于在excel中增加了3列，但輸入2個日期還是得不到剩餘期限啊？這時候我們就想到是否可以用match函數來做排序，設定一些關鍵性節點，再用choose函數來設定對應值，以確定2個日期直接相減落在對應區間内。
比如在G0104中，我們做了一個這樣的函數。
=CHOOSE(MATCH(J2,{10000,1000,5,3,2,1,0.06,0.03,0.00009},-1),"活期","五年以上","三年至五年","兩年至三年","一年至兩年","六個月至一年","三個月至六個月","三個月以内","逾期")
因為是含上不含下，是以要做降序排列，Match_type取-1。如果是G18中含下不含上，則要做升序排序，Match_type取1。
Match_type：1 或省略，表示MATCH 函數會查找小于或等于 lookup_value 的最大值。lookup_array 參數中的值必須按升序排列，例如：...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。
Choose函數，其作用是在EXCEL中choose函數從參數清單中選擇并傳回一個值。這時候傳回值就可以把計算出來的剩餘天數固定在某一區間了。
注意5年以上我們使用的門檻值是1000，此外我們還設定了一個活期的門檻值10000，這時候要把活期存貸款的到期日調整為9999-01-01。1萬年後，銀行還在麼。。。。
同時還設定了0.00009這個逾期的門檻值，比如到期日正好在報告日，還沒有歸還的貸款實際就已經逾期了。小于0.0001，或者為負數的就是逾期貸款。
最後我們省略中間步驟，把間接公式合并成一個直接公式，就可以用2個日期相減直接得到剩餘期限的區間了。
=CHOOSE(MATCH((DATEDIF(D2,E2,"Y")+DATEDIF(D2,E2,"YM")*0.01+DATEDIF(D2,E2,"MD")*0.0001),{10000,1000,5,3,2,1,0.06,0.03,0.00009},-1),"活期","五年以上","三年至五年","兩年至三年","一年至兩年","六個月至一年","三個月至六個月","三個月以内","逾期")
是不是很簡單的一個公式。要注意的是不同報表剩餘期限各種各樣。是以還是要多做幾套公式，尤其是在G21裡面有2個不同的剩餘日期判斷方法。1年按360天，2年則又是對年對月對日，是以要先用if判斷剩餘期限是否超過360天，是的話就用上面公式，不是的話則用2個日期相減的天數來判斷。
=IF((E3-D3)>360,CHOOSE(MATCH((DATEDIF(D3,E3,"Y")
+DATEDIF(D3,E3,"YM")*0.01+DATEDIF(D3,E3,"MD")*0.0001),{10000,10,5},-1),"10年以上","5年至10年","1年至5年"),CHOOSE(MATCH((E3-D3),{360,90,30,7,1,0},-1),"91日至1年","31日至90日","8日至30日","2日至7日","次日","逾期"))
公式還是寫不好，那就加入下面的1104交流群吧，附不同報表剩餘期限計算公式。
加入1104全國交流群
1104愛好者4000+的社群
需下載下傳【企業微信】APP