Tại sao dùng "hàm OR" trong "hàm mảng" cho kết quả không đúng? (1 người xem)
- Thread starter tronghv77
- Ngày gửi
Người dùng đang xem chủ đề này
nhat chi mai
Thành viên gắn bó
- Tham gia
- 22/11/11
- Bài viết
- 1,832
- Được thích
- 9
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.
Đầ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.
Chỉnh sửa lần cuối bởi điều hành viên:
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)
?
Lần chỉnh sửa cuối:
nhat chi mai
Thành viên gắn bó
- Tham gia
- 22/11/11
- Bài viết
- 1,832
- Được thích
- 9
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 đó ạ!
=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
Thành viên gạo cội
- Tham gia
- 26/1/11
- Bài viết
- 6,041
- Được thích
- 8,814
- Giới tính
- Nam
- Nghề nghiệp
- GPE
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:
Hoặc ngắn gọn hơn chỉ cần Enter:
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:
Mã:
=SUM(IF(C2:C11="Bào ngư",G2:G11,IF(C2:C11="Nghêu",G2:G11,0)))
Mã:
=SUMPRODUCT(((C2:C11="Bào ngư")+(C2:C11="Nghêu"))*G2:G11)
Cảm ơn bebo021999!
Cách này t đã biết rồi,
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é!
Lần chỉnh sửa cuối:
nhat chi mai
Thành viên gắn bó
- Tham gia
- 22/11/11
- Bài viết
- 1,832
- Được thích
- 9
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.
=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
Thành viên gắn bó
- Tham gia
- 22/11/11
- Bài viết
- 1,832
- Được thích
- 9
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ề
Lần chỉnh sửa cuối:
nhat chi mai
Thành viên gắn bó
- Tham gia
- 22/11/11
- Bài viết
- 1,832
- Được thích
- 9
bebo021999
Thành viên gạo cội
- Tham gia
- 26/1/11
- Bài viết
- 6,041
- Được thích
- 8,814
- Giới tính
- Nam
- Nghề nghiệp
- GPE
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é.
- Tham gia
- 4/11/07
- Bài viết
- 14,683
- Được thích
- 37,388
- Donate (Momo)
- Giới tính
- Nam
- Nghề nghiệp
- Consultant
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.
Lần chỉnh sửa cuối:
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
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
Thành viên gạo cội
- Tham gia
- 26/1/11
- Bài viết
- 6,041
- Được thích
- 8,814
- Giới tính
- Nam
- Nghề nghiệp
- GPE
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.
Mình đã xem qua bài [h=1]Hàm SUMIF cho ra kết quả như có MA vậy? ai giải thích dùm với![/h]mà bạn bebo021999 đính kèm rồi,
mình sẽ chú ý hơn trong việc sử dụng hàm này
Chân thành cảm ơn!
- Tham gia
- 4/11/07
- Bài viết
- 14,683
- Được thích
- 37,388
- Donate (Momo)
- Giới tính
- Nam
- Nghề nghiệp
- Consultant
Câu phát biểu đúng là: Hàm Or luôn trả về giá trị đơn, dù cho C2:C11="BN" trả về giá trị mảng.
Xem hình trong bài trên thì biết.
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,972
Bài này có nhiều cách làm: Có thể dùng SUMIF, SUMPRODUCT... nhưng nếu cố tình muốn dùng công thức mảng thì cũng không thể dùng OR hay AND
Với dạng bài toán tính tổng hoặc đếm nhiều điều kiện, OR sẽ được thay bằng dấu cộng và AND được thay bằng dấu nhân
Vậy nên, nếu muốn dùng công thức mảng thì phải thế này:
không có vụ OR, AND gì ở đây cả
Công thức trên, nếu biến đổi chút cũng có thể viết thành:
Rồi thay SUM thành SUMPRODUCT thì:
Công thức cuối cùng được cái chỉ cần Enter (khỏi phải bấm tổ hợp phím Ctrl + Shift + Enter)
-------------------------
Tóm lại, chỉ cần nhớ dòng màu đỏ ở trên
Với dạng bài toán tính tổng hoặc đếm nhiều điều kiện, OR sẽ được thay bằng dấu cộng và AND được thay bằng dấu nhân
Vậy nên, nếu muốn dùng công thức mảng thì phải thế này:
Mã:
=SUM(IF((C2:C11="Bào Ngư")[COLOR=#ff0000]+[/COLOR](C2:C11="Nghêu"),G2:G11,""))Công thức trên, nếu biến đổi chút cũng có thể viết thành:
Mã:
=SUM(((C2:C11="Bào Ngư")[COLOR=#ff0000]+[/COLOR](C2:C11="Nghêu"))*(G2:G11))
Mã:
=SUMPRODUCT(((C2:C11="Bào Ngư")[COLOR=#ff0000]+[/COLOR](C2:C11="Nghêu"))*(G2:G11))-------------------------
Tóm lại, chỉ cần nhớ dòng màu đỏ ở trên
[URL="http://www.giaiphapexcel.com/forum/member.php?61139-ndu96081631"]ndu96081631 : Bạn này nói đã đủ tất cả ý rồi đấy. Đúng là khi rớ vào hàm mảng thì OR và AND nó ko còn được xài nữa mà lúc dó được thay bằng dấu + và dấu nhân. Tuy nhiên mình xin bổ sung 1 điểm nho nhỏ là điều này chỉ đúng với việc tạo ra mảng true, false ko trùng nhau (Tức ko có true+true) hay nói một cách dễ hiểu hơn là các bạn chỉ nên áp dụng cho trường hợp cột điều kiện là cột đơn, trường hợp điều kiện nằm ở 2 cột khác nhau thì hãy cẩn thận với phép nhân vì có thể xảy ra true+true=2.[/URL]
Bài viết mới nhất
- Question
- Question
- Question
Hỏi công thức tìm tuần thiếu hàng
(1 người xem)
EM MUỐN TẠO MA TRẬN BIẾN ĐỔI GIÁ TRỊ PHẦN TỬ NGẪU NHIÊN
(2 người xem)
Mở Pass file excel phiên bản .xlsx , xlsm, xlsb
(1 người xem)
hàm đếm theo dòng
(1 người xem)