CH
NG 6
ƯƠ
• CÔNG TH C VÀ HÀM Ứ
TRONG EXCEL
1
6.1. XÂY D NG M T CÔNG TH C
Ộ
Ứ
Ự
6.1.1. Cú pháp
=
• Tên hàm do Excel quy đ nh, không phân bi
Trong đó:
• Các đ i s trong hàm đ
t ch hoa, ch th ng ị ệ ữ ườ ữ
c ngăn cách nhau b i d u ph y (,) ho c d u ở ấ ượ ẩ ặ ấ
ể ấ ổ ọ
• Danh sách các đ i s có th là:
ố ố ch m ph y (;) (có th thay đ i trong tùy ch n Control Panel\Regional And ẩ Language Option)
• Giá tr t
ố ố ể
• Đ a ch ô, đ a ch vùng
nh p c th ị ự ụ ể ậ
• Các công th cứ
2
• Có th l ng nhi u hàm v i nhau. ề
ỉ ị ỉ ị
ể ồ ớ
6.1. XÂY D NG M T CÔNG TH C
Ộ
Ứ
Ự
• Ch n v trí nh p hàm
Các b c th c hi n ướ ự ệ
• Nh p hàm: ậ
Ø Cách 1:
ậ ọ ị
• gõ tên hàm
• nh p danh sách các đ i s ố ố
gõ d u b ng (=) ho c d u c ng (+) ấ ằ ặ ấ ộ
• k t thúc b ng phím Enter
ậ
3
ế ằ
6.1. XÂY D NG M T CÔNG TH C
Ộ
Ứ
Ự
• Ch n v trí nh p hàm
Các b c th c hi n ướ ự ệ
• 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
4
ậ ầ
6.1. XÂY D NG M T CÔNG TH C
Ộ
Ứ
Ự
5
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
6
M t s nhóm hàm thông d ng ộ ố ụ
6.2. S D NG CÁC HÀM CÓ S N
Ử Ụ
Ẵ
• 6.2.1.
• NHÓM HÀM TH NG KÊ
Ố
7
1. HÀM SUM
• Ch c năng ứ
• Cú pháp: Sum(number1, [number2], ...)
: tính t ngổ
•giá tr s ị ố
•đ a ch ô, đ a ch vùng ch a giá tr ị ỉ
Trong đó: + number 1, 2, … có th là:ể
ứ ị ị ỉ
•công th c, hàm có giá tr s
số
8
ị ố ứ
2. HÀM AVERAGE
: tính trung bình
• Ch c năng ứ
• Cú pháp: Average(number1, [number2], ...)
•giá tr s ị ố
•đ a ch ô, đ a ch vùng ch a giá tr ị ỉ
Trong đó: + number 1, 2, … có th là:ể
ứ ị ị ỉ
•công th c, hàm có giá tr s
số
9
ị ố ứ
4. HÀM MIN
3. HÀM MAX
• Ch c năng ứ
: đ a ra giá tr nh nh t ấ ư ỏ ị : đ a ra giá tr l n nh t.
• Ch c năng ứ
• 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 ị ố ị ể ỉ ị ỉ
10
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 a d ữ ứ ế ố
• Ch c năng ứ
: đ m s ô ch d ứ ữ ế ố
• Cú pháp:
• Cú pháp:
li u ki u s ệ ể ố li u (khác r ng) ệ ỗ
Counta(Value1,[Value2],...)
Count(Value1,[Value2],...) Trong đó: + value1, value2, … có th là: d li u nh p vào; đ a ch ô, đ a ch ỉ ữ ệ ể ậ ị ỉ ị
11
vùng ch a d li u; công th c, hàm ữ ệ ứ ứ
7. HÀM COUNTBLANK
: đ m s ô không ch a d li u (ô tr ng, ô r ng)
• Ch c năng ứ
• Cú pháp: CountBlank(range)
ữ ệ ứ ế ố ố ỗ
12
Trong đó: + range: là vùng c n đ m ế ầ
: Hàm đ m s ô ch a giá tr th a mãn đi u ki n.
8. HÀM COUNTIF • Ch c năng ứ
• Cú pháp:
ị ỏ ứ ệ ế ề ố
COUNTIF(Range, Criteria)
• Range: Vùng (danh sách) ch a các giá tr c n đ m
Trong đó:
• Criteria: đi u ki n đ m ề
ị ầ ứ ế
• Có th dùng ký t
ế ệ
• d u “?”: thay th 1 ký t
ể ự ạ đ i di n nh : ư ệ
• d u “*”: thay th nhi u ký t
ế ấ ; ự
13
ế ề ấ ự
: đ m s đ i t ng th a mãn đ ng th i nhi u đi u ki n
9. HÀM COUNTIFS • Ch c năng ứ
• Cú pháp:
ế ố ố ượ ệ ề ề ỏ ồ ờ
COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3, …)
• range1, range2, range3, …: vùng (dãy) các ô ch a giá tr c n đ m.
Trong đó:
• criteria1, criteria2, criteria3, …: đi u ki n đ m t
ị ầ ứ ế
14
ề ệ ế ươ ng ng ứ
10. HÀM SUMIF
ng th a mãn đi u ki n
• Ch c năng ứ
• Cú pháp:
: Hàm tính t ng các đ i t ổ ố ượ ề ệ ỏ
SUMIF(range, criteria, [sum_range])
- Range: vùng (danh sách) ch a các giá tr tham chi u theo đi u ki n
Trong đó:
- 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
ệ ề ổ
ị ầ ứ ế ổ
15
Sum_range trùng v i Range thì có th b qua tham s này. ể ỏ ố ớ
: Hàm x p th h ng
11. HÀM RANK • Ch c năng ứ
• Cú pháp:
ứ ạ ế
RANK(number; ref; [order])
• Number: là giá tr c n x p th h ng trong vùng
Trong đó:
•
ứ ạ ị ầ ế
•
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) ị ặ ề ế ầ ả ị
16
Order=1 : x p theo chi u tăng d n ế ề ầ
6.2. S D NG CÁC HÀM CÓ S N
Ử Ụ
Ẵ
• 6.2.2.
• NHÓM HÀM LOGIC
17
1. HÀM AND
ế ợ ả ề ể
bi u th c đ u đúng, tr v giá tr FALSE khi có ít nh t m t bi u th c sai.
• Ch c năng : K t h p các bi u th c logic, tr v giá tr TRUE khi t ứ ề
• Cú pháp:
t c các ấ ả ứ ả ề ứ ị ị ấ ứ ể ể ộ
AND(Logical1, [Logical2], …)
2. HÀM OR
ế ợ ả ề ể ị
ấ t c các bi u th c đ u sai. m t bi u th c đúng, tr v giá tr FALSE khi t
• 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 ứ ứ ộ
• Cú pháp: OR(Logical1, [Logical2], …)
ấ ả ả ề ứ ị ứ ề ể ể
3. HÀM NOT
: Phép ph đ nh, đ i TRUE thành FALSE ho c FALSE thành ủ ị ặ ổ
• Ch c năng ứ 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 True False False False True False False True False
False True True False True
- Ví d :ụ B3=24
True True True True False
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
ng ng v i t ng
• Ch c năng ứ
: Th c hi n ki m tra đi u ki n và tr v giá tr t ề ả ề ị ươ ự ể ệ ệ ớ ừ ứ
• Cú pháp:
tr ng h p (ĐÚNG/ SAI). ườ ợ
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 ử ả ề ứ ề ể ế ề ệ ị
• value_if_true: giá tr tr v khi đi u ki n đúng ị ả ề
d ng hàm AND ho c OR ụ ặ
• 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 i là “Tr t” Ể ế ỗ c l ượ ạ ượ
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
i là 0 ạ + Còn l - Chú ý:
+ N u có nhi u giá tr t ị ươ ề ế ng ng ứ
ng h p thì s d ng v i nhi u tr ớ ề ườ ử ụ ợ
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
Ử Ụ
Ẵ
• 6.2.3.
• NHÓM HÀM VĂN B NẢ
23
1. HÀM LEN
trong chu i, k c d u : đ a ra đ dài c a chu i (= s ký t ủ ư ố ộ ỗ ự ể ả ấ ỗ
• Ch c năng ứ cách)
• Cú pháp: Len(text)
Trong đó: + text: chu i ký t ỗ ự
3. HÀM RIGHT
2. HÀM LEFT
: rút trích ra m t s ký t
• Ch c năng ứ
ộ ố ự : rút trích ra m t s ký t
• Ch c năng ứ
ộ ố ự
• Cú pháp: Right(text, [num_chars])
• Cú pháp: Left (text, [num_chars]) Trong đó: + text: chu i ký t
trong chu i, tính t ph i sang ỗ ừ ả tính trong chu i, tính t trái sang. ỗ ừ
ự ỗ
25
+ num_chars: s ký t rút trích ra; num_chars > 0; m c đ nh là 1 ố ự ặ ị
4. HÀM MID
: rút trích m t s ký t v trí đ ộ ố ự trong chu i, b t đ u t ỗ ắ ầ ừ ị ượ c ch ra ỉ
trái sang
• Ch c năng ứ tính t ừ
• 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 ả ả ề ỗ
26
+ num_chars: s ký t rút trích ra; num_chars > 0; ố ự
5. HÀM UPPER
6. HÀM LOWER
: chuy n t t c các : chuy n t t c các ký
• Ch c năng ứ
ể ấ ả
• Ch c năng ứ
ể ấ ả
• Cú pháp: Lower(text)
• Cú pháp: Upper(text)
27
ký t hoa t ng ự trong chu i thành ký t ỗ ự ự trong chu i thành ký t ỗ th ự ườ
7. HÀM PROPER
8. HÀM TRIM
: (chu n hóa chu i)
• Ch c năng ứ
ẩ ỗ
• Ch c năng ứ
: (chu n hóa chu i) lo i ạ ẩ ỗ
đ u tiên c a t ng t b các ký t tr ng không c n thi t chuy n ký t ể ự ầ ủ ừ ừ ỏ ự ắ ầ ế
hoa. Các ký trong chu i thành ký t ỗ ự trong chu iỗ
• Cú pháp: Trim(text)
• Cú pháp: Proper(text)
28
t ng. ự khác d ng ch th ạ ữ ườ
9. HÀM SUBSTITUTE
: thay th m t s ký t trong chu i b ng m t s ký t khác
• Ch c năng ứ
• 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 thay th ỗ ự cũ c n đ ầ ượ ế
+ 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 ấ ả
29
cũ s đ c thay th b ng chu i ký t chu i ký t ỗ ự ẽ ượ ế ằ ỗ m i. ự ớ
10. HÀM REPLACE
: thay th m t s ký t trong chu i b ng m t s ký t khác b t
• Ch c năng ứ
ế ộ ố ự ộ ố ỗ ằ ự ắ
• Cú pháp: Replace(old_text, start_num, num_chars, new_text)
đ u t v trí đ ầ ừ ị ượ c ch ra. ỉ
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 ố ự ế
30
+ new_text: chu i ký t m i s thay th vào ỗ ự ớ ẽ ế
11. HÀM REPT
: l p l v i s l n cho tr
• Ch c năng ứ
• Cú pháp: Rept(text, number_times)
ặ ạ i m t s ký t ộ ố ự ớ ố ầ c ướ
Trong đó: + text: ký t c l p đ ự ượ ặ
31
+ number_times: s l n l p ố ầ ặ
12. HÀM SEARCH
trong m t chu i văn b n,
• Ch c năng ứ
: tìm v trí xu t hi n c a m t s ký t ệ ộ ố ủ ấ ị ự ả ộ ỗ
• Cú pháp: Search(find_text, within_text, [start_num])
không phân bi t ký t hoa hay ký t ng. ệ ự th ự ườ
Trong đó: + find_text: chu i ký t c n tìm ỗ ự ầ
c n tìm + within_tex: chu i văn b n g c ch a m t s ký t ả ộ ố ứ ố ỗ ự ầ
+ start_num: v trí b t đ u tìm, m c đ nh là 1 ắ ầ ặ ị ị
32
N u tìm không th y hàm s báo #Value! ế ẽ ấ
13. HÀM VALUE
• Ch c năng ứ
• Cú pháp: Value(text)
: chuy n đ i ki u d li u t ổ ể ữ ệ ừ ể chu i sang s . ố ỗ
33
Trong đó: + text: chu i ký t ỗ ự ầ c n chuy n đ i ổ ể
6.2. S D NG CÁC HÀM CÓ S N
Ử Ụ
Ẵ
• 6.2.4
• NHÓM HÀM TOÁN H CỌ
34
1. HÀM ABS
: l y giá tr tuy t đ i c a m t s
• Ch c năng ứ
• Cú pháp:
ệ ố ủ ộ ố ấ ị
ABS(Number)
2. HÀM SQRT
: l y giá tr căn b c 2 c a m t s
• Ch c năng ứ
• Cú pháp:
ộ ố ủ ấ ậ ị
SQRT(Number)
3. HÀM FACT
: tính giai th a c a m t s . Giai th a (n) = 1*2*3*…*n
• Ch c năng ứ
• 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
: 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.
• Ch c năng ứ ặ
ằ
• Cú pháp:
Ví d : =INT(8.9) k t qu là 8, INT(-8.9) k t qu là -9. ụ ế ế ả ả
INT(Number)
5. HÀM MOD
: l y ph n d c a phép chia Number cho divisor
• Ch c năng ứ
• 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
c.
• Ch c năng ứ
• Cú pháp:
: làm tròn giá tr m t s v i ch s cho tr ị ộ ố ớ ỉ ố ướ
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
: tính lũy th a c a m t s number v i s mũ là power
• Ch c năng ứ
• Cú pháp:
ộ ố ớ ố ủ ừ
POWER(number, power)
• number: là c s (s đ
Trong đó:
• 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
ơ ố ố ượ c lũy th a lên) ừ
ị ố ứ ứ ể ậ ố ị ỉ
• Ví d : =power(-3,2) k t qu là 9.
ho c hàm tr v giá tr s . ị ố ả ề ặ
ụ ế ả
=power(2,-2) k t qu là ¼=0.25 ế ả
8. HÀM PRODUCT
• Ch c năng ứ
• Cú pháp:
: tính tích các số
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
: tính t ng c a các tích
• Ch c năng ứ
• 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
Ử Ụ
Ẵ
• 6.2.5
• NHÓM HÀM TH I GIAN
Ờ
40
1. HÀM NOW
hi n t i c a h th ng máy tính ờ ệ ạ ủ ệ ố
• Ch c năng ứ ạ ớ ị
• Cú pháp:
: tr v giá tr th i gian ngày và gi ị ờ (v i đ nh d ng m c đ nh ki u d li u Date và Time) ể ả ề ặ ữ ệ ị
Now()
2. HÀM TODAY
i c a h th ng máy tính (v i đ nh d ng ả ề ệ ạ ủ ệ ố ớ ị ạ
m c đ nh ki u d li u Date)
• Ch c năng ứ ặ
• Cú pháp:
: tr v giá tr ngày hi n t ị ể ữ ệ ị
Today()
3. HÀM DAY
: tr v giá tr ngày c a tháng (t 1 đ n 31) trong bi u th c
• Ch c năng ứ
• Cú pháp:
ả ề ủ ị ừ ứ ế ể
Day(serial_number)
4. HÀM MONTH
: tr v giá tr tháng trong bi u th c (t
• Ch c năng ứ
• Cú pháp: Month(serial_number)
ả ề ứ ể ị ừ 1 đ n 12) ế
5. HÀM YEAR
: tr v giá tr năm trong bi u th c (t
• Ch c năng ứ
• Cú pháp:
ả ề ứ ể ị ừ 1900 đ n 9999) ế
Year(serial_number)
Trong đó: + serial_number: bi u th c th i gian ứ ể ờ
6. HÀM HOUR
: tr v giá tr gi 0(12:00AM) đ n
• Ch c năng ứ
ả ề ị ờ trong bi u th c(t ể ứ ừ ế
• Cú pháp:
23(11:00PM))
Hour(serial_number)
7. HÀM MINUTE
: tr v giá tr phút trong bi u th c (t
• Ch c năng ứ
• Cú pháp: Minute(serial_number)
ả ề ứ ể ị ừ 1 đ n 59) ế
8. HÀM SECOND
: tr v giá tr giây trong bi u th c (t
• Ch c năng ứ
• Cú pháp:
ả ề ứ ể ị ừ 1 đ n 59) ế
Second(serial_number)
Trong đó: + serial_number: bi u th c th i gian ứ ể ờ
9. HÀM DATE
: chuy n các giá tr s (năm, tháng, ngày) thành d li u ki u Date
• Ch c năng ứ
• Cú pháp:
ữ ệ ị ố ể ể
Date(year, month,day)
10. HÀM TIME
: chuy n đ i các giá tr s (gi , phút, giây) thành d li u ki u Time
• Ch c năng ứ
• Cú pháp:
ị ố ể ổ ờ ữ ệ ể
Time(hour,minute,second)
11. HÀM WEEKDAY
1 đ n 7 đ i di n cho m t Th trong tu n
ị ố ừ
ứ
ệ
ế
ầ
ạ
ộ
• Ch c năng : tr v giá tr s t ứ 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
: tr v giá tr Tu n trong năm c a bi u th c th i gian
• Ch c năng ứ
ả ề
ủ
ứ
ể
ầ
ờ
ị
• Cú pháp: WEEKNUM(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)
ả ề
ể
ặ
ị
ị
6.2. S D NG CÁC HÀM CÓ S N
Ử Ụ
Ẵ
• 6.2.6 NHÓM HÀM
• THAM CHI U VÀ TÌM Ế
KI MẾ
47
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])
-lookup_value: giá tr c n tìm
Trong đó:
- 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])
-lookup_value: giá tr c n tìm
Trong đó:
- 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ụ
50
Ví d 2ụ
51
Chú ý
- Sử dụng hàm Hlookup hay Vlookup là phụ thuộc vào cách thiết lập bảng
- 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.
- 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
tham chiếu ít nhất phải có hai hàng (Hlookup) hoặc hai cột (Vlookup)
i #VALUE!, còn n u ẽ ế ỗ ế
- B ng tham chi u trong công th c nên đ t đ a ch tuy t đ i. ệ ố
col_index_num > s c t trong table_array, hàm s báo l i #REF! ố ộ ẽ ỗ
ặ ị ứ ế ả ỉ
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])
- lookup_value: giá tr c n tìm
Trong đó:
- 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ụ
54
4. HÀM INDEX
c ch ra trong b ng : đ a ra giá tr c a ô ng v i dòng và c t đ ứ ị ủ ộ ượ ớ ả ỉ ư
ế
Ch c nă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
trong danh sách các giá tr đ
• Ch c năng ứ
• Cú pháp: Choose(index_num,value1, [value2], …)
: đ a ra giá tr có s th t ị ố ứ ự ư ị ượ c ch ra ỉ
- index_num: là s th t
Trong đó:
- value1, value2,…: danh sách các giá tr , t
c a giá tr c n l y ra trong danh sách ố ứ ự ủ ị ầ ấ
1 đ n 254. ị ừ ế
6.2. S D NG CÁC HÀM CÓ S N
Ử Ụ
Ẵ
• 6.2.7
• NHÓM HÀM TÀI CHÍNH
57
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.
ể
•
• = 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
=1: thanh toán đ u kỳ; ầ
• Tham s nào n m trong d u [ ] là tham s tùy ch n (có th nh p ho c
ng ị ươ ề ả ả ậ ỏ ị
ể ặ ậ ấ ằ ố ố ọ
không)
1. HÀM RATE
• Ch c năng ứ
• 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 đ
: tính lãi su tấ
c ứ ỗ ử ề ử ậ ượ
• =Rate (5, -1000, 0,6000,0)
s ti n là $6000. Tính lãi su t? ố ề ấ
2. HÀM NPER
• Ch c năng ứ
• Cú pháp: NPER (rate, pmt, pv, [fv], [type])
• Ví dụ:
• M t d án có m c đ u t
: tính s kỳ đ 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 ỏ ứ ữ ệ ấ ồ ớ
• = Nper(35%, -50, -200, 1000, 0)
sau bao lâu s có đ c 1t (1000 tri u) ẽ ượ ỷ ệ
3. HÀM PMT
: tính giá tr tài kho n đ u t (ph i tr ) theo đ nh kỳ
• Ch c năng ứ
• 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 ố ả ố ả ố ỗ
• = Pmt (6%/12, 18, 27, 0, 0)
i mua ph i tr hàng tháng? ti n ng ề ườ ả ả
4. HÀM PV
: tính giá tr tài kho n đ u t (ph i tr ) ban đ u
• Ch c năng ứ
• Cú pháp: PV (rate, nper, pmt, [fv], [type])
• Ví dụ:
• Đ u t
ầ ư ả ị ả ả ầ
ộ ệ ồ
i hay vào m t công ty 270 tri u đ ng trong 3 năm. Cu i năm th ba ượ c 300 tri u đ ng, lãi su t 10%/năm. Xét đ u t ấ ố này có l ầ ư ứ ợ ệ ồ
ầ ư nh n đ ậ không?
= Pv(10%, 3, 0, 300, 0) = 225.39
vào m t công ty 230 tri u đ ng trong 3 năm. Cu i m i năm nh n ệ ậ ộ ồ
• Đ u t ầ ư đ ượ không?
ố này có l i hay c 100 tri u đ ng, lãi su t 10%/ năm. Xét đ u t ấ ầ ư ệ ồ ỗ ợ
= Pv (10%, 3, 100, 0,0) = 248.69
5. HÀM FV
ng lai
• Ch c năng ứ
• Cú pháp: FV (rate, nper, pmt, [pv], [type])
• Ví dụ:
• Đ u t
: tính giá tr tài kho n t ị ả ươ
ầ ư ấ ỗ
c sau 2 năm? ộ ự Tính s ti n nh n đ ậ vào m t d án trong 2 năm, m i năm là 1000$, lãi su t 10%/năm. ố ề ượ
• G i ti
= Fv(10%, 2, -1000, 0,0)
ử ế ệ ệ ấ
t ki m ban đ u là 100 tri u, m i tháng g i thêm 2 tri u, lãi su t ỗ c sau 2 năm, v i hình th c thanh toán ầ ố ề ệ ượ ử ớ ứ ậ
10%/năm. Tính s ti n nh n đ đ u kỳ? ầ
= Fv (10%/12, 24, -2, -100, 1)
