Note: The other languages of the website are Google-translated. Back to English

Làm thế nào để dễ dàng nối văn bản dựa trên các tiêu chí trong Excel?

Giả sử tôi có một cột số ID chứa một số trùng lặp và một cột tên, và bây giờ, tôi muốn ghép các tên dựa trên số ID duy nhất như hình minh họa bên trái, để nhanh chóng kết hợp văn bản dựa trên tiêu chí, làm thế nào chúng ta có thể làm trong Excel?

doc kết hợp văn bản dựa trên tiêu chí 1

Nối văn bản dựa trên tiêu chí với Chức năng do Người dùng Xác định

Nối văn bản dựa trên tiêu chí với Kutools cho Excel


Để kết hợp văn bản với các số ID duy nhất, trước tiên bạn có thể trích xuất các giá trị duy nhất và sau đó tạo Hàm do người dùng xác định để kết hợp các tên dựa trên ID duy nhất.

1. Lấy dữ liệu sau làm ví dụ, trước tiên bạn cần trích xuất các số ID duy nhất, vui lòng áp dụng công thức mảng sau: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""), Nhập công thức này vào ô trống, ví dụ D2, sau đó nhấn Ctrl + Shift + Enter các phím với nhau, xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 2

Mẹo: Trong công thức trên, A2: A15 là phạm vi dữ liệu danh sách mà bạn muốn trích xuất các giá trị duy nhất, D1 là ô đầu tiên của cột mà bạn muốn đưa ra kết quả giải nén.

2. Và sau đó kéo chốt điều khiển điền xuống để trích xuất tất cả các giá trị duy nhất cho đến khi các khoảng trống được hiển thị, xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 3

3. Trong bước này, bạn nên tạo Chức năng do người dùng xác định để kết hợp các tên dựa trên số ID duy nhất, vui lòng nhấn giữ ALT + F11 chìa khóa và nó mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

4. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Mô-đun Cửa sổ.

Mã VBA: nối văn bản dựa trên tiêu chí

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

5. Sau đó, lưu và đóng mã này, quay lại trang tính của bạn và nhập công thức này vào ô E2, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 4

6. Sau đó kéo chốt điền xuống các ô mà bạn muốn áp dụng công thức này và tất cả các tên tương ứng đã được kết hợp dựa trên số ID, xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 5

Lời khuyên:

1. Trong công thức trên, A2: A15 là dữ liệu gốc mà bạn muốn kết hợp dựa trên, D2 là giá trị duy nhất bạn đã trích xuất và B2: B15 là cột tên mà bạn muốn kết hợp với nhau.

2. Như bạn có thể thấy, tôi đã kết hợp các giá trị được phân tách bằng dấu phẩy, bạn có thể sử dụng bất kỳ ký tự nào khác bằng cách thay đổi dấu phẩy “,” của công thức khi bạn cần.


Nếu bạn có Kutools cho Excel, Với khả Các hàng kết hợp nâng cao tiện ích, bạn có thể nhanh chóng và thuận tiện nối các cơ sở văn bản trên các tiêu chí.

Kutools cho Excel : với hơn 300 bổ trợ Excel tiện dụng, dùng thử miễn phí không giới hạn trong 30 ngày.

Sau khi cài đặt Kutools cho Excel, thực hiện như các bước sau:

1. Chọn phạm vi dữ liệu mà bạn muốn kết hợp dựa trên một cột.

2. Nhấp chuột Kutools > Hợp nhất & Tách > Các hàng kết hợp nâng cao, xem ảnh chụp màn hình:

3. Trong Kết hợp các hàng dựa trên cột hộp thoại, bấm vào cột ID và sau đó bấm Khóa chính để đặt cột này làm cột chính mà dữ liệu kết hợp của bạn dựa trên, hãy xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 7

4. Và sau đó nhấp vào Họ tên cột mà bạn muốn kết hợp các giá trị, sau đó nhấp vào Kết hợp và chọn một dấu phân tách cho dữ liệu kết hợp, xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 8

5. Sau khi hoàn tất các cài đặt này, hãy nhấp vào OK để thoát khỏi hộp thoại và dữ liệu trong cột B đã được kết hợp với nhau dựa trên cột chính A. Xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 9

Với tính năng này, vấn đề sau sẽ được giải quyết trong thời gian sớm nhất:

Làm thế nào để kết hợp nhiều hàng thành một và tính tổng các bản sao trong Excel?

Tải xuống và dùng thử miễn phí Kutools cho Excel ngay!


Kutools cho Excel: với hơn 300 bổ trợ Excel tiện dụng, dùng thử miễn phí không giới hạn trong 30 ngày. Tải xuống và dùng thử miễn phí ngay!

Các công cụ năng suất văn phòng tốt nhất

Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn

  • Tái sử dụng: Chèn nhanh công thức phức tạp, biểu đồ và bất cứ thứ gì bạn đã sử dụng trước đây; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
  • Thanh siêu công thức (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
  • Hợp nhất các ô / hàng / cột mà không làm mất dữ liệu; Nội dung phân chia ô; Kết hợp các hàng / cột trùng lặp... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
  • Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
  • Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
  • Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
  • Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
  • Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
  • Hơn 300 tính năng mạnh mẽ. Hỗ trợ Office / Excel 2007-2021 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ tính năng Dùng thử miễn phí 30 ngày. Bảo đảm hoàn lại tiền trong 60 ngày.
tab kte 201905

Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
officetab dưới cùng
Nhận xét (38)
Chưa có xếp hạng. Hãy là người đầu tiên xếp hạng!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Đây là một giải pháp tuyệt vời (mã VBA) và nó giải quyết các yêu cầu của tôi trong vài phút. Tôi sẽ giới thiệu trang web của bạn cho những người khác và tôi sẽ truy cập để tìm mọi thứ mà tôi cần trong tương lai.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Cảm ơn vì mã này. Đó chính xác là những gì tôi cần. Bạn đã tiết kiệm cho tôi rất nhiều công sức, cảm ơn bạn rất nhiều.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Cảm ơn vì đã đăng bài này chính xác là những gì tôi đang tìm kiếm. Tôi dường như không lưu mã vba một cách chính xác. Tôi nhận được thông báo lỗi về tên không rõ ràng được tìm thấy. Bất kỳ đề xuất hoặc từng bước về bước VBA của dự án này? Thanks
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Liệu công cụ này có thể xử lý các kết hợp phân biệt chữ hoa chữ thường, chẳng hạn như jABC 123 abc 345 ABc 678 ABC 912
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Mã VBA này đã lưu trong ngày cho tôi. Cảm ơn bạn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi đang tìm cách sử dụng một biến thể của mã này để tạo danh sách biến thể dựa trên biến thể chính. Sử dụng dữ liệu mẫu của bạn, tôi sẽ cần kết hợp các cột A và B thành các số nhận dạng duy nhất và sau đó nối các số nhận dạng đó với mỗi hàng dựa trên giá trị trong cột A, loại trừ giá trị khỏi kết hợp cho hàng đó và phần còn lại trong sắp xếp alpha thứ tự: Danh sách biến thể id tên id chính CN20150012 Lucy CN20150012-Lucy CN20150012-Andy CN20150012-Monica CN20150012-Phiby US20150011 Tommas US20150011-Tommas US20150011-Rose CN20150012 Monica CN20150012-Monica CN20150012-Andy CN20150012-Lucy CN20150012-Phiby CN20150012-Phiby CN20150012-Phiby CN20150012-Phiby CN20150012 -Andy CN20150012-Lucy CN20150011-Monica US20150011 Rose US20150011-Rose US20150014-Tommas UK20150014 Peter UK20150014-Peter UK20150014-Anith UK20150014-Kristi UK20150010-Libin JP20150010 Ramon JP20150010-Ramon JP20150010-Tommas UK20150014 Peter UK20150014-Peter UK20150014-Anith UK20150014-Kristi UK20150014-Libin JP20150014 Ramon JP20150014-Ramon JP20150014-Brenda JP20150014-Libin UK20150014-Libin UK20150010-Libin UK20150010-Libin UK20150010 Libin -Kristi UK20150010-Peter UK20150010 Anith UK20150012-Anith UK20150012-Kristi UK20150012-Libin UK20150012-Peter JP20150012 James JP20150014 20150014-James JP20150010-Brenda JP20150010-James JP20150014-Matus CN20150014 Andy CN20150014-Andy CN20150014-Lucy CN20150014-Monica CN20150010-Phiby UK20150010 Matus UK20150010-Matus JP20150010-Brenda JP1000-James Li UK4 Kristi UKXNUMX-Knith UKXNUMX-Knith UKXNUMX-Kristi UKXNUMX Peter JPXNUMX Brenda JPXNUMX-Brenda JPXNUMX-James JPXNUMX-Ramon Tôi có một tờ với hơn XNUMX dòng, mỗi mục có tới XNUMX biến thể. Cố gắng làm điều này theo cách thủ công là không thể nhưng tôi không thể tìm thấy giải pháp phù hợp với nhu cầu của mình.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Mất nhiều thời gian hơn để cập nhật cùng một công thức concatenateif (). tôi có 5000 hàng. và hơn 2 giờ rồi nó vẫn đang cập nhật :( Có giải pháp nào để làm cho nó hoạt động nhanh không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Được giải thích chi tiết và dễ hiểu, thực sự hữu ích khi tôi gặp khó khăn trong cùng một tình huống.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Cực kỳ hữu ích và được giải thích độc đáo
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tuyệt vời, cảm ơn bạn! Tôi đã sử dụng giải pháp VBA và nó hoạt động tuyệt vời.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tuyệt quá!!! Cảm ơn bạn rất nhiều!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Nó không hoạt động đối với phạm vi dữ liệu lớn. Tôi thấy phạm vi hoạt động của nó chỉ lên đến A2: A362. Chúng tôi sẽ rất biết ơn nếu bạn chia sẻ giải pháp cho dải dữ liệu lớn như A2: A200000 .... Cảm ơn bạn
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Hoạt động tuyệt vời chỉ chậm. Tôi đang làm điều đó với 27k dòng văn bản trong excel chỉ cần thiết lập nó đi để pha và để nó chạy
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Nó không hoạt động đối với phạm vi dữ liệu lớn. Tôi thấy rằng thay đổi dữ liệu hoạt động của nó lên đến A2: A362. Chúng tôi sẽ rất biết ơn nếu bạn chia sẻ giải pháp để bao phủ dải dữ liệu rộng hơn như A2: A200000 ..... Cảm ơn bạn
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào! concactenateif là Chính xác những gì tôi đang tìm kiếm. Nhưng rất tiếc không thể làm cho nó hoạt động Luôn gặp lỗi biên dịch: lỗi cú pháp. Có ý kiến ​​gì không? Trước đây, với một số mô-đun VBA đã nhập, tôi đã nhận thấy rằng tôi phải thay thế "," bằng ";" như trong PC của tôi, có thể do cài đặt khu vực của tôi, đó là cách duy nhất để nó hoạt động. Sẵn sàng sử dụng sumifs tích hợp sẵn, vv Nhưng không thể hiểu được nơi nào đang xảy ra sai trên cái này. Một khả năng nữa xuất hiện trong tâm trí là trong office 365, "concat" thay thế "concactenate". Bạn có thể giúp đỡ được không? Cảm ơn trước, Yash
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Mã sử ​​dụng một số khoảng trắng không ngắt để thụt đầu dòng, những dấu cách này tăng lên trên Excel2016. Khó phát hiện ra lỗi vô hình ..
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi đã gặp sự cố sau khi dán mã này vào Excel 2016 - nó chứa các dấu cách không thông thường (có lẽ là dấu cách không ngắt?) Gây ra các lỗi cú pháp không rõ ràng cho dù bạn có quan sát kỹ đến đâu vì chúng không nhìn thấy được! Đó là các khoảng trống thụt lề là vấn đề. Dán mã vào Word và bật các ký tự ẩn để xem chúng.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Ồ!! Thiên tài! Làm việc như người ở! Có những khoảng trống hiển thị như một nhân vật khác. Cảm ơn Dave rất nhiều! Tự hỏi làm thế nào bạn nảy ra ý tưởng! Ngoài ra, hãy tự hỏi nó hoạt động như thế nào đối với một số ứng dụng nhìn trộm khác..Bằng cách nào, xin cảm ơn một lần nữa!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có cách nào để làm điều này trên Mac không ????
Đó chính xác là những gì tôi cần - vui lòng cho tôi biết (hoặc nếu bất kỳ phần mềm mac nào sẽ làm điều đó mà bạn biết). Cám ơn
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có cách nào để áp dụng hàm CONCATENATEIF này trong một trang tính riêng biệt không? Nó hoạt động khi tôi đặt nó trong cùng một trang tính làm dữ liệu đầu vào, nhưng tôi cần cả hai bảng trong các trang tính khác nhau và nó không hoạt động.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có, những gì bạn muốn làm là thêm chức năng vào một mô-đun. Đi vào trình soạn thảo VBA, nhấp chuột phải vào "VBAProject" trong Project Explorer, di chuột qua mục menu "Chèn" và trong menu con đó, chọn "Mô-đun". Bất kỳ chức năng nào bạn đưa vào đó sẽ có thể sử dụng được trên bất kỳ trang tính nào trong sổ làm việc của bạn.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào các bạn, tôi gặp lỗi #NAME? khi tôi áp dụng công thức CONCATENATEIF trong tệp excel sau khi đặt mã VBA cho điều này, bất cứ ai có thể giúp tôi giải quyết nó, cảm ơn som uch
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Thật dễ dàng, cảm ơn bạn :)
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có thể thay bộ chia dấu phẩy bằng dấu ngắt dòng, tức là ký tự (10) không? Cảm ơn nhiều.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, David,

Để kết hợp các ô với dấu ngắt dòng, Hàm do Người dùng Xác định sau đây có thể giúp bạn.

Hàm ConcatenateIf_LineBreak (CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim xResult dưới dạng Chuỗi
On Error Resume Next
Nếu CriteriaRange.Count <> ConcatenateRange.Count thì
ConcatenateIf = CVErr (xlErrRef)
Chức năng thoát
Cuối Nếu
Đối với I = 1 Đến CriteriaRange.Count
If CriteriaRange.Cells (I) .Value = Condition thì
xResult = xResult & vbCrLf & ConcatenateRange.Cells (I) .Value
Cuối Nếu
Tiếp theo tôi
Nếu xResult <> "" Thì
xResult = VBA.Mid (xResult, VBA.Len (Dấu phân tách) + 1)
Cuối Nếu
Nối If_LineBreak = xResult
Chức năng thoát
Cuối Chức năng

Sau khi dán mã này, hãy áp dụng công thức này: = ConcatenateIf_LineBreak (A2: A13, F2, B2: B13, ",").

Sau khi nhận được kết quả với công thức này, bạn nên nhấp vào Wrap Text để có được kết quả chính xác mà bạn cần.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Cảm ơn rât nhiều! Điều này rất đơn giản và đã giúp ích rất nhiều !!
Không có bình luận nào được đăng ở đây
Tải thêm
Để lại ý kiến ​​của bạn
Đăng với tư cách khách
×
Đánh giá bài viết này:
0   Nhân vật
Các vị trí được đề xuất

Kết nối với chúng tôi

Bản quyền © 2009 - www.extendoffice.com. | Đã đăng ký Bản quyền. cung cấp bởi ExtendOffice. | BẢN ĐỒ CHI NHÁNH
Microsoft và logo Office là các nhãn hiệu hoặc nhãn hiệu đã đăng ký của Microsoft Corporation tại Hoa Kỳ và / hoặc các quốc gia khác.
Được bảo vệ bởi Sectigo SSL