Các Hàm Excel Nâng Cao Trong Kế Toán | 10 Công Thức Excel Nâng Cao Bạn Phải Biết

by Shubkids
15 February, 2024

TOP 10 Công Thức Excel Nâng Cao Thông Dụng

Hàm Excel nâng cao cho kế toán – Hàm OFFSET kết hợp SUM/AVERAGE

OFFSET là hàm Excel được sử dụng để tham chiếu, tính toán dữ liệu của 1 phạm vi rộng, phạm vi ô 1 một số hàng/1 số cột nhất định. Trong Excel, hàm OFFSET thường được sử dụng khi người dùng cần tính toán dữ liệu của 1 ô, 1 dãy bảng tính. Công thức OFFSET kết hợp cùng SUM/AVERAGE như sau:

OFFSET + SUM: =SUM(OFFSET(reference,rows,cols,[height],[width]))

Trong đó:

Reference: Một ô, một dải ô liền kề cần thực hiện hàm.
Rows: Số hàng di chuyển từ điểm được chọn làm gốc, lên hoặc xuống. Nếu các hàng là dương, công thức di chuyển dưới vùng tham chiếu, nếu các hàng là âm, công thức di chuyển trên vùng tham chiếu.
Cols: Số cột mà bạn muốn công thức của bạn di chuyển. Tương tự như rows, nếu giá trị cols dương sẽ là bên phải tham chiếu, nếu âm là bên trái tham chiếu.
Height: Chiều cao, tính bằng số hàng muốn tham chiếu trả về.
Width: Chiều rộng, tính bằng số cột muốn tham chiếu trả về.

OFFSET + AVERAGE: =AVERAGE(OFFSET(reference,rows,cols,[height],[width])). Các tham số tương tự với công thức OFFSET + SUM.

Các tin cũ hơn

Excel là phần mềm đóng vai trò quan trọng trong công việc văn phòng với nhiều lợi ích không ngờ. Nhờ đó, chúng ta có thể tạo và quản lý dữ liệu trên các bảng tính, hay tính toán và phân tích dữ liệu bằng các hàm.

Nếu bạn là người thường xuyên dùng Excel để xử lý công việc nhưng chưa biết đến các hàm Excel nâng cao cho người đi làm thì bài viết này dành cho bạn. Cùng Gitiho tìm hiểu nhé!

XEM NHANH BÀI VIẾT

Khi bạn cần thao tác và tính toán dữ liệu về thời gian và ngày tháng, hãy tham khảo một số hàm ngày tháng nâng cao và cách sử dụng chúng dưới đây:

DATEDIF(start_date, end_date, “unit”): Dùng để tính số ngày, tháng hoặc năm giữa hai ngày cụ thể.

Ví dụ: Nếu bạn muốn biết khoảng cách thời gian từ ngày 01/01/2023 đến ngày 31/12/2025 là bao nhiêu tháng, bạn có thể sử dụng hàm:

=DATEDIF(A1, B1, "m")

Trong đó: A1 là ngày bắt đầu, B1 là ngày kết thúc, m là kết quả trả về số tháng.

EDATE(start_date, months): Dùng để trả về ngày sau một số tháng từ ngày bắt đầu.

Ví dụ: Nếu bạn muốn biết ngày sau 6 tháng từ ngày 04/10/2023, bạn có thể sử dụng hàm sau:

=EDATE(A2, 6)

trong đó: A2 là ngày bắt đầu. Và bạn cần định dạng thời gian cho ô kết quả (ô D2)

EOMONTH(start_date, months): là một trong các hàm Excel nâng cao để trả về ngày cuối cùng của tháng sau một số tháng từ ngày bắt đầu.

Ví dụ: Nếu bạn muốn biết ngày cuối cùng trong tháng của 6 tháng sau từ ngày 01/01/2023, bạn có thể sử dụng hàm:

=EOMONTH(A3, 6)

Trong đó: A3 là ngày bắt đầu và bạn cần định dạng ô trả về kết quả (D3)

Kết quả khi sử dụng 3 hàm trên như sau:

Để thực hiện nhanh các thao tác trích xuất và biến đổi các chuỗi ký tự, xem ngay một số hàm xử lý chuỗi cơ bản đến nâng cao mà chúng tôi liệt kê dưới đây:

LEFT(text, num_chars): dùng khi muốn trích xuất một số ký tự từ phía bên trái của một chuỗi.

RIGHT(text, num_chars): để trích xuất một số ký tự từ phía bên phải của một chuỗi.

MID(text, start_num, num_chars): dùng để trích xuất một phần của chuỗi, bắt đầu từ vị trí đã cho và với số lượng ký tự xác định.

LEN(text): Trả về số ký tự trong một chuỗi.

Ví dụ: Giả sử chúng ta có chuỗi “GitihoB2C2023”. Lúc này, bạn muốn trích xuất thông tin từ chuỗi này:

Sử dụng hàm LEFT để lấy 6 ký tự từ bên trái:

=LEFT(A1, 6)

Sử dụng hàm RIGHT để lấy 4 ký tự từ bên phải:

=RIGHT(A1, 4)

Sử dụng hàm MID để lấy 3 ký tự từ vị trí thứ 7:

=MID(A1, 7, 3)

LOWER(text): Chuyển đổi các ký tự trong chuỗi thành chữ thường.

UPPER(text): Chuyển đổi các ký tự trong chuỗi thành chữ hoa.

PROPER(text): Chuyển đổi chuỗi thành dạng chữ hoa đầu tiên của từng từ.

Ví dụ: Giả sử chúng ta có chuỗi “hello GitiHo”. Chúng ta muốn thực hiện các thao tác sau:

Chuyển đổi các ký tự thành chữ thường bằng hàm LOWER:

=LOWER(A7)

Chuyển đổi các ký tự thành chữ hoa với hàm UPPER:

=UPPER(A7)

Chuyển đổi chuỗi thành dạng chữ hoa đầu tiên của từng từ bằng hàm PROPER:

=PROPER(A7)

TRIM(text): Loại bỏ các khoảng trắng không cần thiết từ chuỗi.

=TRIM(" Hello ")

CONCATENATE(text1, text2, …): Ghép nối các chuỗi lại với nhau.

Ví dụ:

=CONCATENATE(A17, " ",A18)

FIND(find_text, within_text, [start_num]): Tìm vị trí của chuỗi con trong chuỗi khác.

Ví dụ:

=FIND("l", A17)

REPLACE(old_text, start_num, num_chars, new_text): Thay thế một phần của chuỗi bằng chuỗi khác.

Ví dụ:

=REPLACE(A20, 7, 5, "Universe")

SUBSTITUTE(text, old_text, new_text, [instance_num]): Thay thế các chuỗi con trong chuỗi khác.

Ví dụ:

=SUBSTITUTE(A21, "H", "J", 2)

TEXT(value, format_text): Định dạng một giá trị dưới dạng chuỗi, sử dụng định dạng được chỉ định.

Ví dụ: Định dạng chuỗi số thành tiền tệ:

=TEXT(A22, "$#,##0.00")

Kết quả khi sử dụng các hàm này như hình dưới:

Ưu điểm của Excel so với các phần mềm tin học văn phòng khác là khả năng thực hiện các phép toán phức tạp và tính toán số học bằng hàm. Bạn có thể tham khảo một số hàm tính toán bên nâng cao bên dưới:

SUMIF(range, criteria, [sum_range]): Tính tổng các ô trong một phạm vi nếu các điều kiện được thỏa mãn.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …): Tính tổng các ô trong một phạm vi dựa trên nhiều điều kiện.

SUMPRODUCT(array1, [array2, …]): Nhân các phần tử tương ứng của các mảng với nhau và trả về tổng của các kết quả.

Ví dụ: Giả sử tôi có các dữ liệu sau đây:

Hãy tính tổng của các giá trị trong phạm vi B1:B5 nếu giá trị tương ứng trong A1:A5 lớn hơn 1, và giá trị trong B1:B5 lớn hơn 3.

=SUMIF(A1:A5, ">1", B1:B5) + SUMIFS(B1:B5, A1:A5, ">1", B1:B5, ">3")

Trong đó:

Một yêu cầu khác, tính tổng của tích các phần tử tương ứng của A1:A5 và B1:B5, nếu giá trị tương ứng trong A1:A5 lớn hơn 1 và giá trị tương ứng trong B1:B5 lớn hơn 3.

Ta dùng công thức:

=SUMPRODUCT((A1:A5>1)*(B1:B5>3), C1:C5)

Và thu được kết quả là:

ABS(number): Giá trị tuyệt đối của số.

Công thức:

=ABS(-5)

Kết quả: Giá trị tuyệt đối của -5 là 5.

ROUND(number, num_digits): Làm tròn số với số chữ số thập phân xác định.

Công thức:

=ROUND(3.5678, 2)

Kết quả: Số 3.5678 sẽ được làm tròn thành 3.57.

MOD(number, divisor): Trả về phần dư của một phép chia.

Ví dụ:

=MOD(10, 3)

Kết quả: Khi chia 10 cho 3, ta được phần nguyên là 3 và phần dư là 1.

RAND(): Trả về một số ngẫu nhiên từ 0 đến 1.

=RAND()

Kết quả: Excel sẽ trả về một số ngẫu nhiên khác nhau trong phạm vi từ 0 đến 1.

RANDBETWEEN(bottom, top): Trả về một số ngẫu nhiên trong phạm vi từ nhỏ đến lớn.

=RANDBETWEEN(10, 20)

Kết quả: Excel sẽ trả về một số ngẫu nhiên khác nhau trong phạm vi từ 10 đến 20.

INT(number): Trả về phần nguyên của một số.

=INT(5.8)

Kết quả: Phần nguyên của 5.8 là 5.

PRODUCT(number1, number2, …): Trả về tích của các số.

=PRODUCT(2, 3, 4)

Kết quả: Tích của 2, 3, và 4 là 24.

Bạn có thể xem bảng kết quả minh họa sau:

ROUNDUP(number, num_digits): Làm tròn một số lên đến một số chữ số cụ thể.

Ví dụ:

=ROUNDUP(3.14159, 2)

ROUNDDOWN(number, num_digits): Làm tròn một số xuống đến một số chữ số cụ thể.

Ví dụ:

=ROUNDDOWN(3.14159, 2)

MROUND(number, multiple): Làm tròn số tới số gần nhất là bội số của một số khác.

=MROUND(15, 5)

Kết quả: 15 (vì 15 là bội số của 5)

FLOOR(number, significance): hàm sẽ làm tròn số xuống đến bội số gần nhất của một số khác.

=FLOOR(26, 5)

Kết quả: 25 (vì 25 là bội số của 5)

CEILING(number, significance): hàm dùng để làm tròn số lên đến bội số gần nhất của một số khác.

=CEILING(17, 5)

Kết quả: 20 (vì 20 là bội số của 5)

TRUNC(number, [num_digits]): dùng để cắt bỏ phần thập phân của số.

=TRUNC(3.14159, 2)

Kết quả: 3.14

EVEN(number): hàm làm tròn số lên đến số chẵn gần nhất.

=EVEN(7)

Kết quả: 8

ODD(number): Hàm tròn số lên đến số lẻ gần nhất.

=ODD(6)

Kết quả: 7

Bạn có thể xem hình minh họa dưới đây:

Khi làm việc với dữ liệu lớn, việc tìm kiếm và trích xuất thông tin thủ công sẽ tốn rất nhiều thời gian của bạn. Chính vì điều đó, bạn cần có các hàm dò tìm để hỗ trợ bạn dò thông tin theo điều kiện và tiêu chí cụ thể và nhanh chóng:

Ví dụ: Ta có bảng dữ liệu sau:

Hãy sử dụng các hàm dò tìm để xử lý dữ liệu:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]): Tìm kiếm một giá trị trong một phạm vi và trả về giá trị tương ứng từ cột được chỉ định.

Yêu cầu: Tìm giá trị tương ứng với 90 trong cột A và trả về giá trị tương ứng ở cột B.

=VLOOKUP(90, A1:D3, 2, FALSE)

Kết quả: Kết quả sẽ là 100.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]): Tìm kiếm một giá trị trong một phạm vi và trả về giá trị tương ứng từ hàng được chỉ định.

Yêu cầu: Tìm giá trị tương ứng với 20 trong hàng 1 và trả về giá trị tương ứng ở hàng 2.

=HLOOKUP(20, A1:D3, 2, FALSE)

Kết quả: Kết quả sẽ là 60.

MATCH(lookup_value, lookup_array, [match_type]): Tìm kiếm giá trị trong một mảng và trả về vị trí của giá trị đó.

Yêu cầu: Tìm vị trí của giá trị 50 trong cột A.

=MATCH(50, A1:A3, 0)

Kết quả: Vị trí của giá trị 50 trong cột A là 2 (nằm ở hàng thứ 2).

INDEX(array, row_num, [column_num]): Truy xuất giá trị từ một phạm vi dựa trên số hàng và số cột xác định.

Yêu cầu: Truy xuất giá trị từ phạm vi A1:D3 dựa trên hàng thứ 2 và cột thứ 3.

=INDEX(A1:D3, 2, 3)

Kết quả: Giá trị tại hàng 2, cột 3 là 70.

CHOOSE(index_num, value1, [value2], …): Chọn giá trị từ một danh sách các giá trị dựa trên chỉ số.

Yêu cầu: Chọn giá trị từ danh sách [100, 200, 300] dựa trên chỉ số 2.

=CHOOSE(2, 100, 200, 300)

Kết quả: Chọn giá trị thứ 2 từ danh sách là 200.

SEARCH(find_text, within_text, [start_num]): Tìm kiếm một chuỗi con trong chuỗi khác và trả về vị trí của chuỗi con đó.

Yêu cầu: Tìm vị trí của chuỗi con “20” trong chuỗi “10 20 30”.

=SEARCH("20", "10 20 30")

Kết quả: Vị trí của chuỗi con “20” trong chuỗi “10 20 30” là 4.

Giả sử bạn có danh sách điểm số của sinh viên như sau:

Yêu cầu hãy tính:

Tính trung bình cộng các điểm trong cột B.

Tính trung bình cộng các điểm trong cột C chỉ khi điểm tương ứng trong cột A lớn hơn hoặc bằng 7.

Tìm điểm cao nhất trong danh sách.

Tìm điểm thấp nhất trong danh sách.

Đếm số lượng điểm trong cột D.

Đếm số lượng ô không rỗng trong cột C.

Đếm số lượng điểm trong cột A lớn hơn hoặc bằng 7.

Xếp hạng điểm 9 trong danh sách.

AVERAGE(number1, number2, …): Trung bình cộng của các số.

=AVERAGE(D2:D4)

Kết quả: Trung bình cộng các điểm trong cột B là 7.

AVERAGEIF(range, criteria, [average_range]): Tính trung bình cộng của các ô trong một phạm vi chỉ khi điều kiện được thỏa mãn.

=AVERAGEIF(B2:B4, ">=7", C2:C4)

Kết quả: Trung bình cộng các điểm trong cột C chỉ khi điểm tương ứng trong cột A lớn hơn hoặc bằng 7 là 8.

MAX(number1, number2, …): Giá trị lớn nhất trong danh sách.

=MAX(B2:B4)

Kết quả: Điểm cao nhất trong danh sách là 9.

MIN(number1, number2, …): Giá trị nhỏ nhất trong danh sách.

=MIN(B2:B4)

Kết quả: Điểm thấp nhất trong danh sách là 7.

COUNT(number1, number2, …): Đếm số lượng giá trị trong danh sách.

=COUNT(D2:D4)

Kết quả: Có 3 điểm trong cột D.

COUNTA(value1, value2, …): Đếm số lượng giá trị không rỗng trong danh sách.

=COUNTA(C2:C4)

Kết quả: Có 3 ô không rỗng trong cột C.

COUNTIF(range, criteria): Đếm số lượng ô trong một phạm vi thỏa mãn một điều kiện cụ thể.

=COUNTIF(D2:D4, ">=7")

Kết quả: Có 2 điểm trong cột D lớn hơn hoặc bằng 7.

RANK(number, ref, [order]): Xếp hạng của một số trong một danh sách.

=RANK(9, B2:B4)

Kết quả: Xếp hạng điểm 9 trong danh sách là 1.

Bạn có thể xem hình minh họa bên dưới:

Các hàm Excel này còn được gọi là hàm logic giúp bạn phân tích các dữ liệu dựa trên các điều kiện logic.

Giả sử bạn có bảng điểm của bạn học sinh như sau:

Hãy tính:

Kiểm tra nếu điểm Sinh lớn hơn 8 và điểm Toán lớn hơn hoặc bằng 7, trả về “Học sinh giỏi”, ngược lại trả về “Học sinh khá”.

Kiểm tra nếu có ít nhất một môn nào đó đạt điểm 9 trở lên, trả về “Học sinh xuất sắc”, ngược lại trả về “Học sinh giỏi”.

Đảo ngược kết quả của điều kiện: nếu điểm Lý nhỏ hơn 7, trả về “Cần cố gắng hơn”, ngược lại trả về “Tiếp tục giữ vững”.

Ta dùng các hàm sau:

IF(logical_test, value_if_true, value_if_false): Thực hiện một kiểm tra logic và trả về giá trị dựa trên kết quả kiểm tra.

AND(logical1, [logical2], …): Kiểm tra nhiều điều kiện và trả về TRUE nếu tất cả điều kiện đều đúng.

OR(logical1, [logical2], …): Kiểm tra nhiều điều kiện và trả về TRUE nếu ít nhất một điều kiện đúng.

NOT(logical): Đảo ngược kết quả của một kiểm tra logic.

Để giải bài toán, chúng ta kết hợp các hàm trên thành công thức sau:

=IF(AND(A2>8, B2>=7), "Học sinh giỏi", "Học sinh khá")

Kết quả: Kết quả sẽ là “Học sinh khá” (vì điểm Sinh không lớn hơn 8).

2. Hàm IF và OR

=IF(OR(A2>=9, B2>=9, C2>=9, D2>=9), "Học sinh xuất sắc", "Học sinh giỏi")

Kết quả: Kết quả sẽ là “Học sinh giỏi” (vì không có môn nào đạt điểm 9 trở lên).

3. NOT

=IF(NOT(D2<7), "Tiếp tục giữ vững", "Cần cố gắng hơn")

Kết quả: Kết quả sẽ là “Tiếp tục giữ vững” (vì điểm Lý là 8.0, không nhỏ hơn 7).

IFERROR(value, value_if_error): Hàm trả về một giá trị được chỉ định nếu một lỗi xảy ra, ngược lại trả về giá trị khác.

Ví dụ:

=IFERROR(A1/B1, "Lỗi chia cho 0")

Kết quả: Nếu lỗi xảy ra khi chia A1 cho B1, hàm này sẽ trả về chuỗi “Lỗi chia cho 0”

HYPERLINK(link_location, [friendly_name]): Hàm sẽ tạo một liên kết tới một địa chỉ web hoặc tệp trong máy tính.

Ví dụ:

=HYPERLINK("https://gitiho.com/", "Click here")

OFFSET(reference, rows, cols, [height], [width]): hàm trả về một tham chiếu dịch chuyển từ một ô hoặc phạm vi ô hiện tại.

=OFFSET(A1, 1, 2)

Lấy A1 làm ô gốc, thì kết quả sẽ là giá trị của ô C2 (dịch xuống 1 hàng và qua phải 2 cột).

YEARFRAC(start_date, end_date, [basis]): hàm trả về số năm và thập kỷ tương đương giữa hai ngày.

=YEARFRAC(DATE(2020, 1, 1), DATE(2022, 6, 30))

Kết quả: 2.5 (tương đương 2 năm và nửa năm)

DCOUNTA(database, field, criteria): Hàm dùng để đếm số bản ghi không rỗng trong một cơ sở dữ liệu dựa trên các tiêu chí cung cấp.

=DCOUNTA(A1:C5, "Age", B1:B5)

Kết quả: 2 (có 2 bản ghi có giá trị trong cột “Age”)

DGET(database, field, criteria): Trích xuất một giá trị duy nhất từ một cơ sở dữ liệu dựa trên các tiêu chí cung cấp.

=DGET(A1:C5, "Age", A1:A2)

Kết quả: Công thức trên dùng để trích xuất tuổi của “Chung Đại” là 31.

DSUM(database, field, criteria): Dùng để tính tổng của các giá trị trong một cột cơ sở dữ liệu dựa trên các tiêu chí cung cấp.

=DSUM(A1:C5, "Age", C1:C2)

Kết quả: Tính tổng tuổi của của người thuộc thành phố NY (tức là số tuổi của Chung Đại – 31)

Ví dụ: Bạn đang đầu tư vào một dự án có dòng tiền như sau:

Năm 0: -100,000 (đầu tư ban đầu)

Năm 1: 30,000

Năm 2: 40,000

Năm 3: 50,000

NPV(rate, value1, [value2], …): Dùng để tính giá trị hiện tại của một chuỗi các dòng tiền (tiền vào và tiền ra) dựa trên một tỷ lệ lãi suất.

Với tỷ lệ lãi suất là 5%, công thức NPV sẽ là:

=NPV(0.05, -100000, 30000, 40000, 50000)

IRR(values, [guess]): dùng khi bạn muốn tìm tỷ lệ lãi suất nội bộ mà NPV của chuỗi các dòng tiền là 0.

=IRR(B1:B5)

PV(rate, nper, pmt, [fv], [type]): dùng nếu bạn muốn tính giá trị hiện tại của một khoản tiền trong tương lai.

Ví dụ: Bạn có thể tính giá trị hiện tại của 50,000 đồng sau 5 năm tới (với lãi suất là 6%)

=PV(0.06, 5, 0, 50000)

FV(rate, nper, pmt, [pv], [type]): Hàm dùng để tính giá trị tương lai của một khoản tiền dựa trên lãi suất và số năm.

Ví dụ: Nếu bạn muốn biết giá trị tương của 10,000,000 đồng sau 10 năm với lãi suất 8%, bạn có thể dùng công thức sau:

=FV(0.08, 10, 0, -10000000)

Kết quả khi sử dụng các công thức trên như sau:

Hy vọng qua bài viết tổng hợp các hàm Excel nâng cao này, bạn có thể áp dụng chúng vào thực tế công việc của mình. Việc sử dụng chúng không chỉ hỗ trợ bạn phân tích, xử lý các dữ liệu và yêu cầu phức tạp mà còn giúp bạn tiết kiệm thời gian hơn đấy.

KHÓA HỌC EXCEL ONLINE HOÀN TOÀN MIỄN PHÍ

Khóa học với hơn 14000 HỌC VIÊN đang học và đánh giá trung bình 4.76 SAO

0 thảo luận

Excel là một công cụ phổ biến và hữu ích cho công việc kế toán. Đặc biệt để hiểu được đặc điểm dữ liệu, cách tính toán và lập các báo cáo kế toán thì hầu hết đều phải tìm hiểu trên Excel. Trong bài viết này Học Excel Online sẽ tổng hợp lại cho các bạn những hàm Excel thường sử dụng trong kế toán tổng hợp. Cụ thể là:

Xem nhanh

Cú pháp: COUNTIF(range, criteria)

Tác dụng: Đếm số lần xuất hiện của 1 nội dung trong 1 vùng

Ứng dụng: Đếm số thứ tự dòng nghiệp vụ phát sinh trong sổ Nhật ký chung theo số chứng từ

Trong đó:

Cú pháp: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Tác dụng: Tìm kiếm, tra cứu thông tin có liên quan tới 1 đối tượng trong 1 bảng tính, bảng danh sách.

Ứng dụng:

a. Tìm tên tài khoản dựa theo số tài khoản trên sổ cái, sổ chi tiết

b. Tìm kiếm thông tin của khách hàng, nhà cung cấp dựa theo mã khách hàng khi theo dõi chi tiết cho các tài khoản 131, 331 trong sổ Nhật ký chung

Ngoài ra để thay thế cho hàm Vlookup http://getzonedup.com giúp tìm kiếm, tra cứu nhanh hơn, hiệu quả hơn thì chúng ta có thể sử dụng hàm Index + hàm Match

(Xem thêm: Vì sao sử dụng hàm Index + Match tốt hơn hàm Vlookup)

Cấu trúc: SUMIF(range, criteria, [sum_range])

Tác dụng: Tính tổng số phát sinh theo 1 điều kiện

Ứng dụng:

Tính số phát sinh của các tài khoản trong bảng cân đối số phát sinh

Kết chuyển số dư cuối kỳ của các tài khoản trong sổ Nhật ký chung

Nâng cao: Có thể sử dụng hàm SUMIFS để tính tổng theo nhiều điều kiện hơn so với hàm SUMIF. Ví dụ như tính số phát sinh bên Nợ của Tài khoản 111 trong thời gian từ 01/01 đến 31/03 (chỉ tính cho 3 tháng trong khi sổ NKC có dữ liệu đủ 12 tháng). Khi đó có thể kiểm tra bảng CDPS cho bất kỳ khoảng thời gian nào đều được.

Cấu trúc: TEXT(value, format_text)

Tác dụng: Trình bày lại 1 giá trị (số hoặc ngày tháng) vào trong 1 đoạn text mà vẫn giữ được định dạng của loại dữ liệu đó

Ứng dụng:

Viết lại thông tin liên quan tới thời gian lập các báo cáo kế toán tổng hợp

Còn rất nhiều hàm nữa hay sử dụng trong kế toán tổng hợp. Mời các bạn đón xem tiếp trong các phần tiếp theo của bài viết này nhé.

Tổng hợp những hàm Excel thường sử dụng trong kế toán tổng hợp – Phần 2

Để có thể có thêm nhiều kiến thức về kế toán tổng hợp, sử dụng excel trong kế toán tổng hợp, mời bạn tham gia khóa học

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. Học Viện APT giới thiệu 6 hàm thường dùng nhất trong nghiệp vụ kế toán kiểm toán.

1. HÀM TÍNH TỔNG (SUMPRODUCT, SUMIF, SUMIFS)

a. Hàm SUMPRODUCT

Cú pháp: = SUMPRODUCT(mảng 1,mảng 2, …,mảng n)

Công dụng: Nhân các thành phần tương ứng trong các mảng đã cho và trả về tổng của các tích số này.

Lưu ý:

Các đối số mảng phải có cùng kích thước. Nếu không, hàm SUMPRODUCT trả về giá trị lỗi #VALUE!
Hàm SUMPRODUCT coi các mục của mảng không có dạng số là số không.

Ví dụ: Tổng VAT = 1,000,000 *5% + 2,000,000*10% + 3,000,000 *15%

b. Hàm SUMIF

Cú pháp: =SUMIF(Vùng điều kiện,Điều kiện,Vùng tính tổng)

Công dụng: Tính tổng các ô có giá trị số trong vùng tính tổng mà có ô tương ứng cùng một hàng thuộc vùng điều kiện thỏa điều kiện đặt ra. Điều kiện phải được miêu tả dưới dạng chuỗi (đặt điều kiện trong “dấu ngoặc kép”) và bắt đầu bởi các toán tử >,>=,<,<=,=,<>.

Lưu ý:

Nếu vùng điều kiện trùng với vùng tính tổng, chúng ta chỉ không cần phải nhập vùng tính tổng vào công thức. Khi đó, cú pháp của hàm SUMIF sẽ trở thành như sau: Cú pháp: =SUMIF(Vùng điều kiện,Điều kiện). Ví dụ: tính tổng doanh số khách hàng doanh thu trên 1 tỷ.

c. Hàm SUMIFS

Cú pháp: =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 n, điều kiện n)

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

Lưu ý: Sumifs có nhiều ưu điểm hơn Sumif: trả về vùng dữ liệu sum khi làm truy soát, (Ctrl+[), thao tác trên cùng một worksheet

2. HÀM DÒ TÌM (VLOOKUP, HLOOKUP)

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

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

Kiểu dò tìm = 0 hoặc FALSE: dò tìm chính xác, nếu không tìm thấy sẽ trả về lỗi #N/A.
Kiểu dò tìm = 1 hoặc TRUE: dò tìm gần đúng, khi đó hàm sẽ lấy giá trị lớn nhất gần bằng giá trị dò tìm nhưng nhỏ hơn giá trị dò tìm. Dữ liệu ở cột dò tìm phải được sắp xếp theo thứ tự tăng dần.
Ngược lại với hàm VLOOKUP là hàm HLOOKUP (Horizontal lookup), tức là dò tìm theo chiều ngang.

Ví dụ: hlookup = 100 & vlookup = 100

3. HÀM XỬ LÝ CHUỖI (LEFT, RIGHT, MID, LEN, TRIM)

a. Hàm MID()

– Cấu trúc của hàm Mid() trong Excel MID(Chuỗi ,Vị trí bắt đầu, [Số ký tự])

– Công 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 Giải thích:

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(“Excel thuc hanh kiem toan”,7,9) = thuc hanh

Hàm Mid() sẽ lấy ra 9 ký tự từ vị trí bắt đầu là số 7 (chữ t) của chuỗi “Excel thuc hanh kiem toan”.

b. Hàm LEN()

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

– Chức năng: Hàm Len() dùng để đếm chiều dài (số ký tự) của chuỗi (Text)

Giải thích:

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(“ZaloPay”) = 7

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

c. Hàm LEFT()

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

– Chức năng: để lấy ra ký tự bên trái chuỗi

– Ví dụ:

=Left(“ZALOPAY”,4)=ZALO

d. Hàm MID

– Cấu trúc: =MID(chuỗi,số ký tự bắt đầu, tổng số ký tự muốn lấy)
– Chức năng: lấy các ký tự nằm « giữa » chuỗi
– Ví dụ:

=MID(“2018/05/19”,6,2) sẽ trả kết quả là 05

e. Hàm TRIM

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

– Chức năng: Loại bỏ các khoảng trắng thừa trong chuỗi.

– Ví dụ:

=TRIM(“ Zalo Pay ”) sẽ cho kết quả là ZaloPay.

5. HÀM ĐIỀU KIỆN (IF, IFERROR)

a. Hàm IF

Cú Pháp: IF (điều kiện, giá trị 1, giá trị 2)

Ý Nghĩa: Nếu như “điều kiện” đúng thì kết quả hàm trả về là “giá trị 1”, ngược lại trả về “giá trị 2”.

b. Hàm IFERROR

Cú pháp: = IFERROR(giá trị, giá trị nếu lỗi)

Công dụng: Nếu biểu thức giá trị không tạo ra một lỗi, IFERROR() trả về kết quả của biểu thức; còn nếu không, nó sẽ trả về giá trị nếu lỗi (là chuỗi rỗng hoặc một thông báo lỗi).

c. Kết hợp IFERROR và VLOOKUP

=IFERROR(VLOOKUP(giá trị tìm, vùng tìm 1, số cột tìm 1,0),VLOOKUP(giá trị tìm, vùng tìm 2, số cột tìm 2,0)

6. HÀM ĐẾM DỮ LIỆU (COUNT, COUNTA, COUNTIF)

a. Hàm COUNT

– Cú pháp: COUNT(Value1, Value2, …)

– Chức năng: Hàm này dùng để đếm các ô chứa dữ liệu kiểu số trong dãy

Với các tham số: Value1, Value2… là mảng hay dãy dữ liệu.

b. Hàm COUNTA

– Cú pháp: COUNTA(Value1, Value2, …)

– Chức năng: Đếm tất cả các ô chứa dữ liệu.

c. Hàm COUNTIF

Cú pháp: COUNTIF(Range, Criteria)

Chức nă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.

Các tham số: – Range: Dãy dữ liệu mà bạn muốn đếm. – Criteria: Là tiêu chuẩn cho các ô được đếm.

d. Hàm COUNTIFS

– Cú pháp: COUNTIFS(range1, criteria1, range2, criteria2, …)

– Chức năng: Hàm này dùng để đếm tổng số ô thỏa điều kiện yêu cầu

Sử dụng ứng dụng Excel thì làm việc với HÀM trong Excel là việc rất thường xuyên. Chúng giúp thực hiện tính toán dữ liệu trực tiếp trên bảng tính nhanh và tiện lợi hơn.

Các hàm Excel là những công thức được định nghĩa trước và đã có sẵn trong Excel, sẽ rất có ích với các bạn thường xuyên phải làm việc trên bảng tính Excel, đặc biệt là trong lĩnh vực kế toán, hành chính nhân sự.

Bài học

Tài liệu cho khóa học
Tải tài liệu cho khóa học	00:00:00
Hàm logic, tính toán, thống kê
Hướng dẫn nhập hàm, công thức tính toán	00:06:30
Phân biệt các loại địa chỉ sử dụng trong công thức	FREE	00:07:58
Hàm tính toán SUM, MAX, MIN, AVERAGE	00:05:32
Hàm logic AND	00:02:55
Hàm logic OR	00:03:06
Hàm logic NOT	00:02:31
Hàm rẽ nhánh IF	00:12:49
Hàm chọn trường hợp CHOOSE	00:05:24
Hàm đếm COUNT, COUNTA, COUNTBLANK	00:04:46
Hàm đếm có điều kiện COUNTIF	00:06:20
Hàm tính tổng có điều kiện SUMIF	00:07:15
Hàm tính trung bình có điều kiện AVERAGEIF	00:04:06
Hàm đếm có nhiều điều kiện COUNTIFS	00:12:16
Hàm tính tổng có nhiều điều kiện SUMIFS	00:06:13
Hàm tính trung bình có nhiều điều kiện AVERAGEIFS	00:05:13
Hàm tính toán dữ liệu ở các worksheet khác nhau	00:08:15
Hàm tính toán sử dụng ký tự đặc biệt	00:14:21
Hàm làm tròn số liệu ROUND, ROUNDUP, ROUNDOWN	00:05:16
Hàm lấy số dư, lấy phần nguyên MOD, INT	00:09:58
Hàm xử lý chuỗi ký tự
Hàm đếm số lượng ký tự trong chuỗi LEN	00:02:44
Hàm xử lý ký tự viết hoa, viết thường UPPER, LOWER, PROPER	00:02:34
Hàm tách chuỗi ký tự LEFT, MID, RIGHT	00:06:05
Hàm nối chuỗi ký tự CONCATENATE, &	00:05:15
Hàm chuẩn hoá chuỗi ký tự CLEAN, TRIM	00:06:10
Hàm chuyển chữ số thành số VALUE	00:03:08
Hàm chuyển hóa dữ liệu về dạng chữ TEXT	00:06:27
Hàm dò tìm ký tự trong chuỗi FIND, SEARCH	00:08:59
Hàm thay thế ký tự trong chuỗi REPLACE, SUBSTITUTE	00:06:57
Hàm so sánh hai chuỗi ký tự EXACT	00:02:08
Đếm số lượng một ký tự nào đó trong chuỗi	00:04:32
Sử dụng hàm xử lý bài toán tách họ tên (Phần 1)	00:13:37
Sử dụng hàm xử lý bài toán tách họ tên (Phần 2)	00:06:32
Hàm xử lý thời gian
Hàm lấy thời gian hiện tại TODAY, NOW	00:01:39
Hàm tách các đơn vị thời gian DAY, MONTH, YEAR, HOUR, MINUTE, SECOND	00:02:15
Hàm nhập thời gian chuẩn xác DATE, TIME	00:07:05
Hàm xác định chỉ số ngày trong 1 tuần WEEKDAY	00:11:14
Hàm xác định số ngày làm việc giữa 2 thời điểm NETWORKDAYS	00:05:40
Hàm xác định số ngày làm việc giữa 2 thời điểm với ngày cuối tuần tuỳ chỉnh NETWORKDAYS.INTL	00:10:57
Hàm xác định thời điểm sau/trước một số ngày làm việc nhất định so với mốc WORKDAY	00:04:41
Hàm xác định thời điểm sau/trước một số ngày làm việc nhất định so với mốc (cuối tuần tuỳ chỉnh) WORKDAY.INTL	00:10:43
Hàm tìm khoảng cách giữa 2 thời điểm DATEDIF	00:14:57
Hàm xác định ngày cuối tháng EOMONTH	00:02:38
Xác định năm nhuận	00:07:42
Hàm dò tìm, tham chiếu
Hàm dò tìm LOOKUP	00:10:44
Hàm dò tìm theo cột VLOOKUP	00:22:45
Hàm dò tìm theo hàng HLOOKUP	00:07:49
Hàm trả về vị trí dữ liệu trong tham chiếu MATCH	00:11:28
Hàm trả về giá trị của một ô trong vùng dò tìm INDEX	00:08:34
Kết hợp dò tìm INDEX MATCH	00:13:20
Tìm giá trị khác 0 đầu tiên	00:16:05
Xác định tháng đầu tiên mất doanh thu	00:28:43
Hàm dịch chuyển tham chiếu đến vị trí khác OFFSET	00:21:47
Hàm biến dữ liệu văn bản thành vùng tham chiếu INDIRECT	00:06:32
Tuỳ chọn, thiết lập dành cho công thức tính toán
Thiết lập hiển thị công thức tính toán	00:02:55
Kiểm tra lỗi công thức tính toán	00:07:41
Thiết lập cửa sổ theo dõi công thức	00:04:30
Những thiết lập cho nhóm Formulas Auditing	00:05:39
Thiết lập tùy chọn tính toán Calculation Options	00:05:35
Tính toán vòng lặp – Những tùy biến thật lạ trong Excel	00:14:19

Đánh giá khóa học

Không có đánh giá nào cho khóa học này.

101 Học viên tham gia

Một kế toán viên cần thành thạo nhiều nhóm hàm Excel từ cơ bản đến nâng cao. Hãy cùng EDUSA tìm hiểu ngay 5 nhóm hàm Excel nâng cao cho kế toán mà bạn nên biết ngay sau đây nhé.

IF kết hợp với AND / OR

Công thức: = IF (VÀ (C2> = C4, C2 <= C5), C6, C7)

Bất cứ ai dành nhiều thời gian trong các loại mô hình tài chính đều biết rằng các công thức IF lồng nhau có thể là một cơn ác mộng. Kết hợp IF với hàm AND hoặc OR có thể là cách tuyệt vời để giữ hoặc các công thức dễ kiểm toán hơn và cho người dùng khác hiểu. Trong ví dụ dưới đây, bạn sẽ thấy cách chúng tôi sử dụng các hàm riêng lẻ kết hợp để tạo ra một công thức nâng cao hơn.

Để biết chi tiết về cách thực hiện chức năng này trong Excel, vui lòng xem hướng dẫn miễn phí của chúng tôi về cách sử dụng IF với AND / OR .

Kết luận

Hy vọng với những hàm Excel nâng cao cho kế toán được chia sẻ kinh nghiệm trong bài viết hôm nay, bạn sẽ phát triển hơn trong công việc của mình. Để tìm việc làm kế toán uy tín, lương cao, đừng quên truy cập EDUSA để tiếp cận nhiều hơn đến những hàm Excel khác để phục vụ cho công việc của bạn nhé.

Hướng dẫn được thực hiện trên Laptop hệ điều hành Windows 10 với phiên bản Excel 2013. Bạn cũng có thể thực hiện tương tự trên các sản phẩm Excel 2007, 2010, 2013, 2016, 2109 và trên các dòng laptop có cùng hệ điều hành hay dòng MacOS có cùng thao tác khác.

Thống kê là hệ thống các phương pháp thu thập, tổng hợp, trình bày và tính toán nhằm phục vụ cho quá trình phân tích và đưa ra kết luận.

Ứng dụng của các hàm thống kê trong Excel:

Giúp bạn tính toán dễ dàng các giá trị như: Tính tổng, giá trị lớn nhất, giá trị nhỏ nhất,…
Thao tác chuyên nghiệp hơn khi sử dụng Excel.
Kết hợp được với nhiều hàm khác để hỗ trợ công việc.

XEM NGAY bộ phần mềm chính hãng, sử dụng thả ga tại Thế Giới Di Động:

Hàm COUNT là hàm dùng để đếm ô trong một vùng hay trong toàn bộ bảng dữ liệu.

Cú pháp hàm COUNT: =COUNT(Value1, Value2,….).

Trong đó:

Value 1: Là giá trị đầu tiên để tham chiếu hoặc vùng chọn để đếm ô giá trị.
Value 2: Là tham số không bắt buộc, chỉ có thể để đếm tối đa 255 tham số, ô tham chiếu hoặc vùng chọn bổ sung mà bạn muốn đếm giá trị ô.

Hàm COUNTA là hàm đếm những ô tham chiếu có chứa dữ liệu trong một phạm vi nhất định.

Cú pháp hàm COUNTA: =COUNTA(Value1, [value2], [value3],…)

Trong đó:

Value1: Là đối số bắt buộc và là vùng dữ liệu cần đếm.
Value2 và Value3: Là các tùy chọn và tối đa 255 đối số.

Hàm COUNTIF là hàm đếm có điều kiện trong Excel và thường được dùng với những bảng dữ liệu thống kê.

Cú pháp hàm COUNTIF: =COUNTIF(range;criteria)

Trong đó:

Range: Là phần vùng muốn đếm dữ liệu bắt buộc phải có. Có thể chứa số, mảng hoặc tham chiếu có chứa số.
Criteria: Là phần điều kiện bắt buộc phải có để đếm các giá trị trong range. Có thể là số, biểu thức, tham chiếu ô hoặc chuỗi văn bản.

Hàm COUNTIFS là hàm giúp đếm số ô trong phạm vi khớp với điều kiện được cung cấp và có thể áp dụng nhiều hơn một điều kiện với nhiều hơn một phạm vi.

Cú pháp hàm COUNTIFS: =COUNTIFS(Criteria_range1,Criteria1,[Criteria_range2,Criteria2],…)

Trong đó:

range1: Là phạm vi đầu tiên để đánh giá.
criteria1: Là xác định phạm vi đầu tiên áp dụng điều kiện đầu tiên.
range2: Là phạm vi thứ hai để đánh giá, điều kiện bổ sung.
criteria2: Là xác định phạm vi áp dụng điều kiện thứ 2.

Ví dụ: Tìm số học sinh có điểm 6 cả 2 phần thi trong bảng dữ liệu sau:

Bước 1: Mình nhập hàm =COUNTIFS(C4:C8,6,D4:D8,6) vào bảng tính Excel.

Ví dụ minh họa hàm COUNTIFS

Bước 2: Sau đó bạn nhấn phím Enter để hiển thị kết quả.

Nhấn Enter để hiển thị kết quả

Hàm COUNTBLANK là hàm để đếm số các ô rỗng trong một vùng, một mảng được chọn trước.

Cú pháp hàm COUNTBLANK: COUNTBLANK(Range)

Trong đó:

Range: Là mảng được chọn để đếm số các ô rỗng trong đó.

Ví dụ: Tìm sô ô rỗng trong bảng dữ liệu sau:

Bước 1: Mình nhập hàm =COUNTBLANK(E4:E8) vào bảng tính Excel.

Ví dụ minh họa hàm COUNTBLANK

Bước 2: Sau đó bạn nhấn phím Enter để hiển thị kết quả.

Nhấn Enter để hiển thị kết quả

Hàm SUM là hàm tính tổng tất cả các số có trong dãy ô dữ liệu.

Cú pháp hàm SUM: =SUM(number1, [number2],…)

Trong đó:

Number1 và Number2 là những số hoặc dãy số sẽ tính.

Hàm SUMIF là hàm tính tổng có điều kiện được dùng khi cần tính tổng một vùng nào đó trong bảng tính excel theo điều kiện cho trước.

Cú pháp hàm SUMIF: =SUMIF(range, criteria,sum_range)

Trong đó:

Range: Là vùng được chọn có chứa các ô điều kiện.
Criteria: Là điều kiện để thực hiện hàm này.
Sum_range: Vùng cần tính tổng.

Hàm SUMIFS là hàm tính tổng các ô trong vùng, phạm vi lựa chọn thỏa mãn một hay nhiều điều kiện.

Cú pháp hàm SUMIFS: =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…)

Trong đó:

Sum_range: :Là các ô cần tính tổng bao gồm các số hoặc tên, phạm vi hoặc tham chiếu ô có chứa các số.
Criteria_range1: Là phạm vi cần được đánh giá bằng điều kiện.
Criteria1: Là điều kiện dưới dạng một số, biểu thức, tham chiếu ô.
Criteria_range2,criteria2,… : tùy chọn các phạm vi và điều kiện bổ sung.

Hàm AVERAGE là hàm tính trung bình trong Excel.

Cú pháp của hàm AVERAGE: =AVERAGE(number1;number2;…numbern)

Trong đó:

Number 1: Là số đầu tiên cần tính trung bình.
Number 2: Là số thứ 2 cần tính trung bình.
Number n: Số cuối cùng cần tính trung bình.

Hàm AVERAGEIF là hàm tính trung bình một điều kiện của tất cả các ô dựa trên điều kiện có sẵn.

Cú pháp hàm AVERAGEIF: =AVERAGEIF(range, criteria, [average_range])

Trong đó:

Range (Bắt buộc): Một hoặc nhiều ô để tính giá trị trung bình, bao gồm các số hoặc tên, mảng hoặc tham chiếu có chứa số.
Criteria (Bắt buộc): Điều kiện lấy các số dùng để tính trung bình. Ví dụ: Các tiêu chí có thể được biểu thị dưới dạng như 32, “32”, “>32”, “táo” hoặc B4.
Average_range (Tùy chọn): Vùng dữ liệu được chọn để dùng hàm AVERAGEIF (nếu bỏ qua thì mặc định chọn toàn bộ).

Hàm AVERAGEIFS là hàm tính giá trị trung bình nhiều điều kiện của tất cả các ô dựa trên các điều kiện có sẵn.

Cú pháp hàm AVERAGEIFS: =AVERAGEIFS(range, criteria, [average_range])

Trong đó:

Range (Bắt buộc): Một hoặc nhiều ô để tính giá trị trung bình, bao gồm các số hoặc tên, mảng hoặc tham chiếu có chứa số.
Criteria_range1 (bắt buộc), các đối số criteria_range tiếp theo (tùy chọn): Phạm vi 1 đến 127 để đánh giá các tiêu chí liên quan.
Criteria1 (bắt buộc), các đối số criteria tiếp theo (tùy chọn): 1 đến 127 tiêu chí dưới dạng số, biểu thức, tham chiếu ô hoặc văn bản xác định các ô sẽ tính giá trị trung bình.

Hàm RANK là hàm được dùng để sắp xếp, phân hạng dữ liệu, số liệu.

Cú pháp hàm RANK: =RANK(number,ref, [order])

Trong đó:

Number: Giá trị cần xếp hạng trong khối.
Ref: Danh sách, khối dữ liệu cần sắp xếp.
Order: Thứ tự cần sắp xếp, kiểu sắp xếp (tăng dần hoặc giảm dần). Trong trường hợp order = 0 (hoặc không có tham số này) thì sẽ tính từ cao xuống thấp. Còn order = 1 sẽ xếp từ thấp lên cao.

Hàm ROW là hàm giúp tìm nhanh vị trí hàng của ô đang tham chiếu hoặc hàng đầu tiên của mảng tham chiếu dễ dàng.

Cú pháp hàm ROW: =ROW([reference])

Trong đó:

Reference: Là ô hoặc phạm vi ô mà bạn muốn trả về số hàng.

Hàm ROWS là hàm giúp tính nhanh số lượng hàng có trong dữ liệu đang tham chiếu.

Cú pháp hàm ROWS: =ROWS(array)

Trong đó:

Array: Là tham chiếu đến phạm vi một ô hoặc một vùng mà bạn muốn lấy số hàng.

Hàm COLUMN là hàm giúp tìm nhanh vị trí cột của ô hoặc vùng đang tham chiếu.

Cú pháp hàm COLUMN: =COLUMN([reference])

Trong đó:

Reference: Là ô hoặc vùng mà bạn muốn trả về số cột.

Hàm COLUMNS là hàm giúp trả về số cột trong một mảng hoặc ô tham chiếu.

Cú pháp hàm COLUMNS: =COLUMNS(array)

Trong đó:

Array: Là tham chiếu đến phạm vi một ô hoặc một vùng mà bạn muốn lấy số cột.

Ví dụ: Đếm số cột có trong bảng dữ liệu sau:

Bước 1: Mình nhập hàm =COLUMNS(A3:F7) vào bảng tính Excel.

Ví dụ minh họa hàm COLUMNS

Bước 2: Sau đó bạn nhấn phím Enter để hiển thị kết quả.

Nhấn Enter để hiển thị kết quả

Hàm MIN là hàm trả về giá trị nhỏ nhất trong tập giá trị cho trước.

Cú pháp hàm MIN: =MIN (number 1, number 2, …)

Trong đó:

Number1, Number2, … Là các đối số hoặc vùng dữ liệu.

Hàm MAX là hàm trả về giá trị lớn nhất trong tập giá trị cho trước.

Cú pháp hàm MAX: =MAX(number 1, number 2, …)

Trong đó:

Number1, Number2, … Là các đối số hoặc vùng dữ liệu.

Nguyên nhân: Sử dụng sai dấu phân cách giữa các phần tử trong hàm

Cách khắc phục: Sử dụng đúng dấu phân cách hoặc chỉnh sửa lại định dạng

Nguyên nhân lỗi: Không có ký tự phụ hoặc chưa chuyển định dạng.

Nguyên nhân viết không được số 0 trong Excel

Cách khắc phục: Thêm ký tự dấu ‘ vào trước số 0 hoặc chuyển sang định dạng Text. Ở đây mình hướng dẫn thêm dấu ‘ như hình.

Khắc phục lỗi viết không được số 0 trong Excel

Mục đích là để dễ dàng thực hiện hay kiểm soát khối lượng dữ liệu trong bảng tính hiệu quả nhất.

Là lỗi dữ liệu kiểu số. Trong Excel thường có 3 nguyên nhân chính gây nên lỗi #NUM!.

Trong công thức có đối số có dữ liệu là kiểu số không phù hợp.
Dùng hàm lặp đi lặp lại dẫn đến hàm không thể tìm ra kết quả trả về.
Dùng hàm trả về kết quả là số quá lớn hoặc quá nhỏ vượt ngoài khả năng tính toán của Excel.

Là lỗi hiển thị giá trị kết quả bị lỗi trong quá trình tính toán. Phần lớn thường gặp là do quá trình nhập công thức hoặc do các ô đang tham chiếu bị lỗi.

Là lỗi phép tính khi chia cho số 0 hay sai hàm khi thực hiện phép chia cho số 0.

Là lỗi không tìm thấy giá trị hay là lỗi được trả về khi công thức không tìm thấy giá trị.

Trên đây là Tổng hợp các hàm thống kê trong Excel cực chi tiết, có bài tập minh họa dễ hiểu. Hy vọng bài viết sẽ giúp bạn thực hiện được các hàm thống kê trong công việc cũng như học tập và nếu bạn có góp ý hãy để lại bình luận bên dưới và đứng quên chia sẻ nếu thấy hữu ích bạn nhé.

Excel là một phần mềm được sử dụng phổ biến trong môi trường doanh nghiệp nhằm giúp công việc tính toán, thống kê trở lên nhanh chóng hơn. Đặc biệt là công việc kế toán khi thường xuyên phải sử dụng đến những con số và cần đảm bảo độ chính xác cao.

Tuy nhiên, không phải người kế toán nào cũng nắm được các tiện ích của phần mềm này. Chính vì vậy, trong bài viết dưới đây chúng tôi sẽ chia sẻ những tài liệu để bạn có thể tự học excel kế toán từ cơ bản đến nâng cao nhanh chóng và hiệu quả. Mời các bạn tham khảo.

>>> Xem thêm: Công việc của kế toán trưởng là gì? Những việc quan trọng của kế toán trưởng

Hàm Excel nâng cao cho kế toán – Hàm CHOOSE

Hàm Excel nâng cao CHOOSE giúp người dùng có thể tìm kiếm được giá trong một chuỗi các giá trị sẵn có tại bảng tính. Công thức hàm CHOOSE như sau:

Hàm CHOOSE: =CHOOSE(index_num, value1, value2…)

Trong đó:

Index_num: Vị trí mà giá trị cần tìm sẽ trả về. Giới hạn của Index_num sẽ nằm trong khoảng từ 1 – 2. Giá trị này phải là số nguyên, kết quả của một công thức là số nguyên. Nếu không phải số nguyên, công thức sẽ hiển thị giá trị lỗi #Value!.
Value1, Value2,…: Là giá trị trả về số 1, số 2,…

Xem thêm: Hướng dẫn sử dụng hàm Excel IFS vô cùng dễ hiểu

10 Công thức Excel Nâng cao Bạn Phải Biết

Mỗi nhà phân tích tài chính dành nhiều thời gian trong Excel hơn là họ có thể quan tâm để thừa nhận. Dựa trên nhiều năm và nhiều năm kinh nghiệm, chúng tôi đã biên soạn các công thức Excel quan trọng và tiên tiến nhất mà mọi nhà phân tích tài chính đẳng cấp thế giới đều phải biết.

INDEX MATCH

Công thức: = INDEX (C3: E9, MATCH (B13, C3: C9,0), MATCH (B14, C3: E3,0))

Đây là một thay thế nâng cao cho các công thức VLOOKUP hoặc HLOOKUP (một số hạn chế và hạn chế). INDEX MATCH là một sự kết hợp mạnh mẽ của các công thức Excel sẽ đưa phân tích tài chính của bạn và mô hình tài chính lên cấp độ tiếp theo.

Hàm INDEX trả về giá trị của một ô trong bảng dựa trên số cột và hàng.

MATCH trả về vị trí của một ô trong một hàng hoặc cột.

Dưới đây là ví dụ về các công thức INDEX và MATCH được kết hợp với nhau. Trong ví dụ này, chúng tôi tra cứu và trả về chiều cao của một người dựa trên tên của họ. Vì tên và chiều cao là cả hai biến trong công thức, chúng ta có thể thay đổi cả hai biến này!

Để được giải thích từng bước hoặc cách sử dụng công thức này, vui lòng xem hướng dẫn miễn phí của chúng tôi về cách sử dụng MATCH MATCH MATCH trong Excel .

Câu hỏi thường gặp

7.1 Thời gian học Excel tại EDUSA có linh động không?

100% thời gian học dù là online hay offline đều linh động theo lịch rảnh cá nhân của học viên, đảm bảo được sự thoải mái xuyên suốt quá trình học.

7.2 Khóa học Excel tại EDUSA có bao đậu không?

Khóa học của EDUSA đều sát với đề thi thật, bài tập và đề thi thử được cập nhật thường xuyên. Với chính sách cam kết đầu ra 100% bằng văn bản cụ thể sẽ giúp học viên yên tâm trong suốt quá trình học tại đây.

7.3 EDUSA có gì khác biệt so với các trung tâm luyện thi Excel khác hay không?

Học viên có thể hoàn toàn yên tâm và tin tưởng khi lựa chọn luyện thi Excel tại lớp. Tất cả đều được chúng tôi thể hiện qua văn bản rõ ràng khi thực hiện cam kết với học viên.

6 Kế toán tính công nợ bằng Excel như thế nào?

Nhóm công nợ đôi khi cũng khiến các chị em kế toán đau đầu. Tuy nhiên, nếu biết cách sử dụng hàm để tính toán và dò tìm công nợ thì giúp công việc của bạn trở lên dễ dàng hơn bao giờ hết.

Những hàm thường sử dụng để lập bảng tính công nợ bằng excel bao gồm:

Hàm VLOOKUP

Hàm MIN

Hàm MAX

Hàm SUMIFS

– Công dụng: Tính tổng số liệu theo điều kiện cho trước.

– Công thức: =SUMIFS(sum_range,criteria_range1,,criteria_range2,criteria2,…)

– Ví dụ: =SUMIFS(G6:G10,D6:D10,”Mua hóa chất”,G6:G10,”>2000000″) – tính tổng công nợ tiền mua hóa chất với giá trị >2000000.

Các hàm excel thông dụng nhất trong kế toán kiểm toán | SUM AVERAGE COUNT IF AND OR VLOOKUP

2 Học các hàm Excel kế toán về thống kê

Trong nhóm thống kê, các hàm tính toán được chia thành 12 hàm từ cơ bản tới nâng cao bao gồm:

Hàm SUM

– Công dụng: Dùng để tính tổng giá trị các dãy số trong vùng dữ liệu được chọn.

– Công thức: =SUM(Number1, number2,…)

– Trong đó: Number1, number2, number3,… là các số trong ô dữ liệu cần được tính tổng.

– Ví dụ: =SUM(B5:B15) – tính tổng giá trị từ ô B5 đến B15.

Hàm SUMIF

– Công dụng: Dùng để tính tổng giá trị các dãy số nhưng có điều kiện.

– Công thức: =SUMIF(Range, Criteria, Sum_range)

– Trong đó:

Range: dãy giá trị muốn tính tổng.
Criteria: điều kiện để tính tổng.
Sum_range: vùng dữ liệu cần tính tổng.

– Ví dụ: =SUMIF(F6:F10,”>10”,G6:G10) – tính tổng thành tiền của nhóm hóa chất từ ô G6 đến G10 với điều kiện số lượng >10.

Hàm AVERAGE

– Công dụng: Dùng để tính trung bình cộng của các dãy số.

– Công thức: =AVERAGE(Number1, number2,…)

– Trong đó: Number1, number2, number3,… là các số trong ô dữ liệu cần tính trung bình.

– Ví dụ: =AVERAGE(C2:E2) – tính trung bình từ ô C2 đến E2.

Hàm SUMPRODUCT

– Công dụng: Dùng để tính tích của các dãy số và tính tổng các tích đó.

– Công thức: =SUMPRODUCT(Array1, Array2, Array3…)

– Trong đó: Array1, Array2, Array3,… là các ô cần tính tích, sau đó tính tổng các tích.

– Ví dụ: =SUMPRODUCT(A3:A10, B3:B10, C3:C10).

>>> Xem ngay: Chứng chỉ kiểm toán là gì? Những lợi ích khi có chứng chỉ kế toán

Hàm MAX

– Công dụng: Trả về kết quả giá trị lớn nhất trong dãy số được nhập.

– Công thức: =MAX(Number1, number2,…)

– Trong đó: Number1, number2,… là các số trong dãy cần tìm giá trị lớn nhất.

– Ví dụ: =MAX(C3:C10) – tìm số lớn nhất từ ô C3 đến C10.

Hàm MIN

– Công dụng: Trả về kết quả giá trị nhỏ nhất trong dãy số được nhập.

– Công thức: =MAX(Number1, number2,…)

– Trong đó: Number1, number2,… là các số trong dãy cần tìm giá trị nhỏ nhất.

– Ví dụ: =MIN(D4:C15) – tìm số nhỏ nhất từ ô D4 đến D15.

Hàm LARGE

– Công dụng: Tìm giá trị lớn thứ k trong dãy số được nhập vào.

– Công thức: =LARGE(Array, k)

– Trong đó: Array là dãy các ô cần xác định và k thứ hạng muốn tìm.

– Ví dụ: =LARGER(C5:C50,3) – tìm số lớn thứ 3 trong dãy số từ ô C5 đến C50.

Hàm SMALL

– Công dụng: Tìm giá trị nhỏ thứ k trong dãy số được nhập vào.

– Công thức: =SMALL(Array, k)

– Trong đó: Array là dãy các ô cần xác định và k thứ hạng muốn tìm.

– Ví dụ: =SMALL(B2:B7,1) – tìm số nhỏ thứ 2 trong dãy số từ ô B2 đến B7.

Hàm COUNT

– Công dụng: Dùng để đếm số ô chứa dữ liệu kiểu số trong vùng được chọn.

– Công thức: =COUNT(Value1, Value2,…)

– Trong đó: Value1, Value2 là dãy các ô trong vùng chọn.

– Ví dụ: =COUNT(D4:D8) – đếm số ô có chữ số từ D4 đến D8.

Hàm COUNTA

– Công dụng: Dùng để đếm số ô chứa dữ liệu bất kỳ trong vùng được chọn.

– Công thức: =COUNTA(Value1, Value2,…)

– Trong đó: Value1, Value2 là dãy các ô trong vùng chọn.

– Ví dụ: =COUNTA(B3:B7) – đếm số ô chứa tất cả dữ liệu kể cả ô trống từ B3 đến B7.

Hàm COUNTIF

– Công dụng: Dùng để đếm số ô chứa dữ liệu có điều kiện.

– Công thức: =COUNTIF(Range, Criteria)

– Trong đó: Range là vùng dữ liệu được chọn và Criteria là điều kiện để đếm.

– Ví dụ: =COUNTIF(B2:B100, “>50”) – đếm số ô từ B2 đến B100 khi thỏa mãn điều kiện giá trị lớn hơn 50.

>>> Gợi ý: Chứng chỉ CFA là gì? Điều kiện thi chứng chỉ CFA như thế nào?

Hàm Excel nâng cao cho kế toán – Hàm SUMIF/COUNTIF

SUMIF/COUNTIF được sử dụng để thực hiện tính tổng hoặc đếm số lần lặp lại của một giá trị nào đó thỏa mãn các điều kiện đưa ra trước đó. Công thức của hàm SUMIF/COUNTIF như sau:

SUMIF: =SUMIF(range; criteria; sum_range)

Trong đó:

Range: Vùng được chọn có chứa ô điều kiện.
Criteria: Điều kiện để thực hiện hàm.
Sum_range: Vùng dữ liệu bạn cần tính tổng.

SUMIFS: =SUMIFS(sum_range; criteria_range; criteria;…)

Trong đó:

Sum_range: Các ô cần tính tổng. Bao gồm phạm vi, tên, số, tham chiếu ô có chứa số.
Criteria_range: Phạm vi cần được đánh giá bằng các điều kiện IF.
Criteria: Điều kiện tính tổng dưới dạng số, biểu thức, ô tham chiếu.

COUNTIF: =COUNTIF(range;criteria)

Trong đó:

Range: Vùng dữ liệu mà bạn muốn đếm số và là giá trị bắt buộc phải có. Vùng dữ liệu này có thể là kiểu số, ngày tháng hoặc kiểu văn bản.
Criteria: Là vùng điều kiện để thực hiện đếm dữ liệu trong vùng được chọn. Có thể là kiểu văn bản, số, biểu thức, tham chiếu ô,…

Xem thêm: Cách sử dụng hàm Excel VLOOKUP chi tiết và dễ hiểu nhất

Excel cho kế toán: PivotTable, VLOOKUP, IF, SUMIFS, ROUND, Flash Fill, Chart

7 Kết luận

Trên đây là những nội dung mà chúng tôi đã tổng hợp lại về tự học excel kế toán từ cơ bản tới nâng cao. Bất cứ công việc nào cũng cần phải học từ những thứ đơn giản nhất để tạo nền tảng cho hành trang sau này của bạn. Nắm bắt được những kiến thức này, việc học kế toán sẽ trở nên dễ dàng hơn bao giờ hết.

Chúc các bạn luôn thành công!

04:06 Chiều

–

14/02/2023

Kế toán bán hàng là vị trí không yêu cầu quá cao về mặt nghiệp vụ kế toán hay hiểu biết sâu về quy định của pháp luật nhưng vị trí này cũng cần những kỹ năng nhất định để phục vụ cho công việc. Một trong những kỹ năng quan trọng nhất đối với kế toán bán hàng đó là kỹ năng Excel.

Các công việc hàng ngày của kế toán bán hàng như theo dõi hợp đồng bán hàng, lập hóa đơn bán hàng cho khách hàng hay tổng hợp báo cáo để liên quan đến việc sử dụng Excel. Kỹ năng Excel giúp cho kế toán bán hàng tiết kiệm thời gian và nâng cao hiệu quả công việc.

Vì vậy việc học kỹ năng Excel và sử dụng thuần thục kỹ năng này rất cần thiết cho kế toán bán hàng. Hãy cùng tìm hiểu các hàm excel trong kế toán bán hàng và cách áp dụng trong công việc thực tế của kế toán bán hàng nhé.

Hàm tính tổng gồm các hàm: SUM, SUMIF, SUMPRODUCT

Hàm SUM là hàm cơ bản nhất và rất dễ sử dụng, dùng để tính tổng của nhiều số liệu.

=SUM(Number1, Number2,…)

Hàm SUMIF, SUMIFS dùng để tính tổng với những điều kiện nhất định. Kế toán bán hàng sử dụng hàm này khi cần báo cáo về việc tính doanh thu của những sản phẩm thỏa mãn một tiêu chí nào đó.

=SUMIFS(sum_range; criteria_range; criteria;…)

HÀM SUMPRODUCT được dùng để tính tổng tiền sản phẩm khi có số lượng và đơn giá của từng sản phẩm.

=SUMPRODUCT(array1, [array2], [array3], …)

Các hàm tính tổng rất đơn giản nhưng cũng rất hữu ích cho kế toán bán hàng vì việc làm các báo cáo cần số liệu tổng hợp.

Hàm dò tìm bao gồm hàm VLOOKUP và HLOOKUP.

=VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)

=HLOOKUP (lookup_value, table array, row_index_num, [range_lookup])

Hàm dò tìm có thể coi là một trong những hàm được sử dụng nhiều nhất của kế toán bán hàng nói riêng và kế toán nói chung. Hàm dò tìm được dùng khi cần so sánh, đối chiếu dữ liệu và lên các loại báo cáo.

Điểm lưu ý khi sử dụng hàm dò tìm đó là dữ liệu dùng làm căn cứ để tìm kiếm dữ liệu khác phải là duy nhất thì hàm này mới phát huy tác dụng. Khi hàm dò tìm trả về giá trị #N/A tức là không tìm kiếm được dữ liệu.

Một ví dụ về áp dụng hàm dò tìm trong làm báo cáo đó là trong danh sách doanh thu các mặt hàng rất dài, giám đốc yêu cầu bạn tính doanh thu của một số mặt hàng nhất định. Lúc này bạn phải sử dụng hàm Vlookup hoặc Hlookup để tìm được doanh thu của các mặt hàng đó.

=IF(Logical_test;Value_if_true;Value_if_false)

Hàm IF là hàm điều kiện thường được kết hợp với các hàm khác để làm các báo cáo khác nhau.

Nếu bạn phải làm báo cáo phân loại doanh thu khách hàng hoặc nhân viên kinh doanh theo từng mức doanh số khác nhau, bạn phải sử dụng hàm IF và hàm AND kết hợp.

Lưu ý khi sử dụng hàm IF đó là việc bạn phải kết hợp nhiều hàm IF với nhau, đặc biệt khi có sự xuất hiện của hàm AND thì bạn phải chú ý dùng đủ các dấu ngoặc “(“ “)” trong công thức để không bị lỗi khi dùng hàm.

Hàm này bao gồm các hàm COUNT, COUNTA, COUNTIF

=COUNT(Value1, [Value2],….)

=COUNTIF(range, criteria)

=COUNTA(value1, value2,…)

Việc sử dụng hàm đếm sẽ hỗ trợ cho việc sử dụng hàm Vlookup. Như đã nói việc sử dụng Vlookup chỉ đúng khi giá trị dùng để tìm kiếm dữ liệu là duy nhất. Vậy dùng hàm gì để đảm bảo dữ liệu không bị trùng khớp. Đó là hàm Countif. Bạn sẽ dùng hàm Countif để đếm xem có bao nhiêu dữ liệu thỏa mãn là chính nó trong dãy dữ liệu tìm kiếm. Nếu kết quả trả về là 1 thì đó là dữ liệu duy nhất nhưng nếu kết quả trả về là 2,3 hoặc nhiều hơn thì dữ liệu đó không phải là duy nhất. Bạn cần kiểm tra lại dữ liệu của mình.

Đó là một ví dụ về việc áp dụng hàm đếm.

Hàm xử lý chuỗi bao gồm các hàm LEFT, RIGHT, MID, LEN, TRIM.

=LEFT(text, [num_chars])

=RIGHT(text,num_chars)

=MID(text,m,n)

=LEN

=TRIM(text)

Hàm này hỗ trợ bạn trong việc lấy ra các chuỗi ký tự theo yêu cầu.

Các hàm này cũng hỗ trợ trong việc xử lý dữ liệu hoặc lên báo cáo.

Như hàm TRIM sẽ giúp cắt bỏ khoảng trắng giữa các ký tự. Trong quá trình đánh máy, việc đánh thừa khoảng trắng trong chuỗi ký tự là điều khó có thể tránh khỏi. Hàm TRIM sẽ xử lý điều đó.

Ứng dụng của hàm này có thể thấy trong việc sử dụng hàm Vlookup như sau. Hàm Vlookup sẽ chỉ nhận giá trị tìm kiếm duy nhất thì hàm mới xử lý được dữ liệu. Nhưng nếu có một mã khách hàng nhưng kế toán lại gõ ở một mã khách hàng có khoảng trắng và một mã khác thì không. Khi này, hàm Vlookup sẽ hiểu đây là hai mã khách hàng khác nhau. Vì vậy mà khi chạy ra báo cáo sẽ không chính xác. Do đó, trước khi sử dụng hàm Vlookup, bạn sẽ dùng hàm TRIM để cắt hết khoảng trống trong dữ liệu.

Sử dụng các hàm excel trong kế toán bán hàng là việc không dễ dàng nhưng có thể học được qua việc luyện tập và sử dụng hàng ngày.

Việc cơ bản đầu tiên trong sử dụng Excel đó là nắm đúng công thức của hàm và hiểu rõ cách sử dụng các hàm riêng lẻ. Tuy nhiên để ứng dụng tốt nhất Excel vào công việc kế toán hàng ngày, kế toán bán hàng cần phải kết hợp thuần thục, linh hoạt các hàm với nhau.

Phần mềm kế toán Kaike

Trợ thủ đắc lực cho kế toán và nhà quản trị:

Các hàm excel trong kế toán kho	Các hàm Excel trong kế toán tiền lương
Các hàm Excel trong kế toán công nợ	Các hàm Excel nâng cao trong kế toán
150 hàm Excel thường dùng và ví dụ	excel văn phòng, kế toán
Các hàm Excel dùng trong kế toán bán hàng	Khóa học Excel cho kế toán

Tải mẫu file quản lý công nợ Excel mới nhất, đơn giản, nhanh gọn

Mẫu hóa đơn bán hàng file excel. Tải mẫu hóa đơn mới nhất

Bảng hệ thống tài khoản kế toán file excel theo thông tư mới

09:30 Chiều

–

31/12/2023

09:22 Chiều

–

31/12/2023

05:01 Chiều

–

20/09/2021

11:22 Sáng

–

20/07/2021

Chào mừng bạn đến với Kaike! Vui lòng điền thông tin dưới đây để khởi tạo tài khoản

Mã OTP đã được gửi đến số điện thoại / email. Vui lòng kiểm tra tin nhắn và nhập mã OTP vào ô bên dưới

Tài khoản của bạn đã tồn tại

Vui lòng thử lại với 2 lựa chọn dưới đây!

Đăng nhập Quên mật khẩu

THÔNG BÁO

Chỉ còn 1 bước xác nhận đăng ký tài khoản! Bạn muốn hủy quá trình này?

Tiếp tục đăng ký Hủy đăng ký

Chào mừng bạn đến với Kaike! Vui lòng điền thông tin tài khoản dưới đây

OFFSET kết hợp với SUM hoặc AVERAGE

Công thức: = SUM (B4: OFFSET (B4,0, E2-1))

Hàm OFFSET không phải là đặc biệt nâng cao, nhưng khi chúng ta kết hợp nó với các hàm khác như SUM hoặc AVERAGE, chúng ta có thể tạo ra một công thức khá tinh vi. Giả sử bạn muốn tạo một hàm động có thể tổng hợp một số lượng các ô. Với công thức SUM thông thường, bạn bị giới hạn tính toán tĩnh, nhưng bằng cách thêm OFFSET, bạn có thể di chuyển tham chiếu ô xung quanh.

Làm thế nào nó hoạt động. Để làm cho công thức này hoạt động, chúng tôi thay thế ô tham chiếu kết thúc của hàm SUM bằng hàm OFFSET. Điều này làm cho công thức động và ô được tham chiếu là E2 là nơi bạn có thể cho Excel biết có bao nhiêu ô liên tiếp bạn muốn thêm. Bây giờ chúng tôi đã có một số công thức Excel nâng cao!

Dưới đây là một ảnh chụp màn hình của công thức này phức tạp hơn một chút trong hành động.

Như bạn thấy, công thức SUM bắt đầu trong ô B4, nhưng nó kết thúc bằng một biến, là công thức OFFSET bắt đầu từ B4 và tiếp tục bằng giá trị trong E2 (“3”) trừ đi một. Điều này di chuyển kết thúc của công thức tổng trên 2 ô, tổng hợp 3 năm dữ liệu (bao gồm cả điểm bắt đầu). Như bạn có thể thấy trong ô F7, tổng của các ô B4: D4 là 15 là công thức bù trừ và tổng cho chúng ta.

Tìm hiểu cách xây dựng công thức này theo từng bước trong khóa học Excel nâng cao của chúng tôi .

CHOOSE

Công thức: = CHOOSE (lựa chọn, tùy chọn1, tùy chọn2, tùy chọn3)

Hàm CHOOSE rất tốt cho phân tích kịch bản trong mô hình tài chính. Nó cho phép bạn chọn giữa một số tùy chọn cụ thể và trả lại “lựa chọn” mà bạn đã chọn. Ví dụ: hãy tưởng tượng bạn có ba giả định khác nhau về tăng trưởng doanh thu trong năm tới: 5%, 12% và 18%. Sử dụng công thức CHOOSE bạn có thể trả về 12% nếu bạn nói Excel bạn muốn lựa chọn # 2.

Đọc thêm về phân tích kịch bản trong Excel .

XNPV và XIRR

Công thức: = XNPV (tỷ lệ chiết khấu, dòng tiền, ngày tháng)

Nếu bạn là nhà phân tích làm việc trong ngân hàng đầu tư , nghiên cứu cổ phần, hoặc lập kế hoạch và phân tích tài chính ( FP & A ), hoặc bất kỳ lĩnh vực tài chính doanh nghiệp nào khác yêu cầu chiết khấu dòng tiền thì các công thức này là một phao cứu sinh!

Đơn giản chỉ cần đặt, XNPV và XIRR cho phép bạn áp dụng ngày cụ thể cho từng luồng tiền riêng lẻ đang được chiết khấu. Vấn đề với các công thức NPV và IRR cơ bản của Excel là chúng giả sử khoảng thời gian giữa dòng tiền là bằng nhau. Thường xuyên, với tư cách là một nhà phân tích, bạn sẽ có những tình huống mà dòng tiền không được tính theo thời gian đồng đều, và công thức này là cách bạn khắc phục điều đó.

Để xem chi tiết hơn, hãy xem hướng dẫn công thức IRR và XIRR miễn phí của chúng tôi cũng như hướng dẫn XNPV của chúng tôi .

SUMIF và COUNTIF

Công thức: = COUNTIF (D5: D12, ”> = 21 ″)

Hai công thức tiên tiến này là sử dụng tuyệt vời các hàm có điều kiện. SUMIF thêm tất cả các ô đáp ứng các tiêu chí nhất định và COUNTIF tính tất cả các ô đáp ứng các tiêu chí nhất định. Ví dụ: hãy tưởng tượng bạn muốn đếm tất cả các ô lớn hơn hoặc bằng 21 (độ tuổi uống rượu hợp pháp ở Hoa Kỳ) để tìm ra số chai rượu sâm banh bạn cần cho một sự kiện khách hàng. Bạn có thể sử dụng COUNTIF làm giải pháp nâng cao, như được hiển thị trong ảnh chụp màn hình bên dưới.

PMT và IPMT

Công thức: = PMT (lãi suất, số kỳ, giá trị hiện tại)

Nếu bạn làm việc trong ngân hàng thương mại , bất động sản, FP & A hoặc bất kỳ vị trí phân tích tài chính nào đề cập đến lịch trình nợ, bạn sẽ muốn hiểu hai công thức chi tiết này.

Công thức PMT cung cấp cho bạn giá trị của các khoản thanh toán bằng nhau trong suốt thời gian vay. Bạn có thể sử dụng nó cùng với IPMT (cho bạn biết các khoản thanh toán lãi suất cho cùng một loại khoản vay), sau đó thanh toán tiền gốc và tiền lãi riêng.

Dưới đây là ví dụ về cách sử dụng chức năng PMT để nhận khoản thanh toán thế chấp hàng tháng cho khoản vay thế chấp 1 triệu đô la ở mức 5% trong 30 năm.

LEN và TRIM

Công thức: = LEN (văn bản) và = TRIM (văn bản)

Đây là những công thức ít phổ biến hơn, nhưng chắc chắn rất phức tạp. Các ứng dụng này rất tuyệt vời cho các nhà phân tích tài chính cần tổ chức và thao tác với lượng lớn dữ liệu. Thật không may, dữ liệu chúng tôi nhận được không phải lúc nào cũng được tổ chức hoàn hảo và đôi khi có thể có vấn đề như khoảng trống ở đầu hoặc cuối ô

Trong ví dụ dưới đây, bạn có thể xem cách thức công thức TRIM dọn sạch dữ liệu Excel.

CONCATENATE

Công thức: = A1 & ”văn bản khác”

Kết nối không thực sự là một chức năng riêng của nó, nó chỉ là một cách sáng tạo của việc tham gia thông tin từ các tế bào khác nhau, và làm cho bảng tính năng động hơn. Đây là một công cụ rất mạnh mẽ cho các nhà phân tích tài chính thực hiện mô hình tài chính (xem hướng dẫn lập mô hình tài chính miễn phí của chúng tôi để tìm hiểu thêm).

Trong ví dụ bên dưới, bạn có thể xem cách văn bản “New York” cộng “,” được kết hợp với “NY” để tạo “New York, NY”. Điều này cho phép bạn tạo tiêu đề động và nhãn trong trang tính. Bây giờ, thay vì cập nhật ô B8 trực tiếp, bạn có thể cập nhật ô B2 và D2 một cách độc lập. Với một tập dữ liệu lớn, đây là một kỹ năng có giá trị để bạn sử dụng.

Hàm CELL, LEFT, MID và RIGHT

Các hàm Excel nâng cao này có thể được kết hợp để tạo ra một số công thức rất tiên tiến và phức tạp để sử dụng. Hàm CELL có thể trả về nhiều thông tin khác nhau về nội dung của một ô (tên, vị trí, hàng, cột, v.v.). Hàm LEFT có thể trả về văn bản từ đầu ô (trái sang phải), MID trả về văn bản từ bất kỳ điểm bắt đầu nào của ô (trái sang phải) và hàm RIGHT trả về văn bản từ cuối ô (phải sang trái).

Dưới đây là minh họa cho ba công thức này.

Để xem cách chúng có thể được kết hợp một cách mạnh mẽ với hàm CELL, chúng tôi sẽ chia nhỏ nó cho bạn từng bước trong lớp công thức Excel nâng cao của chúng ta .

Chúc bạn thành công 1

Hướng dẫn Tạo file Quản lý Xuất Nhập Tồn trên Excel chi tiết, dễ hiểu, làm được ngay

Hàm Excel nâng cao cho kế toán – Hàm IF kết hợp AND/OR/NOT

Hàm IF là một trong những hàm Excel nâng cao mà kế toán viên thường xuyên phải sử dụng, nhất là những nhân viên thực hiện công việc kế toán kho. Hàm này giúp bạn có thể tạo ra một sự so sánh logic giữa các giá trị với nhau và trả về kết quả đúng (True) và sai (False). Công thức cụ thể của hàm IF kết hợp AND/OR/NOT như sau:

IF + AND: =IF(AND(condition1, condition2,…), value_if_true, value_if_false)

Trong đó:

Condition1, condition2: Điều kiện 1, điều kiện 2,… các điều kiện để trả kết quả đúng.
Value_if_true: Giá trị trả về nếu đúng.
Value_if_false: Giá trị trả về nếu sai.

IF + OR: =IF(OR(condition1, condition2,…), value_if_true, value_if_false)

Trong đó:

Condition1, condition2: Điều kiện 1, điều kiện 2,… các điều kiện để trả kết quả đúng.
Value_if_true: Giá trị trả về nếu đúng.
Value_if_false: Giá trị trả về nếu sai.

IF + NOT: =IF(NOT(condition1, condition2,…), value_if_true, value_if_false)

Condition1, condition2: Điều kiện 1, điều kiện 2,… các điều kiện để trả kết quả đúng.
Value_if_true: Giá trị trả về nếu đúng.
Value_if_false: Giá trị trả về nếu sai.

Xem thêm: Cách dùng công thức Excel bỏ ký tự nhanh nhất

3 Học các hàm Excel kế toán về tìm kiếm

Trong nhóm tìm kiếm, có 2 hàm tính toán cơ bản bạn cần nắm vững bao gồm:

Hàm HLOOKUP

– Công dụng: Dùng để tìm kiếm dữ liệu trong một bảng theo hàng ngang và trả về kết quả dữ liệu tương ứng theo hàng dọc.

– Công thức: =HLOOKUP(Lookup Value, Table_array, Row_index_num, [range_lookup])

– Trong đó:

Lookup Value: giá trị cần tìm.
Table Array: bảng dữ liệu cần tìm.
Row_index_num: số thứ tự của hàng lấy dữ liệu.
Range lookup: nhập số 0 khi muốn tìm kết quả chính xác và số 1 tìm kết quả tương đối.

– Ví dụ: =HLOOKUP(E5,C13:F14,2,0) – tìm một giá trị bằng giá trị ở ô E5, lấy dữ liệu ở bảng tham chiếu từ ô C13 đến F14 và lấy giá trị tương ứng ở cột thứ 2.

Hàm VLOOKUP

– Công dụng: Dùng để tìm kiếm một giá trị theo cột trong bảng dữ liệu theo đúng giá trị cần tìm. Nhập số 0 nếu muốn tìm chính xác và số 1 để tìm kết quả tương đối.

– Công thức: =VLOOKUP(Lookup Value, Table_Array, Col_index_num, [range lookup])

– Trong đó:

Lookup Value: giá trị cần tìm.
Table Array: bảng dữ liệu cần tìm.
Col_index_num: vị trí cột cần lấy.
Range lookup: nhập số 0 hoặc 1.

– Ví dụ: =VLOOKUP(F6,B2:D5,2,0) – tìm một giá trị bằng giá trị ở ô F6, bảng dữ liệu cần tìm ô B2 đến D5 và lấy giá trị tương ứng ở cột thứ 2.

>>> Xem thêm: So sánh chứng chỉ CPA và chứng chỉ ACCA nên chọn loại chứng chỉ nào?

Học các hàm Excel kế toán về đặt điều kiện

Đối với nhóm về đặt điều kiện, bạn có thể sử dụng hàm IF cho công việc lập bảng lương, tính thưởng theo doanh số, thưởng theo KPIs hoặc tính thuế thu nhập cá nhân,….

Hàm IF

– Công dụng: Dùng để kiểm tra dữ liệu có thỏa mãn điều kiện cho trước và trả về kết quả đúng hoặc sai.

– Công thức: =IF(logical-test,[value_if_true],[value_if_false])

– Trong đó:

Logical-test: Điều kiện đưa ra.
Value_if_true: Trả về kết quả thỏa mãn điều kiện.
Value_if_true: Trả về kết quả không thỏa mãn điều kiện.

– Ví dụ: =IF(C2>=100.000.000,”Đạt”,”Không Đạt”).

25 phím tắt TUYỆT VỜI trong Excel bạn nên biết (25 AWESOME Excel Keyboard Shortcuts)

1 Các phím tắt thường sử dụng trong excel kế toán

Để có thể sử dụng thành thạo excel, đầu tiên bạn cần phải tìm hiểu về những phím tắt thường sử dụng trong phần mềm này. Chúng không chỉ là công cụ hỗ trợ mà còn là tuyệt chiêu giúp bạn làm việc nhanh chóng hơn.

– Ctrl + A: Chọn toàn bộ dữ liệu.

– Ctrl + B: Bôi đậm dữ liệu được chọn.

– Ctrl + C: Sao chép dữ liệu.

– Ctrl + D: Sao chép nội dung những dòng giống nhau.

– Ctrl + V: Dán dữ liệu vừa sao chép.

– Ctrl + X: Cắt một phần dữ liệu đã chọn.

– Ctrl + F: Tìm kiếm thông tin trong bảng dữ liệu.

– Ctrl + P: Đặt lệnh in.

– Ctrl + H: Thay thế dữ liệu.

– Ctrl + U: Gạch dưới văn bản.

– Ctrl + I: Chọn chữ in nghiêng.

– Ctrl + Z: Quay lại thao tác vừa thực hiện.

– Ctrl + 0: Ẩn một hoặc nhiều cột được chọn.

– Ctrl + 9: Ẩn một hoặc nhiều dòng được chọn.

– Ctrl +Y: Lặp lại thao tác vừa thực hiện.

– Alt + Enter: Xuống dòng trong ô.

– Tab: Chuyển sang ô bên phải.

– F2: Chỉnh sửa dữ liệu trong một ô tính.

– F12: Lưu trang tính.

Những phím tắt nêu trên được dân kế toán sử dụng trong công việc hàng ngày. Hãy ghi nhớ chúng và sử dụng bất cứ lúc nào bạn cần. Tiếp theo, bạn nên học về cách sử dụng các hàm excel trong kế toán để phục vụ cho việc lập sổ sách và kế hoạch tài chính của công ty.

>>> Tham khảo: Ngành kiểm toán là gì? Cơ hội nghề nghiệp ra sao?

4 Học các hàm Excel kế toán về Toán học

Các hàm excel kế toán về toán học khá đơn giản, bạn chỉ cần hiểu được ý nghĩa của chúng là có thể dễ dàng sử dụng hàm.

Hàm ABS

– Công dụng: Dùng để trả về giá trị tuyệt đối của một số.

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

– Ví dụ: =ABS(B3) – tính giá trị tuyệt đối của cột B3.

Hàm PRODUCT

– Công dụng: Tính tích của một dãy dữ liệu nhập vào.

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

– Ví dụ: =PRODUCT(F5:F9) – tính tích của dãy dữ liệu từ F5 đến F9

Hàm MOD

– Công dụng: Tính số dư của phép chia.

– Công thức: =MOD(Number, divisor).

– Trong đó: Number là số bị chia và divisor là số chia.

– Ví dụ: =MOD(E7,F7).

Học excel cơ bản Bài 1 / GIẢI BÀI TẬP EXCEL CƠ BẢN / LÀM QUEN VỚI EXCEL

Trung tâm dạy Excel uy tín – EDUSA

Hiện nay, có rất nhiều trung tâm tin học được mở ra nhằm phục vụ những nhu cầu của các bạn. Tuy nhiên, mỗi trung tâm lại có chất lượng giảng dạy khác nhau, và theo sau đó là những khóa học với chi phí và ưu đãi khác nhau.

Đến với trung tâm EDUSA, bạn sẽ được tư vấn kỹ lưỡng về những khoá học phù hợp với bạn nhất. Bên cạnh đó, những mức phí của khóa học đều nằm ở mức trung bình giúp bạn có thể chi trả một cách dễ dàng. Mặc dù mức phí khá thấp nhưng chất lượng giảng dạy tại EDUSA vô cùng tuyệt vời, đội ngũ giáo viên có chuyên môn cao, nhiệt tình giảng dạy các bạn học viên.

Và đặc biệt hơn, EDUSA sẽ cung cấp một vài buổi học và thi lại miễn phí cho các bạn nếu các bạn thi không đạt chứng chỉ ở lần thi đầu tiên. Thật tuyệt vời phải không nào? Hãy đến trung tâm EDUSA để được tư vấn kỹ hơn nhé.

Xem thêm: Khóa học Excel Offline chất lượng tại EDUSA

Xem thêm: Khóa học Excel Online chất lượng tại EDUSA

Hàm Excel nâng cao cho kế toán – Hàm INDEX/MATCH

INDEX/MATCH là hàm Excel được sử dụng để thay thế cho các công thức VLOOKUP, HLOOKUP (một số hạn chế, hạn chế). Đây là một trong những sự kết hợp mạnh mẽ giúp cho các phân tích tài chính, mô hình tài chính của bạn có thể phát triển cao hơn.

INDEX/MATCH là hàm Excel nâng cao hơn của VLOOKUP, HLOOKUP

Trong đó, hàm INDEX sẽ trả về giá trị của ô trong bảng tính và được dựa vào số cột, hàng, hàm MATCH trả về vị trí của ô trong 1 hàng hoặc cột. Công thức của hàm INDEX/MATCH như sau:

Công thức INDEX: =INDEX(array;row_num;column_num).

Trong đó:

Array: Vùng ô/1 hàng số mảng nào đó được chọn.
Row_num: Chọn hàng trong mảng, trả về 1 giá trị nào đó.
Column_num: Chọn cột trong mảng, trả về 1 giá trị nào đó.

Công thức MATCH: =MATCH(lookup_value,lookup_array,match_type).

Lookup_value: Giá trị tìm kiếm trong mảng của Lookup_array.
Lookup_array: Mảng, phạm vi tìm kiếm.
Match_type: Kiểu tìm kiếm.

Xem thêm: Hướng dẫn dùng hàm Excel bỏ dấu đơn giản nhất

Tự học excel cơ bản cấp tốc qua 15 chuyên đề FULL KHÔNG CHE (127 phút)

5 Tính tiền lương bằng Excel như thế nào?

Cách tính tiền lương bằng Excel như thế nào? đang là câu hỏi được rất nhiều người quan tâm. Về cơ bản, bạn có thể sử dụng các hàm thông dụng mà chúng tôi đã chia sẻ phía trên như: VLOOKUP, IF, SUMIF, ,MAX, MIN,..Tùy vào giá trị cần tính mà bạn sẽ sử dụng hàm sao cho phù hợp.

Ngoài ra, còn có một số hàm dùng để kết hợp thêm khi tính lương cho nhân viên bao gồm: