Xin hàm sumifs dạng Vlookup

hongphuong1997 · 24/1/19

Thưa thầy cô và các bác, các anh chị!
Trong excel các công thức về dạng số thì quá nhiều, nhưng các công thức về ký tự và dạng chuỗi thì hầu như không có.
Vì vậy cháu muốn có 1 số hàm về dạng ký tự và chuỗi
Vậy mong các bác giúp đỡ.

dazkangel · 25/1/19

Đổi lại công thức:

Mã:

=SUBSTITUTE(IF(A1=1," ",J22&IFERROR("; "&INDIRECT(TEXT(AGGREGATE(15,6,(COLUMN(D21:H23)+ROW(D21:H23)*1000)/SEARCH(C5,D5:H7)/SEARCH(C9,D9:H11)/SEARCH(C13,D13:H15)/SEARCH(C17,D17:H19),A1-1),"R0C000"),0),""))," ; ","")

snow25 · 25/1/19

hongphuong1997 đã viết:
Anh ơi, quy luật tham chiếu giống hàm sumifs
-Nhưng nó là tìm kiếm trong chuỗi
-Và kết quả trả về không phải là tính tổng

Đây em xem nhé.Dùng như hàm sumifs.

Mã:

Function laygiatri(ByVal tong As Range, ParamArray mang() As Variant) As String
      Dim arr, i As Long, j As Long, dic As Object, dk As String, k As Integer, arr1, s As String, min1 As Long, min2 As Long
      min1 = 10000: min2 = 10000
      Set dic = CreateObject("scripting.dictionary")
          For k = LBound(mang) To UBound(mang) Step 2
             arr = mang(k).Value
              If min1 > UBound(arr, 1) Then min1 = UBound(arr, 1)
              If min2 > UBound(arr, 2) Then min2 = UBound(arr, 2)
          Next k
           arr = tong.Value
              If min1 > UBound(arr, 1) Then min1 = UBound(arr, 1)
              If min2 > UBound(arr, 2) Then min2 = UBound(arr, 2)
           For i = 1 To min1
               For j = 1 To min2
                   dic.Add i & "#" & j, arr(i, j)
               Next j
           Next i
          For k = LBound(mang) To UBound(mang) Step 2
              arr = mang(k).Value
              dk = mang(k + 1).Value
              For i = 1 To min1
               For j = 1 To min2
                     If UCase(dk) <> UCase(arr(i, j)) Then
                       If dic.exists(i & "#" & j) Then
                           dic.Remove (i & "#" & j)
                       End If
                     End If
                 Next j
              Next i
          Next k
            arr = dic.keys
            For i = LBound(arr) To UBound(arr)
                If s = Empty Then
                   s = dic.Item(arr(i))
                Else
                   s = s & ";" & dic.Item(arr(i))
                End If
            Next i
            laygiatri = s
End Function

hongphuong1997 · 25/1/19

snow25 đã viết:

Đây em xem nhé.Dùng như hàm sumifs.

Mã:

Function laygiatri(ByVal tong As Range, ParamArray mang() As Variant) As String
      Dim arr, i As Long, j As Long, dic As Object, dk As String, k As Integer, arr1, s As String, min1 As Long, min2 As Long
      min1 = 10000: min2 = 10000
      Set dic = CreateObject("scripting.dictionary")
          For k = LBound(mang) To UBound(mang) Step 2
             arr = mang(k).Value
              If min1 > UBound(arr, 1) Then min1 = UBound(arr, 1)
              If min2 > UBound(arr, 2) Then min2 = UBound(arr, 2)
          Next k
           arr = tong.Value
              If min1 > UBound(arr, 1) Then min1 = UBound(arr, 1)
              If min2 > UBound(arr, 2) Then min2 = UBound(arr, 2)
           For i = 1 To min1
               For j = 1 To min2
                   dic.Add i & "#" & j, arr(i, j)
               Next j
           Next i
          For k = LBound(mang) To UBound(mang) Step 2
              arr = mang(k).Value
              dk = mang(k + 1).Value
              For i = 1 To min1
               For j = 1 To min2
                     If UCase(dk) <> UCase(arr(i, j)) Then
                       If dic.exists(i & "#" & j) Then
                           dic.Remove (i & "#" & j)
                       End If
                     End If
                 Next j
              Next i
          Next k
            arr = dic.keys
            For i = LBound(arr) To UBound(arr)
                If s = Empty Then
                   s = dic.Item(arr(i))
                Else
                   s = s & ";" & dic.Item(arr(i))
                End If
            Next i
            laygiatri = s
End Function

Em cảm ơn anh rất nhiều
Nhưng cũng chưa chuẩn anh à
Bây giờ là tìm ký tự để so sánh cơ mà anh.

snow25 · 25/1/19

hongphuong1997 đã viết:
Em cảm ơn anh rất nhiều
Nhưng cũng chưa chuẩn anh à
Bây giờ là tìm ký tự để so sánh cơ mà anh.

Không hiểu lắm.Khi hàm sumif bằng không thì làm gì còn ký tự nào.Em xem lại xem.Không thì nói rõ chỗ nào không đúng nhé.À ý em là có kết quả tìm ngược lại à.

hongphuong1997 · 25/1/19

snow25 đã viết:
Không hiểu lắm.Khi hàm sumif bằng không thì làm gì còn ký tự nào.Em xem lại xem.Không thì nói rõ chỗ nào không đúng nhé.À ý em là có kết quả tìm ngược lại à.

Anh ơi!
Anh hiểu ý của em một tý anh ơi
Chuỗi là gồm nhiều ký tự
Trong khi đó tìm ký tự trong chuỗi mà anh.
** Và tìm kiếm các ký tự trong các chuỗi đó để thành kết quả như của anh đó
(Kết quả của anh chỉ đúng với trường hợp đơn thuần thôi anh àh)

snow25 · 25/1/19

hongphuong1997 đã viết:
Anh ơi!
Anh hiểu ý của em một tý anh ơi
Chuỗi là gồm nhiều ký tự
Trong khi đó tìm ký tự trong chuỗi mà anh.
** Và tìm kiếm các ký tự trong các chuỗi đó để thành kết quả như của anh đó
(Kết quả của anh chỉ đúng với trường hợp đơn thuần thôi anh àh)

Trường hợp đơn thuần là sao nhỉ.

hongphuong1997 · 25/1/19

snow25 đã viết:
Trường hợp đơn thuần là sao nhỉ.

Anh ơi anh đang tìm là cả cụm "abc"
Nhưng ý của em là tìm ký tự "a" trong cụm "abc" anh à
Em cảm ơn anh!

snow25 · 25/1/19

hongphuong1997 đã viết:
Anh ơi anh đang tìm là cả cụm "abc"
Nhưng ý của em là tìm ký tự "a" trong cụm "abc" anh à
Em cảm ơn anh!

À hiểu.vậy mai anh chỉnh.Tại em bảo làm giống hàm sumifs.Chứ không phải là tương đối.

hongphuong1997 · 25/1/19

snow25 đã viết:
À hiểu.vậy mai anh chỉnh.Tại em bảo làm giống hàm sumifs.Chứ không phải là tương đối.

Hi hi... em cảm ơn anh
snow25

hongphuong1997 · 26/1/19

dazkangel đã viết:
Đổi lại công thức:

Mã:

=SUBSTITUTE(IF(A1=1," ",J22&IFERROR("; "&INDIRECT(TEXT(AGGREGATE(15,6,(COLUMN(D21:H23)+ROW(D21:H23)*1000)/SEARCH(C5,D5:H7)/SEARCH(C9,D9:H11)/SEARCH(C13,D13:H15)/SEARCH(C17,D17:H19),A1-1),"R0C000"),0),""))," ; ","")

Anh
dazkangel
ơi, em quá kính phục anh, công thức như này mà anh cũng nghĩ ra được.
quá kinh khủng,
Nhưng thật sự để nói thì .....
Mặc dù rất đúng nhưng mà để đem vào làm thì khó quá anh.
Em rất cảm ơn anh!
anh
dazkangel
quá siêu đẳng
Anh
dazkangel
sao không nghĩ dùng hàm Vlookop nhiều điều kiện có thể rút gọn được công thức chăng???

tam888 · 26/1/19

snow25 đã viết:

Đây em xem nhé.Dùng như hàm sumifs.

Mã:

Function laygiatri(ByVal tong As Range, ParamArray mang() As Variant) As String
      Dim arr, i As Long, j As Long, dic As Object, dk As String, k As Integer, arr1, s As String, min1 As Long, min2 As Long
      min1 = 10000: min2 = 10000
      Set dic = CreateObject("scripting.dictionary")
          For k = LBound(mang) To UBound(mang) Step 2
             arr = mang(k).Value
              If min1 > UBound(arr, 1) Then min1 = UBound(arr, 1)
              If min2 > UBound(arr, 2) Then min2 = UBound(arr, 2)
          Next k
           arr = tong.Value
              If min1 > UBound(arr, 1) Then min1 = UBound(arr, 1)
              If min2 > UBound(arr, 2) Then min2 = UBound(arr, 2)
           For i = 1 To min1
               For j = 1 To min2
                   dic.Add i & "#" & j, arr(i, j)
               Next j
           Next i
          For k = LBound(mang) To UBound(mang) Step 2
              arr = mang(k).Value
              dk = mang(k + 1).Value
              For i = 1 To min1
               For j = 1 To min2
                     If UCase(dk) <> UCase(arr(i, j)) Then
                       If dic.exists(i & "#" & j) Then
                           dic.Remove (i & "#" & j)
                       End If
                     End If
                 Next j
              Next i
          Next k
            arr = dic.keys
            For i = LBound(arr) To UBound(arr)
                If s = Empty Then
                   s = dic.Item(arr(i))
                Else
                   s = s & ";" & dic.Item(arr(i))
                End If
            Next i
            laygiatri = s
End Function

Dùng ParamArray rất hay và hợp với bài này
Sao lại tín nhiệm dictionary , bài này đâu cần dictionary nhỉ???

snow25 · 26/1/19

hongphuong1997 đã viết:
Anh ơi anh đang tìm là cả cụm "abc"
Nhưng ý của em là tìm ký tự "a" trong cụm "abc" anh à
Em cảm ơn anh!

Đây em xem.

Mã:

Function laygiatri(ByVal tong As Range, ParamArray mang() As Variant) As String
      Dim arr, i As Long, j As Long, dic As Object, dk As String, k As Integer, arr1, s As String
      Set dic = CreateObject("scripting.dictionary")
           arr = tong.Value
           For i = 1 To UBound(arr, 1)
               For j = 1 To UBound(arr, 2)
                   dic.Add i & "#" & j, arr(i, j)
               Next j
           Next i
          For k = LBound(mang) To UBound(mang) Step 2
              arr = mang(k).Value
              dk = mang(k + 1).Value
              For i = 1 To UBound(arr, 1)
                 For j = 1 To UBound(arr, 2)
                     If InStr(arr(i, j), dk) = 0 Then
                       If dic.exists(i & "#" & j) Then
                           dic.Remove (i & "#" & j)
                       End If
                     End If
                 Next j
              Next i
          Next k
            arr = dic.keys
            For i = LBound(arr) To UBound(arr)
                If s = Empty Then
                   s = dic.Item(arr(i))
                Else
                   s = s & ";" & dic.Item(arr(i))
                End If
            Next i
            laygiatri = s
End Function

Ba Tê · 26/1/19

hongphuong1997 đã viết:
Anh ơi anh đang tìm là cả cụm "abc"
Nhưng ý của em là tìm ký tự "a" trong cụm "abc" anh à
Em cảm ơn anh!

Tìm từng "chuỗi con" trong "chuỗi khác", nếu có thì lấy kết quả tương ứng trong vùng kết quả , nếu có nhiều kết quả thì nối lại với nhau bằng dấu phẩy? Cho kết quả mẫu cũng kèm giải thích kết quả lấy từ đâu, điều kiện nào thì lấy.
Hoang mang quá!

hongphuong1997 · 26/1/19

snow25 đã viết:

Đây em xem.

Mã:

Function laygiatri(ByVal tong As Range, ParamArray mang() As Variant) As String
      Dim arr, i As Long, j As Long, dic As Object, dk As String, k As Integer, arr1, s As String
      Set dic = CreateObject("scripting.dictionary")
           arr = tong.Value
           For i = 1 To UBound(arr, 1)
               For j = 1 To UBound(arr, 2)
                   dic.Add i & "#" & j, arr(i, j)
               Next j
           Next i
          For k = LBound(mang) To UBound(mang) Step 2
              arr = mang(k).Value
              dk = mang(k + 1).Value
              For i = 1 To UBound(arr, 1)
                 For j = 1 To UBound(arr, 2)
                     If InStr(arr(i, j), dk) = 0 Then
                       If dic.exists(i & "#" & j) Then
                           dic.Remove (i & "#" & j)
                       End If
                     End If
                 Next j
              Next i
          Next k
            arr = dic.keys
            For i = LBound(arr) To UBound(arr)
                If s = Empty Then
                   s = dic.Item(arr(i))
                Else
                   s = s & ";" & dic.Item(arr(i))
                End If
            Next i
            laygiatri = s
End Function

Vẫn sai anh oi

snow25 · 26/1/19

hongphuong1997 đã viết:
Vẫn sai anh oi

Chỗ nào vậy ta.

hongphuong1997 · 26/1/19

Ba Tê đã viết:
Tìm từng "chuỗi con" trong "chuỗi khác", nếu có thì lấy kết quả tương ứng trong vùng kết quả , nếu có nhiều kết quả thì nối lại với nhau bằng dấu phẩy? Cho kết quả mẫu cũng kèm giải thích kết quả lấy từ đâu, điều kiện nào thì lấy.
Hoang mang quá!

Em cảm ơn anh
Ba Tê
Đúng roài anh ơi
Nhưng của anh sao làm được có mỗi 1 mảng anh ơi
Anh làm thêm cho em nhiều mảng với anh
Ba Tê

Bài đã được tự động gộp: 26/1/19

snow25 đã viết:
Chỗ nào vậy ta.

Anh snow25 ơi. làm giống như bài của anh
Ba Tê đó, nhưng của anh
Ba Tê
mới chỉ tìm được có mỗi 1 mảng

Ba Tê · 26/1/19

hongphuong1997 đã viết:
Em cảm ơn anh
@Ba Tê
Đúng roài anh ơi
Nhưng của anh sao làm được có mỗi 1 mảng anh ơi
Anh làm thêm cho em nhiều mảng với anh

Bạn nhập thủ công kết quả bạn muốn có đi, tôi sẽ cố hiểu và viết "nhiều mảng" cho bạn.

snow25 · 26/1/19

hongphuong1997 đã viết:
Em cảm ơn anh
Ba Tê
Đúng roài anh ơi
Nhưng của anh sao làm được có mỗi 1 mảng anh ơi
Anh làm thêm cho em nhiều mảng với anh
Ba Tê

Bài đã được tự động gộp: 26/1/19

Anh snow25 ơi. làm giống như bài của anh
Ba Tê đó, nhưng của anh
Ba Tê
mới chỉ tìm được có mỗi 1 mảng

Xem lại file mới sửa lại.Anh gửi code em không xem à.File lúc nãy gửi là file cũ.

hongphuong1997 · 26/1/19

Ba Tê đã viết:
Bạn nhập thủ công kết quả bạn muốn có đi, tôi sẽ cố hiểu và viết "nhiều mảng" cho bạn.

Anh ơi bài của anh như thế là đúng rồi anh à
Bây giờ anh thêm mảng và điều kiện nữa thôi anh à
(Tức là tham chiếu giống như kiểu hàm sumifs)
Tức là các mảng có kích thước bằng nhau và các điều kiện cùng nằm vị trí các ô của các mảng đó anh
Ba Tê

Ba Tê · 26/1/19

hongphuong1997 đã viết:
Anh ơi bài của anh như thế là đúng rồi anh à
Bây giờ anh thêm mảng và điều kiện nữa thôi anh à
(Tức là tham chiếu giống như kiểu hàm sumifs)
Tức là các mảng có kích thước bằng nhau và các điều kiện cùng nằm vị trí các ô của các mảng đó anh
Ba Tê

Sao bạn cứ "SumIf" hoài vậy, cứ giải thích cách lấy kết quả, điều kiện lấy, nếu người khác hiểu là được, chẳng liên quan gì đến SUMIF cả.
Trường hợp này là nối chuỗi có điều kiện, bạn cứ hướng người khác vào SUMIFS, VLOOKUP... làm mọi người chẳng hiểu ra làm sao.
Tôi chỉ hiểu được như vầy (bạn làm biếng viết ra kết quả bằng thủ công sao?)
Quá hoảng thiệt!

Xin hàm sumifs dạng Vlookup

Thành viên tiêu biểu

File đính kèm

<New Horizons>

Thành viên gạo cội

File đính kèm

Thành viên tiêu biểu

File đính kèm

Thành viên gạo cội

Thành viên tiêu biểu

Thành viên gạo cội

Thành viên tiêu biểu

Thành viên gạo cội

Thành viên tiêu biểu

Thành viên tiêu biểu

Thành viên tích cực

Thành viên gạo cội

File đính kèm

Cạo Rồi Khỏi Gội

File đính kèm

Thành viên tiêu biểu

Thành viên gạo cội

Thành viên tiêu biểu

Cạo Rồi Khỏi Gội

Thành viên gạo cội

Thành viên tiêu biểu

Cạo Rồi Khỏi Gội

File đính kèm

Đếm ngược thời gian