跨工作表LINK(讀取)某個指定位置的值，有簡單的方法嗎

帖子: 17
主題: 4
精華: 0
積分: 21
點名: 0
作業系統: WINDOWS
軟體版本: OFFICE 2003
閱讀權限: 10
性別: 男
註冊時間: 2012-6-15
最後登錄: 2018-10-16

11^# 跳轉到 »

發表於 2014-9-30 15:30 | 只看該作者

檔案欄位N~AE：是算費用分攤的月份(算頭不算尾)，超過12(即Y欄)，就算是隔年(本商品最多發行6個月，所以才有13月~18月)
==>單一儲存格公式太多，檢核很辛苦會漏，且每換一列，追綜列就要動態變動

檔案如下網址 : 要下載到個人電腦才能看~
https://docs.google.com/file/d/0BytNKnm2laTvQXFaQUE2bTNRRlNXMThwQjFFOVlNR0gzTFF3/edit?usp=sharing

TOP

u7490074

小學生 Rank: 1

帖子: 17
主題: 4
精華: 0
積分: 21
點名: 0
作業系統: WINDOWS
軟體版本: OFFICE 2003
閱讀權限: 10
性別: 男
註冊時間: 2012-6-15
最後登錄: 2018-10-16

12^#

發表於 2014-9-30 15:51 | 只看該作者

檔案欄位N~AE：是算費用分攤的月份(算頭不算尾)，超過12(即Y欄)，就算是隔年(本商品最多發行6個月，所以才有 ...
u7490074 發表於 2014-9-30 15:30

對了,因為這樣長的公式←無果再加上INDIRECT("M"&ROW())則因為公式太長會被EXCEL拒絕再修改~~~困擾且不好編修~~

【R13儲存格】
=IF(AND(R12<=12,DATE(YEAR(INDIRECT("$b"&ROW())),MONTH(INDIRECT("$b"&ROW()))+1,)=DATE(YEAR(INDIRECT("$b"&ROW())),R12+1,)),ROUND($H13*($L13/$G13),0),IF(AND(R12<=12,DATE(YEAR(INDIRECT("$c"&ROW())),MONTH(INDIRECT("$c"&ROW()))+1,)=DATE(YEAR(INDIRECT("$b"&ROW())),R12+1,)),ROUND($H13*($M13/$G13),0),IF(AND(R12>12,DATE(YEAR(INDIRECT("$C"&ROW())),MONTH(INDIRECT("$C"&ROW()))+1,)=DATE(YEAR(INDIRECT("$b"&ROW()))+1,(R12-12)+1,)),ROUND($H13*($M13/$G13),0),0)))+IF(AND(R12<=12,DATE(YEAR(INDIRECT("$b"&ROW())),MONTH(INDIRECT("$b"&ROW()))+1,)<DATE(YEAR(INDIRECT("$b"&ROW())),R12+1,),DATE(YEAR(INDIRECT("$b"&ROW())),R12+1,)<DATE(YEAR(INDIRECT("$C"&ROW())),MONTH(INDIRECT("$C"&ROW()))+1,)),ROUND($H13*(DAY(DATE(YEAR(INDIRECT("$C"&ROW())),R12+1,))/$G13),0),)+IF(AND(R12>12,DATE(YEAR(INDIRECT("$b"&ROW())),MONTH(INDIRECT("$b"&ROW()))+1,)<DATE(YEAR(INDIRECT("$b"&ROW()))+1,(R12-12)+1,),DATE(YEAR(INDIRECT("$b"&ROW()))+1,(R12-12)+1,)<DATE(YEAR(INDIRECT("$C"&ROW())),MONTH($C13)+1,)),ROUND($H13*(DAY(DATE(YEAR($B13)+1,(R12-12)+1,))/$G13),0),0)

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

13^#

發表於 2014-10-1 09:05 | 只看該作者

回復 12# u7490074

檔案我看了，但沒有看到使用 INDIRECT("M"&ROW())的目的，無從了解及說明，是否在再補充一下
使用原公式中使用 INDIRECT("M"&ROW()) 這種方法太複雜

你可以將原先公式中需要的6個儲存格分別用引用
R1 = 13
R2 = INDIRECT("B"&R1)
R3 = INDIRECT("C"&R1)
R4 = INDIRECT("G"&R1)
R5 = INDIRECT("H"&R1)
R6 = INDIRECT("L"&R1)
R7 = INDIRECT("M"&R1)
再將R2~R7帶入公式中
你也可以先複製表格，用剪下/貼上的方式來移動 B13、C13...L13、M13，公式就能自動改變，再將公式內容複製你要的地方。

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

14^#

發表於 2014-10-1 10:28 | 只看該作者

本帖最後由 ML089 於 2014-10-1 10:37 編輯

回復 12# u7490074
N13
=IF(AND(N$12<=12,DATE(YEAR($B13),MONTH($B13)+1,)=DATE(YEAR($B13),N$12+1,)),ROUND($H13*($L13/$G13),0),IF(AND(N$12<=12,DATE(YEAR($C13),MONTH($C13)+1,)=DATE(YEAR($B13),N$12+1,)),ROUND($H13*($M13/$G13),0),IF(AND(N$12>12,DATE(YEAR($C13),MONTH($C13)+1,)=DATE(YEAR($B13)+1,(N$12-12)+1,)),ROUND($H13*($M13/$G13),0),0)))+IF(AND(N$12<=12,DATE(YEAR($B13),MONTH($B13)+1,)<DATE(YEAR($B13),N$12+1,),DATE(YEAR($B13),N$12+1,)<DATE(YEAR($C13),MONTH($C13)+1,)),ROUND($H13*(DAY(DATE(YEAR($C13),N$12+1,))/$G13),0),IF(AND(N$12>12,DATE(YEAR($B13),MONTH($B13)+1,)<DATE(YEAR($B13)+1,(N$12-12)+1,),DATE(YEAR($B13)+1,(N$12-12)+1,)<DATE(YEAR($C13),MONTH($C13)+1,)),ROUND($H13*(DAY(DATE(YEAR($B13)+1,(N$12-12)+1,))/$G13),0),0))

N13公式可以簡化，只需要起日期/迄日期/利息總費用及月數等資料就可以計算

=ROUND($H13/($C13-$B13)*FREQUENCY(ROW(INDIRECT($B13&":"&$C13-1)),DATE(YEAR($B13),N$12+1,)),) - SUM($M13:M13,-$M13)

每月利息費用會四捨伍入，誤差累積超過1元時該月會增加1元

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

15^#

發表於 2014-10-1 10:36 | 只看該作者

回復 12# u7490074

AG13驗證合計這樣寫會有問題，因為EXCEL儲存格可以格式化為整數但其實是有含小數的數字，用SUM(....)的總數會對，但用眼睛看用計算機計算就是會不一樣。

將 =SUM(N13:AE13)
改為 =SUMPRODUCT(ROUND(N13:AE13,0))

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

16^#

發表於 2014-10-1 10:52 | 只看該作者

回復 14# ML089

14樓公式使用陣列方式比較慢一些，直接數學計算比較快

N13
=ROUND($H13/($C13-$B13)*(MAX(0,MIN($C13,DATE(YEAR($B13),N$12+1,1))-$B13)),) - SUM($M13:M13,-$M13)

公式 = 四捨伍入至整數( 總利息/總天數 * (起日期至各月底天數) ) - 前面的利息合計

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

u7490074

小學生 Rank: 1

帖子: 17
主題: 4
精華: 0
積分: 21
點名: 0
作業系統: WINDOWS
軟體版本: OFFICE 2003
閱讀權限: 10
性別: 男
註冊時間: 2012-6-15
最後登錄: 2018-10-16

17^#

發表於 2014-10-1 12:46 | 只看該作者

回復 u7490074

檔案我看了，但沒有看到使用 INDIRECT("M"&ROW())的目的，無從了解及說明，是否在再補充 ...
ML089 發表於 2014-10-1 09:05

INDIRECT("M"&ROW())或INDIRECT("b"&ROW())或INDIRECT("c"&ROW())...等 : 均是考量在N13儲存格垂直下拉時，列號會變，才用這樣的土方式啦~

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

18^#

發表於 2014-10-1 14:34 | 只看該作者

回復 17# u7490074

你這說明是函數說明，我知道。
如果只是= INDIRECT("M"&ROW()) 下拉，是否採用 =M13 下拉效果一樣

例如
N13 =INDIRECT("M"&ROW())
N13 =M13
以上兩式下拉效果相同，當然採用 N13 =M13

一般使用
A2 輸入你要的列號，例如 13
B2 =INDIRECT("B"&A2) 起日
C2 =INDIRECT("C"&A2) 迄日
D2 =INDIRECT("H"&A2) 利息
E2 輸入你的公式，B2、C2、D2就是你的公式裡參數

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

u7490074

小學生 Rank: 1

帖子: 17
主題: 4
精華: 0
積分: 21
點名: 0
作業系統: WINDOWS
軟體版本: OFFICE 2003
閱讀權限: 10
性別: 男
註冊時間: 2012-6-15
最後登錄: 2018-10-16

19^#

發表於 2014-10-1 15:00 | 只看該作者

回復 u7490074

你這說明是函數說明，我知道。
如果只是= INDIRECT("M"&ROW()) 下拉，是否採用 =M13 下 ...
ML089 發表於 2014-10-1 14:34

拍謝啦~應該是說= INDIRECT("M"&ROW()) 是要固定每列用m欄儲存格，與水平由1月(n13)拖曳到18月時，能利用ROW()固定找該列的m儲存格(即m13)

如果：直接寫N13=M13，則拖曳到O13時，則O13=N13，而非O13仍=M13啦~~前面說明寫錯了~

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

20^#

發表於 2014-10-1 15:12 | 只看該作者

回復 19# u7490074

拍謝啦~應該是說= INDIRECT("M"&ROW()) 是要固定每列用m欄儲存格，與水平由1月(n13)拖曳到18月時，能利用ROW()固定找該列的m儲存格(即m13)

如果：直接寫N13=M13，則拖曳到O13時，則O13=N13，而非O13仍=M13啦~~前面說明寫錯了~

要水平右拉 =$M13 固定 M 不動

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

[收藏此主題] [關注此主題的新回復]

[通過 QQ、MSN 分享給朋友]