作者Jerome0511 (Jerome)
看板Office
标题[问题] 筛选搭配offset 取平均
时间Thu Jan 7 16:12:38 2016
(若是和其他不同软体互动之问题 请记得一并填写)
软体:excel
版本:2010
档案在此:
https://goo.gl/RlonPF
目前遇到的问题是随机筛选号码栏，但无法算平均
不知道是出现什麽问题，指令是用offset搭配SUBTOTAL
谢谢。
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 211.21.159.187
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Office/M.1452154361.A.D64.html
2F：→ Jerome0511: 数值有点怪 如果你把筛选全部展开之後平均值是19 01/08 11:56
5F：→ Jerome0511: 改完之後，若输入号码改3 往前抓一笔平均值变14是错的 01/08 13:43
8F：→ soyoso: 连结图片内在-後有再用括号包起来，原po新提供的连结没有 01/08 14:18
9F：→ Jerome0511: OK谢谢 请教一下有类似Averageif搭配subtotal的指令吗 01/08 16:36
10F：→ soyoso: 是要使用以函数averageif来写吗？ 01/08 18:24
11F：→ Jerome0511: 对啊 但一样要搭配筛选用 01/08 20:24
13F：→ Jerome0511: 不好意思没说清楚，我是想说有办法先筛选完号码之後 01/08 22:40
14F：→ Jerome0511: 再拉一个栏位的数值，当数值大於15其平均值为多少 01/08 22:41
16F：→ Jerome0511: (16+22)/2=19 01/08 22:47
18F：→ Jerome0511: 不好意思 你算的>15的平均值 我需要的是也需要在输入 01/11 11:01
19F：→ Jerome0511: 号码栏之後 完前抓一笔资料的平均值，非整体的 01/11 11:01
20F：→ Jerome0511: 也就是当我输入号码3 往前抓1笔资料，其avrageif要在 01/11 11:02
21F：→ Jerome0511: 此区间范围内 01/11 11:03
22F：→ soyoso: 那将">0"的条件换掉为">=1"，再加一个条件是"<=4" 01/11 11:46
23F：→ soyoso: 储存格当变数用连接符号& 01/11 11:46
24F：→ soyoso: 以回文连结条件，就会是(16+22)/2=19 01/11 11:48
26F：→ Jerome0511: 请问一下为什麽号码那一栏需要那麽多函式 用到 01/11 13:44
27F：→ Jerome0511: 用了IF、COUNTIF、SUBTOTAL 和只用SUBTOTAL有何差别? 01/11 13:45
28F：→ Jerome0511: 所以其实Averageif其实就自己有内建SUBTOTAL的功能吗? 01/11 13:46
29F：→ Jerome0511: 所以其实Averageif其实就自己有内建SUBTOTAL的功能吗? 01/11 13:47
30F：→ soyoso: 加上if、countif和只用subtotal的差别，原po可在其他储存 01/11 13:51
31F：→ soyoso: 格打上=i11，下拉10格储存格就可以看的出差别 01/11 13:51
32F：→ soyoso: subtotal的功能，这里的功能是指？ 01/11 13:56
33F：→ Jerome0511: if、countif和只用subtotal的差别，刚试过往下拉储存 01/11 14:27
※ 编辑: Jerome0511 (60.251.182.146), 01/11/2016 14:39:21
35F：→ Jerome0511: 大於15平均值的条件，算出来也怪怪的，以附件范例为例 01/11 14:40
36F：→ Jerome0511: 在想说averageif是不是也要用offset的写法 01/11 14:43
37F：→ Jerome0511: 因为subtotal的用法不是被筛选後的数值不会被考虑进去 01/11 14:44
38F：→ Jerome0511: 如果单纯只用averageif 那隐藏的数值不是会被算到吗? 01/11 14:45
40F：→ soyoso: 附件为例，条件是i11:i21>=1和i11:i21<=4符合为蓝框 01/11 16:39
42F：→ soyoso: 重覆之处为22,16的平均19，这非原po要的吗? 01/11 16:41
44F：→ Jerome0511: 但如果你把输入号码改为8平均值是错的可参考我上面贴 01/11 17:11
46F：→ Jerome0511: 所以Averageif会算到隐藏的连结，所以才问有没有 01/11 17:13
47F：→ Jerome0511: 类似Averageif搭配subtotal的指令 01/11 17:13
48F：→ soyoso: 输入号码改为8那公式一样是i11:i21>=1和i11:i21<=4吗？ 01/11 17:14
49F：→ soyoso: 公式内的1和4要当变数，於今天上午11:46回文就有写到 01/11 17:17
50F：→ soyoso: 而非只是打>=1和<=4这样 01/11 17:18
51F：→ Jerome0511: I11:I21,">=1",I11:I21,"<="&I3 这样是错的吗? 01/11 17:19
52F：→ soyoso: 公式有报错吗？我测试没有，所以语法没有不正确 01/11 17:22
53F：→ soyoso: 只是结果是否是原po要的而已 01/11 17:22
54F：→ soyoso: 原po输入号码为8，往前抓3笔为5，i11:i21的区间就要以这个 01/11 17:24
55F：→ soyoso: 为范围，如何产生">=5"，可用">="&i3-i5 01/11 17:25
56F：→ Jerome0511: Ok 没问题了 只是你回文有提到averagif 会算到隐藏值 01/12 08:58
57F：→ Jerome0511: 这个有解吗？ 01/12 08:58
58F：→ Jerome0511: 是不是加了">="&i3-i5 "<="&i3。 与j11:j21>我要的限 01/12 09:06
59F：→ Jerome0511: 制范围 就可以踢除隐藏数值直接算平均 01/12 09:06
60F：→ soyoso: averagifs要剔除隐藏数值，想到的是配合subtotal的i11:i20 01/12 11:05
61F：→ soyoso: 如有配合的话，如档案测试应可剔除隐藏数值算平均 01/12 11:13
63F：→ Jerome0511: SUBTOTAL 就可以剔除隐藏值 这是什麽原因? 01/12 11:24
64F：→ Jerome0511: 还有我想增加一栏数量用COUNTIFS写但会出现引数太少 01/12 11:25
65F：→ Jerome0511: 是什麽原因呢? 01/12 11:25
66F：→ soyoso: 以附件为例，i11:i20不就用到subtotal，为何回文写不需要 01/12 11:36
67F：→ soyoso: 用到subtotal呢？且公式averageifs内也有配合i11:i21 01/12 11:37
68F：→ soyoso: countifs写引数太少应表示，填写时省略必需要引数，例如有 01/12 11:40
69F：→ soyoso: 范围，却无条件 01/12 11:40
70F：→ Jerome0511: 哦 原来如此 subtotal 可以直接先用在i11:i21 average 01/12 12:07
71F：→ Jerome0511: if 那边可以不需要在写一次subtotal 会直接套用i11:i2 01/12 12:07
72F：→ Jerome0511: 1的subtotal 罗？ 01/12 12:07
73F：→ Jerome0511: 但我是直接把averagifs 那边的函式 後面的挑件原封不 01/12 12:10
74F：→ Jerome0511: 动 只改countifs应该没有判断不足的问题吧？ 01/12 12:10
75F：→ soyoso: 如原po所述，averageifs内就不用再写一次 01/12 12:13
76F：→ soyoso: averageifs改为countifs，那请将average_range的范围拿掉 01/12 12:17
77F：→ Jerome0511: OK感谢你。 01/12 16:56
78F：→ Jerome0511: 请问一下 想利用vlookup由数值反推号码 如果数值有两 01/13 14:38
79F：→ Jerome0511: 个一样的 那反推回来的号码会以先搜寻到的值为准 第二 01/13 14:38
80F：→ Jerome0511: 个一样数值的值反推号码会无法显示 有什麽办法解决吗 01/13 14:38
81F：→ Jerome0511: 第二个问题有办法利用vlookup 来限制 和我请教你的平 01/13 14:40
82F：→ Jerome0511: 均值一样的区间吗？ 有就是输入号码值往前推几笔的区 01/13 14:41
83F：→ Jerome0511: 间用vlookup 反推号码值 01/13 14:41
84F：→ soyoso: 以回文举例回传同号码第二笔的话，可用区间offset配合 01/13 16:00
85F：→ soyoso: match的方式 01/13 16:00
86F：→ Jerome0511: 回传写法VLOOKUP(M3,IF({1,0},J15:J24,I15:I24),2,0) 01/13 16:14
87F：→ Jerome0511: 所以要增加区间判别要改掉J15:J24,I15:I24这一段罗? 01/13 16:14
88F：→ soyoso: 测试上可修改j15:j24和i15:i24 01/13 16:31
90F：→ Jerome0511: 如附件的红色框框 想问一下 当如果往前抓的直改为7 01/13 20:34
91F：→ Jerome0511: 因为数值A有两笔是5，要怎麽把反推号码值 两个对应到 01/13 20:35
92F：→ Jerome0511: 的一起显示出来? 01/13 20:35
93F：→ soyoso: 抱歉不太了解，是指抓取两笔数值A为5，而显示数值B的14,24 01/13 20:57
94F：→ soyoso: 吗？ 01/13 20:57
95F：→ Jerome0511: 因为数值A有两个5当我的区间范围变大 反推回去的号码 01/13 21:31
96F：→ Jerome0511: 应该会有两笔资料号码是对应到数值A的5。但我目前写法 01/13 21:31
97F：→ Jerome0511: 只能抓到一笔号码 想在抓另一笔对应的号码要怎麽处理 01/13 21:31
98F：→ soyoso: 抱歉不太了解 01/13 21:54
99F：→ soyoso: 如是以数值A的5为条件将对应一笔以上的号码抓出的话，可以 01/13 21:58
100F：→ soyoso: index配合small+if的方式 01/13 21:58
101F：→ Jerome0511: 所以一对多 就不适合用vlookup吗？ 01/13 22:08
102F：→ soyoso: 1对2应可用vlookup，1对3以上用vlookup使用上我会加上辅助 01/13 22:11
103F：→ soyoso: 栏来抓前一笔的列号 01/13 22:11
105F：→ Jerome0511: 对应的号码应该要显示3与8 可以只用VLOOKUP就好? 01/13 22:21
107F：→ soyoso: offset和match 01/13 22:34
108F：→ soyoso: 第二笔为第一笔match的列号+1的范围来对应 01/13 22:48
109F：→ Jerome0511: -(MATCH(K3,I15:I24,0)-MATCH(K3-K5,I15:I24,0)-1) 01/13 23:34
110F：→ Jerome0511: 把+1 改-1吧? 01/13 23:34
111F：→ soyoso: 以连结内改-2可带出8 01/13 23:46
112F：→ Jerome0511: 突然发现这个列号+1的方法 有一个缺点就是当数值A连 01/14 08:57
113F：→ Jerome0511: 续两笔都是一样的值 回传的号码还是会一样的 01/14 08:57
114F：→ Jerome0511: 而且用MATCH如果遇到筛选 他所对应到的列数好像会跑掉 01/14 09:12
115F：→ soyoso: +1的方法，连续两笔一样，回传号码一样方面，不太了解原po 01/14 10:25
116F：→ soyoso: 的意思 01/14 10:25
117F：→ soyoso: 遇到筛选？原po在该问题vlookup上首次说到会用到筛选，所 01/14 10:26
118F：→ soyoso: 以该问题的回文并无考虑到这方面 01/14 10:27
119F：→ soyoso: 上面写到的该问题为原文01/13 14:38起01/14 08:57并无提及 01/14 10:30
120F：→ soyoso: 筛选方面 01/14 10:30
121F：→ Jerome0511: OK 好 所以如果加筛选条件 写法就要改罗? 01/14 11:34
122F：→ soyoso: 加筛选上，如要使用vlookup的话，我会加上辅助栏来针对数 01/14 11:43
123F：→ soyoso: 值A被隐藏的话则为空字串 01/14 11:44
124F：→ Jerome0511: 请问有大概的语法吗？ 01/14 18:49
125F：→ soyoso: 辅助栏写法同号码i15:i24的判断 01/15 01:14