Nhờ xử lý tính toán Số nằm trong chuỗi kí tự (1 người xem)

[acrox84]

Mình có bảng Số liệu để chốt tiền, đôi khi NV tính sai tiền thu về nên muốn nhờ anh chị GPE làm giúp công thức để khi nhìn vào mình biết chỗ nào sai để chỉnh sửa kịp thời. Mình đính kèm File & Ảnh minh họa mô tả yêu cầu, cảm ơn mọi người trước ạ!

*Những STT 1, 4, 6,.. ô màu vàng là cần kiểm tra, dấu hiệu nhận biết là có "dấu _" kết thúc tên Người Nhận.
Cột K: Cách tính là những Số cuối cùng ở tên NN (cột G - định dạng kí tự) thêm 3 số 0 + Tiền Cước (cột J). Nếu KHÁC với Tiền COD (cột E) thì báo "SAI"
VD: ô A3: ĐÚNG, 540.000 + 65.000 = 605.000
+ô A6: SAI, 550.000 + 60.000 = 610.000 (# E6 là 620.000)

p/s: Do bảng chiếm nhiều không gian rồi, nên nếu có thể đánh dấu chỗ sai bằng hàm Conditional Formatting thì quá ngon lành, còn không được thì dùng công thức Cột cuối, mình sẽ thu gọn cột công thức nhỏ lại. Chân thành cảm ơn!

 

[hoangtuaotrang_hp_vn]

Mình có bảng Số liệu để chốt tiền, đôi khi NV tính sai tiền thu về nên muốn nhờ anh chị GPE làm giúp công thức để khi nhìn vào mình biết chỗ nào sai để chỉnh sửa kịp thời. Mình đính kèm File & Ảnh minh họa mô tả yêu cầu, cảm ơn mọi người trước ạ!

*Những STT 1, 4, 6,.. ô màu vàng là cần kiểm tra, dấu hiệu nhận biết là có "dấu _" kết thúc tên Người Nhận.
Cột K: Cách tính là những Số cuối cùng ở tên NN (cột G - định dạng kí tự) thêm 3 số 0 + Tiền Cước (cột J). Nếu KHÁC với Tiền COD (cột E) thì báo "SAI"
VD: ô A3: ĐÚNG, 540.000 + 65.000 = 605.000
+ô A6: SAI, 550.000 + 60.000 = 610.000 (# E6 là 620.000)

p/s: Do bảng chiếm nhiều không gian rồi, nên nếu có thể đánh dấu chỗ sai bằng hàm Conditional Formatting thì quá ngon lành, còn không được thì dùng công thức Cột cuối, mình sẽ thu gọn cột công thức nhỏ lại. Chân thành cảm ơn!
View attachment 306310

Trong cột G bạn sử dụng công thức sau cho Conditional Formatting:

=IF(ISNUMBER(SEARCH("_",$G3)),(SUMPRODUCT(MID(0&$G3,LARGE(INDEX(ISNUMBER(--MID($G3,ROW($1:$100),1))*ROW($1:$100),0),ROW($1:$100))+1,1)*10^(ROW($1:$100)-1))*1000+J3)<>$E3,"")

 

[HUONGHCKT]

Mình có bảng Số liệu để chốt tiền, đôi khi NV tính sai tiền thu về nên muốn nhờ anh chị GPE làm giúp công thức để khi nhìn vào mình biết chỗ nào sai để chỉnh sửa kịp thời. Mình đính kèm File & Ảnh minh họa mô tả yêu cầu, cảm ơn mọi người trước ạ!

*Những STT 1, 4, 6,.. ô màu vàng là cần kiểm tra, dấu hiệu nhận biết là có "dấu _" kết thúc tên Người Nhận.
Cột K: Cách tính là những Số cuối cùng ở tên NN (cột G - định dạng kí tự) thêm 3 số 0 + Tiền Cước (cột J). Nếu KHÁC với Tiền COD (cột E) thì báo "SAI"
VD: ô A3: ĐÚNG, 540.000 + 65.000 = 605.000
+ô A6: SAI, 550.000 + 60.000 = 610.000 (# E6 là 620.000)

p/s: Do bảng chiếm nhiều không gian rồi, nên nếu có thể đánh dấu chỗ sai bằng hàm Conditional Formatting thì quá ngon lành, còn không được thì dùng công thức Cột cuối, mình sẽ thu gọn cột công thức nhỏ lại. Chân thành cảm ơn!
View attachment 306310

Thử tham khảo file sau. Trong file có sử dụng hàm tách số trong chuỗi của tác giả @NDU

 

[acrox84]

Trong cột G bạn sử dụng công thức sau cho Conditional Formatting:

=IF(ISNUMBER(SEARCH("_",$G3)),(SUMPRODUCT(MID(0&$G3,LARGE(INDEX(ISNUMBER(--MID($G3,ROW($1:$100),1))*ROW($1:$100),0),ROW($1:$100))+1,1)*10^(ROW($1:$100)-1))*1000+J3)<>$E3,"")

Mình thử vào file test thì thấy kết quả đúng, copy code vào file chính thì bị lệch kết quả.
File chính thì có khác biệt là bảng từ dòng 6, nên mình có edit G3 thành G6, J3 --> J6 và E3 --> E6

=IF(ISNUMBER(SEARCH("_",$G6)),(SUMPRODUCT(MID(0&$G6,LARGE(INDEX(ISNUMBER(--MID($G6,ROW($1:$100),1))*ROW($1:$100),0),ROW($1:$100))+1,1)*10^(ROW($1:$100)-1))*1000+J6)<>$E6,"")

Nhờ bạn dạy mình edit lại cho đúng, cảm ơn bạn hoangtuaotrang rất nhiều!

Bài đã được tự động gộp: 16/12/24

Thử tham khảo file sau. Trong file có sử dụng hàm tách số trong chuỗi của tác giả @NDU

hàm này chạy tốt rồi ạ, cảm ơn bạn rất nhiều!
Mình có nhờ bạn hoangtuaotrang dạy chỉnh lại code Conditional Formatting, do bẳng đang full thông tin trang A4 nên nếu ko đc mình tìm cách chèn thêm 1 cột phát hiện lỗi của bạn

 

[hoangtuaotrang_hp_vn]

Mình thử vào file test thì thấy kết quả đúng, copy code vào file chính thì bị lệch kết quả.
File chính thì có khác biệt là bảng từ dòng 6, nên mình có edit G3 thành G6, J3 --> J6 và E3 --> E6

Nhờ bạn dạy mình edit lại cho đúng, cảm ơn bạn hoangtuaotrang rất nhiều!

Bài đã được tự động gộp: 16/12/24

hàm này chạy tốt rồi ạ, cảm ơn bạn rất nhiều!
Mình có nhờ bạn hoangtuaotrang dạy chỉnh lại code Conditional Formatting, do bẳng đang full thông tin trang A4 nên nếu ko đc mình tìm cách chèn thêm 1 cột phát hiện lỗi của bạn

Có thể file gốc có vấn đề gì đó, mình không có file nên không biết tình hình bị vướng mắc chỗ nào cả

 

[hoangminhtien]

Mình có bảng Số liệu để chốt tiền, đôi khi NV tính sai tiền thu về nên muốn nhờ anh chị GPE làm giúp công thức để khi nhìn vào mình biết chỗ nào sai để chỉnh sửa kịp thời. Mình đính kèm File & Ảnh minh họa mô tả yêu cầu, cảm ơn mọi người trước ạ!

*Những STT 1, 4, 6,.. ô màu vàng là cần kiểm tra, dấu hiệu nhận biết là có "dấu _" kết thúc tên Người Nhận.
Cột K: Cách tính là những Số cuối cùng ở tên NN (cột G - định dạng kí tự) thêm 3 số 0 + Tiền Cước (cột J). Nếu KHÁC với Tiền COD (cột E) thì báo "SAI"
VD: ô A3: ĐÚNG, 540.000 + 65.000 = 605.000
+ô A6: SAI, 550.000 + 60.000 = 610.000 (# E6 là 620.000)

p/s: Do bảng chiếm nhiều không gian rồi, nên nếu có thể đánh dấu chỗ sai bằng hàm Conditional Formatting thì quá ngon lành, còn không được thì dùng công thức Cột cuối, mình sẽ thu gọn cột công thức nhỏ lại. Chân thành cảm ơn!
View attachment 306310

Vì dữ liệu của bạn có nhiều trường hợp, trước dấu "_" có thể là:
1. Dấu cách (như "Hải Nam 990 _")
2. Tên ("Duy_"): lúc này mình hiểu giá trị số = 0
3. Phần số (có lẽ cách gõ này là chuẩn nhất)
Nên công thức Conditional Formatting bạn dùng như sau:

=IF(RIGHT($G3)="_",(IFERROR(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE($G3,"_",""))," ",REPT(" ",100)),100))*1000,0)+$J3)<>$E3,0)

 

[CHAOQUAY]

Mình có bảng Số liệu để chốt tiền, đôi khi NV tính sai tiền thu về nên muốn nhờ anh chị GPE làm giúp công thức để khi nhìn vào mình biết chỗ nào sai để chỉnh sửa kịp thời. Mình đính kèm File & Ảnh minh họa mô tả yêu cầu, cảm ơn mọi người trước ạ!

*Những STT 1, 4, 6,.. ô màu vàng là cần kiểm tra, dấu hiệu nhận biết là có "dấu _" kết thúc tên Người Nhận.
Cột K: Cách tính là những Số cuối cùng ở tên NN (cột G - định dạng kí tự) thêm 3 số 0 + Tiền Cước (cột J). Nếu KHÁC với Tiền COD (cột E) thì báo "SAI"
VD: ô A3: ĐÚNG, 540.000 + 65.000 = 605.000
+ô A6: SAI, 550.000 + 60.000 = 610.000 (# E6 là 620.000)

p/s: Do bảng chiếm nhiều không gian rồi, nên nếu có thể đánh dấu chỗ sai bằng hàm Conditional Formatting thì quá ngon lành, còn không được thì dùng công thức Cột cuối, mình sẽ thu gọn cột công thức nhỏ lại. Chân thành cảm ơn!
View attachment 306310

Bạn thử công thức dưới đây.
Tại K3, copy xuống dưới. Kết quả: TRUE = "SAI"; FALSE = ""

=AND(RIGHT(G3,1)="_",ISERROR(FIND(" "&((E3-J3)/1000)&"_",G3)))

Bài đã được tự động gộp: 16/12/24

Mình có bảng Số liệu để chốt tiền, đôi khi NV tính sai tiền thu về nên muốn nhờ anh chị GPE làm giúp công thức để khi nhìn vào mình biết chỗ nào sai để chỉnh sửa kịp thời. Mình đính kèm File & Ảnh minh họa mô tả yêu cầu, cảm ơn mọi người trước ạ!

*Những STT 1, 4, 6,.. ô màu vàng là cần kiểm tra, dấu hiệu nhận biết là có "dấu _" kết thúc tên Người Nhận.
Cột K: Cách tính là những Số cuối cùng ở tên NN (cột G - định dạng kí tự) thêm 3 số 0 + Tiền Cước (cột J). Nếu KHÁC với Tiền COD (cột E) thì báo "SAI"
VD: ô A3: ĐÚNG, 540.000 + 65.000 = 605.000
+ô A6: SAI, 550.000 + 60.000 = 610.000 (# E6 là 620.000)

p/s: Do bảng chiếm nhiều không gian rồi, nên nếu có thể đánh dấu chỗ sai bằng hàm Conditional Formatting thì quá ngon lành, còn không được thì dùng công thức Cột cuối, mình sẽ thu gọn cột công thức nhỏ lại. Chân thành cảm ơn!
View attachment 306310

Conditional Formatting xem trong file đính kèm

 

[hoangtuaotrang_hp_vn]

Bạn thử công thức dưới đây.
Tại K3, copy xuống dưới. Kết quả: TRUE = "SAI"; FALSE = ""

=AND(RIGHT(G3,1)="_",ISERROR(FIND(" "&((E3-J3)/1000)&"_",G3)))

Bài đã được tự động gộp: 16/12/24

Conditional Formatting xem trong file đính kèm

Dòng 26 với dòng 30 chưa đúng anh ơi, 2 dòng đó đúng mà vẫn báo sai?

 

[hoangminhtien]

Bạn thử công thức dưới đây.
Tại K3, copy xuống dưới. Kết quả: TRUE = "SAI"; FALSE = ""

=AND(RIGHT(G3,1)="_",ISERROR(FIND(" "&((E3-J3)/1000)&"_",G3)))

Bài đã được tự động gộp: 16/12/24

Conditional Formatting xem trong file đính kèm

Vì dữ liệu gốc nhập không đúng chuẩn lắm nên công thức của bạn chưa tổng quát được.
Vì dụ:
- dòng số 9 (STT 7) nếu J9=98,000 thì đáng ra sẽ trả về đúng nhưng công thức trả về sai (do trước ký tự "_" không phải là số.
- dòng số 26 (STT 24) chưa kết quả đúng nhưng giá trị lại trả về sai (do có dấu cách trước ký tự "_")

 

[CHAOQUAY]

Vì dữ liệu gốc nhập không đúng chuẩn lắm nên công thức của bạn chưa tổng quát được.
Vì dụ:
- dòng số 9 (STT 7) nếu J9=98,000 thì đáng ra sẽ trả về đúng nhưng công thức trả về sai (do trước ký tự "_" không phải là số.
- dòng số 26 (STT 24) chưa kết quả đúng nhưng giá trị lại trả về sai (do có dấu cách trước ký tự "_")

- Dòng số 9: Bạn có thấy trong bài 1 có nói trường hợp nào như vậy?

- Dòng số 26: Đánh giá đúng sai của dòng này cũng còn tùy.
Nếu xét theo dữ liệu đầu vào thì dòng này có lẽ sẽ bị coi là nhập lỗi. Tất cả các dòng khác, nếu có "số" thì luôn liền kề phía sau là "_", riêng dòng này là không theo như vậy

Với kiểu dữ liệu như đầu bài, có lẽ chủ bài đăng cần thêm nhiều ràng buộc bổ sung thì mới có thể có công thức "tổng quát" được bạn

Mình có bảng Số liệu để chốt tiền, đôi khi NV tính sai tiền thu về nên muốn nhờ anh chị GPE làm giúp công thức để khi nhìn vào mình biết chỗ nào sai để chỉnh sửa kịp thời. Mình đính kèm File & Ảnh minh họa mô tả yêu cầu, cảm ơn mọi người trước ạ!

*Những STT 1, 4, 6,.. ô màu vàng là cần kiểm tra, dấu hiệu nhận biết là có "dấu _" kết thúc tên Người Nhận.
Cột K: Cách tính là những Số cuối cùng ở tên NN (cột G - định dạng kí tự) thêm 3 số 0 + Tiền Cước (cột J). Nếu KHÁC với Tiền COD (cột E) thì báo "SAI"
VD: ô A3: ĐÚNG, 540.000 + 65.000 = 605.000
+ô A6: SAI, 550.000 + 60.000 = 610.000 (# E6 là 620.000)

p/s: Do bảng chiếm nhiều không gian rồi, nên nếu có thể đánh dấu chỗ sai bằng hàm Conditional Formatting thì quá ngon lành, còn không được thì dùng công thức Cột cuối, mình sẽ thu gọn cột công thức nhỏ lại. Chân thành cảm ơn!

 

[hoangminhtien]

- Dòng số 9: Bạn có thấy trong bài 1 có nói trường hợp nào như vậy?

Ngay từ bài #6 tôi cũng đã nói rằng dữ liệu nhập đã không chuẩn, nhưng đề bài có nói dấu hiệu nhận biết là dấu "_", thì đối với dòng 9 này có dấu "_" nhưng không có số, do đó tôi mới nói rằng, tôi hiểu số ở đây bằng 0 (cái này đúng hay không thì tác giả mới quyết định được)

- Dòng số 26: Đánh giá đúng sai của dòng này cũng còn tùy.
Nếu xét theo dữ liệu đầu vào thì dòng này có lẽ sẽ bị coi là nhập lỗi. Tất cả các dòng khác, nếu có "số" thì luôn liền kề phía sau là "_", riêng dòng này là không theo như vậy

Tôi cũng cho rằng dữ liệu này có vấn đề, tuy nhiên nếu rất nhiều dữ liệu kiểu như thế này thì công thức cũng cần xử lý vấn đề này.

Với dữ liệu của tác giả, hiện tại có 2 vấn đề đó trên tôi cũng đã nêu ở #6 và đưa ra hướng giải quyết rồi.
Ngoài ra khi thêm dữ liệu nữa thì không biết còn vấn đề gì nữa không?
Còn để công thức đơn giản thì tác giả cần giải quyết triệt để dữ liệu gốc.

 

[HieuCD]

- Dòng số 9: Bạn có thấy trong bài 1 có nói trường hợp nào như vậy?

- Dòng số 26: Đánh giá đúng sai của dòng này cũng còn tùy.
Nếu xét theo dữ liệu đầu vào thì dòng này có lẽ sẽ bị coi là nhập lỗi. Tất cả các dòng khác, nếu có "số" thì luôn liền kề phía sau là "_", riêng dòng này là không theo như vậy

Với kiểu dữ liệu như đầu bài, có lẽ chủ bài đăng cần thêm nhiều ràng buộc bổ sung thì mới có thể có công thức "tổng quát" được bạn

Dùng mảng {"_"," _"} liệt kê các khả năng nhập lung tung như dòng 26.

 

[CHAOQUAY]

Ngay từ bài #6 tôi cũng đã nói rằng dữ liệu nhập đã không chuẩn, nhưng đề bài có nói dấu hiệu nhận biết là dấu "_", thì đối với dòng 9 này có dấu "_" nhưng không có số, do đó tôi mới nói rằng, tôi hiểu số ở đây bằng 0 (cái này đúng hay không thì tác giả mới quyết định được)

Tôi cũng cho rằng dữ liệu này có vấn đề, tuy nhiên nếu rất nhiều dữ liệu kiểu như thế này thì công thức cũng cần xử lý vấn đề này.

Với dữ liệu của tác giả, hiện tại có 2 vấn đề đó trên tôi cũng đã nêu ở #6 và đưa ra hướng giải quyết rồi.
Ngoài ra khi thêm dữ liệu nữa thì không biết còn vấn đề gì nữa không?
Còn để công thức đơn giản thì tác giả cần giải quyết triệt để dữ liệu gốc.

Lệ trân540_

Chắc là "Tôi" đã tính đến mẫu này?

 

[yeudoi]

Mình có bảng Số liệu để chốt tiền, đôi khi NV tính sai tiền thu về nên muốn nhờ anh chị GPE làm giúp công thức để khi nhìn vào mình biết chỗ nào sai để chỉnh sửa kịp thời. Mình đính kèm File & Ảnh minh họa mô tả yêu cầu, cảm ơn mọi người trước ạ!

*Những STT 1, 4, 6,.. ô màu vàng là cần kiểm tra, dấu hiệu nhận biết là có "dấu _" kết thúc tên Người Nhận.
Cột K: Cách tính là những Số cuối cùng ở tên NN (cột G - định dạng kí tự) thêm 3 số 0 + Tiền Cước (cột J). Nếu KHÁC với Tiền COD (cột E) thì báo "SAI"
VD: ô A3: ĐÚNG, 540.000 + 65.000 = 605.000
+ô A6: SAI, 550.000 + 60.000 = 610.000 (# E6 là 620.000)

p/s: Do bảng chiếm nhiều không gian rồi, nên nếu có thể đánh dấu chỗ sai bằng hàm Conditional Formatting thì quá ngon lành, còn không được thì dùng công thức Cột cuối, mình sẽ thu gọn cột công thức nhỏ lại. Chân thành cảm ơn!
View attachment 306310

Bạn có muốn làm trên Google Sheets không.

 

[DeTong]

Mì ăn liền được clean bằng query,;
Chủ thớt add thêm dữ liệu, sang vùng xanh lá, click fải, Refresh...
Tự kiểm tra xem có chính xác chưa...

 

[hoangminhtien]

Lệ trân540_

Chắc là "Tôi" đã tính đến mẫu này?

Tôi chỉ tính đến những dữ liệu của tác giả gửi, dữ liệu này không có nên tôi chưa tính.

 

[CHAOQUAY]

Tôi chỉ tính đến những dữ liệu của tác giả gửi, dữ liệu này không có nên tôi chưa tính.

Vì dữ liệu của bạn có nhiều trường hợp, trước dấu "_" có thể là:
1. Dấu cách (như "Hải Nam 990 _")
2. Tên ("Duy_"): lúc này mình hiểu giá trị số = 0
3. Phần số (có lẽ cách gõ này là chuẩn nhất)
Nên công thức Conditional Formatting bạn dùng như sau:

Mục 1, 2 của bạn trong bài 1 chủ thớt có nói gì đâu bạn.
Có lẽ là với 1 vài ngoại lệ, bạn đang làm theo quan điểm cá nhân thì đúng hơn là theo chỉ dẫn + số liệu của tác giả

 

[hoangminhtien]

Mục 1, 2 của bạn trong bài 1 chủ thớt có nói gì đâu bạn.
Có lẽ là với 1 vài ngoại lệ, bạn đang làm theo quan điểm cá nhân thì đúng hơn là theo chỉ dẫn + số liệu của tác giả

Mục 1, 2 chủ thớt không nói nhưng điều quan trọng là nó lại có trong dữ liệu và tôi xử lý theo những gì có trong dữ liệu đó (và tôi cũng đã nói rõ đó là theo tôi hiểu)

 

[acrox84]

Sorry các bạn, mấy ngày nay mình lo quét dọn kho nên không vào xem bài lại.
Đúng là có nhiều trường hợp do nhiều người nhập nên không đồng nhất được dữ liệu đầu vào như
(1) Chỉ có dấu _ cuối tên, như An_ thì được hiểu là số 0
(2) tên NN là chú Bảy thì nhập là chú 7_ thì TH này số 7 không xử lý theo công thức,...
Nhưng do khâu này mình làm quản lý khi xem báo cáo cuối ngày những điểm đánh dấu này TÔ rõ lên để mình chú ý xem có sai sót gì không
Cảm ơn các bạn đã giúp mình xử lý vấn đề ạ

 

[acrox84]

Vì dữ liệu của bạn có nhiều trường hợp, trước dấu "_" có thể là:
1. Dấu cách (như "Hải Nam 990 _")
2. Tên ("Duy_"): lúc này mình hiểu giá trị số = 0
3. Phần số (có lẽ cách gõ này là chuẩn nhất)
Nên công thức Conditional Formatting bạn dùng như sau:

mình đã thử các bài được anh em gửi trên đây, rất cảm ơn anh em đã cho code mình dùng. Do nhu cầu cá nhân mình sẽ áp dụng bài của bạn HUONGHCKT và hoangminhtien kết hợp để rà soát số liệu báo cáo.
Cảm ơn mọi người rất nhiều ạ