Các hàm Excel cần thiết trong kế toán mà bạn nên biết

Ngoài các phần mềm kế toán chuyên dụng như Misa, Fast, Mcom..., Excel vẫn là công cụ phổ biến không thể thiếu đối với các kế toán viên. Việc thành thạo các hàm Excel thông dụng giúp tiết kiệm không chỉ thời gian mà còn công sức, thay thế cho các phương pháp tính toán thủ công tốn kém. Tripi hôm nay sẽ chia sẻ các hàm Excel thường được sử dụng trong công việc kế toán và cách áp dụng chúng hiệu quả.

Các phép toán cơ bản: Cộng, Trừ, Nhân, Chia

Phép cộng trong Excel

Cú pháp sử dụng hàm =SUM(number1,[number2],…) trong Excel

Cụ thể như sau:

• SUM: Hàm này được dùng để tính tổng các giá trị.
• Number1: Đây là đối số đầu tiên.
• Number2,…: Các đối số tiếp theo, có thể có hoặc không.

Đối số có thể là một dãy ô, một giá trị số học, hoặc một tham chiếu đến ô đơn lẻ, các đối số được phân tách bằng dấu phẩy (,).

Ngoài ra, bạn cũng có thể sử dụng dấu cộng (+) để thực hiện phép cộng đơn giản.

Phép trừ trong Excel

Cú pháp sử dụng: =number1 - number2

Chi tiết như sau:

• Dấu trừ (-): là toán tử dùng để thực hiện phép trừ trong Excel.
• Number1: là số bị trừ trong phép toán.
• Number2: là số sẽ trừ đi từ Number1.

Number có thể là một giá trị số cụ thể hoặc là tham chiếu đến một ô chứa dữ liệu.

Ví dụ: Bạn nhập = 5-2 vào ô tính => kết quả sẽ là 3.

Phép nhân trong Excel

Cú pháp sử dụng hàm: =PRODUCT(number1,[number2],…)

Cấu trúc chi tiết như sau:

• PRODUCT: là hàm dùng để nhân các giá trị lại với nhau.
• Number1: là thừa số đầu tiên.
• Number2,…: là các thừa số tiếp theo (có thể có hoặc không).

Number có thể là một dãy ô, giá trị số, hoặc tham chiếu ô đơn, các đối số được phân tách bởi dấu phẩy (,).

Ngoài ra, bạn cũng có thể sử dụng dấu hoa thị (*) để thực hiện phép nhân trong Excel.

Ví dụ: Bạn nhập công thức =PRODUCT(2,6,9) vào ô tính => kết quả sẽ là 108.

Phép chia trong Excel

Cú pháp sử dụng: =number1 / number2

Cụ thể như sau:

• Dấu gạch chéo (/): là toán tử dùng để thực hiện phép chia trong Excel.
• Number1: là số cần chia.
• Number2: là số chia.

Number có thể là một giá trị số cụ thể hoặc tham chiếu đến một ô chứa dữ liệu.

Ví dụ: Bạn nhập công thức vào ô tính =10/3 => kết quả trả về là 3.33333…

Hàm AVERAGE trong Excel

Cú pháp sử dụng: = AVERAGE(number1, [number2], …)

Cấu trúc chi tiết như sau:

• AVERAGE: là hàm dùng để tính giá trị trung bình của các đối số đã cho.
• Number1: là đối số đầu tiên cần tính trung bình.
• Number2,…: là các đối số tiếp theo, có thể có hoặc không.

Đối số có thể là một dãy ô, giá trị số hoặc tham chiếu đến ô đơn, được phân tách bằng dấu phẩy (,).

Ví dụ: =AVERAGE(1,2,3) => kết quả trả về là 2.

Hàm SUBTOTAL trong Excel

Cú pháp: = SUBTOTAL(function_num, ref1, [ref2],…)

Trong đó, SUBTOTAL là một hàm hữu ích để tính toán tổng các dòng dữ liệu đã được hiển thị, trong khi các dòng bị ẩn bởi bộ lọc sẽ bị loại trừ khỏi phép tính. Nếu bạn sử dụng các tham số 1-11, các hàng bị ẩn thủ công sẽ được tính, còn với tham số 101-111, các hàng ẩn sẽ được bỏ qua hoàn toàn. Thêm vào đó, phạm vi hoặc chuỗi được tham chiếu trong công thức cũng có thể được định rõ để tính tổng phụ một cách chính xác.

Hàm SUBTOTAL rất linh hoạt, cho phép bạn tính tổng các dữ liệu chỉ trên những dòng hiển thị, bất kể các giá trị đã được lọc hay ẩn đi. Cụ thể, tham số Function_num từ 1 đến 11 (hoặc 101 đến 111) cho phép bạn lựa chọn những kiểu tính toán khác nhau, chẳng hạn như tính tổng các dòng bị ẩn thủ công hay không. Ngoài ra, bạn cũng có thể chỉ định phạm vi dữ liệu cho phép tính tổng chính xác.

Ví dụ, khi bạn muốn tính tổng cho các dòng dữ liệu sau khi lọc qua công cụ Filter, bạn có thể sử dụng công thức =SUBTOTAL(9,G3:G12). Công thức này giúp cộng các giá trị trong phạm vi G3:G12, và chỉ tính những dòng mà không bị ẩn đi bởi bộ lọc, mang lại kết quả chính xác theo yêu cầu lọc của bạn.

Ví dụ thực tế, bạn có thể muốn tính tổng doanh thu của các chi nhánh Hà Nội sau khi đã áp dụng bộ lọc, giúp tách biệt thông tin một cách rõ ràng và hiệu quả hơn, chỉ tập trung vào dữ liệu có giá trị cụ thể. Điều này có thể dễ dàng thực hiện với hàm SUBTOTAL trong Excel.

Tổng doanh thu đã được lọc theo quý 1/2018, mang đến cái nhìn rõ ràng về sự thay đổi của các chỉ số kinh doanh trong giai đoạn này. Việc phân tích dữ liệu theo quý giúp ta nắm bắt được những biến động quan trọng và đưa ra những quyết định chiến lược phù hợp.

Hàm tìm kiếm trong Excel, bao gồm các hàm VLOOKUP và HLOOKUP, là công cụ mạnh mẽ giúp tra cứu và kết nối các dữ liệu từ nhiều bảng khác nhau, hỗ trợ người dùng tiết kiệm thời gian và công sức trong việc tìm kiếm thông tin liên quan.

Hàm VLOOKUP (Vertical Lookup) được sử dụng để tra cứu giá trị trong một cột và trả về thông tin tương ứng từ các cột khác trong bảng dữ liệu. Đây là một hàm rất phổ biến và hữu ích trong công việc phân tích dữ liệu.

Cú pháp của hàm =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) giúp xác định giá trị cần tra cứu, vùng dữ liệu cần tìm kiếm, chỉ số cột dữ liệu trả về, và tùy chọn phạm vi tìm kiếm chính xác hay gần đúng, từ đó mang lại kết quả chính xác và nhanh chóng.

Trong đó, hàm VLOOKUP giúp tìm kiếm dữ liệu theo chiều dọc trong bảng, hỗ trợ tra cứu các giá trị cần thiết từ một tập hợp dữ liệu lớn và trả về kết quả chính xác theo yêu cầu của người dùng.

Hàm VLOOKUP dùng để tra cứu các giá trị trong bảng theo hàng dọc. Cụ thể, lookup_value là giá trị bạn cần tìm, table_array là bảng tham chiếu, col_index_num là chỉ số cột chứa giá trị bạn muốn trả về. Ngoài ra, [range_lookup] là đối số tùy chọn giúp bạn chọn trả về kết quả chính xác hoặc gần đúng, với 1/TRUE cho kết quả xấp xỉ và 0/FALSE cho kết quả chính xác.

Giả sử bạn có bảng đơn giá và cần điền thông tin, bạn có thể áp dụng hàm VLOOKUP với công thức dưới đây để tra cứu và tự động điền dữ liệu theo yêu cầu.

Lưu ý: Khi sử dụng hàm VLOOKUP, hãy chắc chắn rằng cột chứa giá trị tìm kiếm là cột đầu tiên trong bảng tham chiếu, để công thức có thể hoạt động chính xác và hiệu quả.

• Cột đầu tiên trong bảng phải chứa giá trị mà bạn muốn tham chiếu để đảm bảo hàm VLOOKUP hoạt động chính xác.
• Vùng tham chiếu nên được đặt dưới dạng giá trị tuyệt đối để tránh sai sót trong quá trình sao chép công thức, trừ khi có yêu cầu khác.
• Hàm VLOOKUP sẽ trả về lỗi #N/A nếu giá trị cần tìm không có trong bảng tham chiếu. Trong trường hợp này, bạn nên kiểm tra lại bảng tham chiếu và bổ sung thông tin cho đầy đủ.

Hàm HLOOKUP là một công cụ tương tự như VLOOKUP, nhưng được sử dụng để tìm kiếm dữ liệu theo chiều ngang trong bảng. Điều này rất hữu ích khi dữ liệu được sắp xếp theo hàng thay vì cột.

Cú pháp của hàm =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) cho phép tra cứu giá trị trong một hàng, và trả về kết quả từ hàng khác trong bảng tham chiếu, giúp tiết kiệm thời gian và tăng hiệu quả làm việc.

Trong đó, lookup_value là giá trị bạn muốn tìm kiếm, table_array là bảng tham chiếu, row_index_num là chỉ số hàng chứa giá trị bạn muốn trả về, và [range_lookup] là đối số tùy chọn để xác định kết quả chính xác hay gần đúng.

• HLOOKUP: là hàm dùng để tra cứu giá trị trong một hàng ngang của bảng, giúp tìm kiếm và trả về dữ liệu từ một hàng cụ thể.
• lookup_value: là giá trị mà bạn muốn tìm kiếm trong bảng tham chiếu.
• table_array: là vùng dữ liệu hoặc bảng chứa các giá trị tham chiếu mà bạn muốn tra cứu.
• row_index_num: là số chỉ định hàng trong bảng tham chiếu từ đó giá trị cần trả về sẽ được lấy.
• [range_lookup]: là đối số tùy chọn cho phép bạn chỉ định kết quả là chính xác hay gần đúng, với 1/TRUE cho phép tìm gần đúng và 0/FALSE cho phép tìm chính xác.

Cách sử dụng hàm HLOOKUP tương tự như hàm VLOOKUP, nhưng điểm khác biệt là HLOOKUP tìm kiếm dữ liệu theo chiều ngang, giúp tra cứu các giá trị trong cùng một hàng thay vì trong một cột.

Hàm IF cho phép bạn thực hiện các quyết định logic trong công thức, giúp xử lý các tình huống khác nhau dựa trên điều kiện đã xác định trước.

Cấu trúc hàm: =IF(Logical_test, [value_if_true], [value_if_false]), trong đó Logical_test là điều kiện bạn muốn kiểm tra, [value_if_true] là giá trị trả về nếu điều kiện đúng, và [value_if_false] là giá trị trả về nếu điều kiện sai.

Trong đó, hàm IF là một công cụ hữu ích giúp xác định giá trị trả về dựa trên một điều kiện logic. Nếu điều kiện đúng, hàm trả về giá trị 1, ngược lại trả về giá trị 2.

• IF: là hàm điều kiện, trả về giá trị 1 khi điều kiện được thỏa mãn, và giá trị 2 khi điều kiện không đúng.
• Logical_test: là điều kiện logic mà bạn muốn kiểm tra.
• [value_if_true]: là giá trị được trả về nếu điều kiện Logical_test đúng. Nếu không nhập giá trị này, Excel sẽ tự động trả về giá trị TRUE nếu điều kiện đúng.
• [value_if_false]: là giá trị được trả về nếu điều kiện Logical_test sai. Nếu không nhập giá trị này, Excel sẽ tự động trả về giá trị FALSE nếu điều kiện sai.

Giả sử bạn có bảng điểm và cần phân loại thí sinh, bạn có thể áp dụng công thức sau để phân loại theo điểm:

• Thí sinh có điểm từ 5 trở lên => Đỗ.
• Thí sinh có điểm dưới 5 => Trượt.

Bạn có thể dùng công thức cho ô C2 như sau: =IF(B2>=5,"Đỗ","Trượt") để tự động phân loại kết quả.

Hàm SUMIF và SUMIFS là những công cụ mạnh mẽ trong Excel giúp tính tổng các giá trị thỏa mãn một hoặc nhiều điều kiện, hỗ trợ người dùng phân tích dữ liệu hiệu quả hơn.

Cấu trúc của hàm SUMIF là: =SUMIF(range, criteria, sum_range), trong đó bạn có thể chỉ định phạm vi điều kiện và vùng dữ liệu cần tính tổng.

Trong đó, hàm SUMIF sẽ sử dụng ba đối số:

• SUMIF: là hàm tính tổng các giá trị thỏa mãn một điều kiện nhất định.
• Range: là vùng tham chiếu chứa các dữ liệu cần kiểm tra điều kiện.
• Criteria: là điều kiện mà bạn muốn áp dụng để tính tổng.
• sum_range: là vùng dữ liệu mà bạn muốn tính tổng sau khi điều kiện được thỏa mãn.

Ví dụ, giả sử bạn có bảng doanh thu và muốn tính tổng doanh thu của chi nhánh Hà Nội. Bạn chỉ cần nhập công thức sau vào một ô trong bảng tính Excel: =SUMIF(B3:B12, "Chi nhánh Hà Nội", G3:G12). Công thức này sẽ tự động tính tổng các giá trị trong vùng G3:G12, chỉ cho những hàng có điều kiện "Chi nhánh Hà Nội" trong cột B.

Cấu trúc của hàm SUMIFS là: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...), cho phép tính tổng các giá trị thỏa mãn nhiều điều kiện khác nhau, mang lại khả năng phân tích mạnh mẽ.

Trong đó, hàm SUMIFS có các đối số như sau:

• SUMIFS: là hàm dùng để tính tổng các giá trị đáp ứng nhiều điều kiện khác nhau.
• sum_range: là vùng dữ liệu bạn muốn tính tổng.
• criteria_range1, 2, 3…: là các vùng dữ liệu chứa điều kiện mà bạn muốn áp dụng.
• criteria1, 2, 3…: là các điều kiện bạn muốn thỏa mãn trong từng vùng tham chiếu.

Giả sử bạn có bảng doanh thu và cần tính tổng doanh thu của chi nhánh Hà Nội trong quí 1/2018. Để thực hiện điều này, bạn có thể sử dụng công thức: =SUMIFS(G3:G12, B3:B12, "Chi nhánh Hà Nội", C3:C12, "1/2018"). Công thức này sẽ tính tổng giá trị trong vùng G3:G12, chỉ cho những hàng có điều kiện "Chi nhánh Hà Nội" trong cột B và "1/2018" trong cột C.

Hàm AND và OR là những công cụ logic trong Excel, cho phép bạn kiểm tra nhiều điều kiện cùng một lúc để đưa ra kết quả chính xác hơn.

Cấu trúc của hàm AND là: =AND(Logical1; [Logical2]; [Logical3]; ...), giúp kiểm tra xem tất cả các điều kiện có đúng hay không.

Trong đó, hàm AND yêu cầu các điều kiện được kiểm tra theo một chuỗi logic, và chỉ trả về kết quả TRUE khi tất cả các điều kiện đều đúng.

• AND: là hàm logic với ý nghĩa 'VÀ', sẽ trả về giá trị TRUE khi tất cả các điều kiện được kiểm tra đều thỏa mãn.
• Logical1, 2, 3…: là các giá trị hoặc biểu thức logic cần đánh giá trong hàm.

Cấu trúc của hàm OR là: =OR(Logical1; [Logical2]; [Logical3];…), cho phép kiểm tra nhiều điều kiện và trả về giá trị TRUE nếu ít nhất một điều kiện đúng.

Trong đó, hàm OR kiểm tra các điều kiện logic và trả về giá trị TRUE nếu có bất kỳ điều kiện nào đúng, ngược lại, trả về FALSE.

• OR: là hàm logic mang ý nghĩa 'HOẶC', trả về giá trị TRUE nếu ít nhất một điều kiện là đúng. Nếu tất cả các điều kiện sai, hàm sẽ trả về FALSE.
• Logical1,2,3…: là các giá trị hoặc biểu thức logic mà bạn muốn kiểm tra trong hàm.

Ví dụ so sánh sự khác biệt giữa hàm AND và OR:

AND(TRUE, FALSE) = FALSE: Trong hàm AND, tất cả các điều kiện phải đúng, nếu có một điều kiện sai, kết quả trả về là FALSE.

OR(TRUE, FALSE) = TRUE: Hàm OR sẽ trả về TRUE nếu ít nhất một điều kiện là đúng, ngay cả khi có một điều kiện sai.

Hàm MIN và MAX giúp tìm giá trị nhỏ nhất và lớn nhất trong một dãy số, rất hữu ích để xác định phạm vi dữ liệu một cách nhanh chóng.

Cấu trúc của hàm là: =MIN(number 1, number 2, …), cho phép tìm ra giá trị nhỏ nhất trong một dãy số đã cho.

MIN được sử dụng để tìm ra giá trị nhỏ nhất trong một chuỗi số được cung cấp, giúp xác định giá trị thấp nhất trong dữ liệu.

• MIN: là hàm dùng để tìm kiếm và trả về giá trị nhỏ nhất trong một tập hợp số.
• number 1,2…: là dãy số mà bạn muốn tìm giá trị nhỏ nhất từ đó.

Cấu trúc hàm MAX: =MAX(number 1, number 2, …), giúp xác định giá trị lớn nhất trong dãy số cho trước.

Giải thích:

• MAX: là hàm được sử dụng để xác định giá trị lớn nhất trong một tập hợp số đã cho.
• number 1,2…: là chuỗi số mà bạn muốn tìm giá trị lớn nhất trong đó.

Ví dụ minh họa: MIN(10,5,16) = 5 và MAX(10,5,16) = 16, giúp dễ dàng so sánh và tìm giá trị cực trị trong dữ liệu.

Hàm LEFT, RIGHT, MID giúp thao tác và trích xuất các phần dữ liệu từ chuỗi văn bản, mang lại sự linh hoạt trong việc xử lý thông tin.

Các hàm LEFT, RIGHT và MID mang đến khả năng trích xuất các ký tự từ chuỗi, giúp người dùng dễ dàng thao tác và xử lý văn bản.

Cấu trúc của các hàm này như sau:

= LEFT(text, [num_chars]) để lấy ký tự từ bên trái.
= RIGHT(text, [num_chars]) để lấy ký tự từ bên phải.
= MID(text, start_num, num_chars) để lấy ký tự từ giữa chuỗi, bắt đầu từ vị trí xác định.

Giải thích chi tiết về các tham số trong các hàm này:

• LEFT: là hàm cho phép trích xuất một đoạn ký tự từ bên trái của chuỗi văn bản.
• RIGHT: là hàm trích xuất các ký tự từ bên phải chuỗi văn bản.
• MID: là hàm dùng để lấy các ký tự bắt đầu từ vị trí start_num mà bạn chỉ định, đếm từ trái sang phải.
• Text: Chuỗi văn bản chứa các ký tự bạn muốn lấy.
• Start_num: Chỉ ra vị trí bắt đầu của ký tự mà hàm MID sẽ lấy.
• Num_chars: Xác định số lượng ký tự muốn trích xuất. Num_chars phải là số không âm. Nếu giá trị này lớn hơn chiều dài chuỗi, hàm sẽ trả về toàn bộ chuỗi. Nếu bỏ qua, mặc định sẽ lấy 1 ký tự.

Giả sử bạn có chuỗi "Tripi", và bạn muốn lấy lần lượt 5 ký tự từ bên trái, bên phải và từ ký tự thứ 4 trở đi.

=LEFT("Tripi",5) => Kết quả: ThuTh;
=RIGHT("Tripi",5) => Kết quả: em.VN;
=MID("Tripi",4,5) => Kết quả: Thuat.

Hàm TEXT giúp bạn định dạng và xử lý văn bản một cách linh hoạt.

Cú pháp của hàm là: = TEXT(số, định_dạng)

Trong đó:

• TEXT: là hàm dùng để thay đổi cách hiển thị một con số.
• Số: là giá trị mà bạn muốn định dạng.
• Định_dạng: là mã định dạng bạn mong muốn áp dụng cho số đó.

Hàm TEXT giúp chuyển đổi số thành chữ hoặc định dạng theo kiểu ngày tháng, phần trăm, tiền tệ,…

Dưới đây là những mã định dạng thông dụng mà Tripi muốn giới thiệu đến bạn:

Trên đây là những hàm phổ biến trong kế toán mà Tripi đã chia sẻ. Hy vọng rằng bài viết này sẽ mang lại giá trị hữu ích cho bạn. Chúc bạn thành công trong công việc!