2. Chuẩn hóa dữ liệu

Tiếp theo, chúng ta sẽ sử dụng các công thức Excel để chuẩn hóa dữ liệu được xuất ra, bao gồm:

• Mã Giao Dịch - transactionref

• Vẫn ở trên bảng LG, tiếp tục chuẩn hóa những trường dữ liệu sau:

• Kỳ kế toán - period: Kỳ kế toán được chuẩn hóa sẽ có định dạng YYYYMMM. Ví dụ, giao dịch vào ngày 16/03/2022 sẽ thuộc về kỳ kế toán 2022003.

• Để làm điều đó, trong cột tiếp sau cột cuối cùng, bắt đầu từ hàng số 4 xuống dưới, sử dụng công thức này =CONCATENATE(YEAR(B4),RIGHT(CONCATENATE("00",MONTH(B4)),3)) để tạo kỳ kế toán từ giá trị của trường ngày giao dịch trong cột B. Trong dữ liệu mẫu của chúng ta, nó bắt đầu từ ô L4. Vị trí chính xác của ô này có thể khác đi với dữ liệu của bạn. Kéo công thức này xuống dưới cho tới hàng cuối cùng của dữ liệu nhật ký chung.

• Loại nghiệp vụ - journaltype: Trong hầu hết các trường hợp, các loại nghiệp vụ có thể được trích xuất từ các mã giao dịch. Như trong dữ liệu mẫu của chúng tôi, nó là phần chữ trước số "không" đầu tiên của mã giao dịch. Ví dụ như như với mã giao dịch PC00074 thì loại nghiệp vụ sẽ là PC - Phiếu Chi. Lưu ý là quy luật này có thể khác đi với dữ liệu của bạn, do đó công thức bên dưới sẽ phải thay đổi cho phù hợp.

• Gợi ý khác: Một quy luật thường gặp khác là loại chứng từ là phần chữ cái đầu tiên trước các con số hoặc khoảng trắng trong mã giao dịch. Ví dụ như với mã giao dịch PC21048, thì loại giao dịch sẽ có được sau khi loại bỏ hết phần số trong mã giao dịch. Để làm điều đó bạn có thể dùng công thức thay thế các con số bằng số “0”, và loại nghiệp vụ sẽ là phần chữ trước số "không" đầu tiên. Ví dụ như sử dụng công thức này =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K4, " ", "0"),"1","0"),"2","0"),"3","0") để tạo số “0” và sau đó áp dụng công thức =LEFT bên trên để tạo loại nghiệp vụ.

• Để làm điều đó, trong cột tiếp sau cột cuối cùng, bắt đầu từ hàng số 4 xuống dưới, sử dụng công thức này =LEFT(K4,FIND("0",K4) - 1) để tạo loại nghiệp vụ từ từ giá trị của trường mã giao dịch đã được chuẩn hóa trong cột K. Trong dữ liệu mẫu của chúng ta, nó bắt đầu từ ô M4. Công thức và vị trí chính xác của ô này có thể khác đi với dữ liệu của bạn. Kéo công thức này xuống dưới cho tới hàng cuối cùng của dữ liệu nhật ký chung.

• 

1. Ô đầu tiên sẽ chỉ định số thứ tự của nghiệp vụ bắt đầu. Thường nó sẽ là giá trị tiếp theo của số thứ tự cuối cùng của tất cả các nghiệp vụ hiện tại trong sổ cái của bạn. Nhắc lại: hãy chắc rằng nó lớn hơn số nghiệp vụ cuối cùng hiện có, nếu bạn không muốn nó ghi đè lên các nghiệp vụ có cùng số nghiệp vụ đã hạch toán, và thay đổi các hạch toán cũ.

Với lần nhập đầu tiên, số nghiệp vụ bắt đầu sẽ là 1. Nếu bạn đã hạch toán nhiều nghiệp vụ, hãy đến Kế Toán Sổ Cái > Tra Cứu > Nghiệp Vụ để tìm số thứ tự của nghiệp vụ lớn nhất.

Trong dữ liệu mẫu của chúng ta, ô bắt đầu chính là ô N4. Hãy nhập số “1” vào ô này.

1. Bắt đầu từ ô N5, vị trí chính xác của ô này có thể khác đi với dữ liệu của bạn, sử dụng công thức này =IF(K5=K4,N4,N4+1) để gán số nghiệp vụ cho từng dòng nhật ký. Kéo công thức này xuống dưới cho tới hàng cuối cùng của dữ liệu nhật ký chung.

• Nợ có - d_c: Quy luật rất trực quan. Tùy theo giá trị trong cột Phát Sinh Nợ hay Phát Sinh Có lớn hơn “0” để xác định.

• Để làm điều đó, trong cột tiếp sau cột cuối cùng, bắt đầu từ hàng số 4 xuống dưới, sử dụng công thức này =IF(I4>0, "D", "C") để tạo hạch toán nợ hay có cho từng dòng nghiệp vụ. Trong dữ liệu mẫu của chúng ta, nó bắt đầu từ ô O4. Công thức và vị trí chính xác của ô này có thể khác đi với dữ liệu của bạn. Kéo công thức này xuống dưới cho tới hàng cuối cùng của dữ liệu nhật ký chung.

• Số tiền - amount: Trong hệ thống KNOWINS, số tiền ghi nợ sẽ được biểu hiện bằng một số dương, và số tiền ghi có là một số âm. Chúng ta có thể dùng công thức tính hiệu số của phát sinh nợ và phát sinh có, lấy giá trị tuyệt đối của nó và gán dấu dương hoặc âm dựa vào giá trị trong cột nợ có trước đó.

• Để làm điều đó, trong cột tiếp sau cột cuối cùng, bắt đầu từ hàng số 4 xuống dưới, sử dụng công thức này =ABS(I4-J4) * IF(O4 = "D", 1, -1) để tạo hạch toán nợ hay có cho từng dòng nghiệp vụ. Trong dữ liệu mẫu của chúng ta, nó bắt đầu từ ô P4. Công thức và vị trí chính xác của ô này có thể khác đi với dữ liệu của bạn. Kéo công thức này xuống dưới cho tới hàng cuối cùng của dữ liệu nhật ký chung.

• Mã đối tượng - addresscode: mã khách hàng hoặc mã nhà cung cấp. Starting from cell R4 downward, use this formula =CONCATENATE(O4,P4,Q4) to assign address code to journals

• Account: Pretty straight forward. Starting from cell U4 downward, use this formula =G4 to assign account codes to journals

• Rate: Currency rate for transaction amount. this has to be looked up from the Accounts Payable USD sheet using transaction reference, starting from cell V4 downward, use this formula =IFNA(VLOOKUP(K4,AP_USD!$F$1:$Y$1000,7,false),0) to lookup currency rates of transactions.

• Trans. Debit: this has to be looked up from the Accounts Payable USD sheet using transaction reference. Starting from cell W4 downward, use this formula =IFNA(VLOOKUP(K4,AP_USD!$F$1:$Y$1000,8,false),"") to look up the transaction debit amount.

• Trans. Credit: this has to be looked up from the Accounts Payable USD sheet using transaction reference. Starting from cell X4 downward, use this formula =IFNA(VLOOKUP(I4,AP_USD!$F$1:$Y$1000,8,false),"") to look up the supplier code.

• Trans. Amount: Combination of transaction credit and debit amounts. Starting from cell Y4 downward, use this formula =IF(W4>0,W4,X4)to assign transaction amounts

• Invoice Number: Pretty straight forward. Starting from cell Z4 downward, use this formula =E4 to the last transaction.

• Invoice Date: Just need to be converted to the correct format. Starting from cell AA4 downward, use this formula =IF(LG!D4 = "", "",CONCATENATE(YEAR(D4),"-",MONTH(D4),"-",DAY(D4))) to fix an invoice date to each transaction.

• Copy all the formulas downward to the last transaction in the LG sheet. That’s all for normalizing your data inputs

• For your convenience, here is the link to download an already normalized data: Sample Data - MISA