Trong trường hợp bạn muốn tập trung vào các giá trị N trên cùng hoặc dưới cùng trong tập dữ liệu, cách tốt nhất là đánh dấu chúng bằng các màu khác nhau. Bài viết này sẽ hướng dẫn bạn cách thực hiện việc này với các cài đặt trước của Excel và thiết lập quy tắc định dạng có điều kiện của riêng bạn dựa trên công thức.
Đánh dấu các ô với Định dạng có điều kiện trong Excel nghe có vẻ như một nhiệm vụ tầm thường. Nhưng không phải nếu bạn nghĩ ra có bao nhiêu cách bạn thực sự có thể làm được. Bạn có muốn tô bóng các số cao nhất hoặc thấp nhất trong một phạm vi, cột hoặc hàng không? Hoặc có thể bạn muốn hiển thị 3 giá trị hàng đầu trong mỗi hàng? Và sẽ thực sự tuyệt vời nếu bạn có thể kiểm soát số lượng ô được đánh dấu trực tiếp trên trang tính mà không cần phải truy cập trình quản lý Quy tắc định dạng có điều kiện mỗi lần. Hướng dẫn này chỉ ra cách thực hiện tất cả những điều này và hơn thế nữa!
Đánh dấu giá trị N trên cùng hoặc dưới cùng trong phạm vi
Trong Microsoft Excel, có một số cách để đánh dấu các giá trị cao nhất và thấp nhất với định dạng có điều kiện. Dưới đây, chúng tôi cung cấp tổng quan về 3 phương pháp khác nhau, vì vậy bạn có thể chọn một phương pháp phù hợp nhất với mình.
Đánh dấu các giá trị trên cùng và dưới cùng với quy tắc tích hợp
Cách nhanh nhất để đánh dấu các giá trị 3, 5, 10 (hoặc n dưới cùng) trên cùng trong Excel là sử dụng quy tắc định dạng có điều kiện có sẵn. Đây là cách thực hiện:
- Chọn phạm vi mà bạn muốn đánh dấu các số.
- Trên Trang Chủ tab, trong Phong cách nhóm, bấm Định dạng có điều kiện.
- Trong menu thả xuống, hãy chọn Quy tắc Trên cùng / Dưới cùng, và sau đó nhấp vào 10 mục hàng đầu… hoặc là 10 mục dưới cùng…
- Trong hộp thoại mở ra, chỉ định số lượng mục để đánh dấu và chọn một trong các tùy chọn định dạng được xác định trước. Để áp dụng định dạng không có trong danh sách, hãy nhấp vào Định dạng tùy chỉnh… . Những thay đổi bạn thực hiện ở đây được phản ánh trong tập dữ liệu của bạn ngay lập tức. Vì vậy, nếu bạn hài lòng với kết quả, hãy nhấp vào đồng ý.
Đối với ví dụ này, chúng tôi chọn làm nổi bật 3 giá trị hàng đầu với mặc định Tô màu đỏ nhạt và nhận được kết quả này:
Nhiều tùy chọn định dạng hơn để hiển thị giá trị cao nhất và thấp nhất
Nếu bạn muốn có nhiều tùy chọn hơn được cung cấp trong Trên / Dưới cài đặt trước, bạn có thể tạo quy tắc mới từ đầu:
- Chọn một dải ô có các giá trị số.
- Trên Trang Chủ tab, nhấp vào Định dạng có điều kiện > Quy tắc mới.
- bên trong Quy tắc định dạng mới hộp thoại, chọn Chỉ định dạng các giá trị được xếp hạng trên cùng hoặc dưới cùng.
- Trong danh sách thả xuống, hãy chọn Hàng đầu hoặc là Dưới cùngvà nhập bao nhiêu giá trị để đánh dấu vào hộp bên cạnh nó.
- Nhấn vào định dạng và chọn bất kỳ định dạng nào bạn muốn Nét chữ, Biên giới và Lấp đầy.
- Nhấp chuột đồng ý.
Ví dụ: đây là cách bạn có thể đánh dấu 5 giá trị hàng đầu bằng màu nền xanh lục.
Đánh dấu các giá trị trên cùng hoặc dưới cùng bằng công thức
Các quy tắc tích hợp được mô tả ở trên rất tiện dụng và dễ áp dụng. Tuy nhiên, chúng có một nhược điểm đáng kể – bất cứ khi nào bạn muốn hiển thị một số giá trị khác, bạn sẽ phải thay đổi số trong Trình quản lý quy tắc định dạng có điều kiện.
Để làm cho quy tắc linh hoạt hơn, bạn có thể dựa vào công thức. Trong trường hợp của chúng tôi, chúng tôi sẽ sử dụng:
Large chức năng đánh bóng các số hàng đầu:
=upper_left_cell> = LARGE (phạm vi, n)
Small chức năng đánh bóng các số dưới cùng:
=upper_left_cell<= NHỎ (phạm vi, n)
Để tô màu các giá trị cao nhất hoặc thấp nhất bằng cách sử dụng quy tắc dựa trên công thức, vui lòng làm theo các bước sau:
- Nhập số lượng giá trị để đánh dấu vào ô đầu vào được xác định trước. Đối với ví dụ này, chúng tôi nhập số giá trị trên cùng trong F2 và số giá trị dưới cùng trong F3.
- Chọn dãy số (A2: C8).
- Trên Trang Chủ tab, nhấp vào Định dạng có điều kiện > Quy tắc mới.
- bên trong Quy tắc định dạng mới hộp thoại, chọn Sử dụng công thức để xác định ô cần định dạng.
- bên trong Định dạng các giá trị trong đó công thức này đúng , nhập một trong các công thức sau:
Để đánh dấu n giá trị hàng đầu:=A2>=LARGE($A$2:$C$8, $F$2)Để đánh dấu n giá trị dưới cùng:
=A2<=SMALL($A$2:$C$8, $F$3)Trong đó $ A $ 2: $ C $ 8 là phạm vi được áp dụng, A2 là ô ngoài cùng bên trái của phạm vi; F2 và F3 là giá trị thứ n.
Vui lòng lưu ý rằng chúng tôi khóa dải ô nguồn và ô đầu vào bằng tuyệt đối tài liệu tham khảo và sử dụng quan hệ tham chiếu cho ô phía trên bên trái.
- Nhấn vào định dạng và chọn định dạng mong muốn.
- Nhấp chuột đồng ý hai lần để đóng cả hai cửa sổ.
Để được hướng dẫn chi tiết hơn, vui lòng xem Tạo quy tắc định dạng có điều kiện với công thức.
Làm xong! 3 giá trị trên cùng và 3 giá trị dưới cùng được đánh dấu bằng các màu khác nhau.
Sau đó, nếu bạn muốn đánh dấu, chẳng hạn như 5 giá trị hàng đầu, bạn chỉ cần nhập 5 vào F2 và Excel sẽ tự động áp dụng thay đổi.
Công thức này hoạt động như thế nào:
Hàm LARGE trả về giá trị lớn nhất thứ n trong phạm vi được chỉ định. Trong trường hợp của chúng tôi, nó tìm thấy 3rd giá trị lớn nhất trong A2: C8, là 92. Công thức so sánh mỗi số trong phạm vi đã chọn với 3rd giá trị cao nhất. Đối với bất kỳ ô nào lớn hơn hoặc bằng 92, nó trả về TRUE và quy tắc định dạng có điều kiện được áp dụng.
Cách tô bóng các hàng chứa giá trị N trên cùng hoặc dưới cùng
Khi phân tích dữ liệu có cấu trúc, thường có thể hữu ích khi xác định toàn bộ hàng có chứa giá trị n trên cùng hoặc dưới cùng trong cột khóa. Đối với điều này, bạn có thể định cấu hình quy tắc tạo có điều kiện dựa trên công thức như được giải thích trong ví dụ trước, nhưng các công thức có một chút khác biệt.
Để hiển thị n hàng trên cùng:
=$B2>=LARGE($B$2:$B$15, $E$2)
Để hiển thị n hàng dưới cùng:
=$B2<=SMALL($B$2:$B$15, $E$3)
Ở đâu:
- $ B2 là ô trên cùng của cột số.
- $ B $ 2: $ B $ 15 là các số để xếp hạng.
- $ E $ 2 là số hàng trên cùng cần đánh dấu.
- $ E $ 3 là số hàng dưới cùng cần tô sáng.
Các quy tắc được áp dụng cho toàn bộ bảng ngoại trừ hàng tiêu đề (A2: B15).
Giống như trong ví dụ trước, các quy tắc này rất linh hoạt. Các công thức sẽ tự động tính toán lại ngay sau khi bạn thay đổi số trong các ô nhập (E2 và E3), và Excel sẽ ngay lập tức phản ánh những thay đổi đó và đánh dấu một số hàng tương ứng.
Cách làm nổi bật N giá trị hàng đầu trong mỗi hàng
Trong trường hợp tập dữ liệu của bạn chứa nhiều cột số, bạn có thể muốn hiển thị giá trị cao nhất hoặc thấp nhất trong mỗi hàng. Để hoàn tất, bạn lại tạo một quy tắc lập công thức có điều kiện trong Excel bằng công thức.
Ví dụ: để đánh dấu 3 số hàng đầu trong mỗi hàng trong bảng dưới đây, công thức là:
=B2>=LARGE($B2:$G2, 3)
Các bước để tạo quy tắc được mô tả trong ví dụ này, vì vậy ở đây chúng tôi chỉ hiển thị kết quả. Quy tắc được áp dụng cho tất cả các ô số (B2: G2):
Để hiển thị 3 hàng dưới cùng, công thức sẽ là:
=B2<=SMALL($B2:$G2, 3)
Công thức này hoạt động như thế nào:
Như bạn có thể nhận thấy, công thức này rất giống với công thức được sử dụng trong các ví dụ trước, ngoại trừ tham chiếu ô và phạm vi. Và thay đổi nhỏ đó tạo ra sự khác biệt lớn!
Trong định dạng có điều kiện của Excel, các tham chiếu có liên quan đến ô phía trên bên trái của phạm vi áp dụng quy tắc. Vì vậy, chúng ta có thể giả sử như thể chúng ta đang viết công thức cho ô ngoài cùng bên trái và Excel đang “sao chép” nó qua tất cả các ô khác trong phạm vi đã chọn.
Trong ví dụ này, quy tắc được áp dụng cho tất cả các ô số (B2: G10), nhưng công thức được viết cho hàng 2:
=B2>=LARGE($B2:$G2, 3)
Hàm LARGE tìm thấy 3rd giá trị lớn nhất trong B2: G2, là 257. Công thức kiểm tra xem B2 lớn hơn hoặc bằng 275 và nếu TRUE, áp dụng định dạng có điều kiện cho ô đó. Bởi vì B2 là một tham chiếu tương đối, Excel sau đó hầu như “sao chép” công thức sang C2, D2, v.v. Vì tọa độ cột bị khóa bằng dấu $ ($ B2: $ G2), phạm vi không thay đổi khi so sánh các ô khác trong cùng một hàng.
Ví dụ: đối với C2, Excel sẽ đánh giá công thức này:
=C2>=LARGE($B2:$G2, 3)
Đối với hàng 3, công thức tự động thay đổi như được hiển thị bên dưới vì tất cả các tọa độ hàng là tương đối:
=B3>=LARGE($B3:$G3, 3)
Và như thế.
Hướng dẫn sau có thể giúp bạn hiểu rõ hơn về cơ học bên trong: Tham chiếu ô tương đối và tuyệt đối trong định dạng có điều kiện.
Đó là cách làm nổi bật các giá trị trên cùng và dưới cùng trong Excel bằng cách sử dụng định dạng có điều kiện. Tôi cảm ơn bạn đã đọc và mong được gặp bạn trên blog của chúng tôi vào tuần tới!
The post Cách tô sáng các giá trị 3, 5, 10 hàng đầu trong Excel appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.
source https://ketoanmvb.com/cach-to-sang-cac-gia-tri-3-5-10-hang-dau-trong-excel.html
Không có nhận xét nào:
Đăng nhận xét