Tim có một sổ làm việc khá lớn và anh ấy tự hỏi liệu có cách nào để thực hiện “kiểm kê” các hàm được sử dụng trong các công thức khác nhau trong sổ làm việc, cùng với các ô mà các hàm đó được sử dụng hay không.

Nhiệm vụ này khó hơn một chút mà thoạt đầu có vẻ như. Bạn có thể đưa ra một số quy tắc về những gì tạo thành một hàm Excel, nhưng những quy tắc đó có thể hơi khó hiểu. Ví dụ, bạn có thể nghĩ rằng một hàm được xác định bởi một từ viết hoa theo sau bởi một dấu ngoặc đơn mở. Tuy nhiên, điều này đã không đúng trong một thời gian, vì tên hàm hiện có thể chứa dấu chấm.

Cách dễ nhất mà tôi đã tìm thấy để có được khoảng không quảng cáo hàm là phát triển một macro sẽ chuyển qua từng công thức trong mỗi trang tính trong sổ làm việc và kiểm tra công thức đó với mọi hàm trang tính có thể có. Macro sau sẽ làm điều đó tốt:

Sub FormulaInventory()

Dim EFunc(500) As String     Dim iEFCnt As Integer     Dim sFile As String     Dim sTemp As String     Dim SourceBook As Workbook     Dim TargetBook As Workbook     Dim TargetSheet As Worksheet     Dim w As Worksheet     Dim c As Range     Dim iRow As Integer     Dim J As Integer     Dim K As Integer     Dim L As Integer

' Read functions from text file     sFile = ActiveWorkbook.Path & "\ExcelFunctions.txt"

iEFCnt = 0     Open sFile For Input As #1     While Not EOF(1)

Line Input #1, sTemp         sTemp = Trim(sTemp)

If sTemp > "" Then             iEFCnt = iEFCnt + 1             EFunc(iEFCnt) = sTemp & "("

End If     Wend     Close #1

' Sort functions; longest to shortest     For J = 1 To iEFCnt - 1         L = J         For K = J + 1 To iEFCnt             If Len(EFunc(L)) < Len(EFunc(K)) Then L = K         Next K         If L <> J Then             sTemp = EFunc(J)

EFunc(J) = EFunc(L)

EFunc(L) = sTemp         End If     Next J

' Create and setup new workbook     Set SourceBook = ActiveWorkbook     Set TargetBook = Workbooks.Add     Set TargetSheet = TargetBook.Worksheets.Add     TargetSheet.Name = "Inventory"

TargetSheet.Cells(1, 1) = "Function Inventory for " & SourceBook.Name     TargetSheet.Cells(3, 1) = "Function"

TargetSheet.Cells(3, 2) = "Worksheet"

TargetSheet.Cells(3, 3) = "Cell"

TargetSheet.Range("A1").Font.Bold = True     TargetSheet.Range("A3:C3").Font.Bold = True     With TargetSheet.Range("A3:C3").Cells.Borders(xlEdgeBottom)

.LineStyle = xlContinuous         .Weight = xlThin     End With

' Perform actual inventory     iRow = 4     For Each w In SourceBook.Worksheets         For Each c In w.Cells.SpecialCells(xlCellTypeFormulas)

sTemp = c.Formula             For J = 1 To iEFCnt                 If InStr(sTemp, EFunc(J)) Then                     TargetSheet.Cells(iRow, 1) = Left(EFunc(J), Len(EFunc(J)) - 1)

TargetSheet.Cells(iRow, 2) = w.Name                     TargetSheet.Cells(iRow, 3) = Replace(c.Address, "$", "")

iRow = iRow + 1                     sTemp = Replace(sTemp, EFunc(J), "")

End If             Next J         Next c     Next w End Sub

Kiểm tra cẩn thận macro chỉ ra rằng macro đọc trong tệp văn bản (ExcelFunctions.txt) bao gồm danh sách tất cả các hàm trang tính của Excel. Tệp này có thể là liên kết: /excelribbon-ExcelFunctions.txt [được tải xuống bằng cách sử dụng liên kết này] và nó phải được lưu trữ trong cùng thư mục với sổ làm việc mà bạn đang phân tích. (Điều này có nghĩa là sổ làm việc bạn đang phân tích phải đã được lưu trong một thư mục.)

Khi các tên hàm của trang tính được tải từ tệp văn bản, macro sẽ thêm một dấu ngoặc đơn mở cho mỗi tên. Các tên này được lưu trữ trong một mảng, và sau đó mảng này được sắp xếp theo độ dài của tên hàm, với các tên hàm dài nhất ở đầu mảng. Điều này được thực hiện vì một số mơ hồ trong tên hàm. Ví dụ: khi mảng tên-hàm được đọc trong bạn sẽ có một hàm có tên LEFT (và một hàm khác có tên T (. Khi nhìn vào một công thức, nếu tìm thấy LEFT (tên thì T (tên cũng sẽ được tìm thấy)). Bằng cách xem xét các tên hàm dài nhất trước và sau đó xóa các tên đó khỏi chuỗi công thức, sau đó bạn loại bỏ khả năng “dương tính giả”.

Cần lưu ý rằng hàm này sẽ chỉ hoạt động với các hàm trang tính đó được liệt kê trong tệp ExcelFunctions.txt. Bạn có thể sửa đổi tệp, như mong muốn, để điều chỉnh những gì được đặt. Nó hiện bao gồm tất cả các hàm trang tính chuẩn cho Excel 2016, nhưng bạn có thể muốn sửa đổi nó để bao gồm các hàm được cung cấp bởi phần bổ trợ của bạn hoặc bạn có thể muốn xóa các chức năng mà bạn thấy quá bí truyền. (Gợi ý:

Bạn cũng có thể chỉ cần thêm dấu nháy đơn vào trước tên hàm trong tệp văn bản và sau đó nó sẽ không bao giờ được đưa vào trong một khoảng không quảng cáo.) Chỉ có dưới 500 tên hàm trong th e nộp hồ sơ ngay; nếu bạn thêm quá nhiều, bạn sẽ cần phải thay đổi số phần tử được khai báo trong macro cho mảng EFunc.

Khi macro hoàn thành, bạn sẽ tìm thấy khoảng không quảng cáo của mình trong sổ làm việc mới mà macro tạo ra. Bạn nên lưu ý rằng thời gian macro hoàn thành có thể thay đổi từ khá nhanh đến chậm đáng kinh ngạc. Sổ làm việc của bạn càng lớn — càng chứa nhiều trang tính và càng nhiều công thức trên các trang tính đó — thì càng mất nhiều thời gian để hoàn thành công việc.

Hãy nhớ rằng nó phải kiểm tra mọi công thức trong sổ làm việc với gần 500 tên hàm trang tính.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (5136) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.