Nội dung
Nếu bạn có một danh sách lớn được sắp xếp theo một số cột, thì sẽ rất tốt nếu bạn tự động phân tách các tập hợp hàng kết quả bằng cách phân tách các đường ngang để rõ ràng:
Trong ví dụ trên, đây là các dòng giữa các quốc gia, nhưng nói chung là giữa bất kỳ mục nào lặp lại trong cùng một cột. Hãy xem xét một vài cách để thực hiện điều này.
Phương pháp 1. Đơn giản
Cách nhanh nhất để làm điều này rất dễ dàng với định dạng có điều kiện, định dạng này sẽ vẽ đường viền dưới cùng của các ô nếu nội dung của ô trong cột A không bằng nội dung của ô tiếp theo trong cùng một cột. Chọn tất cả các ô trong bảng ngoại trừ tiêu đề và chọn Chính tab lệnh Định dạng có điều kiện - Tạo quy tắc (Trang chủ - Định dạng có điều kiện - Quy tắc mới). Chọn loại quy tắc Sử dụng công thức để xác định ô cần định dạng (Sử dụng công thức để xác định ô cần định dạng) và nhập công thức sau vào trường:
Chú ý đến đô la trong các địa chỉ để sửa chữa các ký tự cột, nhưng không phải số hàng, bởi vì. chúng tôi chỉ so sánh các quốc gia trong cột A. Không được có khoảng trắng trong công thức.
Nhấn vào nút Khung (Định dạng) và trong cửa sổ đã mở trên tab Biên giới (Viền) bật dòng có màu mong muốn ở đường viền dưới cùng. Sau khi nhấp vào OK quy tắc của chúng tôi sẽ hoạt động và các đường gạch ngang sẽ xuất hiện giữa các nhóm đường
Phương pháp 2. Với hỗ trợ bộ lọc cho số và ngày
Một nhược điểm nhỏ nhưng rất đáng chú ý của phương pháp đầu tiên là các đường viền như vậy không phải lúc nào cũng hoạt động chính xác khi lọc danh sách theo các cột khác. Vì vậy, ví dụ: nếu chúng tôi lọc bảng của mình theo ngày (chỉ tháng XNUMX), thì các dòng sẽ không còn hiển thị giữa tất cả các quốc gia như trước đây:
Trong trường hợp này, bạn có thể thoát ra bằng cách sử dụng chức năng TIÊU ĐỀ (TIỀM NĂNG), có thể thực hiện các phép toán khác nhau (tổng, trung bình, đếm, v.v.), nhưng chỉ "xem" các ô được lọc. Ví dụ: hãy sắp xếp bảng của chúng ta theo cột cuối cùng với ngày và vẽ một đường phân chia giữa các ngày. Trong định dạng có điều kiện, bạn sẽ phải tạo quy tắc tương tự như phương pháp đầu tiên, nhưng không sử dụng liên kết trực tiếp khi so sánh các ô D2 và D3, nhưng bao gồm chúng dưới dạng đối số trong hàm SUBTOTAL:
Đối số đầu tiên của hàm (số 109) là opcode tổng hợp. Trong thực tế, chúng tôi không thêm bất cứ điều gì ở đây và thực tế, một phép toán ngu ngốc như SUM (D2), tất nhiên, bằng D2. Nhưng chức năng này khác với SUM ở chỗ nó chỉ thực hiện các hành động trên các ô có thể nhìn thấy, tức là và các ô còn lại sau bộ lọc trên màn hình sẽ được so sánh, đó là những gì chúng tôi muốn.
Phương pháp 3. Với hỗ trợ bộ lọc cho bất kỳ dữ liệu nào
Như bạn có thể dễ dàng thấy, phương pháp thứ hai cũng có một hạn chế: hàm sum chỉ có thể được áp dụng cho số hoặc ngày (cũng là số trong Excel), chứ không thể áp dụng cho văn bản. Nghĩa là, nếu chúng ta muốn vẽ một đường thẳng giữa các quốc gia, như trong phương pháp đầu tiên, nhưng để nó được hiển thị chính xác sau khi lọc, thì chúng ta sẽ phải sử dụng một đường dẫn phức tạp hơn nhiều. Chọn lại toàn bộ bảng ngoại trừ tiêu đề, tạo quy tắc mới dựa trên công thức và nhập cấu trúc sau vào trường xác thực:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ($A$1:$A2;СТРОКА($A$1:$A2)-МИН(СТРОКА($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Trong phiên bản tiếng Anh, nó sẽ là:
=SUMPRODUCT(SUBTOTAL(103;OFFSET($A$1:$A2;ROW($A$1:$A2)-MIN(ROW($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Bằng cách nhấp vào nút Khung (Định dạng) thiết lập một đường viền với một đường màu đỏ ở trên cùng và nhấp vào OK. Kết quả phân chia theo quốc gia sẽ hoạt động chính xác ngay cả sau khi lọc, ví dụ: theo ngày:
- Đánh dấu Ngày và Giờ với Định dạng có Điều kiện
- Cách Excel thực sự hoạt động với ngày và giờ
- Cách sử dụng định dạng có điều kiện để đánh dấu các ô theo điều kiện trong Excel