返回列表 上一主題 發帖

滿足時間範圍內且指定條件的資料排序~矩陣公式修正

i723

帖子
1 
主題
1 
精華
0 
積分
2 
點名
0  
作業系統
windows 
軟體版本
10 
閱讀權限
10 
註冊時間
2016-2-14 
最後登錄
2016-2-15 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2016-2-14 12:51 | 只看該作者

滿足時間範圍內且指定條件的資料排序~矩陣公式修正

excel 資料庫中
A欄位是時間
B欄位是品名
C欄位是件數
D欄位是廠商

今天於E1, F1, G1, H1, I1四格子
分別填入起始時間, 結束時間, 指定品名, 指定件數, 指定廠商

我原本的構想:
在J1填入公式讓其回傳符合以上指定項目且時間最小的列
={MOD(SMALL(ABS($E$1*10000-IFERROR(FIND(G1,B:B)*FIND(H1,C:C)*FIND(I1,D:D)*A:A,100000)*10000)*10000+ROW(A:A),1),1000)}

J2也是同樣公式, 只要更改SMALL的RANK為2, J3為3, J4為4.....以此類推

這樣我就可以從J欄得知符合條件的資料在哪一列, 並由指定時間小到大排列了

可是得到的結果卻是在時間範圍外的資料也會進來(好像是絕對值函數ABS的關係....) 請問高手知道如何修改公式嗎?

另外有許多品名的件數(C欄)是填入文字或者為0的, 這些結果我也不想列入陣列比對, 該怎麼加入判別式呢? 感激
收藏 分享

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
2#
發表於 2016-2-14 21:11 | 只看該作者
回復 1# i723

=MOD(SMALL(IF((A$2:A$999>=E$1)*(A$2:A$999<=F$1)*(B$2:B$999=G$1)*(C$2:C$999=H$1)*(D$2:D$999=I$1),ROUND(A$2:A$999*10^9,-5)+ROW($2:$999)),ROW(A1)),10^5)
陣列公式
可下拉

或將A、B、C、D定義名稱
=MOD(SMALL(IF((時間>=E$1)*(時間<=F$1)*(品名=G$1)*(件數=H$1)*(廠商=I$1),ROUND(時間*10^9,-5)+ROW(時間)),ROW(A1)),10^5)
陣列公式
可下拉


大概是這樣吧
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

        靜思自在 : 要用心，不要操心、煩心。
返回列表 上一主題