Sao chỉ có thể tối đa 6 hàm IF lồng nhau ?

[ledoanhthu]

Tôi ngại dùng VBA vì sợ mấy ông ATV dỏm đục mất. Vì thế tôi xây dựng hàm tách tên từ họ và tên bằng công thức phức tạp từ các hàm IF lồng nhau. Với hàm của tôi thì tên người Việt thông thường sẽ được tách rất tốt (chữ Việt dài nhất là NGHIÊNG = 7 chữ cái). Tuy nhiên chỉ cần dài thêm 1 ký tự nữa thì hàm này bị điếc, vì khi thên hàm IF thứ 7 sẽ bị báo lỗi.
Kỳ lạ ?

Không gửi kèm file được, đành viết lên đây:

Cột A (họ & tên): Lê Văn Tèo

Cột C (tên):

=IF(LEFT(RIGHT(A1,2),1)=" ",TRIM(RIGHT(A1,2)),IF(LEFT(RIGHT(A1,3),1)=" ",TRIM(RIGHT(A1,3)),IF(LEFT(RIGHT(A1,4),1)=" ",TRIM(RIGHT(A1,4)),IF(LEFT(RIGHT(A1,5),1)=" ",TRIM(RIGHT(A1,5)),IF(LEFT(RIGHT(A1,6),1)=" ",TRIM(RIGHT(A1,6)),IF(LEFT(RIGHT(A1,7),1)=" ",TRIM(RIGHT(A1,7)),TRIM(RIGHT(A1,8))))))))

Cột B (Họ+):

=IF(LEN(A1)-LEN(C1)>0,TRIM(LEFT(A1,LEN(A1)-LEN(C1)-1)),"")

 

[Hoàng Trọng Nghĩa]

Tôi ngại dùng VBA vì sợ mấy ông ATV dỏm đục mất. Vì thế tôi xây dựng hàm tách tên từ họ và tên bằng công thức phức tạp từ các hàm IF lồng nhau. Với hàm của tôi thì tên người Việt thông thường sẽ được tách rất tốt (chữ Việt dài nhất là NGHIÊNG = 7 chữ cái). Tuy nhiên chỉ cần dài thêm 1 ký tự nữa thì hàm này bị điếc, vì khi thên hàm IF thứ 7 sẽ bị báo lỗi.
Kỳ lạ ?

Hình như tôi nhớ không lầm thì X2003 chỉ cho phép 7 hàm IF lồng vào nhau và giới hạn 255 ký tự. Nếu X2007 bạn xài thoải mái hơn.

 

[ndu96081631]

Tôi ngại dùng VBA vì sợ mấy ông ATV dỏm đục mất. Vì thế tôi xây dựng hàm tách tên từ họ và tên bằng công thức phức tạp từ các hàm IF lồng nhau. Với hàm của tôi thì tên người Việt thông thường sẽ được tách rất tốt (chữ Việt dài nhất là NGHIÊNG = 7 chữ cái). Tuy nhiên chỉ cần dài thêm 1 ký tự nữa thì hàm này bị điếc, vì khi thên hàm IF thứ 7 sẽ bị báo lỗi.
Kỳ lạ ?

Không gửi kèm file được, đành viết lên đây:

Cột A (họ & tên): Lê Văn Tèo

Cột C (tên): =IF(LEFT(RIGHT(A1,2),1)=" ",TRIM(RIGHT(A1,2)),IF(LEFT(RIGHT(A1,3),1)=" ",TRIM(RIGHT(A1,3)),IF(LEFT(RIGHT(A1,4),1)=" ",TRIM(RIGHT(A1,4)),IF(LEFT(RIGHT(A1,5),1)=" ",TRIM(RIGHT(A1,5)),IF(LEFT(RIGHT(A1,6),1)=" ",TRIM(RIGHT(A1,6)),IF(LEFT(RIGHT(A1,7),1)=" ",TRIM(RIGHT(A1,7)),TRIM(RIGHT(A1,8))))))))

Cột B (Họ+): =IF(LEN(A1)-LEN(C1)>0,TRIM(LEFT(A1,LEN(A1)-LEN(C1)-1)),"")

Ai lại làm thế chứ
Vầy đây:
1> Tách tên (tại cell C1)

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

2> Tách họ và chữ lót (tại cell B1)

=TRIM(SUBSTITUTE(A1,C1,""))

Đại khái thế ---> Bạn có thể tùy biến thêm!
Tên bao nhiêu ký tự cũng chơi tuốt

 

[Hoàng Trọng Nghĩa]

Tôi ngại dùng VBA vì sợ mấy ông ATV dỏm đục mất. Vì thế tôi xây dựng hàm tách tên từ họ và tên bằng công thức phức tạp từ các hàm IF lồng nhau. Với hàm của tôi thì tên người Việt thông thường sẽ được tách rất tốt (chữ Việt dài nhất là NGHIÊNG = 7 chữ cái). Tuy nhiên chỉ cần dài thêm 1 ký tự nữa thì hàm này bị điếc, vì khi thên hàm IF thứ 7 sẽ bị báo lỗi.
Kỳ lạ ?

Tên 7 chữ là max (với chữ Việt), thì tạm thời dùng cái này nè bạn, đừng mất công với hàm IF quá nhiều rắc rối!

=TRIM(RIGHT(SUBSTITUTE(TRIM(B1)," "," "),7))

 

[ndu96081631]

Tên 7 chữ là max (với chữ Việt), thì tạm thời dùng cái này nè bạn, đừng mất công với hàm IF quá nhiều rắc rối!

=TRIM(RIGHT(SUBSTITUTE(TRIM(B1)," "," "),7))

Người ta viết hàm TÁCH CHỮ không phải chỉ dành cho việc tách tên họ đâu. Biết đâu người ta tách MÃ gì đó, chẳng hạn: abcdefghijklm 1524
Vì thế xác định chắc chắn độ dài chuổi <= 7 là hơi chủ quan
Thay 7 thành Len(B1) là chăc bắp

 

[Hoàng Trọng Nghĩa]

Ai lại làm thế chứ
Vầy đây:
1> Tách tên (tại cell C1)

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

2> Tách họ và chữ lót (tại cell B1)

=TRIM(SUBSTITUTE(A1,C1,""))

Đại khái thế ---> Bạn có thể tùy biến thêm!
Tên bao nhiêu ký tự cũng chơi tuốt

Hàm tách tên 1> của Thầy có vấn đề nha! Nó không tách gì hết đó Thầy ơi.

 

[ndu96081631]

Hàm tách tên 1> của Thầy có vấn đề nha! Nó không tách gì hết đó Thầy ơi.

Ai chà... Xem file đính kèm này
Lưu ý: Đây chỉ là gợi ý về thuật toán chứ công thức chưa chắc đúng cho mọi trường hợp (ví dụ với tên Phùng Văn Văn)
Hãy cải tiến thêm nhé

 

[ledoanhthu]

Cảm ơn quý vị, không ngờ chủ đề này vui thế ! Đúng là hàm được xây dựng không chỉ để tách tên. Tôi mở Help của Excel để xem hàm SUBSTITUTE ngay bây giờ.

 

[Hoàng Trọng Nghĩa]

Ai chà... Xem file đính kèm này
Lưu ý: Đây chỉ là gợi ý về thuật toán chứ công thức chưa chắc đúng cho mọi trường hợp (ví dụ với tên Phùng Văn Văn)
Hãy cải tiến thêm nhé

Thầy xem, File của Thầy thì lại OK, em chép công thức đó thì Ẹc.. Ẹc... máy em có vấn đề ???

 

[ledoanhthu]

Chắc đặt cái này vào C1 nên phát sinh lỗi ?

=TRIM(SUBSTITUTE(A1,C1," "))

 

[ndu96081631]

Thầy xem, File của Thầy thì lại OK, em chép công thức đó thì Ẹc.. Ẹc... máy em có vấn đề ???

Thử mở file mới này xem (tôi mở bằng Excel 2007 vẫn bình thường)

 

[Hoàng Trọng Nghĩa]

Thử mở file mới này xem (tôi mở bằng Excel 2007 vẫn bình thường)

Cứ xem như Hàm tách tên của Thầy là đúng, thì Hàm tách họ em làm như sau, bảo đảm tuyệt đối!

=TRIM(LEFT(A20,LEN(A20)-TRIM(LEN(C20)+1)))

Cái File 2007 của Thầy em mở cũng thấy OK. (chắc muốn khùng với nó rồi)

 

[ndu96081631]

Cứ xem như Hàm tách tên của Thầy là đúng, thì Hàm tách họ em làm như sau, bảo đảm tuyệt đối!

=TRIM(LEFT(A20,LEN(A20)-TRIM(LEN(C20)+1)))

Cái File 2007 của Thầy em mở cũng thấy OK. (chắc muốn khùng với nó rồi)

Vâng! Dùng LEFT trong trường hợp này sẽ cho kết quả chính xác (đối với trường hợp chuổi là Phùng Văn Văn)
Giờ cải tiến thêm sao cho TACH HỌ không phụ thuộc vào cột TÊN nữa thì mới thật hoàn hảo (tức tách họ và tách tên độc lập với nhau)

 

[huuthang_bd]

Thầy xem, File của Thầy thì lại OK, em chép công thức đó thì Ẹc.. Ẹc... máy em có vấn đề ???

Sao bạn không gửi hẳn cái file ấy lên mà chỉ gửi cái hình như vậy? Tạo ra một cái hình như vậy hoàn toàn không khó.

Giờ cải tiến thêm sao cho TACH HỌ không phụ thuộc vào cột TÊN nữa thì mới thật hoàn hảo (tức tách họ và tách tên độc lập với nhau)

Dùng gián tiếp qua tên đã tách được cho gọn thôi. Chứ muốn công thức độc lập thì trong công thức tách họ, chỗ nào có tên thì thay bằng đoạn công thức tách tên. Thế cũng xong.

 

[Hoàng Trọng Nghĩa]

Sao bạn không gửi hẳn cái file ấy lên mà chỉ gửi cái hình như vậy? Tạo ra một cái hình như vậy hoàn toàn không khó.


Dùng gián tiếp qua tên đã tách được cho gọn thôi. Chứ muốn công thức độc lập thì trong công thức tách họ, chỗ nào có tên thì thay bằng đoạn công thức tách tên. Thế cũng xong.

Thì lấy công thức hàm Left trừ cái tách tên của Thầy dùng chung 1 cell là được chứ gì Thầy! Nhưng hơi dài và khá lủng củng.

 

[Hoàng Trọng Nghĩa]

Chắc đặt cái này vào C1 nên phát sinh lỗi ?

=TRIM(SUBSTITUTE(A1,C1," "))

Tại cột cell C1 mà bạn đặt công thức chứa C1 không bị lỗi mới là lạ đó nha! Cái đó đặt tại B1 chứ!

 

[Hoàng Trọng Nghĩa]

Sao bạn không gửi hẳn cái file ấy lên mà chỉ gửi cái hình như vậy? Tạo ra một cái hình như vậy hoàn toàn không khó.


Dùng gián tiếp qua tên đã tách được cho gọn thôi. Chứ muốn công thức độc lập thì trong công thức tách họ, chỗ nào có tên thì thay bằng đoạn công thức tách tên. Thế cũng xong.

Chà chà, lại không tin nhau rồi. Làm cái nì mà gian dối nhau làm chi nhỉ?

Tải hình lên thì mình cũng đã tải, up file mình cũng đã up. Rồi, mọi người cùng giải thích tại sao với trường hợp này, với công thức này thì đúng với File của Thầy NDU và trường hợp của MinhThien lại ẹc... ẹc... nhé!

 

[huuthang_bd]

Chà chà, lại không tin nhau rồi. Làm cái nì mà gian dối nhau làm chi nhỉ?

Tải hình lên thì mình cũng đã tải, up file mình cũng đã up. Rồi, mọi người cùng giải thích tại sao với trường hợp này, với công thức này thì đúng với File của Thầy NDU và trường hợp của MinhThien lại ẹc... ẹc... nhé!

Không phải là không tin nhau. Ý tôi là bạn đang đánh đố mọi người, nhưng một cái hình thì không thể nào tìm ra nguyên nhân được. Còn có file thì khác, việc tìm ra nguyên nhân không khó.

Trong file bạn, ký tự " " trong không thức không phải là ký tự Space vì vậy SUBSTITUTE() không có tác dụng gì cả. Có thể ký tự " " trong công thức được tạo bằng cách gõ Alt + 255.

Để kiểm tra, hãy copy ký tự " " trong công thức và so sánh với một ký tự " " được gõ bằng phím Space. Kết quả là False.

 

[ndu96081631]

Chà chà, lại không tin nhau rồi. Làm cái nì mà gian dối nhau làm chi nhỉ?

Tải hình lên thì mình cũng đã tải, up file mình cũng đã up. Rồi, mọi người cùng giải thích tại sao với trường hợp này, với công thức này thì đúng với File của Thầy NDU và trường hợp của MinhThien lại ẹc... ẹc... nhé!

Biết sai ở đâu rồi ---> Cái khoảng trắng mà bạn gõ trong công thức thực chất không phải là khoảng trắng (không hiểu bạn đã gõ bẳng cách nào)
- Khoảng trắng gõ bằng phím Spacebar có CODE = 32
- Khoảng trắng của bạn gõ có CODE = 160
Kiểm tra lại xem!

 

[ledoanhthu]

Ai lại làm thế chứ
Vầy đây:
1> Tách tên (tại cell C1)

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

2> Tách họ và chữ lót (tại cell B1)

=TRIM(SUBSTITUTE(A1,C1,""))

Đại khái thế ---> Bạn có thể tùy biến thêm!
Tên bao nhiêu ký tự cũng chơi tuốt

Tôi đã xem định nghĩa các hàm SUBSTITUTE và REPT (quả thật tôi không rành lắm về các hàm của Excel) và hiểu ra cách làm của anh khá tinh quái. Tuy nhiên, để các bạn khác áp dụng thoải mái thì cần phải cảnh báo rằng nên thay A1 bằng TRIM(A1) để khi dữ liệu có trường hợp dư " " ở cuối tên vẫn xài được.
Rất hay !