Kiểm tra số liệu

[Thien]

Hi! All

Có bài toán này nhờ các sư phụ giải đáp.
Mình muốn khi copy cột tài khoản từ 1 file khác vào cột tài khoản của sheet hiện tại thì lập tức sẽ có macro làm việc như sau:
- Kiểm tra xem số tài khoản mới chép vào có đúng với số tài khoản trong data định trước hay không.
- Hiện ra 1 from các tài khoản không đúng và chỉ định số tài khoản đúng để mình nhập vào.
- Sau khi nhập vào các số tài khoản đúng thì thoát. (Lúc này các tài khoản sai sẽ được thay thế bằng các tài khoản đúng mà mình mới nhập trong from).

Có lẽ đây là bài toán khó & rất kỳ cục phải không các bạn. Nhưng mong các bạn giúp mình lời giải.

Hiện tại mình dùng Validation để kiểm tra tài khoản khi nhập vào, còn khi copy vào phải tìm các TK sai bằng thủ công.

Mong các bạn chỉ giáo.

Thân chào.

 

[nvson]

Bạn cho mình biết số tài khoản trong data định trước của bạn là gì?

 

[Đào Việt Cường]

Dear Thien,
-----------
Có nhất thiết phải dùng User Form để đưa vào số tài khoản đúng hay không? Giả định có 60.000 tài khoản không đúng => phải nhập lại 60.000 lần(?!). Điểm mạnh của MS Excel là công thức và cho phép người dùng làm việc với lưới (làm việc cùng lúc với nhiều mẩu tin) tại sao bạn không khai thác.
Giải bài tập:
- Để kiểm tra các tài khoản đưa vào có đúng không bạn phải có danh mục tài khoản để so sánh;
- Sử dụng chức năng Replace All để thay thế tất cả các lỗi sai
Theo mình thì công việc này mất ít công sức hơn là ngồi thiết kế một màn hình và viết lệnh - nhất là màn hình đó ít có tính sử dụng lại!

 

[Thien]

Dear Cường.

Bạn ơi:
- Khi copy sang thì bạn chưa biết tài khoản nào đúng, tài khoản nào sai?
- Danh mục tài khoản (Data) chắc chắn là có sẵn.
- Trong 1000 dòng chứa 1000 tài khoản, trong đó bạn chưa biết tài khoản nào đúng (sai) với Data thì làm sao Sử dụng chức năng Replace All.

Thân chào bạn

 

[Đào Việt Cường]

Dear Thien,
-----------
Bạn sử dụng hàm VLOOKUP(<Tài khoản tìm kiếm>,<Danh mục tài khoản>,<Chỉ số cột tài khoản>,0). Hàm trả về #N/A cho bạn biết <Tài khoản tìm kiếm> không tồn tại trong <Danh mục tài khoản>. Sử dụng chức năng Replace để thay thế hàng loạt các tài khoản sai bằng tài khoản đúng. Bạn cũng có thể sử dụng chức năng AutoFilter để lọc ra từng tài khoản sai rồi sửa lại hàng loạt bằng tổ hợp phím Ctrl + Enter.
Chúc bạn thành công!

 

[Thien]

Dear Thien,
-----------
Bạn sử dụng hàm VLOOKUP(<Tài khoản tìm kiếm>,<Danh mục tài khoản>,<Chỉ số cột tài khoản>,0). Hàm trả về #N/A cho bạn biết <Tài khoản tìm kiếm> không tồn tại trong <Danh mục tài khoản>. Sử dụng chức năng Replace để thay thế hàng loạt các tài khoản sai bằng tài khoản đúng. Bạn cũng có thể sử dụng chức năng AutoFilter để lọc ra từng tài khoản sai rồi sửa lại hàng loạt bằng tổ hợp phím Ctrl + Enter.
Chúc bạn thành công!

Dear Cường.

Bạn ơi cách này hiện mình cũng đang dùng đây.Nhưng có những vấn đề sau:
- Khi trả về #N/A mình dùng AutoFilter để lọc ra #N/A, lúc này mình phải sang sheet chứa data tài khoản để xem tài khoản đúng là gì rồi mới quay lại sữa dòng chứa TK sai lại để #N/A không còn. Rất tốn thời gian & không tiện lợi.
- File sẽ nặng thêm.
- Data tài khoản của mình rất nhiều (do mở nhiều TK chi tiết), nên tìm ra tài khoản dúng để điền vào rất tốn thời gian & bất tiện.

Nên mình đành nhờ đến VBA cho phép ngay sau khi mình copy TK vào là nó tự kiểm tra và hiện ra 1 form chứa các TK sai , kế bên sẽ là các TK đúng, nó sẽ hỏi mình có muốn thay thế TK sai không, mình chỉ cần OK là xong luôn.

Thân chào bạn.

 

[nvson]

Đsung như anh Cường nói...., ngoài ra bạn có thể dùng định dạng điều kiện Condition Formatting để những ô nào có giá trị sai thì sẽ bị tô một màu khác. Nhưng nếu bạn thích sử dụng VBA thì hãy thử Macro sau:

Option Explicit
Public Sub KTraSoChungTu()
On error goto thoat
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim i, j, giatri
Dim rngData As Range
Set rngData = Selection
For i = 1 To rngData.Rows.Count
    For j = 1 To rngData.Columns.Count
        giatri = rngData.Cells(i, j)
        If IsError(WorksheetFunction.VLookup(giatri, Range("abc"), 1, 0)) Then
            Do
                rngData.Cells(i, j).Select
                giatri = InputBox("Sai. Nhap lai ma chung tu:")
                If IsError(WorksheetFunction.VLookup(giatri, Range("abc"), 1, 0)) Then GoTo tieptuc
                rngData.Cells(i, j) = giatri
                Exit Do
tieptuc:
            Loop While True
        End If
    Next j
Next i
thoat:
Msgbox "Co loi"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Chú ý: Nhấn Insert/Names/Define, rồi đặt tên abc cho vùng Sheet1!$F$4:$F$368
Cách sử dụng: Chọn vùng rồi chạy macro trên, nhưng phải định dạng vùng đó là Text nhé.

 

[Đào Việt Cường]

Dear all,
--------
InputBox? Không ổn lắm, vì như Thien mô tả dữ liệu có quá nhiều, không thể làm thủ công bằng tay được. Như đã giả sử, có 60.000 lỗi, chúng ta phải nhập 60.000 lần - cực lắm thay.
Theo mình thì không thể lười trong trường hợp này nếu như giữa tài khoản đúng và tài khoản sai không có dấu hiệu gì để so sánh. Xét ví 2 ví dụ về sai mã tài khoản như sau:
Trường hợp 1: Do thói quen sử dụng tài khoản tổng hợp:
- Mã TK đúng là 1111 - Tiền mặt (Tiền Việt Nam)
- Mã TK sai là 111 - Tiền mặt
Trường hợp 2: Do thay đổi tài khoản hạch toán:
- Mã TK đúng: 1311- Phải thu của khách hàng ABC
- Mã TK sai: 131 - Phải thu khách hàng
Rõ ràng ở trường hợp 2 chúng ta không có đấu hiệu nào để thay thế 131 thành 1311. Buộc người dùng phải tự xác định tài khoản phải thu của khách hàng nào được thay thế vào định khoản tương ứng.
Liệu có macro nào thông minh đến mức để bạn chỉ cần "OK là xong luôn không" (?!)
Nếu có thể, bạn Thien có thể gửi file minh hoạ thì mọi người mới giúp đỡ bạn tốt hơn.

 

[nvson]

Dựa vào những thông tin mà anh Thiện cung cấp, tôi đoán anh muốn tạo một file cơ sở dữ liệu (gồm mã tài khoản...). Sau đó làm việc ở một file khác, muốn kiểm tra số liệu nhập vào có trong file Cơ sở dữ liệu hay không?
Anh thử file sau xem nhé!

 

[Thien]

Dựa vào những thông tin mà anh Thiện cung cấp, tôi đoán anh muốn tạo một file cơ sở dữ liệu (gồm mã tài khoản...). Sau đó làm việc ở một file khác, muốn kiểm tra số liệu nhập vào có trong file Cơ sở dữ liệu hay không?
Anh thử file sau xem nhé!

Xin lỗi các bạn máy mình sáng nay bị hư nên giờ mới vào net được.

Thân chào nvson.

File anh rất hay & đúng với ý của mình rồi. Nhưng còn 1 chỗ chưa hoàn chỉnh là trong from hiện ra không liệt kê danh sách tài khoản đúng để mình chọn, lúc này mình lại không nhớ số tài khoản đúng thì làm sao điền vào from để tiếp tục làm việc.

Hiện tại khi mình không nhớ tài khoản đúng để gõ vào from là nó cứ trơ ra, không cho thoát luôn.

Bạn giúp mình cải tiến 1 chút nha.

Thân chào bạn.

 

[nvson]

Vậy thì bạn thử file sau.

 

[Thien]

Vậy thì bạn thử file sau.

Quá hay, quá tuyệt vời.
Mình rất cảm ơn bạn. File thật hoàn chỉnh theo đúng ý mình.

Cảm ơn bạn đã giúp.

Thân chào bạn.


PS: Dear Đào Việt Cường.
Maccro rất thông minh nếu mình biết sai khiến nó làm việc theo đúng ý mình.
Cảm ơn bạn đã quan tâm góp ý cho mình.
Thân chào bạn.

 

[Đào Việt Cường]

Dear all,
--------
Giải pháp đáp ứng được yêu cầu là giải pháp tốt nhất. Mình không có ý định bàn luận xâu thêm khi mà yêu cầu của Thien đã được giải quyết mà chỉ có ý kiến nhỏ về giải pháp của anh nvson để các bạn vận dụng linh hoạt:
- Việc đưa ra danh sách mã tài khoản đúng trên Form và tìm kiếm tài khoản thay thế có thể chậm hơn việc tìm kiếm trên Workbook danh mục tài khoản. Và dù gì, bạn cũng phải xác định được tài khoản thay thế cho từng tài khoản bị sai tương ứng.
- Thủ tục chỉ kiểm tra được tài khoản hiện hành có trong danh mục hay không mà không kiểm tra được sai sót do tài khoản này định khoản bởi tài khoản khác đã có trong danh mục. Một số lỗi mà thủ tục sẽ không kiểm tra được. (Xem hai trường hợp trong file đính kèm.)
- Thủ tục không thể ngưng cho đến khi kiểm tra hết các tài khoản được chọn. Đây là một hạn chế rất lớn nếu các bạn không biết cơ chế lặp của chương trình và lựa chọn vùng dữ liệu có hàng nghìn dòng. Theo em anh nvson nên cải tiến lại chương trình để cho phép chương trình dừng lại bất cứ khi nào.
Trong file minh hoạ này, các bạn kiểm tra xem trong cột "Mã TK cần kiểm tra" (Cột A) có bao nhiêu trường hợp sai (thận trọng: nên chọn từng tài khoản, ít một) và đánh giá xem việc lựa chọn tài khoản đúng trên danh sách so với lựa chọn từ một workbook, cách nào nhanh hơn!
Số liệu trong file minh hoạ chỉ có tính tham khảo, trên thực tế có thể có nhiều lỗi rất phức tạp. Dù ở lỗi sai nào, theo mình cách giải pháp triệt để nhất là tìm ra quy luật của sự khác biệt rồi khớp mã tài khoản sai bởi tài khoản đúng ngay trên bảng nguồn sau đó mới copy sang.

 

[Thien]

Dear Đào Việt Cường.

Quả thật mình gặp khó khăn gần giống như bạn đã liệt kê khi sử dụng code của Anh nvson, nhưng dẫu sao cũng đã đáp ứng 90% nhu cầu của mình rồi nên mình không dám nhờ hoàn thiện hơn nữa. Nay bạn đã nói thì cho mình gợi ý thêm xem Anh nvson có giúp cải thiện tốt hơn được không.

Dear nvson
Bạn có thể cải tiến hơn:
- Cho phép bỏ những dòng trống khi không có số tài khoản trong vùng lựa chọn để kiểm tra.
- Thủ tục chưa so số tài khoản sai với số tài khoản đúng trong Data để chọn TK gần đúng, giúp người dùng không phải kéo thanh kiếm tài khoản.

Hy vọng bạn sẽ cải thiện được cho hoàn chỉnh.

Thân chào

 

[nvson]

Dear nvson
Bạn có thể cải tiến hơn:
- Cho phép bỏ những dòng trống khi không có số tài khoản trong vùng lựa chọn để kiểm tra.
- Thủ tục chưa so số tài khoản sai với số tài khoản đúng trong Data để chọn TK gần đúng, giúp người dùng không phải kéo thanh kiếm tài khoản

Thủ tục không thể ngưng cho đến khi kiểm tra hết các tài khoản được chọn. Đây là một hạn chế rất lớn nếu các bạn không biết cơ chế lặp của chương trình và lựa chọn vùng dữ liệu có hàng nghìn dòng. Theo em anh nvson nên cải tiến lại chương trình để cho phép chương trình dừng lại bất cứ khi nào.

File sau sẽ khắc phục được những nhược điểm trên.

 

[nvson]

Mình đang dùng excel 2003.
Lưu ý anh: vùng dữ liệu trong File đóng mà mình định copy là rất nặng, khoản 10M. trong khi vùng mình copy thì không nhiều

Hi,
Một vấn đề đặt ra là với file cơ sở dữ liệu lớn thì bạn dùng cách gì để nhớ vùng bạn cần copy mà không mở file (in ra ngoài ư --> không hay lắm,....).
Theo mình thì bạn hãy dùng phần mềm Excel Viewer 2003 để đọc các file *.xls, *.xlt... Phần mềm này cũng là của Microsoft, nó chỉ đọc dữ liệu thôi nên rất nhanh. Bạn có thể mở file dữ liệu bằng Excel Viewer rồi Copy và Paste sang file đang mở trong MS Excel.

 

[Thien]

File sau sẽ khắc phục được những nhược điểm trên.

Hi! bạn.

Ngàn lần thank bạn tốt.

Thân chào bạn.