Ham excel

Bài 5

CÁC HÀM TRONG EXCEL
Phần 1 : Sử dụng Công Thức và hàm
Phần 2 : Các Hàm cơ bản Thường Dùng
Phần 3 : Các Hàm tính toán nâng cao

Ph?n 1: S? D?NG CÔNG THỨC V� H�M
1. S? d?ng Công thức
Một công thức bao gồm:
- Một hoặc nhiều địa chỉ ô, hoặc giá trị
- Các toán tử
- Các hàm
Mỗi công thức bắt đầu bởi một dấu = hoặc dấu +
Khi thực hiện tính giá trị trong công thức, thứ tự ưu tiên sẽ là:
- Hàm tính trước, toán tử tính sau
- Phần trong dấu ngoặc tính trước, phần ngoài dấu ngoặc tính sau
- Toán tử ưu tiên tính trước đến các toán tử có mức ưu tiên kế tiếp
- Bên trái tính trước, bên phải tính sau.
2. Nhập công thức từ bàn phím
Thực hiện các bước sau:
- Di chuyển ô hiện hành đến vị trí cần nhập công thức
- Gõ dấu =
- Nhập toán tử cần thực hiện
- Nhấn Enter
3. Sửa đổi công thức
Thực hiện các bước sau:
- Di chuyển ô hiện hành đến ô có chức công thức cần sửa đổi
- Nhấn phím F2, hoặc nhấp đúp con trỏ chuột
- Sửa đổi nội dung của công thức
- Nhấn phím Enter
4. Sao chép công thức
Thực hiện các bước sau:
- Chọn ô có công thức muốn sao chép
- Rồi thực hiện các lệnh sao chép
- Chọn những ô muốn sao chép công thức
- Rồi thực hiện các lệnh dán.
5. Địa chỉ tương đối, địa chỉ tuyệt đối
Tên của mỗi ô hay vùng được gọi là địa chỉ của ô hay vùng đó.
Có 02 loại địa chỉ :
- Địa chỉ tương đối
- Địa chỉ tuyệt đối
a. Địa chỉ tương đối:
Địa chỉ bình thường của ô hay vùng khi nhập vào trong công thức được gọi là địa chỉ tương đối.
Ví dụ: = B4 + F4 + C4
Khi sao chép công thức các địa chỉ tương đối trong công thức sẽ thay đổi tương ứng ở nơi được chép đến.
b. Địa chỉ tuyệt đối:
Các địa chỉ trong công thức đứng sau dấu $ sẽ trở thành địa chỉ tuyệt đối. Có thể đặt địa chỉ tuyệt đối theo dòng và theo cột.
Khi sao chép công thức, các địa chỉ tuyệt đối trong công thức sẽ không thay đổi.
Nhấn phím F4 để thực hiện việc chuyển đổi giữa hai địa chỉ.
6. Sử dụng hàm
a. Giới thiệu
Excel có hơn 300 hàm và được chia thành 10 nhóm:
        - Haøm Ngaøy vaø Giôø - Haøm thoáng keâ

        - Haøm Toaùn hoïc - Haøm Taøi chính

- Haøm Doø tìm vaø tham chieáu - Haøm Cô sôû döõ lieäu
   - Haøm Xöû lyù chuoåi kyù töï - Haøm kyõ thuaät

- Haøm Logic - Haøm Thoâng tin
b. Cú pháp tổng quát của một hàm
= (đối số1, đối số 2, đối số 3,., đối số n)
Mỗi hàm bao gồm ba thành phần:
Dấu = : Cho biết sau đó là một hàm
Tên hàm: Sử dụng các tên gọi theo quy ước của Excel
Đối số: Là các giá trị số, chuỗi, địa chỉ ô, tên vùng, công thức, hoặc mô�t hàm khác
c. Nhập hàm vào bảng tính
Chọn một trong hai cách sau:
- Nhập hàm từ bàn phím, gõ tên hàm
- Nhấp chuột chọn Insert ? Function

Ph?n 2: CÁC HÀM CƠ BẢN
I. Hàm Toán học
1. Hàm SUM
Cho tổng giá trị của một vùng
Cú pháp:

= SUM(Vùng giá trị)
Tính tổng giá trị các ô trong vùng
Ví dụ:
= SUM(D3:D9) (cho giá trị số từ D3 đến D9)
= SUM(15,15,20) = 50
Vùng giá trị có thể là các giá trị số, d?a ch? các ô chứa trị số, khối ô

Ph?n 2: CÁC HÀM CƠ BẢN
2. Hàm AVERAGE
Cho giá trị trung bình cộng trong vùng
Cú pháp:

= AVERAGE (vùng giá trị)
Vùng giá trị có thể là các giá trị số, d?a ch? các ô chứa trị số, khối ô
Ví dụ: = AVERAGE (F4:F12)
(cho giá trị trung bình từ F4 đến F12)
= AVERAGE(3,6,9) = 6

Ph?n 2: CÁC HÀM CƠ BẢN
3. Hàm MAX, MIN
Hàm Max cho giá trị cao nhất trong vùng.
Hàm Min cho giá trị nhỏ nhất trong vùng.
Cú pháp:

= MAX (vùng giá trị)

= MIN (Vùng giá trị)
Vùng giá trị có thể là các giá trị số, d?a ch? các ô chứa trị số, khối ô

Ph?n 2: CÁC HÀM CƠ BẢN
4. Hàm COUNT
Đếm số ô chứa giá trị số trong một phạm vi
Cú pháp:

= COUNT(Vùng giá trị)
Phạm vi có thể là d?a ch? ô hoặc khối ô

Ph?n 2: CÁC HÀM CƠ BẢN
5. Hàm COUNTA
Đếm số ô có giá trị không rỗng trong vùng
Cú pháp:
= COUNTA(Vùng)
Phạm vi có thể là d?a ch? ô hoặc khối ô

Ph?n 2: CÁC HÀM CƠ BẢN
6. Hàm INT
Cho giá trị phần nguyên của phép chia M cho N
Cú pháp: = INT (M/N)
M, N có thể là số, d?a ch? ô chứa trị số
Ví dụ:
= INT(4.8) = 4
= INT(C2)
= cho giá trị phần nguyên của số trong ô C2

Ph?n 2: CÁC HÀM CƠ BẢN
7. Hàm MOD
Cho trị số dư của phép toán chia M cho N
Cú pháp: = MOD (M, N)
M, N có thể là số, d?a ch? ô chứa trị số
Ví dụ: = MOD (9,2) = 1 ; = MOD(25,5) = 0

Ph?n 2: CÁC HÀM CƠ BẢN
8. Hàm ROUND
Tính tròn trị số đến số chỉ định
Cú pháp:

= ROUND (Biểu thức số , cách làm tròn n)
- Biểu thức số có thể là trị số, d?a ch? chứa ô trị số
- Cách làm tròn:
n < 0 : Làm tròn phần nguyên
n = 0 : Làm tròn hàng đơn vị
Ví dụ:
= ROUND (368.53927,0) = 369

= ROUND (368.53927,2) = 368.54

= ROUND (368.53927, -1) = 370
Chú ý:
Giá trị số ngay tại vị trí làm tròn được cộng thêm 1 nếu số kế cận bên phải >=5
n > 0 : Làm tròn phần thập phân
9. Hàm RANK
Tìm thứ bậc của x trong vùng dò theo cách xếp thứ bậc
Cú pháp:

= RANK (x, Vùng dò, Cách xếp thứ bậc)
Cách xếp thứ tự:
- Nếu là 0 hoặc không nêu ra thì thứ bậc được tính theo giá trị số giảm dần
- Nếu là 1 thì thứ bậc được tính theo giá trị
tăng dần
II. Nhóm hàm chuỗi
1. Hàm LEFT
Trích ký tự ở bên trái chuỗi dữ liệu hoặc trong tọa độ của ô chứa dữ liệu.
Cú pháp: = LEFT (Chuỗi, Số ký tự muốn trích)
2. Hàm RIGHT
Trích ký tự ở bên phải chuỗi dữ liệu
Cú pháp:

= RIGHT (Chuỗi, Số ký tự muốn trích)
3. Hàm MID
Trích ký tự từ số bắt đầu đến số kết thúc.
Cú pháp:
= MID (Chuỗi, Số bắt đầu, Số ký tự muốn trích)
Trong đó:
Chuỗi: Dữ liệu số, nhãn, hoặc địa chỉ ô chứa dữ liệu
Số bắt đầu: Số thứ tự của ký tự bắt đầu trích trên chuỗi
Số ký tự muốn trích: Số ký tự cần trích
trên chuỗi (>0)
Ví dụ:
= LEFT ("Minh Trang",4) = Minh

= LEFT ("Minh Trang",12) = Minh Trang

= LEFT ("356",1) = 3

= RIGHT("Minh Trang",5) = Trang

= RIGHT ("Minh Trang",16) = Minh Trang

= MID ("68932",3,2) = 93
Chú ý:
- Chuỗi ký tự ghi trong hàm phải được dặt trong cặp dấu nháy kép
- Nếu số ký tự cần trích lớn hơn chiều dài của chuỗi dữ liệu thì Excel cho kết quả đúng bằng chiều dài của chuỗi.
- Chiều dài của chuỗi dữ liệu được tính cả khoảng trống trong ô
4. Hàm LEN
Cho biết độ dài của chuỗi ký tự
Cú pháp:
= LEN(chuỗi)
Chuỗi dữ liệu: Dữ liệu loại số, nhãn hoặc địa chỉ ô chứa dữ liệu
Ví dụ :
= LEN ("Xin chào bạn.") = 13
= LEN ("Thu") = 3
= LEN (A6 &B10) = Số ký tự trong 2 ô A6 vàB10
5. Hàm TRIM
Cắt bỏ khoảng trắng ở đầu và cuối chuỗi
Cú pháp:
= TRIM (chuỗi)
6. Hàm TEXT
Biến đổi số thành chuỗi
Cú pháp:

= TEXT (Giá trị số, Cách định dạng )
Ví dụ:
= TEXT (34567,"dd-mm-yy") = 21 -8 -94
7. Hàm VALUE
Đổi chuỗi chứa số ra trị số.
Cú pháp:
= VALUE (Chuỗi)
Ví dụ:
= VALUE ("123") = 123
1. Hàm AND
Trả về trị đúng nếu mọi biểu thức điều kiện đều đúng

Trả về trị sai nếu có một biểu thức điều kiện sai.


Cú pháp:
= AND (Biểu thức điều kiện 1, Biểu thức điều kiện 2,.)
III. Hàm Lôgic
2. Hàm OR
- Trả về trị đúng nếu có ít nhất một biểu thức điều kiện là đúng
Cú pháp:
= OR (Biểu thức điều kiện 1, Biểu thức điều kiện 2, .)
- Trả về trị sai nếu tất cả các biểu thức điều kiện đều sai.
1. Hàm IF
Cú pháp:
= IF (Biểu thức điều kiện, Biểu thức 1, Biểu thức 2)
Trong đó:
Biểu thức điều kiện: Biểu thức có giá trị đúng hoặc sai
- Biểu thức đi?u ki?n dúng hàm cho kết quả là: Biểu thức 1
- Biểu thức đi?u ki?n sai hàm cho kết quả là: Biểu thức 2
Nếu:
Ví dụ:
= IF (DTB>=5,"D?u", "R?t") = D?u (Nếu DTB>=5)
= R?t (Nếu DTB<5)
Chú ý:
- Một biểu thức có thể bao gồm:
Các trị số, dữ liệu,.
Các địa chỉ ô hoặc khối ô cần tham chiếu
Các công thức, hàm
IV. Hàm ngày tháng
1. Hàm DATE
Cho kết quả là trị ứng với ngày, tháng, năm
Cú pháp:
= DATE(năm, tháng, ngày)
Ví dụ:
= DATE (99,08,15) = 15/08/99
(ô được định dạng DD/MM/YY)
2. Hàm DAY
Trả về số thứ tự của ngày trong tháng
Cú pháp:
= DAY(Trị số tuần tự của dữ liệu ngày tháng)
hoặc
= DAY(Chuỗi ngày tháng năm)
Ví dụ:
= DAY("10-DEC-96") = 10
3. Hàm TODAY
Cho kết quả ngày hiện hành
Cú pháp:
= TODAY ()
Ví dụ:
= TODAY () = 26/03/04
4. Hàm MONTH
Cho dữ liệu là tháng trong dữ liệu ngày tháng
Cú pháp:
= MONTH(Trị số tuần tự của dữ liệu ngày tháng)
Hoặc
= MONTH (Chuỗi ngày tháng năm)
Ví dụ:
= MONTH ("10 - DEC - 96") = 12
5. Hàm YEAR
Cho giá trị là năm trong dữ liệu dạng ngày tháng năm.
Cú pháp:
= YEAR(Trị số tuần tự của dữ liệu ngày tháng)
hoặc
= YEAR (Chuỗi ngày tháng năm)
Ví dụ:
= YEAR ("10 - DEC - 96") = 1996
I. Nhóm hàm dò tìm và tham chiếu
1. Hàm VLOOKUP (dò tìm và tham chiếu theo cột)
Thực hiện việc dò tìm giá trị x ở vùng dò để lấy ra giá trị tương ứng của x trên cột dò.
Cú pháp:
= VLOOKUP (Trị dò x, Vùng dò, C?t dị, Cách dò )

Ph?n 3: CÁC HÀM NÂNG CAO
Trong đó:
- Trị dò x: là giá trị mang đi dò tìm. X có thể là mô�t chuỗi, d?a ch? ô chứa dữ liệu, biểu thức có kết quả là một giá trị hay một chuỗi.
- Vùng dò: Phạm vi khối dữ liệu cần khai thác gồm hai phần
Cột đầu tiên: là cột chứa dữ liệu cần
so sánh với x
Các cột kế cận bên phải: là những cột chứa dữ liệu cần khai thác
- Cách dò: Cĩ 2 c�ch (b?ng 0 ho?c b?ng 1)
Cách dò 0: Dò tìm x và giá trị tương ứng trong vùng dò phải chính xác tuyệt đối.
Cách dò 1: Dò tìm x và giá trị tương ứng trong vùng dò xấp xỉ bằng (tương đối).
- Cột dò: Là số thứ tự của cột trong vùng dò chứa giá trị tương ứng với trị dò x
Ví dụ: Cho khối khai thác sau
Với hàm VLOOKUP sau, sẽ có kết quả tương ứng:
= VLOOKUP(17, A1:D4,3) = 18

= VLOOKUP(10, A1:D4,0) = #VALUE!

= VLOOKUP(50, A1:D4,3) = 76

= VLOOKUP("17", A1:D4,2) = #N/A
(Không có dữ liệu chuỗi trong khối khai thác)

= VLOOKUP(17, A1:D4,8) = #REF!
(Số cột khai thác lớn hơn số cột trong khối
khai thác)
2. Hàm HLOOKUP
Cú pháp:
= HLOOKUP (Trị dò x, Vùng dò, H�ng dị, Cách dò )
Thực hiện việc dò tìm giá trị x ở vùng dò để lấy ra giá trị tương ứng của x trên hàng dò.
Trong đó:
- Trị dò x: Có thể là mô�t chuỗi, địa chỉ ô chứa dữ liệu, biểu thức có kết quả là một giá trị hay một chuỗi.
- Vùng dò: Phạm vi khối dữ liệu cần khai thác gồm hai phần
Hàng đầu tiên: Là hàng chứa dữ liệu cần so sánh với x
Các hàng kế cận bên dưới: Những hàng chứa dữ liệu cần khai thác
Cách dò: Cĩ 2 c�ch (b?ng 0 ho?c b?ng 1)
Tuong t? nhu c�ch dị ? h�m VLOOKUP
- Hàng dò: Là số thứ tự của hàng trong vùng dò chứa giá trị tương ứng với trị dò x
Ví dụ: Cho khối khai thác sau

Với hàm HLOOKUP sau, sẽ có kết quả tương ứng:
= HLOOKUP(17, A1:D4,3) = 18

= HLOOKUP(10, A1:D4,0) = #VALUE!

= HLOOKUP(50, A1:D4,3) = 76
= HLOOKUP("17", A1:D4,2) = #N/A
(Không có dữ liệu chuỗi trong khối khai thác)
= HLOOKUP(17, A1:D4,8) = #REF!
(Số h�ng khai thác lớn hơn số h�ng trong khối khai thác)
1. Hàm SUMIF
Tính tổng của những ô trong vùng lấy tổng số, mà những ô cùng hàng tương ứng trong vùng dò thỏa cho điều kiện.
Cú pháp:
= SUMIF (Vùng dò, điều kiện, vùng lấy tổng số )
II. Hàm tính toán có điều kiện
2. Hàm COUNTIF
Đếm số ô trong vùng thỏa điều kiện
Cú pháp:
= COUNTIF (Vùng, điều kiện)
1. Hàm INDEX
Cho giá trị của ô trong vùng dò lệch đi từ góc trên trái xuống x hàng, và qua phải y hàng
Cú pháp:

= INDEX (Vùng dò, x, y)
III. Hàm định vị
2. Hàm MATCH
Cho giá trị tương đối của ô trong vùng dò có giá trị hợp với trị dò x theo cách dò được chỉ ra.
Cú pháp:

= MATCH (Trị dò x, Vùng dò, Cách dò)
Vùng dò: Gồm các ô liên tục trong một cột hoặc hàng.