標題: [發問] 引用A工作表值的公式語法 [打印本頁]

---

作者: ziv976688    時間: 2021-1-8 23:28     標題: 引用A工作表值的公式語法

[attach]32955[/attach]
測試檔︰[attach]32956[/attach]
想將A工作表的B2:S51，V2:AM51，AP2:BG51，BJ2:CA51的公式值，
以公式A，公式B，公式C，公式D等4個模組內的公式值，
各貼上B105:AX122；B155:AX172；B205:AX222；B255:AX272(四個區段各18列)。
請詳見︰(2021-01-05)_效果檔

請問︰
公式A，公式B，公式C，公式D等4個模組內的語法，應該如何編寫？
謝謝！

PS︰有預留1個CSV檔以利測試用。

---

作者: ziv976688    時間: 2021-1-9 12:17

因為不懂怎麼引用其它的工作表(EX:A!)資料，所以只要是能引用公式達到如效果檔的答案即可~
無論是直接將公式放在模組1內或引用公式A，公式B，公式C，公式D等4個模組皆可。
謝謝!

---

作者: ziv976688    時間: 2021-1-9 14:01

本帖最後由 ziv976688 於 2021-1-9 14:06 編輯

抱歉!公式的註解有筆誤~
公式值複製轉至貼上
修正為
公式值複製轉置貼上。
謝謝!

---

作者: n7822123    時間: 2021-1-9 15:13

本帖最後由 n7822123 於 2021-1-9 15:23 編輯

回復 1# ziv976688

想將A工作表的B2:S51，V2:AM51，AP2:BG51，BJ2:CA51的公式值
以公式A，公式B，公式C，公式D等4個模組內的公式值
各貼上B105:AX122；B155:AX172；B205:AX222；B255:AX272(四個區段各18列)。

值 是 值，公式 是 公式，請盡量避免用 公式值 這種敘述.......

很容易混淆額造成誤會，把你的敘述改成如下，就清楚多了

想將'T1'的'A'工作表的B2:S51，V2:AM51，AP2:BG51，BJ2:CA51的 "值"
以公式A，公式B，公式C，公式D等4個模組內的 "公式"
各貼上'總覽'的B105:AX122；B155:AX172；B205:AX222；B255:AX272(四個區段各18列)。

用"公式"去取其他活頁簿的儲存格資料 要多寫該活頁簿的路徑+檔名

如下是取A1儲存格範例

='C:\使用者們\秘密的使用者\桌面\引用A工作表值的公式語法\[49遺漏刪_空數(VBA)_T1.xls]A'!$A$1

光一個儲存格就要寫這麼長!，如果你的A~D都這樣寫，公式會變得更難閱讀...還有超出 255 字元的問題

我個人是很少用VBA 去寫 儲存格公式的，尤其像你這種貼完公式後，還要馬上轉成值的~

這種在程式執行過程中，不能關掉公式自動計算(一關掉就取不到值)

但是不關掉，VBA程式每對任意一個儲存格的內容做變更，就會自動觸發重算整個工作表

會使得效率變的比較低，遇到 資料量大的情況，就會很卡!

而且你的東西看起來越來越複雜了.........建議你要學些VBA，做簡單的計算(捨棄公式)

扯遠了，上面的方式寫起來麻煩，公式也會讓人難以理解

比較簡單的做法是把A 工作表 Copy到 產生出來的"總覽"活頁簿

這樣"總覽"活頁簿的公式，要取A表資料，就不再是 "跨活頁簿" 了

再貼上公式 > 轉值，這樣一來你的公式 完全不用修改

最後"總覽"活頁簿要關閉前，再刪除A表即可

檔案如下



[attach]32958[/attach]

---

作者: ziv976688    時間: 2021-1-9 15:49

回復 4# n7822123
n大 : 您好!
我也很想學VBA，但雙北市都沒有補習班(有詢問過"巨匠"和"聯成")開班教授VBA課程(其它有開班的似乎都在中南部)；
有想上網學習，但卻又是有看沒有懂>*<
還好!這已經是最後的步驟~需求檔終於"完成了"!

謝謝您一再的熱心幫忙和耐心指導與說明~感恩(鞠躬)~~~

---

作者: ziv976688    時間: 2021-1-9 20:24

回復 4# n7822123
n大 : 您好!
不好意思，假設"A"工作表內容不變時~
請問 : 能按1次執行鍵，執行多個CSV檔案嗎 ?
如果可以，請問程式碼要如何增修?
如果不可行，就以目前按1次執行鍵執行1個CSV檔案即可。
謝謝您

---

作者: n7822123    時間: 2021-1-9 20:51

本帖最後由 n7822123 於 2021-1-9 20:54 編輯

回復 6# ziv976688

暈倒，忘記你主程式還在 Dir當中，還沒搜完呢

Dir 不能用了，改一下下面的程式

原本

Sub 複製A表到總覽()
總覽_Nm$ = Dir(Module1.NewName)
With Workbooks(總覽_Nm)
    ThisWorkbook.Sheets("A").Copy After:=.Sheets(.Sheets.Count)
    .Sheets(1).Activate   '回到資料檔
End With
End Sub

Sub 於總覽刪除A表()
Application.DisplayAlerts = False
總覽_Nm$ = Dir(Module1.NewName)
Workbooks(總覽_Nm).Sheets("A").Delete
End Sub

改為

Sub 複製A表到總覽()
總覽_Nm$ = Mid(Module1.NewName, InStrRev(Module1.NewName, "\") + 1)
With Workbooks(總覽_Nm)
    ThisWorkbook.Sheets("A").Copy After:=.Sheets(.Sheets.Count)
    .Sheets(1).Activate   '回到資料檔
End With
End Sub

Sub 於總覽刪除A表()
Application.DisplayAlerts = False
總覽_Nm$ = Mid(Module1.NewName, InStrRev(Module1.NewName, "\") + 1)
Workbooks(總覽_Nm).Sheets("A").Delete
End Sub

[attach]32959[/attach]

---

作者: ziv976688    時間: 2021-1-9 21:46

本帖最後由 ziv976688 於 2021-1-9 22:00 編輯

回復 7# n7822123
n大 : 您好!
全OK了!
萬分感激您的熱心幫忙和耐心指導~受惠良多~感恩再感恩~~~~~ ~~~

PS : 執行40個檔案(全部需求的公式都放進去~482列公式)~耗時51秒~也不算很慢~謝謝您

---

作者: 准提部林    時間: 2021-1-10 12:24

A表既是共用的, 增個AAA表取公式及值, 直接貼上即可,
應不須每個檔案都加載一次公式吧!

[attach]32960[/attach]

---

作者: n7822123    時間: 2021-1-10 13:01

本帖最後由 n7822123 於 2021-1-10 13:09 編輯

回復 9# 准提部林

還是準大厲害~~又把程式簡化了~ ， 程式效率會在UP

如果批次執行49個檔案，A表內容都不會變，公式也是固定的

那算出來的值也不會變，確實不用算那麼多次

在迴圈外算出來一次即可，之後每個檔案都只要複製該值~

那4個公式A~D，我原本也想要像準大那樣合併寫，只改區域範圍

但是考量到需求者不擅長VBA，所以我就盡量不去動了

---

作者: ziv976688    時間: 2021-1-10 13:55

回復 9# 准提部林
版主大大   : 您好 !
效率提高很多~同樣的檔案和同樣的環境(邊聽youtube的音樂和邊在桌面執行操作)，只耗時13秒
感謝您一直以來的熱心幫忙和耐心指導~受惠良多~感恩再感恩(鞠躬)~~~ ~~~~

---

作者: ziv976688    時間: 2021-1-10 14:07

回復 10# n7822123
n大 : 您好!
對小弟而言，您願意幫忙和指導~我已經是感激不盡了
您的考量是正確的~您的程式碼對小弟也是太高深~小弟一般是有看沒有懂，還好您常有加註解，對小弟幫助很大(例如:改為539的格式，到目前為止，都沒有遇到困難)

再次感謝您長期以來的熱心幫忙和耐心指導~惠我良多~感恩再感恩(鞠躬)~~~ ~~~~~

---

作者: ziv976688    時間: 2021-1-10 15:03

本帖最後由 ziv976688 於 2021-1-10 15:06 編輯

回復 11# ziv976688
Sorry~有筆誤~13秒改為33秒
效率提高37%

---

作者: jackyq    時間: 2021-1-10 16:14

回復 13# ziv976688


這程式你自己在用的歐

loto無解  最終就是走向參數孤島

用玄學會比較有準? 你有比較過嗎

---

作者: ziv976688    時間: 2021-1-10 18:56

回復 14# jackyq
見笑了!只是對統計學有興趣，所以利用"概率"來研究樂透罷了!
這個版路是不出牌用的~目前的紀錄是連續36期沒有破版。
玄學完全不懂~哈~哈~

---

作者: ziv976688    時間: 2021-1-16 08:17

本帖最後由 ziv976688 於 2021-1-16 08:23 編輯

回復 9# 准提部林
測試檔 : [attach]32979[/attach]
版主大大 : 您好!
不好意思，如果將
公式A_D模組
Sub DATA檔_AAA表_公式()
Dim AAA_Fx$, xR As Range, N%, xArea
AAA_Fx = "=IF(COLUMN(A$1)>SUM(N(INDEX(A!區域,,ROW($A1))="""")),"""",SMALL(IF(INDEX(A!區域,,ROW($A1))="""",A!$A$2:$A$51),COLUMN(A$1)))"
xArea = Array("", "$B$2:$S$51", "$V$2:$AM$51", "$AP$2:$BG$51", "$BJ$2:$CA$51")   '列4
For Each xR In Sheets("AAA").Range("B2,B52, B102, B152")
    N = N + 1
    xR.FormulaArray = Replace(AAA_Fx, "區域", xArea(N))
    xR.Copy xR(1, 2).Resize(1, 48)
    xR.Resize(1, 49).Copy xR(2, 1).Resize(17, 49)
    xR.Resize(18, 49).Value = xR.Resize(18, 49).Value
Next
End Sub
改為
~~~
~~~
xArea = Array("", "$B$2:$S$51", "$V$2:$AM$51", "$AP$2:$BG$9", "$BJ$2:$CA$9")   '列5
(因為了搜尋儲存格=""時，沒有影響，但如果是要搜尋儲存格<>""或其它值時，會影響到最終的答案，所以這2個範圍必須要調整為實際的計算列(8列))
~~~
~~~

請問 :
其它相關的程式碼要如何再修正?
敬請您賜正，
謝謝您^^

---

作者: 准提部林    時間: 2021-1-16 11:29

回復 16# ziv976688


Sub DATA檔_AAA表_公式()
Dim AAA_Fx$(4), xR As Range, N%, xArea
AAA_Fx(1) = "=SMALL(IF(INDEX(A!$B$2:$S$51,,ROW($A1))="""",A!$A$2:$A$51),COLUMN(A$1))"
AAA_Fx(2) = "=SMALL(IF(INDEX(A!$V$2:$AM$51,,ROW($A1))="""",A!$A$2:$A$51),COLUMN(A$1))"
AAA_Fx(3) = "=SMALL(IF(INDEX(A!$AP$2:$BG$9,,ROW($A1))="""",A!$A$2:$A$9),COLUMN(A$1))"
AAA_Fx(4) = "=SMALL(IF(INDEX(A!$BJ$2:$CA$9,,ROW($A1))="""",A!$A$2:$A$9),COLUMN(A$1))"
For Each xR In Sheets("AAA").Range("B2,B52, B102, B152")
    N = N + 1
    xR.FormulaArray = AAA_Fx(N)
    xR.Copy xR(1, 2).Resize(1, 48)
    xR.Resize(1, 49).Copy xR(2, 1).Resize(17, 49)
    With xR.Resize(18, 49)
        .Value = .Value
        .Replace "#*", "", lookat:=xlWhole
    End With
Next
End Sub

---

作者: ziv976688    時間: 2021-1-16 16:04

回復 17# 准提部林
版主大大   : 您好 !
謝謝您的耐心指導~感恩(鞠躬)

---

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