Computer >> Hướng Dẫn Máy Tính >  >> Phần Mềm >> Office

Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

Conditional formatting dựa trên giá trị workbook bên ngoài cho phép bạn tự động định dạng ô trong một workbook Excel dựa trên dữ liệu được lưu trữ trong một workbook khác. Tính năng này thiết yếu để tạo báo cáo động, bảng điều khiển và so sánh dữ liệu trên nhiều tệp trong môi trường kinh doanh.

Trong hướng dẫn này, chúng tôi sẽ chỉ cách kích hoạt conditional formatting dựa trên giá trị workbook bên ngoài.

Giả sử bạn theo dõi doanh số quý thực tế trong một tệp và mục tiêu doanh số quý trong tệp khác. Trong sheet thực tế, bạn muốn highlight bất kỳ doanh số thực tế nào dưới mục tiêu, kéo đúng mục tiêu từ tệp bên ngoài.

Phương Pháp 1: Cột Trợ Giúp Với Tham Chiếu Bên Ngoài

Đây là phương pháp đáng tin cậy nhất hoạt động trong tất cả các phiên bản Excel. Bạn có thể sử dụng công thức worksheet với tham chiếu bên ngoài trong một cột trợ giúp. Áp dụng conditional formatting dựa trên giá trị của cột trợ giúp.

Bước 1: Chuẩn Bị Workbook Của Bạn

Trước tiên, tạo và lưu cả hai workbook với dữ liệu mẫu trên:

  • Tạo “Sales Target.xlsx” và nhập dữ liệu mục tiêu.
  • Lưu nó trên desktop hoặc một thư mục cụ thể.
  • Tạo “Actual Sales.xlsx” và nhập dữ liệu bán hàng thực tế.
  • Lưu nó ở cùng một vị trí.

Bước 2: Tạo Cột Trợ Giúp Với Tham Chiếu Bên Ngoài

  • Trong “Actual Sales.xlsx”, thêm cột trợ giúp (bắt đầu từ cột G):
  • Chọn ô G2 và chèn công thức sau.
=[SalesTarget.xlsx]Quarterly_Targets!B2
  • Kéo công thức sang phải để tự động điền công thức trong ô H2, I2 và J2.
Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài
  • Để cập nhật giá trị, chọn tệp Sales Target.xlsx.
Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài
  • Chọn ô G2:J2.
  • Kéo công thức xuống để tự động điền công thức trong các ô còn lại.
Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

Bước 3: Áp Dụng Conditional Formatting Sử Dụng Cột Trợ Giúp

Bây giờ sử dụng tham chiếu nội bộ cho conditional formatting.

  • Chọn phạm vi ô (B2:B6).
  • Chuyển đến tab Home >> chọn Conditional Formatting >> chọn New Rule.
  • Chọn Use a formula to determine which cells to format.
  • Nhập công thức sau:
  • Nhấp Format >> chọn màu điền đỏ nhạt.
  • Nhấp OK.
Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

Thêm Nhiều Quy Tắc:

Lặp lại cho từng quý nếu cần.

Quý 2:

  • Nhập công thức sau:
  • Nhấp Format >> chọn màu điền xanh dương nhạt.
  • Nhấp OK.

Quý 3:

  • Nhập công thức sau:
  • Nhấp Format >> chọn màu điền xanh lá nhạt.
  • Nhấp OK.

Quý 4:

  • Nhập công thức sau:
  • Nhấp Format >> chọn màu điền tím nhạt.
  • Nhấp OK.
Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

Bước 4: Ẩn Cột Trợ Giúp (Tùy Chọn)

  • Chọn cột G:J.
  • Nhấp chuột phải >> chọn Hide.
Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

Dữ liệu của bạn sẽ hiển thị conditional formatting dựa trên giá trị workbook bên ngoài, nhưng Excel sử dụng cột trợ giúp nội bộ để tránh hạn chế tham chiếu bên ngoài.

Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

Phương Pháp 2: Giải Pháp Power Query

Power Query cung cấp giải pháp mạnh mẽ cho người dùng Excel 365 hoặc Excel 2016+.

Bước 1: Nhập Dữ Liệu Bên Ngoài Với Power Query

  • Mở workbook “Actual Sales.xlsx”.
  • Chuyển đến tab Data >> chọn Get Data >> chọn From File >> chọn From Workbook.
  • Duyệt để chọn tệp “Sales Target.xlsx”.
  • Chọn bảng “Quarterly_Targets”.
  • Nhấp Import.
Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài
  • Trong cửa sổ Navigator >> chọn sheet dữ liệu.
  • Nhấp Transform Data.
Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài
  • Trong Power Query Editor:
    • Đổi tên cột để phù hợp nhu cầu (Target_Q1, Target_Q2, v.v.).
    • Chuyển đến tab Home >> Close & Load To.
Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài
    • Chọn Table >> chọn New worksheet.
    • Nhấp OK.
Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

Bước 2: Áp Dụng Conditional Formatting

Bây giờ sử dụng conditional formatting tiêu chuẩn với dữ liệu đã nhập như trong giải pháp 1, nhưng chỉ tham chiếu dữ liệu nội bộ.

  • Chọn phạm vi ô (B2:B6).
  • Chuyển đến tab Home >> chọn Conditional Formatting >> chọn New Rule.
  • Chọn Use a formula to determine which cells to format.
  • Nhập công thức sau:
=B2 

    

  • Nhấp Format >> chọn màu điền đỏ nhạt.
    • 

  • Nhấp OK.
    • 


Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

    

  • Thêm quy tắc cho các quý còn lại.
    • 



Quý 2:


=C2 

Quý 3:


=D2 

Quý 4:


=E2 
Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

    

  • Làm mới Power Query bất cứ khi nào mục tiêu thay đổi.

      

    • Nhấp chuột phải >> chọn Refresh.
      • 

    

    • 

  • Bạn có thể lên lịch làm mới tự động nếu dữ liệu thay đổi thường xuyên.
    • 

  • Chuyển đến tab Data >> chọn Queries and Connections.
    • 

  • Nhấp chuột phải vào Queries >> chọn Properties。
    • 


Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

    

  • Trong Refresh every >> chèn 5 phút.
    • 

  • Nhấp OK.
    • 


Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

Phương pháp này tự động làm mới dữ liệu bên ngoài và tránh hạn chế tham chiếu.


Phương Pháp 3: Macro VBA Cho Tự Động Hóa Hoàn Toàn


Nếu bạn thoải mái với VBA, bạn có thể tạo một macro cập nhật conditional formatting dựa trên dữ liệu bên ngoài. Nó sẽ so sánh thực tế và mục tiêu, áp dụng định dạng tự động, ngay cả khi tệp tham chiếu bị đóng.


Để mở Trình Soạn Thảo VBA:


    

  • Mở workbook Actual Sales của bạn.
    • 

  • Chuyển đến tab Developer >> chọn Visual Basic. Hoặc nhấn Alt + F11.
    • 

  • Trong cửa sổ Project, nhấp chuột phải vào workbook của bạn,
    • 

  • Chọn Insert >> chọn Module.
    • 


Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

    

  • Sao chép-dán mã VBA sau.
    • 



Mã VBA:


Sub HighlightSalesBelowTarget()
Dim targetFilePath As String
targetFilePath = "C:\Users\Sales Target.xlsx" ' <--- Update this to your file path
Dim wbTarget As Workbook
Dim wsTarget As Worksheet
Dim wsActual As Worksheet
Dim i As Long, j As Long
Dim salesValue As Variant, targetValue As Variant
Set wsActual = ThisWorkbook.Sheets("Performance_Data")
Set wbTarget = Workbooks.Open(targetFilePath, ReadOnly:=True)
Set wsTarget = wbTarget.Sheets("Quarterly_Targets")
' Data rows: 2 to 6, columns: 2 (B/Q1) to 5 (E/Q4)
For i = 2 To 6 ' Rows: products
For j = 2 To 5 ' Columns: Q1-Q4
salesValue = wsActual.Cells(i, j).Value
targetValue = wsTarget.Cells(i, j).Value
If IsNumeric(salesValue) And IsNumeric(targetValue) Then
If salesValue < targetValue Then
wsActual.Cells(i, j).Interior.Color = RGB(255, 199, 206) ' Light red
Else
wsActual.Cells(i, j).Interior.Pattern = xlNone ' No color
End If
End If
Next j
Next i
wbTarget.Close SaveChanges:=False
MsgBox "Highlighting complete.", vbInformation
End Sub

Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

    

  • Cập nhật đường dẫn tệp với đường dẫn đầy đủ đến tệp Sales Target của bạn.
    • 

  • Macro mở workbook mục tiêu.
    • 

  • Lặp qua từng sản phẩm và từng quý.
    • 

  • Nếu giá trị bán hàng nhỏ hơn mục tiêu, ô được highlight màu đỏ nhạt.
    • 

  • Macro đóng workbook mục tiêu tự động.
    • 



Lưu Và Chạy:


    

  • Lưu workbook của bạn dưới dạng tệp bật macro (.xlsm)。
    • 

  • Chuyển đến tab Developer >> chọn Macros。
    • 

  • Chọn HighlightSalesBelowTarget >> nhấp Run。
    • 


Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

Kết Quả:

Làm Chủ Conditional Formatting Chéo Workbook: Kích Hoạt Định Dạng Từ Dữ Liệu Bên Ngoài

Điều GÌ KHÔNG HOẠT ĐỘNG: Tham Chiếu Bên Ngoài Trực Tiếp & Phạm Vi Đặt Tên


Một số phiên bản Excel hiển thị cảnh báo “You may not use references to other workbooks for Conditional Formatting criteria.”


    

  • Tham chiếu workbook bên ngoài trực tiếp (ví dụ: =[Sales_Targets.xlsx]Quarterly_Targets!B2) không được phép trong quy tắc conditional formatting. Excel sẽ báo lỗi.
    • 

  • Phạm vi đặt tên được định nghĩa trong workbook bên ngoài không thể được tham chiếu trong conditional formatting của workbook khác.
    • 

  • Ngay cả việc sử dụng INDIRECT hoặc các hàm tương tự cũng không hoạt động cross-file trong ngữ cảnh này.
    • 



Không có cách trực tiếp, gốc để sử dụng giá trị bên ngoài trong quy tắc conditional formatting.


Khuyến Nghị


    

  • Cho hầu hết doanh nghiệp: Sử dụng Power Query để nhập dữ liệu bên ngoài. Nó mạnh mẽ, hỗ trợ làm mới và giữ tất cả logic bên trong một workbook.
    • 

  • Cho kiểm tra nhanh/ad-hoc: Sử dụng cột trợ giúp với tham chiếu bên ngoài nếu bạn không介意 giữ cả hai tệp mở.
    • 

  • Cho giải pháp tự động hóa, liên tục: Sử dụng VBA để tự động hóa và định dạng không cần can thiệp, đặc biệt cho tập dữ liệu lớn.
    • 



Kết Luận


External conditional formatting là một tính năng mạnh mẽ cho phép trực quan hóa dữ liệu động, cross-file. Bạn có thể sử dụng bất kỳ phương pháp nào bạn chọn dựa trên tình huống và sự thuận tiện của bạn. Hãy nhớ luôn kiểm tra thiết lập của bạn kỹ lưỡng và duy trì tài liệu rõ ràng về các phụ thuộc bên ngoài để tham khảo trong tương lai và cộng tác với các thành viên trong nhóm.


Kích hoạt conditional formatting dựa trên giá trị từ workbook bên ngoài không thể thực hiện gốc trong Excel.


Nhận Bài Tập Excel Nâng Cao Miễn Phí Có Giải Pháp!