Hỏi:Cách tính tổng có 2 điều kiện không dùng hàm DSum

[buioanhvn]

Hi all,

Ngày hôm qua em mới được biết đến diễn đàn thú vị như thế này, thú thực lúc đầu cũng hơi choáng vì kiến thức quá nhiều, không biết nên bắt đầu đọc từ đâu

Anh chị cho em hỏi 1 tý, Đối với những bài toán tính tổng có 2 điều kiện thì mình thường dùng hàm Dsum kèm với miền tiêu chuẩn. Tuy nhiên liệu có cách nào hay hơn mà không cần phải sử dụng đến miền tiêu chuẩn này ????
Ví dụ:
Tại sheet 1 là sheet dữ liệu, không được làm bất cứ thao tác gì trên sheet này:

| A | B | c | D | E
1 | Mã GD | Ngày | Tên hàng | Mã hàng | Tiền
2 | abcd | 14/10 | A1 | B1 | 30
3 | abde | 15/10 | A2 | B2 | 40
4 | bdce | 15/10 | A3 | B1 | 50
5 | badc | 15/10 | A4 | B1 | 60

Yêu cầu: Tính tổng tiền mã hàng B1 trong ngày 15/10.
Ở đây, nếu dùng hàm Dsum, thì cần phải tạo miền tiêu chuẩn NGÀY và MÃ HÀNG. Bây giờ em không muốn sử dụng hàm này thì sẽ phải sử dụng hàm gì ạ?

Thanks & Best regard.
BO

 

[chibi]

Dùng SUMPRODUCT()

 

[anhtuan1066]

Ngoài SUMPRODUCT ra nếu có 1 d/k như bạn nói là ngày thì cũng có thể dùng SUMIF... Nếu có trục trặc thì đưa file lên đây nhé!
Các hàm DSUM, DCOUNT thường rất khó sử dụng, bạn đã dùng nó thì ko lý nào lại ko biết đến những hàm đơn giản hơn như SUMIF chẳng hạn ???
ANH TUẤN

 

[buioanhvn]

Em cảm ơn anh chị nhiều nhé.
Dear Mr anhtuan1066: Hàm sumif thì em dùng thường xuyên, tuy nhiên, ở ví dụ này nó là có 2 điều kiện đấy chứ: 1 là điều kiện về ngày, 2 là điều kiện về mã hàng mà.
Thanks alot again!
BO

 

[anhtuan1066]

Vâng... nếu thế thì SUMPRODUCT vậy... tuy nhiên vẫn có thể dùng SUM dc đấy... Đó là công thức mãng.. bạn có thể search trên diển đàn này các bài viết của chị Handung107 về vụ mãng này.. Rất hay!

 

[DOSNET]

Bạn tham khảo trích bài viết sau của chị handung107, hy vọng là bạn hiểu thêm phần nào về sumproduct.


Để hiểu hàm SUMPRODUCT làm việc như thế nào, chúng ta sẽ xem thí dụ dưới đây :
=SUMPRODUCT((A1:A5="AA")*(B1:B5="N")*(C1:C5))
Tạm hiểu là số lượng nhập của mặt hàng AA, với cột A là mã MH, cột B là cột cho cho biết nhập (N) hay xuất (X), cột C là cột số lượng

A1:A5 = {AA, BB,AA,AA,AA}
B1:B5 = {X,X,N,N,X}
C1:C5 = {3,4,2,1,4}

1 / Phần đẩu tiên của công thức (A1:A5="AA") sẽ kiểm tra mặt hàng nào là AA và cho giá trị là TRUE, còn lại là False. Như vậy công thức này sẽ tạo ra mảng : (A1:A5 = "AA") = {True, False, True, True, True}

2/ Tương tự cho mảng B1:B5 với giá trị là "N" sẽ cho mảng sau :
(B1:B5 = "N") = {False, False, True, True, False}

3/ Và mảng C1:C5 = {3,4,2,1,4}

Bây giờ, chúng ta có 3 mảng trên, hàm SUMPRODUCT làm việc trên các mảng số (number) nhưng ở đây chúng ta có 2 mảng (True/False). Nhưng khi chúng ta thực hiện phép nhân (*), chúng ta sẽ có mảng số. Vì True*True =1 và True*False =0, do đó, khi nhân 2 mảng ((A1:A5 = "AA")*(B1:B5 = "N")) với nhau, chúng ta có mảng sau :
((A1:A5 = "AA")*(B1:B5 = "N")) = {0, 0, 1, 1, 0}
Và nhân 3 mảng :
((A1:A5 = "AA")*(B1:B5 = "N")*(C1:C5)) = {0, 0, 2, 1, 0}

Hàm SUMPRODUCT là hàm tính tổng của phép nhân 3 mảng với nhau, do đó nó sẽ tính tổng của mảng sau :
SUMPRODUCT((A1:A5 = "AA")*(B1:B5 = "N")*(C1:C5)) = SUM{0,0,2,1,0} và cho kết quả là 3

Từ đây, chúng ta có nhận xét như sau :

- Khi chúng ta thực hiện chuyển giá trị True / False thành 1/0, chúng ta cũng có thể thực hiện những phép tính sau :
* Nhân mảng True/False với giá trị 1 :
=SUMPRODUCT((A1:A5 = "AA")*1,(B1:B5 = "N")*1,(C1:C5))
Hay :
= SUMPRODUCT(1*(A1:A5 = "AA"),1*(B1:B5 = "N"),(C1:C5))
Hay :
=SUMPRODUCT((A1:A5 = "AA")^1,(B1:B5 = "N")^1,(C1:C5))
* Cộng thêm số 0 :
= SUMPRODUCT((A1:A5 = "AA")+0,(B1:B5 = "N")+0,(C1:C5))
*Cách hay nhất là chúng ta thực hiện 2 dấu trừ liên tiếp (--) :
= SUMPRODUCT(--(A1:A5 = "AA"),--(B1:B5 = "N"),(C1:C5))

Hiểu như thế, chúng ta thấy rằng việc thực hiện hàm SUMPRODUCT với đối số là một mảng duy nhất có thể thực hiện được
=SUMPRODUCT((Đk1)*(Đk2))
sẽ được hiểu là : Đk 2 = một mảng tương ứng với các giá trị 1
=SUMPRODUCT (1*Đk1) hay SUMPRODUCT (--(ĐK1))
Cụ thể hơn ta có thể thực hiện hàm sau :
=SUMPRODUCT(--(A1:A5="AA")) để đếm các giá trị "AA" có trong mảng A1:A5, giống hàm COUNTIF

Cấu trúc của hàm SUMPRODUCT như vậy là đã rõ ràng, nhưng trong 5 cách viết, chúng ta sẽ sử dụng cách nào ? Theo bài trên thì :
1/SUMPRODUCT((A1:A5="AA"),(B1:B5="N"),(C1:C5))
2/SUMPRODUCT((A1:A5="AA")*(B1:B5="N")*(C1:C5))
3/SUMPRODUCT(--(A1:A5="AA"),--(B1:B5="N"),(C1:C5))
4/SUMPRODUCT((A1:A5="AA")*1,(B1:B5="N")*1,(C1:C5))
5/SUMPRODUCT((A1:A5="AA")+0,(B1:B5="N")+0,(C1:C5))
Theo www.decisionmodels.com thì
-Sử dụng -- sẽ nhanh hơn +0 hay *1
-Sử dụng --, nếu trong mảng C1:C5 có lẫn giá trị Text, hàm vẫn bỏ qua giá trị này mà không báo lỗi
-Phép (,) sẽ nhanh hơn dấu (*), và phép (*) sẽ gây ra lỗi nếu trong dãy tổng có lẫn giá trị Text

 

[TranThanhPhong]

Hi all,

Ngày hôm qua em mới được biết đến diễn đàn thú vị như thế này, thú thực lúc đầu cũng hơi choáng vì kiến thức quá nhiều, không biết nên bắt đầu đọc từ đâu

Anh chị cho em hỏi 1 tý, Đối với những bài toán tính tổng có 2 điều kiện thì mình thường dùng hàm Dsum kèm với miền tiêu chuẩn. Tuy nhiên liệu có cách nào hay hơn mà không cần phải sử dụng đến miền tiêu chuẩn này ????
Ví dụ:
Tại sheet 1 là sheet dữ liệu, không được làm bất cứ thao tác gì trên sheet này:

| A | B | c | D | E
1 | Mã GD | Ngày | Tên hàng | Mã hàng | Tiền
2 | abcd | 14/10 | A1 | B1 | 30
3 | abde | 15/10 | A2 | B2 | 40
4 | bdce | 15/10 | A3 | B1 | 50
5 | badc | 15/10 | A4 | B1 | 60

Yêu cầu: Tính tổng tiền mã hàng B1 trong ngày 15/10.
Ở đây, nếu dùng hàm Dsum, thì cần phải tạo miền tiêu chuẩn NGÀY và MÃ HÀNG. Bây giờ em không muốn sử dụng hàm này thì sẽ phải sử dụng hàm gì ạ?

Thanks & Best regard.
BO

Bạn xem hình và file đính kèm, tìm đọc thêm hàm Sumproduct và hàm mảng trên diễn đàn để biết cách sử dụng linh hoạt theo nhu cầu:

TP.

 

[buioanhvn]

Dear all,
Em cám ơn anh chị nhé. Em đang đọc phần sumproduct đây.

Dear Mr Phong,
Em cám ơn anh nhiều nhé, anh đã giúp em gỡ phần khó nhất trong bài toán thực tế rồi đấy ạ.

BR
BO

 

[minhtam2005]

Em cũng đang có vấn đề như ban Buioanhvn, nhưng sao em dùng hàm Sumproduc để tính kết quả ở một Sheet khác thi không ra? Hay tại em dùng địa chỉ tuyệt đối ở các cột tiền, mã hàng và ngày... Mong các anh chị giúp em, em xin cảm ơn.

 

[ThuNghi]

địa chỉ tuyệt đối ở các cột tiền, mã hàng và ngày

Quan trọng nhất của sumproduct là các mảng khai báo phải bằng nhau về độ dài.
vd:
Tien: A1:A100
MaHang: B1:B100
Ngay: C1:C100

 

[minhtam2005]

Em đã để các mảng bàng nhau về độ dài, nhưng em để mã hàng là kết quả của hàm Vlooup, liệu điều nay có ảnh hưởng gì không mà em vẫn không ra kết quả? Và để bỏ giá trị Text thì ở công thưc này "=SUMPRODUCT(E2:E5,(B2:B5=DATE(2007,10,15))*1,(D25="B1")*1)" em phải để dấu -- ở đâu để ra kết quả? Mong các anh chị giúp em!

 

[Heo Con Ham Choi]

Theo mình bạn có thể dùng hàm SUMIFS, với bài trên mình có thể dùng như sau: =SUMIFS(F2:F5,C2:C5,C5,E2:E5,E5) , giá trị cũng bằng 110


A B C D E 1 Mã GD Ngày Tên hàng Mã hàng Tie^`n 2 abcd 14/10 A1 B1 30 3 abde 15/10 A2 B2 40 4 bdce 15/10 A3 B1 50 5 badc 15/10 A4 B1 60