CHƯƠNG 6
6.1. XÂY DỰNG MỘT CÔNG THỨC
6.1.1. Cú pháp
=
Trong đó:
• Tên hàm do Excel quy định, không phân biệt chữ hoa, chữ thường • Các đối số trong hàm được ngăn cách nhau bởi dấu phẩy (,) hoặc dấu chấm phẩy (;) (có thể thay đổi trong tùy chọn Control Panel\Regional And Language Option)
• Danh sách các đối số có thể là:
• Giá trị tự nhập cụ thể • Địa chỉ ô, địa chỉ vùng • Các công thức
• Có thể lồng nhiều hàm với nhau.
6.1. XÂY DỰNG MỘT CÔNG THỨC
Các bước thực hiện
• Chọn vị trí nhập hàm
• Nhập hàm:
Cách 1: gõ dấu bằng (=) hoặc dấu cộng (+)
• gõ tên hàm
• nhập danh sách các đối số
• kết thúc bằng phím Enter
6.1. XÂY DỰNG MỘT CÔNG THỨC Các bước thực hiện
• Chọn vị trí nhập hàm
• Nhập hàm:
Cách 2: Formular Insert Functions hoặc chọn nhóm hàm trong
• Chọn hàm cần nhập
• Nhập các đối số theo yêu cầu
• OK
6.1. XÂY DỰNG MỘT CÔNG THỨC 6.1.2. Các lỗi thông dụng
6.1. XÂY DỰNG MỘT CÔNG THỨC
Hàm tài chính
Hàm logic
Hàm văn bản
Hàm thời gian
Hàm toán học và lượng giác
Hàm tìm kiếm và tham chiếu
Một số nhóm hàm thông dụng
6.2. SỬ DỤNG CÁC HÀM CÓ SẴN
1. HÀM SUM • Chức năng: tính tổng
• Cú pháp: Sum(number1, [number2], ...)
Trong đó: + number 1, 2, … có thể là:
• giá trị số • địa chỉ ô, địa chỉ vùng chứa giá trị số • công thức, hàm có giá trị số
2. HÀM AVERAGE • Chức năng: tính trung bình
• Cú pháp: Average(number1, [number2], ...)
Trong đó: + number 1, 2, … có thể là:
• giá trị số • địa chỉ ô, địa chỉ vùng chứa giá trị số • công thức, hàm có giá trị số
3. HÀM MAX
4. HÀM MIN
• Chức năng: đưa ra giá trị nhỏ nhất • Chức năng: đưa ra giá trị lớn nhất.
• Cú pháp: • Cú pháp:
Min (number1, [number2], ...) Max (number1, [number2], ...)
Trong đó: + number1, number2, … có thể là: giá trị số; địa chỉ ô, địa chỉ vùng
chứa giá trị số; công thức, hàm có giá trị số
5. HÀM COUNT
6. HÀM COUNTA
• Chức năng: đếm số ô chứ dữ liệu
(khác rỗng)
• Chức năng: đếm số ô chứa dữ
• Cú pháp:
liệu kiểu số
• Cú pháp:
Counta(Value1,[Value2],...)
Count(Value1,[Value2],...)
Trong đó: + value1, value2, … có thể là: dữ liệu nhập vào; địa chỉ ô, địa chỉ
vùng chứa dữ liệu; công thức, hàm
7. HÀM COUNTBLANK
• Chức năng: đếm số ô không chứa dữ liệu (ô trống, ô rỗng)
• Cú pháp: CountBlank(range)
Trong đó: + range: là vùng cần đếm
8. HÀM COUNTIF
• Chức năng: Hàm đếm số ô chứa giá trị thỏa mãn điều kiện.
• Cú pháp: COUNTIF(Range, Criteria)
Trong đó:
• Range: Vùng (danh sách) chứa các giá trị cần đếm
• Criteria: điều kiện đếm
• Có thể dùng ký tự đại diện như:
• dấu “?”: thay thế 1 ký tự;
• dấu “*”: thay thế nhiều ký tự
9. HÀM COUNTIFS
• Chức năng: đếm số đối tượng thỏa mãn đồng thời nhiều điều kiện
• Cú pháp:
COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3, …)
Trong đó:
• range1, range2, range3, …: vùng (dãy) các ô chứa giá trị cần đếm.
• criteria1, criteria2, criteria3, …: điều kiện đếm tương ứng
10. HÀM SUMIF
• Chức năng: Hàm tính tổng các đối tượng thỏa mãn điều kiện
• Cú pháp: SUMIF(range, criteria, [sum_range])
Trong đó:
- Range: vùng (danh sách) chứa các giá trị tham chiếu theo điều kiện
- Criteria: điều kiện tính tổng
- Sum_range: vùng (danh sách) chứa các giá trị cần tính tổng. Nếu
Sum_range trùng với Range thì có thể bỏ qua tham số này.
11. HÀM RANK
• Chức năng: Hàm xếp thứ hạng
• Cú pháp: RANK(number; ref; [order])
Trong đó:
• Number: là giá trị cần xếp thứ hạng trong vùng
• Ref: vùng chứa các giá trị cần được đánh giá xếp thứ hạng
• Order: kiểu xếp thứ hàng
• Order=0: xếp theo chiều giảm dần (giá trị mặc định)
• Order=1 : xếp theo chiều tăng dần
6.2. SỬ DỤNG CÁC HÀM CÓ SẴN
1. HÀM AND • Chức năng: Kết hợp các biểu thức logic, trả về giá trị TRUE khi tất cả các
biểu thức đều đúng, trả về giá trị FALSE khi có ít nhất một biểu thức sai.
• Cú pháp: AND(Logical1, [Logical2], …)
2. HÀM OR • Chức năng: Kết hợp các biểu thức logic, trả về giá trị TRUE khi có ít nhất một
biểu thức đúng, trả về giá trị FALSE khi tất cả các biểu thức đều sai.
• Cú pháp: OR(Logical1, [Logical2], …)
3. HÀM NOT • Chức năng: Phép phủ định, đổi TRUE thành FALSE hoặc FALSE thành TRUE
• Cú pháp: NOT(Logical)
Trong đó: + Logical: là biểu thức logic, thường chứa các phép so sánh.
Bảng Logic
OR(x, y) AND(x, y) NOT(x) x y
False False False False True
True False True False False
False True True False True
True True True True False
- Ví dụ: B3=24
AND (B3>=23,B3<25,B3>22) True
AND (B3>=23,B3<25,B3<22) False
OR (B3>=23,B3<25,B3>22) True
OR (B3=23,B3<25,B3<22) True
OR (B3=23,B3>25,B3<22) False
4. HÀM IF
• Chức năng: Thực hiện kiểm tra điều kiện và trả về giá trị tương ứng với từng
trường hợp (ĐÚNG/ SAI).
• Cú pháp: IF(logical_test, [value_if_true], [value_if_false])
Trong đó:
• logical_test: là biểu thức điều kiện, thường chứa các phép so sánh (>, <, >=,
<=, = , <>, …) trả về giá trị logic. Nếu có nhiều biểu thức điều kiện thì sử
dụng hàm AND hoặc OR
• value_if_true: giá trị trả về khi điều kiện đúng
• value_if_false: giá trị trả về khi điều kiện sai
4. HÀM IF
- Ví dụ 1:
Điền giá trị cho cột KẾT QUẢ:
+ Nếu ĐIỂM TRUNG BÌNH >=5 là “Đỗ”, ngược lại là “Trượt”
4. HÀM IF
- Ví dụ 2:
Điền giá trị cho cột HB
+ Nếu ĐTB>=8 180000
+ Nếu ĐTB>=7 và ĐTB<8 100000
+ Còn lại là 0
- Chú ý:
+ Nếu có nhiều giá trị tương ứng
với nhiều trường hợp thì sử dụng
hàm IF lồng nhau, nhưng không
vượt quá 7 cấp.
+ Số hàm IF lồng nhau = số điều
kiện -1.
6.2. SỬ DỤNG CÁC HÀM CÓ SẴN
1. HÀM LEN
• Chức năng: đưa ra độ dài của chuỗi (= số ký tự trong chuỗi, kể cả dấu cách)
• Cú pháp: Len(text)
Trong đó: + text: chuỗi ký tự
2. HÀM LEFT
3. HÀM RIGHT
• Chức năng: rút trích ra một số ký tự
trong chuỗi, tính từ phải sang
• Chức năng: rút trích ra một số ký tự
• Cú pháp: Right(text, [num_chars])
tính trong chuỗi, tính từ trái sang.
• Cú pháp: Left (text, [num_chars])
Trong đó: + text: chuỗi ký tự
+ num_chars: số ký tự rút trích ra; num_chars > 0; mặc định là 1
4. HÀM MID
• Chức năng: rút trích một số ký tự trong chuỗi, bắt đầu từ vị trí được chỉ ra
tính từ trái sang
• Cú pháp: Mid(text, start_num, num_chars)
Trong đó: + text: chuỗi ký tự
+ start_num: số thứ tự của ký tự rút trích đầu tiên
start_num >0 nếu start_num > độ dài của chuỗi thì kết
quả trả về là rỗng
+ num_chars: số ký tự rút trích ra; num_chars > 0;
5. HÀM UPPER
6. HÀM LOWER
• Chức năng: chuyển tất cả các ký • Chức năng: chuyển tất cả các ký
tự trong chuỗi thành ký tự hoa tự trong chuỗi thành ký tự thường
• Cú pháp: Upper(text) • Cú pháp: Lower(text)
7. HÀM PROPER
8. HÀM TRIM
• Chức năng: (chuẩn hóa chuỗi) • Chức năng: (chuẩn hóa chuỗi) loại
chuyển ký tự đầu tiên của từng từ bỏ các ký tự trắng không cần thiết
trong chuỗi thành ký tự hoa. Các ký trong chuỗi
tự khác dạng chữ thường. • Cú pháp: Trim(text)
• Cú pháp: Proper(text)
9. HÀM SUBSTITUTE
• Chức năng: thay thế một số ký tự trong chuỗi bằng một số ký tự khác
• Cú pháp: Substitute(text, old_text, new_text [,instance_num])
Trong đó: + text: chuỗi ký tự gốc
+ old_text: chuỗi ký tự cũ cần được thay thế
+ new_text: chuỗi ký tự mới sẽ thay thế vào
+ instance_num: số lần thay thế , nếu bỏ qua thì tất cả
chuỗi ký tự cũ sẽ được thay thế bằng chuỗi ký tự mới.
10. HÀM REPLACE
• Chức năng: thay thế một số ký tự trong chuỗi bằng một số ký tự khác bắt
đầu từ vị trí được chỉ ra.
• Cú pháp: Replace(old_text, start_num, num_chars, new_text)
Trong đó: + text: chuỗi ký tự gốc
+ start_num: vị trí bắt đầu thay thế, nếu start_num>số ký tự
của chuỗi gốc thì sẽ ghép chuỗi mới vào cuối chuỗi gốc
+ num_char: số ký tự thay thế
The picture can't be displayed. The picture can't be displayed.
+ new_text: chuỗi ký tự mới sẽ thay thế vào
11. HÀM REPT
• Chức năng: lặp lại một số ký tự với số lần cho trước
• Cú pháp: Rept(text, number_times)
Trong đó: + text: ký tự được lặp
+ number_times: số lần lặp
12. HÀM SEARCH • Chức năng: tìm vị trí xuất hiện của một số ký tự trong một chuỗi văn bản,
không phân biệt ký tự hoa hay ký tự thường.
• Cú pháp: Search(find_text, within_text, [start_num])
Trong đó: + find_text: chuỗi ký tự cần tìm
+ within_tex: chuỗi văn bản gốc chứa một số ký tự cần tìm
+ start_num: vị trí bắt đầu tìm, mặc định là 1
Nếu tìm không thấy hàm sẽ báo #Value!
13. HÀM VALUE
• Chức năng: chuyển đổi kiểu dữ liệu từ chuỗi sang số.
• Cú pháp: Value(text)
Trong đó: + text: chuỗi ký tự cần chuyển đổi
6.2. SỬ DỤNG CÁC HÀM CÓ SẴN
1. HÀM ABS • Chức năng: lấy giá trị tuyệt đối của một số
• Cú pháp: ABS(Number)
2. HÀM SQRT • Chức năng: lấy giá trị căn bậc 2 của một số
• Cú pháp: SQRT(Number)
3. HÀM FACT • Chức năng: tính giai thừa của một số. Giai thừa (n) = 1*2*3*…*n
• Cú pháp: FACT(Number)
Trong đó: + Number: là một số;
địa chỉ ô chứa giá trị số;
biểu thức hoặc hàm trả về giá trị số.
4. HÀM INT • Chức năng: lấy phần nguyên của một số và số nguyên đó phải nhỏ hơn hoặc
bằng number.
Ví dụ: =INT(8.9) kết quả là 8, INT(-8.9) kết quả là -9.
• Cú pháp: INT(Number)
5. HÀM MOD • Chức năng: lấy phần dư của phép chia Number cho divisor
• Cú pháp: MOD(Number, divisor)
Trong đó: + Number: là một số; địa chỉ ô chứa giá trị số;
biểu thức hoặc hàm trả về giá trị số.
+ divisor: số chia
VD: =MOD(23,4) kết quả là 3.
6. HÀM ROUND • Chức năng: làm tròn giá trị một số với chỉ số cho trước.
• Cú pháp: ROUND(Number, number_digits)
Trong đó: + Number: giá trị số cần làm tròn
+ divisor: chỉ số làm tròn
- Nếu divisor =n >0: làm tròn vị trí thứ n số phần thập phân.
- Nếu divisor =n=0: làm tròn phần nguyên (mặc định)
- Nếu divisor =n <0: làm tròn số trước dấu thập phân
(n=-1 làm tròn hàng chục, n=-2 hàng trăm, n=-3 hàng
nghìn…)
7. HÀM POWER
• Chức năng: tính lũy thừa của một số number với số mũ là power
• Cú pháp: POWER(number, power)
Trong đó:
• number: là cơ số (số được lũy thừa lên)
• power: số mũ
• Number và power: có thể là số nhập vào; địa chỉ ô chứa giá trị số; công thức
hoặc hàm trả về giá trị số.
• Ví dụ: =power(-3,2) kết quả là 9.
=power(2,-2) kết quả là ¼=0.25
8. HÀM PRODUCT • Chức năng: tính tích các số
• Cú pháp: PRODUCT(number1, number2, …)
Trong đó: + number1, number2, … là các số cần tính tích (nhân)
Ví dụ: =PRODUCT(20,5,-2) kết quả -200, …
9. HÀM SUMPRODUCT • Chức năng: tính tổng của các tích
• Cú pháp: SUMPRODUCT(Array1,[Array2],[Array3], …)
Trong đó: + Array1, Array2, …: là các mảng chứa số cần tính.
6. SỬ DỤNG CÁC HÀM CÓ SẴN
1. HÀM NOW • Chức năng: trả về giá trị thời gian ngày và giờ hiện tại của hệ thống máy tính
(với định dạng mặc định kiểu dữ liệu Date và Time)
• Cú pháp: Now()
2. HÀM TODAY • Chức năng: trả về giá trị ngày hiện tại của hệ thống máy tính (với định dạng
mặc định kiểu dữ liệu Date)
• Cú pháp: Today()
3. HÀM DAY • Chức năng: trả về giá trị ngày của tháng (từ 1 đến 31) trong biểu thức
• Cú pháp: Day(serial_number)
4. HÀM MONTH • Chức năng: trả về giá trị tháng trong biểu thức (từ 1 đến 12)
• Cú pháp: Month(serial_number)
5. HÀM YEAR • Chức năng: trả về giá trị năm trong biểu thức (từ 1900 đến 9999)
• Cú pháp: Year(serial_number)
Trong đó: + serial_number: biểu thức thời gian
6. HÀM HOUR • Chức năng: trả về giá trị giờ trong biểu thức(từ 0(12:00AM) đến 23(11:00PM))
• Cú pháp: Hour(serial_number)
7. HÀM MINUTE • Chức năng: trả về giá trị phút trong biểu thức (từ 1 đến 59)
• Cú pháp: Minute(serial_number)
8. HÀM SECOND • Chức năng: trả về giá trị giây trong biểu thức (từ 1 đến 59)
• Cú pháp: Second(serial_number)
Trong đó: + serial_number: biểu thức thời gian
9. HÀM DATE • Chức năng: chuyển các giá trị số (năm, tháng, ngày) thành dữ liệu kiểu Date
• Cú pháp: Date(year, month,day)
10. HÀM TIME • Chức năng: chuyển đổi các giá trị số (giờ, phút, giây) thành dữ liệu kiểu Time
• Cú pháp: Time(hour,minute,second)
11. HÀM WEEKDAY • Chức năng: trả về giá trị số từ 1 đến 7 đại diện cho một Thứ trong tuần
của biểu thức thời gian.
• Cú pháp: WEEKDAY(serial_number, [return_type])
Trong đó:
+ Serial_number: là một biểu thức thời gian
+ Return_type: xác định kiểu trả về (mặc định là 1)
Return_type có các giá trị sau:
12. HÀM WEEKNUM
WEEKNUM(serial_number, [return_type])
• Chức năng: trả về giá trị Tuần trong năm của biểu thức thời gian • Cú pháp: Trong đó:
+ Serial_number: là một biểu thức thời gian
+ Return_type: xác định kiểu trả về (mặc định là 1)
6.2. SỬ DỤNG CÁC HÀM CÓ SẴN
1. HÀM VLOOKUP
Chức năng: Tìm giá trị ở cột trái nhất của bảng tham chiếu và trả về giá trị
tương ứng cùng dòng của cột cần lấy giá trị.
Cú pháp:
Vlookup(lookup_value, table_array, col_index_num, [range_lookup])
Trong đó:
-lookup_value: giá trị cần tìm
- table_array: bảng tham chiếu
-col_index_num: STT của cột cần lấy giá trị trong bảng tham chiếu
-range_lookup: kiểu dò tìm, có hai giá trị sau:
= 0(FALSE): tìm giá trị chính xác
= 1(TRUE-mặc định): tìm giá trị xấp xỉ (trong khoảng) khi
đó bảng tham chiếu phải sắp xếp tăng dần.
2. HÀM HLOOKUP
Chức năng: Tìm giá trị ở hàng trên cùng của bảng tham chiếu và trả về giá trị
tương ứng cùng cột của hàng cần lấy giá trị.
Cú pháp:
Hlookup(lookup_value, table_array, row_index_num, [range_lookup])
Trong đó:
-lookup_value: giá trị cần tìm
- table_array: bảng tham chiếu
-row_index_num: STT của hàng cần lấy giá trị trong bảng tham chiếu
-range_lookup: kiểu dò tìm, có hai giá trị sau:
= 0(FALSE): tìm giá trị chính xác
= 1(TRUE-mặc định): tìm giá trị xấp xỉ (trong khoảng) khi
đó bảng tham chiếu phải sắp xếp tăng dần.
Ví dụ 1
Ví dụ 2
Chú ý
- Sử dụng hàm Hlookup hay Vlookup là phụ thuộc vào cách thiết lập bảng
tham chiếu.
- Trên bảng tính phải có hai bảng, bảng dữ liệu và bảng tham chiếu, bảng
tham chiếu ít nhất phải có hai hàng (Hlookup) hoặc hai cột (Vlookup)
- Nếu không tìm thấy giá trị hàm sẽ báo lỗi #N/A
- Nếu col_index_num<1, hàm sẽ báo lỗi #VALUE!, còn nếu
col_index_num > số cột trong table_array, hàm sẽ báo lỗi #REF!
- Bảng tham chiếu trong công thức nên đặt địa chỉ tuyệt đối.
3. HÀM MATCH
Chức năng: đưa ra vị trí của giá trị cần tìm trong vùng dữ liệu được chỉ ra
Cú pháp: Match(lookup_value, lookup_array, [match_type])
Trong đó:
- lookup_value: giá trị cần tìm
- lookup_array: vùng tìm kiếm, chỉ có thể trên một hàng hoặc một cột
- match_type: kiểu dò tìm, có ba giá trị sau:
= 1(Less than): tìm trong khoảng nhỏ, vùng tìm kiếm sắp xếp tăng dần.
= 0 (Exact match): tìm chính xác, vùng tìm kiếm không cần sắp xếp.
= -1(Greater than): tìm trong khoảng lớn, vùng tìm sắp xếp giảm dần
Ví dụ
4. HÀM INDEX
Chức năng: đưa ra giá trị của ô ứng với dòng và cột được chỉ ra trong bảng
dữ liệu cần tìm kiếm
Cú pháp: Index(array, row_num,[column_num])
Trong đó:
- array: vùng tìm kiếm
- row_num: stt của dòng trong bảng tìm kiếm
- column_num: stt của cột trong bảng tìm kiếm
5. HÀM CHOOSE
• Chức năng: đưa ra giá trị có số thứ tự trong danh sách các giá trị được chỉ ra
• Cú pháp: Choose(index_num,value1, [value2], …)
Trong đó:
- index_num: là số thứ tự của giá trị cần lấy ra trong danh sách
- value1, value2,…: danh sách các giá trị, từ 1 đến 254.
6.2. SỬ DỤNG CÁC HÀM CÓ SẴN
GIỚI THIỆU CÁC THAM SỐ
• rate: lãi suất % • nper (number of periods): số kỳ (thời gian) đầu tư • pmt (payment made each period): giá trị tài khoản đầu tư (phải trả) trong
mỗi kỳ
• pv (present value): giá trị tài khoản ban đầu fv (future value): giá trị tài khoản tương lai • type: kiểu thanh toán. •
• =1: thanh toán đầu kỳ; • = 0: thanh toán cuối kỳ
• Chú ý:
• Tài khoản bỏ ra mang giá trị âm, tài khoản nhận về mang giá trị dương • Tham số nào nằm trong dấu [ ] là tham số tùy chọn (có thể nhập hoặc không)
1. HÀM RATE
• Chức năng: tính lãi suất
• Cú pháp: RATE (nper, pmt, pv, [fv], [type])
• Ví dụ:
• Gửi tiền vào ngân hàng, cứ mỗi năm gửi $1000. Sau 5 năm nhận được
số tiền là $6000. Tính lãi suất?
• =Rate (5, -1000, 0,6000,0)
2. HÀM NPER
• Chức năng: tính số kỳ đầu tư
• Cú pháp: NPER (rate, pmt, pv, [fv], [type])
• Ví dụ:
• Một dự án có mức đầu tư ban đầu là 200 triệu đồng. Sau đó cứ cuối mỗi
năm đầu tư thêm 50 triệu đồng nữa. Với mức lãi suất là 35%/ năm. Hỏi
sau bao lâu sẽ có được 1tỷ (1000 triệu)
• = Nper(35%, -50, -200, 1000, 0)
3. HÀM PMT
• Chức năng: tính giá trị tài khoản đầu tư (phải trả) theo định kỳ
• Cú pháp: PMT (rate, nper, pv, [fv], [type])
• Ví dụ:
• Mua một xe máy theo hình thức trả góp với giá 27 triệu đồng, lãi suất là 6%
/ năm. Trả trong 18 tháng cả gốc và lãi vào cuối mỗi tháng. Tính số tiền
người mua phải trả hàng tháng?
• = Pmt (6%/12, 18, 27, 0, 0)
4. HÀM PV
• Chức năng: tính giá trị tài khoản đầu tư (phải trả) ban đầu
• Cú pháp: PV (rate, nper, pmt, [fv], [type])
• Ví dụ:
• Đầu tư vào một công ty 270 triệu đồng trong 3 năm. Cuối năm thứ ba
nhận được 300 triệu đồng, lãi suất 10%/năm. Xét đầu tư này có lợi hay
không?
= Pv(10%, 3, 0, 300, 0) = 225.39
• Đầu tư vào một công ty 230 triệu đồng trong 3 năm. Cuối mỗi năm nhận
được 100 triệu đồng, lãi suất 10%/ năm. Xét đầu tư này có lợi hay không?
= Pv (10%, 3, 100, 0,0) = 248.69
5. HÀM FV
• Chức năng: tính giá trị tài khoản tương lai • Cú pháp: FV (rate, nper, pmt, [pv], [type]) • Ví dụ:
• Đầu tư vào một dự án trong 2 năm, mỗi năm là 1000$, lãi suất 10%/năm.
Tính số tiền nhận được sau 2 năm?
= Fv(10%, 2, -1000, 0,0)
• Gửi tiết kiệm ban đầu là 100 triệu, mỗi tháng gửi thêm 2 triệu, lãi suất 10%/năm. Tính số tiền nhận được sau 2 năm, với hình thức thanh toán đầu kỳ?
= Fv (10%/12, 24, -2, -100, 1)
