[分享][翻译]你已使用了多少行数和列数？(Rows and Columns)

element

数据显示，人们平均已使用的行数和列数分别为 1102 和18.2。
而我的分别为470和25。我有408张工作表的最后单元格为A1，也就是说工作表为空。我把它归结为2件事：
可能有些工作簿只包含代码；而大多数工作簿里的Sheet2和Sheet3是没有使用的，因为，一个新工作簿默认的工作表数为3张。

还有15张工作表的最后单元格为IV65536。这当然是错误的。特别是使用SpecialCells(xlCellTypeLastCell)方式获取最后单元格时，
但我已经将它排除在外。

如果我不计算空表和满表，我得到下面的数据

平均值：103（行），28（列）
中位数：58（行），11（列）
频率：59（行），11（列）

大概超过2000个文件。当然，这些文件不是我所有的文件，仅仅是在My Documents（我的文档）里。如果你想知道你的平均数，请执行下面代码：

复制内容到剪贴板

代码:

Sub LastCells()
   
    Dim sro As Scripting.FileSystemObject
    Dim srFolder As Scripting.Folder
   
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
   
    Set sro = New Scripting.FileSystemObject
   
    Set srFolder = sro.GetFolder("C:\Documents and Settings\dk\My Documents\")
   
    GetLastCells srFolder
   
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
   
End Sub

Sub GetLastCells(srFolder As Scripting.Folder)
   
    Dim srFile As Scripting.File
    Dim srSubFolder As Scripting.Folder
    Dim wb As Workbook, sh As Worksheet, rLast As Range
   
    For Each srFile In srFolder.Files
        If srFile.Type = "Microsoft Excel Worksheet" Then
            Set wb = Workbooks.Open(srFile.Path)
            For Each sh In wb.Worksheets
                If Not sh.ProtectContents Then
                    Set rLast = sh.Cells.SpecialCells(xlCellTypeLastCell)
                    With ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)
                        .Offset(1, 0).Value = wb.FullName
                        .Offset(1, 1).Value = rLast.Address
                        .Offset(1, 2).Value = rLast.Row
                        .Offset(1, 3).Value = rLast.Column
                    End With
                End If
            Next sh
            wb.Close False
        End If
    Next srFile
   
    For Each srSubFolder In srFolder.SubFolders
        GetLastCells srSubFolder
    Next srSubFolder
      
End Sub

注：1，自行修改需要查找的路径。 2， 引用工程库：Microsoft Scripting Runtime

QUOTE:

引用:

    An average Microsoft Excel spreadsheet document has 1,102 rows and 18.2 columns.I say:
That's 470 rows and 25 columns. I had 408 sheets whose last cell was A1, i.e. blank. I attribute that to two things: I probably have quite a few workbooks that are just code; Most of the workbooks I get from other people have a Sheet2 and a Sheet3 that are unused. The default number of sheets for a new workbook is three.

I also had 15 sheets whose last cell is IV65536, which is clearly wrong. That's a typical problem with using the SpecialCells(xlCellTypeLastCell) method, but I'll just exclude those from the average.

If I don't count the empty sheets and the "full" sheets, I get:

Mean: 103 rows and 28 columns
Median: 58 rows and 11 columns
Mode: 59 rows and 11 columns

That's over about 2,000 files. It's not all the Excel files I have, it's just all of them in the MyDocuments folder. If you'd like to see your average, I've posted the code I used below. It takes a few minutes to run and I had to click a few dialog boxes that asked me to edit links or start an external application.

24th January 2008, 08:50 am by Dick Kusleika

兰色幻想

学习一下[em09]

hhzjxss

谢谢老师的翻译资料，可是，好象没什么用吧，管它用多少，只要够用就好！[em01]

戏子

自愧一下

[em04]

烟雨江南

修改了一下，以适应中文版（准备回家过年了哟，明年见了各位）：

Sub LastCells()
  
    Dim sro As Scripting.FileSystemObject
    Dim srFolder As Scripting.Folder
  
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    Application.EnableEvents = False
  
    Set sro = New Scripting.FileSystemObject
  
    Set srFolder = sro.GetFolder("C:\")
  
    GetLastCells srFolder
   
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Sub GetLastCells(srFolder As Scripting.Folder)
  
    Dim srFile As Scripting.File
    Dim srSubFolder As Scripting.Folder
    Dim wb As Workbook, sh As Worksheet, rLast As Range
  
    For Each srFile In srFolder.Files
        If srFile.Type = "Microsoft Excel 工作表" Then
            Set wb = Workbooks.Open(srFile.Path)
            For Each sh In wb.Worksheets
                If Not sh.ProtectContents Then
                    Set rLast = sh.Cells.SpecialCells(xlCellTypeLastCell)
                    With ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)
                        .Offset(1, 0).Value = wb.FullName
                        .Offset(1, 1).Value = rLast.Address
                        .Offset(1, 2).Value = rLast.Row
                        .Offset(1, 3).Value = rLast.Column
                    End With
                End If
            Next sh
            wb.Close False
        End If
    Next srFile

[此贴子已经被作者于2008-1-31 23:25:31编辑过]

hai

看不懂![em06]

一剑惊心

看来得好好学习一下英语

3dmax456789

谢谢分享！学习一下！