Khi chúng tôi làm việc trên một biểu dữ liệu lớn, chúng tôi cần thay thế màu hàng để hình dung tập dữ liệu của chúng tôi tốt hơn. Chúng ta có thể thực hiện nhiệm vụ thay đổi màu sắc như một ban nhạc hoặc một nhóm. Trong bài viết này, chúng tôi sẽ chứng minh 6 phương pháp dễ dàng để thay thế màu hàng dựa trên nhóm trong Excel. Nếu bạn cũng quan tâm đến nó, hãy tải xuống sách bài tập thực hành của chúng tôi và theo dõi chúng tôi.
Tải xuống sách bài tập thực hành này để thực hành trong khi bạn đang đọc bài viết này.
6 cách nhanh để thay thế màu hàng dựa trên nhóm trong Excel
Để chứng minh các phương pháp tiếp cận, chúng tôi xem xét tập dữ liệu 10 người tiêu dùng trái cây. Tên của các mặt hàng trái cây nằm trong cột B và tên của người mua và số lượng thực phẩm nằm trong cột C và D , tương ứng. Vì vậy, chúng tôi có thể nói rằng tập dữ liệu của chúng tôi nằm trong phạm vi ô B5:D14 . Trong mỗi phương pháp, chúng tôi phải áp dụng định dạng có điều kiện để thay đổi màu ô.
1. Kết hợp AND với các hàm LEN và MOD
Trong phương pháp này, trước tiên, chúng ta sẽ sử dụng hàm IF trong tập dữ liệu của chúng tôi để nhận nhóm số. Bên cạnh đó, chúng tôi phải sử dụng VÀ , LEN và MOD trong quy tắc định dạng có điều kiện để thay thế màu hàng. Các bước của quy trình này được đưa ra dưới đây:
📌 Các bước:
- Trước hết, hãy chọn ô E4 và viết ra Không (0) vào phòng giam.
0
- Nhấn Enter .
- Bây giờ, chọn ô E5 và viết công thức sau vào ô.
=IF(B5=B4,E4,E4+1)
- Tương tự, nhấn Enter .
- Sau đó, nhấp đúp vào trên Xử lý điền biểu tượng để sao chép ô vào ô E14 .
- Sau đó, chọn phạm vi ô B5:E14 .
- Trong Trang chủ , nhấp vào mũi tên thả xuống của Định dạng có điều kiện> Quy tắc mới tùy chọn từ nhóm Kiểu .
- Do đó, một hộp thoại nhỏ được gọi là Quy tắc định dạng mới sẽ xuất hiện.
- Bây giờ, hãy viết công thức sau vào ô trống bên dưới Định dạng các giá trị mà công thức là đúng văn bản.
=AND(LEN($B5)>0,MOD($E5,2)=0)
- Sau đó, nhấp vào Định dạng tùy chọn.
- Một hộp thoại khác có tên Định dạng ô sẽ xuất hiện.
- Trong Điền , chọn màu theo ý muốn của bạn để phân biệt các hàng. Chúng tôi chọn Xanh lục, Accent 6, Nhẹ hơn 60% màu sắc.
- Cuối cùng, nhấp vào OK .
- Một lần nữa, nhấp vào OK để đóng Quy tắc định dạng mới hộp thoại.
- Bạn sẽ thấy trong vòng một giây, nhóm xen kẽ của tập dữ liệu hiển thị màu sắc.
Do đó, chúng tôi có thể nói rằng công thức của chúng tôi hoạt động hoàn hảo và chúng tôi có thể thay thế màu hàng dựa trên nhóm trong Excel.
🔎 Phân tích công thức
Chúng tôi đang chia nhỏ công thức E5 .
👉 LEN ($ B5) :Hàm này đếm độ dài của giá trị ô. Trong trường hợp này, giá trị là 5 .
👉 MOD ($ E5,2) :Hàm này chia giá trị của ô E5 bởi 2 và hiển thị giá trị của phần còn lại . Ở đây, kết quả là 1 .
👉 VÀ (LEN ($ B5)> 0, MOD ($ E5,2) =0) :Trong công thức này, ký tự VÀ chức năng kiểm tra xem giá trị của LEN hàm lớn hơn 5 và kết quả của MOD hàm bằng 0 . Nếu cả hai lôgic đều là Đúng , hàng sẽ hiển thị màu đã chọn của chúng tôi. Nếu không, nó sẽ hiển thị như bình thường.
Đọc thêm: Cách tô màu hàng thay thế cho các ô được hợp nhất trong Excel
2. Áp dụng các hàm MOD, IF và ROW
Trong quá trình này, MOD , NẾU và ROW các hàm sẽ giúp chúng ta phân nhóm theo số của tập dữ liệu của chúng ta. Các bước của phương pháp này được đưa ra như sau:
📌 Các bước:
- Đầu tiên, chọn ô E4 và viết ra Không (0) vào phòng giam.
0
- Nhấn Enter .
- Sau đó, chọn ô E5 và viết công thức sau vào ô.
=MOD(IF(ROW()=2,0,IF(B5=B4,E4,E4+1)),2)
- Một lần nữa, nhấn Enter .
- Bây giờ, nhấp đúp vào trên Xử lý điền biểu tượng để sao chép ô vào ô E14 .
- Sau đó, chọn phạm vi ô B5:E14 .
- Sau đó, trong Trang chủ , nhấp vào mũi tên thả xuống của Định dạng có điều kiện> Quy tắc mới tùy chọn từ nhóm Kiểu .
- Một hộp thoại nhỏ được gọi là Quy tắc định dạng mới sẽ xuất hiện.
- Tiếp theo, ghi công thức sau vào ô trống bên dưới Định dạng các giá trị mà công thức là đúng văn bản.
=$E5=1
- Sau đó, nhấp vào Định dạng tùy chọn.
- Một hộp thoại khác có tên Định dạng ô sẽ xuất hiện.
- Bây giờ, trong Điền , chọn màu theo ý muốn của bạn để phân biệt các hàng. Chúng tôi chọn Xanh lục, Accent 6, Nhẹ hơn 60% màu sắc.
- Cuối cùng, nhấp vào OK .
- Một lần nữa, nhấp vào OK để đóng Quy tắc định dạng mới hộp thoại.
- Bạn sẽ nhận thấy trong vòng một giây, nhóm xen kẽ của tập dữ liệu sẽ hiển thị màu sắc.
Vì vậy, chúng tôi có thể nói rằng công thức của chúng tôi hoạt động chính xác và chúng tôi có thể thay thế màu hàng dựa trên nhóm trong Excel.
🔎 Phân tích công thức
Chúng tôi đang chia nhỏ công thức E5 .
👉 ROW () :Hàm này trả về số hàng. Trong trường hợp này, giá trị là 5 .
👉 IF (B5 =B4, E4, E4 + 1) : IF hàm kiểm tra giá trị của ô B5 với B4 . Nếu cả hai giá trị khớp với nhau, hàm trả về giá trị của ô E4 . Nếu không, nó sẽ thêm 1 với giá trị của ô E4 và trả lại.
👉 IF (ROW () =2,0, IF (B5 =B4, E4, E4 + 1)) :Trong công thức này, IF chức năng kiểm tra xem số hàng bằng 2 . Nếu logic là Đúng , hàm trả về 0 . Hoặc, nếu logic là Sai hàm trả về kết quả của IF thứ hai chức năng.
👉 MOD (IF (ROW () =2,0, IF (B5 =B4, E4, E4 + 1)), 2) :Hàm sẽ chia kết quả của IF chức năng của 2 và hiển thị giá trị của phần còn lại .
Đọc thêm: Cách tô màu hàng thay thế dựa trên giá trị ô trong Excel
3. Kết hợp các hàm MOD và IF
Trong cách tiếp cận này, chúng tôi sẽ sử dụng MOD và IF các hàm để nhận nhóm số của tập dữ liệu của chúng tôi. Các bước của phương pháp này được đưa ra dưới đây:
📌 Các bước:
- Đầu tiên, hãy chọn ô E4 và viết ra Không (0) vào phòng giam.
0
- Nhấn Enter .
- Sau đó, chọn ô E5 và viết công thức sau vào ô.
=MOD(IF(B5=B4,E4,E4+1),2)
- Tương tự, nhấn Enter .
- Sau đó, nhấp đúp vào trên Xử lý điền biểu tượng để sao chép ô vào ô E14 .
- Bây giờ, hãy chọn phạm vi ô B5:E14 .
- Trong Trang chủ , nhấp vào mũi tên thả xuống của Định dạng có điều kiện> Quy tắc mới tùy chọn từ nhóm Kiểu .
- Do đó, một hộp thoại nhỏ được gọi là Quy tắc định dạng mới sẽ xuất hiện.
- Sau đó, hãy ghi lại công thức sau vào ô trống bên dưới Định dạng các giá trị mà công thức là đúng văn bản.
=$E5=1
- Sau đó, nhấp vào Định dạng tùy chọn.
- Một hộp thoại khác có tên Định dạng ô sẽ xuất hiện.
- Trong Điền , chọn màu theo ý muốn của bạn để phân biệt các hàng. Chúng tôi chọn Xanh lục, Accent 6, Nhẹ hơn 60% màu sắc.
- Cuối cùng, nhấp vào OK .
- Một lần nữa, nhấp vào OK để đóng Quy tắc định dạng mới hộp thoại.
- Bạn sẽ nhận được nhóm thay thế của tập dữ liệu hiển thị màu sắc.
Cuối cùng, chúng tôi có thể nói rằng công thức của chúng tôi hoạt động hiệu quả và chúng tôi có thể thay thế màu hàng dựa trên nhóm trong Excel.
🔎 Phân tích công thức
Chúng tôi đang chia nhỏ công thức E5 .
👉 IF (B5 =B4, E4, E4 + 1) : IF hàm kiểm tra giá trị của ô B5 với B4 . Nếu cả hai giá trị khớp với nhau, hàm trả về giá trị của ô E4 . Nếu không, nó sẽ thêm 1 với giá trị của ô E4 và trả lại.
👉 MOD (IF (B5 =B4, E4, E4 + 1), 2) :Hàm sẽ chia kết quả của IF chức năng của 2 và hiển thị giá trị của phần còn lại .
4. Áp dụng chức năng ISODD
Trong quy trình này, chúng tôi sẽ sử dụng IF và SUM trước tiên, để lấy nhóm số của tập dữ liệu của chúng tôi. Sau đó, chúng tôi phải sử dụng định dạng có điều kiện sử dụng chức năng ISODD để thay đổi màu hàng. Quy trình của quá trình này được trình bày như sau:
📌 Các bước:
- Trước tiên, hãy chọn ô E4 và viết ra Không (0) vào phòng giam.
0
- Nhấn Enter .
- Bây giờ, chọn ô E5 và viết công thức sau vào ô.
=IF(B4=B5,E4,SUM(E4,1))
- Một lần nữa, nhấn Enter .
- Sau đó, nhấp đúp vào trên Xử lý điền biểu tượng để sao chép ô vào ô E14 .
- Sau đó, chọn phạm vi ô B5:E14 .
- Trong Trang chủ , nhấp vào mũi tên thả xuống của Định dạng có điều kiện> Quy tắc mới tùy chọn từ nhóm Kiểu .
- Do đó, một hộp thoại nhỏ được gọi là Quy tắc định dạng mới sẽ xuất hiện.
- Bây giờ, hãy viết công thức sau vào ô trống bên dưới Định dạng các giá trị mà công thức là đúng
=ISODD($E5)
- Nhấp vào Định dạng tùy chọn.
- Một hộp thoại khác có tên Định dạng ô sẽ xuất hiện.
- Sau đó, trong Điền , chọn màu theo ý muốn của bạn để phân biệt các hàng. Chúng tôi chọn Xanh lục, Accent 6, Nhẹ hơn 60% màu sắc.
- Cuối cùng, nhấp vào OK .
- Một lần nữa, nhấp vào OK để đóng Quy tắc định dạng mới hộp thoại.
- Bạn sẽ thấy nhóm thay thế của tập dữ liệu hiển thị màu sắc.
Cuối cùng, chúng tôi có thể nói rằng công thức của chúng tôi đã hoạt động thành công và chúng tôi có thể thay thế màu hàng dựa trên nhóm trong Excel.
🔎 Phân tích công thức
Chúng tôi đang chia nhỏ công thức E5 .
👉 SUM (E4,1) :Hàm sẽ thêm 1 với giá trị của ô E4. Đối với ô này, hàm trả về 1.
👉 IF (B4 =B5, E4, SUM (E4,1)) : IF hàm kiểm tra giá trị của ô B5 với B4 . Nếu cả hai giá trị khớp với nhau, hàm trả về giá trị của ô E4 . Mặt khác, nếu logic là Sai , nó trả về kết quả của SUM chức năng.
5. Sử dụng hàm ISEVEN
Giống như chức năng ISODD , chúng tôi có thể sử dụng hàm ISEVEN để thay đổi màu hàng. Tuy nhiên, trước tiên chúng ta phải sử dụng sử dụng IF và SUM để có được nhóm số của tập dữ liệu của chúng tôi. Quy trình của trường hợp này được trình bày dưới đây:
📌 Các bước:
- Trước hết, hãy chọn ô E4 và viết ra Không (0) vào phòng giam.
0
- Nhấn Enter .
- Sau đó, chọn ô E5 và viết công thức sau vào ô.
=IF(B4=B5,E4,SUM(E4,1))
- Một lần nữa, nhấn Enter .
- Tiếp theo, nhấp đúp vào trên Xử lý điền biểu tượng để sao chép ô vào ô E14 .
- Bây giờ, hãy chọn phạm vi ô B5:E14 .
- Sau đó, trong Trang chủ , nhấp vào mũi tên thả xuống của Định dạng có điều kiện> Quy tắc mới tùy chọn từ nhóm Kiểu .
- Do đó, một hộp thoại nhỏ được gọi là Quy tắc định dạng mới sẽ xuất hiện.
- Viết công thức sau vào ô trống bên dưới Định dạng các giá trị mà công thức là đúng văn bản.
=ISEVEN($E5)
- Sau đó, nhấp vào Định dạng tùy chọn.
- Một hộp thoại khác có tên Định dạng ô sẽ xuất hiện.
- Trong Điền , chọn một màu theo mong muốn của bạn để làm cho các hàng dễ phân biệt. Chúng tôi chọn Xanh lục, Accent 6, Nhẹ hơn 60% màu sắc.
- Nhấp vào OK .
- Một lần nữa, nhấp vào OK để đóng Quy tắc định dạng mới hộp thoại.
- You will notice the alternating group of the dataset showing the color.
At last, we can say that our formula worked perfectly, and we were able to alternate the row color based on the group in Excel.
🔎 Breakdown of the Formula
We are breaking down the formula E5 .
👉 SUM(E4,1) :The function will add 1 with the value of cell E4. For this cell, the function returns 1.
👉 IF(B4=B5,E4,SUM(E4,1)) :The IF function checks the value of cell B5 with B4 . If both values match each other, the function returns the value of cell E4 . On the other hand, if the logic is False , it returns the result of the SUM chức năng.
6. Utilizing ISEVEN, CELLING and ROW Functions
In this case, we will use the ISEVEN , CEILING , và ROW functions to alter the row color. The procedure of this method is shown below step-by-step:
📌 Các bước:
- At first, select the range of cells B5:E14 .
- Now, in the Home tab, click on the drop-down arrow of the Conditional Formatting> New Rules option from the group Styles .
- As a result, a small dialog box called New Formatting Rule sẽ xuất hiện.
- Then, write down the following formula into the empty box below Format values where the formula is true text.
=ISEVEN(CEILING(ROW()-4,2)/2)
- After that, click on the Format tùy chọn.
- As a result, another dialog box called Format Cells sẽ xuất hiện.
- Afterward, in the Fill tab, choose a color according to your desire to make the rows distinguishable. We choose Green, Accent 6, Lighter 60% màu sắc.
- Nhấp vào OK to close the Format Cells hộp thoại.
- Again, click OK to close the New Formatting Rule hộp thoại.
- You will get the alternating group of the dataset showing the color.
Finally, we can say that our formula worked effectively, and we were able to alternate the row color based on the group in Excel.
🔎 Breakdown of the Formula
We are breaking down the formula row 5 .
👉 ROW() :This function returns the row number. In this case, the value is 5 .
👉 CEILING(ROW()-4,2) :This function deducts 4 from the result of the ROW function and then multiplies the value with 2 . Here, the result is 2 .
👉 ISEVEN(CEILING(ROW()-4,2)/2) :In this formula, the ISEVEN function checks whether the division value of the result of the CEILING function and 2 . If the value is even , the row will show our selected color. Otherwise, it shows as usual.
💬 Things You Should Know
You may notice that we input the formula directly in the conditional formatting rule box . We don’t perform any numerical grouping either. So, in this case, you have to keep the number of group entities equal to each other . Otherwise, the formula won’t work accurately.
Kết luận
Đó là phần cuối của bài viết này. I hope that this article will be helpful for you and you will be able to alternate the row color based on the group in Excel. Vui lòng chia sẻ thêm bất kỳ truy vấn hoặc đề xuất nào với chúng tôi trong phần nhận xét bên dưới nếu bạn có thêm bất kỳ câu hỏi hoặc đề xuất nào.
Đừng quên kiểm tra trang web ExcelDemy của chúng tôi cho một số vấn đề liên quan đến Excel và giải pháp. Tiếp tục học các phương pháp mới và tiếp tục phát triển!
Các bài viết liên quan
- How to Shade Every Other Row in Excel (3 Ways)
- Cách Thay thế Màu Hàng trong Excel Không có Bảng (5 Phương pháp)