Tìm tổng chuỗi con trong chuỗi lớn

hoangtechu

Thành viên hoạt động
Tham gia ngày
8 Tháng bảy 2011
Bài viết
105
Được thích
48
Điểm
370
Em có 1 vấn đề khá là đau đầu ( với em) nhờ mọi người trong diễn đàn giúp đỡ.
Em có chuỗi các mã phân cách nhau bằng dấu +.
Em ví dụ như này ạ:
MCD+2AUL
MCG
AUL+MCG

Bây giờ em muốn tổng hợp lại kết quả như này:
MCD : 1
MAG:2
AUL:3
mọi người xem giúp em với nhé. tks cả nhà
 

File đính kèm

befaint

|||||||||||||
Tham gia ngày
6 Tháng một 2011
Bài viết
9,328
Được thích
10,905
Điểm
1,560
Em có 1 vấn đề khá là đau đầu ( với em) nhờ mọi người trong diễn đàn giúp đỡ.
Em có chuỗi các mã phân cách nhau bằng dấu +.
Em ví dụ như này ạ:
MCD+2AUL
MCG
AUL+MCG

Bây giờ em muốn tổng hợp lại kết quả như này:
MCD : 1
MAG:2
AUL:3
mọi người xem giúp em với nhé. tks cả nhà
Cho thêm 5 chục dòng dữ liệu xem cái nèo?
 

Ba Tê

Gội Rồi Mới Cạo
Tham gia ngày
5 Tháng năm 2009
Bài viết
11,477
Được thích
16,383
Điểm
1,860
Tuổi
61
Nơi ở
An Giang

File đính kèm

Tham gia ngày
10 Tháng mười 2017
Bài viết
2,830
Được thích
8,277
Điểm
360
Nơi ở
Sài Gòn

File đính kèm

hoangtechu

Thành viên hoạt động
Tham gia ngày
8 Tháng bảy 2011
Bài viết
105
Được thích
48
Điểm
370
Thêm giải pháp công thức:
Mã:
E2=SUMPRODUCT(ISNUMBER(SEARCH(T(TEXT(COLUMN($A:$J)-1," #"))&D2," "&SUBSTITUTE($A$2:$A$36,"+"," ")))*{1,1,2,3,4,5,6,7,8,9})
Enter, fill xuống.
Thân
cảm ơn bạn nhé
Bạn có thể giải thích công thức giúp mình được không? nhìn rối quá không hiểu được ạ :)
 

hoangtechu

Thành viên hoạt động
Tham gia ngày
8 Tháng bảy 2011
Bài viết
105
Được thích
48
Điểm
370
Thêm giải pháp công thức:
Mã:
E2=SUMPRODUCT(ISNUMBER(SEARCH(T(TEXT(COLUMN($A:$J)-1," #"))&D2," "&SUBSTITUTE($A$2:$A$36,"+"," ")))*{1,1,2,3,4,5,6,7,8,9})
Enter, fill xuống.
Thân
cảm ơn bạn nhé
Bạn có thể giải thích công thức giúp mình được không? nhìn rối quá không hiểu được ạ :)
Bạn ơi
cho mình hỏi
{1,1,2,3,4,5,6,7,8,9} chuỗi này mục đích để làm gì nhỉ?
 
Tham gia ngày
10 Tháng mười 2017
Bài viết
2,830
Được thích
8,277
Điểm
360
Nơi ở
Sài Gòn
cảm ơn bạn nhé
Bạn có thể giải thích công thức giúp mình được không? nhìn rối quá không hiểu được ạ :)
=SUMPRODUCT(ISNUMBER(SEARCH(T(TEXT(COLUMN($A:$J)-1," #"))&D2," "&SUBSTITUTE($A$2:$A$36,"+"," ")))*{1,1,2,3,4,5,6,7,8,9})
Còn có thể viết bằng:
=SUMPRODUCT(ISNUMBER(SEARCH({" ",1,2,3,4,5,6,7,8,9}&D2," "&SUBSTITUTE($A$2:$A$36,"+"," ")))*{1,1,2,3,4,5,6,7,8,9})
  • Mảng1= {" ",1,2,3,4,5,6,7,8,9}&D2 --> {" MCD","1MCD","2MCD","3MCD","4MCD","5MCD","6MCD","7MCD","8MCD","9MCD"}
  • Mảng2= " "&SUBSTITUTE($A$2:$A$36,"+"," ")-->Nối Vùng A2:A36, mà Vùng này đã thay các dấu + bằng ký tự rỗng, Vd: "MCD+2AUL" thành "MCD 2AUL", sau đó nối thêm 1 ký tự rỗng ở đầu nữa, Vd: " "&"MCD 2AUL" thành " MCD 2AUL", tức lúc nào cũng có 1 ký tự rỗng trước các đoạn.
  • ISNUMBER(SEARCH( Mảng1, Mảng2))--> Tức tìm các ký tự Mảng1 trong Mảng2, nếu có search() trả về số thứ tự ký tự, nếu không tìm thấy nó báo lỗi #VALUE, nhưng nhở hàm ISNUMBER() nên với những ô mà nó tìm thấy nó báo TRUE, lỗi thì FALSE. Ở đây kết quả sẽ ra 1 Mảng 2 chiều có số cột là 10 cột (ứng với mảng 1), và có số dòng là chiều dài của Vùng A2:A36.
  • Tương ứng với Mảng1: {" MCD","1MCD","2MCD","3MCD","4MCD","5MCD","6MCD","7MCD","8MCD","9MCD"}, ta thấy " MCD" sẽ bằng "1MCD" tức bằng 1, "2MCD" bằng 2, "3MCD" bằng 3......; Vì vậy ta tạo Mảng tương ứng với các vị trí của Mảng 1 là {1,1,2,3,4,5,6,7,8,9}.
  • Cuối cùng, nhân 2 Mảng =ISNUMBER(SEARCH( Mảng1, Mảng2))*{1,1,2,3,4,5,6,7,8,9} tức những giá trị TRUE sẽ lấy tương ứng giá trị trong Mảng {1,1,2,3,4,5,6,7,8,9}. Dùng SUMPRODUCT() để cộng lại các giá trị của 2 Mảng nah6n nhau đó.
Thân
 

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,356
Được thích
52,861
Điểm
11,910
Thêm giải pháp công thức:
Mã:
E2=SUMPRODUCT(ISNUMBER(SEARCH(T(TEXT(COLUMN($A:$J)-1," #"))&D2," "&SUBSTITUTE($A$2:$A$36,"+"," ")))*{1,1,2,3,4,5,6,7,8,9})
Enter, fill xuống.
Thân
Sửa chuỗi MCD+2AUL tại cell A2 thành 0MCD+2AUL thì kết quả sẽ sai
Hình như nó "kị" con zero hay sao ấy
Cũng chuỗi trên, sửa thành MCD+12AUL lại sai tiếp
Hình như nó "kị" con số bự hay sao ấy
 

hoangtechu

Thành viên hoạt động
Tham gia ngày
8 Tháng bảy 2011
Bài viết
105
Được thích
48
Điểm
370
=SUMPRODUCT(ISNUMBER(SEARCH(T(TEXT(COLUMN($A:$J)-1," #"))&D2," "&SUBSTITUTE($A$2:$A$36,"+"," ")))*{1,1,2,3,4,5,6,7,8,9})
Còn có thể viết bằng:
=SUMPRODUCT(ISNUMBER(SEARCH({" ",1,2,3,4,5,6,7,8,9}&D2," "&SUBSTITUTE($A$2:$A$36,"+"," ")))*{1,1,2,3,4,5,6,7,8,9})
  • Mảng1= {" ",1,2,3,4,5,6,7,8,9}&D2 --> {" MCD","1MCD","2MCD","3MCD","4MCD","5MCD","6MCD","7MCD","8MCD","9MCD"}
  • Mảng2= " "&SUBSTITUTE($A$2:$A$36,"+"," ")-->Nối Vùng A2:A36, mà Vùng này đã thay các dấu + bằng ký tự rỗng, Vd: "MCD+2AUL" thành "MCD 2AUL", sau đó nối thêm 1 ký tự rỗng ở đầu nữa, Vd: " "&"MCD 2AUL" thành " MCD 2AUL", tức lúc nào cũng có 1 ký tự rỗng trước các đoạn.
  • ISNUMBER(SEARCH( Mảng1, Mảng2))--> Tức tìm các ký tự Mảng1 trong Mảng2, nếu có search() trả về số thứ tự ký tự, nếu không tìm thấy nó báo lỗi #VALUE, nhưng nhở hàm ISNUMBER() nên với những ô mà nó tìm thấy nó báo TRUE, lỗi thì FALSE. Ở đây kết quả sẽ ra 1 Mảng 2 chiều có số cột là 10 cột (ứng với mảng 1), và có số dòng là chiều dài của Vùng A2:A36.
  • Tương ứng với Mảng1: {" MCD","1MCD","2MCD","3MCD","4MCD","5MCD","6MCD","7MCD","8MCD","9MCD"}, ta thấy " MCD" sẽ bằng "1MCD" tức bằng 1, "2MCD" bằng 2, "3MCD" bằng 3......; Vì vậy ta tạo Mảng tương ứng với các vị trí của Mảng 1 là {1,1,2,3,4,5,6,7,8,9}.
  • Cuối cùng, nhân 2 Mảng =ISNUMBER(SEARCH( Mảng1, Mảng2))*{1,1,2,3,4,5,6,7,8,9} tức những giá trị TRUE sẽ lấy tương ứng giá trị trong Mảng {1,1,2,3,4,5,6,7,8,9}. Dùng SUMPRODUCT() để cộng lại các giá trị của 2 Mảng nah6n nhau đó.
Thân
cảm ơn bạn nhiều lắm
Bài đã được tự động gộp:

Sửa chuỗi MCD+2AUL tại cell A2 thành 0MCD+2AUL thì kết quả sẽ sai
Hình như nó "kị" con zero hay sao ấy
Cũng chuỗi trên, sửa thành MCD+12AUL lại sai tiếp
Hình như nó "kị" con số bự hay sao ấy
đúng rồi anh ơi, vậy công thức phải sửa như nào mới đúng ạ?
 

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,356
Được thích
52,861
Điểm
11,910
đúng rồi anh ơi, vậy công thức phải sửa như nào mới đúng ạ?
Công thức thì cứ để anh Hiệp sĩ kia sửa đi (ảnh là trùm vụ này)
Có điều tôi còn thắc mắc: Trong dữ liệu của bạn có khi nào có dấu gì khác ngoài dấu cộng không? Chẳng hạn là dấu trừ
???
 

Ba Tê

Gội Rồi Mới Cạo
Tham gia ngày
5 Tháng năm 2009
Bài viết
11,477
Được thích
16,383
Điểm
1,860
Tuổi
61
Nơi ở
An Giang
Sửa chuỗi MCD+2AUL tại cell A2 thành 0MCD+2AUL thì kết quả sẽ sai
Hình như nó "kị" con zero hay sao ấy
Cũng chuỗi trên, sửa thành MCD+12AUL lại sai tiếp
Hình như nó "kị" con số bự hay sao ấy
Chưa lường được trường hợp "dzô dziên" 0MCD, Vậy là có đếm nó không?
 

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,356
Được thích
52,861
Điểm
11,910
Chưa lường được trường hợp "dzô dziên" 0MCD, Vậy là có đếm nó không?
Cứ cho là bỏ qua trường hợp trên đi, nhưng còn tình huống này nè anh:
- Cell A2 hiện có giá trị MCD+2AUL và kết quả đếm cho mã AUL là 55
- Nếu em sửa cell A2 thành MCD+AAUL thì kết quả đếm cho mã AUL bị lỗi (mà lý ra trừ bớt đi 2 số còn 53 mới đúng)
Ngoài ra thì còn nhiều tình huống khác nữa, chẳng hạn như việc phân biệt HOA thường cho mã
 

befaint

|||||||||||||
Tham gia ngày
6 Tháng một 2011
Bài viết
9,328
Được thích
10,905
Điểm
1,560
Chưa lường được trường hợp "dzô dziên" 0MCD, Vậy là có đếm nó không?
Dạ, chắc không phải vậy đâu ạ. Em thì cho rằng nếu =0 thì không nhập vào (chắc không có mã nào bắt đầu là số 0).
Còn bài #11.5 chủ thớt thuận tay gõ vậy thôi. Hí hí :):)
 

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,356
Được thích
52,861
Điểm
11,910
Còn bài #11.5 chủ thớt thuận tay gõ vậy thôi. Hí hí :):)
Hình như là bài #11.6 thì phải (tôi canh canh thấy hơi.. méo méo chút) ++--//*
Thôi, chờ tác giả xác nhận vậy. Mà có thêm trừ nhân chia nữa là... vui cmn luôn á
Ẹc... Ẹc...
 
Tham gia ngày
10 Tháng mười 2017
Bài viết
2,830
Được thích
8,277
Điểm
360
Nơi ở
Sài Gòn
Công thức thì cứ để anh Hiệp sĩ kia sửa đi (ảnh là trùm vụ này)
Cái dzụ này thì thầy thừa sức để trị, vì thầy là "trùm của trùm" mà, phim "Ho li wút" gọi là "Gót Pha đờ" (God Father - biên dzầy chứ không anh Ba ảnh hay cự nự):)
vậy công thức phải sửa như nào mới đúng ạ?
Theo cách cũ, tôi điều chỉnh cho bạn đến số: (chỉ chứa số nguyên dương) <= 1000 trước mỗi mã :
Mã:
E2=SUM(ISNUMBER(SEARCH(TEXT(TRANSPOSE(ROW($1:$1001))-1," #")&D2," "&SUBSTITUTE($A$2:$A$36,"+"," ")))*TRANSPOSE(IFERROR(1/(1/(ROW($1:$1001)-1)),1)))
Kết thúc bằng Ctrl+Shift+Enter.

Theo cách mới, thì số của mỗi mã thoải mái (kể cả số lẻ, còn 0MCD thì không tính). Tuy nhiên, có quy định cho phép trong giới hạn, như sau: trong 1 dòng chứa từ 1 đến 10 mã, và cách biệt nhau bằng dấu "+":
Mã:
E2=SUM(IFERROR(--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE($A$2:$A$36&"+","+",REPT(" ",100)),(COLUMN($A:$J)-1)*100+1,100))," "&D2,1),D2,""),))
Kết thúc bằng Ctrl+Shift+Enter.

Thân
 

File đính kèm

Lần chỉnh sửa cuối:

hoangtechu

Thành viên hoạt động
Tham gia ngày
8 Tháng bảy 2011
Bài viết
105
Được thích
48
Điểm
370
Cái dzụ này thì thầy thừa sức để trị, vì thầy là "trùm của trùm" mà, phim "Ho li wút" gọi là "Gót Pha đờ" (God Father - biên dzầy chứ không anh Ba ảnh hay cự nự):)

Theo cách cũ, tôi điều chỉnh cho bạn đến số: (chỉ chứa số nguyên dương) <= 1000 trước mỗi mã :
Mã:
E2=SUM(ISNUMBER(SEARCH(TEXT(TRANSPOSE(ROW($1:$1001))-1," #")&D2," "&SUBSTITUTE($A$2:$A$36,"+"," ")))*TRANSPOSE(IFERROR(1/(1/(ROW($1:$1001)-1)),1)))
Kết thúc bằng Ctrl+Shift+Enter.

Theo cách mới, thì số của mỗi mã thoải mái (kể cả số lẻ, còn 0MCD thì không tính). Tuy nhiên, có quy định cho phép trong giới hạn, như sau: trong 1 dòng chứa từ 1 đến 10 mã, và cách biệt nhau bằng dấu "+":
Mã:
E2=SUM(IFERROR(--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE($A$2:$A$36&"+","+",REPT(" ",100)),(COLUMN($A:$J)-1)*100+1,100))," "&D2,1),D2,""),))
Kết thúc bằng Ctrl+Shift+Enter.

Thân
Cảm ơn anh ạ
 

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,356
Được thích
52,861
Điểm
11,910
Cái dzụ này thì thầy thừa sức để trị, vì thầy là "trùm của trùm" mà, phim "Ho li wút" gọi là "Gót Pha đờ" (God Father - biên dzầy chứ không anh Ba ảnh hay cự nự):)

Theo cách cũ, tôi điều chỉnh cho bạn đến số: (chỉ chứa số nguyên dương) <= 1000 trước mỗi mã :
Mã:
E2=SUM(ISNUMBER(SEARCH(TEXT(TRANSPOSE(ROW($1:$1001))-1," #")&D2," "&SUBSTITUTE($A$2:$A$36,"+"," ")))*TRANSPOSE(IFERROR(1/(1/(ROW($1:$1001)-1)),1)))
Kết thúc bằng Ctrl+Shift+Enter.

Theo cách mới, thì số của mỗi mã thoải mái (kể cả số lẻ, còn 0MCD thì không tính). Tuy nhiên, có quy định cho phép trong giới hạn, như sau: trong 1 dòng chứa từ 1 đến 10 mã, và cách biệt nhau bằng dấu "+":
Mã:
E2=SUM(IFERROR(--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE($A$2:$A$36&"+","+",REPT(" ",100)),(COLUMN($A:$J)-1)*100+1,100))," "&D2,1),D2,""),))
Kết thúc bằng Ctrl+Shift+Enter.

Thân
"Bắt giò" tiếp
- Sửa cell A2 từ MCD+2AUL thành MCD+2 AUL ==> kết quả sai
- Sửa cell A2 từ MCD+2AUL thành MCD+2AUL2 ==> kết quả sai
Ẹc... Ẹc...
 
Tham gia ngày
10 Tháng mười 2017
Bài viết
2,830
Được thích
8,277
Điểm
360
Nơi ở
Sài Gòn
"Bắt giò" tiếp
- Sửa cell A2 từ MCD+2AUL thành MCD+2 AUL ==> kết quả sai
- Sửa cell A2 từ MCD+2AUL thành MCD+2AUL2 ==> kết quả sai
Ẹc... Ẹc...
Đã né nó rồi thầy còn hê lên nữa (mất 1 cử cà phe buổi sáng dzồi!) :(
Công thức điều chỉnh dài thêm chút nha chủ thớt @hoangtechu :
Mã:
E2=SUM(IFERROR(--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE(SUBSTITUTE($A$2:$A$36," ","")&"+","+",REPT(" ",100)),(COLUMN($A:$J)-1)*100+1,100))&" "," "&D2&" ",1),D2&" ",""),))
Kết thúc bằng Ctrl+Shift+Enter.

Thân
 

File đính kèm

Top Bottom