Tìm công thức excel ngắn gọn chính xác (1 người xem)

[vietcongwse]

Xin Chào Anh/Chị,

Trong file đính kèm có 2 sheet : sheet "Part Nb" bao gồm có các thông số mẫu để dưa theo so sánh.
Sheet "Kiểm tra " trong ô "thông số 1 OK" sẽ thực hiện - So sánh thông số trong chuỗi ký tự 1 và chuỗi ký tự 2 với ô "thông số 1" của sheet "part nb" trùng nhau sẽ lấy ra chuỗi Chuỗi thông số 1 OK, nếu khác nhau kết quả lỗi.
Công thức đang dùng hiện tại IF + Len + MId - xác định chiều dài + thông số cần lấy và so sánh.
Nhược điểm mỗi khi thêm chuỗi mới khác chiều dài lại phải sửa công thức và chuỗi cùng chiều dài khác thông số bên trong nó cho ra chưa chính xác.

mọi người có thể chỉ giúp em 1 công nào có thể giải quyết được vấn đề bên trên, nội dung có trong file đính kèm.

 

[Phan Thế Hiệp]

Sheet "Kiểm tra " trong ô "thông số 1 OK" sẽ thực hiện: So sánh thông số trong chuỗi ký tự 1 và chuỗi ký tự 2 với ô "thông số 1" của sheet "part nb":
- Nếu trùng nhau sẽ lấy ra chuỗi Chuỗi thông số 1 OK,
- Nếu khác nhau kết quả lỗi.
mọi người có thể chỉ giúp em 1 công nào có thể giải quyết được vấn đề bên trên, nội dung có trong file đính kèm.

Như yêu cầu trên, bạn có thể thực hiện theo cách khác mà không cần phải cắt ký tự chi cho khổ vậy:

1. Về "Thông số 1 OK": Bạn tạo công thức tìm giá trị "Chuỗi ký tự thứ 1" có hay không bên sheet "Part Nb":

   H4=IF($F4="","",IF(ISERROR(SEARCH(VLOOKUP($F4,'Part Nb'!$D$3:$I$100,COLUMNS($A:D),),$G4)),"Sai mã",VLOOKUP($F4,'Part Nb'!$D$3:$I$100,COLUMNS($A:D),)))

   Enter, fill xuống.
   • Nếu không có thì báo "Sai mã"
   • Nếu có thì lấy "Thông số 1" của sheet "Part Nb" xem có trong "Chuỗi ký tự thứ 2" hay không:
     • Nếu có, thì trả về "Thông số 1" đó.
     • Nếu không có thì báo "Sai mã".
2. Về "Thông số 2 OK": tương tự như trên nhưng lấy "Thông số 2".
3. Về "Thông số 3 Chuỗi 1": thì chỉ làm công thức lấy giá trị "Thông số 3" của sheet "Part Nb" đem qua.

   J4=IF(F4="","",IFERROR(VLOOKUP($F4,'Part Nb'!$D$3:$I$100,COLUMNS($A:F),),"Sai mã"))

   Enter, fill xuống.
4. Về "Thông số 3 Chuỗi 2": cắt theo vị trí của "Thông số 3 Chuỗi 1" có trong "Chuỗi ký tự thứ 1".

   K4=IFERROR(IF(J4="Sai mã","Sai mã",MID(G4,FIND(J4,F4),4)),"")

   Enter, fill xuống. Tuy nhiên, nó có giới hạn và sẽ trả kết quả không đúng khi:
   • "Thông số 3 Chuỗi 1" xuất hiện nhiều vị trí trong "Chuỗi ký tự thứ 1".
   • Chiều dài của hai "Chuỗi ký tự thứ 1" và "Chuỗi ký tự thứ 2" khác nhau.

Thân

 

[vietcongwse]

Như yêu cầu trên, bạn có thể thực hiện theo cách khác mà không cần phải cắt ký tự chi cho khổ vậy:

1. Về "Thông số 1 OK": Bạn tạo công thức tìm giá trị "Chuỗi ký tự thứ 1" có hay không bên sheet "Part Nb":

   H4=IF($F4="","",IF(ISERROR(SEARCH(VLOOKUP($F4,'Part Nb'!$D$3:$I$100,COLUMNS($A:D),),$G4)),"Sai mã",VLOOKUP($F4,'Part Nb'!$D$3:$I$100,COLUMNS($A:D),)))

   Enter, fill xuống.
   • Nếu không có thì báo "Sai mã"
   • Nếu có thì lấy "Thông số 1" của sheet "Part Nb" xem có trong "Chuỗi ký tự thứ 2" hay không:
     • Nếu có, thì trả về "Thông số 1" đó.
     • Nếu không có thì báo "Sai mã".
2. Về "Thông số 2 OK": tương tự như trên nhưng lấy "Thông số 2".
3. Về "Thông số 3 Chuỗi 1": thì chỉ làm công thức lấy giá trị "Thông số 3" của sheet "Part Nb" đem qua.

   J4=IF(F4="","",IFERROR(VLOOKUP($F4,'Part Nb'!$D$3:$I$100,COLUMNS($A:F),),"Sai mã"))

   Enter, fill xuống.
4. Về "Thông số 3 Chuỗi 2": cắt theo vị trí của "Thông số 3 Chuỗi 1" có trong "Chuỗi ký tự thứ 1".

   K4=IFERROR(IF(J4="Sai mã","Sai mã",MID(G4,FIND(J4,F4),4)),"")

   Enter, fill xuống. Tuy nhiên, nó có giới hạn và sẽ trả kết quả không đúng khi:
   • "Thông số 3 Chuỗi 1" xuất hiện nhiều vị trí trong "Chuỗi ký tự thứ 1".
   • Chiều dài của hai "Chuỗi ký tự thứ 1" và "Chuỗi ký tự thứ 2" khác nhau.

Thân

Cám ơn bác đã chỉ giáo Phan Thế Hiệp

1. Về "Thông số 1 OK" sử dụng theo cách của em bác em thấy OK, ngắn gọn và chính xác.
2. Về thông số 2,3 - do lúc đầu em không nói rõ vẫn đề này (thông số 2 nó là ngày tăng dần lên, tương tự thông số 3 sẽ tăng lên).
Bác có thể chỉ giúp em lấy được thông số 2 và 3 khi trong chuỗi thay đổi theo ngày.

 

[Phan Thế Hiệp]

2. Về thông số 2,3 - do lúc đầu em không nói rõ vẫn đề này (thông số 2 nó là ngày tăng dần lên, tương tự thông số 3 sẽ tăng lên).
Bác có thể chỉ giúp em lấy được thông số 2 và 3 khi trong chuỗi thay đổi theo ngày.

Tôi không hiểu các đoạn màu đỏ trong đoạn trích trên!?

Có gì khác, bạn nên đưa file và có dữ liệu giả lập; hoặc dùng mẫu của 1 số mã nào đó để giải thích chi tiết cho yêu cầu của bạn, lúc đó họa may mới biết bạn muốn làm cái gì.

Vậy hén.

Thân

 

[CHAOQUAY]

Xin Chào Anh/Chị,

Trong file đính kèm có 2 sheet : sheet "Part Nb" bao gồm có các thông số mẫu để dưa theo so sánh.
Sheet "Kiểm tra " trong ô "thông số 1 OK" sẽ thực hiện - So sánh thông số trong chuỗi ký tự 1 và chuỗi ký tự 2 với ô "thông số 1" của sheet "part nb" trùng nhau sẽ lấy ra chuỗi Chuỗi thông số 1 OK, nếu khác nhau kết quả lỗi.
Công thức đang dùng hiện tại IF + Len + MId - xác định chiều dài + thông số cần lấy và so sánh.
Nhược điểm mỗi khi thêm chuỗi mới khác chiều dài lại phải sửa công thức và chuỗi cùng chiều dài khác thông số bên trong nó cho ra chưa chính xác.

mọi người có thể chỉ giúp em 1 công nào có thể giải quyết được vấn đề bên trên, nội dung có trong file đính kèm.

Cột G, H sheet Kiểm tra

G4=INDEX('Part Nb'!G$1:G$36,MAX(IFERROR((FIND('Part Nb'!$G$3:$G$36,$E4)=FIND('Part Nb'!$G$3:$G$36,$F4))*ROW('Part Nb'!$G$3:$G$36),0)))

Kết thúc bằng ctrl+shift+enter

 

[Phan Thế Hiệp]

FIND('Part Nb'!$G$3:$G$36,$E4)=FIND('Part Nb'!$G$3:$G$36,$F4)

Chỉ xử lý cho những dòng có mã hiện hành gói gọn trong Vùng: 'Part Nb'!$G$3:$G$36, nếu có cập nhật thêm mã theo số thứ tự (đến số 80) thì buộc phải thay đổi cận dưới của Vùng liền liền

Thân

 

[vietcongwse]

Chỉ xử lý cho những dòng có mã hiện hành gói gọn trong Vùng: 'Part Nb'!$G$3:$G$36, nếu có cập nhật thêm mã theo số thứ tự (đến số 80) thì buộc phải thay đổi cận dưới của Vùng liền liền

Thân

Em gửi các bác file đính kèm sau khi em note lại, để các bác dễ hình dung.
Bổ sung thêm một số chuỗi nhưng trong chuỗi nó thay đổi ngày tháng chính là "thông số 2 OK" với công thức hiện tại nó sẽ báo sai mã.

 

[CHAOQUAY]

Em gửi các bác file đính kèm sau khi em note lại, để các bác dễ hình dung.
Bổ sung thêm một số chuỗi nhưng trong chuỗi nó thay đổi ngày tháng chính là "thông số 2 OK" với công thức hiện tại nó sẽ báo sai mã.

Công thức bài 5, bạn sửa $E4 thành $F4; $F4 thành $G4 rồi áp vào H4 trong file. Lưu ý là kết thúc bằng ctrl+shift+enter, sau đó copy xuống dưới & sang cột bên cạnh.

Lỗi này là do khi lập công thức, thấy cột A không chứa dữ liệu nên xóa cả cột --> chỉ số cột bị giảm
---
Nếu test thấy đạt, việc bổ sung thêm "Sai mã" sẽ tính sau

 

[Phan Thế Hiệp]

Em gửi các bác file đính kèm sau khi em note lại, để các bác dễ hình dung.
Bổ sung thêm một số chuỗi nhưng trong chuỗi nó thay đổi ngày tháng chính là "thông số 2 OK" với công thức hiện tại nó sẽ báo sai mã.

Chỉ đoán ý trên các công thức cũ của bạn, bạn xem đúng chưa nha:

H4=IF($F4="","",IFERROR(LOOKUP(2,1/(MMULT(ISNUMBER(FIND('Part Nb'!$G$3:$G$1000,$F4:$G4))+0,{1;1})=2)/('Part Nb'!$G$3:$G$1000<>""),'Part Nb'!$G$3:$G$1000),"Sai mã"))
I4=IF($H4="Sai mã",$H4,IFERROR(IF(SUMPRODUCT(MID(F4:G4,FIND(VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,6,),VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,2,)),6)*{1,-1}),"c",MID(F4,FIND(VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,6,),VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,2,)),6)),""))
J4=IF($H4="Sai mã",$H4,IFERROR(MID(F4,FIND(VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,7,),VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,2,)),4),""))
K4=IF($H4="Sai mã",$H4,IFERROR(MID(G4,FIND(VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,7,),VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,2,)),4),""))

Toàn bộ chỉ Enter.

Thân

 

[vietcongwse]

Chỉ đoán ý trên các công thức cũ của bạn, bạn xem đúng chưa nha:

H4=IF($F4="","",IFERROR(LOOKUP(2,1/(MMULT(ISNUMBER(FIND('Part Nb'!$G$3:$G$1000,$F4:$G4))+0,{1;1})=2)/('Part Nb'!$G$3:$G$1000<>""),'Part Nb'!$G$3:$G$1000),"Sai mã"))
I4=IF($H4="Sai mã",$H4,IFERROR(IF(SUMPRODUCT(MID(F4:G4,FIND(VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,6,),VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,2,)),6)*{1,-1}),"c",MID(F4,FIND(VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,6,),VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,2,)),6)),""))
J4=IF($H4="Sai mã",$H4,IFERROR(MID(F4,FIND(VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,7,),VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,2,)),4),""))
K4=IF($H4="Sai mã",$H4,IFERROR(MID(G4,FIND(VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,7,),VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,2,)),4),""))

Toàn bộ chỉ Enter.

Thân

Cám ơn 2 bác - Em sử dụng hàm vừa ngắn gọn và chính xác.

Có một số hàm em chưa từng sử dụng các bác có thể chỉ giáo hoặc có tài liệu nào hướng dẫn các hàm này không?

 

[Phan Thế Hiệp]

Có một số hàm em chưa từng sử dụng các bác có thể chỉ giáo hoặc có tài liệu nào hướng dẫn các hàm này không?

Xem hướng dẫn các hàm theo link:

1. Các hàm Excel (theo thứ tự bảng chữ cái):

   Các hàm Excel (theo thứ tự bảng chữ cái)

   Liệt kê tất cả các hàm Excel theo thứ tự bảng chữ cái.

   support.office.com
2. Các hàm Excel (theo thể loại): https://support.office.com/vi-vn/ar...2ea-9da1-fcfe585c79cc&ui=vi-VN&rs=vi-VN&ad=VN
3. SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện:
   

   SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện

   Mình xin phân tích cách dùng SumProduct và Công thức mảng. Hàm SumProduct: Cấu trúc SUMPRODUCT(array1,array2,array3, ...) Array - Mảng dữ liệu là một tập hợp dãy giá trị liên tiếp trong một khảng nào đó. VD A1:C1 hoặ A1:A10,... Phép tính này cho phép chúng ta tính tổng của tích...

   www.giaiphapexcel.com
4. Giải thích và Ví dụ hàm MMULT(): https://www.giaiphapexcel.com/diend...có-ô-trống-theo-điều-kiện.137095/#post-876448

Thân

 

[vietcongwse]

Cám ơn 2 bác - Em sử dụng hàm vừa ngắn gọn và chính xác.

Có một số hàm em chưa từng sử dụng các bác có thể chỉ giáo hoặc có tài liệu nào hướng dẫn các hàm này không?

Cám ơn Bác Phan Thế Hiệp tài liệu rất hữu ích


EM có 1 vấn đề nhờ Bác chỉ giáo giúp em công thức bên dưới sau khi em áp dụng vào có 1 vấn đề là đối với các chuỗi ngắn mình có 3 giá trị thôi nhưng kết quả đều cho ra 6 giá trị.

I4=IF($H4="Sai mã",$H4,IFERROR(IF(SUMPRODUCT(MID(F4:G4,FIND(VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,6,),VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,2,)),6)*{1,-1}),"Sai mã",MID(F4,FIND(VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,6,),VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,2,)),6)),""))

Chi tiết trong file đính kèm em có giải thích,

 

[Phan Thế Hiệp]

Cám ơn Bác Phan Thế Hiệp tài liệu rất hữu ích


EM có 1 vấn đề nhờ Bác chỉ giáo giúp em công thức bên dưới sau khi em áp dụng vào có 1 vấn đề là đối với các chuỗi ngắn mình có 3 giá trị thôi nhưng kết quả đều cho ra 6 giá trị.

I4=IF($H4="Sai mã",$H4,IFERROR(IF(SUMPRODUCT(MID(F4:G4,FIND(VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,6,),VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,2,)),6)*{1,-1}),"Sai mã",MID(F4,FIND(VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,6,),VLOOKUP($E4,'Part Nb'!$C$3:$I$1000,2,)),6)),""))

Chi tiết trong file đính kèm em có giải thích,

Xem file kèm đã chỉnh theo yêu cầu.

Thân