Loại các giá trị 0 trong hàm Sumproduct và hơn thế nữa!

[longlongago]

Chào các anh chị

Em đang cần phải tạo 1 công thức cho một loạt các Ratio. Tuy nhiên khi dùng Sumproduct, nếu các ratio có giá trị 0, #N/A hay # DIV/0 thì sẽ bị sai công thức. Có khá nhiều côngthức nên em không thể nào lại tạo 1 công thức cho mỗi Ratio được!

Các anh chị vui lòng xem giúp em với. Em cảm ơn nhiều!

 

[ndu96081631]

Chào các anh chị

Em đang cần phải tạo 1 công thức cho một loạt các Ratio. Tuy nhiên khi dùng Sumproduct, nếu các ratio có giá trị 0, #N/A hay # DIV/0 thì sẽ bị sai công thức. Có khá nhiều côngthức nên em không thể nào lại tạo 1 công thức cho mỗi Ratio được!

Các anh chị vui lòng xem giúp em với. Em cảm ơn nhiều!

Không biết chính xác chưa nhưng cứ làm đại ---> Bạn kiểm tra lại nhé (dùng công thức mãng chứ không SUMPRODUCT)

=SUM(IF((ISERROR(C2:C17)=FALSE),IF(C2:C17<>0,$B$2:$B$17*C2:C17,0),0))/SUM(IF((ISERROR(C2:C17)=FALSE),IF(C2:C17<>0,$B$2:$B$17,0),0))

 

[hoangdanh282vn]

Chào các anh chị

Em đang cần phải tạo 1 công thức cho một loạt các Ratio. Tuy nhiên khi dùng Sumproduct, nếu các ratio có giá trị 0, #N/A hay # DIV/0 thì sẽ bị sai công thức. Có khá nhiều côngthức nên em không thể nào lại tạo 1 công thức cho mỗi Ratio được!

Các anh chị vui lòng xem giúp em với. Em cảm ơn nhiều!

Bạn thử dùng công thức này xem. Tại ô F20 :

{=SUM(IF(ISERROR(F2:F17),0,F2:F17*$B$2:$B$17))/SUM(NOT(ISERROR(F2:F17))*$B$2:$B$17)}

bạn nhớ bấm Ctrl + Shift + Enter khi nhập xong nha

 

[BNTT]

Chào các anh chị

Em đang cần phải tạo 1 công thức cho một loạt các Ratio. Tuy nhiên khi dùng Sumproduct, nếu các ratio có giá trị 0, #N/A hay # DIV/0 thì sẽ bị sai công thức. Có khá nhiều côngthức nên em không thể nào lại tạo 1 công thức cho mỗi Ratio được!

Các anh chị vui lòng xem giúp em với. Em cảm ơn nhiều!

Bạn dùng cái này thử xem, công thức mảng:

=SUMPRODUCT(($B$2:$B$17)*(IFERROR(C2:C17,0)))/SUM($B$2:$B$17)​

Gõ xong, nhớ nhấn Ctrl+Shift+Enter nghe, rồi copy công thức đến R20.

 

[ndu96081631]

Bạn thử dùng công thức này xem. Tại ô F20 :

{=SUM(IF(ISERROR(F2:F17),0,F2:F17*$B$2:$B$17))/SUM(NOT(ISERROR(F2:F17))*$B$2:$B$17)}

bạn nhớ bấm Ctrl + Shift + Enter khi nhập xong nha

Nếu các cột Ratio = 0 thì phải bỏ luôn giá trị tương ứng tại cột MC (tức không SUM) ---> Hoangdanh còn thiếu điều kiện này nha!
Yêu cầu của tác giả:

Nếu ratio nào có các giá trị con bằng 0, #DIV/0!, #N/A thì loại các giá trị đó trong Sumproduct và cũng loại luôn MC của nó trong tổng MC

 

[BNTT]

Không biết ai đúng ai sai, vì tác giả chưa trả lời, nhưng cái công thức của tôi cho đáp số ở C20 và D20 y chang cái file mẫu...
Công thức của anh Ndu thì sai số chút chút, nhưng nói chung là cũng gần gần giống như thế.
Còn của Hoangdanh thì... nó ra một đáp số hoàn toàn khác.

Tác giả ơi ?

 

[ndu96081631]

Không biết ai đúng ai sai, vì tác giả chưa trả lời, nhưng cái công thức của tôi cho đáp số ở C20 và D20 y chang cái file mẫu...
Công thức của anh Ndu thì sai số chút chút, nhưng nói chung là cũng gần gần giống như thế.
Còn của Hoangdanh thì... nó ra một đáp số hoàn toàn khác.

Dựa vào YÊU CẦU (tô màu xanh) trong file của tác giả mà làm thôi ---> BNTT đọc lại xem và hiểu câu nói ấy như thế nào đây?

Tác giả ơi ?

Tác giả hỏi xong là "lặn mất xác" ---> Không thấy nick trên diển đàn nữa!
Hic...

 

[longlongago]

Dựa vào YÊU CẦU (tô màu xanh) trong file của tác giả mà làm thôi ---> BNTT đọc lại xem và hiểu câu nói ấy như thế nào đây?

Tác giả hỏi xong là "lặn mất xác" ---> Không thấy nick trên diển đàn nữa!
Hic...

Ơ ơ, em ăn trưa rồi ngủ luôn, bi h mới tỉnh .
Với các giá trị Ratio là 0, #N?A và #DIV/0 thì sẽ loại các giá trị đó trong Sumproduct và loại luôn cả MC của nó trong công thức tổngMCC nữa ạ.

Để e thử lại các hướng dẫn của anh chị xem thế nào đã ạ

 

[BNTT]

Dựa vào YÊU CẦU (tô màu xanh) trong file của tác giả mà làm thôi ---> BNTT đọc lại xem và hiểu câu nói ấy như thế nào đây?

Tác giả hỏi xong là "lặn mất xác" ---> Không thấy nick trên diển đàn nữa!
Hic...

Em hiểu rồi. Em quên trường hợp nếu có một phần tử trong Ratio = 0 thì không đưa phần tử tương ứng bên MC vào hàm SUM.

Xin sửa lại công thức trên như sau (vẫn là công thức mảng):

=SUM($B$2:$B$17*IFERROR(C2:C17,0)) / SUM(IF(ISERROR(C2:C17), 0, IF(C2:C17<>0,$B$2:$B$17,0)))​

Bi giờ thì đáp án đã y chang cái của anh Ndu... nhưng mà công thức ngắn hơn, hì hì...

 

[huuthang_bd]

Tôi nghĩ công thức này mới đúng:

C20=SUM(IF(ISERROR(C2:C17),0,C2:C17)*$B$2:$B$17)/SUM(IF(ISERROR(C2:C17),0,IF(C2:C17=0,0,$B$2:$B$17)))

Nếu kết quả giống như kết quả tại C20, D20 trong file thì sẽ sai với mô tả của tác giả.

 

[longlongago]

Dạ, sau khi em kiểm tra 3 công thức trên của các anh chị Hoangdanh282vn, Ndu96081631 và BNTT thì e thấy là công thức của a Ndu96081631 là sát với kết quả nhất. Tuy nhiên công thức của a Ndu96081631 có sai kết quả ở ô E20 và L20. Trong khi đó Công thức của a Danh lại khá sát với kq đúng. Còn 2 công thức còn lại thì sai số khá lớn

Em có đính kèm file để các anh chị xem luôn

 

[huuthang_bd]

Dạ, sau khi em kiểm tra 3 công thức trên của các anh chị Hoangdanh282vn, Ndu96081631 và BNTT thì e thấy là công thức của a Ndu96081631 là sát với kết quả nhất. Tuy nhiên công thức của a Ndu96081631 có sai kết quả ở ô E20 và L20. Trong khi đó Công thức của a Danh lại khá sát với kq đúng. Còn 2 công thức còn lại thì sai số khá lớn

Em có đính kèm file để các anh chị xem luôn

Cả 2 chỗ sai đó là bạn sai chứ không phải người khác sai.
Tại E20, công thức của bạn chưa trừ các giá trị ở cột B tương ứng với Ratio = 0
Tại L20, Mẫu là tổng cột A (Cột B mới đúng)

 

[longlongago]

Dạ, sau khi em kiểm tra 4 công thức trên của các anh chị Hoangdanh282vn, Ndu96081631, BNTT vá Huuthang_bd thì e thấy là công thức của a Ndu96081631và Huuthang_bd là sát với kết quả nhất (hai công thức này cho ra kq giống nhau và cũng sai giống nhau ở ô E20 và L20. Trong khi đó Công thức của a Danh lại khá sát với kq đúng ở L20.

Còn 2 công thức ủa a Danh và BNTT còn lại thì sai số khá lớn


Em đính kèm File để các anh chị xem luôn

 

[longlongago]

Cả 2 chỗ sai đó là bạn sai chứ không phải người khác sai.
Tại E20, công thức của bạn chưa trừ các giá trị ở cột B tương ứng với Ratio = 0
Tại L20, Mẫu là tổng cột A (Cột B mới đúng)

Cảm ơn bạn Thắng nhiều nhiều, mình đã nhìn ra chỗ sai rồi!

 

[DOSNET]

Nhờ mod xóa dùm bài này, thanks

 

[BNTT]

Dạ, sau khi em kiểm tra 4 công thức trên của các anh chị Hoangdanh282vn, Ndu96081631, BNTT vá Huuthang_bd thì e thấy là công thức của a Ndu96081631và Huuthang_bd là sát với kết quả nhất (hai công thức này cho ra kq giống nhau và cũng sai giống nhau ở ô E20 và L20. Trong khi đó Công thức của a Danh lại khá sát với kq đúng ở L20.

Còn 2 công thức ủa a Danh và BNTT còn lại thì sai số khá lớn


Em đính kèm File để các anh chị xem luôn

Tôi không hiểu bạn căn cứ vào cái gì để nói rằng công thức của tôi sai số quá lớn!

Ngay chính trong cái file bạn đính kèm đấy thôi: Công thức của tôi và của anh Ndu ra kết quả hoàn toàn giống nhau, không hề có một cái nào khác cả.

Bạn thử nhập vào ô C27 (ngay dưới hàng công thức của tôi) công thức này:

=C22=C26​

Rồi copy sang bên phải, xem nó có phải là một hàng chữ TRUE không?
(hàng 22 là công thức của anh Ndu, hàng 26 là công thức của tôi).

 

[longlongago]

BNTT thân mến,

Xin lỗi BNTT vì em nhìn nhầm nên nói là kết quả của BNTT sai nhìu. Thật ra là BNTT không sai mà e sai.