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

標題: [發問] 請問EXCEL怎麼取得工作表的數量與資料行數呢(跨工作表)!?謝謝 [打印本頁]

作者: konkon3141 時間: 2012-10-27 07:39 標題: 請問EXCEL怎麼取得工作表的數量與資料行數呢(跨工作表)!?謝謝

請問
問題1:EXCEL怎麼取得工作表的數量!?
我有的檔案有n個工作表
我需要在第1頁顯示n-2個工作表(扣掉目錄與型錄)
要用甚麼方法呢!?

如果有新增會刪減資料表
則工作表數量要更新

我不會用VBA..
版本是2003的
如果用到VBA請大大細心教我要怎麼使用~~

問題2:以下功能去除前2個工作表(扣掉目錄與型錄)
我在第3工作表~後面所有工作表中
在B9~以下無限欄填入資料時
在第1頁能顯示有資料行數

如在B9填了資料第1頁顯示1
B10如果也填了則第1頁顯示2
如果第4個工作表B9也填了
則顯示3

要能去抓B9欄以下(包括B9)有資料行有幾行
而且要跨工作表記算(第3工作表&無窮大(因為有時會新增工作表))
資料會隨著填入而更新

我不會用VBA..
版本是2003的
如果用到VBA請大大細心教我要怎麼使用~~

作者: tincholon 時間: 2012-10-27 16:20

可以使用巨集啊!

作者: stillfish00 時間: 2012-10-28 01:20

本帖最後由 stillfish00 於 2012-10-28 01:21 編輯

回復 1# konkon3141
先到EXCEL選項勾選開發人員 , 進入Visual Basic
左方選第一個工作表 , 複製以下代碼

Private Sub Worksheet_Activate()
'Q1:列出工作表清單
Dim i
Range(Range("A1"), Range("A1").End(xlDown)).ClearContents '先清除
For i = 3 To Sheets.Count '不含前兩個工作表(目錄,型錄)
Range("A1").Offset(i - 3, 0) = Sheets(i).Name
Next
'Q2:計算總數
Range("B1") = "=COUNTA(" & Sheets(3).Name & ":" & Sheets(Sheets.Count).Name & "!B:B)" & "-COUNTA(" & Sheets(3).Name & ":" & Sheets(Sheets.Count).Name & "!B1:B8)"
End Sub

複製代碼

作者: konkon3141 時間: 2012-10-28 09:04

本帖最後由 konkon3141 於 2012-10-28 09:06 編輯

回復 konkon3141
先到EXCEL選項勾選開發人員 , 進入Visual Basic
左方選第一個工作表 , 複製以下代碼
stillfish00 發表於 2012-10-28 01:20

可能我解釋問題比較弱
我傳個範例檔案
[attach]12911[/attach]
EXCEL有註解

問題1是第1工作表統計量中
能計算n-2工作表有幾個(去掉第1工作表跟最後工作表)
顯示"數字"再第1個工作表

問題2是第1工作表統計量中
顯示其後面工作表中(不報括最後工作表)
生產表單
維修表單
異動紀錄表單

共有多少筆資料
需要跨工作表尋找
B9~以下
J9~以下
P9~以下
資料數
因為我還沒填入所以都先打0

範圍如果我給夠大應該就可以當成無限範圍囉吧!?

大大你給我的Visual Basic我不知怎麼使用耶@@
2003只要按alt+f11就可以進入Visual Basic了
拜託大大了~~

作者: stillfish00 時間: 2012-10-28 11:21

回復 4# konkon3141
alt +F1進入Visual Basic , 左邊找到目錄這個工作表點兩下
[attach]12913[/attach]

在右邊區域貼上這段code:

Private Sub Worksheet_Activate()
'Q1:
Range("C9") = Sheets.Count - 2
'Q2:-
Range("C10") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B:B)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B1:B8)"
Range("C12") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J:J)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J1:J8)"
Range("C14") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P:P)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P1:P8)"
End Sub

複製代碼

多試試看能不能符合你的需求嚕
[attach]12912[/attach]

作者: konkon3141 時間: 2012-10-28 12:13

本帖最後由 konkon3141 於 2012-10-28 12:15 編輯

回復 konkon3141
alt +F1進入Visual Basic , 左邊找到目錄這個工作表點兩下

在右邊區域貼上這段cod ...
stillfish00 發表於 2012-10-28 11:21

大大你好
我的資料是要C10,C11,C12
所以我把程式位置改成C11,C12
他出現以下錯誤
我巨集有開低
[attach]12915[/attach]
[attach]12916[/attach]

另外大大我新增工作表後
工具表C9的工具表總數並不會更新耶

作者: stillfish00 時間: 2012-10-28 14:58

本帖最後由 stillfish00 於 2012-10-28 14:59 編輯

回復 6# konkon3141
恩..我不曉得是不是版本不同造成的, 我只有2010執行都正常 , 你試試
1.使用前面的附件時能正常執行嗎?
2.檢查目錄工作表是否有保護?
3.程式Range前指定Sheets("目錄") , 看有沒有差異?

Private Sub Worksheet_Activate()
'Q1:
Sheets("目錄").Range("C9") = Sheets.Count - 2
'Q2:
Sheets("目錄").Range("C10") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B:B)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B1:B8)"
Sheets("目錄").Range("C11") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J:J)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J1:J8)"
Sheets("目錄").Range("C12") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P:P)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P1:P8)"
End Sub

複製代碼

作者: konkon3141 時間: 2012-10-28 15:56

本帖最後由 konkon3141 於 2012-10-28 15:58 編輯

回復 konkon3141
恩..我不曉得是不是版本不同造成的, 我只有2010執行都正常 , 你試試
1.使用前面的附件 ...
stillfish00 發表於 2012-10-28 14:58

我把目錄改成我第1頁的名稱
還是無法執行耶
沒權限下載附件..所以不知道範例是否2003是否可以跑
怎麼檢查目錄工作表是否有保護呢!?

Private Sub Worksheet_Activate()
'Q1:
Sheets("目錄&模具品名").Range("C9") = Sheets.Count - 2
'Q2:
Sheets("目錄&模具品名").Range("C10") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B:B)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!B1:B8)"
Sheets("目錄&模具品名").Range("C11") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J:J)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!J1:J8)"
Sheets("目錄&模具品名").Range("C12") = "=COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P:P)" & "-COUNTA(" & Sheets(2).Name & ":" & Sheets(Sheets.Count - 1).Name & "!P1:P8)"
End Sub

複製代碼

出現以下畫面
[attach]12917[/attach]
[attach]12918[/attach]
[attach]12919[/attach]

拜託大大了q.q謝謝^^~

作者: konkon3141 時間: 2012-10-28 18:31

我剛再試一下工作表會隨著新增而改變數量了

但是C10~C12還是無法顯示...
大大能跟我稍微解釋一下程式碼內容是甚麼嗎!!?
我看看是不是工作表出問題~~
拜託大大了

作者: stillfish00 時間: 2012-10-28 19:15

本帖最後由 stillfish00 於 2012-10-28 19:16 編輯

回復 9# konkon3141
內容就是在C10 填入公式
=COUNTA(開始工作表:結束工作表!B:B)-COUNTA(開始工作表:結束工作表!B1:B8)
即計算所有開始工作表~結束工作表 B欄非空白單元格總數
減去所有開始工作表~結束工作表 B1到B8 非空白單元格總數

因為工作表可能增加 , 不是固定同一個 , 所以用VBA ,
Sheets(Sheets.Count - 1).Name 去找到最後一個工作表名字

C11, C12依此類推 , 我也不明白哪裡有問題

作者: konkon3141 時間: 2012-10-28 20:22

回復 konkon3141
內容就是在C10 填入公式
=COUNTA(開始工作表:結束工作表!B:B)-COUNTA(開始工作表:結束 ...
stillfish00 發表於 2012-10-28 19:15

大大這是我的檔案
[attach]12922[/attach]
請大大幫我看一下
我權限不夠大大處理好可以傳到網路空間嗎Q.Q...
拜託了~~謝謝^^

作者: stillfish00 時間: 2012-10-28 21:20

回復 11# konkon3141
http://d.pr/f/Sxsz

最後發現問題在工作表名稱有含括號 , 所以在使用公式時工作表名稱要另外加單引號才不會出錯
啊啊~這算我沒養成良好習慣啦><

作者: konkon3141 時間: 2012-10-28 21:32

回復 konkon3141
http://d.pr/f/Sxsz

最後發現問題在工作表名稱有含括號 , 所以在使用公式時工作表名 ...
stillfish00 發表於 2012-10-28 21:20

大大我把( ) + &全改成-
結果切換到目錄他一直要我開啟檔案= ="
而且輸入資料也不會更新筆數..
http://www.mediafire.com/?s6eqfqc4dj3wlz9
另外為甚麼工作表名稱不能使用( )呢!?
再此先謝謝大大的幫忙^^~~

作者: stillfish00 時間: 2012-10-28 21:44

回復 13# konkon3141
http://d.pr/f/Sxsz 這個檔案不能用嗎? 這是改過的

作者: konkon3141 時間: 2012-10-28 22:06

回復 konkon3141
http://d.pr/f/Sxsz 這個檔案不能用嗎? 這是改過的
stillfish00 發表於 2012-10-28 21:44

這個可以了!!!
大大有改工作表名稱嗎!?
好像沒有改耶~~
沒有改工作是怎麼解決這個問題呢!?
在EYNY有人說工作表()會造成VBA引用儲存格能夠用的函數
所以很可能會混淆
再此感謝大大的幫忙^^~~
學習到很多EXCEL的知識

作者: konkon3141 時間: 2012-10-28 22:15

另外請問大大我想把第3工作表"PC針模"~新手錶架(組)隱藏
但這樣會造成第1頁的數量無法計算耶...
目錄下方的超連結也會全部失效..

作者: Hsieh 時間: 2012-10-28 22:31

回復 13# konkon3141

自定義函數

Function All_Data(sh As Range, sh1 As Range, MyType As Integer) As Double
Application.Volatile
For Each sht In Sheets
If sht.Name <> sh.Parent.Name And sht.Name <> sh1.Parent.Name Then
Rng = sh.Address(, , , , 0)
Rng1 = sh1.Address(, , , , 0)
Select Case MyType
Case 0
All_Data = All_Data + 1
Case Else
All_Data = All_Data + (Application.CountA(sht.Range(Rng)) - Application.CountA(sht.Range(Rng1)))
End Select
End If
Next
End Function

複製代碼

C9=All_Data(A1,空白格式!A1,0)
C10=All_Data('目錄&模具品名'!B:B,空白格式!B$1:B$8,1)
C11=All_Data('目錄&模具品名'!J:J,空白格式!J$1:J$8,1)
C12=All_Data('目錄&模具品名'!P:P,空白格式!P$1:P$8,1)
[attach]12923[/attach]

作者: konkon3141 時間: 2012-10-28 22:48

回復 konkon3141

自定義函數C9=All_Data(A1,空白格式!A1,0)
C10=All_Data('目錄&模具品名'!B:B,空白格 ...
Hsieh 發表於 2012-10-28 22:31

大大我論壇權限不夠
可以傳網路空間嗎@@!?
謝謝囉^^~~~

作者: stillfish00 時間: 2012-10-28 23:15

回復 16# konkon3141
隱藏工作表的超連結會失效是正常的..
另一種方法是工作表不隱藏 , 到Excel選項裡取消工作表索引標籤
不過看用途啦有時候會不太方便..

作者: konkon3141 時間: 2012-10-28 23:49

回復 konkon3141
隱藏工作表的超連結會失效是正常的..
另一種方法是工作表不隱藏 , 到Excel選項裡取消 ...
stillfish00 發表於 2012-10-28 23:15

在依莉有大大說如果嫌下表工作表索引標籤太多
可以考慮以檔案連結的方式
以我這個例子
就不知道有沒有1個工作表換成1個活頁簿的方法了
而且在目錄的統計量有辦法跨檔案計算嗎...這也是個大問題

取消工作表索引標籤還蠻不方便的@@
如果要新增工作表後要回空白格式複製格式
真的非常麻煩..

感謝大大花了1天熱心的幫忙^^~~
學到了很多是學校不會教的知識~~

作者: mark15jill 時間: 2012-10-29 09:14

簡易顯示行列..

Sub ioxga()
For uxa = 1 To 10
'Sheets.Add After:=Sheets(Sheets.Count)
'Sheets("工作表" & uxa).Select
'Sheets("工作表" & uxa).Name = "工作表" & uxa
工作表1.[a1] = "表單名稱"
工作表1.[b1] = "列數"
工作表1.[c1] = "行數"
工作表1.Cells(uxa + 1, 1) = Sheets("工作表" & uxa).Name
工作表1.Cells(uxa + 1, 2) = Sheets("工作表" & uxa).Range("A1").CurrentRegion.Rows.Count
工作表1.Cells(uxa + 1, 3) = Sheets("工作表" & uxa).Range("A1").CurrentRegion.Columns.Count
Next
End Sub

複製代碼

作者: konkon3141 時間: 2012-10-30 23:49

本帖最後由 konkon3141 於 2012-10-30 23:52 編輯

回復 konkon3141
隱藏工作表的超連結會失效是正常的..
另一種方法是工作表不隱藏 , 到Excel選項裡取消 ...
stillfish00 發表於 2012-10-28 23:15

不好意思大大
因為我想把履歷建的更完善些
所以想新增一些功能
不知道EXCEL是否能有這些功能呢?
如果有些沒有也沒關係
有的功能再請大大給我講~~

1.
首先以下功能不包括目錄與空白格式
第B27~E39
需抓取其後面工作表中B1的名稱
計算有幾個工作表(放B欄)
這些工作表中有幾筆生產表單.維修表單.異動紀錄表單
如有新增工作表需更新資料(新增資料會放在目錄與空白格式之中)
大大做一個就好
其他我在自己改欄位與名稱

2.B46至以下D249
需傳回左邊工作表中H2~H4的值
不知道有辦法批量照A欄名稱傳回嗎(不包括空白行)?
如有新增行數則H2~H4要能顯示資料數

3.第2工作表~第n(包括空白格式)工作表中
H2~H4
需能計算B9,J9,K9(全部包括第9列)以下有幾比資料
大大做一個就好
其他我在統一貼上

4.A9,I9,O9至以下欄
能否判斷B9,J9,K9是否填入資料後
自動產生1.2.3.4的排序呢?

5.
I2~I4
能眺到最後輸入資料數之後的空白欄(Bi,Ji,Ki)
如我現在輸入B9了
按I2可以直接跳到B10
如沒有資料需跳回第9行

東想西想想了不少功能
不知道excel有沒有以上功能呢!?
再此不管結果如何都先謝謝大大了^^~~

大大用好可否傳網路空間呢!?
因為權限不夠不能下載附件Q.Q
拜託了^^~~

http://www.mediafire.com/?jb6y7kd9k8n8882

作者: konkon3141 時間: 2012-10-31 07:49

本帖最後由 konkon3141 於 2012-10-31 07:51 編輯

大大你好
第2功能要改一下
我表格有換格式

2.B46至以下BH有限欄中
需傳回左邊工作表中H2~H4的值
不知道有辦法批量照A欄名稱傳回嗎?
如有新增工做表則H2~H4要能顯示資料數

6.新增工作表後(會再目錄與空白格式之中)能判斷B1值
然後判斷目錄49行
找尋相同B1值
再其行有數值後的空白格
自動建立其工作表名稱
並對名稱超連結至工作表
生產表單維修表單異動紀錄表單
此3項也要做資料新的工作的資訊更新

7.F2能夠搜尋其後工作表(並跳至工作表內)

檔案改成下面這個

http://www.mediafire.com/?z3h9x21vi1zhmoq

大大用好可否傳網路空間呢!?
因為權限不夠不能下載附件Q.Q
拜託了^^~~

作者: konkon3141 時間: 2012-10-31 14:01

大大在別的論壇有人有些一寫了
想請問下面這些該怎麼辦呢!?

1.可以把"目錄"計算功能
寫成"更新資料"的按紐
需要觀看時才更新嗎!?

2.
如我果新增了新的工作表
B1填"盛富"
是不是必須放再PC針模S柄之中
才會計算以下資訊呢!?
模具數量生產表單維修表單異動紀錄表單
因為工作表眾多
我才想說是否有IF之類的判斷式去判斷後面工作表B1的值
這樣如果再任意地方新增才比較方便
不然移工作表還蠻頭痛的..

3.
我新增了新的工作表
B1是"盛富"的客戶
是否能自動再目錄第49行
判斷那一欄為"盛富"
再其後面之空白格(如檔案的A68)
超連結其新增的工作表(與名稱)
並自動計算
生產表單維修表單異動紀錄表單
這行能用程式碼去計算嗎!?
自動超連結其左行名稱工作表的H2~H4
還是只能手動輸入公式呢!?

CommandButton1的字可以改為中文嗎
我改下面這行好像沒用...
Private Sub CommandButton1_Click()

PC針模內輸入下一筆資料中
H3與H4的程式碼該如何打呢!?

A9,I9,O9至以下
可否因為按了H2~H4
自動產生排序1.2.3~n呢

PC針模的程式碼可以批量複製於~n(包括空白格式)嗎!?
我按shitft他都不理我- -"

http://www.mediafire.com/?43ca7xv1g40yubi

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