Làm sao để xử lý thống kê về ngày tháng mà ko dùng cột phụ (1 người xem)

[thanhbaby3005]

Hiện từ câu 12 đến câu 20 của sheet 25 có liên quan đến ngày tháng. Mình thêm 3 cột phụ ( tháng, ngày, thứ) thì có thể giải được bình thường,(ko hiểu sao câu 12 và câu 15 thì ko ra kết quả)
Mong mọi người giúp đỡ giải quyết.

 

[ndu96081631]

Hiện từ câu 12 đến câu 20 của sheet 25 có liên quan đến ngày tháng. Mình thêm 3 cột phụ ( tháng, ngày, thứ) thì có thể giải được bình thường,(ko hiểu sao câu 12 và câu 15 thì ko ra kết quả)
Mong mọi người giúp đỡ giải quyết.

Thí nghiệm cho bạn 1 công thức nhé
Tại cell J35 bạn đang dùng công thức:
=MIN(IF(I5:I14=7,F5:F14,""))
Sửa thành vầy thì khỏi cần cột phụ:
=MIN(IF(MONTH(B5:B14)=7,F5:F14,""))
Những chổ khác bạn tự suy nghĩ đi (cũng gần tương tự)

 

[MinhCong]

Hiện từ câu 12 đến câu 20 của sheet 25 có liên quan đến ngày tháng. Mình thêm 3 cột phụ ( tháng, ngày, thứ) thì có thể giải được bình thường,(ko hiểu sao câu 12 và câu 15 thì ko ra kết quả)
Mong mọi người giúp đỡ giải quyết.

Câu 12:

=SUMIF(D5:D14;"Nghêu";F5:F14)+SUMPRODUCT((D5:D14="Bào Ngư")*(E5:E14=2);F5:F14)

Câu 15:

=SUMPRODUCT((F5:F14<500)+(G5:G14>=200);H5:H14)-SUMPRODUCT((F5:F14<500)*(G5:G14>=200);H5:H14)

Câu 20:

=SUMPRODUCT((WEEKDAY(B5:B14)=1)*(F5:F14+G5:G14))+SUMPRODUCT((WEEKDAY(B5:B14)=7)*(F5:F14+G5:G14))

 

[MinhCong]

Nhận được tin nhắn của Bạn Tôi xem lại và có ý trả lời như sau:
Câu 12: Có thể sửa lại như sau cho gọn:

=SUMPRODUCT((D5:D14="Nghêu")+(D5:D14="Bào Ngư")*(E5:E14=2);F5:F14)

Câu 15:Tổng TonKho của các mặt hàng có NhapKho <500 hoặc XuatKho >=200 là bao nhiêu? => Bạn để ý hoặc (là hàm or) nhé không phải Và (and) -> Hoặc tức là xem miễn sao bên Nhapkho có giá trị <500 (bên Xuatkho có giá trị bất kỳ >=200 hoặc <=200) thì tra sang Tonkho lấy giá trị tương tự cho hoặc Xuatkho>=200 (bên Nhapkho có giá trị bất kỳ <500 hoặc >=500) thì tra sang Tonkho lấy giá trị.
Vậy => Ta sẽ có trường hợp Nhapkho<500 và Xuatkho>=200 (Như vậy đây là những giá trị trùng sau khi tính từ 2 trường hợp trên) nên ta phải trừ ra.
=> Công thức như trên Bạn có thể hiểu như sau:
*Đối với Nhapkho<500 (Kết quả 1 = 748)

=SUMPRODUCT((F5:F14<500)*(H5:H14))

*Đối với Xuatkho>=200 (Kết quả 2 = 993)

=SUMPRODUCT((G5:G14>=200)*(H5:H14))

*Đối với Nhapkho<500 và Xuatkho>=200 (Kết quả 3 = 383)

=SUMPRODUCT((F5:F14<500)*(G5:G14>=200)*(H5:H14))

=>Tổng TonKho của các mặt hàng có NhapKho <500 hoặc XuatKho >=200 là bao nhiêu?

=kết quả 1 + Kết quả 2 - Kết quả 3

Câu 17: Bạn áp dụng công thức như vậy là đúng rồi còn gì.

=SUMPRODUCT((E5:E14=1)*(MONTH(B5:B14)=10)*(G5:G14))

 

[thanhbaby3005]

Cám ơn bạn nhiều lắm,mình đã hiểu. Câu 17 mình sai đúng 1 dấu )

=SUMPRODUCT((E5:E14=1)*(MONTH(B5:B14[COLOR=red])[/COLOR]=10))*(G5:G14))

Nếu có thời gian rảnh, mong bạn giúp đỡ mình làm sheet 12, mình đã làm nhưng kết quả ko giống so với đáp án.

 

[MinhCong]

Cám ơn bạn nhiều lắm,mình đã hiểu. Câu 17 mình sai đúng 1 dấu )

=SUMPRODUCT((E5:E14=1)*(MONTH(B5:B14[COLOR=red])[/COLOR]=10))*(G5:G14))

Nếu có thời gian rảnh, mong bạn giúp đỡ mình làm sheet 12, mình đã làm nhưng kết quả ko giống so với đáp án.

bạn xem file đính kèm nhé!

 

[dat_butmuc]

bạn xem file đính kèm nhé!

Góp vui một số công thức gọn hơn:
- Ngày bắt đầu học (không học chủ nhật):

=IF(WEEKDAY(C6)>5,C6+3,C6+2)

- Ngày thi tốt nghiệp:

=EDATE(D6,HLOOKUP(LEFT(B6),$C$19:$E$20,2,0))

- Hạn cuối nộp học phí:

=EOMONTH(E6,-1)

 

[thanhbaby3005]

sheet 24, cột thành tiền. Có công thức nào khác đơn giản hơn công thức mình đã dùng ko bạn. 1 lần nữa cám ơn bạn.

 

[MinhCong]

sheet 24, cột thành tiền. Có công thức nào khác đơn giản hơn công thức mình đã dùng ko bạn. 1 lần nữa cám ơn bạn.

Bạn thử dùng công thức sau xem sao nhé!

=G5*VLOOKUP(C5;$B$17:$G$19;MATCH(E5;$B$15:$G$15;)+MATCH(F5;$D$16:$E$16;)-1;)

 

[thanhbaby3005]

bị lỗi # N/A rồi, ko ra được kết quả. Hình như bạn xài openoffice hay sao mà toàn dấu ;

 

[Lê Duy Thương]

sheet 24, cột thành tiền. Có công thức nào khác đơn giản hơn công thức mình đã dùng ko bạn. 1 lần nữa cám ơn bạn.

có thêm 2 cách ngắn hơn của bạn đây
=G5*INDEX($B$17:$G$19,MATCH(C5,$B$17:$B$19,0),MATCH(F5,$B$16:$G$16,0)+IF(E5="bán",2,0))

=G5*VLOOKUP($C5,$B$15:$G$19,MATCH(F5,$B$16:$G$16,0)+IF($E5="bán",2,0),0)

 

[dat_butmuc]

sheet 24, cột thành tiền. Có công thức nào khác đơn giản hơn công thức mình đã dùng ko bạn. 1 lần nữa cám ơn bạn.

Bạn thử dùng:

=VLOOKUP(C5,$B$17:$G$19,IF(F5="S",3,4)+(E5="Bán")*2,0)*G5

 

[thanhbaby3005]

nhưng mình vẫn ko hiểu đoạn code này, bạn giải thích giúp với ? (*2 ????)

IF(F5="S",3,4)+(E5="Bán")*2,0
bạn giải thích giúp tại sao *2

 

[dat_butmuc]

nhưng mình vẫn ko hiểu đoạn code này, bạn giải thích giúp với ? (*2 ????)

IF(F5="S",3,4)+(E5="Bán")*2,0
bạn giải thích giúp tại sao *2

Cái này chỉ vừa đủ sử dụng trong trường hợp của bài này thôi
Xét Bảng tra - bắt đầu từ Cột mã hàng đến cột S,L của Mua lần lượt là 3 & 4 cột nên dùng: IF(F5="S",3,4)
Trong trường hợp S,L của Bán cách S,L của Mua là 2 nên dùng: (E5="Bán")*2 -> Ta đang xét biểu thức logic... nếu đúng E5="Bán" = true thì khi đó (E5="Bán")*2 =1*2 =2 , còn biểu thức E5="Bán" = false thì = 0*2=0
Kết hợp lại cho ra vế trên
Bình thường thì tôi chỉ muốn sử dụng công thức vừa đủ cho từng trường hợp riêng biệt, phát sinh thì ta tìm cách khác.


À, tôi thấy trong các bài tập của bạn có nhiều công thức quá dài, rườm rà & máy móc (tôi cũng hay bị vậy), với lại đây là các bài tập củng cố kiến thức nên Bạn cố gắng tìm các cách ngắn gọn hơn thử xem?

 

[chinhto0113]

Hôm nay mới hiểu được hàm sumproduct. Qúa ưa là tuyệt vời thank các anh chị và cá bạn nhiều nhé.
Em viết cho câu 15 sheet 26.
=SUMPRODUCT((F5:F14<500)+(G5:G14>=200)-(F5:F14<500)*(G5:G14>=200),H5:H14)