Tại sao dùng "hàm OR" trong "hàm mảng" cho kết quả không đúng? (1 người xem)

[tronghv77]

Anh chị xem giúp em tại sao hàm này của em không đúng với ak?
Chân thành cảm ơn!

{=SUM(IF(OR(C2:C11="Bào Ngư",C2:C11=" Nghêu"),G2:G11,""))}

 

[nhat chi mai]

Em chỉ phân tích theo ý của em hiểu thôi ạ, nếu mà sai mong sự giúp đỡ của Anh Chị, công thức trên là một dạng công thức mảng, em sẽ phân tích công thức từ trong ra ngoài.

Đầu tiên là hàm OR : OR(C2:C11="Bào Ngư",C2:C11=" Nghêu") hàm này thì không có gì, chỉ lấy những giá tía trị có "Bào ngư", hoặc lấy " Nghêu".
Tiếp theo hàm IF : IF(OR(C2:C11="Bào Ngư",C2:C11=" Nghêu"),G2:G11,""), Nếu một trong hai vùng có C2:C11="Bào Ngư" hoặc C2:C11=" Nghêu" thì sẽ trả về giá trị tổng từ G2:G11, nếu không trả về trắng .Thì hiển nhiên tới đây kết quả sẽ là sum(G2:G11).
Còn sum thì là đơn giản tính tổng giá trị . Vậy kết quả cuối cùng cũng chính là giá trị mà hàm If trả về.

Như vậy, kết quả sẽ trả về là không đúng, bởi nó sẽ tính tổng từ G2:G11.

 

[tronghv77]

Em chỉ phân tích theo ý của em hiểu thôi ạ, nếu mà sai mong sự giúp đỡ của Anh Chị, công thức trên là một dạng công thức mảng, em sẽ phân tích công thức từ trong ra ngoài.

Đầu tiên là hàm OR : OR(C2:C11="Bào Ngư",C2:C11=" Nghêu") hàm này thì không có gì, chỉ lấy những giá tía trị có "Bào ngư", hoặc lấy " Nghêu".
Tiếp theo hàm IF : IF(OR(C2:C11="Bào Ngư",C2:C11=" Nghêu"),G2:G11,""), Nếu một trong hai vùng có C2:C11="Bào Ngư" hoặc C2:C11=" Nghêu" thì sẽ trả về giá trị tổng từ G2:G11, nếu không trả về trắng .Thì hiển nhiên tới đây kết quả sẽ là sum(G2:G11).
Còn sum thì là đơn giản tính tổng giá trị . Vậy kết quả cuối cùng cũng chính là giá trị mà hàm If trả về.

Như vậy, kết quả sẽ trả về là không đúng, bởi nó sẽ tính tổng từ G2:G11.

Cảm ơn bạn nguyencanh160890, mình thấy rất hợp lý rồi!

Và bạn có thể sửa nó thành đúng được không (tức là vẫn dùng OR ấy) ?

 

[nhat chi mai]

Em nghĩ không phải chỗ hàm OR đâu ạ, mà chỗ G2:G11, nếu Anh thử công thức này
=SUM(IF(OR(C2:C11="Bào Ngư",C2:C11=" Nghêu"),SUMIF(C2:C11,"Bào Ngư",G2:G11)+SUMIF(C2:C11,"Nghêu",G2:G11),"") vào ô cách 3 đúng đó ạ!

 

[bebo021999]

C2:C11="Bào Ngư" trả về TRUE nếu có "Bào Ngư" trong vùng C2:C11
C2:C11="Nghêu" trả về TRUE nếu có "Nghêu" trong vùng C2:C11
OR hai cái đó lại, tức là nếu C2:C11 có 1 trong 2 loại hải sản này sẽ SUM toàn bộ vùng G2:G11.

Trong khi ý của bạn là chỉ cộng các dòng có 1 trong 2 loại, có nhiều cách:
Nếu dùng hàm mảng, kết thúc bằng Ctrl-Shift-Enter:

=SUM(IF(C2:C11="Bào ngư",G2:G11,IF(C2:C11="Nghêu",G2:G11,0)))

Hoặc ngắn gọn hơn chỉ cần Enter:

=SUMPRODUCT(((C2:C11="Bào ngư")+(C2:C11="Nghêu"))*G2:G11)

 

[tronghv77]

Em nghĩ không phải chỗ hàm OR đâu ạ, mà chỗ G2:G11, nếu Anh thử công thức này
=SUM(IF(OR(C2:C11="Bào Ngư",C2:C11=" Nghêu"),SUMIF(C2:C11,"Bào Ngư",G2:G11)+SUMIF(C2:C11,"Nghêu",G2:G11),"") vào ô cách 3 đúng đó ạ!

Cảm ơn bạn nhiều, bạn giỏi quá!

 

[tronghv77]

Cảm ơn bebo021999!

=SUM(IF(C2:C11="Bào ngư",G2:G11,IF(C2:C11="Nghêu",G2:G11,0)))

Cách này t đã biết rồi,

=SUMPRODUCT(((C2:C11="Bào ngư")+(C2:C11="Nghêu"))*G2:G11)

Còn cách này thực sự ngắn gọn và t chưa dùng bao giờ, cảm ơn nhiều nhé!

 

[tronghv77]

=SUM(IF(OR(C2:C11="Bào Ngư",C2:C11=" Nghêu"),SUMIF(C2:C11,"Bào Ngư",G2:G11)+SUMIF(C2:C11,"Nghêu",G2:G11),"")

t vừa hiểu được công thức này, mặc dù đã dùng OR() nhưng nội dung vẫn chưa có gì thực sự mới (trùng với cách 1). Dù sao cũng cảm ơn bạn nhiều, vì đã cố gắng giúp đỡ!

 

[nhat chi mai]

t vừa hiểu được công thức này, mặc dù đã dùng OR() nhưng nội dung vẫn chưa có gì thực sự mới (trùng với cách 1). Dù sao cũng cảm ơn bạn nhiều, vì đã cố gắng giúp đỡ!

Thực ra lỗi này là chỗ giá trị trả về mà...em phân tích rồi ấy thôi. Em nghĩ đơn giản thôi, dùng công thức của Bác Bebo SUM(IF(C2:C11="Bào ngư",G2:G11,IF(C2:C11="Nghêu",G2:G11,0))) ngắn gọn và dễ hiểu.

 

[vodoi2x]

t vừa hiểu được công thức này,

=SUM(IF(OR(C2:C11="Bào Ngư",C2:C11=" Nghêu"),SUMIF(C2:C11,"Bào Ngư",G2:G11)+SUMIF(C2:C11,"Nghêu",G2:G11),"")

mặc dù đã dùng OR() nhưng nội dung vẫn chưa có gì thực sự mới (trùng với cách 1). Dù sao cũng cảm ơn bạn nhiều, vì đã cố gắng giúp đỡ!

Công thức lạ quá chỉ cần thế này là đủ

=SUMIF(C2:C11,"Bào Ngư",G2:G11)+SUMIF(C2:C11,"Nghêu",G2:G11)

sao phải loằng ngoằng chi nhỉ, chắc là học nhiều quá rùi đây

 

[nhat chi mai]

Công thức lạ quá chỉ cần thế này là đủ

=SUMIF(C2:C11,"Bào Ngư",G2:G11)+SUMIF(C2:C11,"Nghêu",G2:G11)

sao phải loằng ngoằng chi nhỉ, chắc là học nhiều quá rùi đây

Không đâu Anh, cách của Anh là cách 1 của Anh tronghv77 rồi hì hì, Anh í làm cách 3 Anh ạ!
p/s Món quà của ANh tặng cho Chị em hôm 8-3 em vẫn chưa khám phá hết bí ẩn , khi em thay đổi format nó chỉ toàn số thôi ạ!em kém quá. Nhưng cảm ơn Anh.

 

[vodoi2x]

Không đâu Anh, cách của Anh là cách 1 của Anh tronghv77 rồi hì hì, Anh í làm cách 3 Anh ạ!
p/s Món quà của ANh tặng cho Chị em hôm 8-3 em vẫn chưa khám phá hết bí ẩn , khi em thay đổi format nó chỉ toàn số thôi ạ!em kém quá. Nhưng cảm ơn Anh.

ở cái này
=SUMIF(C2:C11,"Bào Ngư",G2:G11)+SUMIF(C2:C11,"Nghêu",G2:G11)

khác xa với công thức mảng sai này vì cái này luôn cộng tất cả khi 1 cell có Bào Ngư hoặc Nghêu - chỉ đúng khi =0 không có cái nào
{=SUM(IF(OR(C2:C11="Bào Ngư",C2:C11=" Nghêu"),G2:G11,""))}

có cần OR gì đầu

P/S: món quà đó chỉ cần gõ Y vào ô vàng là tạo cách chạy động tạo số 8-3 thế thôi mà, còn khám phá thì xem các công thức trong đó là bit ngay, chúc thành công

P/S2 uhm, có thể tôi nhầm là a ta làm rui, vì không down file kèm về

 

[tronghv77]

Cảm ơn bạn!

sao phải loằng ngoằng chi nhỉ, chắc là học nhiều quá rùi đây

T đang liệt kê ra một số cách có thể dùng cho bài toán này để tiện so sánh

=SUMIF(C2:C11,"Bào Ngư",G2:G11)+SUMIF(C2:C11,"Nghêu",G2:G11)

Cách của bạn là cách 1 rồi (trong file excel đính kèm)

Cảm ơn mn nhiều!

 

[nhat chi mai]

Em nghĩ kết thúc bài tập này ở đây thôi ạ! Cách gọn nhất cũng là cách là anh tronghv77 đã làm rồi. Em xin hết!

 

[bebo021999]

Nên dùng SUMPRODUC vì hàm này là chuẩn trong các trường hợp như thế này. Tránh xa SUMIF ra nhé.

 

[ptm0412]

C2:C11="Bào Ngư" trả về TRUE nếu có "Bào Ngư" trong vùng C2:C11
C2:C11="Nghêu" trả về TRUE nếu có "Nghêu" trong vùng C2:C11
OR hai cái đó lại, tức là nếu C2:C11 có 1 trong 2 loại hải sản này sẽ SUM toàn bộ vùng G2:G11.

bebo giải thích sai:

C2:C11="Bào Ngư" trả về 1 mảng true, false: Chỗ nào Bào Ngư thì true, ngược lại thì False

Tương tự:
C2:C11="Nghêu" trả về 1 mảng true, false: Chỗ nào Nghêu thì true, ngược lại thì False

Tuy nhiên hàm Or không dùng được cho mảng, vì kết quả hàm Or là 1 giá trị đơn, không phải là mảng. Nên ra kết quả sai.

 

[tronghv77]

Tránh xa SUMIF ra nhé

Sẽ ghi lòng tạc dạ điều này ạ

 

[vodoi2x]

Cảm ơn bạn!



T đang liệt kê ra một số cách có thể dùng cho bài toán này để tiện so sánh



Cách của bạn là cách 1 (trong file excel đính kèm)

Vậy thì bạn lưu ý, hàm OR không làm theo nguyên tắc công thức mảng đâu,

Nó sẽ phá mảng và tính toán OR hết cho từng phần tử trong mảng

Cứ thử ví dụ sau sẽ hiểu
Gõ 3 sô 1 2 0 lần lượt vào 3 cells: A1 A2 A3

rồi tại 1 cell kết quả gõ công thức sau
=IF(OR(A1:A3=0,A1:A3=99),A1:A3,-999)

Bôi đen công thức rồi bấm F9 xem kết quả thế nào bạn tự suy luận nhé,
-------------
Sau đó gõ số liệu mới
1 2 5
thử tiếp xem sao

rui sẽ hiểu

 

[vodoi2x]

Sẽ ghi lòng tạc dạ điều này ạ

Đừng nghe xui dại bạn ơi, SUMIF bị lỗi ở đó là trường hợp đặc biệt,
còn SUMIF vẫn chính xác

Việc 1 cơ sở dữ liệu đầy đủ và dùng đúng chuẩn thì không sợ sai

 

[bebo021999]

bebo giải thích sai:

C2:C11="Bào Ngư" trả về 1 mảng true, false: Chỗ nào Bào Ngư thì true, ngược lại thì False

Em đồng ý đúng là C2:C11="BN" trả về 1 mảng.
Nhưng ý em là khi lồng vào OR(C2:C11="BN",...) thì C2:C11="BN" trả về giá trị đơn.