[算表] Excel 末10列有效資料擷取問題

[rafael750626 (一休)]

軟體: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://www.ptt.cc/bbs/Office/M.1669423623.A.CCF.html
推 kinomon: 我會考慮做輔助欄呈現非空白的累計個數
→ kinomon: 例如RoomA輔助欄 J20= row(19:19)-countif(A$2:A20, “”
→ kinomon: )
→ kinomon: 寫錯 J20= row(19:19)-countif(B$2:B20, “”)
→ kinomon: J20算出來是15 往前追溯前十就是用match找J欄為6的位置
→ kinomon: 不過我只是粗略想的 應該還有比較簡潔的解法
輔助列嗎？我思考看看...
推 newacc: 覺得應該可以用INDEX、LARGE、IF的陣列公式處理
→ newacc: 判斷非空白回傳列號，抓列號最大的10個餵給INDEX
→ newacc: 不過我電腦送修要上班日才能幫你寫寫看了
用LARGE加上IF抓出列號前十大的嗎？感覺是一個突破口，周一有空我也思考看看。
※ 編輯: rafael750626 (122.121.11.180 臺灣), 11/26/2022 22:35:29
推 windknife18: 以B36為例算第幾列
→ windknife18: =LARGE(IF($B$1:B36<>"",ROW($B$1:B36)),10)
→ newacc: 參考看看 https://bit.ly/3VBrhch
→ newacc: 不過google在處理公式的邏輯好像跟excel不太一樣
→ newacc: 請下載下來用excel開，計算才會是對的
太感謝了！公式在下載後看起來運作良好。

我現在只剩下想辦法看懂裡面的內容，然後跟同事簡報這樣@@
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
推 kinomon: 評估值公式可以看公式的運作 {}是陣列
→ kinomon: Row($1:$10)就是{1;2;3;4;5;6;7;8;9;10}
推 kinomon: https://rijifang.com/index.php/post/59.html
→ kinomon: 找了找這篇對岸文章寫得比較淺顯
newacc大大抱歉，剛才在測試下載的excel檔案後發現一個問題。
剛下載的時候ok，可是嘗試動到B:D排的原始資料時，
F:H的結果就會變成0或者1 ("不足10例"儲存格內容不變)

請問這是excel版本的問題嗎 (畢竟是從google試算表轉換而來)？
抱歉又需要再請大大協助了orz
※ 編輯: rafael750626 (203.68.96.125 臺灣), 12/01/2022 15:09:58
→ newacc: 結果變成0或1應該是被當作陣列公式處理了，試試看編輯F2
→ newacc: 直接按Enter完成公式，再從F2複製到其他格看看
→ newacc: 加成壓縮檔，應該不會被google動手腳了
→ newacc: https://bit.ly/3VLY4LI 新增公式的解說
→ newacc: https://i.imgur.com/isDK7KW.png

newacc大大抱歉，花了一點時間測試公式。

1. 發現從壓縮包下載好像問題跟之前相同，
也是改了數字之後，所有運算結果都變成0或1。
無論在壓縮包內外開啟皆相同。

2. 用普通的Enter輸入公式之後，也有類似情形 (變成0或1)

3. 還有一個發現：按下ctrl + z 復原無效。

可能再麻煩大大看看是什麼問題，感謝。
※ 編輯: rafael750626 (203.68.96.125 臺灣), 12/05/2022 09:22:10
→ newacc: 檔案右邊有解說，照著把公式打一遍呢？
→ rafael750626: 剛才確認了。如果自行輸入公式，結果會是0或1
→ rafael750626: 在有按下ctrl + shift + Enter的情況下...
推 windknife18: F2=IF(COUNTIF(B$2:B2,"<>")<10,"不足10例",COUNTIF(
→ windknife18: INDIRECT("D"&LARGE(IF(B$2:B2<>"",ROW(B$2:B2)),10)
→ windknife18: &":D"&ROW(B2)),">0")/10)
→ windknife18: 按Ctrl+Shift+Enter結束，然後往下和右複製公式
推 windknife18: 忘了改indirect裡面的D了，所有D要改成B
→ windknife18: 另外公式複製到G2要將裡面的B改成C，依此類堆
推 windknife18: https://tinyurl.com/2p9756se
→ rafael750626: 感謝windkinfe18大大，目前公式可以使用。且原始
→ rafael750626: 資料也可以修改。公式內容我應該看得懂。感謝！