Tính tổng cột A khi cột B tương ứng bằng 0; "" hoặc để trống (4 người xem)

[Dauthivan]

Em chưa biết viết hàm Sumproduct như thế nào trong bài toán sau, cụ thể:

Em muốn tính tổng các hàng có dữ liệu trong cột A với điều kiện cột B tương ứng của hàm đó bằng 0 hoặc để trống hoặc bằng "" (Tức em loại trừ không công những dòng tô màu vàng; kết quả đúng của nó là 1.130).

Xin được nhờ mọi người giúp đỡ

 

[hoamattroicoi]

Bạn thử công thức nửa mùa này coi :

=SUMPRODUCT((B2:B9="")*(A2:A9))+SUMIF(B2:B9;"=0";A2:A9)

Trong ô B4 của bạn có chứa 1 công thức " ="" " ??? Sao vậy ta, bạn cố tình tạo ra nó à?
Nếu xóa nó đi bạn chỉ cần gõ :

=SUMPRODUCT((B2:B9=0)*(A2:A9))

Là đủ

 

[ndu96081631]

Bạn thử công thức nửa mùa này coi :

=SUMPRODUCT((B2:B9="")*(A2:A9))+SUMIF(B2:B9;"=0";A2:A9)

Vầy cũng được

=SUMIF(B2:B9,"",A2:A9)+SUMIF(B2:B9,0,A2:A9)

Trong ô B4 của bạn có chứa 1 công thức " ="" " ??? Sao vậy ta, bạn cố tình tạo ra nó à?
Nếu xóa nó đi bạn chỉ cần gõ :

=SUMPRODUCT((B2:B9=0)*(A2:A9))

Là đủ

Đó là người ta giả lập trường hợp cell rổng do công thức trả về đấy mà

 

[Dauthivan]

Bạn thử công thức nửa mùa này coi :

=SUMPRODUCT((B2:B9="")*(A2:A9))+SUMIF(B2:B9;"=0";A2:A9)

Trong ô B4 của bạn có chứa 1 công thức " ="" " ??? Sao vậy ta, bạn cố tình tạo ra nó à?
Nếu xóa nó đi bạn chỉ cần gõ :

=SUMPRODUCT((B2:B9=0)*(A2:A9))

Là đủ

Vì cột B của em bản chất nó là hệ quả của công thức khác tạo nên, có trường hợp kết quả nó trả về là "" (phức tạp của em là cái này nên em mới lúng túng không giải quyết được).

Thưa thày ndu: Trong trường hợp này công thức Sumproduct có cách viết nào cho gọn hơn (chỉ xuất hiện hàm Sumproduct 1 lần, không cần cộng hai hàm Sumproduct mới ra kết quả không ah).
Tức là ý em sử dụng thêm như OR, AND...gì đó, em làm mãi chưa được thày ah.

 

[ptm0412]

Bạn thử công thức nửa mùa này coi :

=SUMPRODUCT((B2:B9="")*(A2:A9))+SUMIF(B2:B9;"=0";A2:A9)

[/PHP]
Là đủ

SumIf với điều kiện bằng 0, chỉ cần:
SUMIF(B2:B9;0;A2:A9)

Không cần "=0"

 

[Dauthivan]

Em viết như thế này sai ở đâu ah

em viết như sau, nhưng không biết nó sai ở đâu, nhờ mọi người chỉ giúp

SUMPRODUCT((A2:A9)*(B2:B9=OR(0;"")))

 

[ndu96081631]

em viết như sau, nhưng không biết nó sai ở đâu, nhờ mọi người chỉ giúp

SUMPRODUCT((A2:A9)*(B2:B9=OR(0;"")))

Làm việc với mảng không thể có vụ OR gì ở đây cả
- AND <==> phép nhân
- OR <===> phép cộng
vậy, nếu dùng SUMPRODUCT cho bài này thì phải vầy:

=SUMPRODUCT((((B2:B9=0)+(B2:B9=""))>0)*(A2:A9))

Tuy nhiên, phải lưu ý rằng: SUMPRODUCT sẽ cho tốc độ chậm hơn rất nhiều so với SUMIF (dù phải dùng 2 lần SUMIF)

 

[Dauthivan]

Làm việc với mảng không thể có vụ OR gì ở đây cả
- AND <==> phép nhân
- OR <===> phép cộng
vậy, nếu dùng SUMPRODUCT cho bài này thì phải vầy:

=SUMPRODUCT((((B2:B9=0)+(B2:B9=""))>0)*(A2:A9))

Tuy nhiên, phải lưu ý rằng: SUMPRODUCT sẽ cho tốc độ chậm hơn rất nhiều so với SUMIF (dù phải dùng 2 lần SUMIF)

Cảm ơn thày, cái này em chưa nhìn thấy bao giờ. Dữ liệu đầu vào của em ít em muốn sử dụng cái này nhìn cho nó gọn ah.

 

[ptm0412]

Cảm ơn thày, cái này em chưa nhìn thấy bao giờ. Dữ liệu đầu vào của em ít em muốn sử dụng cái này nhìn cho nó gọn ah.

=SUMPRODUCT((((B2:B9=0)+(B2:B9=""))>0)*(A2:A9))

Công thức này bẫy lỗi cho công thức sau:

=SUMPRODUCT(((B2:B9=0)+(B2:B9=""))*(A2:A9))

Nếu không bẫy lỗi thì:
Các ô cột B có giá trị "" do công thức trả về, hoặc bằng 0, cột A tương ứng sẽ được cộng 1 lần.
Các ô cột B bỏ trống, giá trị cột A tương ứng sẽ bị cộng 2 lần: 1 lần cho giá trị 0 và 1 lần cho giá trị trống.

Cho nên, bẫy nó bằng cách xét tổng nó nếu >0, mới cộng 1 lần:
Tổng =1 cũng 1 lần, và tổng bằng 2 cũng chỉ 1 lấn.

Để kiểm tra lập luận trên đây, với ô A1 trống (không chứa giá trị và không chứa công thức), thử gõ 1 công thức tại 1 ô khác:
=(A1=0)+(A1="")

Kết quả sẽ là 2.
Nhưng nếu ô A1 có công thức ="", kết quả sẽ là 1.

 

[Dauthivan]

Làm việc với mảng không thể có vụ OR gì ở đây cả
- AND <==> phép nhân
- OR <===> phép cộng

Em vẫn chưa hiểu lắm cái này, xin được mọi người chỉ giúp em chi tiết hơn.

 

[Dauthivan]

=SUMPRODUCT((((B2:B9=0)+(B2:B9=""))>0)*(A2:A9))

Công thức này bẫy lỗi cho công thức sau:

=SUMPRODUCT(((B2:B9=0)+(B2:B9=""))*(A2:A9))

Nếu không bẫy lỗi thì:
Các ô cột B có giá trị "" do công thức trả về, hoặc bằng 0, cột A tương ứng sẽ được cộng 1 lần.
Các ô cột B bỏ trống, giá trị cột A tương ứng sẽ bị cộng 2 lần: 1 lần cho giá trị 0 và 1 lần cho giá trị trống.

Cho nên, bẫy nó bằng cách xét tổng nó nếu >0, mới cộng 1 lần:
Tổng =1 cũng 1 lần, và tổng bằng 2 cũng chỉ 1 lấn.

Để kiểm tra lập luận trên đây, với ô A1 trống (không chứa giá trị và không chứa công thức), thử gõ 1 công thức tại 1 ô khác:
=(A1=0)+(A1="")

Kết quả sẽ là 2.
Nhưng nếu ô A1 có công thức ="", kết quả sẽ là 1.

Như vậy nếu ô "trắng tinh" nó cũng được hiểu theo 2 nghĩa (0 hoặc "") ?

 

[ptm0412]

Như vậy nếu ô "trắng tinh" nó cũng được hiểu theo 2 nghĩa (0 hoặc "") ?

Chỉ có 1 cách là thử:

Để kiểm tra lập luận trên đây, với ô A1 trống (không chứa giá trị và không chứa công thức), thử gõ 1 công thức tại 1 ô khác:
=(A1=0)+(A1="")

Kết quả sẽ là 2.

Trống không chịu, trắng tinh mới chịu! Cô nàng này thật là ...

Tuy nhiên, chỉ đúng trong 1 số trường hợp. Muốn biết trường hợp nào tính 0, trường hợp nào tính rỗng, trường hợp nào tính cả 2, thì phải dựa vài tính chất của hàm.
Hãy thử với Sum, Count, Min, Max, Average, ...

 

[ndu96081631]

Em vẫn chưa hiểu lắm cái này, xin được mọi người chỉ giúp em chi tiết hơn.

Thì xưa nay bạn vẫn dùng hàm SUMPRODUCT theo kiểu =SUMPRODUCT((mảng 1 = đk1)*(mảng 2 = đk2)*(.....)*(mảng KQ))
Dấu nhân trong công thức trên chính là ám chỉ toán tử AND (tất cả các điều kiện đều thỏa mản thì mới tính mảng KQ ---> Và đấy là AND)
Vậy trong trường hợp muốn làm phép tính liên quan đến toán tử OR (chỉ cần thỏa 1 trong các điều kiện sẽ tính mảng KQ) thì phải thay dấu nhân thành dấu cộng rồi