Phân tích dữ liệu Excel - Định dạng có điều kiện
Trong Microsoft Excel, bạn có thể sử dụng Conditional Formattingđể trực quan hóa dữ liệu. Bạn phải chỉ định định dạng cho một phạm vi ô dựa trên nội dung của phạm vi ô. Các ô đáp ứng các điều kiện được chỉ định sẽ được định dạng như bạn đã xác định.
Thí dụ
Trong phạm vi có chứa số liệu bán hàng của quý trước cho một nhóm nhân viên bán hàng, bạn có thể đánh dấu những ô đại diện cho những người đã đạt được mục tiêu xác định, chẳng hạn như $ 2500.
Bạn có thể đặt điều kiện là tổng doanh số của một người> = $2500và chỉ định mã màu là xanh lục. Excel sẽ kiểm tra từng ô trong phạm vi để xác định xem điều kiện bạn đã chỉ định, tức là tổng doanh thu của một người> = $ 2500 có được thỏa mãn hay không.
Excel áp dụng định dạng bạn đã chọn, tức là màu xanh lá cây cho tất cả các ô thỏa mãn điều kiện. Nếu nội dung của một ô không thỏa mãn điều kiện, định dạng của ô đó vẫn không thay đổi. Kết quả đúng như mong đợi, chỉ đối với những nhân viên bán hàng đã đạt được mục tiêu, các ô được đánh dấu màu xanh lá cây - một hình dung nhanh về kết quả phân tích.
Bạn có thể chỉ định bất kỳ số điều kiện nào để định dạng bằng cách chỉ định Rules. Bạn có thể chọn các quy tắc phù hợp với điều kiện của mình từ
- Đánh dấu các quy tắc ô
- Quy tắc trên cùng / dưới cùng
Bạn cũng có thể xác định các quy tắc của riêng bạn. Bạn có thể -
- Thêm quy tắc
- Xóa quy tắc hiện có
- Quản lý các quy tắc đã xác định
Hơn nữa, bạn có một số tùy chọn định dạng trong Excel để chọn những tùy chọn thích hợp cho Trực quan hóa Dữ liệu của bạn -
- Thanh dữ liệu
- Thang màu
- Bộ biểu tượng
Định dạng có điều kiện đã được tăng cường trên các phiên bản Excel 2007, Excel 2010, Excel 2013. Các ví dụ bạn tìm thấy trong chương này là từ Excel 2013.
Trong các phần sau, bạn sẽ hiểu các quy tắc định dạng có điều kiện, các tùy chọn định dạng và cách làm việc với các quy tắc.
Làm nổi bật Quy tắc ô
Bạn có thể dùng Highlight Cells quy tắc để gán một định dạng cho các ô có nội dung đáp ứng bất kỳ tiêu chí nào sau đây:
- Các số trong một phạm vi số nhất định -
- Lớn hơn
- Ít hơn
- Between
- Tương đương với
- Văn bản chứa một chuỗi văn bản nhất định.
- Ngày xảy ra trong một phạm vi ngày nhất định liên quan đến ngày hiện tại -
- Yesterday
- Today
- Tomorrow
- Trong 7 ngày qua
- Tuần trước
- Tuần này
- Tuần tới
- Tháng trước
- Tháng này
- Tháng tiếp theo
- Giá trị trùng lặp hoặc duy nhất.
Làm theo các bước để định dạng ô có điều kiện -
Chọn phạm vi được định dạng có điều kiện.
Nhấp chuột Conditional Formatting bên trong Styles nhóm dưới Home chuyển hướng.
Nhấp chuột Highlight Cells Rules từ menu thả xuống.
Nhấp chuột Greater Thanvà chỉ định> 750. Chọn màu xanh lá cây.
Nhấp chuột Less Than và chỉ định <500. Chọn màu đỏ.
Nhấp chuột Between và chỉ định 500 và 750. Chọn màu vàng.
Dữ liệu sẽ được đánh dấu dựa trên các điều kiện đã cho và định dạng tương ứng.
Quy tắc Trên cùng / Dưới cùng
Bạn có thể dùng Top / Bottom Rules để gán một định dạng cho các ô có nội dung đáp ứng bất kỳ tiêu chí nào sau đây:
Top 10 items - Các ô xếp hạng N trên cùng, trong đó 1 <= N <= 1000.
Top 10% - Các ô xếp hạng trong n% hàng đầu, trong đó 1 <= n <= 100.
Bottom 10 items - Các ô xếp hạng N dưới cùng, trong đó 1 <= N <= 1000.
Bottom 10% - Các ô xếp hạng n% dưới cùng, trong đó 1 <= n <= 100.
Above average - Các ô trên mức trung bình cho phạm vi đã chọn.
Below average - Các ô dưới mức trung bình cho phạm vi đã chọn.
Làm theo các bước dưới đây để chỉ định các quy tắc Trên cùng / Dưới cùng.
Chọn phạm vi được định dạng có điều kiện.
Nhấp chuột Conditional Formatting bên trong Styles nhóm dưới Home chuyển hướng.
Nhấp chuột Top/Bottom Rulestừ menu thả xuống. Tùy chọn quy tắc Trên cùng / Dưới cùng xuất hiện.
Nhấp chuột Top Ten Items và chỉ định 5. Chọn màu xanh lá cây.
Nhấp chuột Bottom Ten Items và chỉ định 5. Chọn màu đỏ.
Dữ liệu sẽ được đánh dấu dựa trên các điều kiện đã cho và định dạng tương ứng.
Lặp lại ba bước đầu tiên được đưa ra ở trên.
Nhấp chuột Top Ten% và chỉ định 5. Chọn màu xanh lá cây.
Nhấp chuột Bottom Ten% và chỉ định 5. Chọn màu đỏ.
Dữ liệu sẽ được đánh dấu dựa trên các điều kiện đã cho và định dạng tương ứng.
Lặp lại ba bước đầu tiên được đưa ra ở trên.
Nhấp chuột Above Average. Chọn màu xanh lá cây.
Nhấp chuột Below Average. Chọn màu đỏ.
Dữ liệu sẽ được đánh dấu dựa trên các điều kiện đã cho và định dạng tương ứng.
Thanh dữ liệu
Bạn có thể sử dụng màu Data Barsđể xem giá trị trong một ô có liên quan đến giá trị trong các ô khác. Chiều dài của thanh dữ liệu thể hiện giá trị trong ô. Thanh dài hơn biểu thị giá trị cao hơn và thanh ngắn hơn biểu thị giá trị thấp hơn. Bạn có sáu màu đồng nhất để lựa chọn cho các thanh dữ liệu - xanh lam, xanh lục, đỏ, vàng, xanh lam nhạt và tím.
Thanh dữ liệu hữu ích trong việc hình dung các giá trị cao hơn, thấp hơn và trung gian khi bạn có lượng lớn dữ liệu. Ví dụ - Nhiệt độ ngày giữa các vùng trong một tháng cụ thể. Bạn có thể sử dụng các thanh màu tô gradient để trực quan hóa giá trị trong một ô so với các giá trị trong các ô khác. Bạn có sáuGradient Colors để lựa chọn cho các Thanh dữ liệu - Xanh lam, Xanh lục, Đỏ, Vàng, Xanh lam nhạt và Tím.
Chọn phạm vi được định dạng có điều kiện.
Nhấp chuột Conditional Formatting bên trong Styles nhóm dưới Home chuyển hướng.
Nhấp chuột Data Barstừ menu thả xuống. CácGradient Fill các tùy chọn và Fill tùy chọn xuất hiện.
Nhấp vào thanh dữ liệu màu xanh lam trong Gradient Fill các tùy chọn.
Lặp lại ba bước đầu tiên.
Nhấp vào thanh dữ liệu màu xanh lam trong Solid Fill các tùy chọn.
Bạn cũng có thể định dạng thanh dữ liệu sao cho thanh dữ liệu bắt đầu ở giữa ô và kéo dài sang trái cho các giá trị âm và kéo dài sang phải cho các giá trị dương.
Thang màu
Bạn có thể dùng Color Scalesđể xem giá trị trong một ô liên quan đến giá trị trong các ô khác trong một phạm vi nhất định. Như trong trường hợpHighlight Cells Rules, một Color Scalesử dụng tô bóng ô để hiển thị sự khác biệt về giá trị ô. Gradient màu sẽ được áp dụng cho một loạt các ô. Màu sắc cho biết mỗi giá trị ô nằm trong phạm vi đó.
Bạn có thể chọn từ -
- Ba - Thang màu -
- Thang màu xanh - vàng - đỏ
- Thang màu Đỏ - Vàng - Xanh lục
- Thang màu xanh - trắng - đỏ
- Thang màu Đỏ - Trắng - Xanh lục
- Thang màu xanh - trắng - đỏ
- Thang màu Đỏ - Trắng - Xanh
- Thang hai màu -
- Thang màu trắng - đỏ
- Thang màu đỏ - trắng
- Thang màu xanh - trắng
- Thang màu trắng - xanh lá cây
- Thang màu xanh - vàng
- Thang màu vàng - xanh lục
Làm theo các bước dưới đây -
Chọn Phạm vi được định dạng có điều kiện.
Nhấp chuột Conditional Formatting bên trong Styles nhóm dưới Home chuyển hướng.
Nhấp chuột Color Scalestừ menu thả xuống. CácColor Scale tùy chọn xuất hiện.
Nhấp vào Thang màu Xanh lục - Vàng - Đỏ.
Dữ liệu sẽ được đánh dấu dựa trên thang màu Xanh lục - Vàng - Đỏ trong phạm vi đã chọn.
- Lặp lại ba bước đầu tiên.
- Nhấp vào thang màu Xanh lục - Trắng.
Dữ liệu sẽ được đánh dấu dựa trên thang màu Xanh lục - Trắng trong phạm vi đã chọn.
Bộ biểu tượng
Bạn có thể sử dụng các bộ biểu tượng để hình dung sự khác biệt về số lượng. Các bộ biểu tượng sau có sẵn -
Như bạn quan sát, một bộ biểu tượng bao gồm ba đến năm biểu tượng. Bạn có thể xác định tiêu chí để liên kết biểu tượng với từng giá trị trong một phạm vi ô. Ví dụ: mũi tên xuống màu đỏ cho các số nhỏ, mũi tên lên màu xanh lá cây cho các số lớn và mũi tên ngang màu vàng cho các giá trị trung gian.
Chọn phạm vi được định dạng có điều kiện.
Nhấp chuột Conditional Formatting bên trong Styles nhóm dưới Home chuyển hướng.
Nhấp chuột Icon Setstừ menu thả xuống. CácIcon Sets tùy chọn xuất hiện.
Nhấp vào ba mũi tên được tô màu.
Các Mũi tên Màu xuất hiện bên cạnh Dữ liệu dựa trên các Giá trị trong phạm vi đã chọn.
Lặp lại ba bước đầu tiên. CácIcon Sets tùy chọn xuất hiện.
Chọn 5 Xếp hạng. Biểu tượng Xếp hạng xuất hiện bên cạnh dữ liệu dựa trên các giá trị trong phạm vi đã chọn.
Quy tắc mới
Bạn có thể dùng New Rule để tạo công thức của riêng bạn làm điều kiện để định dạng một ô như bạn xác định.
Có hai cách để sử dụng Quy tắc mới -
Với New Rule tùy chọn từ menu thả xuống
Với New Rule nút trong Manage Rules hộp thoại
Với tùy chọn Quy tắc mới từ Menu thả xuống
Chọn Phạm vi được định dạng có điều kiện.
Nhấp chuột Conditional Formatting bên trong Styles nhóm dưới Home chuyển hướng.
Nhấp chuột New Rule từ menu thả xuống.
Các New Formatting Rule hộp thoại xuất hiện.
Từ Hộp Chọn Loại Quy tắc, hãy chọn Sử dụng công thức để xác định ô cần định dạng. Edit the Rule Description hộp xuất hiện.
Trong các giá trị định dạng mà công thức này là đúng: hãy nhập công thức.
Bấm vào nút định dạng và bấm OK.
Các ô chứa giá trị có công thức TRUE, được định dạng như đã xác định.
Với nút quy tắc mới trong hộp thoại quản lý quy tắc
Chọn phạm vi được định dạng có điều kiện.
Nhấp chuột Conditional Formatting bên trong Styles nhóm dưới Home chuyển hướng.
Nhấp chuột Manage Rules từ menu thả xuống.
Các Conditional Formatting Rules Manager hộp thoại xuất hiện.
Nhấn vào New Rule cái nút.
Các New Formatting Rule hộp thoại xuất hiện.
Lặp lại các Bước đã cho ở trên để xác định công thức và định dạng của bạn.
Các Conditional Formatting Rules Manager hộp thoại xuất hiện với New Rulenhấn mạnh. Nhấn vàoApply cái nút.
Các ô chứa giá trị có công thức TRUE, được định dạng như đã xác định.
Quy tắc rõ ràng
Bạn có thể Xóa quy tắc để xóa tất cả các định dạng có điều kiện mà bạn đã tạo
- Các ô đã chọn
- Bảng tính hiện tại
- Bảng đã chọn
- PivotTable đã chọn
Làm theo các bước đã cho -
Chọn Phạm vi / Nhấp vào Trang tính / Nhấp vào bảng> PivotTable nơi quy tắc định dạng có điều kiện cần được loại bỏ.
Nhấp chuột Conditional Formatting bên trong Styles nhóm dưới Home chuyển hướng.
Nhấp chuột Clear Rulestừ menu thả xuống. Các tùy chọn Quy tắc rõ ràng xuất hiện.
Chọn tùy chọn thích hợp. Định dạng có điều kiện sẽ bị xóa khỏi Phạm vi / Trang tính / Bảng / PivotTable.
Quản lý các quy tắc
Bạn có thể Manage Rulestừ Conditional Formatting Rules Managercửa sổ. Bạn có thể xem các quy tắc định dạng cho lựa chọn hiện tại, cho toàn bộ trang tính hiện tại, cho các trang tính khác trong sổ làm việc hoặc các bảng hoặc PivotTables trong sổ làm việc.
Nhấp chuột Conditional Formatting bên trong Styles nhóm dưới Home chuyển hướng.
Nhấp chuột Manage Rules từ menu thả xuống.
Các Conditional Formatting Rules Manager hộp thoại xuất hiện.
Bấm vào mũi tên trong Hộp Danh sách bên cạnh Show formatting rules for Lựa chọn hiện tại, Trang tính này và các Trang tính khác, Bảng, PivotTable nếu tồn tại với Quy tắc định dạng có điều kiện, sẽ xuất hiện.
Lựa chọn This Worksheettừ danh sách thả xuống. Các Quy tắc Định dạng trên Trang tính hiện tại xuất hiện theo thứ tự sẽ được áp dụng. Bạn có thể thay đổi thứ tự này bằng cách sử dụng các mũi tên lên và xuống.
Bạn có thể thêm Quy tắc mới, Chỉnh sửa Quy tắc và Xóa Quy tắc.
Bạn đã thấy New Ruletrong phần trước. Bạn có thể xóa quy tắc bằng cách chọn Quy tắc và nhấp vàoDelete Rule. Quy tắc được đánh dấu sẽ bị xóa.
Để chỉnh sửa Quy tắc, hãy chọn QUY TẮC và nhấp vào Edit Rule. Edit Formatting Rule hộp thoại xuất hiện.
Bạn có thể
Chọn một loại quy tắc
Chỉnh sửa Mô tả Quy tắc
Chỉnh sửa định dạng
Khi bạn đã hoàn tất các thay đổi, hãy nhấp vào OK.
Những thay đổi đối với Quy tắc sẽ được phản ánh trong Conditional Formatting Rules Managerhộp thoại. Nhấp chuộtApply.
Dữ liệu sẽ được đánh dấu dựa trên sửa đổi Conditional Formatting Rules.