123
返回列表 上一主題 發帖

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

u7490074

帖子
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

帖子
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

帖子
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

帖子
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

帖子
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

帖子
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

帖子
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

帖子
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

帖子
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

帖子
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

        靜思自在 : 有心就有福，有願就有力，自造福田，自得福緣。
123
返回列表 上一主題