返回列表 上一主題 發帖

取((某儲存格固定+8個數值)/39)的餘數,當餘數=0時,則以39顯示。

取((某儲存格固定+8個數值)/39)的餘數,當餘數=0時,則以39顯示。

未命名.png
2019-2-12 15:07


將D2固定+1,2,3,5,8,13,21,34等8個和值,各/39的餘數顯示在CL2︰CS2,如果餘數=0時,則以39顯示。
將E2固定+1,2,3,5,8,13,21,34等8個和值,各/39的餘數顯示在CU2︰DB2,如果餘數=0時,則以39顯示。
將F2固定+1,2,3,5,8,13,21,34等8個和值,各/39的餘數顯示在DD2︰DK2,如果餘數=0時,則以39顯示。
將G2固定+1,2,3,5,8,13,21,34等8個和值,各/39的餘數顯示在DM2︰DT2,如果餘數=0時,則以39顯示。
將H2固定+1,2,3,5,8,13,21,34等8個和值,各/39的餘數顯示在DV2︰EC2,如果餘數=0時,則以39顯示。

請問︰
CL2右拉下拉一式到位(或CL2,CU2,DD2,DM2,DV2等5段)的2003版函數公式?
謝謝!

範例附檔︰
單儲存格固定+8個數值.rar (162.57 KB)

N1:
=MOD($D2+INDEX({1,2,3,5,8,13,21,34},COLUMN(A$1))-1,39)+1
N2~N5修改紅色位置

或:
=IF(MOD(COLUMN(A1),9),MOD(INDEX(2:2,INT(COLUMN(I$1)/9)+3)+INDEX({1,2,3,5,8,13,21,34},MOD(COLUMN(A1),9))-1,39)+1,"")
右拉/下拉
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

回復 2# 准提部林

准提版大:
測試成功^^
謝謝您的指導!感恩

TOP

回復 2# 准提部林
合併公式未命名.png
2019-2-12 19:25

版主大大:
不好意思,想將Sheet1的BT3公式(即2#的解答公式)併入DM3公式,以期能省略45欄。
需求說明詳如Sheet2

不知能否再煩請您指導!謝謝您!
範例附檔:
合併公式.rar (163.44 KB)

TOP

BT2/陣列公式:
=IF(MAX(MOD($D2:$H2+{1;2;3;5;8;13;21;34}-1,39)+1)=MAX($BS2:BS2),"",SMALL(IF(FREQUENCY(MOD($D2:$H2+{1;2;3;5;8;13;21;34}-1,39)+1,ROW($1:$39)),ROW($1:$39),99),COLUMN(A$1)))

或:
=TEXT(SMALL(IF(FREQUENCY(MOD($D2:$H2+{1;2;3;5;8;13;21;34}-1,39)+1,ROW($1:$39)),ROW($1:$39),99),COLUMN(A$1)),"[<40]0;;;")
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

本帖最後由 papaya 於 2019-2-13 16:21 編輯

回復 5# 准提部林
版主大大:
2個公式都是正解~感謝您的回覆和解答。

請教一下:
公式1
=IF(MAX(MOD($D2:$H2+{1;2;3;5;8;13;21;34}-1,39)+1)=MAX($BS2:BS2),"",SMALL(IF(FREQUENCY(MOD($D2:$H2+{1;2;3;5;8;13;21;34}-1,39)+1,ROW($1:$39)),ROW($1:$39),99),COLUMN(A$1)))
因為公式"一定要"拉到39格, 才知最後結果,所以當取1的數值個數<=39個,其餘後面的空格會顯示99

有試將99套改為公式2的"[<40]0;;;"。但個數<=39個的多餘後面的空格會顯示#NUM!,不是"空白"^^"

目前是採用公式2,但覺得公式1也非常棒,值得研習,所以再次打擾和請教,敬請見諒!謝謝您!

未命名.png
2019-2-13 16:16

TOP

回復 6# papaya


公式1, 拉到最後不會出現99的~~
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

回復 7# 准提部林
我測試是會~在39格的範圍內的39號數值之後會顯示99(如#6的圖片)。
沒關係,有公式2就很好了。
謝謝您

TOP

回復 8# papaya

看附件:
合併公式v01.rar (183.18 KB)

公式2所得的數字是"文字格式", 可能無法做其它運算,
公式1是"數值格式"
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

回復 9# 准提部林
版主大大:
抓出bug了
=IF(MAX(MOD($D2:$H2+{1;2;3;5;8;13;21;34}-1,39)+1)=MAX($BS2:BS2),"",SMALL(IF(FREQUENCY(MOD($D2:$H2+{1;2;3;5;8;13;21;34}-1,39)+1,ROW($1:$39)),ROW($1:$39),99),COLUMN(A$1)))
因為比對要差1列(BT3要比對$D2:$H2的5個數字+{1;2;3;5;8;13;21;34},
所以只要把$BS2:BS2改為$BS3:BS3,公式的答案值就全對了!
因此不是公式不對,是我學藝不精^^"

感謝您的耐心指導,讓我學習到許多,感恩b^_^d

TOP

        靜思自在 : 人事的艱難與琢磨,就是一種考驗。
返回列表 上一主題