Pam có hai cột dữ liệu. Trong cột A có các định danh đơn giản, chẳng hạn như A, B, C, v.v. Trong cột B có một loạt các giá trị nguyên.

Cô ấy có thể sắp xếp dữ liệu theo số nhận dạng và thứ hai, theo các giá trị số nguyên. Bây giờ cô ấy muốn, trong cột C, có một công thức sẽ nối tất cả các giá trị số nguyên cho một số nhận dạng cụ thể. Do đó, nếu A1: A4 đều chứa số nhận dạng A, thì trong ô C1, cô ấy muốn có tất cả các giá trị trong B1: B4 được nối và chia bằng dấu phẩy, chẳng hạn như “11, 17, 19, 25”. Vì số lượng hàng cho mỗi mã nhận dạng có thể khác nhau, Pam không chắc chắn về cách nối.

Cách dễ nhất để thực hiện điều này là sử dụng macro, có thể được tạo dưới dạng một hàm do người dùng xác định. Đây là một ví dụ:

Function CatSame(c As Range) As String     Application.Volatile     sTemp = ""

iCurCol = c.Column     If iCurCol = 3 Then         If c.Row = 1 Then             sLast = ""

Else             sLast = c.Offset(-1, -2)

End If         If c.Offset(0, -2) <> sLast Then             J = 0             Do                 sTemp = sTemp & ", " & c.Offset(J, -1)

J = J + 1             Loop While c.Offset(J, -2) = c.Offset(J - 1, -2)

sTemp = Right(sTemp, Len(sTemp) - 2)

End If     End If     CatSame = sTemp End Function

Về cơ bản, hàm này nhận một giá trị được chuyển cho nó (một tham chiếu ô) và xác minh rằng tham chiếu ô đó là cho cột C. Nếu có, thì nó bắt đầu nối các giá trị từ cột B dựa trên các giá trị trong cột A. Nó chỉ trả về chuỗi giá trị được nối nếu giá trị ở cột A khác với giá trị ở hàng phía trên nó.

Giả sử các số nhận dạng của bạn nằm trong cột A và các giá trị của bạn được nối nằm trong cột B, bạn có thể đặt giá trị sau vào cột C:

=CatSame(C1)

Sao chép nó xuống nếu cần thiết trong cột C và bạn sẽ có chính xác những gì Pam muốn.

Một hàm đa năng hơn sẽ hoạt động giống như hàm VLOOKUP, nhưng mang lại một danh sách các giá trị được ghép nối phù hợp với bất cứ thứ gì bạn đang tìm kiếm. Xem xét chức năng sau:

Function VLookupAll(vValue, rngAll As Range, _   iCol As Integer, Optional sSep As String = ", ")

Dim rCell As Range     Dim rng As Range     On Error GoTo ErrHandler

Application.Volatile     Set rng = Intersect(rngAll, rngAll.Columns(1))

For Each rCell In rng         If rCell.Value = vValue Then _           VLookupAll = VLookupAll & sSep & _           rCell.Offset(0, iCol).Value     Next rCell

If VLookupAll = "" Then         VLookupAll = CVErr(xlErrNA)

Else         VLookupAll = Right(VLookupAll, Len(VLookupAll) - Len(sSep))

End If ErrHandler:

If Err.Number <> 0 Then VLookupAll = CVErr(xlErrValue)

End Function

Hàm này có tối đa bốn đối số. Đầu tiên là giá trị bạn muốn đối sánh trong tra cứu của mình. Trong trường hợp của Pam, đây sẽ là số nhận dạng bạn muốn, chẳng hạn như A, B hoặc C. Đối số thứ hai là phạm vi ô cần tìm kiếm các kết quả phù hợp (cột A trong trường hợp này).

Đối số thứ ba là một khoảng bù (từ phạm vi trong đối số thứ hai)

đại diện cho các giá trị bạn muốn nối. Bạn có thể sử dụng hàm theo cách sau:

=VLookupAll("B",A1:A99,1)

Nếu bạn muốn chỉ định một dấu phân cách khác nhau giữa các giá trị, bạn có thể thực hiện việc đó bằng cách sử dụng đối số thứ tư tùy chọn. Ví dụ: phần sau trả về một chuỗi trong đó dấu gạch ngang phân tách từng giá trị:

=VLookupAll("B",A1:A99,1,"-")

Các giải pháp cho đến nay đều tập trung vào việc sử dụng macro. Lý do cho điều này tương đối đơn giản: Không có một giải pháp dựa trên công thức nào có thể làm được những gì Pam cần. Sử dụng các câu lệnh IF lồng nhau để đánh giá những gì trong cột A sẽ không hoạt động tốt vì bạn bị hạn chế về mức độ sâu của các câu lệnh IF có thể được lồng vào nhau.

Bạn có thể sử dụng một công thức và một kết quả trung gian nếu bạn không phiền khi các giá trị được nối ở phiên bản cuối cùng của một mã định danh trong cột A. Bắt đầu bằng cách đặt công thức này vào ô C1:

=B1

Công thức này sẽ đi vào ô C2:

=IF(A2=A1,C1 & ", " & B2, B2)

Sao chép công thức này xuống nhiều hàng nếu cần. Những gì bạn nhận được cuối cùng là một chuỗi các giá trị nối ngày càng dài trong cột C, với giá trị dài nhất trong mỗi lần chạy nằm trên cùng một hàng với giá trị nhận dạng tuần tự cuối cùng trong cột A. Sau đó, bạn có thể đặt giá trị sau vào tất cả các ô thích hợp của cột D:

=IF(LEN(C2)>LEN(C1),"",C1)

Công thức này chỉ hiển thị các chuỗi dài nhất từ ​​cột C, đó là những gì Pam cần để bắt đầu.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (9197) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: