Bài 8: TÍNH TOÁN DỮ LIỆU TRÊN BẢNG TÍNH
II. Các hàm cơ bản
8.1, 8.3, 8.4, 8.6, 8.7
8.2, 8.5
I. Tham chieỏu ủũa chổ
I.1. Sao chép công thức
Khi thiết lập công thức cho các ô trong bảng tính, có thể sao chép công thức đó cho nhiều ô có muùc ủớch gioỏng nhau:
Vớ duù:
I.2. Tham chieỏu ủũa chổ
Trong quá trình sử dụng dữ liệu trên bảng tính để lập những công thức, ta cần tham chiếu đến một hay nhiều ô chứa dữ liệu trên bảng tính để lấy được dữ liệu cần thiết cho công thức tính toán.
Mỗi một ô trên bảng tính Worksheet đều mang một địa chỉ cụ thể (không trùng lắp). Địa chỉ của một ô trên bảng tính Worksheet bao gồm 2 thành phần:
Địa chỉ Cột (A, B, C, D, …): sẽ cho biết ô đó nằm ở cột nào trên bảng tính.
Địa chỉ Dòng (1, 2, 3, 4, …): sẽ cho biết ô đó nằm ở dòng nào trên bảng tính.
VD: Địa chỉ của 1 ô trên bảng tính là B5 Ỉ ô dữ liệu này nằm trên cột B, dòng 5.
Trong Excel, ta phân biệt thành 3 cách tham chiếu địa chỉ ô dữ liệu:
Tham chiếu địa chỉ dạng tương đối
Tham chiếu địa chỉ dạng tuyệt đối
Tham chiếu địa chỉ dạng hỗn hợp
I.3. Tham chiếu địa chỉ tương đối
Tham chiếu địa chỉ tương đối là cách sử dụng địa chỉ mà mỗi khi sao chép công thức sang vị trí mới trên Worksheet thì địa chỉ cũ sẽ tự động thay đổi thành địa chỉ mới tùy theo vị trí của ô được sao chép đến.
Để diễn tả việc tham chiếu địa chỉ tương đối, dùng cú pháp : <địa chỉ cột><địa chỉ dòng>
Vớ duù:
I.4. Tham chiếu địa chỉ tuyệt đối
Tham chiếu địa chỉ tuyệt đối là cách cố định địa chỉ trong công thức tính toán bất chấp việc sao chép công thức từ địa chỉ cũ sang địa chỉ mới.
Để diễn tả việc tham chiếu địa chỉ tuyệt đối, dùng cú pháp : $<địa chỉ cột>$<địa chỉ dòng>
Vớ duù:
Kết quả sau khi sao chép công thức:
E4 = D4 * $E$1 E5 = D5 * $E$1 E6 = D6 * $E$1 E7 = D7 * $E$1 E4 = D4 * $E$1
Cố định ô E1
I.5. Tham chiếu địa chỉ hỗn hợp
Tham chiếu địa chỉ hỗn hợp là cách kết hợp 2 dạng tham chiếu địa chỉ tương đối và tuyệt đối nhưng được áp dụng cho từng thành phần của địa chỉ (không áp dụng cho cả địa chỉ).
Điều này có nghĩa là nếu đã tuyệt đối thành phần cột thì thành phần dòng phải tương đối và ngược lại.
Để diễn tả việc tham chiếu địa chỉ hỗn hợp, dùng cú pháp : $<địa chỉ cột><địa chỉ dòng>
hoặc <địa chỉ cột>$<địa chỉ dòng>
Vớ duù:
I.6. Thay đổi cách tham chiếu địa chỉ
Để thay đổi cách tham chiếu địa chỉ trong biểu thức trên ô, chúng ta thực hiện các bước sau :
Quét chọn địa chỉ cần thay đổi trong biểu thức.
Nhấn phím F4 để thay đổi cách tham chiếu trong biểu thức. Thứ tự thay đổi như sau : địa chỉ tương đối → địa chỉ tuyệt đối → địa chỉ tương đối cột tuyệt đối dòng → địa chỉ tuyệt đối cột tương đối dòng.
Nhấn phím Enter để cập nhật giá trị cho biểu thức
C3=$B3*C$2
G7=$B7*G$2 Cố định cột B
Cố định dòng 2
I.7. Tham chieỏu vuứng
Trong trường hợp muốn tham chiếu đến các ô kế cận nhau, gọi là tham chiếu vùng, chúng ta có các cách tham chiếu sau :
I.7.1. Tham chieỏu theo ủũa chổ
Ô bắt đầu và ô kết thúc trên cùng dòng hoặc khác dòng với cú pháp
< địa chỉ ô bắt đầu> : < địa chỉ ô kết thúc>
Vớ duù:
A1 : D1 (các ô trên dòng 1 từ cột A đến cột D)
A2 : D5 (các ô trên cột A dòng 2 đến cột D dòng 5)
Tất cả các ô trên cùng cột hoặc trên nhiều cột với cú pháp:
< cột bắt đầu> : < cột kết thúc>
Vớ duù:
B : B (Tất cả các ô trên cột B) B : D (Tất cả các ô từ cột B đến cột D)
Tất cả các ô trên cùng dòng hoặc trên nhiều dòng với cú pháp:
<dòng bắt đầu> : <dòng kết thúc>
Vớ duù:
2 : 2 (Tất cả các ô trên dòng 2)
2 : 4 (Tất cả các ô từ dòng 5 đến dòng 4)
Các ô trên Worksheet khác trong cùng Workbook với cú pháp
<teõn Worksheet >!< ủũa chổ >
Vớ duù:
Sheet2!B6:D12 (các ô trên cột B đến cột D, từ dòng 6 đến dòng 12 trên sheet có tên Sheet2)
Các ô trong Worksheet trên Workbook khác với cú pháp '[< tên tập tin .xls >]<tên Worksheet >'!< địa chỉ >
Vớ duù:
'[BHAT.XLS]Sheet1'!$A$17:$A$23
(các ô trên cột A, từ dòng17 đến dòng 23 trên Sheet1 của Workbook tên BHAT.XLS)
I.7.2. Tham chiếu theo tên vùng - chỉ dùng cho địa chỉ tuyệt đối:
Quét chọn vùng muốn đặt tên
Nhập tên vùng vào ô Name Box trên thanh công cụ.
Sử dụng tên vùng vừa tạo trong biểu thức hay hàm
II. Các hàm cơ bản
Cú pháp chung của hàm gồm các thành phần :
<Tên hàm> (< các tham số >)
Tùy theo hàm, các tham số có thể có hoặc không. Các tham số có thể là :
Một giá trị cụ thể : 5, 7, “ABC”, “01/02/2002”
Một tham chiếu địa chỉ : A1, $A$5, A$10, A2 : B5
Một biểu thức : 7 + 5, A1 = True, A2 + INT(C4) …
II.1. Hàm logic
Trong Microsoft Excel:
Giá trị 0: FALSE Giátrị <> 0: TRUE
Các hàm logic thường dùng trong Excel :
AND Hàm chỉ trả về giá trị TRUE khi các tham số truyền vào có giá trị là TRUE , ngược lại trả về giá trị FALSE
Cú pháp : AND(< biểu thức logic 1>,< biểu thức logic 2 >, …)
NOT Hàm trả về giá trị phủ định với giá trị của tham số truyền vào.
Cú pháp : NOT (< biểu thức logic >)
OR Hàm chỉ trả về giá trị FALSE khi tất cả các tham số truyền vào có trị FALSE.
Cú pháp : OR(< biểu thức logic 1>,< biểu thức logic 2 >, …)
Vớ duù:
A1 = 7 A2 = 8
AND(3>2, 5>4) : TRUE AND(A1>5, A2>5) : TRUE AND(A1>5, A2>9) : FALSE OR(A1>5, A2>5) : TRUE OR(A1>5, A2>9) : TRUE OR(A1>9, A2>9) : FALSE
NOT(A1>5) : FALSE
NOT(OR(A1>9, A2>9)) : TRUE
II.2. Hàm về số
Các hàm về số thường dùng trong Excel :
ABS Hàm trả về giá trị tuyệt đối của biểu thức truyền vào Cú pháp : ABS(< biểu thức>)
INT Hàm trả về số nguyên có giá trị nhỏ hơn giá trị của biểu thức truyền vào
Cú pháp : INT(< biểu thức>)
MOD Hàm trả về phần dư của phép chia <biểu thức 1> / <biểu thức 2>
Cú pháp : MOD(< biểu thức 1 >, < biểu thức 2 >)
ROUND Hàm trả về giá trị được làm tròn của < biểu thức > tại vị trí do tham số thứ hai chỉ ủũnh.
Cú pháp : ROUND(< biểu thức >, < vị trí làm tròn >)
Nguyên tắc: Nếu giá trị tại vị trí tham số thứ hai +1 lớn hơn 5, giá trị tại vị trí tham số thứ hai được tăng lên một, ngược lại giữ nguyên.
<vị trí làm tròn>: Xác định vị trí số hạng muốn làm tròn
Nếu vị trí là 0, làm tròn tại hàng đơn vị
Nếu vị trí là 2, làm tròn đến hàng thập phân phần trăm
Nếu vị trí là -2, làm tròn đến hàng trăm
TRUNC Hàm trả về phần nguyên của < biểu thức số > tại vị trí do tham số thứ hai chỉ định, mặc định lấy phần nguyên đến vị trí hàng đơn vị
Cú pháp : TRUNC(< biểu thức số >, < vị trí >)
Vớ duù:
ABS(-7) : 7
INT(6.87) : 6
INT (7.12) : 7
INT(-12.25) : -13
MOD (5,2) : 1
MOD (28,5) : 3
ROUND (157.578, 2) : 157.58 ROUND (157.578, 1) : 157.6 ROUND (157.578, 0) : 157 ROUND (157.578, -1) : 160 ROUND (157.578, -2) : 200
TRUNC(8.5) : 8
TRUNC(-12.25, -1) : -10
II.3. Hàm về chuỗi
Các hàm xử lý chuỗi thường dùng trong Excel:
LEFT Hàm trả về các ký tự từ bên trái của một chuỗi. Số ký tự trả về được chỉ định trong tham số thứ 2, < số ký tự >. Giá trị mặc định của tham số này là 1
Cú pháp : LEFT(< biểu thức chuỗi >, < số ký tự >)
Nếu trị của tham số thứ hai lớn hơn chiều dài của chuỗi, hàm trả về cả chuỗi.
RIGHT Hàm trả về các ký tự từ bên phải của một chuỗi. Số ký tự trả về được chỉ định trong tham số thứ 2, < số ký tự >. Giá trị mặc định của tham số này là 1
Cú pháp : RIGHT(< biểu thức chuỗi >, < số ký tự >)
Nếu trị của tham số thứ hai lớn hơn chiều dài của chuỗi, hàm trả về cả chuỗi.
MID Hàm trả về các ký nằm ở giữa một chuỗi. Ký tự bắt đầu được chỉ định trong tham số thứ 2, < vị trí bắt đầu >. Số ký tự trả về được chỉ định trong tham số thứ 3, < số ký tự >.
Cú pháp : MID(< biểu thức chuỗi >, < vị trí bắt đầu >, < số ký tự >)
Nếu trị của tham số thứ hai nhỏ hơn chiều dài của chuỗi, tham số thứ ba vượt quá chiều dài còn lại của chuỗi, hàm trả về từ vị trí của tham số thứ hai đến hết chuoãi.
Nếu trị của tham số thứ hai lớn hơn chiều dài của chuỗi, hàm trả về chuỗi rỗng Nếu tham số thứ hai < 1 hoặc tham số thứ ba < 0, hàm trả về #VALUE
LEN Hàm trả về số ký tự có trong một chuỗi.
Cú pháp : LEN(< biểu thức chuỗi >)
LOWER Hàm trả về chuỗi đã được đổi sang dạng chữ thường từ chuỗi truyền vào.
Cú pháp : LOWER(< biểu thức chuỗi >)
PROPER Hàm trả về chuỗi từ chuỗi truyền vào trong đó ký tự đầu tiên của mỗi từ được đổi sang dạng chữ in hoa.
Cú pháp : PROPER(< biểu thức chuỗi >)
UPPER Hàm trả về chuỗi đã được đổi sang dạng chữ in hoa từ chuỗi truyền vào.
Cú pháp : UPPER(< biểu thức chuỗi >)
TRIM Hàm trả về chuỗi đã được loại trừ các ký tự trắng ở hai đầu của chuỗi truyền vào.
Cú pháp : TRIM(< biểu thức chuỗi >)
Vớ duù:
A1 = "TRUNG TAM TIN HOC"
LEFT(A1, 5) : TRUNG
RIGHT(A1, 5) : N HOC
MID(A1, 7, 3) : TAM
LEN(A1) : 17
LOWER(A1) : trung tam tin hoc
PROPER("trung tam tin hoc") : Trung Tam Tin Hoc
UPPER(A1) : TRUNG TAM TIN HOC
TRIM(" trung tam tin hoc "): "trung tam tin hoc"
II.4. Hàm về ngày giờ
Các hàm ngày giờ thường dùng trong Excel :
DATE Hàm trả về giá trị kiểu ngày dựa trên các tham số truyền vào.
Cú pháp : DATE(< năm >, < tháng >, < ngày >)
TIME Hàm trả về giá trị kiểu giờ dựa trên các tham số truyền vào.
Cú pháp : TIME(< giờ >, < phút >, < giây >)
DAY Hàm trả về ngày trong tháng dựa trên tham số truyền vào.
Cú pháp : DAY(< tham số kiểu ngày, giờ >)
MONTH Hàm trả về tháng trong năm dựa trên tham số truyền vào.
Cú pháp : MONTH(< tham số kiểu ngày, giờ >)
YEAR Hàm trả về năm dựa trên tham số truyền vào.
Cú pháp : YEAR(< tham số kiểu ngày, giờ >)
HOUR Hàm trả về giờ trong ngày dựa trên tham số truyền vào.
Cú pháp : HOUR(< tham số kiểu ngày, giờ >)
MINUTE Hàm trả về phút dựa trên tham số truyền vào.
Cú pháp : MINUTE(< tham số kiểu ngày, giờ >)
SECOND Hàm trả về giây dựa trên tham số truyền vào.
Cú pháp : SECOND(< tham số kiểu ngày, giờ >)
NOW Hàm trả về ngày giờ hiện hành của hệ thống.
Cú pháp : NOW()
TODAY Hàm trả về ngày tháng năm hiện hành của hệ thống.
Cú pháp : TODAY()
WEEKDAY Hàm trả về thứ trong tuần dựa trên tham số truyền vào.
Cú pháp : WEEKDAY(< tham số kiểu ngày, giờ >, < thứ đầu tuần>)
Thứ đầu tuần: Thứ mấy trong tuần được xem là đầu tuần, mặc định <thứ đầu tuần> có giá trị là 1 (Chủ nhật). Nếu là 2, thứ Hai được xem là đầu tuần …
Thứ đầu tuần:
1: Giá trị trả về từ 1: Chủ nhật Ư 7: Thứ bảy 2: Giá trị trả về từ 1: Thứ hai Ư 7: Chủ chật 3: Giá trị trả về từ 0: Thứ hai Ư 6: Chủ nhật
Vớ duù:
DAY(“01/15/2004”) trả về 15 (Định dạng ngày trong hệ thống là mm/dd/yyyy) DAY(“15/01/2004”) trả về 15 (Định dạng ngày trong hệ thống là dd/mm/yyyy) DATE(2004, 1, 31) trả về ngày 31/01/2004 hoặc 38017
DATE(2004, 1, 32) trả về ngày 01/02/2004 hoặc 38018
WEEKDAY(DATE(2003,10,13)) trả về 2 (tương ứng với Thứ Hai trong tuần) WEEKDAY(DATE(2003,10,13),1) trả về 2 (tương ứng với Thứ Hai trong tuần) WEEKDAY(DATE(2003,10,13),2) trả về 1 (tương ứng với Thứ Hai trong tuần)
WEEKDAY(DATE(2003,10,13),3) trả về 0 (tương ứng với Thứ Hai trong tuần) HOUR("23:30:15") trả về 23
HOUR("11:30:15 PM") trả về 23 SECOND("11:30:15 PM") trả về 15 SECOND("11:30 PM") trả về 0
Microsoft Excel lưu giữ ngày giờ dưới dạng số với:
Phần nguyên là ngày
Ngày 01 tháng 01 năm 1900 được lưu giữ giá trị là 1
Phần thập phân là giờ phút giây
12 giờ trưa ngày 01/01/1900 được lưu giữ là 1.5
Vì thế, ngày giờ có thể được thể hiện dưới dạng số và biểu thức số có thể qui đổi ra ngày giờ.
II.5. Hàm thống kê
Các hàm thống kê thường dùng trong Excel :
AVERAGE Hàm trả về giá trị trung bình của các tham số truyền vào.
Cú pháp : AVERAGE(< số thứ 1 >, < số thứ 2 >, < số thứ 3 >…) Số tham số tối đa là 30
AVERAGEA Hàm trả về giá trị trung bình của các ô qua các tham số truyền vào kể cả ô chứa trị chuỗi và luận lý. Giá trị chuỗi khác rỗng được xem là TRUE, ngược lại là FALSE.
Nếu giá trị của tham số là TRUE, giá trị tham số đó được tính là 1, ngược lại giá trị tham số đó được tính là 0.
Cú pháp : AVERAGEA(< số thứ 1 >, < số thứ 2 >, < số thứ 3 >…)
COUNT Hàm đếm các số có trong danh sách các tham số / ô/ vùng được truyền vào Cú pháp : COUNT(< tham số thứ 1 >, < tham số thứ 2 >, < tham số thứ 3 >…) Số tham số tối đa là 30
COUNTA Hàm đếm các ô khác rỗng qua các tham số truyền vào.
Cú pháp : COUNTA(< tham số thứ 1 >, < tham số thứ 2 >, < tham số thứ 3 >…)
MAX Hàm trả về giá trị lớn nhất qua các tham số truyền vào.
Cú pháp : MAX(< tham số thứ 1 >, < tham số thứ 2 >, < tham số thứ 3 >…) Số tham số tối đa là 30
MAXA Hàm trả về giá trị lớn nhất qua các tham số truyền vào kể cả trị chuỗi và luận lý.
Các trị TRUE được xem là 1, ngược lại là 0
Cú pháp : MAXA(< tham số thứ 1 >, < tham số thứ 2 >, < tham số thứ 3 >…)
MIN Hàm trả về giá trị nhỏ nhất qua các tham số truyền vào.
Cú pháp : MIN(< tham số thứ 1 >, < tham số thứ 2 >, < tham số thứ 3 >…) Số tham số tối đa là 30
MINA Hàm trả về giá trị nhỏ nhất qua các tham số truyền vào kể cả trị chuỗi và luận lý.
Các trị TRUE được xem là 1, ngược lại là 0
Cú pháp : MINA(< tham số thứ 1 >, < tham số thứ 2 >, < tham số thứ 3 >…)
RANK Hàm trả về thứ bậc của một số trong danh sách các giá trị số.
Cú pháp : RANK(< số >, < danh sách các số >, < thứ tự >) Thứ tự : 0 hoặc bỏ qua, thứ tự giảm dần, số lớn nhất xếp hạng 1
<> 0, thứ tự tăng dần, số nhỏ nhất xếp hạng 1
Vớ duù:
II.6. Hàm điều kiện
IF Hàm trả về một trong hai giá trị tùy theo điều kiện TRUE hoặc FALSE
Cú pháp : IF(<biểu thức điều kiện trả về TRUE/FALSE>,<giá trị trả về khi biểu thức là TRUE>,<giá trị trả về khi biểu thức là FALSE>)
COUNTIF Hàm trả về giá trị đếm những phần tử trên vùng kiểm tra nếu thỏa mãn điều kiện Cú pháp : COUNTIF(<Vùng kiểm tra>, <Điều kiện>)
SUMIF Hàm trả về giá trị tổng của những phần tử được chọn trên vùng tính tổng, những phần tử này được chọn ứng với những dòng trên vùng kiểm tra có giá trị thỏa mãn điều kiện
Cú pháp : SUMIF(<Vùng kiểm tra>, <Điều kiện>, <Vùng tính tổng>)
Vớ duù:
Vớ duù:
II.7. Hàm đổi kiểu dữ liệu
Các hàm đổi kiểu dữ liệu thường dùng trong Excel TEXT Hàm đổi số thành chuỗi.
Cú pháp : TEXT(< số >, < biểu thức định dạng>)
Biểu thức định dạng sẽ tác động trên giá trị trả về của hàm
VALUE Hàm đổi chuỗi dạng số thành số.
Đặt tên vùng NgayCT
ChucVu
SUMIF(ChucVu,"NV",NgayCT)
=IF(G5>=20,"Công tác nhiều","")
Ví dụ TEXT(12345,"0,000.0") trả về giá trị chuỗi 12,345.0 VALUE("12345") trả về giá trị số 12345
II.8. Hàm tìm kiếm
Các hàm tìm kiếm thường dùng trong Excel:
HLOOKUP Hàm tìm kiếm giá trị trên dòng đầu tiên của một bảng hay một mảng (chuỗi các giá trị dùng làm tham số) và trả về một giá trị của cột tìm thấy trên dòng được chỉ định trong hàm. HLOOKUP là hàm tìm theo chiều ngang (trên dòng)
Cú pháp : HLOOKUP(<giá trị tìm>,<vùng tìm>,<dòng lấy trị>,<cách tìm>)
Giá tri tìm : Giá trị hàm sẽ tìm trên dòng đầu tiên.
Vùng tìm : Vùng tìm kiếm có thể là bảng (vùng có đặt tên) hoặc một mảng
Dòng lấy trị : Dòng sẽ lấy giá trị trả về nếu tìm thấy
Cách tìm : TRUE nếu dòng đầu tiên có sắp xếp tăng dần và tìm gần đúng, ngược lại cách tìm = FALSE (không được sắp xếp) tìm chính xác.
Chuù yù:
Các giá trị dòng đầu tiên có thể là chuỗi, số hoặc giá trị luận lý Hàm không phân biệt chữ HOA, chữ thường
Nếu dòng lấy trị < 1, hàm báo lỗi #VALUE!. Nếu dòng lấy trị > số dòng của vùng tìm, hàm báo lỗi #REF!.
Cách tìm nếu là TRUE hoặc không có và nếu không tìm thấy giá trị chính xác, hàm sẽ trả về trị trên cột mang trị gần đúng nhất nhưng nhỏ Giá trị tìm. Nếu là FALSE và không tìm thấy giá trị chính xác, hàm báo lỗi #N/A.
VLOOKUP Hàm tìm kiếm giá trị trên cột đầu tiên của một bảng hay một mảng (chuỗi các giá trị dùng làm tham số) và trả về một giá trị của dòng tìm thấy trên cột được chỉ định trong hàm. VLOOKUP là hàm tìm theo chiều dọc (trên cột)
Cú pháp : VLOOKUP(<giá trị tìm>,<vùng tìm>,<cột lấy trị>,<cách tìm>) Cách dùng như hàm HLOOKUP