作者Jerome0511 (Jerome)
看板Office
標題[問題] 篩選搭配offset 取平均
時間Thu Jan 7 16:12:38 2016
(若是和其他不同軟體互動之問題 請記得一併填寫)
軟體:excel
版本:2010
檔案在此:
https://goo.gl/RlonPF
目前遇到的問題是隨機篩選號碼欄，但無法算平均
不知道是出現什麼問題，指令是用offset搭配SUBTOTAL
謝謝。
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 211.21.159.187
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1452154361.A.D64.html
2F：→ Jerome0511: 數值有點怪 如果你把篩選全部展開之後平均值是19 01/08 11:56
5F：→ Jerome0511: 改完之後，若輸入號碼改3 往前抓一筆平均值變14是錯的 01/08 13:43
8F：→ soyoso: 連結圖片內在-後有再用括號包起來，原po新提供的連結沒有 01/08 14:18
9F：→ Jerome0511: OK謝謝 請教一下有類似Averageif搭配subtotal的指令嗎 01/08 16:36
10F：→ soyoso: 是要使用以函數averageif來寫嗎？ 01/08 18:24
11F：→ Jerome0511: 對啊 但一樣要搭配篩選用 01/08 20:24
13F：→ Jerome0511: 不好意思沒說清楚，我是想說有辦法先篩選完號碼之後 01/08 22:40
14F：→ Jerome0511: 再拉一個欄位的數值，當數值大於15其平均值為多少 01/08 22:41
16F：→ Jerome0511: (16+22)/2=19 01/08 22:47
18F：→ Jerome0511: 不好意思 你算的>15的平均值 我需要的是也需要在輸入 01/11 11:01
19F：→ Jerome0511: 號碼欄之後 完前抓一筆資料的平均值，非整體的 01/11 11:01
20F：→ Jerome0511: 也就是當我輸入號碼3 往前抓1筆資料，其avrageif要在 01/11 11:02
21F：→ Jerome0511: 此區間範圍內 01/11 11:03
22F：→ soyoso: 那將">0"的條件換掉為">=1"，再加一個條件是"<=4" 01/11 11:46
23F：→ soyoso: 儲存格當變數用連接符號& 01/11 11:46
24F：→ soyoso: 以回文連結條件，就會是(16+22)/2=19 01/11 11:48
26F：→ Jerome0511: 請問一下為什麼號碼那一欄需要那麼多函式 用到 01/11 13:44
27F：→ Jerome0511: 用了IF、COUNTIF、SUBTOTAL 和只用SUBTOTAL有何差別? 01/11 13:45
28F：→ Jerome0511: 所以其實Averageif其實就自己有內建SUBTOTAL的功能嗎? 01/11 13:46
29F：→ Jerome0511: 所以其實Averageif其實就自己有內建SUBTOTAL的功能嗎? 01/11 13:47
30F：→ soyoso: 加上if、countif和只用subtotal的差別，原po可在其他儲存 01/11 13:51
31F：→ soyoso: 格打上=i11，下拉10格儲存格就可以看的出差別 01/11 13:51
32F：→ soyoso: subtotal的功能，這裡的功能是指？ 01/11 13:56
33F：→ Jerome0511: if、countif和只用subtotal的差別，剛試過往下拉儲存 01/11 14:27
※ 編輯: Jerome0511 (60.251.182.146), 01/11/2016 14:39:21
35F：→ Jerome0511: 大於15平均值的條件，算出來也怪怪的，以附件範例為例 01/11 14:40
36F：→ Jerome0511: 在想說averageif是不是也要用offset的寫法 01/11 14:43
37F：→ Jerome0511: 因為subtotal的用法不是被篩選後的數值不會被考慮進去 01/11 14:44
38F：→ Jerome0511: 如果單純只用averageif 那隱藏的數值不是會被算到嗎? 01/11 14:45
40F：→ soyoso: 附件為例，條件是i11:i21>=1和i11:i21<=4符合為藍框 01/11 16:39
42F：→ soyoso: 重覆之處為22,16的平均19，這非原po要的嗎? 01/11 16:41
44F：→ Jerome0511: 但如果你把輸入號碼改為8平均值是錯的可參考我上面貼 01/11 17:11
46F：→ Jerome0511: 所以Averageif會算到隱藏的連結，所以才問有沒有 01/11 17:13
47F：→ Jerome0511: 類似Averageif搭配subtotal的指令 01/11 17:13
48F：→ soyoso: 輸入號碼改為8那公式一樣是i11:i21>=1和i11:i21<=4嗎？ 01/11 17:14
49F：→ soyoso: 公式內的1和4要當變數，於今天上午11:46回文就有寫到 01/11 17:17
50F：→ soyoso: 而非只是打>=1和<=4這樣 01/11 17:18
51F：→ Jerome0511: I11:I21,">=1",I11:I21,"<="&I3 這樣是錯的嗎? 01/11 17:19
52F：→ soyoso: 公式有報錯嗎？我測試沒有，所以語法沒有不正確 01/11 17:22
53F：→ soyoso: 只是結果是否是原po要的而已 01/11 17:22
54F：→ soyoso: 原po輸入號碼為8，往前抓3筆為5，i11:i21的區間就要以這個 01/11 17:24
55F：→ soyoso: 為範圍，如何產生">=5"，可用">="&i3-i5 01/11 17:25
56F：→ Jerome0511: Ok 沒問題了 只是你回文有提到averagif 會算到隱藏值 01/12 08:58
57F：→ Jerome0511: 這個有解嗎？ 01/12 08:58
58F：→ Jerome0511: 是不是加了">="&i3-i5 "<="&i3。 與j11:j21>我要的限 01/12 09:06
59F：→ Jerome0511: 制範圍 就可以踢除隱藏數值直接算平均 01/12 09:06
60F：→ soyoso: averagifs要剔除隱藏數值，想到的是配合subtotal的i11:i20 01/12 11:05
61F：→ soyoso: 如有配合的話，如檔案測試應可剔除隱藏數值算平均 01/12 11:13
63F：→ Jerome0511: SUBTOTAL 就可以剔除隱藏值 這是什麼原因? 01/12 11:24
64F：→ Jerome0511: 還有我想增加一欄數量用COUNTIFS寫但會出現引數太少 01/12 11:25
65F：→ Jerome0511: 是什麼原因呢? 01/12 11:25
66F：→ soyoso: 以附件為例，i11:i20不就用到subtotal，為何回文寫不需要 01/12 11:36
67F：→ soyoso: 用到subtotal呢？且公式averageifs內也有配合i11:i21 01/12 11:37
68F：→ soyoso: countifs寫引數太少應表示，填寫時省略必需要引數，例如有 01/12 11:40
69F：→ soyoso: 範圍，卻無條件 01/12 11:40
70F：→ Jerome0511: 哦 原來如此 subtotal 可以直接先用在i11:i21 average 01/12 12:07
71F：→ Jerome0511: if 那邊可以不需要在寫一次subtotal 會直接套用i11:i2 01/12 12:07
72F：→ Jerome0511: 1的subtotal 囉？ 01/12 12:07
73F：→ Jerome0511: 但我是直接把averagifs 那邊的函式 後面的挑件原封不 01/12 12:10
74F：→ Jerome0511: 動 只改countifs應該沒有判斷不足的問題吧？ 01/12 12:10
75F：→ soyoso: 如原po所述，averageifs內就不用再寫一次 01/12 12:13
76F：→ soyoso: averageifs改為countifs，那請將average_range的範圍拿掉 01/12 12:17
77F：→ Jerome0511: OK感謝你。 01/12 16:56
78F：→ Jerome0511: 請問一下 想利用vlookup由數值反推號碼 如果數值有兩 01/13 14:38
79F：→ Jerome0511: 個一樣的 那反推回來的號碼會以先搜尋到的值為準 第二 01/13 14:38
80F：→ Jerome0511: 個一樣數值的值反推號碼會無法顯示 有什麼辦法解決嗎 01/13 14:38
81F：→ Jerome0511: 第二個問題有辦法利用vlookup 來限制 和我請教你的平 01/13 14:40
82F：→ Jerome0511: 均值一樣的區間嗎？ 有就是輸入號碼值往前推幾筆的區 01/13 14:41
83F：→ Jerome0511: 間用vlookup 反推號碼值 01/13 14:41
84F：→ soyoso: 以回文舉例回傳同號碼第二筆的話，可用區間offset配合 01/13 16:00
85F：→ soyoso: match的方式 01/13 16:00
86F：→ Jerome0511: 回傳寫法VLOOKUP(M3,IF({1,0},J15:J24,I15:I24),2,0) 01/13 16:14
87F：→ Jerome0511: 所以要增加區間判別要改掉J15:J24,I15:I24這一段囉? 01/13 16:14
88F：→ soyoso: 測試上可修改j15:j24和i15:i24 01/13 16:31
90F：→ Jerome0511: 如附件的紅色框框 想問一下 當如果往前抓的直改為7 01/13 20:34
91F：→ Jerome0511: 因為數值A有兩筆是5，要怎麼把反推號碼值 兩個對應到 01/13 20:35
92F：→ Jerome0511: 的一起顯示出來? 01/13 20:35
93F：→ soyoso: 抱歉不太了解，是指抓取兩筆數值A為5，而顯示數值B的14,24 01/13 20:57
94F：→ soyoso: 嗎？ 01/13 20:57
95F：→ Jerome0511: 因為數值A有兩個5當我的區間範圍變大 反推回去的號碼 01/13 21:31
96F：→ Jerome0511: 應該會有兩筆資料號碼是對應到數值A的5。但我目前寫法 01/13 21:31
97F：→ Jerome0511: 只能抓到一筆號碼 想在抓另一筆對應的號碼要怎麼處理 01/13 21:31
98F：→ soyoso: 抱歉不太了解 01/13 21:54
99F：→ soyoso: 如是以數值A的5為條件將對應一筆以上的號碼抓出的話，可以 01/13 21:58
100F：→ soyoso: index配合small+if的方式 01/13 21:58
101F：→ Jerome0511: 所以一對多 就不適合用vlookup嗎？ 01/13 22:08
102F：→ soyoso: 1對2應可用vlookup，1對3以上用vlookup使用上我會加上輔助 01/13 22:11
103F：→ soyoso: 欄來抓前一筆的列號 01/13 22:11
105F：→ Jerome0511: 對應的號碼應該要顯示3與8 可以只用VLOOKUP就好? 01/13 22:21
107F：→ soyoso: offset和match 01/13 22:34
108F：→ soyoso: 第二筆為第一筆match的列號+1的範圍來對應 01/13 22:48
109F：→ Jerome0511: -(MATCH(K3,I15:I24,0)-MATCH(K3-K5,I15:I24,0)-1) 01/13 23:34
110F：→ Jerome0511: 把+1 改-1吧? 01/13 23:34
111F：→ soyoso: 以連結內改-2可帶出8 01/13 23:46
112F：→ Jerome0511: 突然發現這個列號+1的方法 有一個缺點就是當數值A連 01/14 08:57
113F：→ Jerome0511: 續兩筆都是一樣的值 回傳的號碼還是會一樣的 01/14 08:57
114F：→ Jerome0511: 而且用MATCH如果遇到篩選 他所對應到的列數好像會跑掉 01/14 09:12
115F：→ soyoso: +1的方法，連續兩筆一樣，回傳號碼一樣方面，不太了解原po 01/14 10:25
116F：→ soyoso: 的意思 01/14 10:25
117F：→ soyoso: 遇到篩選？原po在該問題vlookup上首次說到會用到篩選，所 01/14 10:26
118F：→ soyoso: 以該問題的回文並無考慮到這方面 01/14 10:27
119F：→ soyoso: 上面寫到的該問題為原文01/13 14:38起01/14 08:57並無提及 01/14 10:30
120F：→ soyoso: 篩選方面 01/14 10:30
121F：→ Jerome0511: OK 好 所以如果加篩選條件 寫法就要改囉? 01/14 11:34
122F：→ soyoso: 加篩選上，如要使用vlookup的話，我會加上輔助欄來針對數 01/14 11:43
123F：→ soyoso: 值A被隱藏的話則為空字串 01/14 11:44
124F：→ Jerome0511: 請問有大概的語法嗎？ 01/14 18:49
125F：→ soyoso: 輔助欄寫法同號碼i15:i24的判斷 01/15 01:14