Hàm concatenate có điều kiện

-
2 Hàm CONCATENATE:3 Những điều cần nhớ khi sử dụng CONCATENATE vào Excel5 Làm nuốm nào nhằm nối một mảng ô trong Excel:

Trong lí giải này, các bạn sẽ học những cách khác nhau để nối những chuỗi, những ô, những mảng, các cột và các hàng trong Excel bằng cách sử dụng hàm CONCATENATE cùng toán tử “&” .Bạn đã xem: Hàm concatenate bao gồm điều kiện

Trong bảng tính Excel của bạn, dữ liệu chưa hẳn lúc nào thì cũng được cấu trúc theo yêu cầu của bạn. Thường thì bạn có thể muốn chia ngôn từ của một ô thành những ô, hoặc làm trái lại – kết hợp dữ liệu từ nhì cột trở lên vào một trong những cột đơn. Ví dụ thông dụng về việc phải ghép nối vào Excel là nhập tên và địa chỉ, phối hợp văn phiên bản với quý giá được triết lý bằng công thức, hiển thị ngày cùng giờ với định dạng mong muốn muốn, để đặt tên,..

Bạn đang xem: Hàm concatenate có điều kiện

“Concatenate” trong Excel là gì?

Về bản chất, có hai cách để kết hợp tài liệu trong bảng tính Excel:


*

*

Hợp nhất những ôKết nối những giá trị của ô.

Khi bạn gộp các ô, tức là hợp nhất hai hoặc những ô vào một ô đối chọi lẻ. Kết trái là các bạn có một ô to hơn được hiển thị trên các hàng cùng / hoặc cột vào bảng tính.

Khi bạn nối các ô trong Excel, các bạn chỉ ghép nội dung của các ô đó. Nói bí quyết khác, ghép vào Excel là thừa trình kết hợp hai hoặc các giá trị với nhau. Phương pháp này hay được sử dụng để phối hợp một vài ba đoạn văn bạn dạng nằm trong những ô khác biệt (về mặt kỹ thuật, chúng được điện thoại tư vấn là text strings hoặc simply strings đơn giản) hoặc chèn một quý giá được giám sát và đo lường bằng công thức ở giữa một trong những văn bản.

Hình dưới đây thể hiện tại sự khác hoàn toàn giữa hai phương pháp này:

*

Kết hợp các ô vào Excel là chủ thể của nội dung bài viết tiếp theo của chúng ta, với trong gợi ý này, chúng ta sẽ tất cả hai cách giải quyết và xử lý để nối các chuỗi trong Excel – bằng cách sử dụng hàm CONCATENATE và toán tử  & .

Đăng ký ngay: khóa học Excel văn phòng 1 thầy 1 trò

Hàm CONCATENATE:

Hàm CONCATENATE trong Excel có phong cách thiết kế để nối các đoạn văn phiên bản khác nhau lại hoặc phối kết hợp các giá chỉ trị xuất phát điểm từ một vài ô vào một ô.

Cú pháp của CONCATENATE như sau:

CONCATENATE (text1, , …)

Trong đó text là một chuỗi văn bản, tham chiếu ô hoặc cực hiếm trong công thức.

Dưới đây là một vài ba ví dụ về kiểu cách sử dụng hàm CONCATENATE trong Excel.

Kết nối những giá trị của một trong những ô:

Công thức CONCATENATE dễ dàng và đơn giản nhất để phối kết hợp các cực hiếm của ô A1 với B1 như sau:

=CONCATENATE(A1, B1)

Xin để ý rằng những giá trị sẽ tiến hành đan lại cùng nhau mà không có dấu phân tách, như trong sản phẩm 2 trong hình mặt dưới.

Để bóc các giá trị với khoảng trắng, hãy nhập “” vào đối số đồ vật hai, như trong dòng 3 trong ảnh chụp màn hình hiển thị dưới đây.

=CONCATENATE(A1, ” “, B1)


*

Chia sẻ tài liệu excel cơ bạn dạng hay nhất 2018

Kết nối chuỗi văn bạn dạng và giá trị di động:

Không bao gồm điều gì hạn chế hàm CONCATENATE chỉ nối quý giá của ô. Bạn có thể sử dụng nó nhằm nối những chuỗi văn phiên bản khác nhau để triển khai cho công dụng có ý nghĩa sâu sắc hơn. Ví dụ:

=CONCATENATE(A1, ” “, B1, ” completed”)

Công thức trên cho những người dùng biết rằng công việc nào đó đã hoàn thành, như trong hàng nhị trong hình dưới đây. Xin để ý rằng họ thêm một dấu biện pháp trước chữ ” completed ” để tách các chuỗi văn bản được nối.

Đương nhiên, bạn cũng có thể thêm một chuỗi văn bản ngay từ trên đầu hoặc làm việc giữa cách làm Concatenate của bạn:

=CONCATENATE(“See “, A1, ” “, B1)

Một khoảng trống (“”) được chế tạo giữa những giá trị kết hợp, để hiệu quả hiển thị như là “Project1 ” chứ không phải là “Project1 “.


*

Kết nối một chuỗi văn bạn dạng và một cực hiếm từ trong bí quyết tính toán

Để có tác dụng cho kết quả trả lại bằng một trong những công thức dễ dàng nắm bắt hơn cho tất cả những người dùng, chúng ta cũng có thể nối nó với 1 chuỗi văn bản để giải thích giá trị đích thực là gì.

Ví dụ, bạn cũng có thể sử dụng công thức dưới đây để trả lại ngày hiện tại:

=CONCATENATE(“Today is “,TEXT(TODAY(), “dd-mmm-yy”))

 

Những điều cần nhớ khi sử dụng CONCATENATE trong Excel

Để bảo đảm an toàn rằng các công thức CONCATENATE của bạn luôn sở hữu lại công dụng chính xác, hãy nhớ những quy tắc dễ dàng và đơn giản sau đây:

CONCATENATE yêu thương cầu ít nhất một “text” đối số để triển khai việc.Trong một công thức CONCATENATE, chúng ta cũng có thể nối ghép lên tới mức 255 chuỗi, tổng số là 8,192 ký kết tự.Kết quả của hàm CONCATENATE luôn vẫn là một chuỗi văn bản, ngay cả khi toàn bộ các giá trị nguồn là các con số.CONCATENATE không nhận thấy các mảng. Mỗi tham chiếu ô buộc phải được liệt kê riêng. Ví dụ, chúng ta nên viết =CONCATENATE(A1, A2, A3) gắng vì =CONCATENATE(A1:A3).Nếu ít nhất một trong những đối số của hàm CONCATENATE không hợp lệ, công thức sẽ trả về lỗi #VALUE!.

Dùng “&” nhằm kết nối các chuỗi vào Excel:

Trong Microsoft Excel, toán tử & là một trong những cách khác để nối những ô. Phương pháp này rất hữu dụng trong những trường hợp cũng chính vì việc gõ (&) nhanh hơn những so với gõ “concatenate”.

Tương từ với hàm CONCATENATE, bạn có thể sử dụng “&” vào Excel để kết hợp các chuỗi văn bản khác nhau, quý giá của ô và kết quả trả về bởi các hàm khác.

Ví dụ về “&”.

Để xem toán tử nối vẫn hoạt động, hãy viết lại những công thức CONCATENATE đã trao đổi ở trên:

Kết nối các giá trị trong A1 cùng B1:

=A1&B1

Kết nối các giá trị trong A1 và B1 với mức trống:

=A1&” “&B1

Kết nối những giá trị vào A1, B1 cùng ​​một chuỗi văn bản:

=A1 & B1 và ” completed”

Kết nối một chuỗi và hiệu quả của hàm TEXT / TODAY:

=”Today is ” và TEXT(TODAY(), “dd-mmm-yy”)

Như đã trình diễn trong ảnh chụp màn hình hiển thị bên dưới, hàm CONCATENATE và toán tử “&” trả hiệu quả giống hệt nhau:


“&” cùng với hàm CONCATENATE vào Excel

Nhiều người tiêu dùng tự hỏi rằng liệu gồm cách hiệu quả hơn nhằm nối các chuỗi vào hàm CONCATENATE hoặc toán tử “&” xuất xắc không.

Sự khác hoàn toàn duy nhất cần thiết giữa hàm CONCATENATE với “&” là hàm CONCATENATE có số lượng giới hạn 255 còn và thì ko có. Ngoài ra, không tồn tại gì khác nhau giữa hai phương thức nối này, cũng không tồn tại sự biệt lập về hiệu lực thực thi giữa các công thức CONCATENATE cùng “&”.

Vì 255 là cũng một con số thực sự khủng và vào thực tế, rất có thể không lúc nào dùng không còn khi phối kết hợp nhiều chuỗi, sự khác hoàn toàn còn lại là sự dễ chịu và tiện lợi hơn. Một số người tiêu dùng thấy công thức CONCATENATE đọc dễ hơn, cá thể tôi ưa thích sử dụng phương pháp “&”. Vì vậy, chỉ việc dùng chuyên môn nối mà chúng ta cảm thấy dễ chịu hơn.

Xem thêm: Cách Thay Đổi Màu Áo Trong Photoshop Cách Đổi Màu Áo, Background Và Chữ Mới

Kết nối những ô bằng dấu cách, vết phẩy và những ký trường đoản cú khác:

Kết nối nhị ô cùng với một khoảng trắng:

=CONCATENATE(A1, ” “, B1) hoặc là =A1 và ” ” & B1

Kết nối nhị ô với dấu phẩy:

=CONCATENATE(A1, “, “, B1) hoặc là =A1 và “, ” và B1

Kết nối hai ô cùng với một vết nối:

=CONCATENATE(A1, “-“, B1) hoặc là =A1 và “-” và B1

Hình tiếp sau đây thể hiện công dụng sẽ như vậy nào:


Kết nối những chuỗi văn bạn dạng với các xuống dòng

Thông thường, bạn sẽ tách những chuỗi văn bạn dạng được nối với các dấu chấm câu với dấu cách, như biểu đạt trong lấy ví dụ như trước. Tuy vậy, Trong một vài trường hợp, rất có thể cần phải tách bóc các giá bán trị bằng một ngắt dòng. Một ví dụ phổ cập là phải hợp nhất địa chỉ gửi thư từ dữ liệu trong các cột riêng biệt.

Một vấn đề xẩy ra là bạn không thể đơn giản chỉ gõ một ngắt dòng trong phương pháp như một ký kết tự bình thường và vì thế cần một hàm CHAR cùng với mã ASCII tương ứng cho phương pháp nối:

Trên Windows, thực hiện CHAR (10) trong những số đó 10 là mã ASCII để xuống dòng .Trên khối hệ thống Mac, thực hiện CHAR (13) trong đó 13 là mã ASCII .

Trong lấy một ví dụ này, bọn họ có các phần địa chỉ trong những cột A đến F, và chúng ta ghép chúng lại cùng với nhau trong cột G bằng cách sử dụng toán tử nối “&”. Các giá trị được hợp nhất được chia cách bằng dấu phẩy (“,”), dấu bí quyết (“”) cùng dấu xuống cái CHAR (10):

=A2 và ” ” & B2 & CHAR(10) và C2 & CHAR(10) và D2 & “, ” & E2 và ” ” và F2


Chú ý. Khi áp dụng ngắt mẫu để tách bóc các giá trị được nối, bạn phải có tùy lựa chọn “Wrap text ” chất nhận được kết trái hiển thị bao gồm xác. Để tiến hành việc này, hãy nhấn Ctrl + 1 để mở hộp thoại Format Cells, gửi sang tab Alignment và kiểm tra hộp Wrap text.

Theo phương pháp tương tự, chúng ta có thể tách biệt chuỗi nối với những ký tự không giống như:

Dấu ngoặc kép (“) – CHAR (34)Chéo chéo (/) – CHAR (47)Dấu sao (*) – CHAR (42)Danh sách khá đầy đủ các mã ASCII có thể dễ dàng tìm thấy.

Mặc cho dù thế, tất cả một cách tiện lợi hơn để chèn các ký tự hoàn toàn có thể in được trong cách làm nối là chỉ cần gõ chúng vào dấu ngoặc kép như họ đã làm trong ví dụ trước .

Dù bằng cách nào, toàn bộ bốn công thức dưới đây mang lại hiệu quả giống hệt nhau:

=A1 & CHAR(47) & B1

=A1 và “/” & B1

=CONCATENATE(A1, CHAR(47), B1)

=CONCATENATE(A1, “/”, B1)


Làm cầm cố nào nhằm nối các cột trong Excel:

Để nối nhị cột trở lên trong Excel, bạn chỉ cần nhập bí quyết nối thông thường vào ô đầu tiên và sau đó coppy nó xuống các ô khác bằng phương pháp kéo dấu cộng (hình vuông nhỏ dại xuất hiện ở góc dưới bên nên của ô Chọn).

Ví dụ, để nối nhị cột (cột A với B) tách bóc các giá bán trị với tầm trắng, chúng ta nhập cách làm sau vào ô C2, và sau đó sao chép nó xuống các ô khác. Khi chúng ta kéo xuống để coppy công thức, nhỏ trỏ loài chuột sẽ chuyển đổi thành hình chữ thập, như biểu hiện trong hình bên dưới:


Mẹo. Một cách nhanh chóng để coppy công thức xuống những ô không giống trong cột là di chuột mang lại ô bao gồm công thức và nhấp lưu ban vào dấu cộng màu đen .

Xin để ý cách Microsoft Excel xác định xào luộc các ô mang lại đâu sau khoản thời gian nhấp đúp chuột ở những ô gồm công thức của bạn. Nếu gồm có ô trống trong bảng của bạn, giả sử là ô A6 và B6 trống trong ví dụ này, thì phương pháp sẽ chỉ được xào luộc đến sản phẩm 5. Trong trường phù hợp này, bạn sẽ cần yêu cầu kéo xuống thủ công bằng tay để nối toàn bộ các cột.

Nên xem: khóa học excel nâng cao Hà Nội

Làm vắt nào để nối một mảng ô vào Excel:

Kết hợp các giá trị từ nhiều ô hoàn toàn có thể mất các công hơn vị hàm CONCATENATE không gật đầu đồng ý các mảng với yêu cầu một tham chiếu ô duy nhất trong những đối số.

Để ghép một trong những ô, lấy ví dụ như A1 tới A4, bạn cần một trong hai bí quyết sau:

=CONCATENATE(A1, A2, A3, A4)

hoặc là

=A1 và A2 và A3 & A4

Khi nối một phạm vi nhỏ, thì nhập toàn bộ các ô tham chiếu trong công thức cũng chưa hẳn vấn đề lớn. Nhưng với nhiều ô thì nhập từng ô theo cách bằng tay sẽ vô cùng mất công. Dưới đây bạn sẽ tìm thấy 3 phương thức ghép nối mau lẹ trong Excel.

Phương pháp 1. Nhận CTRL nhằm chọn các ô nhằm nối

Để cấp tốc chóng chọn 1 số ô, chúng ta cũng có thể nhấn phím CTRL với nhấp vào cụ thể từng ô mà bạn có nhu cầu nối trong bí quyết CONCATENATE. Dưới trên đây là các bước chi tiết:

Chọn một ô nơi bạn muốn nhập công thức.Nhập = CONCATENATE (trong ô đó hoặc trong thanh công thức.Nhấn và giữ CTRL và nhấp vào cụ thể từng ô mà bạn có nhu cầu ghép lại.Nhả nút CTRL, gõ vết ngoặc đóng trong thanh công thức và thừa nhận Enter.


Chú ý. Khi sử dụng cách thức này các bạn phải nhấp vào từng ô riêng biệt lẻ. Việc chọn một dải bởi chuột sẽ thêm một mảng vào công thức, với hàm CONCATENATE ko chấp nhận.

Phương pháp 2. Sử dụng hàm TRANSPOSE để đưa phạm vi

Khi bạn cần ghép một dải rộng lớn lớn bao hàm hàng chục hoặc hàng ngàn ô, phương thức trước đó không được nhanh do nó đòi hỏi phải nhấp vào từng ô. Trong trường hòa hợp này, bao gồm cách giỏi hơn là sử dụng hàm TRANSPOSE để trả về một mảng, và tiếp đến thay nỗ lực nó bằng các tham chiếu ô riêng lẻ.

Chọn ô mà bạn có nhu cầu xuất ra dải ô nối.Nhập công thức TRANSPOSE trong ô đó, ví dụ =TRANSPOSE(A1:A10).Trong thanh công thức, dấn F9 để sửa chữa công thức với các giá trị được tính toán.Xoá dấu ngoặc nhọn trở thành một bí quyết Excel thường thì thành công thức mảng . Kết trái là, bạn sẽ có toàn bộ các ô tham chiếu để nối trong cách làm của bạn.


Gõ = CONCATENATE (ở phía trước của các tham chiếu ô trong thanh công thức, gõ lốt ngoặc đóng và nhấn Enter.

 

Chú ý. Cho dù các bạn sử dụng phương pháp nào, cực hiếm nối vào C1 là một trong những chuỗi văn bản (chú ý tài liệu được canh lề trái vào ô), mặc dù mỗi giá bán trị ban đầu là một số. Điều này là vì hàm CONCATENATE luôn trả về một chuỗi văn bản bất nhắc loại dữ liệu nguồn.

Concatenate với số cùng ngày mon ở các định dạng không giống nhau:

Khi các bạn nối một chuỗi văn bạn dạng với một số trong những hoặc ngày, chúng ta cũng có thể muốn định dạng hiệu quả khác nhau tùy nằm trong vào tập dữ liệu của bạn. Để làm điều này, nhúng những hàm TEXT trong công thức Excel của bạn.

Hàm TEXT (value, format_text) tất cả hai đối số:

Trong đối số đầu tiên ( value), bạn cung cấp một số hoặc ngày nhằm được biến hóa sang văn bạn dạng hoặc tham chiếu mang lại ô bao gồm chứa một giá trị số.Trong đối số máy hai ( format_text ), chúng ta nhập định dạng ước muốn các mã để hàm TEXT có thể hiểu.

Tôi sẽ chỉ nhắc các bạn rằng khi phối hợp một chuỗi văn bạn dạng và ngày tháng , bạn phải thực hiện hàm TEXT để hiển thị ngày làm việc định dạng ý muốn muốn. Ví dụ:

=CONCATENATE(“Today is “, TEXT(TODAY(), “mm/dd/yy”))

hoặc là

=”Today is ” và TEXT(TODAY(), “mm/dd/yy”)

Một vài lấy một ví dụ công thức bổ sung nối tiếp một giá trị văn phiên bản và số sau:

=A2 & ” ” và TEXT(B2, “$#,#0.00”) – hiển thị số cùng với 2 chữ số thập phân với dấu $.

=A2 và ” ” & TEXT(B2, “# ?/???”) – hiển thị số dưới dạng một phân số.


Để rất có thể ứng dụng tốt Excel vào trong công việc, họ không chỉ nắm rõ được các hàm mà còn đề nghị sử dụng xuất sắc cả những công cầm của Excel. Phần nhiều hàm cải thiện giúp áp dụng giỏi vào quá trình như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những quy định thường áp dụng là Data validation, Conditional formatting, Pivot table…

Toàn cỗ những kỹ năng và kiến thức này các bạn đều rất có thể học được trong số khóa học của Học Excel Online. Chúc bàn sinh hoạt tốt