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

excel

dou10801 2020-01-16 16:12:20 ‧ 9021 瀏覽

分享至

編號客戶姓名保額保險日期總保險費
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 }} |

直播中

2 個回答

paicheng0111

iT邦大師 5 級 ‧ 2020-01-16 17:12:16

最佳解答

進階篩選

參考資料:https://support.office.com/zh-tw/article/%E4%BD%BF%E7%94%A8%E9%80%B2%E9%9A%8E%E6%BA%96%E5%89%87%E9%80%B2%E8%A1%8C%E7%AF%A9%E9%81%B8-4c9222fe-8529-4cd7-a898-3f16abdff32b

陣列公式

輸入之後，按Ctrl + Shift + Enter

回應 12
分享
檢舉

看更多先前的回應...收起先前的回應...

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

paicheng0111 iT邦大師 5 級 ‧ 2020-01-17 15:16:12 檢舉

_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),"")

paicheng0111 iT邦大師 5 級 ‧ 2020-01-17 18:08:20 檢舉

自訂已看到三個_t1_3

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

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

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

paicheng0111 iT邦大師 5 級 ‧ 2020-01-20 16:24:51 檢舉

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

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

2010

paicheng0111 iT邦大師 5 級 ‧ 2020-01-20 17:32:59 檢舉

從下列網址可以看到，格式化為表格並沒有自訂
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[西元日期],如何設定(或定義),因沒用過網路也查不到,感恩.

paicheng0111 iT邦大師 5 級 ‧ 2020-01-22 08:47:42 檢舉

https://www.google.com/search?q=%E6%A0%BC%E5%BC%8F%E5%8C%96%E7%82%BA%E8%A1%A8%E6%A0%BC&rlz=1C1GCEU_zh-TWTW819TW820&oq=%E6%A0%BC%E5%BC%8F%E5%8C%96%E7%82%BA%E8%A1%A8%E6%A0%BC&aqs=chrome..69i57&sourceid=chrome&ie=UTF-8

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

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

登入發表回應

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://drive.google.com/file/d/11DR0636jiMafgyqVIYmQ6oD_T9OvQAHT/view?usp=sharing

回應 2
分享
檢舉

paicheng0111 iT邦大師 5 級 ‧ 2020-01-18 11:41:59 檢舉

條件若由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

登入發表回應