作者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/cn.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