Đố vui mừng sinh nhật GPE lần 5

Liên hệ QC
Trong lần gặp gỡ với NghiaPhuc, Bác Concogia có đố một câu cũng vui vui, tôi nghĩ cũng nên chia sẽ câu đố này:

Bằng hàm của Excel (không dùng VBA), không dùng name, không cột phụ, làm sao có thể tách được số ra khỏi chữ với cấu trúc TextNumber hoặc NumberText, với chiều dài của số và chữ không giới hạn.

Ví dụ:

1) TextNumber: Learning321 (KQ: 321), Learning_Excel1234 (KQ: 1234), ...

2) NumberText: 123Nghia (KQ: 123), 45456456TrongNghia (KQ: 45456456), ...

@: Concogia đố vậy chứ không biết đã có giải pháp chưa nhỉ? hihihi
 
Trong lần gặp gỡ với NghiaPhuc, Bác Concogia có đố một câu cũng vui vui, tôi nghĩ cũng nên chia sẽ câu đố này:

Bằng hàm của Excel (không dùng VBA), không dùng name, không cột phụ, làm sao có thể tách được số ra khỏi chữ với cấu trúc TextNumber hoặc NumberText, với chiều dài của số và chữ không giới hạn.

Ví dụ:

1) TextNumber: Learning321 (KQ: 321), Learning_Excel1234 (KQ: 1234), ...

2) NumberText: 123Nghia (KQ: 123), 45456456TrongNghia (KQ: 45456456), ...

@: Concogia đố vậy chứ không biết đã có giải pháp chưa nhỉ? hihihi
1 ví dụ cho trường hợp NumberText
PHP:
=LEFT($A1,COUNT(IF(ISERROR(1*MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)),"",1)))
Trường hợp TextNumber cũng tương tự (thay LEFT bằng RIGHT)
 
Lần chỉnh sửa cuối:
1 ví dụ cho trường hợp NumberText
PHP:
=LEFT($A1,COUNT(IF(ISERROR(1*MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)),"",1)))
Trường hợp TextNumber cũng tương tự (thay LEFT bằng RIGHT)
Em đã thử công thức trên nhưng sao chỉ tách được số đầu tiên thôi anh ơi.
NumberText.png
 
Trong lần gặp gỡ với NghiaPhuc, Bác Concogia có đố một câu cũng vui vui, tôi nghĩ cũng nên chia sẽ câu đố này:

Bằng hàm của Excel (không dùng VBA), không dùng name, không cột phụ, làm sao có thể tách được số ra khỏi chữ với cấu trúc TextNumber hoặc NumberText, với chiều dài của số và chữ không giới hạn.

Ví dụ:

1) TextNumber: Learning321 (KQ: 321), Learning_Excel1234 (KQ: 1234), ...

2) NumberText: 123Nghia (KQ: 123), 45456456TrongNghia (KQ: 45456456), ...

@: Concogia đố vậy chứ không biết đã có giải pháp chưa nhỉ? hihihi
Thêm một cách khác:
Mã:
=LEFT(A1,SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW($1:$10)-1,""))))
Nhân tiện đố về tách số mình có câu này khó hơn nè.
Tách lấy số trong một chuỗi có lẫn lộn số và các ký tự khác.
Ví dụ:
2x + 3y + z = 12 >> 2312
100 bạn tham dự GPE lần 5 >> 1005
8
h ngày 23 tháng 7 năm 2011 >> 82372011
 
Nhân tiện đố về tách số mình có câu này khó hơn nè.
Tách lấy số trong một chuỗi có lẫn lộn số và các ký tự khác.
Ví dụ:
2x + 3y + z = 12 >> 2312
100 bạn tham dự GPE lần 5 >> 1005
8
h ngày 23 tháng 7 năm 2011 >> 82372011
Đúng ra thì bài này đã từng làm trên GPE nhiều lần rồi. Vậy có gì đâu mà ĐỐ hả Thắng?
Chắc phải có ràng buộc thêm gì đó để làm khó chứ (chẳng hạn như không dùng name, dùng 1 công thức duy nhất, không cột phụ... vân vân)
Ẹc... Ẹc...
 
Đúng ra thì bài này đã từng làm trên GPE nhiều lần rồi. Vậy có gì đâu mà ĐỐ hả Thắng?
Chắc phải có ràng buộc thêm gì đó để làm khó chứ (chẳng hạn như không dùng name, dùng 1 công thức duy nhất, không cột phụ... vân vân)
Ẹc... Ẹc...
Đương nhiên là không được dùng cột phụ chứ anh. Name cũng chỉ mang tính chất rút gọn công thức thôi. Dùng cũng được, không dùng thì càng tốt.
 
Đương nhiên là không được dùng cột phụ chứ anh. Name cũng chỉ mang tính chất rút gọn công thức thôi. Dùng cũng được, không dùng thì càng tốt.
Thôi thì Thắng làm bài này mà không dùng name xem thử
Mình bó tay! (nếu mình làm thì ít nhất phải có 1 name)
 
Thôi thì Thắng làm bài này mà không dùng name xem thử
Mình bó tay! (nếu mình làm thì ít nhất phải có 1 name)
Anh đưa cách làm của anh lên thử đi. Em làm một công thức duy nhất cũng chỉ làm được ở Excel 2007 trở về sau. Excel 2003 bị lỗi do số hàm lồng nhau vượt quá cho phép.
 
Anh đưa cách làm của anh lên thử đi. Em làm một công thức duy nhất cũng chỉ làm được ở Excel 2007 trở về sau. Excel 2003 bị lỗi do số hàm lồng nhau vượt quá cho phép.
Thì đó... tôi làm trên Excel 2003 và cũng bị lỗi này
Tôi làm như sau:
- Đặt 1 name:
PHP:
Temp =IF(ISNUMBER(1*MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),"")
- Công thức tại B2:
PHP:
=IF(COUNT(Temp)=0,"",SUMPRODUCT((1*MID($A2,SMALL(Temp,ROW(INDIRECT("1:"&COUNT(Temp)))),1))*(10^(COUNT(Temp)-ROW(INDIRECT("1:"&COUNT(Temp)))))))
---------------
Nếu đặt thêm 1 name nữa thì công thức sẽ đẹp hơn
 

File đính kèm

  • Tachso.xls
    17 KB · Đọc: 23
Lần chỉnh sửa cuối:
1 ví dụ cho trường hợp NumberText
PHP:
=LEFT($A1,COUNT(IF(ISERROR(1*MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)),"",1)))
Trường hợp TextNumber cũng tương tự (thay LEFT bằng RIGHT)
Em nghĩ công thức của anh Ndu chỉ cần tới đây là đủ rồi đâu cần lồng hàm "If(iserror)" vào
PHP:
=RIGHT(A1;COUNT(1*MID(A1;ROW(INDIRECT("1:"&LEN(A1)));1)))
Vì em có thử hàm Count không đếm giá trị #Value không biết có đúng không
 
Lần chỉnh sửa cuối:
Thì đó... tôi làm trên Excel 2003 và cũng bị lỗi này
Tôi làm như sau:
- Đặt 1 name:
PHP:
Temp =IF(ISNUMBER(1*MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),ROW(INDIRECT("1:"&LEN($A2))),"")
- Công thức tại B2:
PHP:
=IF(COUNT(Temp)=0,"",SUMPRODUCT((1*MID($A2,SMALL(Temp,ROW(INDIRECT("1:"&COUNT(Temp)))),1))*(10^(COUNT(Temp)-ROW(INDIRECT("1:"&COUNT(Temp)))))))
---------------
Nếu đặt thêm 1 name nữa thì công thức sẽ đẹp hơn
Em cũng làm giống như anh nhưng thêm hàm TEXT() để không mất các số 0 đứng đầu và dùng hàm LARGE() thay cho hàm SMALL() để công thức gọn hơn một chút.
Mã:
=IF(COUNT(Temp)=0,"",TEXT(SUMPRODUCT((1*MID($A2,LARGE(Temp,ROW(INDIRECT("1:"&COUNT(Temp)))),1))*(10^(ROW(INDIRECT("1:"&COUNT(Temp)))-1))),REPT(0,COUNT(Temp))))
 
1) TextNumber: Learning321 (KQ: 321), Learning_Excel1234 (KQ: 1234), ...

Cách này cũng khá thú vị mà không cần dùng C+S+E nè:
PHP:
=SUBSTITUTE(A2, TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")-1))), "")
2) NumberText: 123Nghia (KQ: 123), 45456456TrongNghia (KQ: 45456456), ...
PHP:
=1*LEFT(A1,MAX(NOT(ISERROR(1*(LEFT($A1,ROW(OFFSET($A$1,0,0,LEN($A1),1))))))*ROW(OFFSET($A$1,0,0,LEN($A1),1))))
Nhưng cái này lại cần C+S+E.

Đúng là công thức có nhiều giải pháp.
 
Thêm một cách khác:
Mã:
=LEFT(A1,SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW($1:$10)-1,""))))
Nhân tiện đố về tách số mình có câu này khó hơn nè.
Tách lấy số trong một chuỗi có lẫn lộn số và các ký tự khác.
Ví dụ:
2x + 3y + z = 12 >> 2312
100 bạn tham dự GPE lần 5 >> 1005
8
h ngày 23 tháng 7 năm 2011 >> 82372011

Search thử trên mạng thấy có mấy giải pháp:

Công thức mảng
PHP:
=SUM(MID(A1,LARGE(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),ROW($A$1:INDEX($A:$A,COUNT(--MID(A1,ROW($1:$25),1))))),1)*10^(ROW(INDEX($1:$25,COUNT(--MID(A1,ROW(INDEX($1:$25,1,1):
INDEX($1:$25,LEN(A1),1)),1)),1):INDEX($1:$25,1,1))-1))

Cách này không là công thức mảng, làm ví dụ cho chuỗi dài 25 ký tự (nếu chuỗi dài hơn thì điều chỉnh trong công thức tại hàm Row)
PHP:
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Công thức mảng
PHP:
=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)

TTP
 
Với kiểu tách số của Hữu Thắng, có một Addins với nhiều hàm độc đáo có thể giải được nhiều vấn đề độc đáo.
Với hàm này, cho dù số ở bất cứ nơi đâu trong chuỗi, nó cũng tách ra được!
PHP:
=REGEX.SUBSTITUTE(A3,"a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z","")

Tách chữ:

PHP:
=REGEX.SUBSTITUTE(A2,"0|1|2|3|4|5|6|7|8|9","")
Để tải MoreFunc các bạn vào đường dẫn dưới đây:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html
 
Với kiểu tách số của Hữu Thắng, có một Addins với nhiều hàm độc đáo có thể giải được nhiều vấn đề độc đáo.
Với hàm này, cho dù số ở bất cứ nơi đâu trong chuỗi, nó cũng tách ra được!
PHP:
=REGEX.SUBSTITUTE(A3,"a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z","")

Tách chữ:

PHP:
=REGEX.SUBSTITUTE(A2,"0|1|2|3|4|5|6|7|8|9","")
Để tải MoreFunc các bạn vào đường dẫn dưới đây:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html
Ẹc... Ẹc... nếu phải dựa vào Add-In tải từ nước ngoài sao ta không tự mình lập trình lấy... Vụ tách lấy number, text có đầy trên GPE rồi còn gì
http://www.giaiphapexcel.com/forum/showthread.php?16983-Tách-số-không-dùng-vòng-lặp
 
Web KT
Back
Top Bottom