作者abc9gad (鄉民愛發呆)
看板Office
標題[算表] 每個區間抓出最大/最小值並標示日期
時間Wed Mar 13 16:10:38 2019
軟體:excel
版本:2010
請問前輩，我想將表格中起始欄 "頭->結算"為一個區間
每個區間抓出最大與最小值，並且串入該值是星期幾的時候
請問應該要如何輸入 謝謝
https://reurl.cc/kr20d
--
Arguing on the Internet is like running in the Special Olympics: even if you
win, you're still retarded."
Jeriah
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 210.242.212.205
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1552464649.A.867.html
2F：→ soyoso: 篩選結算拖曳公式，low方面亦同 03/13 16:21
3F：→ soyoso: 看了一下周別24沒有"結算"，43l、55有二個"結算" 03/13 16:28
4F：→ soyoso: 這方面就要看原po實際要如何處理了 03/13 16:28
5F：→ soyoso: ^^^43 03/13 16:29
6F：→ abc9gad: 請問s大 我low的星期 用high的方式值變成1 不知道為什麼 03/13 16:45
7F：→ abc9gad: =VLOOKUP(I6,OFFSET(C$1,MATCH(E6,E:E,0)-1,,COUNTIF(E:E 03/13 16:45
8F：→ abc9gad: ,E6),3),3,0) 03/13 16:46
9F：→ abc9gad: 謝s大 那個週別結算的部分我再移除就好~不過431我沒看到 03/13 16:46
10F：→ abc9gad: 兩個結算~ 03/13 16:46
11F：→ soyoso: 回文不是有改為43嗎？ 03/13 16:53
12F：→ soyoso: 回傳1為vlookup回傳offset(..)範圍內左起第3欄，這個範圍 03/13 16:58
13F：→ soyoso: 第3欄為周別，要改為2 03/13 16:58
14F：→ abc9gad: 抱歉 我沒有注意到回文，照s大改的成功了感謝 來研究公式 03/13 17:10
15F：→ abc9gad: 如遇到疑問還請s大不吝指教 03/13 17:10
16F：→ abc9gad: s大我看出一個疑問，我想要的是他在每個周別HIGH與LOW 03/13 17:53
17F：→ abc9gad: 一起搜尋，例如周別1我要尋找MAX(B2:C6) MIN(B2:C6) 03/13 17:53
18F：→ abc9gad: 剛剛嘗試修改offset失敗，對這個函數還不是很懂 03/13 17:54
我將High跟Low改為=MAX/MIN(OFFSET(B$1,MATCH(E6,E:E,0)-1,,COUNTIF(E:E,E6),2))
看起來是可以抓出範圍內的最大與最小值，不過變成我用Vlookup抓星期會抓不出來
因為搜尋條件變成兩欄
ex:如果剛好B欄的High是這個範圍的最小值，那我就抓不出是星期幾了
※ 編輯: abc9gad (150.116.196.163), 03/13/2019 18:57:40
19F：→ soyoso: 如是b:c欄的話offset(b$1則改為b$1:c$1或是width的部分改 03/13 18:55
20F：→ soyoso: 為2 03/13 18:55
21F：→ soyoso: 1.可以vlookup()&vlookup來搜尋b欄和c欄，再回傳周別 03/13 19:05
22F：→ soyoso: 因可能回傳錯誤值可配合iferror 03/13 19:06
24F：→ soyoso: k6、L6為輔助用，如不要的話就寫入公式內 03/13 19:08
25F：→ soyoso: 思考了一下最小值有可能出現在high嗎？如果有可能的話那最 03/13 19:36
26F：→ soyoso: 小值在high的low就不可能比它小了吧，那high-low就應會有 03/13 19:36
27F：→ soyoso: 負數，但看了這5200多筆沒一筆符合的，如果high一定大於等 03/13 19:36
28F：→ soyoso: 於low的話那最小值是否就看low那一欄就好 03/13 19:36
29F：→ soyoso: high一定大於等於low的話max和min應也可只判斷high和low的 03/13 19:42
30F：→ soyoso: 部分 03/13 19:42
S大 如您所說，我剛也想了一下上述問題是不會發生的
但我還是滿好奇如果範圍要vlookup要如何使用
能否詳細說明1.vlookup()&vlookup的使用方法呢 感激不盡
※ 編輯: abc9gad (150.116.196.163), 03/13/2019 19:47:52
請問S大，我照你所寫用了第二個方法結果會出現錯誤 請問是為什麼呢
https://reurl.cc/13VNX
※ 編輯: abc9gad (210.242.212.205), 03/14/2019 13:11:32
32F：→ soyoso: 如連結內所寫為陣列公式，需在公式列加上組合鍵 03/14 13:53
33F：→ soyoso: 如果原po有按的話，公式前後會出現大括號，而回文內連結並 03/14 13:54
→ soyoso: 無看到
謝S大，不好意思想再請問MAX後面*ROW(OFFSET(B$1,K6,,L6))這段的意思是什麼 03/14 13:54
※ 編輯: abc9gad (210.242.212.205), 03/14/2019 16:56:30
34F：→ soyoso: 取得offset(..)這個範圍的列號 03/14 17:07
=INDEX(D:D,MAX((OFFSET(B$1,K6,,L6,2)=I6)*ROW(OFFSET(B$1,K6,,L6))))
拆解後還是無法理解
=INDEX抓出範圍(D欄,MAX(OFFSET=I6)會變TRUE*ROW(OFFSET)=2
INDEX{(D欄,MAX(TRUE*2)} 怎麼判斷的出是週一 ><
※ 編輯: abc9gad (210.242.212.205), 03/14/2019 17:43:03
35F：→ soyoso: offset(..)=i6為真true，為假false，在運算時true為1， 03/14 17:50
36F：→ soyoso: false為0，乘於列號，比如k1帶入1，L6帶入5，列號就是2;3; 03/14 17:51
37F：→ soyoso: 4;5;6，再max抓出最大值 03/14 17:52
也就是說會變成MAX(1)*ROW(OFFSET範圍B2:B6=2,3,4,5,6) 抓出最大值是6這樣嗎
感覺不對...因為拆解MAX後面的公式會變4，就是答案的週一 但還是不明白4怎麼算出來的
抱歉S大真的很想了解 怕把您問煩了
※ 編輯: abc9gad (210.242.212.205), 03/14/2019 18:12:16
38F：→ soyoso: 是false,false;false,false;false,true...乘於2;3;4;5;6 03/14 18:16
39F：→ soyoso: 運算時false為0，true為1，false為0，乘於任何列號都為0 03/14 18:18
40F：→ soyoso: true*4=4，以這個範圍來看就一筆符合，max就回傳4 03/14 18:18
41F：→ soyoso: index(d:d,4)回傳儲存格d4資料 03/14 18:20
謝s大，大致上明白了!! 那請問後面)*ROW(OFFSET(B$1,K6,,L6)
只是為了要算出他有幾列，如果改成C$1也是正確的對吧? (剛試過日期沒變)
另外一個問題是前面(OFFSET(B$1,K6,,L6,2)=I6因為OFFSET尋找B2:C6中，找到I6是第三列
那如果我想找的是I6是在哪一欄 是將後面的ROW改成COLUMN嗎
※ 編輯: abc9gad (150.116.196.163), 03/14/2019 23:42:22
42F：→ soyoso: 說法不太一樣，不是算出他有幾列，這樣的說法像是這個範圍 03/15 00:01
43F：→ soyoso: 有多少列數。但這是要回傳這個範圍內的列號 03/15 00:02
44F：→ soyoso: 改為c$1方面如原po所述 03/15 00:03
45F：→ soyoso: 哪一欄的話，如原po所述，改為column，就不用offset的部分 03/15 00:08
46F：→ soyoso: 只要b:c 03/15 00:08
47F：→ soyoso: 如要回傳high或low的話， 儲存格範圍則改為第1列 03/15 00:14
48F：→ abc9gad: 明白了!!感謝s大 03/15 12:02
突然想到一個問題想問S大，雙VLOOK的方式只要用IFERROR再用&連接
就可以搜尋兩欄以上嗎，所以這種方式也可以用三個以上嘛
※ 編輯: abc9gad (210.242.212.205), 03/15/2019 14:02:11
49F：→ soyoso: 應如原po所述 03/15 14:44
謝謝，不過好奇想問為什麼要用IFERROR，我剛試沒用IFERROR就會是錯誤
不太懂為什麼
※ 編輯: abc9gad (210.242.212.205), 03/15/2019 14:52:25
50F：→ soyoso: vlookup未搜尋到時回傳錯誤值#n/a，直接連接錯誤值的話還 03/15 15:17
51F：→ soyoso: 是回傳錯誤值，因此用iferror來調整 03/15 15:17
52F：→ abc9gad: 明白了 感謝s大 03/15 16:16