Tìm tổng chuỗi con trong chuỗi lớn (4 người xem)

[Phan Thế Hiệp]

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:

https://www.giaiphapexcel.com/diend...ập-hàng-chân-thành-cám-ơn.123588/#post-773512​

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]

1. Nghiên cứu hàm Lookup(2,1/...) tại đây:

https://www.giaiphapexcel.com/diendan/threads/nhờ-các-anh-chị-lập-công-thức-tính-số-ngày-nhập-hàng-chân-thành-cám-ơn.123588/#post-773512​

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!

 

[Phan Thế Hiệp]

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]

Đó 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 ạ.

 

[Phan Thế Hiệp]

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 ạ.

Không có chi đâu bạn!
Chúc bạn học tập vui với anh em Diễn đàn GPE.
Thân

 

[hoangtechu]

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&" ",""),))

 

[Phan Thế Hiệp]

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

 

[hoangtechu]

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

 

[Phan Thế Hiệp]

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

Đã sửa rồi hén!
Thân

 

[hoangtechu]

Đã sửa rồi hén!
Thân

em cảm ơn nhiều ạ.
anh có mở lớp dạy ko em theo với ạ

 

[Phan Thế Hiệp]

em cảm ơn nhiều ạ.
anh có mở lớp dạy ko em theo với ạ

Về Google Sheets bạn nên "níu áo" thọ giáo với thầy @yeudoi của Diễn đàn GPE này.
Tôi chỉ có vài ba miếng nho nhỏ thủ thân, bạn níu kéo làm mất thời gian cả hai mà thôi...

Thân.

 

[hoangtechu]

Về Google Sheets bạn nên "níu áo" thọ giáo với thầy @yeudoi của Diễn đàn GPE này.
Tôi chỉ có vài ba miếng nho nhỏ thủ thân, bạn níu kéo làm mất thời gian cả hai mà thôi...

Thân.

vâng
thôi em không " níu kéo " thầy nữa . có gì ko biết em xin phép hỏi tiếp nhé

 

[yeudoi]

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.

 

[hoangtechu]

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.

vậy à anh, thế gg sheet thì nên dùng công thức nào ạ? anh chỉ giúp em với. Em gà mờ quá

 

[yeudoi]

Đã sửa rồi hén!
Thân

Nhưng nếu giá trị số đầu mà là 2 số thì không được @Phan Thế Hiệp nha. Ví dụ: 20QCTh-1

 

[Phan Thế Hiệp]

Nhưng nếu giá trị số đầu mà là 2 số thì không được @Phan Thế Hiệp nha. Ví dụ: 20QCTh-1

Đã 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

 

[hoangtechu]

Đã 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]

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]

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]

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 ạ )