返回列表 上一主題 發帖

[發問] 函數_指定對應數字再顯示它列的公式。

[發問] 函數_指定對應數字再顯示它列的公式。

本帖最後由 Airman 於 2016-1-31 10:20 編輯

TEST.rar (17.29 KB)

請問︰符合如下說明需求的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: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修改後前段公式~
後段公式~   敬請各位高手不吝賜教是幸!感恩

TOP

陣列:
=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,"")
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

本帖最後由 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個儲存格單獨公式?(以備當要單獨用時之需)~
謝謝您

TOP

本帖最後由 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之和值的公式需求~
敬請您賜教為禱~感恩

TOP

回復 5# Airman


公式不難,要會〔層層〕逐步解,以下用F9逐一檢視:
U7:=SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))  
   {0,0,0,33,0,0,21}
V7:=SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))^0  
   {#NUM!,#NUM!,#NUM!,1,#NUM!,#NUM!,1}
W7:=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}
X7:=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}

公式及程式,其實原理差不多,要從錯誤中去修正,了解錯在哪?才能學到真正的點,
今天做不出來,明天再來,這是我強調的〔三花〕.花時間.花精神.花體力,
VBA則再加〔一花〕>〔花錢〕>買書看∼∼∼沒有捷徑的!
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

回復 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 都是將陣列收合為單一值,須注意運用時機!
多揣摩幾次吧!!!
 
 
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

可再用以下公式逐一推敲!!!

=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,"")
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

本帖最後由 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有相同值就是修改需求後的答案了

TOP

回復 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之同欄位儲存格。

謝謝您^^

TOP

        靜思自在 : 做好事不能少我一人,做壞事不能多我一人。
返回列表 上一主題