編號 客戶姓名保額 保險日期 總保險費
A001 王舜生 50萬 109/1/1 3696
A002 林寵惠 60萬 109/3/5 2252
A003 李淑媛 60萬 108/11/20 5854
A004 黃大雄 60萬 108/5/23 9008
A005 黃瑞山 60萬 108/10/15 4278
A006 簡小媛 60萬 109/2/28 4504
A007 林一雄 60萬 109/5/6 3604
A008 陳福基 60萬 109/7/11 5854
A009 甘淑惠 50萬 108/9/3 616
A010 呂建三 50萬 109/8/7 616
A011 鄭五源 55萬 109/11/11 1430
A012 呂麗華 50萬 108/12/6 616
輸入條件:日期:108/5/23--109/2/28 保額:60萬
顯示如下
編號 客戶姓名 保額 保險日期 總保險費
A003 李淑媛 60萬 108/11/20 5854
A004 黃大雄 60萬 108/5/23 9008
A005 黃瑞山 60萬 108/10/15 4278
A006 簡小媛 60萬 109/2/28 4504
請教各位前輩用EXCEL公式如何達成,謝謝!
輸入之後,按Ctrl + Shift + Enter
請教達人,_t1_3,_t1_3[保額],_t1_3[西元日期],是什麼?,謝謝.
公式裡有A欄($A:$A)是何作用,謝謝.是否能傳檔案給我感恩.
dou10801@gmail.com
_t1_3,_t1_3[保額],_t1_3[西元日期],是什麼?
EXCEL的常用
標籤下,有一個格式化為表格
的按鈕。
這個按鈕可以將某一塊區域變為表格(Table
),並給予一個名稱(在這裡我賦予其名稱為_t1_3
)。_t1_3
,代表整張表格。_t1_3[保額]
,代表整個保額
欄位。_t1_3[西元日期]
,代表整個西元日期
欄位。
公式裡有A欄($A:$A)是何作用
在陣列公式中,我們可以用Row(A1:A10)
,產生一個從1到10的整數陣列。
這裡因為資料量是不固定的,因此上面的A10
是不確定的。
如果資料有100筆,就是A100
;如果資料有1000筆,就是A1000
;如果資料有N筆,就是A
N。
所以我們先用Counta(Index(_t1_3,,1))
計算資料筆數,然後用Index($A:$A,Counta(Index(_t1_3,,1)))
得出A
N,再套進Row(A1:Index($A:$A,Counta(Index(_t1_3,,1))))
得出一個從1到N的整數陣列。
其實這一段,可以用Indirect
函數或是Offset
函數改寫為比較簡單或直觀的公式,但是這二個函數都有Volatile的問題,當資料量大時會嚴重影響效能,所以我還是用Index
函數。
達人真的很抱歉,我對EXCEL只會皮毛,先感謝你詳細回覆,我照你的方法設定後,在格式化表格中,自訂已看到三個_t1_3,但執行後出現程式錯誤.I8=IFERROR(INDEX(_t1_3,SMALL(IFERROR(MATCH(_t1_3[保額],$I$3,0)*MATCH(_t1_3[西元日期],$J$3,1)*MATCH(_t1_3[西元日期],$K$3,-1)*ROW(_t1_3),""),ROW($A$1:INDEX($A:$A,COUNTA(INDEX(_t1_3,,1)),1)))-ROW(INDEX(_t1_3,1,1))+1,1),"")
自訂已看到三個_t1_3
甚麼意思?哪裡看到三個?
在工列=[常用]=[檥式]=[格式化為表格]=[自訂]=_t1_3,_t1_3[保額],_t1_3[西元日期]。
你的Excel版本是?
我的office 2016與office 360,在格式化為表格
並沒有自訂
2010
從下列網址可以看到,格式化為表格
並沒有自訂
https://blog.xuite.net/skhung/digilife/36158833-Excel+2007%2F2010+%E8%A1%A8%E6%A0%BC
1.我是將保額直接輸入數值。
2.K2公式:
=SUMPRODUCT(LARGE(($C$2:$C$13=600000)($D$2:$D$13>=42513)($D$2:$D$13<=43889)*($E$2:$E$13),ROW()-1))
再複製到K3:K13。
3.G2公式
=IFERROR(VLOOKUP($K2,IF({1,0},$E$2:$E$13,A$2:A$13),2,0),"")
再將公式複製到G2:J5
檔案:
https://drive.google.com/file/d/11DR0636jiMafgyqVIYmQ6oD_T9OvQAHT/view?usp=sharing