Sử dụng so sánh có điều kiện để các giá trị từ hàng này sang hàng khác

Quy tắc định dạng từng hàng dễ áp ​​dụng như quy tắc từng cột trong Microsoft Excel, nhưng bạn có thể không nhận được kết quả như mong đợi.

Windows: Phạm vi phải đọc

Định dạng có điều kiện trong Microsoft Excel đã có từ lâu, nhưng tôi thấy rằng hầu hết các quy tắc đều đánh giá các cột; nó hiếm khi đơn giản như vậy, nhưng công bằng mà nói rằng hầu hết các quy tắc có điều kiện đều so sánh các giá trị từ cột này sang cột khác. Nó vốn có trong cách chúng ta cấu trúc dữ liệu. Tuy nhiên, đôi khi chúng ta cần so sánh các giá trị từ hàng này sang hàng khác. Tin tốt là việc triển khai quy tắc định dạng so sánh hàng không khó hơn so với so sánh các cột. Trong bài viết này, chúng tôi sẽ sử dụng quy tắc để so sánh các giá trị từ hàng này sang hàng khác. Nhưng mấu chốt là, bất cứ khi nào bạn so sánh giá trị này với giá trị khác, cho dù theo cột hay hàng, bạn có thể không nhận được kết quả như mong đợi.

Tôi đang sử dụng Microsoft 365 trên hệ thống Windows 10 64-bit. (Cố gắng ngừng nâng cấp lên Windows 11 cho đến khi xử lý xong tất cả các lỗi.) Excel Online sẽ hiển thị các định dạng có điều kiện hiện có và cho phép bạn thêm các quy tắc cài sẵn. Tuy nhiên, bạn không thể áp dụng các biểu thức làm quy tắc. Để thuận tiện cho bạn, bạn có thể tải xuống các tệp .xlsx và .xls trình diễn.

Yêu cầu về định dạng

Hãy giữ điều này đơn giản nhất có thể cho bây giờ. Giả sử bạn muốn biết khi nào hoa hồng có vẻ thấp hơn bình thường một chút. Hình A hiển thị một bảng theo dõi đơn giản về doanh số và tiền hoa hồng cho năm nhân viên bằng cách sử dụng đối tượng Bảng, mặc dù, bạn có thể làm như vậy bằng cách sử dụng một phạm vi dữ liệu bình thường. Giả sử bạn muốn biết liệu một tỷ lệ phần trăm có thấp hơn dự kiến ​​hay không khi so sánh với hoa hồng ở hàng trên. Bây giờ, điều này sẽ không thực sự cung cấp cho chúng tôi phân tích mà chúng tôi thực sự cần trong tình huống này, nhưng nó cung cấp cho chúng tôi một chút ví dụ giả định để so sánh các giá trị từ hàng này sang hàng khác. Chúng tôi sẽ giải quyết lỗ hổng hợp lý sau.

Hình A

Để thực hiện yêu cầu đơn giản này, hãy làm nổi bật số tiền hoa hồng thấp hơn 25% so với hàng ở trên:

1. Chọn tập dữ liệu — trong trường hợp này là B4: E14. Nếu bạn chỉ muốn đánh dấu ô hoa hồng, chỉ chọn cột giá trị đó. Nhưng đối với ví dụ này, tôi muốn làm nổi bật toàn bộ bản ghi.
2. Bấm vào tab Trang đầu, bấm Định dạng có Điều kiện trong nhóm Kiểu, rồi chọn Quy tắc Mới từ danh sách thả xuống.
3. Trong ngăn trên cùng, chọn Sử dụng công thức để xác định ô cần định dạng.
4. Trong ngăn dưới cùng, nhập biểu thức =$E4<($E5 - ($E5 *0.25)). Dấu chấm sau các ký tự)) là dấu chấm câu và không phải là một phần của biểu thức.
5. Bấm vào nút Định dạng, bấm vào tab Tô, sau đó chọn một màu đánh dấu. Tôi chọn màu đỏ vì chúng tôi đang tìm kiếm các giá trị thấp hơn.
6. Bấm OK để quay lại ngăn đầu tiên, ngăn này hiển thị quy tắc và định dạng (Hình B).
7. Bấm OK để áp dụng định dạng và quay lại trang tính, được hiển thị trong Hình C. Bây giờ, đừng lo lắng về các ô ở hàng 1. Chúng tôi sẽ sử dụng chúng một chút.

Hình B

Hình C

Với một đánh giá nhanh, tất cả đều có vẻ tốt, ngoại trừ giá trị trong E11. Khi so sánh với các khoản hoa hồng khác, nó có vẻ phù hợp. Vấn đề là giá trị mà nó được so sánh với: $ 500 trong E12. Quy tắc đang hoạt động như bình thường. Hãy nhớ rằng, chúng tôi không so sánh toàn bộ tập hợp các giá trị hoa hồng nói chung. Chúng tôi đang so sánh chúng từ hàng này sang hàng khác. Đúng, nó không hoạt động tốt như chúng ta có thể thích vì nó hơi có chủ đích, vì vậy bạn có thể thấy dễ dàng như thế nào khi nghĩ rằng bạn đã hiểu đúng logic, chỉ để tìm ra một sai sót.

Điều quan trọng cần lưu ý là tham chiếu tuyệt đối cho cột E là điều cần thiết nếu bạn muốn định dạng toàn bộ ô. Tương tự, hàng phải tương đối.

Tại thời điểm này, bạn có thể thắc mắc về các ô được định dạng trong hàng 1. C1 là ô đầu vào. Bằng cách tham chiếu tỷ lệ phần trăm theo cách này, bạn có thể thay đổi tỷ lệ phần trăm một cách nhanh chóng mà không cần sửa đổi quy tắc thực tế.

Cách sửa đổi quy tắc

Bằng cách thêm một ô đầu vào, bạn có thể nhanh chóng cập nhật tỷ lệ phần trăm được sử dụng trong quy tắc có điều kiện. Hãy làm điều đó ngay bây giờ:

1. Bấm vào tab Trang đầu, bấm Định dạng có Điều kiện trong nhóm Kiểu, sau đó chọn Quản lý Quy tắc từ danh sách thả xuống.
2. Từ trình đơn thả xuống Hiển thị Quy tắc Định dạng, hãy chọn Trang tính này.
3. Trong ngăn kết quả, chọn quy tắc và chọn Chỉnh sửa quy tắc trong hàng menu ở trên.
4. Nhấp vào bên trong quy tắc và nhấn F2 để bạn có thể chỉnh sửa dễ dàng.
5. Thay thế giá trị 0,25 bằng một tham chiếu tuyệt đối đến $ C $ 1 (Hình dung).
6. Bấm OK hai lần để áp dụng và quay lại trang tính.

Hình dung

Quy tắc sẽ không hoạt động chính xác cho đến khi bạn nhập giá trị đầu vào vào C1, vì vậy hãy nhập .25 ngay bây giờ. Quy tắc làm nổi bật các bản ghi giống như trước đây. Thay đổi .25 thành .10 để bạn có thể thấy nó cập nhật. Như bạn có thể thấy trong Hình Equy tắc bao gồm một bản ghi nữa, hàng 4. Khi bạn thấp hơn phần trăm, bạn có thể tăng số lượng bản ghi được đánh dấu và ngược lại. Nếu bạn muốn đánh dấu các bản ghi vượt quá bản ghi bên dưới theo một tỷ lệ phần trăm cụ thể, chỉ cần thay đổi < sign in the rule to >. Trên thực tế, bạn có thể sử dụng cả hai quy tắc, nhưng bạn có thể chọn một màu khác, chẳng hạn như màu xanh lá cây, để làm nổi bật mức hoa hồng cao hơn bình thường.

Hình E

Làm thế nào để sửa lỗi logic

Như vậy, quy tắc không hoạt động theo cách chúng ta có thể muốn vì chúng ta đang so sánh hàng này với hàng khác và bỏ qua toàn bộ — ngược lại, Trung bình cộng Uỷ ban. Tôi khuyên bạn nên sử dụng tùy chọn Quản lý quy tắc để xóa quy tắc hiện tại trước khi tiếp tục. Nó không hoàn toàn cần thiết, nhưng sẽ dễ dàng hơn để xem các kết quả mới.

Điều đầu tiên chúng ta phải xem xét là làm thế nào để bao gồm hoa hồng trung bình một cách nhanh chóng và dễ dàng trong quy tắc mới của chúng tôi. Chúng tôi có thể thêm nó vào quy tắc trực tiếp, nhưng chúng tôi sẽ kết thúc với một quy tắc phức tạp. Cách dễ dàng hơn là nhập hàm tính trung bình trong C2

=AVERAGE(Table12[Commission])

Nếu bạn không sử dụng đối tượng Table, hãy nhập

=AVERAGE(E4:E14)

Bây giờ, hãy sửa lỗi logic của chúng ta bằng cách sử dụng một quy tắc mới:

1. Chọn tập dữ liệu và sử dụng Định dạng có điều kiện để thêm quy tắc mới, như bạn đã làm trước đó.
2. Nhập biểu thức =$E4<($C$2-($C$2*$C$1)). Hãy nhớ rằng dấu chấm không được bao gồm trong biểu thức và việc tham chiếu tuyệt đối / tương đối là quan trọng.
3. Nhấp vào nút Định dạng, áp dụng màu tô (tôi đã chọn màu vàng) và nhấp vào OK hai lần để quay lại trang tính.

Như bạn có thể thấy trong Hình F, quy tắc mới này chỉ làm nổi bật hai bản ghi. Logic mới chính xác hơn với nhu cầu của chúng ta. Nhưng chúng tôi đã loại bỏ hoàn toàn tham chiếu từng hàng khi làm như vậy, nhưng điều này vẫn thường xảy ra. Đó là lý do tại sao tôi bắt đầu với một ví dụ giả tạo như vậy. Điều này có thể dễ dàng xảy ra khi so sánh cột với cột.

Hình F

Như trước đây, bạn có thể thay đổi tỷ lệ phần trăm trong C1 để cập nhật toàn bộ chế độ xem. Ngoài ra, nếu bạn muốn thấy hoa hồng vượt quá mức trung bình theo tỷ lệ phần trăm cụ thể (C1), hãy thay đổi < in the rule to >. Cuối cùng, vì chúng ta đang sử dụng một đối tượng Table, nên tất cả đều là động. Hàm AVERAGE () sẽ tự động cập nhật khi bạn sửa đổi, thêm hoặc xóa các bản ghi. Nếu bạn đang sử dụng phạm vi dữ liệu bình thường, điều đó sẽ không xảy ra.

Bạn có thể tự hỏi tại sao tôi bắt đầu với một ví dụ từng hàng chỉ để loại bỏ hoàn toàn nó. Đầu tiên, tôi muốn có một ví dụ siêu dễ để giới thiệu ý tưởng đánh giá quy tắc định dạng theo hàng. Như tôi đã đề cập hai lần, việc tham khảo là rất quan trọng. Ngoài ra, đối với những quy tắc đơn giản nhất, đôi khi chúng không đầy đủ và bạn cần phải suy nghĩ lại về nỗ lực đầu tiên của mình. Không có gì sai với điều đó; Tôi làm nó suốt.