Excel định dạng có điều kiện các ô được mã hóa cứng

Bài viết này mô tả các cách khác nhau để xác định giá trị bằng chữ hoặc giá trị được mã hóa cứng trong công thức. Hình ảnh trên cho thấy kết quả từ một macro lặp qua từng trang tính trong một sổ làm việc nhất định để tìm kiếm các giá trị được mã hóa cứng trong công thức

Một liên kết được tạo và bản thân công thức được hiển thị nếu tìm thấy giá trị được mã hóa cứng

Có gì trên trang web này

1. Tìm các giá trị được mã hóa cứng trong công thức [Định dạng có điều kiện]

Tôi tìm thấy UDF này trong Thư điện tử dành cho Chuyên gia Excel của David Hager [EEE] trên trang web của J Walkenbach. Bạn cần áp dụng định dạng có điều kiện với công thức sử dụng Hàm do người dùng xác định được mô tả bên dưới

Định dạng có điều kiện làm nổi bật các ô có công thức chứa các giá trị được mã hóa cứng, hình ảnh trên cho biết ô nào chứa các giá trị được mã hóa cứng. Chúng được đánh dấu màu xanh lá cây. Ví dụ, ô C3 chứa công thức sau

=SUM[A2. A5, 5]

Đối số thứ hai trong hàm SUM là một giá trị được mã hóa cứng, ô C3 được tô sáng

Lưu ý, Định dạng có điều kiện rất dễ bay hơi và có thể làm chậm sổ làm việc của bạn một cách đáng kể. Nó chỉ đánh dấu các ô, bạn cần tự tìm các ô được đánh dấu

Tôi giới thiệu macro trong phần 2. Macro tạo một trang tính mới và liệt kê tất cả các công thức chứa các giá trị được mã hóa cứng cũng như liên kết đến các ô đó

'Name User Defined Function
Function CellUsesLiteralValue[Cell As Range] As Boolean

'Check if cell has not a formula
If Not Cell.HasFormula Then

'Save boolean value FALSE to variable CellUsesLiteralValue 
CellUsesLiteralValue = False

'Continue here if cell has a formula 
Else

'Use like operator to determine if cell formula contains hardcoded values, it returns TRUE if found.
'Characters enclosed in brackets allows you to match any single character in the string.
' The hashtag matches any single digit, the asterisk matches zero or more characters
CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/[], ]#*"
End If
End Function

VBA. Thuộc tính công thức phạm vi. thích nhà điều hành. thuộc tính HasFormula. . câu lệnh if then

1. 1 Làm cách nào tôi có thể định vị các ô chứa công thức có giá trị bằng chữ?

Sử dụng UDF làm công thức định dạng có điều kiện của bạn, tôi sẽ giải thích chính xác cách thực hiện trong phần tiếp theo bên dưới nếu bạn quan tâm. Nó chấp nhận một ô duy nhất làm đối số. Nó trả về True nếu công thức của ô chứa một toán tử theo sau là một chữ số. Nói cách khác, nó xác định các ô có công thức chứa giá trị số bằng chữ

Bạn có thể kiểm tra từng ô trong phạm vi và đánh dấu ô đó nếu hàm trả về True. UDF với định dạng có điều kiện làm nổi bật các ô chứa công thức bằng chữ, cột C. Bạn có thể lấy file excel ở cuối bài viết này nếu bạn quan tâm đến kỹ thuật này

Tôi đã in đậm tất cả các giá trị bằng chữ trong công thức, cột E. Nó dường như cũng hoạt động với các công thức lồng nhau. Các giá trị Boolean có thể được biểu thị là TRUE/FALSE nhưng cũng có thể là 1/0 trong Excel. Lưu ý, một số công thức được tạo để có các đối số được mã hóa cứng, chẳng hạn như col_index_num trong hàm VLOOKUP

1. 2 Cách sử dụng Hàm do người dùng xác định với Định dạng có điều kiện

1. Chuyển đến trang tính bạn muốn tìm kiếm
2. Nhấn chuột trái vào nút có hình tam giác để chọn tất cả các ô trên trang tính, xem hình trên
3. Chuyển đến tab "Trang chủ" trên dải băng nếu bạn chưa ở đó
4. Nhấn nút chuột trái vào nút "Định dạng có điều kiện", xem hình trên. Một menu bật lên xuất hiện
5. Nhấn chuột trái vào "Quy tắc mới. ", xem hình trên. Một hộp thoại hiện lên màn hình.
   
   
   Hình ảnh trên hiển thị sai tham chiếu ô C2, ô này phải là A1.
6. Nhấn bằng nút chuột trái trên "Sử dụng công thức để xác định ô cần định dạng"
7. Nhập công thức sau. =CellUsesLiteralValue[A1]
8. Nhấn chuột trái vào "Định dạng. " cái nút. Một hộp thoại khác xuất hiện
9. Chuyển đến tab "Điền"
10. Chọn một màu
11. Nhấn chuột trái vào nút "OK"
12. Nhấn chuột trái vào nút "OK" một lần nữa

2. Tìm các giá trị được mã hóa cứng trong các công thức trên các trang tính

Tôi có một sổ làm việc lớn và tôi đang vội

Các tác giả muốn bạn sử dụng nó làm công thức định dạng có điều kiện để phát hiện các ô chứa công thức có giá trị bằng chữ, đây có thể là công việc tẻ nhạt với một sổ làm việc lớn. Tôi muốn chỉ cho bạn cách sử dụng udf để tạo danh sách tất cả các ô chứa giá trị bằng chữ, từ toàn bộ sổ làm việc

Hình trên cho bạn thấy macro của tôi bên dưới trả về cái gì, trước tiên, một trang tính mới được chèn vào. Sau đó, nó được phổ biến với các liên kết đến tất cả các ô chứa công thức bằng chữ và các công thức tương ứng của chúng

Tôi đã sử dụng lại một số phần của Rick Rothstein

'Name macro
Sub FindLiteralsInWorkbook[]

'Dimension variables and declare data types
Dim C As Range, A As Range, Addresses As String, i As Single
Dim cell As Range

'Add worksheet to workbook
Sheets.Add

'The SET statement allows you to save an object reference to a variable, in this case the active worksheet
Set x = ActiveSheet

'Save text "Link" to cell A1.
x.Range["A1"] = "Link"

'Save text "Formula" to cell B1.
x.Range["B1"] = "Formula"

'Save number 1 to variable i.
i = 1

'Iterate through worksheets in active workbook
For Each sh In ActiveWorkbook.Worksheets

    'Enable error handling
    On Error Resume Next

    'Find cells containing constants and save to object C
    Set C = sh.Cells.SpecialCells[xlConstants]

    'Check if C is empty
    If C Is Nothing Then

      'Find cells containing formulas and save to object C
      Set C = sh.Cells.SpecialCells[xlFormulas]
    
    'Continue here if C is not empty
    Else

      'Returns the union of the two cell ranges and saves to object C
      Set C = Union[C, sh.Cells.SpecialCells[xlFormulas]]
    End If

    'Iterate through all the ranges in a multiple-area selection.
    For Each A In C.Areas

      'Iterate through all cells in cell range A
      For Each cell In A

         'Use User Defined Function to determine if cell contains at least one hardcoded value
         If CellUsesLiteralValue[cell] = True Then

              'Add 1 to variable i 
              i = i + 1

              'Create a hyperlink based on the cell address
              x.Hyperlinks.Add Anchor:=x.Range["A" & i], _
              Address:=ActiveWorkbook.Path & "\" & ActiveWorkbook.Name, _
              SubAddress:=sh.Name & "!" & cell.Address, _
              TextToDisplay:=sh.Name & "!" & cell.Address
              x.Range["B" & i] = "'" & cell.Formula
         End If

      'Continue with next cell
      Next cell

    'Continue with next cell range
    Next A

    'Disable error handling
    On Error GoTo 0

'Continue with next worksheet
Next sh

'Resize column A:E widths 
x.Columns["A:E"].AutoFit
End Sub

VBA. Ô đặc biệt[xlConstants]. liên hiệp. Khu vực

3. Đặt mã ở đâu?

1. Sao chép chức năng do người dùng xác định CellUsesLiteralValue
2. Chuyển đến Trình chỉnh sửa VB [Alt + F11]
3. Chuyển đến menu và nhấn bằng nút chuột trái trên Chèn
4. Nhấn chuột trái vào Module
5. Dán UDF vào mô-đun mã của sổ làm việc của bạn
6. Sao chép macro FindLiteralsInWorkbook
7. Dán vào mô-đun. Cả UDF và macro giờ đây sẽ hiển thị trong mô-đun, xem hình ảnh ở trên
8. Quay lại Excel

4. Cách chạy macro

1. Chuyển đến tab Nhà phát triển trên dải băng
2. Nhấn bằng chuột trái vào nút "Macro"
3. Nhấn bằng nút chuột trái trên "FindLiteralsInWorkbook" và sau đó nhấn bằng nút chuột trái trên "Run"

Lấy tệp Excel

đề nghị đọc

siêu liên kết. Thêm phương thức [Excel]

danh mục vĩ mô

Thêm hoặc xóa giá trị trong danh sách thả xuống theo cách lập trình
Bài viết này trình bày cách thêm hoặc xóa giá trị trong danh sách thả xuống thông thường dựa trên danh sách […]
Kết thúc hàm