PTT數位生活區 / Office

[算表] 每個區間抓出最大/最小值並標示日期

看板Office作者 (鄉民愛發呆)時間5年前 (2019/03/13 16:10), 5年前編輯推噓0(0推 0噓 52→)
留言52則, 2人參與, 5年前最新討論串1/1
軟體: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://www.ptt.cc/bbs/Office/M.1552464649.A.867.html
→
03/13 16:20, 5年前 , 1F
類似這樣
03/13 16:20, 1F
→
03/13 16:21, 5年前 , 2F
篩選結算拖曳公式，low方面亦同
03/13 16:21, 2F
→
03/13 16:28, 5年前 , 3F
看了一下周別24沒有"結算"，43l、55有二個"結算"
03/13 16:28, 3F
→
03/13 16:28, 5年前 , 4F
這方面就要看原po實際要如何處理了
03/13 16:28, 4F
→
03/13 16:29, 5年前 , 5F
^^^43
03/13 16:29, 5F
→
03/13 16:45, 5年前 , 6F
請問s大 我low的星期 用high的方式值變成1 不知道為什麼
03/13 16:45, 6F
→
03/13 16:45, 5年前 , 7F
=VLOOKUP(I6,OFFSET(C$1,MATCH(E6,E:E,0)-1,,COUNTIF(E:E
03/13 16:45, 7F
→
03/13 16:46, 5年前 , 8F
,E6),3),3,0)
03/13 16:46, 8F
→
03/13 16:46, 5年前 , 9F
謝s大 那個週別結算的部分我再移除就好~不過431我沒看到
03/13 16:46, 9F
→
03/13 16:46, 5年前 , 10F
兩個結算~
03/13 16:46, 10F
→
03/13 16:53, 5年前 , 11F
回文不是有改為43嗎？
03/13 16:53, 11F
→
03/13 16:58, 5年前 , 12F
回傳1為vlookup回傳offset(..)範圍內左起第3欄，這個範圍
03/13 16:58, 12F
→
03/13 16:58, 5年前 , 13F
第3欄為周別，要改為2
03/13 16:58, 13F
→
03/13 17:10, 5年前 , 14F
抱歉 我沒有注意到回文，照s大改的成功了感謝 來研究公式
03/13 17:10, 14F
→
03/13 17:10, 5年前 , 15F
如遇到疑問還請s大不吝指教
03/13 17:10, 15F
→
03/13 17:53, 5年前 , 16F
s大我看出一個疑問，我想要的是他在每個周別HIGH與LOW
03/13 17:53, 16F
→
03/13 17:53, 5年前 , 17F
一起搜尋，例如周別1我要尋找MAX(B2:C6) MIN(B2:C6)
03/13 17:53, 17F
→
03/13 17:54, 5年前 , 18F
剛剛嘗試修改offset失敗，對這個函數還不是很懂
03/13 17:54, 18F
我將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
→
03/13 18:55, 5年前 , 19F
如是b:c欄的話offset(b$1則改為b$1:c$1或是width的部分改
03/13 18:55, 19F
→
03/13 18:55, 5年前 , 20F
為2
03/13 18:55, 20F
→
03/13 19:05, 5年前 , 21F
1.可以vlookup()&vlookup來搜尋b欄和c欄，再回傳周別
03/13 19:05, 21F
→
03/13 19:06, 5年前 , 22F
因可能回傳錯誤值可配合iferror
03/13 19:06, 22F
→
03/13 19:06, 5年前 , 23F
方式
03/13 19:06, 23F
→
03/13 19:08, 5年前 , 24F
k6、L6為輔助用，如不要的話就寫入公式內
03/13 19:08, 24F
→
03/13 19:36, 5年前 , 25F
思考了一下最小值有可能出現在high嗎？如果有可能的話那最
03/13 19:36, 25F
→
03/13 19:36, 5年前 , 26F
小值在high的low就不可能比它小了吧，那high-low就應會有
03/13 19:36, 26F
→
03/13 19:36, 5年前 , 27F
負數，但看了這5200多筆沒一筆符合的，如果high一定大於等
03/13 19:36, 27F
→
03/13 19:36, 5年前 , 28F
於low的話那最小值是否就看low那一欄就好
03/13 19:36, 28F
→
03/13 19:42, 5年前 , 29F
high一定大於等於low的話max和min應也可只判斷high和low的
03/13 19:42, 29F
→
03/13 19:42, 5年前 , 30F
部分
03/13 19:42, 30F
S大 如您所說，我剛也想了一下上述問題是不會發生的 但我還是滿好奇如果範圍要vlookup要如何使用 能否詳細說明1.vlookup()&vlookup的使用方法呢 感激不盡 ※ 編輯: abc9gad (150.116.196.163), 03/13/2019 19:47:52
→
03/13 19:51, 5年前 , 31F
將原本的公式以&連接
03/13 19:51, 31F
請問S大，我照你所寫用了第二個方法結果會出現錯誤 請問是為什麼呢 https://reurl.cc/13VNX ※ 編輯: abc9gad (210.242.212.205), 03/14/2019 13:11:32
→
03/14 13:53, 5年前 , 32F
如連結內所寫為陣列公式，需在公式列加上組合鍵
03/14 13:53, 32F
→
03/14 13:54, 5年前 , 33F
如果原po有按的話，公式前後會出現大括號，而回文內連結並
03/14 13:54, 33F
→ soyoso: 無看到 謝S大，不好意思想再請問MAX後面*ROW(OFFSET(B$1,K6,,L6))這段的意思是什麼 03/14 13:54 ※ 編輯: abc9gad (210.242.212.205), 03/14/2019 16:56:30
→
03/14 17:07, 5年前 , 34F
取得offset(..)這個範圍的列號
03/14 17:07, 34F
=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
→
03/14 17:50, 5年前 , 35F
offset(..)=i6為真true，為假false，在運算時true為1，
03/14 17:50, 35F
→
03/14 17:51, 5年前 , 36F
false為0，乘於列號，比如k1帶入1，L6帶入5，列號就是2;3;
03/14 17:51, 36F
→
03/14 17:52, 5年前 , 37F
4;5;6，再max抓出最大值
03/14 17:52, 37F
也就是說會變成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
→
03/14 18:16, 5年前 , 38F
是false,false;false,false;false,true...乘於2;3;4;5;6
03/14 18:16, 38F
→
03/14 18:18, 5年前 , 39F
運算時false為0，true為1，false為0，乘於任何列號都為0
03/14 18:18, 39F
→
03/14 18:18, 5年前 , 40F
true*4=4，以這個範圍來看就一筆符合，max就回傳4
03/14 18:18, 40F
→
03/14 18:20, 5年前 , 41F
index(d:d,4)回傳儲存格d4資料
03/14 18:20, 41F
謝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
→
03/15 00:01, 5年前 , 42F
說法不太一樣，不是算出他有幾列，這樣的說法像是這個範圍
03/15 00:01, 42F
→
03/15 00:02, 5年前 , 43F
有多少列數。但這是要回傳這個範圍內的列號
03/15 00:02, 43F
→
03/15 00:03, 5年前 , 44F
改為c$1方面如原po所述
03/15 00:03, 44F
→
03/15 00:08, 5年前 , 45F
哪一欄的話，如原po所述，改為column，就不用offset的部分
03/15 00:08, 45F
→
03/15 00:08, 5年前 , 46F
只要b:c
03/15 00:08, 46F
→
03/15 00:14, 5年前 , 47F
如要回傳high或low的話， 儲存格範圍則改為第1列
03/15 00:14, 47F
→
03/15 12:02, 5年前 , 48F
明白了!!感謝s大
03/15 12:02, 48F
突然想到一個問題想問S大，雙VLOOK的方式只要用IFERROR再用&連接 就可以搜尋兩欄以上嗎，所以這種方式也可以用三個以上嘛 ※ 編輯: abc9gad (210.242.212.205), 03/15/2019 14:02:11
→
03/15 14:44, 5年前 , 49F
應如原po所述
03/15 14:44, 49F
謝謝，不過好奇想問為什麼要用IFERROR，我剛試沒用IFERROR就會是錯誤 不太懂為什麼 ※ 編輯: abc9gad (210.242.212.205), 03/15/2019 14:52:25
→
03/15 15:17, 5年前 , 50F
vlookup未搜尋到時回傳錯誤值#n/a，直接連接錯誤值的話還
03/15 15:17, 50F
→
03/15 15:17, 5年前 , 51F
是回傳錯誤值，因此用iferror來調整
03/15 15:17, 51F
→
03/15 16:16, 5年前 , 52F
明白了 感謝s大
03/15 16:16, 52F
‣ 更多 abc9gad 的文章
文章代碼(AID): #1SYBi9Xd (Office)
Office 近期熱門文章
更多 近期熱門文章 >>
PTT數位生活區 即時熱門文章
更多 即時熱門文章 >>
‣ 更多 abc9gad 的文章
文章代碼(AID): #1SYBi9Xd (Office)