Nhờ mọi người giúp hàm lookup tương tự hàm vlookup (1 người xem)

[ginkgo36]

Em chào mọi người,
Em có 1 cột danh sách các loại trái cây, và 1 cột chỉ nơi xuất xứ của từng loại trái cây. ( Mọi người vui lòng xem file đính kèm.)
Mong mọi người giúp em 1 macro khi em truyền vào cột "Loại trái cây" và "Xuất xứ" thì kết quả sẽ tương tự như cột "Kết quả" của em.
Tìm kiếm tất cả các giá trị bên cột "Xuất xứ" có chứa chuỗi bên cột "Loại trái cây" và ghép các kết quả tìm kiếm lại với nhau ngăn cách bằng "; ".

Em cám ơn mọi người nhiều.

 

[Let'GâuGâu]

Em chào mọi người,
Em có 1 cột danh sách các loại trái cây, và 1 cột chỉ nơi xuất xứ của từng loại trái cây. ( Mọi người vui lòng xem file đính kèm.)
Mong mọi người giúp em 1 macro khi em truyền vào cột "Loại trái cây" và "Xuất xứ" thì kết quả sẽ tương tự như cột "Kết quả" của em.
Tìm kiếm tất cả các giá trị bên cột "Xuất xứ" có chứa chuỗi bên cột "Loại trái cây" và ghép các kết quả tìm kiếm lại với nhau ngăn cách bằng "; ".

Em cám ơn mọi người nhiều.

hàm jointext của anh ndu

Function JoinText(ByVal Delimiter As String, ParamArray Arrays()) As String
  Dim aTmp, Arr(), Item, tmp As String
  Dim i As Long, n As Long
  'On Error Resume Next
  For i = LBound(Arrays) To UBound(Arrays)
    aTmp = Arrays(i)
    If Not IsArray(aTmp) Then aTmp = Array(aTmp)
    For Each Item In aTmp
      If TypeName(Item) <> "Error" Then
        tmp = CStr(Item)
        n = n + 1
        ReDim Preserve Arr(1 To n)
        Arr(n) = tmp
      End If
    Next
  Next
  If n Then JoinText = Join(Arr, Delimiter)
End Function

B2=JoinText(";";IF(1/COUNTIF(OFFSET($C$1;ROW($C$2:$C$14)-1;);A2&"*");$C$2:$C$14))

ctrl shift enter

 

[ginkgo36]

Thanks bạn, nhưng hàm chưa chạy được, file của bạn kết quả là #VALUE!. Mình đang dùng excel 2010

 

[Let'GâuGâu]

Thanks bạn, nhưng hàm chưa chạy được, file của bạn kết quả là #VALUE!. Mình đang dùng excel 2010

tôi cũng xài 2010,
bạn có enable macro không vậy?

 

[ginkgo36]

Rồi bạn ah. Khi mình copy code "=JoinText(";";IF(1/COUNTIF(OFFSET($C$1;ROW($C$2:$C$14)-1;A2&"*");$C$2:$C$14))" ở trên của bạn và nhấn ctrl + shift + enter thì nó báo lỗi ngay dấu nháy " trong ;";. và không chạy. Còn mở file của bạn lên và chạy lại công thức bạn để sẵn thì nó ra chuỗi "Quýt Thái;Cam Sành;Quýt Hồng;Cam Nhập khẩu;Cam nội;Táo Mỹ;Dưa hấu;Táo Nhật;Dưa leo;Táo Úc;Dưa lê;Táo Nội;Táo Nội"

 

[Let'GâuGâu]

Rồi bạn ah. Khi mình copy code "=JoinText(";";IF(1/COUNTIF(OFFSET($C$1;ROW($C$2:$C$14)-1;A2&"*");$C$2:$C$14))" ở trên của bạn và nhấn ctrl + shift + enter thì nó báo lỗi ngay dấu nháy " trong ;";. và không chạy. Còn mở file của bạn lên và chạy lại công thức bạn để sẵn thì nó ra chuỗi "Quýt Thái;Cam Sành;Quýt Hồng;Cam Nhập khẩu;Cam nội;Táo Mỹ;Dưa hấu;Táo Nhật;Dưa leo;Táo Úc;Dưa lê;Táo Nội;Táo Nội"

bạn thử làm lại như sau, còn ko đúng nữa thì tôi cũng ko biết tại sao nữa (tôi tải về thì nó vẫn ra đúng kết quả)
1- khi bạn chép cthức xuống nó báo lổi ";" là do ẽxcel của tôi cài đặt dấu phân cách trong cthức là ; còn của bạn là ,
để bạn có thể vào control panel sữa lại hoặc chịu khó thấy cái nào là ; thì sữa lại ,
2-tôi nghỉ là có tác động vào cthức rồi (cthức mảng, bạn phải thấy có 2 dấu {}). bạn chọn lại cell đó nhấn F2, nhấn tổ hợp 3 phím cùng lúc Ctrl Shift Enter

 

[ginkgo36]

Thanks bạn, mình thay toàn bộ dấu ; thành dấu , là ok. Thanks bạn nhiều

 

[ginkgo36]

bạn cho mình hỏi ý nghĩa của khúc này "OFFSET($C$1;ROW($C$2:$C$14)-1;" nhe. Tại sao tới đây bạn ko truyền đủ tham số của hàm Offset, bạn mới chỉ truyền reference, rows. sau đó là Cols và height, width nữa. Nếu truyền như bạn ( OFFSET(reference, rows,) ) thì có ý nghĩa gì? Mình thử xóa dấu ; đi thì hàm ko chạy do thiếu tham số.

 

[ginkgo36]

Bạn có thể giải thích code của bạn cho mình hiểu chút được ko

 

[Let'GâuGâu]

Bạn có thể giải thích code của bạn cho mình hiểu chút được ko

kaka,,,,, code của and ndu bạn............bạn có thấy cái nick đó tràn ngập trên diễn dàn ko,,,,,,tôi chỉ copy sử dụng thôi bạn

còn hàm offset
thì tôi chỉ cần tham số row thôi, ko cần những cái còn lại
tức là tượng tự như vậy

=COUNTIF($C$2:$C$14;A2&"*")

nhưng nếu làm như vậy thì nó ko tạo ra một mảng được

cú pháp của hàm offset nó yêu cầu có ít nhất 2 tham số bạn ạh (chắc vậy)
offset(reference,row,) col để trống có nghĩa là 1

 

[ginkgo36]

Có cách nào tối ưu hóa được câu lệnh "=JoinText(";";IF(1/COUNTIF(OFFSET($C$1;ROW($C$2:$C$14)-1;A2&"*");$C$2:$C$14))" này cho đơn giản và dễ sử dụng hơn không bạn? Nếu đưa câu này cho 1 người mới sử dụng excel chắc người đó sẽ rất là bối rối vì xử dụng nhiều hàm + phải nhấn tổ hợp ctrl + shift + enter --> cái này đối với người mới sử dụng người ta sẽ dễ quên do thói quen gõ hàm xong nhấn enter .
Ở đây mình thấy có có 3 tham số là: $C$1, $C$2:$C$14, và A2. Làm cách nào đó để hàm chỉ cần truyền vào 3 tham số đó thì trả về kết quả tương tự không bạn?.

 

[ginkgo36]

Bạn cho mình hỏi chút, mình thử tách các hàm trong "=JoinText(";";IF(1/COUNTIF(OFFSET($C$1;ROW($C$2:$C$14)-1;A2&"*");$C$2:$C$14))" ra chạy riêng để tìm hiểu cách chạy của nó thì mình thấy như sau:
- ROW($C$2:$C$14) --> kết quả là 2
- ROW($C$2:$C$14)-1 --> Kết quả là 1
- OFFSET($C$1;ROW($C$2:$C$14)-1, 1 cách đơn giản có thể viết để dễ hiểu là =OFFSET($C$1;1 --> kết quả trả ra sẽ là dòng C2 = Quýt Thái
- COUNTIF(OFFSET($C$1;ROW($C$2:$C$14)-1;A2&"*" = COUNTIF(C2;A2&"*") = 1

+ Mình không hiểu làm thế nào mà hàm trên xác định được những chuỗi liên quan đến Quýt như Quýt Thái; Quýt Hồng để có thể gom nó lại thành 1 chuỗi bằng hàm JoinText?
+ Trong câu If mình không thấy mệnh đề điều kiện vậy sao nó lại chạy được?
+ Vế ;$C$2:$C$14 ở cuối cùng mình không hiểu sao lại được nằm ở đây?

Mong bạn giải thích. Cám ơn bạn

 

[ndu96081631]

Bài toán này dùng JoinText cũng được, nhưng JoinIf sẽ khỏe hơn
Toàn bộ code JoinIf

Function JoinIf(ByVal Delimiter As String, ByVal CriteriaArray, ByVal Criteria, Optional ByVal TargetArray) As String
  Dim aTmpCrit, aTmpDes, tmp1, tmp2, arr(), dic As Object
  Dim bComp As Boolean, Chk As Boolean
  Dim i As Long, j As Long, k As Long, dTmpVal As Double
  Set dic = CreateObject("Scripting.Dictionary")
  If IsMissing(TargetArray) Then TargetArray = CriteriaArray
  aTmpCrit = ConvertTo1DArray(CriteriaArray)
  aTmpDes = ConvertTo1DArray(TargetArray)
  If (Not IsArray(aTmpCrit)) Or (Not IsArray(aTmpDes)) Then Exit Function
  On Error Resume Next
  bComp = (InStr("<>=", Left(Criteria, 1)) > 0)
  For i = LBound(aTmpDes) To UBound(aTmpDes)
    tmp1 = aTmpCrit(i): tmp2 = aTmpDes(i)
    If bComp And Len(Criteria) Then
      dTmpVal = CDbl(aTmpCrit(i))
      If Evaluate(dTmpVal & Criteria) Then dic.Add tmp2, ""
    Else
      If (Left(Criteria, 1) = "!") Then
        If Not (UCase(tmp1) Like UCase(Mid(Criteria, 2, Len(Criteria)))) Then dic.Add tmp2, ""
      Else
        If (UCase(tmp1) Like UCase(Criteria)) Then dic.Add tmp2, ""
      End If
    End If
  Next
  If dic.Count Then
    arr = dic.Keys
    JoinIf = Join(arr, Delimiter)
  End If
End Function
Private Function ConvertTo1DArray(ByVal SourceArray)
  Dim aTmp, Item, arr()
  Dim n As Long
  On Error Resume Next
  aTmp = SourceArray
  If Not IsArray(aTmp) Then aTmp = Array(aTmp)
  For Each Item In aTmp
    n = n + 1
    ReDim Preserve arr(1 To n)
    arr(n) = Item
  Next
  ConvertTo1DArray = arr
End Function

Công thức áp dụng:

=JoinIf("; ",$C$2:$C$14,A2&"*")

Enter bình thường
-----------
Cú pháp hàm:
JoinIf(dấu phân cách, mảng điều kiện, điều kiện, mảng kết quả)
Trong trường hợp công thức thiếu mảng kết quả thì xem như mảng điều kiện cũng là mảng kết quả (giống hàm SUMIF)

 

[pro8x]

Mọi người cho e hỏi.Sao khi tải file cua thầy NDU96081631 e mỏ thì công thức báo lỗi #NAME? nhỉ. e dùng 2k7

=> k bật macro