Giáo trình ứng dụng excel kế toán 1

ỦY BAN NHÂN DÂN TP. HỒ CHÍ MINH

TRƢỜNG CAO ĐẲNG CÔNG NGHỆ THỦ ĐỨC

KHOA TÀI CHÍNH KẾ TOÁN

GIÁO TRÌNH

HỌC PHẦN: ỨNG DỤNG EXCEL KẾ TOÁN 1

NGÀNH/NGHỀ: KẾ TOÁN

TRÌNH ĐỘ: CAO ĐẲNG

Ban hành kèm theo Quyết định số:... ../QĐ-CNTĐ-CN ngày....tháng….năm...

của………………………………

TP. Hồ Chí Minh, năm 2020

i

TUYÊN BỐ BẢN QUYỀN

Giáo trình này thuộc loại giáo trình giảng dạy nên các nguồn thông tin có thể

đƣợc phép dùng nguyên bản hoặc trích dùng cho các mục đích về đào tạo và tham

khảo. Mọi mục đích khác mang tính lệch lạc hoặc sử dụng với mục đích kinh doanh

thiếu lành mạnh sẽ bị nghiêm cấm.

LỜI GIỚI THIỆU

Excel là một ứng dụng trong bộ office, đƣợc kế toán viên sử dụng thƣờng xuyên

trong công việc của mình, tại những cơ sở, doanh nghiệp nhỏ khi chƣa có phần mềm

thì ứng dụng Excel trở thành ứng dụng không thể thiếu trong công tác kế toán và các

công việc khác. Xuất phát từ vấn đề trên nhóm tác giả nhận thấy cần triển khai áp

dụng ứng dụng excel trong công tác kế toán từ thiết lập các mẫu biểu, sổ sách, báo

cáo kế toán đến việc truy xuất thông tin đáp ứng nhu cầu cung cấp thông tin kế toán

cho các bên liên quan.

Trong quá trình thực hiện giáo trình này, nhóm tác giả xin gửi lời cảm ơn lãnh

đạo nhà trƣờng đã tạo điều kiện thuận lợi để nhóm tác giả hoàn thành giáo trình. Xin

gửi lời cảm ơn lãnh đạo các doanh nghiệp và quý thầy cô đồng nghiệp đã có những

góp ý xây dựng, hỗ trợ nhóm tác giả trong suốt quá trình viết giáo trình.

Trong thời gian tới, nhóm tác giả rất mong tiếp tục nhận đƣợc các ý kiến đóng

góp để giáo trình tốt hơn.

Xin chân thành cảm ơn!

Tp. Hồ Chí Minh, ngày 15 tháng 06 năm 2020

Tham gia biên soạn

1. TRẨN HOÁ

2. LÊ VĂN THỪA

ii

MỤC LỤC

DANH MỤC CHỮ VIẾT TẮT ..................................................................................... vi

DANH MỤC BẢNG BIỂU......................................................................................... viii

BÀI 1: HỆ THỐNG CÁC HÀM EXCEL....................................................................... 1

1.1 Các ký hiệu và phép tính .....................................................................................1

1.2 Các hàm cơ bản....................................................................................................6

1.3 Hàm xử lý chuỗi...................................................................................................9

1.4 Hàm xử lý số......................................................................................................14

1.5 Hàm xử lý thời gian ...........................................................................................16

1.6 Hàm điều kiện....................................................................................................20

1.7 Hàm dò tìm ........................................................................................................23

1.8 Hàm cơ sở dữ liệu:.............................................................................................28

BÀI 2: LẬP BẢNG LƢƠNG VÀ HẠCH TOÁN LƢƠNG......................................... 34

2.1 Lập hồ sơ nhân viên...........................................................................................41

2.1.1 Nội dung và công dụng...............................................................................41

2.1.2 Thao tác lập.................................................................................................41

2.2 Lập bảng chấm công..........................................................................................42

2.2.1 Bảng chấm công trong giờ hành chính .......................................................42

2.2.2 Bảng chấm công tăng ca .............................................................................43

iii

2.3 Lập bảng lƣơng ..................................................................................................44

2.3.1 Nội dung và công dụng...............................................................................44

2.3.2 Thao tác lập.................................................................................................46

2.4 Lập bảng tổng hợp tiền lƣơng và bảo hiểm. ......................................................51

2.4.1 Nội dung và công dụng...............................................................................51

2.4.2 Thao tác lập.................................................................................................52

2.5 Lập phiếu lƣơng.................................................................................................53

2.5.1 Nội dung và công dụng...............................................................................53

2.5.2 Thao tác lập.................................................................................................54

2.6 Hạch toán tiền lƣơng..........................................................................................57

BÀI 3: THEO DÕI NHẬP XUẤT TỒN VẬT TƢ- SẢN PHẨM - HÀNG HÓA...... 65

3.1 Lập danh mục hàng tồn kho (DMHTK) ............................................................68

3.1.1 Mẫu .................................................................................................................68

3.1.2 Thao tác lập.................................................................................................69

3.2 Lập danh mục khách hàng và bộ phận (DMKH&BP).......................................69

3.2.1 Mẫu .................................................................................................................69

3.2.2 Thao tác lập.....................................................................................................69

3.3 Lập bảng kê nhập xuất (BKNX)........................................................................70

3.3.1 Nội dung và công dụng...............................................................................70

iv

3.3.2 Thao tác lập.................................................................................................70

3.4 Lập Bảng tổng hợp nhập nhập xuất tồn (BTHNXT).........................................76

3.4.1 Nội dung và công dụng...............................................................................76

3.4.2 Thao tác lập.................................................................................................77

3.5 Lập sổ chi tiết vật tƣ sản phẩm hàng hóa...........................................................79

3.5.1 Nội dung và công dụng...............................................................................79

3.5.2 Thao tác lập.................................................................................................79

3.6 Lập phiếu nhập – phiếu xuất..............................................................................83

3.6.1 Phiếu nhập kho ...........................................................................................83

3.6.2 Phiếu xuất kho ............................................................................................86

3.6.1.2 Thao tác lập..............................................................................................87

BÀI 4: THEO DÕI CÔNG NỢ PHẢI THU, PHẢI TRẢ ............................................ 92

4.1 Lập Danh mục khách hàng và bộ phận..............................................................93

4.2 Lập bảng kê mua bán và thanh toán ..................................................................97

4.2.1 Nội dung và công dụng...............................................................................97

4.2.2 Thao tác lập.................................................................................................98

4.3 Lập bảng tổng hợp công nợ phải thu, phải trả .................................................105

4.3.1 Nội dung và công dụng.............................................................................105

4.3.2 Thao tác lập...............................................................................................105

v

4.4 Lập sổ chi tiết thanh toán.................................................................................118

4.4.1 Nội dung và công dụng.............................................................................118

4.4.2 Thao tác lập...............................................................................................119

4.5 Lập biên bản đối chiếu công nợ.......................................................................139

4.5.1 Nội dung và công dụng.............................................................................139

4.5.2 Thao tác lập...............................................................................................140

TÀI LIỆU THAM KHẢO .......................................................................................... 146

vi

DANH MỤC CHỮ VIẾT TẮT

Chữ viết tắt Nghĩa của chữ viết tắt

BANGKE Bảng kê mua bán và thanh toán

BCCTC Bảng chấm công tăng ca

BCCTG Bảng chấm công trong giờ hành chính

BH Bán hàng

BHTN Bảo hiểm thất nghiệp

BHXH Bảo hiểm xã hội

BHYT Bảo hiểm y tế

BKNX Bảng kê nhập xuất

BTHTL&BH Bảng tổng hợp tiền lƣơng và bảo hiểm

CBCNV Cán bộ công nhân viên

CKTT Chiết khấu thanh toán

ĐGN Đơn giá nhập

ĐGX Đơn giá xuất

GTGT Giá trị gia tang

HSNV Hồ sơ nhân viên

KPCĐ Kinh phí công đoàn

MST Mã số thuế

NV Nhân viên

PN Phiếu nhập

PX Phiếu xuất

QLDN Quản lý doanh nghiệp

QLSX Quản lý sản xuất

SCTTT Sổ chi tiết thanh toán

SLN Số lƣợng nhập

SLX Số lƣợng xuất

TK Tài khoản

TKC Tài khoản có

vii

TKN Tài khoản nợ

TNCN Thu nhập cá nhân

TNTT Thu nhập tính thuế

TTN Thành tiền nhập

TTSX Trực tiếp sản xuất

TTX Thành tiền xuất

VNĐ Việt Nam Đồng

XLLĐ Xếp loại lao động

viii

DANH MỤC BẢNG BIỂU

Ký hiệu Tên bảng Trang

Bảng 1.1 Bảng kết quả luyện tập phép tính cơ bản 1 3

Bảng 1.2 Bảng kết quả luyện tập phép tính cơ bản 2 5

Bảng 1.3 Bảng kết quả luyện tập phép tính cơ bản 3 6

Bảng 1.4 Bảng kết quả luyện tập hàm cơ bản 9

Bảng 1.5 Bảng kết quả luyện tập hàm xử lý chuỗi 13

Bảng 1.6 Bảng kết quả luyện tập hàm xử lý số 16

Bảng 1.7 Bảng kết quả luyện tập hàm xử lý thời gian 1 18

Bảng 1.8 Bảng kết quả luyện tập hàm xử lý thời gian 2 20

Bảng 1.9 Bảng kết quả luyện tập hàm điều kiện 23

Bảng 1.10 Bảng kết quả luyện tập hàm dò tìm 1 25

Bảng 1.11 Bảng kết quả luyện tập hàm dò tìm 2 26

Bảng 1.12 Bảng kết quả luyện tập hàm dò tìm 3 28

Bảng 1.13 Bảng kết quả luyện tập hàm cơ sở dữ liệu 1 30

Bảng 1.14 Bảng kết quả luyện tập hàm cơ sở dữ liệu 2 33

Bảng 2.1 Thang bậc lƣơng 35

Bảng 2.2 Thông tin về cán bộ công nhân viên 36

Bảng 2.3 Bảng theo dõi tạm ứng lƣơng 38

Bảng 2.4 Bảng xếp loại lao động 38

Bảng 2.5 Bảng xác nhận ngày công trong giờ hành chính 39

Bảng 2.6 Bảng xác nhận ngày công tăng ca 40

Bảng 2.7 Hồ sơ nhân viên 42

Bảng 2.8 Kết quả chấm công trong giờ 43

Bảng 2.9 Kết quả bảng chấm công tăng ca 44

Bảng 2.10 Mẫu bảng lƣơng 45

Bảng 2.11 Bảng lƣơng 49

Bảng 2.12 Mẫu Bảng tổng hợp tiền lƣơng và bảo hiểm 51

Bảng 2.13 Bảng tổng hợp tiền lƣơng và bảo hiểm 53

ix

Ký hiệu Tên bảng Trang

Bảng 2.14 Mẫu phiếu lƣơng 54

Bảng 2.15 Phiếu lƣơng 56

Bảng 3.1 Bảng hàng tồn kho đầu kỳ 66

Bảng 3.2 Mẫu Danh mục hàng tồn kho 68

Bảng 3.3 Bảng danh mục hàng tồn kho 69

Bảng 3.4 Mẫu Bảng danh mục khách hàng và bộ phận 69

Bảng 3.5 Bảng danh mục khách hàng và bộ phận 70

Bảng 3.6 Mẫu bảng kê nhập xuất 70

Bảng 3.7 Bảng kê nhập xuất 75

Bảng 3.8 Mẫu Bảng tổng hợp nhập xuất tồn 77

Bảng 3.9 Bảng tổng hợp nhập xuất tồn 78

Bảng 3.10 Mẫu sổ chi tiết vật tƣ sản phẩm hàng hóa 79

Bảng 3.11 Sổ chi tiết vật tƣ sản phẩm hàng hóa 82

Bảng 3.12 Mẫu phiếu nhập kho 84

Bảng 3.13 Phiếu nhập kho 85

Bảng 3.14 Mẫu phiếu xuất kho 86

Bảng 3.15 Phiếu xuất kho 88

Bảng 4.1 Kết quả danh mục khách hàng và bộ phận 96

Bảng 4.2 Kết quả bảng kê mua bán và thanh toán 104

Bảng 4.3 Kết quả bảng tổng hợp công nợ phải thu khách hàng VNĐ 110

Bảng 4.4 Kết quả bảng tổng hợp công nợ phải thu khách hàng USD 114

Bảng 4.5 Kết quả bảng tổng hợp công nợ phải trả nhà cung cấp VNĐ 117

Bảng 4.6 Kết quả sổ chi tiết thanh toán với ngƣời mua VNĐ 128

Bảng 4.7 Kết quả sổ chi tiết thanh toán với ngƣời mua USD 135

Bảng 4.8 Kết quả sổ chi tiết thanh toán với ngƣời bán VNĐ 139

Bảng 4.9 Kết quả bảng đối chiếu công nợ 142

x

GIÁO TRÌNH

Tên học phần: ỨNG DỤNG EXCEL KẾ TOÁN 1

Mã học phần: CNC110123

Vị trí, tính chất, ý nghĩa và vai trò của học phần:

- Vị trí: Học phần ứng dụng excel kế toán 1 đƣợc bố trí vào học kỳ 3 của khóa học

- Tính chất: Đây là học phần bắt buộc

- Ý nghĩa và vai trò của môn học/mô đun:

Học phần gi p ngƣời học thiết lập đƣợc các mẫu biểu Bảng chấm công, bảng

lƣơng, phiếu lƣơng, danh mục hàng tồn kho, danh mục khách hàng và bộ phận, bảng

kê nhập xuất, bảng kê mua bán và thanh toán , sổ kế toán chi tiết sổ chi tiết vật tƣ

sản phẩm hàng hóa, sổ chi tiết thanh toán và báo cáo bảng tổng hợp tiền lƣơng và

bảo hiểm, bảng tổng hợp nhập xuất tồn, bảng tổng hợp công nợ) trên Excel.

Xây dựng các bảng biểu nhập dữ liệu, từ đó vận dụng các hàm excel truy xuất ra

các sổ kế toán chi tiết và báo cáo kế toán quản trị theo yêu cầu của nhà quản lý và cơ

quan chủ quản, học phần gi p ngƣời học nhận thức đƣợc tầm quan trọng của việc ứng

dụng tin học trong công tác kế toán, r n luyện tính cẩn thận, sáng tạo và tinh thần làm

việc nhóm.

Mục tiêu của học phần:

*Kiến thức:

- Liệt kê đƣợc hàm Excel sử dụng trong từng mẫu biểu

- Phân loại đƣợc hàm excel

- Trình bày đƣợc kết cấu công thức của từng hàm excel

- Mô tả đƣợc nội dung các mẫu biểu kế toán

- Trình bày đƣợc phƣơng pháp truy xuất từ dữ liệu ra chứng từ, sổ sách, báo cáo

kế toán

* Kỹ năng:

- Lập đƣợc các mẫu sổ và bảng biểu kế toán

- Vận dụng hàm excel vào từng trƣờng hợp cụ thể

xi

- Thiết lập đƣợc công thức và truy xuất số liệu từ dữ liệu ra chứng từ, sổ sách và

báo cáo kế toán chi tiết

* Thái độ

- Nhận thức đƣợc tầm quan trọng của việc ứng dụng tin học trong công tác kế

toán

- R n luyện tính cẩn thận, sáng tạo.

- Tinh thần làm việc nhóm.

Nội dung của học phần:

Học phần gồm 4 bài học:

- Bài 1: Hệ thống hàm Excel

- Bài 2: Lập bảng lƣơng – Hạch toán lƣơng

- Bài 3: Theo dõi nhập xuất tồn Vật tƣ – Hàng hóa

- Bài 4: Theo dõi công nợ Phải thu – Phải trả

1

BÀI 1: HỆ THỐNG CÁC HÀM EXCEL

Giới thiệu

Bài hệ thống các hàm excel trình bày các hàm excel từ cơ bản đến nâng cao,

giúp ngƣời học thiết lập công thức các hàm excel, ứng dụng hàm excel vào từng

trƣờng hợp cụ thể phục vụ trong công tác kế toán nói riêng và công việc nói chung.

Mục tiêu

Sau khi học xong phần này, ngƣời học có thể:

* Kiến thức

 Liệt kê đƣợc các hàm excel

 Trình bày đƣợc công thức các hàm excel

* Kỹ năng

 Xác định đƣợc hàm excel đƣợc sử dụng trong từng trƣờng hợp cụ thể

 Thiết lập đƣợc công thức hàm excel

* Thái độ:

 R n luyện tính cẩn thận và sáng tạo

 R n luyện tác phong công nghiệp

 Tinh thần làm việc nhóm

Nội dung chính

1.1 Các ký hiệu và phép tính

+ Ký hiệu dùng để thực hiện phép tính Cộng + trong công thức Excel

- Ký hiệu dùng để thực hiện phép tính Trừ - trong công thức Excel

* Ký hiệu dùng để thực hiện phép tính Nhân (x trong công thức Excel

/ Ký hiệu dùng để thực hiện phép tính Chia (/ trong công thức Excel

^ Ký hiệu dùng để thực hiện phép tính Lũy thừa trong công thức Excel

Luyện tập 1.1.1: số liệu trên Excel đƣợc cho nhƣ sau:

2

Yêu cầu: thực hiện lập công thức các cột đƣợc tô màu trong bảng số liệu.

Hƣớng dẫn 1.1.1:

- Thiết lập công thức ô đầu tiên trên mỗi cột cần lập công thức nhƣ sau:

- Thực hiện copy công thức từ ô đầu tiên cho cả cột:

Tƣơng tự cho các cột còn lại

- Hoặc ch ng ta có thể quét chọn tất cả các ô đầu tiên của các cột cần lập công

thức, sau đó, thực hiện copy cho cả cột

+

+

3

Bảng 1.1. Bảng kết quả luyện tập phép tính cơ bản 1

“ ” Ký hiệu Nháy đôi. Khi trong công thức Excel có sử dụng Text không phải là số

thì ch ng ta sử dụng ký hiệu Nháy đôi này đặt ở trƣớc và sau Text đó thì chƣơng trình

Excel mới thực hiện đƣợc công thức mà ch ng ta đang thiết lập.

> Ký hiệu so sánh Lớn hơn

>= Ký hiệu so sánh Lớn hơn hoặc bằng

< Ký hiệu so sánh Nhỏ hơn

<= Ký hiệu so sánh Nhỏ hơn hoặc bằng

<> Ký hiệu so sánh Khác

= Ký hiệu so sánh Bằng. Ký hiệu này cũng đƣợc sử dụng trong các hàm Excel có

sử dụng yếu tố so sánh. Ngoài ra, ký hiệu này còn là ký hiệu đầu tiên khi thiết lập công

thức Excel

() Ký hiệu mở ngoặc đóng ngoặc. Kết cấu chung khi thiết lập 1 hàm Excel là =TÊN

HÀM CÁC YẾU TỐ TRONG HÀM . Dấu đƣợc sử dụng ngay sau Tên hàm Excel

và dấu đƣợc dùng để kết th c hàm Excel.

& Ký hiệu Nối chuỗi, dùng để kết nối: Số với Số, Số với Text, Text với Text, 1 ô

excel với Số, 1 ô excel với Text, Số với Text với 1 ô…  Tạo ra 1 kết quả là sự kết

hợp nhiều thành phần.

, Ký hiệu phẩy

; Ký hiệu chấm phẩy

+

+

Các ký hiệu này đƣợc sử dụng trong

các hàm Excel có sử dụng yếu tố so

sánh nhƣ: Hàm lập luận điều kiện If,

hàm Tính tổng có điều kiện Sumif

hoặc Sumifs, hàm đếm có điều kiện

Countif hoặc Countifs

Các ký hiệu này đƣợc sử dụng để ngăn cách, phân biệt

các yếu tố / thành phần trong 1 hàm Excel. Ví dụ:

IF Điều kiện, Kết quả khi điều kiện xảy ra, Kết quả

ngƣợc lại khi điều kiện không xảy ra hoặc IF Điều

kiện; Kết quả khi điều kiện xảy ra; Kết quả ngƣợc lại

khi điều kiện không xảy ra . Ch ng ta có thể tùy chỉnh

dấu , hay ; này trong Control Panel của máy vi tinh.