標題: 取((某儲存格固定+8個數值)/39)的餘數，當餘數=0時，則以39顯示。 [打印本頁]

---

作者: papaya    時間: 2019-2-12 15:09     標題: 取((某儲存格固定+8個數值)/39)的餘數，當餘數=0時，則以39顯示。

[attach]30087[/attach]

將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版函數公式?
謝謝!

範例附檔︰
[attach]30088[/attach]

---

作者: 准提部林    時間: 2019-2-12 15:54

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,"")
右拉/下拉

---

作者: papaya    時間: 2019-2-12 16:54

回復 2# 准提部林

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

---

作者: papaya    時間: 2019-2-12 19:27

回復 2# 准提部林
[attach]30095[/attach]
版主大大:
不好意思，想將Sheet1的BT3公式(即2#的解答公式)併入DM3公式，以期能省略45欄。
需求說明詳如Sheet2

不知能否再煩請您指導!謝謝您!
範例附檔:
[attach]30096[/attach]

---

作者: 准提部林    時間: 2019-2-13 13:34

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;;;")

---

作者: papaya    時間: 2019-2-13 16:09

本帖最後由 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也非常棒，值得研習，所以再次打擾和請教，敬請見諒!謝謝您!

[attach]30098[/attach]

---

作者: 准提部林    時間: 2019-2-13 16:35

回復 6# papaya


公式1, 拉到最後不會出現99的~~

---

作者: papaya    時間: 2019-2-13 17:46

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

---

作者: 准提部林    時間: 2019-2-13 19:21

回復 8# papaya

看附件:
[attach]30099[/attach]

公式2所得的數字是"文字格式", 可能無法做其它運算,
公式1是"數值格式"

---

作者: papaya    時間: 2019-2-13 20:38

回復 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

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)