Ố
CHCHƯƠƯƠNG IV NG IV CÔNG TH C & HÀM S Ứ
Ộ
N I DUNG
Ể
Ứ §4.1. CÔNG TH C (BI U TH C
Ứ FORMULA)
Ộ Ố §4.2. M T S HÀM (
FUNCTION) THÔNG D NGỤ
Ớ
Ậ
Ẫ
Ả §4.3. HƯ NG D N GI I CÁC BÀI T P EXCEL
ể
1. Bi u th c (
ứ Expression)
ậ
ấ ầ
ấ ằ ừ ờ
ằ ở ặ ấ ộ
ở
ộ
ả ạ ố ăn b n, ngày tháng)
ệ
ứ ể ặ ữ ệ Chương II đã trình bày cách nh p d li u cho ứ ộ ể ữ ệ đó có th là m t công th c (hay ộ m t ô. D li u ấ ộ ắ đ u b i 1 trong 3 d u là: d u c ng ể ứ bi u th c) b t ư ng là d u ấ (+), d u tr () ho c d u b ng (=), th ố ớ ạ b ng, theo sau là m t dãy các toán h ng n i v i ể ạ đó, toán h ng có th nhau b i các phép toán. Trong là m t trong các lo i sau: Giá tr h ng (s , v ị ằ Hàm toán h c (ọ Function call). Ký hi u ô, Đ a ch vùng (kh i), Tên kh i. ố ố ỉ ị Bi u th c trong d u ngo c tròn. ấ
ắ
ổ ấ ừ ầ
ộ
ụ ạ
ứ ế Các phép toán (s p theo th t ứ ự ưu tiên) g m ồ ấ phép đ i d u (hay phép tr 1 ngôi), l y ph n trăm(%), lũy th a(ừ ^), nhân và chia(* / ), c ng và ỗ &), và các phép so ố tr ừ (+ ), phép n i hai chu i ( ể i ô D6 ta có th sánh ( >, >=, <, <=, =, <> ). Ví d , t vi t công th c: =B9+B3+B2.
ẽ ớ Trong chương này chúng ta s xem xét t
i các hàm ứ ử ụ
ạ ệ
ị
ị
ế ọ ờ ợ ấ i 1 k t qu nào
ủ c a EXCEL và cách s d ng chúng trong công th c: Hàm là m t chộ ệ ể ự ương trình con, có th th c hi n ộ ả ụ ể ộ ế m t công vi c nào i m t k t qu c th , đó và cho l ợ đ u ầ đư c cung c p cho hàm. Các ấ tùy theo giá tr ban ố Parameter) ọ đ u ầ đó đư c g i là tham s ( ị giá tr ban ộ ố ệ ủ c a hàm. Vi c cung c p cho hàm m t s giá tr ban đ u ầ đ thu l ợ đó (thì) đư c g i là l ạ ể i ọ g i hàm (
ụ ố ả Function Call). Hàm có d ng:ạ TÊN HÀM (các tham s ). Ví d : Sin (30).
ụ
ị ị ị SUM(giátr 1, giátr 2, ...)
ế
ố AVERAGE(…).
ị
ộ ủ ấ ớ ề ề
HLOOKUP()
ọ VLOOKUP()
ề đi u ki n: ệ SUMIF(), COUNTIF()…
ắ
ộ ố 2. M t s hàm thông d ng. ổ (a) Tính t ng giá tr : ị COUNT(…). ố (b) Đ m s ô có giá tr (c) Tính trung bình c ng c a các s ấ MIN(), MAX(). ỏ (d) Tìm giá tr nh nh t/l n nh t ị ị ệ ệ IF (đi u ki n, giátr 1, giá tr 2) (e) Hàm đi u ki n: ề ề (f) Hàm tìm trong mi n theo chi u d c ề ề (g) Hàm tìm trong mi n theo chi u ngang ỗ LEFT(), RIGHT(), MID(). ử (h) Hàm x lý chu i: : ờ DAY(), MONTH(), YEAR(). (i) Hàm ngày gi ử (j) Các hàm x lý có ả ế (k) S p x p m ng…
ể
ố ị
ị ố ị ố a. Hàm tính t ngổ : SUM (gt1, gt2, …) Các gt1, gt2, … có th là các giá tr s (Literal), ỉ ủ ứ ộ ờ ể
ừ Ví d : Tính t ng các giá tr s c a các ô t đ n ế C4
ừ ị ố đ a ị ỉ ộ ặ đ a ch m t kh i (vùng) ch c a ô có giá tr s ho c ặ i có ch a các giá tr s ; ho c cũng có th là m t l ọ g i hàm. ụ D13 và các ô t ị ố ủ ế t: ế đ n J13 thì vi
ổ G4 = SUM (C4:D13, G4:J13) = SUM (C4:D13, G4:J13)
ố ế
ể
ố ị
ị ố ị ố ộ ờ ể
ị ừ ế ế đ n D13 và các C4
Ví d : Đ m các ô có giá tr t ừ ế ị: COUNT(gt1, gt2, …) b. Hàm đ m s ô có giá tr ị ố đ a ị Các gt1, gt2, … có th là các giá tr s (Literal), ỉ ộ ặ đ a ch m t kh i (vùng) ỉ ủ ch c a ô có giá tr s ho c ặ ứ i có ch a các giá tr s ; ho c cũng có th là m t l ọ g i hàm. ụ G4 ế đ n J13 thì vi ô t t:
= COUNT (C4:D13, G4:J13) = COUNT (C4:D13, G4:J13)
ể
ố ị
ị c. Tính giá tr trung bình Các gt1, gt2, … có th là các giá tr s (Literal), ỉ ủ ứ ị ố ị ố ộ ờ ể
: AVERAGE(gt1, gt2, …) ị ố đ a ị ỉ ộ ặ đ a ch m t kh i (vùng) ch c a ô có giá tr s ho c ặ i có ch a các giá tr s ; ho c cũng có th là m t l ọ g i hàm. ụ ỉ ừ ị đ a ch t C4
ế ị Ví d : Tính giá tr trung bình các ô có ừ t: G4 ế đ n D13 và các ô t ế đ n J13 thì vi
= AVERAGE (C4:D13, G4:J13) = AVERAGE (C4:D13, G4:J13)
ỏ
ố ị
ị ố ị ố ấ : MIN(gt1, gt2, …) ị d. Tìm giá tr nh nh t ể Các gt1, gt2, … có th là các giá tr s (Literal), ỉ ủ ứ ộ ờ ể
ị ố đ a ị ỉ ộ ặ đ a ch m t kh i (vùng) ch c a ô có giá tr s ho c ặ i có ch a các giá tr s ; ho c cũng có th là m t l ọ g i hàm. ụ ỉ ừ ấ ị C4
ừ ỏ ế đ n J13 thì vi ế đ n D13 và các ô t
ị đ a ch t Ví d : Tính giá tr nh nh t các ô có ế t: G4 = MIN (C4:D13, G4:J13) = MIN (C4:D13, G4:J13)
ị ớ ấ : MAX(gt1, gt2, …)
ố ị
ị ố ị ố e. Tìm giá tr l n nh t ể Các gt1, gt2, … có th là các giá tr s (Literal), ỉ ủ ứ ộ ờ ể
ị ố đ a ị ỉ ộ ặ đ a ch m t kh i (vùng) ch c a ô có giá tr s ho c ặ i có ch a các giá tr s ; ho c cũng có th là m t l ọ g i hàm. ụ ỉ ừ ấ C4
ừ ế đ n D13 và các ô t ế đ n J13 thì vi
ị ị ớ đ a ch t Ví d : Tính giá tr l n nh t các ô có ế t: G4 = MAX (C4:D13, G4:J13) = MAX (C4:D13, G4:J13)
ề ệ ệ : IF (đki n, gt1, gt2, …)
ể
ự ỉ ộ ể ộ ờ ọ ị ặ ị đ a ị ặ đ a ch m t kh i (vùng) có i g i hàm
f. Hàm đi u ki n Các gt1, gt2 có th là các tr c hàng (Literal), ị ỉ ủ ố ch c a ô có giá tr ho c ứ ch a các giá tr ; ho c cũng có th là m t l khác.
ụ ế
ợ ạ ữ Ví d : N u B2 là ch A thì giá tr nh n i thì nh n ậ đ ị ợ ậ đư c dãy “ ựợ c là chi n ế
“Anh hùng”, ngư c l t:ế sỹ”, thì vi
= IF (B2=“A”, “Anh hùng”, “”, “Chi n sế ỹ”)”) = IF (B2=“A”, “
ự phía trái
ự ầ ấ
ỗ ố ư ng ký t ợ ủ ầ ọ : c n l y) ứ ữ đ u c a ô B4 ch a h và tên
ế ộ ố ấ g. Hàm l y m t s ký t LEFT (chu i, s l ấ ụ Ví d : L y 3 ch ọ h c viên, ta vi t:
ả = LEFT (B4, 3) = LEFT (B4, 3) ế =LEFT(“ABCDEFGH”,3) cho k t qu là “ =LEFT ABCABC”
ự phía ph i
ả : ự ầ ấ c n l y) ứ ọ ộ ố ấ h. Hàm l y m t s ký t ợ ỗ ố ư ng ký t RIGHT (chu i, s l ố ủ ữ Ví d : L y 5 ch cu i c a ô B4 ch a h và tên
ế ụ ấ ọ h c viên, ta vi t:
ả = RIGHT (B4, 5) = RIGHT (B4, 5) ế =RIGHT(“ABCDEFGH”,3) cho k t qu là “ =RIGHT FGHFGH”
ấ
ộ ố ị ự ầ i. Hàm l y m t s ký t ỗ MID (chu i, v trí b t ỗ : ự ở trong chu i ợ ố ư ng ký t ầ ắ đ u, s l c n
l y)ấ
ụ ấ ữ ứ ủ ế Ví d : L y ch th 2 c a ô B5, ta vi t:
= MID (B5, 2, 1) = MID (B5, 2, 1)
ế ả =MID=MID(“ABCDEFGH”,3,2) cho k t qu là “ CDECDE”
ố:
ế t:
ỗ ổ j. Hàm đ i chu i thành s VALUE (chu i)ỗ ố ỗ ụ ổ Ví d : Đ i chu i “12345” thành s 12.345, ta vi = VALUE (“12345”) = VALUE (“12345”)
ố =Value(Right(“A0101”,4)) thành s 101 =Value(Right(“A0101”,4))
ấ ố ủ ự:
ữ ố ỗ đ u tiên trong chu i
ế k. Hàm l y mã s c a ký t CODE (chu i)ỗ ầ ụ ấ Ví d : l y mã s ch cái ự ứ “ABC” (t c là mã ASCII c a ký t A), ta vi t:
ủ = CODE (“ABC”) = CODE (“ABC”)
ị ừ ộ ả ề l. Hàm tìm giá tr t ọ m t b ng theo chi u d c
ộ ấ ề VLOOKUP(
ủ ầ
ị
ả ề đư c thì tr v giá tr trên < ợ
Ý nghĩa: Tìm giá tr <ị gt> trên c t ộ đ u tiên c a kh i ố c t ộ ợ ị ố đã đư c s p theo giá tr ắ ố ứ ủ ầ ủ ộ ế (< mi nề >), n u tìm ế ố l yấ > c a kh i. N u kh i ứ ấ tăng d n c a c t th nh t thì tham s th 4 là 1.
ụ Ví d : Bài toán đi Du l chị
ị ừ ộ ả ề m. Hàm tìm giá tr t m t b ng theo chi u ngang
ấ ề
HLOOKUP(
ủ ầ
ị
ế ố
Ý nghĩa: Tìm giá tr <ị gt> trên dòng đ u tiên c a
ả ề
đư c thì tr v giá tr trên
ợ
ợ ắ
ố đã đư c s p theo giá
ố ứ ủ
ầ ủ ộ mi nề >), n u tìm
kh i (<ố
ế
ụ ị Ví d : Bài toán đi Du l ch đư c ợ đ i l ổ ạ i
ạ ế n Hàm x p h ng
ề RANK(
ị ạ Ý nghĩa: Xác đ nh
ừ ấ ỏ
ấ ạ ế
ệ ế ứ ạ ấ ỏ
giá tr <ị ứ ấ gt> đ ng h ng m y ệ ố ứ ế trong kh i (<ố mi nề >). N u tham s th 4 là 1, thì vi c ứ ỏ đ n l n (giá tr nh nh t có th ị ế ớ ạ ế x p h ng là t nh ứ ạ ấ ị ớ ạ h ng là 1; giá tr l n nh t có th h ng cao nh t). ố ứ ợ ạ Ngư c l i, n u tham s th 4 là 0, thì vi c x p h ng ỏ ị ế ừ ớ đ n nh (giá tr nh nh t có th h ng cao l n là t ứ ạ ấ ị ớ ấ nh t; giá tr l n nh t có th h ng là 1).
ụ ể Ví d : Bài toán tính đi m thi
ề
ệ
n Các hàm tính toán có đi u ki n
ẩ
ổ
SUMIF(
ầ
ế ạ ộ đ u tiên tho tiêu
i c t
ợ
ổ
chu n tính toán thì c ng vào t ng giá tr trên vùng
ả đư c xét .
ị OR/AND ị OR/AND … ầ ừ ắ đ u t A1
ế đ n B7):
tính
ư c ớ
tr cho n
ị ế i ô D7 ta vi
t:
ệ
ố ầ ạ
ế
t:
Ý nghĩa: Xét trong vùng đã cho, n u t
ộ
ị
ẩ
ạ :
ẩ
Tiêu chu n có d ng
10 20 10 30 10 30 20
ổ ể t ng giá Đ ạ “Thailand” t =SumIf(A1:B7, “Thailand”,B1:B7) Đ ể đ m s l n xu t hi n ch ữ ấ ế “Thailand”, t i ô D8 ta vi =CountIf(A1:B7, “Thailand”,B1:B7)
Ví d :ụ
