Ví dụ, trong ví dụ trên, trong khi công thức được sử dụng là =CONCATENATE(A1. A5), kết quả chỉ hiển thị 'Hôm nay' và không kết hợp tất cả các ô Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách kết hợp nhiều ô bằng cách sử dụng hàm CONCATENATE Ghi chú. Nếu đang sử dụng Excel 2016, bạn có thể sử dụng hàm TEXTJOIN được tạo để kết hợp nhiều ô bằng dấu phân cách CONCATENATE Phạm vi Excel (Không có bất kỳ Dấu phân cách nào)![Concatenate Excel Range Without separator]()
Dưới đây là các bước để nối một phạm vi Excel mà không có bất kỳ dấu phân cách nào (như trong hình) - Chọn ô mà bạn cần kết quả
- Chuyển đến thanh công thức và nhập =TRANSPOSE(A1. A5)
- Dựa trên cài đặt khu vực của bạn, bạn cũng có thể thử =A1. A5 (thay vì =TRANSPOSE(A1. A5))
- Chọn toàn bộ công thức và nhấn F9 (thao tác này chuyển đổi công thức thành giá trị).
- Loại bỏ các dấu ngoặc nhọn từ cả hai đầu
- Thêm =CONCATENATE( vào đầu văn bản và kết thúc bằng dấu ngoặc tròn ).
- nhấn nút Enter
Làm điều này sẽ kết hợp phạm vi ô thành một ô (như trong hình trên). Lưu ý rằng vì chúng tôi sử dụng bất kỳ dấu phân cách nào (chẳng hạn như dấu phẩy hoặc dấu cách), nên tất cả các từ được nối với nhau mà không có bất kỳ dấu phân cách nào CONCATENATE Phạm vi Excel (Với dấu phân cách)![Concatenate Excel Range with a separator (space)]()
Dưới đây là các bước để nối một Phạm vi Excel với khoảng trắng làm dấu phân cách (như minh họa trong hình) - Chọn ô mà bạn cần kết quả
- Chuyển đến thanh công thức và nhập =TRANSPOSE(A1. A5)&” “
- Dựa trên cài đặt khu vực của bạn, bạn cũng có thể thử =A1. A5 (thay vì =TRANSPOSE(A1. A5))
- Chọn toàn bộ công thức và nhấn F9 (thao tác này chuyển đổi công thức thành giá trị).
- Loại bỏ các dấu ngoặc nhọn từ cả hai đầu
- Thêm =CONCATENATE( vào đầu văn bản và kết thúc bằng dấu ngoặc tròn ).
- nhấn nút Enter
Lưu ý rằng trong trường hợp này, tôi đã sử dụng ký tự khoảng trắng làm dấu phân cách (dấu phân cách). Nếu muốn, bạn có thể sử dụng các dấu phân cách khác như dấu phẩy hoặc dấu gạch nối CONCATENATE Phạm vi Excel (Sử dụng VBA)Dưới đây là một ví dụ về hàm tùy chỉnh mà tôi đã tạo bằng VBA (tôi đặt tên là CONCATENATEMULTIPLE) cho phép bạn kết hợp nhiều ô cũng như chỉ định dấu phân cách/dấu phân cách ![Excel Concatenate Ranges - custom function]()
Đây là mã VBA sẽ tạo chức năng tùy chỉnh này để kết hợp nhiều ô Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Result As String
For Each Cell In Ref
Result = Result & Cell.Value & Separator
Next Cell
CONCATENATEMULTIPLE = Left(Result, Len(Result) - 1)
End Function Dưới đây là các bước để sao chép mã này trong Excel - Chuyển đến Tab Nhà phát triển và nhấp vào biểu tượng Visual Basic (hoặc sử dụng phím tắt Alt + F11)
![Excel Concatenate Ranges multiple cells - Visual Basic]() - Trong VB Editor, nhấp chuột phải vào bất kỳ đối tượng nào và đi đến Chèn và chọn Mô-đun
![Inserting a module to create function to combine multiple cells into a single cell]() - Sao chép dán đoạn mã trên vào cửa sổ mã mô-đun
- Đóng Trình soạn thảo VB
Nhấn vào đây để tải về tập tin ví dụ Giờ đây, bạn có thể sử dụng hàm này như bất kỳ hàm trang tính thông thường nào trong Excel CONCATENATE Phạm vi Excel Sử dụng Hàm TEXTJOIN (có sẵn trong Excel với đăng ký Office 365)Trong Excel đi kèm với Office 365, một chức năng mới – TEXTJOIN – đã được giới thiệu Hàm này, đúng như tên gọi, có thể kết hợp văn bản từ nhiều ô thành một ô duy nhất. Nó cũng cho phép bạn chỉ định một dấu phân cách Đây là cú pháp của hàm TEXTJOIN(dấu phân cách, ign_empty, text1, [text2],…) - dấu phân cách – đây là nơi bạn có thể chỉ định dấu phân cách (dấu phân cách của văn bản). Bạn có thể nhập thủ công hoặc sử dụng tham chiếu ô có dấu phân cách
- ignore_empty – nếu giá trị này là TRUE, nó sẽ bỏ qua các ô trống
- text1 – đây là đoạn văn bản cần nối. Nó có thể là một chuỗi văn bản hoặc một mảng các chuỗi, chẳng hạn như một dải ô
- [text2] – đây là đối số tùy chọn trong đó bạn có thể chỉ định tối đa 252 đối số có thể là chuỗi văn bản hoặc phạm vi ô
Đây là một ví dụ về cách hoạt động của hàm TEXTJOIN ![Concatenate Excel Ranges using the Textjoin function]()
Trong ví dụ trên, một ký tự khoảng trắng được chỉ định làm dấu phân cách và nó kết hợp các chuỗi văn bản trong A1. A5 Bạn có thể đọc thêm về hàm TEXTJOIN tại đây Bạn đã gặp những tình huống mà điều này có thể hữu ích? . Đừng để lại dấu chân của bạn trong phần bình luận Bạn cũng có thể thích các hướng dẫn Excel sau đây - Cách hợp nhất các ô trong Excel đúng cách
- Cách tìm các ô đã hợp nhất trong Excel (và loại bỏ chúng)
- Cách chuyển đổi dữ liệu nhanh chóng trong Excel
- Cách chia dữ liệu bằng văn bản thành cột
- Hướng dẫn cơ bản để tìm và loại bỏ các bản sao trong Excel
![Excel Ebook Subscribe]()
SÁCH EXCEL MIỄN PHÍ Nhận Ebook 51 mẹo Excel để tăng năng suất và hoàn thành công việc nhanh hơn Tên E-mail CÓ - GỬI SÁCH ĐIỆN TỬ CHO TÔI 54 suy nghĩ về "CONCATENATE Phạm vi Excel (có và không có dấu phân cách)"alexander cordova cảm ơn bạn rất nhiều. từ Philippines William Vì một số lý do, công thức =@CONCATENATEMULTIPLE(A1. A5,” “) OR =CONCATENATEMULTIPLE(A1. A5,” “) trong bảng tính của riêng tôi. Tôi nhận được lỗi #NAME. Chỉ hoạt động trong ví dụ có thể tải xuống của bạn – Tại sao điều này lại xảy ra? Gaurav Khurana textjoin thật tuyệt vời R JORDAN Cảm ơn bạn rất nhiều vì mẹo này. Như những người khác đã nói, nó giải quyết vấn đề thời gian =CONCANT
Tôi sử dụng điều này hàng ngày trong Maximo để biến một cột gồm các WO # thành một hàng có dấu phẩy và dấu cách giữa mỗi cột. Sau đó tôi có thể dán chúng vào một chương trình để sửa đổi gatechaero77 Textjoin = Không còn sự tra tấn Ghép nối trên nhiều phép nối theo cột. Thanks Harleen Wow điều này cực kỳ hữu ích, giúp tôi tiết kiệm RẤT NHIỀU thời gian. Cảm ơn người đàn ông Kenzie Macro của bạn vừa cứu mạng tôi. Cảm ơn bạn eli I. Yêu và quý. Bạn Karlo hỷ
Ai đó có thể giúp tôi với công thức này hoặc tương tự xin vui lòng? Tôi muốn sử dụng texjoin cho một hàng chỉ có kết quả trong các ô không trống, nhưng tôi nhận được kết quả bằng số 0
và tôi cũng phải kết hợp tên cột với dữ liệu được tìm thấy trong ô cụ thể tên1 Tên2 Tên3
hàng 0 1 2 =1 Tên2, 2 tên3 điều đó có thể không?
tnx Nikola_Grujicic_Excel Trong trường hợp bạn có giá trị
Tên1 Tên2 Tên3
0 1 2
trong phạm vi A1. C2, bạn có thể chèn công thức
=IF(A2=0,””,TEXTJOIN(” “,,A2,A1))
vào ô A3 và điền vào hai ô bên phải
Sau đó, bạn sẽ nhận được
(Ô trống),1 Tên2, 2 Tên3
trong các ô A3. C3
Narasimha R Casturi Cảm ơn rất nhiều. Nó hoạt động rất tốt. Tôi có 1000 của nhiều “. ” các hàng được phân cách và chức năng này đã làm rất tốt. Chúa phù hộ bạn Joshua Cảm ơn, rất hữu ích. Một gợi ý là loại bỏ số lượng ký tự trong dấu phân cách (bao gồm các dấu phân cách có nhiều ký tự, e. g. dấu phẩy với khoảng trắng, như “,“) CONCATENATEMULTIPLE = Trái(Kết quả, Len(Kết quả) – Len(Dấu phân cách)) Kaanch Đây là một giải pháp tổng quát hơn cho bản sửa lỗi ngăn cách chuỗi trống (“”) cụ thể của Rowan. làm tốt lắm và cảm ơn vì đã chia sẻ Lưu ý, vì một số lý do trong Excel MVBA 7. 1, tôi không thể áp dụng mã này trực tiếp. Tôi đã phải áp dụng phép toán đơn giản từ tham số thứ hai trong Len() để trình soạn thảo VB chấp nhận mã. Tôi. e resLen = Len(Kết quả)
sepLen = Len(Dấu phân cách)
tmpLen = resLen – sepLen CONCATENATEMULTIPLE = Trái(Kết quả, tmpLen)
Nhĩ Hán Cảm ơn rất nhiều về mã và lời giải thích. Chức năng tuyệt vời 🙂 Santosh Kumar Xin chào, đang tìm mã VBA để nối toàn bộ hàng như (A1. A25 trong A26). cách dễ dàng để làm điều đó là gì… Andrew Rất cám ơn vì Nhiều tùy chọn – được đánh giá cao thanh lương trà Concatenatemultiple thật tuyệt vời. Hạn chế duy nhất mà tôi nhận thấy là nếu bạn không có dấu phân cách (sử dụng “”) thì nó sẽ cắt bỏ giá trị cuối cùng của văn bản. Vì vậy, với một chức năng if nhỏ, nó đã được sửa
Nếu Dấu phân cách = “” Thì
CONCATENATEMULTIPLE = Left(Result, Len(Result))
Khác
CONCATENATEMULTIPLE = Left(Result, Len(Result) – 1)
kết thúc nếu Kaanch Cảm ơn đã cung cấp bản sửa lỗi. Tôi chạy vào đây
SD Mã CONCATENATEMULTIPLE hoạt động tốt. Còn khi số lượng ô cần nối có thể thay đổi thì sao? Sufiyan Xin chào, Có ai biết một cách để làm như sau Nối các giá trị của một số ô thành một ô duy nhất và phân tách chúng bằng bất kỳ dấu phân cách nào bạn chọn Tên dự án Kết quả
Project1 Mike Project1, Mike, Neal, Peter
Dự án1 Neal
Dự án1 Peter
Project2 Mike Project2, Mike, Neal, Peter
Dự án Neal2
Dự án2 Peter Nikola_Grujicic_Excel Điều này có thể được thực hiện đơn giản hơn nhiều với VBA, nhưng cũng có nhiều giải pháp với các công thức được nhúng trong Excel
Giả sử bạn có các giá trị sau trong cột A
Dự án1 Mike
Dự án1 Neal
Dự án1 Peter
Dự án2 Mike
Dự án Neal2
Dự án2 Peter
Đầu tiên, thêm một hàng trên tất cả và để trống, để nội dung bắt đầu bằng ô A2
Thứ hai, thêm công thức =IF(ROW()=2,LEFT(A2,8)&”, “&RIGHT(A2,LEN(A2)-8-1),IF(LEFT(A2,8)=LEFT(A1,
Để xóa bổ sung, hãy thêm công thức =IF(ISNUMBER(FIND(B2,B3)),””,B2) vào ô C2 và điền vào cột C
Việc thay đổi dấu phân cách có thể được thực hiện bằng cách thay đổi mọi dấu “,” trong công thức đầu tiên
Nếu từ được chia sẻ của bạn không phải là “Project1”, bạn sẽ phải thay đổi cứ sau 8 (độ dài của từ Project1) trong công thức đầu tiên bằng độ dài của một từ khác
Brian Zammit Cảm ơn, chức năng nhỏ xinh. Cũng được điều chỉnh để sử dụng giá trị 'Len(Dấu phân cách)' Hàm CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String Làm mờ ô theo phạm vi
Kết quả mờ dưới dạng chuỗi Đối với mỗi ô trong tham chiếu
Kết quả = Kết quả & Ô. Giá trị & dấu tách
ô tiếp theo CONCATENATEMULTIPLE = Trái(Kết quả, Len(Kết quả) – Len(Dấu phân cách)) chức năng kết thúc David Đây là một bài viết tuyệt vời và sẽ giúp tôi tiết kiệm rất nhiều thời gian. Chỉ cần một truy vấn, làm cách nào tôi có thể thiết lập mã VBA để bỏ qua các mục nhập #N/A? . Thanks… Shaun =IFNA(TEXTJOIN(… Shaun Tôi nghĩ rằng tôi nên làm rõ đề nghị của tôi
=IFNA(TEXTJOIN(” “,0,$A$1. $A$6),””)
Thật tuyệt khi loại bỏ #N/A nếu chúng xuất hiện
Có các tài liệu tham khảo về cách sử dụng nó trên các trang web khác, nhưng về cơ bản, nó là một câu lệnh IF được kích hoạt khi #N/A là đúng, nếu không thì “”. Bạn có thể thay thế bất cứ thứ gì bạn muốn sang phần thứ hai thay vì “”. TEXTJOIN này thực sự hữu ích và tôi đã sử dụng nó kể từ khi tôi tình cờ thấy nó
gabor Có lẽ bạn có thể triển khai chức năng TEXTJOIN từ Bảng tính Google. Đây là triển khai của tôi Hàm TEXTJOIN(dấu phân cách Dưới dạng Chuỗi, bỏ quaEmpty dưới dạng Boolean, Tham chiếu dưới dạng Phạm vi) Dưới dạng Chuỗi
Dim i dưới dạng số nguyên
Làm mờ tmp dưới dạng chuỗi
Đối với mỗi ô trong tham chiếu
Nếu (Ô. Giá trị “”) Sau đó
tmp = tmp & Ô. Giá trị & dấu tách
kết thúc nếu
ô tiếp theo
TEXTJOIN = Left(tmp, Len(tmp) – Len(dấu phân cách))
chức năng kết thúc Ánh sáng tối Cảm ơn vì những giải pháp này
Tôi muốn thay đổi phương thức VBA để tránh các ô trống (tôi. e. để người dùng có thể chọn toàn bộ cột, nhưng chỉ ghép các ô không trống)
Tôi biết nó không tầm thường - nhưng nó sẽ giúp biến chức năng này trở nên mạnh mẽ hơn Koen Stabel Xin chào, Có ai biết một cách để làm như sau Tôi muốn kết hợp hoặc nối văn bản trong mọi ô trong cột A với mọi ô trong cột B mà không cần lặp lại hoặc điền nhanh vì điều đó sẽ không hiệu quả
Ví dụ ở đây CỘT A chứa
A
B
C CỘT B chứa
10
20
30
40 Những gì tôi muốn làm đầu ra trong một CỘT khác
A10
A20
A30
A40
B10
B20
B30
B40
C10
C20
C30
C40 Bất cứ ai một ý tưởng làm thế nào để làm điều này? Chad Demick Tôi đang tìm cách làm điều tương tự. Bạn đã tìm ra cách để làm điều này? Nikola_Grujicic_Excel Một trong những giải pháp không có VBA được gửi ở trên Nikola_Grujicic_Excel Một giải pháp khác không có VBA là
Nhập công thức =COUNTIF(A. A,”?*”) vào ô C1 (đếm số ô có văn bản trong cột A)
Nhập công thức =COUNTIF(B. B,”>0″) vào ô C2 (đếm số ô có số >=0 trong cột B)
Nhập công thức =INDIRECT(ADDRESS(QUOTIENT(ROW()-1,$C$2)+1,1)) vào ô D1 và điền cho đến khi nó bắt đầu cho số 0
Nhập công thức =D1&INDIRECT(ADDRESS(COUNTIF(D$1. D1,D1),2)) vào ô E1 và điền xuống cho đến khi nó có các giá trị trong cột D. Giải pháp của bạn phải ở cột E
Tái bút. Công thức trong ô C1 được đưa ra chỉ cần tạo cặp với dữ liệu văn bản Nikola_Grujicic_Excel Giải pháp thứ ba không có VBA (trong một dòng) có thể được thực hiện với công thức =INDIRECT(ADDRESS(QUOTIENT(ROW()-1,COUNTIF(B. B,”>0″))+1,1))&INDIRECT(IF(MOD(ROW()),COUNTIF(B. B,”>0″))0,ADDRESS(MOD(ROW(),COUNTIF(B. B,”>0″)),2),ĐỊA CHỈ(COUNTIF(B. B,”>0″),2))) trong ô C1 và điền nó xuống cho đến khi nó bắt đầu đưa ra các giải pháp thừa. Trong trường hợp bạn đang ghép nối văn bản trong cột B, hãy thay đổi tất cả COUNTIF(B. B,”>0″) trong công thức với COUNTIF(B. B,”>0”) Nikola_Grujicic_Excel * Điều chỉnh
Trong trường hợp bạn đang ghép nối văn bản trong cột B, hãy thay đổi tất cả COUNTIF(B. B,”>0″) trong công thức với COUNTIF(B. B,”?*”)
Nikola_Grujicic_Excel Sao chép các giá trị từ cột A sang cột C, bắt đầu từ C2. C4. Sao chép các giá trị từ cột B sang cột D, bắt đầu từ D1, nhưng với Paste_Special>>Transpose, để mọi thứ bắt đầu giống như một bảng trống với các chữ cái cho hàng và số cho cột. Bây giờ, chọn ô D2 và nhập công thức =$C2&D$1 (các ký hiệu đô la phải ở những nơi đó). Bây giờ hãy điền công thức cho đến hết hàng và cột. Bây giờ chọn tất cả 12 giá trị và sao chép nó mà không di chuyển vùng chọn, sau đó dán>>paste_values. Bây giờ bạn phải đặt các giá trị trong một cột. Mở trang tính mới, sao chép 12 giá trị, chọn B1 và Paste_Special>>Transpose. Bây giờ hãy chèn công thức =IF(ROW()*1/4=INT(ROW()*1/4),ROW()*1/4,INT(ROW()*1/4)+1) vào ô A1. Điều quan trọng nhất là số 4, bởi vì nó có liên quan đến số hàng chứa các giá trị wit. ***Trong trường hợp 50 hàng có dữ liệu, công thức sẽ là =IF(ROW()*1/50=INT(ROW()*1/50),ROW()*1/50,INT(ROW()* . *** Điền công thức (với số bốn) 12 lần (vì bạn có 12 giá trị). Bây giờ hãy chèn công thức =INDIRECT(ADDRESS(ROW()-(A5-1)*4,COLUMN()+(A5-1),4)) vào ô B5 và điền xuống ô B12. Điều quan trọng nhất trong công thức này là số 4 ĐẦU TIÊN có vai trò giống như trước đó và A5 ở đó vì chúng tôi đặt công thức trong B5. ***Trường hợp 50 hàng công thức dữ liệu sẽ là =INDIRECT(ADDRESS(ROW()-(A51-1)*50,COLUMN()+(A51-1),4)) *** Số 4 ở cuối . *** Cột B là một trong những giải pháp không dùng VBA
Cornan Rõ ràng TEXTJOIN được liên kết với Office 365, KHÔNG phải Excel 2016 TEXTJOIN() KHÔNG khả dụng trên phiên bản Excel 2016 dành cho máy tính để bàn của tôi Sumit Bansal Này Cornan. Bạn đúng. Tôi đã chỉnh sửa hướng dẫn cho phù hợp adriana galian Xin chào Sumit, tôi cần mẫu theo dõi rời khỏi năm 2018. Bạn có thể chia sẻ phiên bản excel đã cập nhật cho adriana không. galiano@gmail. com
James Golding Cảm ơn vì điều này, sự thay thế hoàn hảo cho công thức MCONCAT từ phần bổ trợ morefunc hiện không còn tồn tại (dành cho bất kỳ ai trên HĐH 64 bit) Shakeel Ahmed Mát lạnh. Tôi đã tìm kiếm để tự động cập nhật nối. Cảm ơn mã VBA Kevin Điều này thật tuyệt - cảm ơn Sẽ đề xuất một thay đổi đối với mã VBA - thay vì sử dụng
CONCATENATEMULTIPLE = Left(Result, Len(Result) – 1), bạn có thể sử dụng
CONCATENATEMULTIPLE = Left(Kết quả, Len(Kết quả) – Len(Dấu phân cách)); gabor … và thậm chí cho phép một chuỗi rỗng làm dấu phân cách. Một lỗi nhỏ khó chịu 😀
Julius Strickland Điều này đã hoàn toàn cứu tôi hôm nay khi tạo bảng tính danh sách liên hệ để nhập ở nơi khác Rơ Lan Đỏ Xuất sắc. Điều này đã làm chính xác những gì tôi cần khi kết hợp với Dải động Jason Simons Có một lỗi trong mã. Excel Concatenate không bằng CONCATENATEMULTIPLE bạn nên đặt CONCATENATEMULTIPLE = Alok Morya Cảm ơn vì đã chia sẻ thủ thuật tuyệt vời như vậy
Làm thế nào để bạn làm điều ngược lại, xin vui lòng đề nghị Sumit Bansal Xin chào Alok. Nói ngược lại, ý bạn là tách những từ này thành các ô riêng biệt? . Hãy xem #7 trong này – http. // trumpexcel. com/2014/08/clean-data-in-excel/
Thiyagarajan Rất Rất tiết kiệm thời gian & Thú vị anh em, Thủ thuật hay Sumit Bansal Cảm ơn đã bình luận. Rất vui vì bạn thích nó 🙂
súng Bạn có thể thêm tài liệu mẫu? Thanks… trời ơi đất hỡi Đó là một giải pháp thực sự mát mẻ. Sẽ tiết kiệm rất nhiều thời gian. Phương pháp nâng cao hơn tự động loại bỏ dấu ngoặc nhọn là gì? Sumit Bansal Cách nâng cao hơn sẽ là sử dụng hai ô. Trong một ô, bạn sẽ sử dụng phím F9 và nhận các giá trị được mã hóa cứng, và trong một ô khác, bạn có thể có một công thức tự động xóa dấu ngoặc nhọn (sử dụng thay thế/thay thế). Bạn có thể đi theo cách đó nếu bạn muốn điều này trở nên năng động một phần. Nhưng tôi muốn nói rằng cách được đề cập trong bài viết là cách dễ dàng và nhanh hơn
Có công thức tách trong Excel không?
Có sự ngược lại của phép nối trong Excel không?
Ngược lại với nối trong Excel là chia nội dung của một ô thành nhiều ô . Điều này có thể được thực hiện theo một vài cách khác nhau. Tính năng chuyển văn bản thành cột. Tùy chọn Flash Fill trong Excel 2013 trở lên.
|
