Tổng hợp các hàm cơ bản trong Microsoft Excel 2010

Bởi

trannhuomedu

8 Tháng Ba, 2020

1093

Mục lục

TỔNG QUAN

Dạng tổng quát của hàm: tên hàm(các tham số)
Các tham số cách nhau bởi dấu phẩy (có thể thay đổi dấu ngăn cách giữa các tham số bởi ký hiệu khác bằng cách dùng lệnh Start\Control Panel\Region and Language. Chọn Additional settings… rồi chọn thẻ Numbers, chọn ký hiệu trong hộp List Separator)
Tham số có thể là công thức, địa chỉ ô, địa chỉ vùng, hoặc giá trị hằng; tuy vậy bao giờ cũng phải thỏa mãn kiểu của từng tham số. Có tham số bắt buộc và có tham số tùy chọn. Hàm luôn trả về một giá trị.

HÀM XỬ LÝ CHUỖI

https://www.youtube.com/watch?v=7CBrLJzzvfU&list=PLMThdSwEsaOVXvpw0DIZMCpHUr6gF6Ghx

Tên hàm	Cú pháp và công dụng
LEFT	LEFT(chuỗi, [n]): trả về n ký tự bên trái của tham số chuỗi. Lưu ý, nếu ta bỏ qua n thì mặc định n=1. Ví dụ: =LEFT(“Tin học kinh tế”,7) à Kết quả: Tin học
RIGHT	RIGHT(chuỗi, [n]): trả về n ký tự bên phải của tham số chuỗi. Lưu ý, nếu ta bỏ qua n thì mặc định n=1. Ví dụ: =RIGHT(“Tin học kinh tế”,7) à Kết quả: kinh tế
MID	MID(chuỗi, m, n): trả về n ký tự kể từ ký tự thứ m của chuỗi Ví dụ: =MID(“Tin học kinh tế”,5,3) à Kết quả: học
TRIM	TRIM(chuỗi): trả về chuỗi đã cắt bỏ những khoảng trống của chuỗi, khi đó mỗi từ chỉ cách nhau một khoảng trắng. Ví dụ: =TRIM(“ Tin học kinh tế”,7) à Kết quả: Tin học kinh tế
LOWER	LOWER(chuỗi): trả về chuỗi chữ thường tương ứng với chuỗi đã cho Ví dụ: =LOWER(“TIN HỌC KINH TẾ”) à Kết quả: tin học kinh tế
UPPER	UPPER(chuỗi): trả về chuỗi chữ hoa tương ứng với chuỗi đã cho Ví dụ: =UPPER(“Tin học kinh tế”) à Kết quả: TIN HỌC KINH TẾ
PROPER	PROPER(chuỗi): trả về một chuỗi mà ký tự đầu của mỗi từ đều là chữ hoa tương ứng với chuỗi đã cho Ví dụ: =PROPER(“Tin học kinh tế”) à Kết quả: Tin Học Kinh Tế
LEN	LEN(chuỗi): trả về chiều dài của chuỗi, nghĩa là số ký tự kể cả khoảng trắng trong chuỗi đã cho Ví dụ: =LEN(“Tin học kinh tế”) à Kết: 15
FIND	FIND(chuỗi_1, chuỗi, [n]): trả về vị trí xuất hiện đầu tiên của chuỗi_1 trong chuỗi, bắt đầu tìm từ vị trí thứ n bên trái chuỗi trở đi (mặc định n = 1). Có phân biệt chữ hoa chữ thường Ví dụ: =FIND(“học”, “Tin học kinh tế”) à Kết quả: 5 =FIND(“Học”, “Tin học kinh tế”) à Kết quả: #VALUE!
SEARCH	SEARCH(chuỗi_1, chuỗi, [n]): trả về vị trí xuất hiện đầu tiên của chuỗi_1 trong chuỗi, bắt đầu tìm từ vị trí thứ n bên trái chuỗi trở đi (mặc định n = 1). Không phân biệt chữ hoa chữ thường Ví dụ: = SEARCH(“học”, “Tin học kinh tế”) à Kết quả: 5 = SEARCH (“Học”, “Tin học kinh tế”) à Kết quả: 5
REPLACE	REPLACE(chuỗi, i, n, chuỗi_mới): thay thế n ký tự trong chuỗi bắt đầu từ ký tự thứ i bằng chuỗi_mới. Ví dụ:= REPLACE(“Tin học kinh tế”,9,7,“B”) à Kết quả: Tin học B
SUBSTITUTE	SUBSTITUTE(chuỗi, chuỗi_cũ, chuỗi_mới, [i]): thay chuỗi_cũ thứ i trong chuỗi bằng chuỗi_mới, nếu bỏ qua i nghĩa là thay thế tất cả chuỗi_cũ bằng chuỗi_mới. Ví dụ: = SUBSTITUTE((“Tin học kinh tế”, “kinh tế”, “B”) à Kết quả: Tin học B
TEXT	TEXT(số, định_dạng): đưa số từ kiểu số về kiểu chuỗi với định_dạng (xem thêm phần định dạng số) Ví dụ: =TEXT(2000,“$#,###.00”) à Kết quả: $2,000.00
VALUE	VALUE(chuỗi-số): trả về số tương ứng với chuỗi-số đã cho Ví dụ: =VALUE(“0045”) à Kết quả: 45

HÀM XỬ LÝ NGÀY THÁNG

Tên hàm	Cú pháp và công dụng
NOW	NOW(): trả về ngày-tháng-năm và giờ hiện hành mà máy đang lưu giữ Ví dụ: =NOW() à Kết quả: 13/04/2013 19:20
TODAY	TODAY(): trả về ngày-tháng-năm hiện hành mà máy đang lưu giữ Ví dụ: =TODAY() à Kết quả: 13/04/2013
DATE	DATE(năm, tháng, ngày): trả về ngày-tháng-năm tương ứng. Ví dụ: =DATE(2013,04,30) à Kết quả: 30/04/2013
DAY	DAY(ngày-tháng-năm): trả về một con số (1 à 31) chỉ ngày tương ứng với tham số ngày-tháng-năm Ví dụ: =DAY(“30/04/2013”) à Kết quả: 30
MONTH	MONTH(ngày-tháng-năm) : trả về một số (1 à 12) chỉ tháng tương ứng với tham số ngày-tháng-năm Ví dụ: =MONTH(“30/04/2013”) à Kết quả: 4
YEAR	YEAR(ngày-tháng-năm): trả về một con số (gồm 4 chữ số) chỉ năm tương ứng với tham số ngày-tháng-năm Ví dụ: =YEAR(“30/04/2013”) à Kết quả: 2013
WEEKDAY	WEEKDAY(ngày-tháng-năm,[kiểu]): trả về số thứ tự (nghĩa là Thứ) của ngày-tháng-năm trong tuần. Các kiểuthông dụng: · 1: hàm trả về 1 là chủ nhật à 7 là thứ bảy (mặc định) · 2: hàm trả về 1 là thứ hai à 7 là chủ nhật. 3: hàm trả về 0 là thứ hai à 6 là chủ nhật. Ví dụ : = WEEKDAY(“30/04/2013”) à Kết quả: 3
TIME	TIME(giờ, phút, giây): trả về thời gian tương ứng với 3 tham số: giờ, phút, giây đã cho Ví dụ : = TIME(6,30,5) à Kết quả 6:30:05
HOUR	HOUR(btgiờ): trả về một số chỉ giờ (0 à 23) tương ứng với btgiờ Ví dụ : = HOUR(“08:30:15”) à Kết quả : 8
MINUTE	MINUTE(btgiờ): trả về một số chỉ phút (0 à 59) tương ứng với btgiờ Ví dụ: =MINUTE(“08:30:15”) à Kết quả : 30
SECOND	SECOND(btgiờ): trả về một số chỉ giây (0 à 59) tương ứng với btgiờ Ví dụ : =SECOND(“08:30:15”) à Kết quả : 15

HÀM SỐ HỌC

Tên hàm	Cú pháp và công dụng
ABS	ABS(x): trả về giá trị tuyệt đối (ABSolute) của biểu-thức-số x Ví dụ: =ABS(3-5) à Kết quả: 2
INT	INT(x): trả về số nguyên (INTeger) lớn nhất còn nhỏ hơn hoặc bằng x Ví dụ: =INT(9/2) à Kết quả: 4
MOD	MOD(x, y): trả về số dư của phép chia nguyên x cho y theo qui tắc sau: MOD(x, y) = x – y* INT(x/y) Ví dụ: =MOD(9,2) à Kết quả: 1
ROUND	ROUND(x, n): làm tròn số x tùy theo n; với n > 0: làm tròn với n vị trí thập phân, n = 0: làm tròn đến hàng đơn vị, n = -1 : làm tròn đến hàng chục, n = -2: làm tròn đến hàng trăm,… Ví dụ: =ROUND(1.23456,2) à Kết quả : 1.23
PRODUCT	PRODUCT(x1 , x2 , … , xn): trả về tích các số x1 , x2 , … , xn
SUM	SUM(x1 , x2 , … , xn): trả về tổng các số x1 , x2 , … , xn
SUMIF	SUMIF(vùng1, điều-kiện, vùng2): trả về tổng các ô trong vùng2 tương ứng theo thứ tự với các ô trong vùng1 thỏa điều-kiện. Nếu bỏ qua tham số vùng_2 nghĩa là vùng_2 = vùng_1. Ví dụ:
SUMIFS	SUMIFS(vùng-tính-tổng, vùng-chứa-điều-kiện-1,điều-kiện-1, [vùng- chứa-điều-kiện-2,điều-kiện-2],…) : trả về tổng các ô trong vùng-tính- tổng ứng với thứ tự các ô trong vùng-chứa-điều-kiện-1 thỏa điều-kiện- 1 và các ô trong vùng-chứa-điều-kiện-2 thỏa điều-kiện-2,… Ví dụ:
SUMPRODUCT	SUMPRODUCT(vùng1, vùng2, …): trả về tổng của tích các ô tương ứng trong vùng1, vùng2,… Ví dụ:
RAND	RAND(): trả về 1 số ngẫu nhiện trong khoảng từ 0 đến 1
RANDBETWEEN	RANDBETWEEN(số_nhỏ, số_lớn): trả về một số ngẫu nhiên trong khoảng từ số_nhỏ đến số_lớn

HÀM THỐNG KÊ

Tên hàm	Cú pháp và công dụng
MIN	MIN(danh-sách) : trả về giá trị nhỏ nhất của các số trong danh-sách, Ví dụ: = MIN(6,4,2,8) à Kết quả: 2
MAX	MAX(danh-sách): trả về giá trị lớn nhất của các số trong danh-sách Ví dụ: = MAX(6,4,2,8) à Kết quả: 8
AVERAGE	AVERAGE(danh-sách): trả về giá trị trung bình của các số trong danh- sách, Ví dụ: = AVERAGE (6,4,2,8) à Kết quả: 5.75
AVERAGEIF	AVERAGEIF(vùng_1, điều_kiện[, vùng_2]): trả về giá trị trung bình của các ô trong vùng2 tương ứng theo thứ tự với các ô trong vùng1 thỏa điều-kiện. Nếu bỏ qua tham số vùng_2 nghĩa là vùng_2 = vùng_1, Ví dụ:
COUNT	COUNT(danh-sách) : trả về số lượng các ô có kiểu số trong danh-sách Ví dụ: =COUNT(2,4,7, “A”) à Kết quả: 3
COUNTA	COUNTA(danh-sách): trả về số lượng các ô có nội dung khác rỗng trong danh sách, Ví dụ: =COUNTA(2,4,7, “A”) và Kết quả: 4
COUNTIF	COUNTIF(vùng, điều-kiện) : trả về số lượng các ô trong vùng thỏa điều-kiện, Ví dụ:
RANK	RANK(ô, vùng, kiểu) : trả về thứ tự xếp hạng của ô trong vùng; kiểu = 1: xếp hạng tăng dần, kiểu = 0 hoặc lờ đi: xếp hạng giảm dần, Ví dụ:

HÀM LUẬN LÝ

Tên hàm	Cú pháp và công dụng
IF	IF(btLogic1, bt1, bt2): Nếu btLogic có giá trị TRUE thì trả về bt1, ngược lại btLogic có giá trị FALSE thì trả về bt2 Ví dụ : =IF(1>2,1,2) à Kết quả: 2
AND	AND(btLogic1, btLogic2, …): trả về TRUE nếu tất cả các btLogic đều có giá trị TRUE, trả về FALSE nếu có ít nhất một btLogic có giá trị FALSE Ví dụ: =AND(1>2,2<3) à Kết quả : FALSE
OR	OR(btLogic1, btLogic2, …): trả về FALSE nếu tất cả các btLogic đều có giá trị FALSE, trả về TRUE nếu có ít nhất một btLogic có giá trị TRUE Ví dụ : =OR(1>2,2<3) à Kết quả : TRUE
NOT	NOT(btLogic): trả về giá trị phủ định của btLogic Ví dụ: =NOT(AND(1>2,2<3)) à Kết quả: TRUE

HÀM DÒ TÌM (QUAN TRỌNG)

Tên hàm	Cú pháp và công dụng
VLOOKUP	VLOOKUP(giá-trị-dò, bảng-dò, cột, kiểu-dò): dùng giá-trị-dò để dò tìm trên cột đầu tiên của bảng-dò (Không tìm thấy trả về #N/A) Có 2 trường hợp: + Kiểu dò là False hay 0: nghĩa là dò tìm chính xác. + Kiểu dò là True, hoặc 1, hoặc lờ đi: nghĩa là dò tìm xấp xỉ. Excel dò đến một giá trị lớn nhất còn nhỏ hơn hay bằng giá-trị-dò. Cột đầu tiên của bảng-dò nên được sắp xếp tăng dần. Ví dụ:
HLOOKUP	HLOOKUP(giá-trị-dò, bảng-dò, dòng, kiểu-dò): dùng giá-trị-dò để dò tìm trên dòng đầu tiên của bảng-dò (Không tìm thấy trả về #N/A) Có 2 trường hợp: + Kiểu-dò là False hay 0: dò tìm chính xác. + Kiểu-dò là True, hoặc 1, hoặc lờ đi: nghĩa là dò tìm xấp xỉ. Excel dò đến một giá trị lớn nhất còn nhỏ hơn hay bằng giá-trị-dò. Dòng đầu tiên của bảng-dò nên được sắp tăng dần.
MATCH	MATCH(giá-trị-dò, bảng-dò, kiểu-dò): Trả về thứ tự vị trí của giá-trị-dò trong bảng-dò (chỉ là 1 dòng hoặc 1 cột). + Kiểu-dò là 0 (dò chính xác và bảng-dò không cần sắp xếp) + Kiểu-dò là 1 hoặc lờ đi (MATCH tìm đến giá trị lớn nhất còn nhỏ hơn hay bằng giá-trị-dò và bảng-dò phải được sắp xếp tăng dần) + Kiểu-dò là -1 (MATCH tìm đến giá trị nhỏ nhất còn lớn hơn hay bằng giá-trị-dò và bảng-dò phải được sắp xếp giảm dần). Ví dụ:
CHOOSE	CHOOSE(thứ-tự, giá trị 1, giá trị 2, …): trả về một giá trị trong một danh sách các giá trị; giá trị đó tương ứng với thứ-tự chỉ ra. Nếu thứ-tự nhỏ hơn 1 hoặc lớn hơn số giá trị trong danh sách thì CHOOSE trả về #VALUE! Ví dụ:
INDEX	INDEX(bảng-dò, dòng, cột): trả về giá trị trong bảng-dò tương ứng với tọa độ dòng, cột. Ví dụ: