PTT數位生活區 / Office

[問題] 篩選搭配offset 取平均

看板Office作者 (Jerome)時間9年前 (2016/01/07 16:12), 8年前編輯推噓0(0推 0噓 125→)
留言125則, 2人參與, 最新討論串1/1
(若是和其他不同軟體互動之問題 請記得一併填寫) 軟體:excel 版本:2010 檔案在此: https://goo.gl/RlonPF 目前遇到的問題是隨機篩選號碼欄，但無法算平均 不知道是出現什麼問題，指令是用offset搭配SUBTOTAL 謝謝。 -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 211.21.159.187 ※ 文章網址: https://www.ptt.cc/bbs/Office/M.1452154361.A.D64.html
→
01/07 16:31, , 1F
https://goo.gl/omq2Z4 配合match試試
01/07 16:31, 1F
→
01/08 11:56, , 2F
數值有點怪 如果你把篩選全部展開之後平均值是19
01/08 11:56, 2F
→
01/08 11:57, , 3F
https://goo.gl/xjxiwk 平均值應該是27才對
01/08 11:57, 3F
→
01/08 12:12, , 4F
修改一下
01/08 12:12, 4F
→
01/08 13:43, , 5F
改完之後，若輸入號碼改3 往前抓一筆平均值變14是錯的
01/08 13:43, 5F
→
01/08 13:57, , 6F
改為這樣？
01/08 13:57, 6F
→
01/08 14:11, , 7F
還是怪怪的https://goo.gl/1udJjW
01/08 14:11, 7F
→
01/08 14:18, , 8F
連結圖片內在-後有再用括號包起來，原po新提供的連結沒有
01/08 14:18, 8F
→
01/08 16:36, , 9F
OK謝謝 請教一下有類似Averageif搭配subtotal的指令嗎
01/08 16:36, 9F
→
01/08 18:24, , 10F
是要使用以函數averageif來寫嗎？
01/08 18:24, 10F
→
01/08 20:24, , 11F
對啊 但一樣要搭配篩選用
01/08 20:24, 11F
→
01/08 20:45, , 12F
01/08 20:45, 12F
→
01/08 22:40, , 13F
不好意思沒說清楚，我是想說有辦法先篩選完號碼之後
01/08 22:40, 13F
→
01/08 22:41, , 14F
再拉一個欄位的數值，當數值大於15其平均值為多少
01/08 22:41, 14F
→
01/08 22:46, , 15F
https://goo.gl/pucXC5 如照片大於15的平均值應該為19
01/08 22:46, 15F
→
01/08 22:47, , 16F
(16+22)/2=19
01/08 22:47, 16F
→
01/08 22:55, , 17F
01/08 22:55, 17F
→
01/11 11:01, , 18F
不好意思 你算的>15的平均值 我需要的是也需要在輸入
01/11 11:01, 18F
→
01/11 11:01, , 19F
號碼欄之後 完前抓一筆資料的平均值，非整體的
01/11 11:01, 19F
→
01/11 11:02, , 20F
也就是當我輸入號碼3 往前抓1筆資料，其avrageif要在
01/11 11:02, 20F
→
01/11 11:03, , 21F
此區間範圍內
01/11 11:03, 21F
→
01/11 11:46, , 22F
那將">0"的條件換掉為">=1"，再加一個條件是"<=4"
01/11 11:46, 22F
→
01/11 11:46, , 23F
儲存格當變數用連接符號&
01/11 11:46, 23F
→
01/11 11:48, , 24F
以回文連結條件，就會是(16+22)/2=19
01/11 11:48, 24F
→
01/11 11:49, , 25F
01/11 11:49, 25F
→
01/11 13:44, , 26F
請問一下為什麼號碼那一欄需要那麼多函式 用到
01/11 13:44, 26F
→
01/11 13:45, , 27F
用了IF、COUNTIF、SUBTOTAL 和只用SUBTOTAL有何差別?
01/11 13:45, 27F
→
01/11 13:46, , 28F
所以其實Averageif其實就自己有內建SUBTOTAL的功能嗎?
01/11 13:46, 28F
→
01/11 13:47, , 29F
所以其實Averageif其實就自己有內建SUBTOTAL的功能嗎?
01/11 13:47, 29F
→
01/11 13:51, , 30F
加上if、countif和只用subtotal的差別，原po可在其他儲存
01/11 13:51, 30F
→
01/11 13:51, , 31F
格打上=i11，下拉10格儲存格就可以看的出差別
01/11 13:51, 31F
→
01/11 13:56, , 32F
subtotal的功能，這裡的功能是指？
01/11 13:56, 32F
→
01/11 14:27, , 33F
if、countif和只用subtotal的差別，剛試過往下拉儲存
01/11 14:27, 33F
※ 編輯: Jerome0511 (60.251.182.146), 01/11/2016 14:39:21
→
01/11 14:39, , 34F
往下拉看起來數值一樣https://goo.gl/Ui4D9H
01/11 14:39, 34F
→
01/11 14:40, , 35F
大於15平均值的條件，算出來也怪怪的，以附件範例為例
01/11 14:40, 35F
→
01/11 14:43, , 36F
在想說averageif是不是也要用offset的寫法
01/11 14:43, 36F
→
01/11 14:44, , 37F
因為subtotal的用法不是被篩選後的數值不會被考慮進去
01/11 14:44, 37F
→
01/11 14:45, , 38F
如果單純只用averageif 那隱藏的數值不是會被算到嗎?
01/11 14:45, 38F
還有 47 則推文
→
01/13 16:14, , 86F
回傳寫法VLOOKUP(M3,IF({1,0},J15:J24,I15:I24),2,0)
01/13 16:14, 86F
→
01/13 16:14, , 87F
所以要增加區間判別要改掉J15:J24,I15:I24這一段囉?
01/13 16:14, 87F
→
01/13 16:31, , 88F
測試上可修改j15:j24和i15:i24
01/13 16:31, 88F
→
01/13 20:33, , 89F
https://goo.gl/zb6V7i 反推號碼的函式有新增了
01/13 20:33, 89F
→
01/13 20:34, , 90F
如附件的紅色框框 想問一下 當如果往前抓的直改為7
01/13 20:34, 90F
→
01/13 20:35, , 91F
因為數值A有兩筆是5，要怎麼把反推號碼值 兩個對應到
01/13 20:35, 91F
→
01/13 20:35, , 92F
的一起顯示出來?
01/13 20:35, 92F
→
01/13 20:57, , 93F
抱歉不太了解，是指抓取兩筆數值A為5，而顯示數值B的14,24
01/13 20:57, 93F
→
01/13 20:57, , 94F
嗎？
01/13 20:57, 94F
→
01/13 21:31, , 95F
因為數值A有兩個5當我的區間範圍變大 反推回去的號碼
01/13 21:31, 95F
→
01/13 21:31, , 96F
應該會有兩筆資料號碼是對應到數值A的5。但我目前寫法
01/13 21:31, 96F
→
01/13 21:31, , 97F
只能抓到一筆號碼 想在抓另一筆對應的號碼要怎麼處理
01/13 21:31, 97F
→
01/13 21:54, , 98F
抱歉不太了解
01/13 21:54, 98F
→
01/13 21:58, , 99F
如是以數值A的5為條件將對應一筆以上的號碼抓出的話，可以
01/13 21:58, 99F
→
01/13 21:58, , 100F
index配合small+if的方式
01/13 21:58, 100F
→
01/13 22:08, , 101F
所以一對多 就不適合用vlookup嗎？
01/13 22:08, 101F
→
01/13 22:11, , 102F
1對2應可用vlookup，1對3以上用vlookup使用上我會加上輔助
01/13 22:11, 102F
→
01/13 22:11, , 103F
欄來抓前一筆的列號
01/13 22:11, 103F
→
01/13 22:20, , 104F
https://goo.gl/gXCNR9 那像附件這個範例 數值A是5
01/13 22:20, 104F
→
01/13 22:21, , 105F
對應的號碼應該要顯示3與8 可以只用VLOOKUP就好?
01/13 22:21, 105F
→
01/13 22:33, , 106F
測試上是用vlookup配合
01/13 22:33, 106F
→
01/13 22:34, , 107F
offset和match
01/13 22:34, 107F
→
01/13 22:48, , 108F
第二筆為第一筆match的列號+1的範圍來對應
01/13 22:48, 108F
→
01/13 23:34, , 109F
-(MATCH(K3,I15:I24,0)-MATCH(K3-K5,I15:I24,0)-1)
01/13 23:34, 109F
→
01/13 23:34, , 110F
把+1 改-1吧?
01/13 23:34, 110F
→
01/13 23:46, , 111F
以連結內改-2可帶出8
01/13 23:46, 111F
→
01/14 08:57, , 112F
突然發現這個列號+1的方法 有一個缺點就是當數值A連
01/14 08:57, 112F
→
01/14 08:57, , 113F
續兩筆都是一樣的值 回傳的號碼還是會一樣的
01/14 08:57, 113F
→
01/14 09:12, , 114F
而且用MATCH如果遇到篩選 他所對應到的列數好像會跑掉
01/14 09:12, 114F
→
01/14 10:25, , 115F
+1的方法，連續兩筆一樣，回傳號碼一樣方面，不太了解原po
01/14 10:25, 115F
→
01/14 10:25, , 116F
的意思
01/14 10:25, 116F
→
01/14 10:26, , 117F
遇到篩選？原po在該問題vlookup上首次說到會用到篩選，所
01/14 10:26, 117F
→
01/14 10:27, , 118F
以該問題的回文並無考慮到這方面
01/14 10:27, 118F
→
01/14 10:30, , 119F
上面寫到的該問題為原文01/13 14:38起01/14 08:57並無提及
01/14 10:30, 119F
→
01/14 10:30, , 120F
篩選方面
01/14 10:30, 120F
→
01/14 11:34, , 121F
OK 好 所以如果加篩選條件 寫法就要改囉?
01/14 11:34, 121F
→
01/14 11:43, , 122F
加篩選上，如要使用vlookup的話，我會加上輔助欄來針對數
01/14 11:43, 122F
→
01/14 11:44, , 123F
值A被隱藏的話則為空字串
01/14 11:44, 123F
→
01/14 18:49, , 124F
請問有大概的語法嗎？
01/14 18:49, 124F
→
01/15 01:14, , 125F
輔助欄寫法同號碼i15:i24的判斷
01/15 01:14, 125F
‣ 更多 Jerome0511 的文章
文章代碼(AID): #1MZXtvra (Office)
Office 近期熱門文章
更多 近期熱門文章 >>
PTT數位生活區 即時熱門文章
更多 即時熱門文章 >>
‣ 更多 Jerome0511 的文章
文章代碼(AID): #1MZXtvra (Office)