Đánh dấu hàng với 3 giá trị dưới cùng với một tiêu chí
Chúng tôi đánh dấu các ô trong excel để chúng tôi có thể dễ dàng xác định các vùng trọng tâm trên dữ liệu. Định dạng các ô trong excel cũng được thực hiện để hiển thị các giá trị dữ liệu dựa trên màu sắc và kiểu dáng. Ví dụ: bạn có thể muốn tập trung vào 3 giá trị nhỏ nhất trong dữ liệu của mình. Thật khó để thực hiện trong một dữ liệu lớn nhưng nếu bạn có thể yêu cầu excel đánh dấu 3 giá trị thấp nhất thì nó sẽ khá dễ dàng. May mắn thay, chúng ta có thể làm điều đó bằng cách sử dụng tùy chọn định dạng có điều kiện trong Excel.
Thật dễ dàng để chỉ cần đánh dấu 3 giá trị nhỏ nhất trong excel nhưng sẽ hơi phức tạp khi chúng ta thêm tiêu chí. Trong bài này, chúng ta sẽ học cách làm nổi bật 3 giá trị nhỏ nhất với một điều kiện.
Công thức chung trong định dạng có điều kiện
=AND(check cell=criteria, value cell<=SMALL(IF(criteria range=criteria,value range),3))
Kiểm tra ô: Đây là ô mà bạn muốn kiểm tra xem nó có xác định được tiêu chí đầu tiên (danh mục) hay không.
Tiêu chí: Tiêu chí mà bạn muốn lọc.
Ô giá trị: Là ô chứa giá trị số. Chúng tôi sẽ so sánh nó với giá trị nhỏ thứ 3 trong danh mục của nó, trong phạm vi giá trị. Phạm vi tiêu chí: Là phạm vi tuyệt đối chứa tiêu chí.
Phạm vi giá trị: Phạm vi chứa tất cả các giá trị.
Hãy xem một ví dụ để làm rõ mọi thứ.
Ví dụ: Đánh dấu ba lần bán hàng dưới cùng do Cục 1 thực hiện
Ở đây tôi có một tệp công việc mẫu chứa doanh số bán hàng được thực hiện bởi các bộ phận khác nhau trong các tháng khác nhau.
Tôi muốn làm nổi bật ba doanh số bán hàng dưới cùng do Cục 1.
Hãy xác định các biến chúng ta có ở đây.
Kiểm tra ô: $ B2 (* chúng tôi muốn kiểm tra xem ô B2 có chứa “đại lý 1” hay không.
Vì chúng tôi không muốn cột thay đổi, chúng tôi sử dụng dấu $. Điều này làm cho toàn bộ hàng được đánh dấu.)
Tiêu chí: “Khoa 1”
Ô giá trị: $ C2 Phạm vi tiêu chí: $ B $ 2: $ B $ 12 Phạm vi giá trị: $ C $ 2: $ C $ 12 Bây giờ chúng ta đã biết tất cả các đối số mà chúng ta cần chuyển vào công thức, đã đến lúc triển khai công thức chung trên trên dữ liệu bộ.
Chọn dữ liệu. Đi về nhà ? định dạng có điều kiện? Quy tắc mới Chọn tùy chọn “Sử dụng công thức để xác định ô cần định dạng”.
Trong hộp văn bản bên dưới, hãy viết công thức này Chọn định dạng từ việc sử dụng nút định dạng. Tôi đã sử dụng màu cam (có thể, đừng đánh giá giới tính của tôi) điền vào.
Nhấn nút OK. Và nó đã hoàn thành. Ba dưới cùng hoặc nói 3 doanh thu nhỏ nhất từ bộ phận 1 được đánh dấu.
Nó hoạt động như thế nào?
Hãy chia nhỏ công thức từ bên trong.
Công thức này là một công thức mảng nhưng CTRL + SHIFT + ENTER không cần thiết trong định dạng có điều kiện.
_ $ B2 = “Đại lý 1” _
Tiêu chí đầu tiên là dễ kiểm tra. Chúng tôi chỉ đơn giản đặt câu lệnh này $ B2 = “Đại lý 1”. Sẽ là TRUE nếu ô B2 hiện tại chứa
Đại lý 1.
Khi chúng ta đánh giá nó, công thức này sẽ giải quyết theo cách này:
Chúng tôi kiểm tra phạm vi $ B $ 2: $ B $ 12 và nhận được các ô có chứa “Đại lý 1”
sử dụng câu lệnh $ B $ 2: $ B $ 12 = “Đại lý 1”. Chúng ta sẽ có một mảng TRUE và FALSE.
$C2<=SMALL(IF({TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE},$C$2:$C$12),3)
Đối với mỗi TRUE, giá trị sẽ được trả về từ dải giá trị $ C $ 2: $ C $ 12 và chúng ta sẽ có một mảng khác.
$C2<=SMALL({12;FALSE;25;FALSE;FALSE;FALSE;FALSE;18;FALSE;FALSE;14},3)
Bây giờ chúng ta có:
$C2<=18
Cuối cùng, công thức là
=AND($B2="Dealership 1",$C2<=18)
Bây giờ nó kiểm tra Nếu B2 có chứa Dealership 1. Nó có. Do đó nó là SỰ THẬT
=AND(TRUE,$C2<=18)
Tiếp theo, nó sẽ kiểm tra xem C2 nhỏ hơn hoặc bằng 18 (doanh số bán hàng nhỏ thứ ba ở đại lý 1). Nó là SỰ THẬT (12).
=AND(TRUE, TRUE)
Vì cả hai câu lệnh đều TRUE nên công thức trả về TRUE và chúng tôi nhận được ROW đầu tiên của mình được đánh dấu bằng màu sắc. Tương tự xảy ra trong mỗi hàng.
Rất tiếc !!! Thật khó để giải thích. Tôi hy vọng tôi đã đủ rõ ràng để làm cho bạn hiểu phương pháp. Trong thực tế, nó là dễ dàng.
Ở đây chúng tôi đã sử dụng tiêu chí mã hóa cứng nhưng chúng tôi cũng có thể cung cấp tham chiếu ô.
Điều này sẽ làm cho công thức linh hoạt hơn.
Ở đây, đối với Tiêu chí, tôi đã sử dụng tham chiếu ô E1. Cho dù bạn có đại lý nào trong E1, ba giá trị nhỏ nhất từ đại lý đó sẽ được đánh dấu.
Công thức ở định dạng có điều kiện là:
=AND($B2=$E$1,$C2<=SMALL(IF($B$2:$B$12=$E$1,$C$2:$C$12),3))
Vì vậy, đây là cách bạn có thể làm nổi bật giá trị ba dưới cùng với một điều kiện trong excel. Tôi hy vọng bài viết là hữu ích. Nếu bạn có bất kỳ câu hỏi nào, vui lòng sử dụng phần bình luận bên dưới. Bạn có thể đặt câu hỏi liên quan đến bài viết này hoặc bất kỳ bài viết nào khác liên quan đến excel. Chúng tôi sẽ rất vui khi được giúp bạn.
Bài viết liên quan:
liên kết: / có điều kiện-định dạng-có điều kiện-định dạng-với-công thức-2016 [Định dạng có điều kiện với công thức 2016]