iT邦幫忙

1

EXCEL 符合區間日期和品項,顯示資料

  • 分享至 

  • xImage

編號 客戶姓名保額 保險日期 總保險費
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公式如何達成,謝謝!

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

1
paicheng0111
iT邦大師 5 級 ‧ 2020-01-16 17:12:16
最佳解答
看更多先前的回應...收起先前的回應...
dou10801 iT邦新手 5 級 ‧ 2020-01-17 12:49:57 檢舉

請教達人,_t1_3,_t1_3[保額],_t1_3[西元日期],是什麼?,謝謝.

dou10801 iT邦新手 5 級 ‧ 2020-01-17 13:20:54 檢舉

公式裡有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筆,就是AN。
所以我們先用Counta(Index(_t1_3,,1))計算資料筆數,然後用Index($A:$A,Counta(Index(_t1_3,,1)))得出AN,再套進Row(A1:Index($A:$A,Counta(Index(_t1_3,,1))))得出一個從1到N的整數陣列。

其實這一段,可以用Indirect函數或是Offset函數改寫為比較簡單或直觀的公式,但是這二個函數都有Volatile的問題,當資料量大時會嚴重影響效能,所以我還是用Index函數。

dou10801 iT邦新手 5 級 ‧ 2020-01-17 17:13:19 檢舉

達人真的很抱歉,我對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

甚麼意思?哪裡看到三個?

dou10801 iT邦新手 5 級 ‧ 2020-01-20 13:13:05 檢舉

在工列=[常用]=[檥式]=[格式化為表格]=[自訂]=_t1_3,_t1_3[保額],_t1_3[西元日期]。

你的Excel版本是?
我的office 2016與office 360,在格式化為表格並沒有自訂

dou10801 iT邦新手 5 級 ‧ 2020-01-20 16:30:58 檢舉

2010

從下列網址可以看到,格式化為表格並沒有自訂
https://blog.xuite.net/skhung/digilife/36158833-Excel+2007%2F2010+%E8%A1%A8%E6%A0%BC

dou10801 iT邦新手 5 級 ‧ 2020-01-22 08:08:19 檢舉

請教達人,_t1_3,_t1_3[保額],_t1_3[西元日期],如何設定(或定義),因沒用過網路也查不到,感恩.

dou10801 iT邦新手 5 級 ‧ 2020-01-30 08:54:28 檢舉

還是不太懂,因業務忙,但會好好學習,感謝指導.

0
ccenjor
iT邦大師 9 級 ‧ 2020-01-17 22:24:59

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://ithelp.ithome.com.tw/upload/images/20200117/20109881Ucd98bIRQ3.png
檔案:
https://drive.google.com/file/d/11DR0636jiMafgyqVIYmQ6oD_T9OvQAHT/view?usp=sharing

條件若由60萬改為50萬,這個寫法會出錯。
因為不只一個人的總保費為616。

ccenjor iT邦大師 9 級 ‧ 2020-01-20 20:13:42 檢舉

沒錯,若有重覆的數值會出錯,必須將G2公式變為
=IFERROR(OFFSET(A$1,SUMPRODUCT(LARGE(($C$2:$C$13=500000)($D$2:$D$13>=42513)($D$2:$D$13<=43889)*ROW($E$2:$E$13),ROW()-1))-1,,,),"")
再複製到G2:K13

我要發表回答

立即登入回答