Bạn muốn làm nổi bật một số ô tính có giá trị quan trọng nhưng ngại việc định dạng thủ công sẽ dễ gây nhầm lẫn và thiếu sót?
Chức năng Conditional Formatting (Định dạng có điều kiện) trong Excel sẽ giúp bạn giải quyết vần đề này. Với những tính năng đơn giản mà hiệu quả, Conditional Formatting trong thực tế rất cần thiết, giúp người sử dụng dễ dàng phân biệt các ô tính quan trọng, nhất là khi phải làm việc với một lượng dữ liệu lớn và cần kiểm tra lại hoặc nhấn mạnh các giá trị quan trọng trong bảng tính.
Ngoài ra, bạn không cần lo lắng định dạng theo điều kiện sẽ làm mất định dạng gốc vì Excel vẫn giữ lại định dạng cũ, chỉ bổ sung quy tắc mới và chép đè định dạng mới lên trên. Khi xóa quy tắc của Conditional Formatting, định dạng cũ của ô tính sẽ được trả lại như ban đầu.
1. Định dạng theo điều kiện có sẵn
Để bắt đầu sử dụng, bạn vào thẻ Home trên thanh Ribbon, nhấn Conditional Formatting trong mục Styles và chọn kiểu điều kiện bạn muốn:
- Highlight Cells Rules - Quy tắc đánh dấu nổi bật ô tính theo giá trị:
Excel sẽ làm nổi bật ô tính thỏa mãn điều kiện bạn chọn dựa trên giá trị từng ô, cụ thể như sau:
- Greater Than: Lớn hơn một giá trị xác định.
- Less Than: Nhỏ hơn giá trị cho trước.
- Between: Nằm trong khoảng giá trị định sẵn.
- Equal To: Bằng đúng giá trị xác định.
- Text That Contains: Nội dung ô tính có chứa phần chữ cho trước.
- A Date Occurring: Giá trị ô tính chứa một ngày tháng định sẵn.
- Duplicate Values: Xác định những giá trị trùng lặp.
- Top / Bottom Rules - Quy tắc xác định ô tính theo xếp hạng:
Excel xác định những giá trị theo thứ hạng dựa trên vùng ô tính chọn xét điều kiện và định dạng riêng các ô tính chứa các giá trị này.
- Top 10 Items: 10 giá trị lớn nhất.
- Top 10%: 10% số lượng ô tính có giá trị lớn nhất.
- Bottom 10 Items: 10 giá trị nhỏ nhất.
- Bottom 10%: 10% số lượng ô tính có giá trị lớn bé nhất.
- Above Average: Những giá trị lớn hơn giá trị trung bình của toàn vùng chọn.
- Below Average: Các giá trị nhỏ hơn giá trị trung bình của toàn vùng chọn.
Bạn có thể tùy chỉnh số lượng giá trị hoặc số phần trăm bất kỳ khác 10 với 4 kiểu định dạng đầu tiên.
- Data Bars - Hiển thị mức độ lớn nhỏ của giá trị:
Với cách định dạng này, các giá trị sẽ được đo lường rõ ràng ngay trong từng ô tính của vùng chọn định dạng điều kiện.
- Color Scales - Phân biệt độ lớn nhỏ của các giá trị bằng màu sắc:
Các giá trị thể hiện mức độ màu khác nhau dựa trên độ lớn nhỏ của từng giá trị trong vùng.
- Icon Sets - Thêm biểu tượng vào ô tính dựa trên giá trị:
Tương tự như Data Bars và Color Scales, giá trị ô tính được phân nhóm theo biểu tượng.
2. Định dạng theo điều kiện riêng:
Ngoài các thao tác định dạng sẵn có, bạn còn có thể tự tạo quy tắc riêng cho định dạng điều kiện của mình. Bạn chỉ cần thực hiện tương tự như định dạng kiểu có sẵn và chọn More Rules ở từng kiểu hoặc New Rule (bên dưới Icon Sets) để tùy chỉnh riêng.
3. Xóa và quản lý Conditional Formatting
Bạn có thể chọn Clear Rules để xóa theo vùng đang chọn (Selected Cells), theo toàn bộ bảng tính (Entire Sheet), theo bảng này (This Table), theo bảng phân tích dữ liệu (This PivotTable).
Trong trường hợp cần quản lý các định dạng có điều kiện thì chọn Manage Rules để dễ dàng thay đổi, xóa và cập nhật các định dạng điều kiện mới.
Trong hình ví dụ trên, ta có thể thấy việc định dạng Duplicate Values giúp xác định lỗi đánh máy ở một trong hai mã hàng BC35. Sau khi chỉnh xong lỗi này, bạn có thể chọn quy tắc Duplicate Values và nhấn Delete Rule. Ngoài ra, giá tiền cũng thế hiện được mức độ cao thấp nhờ Data Bar.
Lưu ý: Khi bạn sử dụng tìm kiếm để tìm kiếm dữ liệu đã lọc, chỉ có dữ liệu được hiển thị sẽ được tìm kiếm; dữ liệu không được hiển thị sẽ không được tìm kiếm. Để tìm kiếm tất cả dữ liệu, hãy xóa tất cả các bộ lọc.
Không chỉ vì sự quen thuộc tới từ phần mềm này mà còn vì cả những tính năng hỗ trợ đắc lực cho công việc mà nó có thể mang tới. Và vẽ biểu đồ là một trong những tính năng mà nhiều bạn vẫn hàng ngày sử dụng nó trong việc làm báo cáo.
Trong bài viết này mình sẽ hướng dẫn các bạn vẽ 1 biểu đồ Highlight các giá trị Top N trên biểu đồ.
Nói đơn giản thì tính năng này tương tự như Conditional Formatting nhưng nó sẽ được sử dụng trên biểu đồ chứ không phải trên bảng tính.
Giả sử chúng ta sẽ có khối dữ liệu như sau.
Giờ ta sẽ vẽ 1 biểu đồ thể hiện doanh số theo từng Department như sau
Sau khi vẽ xong biểu đồ này thì trưởng phòng của mình có yêu cầu thêm là anh ấy muốn highlight các Department có Sales nằm trong Top 3.
Với yêu cầu như vậy thì cách làm đơn giản nhất là mình sẽ chọn các giá trị nằm trong Top 3 rồi đổi màu nó là được. Và đây là kết quả.
Tuy nhiên việc làm như vậy không được Dynamic cho lắm. Bạn có thể hiểu đơn giản là hiện bạn muốn hiển thị Top 3 thì bạn làm 3 lần. Vậy nếu chẳng may trưởng phòng của bạn muốn Highlight Top 5 thì sao, rồi lại Top 6 nữa? Chẳng lẽ mỗi lần có yêu cầu mới thì bạn lại làm bằng sức “CƠM” như vậy?
Câu trả lời đương nhiên là không và đó mới là mục đích chính của bài viết này.
Đầu tiên bạn hãy tạo ra 1 ô phụ như sau
Với ô phụ nằm ở ô B10 thì bạn sẽ tạm thời gõ số 3 vào đó.
Sau đó bạn hãy tạo thêm cột phụ như sau.
Ô C1 có công thức là:
\="Top "&B10
Ô C2 có công thức là:
\=IF(B2>=LARGE($B$2:$B$8,$B$10),B2,"")
Sau đó hãy kéo công thức cho vùng từ C2 đến C8.
Ở đây ta sử dụng hàm LARGE để lấy ra giá trị lớn thứ 3 trong vùng B2:B8.
Còn lại thì ta dùng hàm IF để trả về kết quả là giá trị ô thuộc cột B trong dòng tương ứng nếu giá trị đó nằm trong Top 3, ngược lại thì trả về kết quả là “”.
Lý do ta để “” đó là vì khi bạn sử dụng tính năng Insert Chart trong Excel thì khi gặp phải ô trống thì Excel sẽ bỏ qua và không vẽ đồ thị cho dòng đó.
Giờ bạn hãy chọn vùng từ A1:B8 và chèn vào 1 biểu đồ Bar Chart như sau.
Để vẽ biểu đồ Bar Chart thì bạn sẽ vào Insert và chọn
Và đây là kết quả.
Để mở rộng và lấy thêm Data từ cột Top 3 và đưa vào biểu đồ thì ta sẽ đưa con chuột tới dưới cùng của ô B8 rồi kéo sang phải là được.
Lưu ý là bạn cần phải chọn vào biểu đồ của chúng ta đang vẽ trước để Excel có thể hiển thị các vùng dữ liệu đang được dùng để vẽ nên biểu đồ như sau.
Và đây là kết quả:
Giờ bạn hãy chèn thêm Data Label vào cho biểu đồ này bằng cách chọn biểu đồ. Sau đó bạn hãy ấn vào dấu + bên tay phải biểu đồ và chọn Data Labels.
Giờ bạn hãy xoá đi Data Label của phần biểu đồ màu cam bằng cách chọn vào Data Label nằm bên tay phải các Bar Chart màu cam. Sau đó nhấn Delete.
Tiếp theo ta cần đè 2 biểu đồ trên lên nhau, nói đơn giản là khiến cho 2 biểu đồ trên hoà làm 1.
Để làm điều đó thì bạn hãy Click vào phần Bar Chart màu cam.
Sau đó Ấn Ctrl 1 để mở cửa sổ Format Data Series. Sau đó bạn hãy chọn tới mục Series Options.
Tại mục Series Overlap bạn hãy chỉnh thành 100% như hình sau.
Sau khi bạn chỉnh sau thì biểu đồ của chúng ta đã thành hình như sau.
Giờ nếu bạn muốn Highlight Top 4 thì sao?
Bạn chỉ cần tới ô B10 và gõ số 4 vào là được. Lúc này thì biểu đồ của chúng ta đã trở thành Dynamic rồi đấy.
Bạn nên nhớ là khi bạn vẽ biểu đồ hay viết bất cứ công thức nào trong Excel thì nên lưu ý đến việc có thể tạo ra biểu đồ Dynamic nhé.