CÁC CÔNG THỨC EXCEL THƯỜNG DÙNG TRONG KẾ TOÁN

Excel là công cụ làm việc gần như bắt buộc phải có với kế toán và kiểm toán. Đây cũng là công cụ đắc lực giúp kế toán và kiểm toán viên hoàn thành công việc một cách nhanh chóng nhất. Sau đây ACC CLASS sẽ chia sẻ một số hàm thông dụng mà kế toán sử dụng phổ biến nhất.

                            

1.Hàm tính tổng (SUM, SUMIF, SUMIFS, SUBTOTAL):

a. Hàm SUM:

- Cấu trúc: =SUM(number1, number2, number3,…..)=SUM(các đối số mà bạn muốn tính tổng,…..)

- Tác dụng: Tính tổng các số trong một phạm vi

b. Hàm SUMIF:

- Cấu trúc: =SUMIF(range, criteria, [sum_range])=SUMIF(Vùng điều kiện,Điều kiện,Vùng tính tổng)

- Tác dụng: Tính tổng số phát sinh theo 1 điều kiện. (Tính số phát sinh của các tài khoản trong bảng cân đối số phát sinh).

c. Hàm SUMIFS:

- Cấu trúc: =SUMIFS (sum_range, criteria_range1, criteria1, [criter_range2, criteria2], …) = SUMIFS(Vùng tính tổng, vùng điều kiện 1, điều kiện 1, vùng điều kiện 2, điều kiện 2,…)

- Tác dụng: Tính tổng theo 1 hoặc nhiều điều kiện

d. Hàm SUBTOTAL:

- Cấu trúc: =SUBTOTAL(function_num, ref1, [ref2],…)=SUBTOTAL( số xác định chức năng thực hiện, các đối số bạn muốn tính tổng)

- Tác dụng: Tính tổng trong 1 nhóm con trong một danh sách hoặc bảng dữ liệu, bỏ qua các hàng đã được lọc ra hoặc ẩn đi.

2. Hàm tìm kiếm (VLOOKUP, HLOOKUP):

a. Hàm VLOOKUP:

- Cấu trúc:

= VLOOKUP (lookup_value,table_array,col_index_num,[range_lookup])

= VLOOKUP(Giá trị dò tìm,Bảng dò tìm,Số thứ tự cột dò tìm,Kiểu dò tìm)

- Tác dụng: Tìm kiếm giá trị từ bảng dò tìm theo chiều dọc.

b. Hàm HLOOKUP:

- Cấu trúc: =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]) = HLOOKUP(Giá trị dò tìm,Bảng dò tìm,Số thứ tự hàng dò tìm,Kiểu dò tìm)

- Tác dụng: Tìm kiếm giá trị từ bảng dò tìm theo chiều ngang.

c. Hàm INDEX:

- Cấu trúc: =INDEX(Array,Row_num,Col_num)

- Trong đó:

+ Array: Là một vùng chứa các ô hoặc một mảng bất biến. Nếu Array chỉ chứa một hàng và một cột, tham số Row_num hoặc Col_num tương ứng là tùy ý. Nếu Array có nhiều hơn một hàng hoặc một cột thì chỉ một Row_num hoặc Col_num được sử dụng.

+ Row_num: Chọn lựa hàng trong Array. Nếu Row_num được bỏ qua thì Col_num là bắt buộc.

+ Col_num: Chọn lựa cột trong Array. Nếu Col_num được bỏ qua thì Row_num là bắt buộc.

- Tác dụng:  Trả về một giá trị hay một tham chiếu đến một giá trị trong phạm vi bảng hay vùng dữ liệu.

3. Hàm điều kiện: (IF, IFERROR)

a. Hàm IF:

- Cấu trúc: IF (logical_test, [value_if_true], [value_if_false]) =IF (điều kiện, giá trị đúng, giá trị sai)

- Tác dụng: đánh giá một điều kiện nhất định và trả về giá trị mà bạn chỉ định nếu điều kiện là TRUE và trả về một giá trị khác nếu điều kiện là FALSE.

b. Hàm IFERROR:

- Cấu trúc = IFERROR(value, value_if_error)= IFERROR (giá trị, giá trị nếu lỗi)

- Tác dụng: hàm trả về giá trị do bạn chỉ định nếu một công thức bị lỗi, nếu không sẽ trả về giá trị của công thức đó.

4. Hàm xử lý chuỗi (LEFT, RIGHT, MID, LEN, TRIM):

a. Hàm MID:

- Cấu trúc = MID(Text ,Start_num, Num_chars) = MID(Chuỗi ,Vị trí bắt đầu, [Số ký tự])

- Tác dụng: Hàm Mid() dùng để lấy ra n ký tự của chuỗi (Text) từ ngay vị trí bắt đầu được chỉ định. Trong đó:

Chuỗi: Là chuỗi văn bản có chứa các ký tự cần lấy ra.

Vị trí bắt đầu: Vị trí bắt đầu để lấy n ký tự từ trong chuỗi đã cho

Số ký tự: Là số ký tự muốn lấy ra từ ngay vị trí bắt đầu của Chuỗi đã cho.

- Ví dụ: =Mid(“Acc Class lop dao tao ke toan thue”,1,9) = Acc Class

Hàm Mid() sẽ lấy ra 9 ký tự từ vị trí bắt đầu là số 1 (chữ A) của chuỗi “Acc Class lop dao tao ke toan thue”.

b. Hàm LEN:

- Cấu trúc: =LEN(Text) = LEN(Chuỗi)

- Tác dụng: Hàm Len() dùng để đếm chiều dài (số ký tự) của chuỗi (Text)

Trong đó: Chuỗi: Là chuỗi văn bản có chứa các ký tự cần đếm tổng chiều dài bao nhiêu ký tự.

– Ví dụ: =Len(“ACCClass”) = 8

Hàm Len() sẽ đếm tổng số ký tự của chuỗi “ACCClass”.

c. Hàm LEFT:

- Cấu trúc: = LEFT (Text ,Num_chars) = LEFT (chuỗi, số ký tự)

- Tác dụng: để lấy ra ký tự bên trái chuỗi

- Ví dụ: =Left(“ACCClass”,3)=ACC

d. Hàm MID:

- Cấu trúc: = MID(Text ,Start_num, Num_chars) = MID(chuỗi,số ký tự bắt đầu, tổng số ký tự muốn lấy)

- Tác dụng: lấy các ký tự nằm « giữa » chuỗi

- Ví dụ: =MID(“KetoanthueACCClass”,11,8) = ACCClass

e. Hàm TRIM:

- Cấu trúc: = TRIM(Text) = TRIM(chuỗi ký tự)

- Tác dụng: Loại bỏ các khoảng trắng thừa trong chuỗi.

- Ví dụ: =TRIM(“ ACC Class ”) = ACCClass

f. Hàm UPPER:

- Cấu trúc: = UPPER(Text)

- Tác dụng: Đổi chuỗi nhập vào thành chữ hoa

g. Hàm LOWER:

- Cấu trúc: = LOWER(Text)

- Tác dụng: Đổi chuỗi nhập vào thành chữ thường.

h. Hàm PROPER:

- Cấu trúc: = PROPER(Text)

- Tác dụng: Đổi ký từ đầu của từ trong chuỗi thành chữ hoa.

5. Hàm MIN, Hàm MAX:

a. Hàm MIN

- Cấu trúc: = Min (Number 1, Number2,….) = Min (Các đối số hoặc các vùng dữ liệu,…)

- Tác dụng: Hàm sẽ trả về giá trị thấp nhất trong vùng dữ liệu.

b. Hàm MAX

- Cấu trúc: = Max (Number 1, Number 2,…) = Max (Các đối số hoặc các vùng dữ liệu,…)

- Tác dụng: Hàm sẽ trả về giá trị cao nhất trong bảng dữ liệu mà người dùng chỉ định.

6. Hàm đếm dữ liệu (COUNT, COUNTA, COUNTIF, COUNTIFS):

a. Hàm COUNT

- Cấu trúc: = COUNT(Value1, Value2, …) = COUNT (Các mảng hoặc dãy dữ liệu,…)

- Tác dụng: Hàm này dùng để đếm các ô chứa dữ liệu kiểu số trong dãy.

b. Hàm COUNTA

- Cấu trúc: = COUNTA(Value1, Value2, …) = COUNTA (Các mảng hoặc dãy dữ liệu,…)

- Tác dụng: Đếm tất cả các ô chứa dữ liệu.

c. Hàm COUNTIF

- Cấu trúc: = COUNTIF(Range, Criteria) = COUNTIF (Dãy dữ liệu muốn đếm, tiêu chuẩn cho các ô được đếm…)

- Tác dụng: Hàm này có chức năng đếm các ô chứa dữ liệu kiểu số theo một điều kiện cho trước.

d. Hàm COUNTIFS:

- Cấu trúc: = COUNTIFS(range1, criteria1, range2, criteria2, …) = COUNTIFS (Dãy dữ liệu muốn đếm, tiêu chuẩn cho các ô được đếm…)

- Tác dụng: Hàm này dùng để đếm tổng số ô thỏa mãn điều kiện yêu cầu.

7. Hàm logic (AND, OR, NOT,…)

a. Hàm AND:

- Cấu trúc = AND(Logical1, logical 2,..)

- Trong đó, Logical1, Logical2,… là các biểu thức có điều kiện. 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 kết quả sẽ trả về #VALUE!

- Tác dụng: Nếu hàm cho kết 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.

b. Hàm OR

- Công thức: = OR(Logical1, Logical2…)

- Trong đó, Logical1, Logical2… là các biểu thức điều kiện.

- Tác dụng: 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ất cả các đối số nhập vào sai.

c. Hàm NOT

- Công thức: = NOT(Logical)

- Trong đó Logical là biểu thức logic hoặc một giá trị

- Tác dụng: Đây là hàm đảo ngược giá trị của đối số nhập vào.

8. Hàm toán học ABS, PRODUCT, MOD, ROUNDUP…

a. Hàm ABS

- Công thức: = ABS(Number)

- Trong đó, Number là một giá trị số, một biểu thức/ tham chiếu

- Tác dụng: Hàm ABS giúp lấy giá trị tuyệt đối của một số.

b. Hàm PRODUCT

- Công thức: = PRODUCT(Number1, Number2, Number3…)

- Trong đó, Number1, Number2, Number3… là dãy số cần tính tích.

- Tác dụng: Tính tích của một dãy số nhập vào.

c. ​Hàm MOD

- Công thức: MOD(Number, pisor)

- Trong đó, number là số bị chia, pisor là số chia.

- Tác dụng: Tính giá trị dư của phép chia.

d. Hàm ROUNDUP

- Công thức: ROUNDUP(Number, Num_digits) = ROUNDUP(số thực muốn làm tròn, bậc số muốn làm tròn)

- Tác dụng:  Làm tròn một số

- Chú ý:

+ Nếu Num_digits > 0 sẽ làm tròn phần thập phân.

+ Nếu Num_digits = 0 sẽ làm tròn lên số tự nhiên gần nhất.

+ Nếu Num_digits < 0 sẽ làm tròn phần nguyên sau dấu thập phân.

e. Hàm EVEN:

- Công thức: = EVEN(Number)

- Trong đó: Number là số mà bạn muốn làm tròn.

- Tác dụng:  Làm tròn lên thành số nguyên chẵn gần nhất.

- Chú ý: Nếu Number không phải là kiểu số thì hàm trả về lỗi #VALUE!

f. Hàm ODD:

- Công thức: = ODD(Number)

- Trong đó: Number là số mà bạn muốn làm tròn.

- Tác dụng: Làm tròn lên thành số nguyên lẻ gần nhất.

g. Hàm ROUNDDOWN:

- Công thức: = ROUNDDOWN(Number, Num_digits)

- Trong đó: tương tự như hàm ROUNDUP.

- Tác dụng: Làm tròn xuống một số.

9. Hàm ngày tháng:

a. Hàm DATE:

- Công thức: = DATE(year,month,day)

- Tác dụng: Hàm Date trả về một chuỗi trình bày một kiểu ngày đặc thù.

- Trong đó:

+ Year: miêu tả năm, có thể từ 1 đến 4 chữ số. Nếu bạn nhập 2 chữ số, theo mặc định Excel sẽ lấy năm bắt đầu là: 1900.

+ Month: miêu tả tháng trong năm. Nếu month lớn hơn 12 thì Excel sẽ tự động tính thêm các tháng cho số miêu tả năm.

+ Day: miêu tả ngày trong tháng. Nếu Day lớn hơn số ngày trong tháng chỉ định, thì Excel sẽ tự động tính thêm ngày cho số miêu tả tháng.

Lưu ý: - Excel lưu trữ kiểu ngày như một chuỗi số liên tục, vì vậy có thể sử dụng các phép toán   cộng (+), trừ (-) cho kiểu ngày.

b. Hàm DAY:

- Công thức: = DAY(Serial_num)

- Tác dụng: Trả về ngày tương ứng với chuỗi ngày đưa vào. Giá trị trả về là một số kiểu Integer ở trong khoảng từ 1 đến 31.

- Trong đó: Serial_num: Là dữ liệu kiểu Date, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác.

c. Hàm MONTH:

- Công thức: =  MONTH(Series_num)

- Tác dụng: Trả về tháng của chuỗi ngày được mô tả. Giá trị trả về là một số ở trong khoảng 1 đến 12.

- Trong đó: Series_num: Là một chuỗi ngày, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác.

d. Hàm YEAR:

- Công thức: =  YEAR(Serial_num)

- Tác dụng: Trả về năm tương ứng với chuỗi ngày đưa vào. Year được trả về là một kiểu Integer trong khoảng 1900-9999.

- Trong đó: Serial_num: Là một dữ liệu kiểu ngày, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác

f. Hàm TODAY:

- Công thức: =TODAY()

- Tác dụng: Trả về ngày hiện thời của hệ thống.

- Hàm này không có các đối số.

g. Hàm WEEKDAY:

- Tác dụng: Trả về số chỉ thứ trong tuần.

- Công thức: =WEEKDAY(Serial, Return_type)

- Trong đó: Serial: một số hay giá trị kiểu ngày. Return_type: chỉ định kiểu dữ liệu trả về.

10. Hàm thời gian:

a. Hàm TIME:

- Công thức: =TIME(Hour,Minute,Second)

- Tác dụng: Trả về một chuỗi trình bày một kiểu thời gian đặc thù. Giá trị trả về là một số trong khoảng từ 0 đến 0.99999999, miêu tả thời gian từ 0:00:00 đến 23:59:59.

- Trong đó:

+ Hour: miêu tả giờ, là một số từ 0 đến 32767.

+ Minute: miêu tả phút, là một số từ 0 đến 32767.

+ Second: miêu tả giây, là một số từ 0 đến 32767.

b. Hàm HOUR:

- Công thức: =HOUR(Serial_num)

- Tác dụng: Trả về giờ trong ngày của dữ liệu kiểu giờ đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 (12:00A.M) đến 23 (11:00P.M).

- Trong đó:

+ Serial_num: Là dữ liệu kiểu Time. Thời gian có thể được nhập như:

+ Một chuỗi kí tự nằm trong dấu nháy (ví dụ “5:30 PM”)

+ Một số thập phân (ví dụ 0,2145 mô tả 5:08 AM)

+ Kết quả của một công thức hay một hàm khác.

c. Hàm MINUTE:

- Công thức: =MINUTE(Serial_num)

- Tác dụng: Trả về phút của dữ liệu kiểu Time đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 đến 59.

- Trong đó: Serial_num: Tương tự như trong công thức HOUR.

d. Hàm SECOND:

- Công thức: =SECOND(Serial_num)

- Tác dụng:Trả về giây của dữ liệu kiểu Time đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 đến 59.

- Trong đó: Serial_num: Tương tự như trong công thức HOUR.

e. Hàm NOW:

- Công thức: =Cú pháp: NOW()

- Tác dụng: Trả về ngày giờ hiện thời của hệ thống.

- Hàm này không có các đối số.

Trên đây là một số công thức Excel thông dụng thường dùng trong kế toán mà ACC CLASS đã tổng hợp lại gửi tới các bạn. ACC CLASS chúc các bạn áp dụng các công thức Excel vào công việc của mình thật hiệu quả.

Tác giả: Thu Hương Nguyễn