Office 板


LINE

軟體: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函數往上推更多資料進入選取區域也可能會遇到更多空白,解法並不完美。 怎麼查資料都查不到一個好的寫法。 想就這個部分跟各位大大請益。 感謝。 --
QR Code



※ 發信站: 批踢踢實業坊(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
12F:→ newacc: 參考看看 https://bit.ly/3VBrhch 11/29 16:30
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
17F:推 kinomon: https://rijifang.com/index.php/post/59.html 11/30 19:32
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
22F:→ newacc: https://bit.ly/3VLY4LI 新增公式的解說 12/02 12:21
23F:→ newacc: https://i.imgur.com/isDK7KW.png 12/02 12:23
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
33F:推 windknife18: https://tinyurl.com/2p9756se 12/05 22:09
34F:→ rafael750626: 感謝windkinfe18大大,目前公式可以使用。且原始 12/06 09:29
35F:→ rafael750626: 資料也可以修改。公式內容我應該看得懂。感謝! 12/06 09:29







like.gif 您可能會有興趣的文章
icon.png[問題/行為] 貓晚上進房間會不會有憋尿問題
icon.pngRe: [閒聊] 選了錯誤的女孩成為魔法少女 XDDDDDDDDDD
icon.png[正妹] 瑞典 一張
icon.png[心得] EMS高領長版毛衣.墨小樓MC1002
icon.png[分享] 丹龍隔熱紙GE55+33+22
icon.png[問題] 清洗洗衣機
icon.png[尋物] 窗台下的空間
icon.png[閒聊] 双極の女神1 木魔爵
icon.png[售車] 新竹 1997 march 1297cc 白色 四門
icon.png[討論] 能從照片感受到攝影者心情嗎
icon.png[狂賀] 賀賀賀賀 賀!島村卯月!總選舉NO.1
icon.png[難過] 羨慕白皮膚的女生
icon.png閱讀文章
icon.png[黑特]
icon.png[問題] SBK S1安裝於安全帽位置
icon.png[分享] 舊woo100絕版開箱!!
icon.pngRe: [無言] 關於小包衛生紙
icon.png[開箱] E5-2683V3 RX480Strix 快睿C1 簡單測試
icon.png[心得] 蒼の海賊龍 地獄 執行者16PT
icon.png[售車] 1999年Virage iO 1.8EXi
icon.png[心得] 挑戰33 LV10 獅子座pt solo
icon.png[閒聊] 手把手教你不被桶之新手主購教學
icon.png[分享] Civic Type R 量產版官方照無預警流出
icon.png[售車] Golf 4 2.0 銀色 自排
icon.png[出售] Graco提籃汽座(有底座)2000元誠可議
icon.png[問題] 請問補牙材質掉了還能再補嗎?(台中半年內
icon.png[問題] 44th 單曲 生寫竟然都給重複的啊啊!
icon.png[心得] 華南紅卡/icash 核卡
icon.png[問題] 拔牙矯正這樣正常嗎
icon.png[贈送] 老莫高業 初業 102年版
icon.png[情報] 三大行動支付 本季掀戰火
icon.png[寶寶] 博客來Amos水蠟筆5/1特價五折
icon.pngRe: [心得] 新鮮人一些面試分享
icon.png[心得] 蒼の海賊龍 地獄 麒麟25PT
icon.pngRe: [閒聊] (君の名は。雷慎入) 君名二創漫畫翻譯
icon.pngRe: [閒聊] OGN中場影片:失蹤人口局 (英文字幕)
icon.png[問題] 台灣大哥大4G訊號差
icon.png[出售] [全國]全新千尋侘草LED燈, 水草

請輸入看板名稱,例如:WOW站內搜尋

TOP