[發問] 請問EXCEL怎麼取得工作表的數量與資料行數呢(跨工作表)!?謝謝

帖子: 17
主題: 4
精華: 0
積分: 21
點名: 0
作業系統: XP
軟體版本: 2003
閱讀權限: 10
註冊時間: 2012-10-16
最後登錄: 2012-12-1

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 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

帖子: 2
主題: 0
精華: 0
積分: 7
點名: 0
作業系統: windowns
軟體版本: office
閱讀權限: 10
性別: 男
註冊時間: 2012-10-27
最後登錄: 2014-6-1

2^#

發表於 2012-10-27 16:20 | 只看該作者

可以使用巨集啊!

帖子: 1018
主題: 15
精華: 0
積分: 1058
點名: 0
作業系統: win7 32bit
軟體版本: Office 2016 64-bit
閱讀權限: 50
性別: 男
來自: 桃園
註冊時間: 2012-5-9
最後登錄: 2022-9-28

3^#

發表於 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

複製代碼

帖子: 17
主題: 4
精華: 0
積分: 21
點名: 0
作業系統: XP
軟體版本: 2003
閱讀權限: 10
註冊時間: 2012-10-16
最後登錄: 2012-12-1

4^#

發表於 2012-10-28 09:04 | 只看該作者

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

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

可能我解釋問題比較弱
我傳個範例檔案

1.rar (2.6 KB)
EXCEL有註解

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

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

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

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

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

帖子: 1018
主題: 15
精華: 0
積分: 1058
點名: 0
作業系統: win7 32bit
軟體版本: Office 2016 64-bit
閱讀權限: 50
性別: 男
來自: 桃園
註冊時間: 2012-5-9
最後登錄: 2022-9-28

5^#

發表於 2012-10-28 11:21 | 只看該作者

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

在右邊區域貼上這段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

複製代碼

多試試看能不能符合你的需求嚕

Book1.zip (1.68 KB)

帖子: 17
主題: 4
精華: 0
積分: 21
點名: 0
作業系統: XP
軟體版本: 2003
閱讀權限: 10
註冊時間: 2012-10-16
最後登錄: 2012-12-1

6^#

發表於 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
他出現以下錯誤
我巨集有開低

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

帖子: 1018
主題: 15
精華: 0
積分: 1058
點名: 0
作業系統: win7 32bit
軟體版本: Office 2016 64-bit
閱讀權限: 50
性別: 男
來自: 桃園
註冊時間: 2012-5-9
最後登錄: 2022-9-28

7^#

發表於 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

複製代碼

帖子: 17
主題: 4
精華: 0
積分: 21
點名: 0
作業系統: XP
軟體版本: 2003
閱讀權限: 10
註冊時間: 2012-10-16
最後登錄: 2012-12-1

8^#

發表於 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

複製代碼

出現以下畫面

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

帖子: 17
主題: 4
精華: 0
積分: 21
點名: 0
作業系統: XP
軟體版本: 2003
閱讀權限: 10
註冊時間: 2012-10-16
最後登錄: 2012-12-1

9^#

發表於 2012-10-28 18:31 | 只看該作者

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

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

帖子: 1018
主題: 15
精華: 0
積分: 1058
點名: 0
作業系統: win7 32bit
軟體版本: Office 2016 64-bit
閱讀權限: 50
性別: 男
來自: 桃園
註冊時間: 2012-5-9
最後登錄: 2022-9-28

10^#

發表於 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依此類推 , 我也不明白哪裡有問題