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

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
tuyệt vời anh ạ.
em chưa hiểu hiểu chỗ này lắm, anh có thể giải thích giúp em đc không?
LOOKUP(2,1/SEARCH('Mã SP'!$C$3:$C$87,TRIM(MID(SUBSTITUTE($E2&"+","+",REPT(" ",100)),(RIGHT(G$1)-1)*100+1,100))),'Mã SP'!$C$3:$C$87))

=IF(ISERROR(IF(G2<>"",--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE($E2&"+","+",REPT(" ",100)),(RIGHT(H$1)-1)*100+1,100))&" "," "&G2&" ",1),G2&" ",""),)),,IF(G2<>"",--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE($E2&"+","+",REPT(" ",100)),(RIGHT(H$1)-1)*100+1,100))&" "," "&G2&" ",1),G2&" ",""),))

Em dốt công thức mảng quá, anh có mở lớp dạy hoặc dạy ol không cho em theo học với nhé
1. Nghiên cứu hàm Lookup(2,1/...) tại đây:

2. Do trên Google Sheets dùng được hàm IFERROR(), nên nó thay cho IF( ISERROR('Công thức'), "" , 'Công thức'). Do vậy, bạn có thể thay công thức: =IF(ISERROR(IF(G2<>"",--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE($E2&"+","+",REPT(" ",100)),(RIGHT(H$1)-1)*100+1,100))&" "," "&G2&" ",1),G2&" ",""),)),,IF(G2<>"",--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE($E2&"+","+",REPT(" ",100)),(RIGHT(H$1)-1)*100+1,100))&" "," "&G2&" ",1),G2&" ",""),))

Thành:
=IFERROR(IF(G2<>"",--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE($E2&"+","+",REPT(" ",100)),(RIGHT(H$1)-1)*100+1,100))&" "," "&G2&" ",1),G2&" ",""),)),)
Cái này tương tự cách đã giải thích tại bước 1/

Thâ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
1. Nghiên cứu hàm Lookup(2,1/...) tại đây:

2. Do trên Google Sheets dùng được hàm IFERROR(), nên nó thay cho IF( ISERROR('Công thức'), "" , 'Công thức'). Do vậy, bạn có thể thay công thức: =IF(ISERROR(IF(G2<>"",--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE($E2&"+","+",REPT(" ",100)),(RIGHT(H$1)-1)*100+1,100))&" "," "&G2&" ",1),G2&" ",""),)),,IF(G2<>"",--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE($E2&"+","+",REPT(" ",100)),(RIGHT(H$1)-1)*100+1,100))&" "," "&G2&" ",1),G2&" ",""),))

Thành:
=IFERROR(IF(G2<>"",--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE($E2&"+","+",REPT(" ",100)),(RIGHT(H$1)-1)*100+1,100))&" "," "&G2&" ",1),G2&" ",""),)),)
Cái này tương tự cách đã giải thích tại bước 1/

Thân.
vâng, anh cho em hỏi thêm với nhé
--SUBSTITUTE(SUBSTITUTE... thì cái dấu -- đó ý nghĩa gì ạ?
em thấy hàm sumproduct thỉnh thoảng cũng dùng 2 dấu đó ạ.
tks anh!
 
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
vâng, anh cho em hỏi thêm với nhé
--SUBSTITUTE(SUBSTITUTE... thì cái dấu -- đó ý nghĩa gì ạ?
em thấy hàm sumproduct thỉnh thoảng cũng dùng 2 dấu đó ạ.
tks anh!
Đó là một trong các cách để ép "Chuỗi-số" về đúng dạng "Số". Ví dụ: =--"0908372981" sẽ thành số =908372981.
Các cách tương tự:
  1. = Value("Chuỗi-số")
  2. ="Chuỗi-số"*1 ; hoặc ="Chuỗi-số"/1
  3. ="Chuỗi-số"+0 ; hoặc ="Chuỗi-số" -0
  4. Hoặc "Chuỗi-số" cộng trừ nhân chia (bị chia) với một số, hoặc công thức có kết quả là số, chuỗi-số, hoặc giá trị luận lý khác thì cũng chuyển về số.
Riêng kết quả là giá trị luận lý: TRUE và FALSE, ngoài các cách trên còn có thể dùng hàm N('biểu thức so sánh cho kết quả TRUE/FALSE'), nếu TRUE đổi thành 1, và FALSE thành 0.

Thâ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
Đó là một trong các cách để ép "Chuỗi-số" về đúng dạng "Số". Ví dụ: =--"0908372981" sẽ thành số =908372981.
Các cách tương tự:
  1. = Value("Chuỗi-số")
  2. ="Chuỗi-số"*1 ; hoặc ="Chuỗi-số"/1
  3. ="Chuỗi-số"+0 ; hoặc ="Chuỗi-số" -0
  4. Hoặc "Chuỗi-số" cộng trừ nhân chia (bị chia) với một số, hoặc công thức có kết quả là số, chuỗi-số, hoặc giá trị luận lý khác thì cũng chuyển về số.
Riêng kết quả là giá trị luận lý: TRUE và FALSE, ngoài các cách trên còn có thể dùng hàm N('biểu thức so sánh cho kết quả TRUE/FALSE'), nếu TRUE đổi thành 1, và FALSE thành 0.

Thân.
vâng
em cảm ơn cách anh chị GPE , đặc biệt là anh @Phan Thế Hiệp đã giúp đỡ em nhiều ạ.
 

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
Như đã hứa, bạn xem hình và file kèm.
Thân
View attachment 201380
Em đang nghiên cứu công thức của anh, những vấn đề anh giải thích trên em cũng gần hiểu rồi ạ. còn cái này ko thấy anh nhắc đến, anh có thể giải thích thêm giúp em cái phần em bôi đậm này nó liên quan gì đến cả công thức của anh ạ?
'=SUM(IFERROR(--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE(SUBSTITUTE($A$2:$A$36," ","")&"+","+",REPT(" ",100)),(COLUMN($A:$J)-1)*100+1,100))&" "," "&D2&" ",1),D2&" ",""),))
 
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
Em đang nghiên cứu công thức của anh, những vấn đề anh giải thích trên em cũng gần hiểu rồi ạ. còn cái này ko thấy anh nhắc đến, anh có thể giải thích thêm giúp em cái phần em bôi đậm này nó liên quan gì đến cả công thức của anh ạ?
'=SUM(IFERROR(--SUBSTITUTE(SUBSTITUTE(" "&TRIM(MID(SUBSTITUTE(SUBSTITUTE($A$2:$A$36," ","")&"+","+",REPT(" ",100)),(COLUMN($A:$J)-1)*100+1,100))&" "," "&D2&" ",1),D2&" ",""),))
Đã có ghi trong bước 1/
Xem kỹ các công thức nhằm giải thích, trong file kèm.
Thâ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
1/ Theo cách cũ: Dùng trên Google Sheets bạn phải điều chỉnh như sau: Sheet "Tổng Hợp......."

D1=ARRAYFORMULA(sumproduct(isnumber(search({" ",1,2,3,4,5,6,7,8,9}&B1 ," "&substitute('Trang tính1'!$E$2:$E$341,"+"," ")))*{1,1,2,3,4,5,6,7,8,9}))

Lưu ý là dấu "," trong các Mảng: {" ",1,2,3,4,5,6,7,8,9} và {1,1,2,3,4,5,6,7,8,9}, chứ không phải dấu ";" như trên file của bạn nha!

2/ Tôi gửi thêm cho bạn một gợi ý khác, bạn thử mang lên Google Sheets, bạn sẽ thấy cách mới đơn giản hơn nhiều.
Xem file kèm.

Thân
anh @Phan Thế Hiệp xem giúp em file gg sheet này , em nhập công thức nó báo lỗi phân tích cú pháp, anh có thể sửa công thức luôn ở đó giúp em với nhé
em cảm ơn nhiều ạ
https://docs.google.com/spreadsheet...4Z3Xw89xwtu52ySphnhNZpt3U/edit#gid=1969973558
 
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
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

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

yeudoi

Thành viên gắn bó
Thành viên BQT
Moderator
Tham gia ngày
12 Tháng sáu 2006
Bài viết
2,906
Được thích
7,083
Điểm
910
Nơi ở
TP Hội An
Công thức này mình thấy sử dụng phù hợp cho excel chứ google sheets thì chưa tối ưu.
 
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

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
Đã giải quyết ở bài #17. Nhưng chủ thớt thấy 1 số là đủ xài rồi, nên dùng công thức cũ nhất.
Thân
vâng, đúng vậy ạ
với lại công thức đó em còn hiểu được. Những công thức sau phức tạp e chưa hiểu nên ko dám dùng ạ
anh @yeudoi ơi, trên gg sheet anh bảo còn công thức nào tối ưu phải không ạ? anh có thể chia sẻ cho bọn em được học hỏi ko anh?
 

yeudoi

Thành viên gắn bó
Thành viên BQT
Moderator
Tham gia ngày
12 Tháng sáu 2006
Bài viết
2,906
Được thích
7,083
Điểm
910
Nơi ở
TP Hội An
Mình
vâng, đúng vậy ạ
với lại công thức đó em còn hiểu được. Những công thức sau phức tạp e chưa hiểu nên ko dám dùng ạ
anh @yeudoi ơi, trên gg sheet anh bảo còn công thức nào tối ưu phải không ạ? anh có thể chia sẻ cho bọn em được học hỏi ko anh?
Đó là công thức split nhưng mình đang thấy nó hơi dài
 

Mộng Thường 2001

Thành viên tiêu biểu
Tham gia ngày
28 Tháng mười một 2017
Bài viết
616
Được thích
1,627
Điểm
360
Tuổi
18
vâng, đúng vậy ạ
với lại công thức đó em còn hiểu được. Những công thức sau phức tạp e chưa hiểu nên ko dám dùng ạ
anh @yeudoi ơi, trên gg sheet anh bảo còn công thức nào tối ưu phải không ạ? anh có thể chia sẻ cho bọn em được học hỏi ko anh?
Bạn cần thì mình bạn học là được rồi, lôi kéo tôi vô chi mà thành "bọn em" lận.
Yên tâm, thấy bạn hỏi từ hôm thứ 6 đến giờ, anh @yeudoi sẽ rút ngắn công thức lẫn thời gian cho bạn.
Thân.
ps: Sau bài #56, tự dưng nick @Phan Thế Hiệp "bị ngọng", mất ba cái nút dưới cùng "Báo cáo", "Thích", và "Trả lời", đành xài tạm nick nà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
Bạn cần thì mình bạn học là được rồi, lôi kéo tôi vô chi mà thành "bọn em" lận.
Yên tâm, thấy bạn hỏi từ hôm thứ 6 đến giờ, anh @yeudoi sẽ rút ngắn công thức lẫn thời gian cho bạn.
Thân.
ps: Sau bài #56, tự dưng nick @Phan Thế Hiệp "bị ngọng", mất ba cái nút dưới cùng "Báo cáo", "Thích", và "Trả lời", đành xài tạm nick này.
Dạ, bọn em ý là em và các bạn em thôi anh ạ :))
 
Top Bottom