Hàm nối chuỗi có điều kiện

4 Responses

1. hands says:

   06/05/2023 at 1:53 am

   Hàm đó "xưa rồi diễm ơi". Sau này đã cải tiến lại:

   Function JoinText(ByVal Delimiter As String, ParamArray Arrays()) As String
     Dim aDest()   As Variant
     Dim aSub      As Variant
     Dim item      As Variant
     Dim idx       As Long
     Dim n         As Long
     Dim sItem     As String
     'On Error Resume Next
     For n = LBound(Arrays) To UBound(Arrays)
       aSub = Arrays(n)
       If Not IsArray(aSub) Then aSub = Array(aSub)
       For Each item In aSub
         If TypeName(item) <> "Error" Then
           sItem = CStr(item)
           idx = idx + 1
           ReDim Preserve aDest(1 To idx)
           aDest(idx) = sItem
         End If
       Next
     Next
     If idx Then JoinText = Join(aDest, Delimiter)
   End Function

   Và áp dụng trên sheet:

   =JoinText("; ",IF(A2:E2<>"ok",$A$1:$E$1&": "&A2:E2,1/0))

   Ngày trước tôi nghĩ nên có đối số IgnoreBlanks cho trường hợp muốn loại bỏ phần tử rổng. Sau này thấy không cần thiết, muốn rổng hoặc không rổng gì thì cứ phát biểu vào biểu thức là được

   Reply
2. hands says:

   06/05/2023 at 1:54 am

   Sao anh không cấp nhật hàm JionIf và hàm JionText mới nhất lên "Trang Chủ" để dễ tim kiếm

   Nguyên bộ hàm liên quan đến nối chuỗi có điều kiện của mình:

   Function JoinText(ByVal Delimiter As String, ParamArray Arrays()) As String
     Dim aDest()   As Variant
     Dim aSub      As Variant
     Dim item      As Variant
     Dim idx       As Long
     Dim n         As Long
     Dim sItem     As String
     'On Error Resume Next
     For n = LBound(Arrays) To UBound(Arrays)
       aSub = Arrays(n)
       If Not IsArray(aSub) Then aSub = Array(aSub)
       For Each item In aSub
         If TypeName(item) <> "Error" Then
           sItem = CStr(item)
           idx = idx + 1
           ReDim Preserve aDest(1 To idx)
           aDest(idx) = sItem
         End If
       Next
     Next
     If idx Then JoinText = Join(aDest, Delimiter)
   End Function
   Function JoinIf(ByVal Delimiter As String, ByVal CriteriaArray, ByVal Criteria, Optional ByVal TargetArray) As String
     Dim aDest()       As Variant
     Dim aCriteria     As Variant
     Dim aTarget       As Variant
     Dim sCriteria     As Variant
     Dim sTarget       As Variant
     Dim dic           As Object
     Dim bComp         As Boolean
     Dim idx           As Long
     Dim dTmpVal       As Double
   
   'On Error Resume Next
     Set dic = CreateObject("Scripting.Dictionary")
     If IsMissing(TargetArray) Then TargetArray = CriteriaArray
     aCriteria = ConvertTo1DArray(CriteriaArray)
     aTarget = ConvertTo1DArray(TargetArray)
     If (Not IsArray(aCriteria)) Or (Not IsArray(aTarget)) Then Exit Function
   
   bComp = (InStr("<>=", Left(Criteria, 1)) > 0)
     For idx = LBound(aTarget) To UBound(aTarget)
       sCriteria = aCriteria(idx): sTarget = aTarget(idx)
       If TypeName(sCriteria) <> "Error" Then
         If TypeName(sTarget) <> "Error" Then
           If bComp And Len(Criteria) Then
             dTmpVal = CDbl(aCriteria(idx))
             If Evaluate(dTmpVal & Criteria) Then
               If Not dic.Exists(sTarget) Then dic.Add sTarget, ""
             End If
           Else
             If (Left(Criteria, 1) = "!") Then
               If Not (UCase(sCriteria) Like UCase(Mid(Criteria, 2))) Then
                 If Not dic.Exists(sTarget) Then dic.Add sTarget, ""
               End If
             Else
               If (UCase(sCriteria) Like UCase(Criteria)) Then
                 If Not dic.Exists(sTarget) Then dic.Add sTarget, ""
               End If
             End If
           End If
         End If
       End If
     Next
     If dic.Count Then
       aDest = dic.Keys
       JoinIf = Join(aDest, Delimiter)
     End If
     Set dic = Nothing
     'If Err.Number Then MsgBox Err.Description
   End Function
   Private Function ConvertTo1DArray(ByVal SourceArray)
     Dim aDest()   As Variant
     Dim aSource   As Variant
     Dim item      As Variant
     Dim idx       As Long
     'On Error Resume Next
     aSource = SourceArray
     If Not IsArray(aSource) Then aSource = Array(aSource)
     For Each item In aSource
       idx = idx + 1
       ReDim Preserve aDest(1 To idx)
       aDest(idx) = item
     Next
     ConvertTo1DArray = aDest
     'If Err.Number Then MsgBox Err.Description
   End Function
   Function UniqueList(ParamArray Arrays())
     Dim aDest()   As Variant
     Dim aSub      As Variant
     Dim item      As Variant
     Dim idx       As Long
     Dim n         As Long
     Dim sItem     As String
     Dim dic       As Object
     'On Error Resume Next
     Set dic = CreateObject("Scripting.Dictionary")
     For n = LBound(Arrays) To UBound(Arrays)
       aSub = Arrays(n)
       If Not IsArray(aSub) Then aSub = Array(aSub)
       For Each item In aSub
         If TypeName(item) <> "Error" Then
           sItem = CStr(item)
           If Len(sItem) Then
             If Not dic.Exists(sItem) Then dic.Add sItem, Empty
           End If
         End If
       Next
     Next
     If dic.Count Then UniqueList = dic.Keys
     Set dic = Nothing
     'If Err.Number Then MsgBox Err.Description
   End Function

   Lưu ý: hàm JoinIf dùng giống như SUMIF hay COUNTIF, có nghĩa là cho phép dùng các toán tử so sánh (như =, <, >…) hoặc ký tự đại diện (như *, ?)

   Reply
3. hands says:

   06/05/2023 at 1:55 am

   Góp vui cho bạn công thức mảng (excel 2016 hoặc 365), kết thúc Ctrl-Shift-Enter
   =TEXTJOIN(";",TRUE,IF(A2:E2="ok","",$A$1:$E$1&": "&A2:E2))

   MS học bác @ndu96081631 viết hàm TEXTJOIN rồi bác ơi.

   Vậy là phải cập nhật và nâng cấp lên 'version' 2 hả thầy! 🙂 Cảm ơn thầy đã quan tâm đến anh em GPE.
   Nhờ hàm UDF này của thầy mà 1 số công đoạn nối chuỗi kết quả từ hàm được thông mạch.

   Excel: là động từ 'xuất sắc, hơn trội', danh từ là excellence (Trích: vdict.com/excel,1,0,0.html)

   Bản thân excel đơn thuần là "bảng tính", là tổng thể 1 môi trường bao gồm trong đó có 'nơi chứa', và các 'công cụ – dụng cụ' nhằm mục đích tính toán. Chính nhờ sự thấu hiểu của các kỹ sư phần mềm về tâm lý và thói quen của người sử dụng mà môi trường này được sử dụng và phục vụ đa dạng cho mọi ngành nghề, một môi trường mà các công cụ – dụng cụ tuy có các chức năng khác nhau, nhưng nếu biết phát huy điểm mạnh của từng cái, và phối hợp chúng nhuần nhuyễn đúng nơi đúng lúc thì nó mới gọi là "EXCEL – 'xuất sắc' " được. Cũng giống như các bộ phận trong cơ thể con người, bạn không thể dùng tay để 'nếm hoặc ngửi', dùng chân để 'phân biệt màu sắc', dùng đầu để đi được….Excel cũng vậy, nếu thiên về 1 chức năng nào quá, bạn cũng như người bị khuyết tật, phải lấy tay dò đường khi không còn đôi mắt, hoặc dùng các ký hiệu bàn tay ra dấu khi không còn giọng nói, hoặc dùng chân để viết khi không còn đôi tay…

   Có những bài toán (hoặc yêu cầu) chỉ đơn thuần dùng một hay kết hợp vài hàm cơ bản là giải quyết tốt, có bài khác phải dùng công thức Mảng mà ngay cả VBA cũng không thể nào làm nhanh và gọn hơn, có những bài cần thống kê thì không công cụ nào qua khỏi PivotTable hay Advance Filter, lại có những bài phải xử lý dữ liệu lớn và có tính cách lặp đi lặp lại thì không gì bằng VBA….

   Nói tóm lại, thái quá hay bất cập đều có hại cho người học excel, đã học thì không nên có thành kiến: thích hay ghét hàm này hàm kia, công cụ này dụng cụ kia, bạn cứ như một hồ chứa nước chưa bao giờ đầy, nên nhận và chọn lọc những gì bạn muốn chứa để phục vụ mục tiêu chính: 'Làm tốt công việc mưu sinh', Cho nên phải biết sử dụng 'mọi công cụ' đúng nơi đúng lúc thì bạn mới là 'tông đồ' của 'EXCEL'.

   Vài chia sẻ về excel với bạn.

   Thân

   Thiệt ra, với sự cải tiến của máy tính và công nghệ thông tin ngày nay, ngừoi ta chỉ cần học 3 điều cho thật tốt:
   1. Học cách tình bày bảng tính bằng cách chuẩn hoá dữ liệu. Cái này gồm 2 phần:
   1.1 Chuẩn hoá dữ liệu về đồng loạt. Tức là những dữ liệu cùng cột phải đồng dạng với nhau.
   1.2 Chuẩn theo dang CSDL LH, càng gần càng tốt.
   2. Học cách dự đoán những điều bất trắc, những giới hạn của dữ liệu.
   3. Học cách diễn tả vấn đề của mình. Nếu vấn đề dài, phức tạp thì nên viết ra giấy, diễn giải một bảng lô gic, sau đó đọc lại xem đã diễn giải đủ chưa.

   Có được 3 điều trên thì chỉ còn việc đem bài lên đây hỏi, khỏi cần rèn luyện cốt kiếc, công thức gì cả.

   Ỏ đây, phần lớn bài đều bị vướng mắc ở 1, 2, hay cả 3 điều trên cho nên hầu như luôn luôn phải mất trên 5 bài trả lời mới thấy giải đáp đúng.

   Reply
4. hands says:

   06/05/2023 at 1:55 am

   Em đọc chưa hiểu các tham số hàm Jointext
   Muốn nhờ thầy và anh chị em giúp hàm này trong ví dụ thực tế,
   nối chuỗi nhiều điều kiện
   =jointext(CHAR(10);TRUE;LOOKUP(2;1/(D13>=$D$3:$D$8)*1/(D13<=$F$3:$F$8);$C$3:$C$8))
   Yêu cầu nối chuỗi theo ngày các công việc thực hiện trong cùng một ngày được liệt kê theo dòng từng ngày
   – em thử theo cách nối chuỗi 1 điều kiện:
   =jointext(CHAR(10);TRUE;IF((D13=$D$3:$D$8;$C$3:$C$8;""))
   thì được kết quả chưa mong muốn
   – nối chuỗi theo 2 điều kiện, lồng thêm điều kiện và hàm if
   =jointext(CHAR(10);TRUE;IF(AND(D13>=$D$3:$D$8;D13<=$F$3:$F$8);$C$3:$C$8;""))
   thì không được
   Mong được các thầy và anh chị em chỉ giúp

   Tham khảo file đính kèm.

   Thân

   Reply