📨Journal Entry with Excel
Một công việc kế toán viên thường phải làm thủ công là lấy số liệu từ một hệ thống khác và nhập vào sổ sách kế toán. Ví dụ, công ty bạn có một hệ thống bán hàng online, mỗi ngày bạn phải trích xuất số liệu bán hàng ra file Excel và hạch toán thủ công từng giao dịch.
Một số phần mềm kế toán cho phép import trực tiếp số liệu từ file Excel vào sổ cái. Tuy nhiên, do đặc thù khác nhau của mỗi công ty, định dạng số liệu trong file Excel rất khác nhau từ tên cho đến thứ tự cột dữ liệu. Hay tệ hơn, bạn chỉ có các số liệu trung gian, cần phải được xử lý trước khi import.
KNOWINS có thể giúp giải quyết tất cả các rắc rối này, giúp bạn tiết kiệm thời gian và tâm trí xử lý các công việc khác quan trọng hơn.
Let's do it.
Export Data
Excel Parameter
Collect Data
Journal Entry Import Control
KNOWINS allows users to place controls on a presentation sheet. Such controls can be used to collect data showing on screen to a journal entry import, drill-down into further data details, etc…
You create a user control in 2 simple steps:
Select a cell to be a user control and insert a cell comment
Apply a user control function into the comment
Right click on cell I5 of the presentation sheet and select Edit Comment
Then add the following comment:
User Control Function: Import
Refer to the Report Design Reference chapter for a full description.
Syntax.
Import(form, data_tag, field1, [field2]...)
Parameters
form: A form that receives data, such as JNE for Journal Entry
data_tag: Placeholder cell for data. The data should have been already available in a certain cell. Data tag links that data to an input of this function by matching the cell’s comment with data_tag value. For example, if data is in cell A1, and cell A1 has MyData as its comment, then the data_tag should be MyData.
Fields…: The list of field names. Data has to be a list of values separated with each other by the character | (straight slash). Each of them are mapped to the field names in order. When it reaches the last field in the list, it would roll over to the first field,
Description
This functions calls a form and feed it with the collected data:
Example
Import(JNE, data, period, transactiondate, accountcode, amount)
This user function calls JNE form, which is Journal Entry, and collects data from a cell having data as a value of its comment. This data would be transformed into a data table with 4 column period, transactiondate, accountcode, amount.
Let’s use cell A1 as a placeholder cell for data. Right click on cell A1 and add this comment:
To collect data on screen, we are going to use the excel function TEXTJOIN. Enter this formula =TEXTJOIN("|",FALSE,B10:J15) to the cell A1. Note that, the data range is from B10 to J15, which would be automatically expanded to the full data range when running a report.
Excel Function: TEXTJOIN
Please refer to TEXTJOIN function - Office Support for detailed instructions
Syntax.
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
Parameters
delimiter: A text string, either empty, or one or more characters enclosed by double quotes, or a reference to a valid text string. If a number is supplied, it will be treated as text. Note: Always use this character | (straight slash) for our reports.
ignore_empty: If TRUE, ignores empty cells.
text1: Text item to be joined. A text string, or array of strings, such as a range of cells,
[text2, ...]: Additional text items to be joined.,
Description
This function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
We are going to test this report. Click on the button the journal entry form should pops up showing the first of 16 closing journal lines. Click on the grid view icon to see all journal lines.
Run and Test
Congratulations! You have made really good progress so far.
Last updated