Hàm Excel thường được sử dụng (phần 1)

06/05/2020

Hàm trong Excel luôn bắt đầu bởi dấu = (hoặc +), sau đó đến Tên hàm và các đối số bên trong nó (nếu có) theo dạng:

=TÊN HÀM(Danh sách các đối số)

– Tên hàm không phân biệt chữ hoa hoặc chữ thường, phải viết đúng theo cú pháp và quy ước của Excel.

– Nếu hàm có nhiều đối số thì các đối số phải đặt cách nhau bởi dấu phân cách (dấu phẩy hoặc dấu chấm phẩy tùy vào hiệu chỉnh ban đầu trong Control Pannel-Region and Language-List Separator).

– Hàm không có đối số cũng phải có cặp ngoặc đơn () sau nó.

– Các hàm có thể lồng nhau nhưng phải đảm bảo cú pháp của hàm.

Các hàm Excel thường được sử dụng:

Hàm chuỗi (Text)

=Find(find_text,within_text,[start_num])

Kết quả trả về là số chỉ vị trí của chuỗi cần tìm (find_text) trong một chuỗi khác (within_text), [start_num] có thể bỏ qua để lấy mặc định là 1.

VD: =Find(“/”,”Tiền mặt / Việt nam đồng”) ==> Kết quả=10

=Left(text,[num_chars])

Kết quả trả về là số chuỗi ký tự [num_chars] được cắt ra từ bên trái của chuỗi ban đầu (text). Nếu [num_chars] được bỏ qua thì mặc định là 1.

VD: =Left(“Tiền mặt / Việt nam đồng”, Find(“/”,”Tiền mặt / Việt nam đồng”)-1)

==> Kết quả=”Tiền mặt “

Hàm toán học (Math)

=Max(number1,[number2],…)

=Min(number1,[number2],…)

Kết quả trả về là số lớn nhất hay nhỏ nhất trong các dãy số đã chọn.

VD: =MAX(E7+G7-F7-H7,0) ==> Kết quả=0 nếu E7+G7-F7-H7<0

=MIN(E7+G7-F7-H7,0) ==> Kết quả=E7+G7-F7-H7 nếu E7+G7-F7-H7<0

=Sum(number1,[number2],…)

Kết quả trả về là số tổng của các số (number) được chọn trong bảng tính.

VD: =Sum(1,3,5,10) ==> Kết quả=19

=Sum(A1:A1000) ==> Kết quả=tổng của tất cả các số trong cột A từ dòng 1 đến 1000

=Sumif(range,criteria,[sum_range])

Kết quả trả về là số tổng của các số trong vùng chọn [sum_range] thỏa điều kiện (criteria) đã được đối chiếu trong vùng điều kiện (range)

VD: =Sumif(TK_nợ,”1111”,Số_tiền)

==> Kết quả=tổng tất cả cột Số_tiền thỏa điều kiện là TK_nợ bằng tiền mặt VNĐ.

=Sumifs(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Kết quả trả về như sumif nhưng thỏa cùng lúc nhiều điều kiện hơn, từng cặp điều kiện (criteria) đối chiếu với vùng điều kiện (criteria_range), các điều kiện này cùng xảy ra.

VD: =Sumifs(Số_tiền,TK_nợ,”1111”,Tháng,2)

==> Kết quả=tổng cột Số_tiền thỏa điều kiện là TK_nợ bằng tiền mặt VNĐ và trong Tháng 2

=SubTotal(9,ref1,ref2…)

Kết quả trả về là tổng của các tham số (hoặc vùng số) mà đang hiển thị trên màn hình. Công thức này thường dùng kết hợp với chức năng Auto Filter.

Hàm Logical

=And(logical1,[logical2],…)

=Or(logical1,[logical2],…)

Hai hàm này, kết quả trả về là TRUE hoặc FALSE.

Logical1…là một biểu thức so sánh cho kết quả là TRUE hoặc FALSE, nó phải bao gồm một trong các toán tử sau: =, >, <, >=, <=, <>

Hàm And chỉ trả về TRUE khi tất cả các biểu thức logical trong ngoặc đều là TRUE, ngược lại là FALSE. Hàm Or thì trả về TRUE khi một trong các biểu thức logical trong ngoặc là TRUE và chỉ trả về FALSE khi tất cả các biểu thức logical trong ngoặc là FALSE.

Trong giáo trình này tác giả lại muốn sử dụng toán tử * thay cho hàm And và toán tử + thay cho hàm Or để trình bày một cách khác khi muốn And hoặc Or.

VD:=(‘Nhaplieu’!C2=1)*((‘Nhaplieu’!F2=”1121″)+(‘Nhaplieu’!G2=”1121″))

And(‘Nhaplieu’!C2=1,Or(‘Nhaplieu’!F2=”1121″,’Nhaplieu’!G2=”1121″))

=If(logical_test,[value_if_true],[value_if_false])

Kết quả trả về là giá trị bất kỳ, nếu biểu thức so sánh (logical_test) là TRUE thì kết quả trả về là đối số thứ 2 [value_if_true], là FASLE thì trả về là đối số thứ 3 [value_if_false].

VD: =IF(LEFT(L2)=”A”,L2,””)

==> Kết quả=là giá trị của ô L2 nếu ký tự đầu tiên bên trái của ô L2 là A, nếu không phải thì bằng rỗng “”.

=Ifna(value,value_if_na)

Hàm này kiểm tra lỗi #N/A (not available), lỗi này thường xuất hiện khi chúng ta dùng các hàm tìm kiếm nhưng không tìm thấy chính xác kết quả. Kết quả trả về là đối số 2 (value_if_na) khi đối số 1 (value) có lỗi là #N/A, nếu đối số 1 không có lỗi #N/A thì kết quả trả về vẫn là nó (value).

VD: =IFNA(VLOOKUP($A13,$L$16:$T$202,MATCH(E$11,$L$16:$T$16,0),0),0)

==> Kết quả=0 nếu hàm vlookup() báo lỗi #N/A, nếu không lỗi #N/A thì

==> Kết quả= VLOOKUP($A13,$L$16:$T$202,MATCH(E$11,$L$16:$T$16,0),0)

=Iferror(value,value_if_error)

Hàm này tương tự như hàm Ifna nhưng rộng hơn. Hàm này cũng kiểm tra lỗi nhưng sẽ kiểm tra tất cả lỗi nếu có, chứ không riêng gì lỗi #N/A.

Hàm tìm kiếm và tham chiếu (Lookup & Reference)

=Match(lookup_value,lookup_array,[match_type])

Kết quả trả về là số chỉ vị trí của giá trị dò (lookup_value) trên mảng dò (lookup_array), [match_type] là cách dò_chúng ta thường dùng 0 để dò tuyệt đối chính xác.

VD:= MATCH(E$11,$L$16:$T$16,0)

Nếu giá trị ở ô E11 nằm ở vị trí thứ tư trên mảng L16:T16 thì ==> Kết quả=4

Chúng ta thường dùng hàm này để thay thế cho đối số (col_index_num) trong hàm vlookup hay đối số (row_index_num) trong hàm hlookup để tăng tính tự động cho hàm.

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

Hàm này dò tìm theo cột với giá trị dò là lookup_value và dò trên cột đầu tiên của bảng dò (table_array) lấy ra giá trị tương ứng ở cột thứ mấy trên bảng dò (col_index_num) theo điều kiện dò [range_lookup] (thường là 0 để dò tuyệt đối chính xác).

VD: =VLOOKUP($A13,$L$16:$T$202,3,0)

==> Kết quả=là giá trị tương ứng ở cột thứ 3 trong bảng dò (L16:T202) nếu giá trị dò A13 tìm thấy chính xác ở cột L16:L202 trong bảng dò. Nếu không tìm thấy báo lỗi #N/A.