Dùng dấu "&" nối text và hàm IF như thế nào cho hiệu quả trong trường hợp sau? (2 người xem)

[longlongago]

Kính gửi các anh/chị,

Em đang có một vấn đề này mà chưa giải đáp đc. Em đang cần combine text của các ô với nhau. Hiện tại em đang dùng hàm IF và dấu &. Tuy nhiên em mới chỉ xây dựng đc công thức cho 3 trường hơp. Nếu phải nối text của nhiều trường hợp hơn (8 chẳng hạn) thì em thấy công thức của em không hiệu quả, và dài dòng. Anh/chị vui lòng tham khảo trong file và giúp đỡ em giải quyết vấn đề này nhé. Em cảm ơn các anh/chị nhiều!

 

[ndu96081631]

Kính gửi các anh/chị,

Em đang có một vấn đề này mà chưa giải đáp đc. Em đang cần combine text của các ô với nhau. Hiện tại em đang dùng hàm IF và dấu &. Tuy nhiên em mới chỉ xây dựng đc công thức cho 3 trường hơp. Nếu phải nối text của nhiều trường hợp hơn (8 chẳng hạn) thì em thấy công thức của em không hiệu quả, và dài dòng. Anh/chị vui lòng tham khảo trong file và giúp đỡ em giải quyết vấn đề này nhé. Em cảm ơn các anh/chị nhiều!

Nối chuổi không phải là thế mạnh của các công thức Excel... Bạn chỉ có thể dùng VBA hoặc dùng cột phụ thội

 

[longlongago]

Em không ràng về VBA bác Du ạ. Bác có thể chỉ cho em cách dùng cột phụ được không ạ? Em cảm ơn bác nhiều!

 

[ptm0412]

Nếu 8 ô không có quá 3 ô trống liền kề thì dùng:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B6&"+"&B7&"+"&B8&"+"&B9&"+"&B10&"+"&B11&"+"&B12&"+"&B13;"++++";"+");"+++";"+");"++";"+")

Còn 4, 5, ô trống không kề nhau thì công thức này xài thoải mái.
Nhưng trống đầu trống đuôi cũng thua.

Nên dùng VBA hay hơn:

Function Connect(Rng As Range) As String
For Each cll In Rng
    If cll.Value <> "" Then
        Connect = Connect & "+" & cll.Value
    End If
Next
    Connect = Right(Connect, Len(Connect) - 1)
     If Right(Connect, 1) = "+" Then Connect = Left(Connect, Len(Connect) - 1)
   
End Function

 

[ndu96081631]

Nếu 8 ô không có quá 3 ô trống liền kề thì dùng:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B6&"+"&B7&"+"&B8&"+"&B9&"+"&B10&"+"&B11&"+"&B12&"+"&B13;"++++";"+");"+++";"+");"++";"+")

Còn 4, 5, ô trống không kề nhau thì công thức này xài thoải mái.
Nhưng trống đầu trống đuôi cũng thua.

Nên dùng VBA hay hơn:

Function Connect(Rng As Range) As String
For Each cll In Rng
    If cll.Value <> "" Then
        Connect = Connect & "+" & cll.Value
    End If
Next
    Connect = Right(Connect, Len(Connect) - 1)
     If Right(Connect, 1) = "+" Then Connect = Left(Connect, Len(Connect) - 1)
   
End Function

Có nối chuổi với cột A nữa sư phụ ơi! ---> Gần giống như SUMPRODUCT nhưng thay vì SUM thì cái này nối chuổi lại

 

[ptm0412]

Hic, mờ mắt rồi hay sao á.
Làm lại, ngang dọc gì cũng được.

Function Connect(Rng As Range, CriteriaRng As Range) As String
Dim RngArr, CritArr
Dim i As Long, j As Long
RngArr = Rng.Value
CritArr = CriteriaRng.Value
For i = LBound(CritArr, 1) To UBound(CritArr, 1)
    For j = LBound(CritArr, 2) To UBound(CritArr, 2)
        If CritArr(i, j) <> "" Then
            Connect = Connect & "+" & RngArr(i, j) & CritArr(i, j)
        End If
    Next
Next
    Connect = Right(Connect, Len(Connect) - 1)
End Function

 

[nguyenthi_bichvan83]

Hi bạn, mình thấy baì bạn đc ra cũng ko đến mức phức tạp.
1. Hàm If chỉ cho tối đa 7 lần dùng trong 01 công thức, nên bạn dùng đến if thứ 8 sẽ ko ra.
2. Mình ko cần phải dùng hàm isbank để xác định có dữ liệu ko????, khi bạn nối chuỗi , hàm sẽ tự động bỏ ra thôi.
Mình hy vọng sẽ giải quyết đc vấn đề.

 

[Ba Tê]

Hi bạn, mình thấy baì bạn đc ra cũng ko đến mức phức tạp.
1. Hàm If chỉ cho tối đa 7 lần dùng trong 01 công thức, nên bạn dùng đến if thứ 8 sẽ ko ra.
2. Mình ko cần phải dùng hàm isbank để xác định có dữ liệu ko????, khi bạn nối chuỗi , hàm sẽ tự động bỏ ra thôi.
Mình hy vọng sẽ giải quyết đc vấn đề.

Chả hiểu muốn nói gì? Muốn nói đến bài nào? 2 giải pháp đó dùng cho trường hợp nào?
"Hổng dám đâu, em còn phải học bài"
Híc! Cò Ơi! Cứu tôi với!
-------- ("Phia rồi" (Bắt chước PTM...)

 

[ptm0412]

Chả hiểu muốn nói gì? Muốn nói đến bài nào? 2 giải pháp đó dùng cho trường hợp nào?

Đó là nói đến bài 1: công thức 8 if cho 8 dòng. (Mới 12:18 mà phia? 1, 2 giờ mới gọi là phia)
Vấn đề là:
1. Nếu chịu đọc công thức sẽ thấy là 8 If riêng rẽ chứ không phải 8 if lồng. (Excel chỉ giới hạn 7 if nếu cả 7 lồng nhau mà thôi)
2. IsBlank (không phải Isbank), vẫn phải dùng, nếu không sẽ dư 1 dấu "+" và 1 chuỗi của cột A.

 

[longlongago]

Hic, mờ mắt rồi hay sao á.
Làm lại, ngang dọc gì cũng được.

Đa tạ sư phụ! . Nhưng mà sư phụ ơi, bác Du có nói đến 1 cách dùng cột phụ, vậy sư phụ có thể chỉ cho đệ tử cả cách dùng cột phụ được không ạ?
Ngoài ra, cũng với đề bài như thế, nhưng phần trình bày của các text trong chuỗi đấy không cần nối bằng dấu phẩy mà nối bằng xuống dòng thì có được không ạ? Dữ liệu nối vẫn cùng 1 ô nhưng kiểu như thế này ạ:

a200
b30
c20
f30
g40
h40

Đệ tử ngu muội, mong được các sư phụ chỉ giáo!

 

[ndu96081631]

Đa tạ sư phụ! . Nhưng mà sư phụ ơi, bác Du có nói đến 1 cách dùng cột phụ, vậy sư phụ có thể chỉ cho đệ tử cả cách dùng cột phụ được không ạ?
Ngoài ra, cũng với đề bài như thế, nhưng phần trình bày của các text trong chuỗi đấy không cần nối bằng dấu phẩy mà nối bằng xuống dòng thì có được không ạ? Dữ liệu nối vẫn cùng 1 ô nhưng kiểu như thế này ạ:

a200
b30
c20
f30
g40
h40

Đệ tử ngu muội, mong được các sư phụ chỉ giáo!

Ví dụ thế này:
- Gõ vào cell F6 công thức =TRIM(IF(B6="",F5,F5&" "&$A6&B6))
- Kéo fill xuống đến F13
- Xử lý kết quả tại F13, ra được kết quả cần tìm. Chẳng hạn: =SUBSTITUTE(F13," ","+")
- Muốn thay dấu "+" bằng ký tự xuống dòng thì vầy: =SUBSTITUTE(F13," ",CHAR(10)) ---> Cell chứa kết quả này phải Format Wrap Text
---------------------------
Chỉ là làm chơi thôi chứ dữ liệu nhiều thì.. chắc chết (quá nhiều cột phụ)

 

[ptm0412]

Đa tạ sư phụ! .
Ngoài ra, cũng với đề bài như thế, nhưng phần trình bày của các text trong chuỗi đấy không cần nối bằng dấu phẩy mà nối bằng xuống dòng thì có được không ạ?

Ở trên đang nối bằng dấu "+" chứ có phải dấu phẩy đâu?
Thay dấu "+" trong code bằng Chr(10) là xong.