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

[Jerome0511 (Jerome)]

(若是和其他不同軟體互動之問題 請記得一併填寫)
軟體: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
→ soyoso: https://goo.gl/omq2Z4 配合match試試
→ Jerome0511: 數值有點怪  如果你把篩選全部展開之後平均值是19
→ Jerome0511: https://goo.gl/xjxiwk  平均值應該是27才對
→ soyoso: http://imgur.com/BrePrwO 修改一下
→ Jerome0511: 改完之後，若輸入號碼改3 往前抓一筆平均值變14是錯的
→ soyoso: http://imgur.com/qHFi0md 改為這樣？
→ Jerome0511: 還是怪怪的https://goo.gl/1udJjW
→ soyoso: 連結圖片內在-後有再用括號包起來，原po新提供的連結沒有
→ Jerome0511: OK謝謝 請教一下有類似Averageif搭配subtotal的指令嗎
→ soyoso: 是要使用以函數averageif來寫嗎？
→ Jerome0511: 對啊 但一樣要搭配篩選用
→ soyoso: https://goo.gl/lSCDqR 類似這樣
→ Jerome0511: 不好意思沒說清楚，我是想說有辦法先篩選完號碼之後
→ Jerome0511: 再拉一個欄位的數值，當數值大於15其平均值為多少
→ Jerome0511: https://goo.gl/pucXC5 如照片大於15的平均值應該為19
→ Jerome0511: (16+22)/2=19
→ soyoso: https://goo.gl/lSCDqR 重下試試
→ Jerome0511: 不好意思 你算的>15的平均值 我需要的是也需要在輸入
→ Jerome0511: 號碼欄之後 完前抓一筆資料的平均值，非整體的
→ Jerome0511: 也就是當我輸入號碼3 往前抓1筆資料，其avrageif要在
→ Jerome0511: 此區間範圍內
→ soyoso: 那將">0"的條件換掉為">=1"，再加一個條件是"<=4"
→ soyoso: 儲存格當變數用連接符號&
→ soyoso: 以回文連結條件，就會是(16+22)/2=19
→ soyoso: http://imgur.com/F39SpAh
→ Jerome0511: 請問一下為什麼號碼那一欄需要那麼多函式 用到
→ Jerome0511: 用了IF、COUNTIF、SUBTOTAL  和只用SUBTOTAL有何差別?
→ Jerome0511: 所以其實Averageif其實就自己有內建SUBTOTAL的功能嗎?
→ Jerome0511: 所以其實Averageif其實就自己有內建SUBTOTAL的功能嗎?
→ soyoso: 加上if、countif和只用subtotal的差別，原po可在其他儲存
→ soyoso: 格打上=i11，下拉10格儲存格就可以看的出差別
→ soyoso: subtotal的功能，這裡的功能是指？
→ Jerome0511: if、countif和只用subtotal的差別，剛試過往下拉儲存
※ 編輯: Jerome0511 (60.251.182.146), 01/11/2016 14:39:21
→ Jerome0511: 往下拉看起來數值一樣https://goo.gl/Ui4D9H
→ Jerome0511: 大於15平均值的條件，算出來也怪怪的，以附件範例為例
→ Jerome0511: 在想說averageif是不是也要用offset的寫法
→ Jerome0511: 因為subtotal的用法不是被篩選後的數值不會被考慮進去
→ Jerome0511: 如果單純只用averageif 那隱藏的數值不是會被算到嗎?
→ soyoso: http://imgur.com/efZUIEq 篩選後的值不同
→ soyoso: 附件為例，條件是i11:i21>=1和i11:i21<=4符合為藍框
→ soyoso: j11:j21>15為紅框 http://imgur.com/fOxjp3U
→ soyoso: 重覆之處為22,16的平均19，這非原po要的嗎?
→ soyoso: 測試上averageif會算入隱藏數值http://imgur.com/6oeBKxS
→ Jerome0511: 但如果你把輸入號碼改為8平均值是錯的可參考我上面貼
→ Jerome0511: 的連結https://goo.gl/Ui4D9H
→ Jerome0511: 所以Averageif會算到隱藏的連結，所以才問有沒有
→ Jerome0511: 類似Averageif搭配subtotal的指令
→ soyoso: 輸入號碼改為8那公式一樣是i11:i21>=1和i11:i21<=4嗎？
→ soyoso: 公式內的1和4要當變數，於今天上午11:46回文就有寫到
→ soyoso: 而非只是打>=1和<=4這樣
→ Jerome0511: I11:I21,">=1",I11:I21,"<="&I3 這樣是錯的嗎?
→ soyoso: 公式有報錯嗎？我測試沒有，所以語法沒有不正確
→ soyoso: 只是結果是否是原po要的而已
→ soyoso: 原po輸入號碼為8，往前抓3筆為5，i11:i21的區間就要以這個
→ soyoso: 為範圍，如何產生">=5"，可用">="&i3-i5
→ Jerome0511: Ok 沒問題了 只是你回文有提到averagif 會算到隱藏值
→ Jerome0511:  這個有解嗎？
→ Jerome0511: 是不是加了">="&i3-i5 "<="&i3。 與j11:j21>我要的限
→ Jerome0511: 制範圍 就可以踢除隱藏數值直接算平均
→ soyoso: averagifs要剔除隱藏數值，想到的是配合subtotal的i11:i20
→ soyoso: 如有配合的話，如檔案測試應可剔除隱藏數值算平均
→ Jerome0511: https://goo.gl/ZnHlp0  以附件為例，感覺不需要用到
→ Jerome0511: SUBTOTAL 就可以剔除隱藏值 這是什麼原因?
→ Jerome0511: 還有我想增加一欄數量用COUNTIFS寫但會出現引數太少
→ Jerome0511: 是什麼原因呢?
→ soyoso: 以附件為例，i11:i20不就用到subtotal，為何回文寫不需要
→ soyoso: 用到subtotal呢？且公式averageifs內也有配合i11:i21
→ soyoso: countifs寫引數太少應表示，填寫時省略必需要引數，例如有
→ soyoso: 範圍，卻無條件
→ Jerome0511: 哦 原來如此 subtotal 可以直接先用在i11:i21 average
→ Jerome0511: if 那邊可以不需要在寫一次subtotal 會直接套用i11:i2
→ Jerome0511: 1的subtotal 囉？
→ Jerome0511: 但我是直接把averagifs 那邊的函式 後面的挑件原封不
→ Jerome0511: 動 只改countifs應該沒有判斷不足的問題吧？
→ soyoso: 如原po所述，averageifs內就不用再寫一次
→ soyoso: averageifs改為countifs，那請將average_range的範圍拿掉
→ Jerome0511: OK感謝你。
→ Jerome0511: 請問一下 想利用vlookup由數值反推號碼 如果數值有兩
→ Jerome0511: 個一樣的 那反推回來的號碼會以先搜尋到的值為準 第二
→ Jerome0511: 個一樣數值的值反推號碼會無法顯示 有什麼辦法解決嗎
→ Jerome0511: 第二個問題有辦法利用vlookup 來限制 和我請教你的平
→ Jerome0511: 均值一樣的區間嗎？ 有就是輸入號碼值往前推幾筆的區
→ Jerome0511: 間用vlookup 反推號碼值
→ soyoso: 以回文舉例回傳同號碼第二筆的話，可用區間offset配合
→ soyoso: match的方式
→ Jerome0511: 回傳寫法VLOOKUP(M3,IF({1,0},J15:J24,I15:I24),2,0)
→ Jerome0511: 所以要增加區間判別要改掉J15:J24,I15:I24這一段囉?
→ soyoso: 測試上可修改j15:j24和i15:i24
→ Jerome0511: https://goo.gl/zb6V7i  反推號碼的函式有新增了
→ Jerome0511: 如附件的紅色框框 想問一下 當如果往前抓的直改為7
→ Jerome0511: 因為數值A有兩筆是5，要怎麼把反推號碼值 兩個對應到
→ Jerome0511: 的一起顯示出來?
→ soyoso: 抱歉不太了解，是指抓取兩筆數值A為5，而顯示數值B的14,24
→ soyoso: 嗎？
→ Jerome0511: 因為數值A有兩個5當我的區間範圍變大 反推回去的號碼
→ Jerome0511: 應該會有兩筆資料號碼是對應到數值A的5。但我目前寫法
→ Jerome0511: 只能抓到一筆號碼 想在抓另一筆對應的號碼要怎麼處理
→ soyoso: 抱歉不太了解
→ soyoso: 如是以數值A的5為條件將對應一筆以上的號碼抓出的話，可以
→ soyoso: index配合small+if的方式
→ Jerome0511: 所以一對多 就不適合用vlookup嗎？
→ soyoso: 1對2應可用vlookup，1對3以上用vlookup使用上我會加上輔助
→ soyoso: 欄來抓前一筆的列號
→ Jerome0511: https://goo.gl/gXCNR9 那像附件這個範例 數值A是5
→ Jerome0511: 對應的號碼應該要顯示3與8 可以只用VLOOKUP就好?
→ soyoso: 可以 http://imgur.com/FA2g7T9 測試上是用vlookup配合
→ soyoso: offset和match
→ soyoso: 第二筆為第一筆match的列號+1的範圍來對應
→ Jerome0511: -(MATCH(K3,I15:I24,0)-MATCH(K3-K5,I15:I24,0)-1)
→ Jerome0511: 把+1 改-1吧?
→ soyoso: 以連結內改-2可帶出8
→ Jerome0511: 突然發現這個列號+1的方法 有一個缺點就是當數值A連
→ Jerome0511: 續兩筆都是一樣的值 回傳的號碼還是會一樣的
→ Jerome0511: 而且用MATCH如果遇到篩選 他所對應到的列數好像會跑掉
→ soyoso: +1的方法，連續兩筆一樣，回傳號碼一樣方面，不太了解原po
→ soyoso: 的意思
→ soyoso: 遇到篩選？原po在該問題vlookup上首次說到會用到篩選，所
→ soyoso: 以該問題的回文並無考慮到這方面
→ soyoso: 上面寫到的該問題為原文01/13 14:38起01/14 08:57並無提及
→ soyoso: 篩選方面
→ Jerome0511: OK  好 所以如果加篩選條件 寫法就要改囉?
→ soyoso: 加篩選上，如要使用vlookup的話，我會加上輔助欄來針對數
→ soyoso: 值A被隱藏的話則為空字串
→ Jerome0511: 請問有大概的語法嗎？
→ soyoso: 輔助欄寫法同號碼i15:i24的判斷