麻辣家族討論版版 - Powered by Discuz! Board

標題: 跨工作表LINK(讀取)某個指定位置的值，有簡單的方法嗎 [打印本頁]

作者: u7490074 時間: 2014-9-21 11:29 標題: 跨工作表LINK(讀取)某個指定位置的值，有簡單的方法嗎

請問：這如果要在【工作表1】的C2儲存格取得【工作表2】D3儲存格的數值，
在C3儲存格輸入=A2&B2&"!"&D3是錯誤的，小問題該如何解決?(PS : 因為有很多個類【工作表2】，但要在一個【工作表1】統一看各資料的變化，希望用函數或其他方式簡化LINK的作業)

工作表1 A B C
1 代號名稱股價
2 1532 勤美

假設【工作表2】名稱是 1532勤美
工作表2 A B C D
1
2
3 58

作者: ML089 時間: 2014-9-21 15:56

回復 1# u7490074

C3儲存格輸入=INDIRECT(A2&B2&"!D3")

作者: u7490074 時間: 2014-9-21 19:56

回復 u7490074

C3儲存格輸入=INDIRECT(A2&B2&"!D3")
ML089 發表於 2014-9-21 15:56

==>如果是下列這類的名稱(英文+符號+數字)，無法用這函數解決，不知問題出在那? <==

工作表1 A B C
1 代號名稱股價
2 1256 F-鮮活 #REF!

工作表2 A B C D
1
2
3 58

作者: ML089 時間: 2014-9-21 21:00

回復 3# u7490074

工作表名稱用單引號括住( '工作表名稱'!儲存格名稱 )
C3儲存格輸入=INDIRECT("'"&A2&B2&"'!D3")

作者: u7490074 時間: 2014-9-21 22:06

最後請問一個問題：
如果【工作表1】的C2儲存格要取得【工作表2】的AVERAGE(B2：D2)
是否只能用AVERAGE(INDIRECT("'"&A2&B2&"'!B2"),INDIRECT("'"&A2&B2&"'!C2"),INDIRECT("'"&A2&B2&"'!D2"))之方式，如果平均數多就很困擾~

工作表1 A B C
1 代號名稱股價
2 1256 F-鮮活

工作表2 A B C D
1
2
3 53 50 58

作者: ML089 時間: 2014-9-22 08:58

回復 5# u7490074

最後請問一個問題：
如果【工作表1】的C2儲存格要取得【工作表2】的AVERAGE(B2：D2)
是否只能用AVERAGE(INDIRECT("'"&A2&B2&"'!B2"),INDIRECT("'"&A2&B2&"'!C2"),INDIRECT("'"&A2&B2&"'!D2"))之方式，如果平均數多就很困擾~

=AVERAGE(INDIRECT("'"&A2&B2&"'!B2:D2")

作者: u7490074 時間: 2014-9-29 10:20

【請問版主】
如果在A3的儲存格，寫入SUM(Y3：Z3)，即讀取A3儲存格=SUM(Y3：Z3)的儲存格合計
且=SUM($Y$3：$Z$3)要改成=SUM($Y$ROW()：$Z$ROW())之意，連接語法該怎表示才正常
(加總第三列：儲存格Y~Z的數值)

作者: ML089 時間: 2014-9-29 21:40

回復 7# u7490074

猜猜看，是不是這樣
=SUM(INDIRECT("Y"&ROW()&"：Z"&ROW()))

作者: u7490074 時間: 2014-9-30 00:10

回復 u7490074

猜猜看，是不是這樣
=SUM(INDIRECT("Y"&ROW()&"：Z"&ROW()))
ML089 發表於 2014-9-29 21:40

是可以用，但如果單一儲存格打入="F"&ROW() .......假設在第18列則得到是F18，
是一定要用INDIRECT("F"&ROW())才能得到F18儲存格的值嗎?  與在A18儲存格輸入=F18寫法，要多不少語法對嗎.......因為我有多判別式，所以一個儲存格內放好長的語法，如下，有方法能再精簡or更簡明的語法嗎?

【儲存格A~L內容】
起始年度            終迄年度                            單據編號          原本金      利率總天數總利息費用    總月數    起月    迄月       起月天數  迄月天數
2014年08月04日 2014年10月06日 40C000800 30,000,000 1.080% 63 55,920          3       8 10 28 5

【儲存格M~AD內容】
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
0 0 0 0 0 0 0 24,853 26,629 4,438 0 0 0 0 0 0 0 0

【儲存格T2內容】
其中，8月份=24853元=IF(AND(U2<=12,DATE(YEAR($B3),MONTH($B3)+1,)=DATE(YEAR($B3),U2+1,)),ROUND($H3*($L3/$G3),0),IF(AND(U2>12,DATE(YEAR($B3),MONTH($B3)+1,)=DATE(YEAR($B3),(U2-12)+1,)),ROUND($H3*($L3/$G3),0),IF(AND(U2<=12,DATE(YEAR($C3),MONTH($C3)+1,)=DATE(YEAR($B3),U2+1,)),ROUND($H3*($M3/$G3),0),IF(AND(U2>12,DATE(YEAR($C3),MONTH($C3)+1,)=DATE(YEAR($B3)+1,(U2-12)+1,)),ROUND($H3*($M3/$G3),0),0))))+IF(AND(U2<=12,DATE(YEAR($B3),MONTH($B3)+1,)<DATE(YEAR($B3),U2+1,),DATE(YEAR($B3),U2+1,)<DATE(YEAR($C3),MONTH($C3)+1,)),ROUND($H3*(DAY(DATE(YEAR($B3)+1,(U2-12)+1,))/$G3),0),IF(AND(U2>12,DATE(YEAR($B3),MONTH($B3)+1,)<DATE(YEAR($B3)+1,(U2-12)+1,),DATE(YEAR($B3)+1,(U2-12)+1,)<DATE(YEAR($C3),MONTH($C3)+1,)),ROUND($H3*(DAY(DATE(YEAR($B3)+1,(U2-12)+1,))/$G3),0),0))

【儲存格U2內容】
其中，9月份=26629元=IF(AND(V2<=12,DATE(YEAR($B3),MONTH($B3)+1,)=DATE(YEAR($B3),V2+1,)),ROUND($H3*($L3/$G3),0),IF(AND(V2>12,DATE(YEAR($B3),MONTH($B3)+1,)=DATE(YEAR($B3),(V2-12)+1,)),ROUND($H3*($L3/$G3),0),IF(AND(V2<=12,DATE(YEAR($C3),MONTH($C3)+1,)=DATE(YEAR($B3),V2+1,)),ROUND($H3*($M3/$G3),0),IF(AND(V2>12,DATE(YEAR($C3),MONTH($C3)+1,)=DATE(YEAR($B3)+1,(V2-12)+1,)),ROUND($H3*($M3/$G3),0),0))))+IF(AND(V2<=12,DATE(YEAR($B3),MONTH($B3)+1,)<DATE(YEAR($B3),V2+1,),DATE(YEAR($B3),V2+1,)<DATE(YEAR($C3),MONTH($C3)+1,)),ROUND($H3*(DAY(DATE(YEAR($B3)+1,(V2-12)+1,))/$G3),0),IF(AND(V2>12,DATE(YEAR($B3),MONTH($B3)+1,)<DATE(YEAR($B3)+1,(V2-12)+1,),DATE(YEAR($B3)+1,(V2-12)+1,)<DATE(YEAR($C3),MONTH($C3)+1,)),ROUND($H3*(DAY(DATE(YEAR($B3)+1,(V2-12)+1,))/$G3),0),0))

作者: ML089 時間: 2014-9-30 09:18

回復 9# u7490074

是一定要用INDIRECT("F"&ROW())才能得到F18儲存格的值嗎? 與在A18儲存格輸入=F18寫法，要多不少語法對嗎.

A18 =F18 當然比 A18 =INDIRECT("F"&ROW()) 簡單多了
每個函數各有使用時機。目前是看不出來你要怎麼用，有檔案說明比較容易了解你要什麼?

你的T2、U2公式不變，其結果引用回來就可以

作者: u7490074 時間: 2014-9-30 15:30

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

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

作者: u7490074 時間: 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)

作者: ML089 時間: 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，公式就能自動改變，再將公式內容複製你要的地方。

作者: ML089 時間: 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元

作者: ML089 時間: 2014-10-1 10:36

回復 12# u7490074

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

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

作者: ML089 時間: 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)

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

作者: u7490074 時間: 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儲存格垂直下拉時，列號會變，才用這樣的土方式啦~

作者: ML089 時間: 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就是你的公式裡參數

作者: u7490074 時間: 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啦~~前面說明寫錯了~

作者: ML089 時間: 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 不動

作者: u7490074 時間: 2014-10-1 15:18

以前一直沒注意$的用法：
$M13：水平拖曳時，M13是整個被固定不變，而非只是M不變，13一直往右增加

M$13：水平拖曳時，只有13不變，M會增減變動 ^*^~

作者: ML089 時間: 2014-10-1 15:42

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

回復 21# u7490074

以前一直沒注意$的用法：
$M13：水平拖曳時，M13是整個被固定不變，而非只是M不變，13一直往右增加

M$13：水平拖曳時，只有13不變，M會增減變動 ^*^~

=M13
右拉時，M 會動，13 不動，所以用 $M13
下拉時，M 不動，13 會動，所以用 M$13
若右拉下拉，用 $M$13 鎖住

公式相對及絕對$的使用極為重要，應該是最基本的觀念，不然公式就無法右拉下拉簡易完成工作

作者: u7490074 時間: 2014-10-11 23:49

https://drive.google.com/file/d/0BytNKnm2laTvZWlrTTI2TFZ6MU0/view?usp=sharing
(檔案應該可以編輯了)

說明：在第13~14列之K13~AB14區的資料，由於第12列是只填1~18(K12~AB12)，造成K13~AB13是ERROR的錯誤結果，請問是否將(K12~AB12)改為想要的年月(該如何表示)，會更能精確得到正確結果，不會再有檔案中發生的ERROR狀況。
請問：如何修改才能將資料103年1月~12月的利息分攤月份正確計算出來~

作者: ML089 時間: 2014-10-12 09:52

本帖最後由 ML089 於 2014-10-12 11:27 編輯

回復 23# u7490074

[attach]19325[/attach]

這樣改還不行，

因為這公式是用『本月金額 = 本月底總合- 前月底總和』計算方式，表格改為中間一部份使得前面部分不見的無法計算。
計算方式還要在研究一下

K13 改為
=ROUND($H13/($D13-$C13)*(MAX(0,MIN($D13,DATE(2014,K$5+1,1))-$C13)),) - ROUND($H13/($D13-$C13)*(MAX(0,MIN($D13,DATE(2014,K$5,1))-$C13)),)

作者: u7490074 時間: 2014-10-13 15:20

回復 u7490074

這樣改還不行，

因為這公式是用『本月金額 = 本月底總合- 前月底總和』計算方式 ...
ML089 發表於 2014-10-12 09:52

==>我有一個想法 : 為因應長期(即: 一年以上)，原來1~18，可以放入以年月為代表的數值(如 : 2014.01/2014.02/...)
(一)【工作表1 】: 是放入基本參數資料(借款的相關條件及約定)
(二)【工作表2】開始 :就放分別以西元年度為工作表名稱，將【工作表1】內的每年各月份的數值放入相對應年度工作表之各月份的欄位中

~~~不過，真的有點困難度~

作者: ML089 時間: 2014-10-13 16:25

回復 25# u7490074

K10輸入2014/1/1，儲存格格式改為 "e/m/d"

K13 改為
=ROUND($H13/($D13-$C13)*(MAX(0,MIN($D13,DATE(YEAR(K10),K$5+1,1))-$C13)),) - ROUND($H13/($D13-$C13)*(MAX(0,MIN($D13,DATE(YEAR(K10),K$5,1))-$C13)),)

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