SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện

[dat_butmuc]

Phiền mọi người giúp em lỗi của hàm Sumproduct em sử dụng mà không biết lỗi ở đâu (tại ô em môi nền màu vàng). Em xin cảm ơn

Dùng sumproduct thì thế này:

=SUMPRODUCT((MONTH($A$4:$A$36)=A42)*($B$4:$B$36=B42)*$C$4:$C$36)

 

[dat_butmuc]

Bạn thử sửa lại thành vầy xem sao:
Mà Bạn dùng office 2007 sao không dùng hàm SUMIFS cho nó khỏe nhỉ?

Sumifs trong trường hợp này thì lại dài dòng hơn, vì cái điều kiện month (cột A)!

 

[ndu96081631]

Phiền mọi người giúp em lỗi của hàm Sumproduct em sử dụng mà không biết lỗi ở đâu (tại ô em môi nền màu vàng). Em xin cảm ơn

Bạn sửa lại công thức như MinhCong và dat_butmuc là được rồi
Có nhiều cú pháp thể hiện khi dùng SUMPRODUCT, nhưng xin bạn cứ nhớ thuộc lòng cú pháp tôi viết dưới đây

SUMPRODUCT((Vùng 1 = d/k1)*(Vùng 2 = d/k2)*(.......)*(Vùng tính tổng))

Nếu không có Vùng tính tổng thì công thức trên sẽ thành ĐẾM (thay vì SUM)
Cú pháp trên là hoàn hảo nhất so với bất cứ cú pháp nào vì nó tổng hợp được trên các mảng có kích thước khác nhau (mà các kiểu viết khác không thể làm được)

 

[Phanhanhdai]

Vấn đề em thắc mắc là mặc dù đã sửa rồi KQ tháng 1 của Cty A lại ra bằng 0 nhỉ? Nếu đúng ra nó phải là 200.546.667, phiền các bác xem lại hộ em với

 

[ndu96081631]

Vấn đề em thắc mắc là mặc dù đã sửa rồi KQ tháng 1 của Cty A lại ra bằng 0 nhỉ? Nếu đúng ra nó phải là 200.546.667, phiền các bác xem lại hộ em với

Cứ ý công thức ở trên mà làm thì sao kết quả =0 được cơ chứ ---> Xem file đây

 

[Phanhanhdai]

Cảm ơn thày em tìm ra nguyên nhân rùi, tại em không đóng mở ngoặc () thành phần MONTH($A$4:$A$36)=A42, xin thày giải thích giúp em từ công thức của thày SUMPRODUCT((MONTH($A$4:$A$36)=A42)*($B$4:$B$36=B42)*($C$4:$C$36)) nếu em bỏ đóng mở ngoặc thành phần thứ 3 đi thành SUMPRODUCT((MONTH($A$4:$A$36)=A42)*($B$4:$B$36=B42)*$C$4:$C$36) thì vẫn OK mà bỏ đóng mở ngoặc thành phần thứ nhất tức công thức thành SUMPRODUCT(MONTH($A$4:$A$36)=A42*($B$4:$B$36=B42)*($C$4:$C$36)) thì lại không được (kết quả bằng 0) ah?

 

[ndu96081631]

Cảm ơn thày em tìm ra nguyên nhân rùi, tại em không đóng mở ngoặc () thành phần MONTH($A$4:$A$36)=A42, xin thày giải thích giúp em từ công thức của thày SUMPRODUCT((MONTH($A$4:$A$36)=A42)*($B$4:$B$36=B42)*($C$4:$C$36)) nếu em bỏ đóng mở ngoặc thành phần thứ 3 đi thành SUMPRODUCT((MONTH($A$4:$A$36)=A42)*($B$4:$B$36=B42)*$C$4:$C$36) thì vẫn OK mà bỏ đóng mở ngoặc thành phần thứ nhất tức công thức thành SUMPRODUCT(MONTH($A$4:$A$36)=A42*($B$4:$B$36=B42)*($C$4:$C$36)) thì lại không được (kết quả bằng 0) ah?

Bạn nói sao chứ nếu bỏ dấu ngoặc nhất hay cuối gì thì nó cũng báo lỗi liền, làm gì ra kết quả
Nguyên tắc về cú pháp người ta ghi thế thì mình cứ theo thôi

 

[Phanhanhdai]

Bạn nói sao chứ nếu bỏ dấu ngoặc nhất hay cuối gì thì nó cũng báo lỗi liền, làm gì ra kết quả
Nguyên tắc về cú pháp người ta ghi thế thì mình cứ theo thôi

Được mà thày nếu thày cứ thử bỏ đóng mở ngoặc của $C$4:$C$36 thì kết quả vẫn OK. Thực ra em muốn tìm hiểu bản chất của nó thôi, chứ cứ đóng mở ngoặc là đúng là "chắc ăn" hơn.

 

[ndu96081631]

Được mà thày nếu thày cứ thử bỏ đóng mở ngoặc của $C$4:$C$36 thì kết quả vẫn OK. Thực ra em muốn tìm hiểu bản chất của nó thôi, chứ cứ đóng mở ngoặc là đúng là "chắc ăn" hơn.

Vậy chắc máy bạn nó sao ấy chứ tôi thử rồi mới nói bạn à ---> Nguyên tắc của công thức luôn luôn là: Tổng số dấu ( phải bằng với tổng số dấu ) ---> Tức số dấu ngoặc mở luôn = số dấu ngoặc đóng
Vậy không lý gì trong công thức lại có 5 dấu ( mà chỉ có 4 dấu )
Không tin cứ kêu ai đó thử là biết liền

 

[linhngoc]

nếu bỏ đóng mở ngoặc thành phần thứ nhất tức công thức thành SUMPRODUCT(MONTH($A$4:$A$36)=A42*($B$4:$B$36=B42)*($C$4:$C$36)) thì lại không được (kết quả bằng 0) ah?

-----Mình thấy hàm sumproduct nếu bỏ dấu ngoặc ở đầu:

=SUMPRODUCT(MONTH($A$4:$A$36)=A42*($B$4:$B$36=B42)*($C$4:$C$36))

- kết quả = 0 là do hàm ưu tiên tính các phần tử trong () trước xong đến tính tiếp đến dấu * cuối cùng mới tính đến dấu =. Như công thức trên hàm sẽ tính các thành phần:
- MONTH($A$4:$A$36) ra mảng các tháng (1)
-A42*($B$4:$B$36=B42)*($C$4:$C$36) ra 1 mảng (2)
---> cuối cùng so 2 mảng (1) = (2) mảng này chắc chắn là false do vậy kết quả =0.

nếu bỏ đóng mở ngoặc thành phần thứ 3 đi thành SUMPRODUCT((MONTH($A$4:$A$36)=A42)*($B$4:$B$36=B42)*$C$4:$C$36) thì vẫn OK

-Nếu theo như quy luật ưu tiên tính toán trong hàm () tính trước, * tính sau thì.
+ Hàm tính: (MONTH($A$4:$A$36)=A42) ra 1 mảng.
+($B$4:$B$36=B42) ra 1 mảng
+$C$4:$C$36 hay ($C$4:$C$36) cũng ra 1 mảng như nhau.
- Các mảng này thỏa mãn thì cho kết quả thôi.
*, Nói chung các hàm đều tính toán trong ngoặc trước, xong rồi đến nhân chia cộng trừ...chứ không riêng gì sumproduct!

P/S: Dạ đấy là theo em nghĩ vậy. Nếu không đúng thì các thầy, cô sửa sai giúp!

 

[ptm0412]

Lập luận của nhóc linhngoc hoàn toàn chính xác ở chỗ Excel ưu tiên tính

- trong ngoặc trước ngoài ngoặc sau,
- ngoặc trong trước, ngoặc ngoài sau,
- nhân chia trước, cộng trừ sau,
- dấu bằng sau chót

Kết quả luôn luôn bằng 0 vì

=SUMPRODUCT(MONTH($A$4:$A$36)=A42*($B$4:$B$36=B42) *($C$4:$C$36))
Nhóm thứ nhất (màu xanh) là 1 mảng gồm các tháng của các ô A4:A36, là các số rải từ 1 đến 12

Nhóm thứ 2 (màu đỏ), là tích của 3 thành phần: 1 giá trị A42, 1 mảng True False, 1 mảng giá trị C4:C36, kết quả là 1 mảng các số

So sánh 2 mảng số đó với nhau bằng dấu =, thì ra 1 mảng mới, gồm toàn True, False

Và sum của True False, thì bằng 0.

 

[Phanhanhdai]

Cái đó thì em thừa nhận, nhưng máy em công thức như sau =SUMPRODUCT(MONTH($A$4:$A$36)=A42*($B$4:$B$36=B42) *$C$4:$C$36)
vẫn ra đáp số đúng (thày ndu thử thì bị lỗi) vậy máy các bác như thế nào trong THợp này?

 

[ptm0412]

Cái đó thì em thừa nhận, nhưng máy em công thức như sau =SUMPRODUCT(MONTH($A$4:$A$36)=A42*($B$4:$B$36=B42) *$C$4:$C$36)
vẫn ra đáp số đúng (thày ndu thử thì bị lỗi) vậy máy các bác như thế nào trong THợp này?

Ra kết quả zero chứ? Nhưng vẫn ra kết quả, không lỗi.
Chắc ndu nhầm là chỉ bỏ 1 dấu ( hoặc 1 dấu ). Thực sự là bỏ đi 1 cặp () lận. Chỉ ra kết quả sai thôi chứ không lỗi.

 

[nguyenmanhnam]

Công thức của bạn tại cell CF7 hiện là:

=SUMPRODUCT([COLOR=red][B]D4:CD4[/B][/COLOR],D7:CD7)

Lý ra phải là:

=SUMPRODUCT([COLOR=red][B]$D$4:$CD$4[/B][/COLOR],D7:CD7)

phải khóa dòng và cột để khi kéo fill thì chổ màu đỏ này mới không thay đổi
Thêm nữa, trong file của bạn, dòng đơn giá $D$4:$CD$4 chẳng có tí dữ liệu nào nên chắc chắn kết quả sẽ luôn = Zero rồi

em thấy Sumproduct một số trường hợp dùng dấu, (=SUMPRODUCT(D4:CD4,D7:CD7)) nhưng một số trường hợp dấu , không được phải chuyển sang dấu nhân (*) tức công thức trên là SUMPRODUCT((D4:CD4)*(D7:CD7)). Vậy bản chất của nó thế nào ah?

 

[ndu96081631]

em thấy Sumproduct một số trường hợp dùng dấu, (=SUMPRODUCT(D4:CD4,D7:CD7)) nhưng một số trường hợp dấu , không được phải chuyển sang dấu nhân (*) tức công thức trên là SUMPRODUCT((D4:CD4)*(D7:CD7)). Vậy bản chất của nó thế nào ah?

Bản chất vẫn như nhau, chỉ là cách thể hiện của mỗi người.
Tuy nhiên, trong các kiểu thể hiện hàm SUMPRODUCT thì cách dùng dấu nhân là ưu việt nhất:
- Khỏi lo dấu phân cách khác nhau trên từng máy tính (có máy dùng dấu phẩy, có máy dùng dấu chấm phẩy)
- Có thể thực hiện tính toán trên các mảng không cùng kích thước <--- Và đây chính là ưu điểm cần quan tâm của nó
Chính vì lẽ đó, bây giờ học SUMPRODUCT, tốt nhất bạn nên dùng dấu nhân, mấy dấu khác khỏi cần quan tâm đến làm gì
(Có người nói cách dùng dấu -- sẽ cho tốc độ nhanh hơn dùng dấu * , Tôi thì không tin gì mấy, nếu có thì chắc là chênh lệch cũng không đáng kể. Với dữ liệu lớn, SUMPRODUCT nhiều điều kiện thì 2 cách này cũng "lết bánh" như nhau mà thôi)

 

[anhchot]

Hàm Sumproduct

Chào bà con!

Mình đang làm về bê tông muốn tách riêng khối lượng xuất bê tông của từng lái xe. mình đã từng xem một ví dụ nhưng không thể nào mà tính được.

các bạn giúp mình xem tách kiểu gì nhé. ( mình có gửi kèm file đính kèm). nếu được mong các bạn gửi vào mail này cho mình nha: vananhkt7a@gmail.com

Thank!

 

[anhchot]

Hàm Sumproduct

đính kèm fileẻtguivdfngvdf

 

[hoamattroicoi]

Tôi thấy bạn post nhiêu nơi quá mà tôi tìm hoài không thấy file đính kèm của bạn nằm ở đâu là sao? Kỳ quá !

 

[thanhan]

Bản chất vẫn như nhau, chỉ là cách thể hiện của mỗi người.
Tuy nhiên, trong các kiểu thể hiện hàm SUMPRODUCT thì cách dùng dấu nhân là ưu việt nhất:
- Khỏi lo dấu phân cách khác nhau trên từng máy tính (có máy dùng dấu phẩy, có máy dùng dấu chấm phẩy)
- Có thể thực hiện tính toán trên các mảng không cùng kích thước <--- Và đây chính là ưu điểm cần quan tâm của nó
Chính vì lẽ đó, bây giờ học SUMPRODUCT, tốt nhất bạn nên dùng dấu nhân, mấy dấu khác khỏi cần quan tâm đến làm gì
(Có người nói cách dùng dấu -- sẽ cho tốc độ nhanh hơn dùng dấu * , Tôi thì không tin gì mấy, nếu có thì chắc là chênh lệch cũng không đáng kể. Với dữ liệu lớn, SUMPRODUCT nhiều điều kiện thì 2 cách này cũng "lết bánh" như nhau mà thôi)

Em đã vận dụng hàm này với cơ sở dữ liệu hết các dòng trong ex 2003. Nhưng thực sự là chậm và chậm đến không thể tưởng trượng được.
Và rồi sau này em đã lên đời 2007 và dùng sumifs. Trời ơi, nó chạy sướng quá. Qua đó bác nào sử dụng nhiều đến hàm SUMPRODUCT thì lên đời đi thôi.
Chúc thành công!

 

[Lê Duy Thương]

Em đã vận dụng hàm này với cơ sở dữ liệu hết các dòng trong ex 2003. Nhưng thực sự là chậm và chậm đến không thể tưởng trượng được.
Và rồi sau này em đã lên đời 2007 và dùng sumifs. Trời ơi, nó chạy sướng quá. Qua đó bác nào sử dụng nhiều đến hàm SUMPRODUCT thì lên đời đi thôi.
Chúc thành công!

đương nhiên sumifs sẽ nhanh hơn gấp vài lần so với sumproduct
nhưng với điều kiện dữ liệu phải nhiều thì mới có sự khác biệt(cỡ >20000 row). chứ dữ liệu ít thi ko phân biệt được
tôi đã sử dụng sumifs từ khi biết excel 2007 đến nay và đã ko dùng sumproduct nhiều như trước. nhưng có nhiều trường hợp vẫn phải dùng sumproduct