標題:
[發問]
函數_指定對應數字再顯示它列的公式。
[打印本頁]
作者:
Airman
時間:
2016-1-31 10:14
標題:
函數_指定對應數字再顯示它列的公式。
本帖最後由 Airman 於 2016-1-31 10:20 編輯
[attach]23212[/attach]
請問︰符合如下說明需求的
T7
公式要如何增編？謝謝！
如果函數公式
無法
達到
需求
，亦勞駕告知。再次謝謝！
當I欄的T$5期數之J︰P儲存格數字有顯示在I欄的$R7期數之J︰P儲存格，
且I欄的T$5期數之J︰P儲存格的該數字在I欄的$R7期數之J︰P對應儲存格數字，
亦有再顯示於I欄的$R7+T$3期數之J︰P儲存格時~
則
$T7
儲存格顯示$R7+T$3之和值，否則顯示空白
。
EX1︰I欄的T$5期數之M94(=33)有顯示在N7(=33)，且M94在$R7期數之M7對應儲存格數字(=13)，
亦有再顯示於I欄的$R7+T$3期數之K13(=13)儲存格時~則$T7顯示$R7+T$3之和值(=13)。
EX2︰I欄的T$5期數之J94(=23)有顯示在P15(=23)，且J94在$R15期數之J15對應儲存格數字(=04)，
亦有再顯示於I欄的$R15+T$3期數之P27(=04)儲存格時~則$T15顯示$R15+T$3之和值(=21)。
EX3︰I欄的T$5期數之O94(=38)有顯示在M34(=38)，且O94在$R34期數之O34對應儲存格數字(=43)，
亦有再顯示於I欄的$R34+T$3期數之N46(=43)儲存格時~則$T34顯示$R34+T$3之和值(=40)。
EX4︰I欄的T$5期數之N94(=36)有顯示在N35(=36)，且N94在$R35期數之N35對應儲存格數字(=36)，
亦有再顯示於I欄的$R35+T$3期數之N47(=36)儲存格時~則$T35顯示$R35+T$3之和值(=41)。
其餘......同理類推。
詳如附件。
作者:
Airman
時間:
2016-2-1 16:02
本帖最後由 Airman 於 2016-2-1 16:17 編輯
回復
1#
Airman
以
公式修改
的方式
作
補充說明
︰
本來的
公式
都是以
$R$5值
為
搜尋標準
~
EX︰
=IF(OR($R7="",$R7>=T$5),"",IF(
(COUNT
(RANK(
$R$5
,OFFSET($I$6,CHOOSE({1,2},T$5,$R7),1,,7)))=2
)
*
OR(
SUM((
$R$5
=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7))=OFFSET($I$6,$R7+T$3,1,,7)
)
,$R7+T$3,""))
但因有的
$R$5值
會
100期
都無
答案~(EX︰
$R$5=21
)~
所以
不再限制為
$R$5
儲存格
值
，而將其
擴大為
一期7個儲存格值
。
但
前段
學習了，
後段
自行修改
一直沒有成功
~因此
提問
只PO修改後
之
前段
公式~
後段
公式~
敬請各位高手不吝賜教是幸!感恩
作者:
准提部林
時間:
2016-2-1 17:02
陣列:
=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)/SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))^0)),R7+T$3,"")
作者:
Airman
時間:
2016-2-1 17:41
本帖最後由 Airman 於 2016-2-1 17:42 編輯
回復
3#
准提部林
准大:
測試OK了~感恩
可否另請您再順道賜教
後段
公式~
OR(SUM((
$R$5
=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7))=OFFSET($I$6,$R7+T$3,1,,7))
由
$R$5
儲存格
值
擴大為
一期7個
儲存格
值
的
單獨
公式?(以備當要單獨用時之需)~
謝謝您
作者:
Airman
時間:
2016-2-2 11:55
本帖最後由 Airman 於 2016-2-2 12:08 編輯
回復
3#
准提部林
准大:
不好意思，雖然SUMPRODUCT,SUMIF,OFFSET等3個函數的意義都了解，但為何能以"
/
"連結而達到需求答案~至今尚不知所以然
初看貴解時，小弟認為應該是後段公式將$R$5去掉後，擴大為T$5期數的7個儲存格值在$R7期數的對應值有再顯示於$R7+T$3期數之J︰P儲存格~
但不論
有
或
沒有
加上
前段
公式，其答案
都是
一樣的
正解
答案~因此以答案說來，小弟又認為貴解答公式應該是將二段公式需求合併成一整段~
也因此才再提出
#4
的 *OR(SUM(($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7))=OFFSET($I$6,$R7+T$3,1,,7))
將$R$5值擴大為T$5期數的7個儲存格值在$R7期數的對應值有再顯示於$R7+T$3期數之J︰P儲存格時，顯示$R7+T$3之和值的公式需求~
敬請您賜教為禱~感恩
作者:
准提部林
時間:
2016-2-2 12:56
回復
5#
Airman
公式不難，要會〔層層〕逐步解，以下用Ｆ９逐一檢視：
Ｕ７：=SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))
{0,0,0,33,0,0,21}
Ｖ７：=SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))^0
{#NUM!,#NUM!,#NUM!,1,#NUM!,#NUM!,1}
Ｗ７：=OFFSET($I$6,R7,1,,7)/SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))^0
{#NUM!,#NUM!,#NUM!,13,#NUM!,#NUM!,21}
Ｘ７：=SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)/SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))^0)
{0,0,0,13,0,0,0}
公式及程式，其實原理差不多，要從錯誤中去修正，了解錯在哪？才能學到真正的點，
今天做不出來，明天再來，這是我強調的〔三花〕．花時間．花精神．花體力，
ＶＢＡ則再加〔一花〕＞〔花錢〕＞買書看∼∼∼沒有捷徑的！
作者:
准提部林
時間:
2016-2-2 13:06
回復
5#
Airman
OR(
SUM(($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7))
=OFFSET($I$6,$R7+T$3,1,,7))
SUM 將 ($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7) 加總，
哪有可能與 OFFSET($I$6,$R7+T$3,1,,7) 比對？？？
SUM 與 OR 都是將陣列收合為單一值，須注意運用時機！
多揣摩幾次吧！！！
作者:
准提部林
時間:
2016-2-2 13:41
可再用以下公式逐一推敲!!!
=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*(SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))>0))),R7+T$3,"")
=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*(COUNTIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))))),R7+T$3,"")
=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*ISNUMBER(RANK(OFFSET($I$6,T$5,1,,7),OFFSET($I$6,R7,1,,7))))),R7+T$3,"")
=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*(RANK(OFFSET($I$6,T$5,1,,7),OFFSET($I$6,R7,1,,7))>0))),R7+T$3,"")
=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*(MATCH(OFFSET($I$6,T$5,1,,7),OFFSET($I$6,R7,1,,7),)>0))),R7+T$3,"")
作者:
Airman
時間:
2016-2-2 14:26
本帖最後由 Airman 於 2016-2-2 14:29 編輯
回復
8#
准提部林
准大:
謝謝您的耗時費神的耐心教導~感恩
小弟尚在研究和努力中~回應先
另外~
SUM 將 ($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7) 加總，
哪有可能與 OFFSET($I$6,$R7+T$3,1,,7) 比對？？？
您誤會了~
小弟的意思是要將~T$5期數有$R$5值且當與該$R$5值同欄位的$R7期數之儲存格值亦有顯示在$R7+T$3期數之J︰P儲存格時，則顯示$R7+T$3之和值~的上述公式~
改為~
當T$5期數的7個儲存格值，其在$R7期數同欄位的儲存格值亦有顯示在$R7+T$3期數之J︰P儲存格時
，則顯示$R7+T$3之和值。
但現在想通了~是自己將它想得太複雜而掉進死胡同~只要
$R7期數和$R7+T$3有相同值
就是修改需求後的答案了
作者:
Airman
時間:
2016-2-4 20:06
回復
8#
准提部林
准大:
不好意思~下列的貴2個解答公式~
=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,$R7+T$3,1,,7),OFFSET($I$6,$R7,1,,7)*(SUMIF(OFFSET($I$6,$R7,1,,7),OFFSET($I$6,T$5,1,,7))>0))),$R7+T$3,"")
=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,$R7+T$3,1,,7),OFFSET($I$6,$R7,1,,7)*(RANK(OFFSET($I$6,T$5,1,,7),OFFSET($I$6,$R7,1,,7))>0))),$R7+T$3,"")
要如何將其中顯示於$R7+T$3的J︰P
任一
儲存格之條件~
改成
顯示於$R7+T$3的J︰P之
同欄位
儲存格。
謝謝您^^
作者:
准提部林
時間:
2016-2-5 09:41
本帖最後由 准提部林 於 2016-2-5 17:21 編輯
同欄位不就是：
Ａ區
＝
Ｂ區
=IF(SUMPRODUCT(N(
OFFSET($I$6,$R7+T$3,1,,7)
=
OFFSET($I$6,$R7,1,,7)*(SUMIF(OFFSET($I$6,$R7,1,,7),OFFSET($I$6,T$5,1,,7))>0)
)),$R7+T$3,"")
作者:
Airman
時間:
2016-4-14 06:28
回復
11#
准提部林
准大:
呵~呵~當天只
有
看到
提醒
，但奇怪的是
連續數日
帖中一直
沒有
顯示答案
。
今天因為上網查資料，才突然看到貴解(日期還是當天2/5的)~真玄!
謝謝您的耐心和費神指導!感恩
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
