Hàm Thống Kê Dữ Liệu

[thaty]

Mong các bạn giúp đỡ! Cảm ơn các Bạn nhiều nhiều!

Mình có một file excel mà muốn thống kê số tiền bán được cho MỖI NGÀY TRONG THÁNG 8 và cho mỗi MÃ HÀNG.

Sheet "Tiền" là nơi thống kê
Sheet "Dữ liệu" là nơi chứa các dữ liệu cần thống kê
1. Từ (A4:AD203) là ngày cần thống kê trong tháng 8, tương ứng với dòng từ (B150: AF150) của sheet "Tiền"
2. Cột (AV4:AV203) là mã hàng, nó tương ứng với cột (A151:A163) của sheet "Tiền"
3. Cột (AR4:AR203) là doanh thu tương ứng với mỗi ngày.

Cảm ơn!

 

[HieuCD]

Mong các bạn giúp đỡ! Cảm ơn các Bạn nhiều nhiều!

Mình có một file excel mà muốn thống kê số tiền bán được cho MỖI NGÀY TRONG THÁNG 8 và cho mỗi MÃ HÀNG.

Sheet "Tiền" là nơi thống kê
Sheet "Dữ liệu" là nơi chứa các dữ liệu cần thống kê
1. Từ (A4:AD203) là ngày cần thống kê trong tháng 8, tương ứng với dòng từ (B150: AF150) của sheet "Tiền"
2. Cột (AV4:AV203) là mã hàng, nó tương ứng với cột (A151:A163) của sheet "Tiền"
3. Cột (AR4:AR203) là doanh thu tương ứng với mỗi ngày.

Cảm ơn!

B151 =SUMPRODUCT('Dữ liệu'!$AR$4:$AR$203*('Dữ liệu'!$A$4:$AD$203=B$150)*('Dữ liệu'!$AV$4:$AV$203=$A151))

 

[VetMini]

Cái này người ta thêm một cột tính ra tháng. Rồi dùng Pivot Tabel, Slicers.

 

[thaty]

B151 =SUMPRODUCT('Dữ liệu'!$AR$4:$AR$203*('Dữ liệu'!$A$4:$AD$203=B$150)*('Dữ liệu'!$AV$4:$AV$203=$A151))

Bạn ơi mình làm được rồi. Cảm ơn bạn nhiều lắm!!!

Bài đã được tự động gộp: 16/7/20

Cái này người ta thêm một cột tính ra tháng. Rồi dùng Pivot Tabel, Slicers.

Cảm ơn bạn nhiều nhiều nhé!

 

[thaty]

Bạn ơi mình làm được rồi. Cảm ơn bạn nhiều lắm!!!

Bài đã được tự động gộp: 16/7/20

Cảm ơn bạn nhiều nhiều nhé!

B151 =SUMPRODUCT('Dữ liệu'!$AR$4:$AR$203*('Dữ liệu'!$A$4:$AD$203=B$150)*('Dữ liệu'!$AV$4:$AV$203=$A151))

Bài đã được tự động gộp: 18/8/20

B151 =SUMPRODUCT('Dữ liệu'!$AR$4:$AR$203*('Dữ liệu'!$A$4:$AD$203=B$150)*('Dữ liệu'!$AV$4:$AV$203=$A151))

Bạn ơi cho mình hỏi thêm bạn một ý này nữa được không?

Dữ liệu'!$A$4:$AD$20: Là mình tự chèn thủ công vào khi giá trị cột AE>1. Thay vì mình tự thủ công như vậy thì có cách nào để excel tự áp dụng cho những ngày còn lại khi giá trị cột AE>1 không bạn? Nếu được vậy thì tốt quá. Mình có thể xóa hết từ cột $B$4:$AD$20.

Ví dụ:
Cột A155 = 03/08/2020
Cột AE155 = 2
==> Cột A155 = 03/08/2020 và Cột B155 = 04/08/2020
==> Excel tự tính thêm giá trị tương tự cho ngày 04/08/2020 nữa

Cảm ơn bạn nhiều lắm bạn ơi!

Monday, 3 August, 2020​

 

[HieuCD]

Bài đã được tự động gộp: 18/8/20

Bạn ơi cho mình hỏi thêm bạn một ý này nữa được không?

Dữ liệu'!$A$4:$AD$20: Là mình tự chèn thủ công vào khi giá trị cột AE>1. Thay vì mình tự thủ công như vậy thì có cách nào để excel tự áp dụng cho những ngày còn lại khi giá trị cột AE>1 không bạn? Nếu được vậy thì tốt quá. Mình có thể xóa hết từ cột $B$4:$AD$20.

Ví dụ:
Cột A155 = 03/08/2020
Cột AE155 = 2
==> Cột A155 = 03/08/2020 và Cột B155 = 04/08/2020
==> Excel tự tính thêm giá trị tương tự cho ngày 04/08/2020 nữa

Cảm ơn bạn nhiều lắm bạn ơi!

Monday, 3 August, 2020​

B151 =SUMPRODUCT('Dữ liệu'!$AR$4:$AR$203*('Dữ liệu'!$A$4:$A$203<=B$150)*('Dữ liệu'!$A$4:$A$203+'Dữ liệu'!$AE$4:$AE$203-1>=B$150)*('Dữ liệu'!$AV$4:$AV$203=$A151))

 

[thaty]

B151 =SUMPRODUCT('Dữ liệu'!$AR$4:$AR$203*('Dữ liệu'!$A$4:$A$203<=B$150)*('Dữ liệu'!$A$4:$A$203+'Dữ liệu'!$AE$4:$AE$203-1>=B$150)*('Dữ liệu'!$AV$4:$AV$203=$A151))

Bạn ơi, Mình áp dụng công thức tương tự vào file excel mới này nhưng nó không ra (File excel mới thì cần có thêm điều kiện là Sheet1!$M:$M=Corp!$B$1.
Ô cần tính là: Corp!AJ$204

Cảm ơn bạn lắm!

 

[HieuCD]

Bạn ơi, Mình áp dụng công thức tương tự vào file excel mới này nhưng nó không ra (File excel mới thì cần có thêm điều kiện là Sheet1!$M:$M=Corp!$B$1.
Ô cần tính là: Corp!AJ$204

Cảm ơn bạn lắm!

Hàm sumproduce rất nặng, vùng tham chiếu nguyên cột càng nặng file hơn, tạm tính dòng 500, bạn chỉnh lại cho phù hợp
=SUMPRODUCT(Sheet1!$J$4:$J$500*(Sheet1!$D$4:$D$500<=Corp!AJ$203)*(Sheet1!$D$4:$D$500+Sheet1!$H$4:$H$500-1>=Corp!AJ$203)*(Sheet1!$C$4:$C$500=Corp!$B204)*(Sheet1!$M$4:$M$500=Corp!$B$1))

 

[thaty]

Hàm sumproduce rất nặng, vùng tham chiếu nguyên cột càng nặng file hơn, tạm tính dòng 500, bạn chỉnh lại cho phù hợp
=SUMPRODUCT(Sheet1!$J$4:$J$500*(Sheet1!$D$4:$D$500<=Corp!AJ$203)*(Sheet1!$D$4:$D$500+Sheet1!$H$4:$H$500-1>=Corp!AJ$203)*(Sheet1!$C$4:$C$500=Corp!$B204)*(Sheet1!$M$4:$M$500=Corp!$B$1))

Cảm ơn bạn rất nhiều. Mà sao bạn siêu thế

 

[thaty]

@HieuCD
Bạn ơi, mong bạn giúp mình nốt file này nữa được không? Có duyên gặp nhau xin hậu tạ!!!

 

[HieuCD]

@HieuCD
Bạn ơi, mong bạn giúp mình nốt file này nữa được không? Có duyên gặp nhau xin hậu tạ!!!

Y4 =SUMPRODUCT(($B$1=TRIM($AZ$6:$AZ$8))*($C4=$AT$6:$AT$8)*TEXT(TEXT($AW$6:$AW$8-$AV$6:$AV$8,"[>0];\0")-TEXT($AW$6:$AW$8-EOMONTH(DATEVALUE("1-" &LOOKUP("zz",$C$2:Y$2)),0)-1,"[>0];\0")-TEXT(DATEVALUE("1-" &LOOKUP("zz",$C$2:Y$2))-$AV$6:$AV$8,"[>0];\0"),"[>0];\0"))
Z4 =SUMPRODUCT(($B$1=TRIM($AZ$6:$AZ$8))*($C4=$AT$6:$AT$8)*TEXT(TEXT($AW$6:$AW$8-$AV$6:$AV$8,"[>0];\0")-TEXT($AW$6:$AW$8-EOMONTH(DATEVALUE("1-" &LOOKUP("zz",$C$2:Z$2)),0)-1,"[>0];\0")-TEXT(DATEVALUE("1-" &LOOKUP("zz",$C$2:Z$2))-$AV$6:$AV$8,"[>0];\0"),"[>0];\0")*$BA$6:$BA$8/$AX$6:$AX$8)

Copy xuống dòng 30

Y31 =SUMPRODUCT(($B$1=TRIM($AZ$6:$AZ$8))*(COUNTIF($C$4:$C$30,$AT$6:$AT$8)=0)*TEXT(TEXT($AW$6:$AW$8-$AV$6:$AV$8,"[>0];\0")-TEXT($AW$6:$AW$8-EOMONTH(DATEVALUE("1-" &LOOKUP("zz",$C$2:Y$2)),0)-1,"[>0];\0")-TEXT(DATEVALUE("1-" &LOOKUP("zz",$C$2:Y$2))-$AV$6:$AV$8,"[>0];\0"),"[>0];\0"))
Z31 =SUMPRODUCT(($B$1=TRIM($AZ$6:$AZ$8))*(COUNTIF($C$4:$C$30,$AT$6:$AT$8)=0)*TEXT(TEXT($AW$6:$AW$8-$AV$6:$AV$8,"[>0];\0")-TEXT($AW$6:$AW$8-EOMONTH(DATEVALUE("1-" &LOOKUP("zz",$C$2:Z$2)),0)-1,"[>0];\0")-TEXT(DATEVALUE("1-" &LOOKUP("zz",$C$2:Z$2))-$AV$6:$AV$8,"[>0];\0"),"[>0];\0")*$BA$6:$BA$8/$AX$6:$AX$8)

copy 2 cột dán vào các cột tương ứng

 

[thaty]

Y4 =SUMPRODUCT(($B$1=TRIM($AZ$6:$AZ$8))*($C4=$AT$6:$AT$8)*TEXT(TEXT($AW$6:$AW$8-$AV$6:$AV$8,"[>0];\0")-TEXT($AW$6:$AW$8-EOMONTH(DATEVALUE("1-" &LOOKUP("zz",$C$2:Y$2)),0)-1,"[>0];\0")-TEXT(DATEVALUE("1-" &LOOKUP("zz",$C$2:Y$2))-$AV$6:$AV$8,"[>0];\0"),"[>0];\0"))
Z4 =SUMPRODUCT(($B$1=TRIM($AZ$6:$AZ$8))*($C4=$AT$6:$AT$8)*TEXT(TEXT($AW$6:$AW$8-$AV$6:$AV$8,"[>0];\0")-TEXT($AW$6:$AW$8-EOMONTH(DATEVALUE("1-" &LOOKUP("zz",$C$2:Z$2)),0)-1,"[>0];\0")-TEXT(DATEVALUE("1-" &LOOKUP("zz",$C$2:Z$2))-$AV$6:$AV$8,"[>0];\0"),"[>0];\0")*$BA$6:$BA$8/$AX$6:$AX$8)

Copy xuống dòng 30

Y31 =SUMPRODUCT(($B$1=TRIM($AZ$6:$AZ$8))*(COUNTIF($C$4:$C$30,$AT$6:$AT$8)=0)*TEXT(TEXT($AW$6:$AW$8-$AV$6:$AV$8,"[>0];\0")-TEXT($AW$6:$AW$8-EOMONTH(DATEVALUE("1-" &LOOKUP("zz",$C$2:Y$2)),0)-1,"[>0];\0")-TEXT(DATEVALUE("1-" &LOOKUP("zz",$C$2:Y$2))-$AV$6:$AV$8,"[>0];\0"),"[>0];\0"))
Z31 =SUMPRODUCT(($B$1=TRIM($AZ$6:$AZ$8))*(COUNTIF($C$4:$C$30,$AT$6:$AT$8)=0)*TEXT(TEXT($AW$6:$AW$8-$AV$6:$AV$8,"[>0];\0")-TEXT($AW$6:$AW$8-EOMONTH(DATEVALUE("1-" &LOOKUP("zz",$C$2:Z$2)),0)-1,"[>0];\0")-TEXT(DATEVALUE("1-" &LOOKUP("zz",$C$2:Z$2))-$AV$6:$AV$8,"[>0];\0"),"[>0];\0")*$BA$6:$BA$8/$AX$6:$AX$8)

copy 2 cột dán vào các cột tương ứng

Bạn ơi, mình giơ tay xin hàng rồi. Công thức này khó quá mình không áp dụng vào file gốc của mình được. Mình gửi lại bạn file gốc, bạn dành chút thời gian giúp mình xử lý công thức trên file gốc này luôn bạn nhé. Xin lỗi vì phiền bạn nhiều quá!
À, cột $R4$:$R5500$ là giá trung bình của mỗi đêm tiền phòng rồi bạn nhé, không phải là tổng tiền nên không cần chia lại cho cột $G4$:$G5500$. Mình không biết dùng công thức nên đành tạo thêm cột này là bước đệm.
(R4=SUM(H4:I4)/G4)
Bạn giúp mình cho mã code: CORP nhé. Những mã còn lại mình sẽ làm theo.

 

[n0thing1988]

Bạn ơi, mình giơ tay xin hàng rồi. Công thức này khó quá mình không áp dụng vào file gốc của mình được. Mình gửi lại bạn file gốc, bạn dành chút thời gian giúp mình xử lý công thức trên file gốc này luôn bạn nhé. Xin lỗi vì phiền bạn nhiều quá!
À, cột $R4$:$R5500$ là giá trung bình của mỗi đêm tiền phòng rồi bạn nhé, không phải là tổng tiền nên không cần chia lại cho cột $G4$:$G5500$. Mình không biết dùng công thức nên đành tạo thêm cột này là bước đệm.
(R4=SUM(H4:I4)/G4)
Bạn giúp mình cho mã code: CORP nhé. Những mã còn lại mình sẽ làm theo.

Bạn không áp dụng được công thức trên thì áp dụng công thức sumproduct đơn giản nhất. Tách thêm 2 cột số ngày nghỉ trong tháng 8, và số ngày nghỉ trong tháng 9. Sau đó sumproduct lại là ra kết quả.

 

[HieuCD]

Bạn ơi, mình giơ tay xin hàng rồi. Công thức này khó quá mình không áp dụng vào file gốc của mình được. Mình gửi lại bạn file gốc, bạn dành chút thời gian giúp mình xử lý công thức trên file gốc này luôn bạn nhé. Xin lỗi vì phiền bạn nhiều quá!
À, cột $R4$:$R5500$ là giá trung bình của mỗi đêm tiền phòng rồi bạn nhé, không phải là tổng tiền nên không cần chia lại cho cột $G4$:$G5500$. Mình không biết dùng công thức nên đành tạo thêm cột này là bước đệm.
(R4=SUM(H4:I4)/G4)
Bạn giúp mình cho mã code: CORP nhé. Những mã còn lại mình sẽ làm theo.

Tạo công thức dòng 1 để công thức chính gọn hơn
Tạm lấy 5500 dòng dữ liệu, bạn tùy chỉnh phù hợp với file thực tế
Hàm Sumproduct rất nặng
Copy 2 cột dán vào các cột tương ứng
Xem file

 

[thaty]

Tạo công thức dòng 1 để công thức chính gọn hơn
Tạm lấy 5500 dòng dữ liệu, bạn tùy chỉnh phù hợp với file thực tế
Hàm Sumproduct rất nặng
Copy 2 cột dán vào các cột tương ứng
Xem file

Bạn không biết rằng Bạn đã giúp mình được nhiều như thế nào đâu! Cảm ơn "hai món quà" của bạn!!!

Bạn không áp dụng được công thức trên thì áp dụng công thức sumproduct đơn giản nhất. Tách thêm 2 cột số ngày nghỉ trong tháng 8, và số ngày nghỉ trong tháng 9. Sau đó sumproduct lại là ra kết quả.

Cảm ơn bạn nhiều nhiều nhé. Mình học được rất nhiều từ bạn!

 

[tranhungdao12a3]

Tôi làm thử, có chỉnh lại data cho phù hợp với Power Bi. Tạo 2 Measure tháng 8 và tháng 9 tách số liệu. Kết quả ra như sau:

Nếu bạn cần một kết quả thì gửi data tôi xuất ra cho bạn. Bạn cần công thức để Link thì xem hướng dẫn của anh HieuCD. Nếu data lớn tôi nghĩ Sumproduct chạy không nổi, nên nghĩ cách khác nếu dữ liệu lớn.

 

[excel_lv1.5]

Bạn ơi, mình giơ tay xin hàng rồi. Công thức này khó quá mình không áp dụng vào file gốc của mình được. Mình gửi lại bạn file gốc, bạn dành chút thời gian giúp mình xử lý công thức trên file gốc này luôn bạn nhé. Xin lỗi vì phiền bạn nhiều quá!
À, cột $R4$:$R5500$ là giá trung bình của mỗi đêm tiền phòng rồi bạn nhé, không phải là tổng tiền nên không cần chia lại cho cột $G4$:$G5500$. Mình không biết dùng công thức nên đành tạo thêm cột này là bước đệm.
(R4=SUM(H4:I4)/G4)
Bạn giúp mình cho mã code: CORP nhé. Những mã còn lại mình sẽ làm theo.

Dữ liệu nhiều thì nên làm pivot bạn ạ

 

[HieuCD]

Dữ liệu nhiều thì nên làm pivot bạn ạ

Nếu có dữ liệu 28/8/2020 đến 3/9/2020 làm sao tách ra

 

[excel_lv1.5]

Nếu có dữ liệu 28/8/2020 đến 3/9/2020 làm sao tách ra

Lúc đầu tôi làm theo công thức của bạn , nên cũng chưa hiểu hết bài toán, giờ coi lại thì đã hiểu, như vậy thì đơn giản hơn, tôi chỉnh lại pivot có sai số với bạn khi sửa Data như vầy, kết quả JP tháng 08 của tôi là 35 của bạn là 36, tôi tính tay thì ra 35

 

[HieuCD]

Lúc đầu tôi làm theo công thức của bạn , nên cũng chưa hiểu hết bài toán, giờ coi lại thì đã hiểu, như vậy thì đơn giản hơn, tôi chỉnh lại pivot có sai số với bạn khi sửa Data như vầy, kết quả JP tháng 08 của tôi là 35 của bạn là 36, tôi tính tay thì ra 35
View attachment 243736

Từ 28/8/2020 đến 2/9/2020
Cách tính số ngày từng tháng
Tổng số ngày = 2/9/2020 - 28/8/2020
Số ngày của tháng 8 = 1/9/2020 - 28/8/2020 = 4
Số ngày của tháng 9 = 2/9/2020 - 1/9/2020 = 1
Hoặc
Ngày 2/9/2020 không tính, chỉ tính trước 1 ngày: 1/9/2020
Tổng số ngày = 1/9/2020 - 28/8/2020 +1
Số ngày của tháng 8 = 31/8/2020 - 28/8/2020 + 1 = 4
Số ngày của tháng 9 = 1/9/2020 - 1/9/2020 + 1 = 1