Bài giảng Tin văn phòng - Chủ đề: Microsoft Office - Trương Xuân Nam (Phần 9)

Chủ đề: Microsoft Office
TIN VĂN PHÒNG
Nội dung
 Công thức trong Excel
 Các loại địa chỉ trong công thức
 Phép tính và loại dữ liệu
 Hàm - khái niệm và cách sử dụng
 Các hàm xử lý thời gian
 Các hàm xử lý chữ
 Các hàm toán học
Trương Xuân Nam - Khoa CNTT 2
Công thức trong Excel
 Ngoài việc điền dữ liệu vào mỗi ô, Excel còn cho 
phép chúng ta thiết lập các công thức tính toán từ 
các nguồn dữ liệu khác
 Ưu điểm: thiết lập được quan hệ giữa các ô dữ 
liệu, mỗi khi ta thay đổi giá trị một ô thì những ô 
liên quan cũng được cập nhật giá trị
 Ô chứa công thức trong Excel thường bắt đầu bởi 
dấu bằng (=), hoặc dấu cộng / trừ
Trương Xuân Nam - Khoa CNTT 3
Công thức trong Excel
Trương Xuân Nam - Khoa CNTT 4
Công thức có 
dùng hàm
Công thức có 
địa chỉ cố định
Các loại địa chỉ trong công thức
 Mỗi ô trong excel có địa chỉ giúp các công thức 
có thể lấy số liệu ra để thực hiện tính toán. Ví dụ: 
ô B10 tức là ô ở cột B dòng 10
 Khi thực hiện sao chép công thức, địa chỉ bên 
trong công thức sẽ thay đổi theo. Ví dụ:
 Gõ công thức =A1 ở ô B1
 Sao chép từ B1 xuống B2, công thức đổi thành =A1
Trương Xuân Nam - Khoa CNTT 5
Các loại địa chỉ trong công thức
 Lý do: Excel chú trọng việc giữ tương quan giữa 
ô trong công thức
 A1 là ô nằm bên cạnh B1
 Khi sao chép xuống B2, ô bên cạnh sẽ là A2
 Vậy nên trong công thức sẽ đổi A1 thành A2
 Trường hợp chúng ta không muốn bị thay đổi: ta 
chỉ ra cho Excel những thành phần cố định bằng 
cách thêm dấu $ vào phía trước
Trương Xuân Nam - Khoa CNTT 6
Các loại địa chỉ trong công thức
 Các loại địa chỉ:
 Tương đối: D15
 Cố định cột: $D15
 Cố định hàng: D$15
 Tuyệt đối: $D$15
 Ngoài cách gõ trực tiếp, ta có thể sử dụng phím F4 
để chuyển đổi giữa các loại địa chỉ
 Cần đặc biệt chú ý thiết lập loại địa chỉ khi viết công 
thức
Trương Xuân Nam - Khoa CNTT 7
Phép tính và loại dữ liệu
Trong công thức của Excel cho phép chúng ta sử 
dụng các phép tính thông thường để thực hiện tính 
toán, tuy nhiên kết quả của phép tính rất phụ thuộc 
vào loại dữ liệu trong ô, ví dụ:
 Phép ^: cho phép thực hiện lũy thừa
 Phép &: cho phép ghép nội dung 2 thành chuỗi
 Phép -: nếu hai ngày tháng trừ đi nhau thì ra số
 Phép -: ngày tháng trừ đi số thì ra ngày tháng
 Phép +: ngày tháng cộng với số ra ngày tháng
Trương Xuân Nam - Khoa CNTT 8
Hàm - khái niệm và cách sử dụng
 Khái niệm Hàm trong Excel tương tự khái niệm 
Hàm trong toán học, có những quy tắc riêng
 Tên hàm trong Excel thường gợi nhớ (theo tiếng 
Anh), ví dụ: SUM, DAY, IF, AND,
 Tên hàm không phân biệt chữ hoa hay chữ thường 
(viết lẫn lộn cũng được)
 Nếu viết sai Excel sẽ có thông báo lỗi cho người dùng 
biết để sửa
 Hàm luôn trả về một kết quả
Trương Xuân Nam - Khoa CNTT 9
Hàm - khái niệm và cách sử dụng
 Quy tắc gọi hàm:
 Viết tên hàm trước
 Các đối số được liệt kê trong cặp ngoặc đơn, phân 
cách bởi dấu phẩy (hoặc chấm phẩy – tùy vào thiết 
lập của máy tính)
 Không có đối số cũng vẫn phải viết căp ngoặc
 Chẳng hạn DAY(“12/24/2012”) - trả về ngày trong 
dãy 12/24/2012 (trường hợp này là 24)
 Hàm có thể lồng nhau:SUM(10,DAY(“12/24/2012”))
Trương Xuân Nam - Khoa CNTT 10
Trương Xuân Nam - Khoa CNTT 11
CÁC HÀM XỬ LÝ THỜI GIAN
Các hàm xử lý thời gian
 Chú ý: định dạng ngày giờ trong Excel phụ thuộc 
vào thiết lập của máy tính. Thường là theo kiểu
Mỹ “tháng/ngày/năm” (M/d/yyyy)
 DATE(year, month, day): trả về ngày tháng ứng 
với số ngày tháng năm
 DATE(2013, 3, 14) trả về 3/14/2013
 DATE(2012, 2, 30) trả về 3/1/2012
Trương Xuân Nam - Khoa CNTT 12
Các hàm xử lý thời gian
 DAY(serial_number): trả về ngày trong chuỗi 
serial_number
 DAY(“12/24/2012”) trả về 24
 MONTH(serial_number): trả về tháng trong
chuỗi serial_number
 MONTH(“12/24/2012”) trả về 12
 YEAR(serial_number): trả về năm trong chuỗi
serial_number
 YEAR(“12/24/2012”) trả về 2012
Trương Xuân Nam - Khoa CNTT 13
Các hàm xử lý thời gian
 TIME(hour, minute, second): trả về thời gian 
ghép bởi 3 tham số hour, minute và second
 TIME(19, 5, 14) trả về 19:05:14 (hoặc 7:05 PM)
 TODAY(): trả về ngày hiện tại
 DAYS(end_date, start_date): trả về khoảng 
cách (số ngày) giữa hai thời điểm
 DAYS(TODAY(),“1/1/2015”): trả về số ngày từ đầu 
năm tới hiện tại
Trương Xuân Nam - Khoa CNTT 14
Các hàm xử lý thời gian
 WEEKDAY(serial_number, return_type ) 
 serial_number: là một giá trị biến biểu diễn theo ngày 
tháng
 return_type: quy định kiểu tính ngày đầu tuần
 =1: tính chủ nhật là 1, thứ 2 là 2,, thứ 7 là 7
 =2: tính thứ 2 là 1,, thứ 7 là 6, chủ nhật là 7
 =3: tính thứ 2 là 0,, thứ 7 là 5, chủ nhật là 6
 Ví dụ: WEEKDAY(“12/24/2012”, 1) trả về 2, tức 
ngày 24/12/2012 là ngày thứ hai
Trương Xuân Nam - Khoa CNTT 15
CÁC HÀM XỬ LÝ CHỮ
Trương Xuân Nam - Khoa CNTT 16
Các hàm xử lý chữ
 EXACT(text1, text2): Trả về TRUE nếu text1 
và text2 giống hệt nhau, nếu không thì trả về 
FALSE
 EXACT("EXCEL","EXCEL") trả về TRUE
 EXACT("EXCEL","Excel") trả về FALSE
 LOWER(text): trả về nội dung trong text ở dạng 
chữ thường
 LOWER("EXCEL") trả về “excel”
Trương Xuân Nam - Khoa CNTT 17
Các hàm xử lý chữ
 PROPER(text): Trả về nội dung text được chuẩn 
hóa (chuyển các chữ cái đầu mỗi từ của text 
thành chữ viết hoa)
 PROPER("excel for windows") trả về “Excel For 
Windows”
 PROPER("việt nam") trả về “Việt Nam”
 UPPER(text): trả về nội dung text nhưng chuyển 
thành chữ in hoa toàn bộ
 UPPER("excel") trả về “EXCEL”
Trương Xuân Nam - Khoa CNTT 18
Các hàm xử lý chữ
 FIND(A, B): trả vị trí xuất hiện đầu tiên của A trong 
B
 FIND(“m”, “Nam”) trả về 3
 Nếu không tìm thấy thì trả về #VALUE!
 Có thể thêm tham số thứ 3 quy định vị trí bắt đầu tìm 
FIND(“m”, “Nam”, 2) – tìm từ chữ thứ 2 trở đi
 LEFT(A, n): trả về n chữ bên trái của A
 RIGHT(A, n): trả về n chữ bên phải của A
 LEN(A): trả về độ dài (số chữ) của A
Trương Xuân Nam - Khoa CNTT 19
Các hàm xử lý chữ
 MID(A, n, k): trả về đoạn giữa của A, bắt đầu từ 
vị trí n, lấy k chữ
 MID(“Fluid Flow”, 1, 5) trả về “Fluid”
 REPLACE (A, n, k, B): cắt đoạn giữa của A, bắt 
đầu từ vị trí n, lấy k chữ, thay bằng B
 REPLACE(“2009”, 3, 2, “10”) trả về “2010”
 TRIM(A): cắt hết các kí tự trống ở đầu cuối A
 TRIM(“ ABC ”) trả về “ABC”
Trương Xuân Nam - Khoa CNTT 20
CÁC HÀM TOÁN HỌC
Trương Xuân Nam - Khoa CNTT 21
Các hàm toán học
 SUM(n1, n2,...): tính tổng các số n1, n2, ...
 EXP(x): trả về ex
 EXP(1) trả về 2.718281
 SIGN(x): xác định dấu của một số. Trả về 1 nếu 
x > 0, 0 nếu x = 0, -1 nếu x<0
 PI(): trả về số π bằng 3.141592654
 RAND(): trả về số ngẫu nhiên giữa 0 và 1
Trương Xuân Nam - Khoa CNTT 22
Các hàm toán học
 TRUNC(x): cắt bỏ phần thập phân của số x để chỉ 
lấy phần nguyên
 TRUNC(2.789) trả về 2
 TRUNC(-2.4) trả về -2
 INT(x): trả về số nguyên lớn nhất không vượt quá x
 INT(2.789) trả về 2
 INT(-2.4) trả về -3 (vì -3 nhỏ hơn -2.4)
 MOD(n, t): trả về dư của phép chia nguyên n/t
 MOD(11, 3) trả về 2
Trương Xuân Nam - Khoa CNTT 23
Các hàm toán học
 ROUND(x, n): làm tròn số x với độ chính xác 
đến con số thứ n 
 Nếu n < 0 thì x được làm tròn đến chữ số thập phân 
thứ n 
 Nếu n > 0 thì x được làm tròn đến chữ số bên trái của 
dấu (chấm) thập phân
 ABS(x): trả về trị tuyệt đối của x
 LOG(a, b): trả về logb a, nếu không viết b thì 
mặc định b=10
Trương Xuân Nam - Khoa CNTT 24
Các hàm toán học
 POWER(a, b): trả về ab
 PRODUCT(n1, n2,): tính tích n1*n2*
 SQRT(n): tính căn bậc 2 của n
 FACT(n): tính n! = 1*2**n
 Chú ý: trong Excel không có hàm tính căn bậc a 
của b, nhưng ta có thể dùng thủ thuật để tính, 
chẳng hạn tính POWER(a, 1/b)
Trương Xuân Nam - Khoa CNTT 25
Trương Xuân Nam - Khoa CNTT 26
BÀI TẬP
Bài tập 1
Trương Xuân Nam - Khoa CNTT 27
Bài tập 2
Trương Xuân Nam - Khoa CNTT 28
Bài tập 3
Trương Xuân Nam - Khoa CNTT 29
Bài tập 4
Trương Xuân Nam - Khoa CNTT 30