作者AirRider (又怎麼了)
看板Office
標題[算表] 取出最近且小於/大於的數字?
時間Wed Oct 17 12:59:10 2018
軟體: excel
版本: 2016
兩序列數字
想要從特定位置開始
按照順序,由左至右
找出第一個小於對應位置的數
工作表1
A B C D E F G H I J K L
1 2 4 6
8 10 12 14
4 4.9 10 12 14
工作表2
A B C D E F G H I J K L
1 2 3 4
5 6 7 8
9 10 11 12 13
起始 工作表2 D1格
要從 工作表1 D1 開始往右找
找出第一個 小於或等於 工作表1 與工作表2 對應位置 的值
EX
工作表1 D1 =8 大於 工作表2 D1 = 5 (X)
E1 =10 大於 E1 = 6 (X)
F1 =12 大於 F1 = 7 (X)
G1 =14 大於 G1 = 8 (X)
H1 =4 小於 H1 = 9 (O) GET!第一個小於
I1 =4.9 小於 I1 = 10 (X) 不是第一個小於
答案為 工作表1 H1的4
(回傳值為工作表1對應位置的內容)
但如果用match會找到4.9(最近且小於)
請問該如何找出此值?
問題2
續前問
若工作表1與2並未完全映射(mapping)於工作表的對應位置
------------->
工作表1 E F G H I J K L
2
2 4 6 8
4 5 8 10
------------->
工作表2 M N O P Q R S
8
1 2 3 4
5 6 7
要從 工作表1的E2格 與 工作表2的M8格 由左往右 開始
最終取出I2格的4
該如何找呢?
感恩眾多熱心前輩高手協助,受助甚多,感激不盡
update : 修正問題二, 為 由左往右
問題3
抱歉,延伸上面兩問,對於參照仍有不瞭解之處還請指導
現在想從工作表3的來控制想取得的值
工作表1
A B C D E F G H I J K L M
1 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112
2 小明 2 4 6 8 10 12 14 4 4.9 10 12 14
3 小華 3 5 7 9 11 13 15 17 3 8 6 4
工作表2
A B C D E F G H I J K L M
1 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112
2 小明 2 3 4 5 6 7 8 9 10 11 12 13
3 小華 1 3 5 7 9 11 13 15 17 15 13 11
工作表3
A B C D
1 小明 1104 ????
2
工作表3之C1為其值回傳的位置
按照前問的作法
改寫後會是
{=OFFSET(D2,,MATCH(TRUE,工作表1!E2:M2<工作表2!E2:M2,0))}
已然實做,這部分沒有問題
若比較之起始位置 E2 想要用工作表3之A1,B1分別決定
且如果M行之後還有很多很多行
無法事先得知其範圍
僅知道開始比較的出發點E2
該如何改寫呢?
抱歉問題有點多,尚祈眾師傅指導
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 114.34.235.142
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1539752353.A.EA1.html
※ 編輯: AirRider (114.34.235.142), 10/17/2018 13:00:05
2F：→ soyoso: 問題2 同上寫法，改一下範圍 10/17 13:09
4F：→ soyoso: 另外箭頭方向往右，應是由左往右，和原文寫的由右往左不同 10/17 13:11
※ 編輯: AirRider (114.34.235.142), 10/17/2018 13:43:41
5F：→ AirRider: 感謝,的確是由左往右,方向都是一樣的 10/17 13:43
6F：→ AirRider: 抱歉,請問一下,這兩個工作表並未在一起,要如何改呢?? 10/17 23:38
7F：→ AirRider: 範圍的地方用 工作表1!D1:L1<工作表2!D1:L1 取不出東西 10/17 23:41
8F：→ soyoso: 未在一起指的是？不在同一個活頁簿內？ 10/17 23:46
9F：→ soyoso: 以回文的方式，如是在同一個活頁簿，而表格是在不同工作表 10/17 23:46
10F：→ soyoso: 內，如回文寫的工作表1、工作表2的話 10/17 23:47
12F：→ AirRider: 我在工作表3 {=MATCH(TRUE,工作表1!省略<工作表2!省略,0 10/18 08:36
13F：→ AirRider: ctrl shift enter仍然是該命令列沒有回傳值..... 10/18 08:36
14F：→ AirRider: 完整公式是{=OFFSET(起始點,MATCH(...),MATCH(TRUE..))} 10/18 08:40
15F：→ AirRider: 前面那段MATCH可以正常偏移(後面true那段用正整數帶入ok 10/18 08:41
16F：→ AirRider: 但用MATCH(TRUE,...) 替換掉後就沒有回傳了 10/18 08:41
17F：→ soyoso: 回文列偏移也有用match，但不知要尋找條件和欄位為何，就 10/18 08:48
18F：→ soyoso: 模擬，搜尋2於原文所寫到的a欄內 10/18 08:48
20F：→ AirRider: 啊啊啊沒事我耍蠢了,正常call出要的東西了,感恩不盡!!! 10/18 08:51
21F：→ AirRider: 非常感謝,我知道我哪裡做錯了 10/18 08:51
※ 編輯: AirRider (114.34.235.142), 10/18/2018 13:23:54
22F：→ AirRider: 抱歉,又新增一問,懇請指導.... 10/18 13:24
※ 編輯: AirRider (114.34.235.142), 10/18/2018 13:25:15
※ 編輯: AirRider (114.34.235.142), 10/18/2018 13:26:06
24F：→ soyoso: 範圍會從搜尋到的欄號至最右側欄xfd，如覺得不需要或執行 10/18 13:45
25F：→ soyoso: 上效率不好的話，可改一個較大或以函數count或counta來計 10/18 13:46
26F：→ soyoso: 數日期列(如連結來看為第1列)，來做為offset範圍的欄數 10/18 13:47
27F：→ AirRider: 學習了,感謝您的回覆,正在試做中 10/18 14:24
28F：→ AirRider: 感謝,已經試做成功,感謝您的熱心指導 10/20 16:16