Lọc tìm TOP 5 màu vải nhập về theo điều kiện

moonbean21252000 · 17/5/23

Em chào anh/chị.
Em có file dữ liệu nhập vải về theo các màu. Em muốn lọc tìm TOP 5 màu vải theo số lượng và ngày tháng. Mong anh/chị giúp đỡ. Em cảm ơn.

hichlehg · 17/5/23

Hi bạn, trong khi chờ cao nhân giúp thì mình dùng tạm các của mình nha.
Bước 1: Tạo table bảng tính (Sau này nếu có phát sinh thêm màu khác thì Table sẽ tự mở rộng)
Bước 2: Dùng Query xử lý dữ liệu như sheet Table1 (Để sử dụng hàm MAXIFS)
Bước 3: Lập công thức ngày và số lượng theo màu như bảng tính của mình
Để tìm hiểu về Query bạn theo dõi clip bên dưới nha

Liên kết: https://www.youtube.com/watch?v=Yyj3Y8WWSvU&feature=youtu.be

Bài đã được tự động gộp: 17/5/23

Hỏi thêm: Là trong TOP 5 bạn chọn ra là dựa trên tiêu chí gì?

VetMini · 17/5/23

Đã sử dụng Query thì dùng nó để unpivot và cho vào một Data Model.
Sau đó dùng Pivot Table hay DAX functions để làm thống kê.

Chú thích:
Dữ liệu cúa thớt bị trỉnh bày theo kiểu CrossTab (Pivot) cho nên mọi công thức đều rất phức tạp.

hichlehg · 17/5/23

VetMini đã viết:
Đã sử dụng Query thì dùng nó để unpivot và cho vào một Data Model.
Sau đó dùng Pivot Table hay DAX functions để làm thống kê.

Chú thích:
Dữ liệu cúa thớt bị trỉnh bày theo kiểu CrossTab (Pivot) cho nên mọi công thức đều rất phức tạp.

Mình cho vào data model để dùng pivot lập bảng như chủ thớt mà không biết cách làm nên mình lập công thức cho nhanh.

TongDe · 17/5/23

XIn xóa bài vì nhầm...

VetMini · 17/5/23

Cần định nghĩa rõ "top 5" có nghĩa là 5 trự với điểm cao nhất, hay 5 điểm cao nhất với mỗi điểm ứng với trự có ngày nhỏ nhất?

Ví dụ có 5 thằng đồng điểm cao nhất thì 5 thằng ấy là "top 5", hay chỉ lấy thằng có ngày nhỏ nhất. Vị trí thứ hai nhường cho những thằng có điểm cao thứ 2.

Ở bài #1 thớt có giải thích, nhưng rất tiếc ví dụ (trong file) lại lọt vào vị trí thứ 5 cho nên vẫn chưa tỏ rõ điều kiện.

HieuCD · 17/5/23

moonbean21252000 đã viết:
Em chào anh/chị.
Em có file dữ liệu nhập vải về theo các màu. Em muốn lọc tìm TOP 5 màu vải theo số lượng và ngày tháng. Mong anh/chị giúp đỡ. Em cảm ơn.

Mã:

E16 =LARGE($D$4:$L$13,ROWS($1:1))
C16 =INDEX($C$4:$C$13,AGGREGATE(15,6,(ROW($1:$10)*1000+COLUMN($A:$I))/($D$4:$L$13=E16),COUNTIF(E$15:E16,E16))/1000)
D16 =INDEX($D$3:$L$3,,MOD(AGGREGATE(15,6,(ROW($1:$10)*1000+COLUMN($A:$I))/($D$4:$L$13=E16),COUNTIF(E$15:E16,E16)),1000))

Copy xuống . . .

moonbean21252000 · 18/5/23

HieuCD đã viết:

Mã:

E16 =LARGE($D$4:$L$13,ROWS($1:1))
C16 =INDEX($C$4:$C$13,AGGREGATE(15,6,(ROW($1:$10)*1000+COLUMN($A:$I))/($D$4:$L$13=E16),COUNTIF(E$15:E16,E16))/1000)
D16 =INDEX($D$3:$L$3,,MOD(AGGREGATE(15,6,(ROW($1:$10)*1000+COLUMN($A:$I))/($D$4:$L$13=E16),COUNTIF(E$15:E16,E16)),1000))

Copy xuống . . .

Dạ đúng rồi ạ. Cảm ơn anh rất nhiều. Công thức hay quá.

Thóc Sama · 18/5/23

Thử cách dùng code vba xem nhé.

Mã:

Option Explicit
Sub GPE_TOP5()
    Dim Dic As Object, Key, i&, k&, j&
    Dim Lr&, Arr(), Res(1 To 10000, 1 To 4)
    Dim stt, ngay, mau, sl, a
    Set Dic = CreateObject("Scripting.Dictionary")
    With Sheets("Sheet2")
    .Range("N4").Resize(1000, 4).ClearContents
    a = InputBox(prompt:="Nhap TOP???", Title:="GPE")
    If a = "" Or IsNumeric(a) = False Then
        MsgBox "Khong chon TOP"
        .Range("P1").ClearContents
        Exit Sub
    End If
        Lr = .Range("B" & Rows.Count).End(xlUp).Row
        Arr = .Range("C3:L" & Lr).Value
        For i = 2 To UBound(Arr, 1)
            For j = 2 To UBound(Arr, 2)
                Key = Arr(i, 1) & "|" & Arr(1, j)
                If Not Dic.exists(Key) Then
                    k = k + 1
                    Dic.Add (Key), Arr(i, j)
                    Res(k, 1) = k: Res(k, 2) = Arr(i, 1)
                    Res(k, 3) = Arr(1, j): Res(k, 4) = Arr(i, j)
                End If
            Next j
        Next i
        For i = 1 To k + 1
            For j = i + 1 To k
                If Res(j, 4) > Res(i, 4) Then
                    stt = Res(i, 1): ngay = Res(i, 2)
                    mau = Res(i, 3): sl = Res(i, 4)
                    Res(i, 1) = Res(j, 1): Res(i, 2) = Res(j, 2)
                    Res(i, 3) = Res(j, 3): Res(i, 4) = Res(j, 4)
                    Res(j, 1) = stt: Res(j, 2) = ngay
                    Res(j, 3) = mau: Res(j, 4) = sl
                ElseIf Res(j, 4) = Res(i, 4) Then
                    If Res(j, 2) < Res(i, 2) Then
                        stt = Res(i, 1): ngay = Res(i, 2)
                        mau = Res(i, 3): sl = Res(i, 4)
                        Res(i, 1) = Res(j, 1): Res(i, 2) = Res(j, 2)
                        Res(i, 3) = Res(j, 3): Res(i, 4) = Res(j, 4)
                        Res(j, 1) = stt: Res(j, 2) = ngay
                        Res(j, 3) = mau: Res(j, 4) = sl
                    End If
                End If
            Next j
        Next i
        For i = 1 To k
            Res(i, 1) = i
        Next i
        If a Then
            .Range("N4").Resize(a, 4).Value = Res
            .Range("P1") = "TOP: " & a
            MsgBox "Done"
        End If
    End With
    Set Dic = Nothing
End Sub

moonbean21252000 · 18/5/23

THÓC SAMA đã viết:

Thử cách dùng code vba xem nhé.

Mã:

Option Explicit
Sub GPE_TOP5()
    Dim Dic As Object, Key, i&, k&, j&
    Dim Lr&, Arr(), Res(1 To 10000, 1 To 4)
    Dim stt, ngay, mau, sl, a
    Set Dic = CreateObject("Scripting.Dictionary")
    With Sheets("Sheet2")
    .Range("N4").Resize(1000, 4).ClearContents
    a = InputBox(prompt:="Nhap TOP???", Title:="GPE")
    If a = "" Or IsNumeric(a) = False Then
        MsgBox "Khong chon TOP"
        .Range("P1").ClearContents
        Exit Sub
    End If
        Lr = .Range("B" & Rows.Count).End(xlUp).Row
        Arr = .Range("C3:L" & Lr).Value
        For i = 2 To UBound(Arr, 1)
            For j = 2 To UBound(Arr, 2)
                Key = Arr(i, 1) & "|" & Arr(1, j)
                If Not Dic.exists(Key) Then
                    k = k + 1
                    Dic.Add (Key), Arr(i, j)
                    Res(k, 1) = k: Res(k, 2) = Arr(i, 1)
                    Res(k, 3) = Arr(1, j): Res(k, 4) = Arr(i, j)
                End If
            Next j
        Next i
        For i = 1 To k + 1
            For j = i + 1 To k
                If Res(j, 4) > Res(i, 4) Then
                    stt = Res(i, 1): ngay = Res(i, 2)
                    mau = Res(i, 3): sl = Res(i, 4)
                    Res(i, 1) = Res(j, 1): Res(i, 2) = Res(j, 2)
                    Res(i, 3) = Res(j, 3): Res(i, 4) = Res(j, 4)
                    Res(j, 1) = stt: Res(j, 2) = ngay
                    Res(j, 3) = mau: Res(j, 4) = sl
                ElseIf Res(j, 4) = Res(i, 4) Then
                    If Res(j, 2) < Res(i, 2) Then
                        stt = Res(i, 1): ngay = Res(i, 2)
                        mau = Res(i, 3): sl = Res(i, 4)
                        Res(i, 1) = Res(j, 1): Res(i, 2) = Res(j, 2)
                        Res(i, 3) = Res(j, 3): Res(i, 4) = Res(j, 4)
                        Res(j, 1) = stt: Res(j, 2) = ngay
                        Res(j, 3) = mau: Res(j, 4) = sl
                    End If
                End If
            Next j
        Next i
        For i = 1 To k
            Res(i, 1) = i
        Next i
        If a Then
            .Range("N4").Resize(a, 4).Value = Res
            .Range("P1") = "TOP: " & a
            MsgBox "Done"
        End If
    End With
    Set Dic = Nothing
End Sub

Cảm ơn bác nhiều nhé

HieuCD · 18/5/23

Code VBA không cần dictionary

Mã:

Sub XYZ()
  Dim arr(), a&(), Res(), t, tmp, sRow&, i&, r&, j&
  Const top& = 5
 
  With Sheets("Sheet2")
    arr = .Range("C3:L" & .Range("B" & Rows.Count).End(xlUp).Row).Value
  End With
  sRow = (UBound(arr, 1) - 1) * (UBound(arr, 2) - 1)
  ReDim a(1 To top, 1 To 3)
  For i = 2 To UBound(arr, 1)
    For j = 2 To UBound(arr, 2)
      If arr(i, j) > a(top, 1) Then
        tmp = Array(arr(i, j), i, j)
        For r = 1 To top
          If tmp(0) > a(r, 1) Then
             t = Array(tmp(0), tmp(1), tmp(2))
             tmp(0) = a(r, 1):  tmp(1) = a(r, 2):   tmp(2) = a(r, 3)
             a(r, 1) = t(0):     a(r, 2) = t(1):    a(r, 3) = t(2)
          End If
        Next r
      End If
    Next j
  Next i
  ReDim Res(1 To top, 1 To 4)
  For i = 1 To top
    If a(i, 1) = 0 Then Exit For
    Res(i, 1) = i
    Res(i, 2) = arr(a(i, 2), 1)
    Res(i, 3) = arr(1, a(i, 3))
    Res(i, 4) = a(i, 1)
  Next i
  Sheets("Sheet2").Range("N4").Resize(top, 4).Value = Res
End Sub

moonbean21252000 · 18/5/23

HieuCD đã viết:

Code VBA không cần dictionary

Mã:

Sub XYZ()
  Dim arr(), a&(), Res(), t, tmp, sRow&, i&, r&, j&
  Const top& = 5
 
  With Sheets("Sheet2")
    arr = .Range("C3:L" & .Range("B" & Rows.Count).End(xlUp).Row).Value
  End With
  sRow = (UBound(arr, 1) - 1) * (UBound(arr, 2) - 1)
  ReDim a(1 To top, 1 To 3)
  For i = 2 To UBound(arr, 1)
    For j = 2 To UBound(arr, 2)
      If arr(i, j) > a(top, 1) Then
        tmp = Array(arr(i, j), i, j)
        For r = 1 To top
          If tmp(0) > a(r, 1) Then
             t = Array(tmp(0), tmp(1), tmp(2))
             tmp(0) = a(r, 1):  tmp(1) = a(r, 2):   tmp(2) = a(r, 3)
             a(r, 1) = t(0):     a(r, 2) = t(1):    a(r, 3) = t(2)
          End If
        Next r
      End If
    Next j
  Next i
  ReDim Res(1 To top, 1 To 4)
  For i = 1 To top
    If a(i, 1) = 0 Then Exit For
    Res(i, 1) = i
    Res(i, 2) = arr(a(i, 2), 1)
    Res(i, 3) = arr(1, a(i, 3))
    Res(i, 4) = a(i, 1)
  Next i
  Sheets("Sheet2").Range("N4").Resize(top, 4).Value = Res
End Sub

Thật sự quá khâm phục bác. Cả công thức và code của bác đều tuyệt vời. Cảm ơn bác rất nhiều.

Lọc tìm TOP 5 màu vải nhập về theo điều kiện

moonbean21252000

Thành viên mới

File đính kèm

hichlehg

Thành viên hoạt động

File đính kèm

VetMini

Ăn cùng góc phố

hichlehg

Thành viên hoạt động

TongDe

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

VetMini

Ăn cùng góc phố

HieuCD

Chuyên gia GPE

moonbean21252000

Thành viên mới

Thóc Sama

＿/＿/＿/＿/＿/＿/＿/

File đính kèm

moonbean21252000

Thành viên mới

HieuCD

Chuyên gia GPE

moonbean21252000

Thành viên mới

Bài viết mới nhất

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian