返回列表 上一主題 發帖

[發問] 請問sum函數如何加總不確定的範圍?(目前用match無法解決)

summergod

帖子
2 
主題
1 
精華
0 
積分
4 
點名
0  
作業系統
windows7 
軟體版本
office2010 
閱讀權限
10 
性別
男 
來自
台灣台南 
註冊時間
2015-5-11 
最後登錄
2015-5-13 
1# 跳轉到 » 正序看帖
打印
tT
發表於 2015-5-11 21:39 | 只看該作者

[發問] 請問sum函數如何加總不確定的範圍?(目前用match無法解決)

本帖最後由 summergod 於 2015-5-11 21:41 編輯

請問各位前輩 如果我想在一串亂數資料中 統計 "和周圍數字加起來等於20" 的資料有幾個 該如何做呢?

ex :
            資料--1  18  20   5  16  17  11   8  13 15   5   2  18   7   1   1   10   6   2
符合的情況--             X                                            X       X                              X
=> X總共有4個

目前因為不知道sum的範圍要加幾個
所以只好用這種寫法在資料旁邊一個個加----if( sum(a1:a2)=20, "X", if( sum(a1:a3)=20, "X", if( sum(a1:a4)=20, "X".............)
(就是用sum一格兩格三格....到N格這樣去檢查，最後再統計"X"的個數)

請問有什麼函數的組合方式可以擺脫這種地獄 ? QAQ
收藏 分享

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
4#
發表於 2015-5-14 15:56 | 只看該作者
本帖最後由 ML089 於 2015-5-14 15:59 編輯

回復 3# summergod


   

B2 =IF(ISNA(MATCH(20,SUBTOTAL(9,OFFSET(B1,,,,{1,2,3,4,5,6,7,8,9})),)),"","X")

公式右拉複製


20天降雨需要將 {1,2,3,4,5,6,7,8,9} 改為 {1,2,3,4,5,6,7,8,9 .... 20}
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

summergod

帖子
2 
主題
1 
精華
0 
積分
4 
點名
0  
作業系統
windows7 
軟體版本
office2010 
閱讀權限
10 
性別
男 
來自
台灣台南 
註冊時間
2015-5-11 
最後登錄
2015-5-13 
3#
發表於 2015-5-13 11:45 | 只看該作者
回復 2# tyrone123456


多謝前輩指點
之前有用過VBA
不過因為每次都要再重跑一次而且跑很久(巨集寫得太繁瑣了orz
所以才想用函數一次解決

這個問題的原型大概如下--
假設那些變數為"每次降雨日所間隔的天數" ( 第1次降雨後過了X天後又降雨...這樣的表現 ),那要如何統計出"到了20天一定會降雨"的情況?

目前自己的解決方式 就回到原始的資料那裏 用countifs 來統計兩個範圍(今天&20天前)都有">=1"的情況
得到統計結果
算是解脫了函數地獄  
不過如果以後再遇到類似的情況可能會GG

所以如果各位前輩有什麼點子還請不吝賜教 感謝大家

TOP

tyrone123456

帖子
49 
主題
0 
精華
0 
積分
54 
點名
0  
作業系統
WinXP 
軟體版本
Office 2003 
閱讀權限
20 
性別
男 
註冊時間
2015-4-14 
最後登錄
2015-7-16 
2#
發表於 2015-5-12 21:18 | 只看該作者
建議使用VBA來解決，單純用公式難以解決，因為這是階乘數學，例如假設有10個變數，那就有
10+9+8+...+2+1=55種組合，也就是要寫55個if，這是不可能的，因為Excel的儲存格內好像只能寫10個if，所以必需使用VBA的寫法，也就是迴圈寫法

TOP

        靜思自在 : 得理要饒人，理直要氣和。
返回列表 上一主題