(若是和其他不同軟體互動之問題 請記得一併填寫)
軟體:excel
版本:2010
檔案在此:
https://goo.gl/RlonPF
目前遇到的問題是隨機篩選號碼欄，但無法算平均
不知道是出現什麼問題，指令是用offset搭配SUBTOTAL
謝謝。

https://goo.gl/omq2Z4 配合match試試

數值有點怪 如果你把篩選全部展開之後平均值是19https://goo.gl/xjxiwk 平均值應該是27才對

http://imgur.com/BrePrwO 修改一下

改完之後，若輸入號碼改3 往前抓一筆平均值變14是錯的

http://imgur.com/qHFi0md 改為這樣？

還是怪怪的https://goo.gl/1udJjW

連結圖片內在-後有再用括號包起來，原po新提供的連結沒有

OK謝謝 請教一下有類似Averageif搭配subtotal的指令嗎

是要使用以函數averageif來寫嗎？

對啊 但一樣要搭配篩選用

https://goo.gl/lSCDqR 類似這樣

不好意思沒說清楚，我是想說有辦法先篩選完號碼之後再拉一個欄位的數值，當數值大於15其平均值為多少https://goo.gl/pucXC5 如照片大於15的平均值應該為19(16+22)/2=19

https://goo.gl/lSCDqR 重下試試

不好意思 你算的>15的平均值 我需要的是也需要在輸入號碼欄之後 完前抓一筆資料的平均值，非整體的也就是當我輸入號碼3 往前抓1筆資料，其avrageif要在此區間範圍內

那將">0"的條件換掉為">=1"，再加一個條件是"<=4"儲存格當變數用連接符號&以回文連結條件，就會是(16+22)/2=19http://imgur.com/F39SpAh

請問一下為什麼號碼那一欄需要那麼多函式 用到用了IF、COUNTIF、SUBTOTAL 和只用SUBTOTAL有何差別?所以其實Averageif其實就自己有內建SUBTOTAL的功能嗎?所以其實Averageif其實就自己有內建SUBTOTAL的功能嗎?

加上if、countif和只用subtotal的差別，原po可在其他儲存格打上=i11，下拉10格儲存格就可以看的出差別subtotal的功能，這裡的功能是指？

if、countif和只用subtotal的差別，剛試過往下拉儲存往下拉看起來數值一樣https://goo.gl/Ui4D9H大於15平均值的條件，算出來也怪怪的，以附件範例為例在想說averageif是不是也要用offset的寫法因為subtotal的用法不是被篩選後的數值不會被考慮進去如果單純只用averageif 那隱藏的數值不是會被算到嗎?

http://imgur.com/efZUIEq 篩選後的值不同附件為例，條件是i11:i21>=1和i11:i21<=4符合為藍框j11:j21>15為紅框 http://imgur.com/fOxjp3U重覆之處為22,16的平均19，這非原po要的嗎?測試上averageif會算入隱藏數值http://imgur.com/6oeBKxS

但如果你把輸入號碼改為8平均值是錯的可參考我上面貼的連結https://goo.gl/Ui4D9H所以Averageif會算到隱藏的連結，所以才問有沒有類似Averageif搭配subtotal的指令

輸入號碼改為8那公式一樣是i11:i21>=1和i11:i21<=4嗎？公式內的1和4要當變數，於今天上午11:46回文就有寫到而非只是打>=1和<=4這樣

I11:I21,">=1",I11:I21,"<="&I3 這樣是錯的嗎?

公式有報錯嗎？我測試沒有，所以語法沒有不正確只是結果是否是原po要的而已原po輸入號碼為8，往前抓3筆為5，i11:i21的區間就要以這個為範圍，如何產生">=5"，可用">="&i3-i5

Ok 沒問題了 只是你回文有提到averagif 會算到隱藏值這個有解嗎？是不是加了">="&i3-i5 "<="&i3。 與j11:j21>我要的限制範圍 就可以踢除隱藏數值直接算平均

averagifs要剔除隱藏數值，想到的是配合subtotal的i11:i20如有配合的話，如檔案測試應可剔除隱藏數值算平均

https://goo.gl/ZnHlp0 以附件為例，感覺不需要用到SUBTOTAL 就可以剔除隱藏值 這是什麼原因?還有我想增加一欄數量用COUNTIFS寫但會出現引數太少是什麼原因呢?

以附件為例，i11:i20不就用到subtotal，為何回文寫不需要用到subtotal呢？且公式averageifs內也有配合i11:i21countifs寫引數太少應表示，填寫時省略必需要引數，例如有範圍，卻無條件

哦 原來如此 subtotal 可以直接先用在i11:i21 averageif 那邊可以不需要在寫一次subtotal 會直接套用i11:i21的subtotal 囉？但我是直接把averagifs 那邊的函式 後面的挑件原封不動 只改countifs應該沒有判斷不足的問題吧？

如原po所述，averageifs內就不用再寫一次averageifs改為countifs，那請將average_range的範圍拿掉

OK感謝你。請問一下 想利用vlookup由數值反推號碼 如果數值有兩個一樣的 那反推回來的號碼會以先搜尋到的值為準 第二個一樣數值的值反推號碼會無法顯示 有什麼辦法解決嗎第二個問題有辦法利用vlookup 來限制 和我請教你的平均值一樣的區間嗎？ 有就是輸入號碼值往前推幾筆的區間用vlookup 反推號碼值

以回文舉例回傳同號碼第二筆的話，可用區間offset配合match的方式

回傳寫法VLOOKUP(M3,IF({1,0},J15:J24,I15:I24),2,0)所以要增加區間判別要改掉J15:J24,I15:I24這一段囉?

測試上可修改j15:j24和i15:i24

https://goo.gl/zb6V7i 反推號碼的函式有新增了如附件的紅色框框 想問一下 當如果往前抓的直改為7因為數值A有兩筆是5，要怎麼把反推號碼值 兩個對應到的一起顯示出來?

抱歉不太了解，是指抓取兩筆數值A為5，而顯示數值B的14,24嗎？

因為數值A有兩個5當我的區間範圍變大 反推回去的號碼應該會有兩筆資料號碼是對應到數值A的5。但我目前寫法只能抓到一筆號碼 想在抓另一筆對應的號碼要怎麼處理

抱歉不太了解如是以數值A的5為條件將對應一筆以上的號碼抓出的話，可以index配合small+if的方式

所以一對多 就不適合用vlookup嗎？

1對2應可用vlookup，1對3以上用vlookup使用上我會加上輔助欄來抓前一筆的列號

https://goo.gl/gXCNR9 那像附件這個範例 數值A是5對應的號碼應該要顯示3與8 可以只用VLOOKUP就好?

可以 http://imgur.com/FA2g7T9 測試上是用vlookup配合offset和match第二筆為第一筆match的列號+1的範圍來對應

-(MATCH(K3,I15:I24,0)-MATCH(K3-K5,I15:I24,0)-1)把+1 改-1吧?

以連結內改-2可帶出8

突然發現這個列號+1的方法 有一個缺點就是當數值A連續兩筆都是一樣的值 回傳的號碼還是會一樣的而且用MATCH如果遇到篩選 他所對應到的列數好像會跑掉

+1的方法，連續兩筆一樣，回傳號碼一樣方面，不太了解原po的意思遇到篩選？原po在該問題vlookup上首次說到會用到篩選，所以該問題的回文並無考慮到這方面上面寫到的該問題為原文01/13 14:38起01/14 08:57並無提及篩選方面

OK 好 所以如果加篩選條件 寫法就要改囉?

加篩選上，如要使用vlookup的話，我會加上輔助欄來針對數值A被隱藏的話則為空字串

請問有大概的語法嗎？

輔助欄寫法同號碼i15:i24的判斷