Cách xử lý lỗi #Value!

[bluecat]

Mình có 1 công thức như sau :
A1=VALUE(IF($A$22="TP2";KQ!HZ1;"")&IF($A$22="DT";KQ!HZ22;"")&IF($A$22="BT";KQ!HZ43;"")&IF($A$22="VT";KQ!HZ64;"")&IF($A$22="DN";KQ!HZ85;"")&IF($A$22="CT";KQ!HZ106;"")&IF($A$22="TN";KQ!HZ127;"")&IF($A$22="AG";KQ!HZ148;"")&IF($A$22="SB";KQ!HZ169;"")&IF($A$22="VL";KQ!HZ190;"")&IF($A$22="TP7";KQ!HZ211;"")&IF($A$22="LA";KQ!HZ232;"")&IF($A$22="TG";KQ!HZ253;"")&IF($A$22="KG";KQ!HZ274;"")&IF($A$22="TPHCM";TPHCM!HZ1;"")&IF($A$22="Chính";ChinhPhu!HZ1;"")&IF($A$22="Phụ";ChinhPhu!HZ22;""))

Xin cho hỏi có cách nào đặt tên để làm cho nó ngắn lại. Công thức này tự động thay đổi những địa chỉ không cố định, vì nó được sử dụng từ A1 tới IV1. Vậy khi mình đặt cho nó 1 cái tên thì A2,A3 ... có tự động thay đổi giá trị được không?
Và làm cách nào để xử lý được lỗi #Value! Nếu dùng hàm IF và ERROR.TYPE thì qúa dài và nó trả về lỗi #N/A
Xin các cao thủ giúp đỡ. Thanks Much!

 

[SA_DQ]

Mình có 1 công thức như sau :. . .
Xin các cao thủ giúp đỡ. Thanks Much!

Hãy tham khảo hàm SWITCH TẠI BÀI 6:
http://giaiphapexcel.com/forum/showthread.php?t=1949&highlight=switch
Bổ sung ví dụ:

[b]Function udfSWITCH(Rng As Range)[/b]
 Dim RVal
 RVal = Rng.Value
 udfSWITCH = Switch(RVal = "TP2", Range("HZ1"), RVal = "DT", Range("HZ22"), RVal = "BT", Range("HZ43"))

[b]End Function[/b]

 

[Đào Việt Cường]

Dear bluecat,
-------------
Hoan hô!!! Rất khó hiểu!!!
Để xem nào:
- Giá trị trại ô $A$22 sẽ thay đổi tùy theo giá trị mà bạn muốn đưa vào:
TP2; BT; VT; DN; C T; TN; AG; SB; VL; TP7; LA; TG; KG;TPHCM; Chính; Phụ
- Công thức chỉ trả về chuỗi của 1 hàm IF có điều kiện đúng, các chuỗi còn lại là chuỗi rỗng.
- Khi bạn copy công thức này sang ô khác thì kết quả trả về của hàm IF có điều kiện đúng sẽ trả về giá trị của ô tịnh tiến.
- Cuối cùng: không tài nào đoán được bạn đang định làm gì, nếu bạn muốn đặt tên cho công thức thì chỉ cần Ctrl+F3 ra rồi điền từ hàm IF vào cho mỗi Name sau đó tại A1 kết hợp các Name lại với nhau bởi dấu &. Theo mình bạn nên lập danh mục rồi dùng VLOOKUP xem có hợp lý không:

TP2    KQ!HZ1
BT    KQ!HZ43
VT    KQ!HZ64
DN    KQ!HZ85
C T    KQ!HZ106
TN    KQ!HZ127
AG    KQ!HZ148
SB    KQ!HZ169
VL    KQ!HZ190
TP7    KQ!HZ211
LA    KQ!HZ232
TG    KQ!HZ253
KG    KQ!HZ274
TPHCM    TPHCM!HZ1
Chính    ChinhPhu!HZ1
Phụ    ChinhPhu!HZ22

 

[bluecat]

To : Đào Việt Cường!
Mình không biết gì về cách lập danh mục bạn có thể nói rõ hơn hoặc cho mình ví dụ cụ thể nào đó. Cảm ơn bạn về cách đặt tên.

 

[Đào Việt Cường]

Dear bluecat,
-------------
Vấn đề của bạn cần cụ thể hơn: Bạn đang có cái A và phải làm cái B để có cái C. Bạn đang tìm cái B nhưng các bạn khác lại muốn biết cái A và cái C cơ. Nếu bạn chỉ đưa cách xử lý của bạn rồi nhờ mọi người sửa theo hướng đó thì chắc không thể có cách xử lý tốt hơn.
Mình chỉ đưa ra hướng xử lý của mình khi gặp trường hợp tìm kiếm nhiều điều kiện như ở trên: lập danh mục rồi tìm kiếm (VLOOKUP). Theo như công thức của bạn thì giá trị trả về chỉ là một hàm IF được thực hiện với điều kiện đúng (vì tất cả các hàm IF chỉ tham chiếu đến 1 giá trị, tại ô $A$22)
Tuy nhiên, nếu lập danh mục bạn sẽ gặp phải vấn đề là giá trị trả về là 1 tham chiếu trong khi bạn muốn lấy ra giá trị tại tham chiếu đó. Không sao, bạn sử dụng hàm INDIRECT để "convert" tham chiếu này về dạng tham chiếu thực thụ. Sau đây là 1 ví dụ:
Giả sử bạn đã lập được danh mục như trong bài #3 (đấy là tại bạn không điền được giá trị thay bởi tham chiếu), bây giờ đặt tên cho danh mục này, ví dụ là DANHMUC, công thức tại ô A1 như sau:

= INDIRECT(VLOOKUP($A$22, DANHMUC,2,0))

Khi giá trị trong ô $A$22 khớp đúng với cột 1 trong danh mục, hàm VLOOKUP trả về giá trị tương ứng trong cột 2. Hàm INDIRECT có nhiệm vụ chuyển đổi chuỗi giá trị tìm được thành tham chiếu thực thụ và trả về giá trị tương ứng tại tham chiếu đó.

Nếu vẫn không làm được, bạn gửi file minh họa lên, đảm bảo chủ đề không có thêm bài viết thứ 3!

 

[bluecat]

Dear Đào Việt Cường,
Cảm ơn đã giúp mình hiểu về cách lập DANHMUC (đó có phải chính là DynamicRangeName không bạn?).
Theo cách bạn chỉ mình làm được công thức tại A1, nhưng nếu mình kéo công thức sang A2, kéo xuống B2 thì nó không trả về KQ của ô tịnh tiến.
Nếu chủ đề không có thêm bài viết thứ #3 thì buồn quá!!!!

Còn lỗi #Value làm sao xử lý vậy bạn? Cách đặt tên của bạn mình làm được rồi nhưng vẫn bị mắc lỗi #Value.

 

[Đào Việt Cường]

Dear bluecat,
-------------
Phức tạp rồi đây! Hình như số liệu của bạn quan trọng lắm hay sao mà không gửi lên bạn nhỉ (vẫn chưa tính bài thứ 1 thì đâu đã có bài thứ 3!)
Trong trường hợp bạn áp dụng công thức cho các ô khác thì phương pháp lập danh mục đúng là không ổn thật! Nhưng liệu có giải pháp nào (phụ thuộc vào thông tin trên sheet của bạn) để xác định trước danh mục không? Bạn đang tìm kiếm cái gì dựa vào cái gì vậy? Trường hợp ô bên bảng nguồn bị sắp xếp lại thì phương pháp tìm kiếm của bạn còn chính xác nữa không?
Tốt nhất là bạn nên nêu ra ý tưởng của mình hơn là nhờ mọi người cố gắng "mò" ra cái lỗi #VALUE!. (Theo kinh nghiệm, các lỗi này có thể tìm ra rất nhanh bằng công cụ Formula Auditing, đặc biệt là Evaluate Formula, bạn cố gắng xem).
Bài thứ 1 chỉ được tính khi bạn diễn đạt thật chi tiết vấn đề của bạn hoặc gửi file minh họa nhé!

 

[bluecat]

Có gì đâu mà phức tạp bạn ơi! Đây là file chứa lỗi #Value! nè. Mời bạn tham khảo và cho ý kiến. Cảm ơn trước nha.

 

[Đào Việt Cường]

Dear bluecat,
-------------
Đúng là "có gì đâu mà phức tạp", lỗi #VALUE! xảy ra chính bởi vì bạn muốn ...VALUE.
Hãy xem ghi chú đầu tiên trong Remarks của hàm này:

o Text can be in any of the constant number, date, or time formats recognized by Microsoft Excel. If text is not in one of these formats, VALUE returns the #VALUE! error value.
o ...

Cụ thể trong trương hợp này text is not in one of these formats là vì đối số của hàm VALUE là một chuỗi rỗng. Nghĩa là công thức:
=VALUE(IF($A$22="TP2";KQ!HZ1;"")&IF($A$22="DT";K Q!HZ22;"")&IF($A$22="BT";KQ!HZ43;"")&IF($A$22="VT" ;KQ!HZ64;"")&IF($A$22="DN";KQ!HZ85;"")&IF($A$22="C T";KQ!HZ106;"")&IF($A$22="TN";KQ!HZ127;"")&IF($A$2 2="AG";KQ!HZ148;"")&IF($A$22="SB";KQ!HZ169;"")&IF( $A$22="VL";KQ!HZ190;"")&IF($A$22="TP7";KQ!HZ211;"" )&IF($A$22="LA";KQ!HZ232;"")&IF($A$22="TG";KQ!HZ25 3;"")&IF($A$22="KG";KQ!HZ274;"")&IF($A$22="TPHCM"; TPHCM!HZ1;"")&IF($A$22="Chính";ChinhPhu!HZ1;"")&I F($A$22="Phụ";ChinhPhu!HZ22;""))

trả về chuỗi rỗng.

Hãy kiểm chứng bằng cách nhập vào ô $A$22 giá trị "TN", hàm sẽ trả về giá trị tại ô KQ!HZ127, kiểm tra trên tập tin của bạn thì đúng là ô này không có dữ liệu.
Trên đây mình chỉ nêu ra nguyên nhân, còn cách khắc phục tùy thuộc vào bạn tổ chức lại cơ sở dữ liệu.

function go(url) { window.open(url, "_self"); } function OpenInNewWindow(url) { go(url); } function go(url) { window.open(url, "_self"); } function OpenInNewWindow(url) { go(url); } function go(url) { window.open(url, "_self"); } function OpenInNewWindow(url) { go(url); }

 

[bluecat]

Công thức chính xác là bắt đầu tham chiếu tại cột A chứ không phải là HZ(hôm trước mình copy lộn chút xíu hihi..). tuy nhiên vì công thức kéo dài đến cột 256 nên chắc chắn là sẽ báo lỗi. Mình biết tại sao gây ra lỗi, mong bạn chỉ cách khắc phục. Còn dữ liệu nó sẽ mở rộng ra đến cột 256, theo bạn mình nên tổ chức lại ntn cho phù hợp? Mong bạn cho ý kiến. Chân thành cảm ơn.