Bài giảng Tin học cơ bản: Chương 5.2 - Nguyễn Quỳnh Diệp
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (705.82 KB, 35 trang )
CHƯƠNG 5
BÀI 2+3
CÁC HÀM TRONG EXCEL
Giảng viên : Nguyễn Quỳnh Diệp
Khoa CNTT – Đại học Thủy lợi
Email
:
Site: http//sites/google.com/site/nqdieptvp/
NỘI DUNG
• Cơng thức trong Excel
• Các loại địa chỉ trong cơng thức
• Sử dụng Hàm trong Excel
• Một số hàm thường dùng:
– Hàm xử lý thời gian, hàm xử lý ký tự, hàm lượng
giác, hàm logic, hàm toán học, hàm thống kê, hàm xử
lý theo điều kiện, hàm tìm kiếm
Cơng thức trong Excel
• Ngồ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 tố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ừ
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 tố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 ơ,
Các loại địa chỉ trong cơng thức
•
Địa chỉ tương đối:
– <têncột><tênhàng>: địa chỉ này thay đổi khi copy công thức sang ơ
khác
– Ví dụ: A2, C4…
•
Địa chỉ tuyệt đối:
– $<têncột>$<tênhàng>: địa chỉ này không bị thay đổi khi copy công
thức
– Ví dụ: $A$2, $C$4
•
Địa chỉ hỗn hợp:
– Đánh địa chỉ tuyệt đối theo hàng hoặc theo cột:
– Ví dụ: A$1,$C2
Các loại địa chỉ trong cơng thức
• Tham chiếu đến địa chỉ Sheet khác:
– <tên sheet>!<địa chỉ ơ>
– Ví dụ: Sheet1!A2, ‘Sheet Moi’!B2…
• Tham chiếu đến địa chỉ WorkBook khác:
– [<tên workbook>]<tên sheet >!<tên địa chỉ ơ>
– Ví dụ: [Bai2.xlsx]Sheet2!A$2
Sử dụng hàm trong Excel
• Hàm được lập trình có sẵn nhằm thực hiện
chức năng nào đó mà tốn tử đơn giản khơng
thực hiện được
• Cú pháp của hàm: = tenham(đối số)
– Tên hàm viết liền, có thể viết hoa hoặc thường
– Đối số có thể là giá trị, địa chỉ ô hoặc một dãy ô
Sử dụng hàm trong Excel
• Cách 1: gõ trực tiếp tên hàm vào ơ cần tính
– Gõ dấu =, sau đó gõ <tên hàm >
• Cách 2:
– Vào ribbon Fomulas
– Nhấn chọn Insert Function
– Chọn function cần tính
Sử dụng hàm trong Excel
– Vào ribbon Fomulas
– Chọn các function trên menu
HÀM XỬ LÝ THỜI GIAN
• DAY(serial_number): trả ra ngày của tham số
• MONTH(serial_number): trả ra tháng của tham số
• YEAR (serial_number) : trả ra năm của tham số
• TIME(hour,minute, second)
– Trả về thời gian ghép bởi 3 tham số: hour, minute,
second
– Ví dụ: TIME(19,15,20) trả về kết quả 19:15:20 hoặc 7:15
PM
Hàm xử lý thời gian
• TODAY()
– Trả về ngày hiện tại.
• NOW()
– Trả về ngày và thời gian hiện tại.
• DAYS(end_date, start_date)
– Trả về số ngày giữa hai mốc thời gian.
HÀM KÝ TỰ
• LOWER (A)
– Chuyển đổi A từ chữ hoa thành chữ thường.
• UPPER(A)
– Chuyển đổi A từ chữ thường thành chữ hoa
• TRIM(A)
– Loại bỏ tất cả khoảng trống ra khỏi A, chỉ để lại một
khoảng trống giữa các từ.
Hàm ký tự
• LEN(A)
– Trả về số ký tự (chiều dài) của A.
• MID(A,m,n)
– Trích từ A n ký tự, bắt đầu từ ký tự thứ m.
• LEFT(A, m)
– Trả ra m ký tự bên trái của A
• RIGHT(A,m)
– Trả ra m ký tự bên phải của A
Hàm ký tự
• FIND, SEARCH(B,A,[m])
– Tìm B trong A, nếu thấy thì trả về vị trí bắt đầu tìm
được.
– Nếu khơng tìm thấy sẽ trả ra #VALUE
– Nếu có tùy chọn [m] thì bắt đầu tìm từ ký tự thứ m.
Cơng thức
Mơ tả
Kết
quả
=FIND("M",A2)
Vị trí của chữ "M" thứ nhất trong ô A2
?
=FIND("m",A2)
Vị trí của chữ "M" thứ nhất trong ô A2
?
=FIND("M",A2,3)
Vị trí của chữ "M" thứ nhất trong ơ A2,
bắt đầu từ ký tự thứ ba
?
Hàm tốn học
• MOD(x,n)
– Trả về số dư sau khi chia x cho n. Kết quả có cùng dấu với ước
số.
• INT(x)
– Là hàm trả về số được làm trịn đến số ngun gần
nhất của x.
• ROUND(x,n)
– Làm trịn x tới n chữ số sau dấu phẩy.
Hàm toán học
HÀM THỐNG KÊ
• SUM(m,n,…)
– Hàm tính tổng các số m,n,….
• MAX(m,n,…)
– Là hàm trả về giá trị lớn nhất của các số m,n,…
• MIN(m,n,…)
– Là hàm trả về giá trị nhỏ nhất của các số m,n,…
• AVERAGE(m,n,…)
– Trả về trung bình (trung bình cộng) của các đối số.
Hàm thống kê
• COUNT(m,[n],…): Hàm đếm số ơ chứa số
• COUNTA(m, n, …): đếm số các ơ khơng rỗng
• Chú ý: Nếu các tham số là những ô liền kề nhau
thì có thể dùng địa chỉ vùng
[địa chỉ ơ đầu] : [địa chỉ ô cuối]
Dữ liệu
08/12/08
19
Công thức
Mô tả
Kết quả
=COUNT(A2:A7)
Đếm số ô chứa số trong
các ô từ A2 tới A7.
3
=COUNT(A5:A7)
Đếm số ô chứa số trong
các ô từ A5 tới A7.
2
=COUNT(A2:A7,
2)
Đếm số ô chứa số trong
các ô từ A2 tới A7 và giá trị
2.
4
22,24
TRUE
#DIV/0!
Hàm thống kê
• RANK(x, A,n)
– Là hàm trả về thứ hạng của số x trong vùng A. Thứ
hạng của số là kích thước của nó trong tương quan
với các giá trị khác trong danh sách.
– n nhận giá trị 0 để lấy thứ hạng theo thứ tự giảm dần,
1 để lấy theo thứ tự tăng dần
HÀM LOGIC
• AND(a,b,c, …)
– Trả về TRUE nếu tất cả các đối số a,b,c là TRUE; trả
về FALSE nếu một hoặc nhiều đối số là FALSE.
Công thức
Mô tả
Kết quả
=AND(TRUE, TRUE)
Tất cả các đối số là
TRUE
TRUE
=AND(TRUE, FALSE)
Một đối số là FALSE
FALSE
=AND(2+2=4, 2+3=5)
Tất cả các đối số đều
định trị là TRUE
TRUE
Hàm Logic
• OR(a,b,c,…)
– Trả về TRUE nếu bất kỳ đối số nào là TRUE, trả về
FALSE nếu tất cả các đối số là FALSE.
Hàm Logic
• NOT(a)
– Trả về FALSE nếu a là TRUE, trả về TRUE nếu a là
FALSE.
HÀM XỬ LÝ THEO ĐIỀU KIỆN
• IF(test, A, B):
– Tính biểu thức điều kiện test,
– Nếu test là đúng thì thực hiện A,
– Nếu test là sai thì thực hiện B
– Hàm IF có thể lồng nhau nhiều cấp
Hàm xử lý theo điều kiện
• Ví dụ:
Chi phí Thực tế
Chi phí Dự kiến
$1.500
$900
$500
$900
$500
$925
Cơng thức
Mơ tả
Kết quả
=IF(A2>B2,"Vượt dự
tốn","OK")
Kiểm tra xem chi phí thực tế trong hàng 2
có lớn hơn chi phí dự kiến hay khơng. Trả
về "Vượt Dự tốn" vì kết quả của phép thử
này là True.
Vượt Dự
toán
=IF(A3>B3,"Vượt Dự
toán","OK")
Kiểm tra xem chi phí thực tế trong hàng 3
có lớn hơn chi phí dự kiến hay khơng. Trả
về "OK" vì kết quả của phép thử này là
False.
OK
Hàm xử lý theo điều kiện
Điểm
• Ví dụ:
45
90
78
Cơng thức
Mơ tả
Kết quả
=IF(A2>89,"A",IF(A2>79,"B",
IF(A2>69,"C",IF(A2>59,"D","F"))))
Dùng các điều kiện IF lồng để gán điểm
?
bằng chữ vào điểm số trong ô A2.
=IF(A3>89,"A",IF(A3>79,"B",
IF(A3>69,"C",IF(A3>59,"D","F"))))
Dùng các điều kiện IF lồng để gán điểm
?
bằng chữ vào điểm số trong ô A3.
=IF(A4>89,"A",IF(A4>79,"B",
IF(A4>69,"C",IF(A4>59,"D","F"))))
Dùng các điều kiện IF lồng để gán điểm
?
bằng chữ vào điểm số trong ô A4.
