Xin giúp đỡ về hàm thống kê thỏa mãn nhiều điều kiện (1 người xem)

[Nguyễn Hồng Quang]

Em có gặp 1 vấn đề thống kê vượt quá khả năng tư duy của em.
Mong các thầy giúp đỡ em hàm trong vấn đế thống kê đơn giá như sau:
Em có tạo Bảng cần thống kê đơn giá của các mặt hàng thỏa mãn 4 điều kiện (Mã hàng, Mã NCC, Phiếu nhập và lần thay đổi đơn giá).
Lần thay đổi đơn giá được hiểu là: Cứ mỗi lần thay đổi đơn giá của lần nhập sau được hiểu là 1 lần thay đổi tuy nhiên nếu đơn giá của lần nhập sau bằng đơn giá của lần nhập ngay trước đó thì bỏ qua không phải thống kê.
( Ghi chú: Bảng Data Nhap xuat đã được sort theo thứ tự ngày tháng, nên các lần nhập sau được hiểu là các lần nhập của các ngày tăng dần)
Xin gửi file đính kèm để tiện theo dõi

 

[befaint]

Dùng pivot làm bước trung gian rồi tổng hợp lại...

 

[eke_rula]

Em có gặp 1 vấn đề thống kê vượt quá khả năng tư duy của em.
Mong các thầy giúp đỡ em hàm trong vấn đế thống kê đơn giá như sau:
Em có tạo Bảng cần thống kê đơn giá của các mặt hàng thỏa mãn 4 điều kiện (Mã hàng, Mã NCC, Phiếu nhập và lần thay đổi đơn giá).
Lần thay đổi đơn giá được hiểu là: Cứ mỗi lần thay đổi đơn giá của lần nhập sau được hiểu là 1 lần thay đổi tuy nhiên nếu đơn giá của lần nhập sau bằng đơn giá của lần nhập ngay trước đó thì bỏ qua không phải thống kê.
( Ghi chú: Bảng Data Nhap xuat đã được sort theo thứ tự ngày tháng, nên các lần nhập sau được hiểu là các lần nhập của các ngày tăng dần)
Xin gửi file đính kèm để tiện theo dõi

Dùng 1 CT thì hơi phức tạp, mình dùng Cột phụ cho đơn giản tý!!!

 

[befaint]

... [Tiếp theo #2]
Thêm đoạn sau để tổng hợp [Mã SP] [Mã NCC] [Loại phiếu] và [Giá các lần thay đổi...]

Sub DG()
Dim ws As Worksheet, tmp As Variant, KQ() As Variant, z As Long, x As Long
Dim r As Long, c As Long, T, k As Long
Set ws = Sheets("Pivot")
tmp = ws.UsedRange: z = UBound(tmp, 1) - 2: x = UBound(tmp, 2)
ReDim KQ(1 To z, 1 To x)
For r = 1 To z
    KQ(r, 1) = tmp(r + 2, 2)
    KQ(r, 2) = tmp(r + 2, 1)
    KQ(r, 3) = tmp(r + 2, 3)
    k = 4
    For c = 4 To x
        T = tmp(r + 2, c)
        If T <> Empty And IsNumeric(T) Then
            If KQ(r, k - 1) <> T Then
                KQ(r, k) = T: k = k + 1
            End If
        End If
    Next c
Next r
Sheets("Thong ke don gia").Range("A3").Resize(1000, 300).ClearContents
Sheets("Thong ke don gia").Range("A3").Resize(z, x) = KQ
End Sub

 

[HieuCD]

Em có gặp 1 vấn đề thống kê vượt quá khả năng tư duy của em.
Mong các thầy giúp đỡ em hàm trong vấn đế thống kê đơn giá như sau:
Em có tạo Bảng cần thống kê đơn giá của các mặt hàng thỏa mãn 4 điều kiện (Mã hàng, Mã NCC, Phiếu nhập và lần thay đổi đơn giá).
Lần thay đổi đơn giá được hiểu là: Cứ mỗi lần thay đổi đơn giá của lần nhập sau được hiểu là 1 lần thay đổi tuy nhiên nếu đơn giá của lần nhập sau bằng đơn giá của lần nhập ngay trước đó thì bỏ qua không phải thống kê.
( Ghi chú: Bảng Data Nhap xuat đã được sort theo thứ tự ngày tháng, nên các lần nhập sau được hiểu là các lần nhập của các ngày tăng dần)
Xin gửi file đính kèm để tiện theo dõi

dùng cột phụ để tính

 

[HieuCD]

dùng VBA

Sub GPE()
Dim Sarr(), Darr(), Arr(), Dic As Object, Dk As String, Tmp As String
Dim i As Long, k As Long, j As Integer, N As Integer
Set Dic = CreateObject("Scripting.Dictionary")
With Sheets("Data Nhap xuat")
  Dk = .Range("F1").Value
  Darr = .Range("A2:E" & .Range("A2").End(xlDown).Row).Value
End With
ReDim Sarr(1 To UBound(Darr), 1 To 3)
For i = 1 To UBound(Darr)
  If Darr(i, 3) = Dk Then
    Tmp = Darr(i, 1) & "#" & Darr(i, 2)
    If Not Dic.exists(Tmp) Then
      Dic.Add Tmp, Array(1, Darr(i, 5))
      Dic.Add Tmp & "#" & 1, Darr(i, 5)
      k = k + 1
      Sarr(k, 1) = Darr(i, 1):  Sarr(k, 2) = Darr(i, 2): Sarr(k, 3) = Darr(i, 3)
    Else
      If Dic.Item(Tmp)(1) <> Darr(i, 5) Then
        Dic.Item(Tmp) = Array(Dic.Item(Tmp)(0) + 1, Darr(i, 5))
        Dic.Add Tmp & "#" & Dic.Item(Tmp)(0), Darr(i, 5)
        If N < Dic.Item(Tmp)(0) Then N = Dic.Item(Tmp)(0)
      End If
    End If
  End If
Next i
ReDim Arr(1 To k, 1 To N)
For i = 1 To k
  For j = 1 To N
    Tmp = Sarr(i, 1) & "#" & Sarr(i, 2) & "#" & j
    Arr(i, j) = Dic.Item(Tmp)
  Next j
Next i
With Sheets("Thong ke don gia")
  .Range("A3:X1000").ClearContents
  .Range("A3").Resize(k, 3) = Sarr
  .Range("D3").Resize(k, N) = Arr
End With
End Sub

 

[Nguyễn Hồng Quang]

dùng cột phụ để tính

Thưa thầy . Em hiểu công thức của thầy rồi (đánh số thứ tự theo sự thay đổi của đơn giá).
F2=IF(OR(AND(C2="Phiếu nhập";IFERROR(LOOKUP(2;1/($A1:A$2=A2)/($B1:B$2=B2);$E1:E$2);"")<>E2);ROW()=2);A2&B2&" "&COUNTIF($F$1:F1;A2&B2&"*")+1;"")
Nhưng mà em ko thấy thầy xét đến điều kiện Loại Phiếu= Phiếu Nhập trong đoạn (Lookup(2;1/........) ở trong công thức này ạ
cho em hỏi thêm ạ.
Ở cột phụ của thầy, khi em chuyển sang đánh số thứ tự theo sự thay đổi của ngày tháng thì công thức lại không ra kết quả như ý muốn ạ
Em chuyển thành
F2=IF(OR(AND(C2="Phiếu nhập";IFERROR(LOOKUP(2;1/($A1:A$2=A2)/($B1:B$2=B2);$D1$2);"")<>D2);ROW()=2);A2&B2&" "&COUNTIF($F$1:F1;A2&B2&"*")+1;"")
Mong thầy chỉ giáo thêm

 

[Nguyễn Hồng Quang]

Dùng pivot làm bước trung gian rồi tổng hợp lại...

Em cũng làm pivot như thầy (3 cột Mã NCC, Mã SP, Loại Phiếu ở Row Label, cột Ngày tháng ở Column Label, cột Đơn giá ở Values)
Sao File của em không ra được cách trình bày của thầy ạ. Mong thầy cho thêm gợi ý

 

[befaint]

Em cũng làm pivot như thầy (3 cột Mã NCC, Mã SP, Loại Phiếu ở Row Label, cột Ngày tháng ở Column Label, cột Đơn giá ở Values)
Sao File của em không ra được cách trình bày của thầy ạ. Mong thầy cho thêm gợi ý

Vào pivot field list
Mục Row Labels, click vào từng Field, Field settings..., cửa sổ hiện ra (Field settings)
- Tab Subbtotals & Filters: None
- Tab Layout & Print:
Show item labels in tabulas form
Check dòng Repeat item lables (Với {Loại Phiếu} thì không cần check dòng này)

 

[Nguyễn Hồng Quang]

Dùng 1 CT thì hơi phức tạp, mình dùng Cột phụ cho đơn giản tý!!!

Cảm ơn bạn eke_rula đã cho mình thêm 1 cách xử lý khác.

 

[Nguyễn Hồng Quang]

... [Tiếp theo #2]
Thêm đoạn sau để tổng hợp [Mã SP] [Mã NCC] [Loại phiếu] và [Giá các lần thay đổi...]

Sub DG()
Dim ws As Worksheet, tmp As Variant, KQ() As Variant, z As Long, x As Long
Dim r As Long, c As Long, T, k As Long
Set ws = Sheets("Pivot")
tmp = ws.UsedRange: z = UBound(tmp, 1) - 2: x = UBound(tmp, 2)
ReDim KQ(1 To z, 1 To x)
For r = 1 To z
    KQ(r, 1) = tmp(r + 2, 2)
    KQ(r, 2) = tmp(r + 2, 1)
    KQ(r, 3) = tmp(r + 2, 3)
    k = 4
    For c = 4 To x
        T = tmp(r + 2, c)
        If T <> Empty And IsNumeric(T) Then
            If KQ(r, k - 1) <> T Then
                KQ(r, k) = T: k = k + 1
            End If
        End If
    Next c
Next r
Sheets("Thong ke don gia").Range("A3").Resize(1000, 300).ClearContents
Sheets("Thong ke don gia").Range("A3").Resize(z, x) = KQ
End Sub

Hình như đoạn code này ko hoạt động thưa thầy.
Vì sau khi pivot table trả kết quả như file của thầy, em chạy code thì nó vẫn giữ nguyên kết quả thống kê.

 

[befaint]

Hình như đoạn code này ko hoạt động thưa thầy. Vì sau khi pivot table trả kết quả như file của thầy, em chạy code thì nó vẫn giữ nguyên kết quả thống kế.

Nêu vậy thì khó lắm, còn xem bạn áp dụng như nào.

 

[HieuCD]

Thưa thầy . Em hiểu công thức của thầy rồi (đánh số thứ tự theo sự thay đổi của đơn giá).
F2=IF(OR(AND(C2="Phiếu nhập";IFERROR(LOOKUP(2;1/($A1:A$2=A2)/($B1:B$2=B2);$E1:E$2);"")<>E2);ROW()=2);A2&B2&" "&COUNTIF($F$1:F1;A2&B2&"*")+1;"")
Nhưng cho em hỏi thêm ạ.
Ở cột phụ của thầy, khi em chuyển sang đánh số thứ tự theo sự thay đổi của ngày tháng thì công thức lại không ra kết quả như ý muốn ạ
Em chuyển thành
F2=IF(OR(AND(C2="Phiếu nhập";IFERROR(LOOKUP(2;1/($A1:A$2=A2)/($B1:B$2=B2);$D1$2);"")<>D2);ROW()=2);A2&B2&" "&COUNTIF($F$1:F1;A2&B2&"*")+1;"")
Mong thầy chỉ giáo thêm

2 công thức còn thiếu 1 điều kiện

F2 =IF(OR(AND(C2="Phiếu nhập",IFERROR(LOOKUP(2,1/($A1:A$2=A2)/($B1:B$2=B2)/($C1:C$2="Phiếu nhập"),$E1:E$2),"")<>E2),ROW()=2),A2&B2&" "&COUNTIF($F$1:F1,A2&B2&"*")+1,"")

F2 =IF(OR(AND(C2="Phiếu nhập",IFERROR(LOOKUP(2,1/($A1:A$2=A2)/($B1:B$2=B2)/($C1:C$2="Phiếu nhập"),$D1:D$2),"")<>D2),ROW()=2),A2&B2&" "&COUNTIF($F$1:F1,A2&B2&"*")+1,"")

 

[Nguyễn Hồng Quang]

Vào pivot field list
Mục Row Labels, click vào từng Field, Field settings..., cửa sổ hiện ra (Field settings)
- Tab Subbtotals & Filters: None
- Tab Layout & Print:
Show item labels in tabulas form
Check dòng Repeat item lables (Với {Loại Phiếu} thì không cần check dòng này)

Em đã làm đúng như chỉ dẫn của thầy.
Nhưng cột Mã NCC nó không trình bày được như của thầy. Nó lại trình bày kiểu Header trên cùng (chứ không Fill các ô như cách trình bày của thầy), lục tìm mãi trong Field settings ko thấy. Có phải do em dùng Excel 2007, nên ko có các tùy chỉnh kiểu này phải ko ạ
Với lại Column Label của Ngày tháng làm thế nào để nó chỉ hiện ra ngày như của thầy ạ. Của em nó cứ hiện ra đầy đủ theo kiểu dd/mm/yyyy

 

[Nguyễn Hồng Quang]

2 công thức còn thiếu 1 điều kiện

F2 =IF(OR(AND(C2="Phiếu nhập",IFERROR(LOOKUP(2,1/($A1:A$2=A2)/($B1:B$2=B2)/($C1:C$2="Phiếu nhập"),$E1:E$2),"")<>E2),ROW()=2),A2&B2&" "&COUNTIF($F$1:F1,A2&B2&"*")+1,"")

F2 =IF(OR(AND(C2="Phiếu nhập",IFERROR(LOOKUP(2,1/($A1:A$2=A2)/($B1:B$2=B2)/($C1:C$2="Phiếu nhập"),$D1:D$2),"")<>D2),ROW()=2),A2&B2&" "&COUNTIF($F$1:F1,A2&B2&"*")+1,"")

Cảm ơn thầy đã cho e 1 công thức và cách làm hay cho các tình huống thống kê theo nhiều điều kiện. ko biết bài viết này có thể được các BQT lưu vào thư viện để mọi người cùng tham khảo ko ạ. Trươc khi viết em có lục tìm trong GPE nhưng ko tìm thấy hoặc có tìm thấy nhưng cách trình bày ko mạch lạc dẫn đến đọc ko hiểu

 

[befaint]

Em đã làm đúng như chỉ dẫn của thầy.
Nhưng cột Mã NCC nó không trình bày được như của thầy. Nó lại trình bày kiểu Header trên cùng (chứ không Fill các ô như cách trình bày của thầy), lục tìm mãi trong Field settings ko thấy. Có phải do em dùng Excel 2007, nên ko có các tùy chỉnh kiểu này phải ko ạ
Với lại Column Label của Ngày tháng làm thế nào để nó chỉ hiện ra ngày như của thầy ạ. Của em nó cứ hiện ra đầy đủ theo kiểu dd/mm/yyyy

(1) Field settings:

(2) Bỏ Grand Total:
Click vào vùng pivot, menu ribbon, Design, Grand Totals: Off for Rows and Columns

(3) chỉ hiện ra ngày:
Chọn vùng, format cells, custom: dd

 

[Nguyễn Hồng Quang]

(2) Bỏ Grand Total:
Click vào vùng pivot, menu ribbon, Design, Grand Totals: Off for Rows and Columns

(3) chỉ hiện ra ngày:
Chọn vùng, format cells, custom: dd

Cảm ơn thầy
Trong dân gian ngày nay vẫn có câu: "Ko có việc gì khó, chỉ sợ các thầy GPE không trả lời", em thấy thật là đúng

 

[thangbithu]

Công thức:
D3 = IFERROR(LOOKUP(2,1/('Data Nhap xuat'!$A$2:$A$27='Thong ke don gia'!$A3)/('Data Nhap xuat'!$B$2:$B$27='Thong ke don gia'!$B3)/('Data Nhap xuat'!$C$2:$C$27='Thong ke don gia'!$C3)/(DAY('Data Nhap xuat'!$D$2:$D$27)='Thong ke don gia'!D$2),'Data Nhap xuat'!$E$2:$E$27),"")