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