PTT數位生活區 / Office

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

看板Office作者 (一休)時間2年前 (2022/11/26 08:47), 2年前編輯推噓5(5推 0噓 18→)
留言23則, 3人參與, 2年前最新討論串1/1
軟體: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
推
11/26 16:50, 2年前 , 1F
我會考慮做輔助欄呈現非空白的累計個數
11/26 16:50, 1F
→
11/26 16:57, 2年前 , 2F
例如RoomA輔助欄 J20= row(19:19)-countif(A$2:A20, “”
11/26 16:57, 2F
→
11/26 16:57, 2年前 , 3F
)
11/26 16:57, 3F
→
11/26 16:58, 2年前 , 4F
寫錯 J20= row(19:19)-countif(B$2:B20, “”)
11/26 16:58, 4F
→
11/26 17:02, 2年前 , 5F
J20算出來是15 往前追溯前十就是用match找J欄為6的位置
11/26 17:02, 5F
→
11/26 17:04, 2年前 , 6F
不過我只是粗略想的 應該還有比較簡潔的解法
11/26 17:04, 6F
輔助列嗎？我思考看看...
推
11/26 19:59, 2年前 , 7F
覺得應該可以用INDEX、LARGE、IF的陣列公式處理
11/26 19:59, 7F
→
11/26 19:59, 2年前 , 8F
判斷非空白回傳列號，抓列號最大的10個餵給INDEX
11/26 19:59, 8F
→
11/26 19:59, 2年前 , 9F
不過我電腦送修要上班日才能幫你寫寫看了
11/26 19:59, 9F
用LARGE加上IF抓出列號前十大的嗎？感覺是一個突破口，周一有空我也思考看看。 ※ 編輯: rafael750626 (122.121.11.180 臺灣), 11/26/2022 22:35:29
推
11/27 21:24, 2年前 , 10F
以B36為例算第幾列
11/27 21:24, 10F
→
11/27 21:25, 2年前 , 11F
=LARGE(IF($B$1:B36<>"",ROW($B$1:B36)),10)
11/27 21:25, 11F
→
11/29 16:30, 2年前 , 12F
11/29 16:30, 12F
→
11/29 16:31, 2年前 , 13F
不過google在處理公式的邏輯好像跟excel不太一樣
11/29 16:31, 13F
→
11/29 16:31, 2年前 , 14F
請下載下來用excel開，計算才會是對的
11/29 16:31, 14F
太感謝了！公式在下載後看起來運作良好。 我現在只剩下想辦法看懂裡面的內容，然後跟同事簡報這樣@@ 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
推
11/30 19:21, 2年前 , 15F
評估值公式可以看公式的運作 {}是陣列
11/30 19:21, 15F
→
11/30 19:24, 2年前 , 16F
Row($1:$10)就是{1;2;3;4;5;6;7;8;9;10}
11/30 19:24, 16F
推
11/30 19:32, 2年前 , 17F
11/30 19:32, 17F
→
11/30 19:32, 2年前 , 18F
找了找這篇對岸文章寫得比較淺顯
11/30 19:32, 18F
newacc大大抱歉，剛才在測試下載的excel檔案後發現一個問題。 剛下載的時候ok，可是嘗試動到B:D排的原始資料時， F:H的結果就會變成0或者1 ("不足10例"儲存格內容不變) 請問這是excel版本的問題嗎 (畢竟是從google試算表轉換而來)？ 抱歉又需要再請大大協助了orz ※ 編輯: rafael750626 (203.68.96.125 臺灣), 12/01/2022 15:09:58
→
12/02 11:38, 2年前 , 19F
結果變成0或1應該是被當作陣列公式處理了，試試看編輯F2
12/02 11:38, 19F
→
12/02 11:39, 2年前 , 20F
直接按Enter完成公式，再從F2複製到其他格看看
12/02 11:39, 20F
→
12/02 12:21, 2年前 , 21F
加成壓縮檔，應該不會被google動手腳了
12/02 12:21, 21F
→
12/02 12:21, 2年前 , 22F
https://bit.ly/3VLY4LI 新增公式的解說
12/02 12:21, 22F
→
12/02 12:23, 2年前 , 23F
12/02 12:23, 23F
‣ 更多 rafael750626 的文章
文章代碼(AID): #1ZWM87pF (Office)
Office 近期熱門文章
更多 近期熱門文章 >>
PTT數位生活區 即時熱門文章
更多 即時熱門文章 >>
‣ 更多 rafael750626 的文章
文章代碼(AID): #1ZWM87pF (Office)