Với định dạng có điều kiện dựa trên công thức, thật dễ dàng để định dạng dựa trên các ô khác trong sổ làm việc, chỉ bằng cách tham chiếu đến các ô đó trong công thức. Tuy nhiên, sẽ phức tạp hơn nếu bạn muốn các thang màu bắt nguồn từ các giá trị trong phạm vi khác. Trong bài đăng này, tôi thảo luận về hai cách để căn cứ thang màu trên một phạm vi khác. Cái đầu tiên sử dụng công cụ máy ảnh trong khi cái thứ hai là chương trình con VBA bắt chước định dạng có điều kiện
Dưới đây là một ví dụ về những gì tôi đang nói về. Định dạng màu không dựa trên các giá trị trong bảng tổng hợp. Thay vào đó, nó phản ánh các giá trị trong bảng thứ hai, mỗi ô chứa sự khác biệt so với năm trước trong bảng tổng hợp. Màu sắc trải dài từ đỏ ở cấp thấp đến xanh lục ở cấp cao
Vì vậy, đây là hai cách tiếp cận của tôi để làm điều này
Sử dụng Công cụ Máy ảnh
Phương pháp này sử dụng công cụ máy ảnh chưa được công khai của Excel, công cụ này tạo ra một hình ảnh trực tiếp được liên kết với một nhóm ô. Trong trường hợp này, định dạng được áp dụng cho bảng tổng hợp, nhưng bạn có thể thực hiện với bất kỳ phạm vi nào. Đây là các bước
- Tạo phạm vi công thức mà bạn sẽ dựa trên định dạng có điều kiện
- Định dạng các số trong phạm vi đó thành ẩn, bằng cách sử dụng định dạng tùy chỉnh là “;;;”. Tất cả những gì bạn muốn xem là định dạng có điều kiện
- Sử dụng công cụ máy ảnh để chụp ảnh toàn bộ bảng tổng hợp và dán nó vào phạm vi bạn vừa tạo, sắp xếp các ô được định dạng theo điều kiện. Đặt ảnh hoàn toàn trong suốt, sử dụng cài đặt “no fill”. Bằng cách này, bạn có thể nhìn xuyên qua hình ảnh đến các ô được định dạng có điều kiện bên dưới
Kết quả sẽ như hình minh họa bên dưới. Bảng tổng hợp nguồn nằm trong các hàng từ 11 đến 18 và bạn có thể thấy rằng hình ảnh bắt đầu từ hàng 2 được liên kết với nó. Các ô bên dưới hình ảnh chứa các công thức đề cập đến bảng tổng hợp. Định dạng có điều kiện dựa trên các ô này, có văn bản ẩn do định dạng tùy chỉnh
Một điều cần lưu ý là hình ảnh không cập nhật cho đến khi có tính toán lại trang tính. Bạn có thể phải tính toán lại với F9 để cập nhật hình ảnh
Đối với một dự án, tôi đã tăng cường phương pháp này bằng cách viết mã cho phép tôi chuyển đổi qua lại giữa các giá trị trong bảng tổng hợp và các giá trị mà định dạng có điều kiện dựa trên
Sử dụng VBA để tạo “FauxMatting”
Như tiêu đề ngụ ý, phương pháp này cố gắng sao chép định dạng có điều kiện bằng VBA. Chương trình con sau đây lấy hai phạm vi – một phạm vi nguồn và một phạm vi đích – làm đối số của nó. Nó tìm thấy các giá trị cao nhất và thấp nhất trong phạm vi nguồn. Nó gán cho mỗi giá trị đó một màu theo thang từ xanh sang đỏ, với màu trắng ở giữa. Điều này được thực hiện bằng cách chia phạm vi giá trị nguồn giá trị thành 255 gia số. Các màu sau đó được gán cho phạm vi mục tiêu
Sub ConditionalFauxmatting[rngSource As Excel. Phạm vi, mục tiêu lại As Excel. Phạm vi]
Const NUMBER_OF_INCREMENTS As Long = 255
Dim MinValue As Double
Dim MaxValue As Double
Dim ScaleIncrement As Double
Dim ScalePosition As Long
Dim var As Variant
Dim CellColor[] As Long
Dim i As Long, j As Long
If Not [rngSource.Rows.Count = rngTarget.Rows.Count Và rngSource. Cột. Đếm = rngTarget. Cột. Count] Sau đó
MsgBox "Phạm vi nguồn và đích phải là" & vbCrLf . Bảng TínhChức Năng. Min[rngSource. Giá trị] "same shape and size"
GoTo exit_point
End If
MinValue = Application.WorksheetFunction.Min[rngSource.Value]
MaxValue = Ứng dụng. Bảng TínhChức Năng. Tối đa [rngSource. Value]
'chia phạm vi giữa các giá trị Tối thiểu và Tối đa thành 255 gia số
Gia tăng tỷ lệ = [Giá trị tối đa - Giá trị tối thiểu] / NUMBER_OF_INCREMENTS
. tế bào. Đếm = 1
If ScaleIncrement = 0 Or rngSource.Cells.Count = 1 Sau đó
rngTarget. tế bào. Nội địa. Màu = RGB[255, 255, 255]
Đi tới điểm thoát
Kết thúc< . Giá trị If
'assign all the values to variant array
var = rngSource.Value
ReDim CellColor[ UBound [var, 1], UBound[var, 2]]
For i = LBound[var, 1] To UBound[var, 1]
For j = LBound[var, 2] To UBound[var, 2]
'the scale position must be a value between 0 and 255
ScalePosition = [var[i, j] - MinValue] * [1 / ScaleIncrement]
'this formula goes from blue to red, hitting white - RGB[255,255,255] at the midpoint
CellColor[i, j] = RGB[Application.WorksheetFunction.Min[ScalePosition * 2, 255], _
IIf[ScalePosition < 127, 255, abs[ScalePosition - 255] * 2], _
IIf[ScalePosition . hàng. Đếm
Next j
Next i
'assign the colors stored in the array
'to the target range
With rngTarget
For i = 1 To .Rows.Count
Vì j = 1 Tới .
. Ô[i,j]. Nội địa. Màu = CellColor[i, j]
Tiếp theo j
Next i
End With
exit_point:
Hết Phụ
Kết quả trông như thế này
Tôi không chắc điều này thực tế đến mức nào, nhưng thật thú vị khi tìm ra. Rõ ràng, bạn muốn liên kết điều này với sự kiện bảng tính hoặc bảng tổng hợp để cập nhật định dạng khi giá trị thay đổi