Công thức báo lỗi do đối số la String

[luongcp]

Thân gửi các bạn,

Trong một công thức nào đó yêu cầu đối số trong đó phải là number, thì khi ta nhập đối số là String thì CT cho ra giá trị [HASHTAG]#Value[/HASHTAG]

Nếu mình muốn CT hiểu String đó là 0 hoặc rỗng "" để CT vẫn chạy bình thường thì phải làm sao

Cứu bồ nhé ...

Thanks

 

[luongcp]

Còn 1 trường hợp nữa là :

VD : Sum(A1:A4) với A1:A4 = {1; 2; #DIV/0!; 6}

Mình muốn CT hiểu đối số #DIV/0! là 0 hoặc rỗng "" và vẫn cho ra kết quả = 9 (1+2+6) thì làm cách nào ?

Các bạn chỉ giáo nhé

 

[ForestC]

Có rất nhiều cách để giải quyết vấn đề này, sau đây mình xin đưa ra cách ..phức tạp nhất và ít hiệu quả nhất: if(type(a1)=2;value(a1);a1).

Mình nhớ không rõ có phải kiểu 2 là kiểu ký tự không, cái này bạn có thể xem lại cho chính xác nhé, đọc help của hàm type là biết ngay chứ gì ..

Còn nếu đúng kiểu 2 là ký tự thì giải thích hàm thế này:
Nếu a1 thuộc lại ký tự (dạng số) thì chuyển A1 qua dạng số. còn không thì .. Let It Be.

 

[luongcp]

Nếu có cách nào mà không cần phải dùng hàm IF để loại trừ mớ String, #DIV/0! ... thì hay

 

[ForestC]

Còn 1 trường hợp nữa là :

VD : Sum(A1:A4) với A1:A4 = {1; 2; #DIV/0!; 6}

Mình muốn CT hiểu đối số #DIV/0! là 0 hoặc rỗng "" và vẫn cho ra kết quả = 9 (1+2+6) thì làm cách nào ?

Các bạn chỉ giáo nhé

Trường hợp này chị Hân Dung thạo nhất:
={sum(if(iserror(a1:a4);0;a1:a4))}

Hàm Iserror bắt cho bạn tất cả mọi loại lỗi: #N/a, #Div/0 ..
Is= to be = là
erorror = lỗi
Dịch ra là: là lỗi (dễ hiểu chưa?!)

 

[ForestC]

Nếu có cách nào mà không cần phải dùng hàm IF để loại trừ mớ String, #DIV/0! ... thì hay

Yêu cầu hơi bị cao đó nhé, ai đời 1+luongcp = ? thì papa của Bill Gates sang Việt Nam cũng phải khóc trên cành cóc.
Tuy nhiên yêu cầu của bạn ..vẫn có thể đáp ứng được nếu như bạn cụ thể hoá nó ra bằng cách diễn giải bạn dùng trong trường hợp cụ thể nào hoặc tốt hơn thì bạn gửi file lên càng tốt!

 

[luongcp]

Mình từng gặp trường hợp là :

VD : Sum(A1:A4), nhưng ta nhập A2 = djkfhsdjsdkl chẳng hạn, thì hàm sum đó vẫn chạy tốt, nó không hề báo lỗi, và cho kết quả vẫn chính xác vì hiểu djkfhsdjsdkl là rỗng hoặc 0. Không hề dùng IF. Không biết có phải do Format không

 

[luongcp]

Maika ghi ra địa chỉ mail để mình gửi File Excel có trường hợp đó cho Maika kiểm chứng luôn

 

[ForestC]

Thực ra 2 công thức này sẽ cho kết quả khác nhau trong nhiều trương hợp đấy, bạn xem lại nhé:

=sum(a1:a2) và =a1+a2

Không phải lúc nào cũng cho ra kết quả giống nhau. Trong đó hàm sum mạnh và thông minh hơn phép tính cộng nhiều. Nói vậy, bạn có rút ra được điều gì không? Nếu không xin mời cô XYZ vào đây truyền lại kinh nghiệm cho luongcp nhé, cộng trừ nhân chia mấy cái có liên quan đến la string (tiếng Pháp chăng???) cô ý rất thạo, nào W, vào đây chơi đi cho nó đỡ stress!

 

[luongcp]

H`m .....Giận quá !

Nếu mình có quyền gửi kèm file lên đây thì sẽ cho các hạ thấy : Sum() những giá trị là text thì vẫn chạy tốt

 

[ForestC]

Bạn nghiên cứu thêm các vấn đề liên quan ở đây nhé, như mình đã nói ban đầu là chị Dung có thể sẽ giúp được bạn rất nhiều mà:

http://www.webketoan.com/forum/showthread.php?t=7066

 

[handung107]

Mình từng gặp trường hợp là :

VD : Sum(A1:A4), nhưng ta nhập A2 = djkfhsdjsdkl chẳng hạn, thì hàm sum đó vẫn chạy tốt, nó không hề báo lỗi, và cho kết quả vẫn chính xác vì hiểu djkfhsdjsdkl là rỗng hoặc 0. Không hề dùng IF. Không biết có phải do Format không

Bạn nói đúng, vì Excel luôn luôn tính tổng các giá trị số trong một dãy, kể cả dãy đó có chứa chuỗi dạng Text. Nếu trong dãy đó, bạn nhập #DIV/0! để Excel hiểu đó là chuỗi, thì công thức của bạn vẫn hòan thành, nhưng đó là kết quả trả về của phép tính chia cho số 0, thì Excel sẽ báo lỗi ngay. Bạn không có cách nào khác để cộng các giá trị số trong một mảng có chứa các giá trị lỗi ngòai cách dùng công thức mảng như tôi đã giới thiệu tron gbài "Tìm hiểu về mảng và công thức mảng" và bài "Tạo hàm để đếm và tính tổng". Đó là :
{=SUM(IF(ISERROR(A1:A4),0,A1:A4))}

 

[luongcp]

Cảm ơn 2 cao thủ Maika và handung107 nhé

 

[luongcp]

Cứu bồ nữa nhé Maika và handung107,

Mình có 1 công thức mảng dài thoòng loòng như thế này :

(Máy của mình chọn ngăn cách bằng dấu ; chứ không phải .)

={SUM(IF((F8:F57+G8:G57+H8:H57)<=2;0;IF((F8:F57+G8:G57+H8:H57)<=4;0,25;IF((F8:F57+G8:G57+H8:H57)<=9;0,5;IF((F8:F57+G8:G57+H8:H57)<=12;1;1,25))))}

Giá trị trong F8:H57 có khi mình nhập vào là number, có khi mình nhập vào ký tự H.

Nhập H thì CT trên đương nhiên cho kết quả [HASHTAG]#Value[/HASHTAG]!

Nếu dùng if(iserror ...) để loại thì cũng được, nhưng như thế rất dài dòng mỏi mệt đúng không ...

Vậy có cách nào ưu hơn không hỉ ... ???

 

[Bình_OverAC]

Vậy sao bạn không dùng hàm sum để cộng các cụm 3 số lại với nhau để có thể tránh được chữ H

 

[luongcp]

Ý bác OverAC có phải là thêm 1 cột (chẳng hạn cột I) bằng :

F8+G8+H8
F9+G9+H9
. . .
. . .
Sau đó ở cuối cột I này thiết lập công thức Sum(if(I8:I57 ...)

Nếu vậy thì không ổn vì mình không chỉ có 1 cụm 3 cột từ F:H mà có đến 31 cụm, mỗi cụm 3 cột. Cụm tiếp theo là từ I:K , L:N , ...

 

[Bình_OverAC]

Xin lổi nhầm! Vì vội quá nên chưa kiểm tra trước khi nói, chỉ nêu lên ý tưởng ... tượng thôi.! Nếu vậy thì thua! Dạo này em lười suy nghĩ lắm bác ạh. Có lẻ bác cũng nên nhọc công một chút. Nếu công thức này là thường xuyên với bác thì việc viết cho nó một cái hàm userdefine là một giải pháp tốt.

 

[handung107]

Có lẽ, nếu công thức dài quá, bạn hãy đặt tên cho từng đọan công thức. Bạn hãy xem lại bài "Tên và nhãn trong công thức"
Tôi giả sử trong công thức trên, bạn nhận thấy nếu ta sử dụng hàm SUM(F8:F57)+SUM(G8:G57)+SUM(H8:H57) thì kết quả sẽ cộng ngay cả trong các mảng này chứa dữ liệu Text. Vậy, bạn hãy đặt tên cho công thức này là TongCong chẳng hạn
Khi đó, công thức mảng của bạn :
={SUM(IF((F8:F57+G8:G57+H8:H57)<=2;0;IF((F8:F57+G8:G57+H8:H57)<=4;0,25;IF((F8:F57+G8:G57+H8:H57)<=9;0,5;IF((F8:F57+G8:G57+H8:H57)<=12;1;1,25))))}
Sẽ được viết lại thành :
=IF(TongCong<=2,0,IF(TongCong<=4,0.25,IF(TongCong<=9,0.5,IF(TongCong<=12,1,1.25))))
Bạn cũng có thể tiếp tục đặt tên nếu cần thiết khi công thức quá phức tạp. Nói chung, bạn cần đưa ra một vấn đề hòan chỉnh hoặc gửi File cho tôi, tôi mới có thể giúp bạn cụ thể, rõ ràng hơn được, vì đôi khi xem xét vấn đề trên góc độ tòan diện sẽ cho những giải pháp khác hơn góc độ riêng lẻ

 

[Tuanktcdcn]

I không rõ mục đích chính của bạn là phải gắng tìm cho ra cách của bạn hay vì tính hiệu quả?
Theo tôi khi lập công thức cho một hay nhiều ô, bản thân tại công thức đó phải xử lý tốt mọi tình huống luôn không bao giờ một công thức hoàn chỉnh lại cho ra #DIV/0, [HASHTAG]#Value[/HASHTAG]!,..EXCEL đã cung cấp các hàm Information như "Iserror", "Istext", "Isnumber" để chúng ta dùng. Nếu công thức đó mục đích là để + - * / ^ thì nếu lỗi phải = 0 lúc đó nó lại tiếp tục tham gia vào các công thức khác để + - * / ^ thì không bao giờ có lỗi về cấu trúc.
Tầm quan trọng của một công thức là phải chính xác 100% về mặt logic (nghiệp vụ), còn về cấu trúc và lỗi thì đừng bao giờ để nó tồn tại - nó là lỗi không đáng có, những lỗi kiểu này là do chúng ta chưa kinh nghiệm cần phải xử nó ngay, lúc đó nó mới được coi là công thức. Một công thức mà vẫn để các giá trị lỗi "#DIV/0"=một số chia cho 0, [HASHTAG]#Value[/HASHTAG] hiểu theo kiểu "1 xe máy+1 xe đạp = 2 chiếc xe" trông nó buồn cười lắm, vậy mà ta lại gọi nó là công thức.
Cái gì thuộc về phát huy tính tư duy theo hướng tích cực thì làm thôi!

 

[luongcp]

Thanks bác Tuanktcdcn,

Tôi hoàn toàn đổng ý với bác xử lý thẳng tay các lỗi #DIV/0!, [HASHTAG]#Value[/HASHTAG]! ... (nếu có), thông qua việc áp dụng các hàm information.

Có điều là đôi có những công thức đã dài lắm rồi (có khi dài đến 3 dòng bề ngang màn hình) thì nếu thêm các hàm information ghéo vào thì lài càng dài và vất vả. Những trường hợp đó tôi hay chấp nhận các kết quả [HASHTAG]#Value[/HASHTAG]! ... như nói trên.

Tuy nhiên, từ nay chắc phải cần cù hơn hơn thôi