Chia sẻ hàm tách số, tách chữ khỏi chuỗi, tìm ô có chứa ký tự (chuỗi) (1 người xem)

[Yunnis Ngo]

Trong file là một số hàm mình hay dùng, chia sẻ cho mọi người góp ý cải tiến và tham khảo

 

[Duy Tùng 9x]

Trong file là một số hàm mình hay dùng, chia sẻ cho mọi người góp ý cải tiến và tham khảo

Thấy công thức Office365 nên cũng mạo muội vào học hỏi, vì kiến thức mình cũng hạn hẹp, xin được học hỏi thêm.
- Bài tách số

=REDUCE(C2:D2,B3:B18,LAMBDA(x,y,LET(a,MID(y,ROW(1:50),1),IFERROR(VSTACK(x,IF({1,0},CONCAT(TOCOL(a*1,3)),CONCAT(T(IFERROR(--a,a))))),""))))

Hoặc Regex thì: =REGEXREPLACE(B3:B18,{"[^\d]","[^\D]"},)
- Bài Filter nâng cao gì đó thì:

=FILTER(CHOOSECOLS(A4:G20,1,2,4,6,7),((F4:F20=J2)+(F4:F20=K2))*(G4:G20=J1))

 

[Yunnis Ngo]

View attachment 307907
Thấy công thức Office365 nên cũng mạo muội vào học hỏi, vì kiến thức mình cũng hạn hẹp, xin được học hỏi thêm.
- Bài tách số

=REDUCE(C2:D2,B3:B18,LAMBDA(x,y,LET(a,MID(y,ROW(1:50),1),IFERROR(VSTACK(x,IF({1,0},CONCAT(TOCOL(a*1,3)),CONCAT(T(IFERROR(--a,a))))),""))))

Hoặc Regex thì: =REGEXREPLACE(B3:B18,{"[^\d]","[^\D]"},)
- Bài Filter nâng cao gì đó thì:

=FILTER(CHOOSECOLS(A4:G20,1,2,4,6,7),((F4:F20=J2)+(F4:F20=K2))*(G4:G20=J1))

- Filter: e xin học hỏi cái choosecols của bác. =FILTER(CHOOSECOLS($A$4:$G$20,1,2,4,6,7),ISNUMBER(SEARCH($J$1,$G$4:$G$20))*(ISNUMBER(SEARCH($J$2,$F$4:$F$20))+ISNUMBER(SEARCH($K$2,$F$4:$F$20))))
Nhưng em để cái Isnumber(search) là để có thể tìm bất kỳ kí tự nào trong chuỗi mà nó vẫn ra, VD: Cty ABC, chỉ cần gõ "ABC" nó cũng sẽ tìm ra, chứ không cần phải match đúng từng chữ (Cty ABC) như công thức rút gọn của bác.

- Tách số, tham khảo công thức của bác, xong em rút gọn công thức của em, em nghĩ ko cần dùng hàm reduce
=LAMBDA(cell,LET(a,(MID(cell,SEQUENCE(LEN(cell)),1)),CHOOSE({2,1},CONCAT(T(IFERROR(--a,a))),CONCAT(TOCOL(a*1,3)))))(B4)

 

[Duy Tùng 9x]

- Filter: e xin học hỏi cái choosecols của bác. =FILTER(CHOOSECOLS($A$4:$G$20,1,2,4,6,7),ISNUMBER(SEARCH($J$1,$G$4:$G$20))*(ISNUMBER(SEARCH($J$2,$F$4:$F$20))+ISNUMBER(SEARCH($K$2,$F$4:$F$20))))
Nhưng em để cái Isnumber(search) là để có thể tìm bất kỳ kí tự nào trong chuỗi mà nó vẫn ra, VD: Cty ABC, chỉ cần gõ "ABC" nó cũng sẽ tìm ra, chứ không cần phải match đúng từng chữ (Cty ABC) như công thức rút gọn của bác.

- Tách số, tham khảo công thức của bác, xong em rút gọn công thức của em, em nghĩ ko cần dùng hàm reduce
=LAMBDA(cell,LET(a,(MID(cell,SEQUENCE(LEN(cell)),1)),CHOOSE({2,1},CONCAT(T(IFERROR(--a,a))),CONCAT(TOCOL(a*1,3)))))(B4)

- Mình lại góp tiếp. Nếu bạn search hẳn thì cái ID mình lại dùng byrow cho ngắn
- Còn cái tách số và chữ, mình dùng reduce và bạn không dùng thì bạn thấy bạn khác mình ở cái gì không. Ngoại trừ data trăm nghìn dòng bỏ qua, còn vài trăm vài nghìn. Nếu dùng 365 thì mình thích theo kiểu one cell bạn ạ. Có thể là mất thời gian và đôi khi là khó tìm ra nguyên nhân vấn đề nếu lỗi. Nhưng cái mình thích đó là cách tư duy từ các hàm đó. Có lẽ bạn mới học được cái Lambda thầy Mỹ mới chia sẻ, nên thích hướng đó

 

[Yunnis Ngo]

View attachment 307972
- Mình lại góp tiếp. Nếu bạn search hẳn thì cái ID mình lại dùng byrow cho ngắn
- Còn cái tách số và chữ, mình dùng reduce và bạn không dùng thì bạn thấy bạn khác mình ở cái gì không. Ngoại trừ data trăm nghìn dòng bỏ qua, còn vài trăm vài nghìn. Nếu dùng 365 thì mình thích theo kiểu one cell bạn ạ. Có thể là mất thời gian và đôi khi là khó tìm ra nguyên nhân vấn đề nếu lỗi. Nhưng cái mình thích đó là cách tư duy từ các hàm đó. Có lẽ bạn mới học được cái Lambda thầy Mỹ mới chia sẻ, nên thích hướng đó

Dạ em mới biết đc trang forum của mình vài ngày à, em search google với coi youtube rồi mò tạo công thức theo nhu cầu. Hàm 365 là hàm mới hả anh, nếu a có link hay clip nào hay cho em xin để học hỏi với nhé.
Em tưởng mình dùng Reduce chỉ để khai báo thêm biến, mong anh chỉ thêm, em chỉ dùng lambda để gán cth vào short name cho nó thành cth rút gọn trong Name manager.
Em sn 2000, làm kế toán, em tự mò excel thui ạ

 

[Duy Tùng 9x]

Dạ em mới biết đc trang forum của mình vài ngày à, em search google với coi youtube rồi mò tạo công thức theo nhu cầu. Hàm 365 là hàm mới hả anh, nếu a có link hay clip nào hay cho em xin để học hỏi với nhé.
Em tưởng mình dùng Reduce chỉ để khai báo thêm biến, mong anh chỉ thêm, em chỉ dùng lambda để gán cth vào short name cho nó thành cth rút gọn trong Name manager.
Em sn 2000, làm kế toán, em tự mò excel thui ạ

Nói chung mình cũng tiếp cận với excel cỡ 1,5 năm thôi bạn. Toàn vào các nhóm zalo là nhiều, xem các anh làm rồi mình tách nhỏ mò phân tích. Cũng chưa học qua 1 ai cả, nên lý thuyết về các hàm đó có thể mình chưa hiểu rõ hết và đúng hết. Nhưng làm bài thì cũng may vẫn ổn và đúng thôi. 365 thì nhiều hàm mà, lambda là có 365 rồi. Nên học Map, Makearray, Reduce, Scan, Byrow, Bycol. Nói chung các bộ này phải đi cùng lambda thì mới thấy nó mạnh như nào. Mỗi hàm có 1 cái hay và ý nghĩa riêng.

 

[Hoàng Tuấn 868]

excel thui

Trước đây mình chỉ nghe thấy chó thui, bê thui, lần đầu thấy "excel thui". Không biết có ngon như mấy món kia không nhỉ.

 

[thuytrangbp28]

Trường hợp trên 365 thì công thức Regex của @Duy Tùng 9x chắc là tối ưu nhất rồi, muốn dùng Define Name bằng LAMBDA thì chuyển nó thành =LAMBDA(x,REGEXREPLACE(x,{"[^\d]","[^\D]"},))(B3:B18)
Để tìm hiểu thêm thì theo mình nên viết công thức mảng động, mất công viết dài lê thê mà phải copy công thức trên 365 thì không cần thiết, với khi các số trong chuỗi không liền nhau thì mỗi số chắc có một ý nghĩa nào đó, nên chăng phân biệt chúng ra, chứ nối dính lại có khi thành vô nghĩa, xin góp thêm công thức tách số (viết đúng theo ý của thớt để dùng trong Define Name):

=LAMBDA(a,BYROW(a,LAMBDA(x,SUBSTITUTE(TRIM(CONCAT(IFERROR(MID(x,SEQUENCE(LEN(x)),1)*1," ")))," ",", "))))(B3:B18)

Công thức tách chữ cũng tương tự. Nhưng có một công thức cùi bắp cách đây 3 năm mình dùng, chia sẻ để nhớ lại những ngày mới bắt đầu tìm hiểu về excel. Công thức tách chữ:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,
"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")