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

Quảng cáo

ThuNghi

Hãy cho rồi sẽ nhận!
Thành viên danh dự
Tham gia ngày
16 Tháng tám 2006
Bài viết
3,814
Được thích
4,464
Điểm
0

Ếch Xanh

Thành viên tích cực
Tham gia ngày
12 Tháng tám 2009
Bài viết
865
Được thích
1,564
Điểm
0
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
 

ndu96081631

Huyền thoại GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
30,725
Được thích
53,557
Điểm
50
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:

nghiaphuc

Thành viên gạo cội
Thành viên danh dự
Tham gia ngày
25 Tháng chín 2009
Bài viết
5,732
Được thích
8,823
Điểm
50
Nơi ở
Ayun Pa - Gia Lai
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.
 

ndu96081631

Huyền thoại GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
30,725
Được thích
53,557
Điểm
50

huuthang_bd

Chuyên gia GPE
Tham gia ngày
10 Tháng chín 2008
Bài viết
8,018
Được thích
9,366
Điểm
3,168
Nơi ở
TP.HCM
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
 

ndu96081631

Huyền thoại GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
30,725
Được thích
53,557
Điểm
50
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...
 

huuthang_bd

Chuyên gia GPE
Tham gia ngày
10 Tháng chín 2008
Bài viết
8,018
Được thích
9,366
Điểm
3,168
Nơi ở
TP.HCM
Đú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.
 

ndu96081631

Huyền thoại GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
30,725
Được thích
53,557
Điểm
50
Đươ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)
 

huuthang_bd

Chuyên gia GPE
Tham gia ngày
10 Tháng chín 2008
Bài viết
8,018
Được thích
9,366
Điểm
3,168
Nơi ở
TP.HCM
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.
 

ndu96081631

Huyền thoại GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
30,725
Được thích
53,557
Điểm
50
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: 22
Lần chỉnh sửa cuối:

nmhung49

Thành viên tích cực
Tham gia ngày
20 Tháng tám 2009
Bài viết
1,187
Được thích
1,331
Điểm
0
Tuổi
33
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:

huuthang_bd

Chuyên gia GPE
Tham gia ngày
10 Tháng chín 2008
Bài viết
8,018
Được thích
9,366
Điểm
3,168
Nơi ở
TP.HCM
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))))
 

Ếch Xanh

Thành viên tích cực
Tham gia ngày
12 Tháng tám 2009
Bài viết
865
Được thích
1,564
Điểm
0
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.
 

TranThanhPhong

Chúc mừng năm mới!
Thành viên danh dự
Tham gia ngày
16 Tháng ba 2007
Bài viết
2,105
Được thích
19,012
Điểm
4,368
Nơi ở
TP. HCM
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
 

Ếch Xanh

Thành viên tích cực
Tham gia ngày
12 Tháng tám 2009
Bài viết
865
Được thích
1,564
Điểm
0
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
 

ndu96081631

Huyền thoại GPE
Thành viên BQT
Super Moderator
Tham gia ngày
5 Tháng sáu 2008
Bài viết
30,725
Được thích
53,557
Điểm
50
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
 
Quảng cáo
Top Bottom