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 Show
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ày1. 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
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ínhTô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?
4. Cách chạy macro
Lấy tệp ExcelTìm-ô-chứa-công-thức-có-giá-trị-bằng-chữv2. xlsm đề nghị đọcsiê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 Thêm giá trị vào danh sách thả xuống thông thường theo chương trình Thêm giá trị vào bảng hai chiều dựa trên điều kiện [VBA] Thêm giá trị vào trang tính dựa trên điều kiện [VBA] Áp dụng động danh sách thả xuống Tự động thay đổi kích thước cột khi bạn nhập Tự động nhập dữ liệu [VBA] Nhập dữ liệu cơ bản [VBA] Lịch có lập lịch [vba] Thay đổi phạm vi dữ liệu biểu đồ bằng cách sử dụng Danh sách thả xuống [VBA] Thay đổi chuỗi biểu đồ bằng cách nhấp vào dữ liệu [VBA] Nhấp vào một ô để ẩn hoặc hiển thị một cột [VBA] Hợp nhất trang tính [vba] Sao chép phạm vi ô động [VBA] Sao chép dữ liệu từ sổ làm việc trong thư mục và thư mục con Sao chép tiêu chí bộ lọc Bảng Excel theo chương trình Sao chép trang tính trong sổ làm việc đang hoạt động sang sổ làm việc mới Đếm chuỗi văn bản trong tất cả các công thức trong trang tính [VBA] Tạo nút In [VBA] Tạo danh sách riêng biệt duy nhất bằng cách sử dụng Bộ lọc nâng cao trong macro [VBA] Tạo nhận xét nếu giá trị ô lớn hơn cột Tạo trang tính mới theo chương trình dựa trên các giá trị trong phạm vi ô [VBA] Tùy chỉnh ruy-băng và cách thêm macro của bạn Chỉnh sửa dữ liệu hóa đơn [VBA] Lịch Excel [VBA] Mẫu Excel. Hoàn thành công việc [VBA] Trích xuất các tham chiếu ô được điền các giá trị [VBA] Lọc Bảng do Excel xác định dựa trên ô đã chọn [VBA] Lọc Bảng do Excel xác định theo chương trình [VBA] Tìm giá trị lặp lại liên tiếp nhiều nhất/ít nhất [VBA] Ẩn các cột cụ thể theo lập trình Ẩn các trang tính cụ thể theo cách lập trình Đánh dấu các phạm vi ngày chồng lấp bản ghi đã chọn [VBA] Cách thêm một mục được tạo tùy chỉnh vào menu lối tắt [VBA] Cách thêm macro vào Thanh công cụ truy cập nhanh Excel của bạn Cách thay đổi động ảnh trong trang tính [VBA] Cách sao chép mọi hàng thứ n từ một danh sách Cách sao chép bảng Excel theo chương trình Cách sao chép các phạm vi ô không liền kề Cách tạo danh sách nhận xét từ trang tính theo cách lập trình Cách tạo biểu đồ Excel tương tác [VBA] Cách đánh dấu hàng của ô đã chọn theo chương trình Cách ghi nhật ký khi mở và đóng sổ làm việc [VBA] Cách chọn nhanh các ô trống Cách lưu các hàm và macro tùy chỉnh vào Phần bổ trợ Cách lưu nhiều trang tính cụ thể vào một tệp pdf theo chương trình Cách sử dụng HỘP ĐỐI THOẠI Cách sử dụng Thanh cuộn Xác định các số bị thiếu trong một cột Liệt kê tất cả các sổ làm việc đang mở và các trang tính tương ứng [VBA] Liệt kê các Bảng do Excel xác định trong sổ làm việc [VBA] Định vị hình dạng trong sổ làm việc Di chuyển hình [VBA] Nhân các số trong mỗi hàng với các hàng còn lại trong phạm vi ô (UDF) Chuẩn hóa dữ liệu [VBA] Mở tệp Excel trong một thư mục [VBA] Mở sổ làm việc sẽ tự động chạy macro Điền vào hộp tổ hợp (điều khiển biểu mẫu) [VBA] Điền vào hộp tổ hợp các giá trị từ bảng tổng hợp [VBA] Điền vào hộp danh sách các giá trị riêng biệt duy nhất từ bảng Excel đã lọc [VBA] Điền vào hộp danh sách các giá trị riêng biệt duy nhất [VBA] Chuẩn bị dữ liệu cho Pivot Table – Cách phân tách các giá trị được nối? Sắp xếp lại các giá trị dựa trên danh mục [VBA] Chạy Macro từ danh sách thả xuống [VBA] Lưu dữ liệu hóa đơn [VBA] Tìm kiếm hai bảng có liên quan [VBA] Chọn và xem hóa đơn [VBA] Chọn ô A1 trên tất cả các trang tính trước khi bạn đóng sổ làm việc [VBA] Hiển thị/ẩn ảnh bằng nút Sắp xếp các giá trị trong một ô dựa trên ký tự phân cách Sắp xếp các giá trị trong bảng Excel theo chương trình [VBA] Chia dữ liệu trên nhiều trang tính [VBA] Bật/tắt macro bằng một nút Sử dụng bộ định tính văn bản để chuyển đổi văn bản thành cột dễ dàng hơn [VBA] Làm việc với COMBO BOXES [Điều khiển biểu mẫu] Làm việc với TỆP Làm việc với HỘP VĂN BẢN [Điều khiển biểu mẫu] danh mục exceltrang chủ Bài viết cập nhật mới nhất Hàm Excel Hơn 300 hàm Excel với thông tin chi tiết bao gồm cú pháp, đối số, giá trị trả về và ví dụ cho hầu hết các hàm được sử dụng trong công thức Excel Công thức Excel Hơn 1300 công thức được sắp xếp trong các danh mục con Bảng Excel Bảng Excel đơn giản hóa công việc của bạn với dữ liệu, thêm hoặc xóa dữ liệu, lọc, tính tổng, sắp xếp, nâng cao khả năng đọc bằng cách sử dụng định dạng ô, tham chiếu ô, công thức, v.v. Bộ lọc nâng cao Cho phép bạn lọc dữ liệu dựa trên giá trị đã chọn , văn bản nhất định hoặc tiêu chí khác. Nó cũng cho phép bạn lọc dữ liệu hiện có hoặc di chuyển các giá trị đã lọc sang vị trí mới Xác nhận dữ liệu Cho phép bạn kiểm soát những gì người dùng có thể nhập vào một ô. Nó cho phép bạn chỉ định các điều kiện và hiển thị thông báo tùy chỉnh nếu dữ liệu đã nhập không hợp lệ Danh sách thả xuống Cho phép người dùng làm việc hiệu quả hơn bằng cách hiển thị danh sách mà người dùng có thể chọn một giá trị từ đó. Điều này cho phép bạn kiểm soát những gì được hiển thị trong danh sách và nhanh hơn so với nhập vào một ô Phạm vi được đặt tên Cho phép bạn đặt tên cho một hoặc nhiều ô, điều này giúp bạn dễ dàng tìm ô bằng cách sử dụng hộp Tên, đọc và hiểu các công thức chứa tên thay vì tham chiếu ô Trình giải Excel Bộ giải Excel là một phần bổ trợ miễn phí sử dụng các ô mục tiêu, các ràng buộc dựa trên các công thức trên trang tính để thực hiện phân tích giả định và các vấn đề quyết định khác như hoán vị và kết hợp biểu đồ Một tính năng của Excel cho phép bạn trực quan hóa dữ liệu trong biểu đồ Định dạng có điều kiện Định dạng ô hoặc giá trị ô dựa trên điều kiện hoặc tiêu chí, có nhiều công cụ Định dạng có điều kiện tích hợp sẵn mà bạn có thể sử dụng hoặc sử dụng công thức định dạng có điều kiện tùy chỉnh Bảng tổng hợp Cho phép bạn nhanh chóng tóm tắt lượng dữ liệu khổng lồ theo cách rất thân thiện với người dùng. Tính năng Excel mạnh mẽ này cho phép bạn phân tích, sắp xếp và phân loại dữ liệu quan trọng một cách hiệu quả VBA VBA là viết tắt của Visual Basic for Applications và là ngôn ngữ lập trình máy tính do Microsoft phát triển, nó cho phép bạn tự động hóa các tác vụ tốn thời gian và tạo các chức năng tùy chỉnh macro Một chương trình hoặc chương trình con được xây dựng trong VBA mà bất kỳ ai cũng có thể tạo. Sử dụng trình ghi macro để nhanh chóng tạo macro VBA của riêng bạn UDF UDF là viết tắt của Hàm do người dùng xác định và là các hàm được tạo tùy chỉnh mà bất kỳ ai cũng có thể tạo Lưu trữ Danh sách tất cả các bài báo đã xuất bản 12 phản hồi cho “Tìm ô chứa công thức có giá trị bằng chữ (được mã hóa cứng)”
|
