Chương 1: TẠO CƠ SỞ DỮ LIỆU KẾ TOÁN TRÊN EXCEL Mục tiêu của chương: - Thiết lập chế độ làm việc cho máy tính theo chế độ Tiếng Việt. - Phân tích nội dung, kết cấu và cách ứng dụng các hàm cơ bản trong Excel. - Ứng dụng các thủ thuật trong Excel để làm kế toán. - Phân tích những lợi ích của việc thực hiện kế toán trên Excel với phần mềm kế toán thương mại theo hình thức Nhật ký chung.
- Xây dựng cơ sở dữ liệu về kế toán doanh nghiệp.1 Excel cơ bản và nâng cao ứng dụng làm kế toán 1.1 Thiết lập định dạng trên máy tính (1) Thiết lập chế độ làm việc cho máy tính: Start/ Setings/ Control panel/Regional/ Additional setting - Decimat symbol/ chọn dấu phầy (,) - No of digitsatter decial/ chọn số 2 - Digits groupng Symbol/ chọn dấu chấm (. - Negative sign Symbol: Chèn gạch ngang - Negative number format: -1,1 - Display leadinh Zeros: 0,7 - List Separator: Dấu phẩy (,) - Apply. Tiếp tục kích Date/ cửa sổ/ Short date for / đặt dd/mm/yyyy. 1: Thiết lập chế độ làm việc cho máy tính (Nguồn: Microsoft office 365) Hình 1.
2: Định dạng ngày, tháng, năm theo chế độ Tiếng Việt (Nguồn: Microsoft office 365) 2 (2) Định dạng Ngày tháng trong bảng tính Excel Định dạng cột B, cột ngày tháng: Chuột phải/Format Cells/Number/Date Hình 1. 3: Định dạng ngày, tháng, năm trong bảng tính Excel (Nguồn: Nhóm tác giả xây dựng trên nền Microsoft office 365) (3) Định dạng Số trong bảng tính Excel Chọn vùng cần định dang Số trong bảng tính Excel: Chuột phải/Format Cells/Number/Ok Ví dụ: Vùng chứ dữ liệu về số lượng, đơn giá, thành tiền cần định dạng Number. Ta chọn như sau: Chọn vùng từ E3: G8/ Chuột phải/Format Cells/Number/Ok. 4: Định dạng Số trong bảng tính Excel (Nguồn: Nhóm tác giả xây dựng trên nền Microsoft office 365) 1.2 Các hàm cơ bản trong Excel ứng dụng làm kế toán (1) Hàm IF Cách tiếp cận 1: – Hàm trả về giá trị 1 nếu điều kiện đúng, Hàm trả về giá trị 2 nếu điều kiện sai.
Các tham số: + Logicaltest: Điều kiện dùng để trắc nghiệm (xác định điều kiện này là đúng hay sai). + Truevalue: Là kết quả trong hàm IF nếu logicaltest đúng + Falsevalue: Là kết quả trong hàm IF nếu logicaltest sai các hàm thường dùng trong excel kế toán. Cách tiếp cận 2: Hàm If là hàm điều kiện. Cú pháp: =if(Nếu;Thì;Không thì) 4 Ví dụ 1: Bảng 1.
1: Bảng kê bán hàng (Nguồn: Nhóm tác giả xây dựng trên nền Microsoft office 365) (Nguồn: Nhóm tác giả xây dựng) (2) Hàm SUMIF Cách tiếp cận 1: – Tính tổng trong các ô được chỉ định bởi những tiêu chuẨn đưa vào. – Cú pháp: SUMIF(Range, Criteria, Sum_range) – Các tham số: + Range: Là dãy mà bạn muốn xác định. + Criteria: các tiêu chuẨn mà muốn tính tổng. Tiêu chuẨn này có thể là số, biểu thức hoặc chuỗi.
5 + Sum_range: Là các ô thực sự cần tính tổng. Hàm này trả về giá trị tính tổng trong các ô trong vùng cần tính thoả mãn một điều kiện đưa vào. Cách tiếp cận 2: – Hàm SUMIF là hàm tính tổng trong một điều kiện – Cú pháp: =Sumif(Vùng chứa đối tượng tính tổng;Đối tượng tính tổng;Vùng tính tổng). 2: Bảng kê bán hàng (Nguồn: Nhóm tác giả xây dựng trên nền Microsoft office 365) Hãy tính tổng số Ti vi (có mã hàng: TV) bán được trong tháng 10/N (Nguồn: Nhóm tác giả xây dựng) 6 (3) Hàm VLOOKUP Cách tiếp cận 1: Hàm Vlookup là hàm trả về giá trị dò tìm theo cột đưa từ bảng tham chiếu lên bảng cơ sở dữ liệu theo đúng giá trị dò tìm.
X=0 là dò tìm một cách chính xác. X=1 là dò tìm một cách tương đối. – Cú pháp: Vlookup(lookup_value;table_array;col_index_num;[range_lookup]) – Các tham số: + Lookup Value: Giá trị cần đem ra so sánh để tìm kiếm. + Table array: Bảng chứa thông tin mà dữ liệu trong bảng là dữ liệu để so sánh.
Vùng dữ liệu này phải là tham chiếu tuyệt đối. + Nếu giá trị Range lookup là TRUE hoặc được bỏ qua, thì các giá trị trong cột dùng để so sánh phải được sắp xếp tăng dần. + Col idx num: số chỉ cột dữ liệu mà bạn muốn lấy trong phép so sánh. + Range lookup: Là một giá trị luận lý để chỉ định cho hàm VLOOKUP tìm giá trị chính xác hoặc tìm giá trị gần đúng.
+ Nếu Range lookup là TRUE hoặc bỏ qua, thì giá trị gần đúng được trả về. Chú ý: Nếu giá trị Lookup value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên trong bảng Table array, nó sẽ thông báo lỗi #N/A. Cách tiếp cận 2: Hàm Vlookup là hàm tìm kiếm theo cột. Hay =Vlookup(Đối tượng tìm kiếm;Vùng chứa đối tượng tìm kiếm có cột đầu tiên chứa đối tượng tìm kiếm;Thứ tự cột cần lấy;X) Ví dụ 3: Hãy tìm tên trong hàng hóa có mã là “TV) trong ví dụ 1.
Hàm LEFT Hàm LEFT trong Excel được sử dụng để trả về số lượng kí tự nhất định từ mở đầu trong một chuỗi. Cú pháp: =LEFT(text, [num_chars]) – Các tham số: + Text (bắt buộc): chuỗi văn bản gốc để từ đó phân xuất ra chuỗi kí tự con. Đóng vai trò như khung tham chiếu trong ô chứa văn bản. + Num_chars (không bắt buộc): số lượng kí tự muốn phân xuất, bắt đầu từ phía bên trái trong chuỗi.
- Nếu num_chars bị bỏ sót, tự động kết quả sẽ trả về 1 kí tự. - Nếu num_chars lớn hơn tổng độ dài văn bản, hàm LEFT sẽ trả về toàn bộ văn bản. 3: Ứng dụng của hàm left (Nguồn: Nhóm tác giả xây dựng trên nền Microsoft office 365) (5) Hàm AND: – Cú pháp: AND(Logical1, Logical2, ….) nghĩa là And(đối 1, đối 2,. 8 – Các đối số: Logical1, Logical2… là các biểu thức điều kiện.
– Hàm này là Phép VÀ, chỉ đúng khi tất cả các đối số có giá trị đúng. Các đối số là các hằng, biểu thức logic. Hàm trả về giá trị TRUE (1) nếu tất cả các đối số trong nó là đúng, trả về giá trị FALSE (0) nếu một hay nhiều đối số trong nó là sai. Lưu ý: – Các đối số phải là giá trị logic hoặc mảng hay tham chiếu có chứa giá trị logic.
– Nếu đối số tham chiếu là giá trị text hoặc Null (rỗng) thì những giá trị đó bị bỏ qua. – Nếu vùng tham chiếu không chứa giá trị logic thì hàm trả về lỗi #VALUE! (6) Hàm SUM – Cộng tất cả các số trong một vùng dữ liệu được chọn. – Cú pháp: SUM(Number1, Number2…) – Các tham số: Number1, Number2… là các số cần tính tổng. (7) Hàm OR – Cú pháp: OR(Logical1, Logical2…) nghĩa là Or(đối 1, đối 2,.
– Các đối số: Logical1, Logical2… là các biểu thức điều kiện. – Hàm này là Phép HOẶC, chỉ sai khi tất cả các đối số có giá trị sai. Hàm trả về giá trị TRUE (1) nếu bất cứ một đối số nào trong nó là đúng, trả về giá trị FALSE (0) nếu tất cả các đối số trong nó là sai. (8) Hàm MAX – Trả về số lớn nhất trong dãy được nhập.
– Cú pháp: MAX(Number1, Number2…) – Các tham số: Number1, Number2… là dãy mà bạn muốn tìm giá trị lớn nhất ở trong đó. (9) Hàm MID Hàm MID trong Excel được dùng để cắt chuỗi ký tự ở giữa xâu ký tự cho trước. Hiện nay, nó được sử dụng khá phổ biến trong quá trình tính toán và thống kê trên bảng tính Excel. – Cú pháp: =MID(text,m,n) 9 – Trong đó: + text: Chuỗi ký tự.
+ m: Vị trí bắt đầu cần cắt chuỗi ký tự. + n: Số ký tự cần cắt ra từ chuỗi ký tự. 4: ứng dụng của hàm Mid (Nguồn: Nhóm tác giả xây dựng trên nền Microsoft office 365) (10) Hàm Countif Hàm đếm có điều kiện - Cú pháp: =Countif(Vùng điều kiện có chứa đối tượng mẫu;Đối tượng mẫu) (11) Hàm OFFSET - Cú pháp: =OFFSET(reference, rows, cols, height, width) =OFFSET(Ô bắt đầu;Lên(xuống;Trái(phải);Dài;Rộng) Trong đó: + reference: là vùng tham chiếu làm cơ sở cho hàm (làm điểm xuất phát) để tạo vùng tham chiếu mới. + rows: là số dòng bên trên hoặc bên dưới reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference.
Ví dụ nếu rows là 3, sẽ có 3 dòng trả về và nằm bên dưới reference. Khi rows là số dương thì các dòng trả về nằm bên dưới reference, khi rows là số âm thì các dòng trả về nằm bên trên reference. + cols: là số cột bên trái hoặc bên phải reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference. 10 Ví dụ nếu cols là 4 sẽ có 4 cột trả về và nằm bên phải của reference.
Khi cols là số dương thì các cột trả về nằm bên phải reference, khi cols là số âm thì các cột trả về nằm bên trái reference. + height: là số dòng của vùng tham chiếu cần trả về. Height phải là số dương. + width: là số cột của vùng tham chiếu cần trả về.
Width phải là số dương. Chú ý: - Nếu rows và cols làm cho vùng tham chiếu vượt ra khỏi phạm vi của bảng tính thì hàm OFFSET() sẽ báo lỗi #REF!. - Hàm OFFSET() không di chuyển hay làm thay đổi bất kỳ phần nào được chọn vì hàm chỉ trả về một tham chiếu. Các bạn có thể sử dụng hàm OFFSET() kết hợp với các hàm cần đến một đối số tham chiếu.3 Một số thủ thuật trong Excel ứng dụng làm kế toán 1.1 Các phím tắt trong Excel Bảng 1.