tài liệu kế toán


truy Lượt Xem:12039

Hướng dẫn cách sử dụng các hàm excel thường dùng trong kế toán để lập sổ sách báo cáo tài chính bao gồm các hàm sumif, vlookup, subtotal, sum, if, and, or, max, min

I/ Một số hàm excel quan trọng thường hay áp dụng trong kế toán:

1/ Hàm Sumif:  Hàm Sumif  là một hàm excel thông dụng thường dùng trong kế toán với mục đích tổng hợp số liệu từ Bảng nhập liệu lên bảng cân đối phát sinh, kết chuyển các bút toán cuối kỳ trên Excel...

Hàm Sumif trong excel kế toán có tác dụng gì?

- Kết chuyển các bút toán cuối kỳ

- Tổng hợp số liệu từ Bảng nhập liệu (BNL) lên Bảng cân đối phát sinh tháng/năm, lên bảng nhập xuất tồn kho, lên Bảng tổng hợp phải thu, phải trả cho khách hàng

- và các bảng tính có liên quan

Cú pháp của hàm Sumif:

cách sử dụng hàm sumif thường dùng trong excel kế toán

Giải thích:

 - Dãy ô điều kiện: Là dãy ô chưa điều kiện cần tính. Cụ thể trong bài: Là dãy ô chứa tài khoản trong cột TK Nợ/TK Có trên BNL, hoặc dãy ô chứa mã hàng trên Phiếu nhập kho, Xuất kho …vv

 - Điều kiện cần tính: Phải có “ Tên “ trong dãy ô điều kiện. Cụ thể trong bài : Là Tài khoản cần tính trên BNL hoặc mã hàng trên kho ( bảng Nhập Xuất Tồn ) hoặc TK cần tổng hợp trên bảng Cân đối phát sinh…. ( Điều kiện cần tính chỉ là một ô )

 - Dãy ô tính tổng: Là dãy ô chưa giá trị tương ứng liên quan đến điều kiện cần tính. Cụ thể trong bài: là cột phát sinh Nợ hoặc phát sinh Có trên BNL…. Chú ý: Điểm đầu và điểm cuối của dãy ô tính tổng và dãy ô điều kiện phải tương ứng nhau.

 - Hướng dẫn khi muốn tuyệt đối dòng hoặc cột ( việc tuyệt đối dòng hoặc cột là tuỳ vào từng trường hợp):

- Bấm F4 (1 lần): Để có gái trị tuyệt đối ( tuyệt đối được hiểu là cố định cột và cố định dòng – ($cột$dòng); Ví dụ: $E$12 – tức là cố định cột E và cố định dòng 12

 - Bấm F4 (2 lần): Để có giá trị tương đối cột và tuyệt đối dòng – được hiểu là cố định dòng thay đổi cột – (cột$dòng) ; Ví dụ: E$12 – tức là cố định Dòng 12, không cố định cột E

 - Bấm F4( 3lần): Để có giá trị tương đối dòng và tuyệt đối cột – được hiểu là cố định cột thay đổi dòng – ($cột dòng) ; Ví dụ: $E12 – tức là cố định Cột E, không cố định dòng 12.

VÍ DỤ VỀ HÀM SUMIF TRONG EXCEL KẾ TOÁN:  ( Đặt công thức tại địa chỉ ô cần tính)

cách sử dụng hàm sumif trong excel kế toán

Theo công thức trong bảng tính thì:

- Dãy ô điều kiện là E15 đến E180 ( tức là kéo từ nghiệp vụ phát sinh đầu tiên của tháng )

- Điều kiện cần tính là ô E112 ( tức là ô điều kiện cần tính )

- Dãy ô tính tổng là H15 đến H180

 Chú ý: Với các bút toán kết chuyển cuối kỳ thì điều kiện cần tính có thể bấm trực tiếp vào ô chứa nó hoặc gõ trực tiếp tài khoản cần kết chuyển vào công thức

Ví dụ:   =SUMIF( $E15:$E180, 5111,$H$15:$H$180)

 

2/ Hàm Vlookup: Là một trong những hàm excel thường dùng trong kế toán với mục đích Tìm đơn giá Xuất kho, tìm Mã hàng hoá, tên hàng hoá, tìm số dư, tìm số Khấu hao phân bổ kỳ trước...

Dưới đây tailieuketoan.vn sẽ hướng dẫn các bạn cách sử dụng hàm vlookup áp dụng trong excel kế toán như sau:

Tác dụng của hàm Vlookup trong excel kế toán:

- Tìm đơn giá Xuất kho từ bên Bảng Nhập Xuất Tồn về Phiếu Xuất kho.
- Tìm Mã TK, Tên TK từ Danh mục tài khoản về bảng CĐPS, về Sổ 131, 331…
- Tìm Mã hàng hoá, tên hàng hoá từ Danh mục hàng hoá về Bảng Nhập Xuất Tồn
- TÌm số dư của đầu tháng N căn cứ vao cột số dư của tháng N-1
- Tìm số Khấu hao (Phân bổ) luỹ kế từ kỳ trước, căn cứ vào Giá trị khấu hao( phân bổ) luỹ kế (của bảng 242, 214)
- Và các bảng khác liên quan….
 
Cú pháp của hàm Vlookup:
cách sử dụng hàm vlookup trong excel kế toán
 
Việc sử dụng phím F4:
- Bấm F4 (1 lần): Để có gái trị tuyệt đối (tuyệt đối được hiểu là cố định cột và cố định dòng – ($cột$dòng); Ví dụ: $E$12 – tức là cố định cột E và cố định dòng 12
- Bấm F4 (2 lần): Để có giá trị tương đối cột và tuyệt đối dòng – được hiểu là cố định dòng thay đổi cột – (cột$dòng) ; Ví dụ: E$12 – tức là cố định Dòng 12, không cố định cột E
- Bấm F4 (3 lần): Để có giá trị tương đối dòng và tuyệt đối cột – được hiểu là cố định cột thay đổi dòng – ($cột dòng); Ví dụ: $E12 – tức là cố định Cột E, không cố định dòng 12.
Giải thích:
Giá trị để tìm kiếm: Giá trị để tìm kiếm chỉ là một Ô và phải có Tên trong Vùng dữ liệu tìm kiếm (Là Ô mã hàng hoá, Mã tài khoản, Mã tài sản, Mã Công Cụ dụng cụ….)
Vùng dữ liệu tìm kiếm: “Vùng dữ liệu tìm kiếm” phải chứa tên của “Giá trị để tìm kiếm”. (Cụ thể: là bảng dữ liệu của tháng trước hoặc dữ liệu của Sheet khác)
Cột trả về giá trị tìm kiếm: Là số thứ tự cột, tính từ bên trái sang vủa vùng dữ liệu tìm kiếm (Ví dụ: cột thứ 8 tính từ bên trái sang)
Tham số “ 0”: Không thực hiện sắp xếp theo thứ tự nào.
Ví dụ về hàm vlookup trong excel kế toán như sau:
 Đi tìm Đơn giá xuất kho của mã hàng GEN_ASG24R từ bảng Nhập xuất tồn về Phiếu Xuất kho
- Giá trị tìm kiếm: Là Ô chứa mã hàng GEN_ASG24R trên PXK – Ví dụ: Ô F8
- Vùng dữ liệu tìm kiếm: Là bảng Nhập Xuất Tồn, điểm bắt đầu của vùng được tính từ dãy ô có chứa mã hàng GEN_ASG24R đến dãy ô có chứa giá trị cần tìm – Ví dụ: Vùng từ A9:L19.
- Cột trả về giá trị tìm kiếm: là cột “ Đơn giá xuất kho” trên Bảng Nhập Xuất Tồn và được đếm từ bên trái của vùng sang – Ví dụ: là cột thứ 8.
MÔ PHỎNG: ( Đặt công thức tại địa chỉ ô cần tính )

cách sử dụng hàm vlookup thông dụng trong excel kế toán

 

3/ Hàm SUBTOTAL : Là một trong những hàm excel thường dùng trong kế toán với mục đích tính tổng phát sinh trong kỳ, tính tổng tiền tồn cuối ngày, tính tổng cho từng tài khoản kế toán trên Mẫu sổ sách kế toán trên Excel

Hàm SUBTOTAL trong excel kế toán có tác dụng gì?

- Tính tổng dãy ô có điều kiện như:

                 Tính tổng phát sinh trong kỳ.
                 Tính tổng cho từng tài khoản cấp 1.
                 Tính tổng tiền tồn cuối ngày.
- Cú pháp hàm: = SUBTOTAL(9; dãy ô cần tính tổng)
 ( Số 9 là cú pháp mặc định của hàm cho việc tính tổng)

- Hàm Subtotal là hàm tính toán cho một nhóm con trong một danh sách hoặc bảng dữ liệu tuỳ theo phép tính mà bạn chọn lựa trong đối số thứ nhất.

- Đối số thứ nhất của hàm SUBTOTAL bắt buộc bạn phải nhớ con số đại diện cho phép tính cần thực hiện trên tập số liệu. Đối số đó được xác định hàm thực sự nào sẽ được sử dụng khi tính toán trong danh sách bên dưới.
cách sử dụng hàm subtotal trong excel kế toán
Nếu đối số là 1 thì hàm SUBTOTAL hoạt động giống nhưng hàm AVERAGE, nếu đối số thứ nhất là 9 thì hàm hàm SUBTOTAL hoạt động giống nhưng hàm SUM.
Chú ý: Tuy có nhiều đối số là từ 1 – 11 nhưng trong kế toán chúng ta thường sử dụng đối số 9 và thường sử dụng trong việc tính tổng cho từng tài khoản, tính tổng phát sinh bên Nợ, Có, tỉnh tổng số tiền cuối ngày
Ví dụ về hàm SUBTOTAL trong excel kế toán như sau:

hướng dẫn sử dụng hàm subtotal trong excel kế toán

- Tổng phát sinh bên Nợ:  = Subtotal (9;H13:H190)
- Tổng phát sinh bên Có: = Subtotal(9;H13:H190)
- Cột tồn tiền cuối ngày dùng hàm Subtotal:
Cú pháp hàm: = $J$9+Subtotal(9,H$11:H11)-Subtotal(9,I$11:I11)
Ghi chú:
- Nếu có hàm subtotal khác lồng đặt tại các đối số ref1, ref2,… thì các hàm lồng này sẽ bị bỏ qua không được tính nhằm tránh trường hợp tính toán 2 lần.
- Đối số function_num nếu từ 1 đến 11 thì hàm SUBTOTAL tính toán bao gồm cả các giá trị ẩn trong tập số liệu (hàng ẩn). Đối số function_num nếu từ 101 đến 111 thì hàm SUBTOTAL chỉ tính toán cho các giá trị không ẩn trong tập số liệu (bỏ qua các giá trị ẩn).
- Hàm SUBTOTAL sẽ bỏ qua không tính toán tất cả các hàng bị ẩn bởi lệnh Filter (Auto Filter) không phụ thuộc vào đối số function_num được dùng.
- Hàm SUBTOTAL được thiết kế để tính toán cho các cột số liệu theo chiều dọc, nó không được thiết kế để tính theo chiều ngang.
- Hàm này chỉ tính toán cho dữ liệu 2-D do vậy nếu dữ liệu tham chiếu dạng 3-D (Ví dụ về tham chiếu 3-D: =SUM(Sheet2:Sheet13!B5) thì hàm SUBTOTAL báo lỗi #VALUE!. (Các loại tham chiếu xem bài sẽ đăng tiếp sau)

 

II/ Ngoài ra là một số hàm excel cơ bản thường dùng trong excel kế toán

1/ Hàm SUM: Là một trong những hàm excel thường dùng trong kế toán với mục đích sử dụng đơn giản là cộng tất cá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.
 
2/ Hàm IF:
- 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ú pháp: If(logical_test,[value_if_true],[value_if_false]) nghĩa là If(Điều kiện, Giá trị 1, Giá trị 2).
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ả của hàm IF nếu logicaltest đúng
- Falsevalue: Là kết quả của hàm IF nếu logicaltest sai
Ví dụ: 
= IF(B2>=4,“DUNG”,“SAI”) = DUNG.         
= IF(B2>=5,“DUNG”,“SAI”) = SAI
 
3/ Hàm AND:
- Cú pháp:   AND(Logical1, Logical2, ….) nghĩa là And(đố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 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ố của nó là đúng, trả về giá trị FALSE (0) nếu một hay nhiều đối số của 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!
- Ví dụ:    =AND(D7>0,D7<5000)
 
4/ 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 của nó là đúng, trả về giá trị FALSE (0) nếu tất cả các đối số của nó là sai.
- Ví dụ:  =OR(F7>03/02/74,F7>01/01/2013)
 
5/ Hàm MIN: Là một trong những hàm excel thường dùng trong kế toán với mục đích sử dụng là để trả về số nhỏ nhất trong dãy được nhập vào
- Cú pháp:   MIN(Number1, Number2…)
- Các tham số: Number1, Number2… là dãy mà bạn muốn tìm giá trị nhỏ nhất ở trong đó
 
6/ Hàm MAX: Là một trong những hàm excel thường dùng trong kế toán với mục đích sử dụng là để trả về số lớn nhất trong dãy được nhập vào
- 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 đó.

Các bài viết mới

Không có tin nào

Các tin cũ hơn

Không có tin nào

Kế Toán Thuế

Số lượt xem

Đang online18
Tổng xem1