Định dạng có điều kiện dựa trên các giá trị sổ làm việc bên ngoài cho phép bạn tự động định dạng các ô trong một sổ làm việc Excel dựa trên dữ liệu được lưu trữ trong một sổ làm việc khác. Tính năng này rất cần thiết để tạo báo cáo động, trang tổng quan 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ỉ ra cách kích hoạt định dạng có điều kiện dựa trên các giá trị sổ làm việc bên ngoài.
Giả sử bạn theo dõi doanh số bán hàng thực tế hàng quý trong một tệp và mục tiêu bán hàng hàng quý trong một tệp khác. Trong bảng số liệu thực tế, bạn muốn đánh dấu bất kỳ doanh số bán hàng thực tế nào dưới mức mục tiêu, lấy các mục tiêu chính xác từ tệp bên ngoài.
Phương pháp 1:Cột trợ giúp có 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 trang tính có tham chiếu bên ngoài trong cột trợ giúp. Áp dụng định dạng có điều kiện dựa trên các giá trị của cột trợ giúp.
Bước 1:Chuẩn bị sổ làm việc của bạn
Trước tiên, hãy tạo và lưu cả hai sổ làm việc 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ó vào màn hình của bạn hoặc một thư mục cụ thể.
- Tạo “Doanh số thực tế.xlsx” và nhập dữ liệu doanh số 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 “Doanh số thực tế.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 vào các ô H2, I2 và J2.
- Để cập nhật giá trị, hãy chọn Sales Target.xlsx tập tin.
- Chọn ô G2:J2.
- Kéo công thức xuống để tự động điền công thức vào các ô còn lại.
Bước 3:Áp dụng định dạng có điều kiện bằng cột trợ giúp
Bây giờ hãy sử dụng tham chiếu nội bộ để định dạng có điều kiện.
- Chọn phạm vi ô (B2:B6).
- Truy cập Trang chủ tab>> chọn Định dạng có điều kiện>> chọn Quy tắc mới .
- Chọn Sử dụng công thức để xác định ô nào cần định dạng .
- Nhập công thức sau:
- Nhấp vào Định dạng>> chọn màu tô màu đỏ nhạt.
- Nhấp vào OK .
Thêm quy tắc khác:
Lặp lại cho mỗi quý nếu cần.
Hiệp 2:
- Nhập công thức sau:
- Nhấp vào Định dạng>> chọn màu tô màu xanh nhạt.
- Nhấp vào OK .
Quý 3:
- Nhập công thức sau:
- Nhấp vào Định dạng>> chọn màu tô màu xanh nhạt.
- Nhấp vào OK .
Quý 4:
- Nhập công thức sau:
- Nhấp vào Định dạng>> chọn màu tô màu tím nhạt.
- Nhấp vào OK .
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 Ẩn .
Dữ liệu của bạn sẽ hiển thị định dạng có điều kiện dựa trên các giá trị sổ làm việc bên ngoài, nhưng Excel sử dụng các cột trợ giúp nội bộ để tránh giới hạn tham chiếu bên ngoài.
Phương pháp 2:Sử dụng 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 bằng Power Query
- Mở sổ làm việc “Doanh số thực tế.xlsx”.
- Đi tới Dữ liệu tab>> chọn Nhận dữ liệu>> chọn Từ tệp>> chọn Từ sổ làm việc .
- Duyệt qua để chọn tệp “Sales Target.xlsx”.
- Chọn bảng “Mục tiêu hàng quý”.
- Nhấp vào Nhập .
- Trong Bộ điều hướng cửa sổ>> chọn bảng dữ liệu.
- Nhấp vào Chuyển đổi dữ liệu .
- Trong Trình soạn thảo Power Query:
- Đổi tên các cột để phù hợp với nhu cầu của bạn (Target_Q1, Target_Q2, v.v.).
- Truy cập Trang chủ tab>> Đóng và tải vào .
- Chọn Bảng>> chọn Bảng tính mới .
- Nhấp vào OK .
Bước 2:Áp dụng định dạng có điều kiện
Bây giờ hãy sử dụng định dạng có điều kiện 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).
- Truy cập Trang chủ tab>> chọn Định dạng có điều kiện>> chọn Quy tắc mới .
- Chọn Sử dụng công thức để xác định ô nào cần định dạng .
- Nhập công thức sau:
=B2 <Quarterly_Targets!$B2
- Nhấp vào Định dạng>> chọn màu tô màu đỏ nhạt.
- Nhấp vào OK .
- Thêm các quy tắc khác cho các quý còn lại.
Hiệp 2:
=C2 <Quarterly_Targets!$C2
Quý 3:
=D2 <Quarterly_Targets!$D2
Quý 4:
=E2 <Quarterly_Targets!$E2
- Làm mới Power Query bất kỳ lúc nào mục tiêu thay đổi.
- Nhấp chuột phải>> chọn Làm mới .
- Bạn có thể lên lịch làm mới tự động nếu dữ liệu của bạn thay đổi thường xuyên.
- Đi tới Dữ liệu tab>> chọn Truy vấn và kết nối .
- Nhấp chuột phải vào Truy vấn >> chọn Thuộc tính .
- Trong Làm mới mọi>> chèn 5 phút.
- Nhấp vào OK .
Phương pháp này tự động làm mới dữ liệu ngoài và tránh các giới hạn tham chiếu.
Phương pháp 3:Macro VBA để tự động hóa hoàn toàn
Nếu bạn cảm thấy thoải mái với VBA, bạn có thể tạo macro cập nhật định dạng có điều kiện dựa trên dữ liệu bên ngoài. Nó sẽ so sánh thực tế và mục tiêu, tự động áp dụng định dạng, ngay cả khi tệp tham chiếu bị đóng.
Để mở Trình soạn thảo VBA:
- Mở sổ làm việc Bán hàng thực tế của bạn.
- Đi tới Nhà phát triển tab>> chọn Visual Basic . Hoặc nhấn Alt + F11 .
- Trong cửa sổ Dự án, nhấp chuột phải vào sổ làm việc của bạn,
- Chọn Chèn >> chọn Mô-đun .
- 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
- Cập nhật đường dẫn tệp bằng đường dẫn đầy đủ tới tệp Mục tiêu bán hàng của bạn.
- Macro mở sổ làm việc đích.
- Lặp lại 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, ô sẽ được đánh dấu màu đỏ nhạt.
- Macro tự động đóng sổ làm việc mục tiêu.
Lưu và chạy:
- Lưu sổ làm việc của bạn dưới dạng tệp hỗ trợ macro (.xlsm).
- Đi tới Nhà phát triển tab>> chọn Macro .
- Chọn HighlightSalesBelowTarget>> nhấp vào Chạy .
Đầu ra:
Điều gì KHÔNG hoạt động:Tham chiếu trực tiếp bên ngoài &Phạm vi được đặt tên
Một số phiên bản Excel hiển thị cảnh báo “Bạn không được sử dụng tham chiếu đến các sổ làm việc khác cho tiêu chí Định dạng có điều kiện”.
- Tham chiếu trực tiếp tới sổ làm việc bên ngoài (ví dụ:=[Sales_Targets.xlsx]Quarterly_Targets!B2) không được phép trong các quy tắc định dạng có điều kiện. Excel sẽ báo lỗi.
- Dải ô được đặt tên được xác định trong sổ làm việc bên ngoài không thể được tham chiếu theo định dạng có điều kiện của sổ làm việc khác.
- Ngay cả việc sử dụng INDIRECT hoặc các hàm tương tự cũng sẽ không hoạt động trên các tệp trong ngữ cảnh này.
Không có cách trực tiếp, nguyên bản nào để sử dụng các giá trị bên ngoài trong quy tắc định dạng có điều kiện.
Đề xuất
- Đối với hầu hết các 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 sổ làm việc.
- Đối với kiểm tra đặc biệt hoặc kiểm tra nhanh: Sử dụng các cột trợ giúp có tham chiếu bên ngoài nếu bạn không ngại mở cả hai tệp.
- Đối với các giải pháp tự động, liên tục: Sử dụng VBA để tự động hóa và định dạng, đặc biệt đối với các tập dữ liệu lớn hơn.
Kết luận
Định dạng có điều kiện bên ngoài là một tính năng mạnh mẽ cho phép trực quan hóa dữ liệu động, đa tệp. 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 kỹ lưỡng thiết lập của bạn và duy trì tài liệu rõ ràng về các phần phụ thuộc bên ngoài để tham khảo và cộng tác với các thành viên trong nhóm trong tương lai.
Nguyên bản không thể kích hoạt định dạng có điều kiện dựa trên các giá trị từ sổ làm việc bên ngoài trong Excel.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!