eXcel kế toán | Là 1 kế toán thường xuyên với những con số lớn nhỏ thì đòi hỏi cần phải biết ít nhất 1 số hàm excel kế toán cơ bản. Excel luôn là công cụ hỗ trợ nhân viên làm việc linh động và hiệu quả nhất với số liệu.để ghi nhận sổ sách kế toán và lập kế hoạch tài chính. Có thể tạo ra một chương trình kế toán cơ bản hoặc sổ kế toán để theo dõi các giao dịch tài chính và so sánh thu nhập, chi tiêu của tổ chức – hãy cùng Kế toán Việt Hưng tìm hiểu sâu hơn qua bài viết.
1. Các hàm excel kế toán nhóm THỐNG KÊ
♦ Hàm SUM
– Tính tổng toàn bộ các số trong vùng tài liệu được chọn .
– Công thức: SUM(Number1, Number2, Number3…)
– Trong đó, Number1, Number2, Number3 … là các số cần phải tính tổng .
♦ Hàm SUMIF
– Tính tổng giá trị của các ô được chỉ định với điều kiện kèm theo đơn cử, thường dùng trong nhiệm vụ kết chuyển cuối tháng khi làm Nhật ký chung, làm các bảng tổng hợp nhập – xuất hàng …
– Công thức SUMIF(Range, Criteria, Sum_range) = SUMIF(Vùng chứa điều kiện, Điều kiện, Vùng cần tính tổng)
– Trong đó :
Range : Dãy số muốn xác lập tổngCriteria : điều kiện kèm theo muốn tính tổng ( hoàn toàn có thể là biểu thức, chuỗi hoặc số ) Sum_range : các ô chứa giá trị cần tính tổng
– Ví dụ : = SUMIF ( B2 : B10, “ < = 100 ” ) – tính tổng giá trị trong vùng chọn từ B2 đến B10 với điều kiện kèm theo giá trị nhỏ hơn hoặc bằng 100 )
♦ Hàm AVERAGE
– Hàm tính giá trị trung bình của các đối số nhập vào .
– Công thức AVERAGE(Number1, Number2, Number3…)
– Trong đó, Number1, Number2, Number3 … là các số nhập vào cần tính giá trị trung bình .
♦ Hàm SUMPRODUCT
– Tính tích của dãy ô, sau đó tính tổng của các tích đó .
– Công thức: SUMPRODUCT(Array1, Array2, Array3…)
– Trong đó : Array1, Array2, Array3 … lần lượt là các dãy ô bạn muốn tính tích, sau đó tính tổng các tích .
– Ví dụ : = SUMPRODUCT ( A2 : A7, B3 : B8, C5 : C12 )
♦ Hàm MAX
– Trả về hiệu quả số lớn nhất trong dãy được nhập vào .
– Công thức: MAX(Number1, Number2, Number3…)
– Trong đó, Number1, Number2, Number3 … là dãy số bạn muốn tìm giá trị lớn nhất trong đó .
♦ Hàm LAGRE
– Tìm số lớn thứ k trong dãy ô được nhập .
– Công thức: LAGRE(Array, k)
– Trong đó, Array là dãy ô cần xác lập, k là thứ hạng số muốn tìm, tính từ số lớn nhất .
Ví dụ = LAGRE ( D4 : D20, 3 ) – tìm số lớn thứ 3 trong dãy ô từ D4 đến D20 .
♦ Hàm MIN
– Trả về hiệu quả là số nhỏ nhất trong dãy được nhập vào
– Công thức: MIN(Number1, Number2, Number3…)
– Trong đó, Number1, Number2, Number3 … là dãy số mà bạn muốn tìm giá trị nhỏ nhất trong đó .
♦ Hàm SMALL
– Tìm số có giá trị nhỏ thứ k trong dãy ô được nhập vào .
– Công thức: SMALL(Array, k)
– Trong đó, Array là dãy ô được chọn, k là thứ hạng của số cần tìm tính từ số nhỏ nhất ) .
♦ Hàm COUNT
– Là hàm đếm tài liệu của các ô chứa tài liệu kiểu số trong dãy được nhập .
– Công thức: COUNT(Value1, Value2, Value3…)
– Trong đó, Value1, Value2, Value3 … là dãy hay mảng tài liệu được chọn .
♦ Hàm COUNTA
– Là hàm đếm tài liệu của toàn bộ của tổng thể các cô chứa tài liệu .
– Công thức: COUNTA(Value1, Value2, Value3…)
♦ Hàm COUNTIF
– Đếm các ô chứa giá trị thỏa mãn nhu cầu điều kiện kèm theo cho trước .
– Công thức: COUNTIF(Range, Criteria)
– Trong đó :
Range là dãy tài liệu muốn đếm. Criteria : điều kiện kèm theo cho các ô được đếm
– Ví dụ : = COUNTIF ( A2 : A30, “ > 1000 ” ) – Đếm tổng thể các ô trong dãy từ A2 đến A30 thỏa mãn nhu cầu điều kiện kèm theo lớn hơn 1000 .
2. Các hàm excel kế toán nhóm ĐIỀU KIỆN
♦ Hàm IF
– Hàm điều kiện kèm theo này trả về giá trị 1 nếu điều kiện kèm theo đúng, trả về giá trị 2 nếu điều kiện kèm theo sai. Hàm này thường được sử dụng khi lập bảng lương cho nhân viên cấp dưới, tính thuế thu nhập cá thể, thưởng doanh thu cho nhân viên cấp dưới kinh doanh thương mại …
– Công thức: IF(logical-test,[value_if_true],[value_if_true]) = IF(Điều kiện, Giá trị 1, Giá trị 2)
– Ví dụ : = IF ( C2 > = 5, “ DUNG ”, “ SAI ” ) = DUNG
= IF ( C2 > = 6, “ DUNG ”, “ SAI ” ) = SAI
3. Các hàm excel kế toán nhóm TÌM KIẾM
♦ Hàm VLOOKUP
– Là hàm trả về giá trị tìm kiếm theo cột đưa từ bảng tham chiếu với bảng cơ sở tài liệu theo đúng giá trị dò tìm. Nếu X = 0 thì tác dụng dò tìm đúng mực, X = 1 là tác dụng dò tìm một cách tương đối .
– Công thức: VLOOKUP(Lookup Value, Table Array, Col idx num, [range lookup]) = VLOOKUP(Giá trị dò tìm, Bảng tham chiếu, Cột cần lấy, X)
– Ví dụ : = VLOOKUP ( E15, USD C USD 20 : USD D $ 22,3,0 ) – tìm một giá trị bằng giá trị ở ô E15 và lấy giá trị tương ứng ở cột thứ 3 .
♦ Hàm HLOOKUP
– Tìm kiếm giá trị như hàm VLOOKUP nhưng bằng cách so sánh với giá trị trong hàng tiên phong của bảng tham chiếu nhập vào .
– Công thức: HLOOKUP(Lookup Value, Table Array, Col idx num, [range lookup])
4. Các hàm excel kế toán nhóm LOGIC
♦ Hàm AND
– Công thức: AND(Logical1, Logical2,…)
– Trong đó, Logical1, Logical2, … là các biểu thức có điều kiện kèm theo. Các đối số nhập vào phải là giá trị logic hoặc mảng / tham chiếu có chứa giá trị logic, nếu không tác dụng sẽ trả về # VALUE !
– Nếu hàm cho hiệu quả TRUE ( 1 ) nếu các đối số nhập vào là đúng, và khi hàm trả về giá trị FALSE ( 0 ) nếu có 1 hay nhiều đối số của nó bị nhập sai .
– Ví dụ : = AND ( D6 > 0, D6 < 6000 )
♦ Hàm OR
– Công thức: OR(Logical1, Logical2…)
– Trong đó, Logical1, Logical2 … là các biểu thức điều kiện kèm theo. Hàm sẽ trả về giá trị True ( 1 ) nếu bất kể đối số nào nhập vào là đúng và trả về giá trị FALSE ( 0 ) nếu tổng thể các đối số nhập vào sai .
– Ví dụ : = OR ( D6 > 04/03/67, D6 > 01/01/2018 )
♦ Hàm NOT
– Đây là hàm đảo ngược giá trị của đối số nhập vào .
– Công thức: NOT(Logical)
– Trong đó Logical là biểu thức logic hoặc một giá trị
5. Các hàm excel kế toán nhóm TOÁN HỌC
♦ Hàm ABS
– Hàm ABS giúp lấy giá trị tuyệt đối của 1 số ít .
– Công thức: ABS(Number)
– Trong đó, Number là một giá trị số, một biểu thức / tham chiếu
– Ví dụ : = ABS ( B7 + 7 )
♦ Hàm PRODUCT
– Tính tích của một dãy số nhập vào.
– Cú pháp: PRODUCT(Number1, Number2, Number3…)
– Trong đó, Number1, Number2, Number3 … là dãy số cần tính tích .
♦ Hàm MOD
– Tính giá trị dư của phép chia
– Công thức: MOD(Number, pisor)
– Trong đó, number là số bị chia, pisor là số chia .
6. Các hàm excel kế toán nhóm TIỀN LƯƠNG
– Những hàm sẽ sử dụng khi lập bảng tiền lương gồm có :
- Hàm LEFT ( text, số ký tự cần lấy )
VD : LEFT ( “ ketoanviethung ”, 13 ) = “ ketoanviethung ”
- Hàm VLOOKUP như trên
-
Hàm LEN(text)
VD : LEN ( “ ketoanviethung ” ) = 13
-
Hàm SUMIF
-
Hàm SUBTOTAL(function_num,relf1,relf2,…)
function_num : là số lượng từ 1 -> 11 ( có thêm 101 đến 111 trong Excel 2003,2007 )
relf1, relf2 : là các vùng địa chỉ tham chiếu
VD : Kế toán thường sử dụng đối số 9 và thường sử dụng tính tổng cho từng thông tin 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 .
= 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 MAX
-
Hàm MIN
- Hàm MID
-
Hàm IF
-
Hàm AND(đối 1, đối 2,…)
VD : = AND ( D7 > 0, D7 < 5000 ) Các đối số : là các biểu thức có điều kiện kèm theo
-
Hàm OR(đối 1, đối 2,…)
VD : = OR ( F7 > 03/02/74, F7 < 01/01/20 XX ) – Tra cứu thông tin nhân viên cấp dưới có trong bảng list nhân viên cấp dưới : Hàm VLOOKUP ( lookup_value, table_array, col_index_num, [ range_lookup ] ) Hàm INDEX + MATCH : phối hợp 2 hàm này với nhau để truy vấn, tìm kiếm thông tin – Việc chấm công, tính lương thực hiện theo tháng, thế cho nên những hàm giải quyết và xử lý ngày tháng, thời hạn trong excel cũng thường được sử dụng :
- Hàm DATE ( year, month, day ) : tạo ra 1 giá trị ngày tháng xác lập rõ bởi năm, tháng, ngày
- Hàm YEAR ( serial_number ) : theo dõi số năm của 1 giá trị ngày tháng
- Hàm MONTH ( serial_number ) : theo dõi số tháng của 1 giá trị ngày tháng
- Hàm DAY ( serial_number ) : theo dõi số ngày của 1 giá trị ngày tháng
- Hàm HOUR ( serial_number ) : theo dõi số giờ của 1 giá trị thời hạn
- Hàm MIN ( serial_number ) : theo dõi số phút của 1 giá trị thời hạn
– Chấm công thao tác của nhân viên cấp dưới trong công ty
- COUNTIF : Đếm số ký hiệu công trong bảng chấm công ( theo 1 điều kiện kèm theo duy nhất )
- COUNTIFS : Đếm số ký hiệu công trong bảng chấm công ( theo nhiều điều kiện kèm theo cùng lúc )
- SUMIF : Tính tổng số giờ công trong bảng chấm công ( theo 1 điều kiện kèm theo duy nhất )
- SUMIFS : Tính tổng số giờ công trong bảng chấm công ( theo nhiều điều kiện kèm theo cùng lúc )
– Tính lương cho nhân viên cấp dưới
- IF : Cần biện luận logic trong việc thống kê giám sát trong 1 số trường hợp ( Nếu theo nhu yếu 1 thì hiệu quả là gì, nếu không thỏa mãn nhu cầu nhu yếu 1 thì tác dụng là gì )
- Hàm AND / OR : Khi cần biện luận nhiều điều kiện kèm theo tích hợp nhau thì sẽ sử dụng hàm And / Or để ghép nối các điều kiện kèm theo trong hàm IF
- Hàm LOOKUP / VLOOKUP : Truy vấn tới các nội dung : tin tức tính lương, thông tin nhân viên cấp dưới, tác dụng chấm công …
- Hàm SUM / SUMIF / SUMIFS : Tính tổng ( có điều kiện kèm theo hoặc không ) các nội dung tiền lương .
7. Các hàm excel kế toán nhóm CÔNG NỢ
Những hàm sẽ sử dụng khi lập bảng nợ công gồm có :
- Hàm VLOOKUP, SUMIFS
- Hàm MIN, MAX
(1) Cách tính số dư đầu kỳ công nợ
Hướng dẫn lập bảng tổng hợp nợ công phải thu người mua trên Excel tất cả chúng ta pháp luật số dư đầu kỳ là số dư tính tới thời gian từ ngày ( G7 ). Từ đó tất cả chúng ta có công thức ở ô E12 tức là nợ đầu kỳ như sau :
=MAX(VLOOKUP(C12,DMKH!$A$3:$H$6,7,0) -VLOOKUP(C12,DMKH!$A$3:$H$6,8,0)+SUMIFS(SO_TIEN,CT_NO,C12,NGAY_GS,”<” & $G$7) – SUMIFS(SO_TIEN,CT_CO,C12,NGAY_GS, “<” &$G$7),0).
Công thức ở ô E13 là :
=-MIN(VLOOKUP(C12,DMKH!$A$3:$H$6,7,0) -VLOOKUP(C12,DMKH!$A$3:$H$6,8,0)+SUMIFS(SO_TIEN,CT_NO,C12,NGAY_GS,”<” & $G$7) – SUMIFS(SO_TIEN,CT_CO,C12,NGAY_GS, “<” &$G$7),0).
Dễ thấy rằng 2 công thức trên có nhiều điểm tương đương. Thay vì sử dụng tích hợp hàm MAX để xác lập số dư bên Nợ thì số dư bên Có chúng hoàn toàn có thể sửa chữa thay thế là MAX = – MIN. Qua đó, việc bạn biến hóa ngày tháng ở ô G7 thì số dư này luôn bảo vệ tính đúng chuẩn .
(2) Cách tính số phát sinh trong kỳ công nợ
Số phát sinh bên Nợ ( G12 ) triển khai theo công thức :
=SUMIFS(SO_TIEN,CT_NO,C12,NGAY_GS,”>=” & $G$7, NGAY_GS, “<=” & $G$8).
Số phát sinh bên Có ( H12 ) vận dụng công thức :
=SUMIFS(SO_TIEN,CT_CO,C12,NGAY_GS,”>=” & $G$7, NGAY_GS, “<=” & $G$8).
(3) Tính số dư cuối kỳ
Nhập công thức tại ô I12 =MAX(E12+G12-F12-H12,0) nhằm mục đích tính số dư cuối kỳ bên Nợ.
Nhập công thức tại ô J12 để tính số dư cuối kỳ bên Có: =-MIN(E12+G12-F12-H12,0).
THAM KHẢO: Hơn 60 Khoá học kế toán Online chất lượng cam kết đầu ra
Trên đây là các hàm cơ bản trong Excel kế toán cần nắm rõ dễ dàng áp dụng khi xử lý các nghiệp vụ kế toán hàng ngày kết hợp với phần mềm kế toán giảm tải công việc không mất quá nhiều thời gian – Tham gia ngay Khoá học kế toán Online 1 kèm 1 bậc thầy chỉ sau 48H cam kết làm được việc!
Bình chọn
Source: https://thiennhuong.com
Category: Thủ Thuật
