Thống kê ngày hết hạn của sản phẩm

tranphuson · 15/5/24

Xin chào các Anh, Chị

Vui lòng hỗ trợ file thống kê ngày hết hạn của sản phẩm - mình đang làm thống kê theo dạng PivotTable nên chưa có nhiều sự lựa chọn tốt nhất nên nhờ hỗ trợ bằng VBA theo như Sheet "PV" (file đính kèm) và hình minh họa bên dưới:

1. Ở B1 nếu chọn khu vực "Miền Tây" và điều kiện ở cột E1 điều kiện = 30 ngày thì lọc ra dữ liệu toàn bộ có số ngày <=30 (dựa theo Sheet "Inventory onhand")

2. Ở trong bảng tính này sẽ tách ra ở cột A: "ngày còn lại" tách ra 3 dòng: 10 ngày, 20 ngày, 30 ngày và tương ứng mỗi ngày sẽ có dòng công thức 'Tổng cộng 10 ngày, 20 ngày, 30 ngày)

3. Cuối cùng sẽ tự động thêm dòng "Grand total" sẽ cộng lại 3 dòng: 10 ngày + 20 ngày + 30 ngày

Xin cảm ơn

Hoàng Tuấn 868 · 15/5/24

tranphuson đã viết:
Vui lòng hỗ trợ file thống kê ngày hết hạn của sản phẩm - mình đang làm thống kê theo dạng PivotTable nên chưa có nhiều sự lựa chọn tốt nhất nên nhờ hỗ trợ bằng VBA theo như Sheet "PV" (file đính kèm) và hình minh họa bên dưới:

1. Ở B1 nếu chọn khu vực "Miền Tây" và điều kiện ở cột E1 điều kiện = 30 ngày thì lọc ra dữ liệu toàn bộ có số ngày <=30 (dựa theo Sheet "Inventory onhand")

2. Ở trong bảng tính này sẽ tách ra ở cột A: "ngày còn lại" tách ra 3 dòng: 10 ngày, 20 ngày, 30 ngày và tương ứng mỗi ngày sẽ có dòng công thức 'Tổng cộng 10 ngày, 20 ngày, 30 ngày)

3. Cuối cùng sẽ tự động thêm dòng "Grand total" sẽ cộng lại 3 dòng: 10 ngày + 20 ngày + 30 ngày

Xin cảm ơn
View attachment 300843

Người xưa có câu: Lời chào cao hơn mâm cỗ.

snow25 · 15/5/24

tranphuson đã viết:
Xin chào các Anh, Chị

Vui lòng hỗ trợ file thống kê ngày hết hạn của sản phẩm - mình đang làm thống kê theo dạng PivotTable nên chưa có nhiều sự lựa chọn tốt nhất nên nhờ hỗ trợ bằng VBA theo như Sheet "PV" (file đính kèm) và hình minh họa bên dưới:

1. Ở B1 nếu chọn khu vực "Miền Tây" và điều kiện ở cột E1 điều kiện = 30 ngày thì lọc ra dữ liệu toàn bộ có số ngày <=30 (dựa theo Sheet "Inventory onhand")

2. Ở trong bảng tính này sẽ tách ra ở cột A: "ngày còn lại" tách ra 3 dòng: 10 ngày, 20 ngày, 30 ngày và tương ứng mỗi ngày sẽ có dòng công thức 'Tổng cộng 10 ngày, 20 ngày, 30 ngày)

3. Cuối cùng sẽ tự động thêm dòng "Grand total" sẽ cộng lại 3 dòng: 10 ngày + 20 ngày + 30 ngày

Xin cảm ơn
View attachment 300843

Nếu Ô E1 có giá trị 40 thì tách thế nào bạn.

tranphuson · 15/5/24

snow25 đã viết:
Nếu Ô E1 có giá trị 40 thì tách thế nào bạn.

Hiện tại mình chỉ dùng cho điều kiện E1 = 30 ngày nên tách theo file mẫu mình đã gửi. Dĩ nhiên mình biết là nhu cầu sẽ có 40, 50 ngày nhưng trước tiên mình cần 30 ngày trước
Tóm lại, nếu bạn cho chọn điều kiện ngày 30, 40,... thì bên dưới bảng thêm dòng ngày chọn điều kiện ( thêm dòng 40 ngày rồi sau đó TỔNG CỘNG) lại là quá tốt

Xin cảm ơn

hocexcel_1991 · 15/5/24

Bạn dùng PowerPivot, chọn ngày còn lại rồi refresh

tranphuson · 15/5/24

hocexcel_1991 đã viết:
Bạn dùng PowerPivot, chọn ngày còn lại rồi refresh
View attachment 300848

Nhưng máy của mình Power Pivot không có sẵn trong Office 2016 và cũng chưa biết sử dụng tính năng này.
Ngoài ra, báo cáo cần gom theo ngày quy định: 10 ngày (những sản phẩm còn date 0 -> 10 ngày), 20 ngày (những sản phẩm date còn 11 ngày đến 20 ngày), 30 ngày (những sản phẩm date từ 21 ngày đến 30 ngày). Như vậy sẽ dễ nhìn hơn và thống kê số lượng sẽ theo dõi
Cảm ơn bạn đã giúp

snow25 · 16/5/24

tranphuson đã viết:
Xin chào các Anh, Chị

Vui lòng hỗ trợ file thống kê ngày hết hạn của sản phẩm - mình đang làm thống kê theo dạng PivotTable nên chưa có nhiều sự lựa chọn tốt nhất nên nhờ hỗ trợ bằng VBA theo như Sheet "PV" (file đính kèm) và hình minh họa bên dưới:

1. Ở B1 nếu chọn khu vực "Miền Tây" và điều kiện ở cột E1 điều kiện = 30 ngày thì lọc ra dữ liệu toàn bộ có số ngày <=30 (dựa theo Sheet "Inventory onhand")

2. Ở trong bảng tính này sẽ tách ra ở cột A: "ngày còn lại" tách ra 3 dòng: 10 ngày, 20 ngày, 30 ngày và tương ứng mỗi ngày sẽ có dòng công thức 'Tổng cộng 10 ngày, 20 ngày, 30 ngày)

3. Cuối cùng sẽ tự động thêm dòng "Grand total" sẽ cộng lại 3 dòng: 10 ngày + 20 ngày + 30 ngày

Xin cảm ơn
View attachment 300843

Cái Wh1 WH2 ,Wh3,Wh4 tính như thế nào nhỉ bạn.Nó sao lại tính tách ra như vậy.

hocexcel_1991 · 16/5/24

tranphuson đã viết:
Nhưng máy của mình Power Pivot không có sẵn trong Office 2016 và cũng chưa biết sử dụng tính năng này.
Ngoài ra, báo cáo cần gom theo ngày quy định: 10 ngày (những sản phẩm còn date 0 -> 10 ngày), 20 ngày (những sản phẩm date còn 11 ngày đến 20 ngày), 30 ngày (những sản phẩm date từ 21 ngày đến 30 ngày). Như vậy sẽ dễ nhìn hơn và thống kê số lượng sẽ theo dõi
Cảm ơn bạn đã giúp

1/Office 2016 đã có sẵn Power Pivot và Power Query rồi, bạn có thể vào tìm google tìm hiểu.
2/Ngoài ra có thể dùng Power Query
3/Như thế này được không?

tranphuson · 16/5/24

hocexcel_1991 đã viết:
1/Office 2016 đã có sẵn Power Pivot và Power Query rồi, bạn có thể vào tìm google tìm hiểu.
2/Ngoài ra có thể dùng Power Query
3/Như thế này được không?
View attachment 300866

Cảm ơn bạn đã giúp. Mình sẽ thử xem thêm google cách sử dụng này

DeTong · 16/5/24

Thêm động lực cho bạn tìm hiểu query từ cơ bản nè!

Click cell C3, C7 và chọn thay đổi các thông số...

HieuCD · 16/5/24

tranphuson đã viết:
Xin chào các Anh, Chị

Vui lòng hỗ trợ file thống kê ngày hết hạn của sản phẩm - mình đang làm thống kê theo dạng PivotTable nên chưa có nhiều sự lựa chọn tốt nhất nên nhờ hỗ trợ bằng VBA theo như Sheet "PV" (file đính kèm) và hình minh họa bên dưới:

1. Ở B1 nếu chọn khu vực "Miền Tây" và điều kiện ở cột E1 điều kiện = 30 ngày thì lọc ra dữ liệu toàn bộ có số ngày <=30 (dựa theo Sheet "Inventory onhand")

2. Ở trong bảng tính này sẽ tách ra ở cột A: "ngày còn lại" tách ra 3 dòng: 10 ngày, 20 ngày, 30 ngày và tương ứng mỗi ngày sẽ có dòng công thức 'Tổng cộng 10 ngày, 20 ngày, 30 ngày)

3. Cuối cùng sẽ tự động thêm dòng "Grand total" sẽ cộng lại 3 dòng: 10 ngày + 20 ngày + 30 ngày

Xin cảm ơn
View attachment 300843

Kết quả ơ sheet1

Mã:

Option Explicit
Sub XYZ()
  Dim arr(), res(), a(), b(), c(), d(), aCol
  Dim sh As Worksheet, rng As Range, dic As Object
  Dim sRow&, N&, i&, r&, k&, ik&, j&, loca$, key$, ngay&
 
  Set dic = CreateObject("scripting.dictionary")
  Set sh = Sheets("Sheet1")
  i = Range("A" & Rows.Count).End(xlUp).Row
  If i > 4 Then Range("A5:N" & i).Clear
  loca = sh.Range("B1").Value
  ngay = sh.Range("E1").Value
  If ngay = 0 Then Exit Sub
 
  N = Int(ngay / 10) - ((ngay Mod 10) > 0)
  ReDim a(1 To N) ' mang cac muc ket qua
  ReDim b(1 To N) 'Thu tu dong tung mang ket qua
  ReDim c(1 To 1000, 1 To 14) 'Mang ket qua tung muc
  For j = 1 To N
    a(j) = c
  Next j
  aCol = Array(0, 29, 1, 2, 5, 8, 17, 10, 27, 28)
 
  With Sheets("Inventory onhand")
    arr = .Range("B2", .Range("AD" & Rows.Count).End(xlUp)).Value
  End With
  sRow = UBound(arr)
 
  For i = 1 To sRow
    If arr(i, 26) = loca And arr(i, 29) <= ngay And arr(i, 29) > 0 Then
      r = Int((arr(i, 29) - 1) / 10) + 1
      key = CStr(r)
      For j = 1 To 8
        key = key & "|" & arr(i, j)
      Next j
      If dic.exists(key) = False Then
        k = k + 1
        b(r) = b(r) + 1
        dic.Add key, b(r)
        For j = 1 To 9
          a(r)(b(r), j) = arr(i, aCol(j))
        Next j
      End If
      ik = dic(key)
      j = CLng(Right(arr(i, 25), 1)) + 9
      a(r)(ik, j) = a(r)(ik, j) + arr(i, 14)
      a(r)(ik, 14) = a(r)(ik, 14) + arr(i, 14)
    End If
  Next i
 
  ReDim c(1 To 1, 1 To 14)
  c(1, 1) = "Grand Total"
  For r = 1 To N
    k = sh.Range("A" & Rows.Count).End(xlUp).Row + 1
    sh.Range("A" & k) = r * 10 & " Ngay"
    If b(r) > 0 Then
      Set rng = sh.Range("A" & k + 1).Resize(b(r), 14)
      rng.Value = a(r)
      rng.Sort rng(1, 1), 1, rng(1, 2), , 1, Header:=xlNo
    
      ReDim d(1 To 1, 1 To 14)
      d(1, 1) = "Tong cong " & r * 10 & " Ngay"
      For i = 1 To b(r)
        For j = 10 To 14
          d(1, j) = d(1, j) + a(r)(i, j)
          c(1, j) = c(1, j) + a(r)(i, j)
        Next j
      Next i
      k = k + b(r) + 1
      sh.Range("A" & k).Resize(, 14) = d
    End If
  Next r
  k = k + 1
  sh.Range("A" & k).Resize(, 14) = c
  sh.Range("A5:N" & k).Borders.LineStyle = 1
End Sub

tranphuson · 16/5/24

HieuCD đã viết:

Kết quả ơ sheet1

Mã:

Option Explicit
Sub XYZ()
  Dim arr(), res(), a(), b(), c(), d(), aCol
  Dim sh As Worksheet, rng As Range, dic As Object
  Dim sRow&, N&, i&, r&, k&, ik&, j&, loca$, key$, ngay&
 
  Set dic = CreateObject("scripting.dictionary")
  Set sh = Sheets("Sheet1")
  i = Range("A" & Rows.Count).End(xlUp).Row
  If i > 4 Then Range("A5:N" & i).Clear
  loca = sh.Range("B1").Value
  ngay = sh.Range("E1").Value
  If ngay = 0 Then Exit Sub
 
  N = Int(ngay / 10) - ((ngay Mod 10) > 0)
  ReDim a(1 To N) ' mang cac muc ket qua
  ReDim b(1 To N) 'Thu tu dong tung mang ket qua
  ReDim c(1 To 1000, 1 To 14) 'Mang ket qua tung muc
  For j = 1 To N
    a(j) = c
  Next j
  aCol = Array(0, 29, 1, 2, 5, 8, 17, 10, 27, 28)
 
  With Sheets("Inventory onhand")
    arr = .Range("B2", .Range("AD" & Rows.Count).End(xlUp)).Value
  End With
  sRow = UBound(arr)
 
  For i = 1 To sRow
    If arr(i, 26) = loca And arr(i, 29) <= ngay And arr(i, 29) > 0 Then
      r = Int((arr(i, 29) - 1) / 10) + 1
      key = CStr(r)
      For j = 1 To 8
        key = key & "|" & arr(i, j)
      Next j
      If dic.exists(key) = False Then
        k = k + 1
        b(r) = b(r) + 1
        dic.Add key, b(r)
        For j = 1 To 9
          a(r)(b(r), j) = arr(i, aCol(j))
        Next j
      End If
      ik = dic(key)
      j = CLng(Right(arr(i, 25), 1)) + 9
      a(r)(ik, j) = a(r)(ik, j) + arr(i, 14)
      a(r)(ik, 14) = a(r)(ik, 14) + arr(i, 14)
    End If
  Next i
 
  ReDim c(1 To 1, 1 To 14)
  c(1, 1) = "Grand Total"
  For r = 1 To N
    k = sh.Range("A" & Rows.Count).End(xlUp).Row + 1
    sh.Range("A" & k) = r * 10 & " Ngay"
    If b(r) > 0 Then
      Set rng = sh.Range("A" & k + 1).Resize(b(r), 14)
      rng.Value = a(r)
      rng.Sort rng(1, 1), 1, rng(1, 2), , 1, Header:=xlNo
   
      ReDim d(1 To 1, 1 To 14)
      d(1, 1) = "Tong cong " & r * 10 & " Ngay"
      For i = 1 To b(r)
        For j = 10 To 14
          d(1, j) = d(1, j) + a(r)(i, j)
          c(1, j) = c(1, j) + a(r)(i, j)
        Next j
      Next i
      k = k + b(r) + 1
      sh.Range("A" & k).Resize(, 14) = d
    End If
  Next r
  k = k + 1
  sh.Range("A" & k).Resize(, 14) = c
  sh.Range("A5:N" & k).Borders.LineStyle = 1
End Sub

File này rất hay và đúng như mình cần, cảm ơn bạn đã hỗ trợ rất nhiều

VetMini · 17/5/24

hocexcel_1991 đã viết:
1/Office 2016 đã có sẵn Power Pivot và Power Query rồi, bạn có thể vào tìm google tìm hiểu.
2/Ngoài ra có thể dùng Power Query
3/Như thế này được không?
...

DeTong đã viết:
Thêm động lực cho bạn tìm hiểu query từ cơ bản nè!

Click cell C3, C7 và chọn thay đổi các thông số...

Có VBA chạy đúng rồi, chỉ thằng khùng mới đi nghiên cứu thêm về ba cái mớ Power's.

Thống kê ngày hết hạn của sản phẩm

tranphuson

Thành viên thường trực

File đính kèm

Hoàng Tuấn 868

Đăng ký hôm qua

snow25

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

tranphuson

Thành viên thường trực

hocexcel_1991

Bắt đầu học Excel

File đính kèm

tranphuson

Thành viên thường trực

snow25

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

hocexcel_1991

Bắt đầu học Excel

tranphuson

Thành viên thường trực

DeTong

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

HieuCD

Chuyên gia GPE

File đính kèm

tranphuson

Thành viên thường trực

VetMini

Ăn cùng góc phố

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