Nếu bạn đang tìm cách dễ dàng nhất để gộp hàng trong Excel dựa trên tiêu chí, bạn đang ở đúng nơi. Ở đây tôi sẽ cố gắng chỉ các cách gộp hàng trong Excel dựa trên tiêu chí.
Tải file Excel thực hành
Các phương pháp dễ dàng nhất gộp hàng trong Excel dựa trên tiêu chí
Hãy xem bảng dữ liệu trước. Ở đây, tôi lấy tập dữ liệu gồm 3 cột tên Tác giả (Author), Sách (Books), Giá (VNĐ) (Price), và 9 hàng. Đôi khi dựa trên tiêu chí nhiều hàng sẽ được gộp ở đây bằng các hàm khác nhau.
Phương pháp-1: Dùng tùy chọn Consolidate (Hợp nhất) để gộp giá trị trùng lặp
Giả sử bạn phải gộp hàng dựa trên cùng tác giả và theo tên của họ, tổng giá sách sẽ được cộng lại.
- Để đáp ứng tiêu chí này, trước hết làm theo Data tab >> Data Tools >> Consolidate (Hợp nhất)
2. Hộp thoại Consolidate xuất hiện, chọn toàn bộ phạm vi dữ liệu làm Reference (Tham chiếu), nhấp Add (Thêm) vào All references , nhấp Left Column (Cột bên trái), cuối cùng nhấn OK.
3. Sau đó, các ô trùng lặp cột Tác giả sẽ được gộp và giá trị Giá (VNĐ) tương ứng được cộng.
Trong tình huống trên, tôi dùng hàm SUM của Consolidate. Nhưng dựa trên tiêu chí khác như đếm số lần lặp tên tác giả đối với tên tác giả đã gộp.
4. Để giải quyết, chỉ cần chọn hàm Count Numbers (Đếm số) như dưới.
5. Sau khi nhấn OK, bảng sau xuất hiện.
Đọc thêm: Gộp hàng trùng lặp trong Excel
Phương pháp-2: Dùng hàm Logic để gộp văn bản giống nhau
- Giả sử tiêu chí khác là gộp tất cả sách vào một ô theo cùng tác giả. Để giải quyết, tôi dùng hàm IF. Trước hết chọn cột Tác giả rồi làm theo Home tab >> Editing (Chỉnh sửa) >> Sort & Filter (Sắp xếp & Lọc) >> Sort A to Z (Sắp xếp A đến Z)
- Sau đó, dữ liệu đã sắp xếp xuất hiện.
- Dùng hàm IF như dưới đây
=IF( logical test, value if true, value if false)
Ở đây, logical test (điều kiện logic) là B3=B4 (kiểm tra tác giả có giống nhau không)
value if true (giá trị nếu đúng) là nối tên sách của cùng tác giả
value if false (giá trị nếu sai) là hiển thị tên sách cho tác giả không trùng lặp thêm.
=IF(B3=B4, E3& "" &C4,C4)
- Nhập công thức và kéo xuống đến Công thức 1 . Bảng sau xuất hiện.
- Dùng hàm IF lại ở Công thức 2
Ở đây, =IF(B5<>B4,0,1)
Nghĩa là nếu hai ô tiếp theo không bằng nhau trả về 0, ngược lại trả về 1.
- Nhập công thức và kéo xuống sao chép vào Công thức 2
- Lọc cột Công thức 2 và chọn tùy chọn lọc 0 như dưới.
- Nhấn OK, bạn sẽ nhận bảng sau nơi sách cùng tác giả được gộp.
Phương pháp 3: Dùng PivotTable để gộp hàng
Phần này, để gộp hàng dựa trên cùng tác giả và cộng giá sách theo tác giả, dùng tùy chọn PivotTable.
- Trước hết làm theo Insert >> Table >> PivotTable
- Hộp thoại xuất hiện, chọn phạm vi dữ liệu ở Table/ Range
- Sheet mới xuất hiện.
- Kéo Tác giả vào trường Rows (Hàng) và Tổng Giá (VNĐ) vào trường Values (Giá trị). Bảng xuất hiện: hàng cùng tác giả được gộp ở cột Tác giả, giá được cộng theo tác giả.
Phương pháp-4: Dùng COUNTIF để Gộp hàng
Phần này, tôi muốn nối tất cả Sách theo mỗi Tác giả.
- Trước hết dùng hàm sau. Ở đây dùng AMPERSAND (&) và COUNTIF .
=B4&” “&COUNTIF($B$4:$B4,B4)
=COUNTIF(criteria range, criteria)
Ở đây & nối hai Văn bản và Khoảng trắng
- Nhập công thức và kéo xuống đến cột Combined (Kết hợp), kết quả sau xuất hiện.
- Dùng hàm logic sau.
=IF($B4&” “&1=E4, INDEX(C:C,MATCH($B4&” “&1,E:E,0))&” “&IFERROR(INDEX(C:C,MATCH($B4&” “&2,E:E,0)),"")&” “&IFERROR(INDEX(C:C,MATCH($B4&” “&3,E:E,0)),"")&” “&IFERROR(INDEX(C:C,MATCH($B4&” “&4,E:E,0)),""),"")
- Nhập hàm và kéo qua cột Hàng gộp tên Sách (Merged row of Books name), các hàng gộp theo tiêu chí xuất hiện.
Kết luận:
Hy vọng bài viết giúp bạn gộp hàng dựa trên tiêu chí dễ dàng. Đây là những cách dễ nhất tôi cố gắng bao quát. Thoải mái bình luận nếu khó hiểu. Nếu bạn có cách khác hãy chia sẻ.
Đọc thêm
- Cách gộp hàng và cột trong Excel (2 cách)
- Excel gộp hàng cùng giá trị (4 cách)
- Cách kết hợp hàng cùng ID trong Excel (3 phương pháp nhanh)
- Cách gộp nhiều hàng thành một ô trong Excel
- Gộp hàng trùng lặp và cộng giá trị trong Excel