作者rafael750626 (一休)
看板Office
标题[算表] Excel 末10列有效资料撷取问题
时间Sat Nov 26 08:47:01 2022
软体:Microsoft Excel
版本: 家用版 2016
资料样式范例
https://imgur.com/a/2pz4KHC
Date 表示测试日期
RoomA/RoomB/RoomC 表示不同测试区域
B ~ D栏的数字为测试结果，空白储存格是该次未做检测的意思。
日期相同表示同一日期不同次测试。
主要问题
想请教各位板友。如果今天我要撷取
「从第x列往前计算，最後10个有测试样本的滚动阳性率」 (测试结果>=0的比例)，
该如何设计式子呢？
举例：
Room A 的B13储存格：撷取 B1 ~ B13格的资料算阳性率。
Room A 的B36储存格：撷取 B25 ~ B36格的资料算阳性率。
Room B 的C25储存格：撷取 C13 ~ C25格的资料算阳性率。
Room C 的D18储存格：撷取 D7 ~ D18格的资料算阳性率。
附带条件
不要用筛选功能，因为测试区域不只一个，希望能并排比较。
尝试与困难
之前尝试像OFFSET，MATCH等函数都会遇到一个共同的问题。
因为要定义「非空白的最後10笔资料」在公式设计上就会撞墙。
因为
范围是可变的，
不能直接写OFFSET然後向上10格 (会包含空白)
用单一的IF函数往上推更多资料进入选取区域也可能会遇到更多空白，解法并不完美。
怎麽查资料都查不到一个好的写法。
想就这个部分跟各位大大请益。
感谢。
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 223.138.104.138 (台湾)
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Office/M.1669423623.A.CCF.html
1F：推 kinomon: 我会考虑做辅助栏呈现非空白的累计个数 11/26 16:50
2F：→ kinomon: 例如RoomA辅助栏 J20= row(19:19)-countif(A$2:A20, “” 11/26 16:57
3F：→ kinomon: ) 11/26 16:57
4F：→ kinomon: 写错 J20= row(19:19)-countif(B$2:B20, “”) 11/26 16:58
5F：→ kinomon: J20算出来是15 往前追溯前十就是用match找J栏为6的位置 11/26 17:02
6F：→ kinomon: 不过我只是粗略想的 应该还有比较简洁的解法 11/26 17:04
辅助列吗？我思考看看...
7F：推 newacc: 觉得应该可以用INDEX、LARGE、IF的阵列公式处理 11/26 19:59
8F：→ newacc: 判断非空白回传列号，抓列号最大的10个喂给INDEX 11/26 19:59
9F：→ newacc: 不过我电脑送修要上班日才能帮你写写看了 11/26 19:59
用LARGE加上IF抓出列号前十大的吗？感觉是一个突破口，周一有空我也思考看看。
※ 编辑: rafael750626 (122.121.11.180 台湾), 11/26/2022 22:35:29
10F：推 windknife18: 以B36为例算第几列 11/27 21:24
11F：→ windknife18: =LARGE(IF($B$1:B36<>"",ROW($B$1:B36)),10) 11/27 21:25
13F：→ newacc: 不过google在处理公式的逻辑好像跟excel不太一样 11/29 16:31
14F：→ newacc: 请下载下来用excel开，计算才会是对的 11/29 16:31
太感谢了！公式在下载後看起来运作良好。
我现在只剩下想办法看懂里面的内容，然後跟同事简报这样@@
1. SUMPRODUCT与IF函数结合後，是透过"--" 与 "{}"才转换成数字的吗？
2. 此外，在其中一组ROW函数的($1:$10)如何刚好选到最後10列？
3. SUMPRODUCT(...)独自输出只有0, 1两种数字。相除後却可以变成比例？
我目前正在查资料。如果有想要解惑也欢迎。
感谢newacc大大~~
※ 编辑: rafael750626 (203.68.96.125 台湾), 11/30/2022 17:17:25
※ 编辑: rafael750626 (203.68.96.125 台湾), 11/30/2022 17:17:58
15F：推 kinomon: 评估值公式可以看公式的运作 {}是阵列 11/30 19:21
16F：→ kinomon: Row($1:$10)就是{1;2;3;4;5;6;7;8;9;10} 11/30 19:24
18F：→ kinomon: 找了找这篇对岸文章写得比较浅显 11/30 19:32
newacc大大抱歉，刚才在测试下载的excel档案後发现一个问题。
刚下载的时候ok，可是尝试动到B:D排的原始资料时，
F:H的结果就会变成0或者1 ("不足10例"储存格内容不变)
请问这是excel版本的问题吗 (毕竟是从google试算表转换而来)？
抱歉又需要再请大大协助了orz
※ 编辑: rafael750626 (203.68.96.125 台湾), 12/01/2022 15:09:58
19F：→ newacc: 结果变成0或1应该是被当作阵列公式处理了，试试看编辑F2 12/02 11:38
20F：→ newacc: 直接按Enter完成公式，再从F2复制到其他格看看 12/02 11:39
21F：→ newacc: 加成压缩档，应该不会被google动手脚了 12/02 12:21
newacc大大抱歉，花了一点时间测试公式。
1. 发现从压缩包下载好像问题跟之前相同，
也是改了数字之後，所有运算结果都变成0或1。
无论在压缩包内外开启皆相同。
2. 用普通的Enter输入公式之後，也有类似情形 (变成0或1)
3. 还有一个发现：按下ctrl + z 复原无效。
可能再麻烦大大看看是什麽问题，感谢。
※ 编辑: rafael750626 (203.68.96.125 台湾), 12/05/2022 09:22:10
24F：→ newacc: 档案右边有解说，照着把公式打一遍呢？ 12/05 12:17
25F：→ rafael750626: 刚才确认了。如果自行输入公式，结果会是0或1 12/05 15:54
26F：→ rafael750626: 在有按下ctrl + shift + Enter的情况下... 12/05 15:54
27F：推 windknife18: F2=IF(COUNTIF(B$2:B2,"<>")<10,"不足10例",COUNTIF( 12/05 18:38
28F：→ windknife18: INDIRECT("D"&LARGE(IF(B$2:B2<>"",ROW(B$2:B2)),10) 12/05 18:38
29F：→ windknife18: &":D"&ROW(B2)),">0")/10) 12/05 18:38
30F：→ windknife18: 按Ctrl+Shift+Enter结束，然後往下和右复制公式 12/05 18:40
31F：推 windknife18: 忘了改indirect里面的D了，所有D要改成B 12/05 22:06
32F：→ windknife18: 另外公式复制到G2要将里面的B改成C，依此类堆 12/05 22:07
34F：→ rafael750626: 感谢windkinfe18大大，目前公式可以使用。且原始 12/06 09:29
35F：→ rafael750626: 资料也可以修改。公式内容我应该看得懂。感谢！ 12/06 09:29