Trong video này, chúng ta sẽ xem xét cách làm nổi bật các tra cứu đối sánh gần đúng với định dạng có điều kiện.
Ở đây chúng tôi có một bảng tra cứu đơn giản cho thấy chi phí vật liệu cho các chiều cao và chiều rộng khác nhau. Công thức trong K8 sử dụng hàm INDEX và MATCH để truy xuất chi phí chính xác dựa trên các giá trị chiều rộng và chiều cao đã nhập trong K6 và K7.
Lưu ý rằng việc tra cứu dựa trên kết quả so khớp gần đúng. Vì các giá trị theo thứ tự tăng dần, MATCH kiểm tra các giá trị cho đến khi đạt đến giá trị lớn hơn, sau đó lùi lại và trả về vị trí trước đó.
Hãy xây dựng quy tắc định dạng có điều kiện để làm nổi bật hàng và cột phù hợp.
Như mọi khi với định dạng có điều kiện phức tạp hơn, tôi khuyên bạn nên làm việc với các công thức giả trước tiên, sau đó chuyển trực tiếp một công thức đang hoạt động sang quy tắc định dạng có điều kiện. Bằng cách này, bạn có thể sử dụng tất cả các công cụ của Excel khi gỡ lỗi công thức, điều này sẽ giúp bạn tiết kiệm rất nhiều thời gian.
Đầu tiên tôi sẽ thiết lập công thức cho chiều rộng. Chúng ta cần trả về TRUE cho mọi ô trong hàng 7, trong đó chiều rộng phù hợp là 200.
Điều này có nghĩa là chúng ta bắt đầu công thức của mình với $ B5 =, và chúng ta cần khóa cột.
= $ B5 =
Bây giờ, chúng ta không thể tìm 275 trong cột width, bởi vì nó không có ở đó. Thay vào đó, chúng tôi cần một kết quả gần đúng tìm thấy 200, giống như công thức tra cứu của chúng tôi.
Cách dễ nhất là sử dụng hàm LOOKUP. LOOKUP tự động thực hiện so khớp gần đúng và thay vì trả về một vị trí như MATCH, LOOKUP trả về giá trị so khớp thực tế. Vì vậy, chúng ta có thể viết:
$ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12)
Với chiều rộng đầu vào của chúng tôi cho giá trị tra cứu và tất cả các chiều rộng trong bảng cho vectơ kết quả.
Nếu tôi sử dụng F9, bạn có thể thấy giá trị LOOKUP trả về.
Bây giờ khi tôi nhập công thức trên bảng, chúng tôi nhận được TRUE cho mỗi ô trong hàng 200 chiều rộng.
Bây giờ chúng ta cần mở rộng công thức để phù hợp với cột chiều cao. Để làm điều này, tôi sẽ thêm hàm OR và sau đó là công thức thứ hai để khớp với chiều cao.
Chúng ta sẽ bắt đầu công thức theo cùng một cách, nhưng lần này chúng ta cần khóa hàng:
= B $ 5
Sau đó, chúng tôi sử dụng lại hàm LOOKUP với chiều cao cho giá trị tra cứu và tất cả các chiều cao trong bảng là vectơ kết quả.
= HOẶC ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Khi tôi sao chép công thức trên toàn bộ bảng, chúng tôi nhận được ĐÚNG cho mọi ô trong cột phù hợp và mọi ô trong hàng phù hợp - chỉ những gì chúng tôi cần cho định dạng có điều kiện.
Tôi chỉ có thể sao chép chính xác công thức ở ô phía trên bên trái và tạo quy tắc mới.
Bây giờ nếu tôi thay đổi chiều rộng hoặc chiều cao, phần tô sáng sẽ hoạt động như mong đợi.
Cuối cùng, nếu bạn chỉ muốn làm nổi bật giá trị tra cứu, đó là một thay đổi đơn giản. Chỉ cần chỉnh sửa công thức và thay thế hàm OR bằng hàm AND.
= VÀ ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Khóa học
Định dạng có điều kiệnCác phím tắt liên quan
Nhập cùng một dữ liệu vào nhiều ô Ctrl + Enter ⌃ + Return Hiển thị hộp thoại Dán Đặc biệt Ctrl + Alt + V ⌘ + ⌃ + V Chuyển đổi tham chiếu tuyệt đối và tương đối F4 ⌘ + T
