, October 02, 2022

0 kết quả được tìm thấy

Data Analyst with Excel: Conditional Formatting

  • Đăng bởi  Groot
  •  Aug 25, 2022

  •   15 min reads
Data Analyst with Excel: Conditional Formatting

Trong bài viết này chúng ta sẽ tìm hiểu tất tần tật chủ đề Conditional Formatting trong Excel nhé!

Note: Bạn có thể kéo xuống dưới cùng để download tài liệu của bài viết này.

Conditional Formatting

Conditional Formatting trong Excel cho phép bạn highlight các ô với một màu sắc nhất định. Màu sắc đó sẽ phụ thuộc vào giá trị của ô đó.

Highlight Cells Rules

Để highlight các ô có giá trị lớn hơn một giá trị nào đó khác, bạn hãy thực thi các bước sau:

1. Chọn phạm vi A1:A10.

2. Ở tab Home, trong group Styles, click Conditional Formatting.

click Conditional Formatting
click Conditional Formatting

3. Click Highlight Cells Rules => Great Than

Highlight Cells Rules
Highlight Cells Rules

4. Nhập giá trị 80 và chọn kiểu định dạng màu sắc

Great Than
Great Than

5. Click OK

Kết quả là Excel sẽ highlight những ô lớn hơn 80

Kết quả
Kết quả

6. Thay đổi giá trị của ô A1 thành 81, bạn sẽ thấy ô tự động thay đổi ngay lập tức

Ở bước ba, bạn hãy khám phá thêm các điều kiện khác để định dạng màu sắc nhé. Ví dụ như là: Less than, Between, Equal to, text that contains,...

Clear Rules

Để xóa các quy tắc của conditional formatting, bạn hãy thực thi các bước sau:

1. Chọn phạm vi A1:A10.

2.  Ở tab Home, trong group Styles, click Conditional Formatting.

Conditional Formatting.
Conditional Formatting.

3. Click Clear Rules => Clear Rules from Selected Cells.

Clear Rules from Selected Cells
Clear Rules from Selected Cells

Top/Bottom

Để highlight những ô chứa các giá trị trên trung bình, hãy thực thi những bước sau.

1. Click Top/Bottom Rules => Above Average.

Above Average
Above Average

2. Chọn một kiểu định dạng màu sắc

3. Click OK

Excel sẽ tự tính toán ra giá trị trung bình (42,5) và highlight các ô chứa giá trị trên mức này.

Ở bước 1, bạn cũng có thể khám phá thêm các điều kiện khác như: Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%,...

Conditional Formatting với các công thức

Nâng trình Excel của bạn lên level tiếp theo bằng cách sử dụng công thức để xác định ô nào nên format. Các công thức áp dụng conditional formatting phải trả về TRUE hoặc FALSE.

1. Chọn phạm vi A1:E5

2.  Ở tab Home, trong group Styles, click Conditional Formatting.

Conditional Formatting
Conditional Formatting

3. Click New Rule

Click New Rule
Click New Rule

4. Chọn 'Use a formula to determine which cells to format'

5. Nhập công thức =ISODD(A1)

6. Chọn kiểu formatting và click OK

Kết quả là Excel sẽ highlights tất cả các số lẻ

Một ví dụ tiếp theo

7. Chọn phạm vi A2:D7

8. Lặp lại từ bước 2-4 ở trên

9. Nhập công thức =$C2="USA"

10. Chọn một kiểu formating và click OK

Kết quả là Excel highlight tất cả các hàng USA

Manage Rules

Để xem tất cả các quy tắc conditional formatting trong một workbook, bạn hãy sử dụng Conditional Formatting Rules Manager.

1. Chọn ô A1.

2. Ở tab Home, trong group Styles, click Conditional Formatting.

Conditional Formatting
Conditional Formatting

3. Click Manage Rules

Click Manage Rules
Click Manage Rules

Hộp thoại Conditional Formatting Rules Manager sẽ xuất hiện

Conditional Formatting Rules Manager
Conditional Formatting Rules Manager

4. Từ danh sách thả xuống, thay đổi Current Selection thành This Worksheet để xem tất cả conditional formatting rules trong worksheet này.

conditional formatting rules
conditional formatting rules

Bạn có thể click New Rule, Edit Rule và Delete Rule để tạo, chỉnh sửa và xóa các rules.

Data Bars

Data bars trong Excel giúp trực quan hóa các giá trị một cách dễ dàng hơn. Một bar dài hơn thể hiện được một giá trị cao hơn.

Để thêm các Data Bars, bạn hãy thực thi các bước sau.

1. Chọn một phạm vi các giá trị

2. Ở tab Home, trong group Styles, click Conditional Formatting.

 Conditional Formatting
Conditional Formatting

3. Click Data Bars và chọn kiểu mà bạn muốn

Data Bars
Data Bars

Kết quả được thể hiện như sau:

Theo mặc định, ô có giá trị nhỏ nhất (là giá trị 0 nếu không có giá trị âm) sẽ không có data bar. Ô có giá trị tối đa (là 95 trong ví dụ trên) có data bars lấp đầy toàn bộ ô. Tất cả các ô khác được lấp đầy theo tỷ lệ tương ứng.

4. Thay đổi các giá trị

Khi bạn thay đổi các giá trị trong ô thì data bars sẽ tự động thay đổi theo. Đọc tiếp để biết thêm cách tùy chỉnh những data bars này.

5. Chọn phạm vi A1:A10

6. Ở tab Home, trong group Styles, click Conditional Formatting => Manage Rules.

7. Click Edit Rule

Excel sẽ xuất hiện hộp thoại Edit Formatting Rule. Tại đây bạn có thể tùy chỉnh thêm về data bars (Show Bar Only, Minimum and Maximum, Bar Appearance, Negative Value và Axis, Bar Direction,...)

Edit Formatting Rule
Edit Formatting Rule

8. Chọn Number từ trường Minimum nhập vào giá trị 100. Chọn Number từ trường Maximum và nhập giá trị 150

9. Click OK hai lần.

Kết quả

Kết quả
Kết quả

Những ô chứa giá trị 100 (nếu có) sẽ không có data bar và những ô chứa giá trị 150 (nếu có) sẽ có data bar lấp đầy toàn bộ ô.

Color Scales

Những giá trị trong các ô sẽ trực quan hóa hơn nếu bạn dùng Color Scales. Sắc thái của màu sắc sẽ đại diện cho giá trị trong một ô.

Để thêm Color Scales, bạn hãy thực thi các bước sau.

1. Hãy chọn một phạm vi các giá trị.

2. Ở tab Home, trong group Styles, click Conditional Formatting.

Conditional Formatting
Conditional Formatting

3. Click Color Scales và chọn một kiểu mà bạn muốn.

Color Scales
Color Scales

kết quả sẽ như thế này.

Kết quả
Kết quả

Theo mặc định, Excel thực hiện phép tính trung vị cho các giá trị. Ô có giá trị nhỏ nhất (trong vd trên là 9) có màu đỏ. Ô có giá trị trung vị (36) có màu vàng. Ô có giá trị lớn nhất (80) có màu xanh lá cây. Tất cả các ô còn lại có màu sắc theo tỷ lệ tương ứng.

Đọc tiếp để biết cách tùy chỉnh thêm cho tính năng Color Scale này nhé!

4. Chọn phạm vi A1:A7.

5. Ở tab Home, trong group Styles, click Conditional Formatting => Manage Rules.

6. Click Edit rule.

Excel sẽ xuất hiện hộp thoại Edit Formatting Rule. Tại đây bạn có thể tùy chỉnh thêm các tính năng color scale (Format Style, Minium, Midpoint và Maximum, Color,...).

Edit Formatting Rule
Edit Formatting Rule

7. Chọn 2-Color Scale từ trường Format Style và chọn màu trắng và xanh.

8. Click OK hai lần.

Edit Formatting Rule
Edit Formatting Rule

Kết quả sẽ như sau.

Kết Quả
Kết Quả

Icon Sets

Icon Sets trong Excel cũng là một tính năng giúp chúng ta trực quan hóa giá trị trong ô. Mỗi icon đại diện một phạm vi các giá trị.

Để thêm một icon set, bạn hãy thực thi các bước sau.

1. Chọn một phạm vi.

2. Ở tab Home, trong group Styles, click Conditional Formatting.

Conditional Formatting
Conditional Formatting

3. Click Icon Sets và chọn kiểu mà bạn muốn.

Icon Sets
Icon Sets

Kết quả như thế này:

Kết Quả
Kết Quả

Theo mặc định, đối với 3 icons, Excel thực hiện tính toán phân vị thứ 67 và phân vị thứ 33.

Phân vị thứ 67 = min + 0.67 * (max-min) = 2 + 0.67 * (95-2) = 64.31

Phân vị thứ 33 = min + 0.33 * (max-min) = 2 + 0.33 * (95-2) = 32.69

Mũi tên màu xanh lá sẽ thể hiện các giá trị bằng hoặc lớn hơn 64.31. Mũi tên màu vàng sẽ thể hiện các giá trị nhỏ hơn 64.31 hoặc bằng hoặc lớn hơn 32.69. Mũi tên màu đỏ sẽ thể hiện các giá trị nhỏ hơn 32.69

4. Thay đổi các giá trị.

Khi bạn thay đổi các giá trị thì Excel sẽ tự động cập nhập lại các icon.

Một số tùy chỉnh khác cho tính năng icon set này.

5. Chọn phạm vị A1:A10

6. Ở tab Home, trong group Styles, click Conditional Formatting => Manage Rules.

7. Click Edit Rule

Click Edit Rule
Click Edit Rule

Excel sẽ xuất hiện hộp thoại Edit Formatting Rule. Tại đây bạn có thể tùy chỉnh thêm các tính năng khác của Icon set (Icon Style, Reverse Icon Order, Show Icon Only, Icon, Value, Type,...)

8. Chọn 3 icon từ trường Icon Style. Chọn No Cell Icon từ danh sách thả xuống của icon thứ 2. Ở trường Types thay đổi giá trị thành Number. Ở trường Value nhập giá trị 100 và 0. Chọn biểu tượng lớn hơn (>) bên cạnh giá trị 0.

Bạn có thể xem thêm hình bến dưới để rõ cách làm

9. Click OK hai lần.

Edit Formatting Rule
Edit Formatting Rule

Kết quả sẽ hiện thị như sau.

Kết Quả
Kết Quả

Shade Alternate Rows

Để phủ màu luân phiên các hàng trong Excel, bạn hãy sử dụng conditional formatting hoặc áp dụng một table style. Việc đổ màu cho từng hàng khác nhau sẽ giúp cho việc đọc dữ liệu của bạn dễ dàng hơn.

1. Chọn một phạm vi.

Để phủ màu cho toàn bộ worksheet, bạn hãy click vào nút Select All (mũi tên màu cam trên hình)

2. Ở tab Home, trong group Styles, click Conditional Formatting.

Conditional Formatting
Conditional Formatting

3. Click New Rule.

Click New Rule.
Click New Rule.

4. Chọn "Use a formula to determine which cells to format"

5. Nhập công thức sau =MOD(ROW(),2)=0

6. Chọn một kiểu định dạng rồi click OK.

Use a formula to determine which cells to format
Use a formula to determine which cells to format

Kết quả hiển thị như sau.

Hàm MOD trả về số dư của một phép chia. Hàm ROW trả về số hàng của một ô.

Ví dụ: Ở hàng thứ 7, MOD (7,2) = 1 vì 7 chia 2 bằng 3 và có số dư là 1. Ở hàng thứ 8, MOD (8,2) = 0 vì 8 chia 2 bằng 4 và có số dư là 0.

Như vậy tất cả các ô trong từng hàng ở ví dụ trên nếu trả về là 0 sẽ được phủ màu lên.

Nếu bạn không thích sử dụng công thức thì có thể áp dụng table style để phủ màu luân phiên các hàng trong Excel.

7. Chọn một ô bất kỳ bên trong phạm vi các giá trị

8. Ở tab Home, trong group Styles, click Format as Table.

Format as Table
Format as Table

9. Chọn một kiểu table với các hàng được luân phiên tô màu.

10. Click OK

Kết quả.

Để thay đổi màu từ hàng sang cột, bạn hãy thực thi các bước sau.

11. Đầu tiên, hãy chọn một ô bên trong table, tiếp theo, chọn tab Table Design. Trong group Table Styles Options, hãy uncheck Banded Rows và check Banded Columns.

Banded Columns
Banded Columns

Kết quả sẽ hiển thị như sau.

Kết Quả
Kết Quả

Để chuyển table này thành một phạm vi các ô bình thường, hãy thực thi các bước sau.

12. Đâu tiên, hãy chọn một ô bên trong table. Tiếp theo, ở tab Table Design, trong group Tools, click Convert to Range.

Convert to Range
Convert to Range

Kết quả hiển thị như sau.

So sánh hai danh sách

Phần này sẽ hướng dẫn bạn cách so sánh hai danh sách bằng cách sử dụng conditional formatting. Ví dụ, bạn có 2 danh sách các team NFL

Danh sách các team NFL
Danh sách các team NFL

Để hightlight các team có trong danh sách đầu tiên mà không có trong danh sách thứ hai, hãy thực thi các bước sau.

1. Đầu tiên, hãy chọn phạm vi A1:A18 và đặt tên cho nó là firstList, chọn phạm vi B1:B20 và đặt tên cho nó là secondList.

2. Tiếp theo, hãy chọn phạm vi A1:A18

3. Ở tab Home, trong group Styles, click Conditional Formatting.

Conditional Formatting
Conditional Formatting

4. Click New Rule

Click New Rule
Click New Rule

5. Chọn 'Use a formula to determine which cells to format'.

6. Nhập công thức =COUNTIF(secondList,A1)=0

7. Chọn một formatting style và click OK

New Formatting Rule
New Formatting Rule

Kết quả là Miami Dolphins và Tennessee Titans không có trong danh sách thứ hai.

COUNTIF(secondList,A1) đếm số teams trong secondList mà bằng với team trong ô A1.

Nếu COUNTIF(secondList,A1) = 0 thì team trong ô A1 không có trong secondList. Với kết quả này, Excel sẽ phủ màu xanh dương cho ô đó.

Luôn luôn lấy giá trị của ô ở trên cùng phía bên trên của phạm vi đã chọn (A1:A18) cho công thức. Excel sẽ tự động copy công thức đó cho các ô khác. Do đó, ở ô A2 sẽ có công thức =COUNTIF(secondList,A2)=0, ô A3 =COUNTIF(secondList,A3)=0,...

8. Để highlight các team có trong secondList mà không chứa trong firstList, hãy chọn phạm vị B1:B20, tạo một new rule bằng công thức =COUNTIF(firstList,B1)=0 rồi cài đặt định dạng fill màu cam.

Kết quả là các team :Denver Broncos, Arizona Cardinals, Minnesota Vikings và Pittsburgh Steelers không có trong danh sách đầu tiên.

Conflicting Rules

Thỉnh thoảng một vài conditional formatting rules trong Excel bị xung đột với nhau. Phần này sẽ minh họa cho bạn hai kết quả khác nhau.

1. Giá trị 95 thì lớn hơn 80 những cũng là giá trị cao nhất (top 1). Các định dạng (yellow fill vs green fill và yellow text color vs green text color) bị xung đột. Bạn hãy nhìn vào hình bên dưới, quy tắc nào được đặt cao hơn sẽ được thực thi. Do đó, giá trị 95 được tô màu vàng.

Kết quả.

2. Nếu di chuyển quy tắc thứ 2 lên trên cùng. Giá trị 95 sẽ được tô màu xanh lá cây.

Kết quả.

Heat Map

Để tạo heat map trong Excel, bạn có thể sử dụng conditional formatting. Heat map là một biểu diễn đồ họa của dữ liệu trong đó các giá trị riêng lẻ được biểu diễn dưới dạng màu sắc.

Để tạo một heat map, bạn hãy thực thi các bước sau.

1. Chọn phạm vi B3:M11

2. Ở tab Home, trong group Styles, click Conditional Formatting.

3. Click Color Scale và chọn kiểu mà bạn muốn.

Kết quả.

4. Chọn phạm vi B3:M11

5. Click chuột phải và sau đó chọn click Format Cells (hoặc nhấn tổ hợp phím CTRL + 1)

6. Chọn Custom category

7. Nhập ;;;

8. Click OK

Kết quả

Nếu bạn có định hướng trở thành Data Analyst chuyên nghiệp thì bạn có thể tham khảo bộ khóa học toàn diện chuyên nghiệp với combo 4 khóa học trong 1 bootcamp của 200Lab tại đây.

Ngoài ra, bạn có thể nhấn vào link này để tham gia vào nhóm và nhận thêm nhiều tài liệu hữu ích khác về Data nhé!

Bài viết cùng seri

Bài viết liên quan

Master data là gì? Sự khác nhau giữa Master data và Transaction data

Master data là tập hợp các định danh thống nhất và các thuộc tính mở rộng. Nó mô tả các thực thể cốt lõi của doanh nghiệp bao gồm khách hàng,.......

Master data là gì? Sự khác nhau giữa Master data và Transaction data
Danh mục các loại biểu đồ trong Data Visualization

Bạn có thể tìm thấy danh sách các loại biểu đồ, nó sẽ hoạt động như một hướng dẫn đầy hữu ích giúp bạn lựa chọn được biểu đồ phù hợp với nhu cầu của bản thân....

Danh mục các loại biểu đồ trong Data Visualization
Data Analysis with Excel: Analysis ToolPak

Bộ công cụ Analysis ToolPak trên Excel sẽ giúp bạn tiết kiệm thời gian và đơn giản hóa các bước phân tích dữ liệu tài chính, thống kê ....

Data Analysis with Excel: Analysis ToolPak
Data Analysis with Excel: Solver

Excel có một công cụ được gọi là solver cung cấp các lệnh và các tính năng tùy chỉnh để giải quyết các vấn đề quyết định....

Data Analysis with Excel: Solver
Data Analysis with Excel: What-If Analysis

What-If Analysis trong Excel cho phép bạn thử các giá trị (scenarios) khác nhau cho các công thức....

Data Analysis with Excel: What-If Analysis
You've successfully subscribed to 200Lab Blog
Great! Next, complete checkout for full access to 200Lab Blog
Xin chào mừng bạn đã quay trở lại
OK! Tài khoản của bạn đã kích hoạt thành công.
Success! Your billing info is updated.
Billing info update failed.
Your link has expired.