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

Liên hệ QC

hoangtechu

Thành viên hoạt động
Tham gia
8/7/11
Bài viết
105
Được thích
48
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

  • nhờ mọi người chỉ giúp.xlsx
    11.8 KB · Đọc: 6
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?
 

File đính kèm

  • DemLuXuBu.xlsm
    17 KB · Đọc: 12

File đính kèm

  • nhờ mọi người chỉ giúp.xlsx
    11 KB · Đọc: 15
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 ạ :)
 
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ỉ?
 
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
 
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
 
=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 ạ?
 
đú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ừ
???
 
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?
 
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ã
 
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í :):)
 
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...
 
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

  • GPE_CongMa.xlsx
    13.1 KB · Đọc: 4
Lần chỉnh sửa cuối:
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 ạ
 
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...
 
"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

  • GPE_CongMa.xlsx
    11.9 KB · Đọc: 15
Web KT
Back
Top Bottom