Giúp em công thức chọn từ ngày đến ngày cho chạy số liệu tương ứng? (1 người xem)

[hitlecp]

Chào các anh/chị,
Em có tạo công thức cho kết quả ở sheet "BM_TheoMa". Nhưng giờ em có thêm Ngày tích thu tiền là: khi nhập từ ngày đến ngày thì số liệu sẽ nhảy tương ứng như kết quả (dữ liệu lấy từ sheet "Dulieu") mà không biết công thức như thế nào.
Nhờ các anh/chị giúp em ạ. Em cám ơn!

 

[HieuCD]

Chào các anh/chị,
Em có tạo công thức cho kết quả ở sheet "BM_TheoMa". Nhưng giờ em có thêm Ngày tích thu tiền là: khi nhập từ ngày đến ngày thì số liệu sẽ nhảy tương ứng như kết quả (dữ liệu lấy từ sheet "Dulieu") mà không biết công thức như thế nào.
Nhờ các anh/chị giúp em ạ. Em cám ơn!

Nhập công thức 2 ô

B8 =SUMPRODUCT((Dulieu!$G$2:$G$5000=$A8)*(Dulieu!$N$2:$N$5000=B$6)*(--Dulieu!$E$2:$E$5000>=$C$3)*(--Dulieu!$E$2:$E$5000<$C$4+1))
C8 =SUMPRODUCT(Dulieu!$F$2:$F$5000*(Dulieu!$G$2:$G$5000=$A8)*(Dulieu!$N$2:$N$5000=B$6)*(--Dulieu!$E$2:$E$5000>=$C$3)*(--Dulieu!$E$2:$E$5000<$C$4+1))

Copy 2 ô cho tất cả các ô còn lại

 

[hitlecp]

Nhập công thức 2 ô

B8 =SUMPRODUCT((Dulieu!$G$2:$G$5000=$A8)*(Dulieu!$N$2:$N$5000=B$6)*(--Dulieu!$E$2:$E$5000>=$C$3)*(--Dulieu!$E$2:$E$5000<$C$4+1))
C8 =SUMPRODUCT(Dulieu!$F$2:$F$5000*(Dulieu!$G$2:$G$5000=$A8)*(Dulieu!$N$2:$N$5000=B$6)*(--Dulieu!$E$2:$E$5000>=$C$3)*(--Dulieu!$E$2:$E$5000<$C$4+1))

Copy 2 ô cho tất cả các ô còn lại

Dạ, công thức báo lỗi value ạ. Nhờ anh chỉ lại giúp em ạ

 

[HieuCD]

Dạ, công thức báo lỗi value ạ. Nhờ anh chỉ lại giúp em ạ

Xem file

 

[hitlecp]

Xem file

dạ, vẫn chưa chạy được công thức ạ. Nhờ anh chỉ lại giúp em ạ

 

[Phuocam]

dạ, vẫn chưa chạy được công thức ạ. Nhờ anh chỉ lại giúp em ạ

Máy của bạn đang cài dd/mm/yyyy nên báo lỗi, máy tôi cài mm/dd/yyyy chạy bình thường

 

[hitlecp]

Máy của bạn đang cài dd/mm/yyyy nên báo lỗi, máy tôi cài mm/dd/yyyy chạy bình thường

vậy giờ xử lý lại sao ạ, nếu muốn dùng dd/mm/yyyy?

 

[Phuocam]

vậy giờ xử lý lại sao ạ, nếu muốn dùng dd/mm/yyyy?

Thua, dữ liệu của bạn xuất ra từ hệ thống là mm/dd/yyyy và dạng text. Xử lý nó đúng chuẩn Date trước khi dùng công thức.

 

[hitlecp]

Thua, dữ liệu của bạn xuất ra từ hệ thống là mm/dd/yyyy và dạng text. Xử lý nó đúng chuẩn Date trước khi dùng công thức.

Vậy mình chỉnh công thức ở ô B8, C8 định dạng theo dd/mm/yyyy được không ạ?

 

[Phuocam]

Vậy mình chỉnh công thức ở ô B8, C8 định dạng theo dd/mm/yyyy được không ạ?

Chỉnh trong CP

 

[vuthiphuc]

Chào các anh/chị,
Em có tạo công thức cho kết quả ở sheet "BM_TheoMa". Nhưng giờ em có thêm Ngày tích thu tiền là: khi nhập từ ngày đến ngày thì số liệu sẽ nhảy tương ứng như kết quả (dữ liệu lấy từ sheet "Dulieu") mà không biết công thức như thế nào.
Nhờ các anh/chị giúp em ạ. Em cám ơn!

B OI CHO MINH XINCACH LAM SHEET BM TRONG FILE CUA BAN VOI Ạ, TKS NHIEU A

 

[bebo021999]

Làm cột phụ tại cột O để chuyển ngày tháng dạng text sang số nhé:
O2:

=IFERROR(INT(E2),INT(DATE(MID(D2,7,4),MID(D2,4,2),LEFT(D2,2))))

Kéo xuống
Sang sheet BH_theoma:
B8:

=COUNTIFS(Dulieu!$G$2:$G$5000,$A8,Dulieu!$N$2:$N$5000,B$6,Dulieu!$O$2:$O$5000,">="&$C$3,Dulieu!$O$2:$O$5000,"<="&$C$4)

C8:

=SUMIFS(Dulieu!$F$2:$F$5000,Dulieu!$G$2:$G$5000,$A8,Dulieu!$N$2:$N$5000,B$6,Dulieu!$O$2:$O$5000,">="&$C$3,Dulieu!$O$2:$O$5000,"<="&$C$4)

Kéo 2 ô này xuống
Copy cột B:C sang các cột bên phải

 

[hocexcel_1991]

dạ, vẫn chưa chạy được công thức ạ. Nhờ anh chỉ lại giúp em ạ

Sửa lại tí từ công thức bài #2, nhấn CSE

B8=SUM(IFERROR((Dulieu!$G$2:$G$5000=$A8)*(Dulieu!$N$2:$N$5000=B$6)*(DATE(MID(Dulieu!$E$2:$E$5000,7,4),MID(Dulieu!$E$2:$E$5000,4,2),LEFT(Dulieu!$E$2:$E$5000,2))>=$C$3)*(DATE(MID(Dulieu!$E$2:$E$5000,7,4),MID(Dulieu!$E$2:$E$5000,4,2),LEFT(Dulieu!$E$2:$E$5000,2))<$C$4+1),))
C8=SUM(IFERROR(Dulieu!$F$2:$F$5000*(Dulieu!$G$2:$G$5000=$A8)*(Dulieu!$N$2:$N$5000=B$6)*(DATE(MID(Dulieu!$E$2:$E$5000,7,4),MID(Dulieu!$E$2:$E$5000,4,2),LEFT(Dulieu!$E$2:$E$5000,2))>=$C$3)*(DATE(MID(Dulieu!$E$2:$E$5000,7,4),MID(Dulieu!$E$2:$E$5000,4,2),LEFT(Dulieu!$E$2:$E$5000,2))<$C$4+1),))

 

[hitlecp]

Làm cột phụ tại cột O để chuyển ngày tháng dạng text sang số nhé:
O2:

=IFERROR(INT(E2),INT(DATE(MID(D2,7,4),MID(D2,4,2),LEFT(D2,2))))

Kéo xuống
Sang sheet BH_theoma:
B8:

=COUNTIFS(Dulieu!$G$2:$G$5000,$A8,Dulieu!$N$2:$N$5000,B$6,Dulieu!$O$2:$O$5000,">="&$C$3,Dulieu!$O$2:$O$5000,"<="&$C$4)

C8:

=SUMIFS(Dulieu!$F$2:$F$5000,Dulieu!$G$2:$G$5000,$A8,Dulieu!$N$2:$N$5000,B$6,Dulieu!$O$2:$O$5000,">="&$C$3,Dulieu!$O$2:$O$5000,"<="&$C$4)

Kéo 2 ô này xuống
Copy cột B:C sang các cột bên phải

Em thêm công thức này vào ô F8 dữ liệu e đổ đến ngày 26/02 luôn nhưng số liệu chỉ đến ngày 25/02 thôi là sao ạ. Nhờ anh chỉ giúp ạ

 

[HieuCD]

dạ, vẫn chưa chạy được công thức ạ. Nhờ anh chỉ lại giúp em ạ

Tạo cột phụ cho nhẹ file

O2 =IFERROR(IF(AND(DATE(MID(E2,7,4),MID(E2,1,2),MID(E2,4,2))>=BM_TheoMa!$C$3,DATE(MID(E2,7,4),MID(E2,1,2),MID(E2,4,2))<=BM_TheoMa!$C$4),G2&"#"&N2,""),"")

Nhập công thức

B8 =COUNTIF(Dulieu!$O$2:$O$5000,$A8&"#"&B$6)
C8 =SUMIF(Dulieu!$O$2:$O$5000,$A8&"#"&B$6,Dulieu!$F$2:$F$5000)

copy 2 ô cho các ô còn lại

 

[hitlecp]

Tạo cột phụ cho nhẹ file

O2 =IFERROR(IF(AND(DATE(MID(E2,7,4),MID(E2,1,2),MID(E2,4,2))>=BM_TheoMa!$C$3,DATE(MID(E2,7,4),MID(E2,1,2),MID(E2,4,2))<=BM_TheoMa!$C$4),G2&"#"&N2,""),"")

Nhập công thức

B8 =COUNTIF(Dulieu!$O$2:$O$5000,$A8&"#"&B$6)
C8 =SUMIF(Dulieu!$O$2:$O$5000,$A8&"#"&B$6,Dulieu!$F$2:$F$5000)

copy 2 ô cho các ô còn lại

Em thêm sheet và công thức này vào ô F8 dữ liệu e đổ đến ngày 26/02 luôn nhưng số liệu chỉ đến ngày 25/02 thôi là sao ạ. Nhờ anh chỉ giúp ạ

 

[vuthiphuc]

Xem file

A oi cho e hoi cai, e chua hieu lam cai doan dieu kiện ngày ấy ạ ....(--Dulieu!$E$2:$E$20>=$C$3)*(--Dulieu!$E$2:$E$20<$C$4+1)) vi sao lại phai de 2 dau tru dang truoc, voi lai sao lại +1 ở công thức số 2. và sao khi e F9 công thức số 2 Dulieu!$E$2:$E$20<$C$4+1 thì nó là false hết, trong khi thực tế là nó nhỏ hơn ngày yêu cầu. và khi F9 cả 2 dấu trừ đằng trước thì lại la true. e k hiểu doạn này cho lắm ạ. còn công thức 1 thì dù có 2 dấu trừ hay không thì nó vẫn ra 1 kết quả. mong A giai dap gium e voi a.

 

[HieuCD]

A oi cho e hoi cai, e chua hieu lam cai doan dieu kiện ngày ấy ạ ....(--Dulieu!$E$2:$E$20>=$C$3)*(--Dulieu!$E$2:$E$20<$C$4+1)) vi sao lại phai de 2 dau tru dang truoc, voi lai sao lại +1 ở công thức số 2. và sao khi e F9 công thức số 2 Dulieu!$E$2:$E$20<$C$4+1 thì nó là false hết, trong khi thực tế là nó nhỏ hơn ngày yêu cầu. và khi F9 cả 2 dấu trừ đằng trước thì lại la true. e k hiểu doạn này cho lắm ạ. còn công thức 1 thì dù có 2 dấu trừ hay không thì nó vẫn ra 1 kết quả. mong A giai dap gium e voi a.

Dulieu!$E$2:$E$20 là dữ liệu chuỗi không phải dạng ngày, thêm 2 dấu trừ --Dulieu!$E$2:$E$20 chuyển về dạng số (ngày là dạng đặc biệt của số) từ đó mới so sánh được với ô ngày C3 và C4
Dulieu!$E$2:$E$20 là dữ liệu chuổi của ngày và giờ, ví dụ 2/5/2019 05:15, ô C4 là ngày ví dụ 2/5/2019 (ngày 5 tháng 2)
2/5/2019 05:15 thuộc về ngày 2/5/2019 nhưng nếu so sánh 2/5/2019 05:15 sẽ lớn hơn 2/5/2019 do đó điều kiện Dulieu!$E$2:$E$20<=$C$4 sẽ không thỏa, cần phải chuyển thành Dulieu!$E$2:$E$20<$C$4+1
"<$C$4+1" ví dụ "<2/5/2019+1" là "<2/6/2019" về ý nghĩa tương đương "<=2/5/2019" nhưng chính xác hơn khi có thêm giờ phút

 

[HieuCD]

Em thêm sheet và công thức này vào ô F8 dữ liệu e đổ đến ngày 26/02 luôn nhưng số liệu chỉ đến ngày 25/02 thôi là sao ạ. Nhờ anh chỉ giúp ạ

F8 =SUMIFS('B021'!$K$2:$K$50000,'B021'!$C$2:$C$50000,$C8,'B021'!$Y$2:$Y$50000,F$6,'B021'!$G$2:$G$50000,TK!F$7,'B021'!$B$2:$B$50000,">="&$B$3,'B021'!$B$2:$B$50000,"<"&$B$4+1)

 

[hitlecp]

Dulieu!$E$2:$E$20 là dữ liệu chuỗi không phải dạng ngày, thêm 2 dấu trừ --Dulieu!$E$2:$E$20 chuyển về dạng số (ngày là dạng đặc biệt của số) từ đó mới so sánh được với ô ngày C3 và C4
Dulieu!$E$2:$E$20 là dữ liệu chuổi của ngày và giờ, ví dụ 2/5/2019 05:15, ô C4 là ngày ví dụ 2/5/2019 (ngày 5 tháng 2)
2/5/2019 05:15 thuộc về ngày 2/5/2019 nhưng nếu so sánh 2/5/2019 05:15 sẽ lớn hơn 2/5/2019 do đó điều kiện Dulieu!$E$2:$E$20<=$C$4 sẽ không thỏa, cần phải chuyển thành Dulieu!$E$2:$E$20<$C$4+1
"<$C$4+1" ví dụ "<2/5/2019+1" là "<2/6/2019" về ý nghĩa tương đương "<=2/5/2019" nhưng chính xác hơn khi có thêm giờ phút

Em cám ơn nhiều ạ