Bài giảng Tin học ứng dụng - Chương: Công thức và hàm - Lê Viết Mẫn
Công thức và hàm
v 1.0 - 04/2012
1
Công thức và hàm
Monday, April 9, 2012
Nội dung
1. Công thức
2. Hàm
3. Các hàm cơ bản
4. Các toán tử và dạng dữ liệu
5. Tham chiếu tương đối, tuyệt đối
6. Cách sử dụng hàm mảng
7. Cách kiểm tra công thức
8. Các chú ý khi thành lập công thức
2
Công thức và hàm
Monday, April 9, 2012
Công thức
Công thức trong Excel bắt đầu bằng dấu “=” và sau đó là sự kết
hợp của các toán tử, trị số, địa chỉ ô và các hàm
•
Dấu bằng
Trị số
Toán tử
Hàm
Tham chiếu
Tham chiếu : là địa chỉ của một ô (cell) hay một dãy ô (range), địa chỉ ô bao gồm
tên cột và vị trí của hàng. Ví dụ : A1 là vị trí của ô có tên cột là A và vị trí hàng là 1
3
Công thức và hàm
Monday, April 9, 2012
Hàm
Hàm trong Excel được lập trình sẵn dùng tính toán hoặc thực hiện
chức năng nào đó khi người sử dụng cung cấp các đối số đã định
sẵn
•
Tên hàm (hàm IF)
Dấu mở ngoặc
Đối số
Dấu ngăn cách
Dấu đóng ngoặc
Insert > Function
4
Công thức và hàm
Monday, April 9, 2012
Các hàm cơ bản
Hàm tài chính PV, FV, IRR, NPV, PMT, RATE
Hàm ngày giờ DATE, DATEVALUE, TODAY, NOW
Hàm toán và ma trận ABS, COUNTIF, SUM, SUMIF, SUMPRODUCT, MDETERM,
MINVERSE, MMULT
Hàm thống kê AVERAGE, COUNT, COUNTA, COVAR, FREQUENCY, LINEST, MAX,
MEDIAN, MIN, MODE, NORMINV, NORMSDIST, NORMSINV, RANK, STDEV, STDEVP,
VAR
Hàm tìm kiếm và tham chiếu HLOOKUP, VLOOKUP, INDEX, ROW, COLUMN,
OFFSET, MATCH
Hàm dữ liệu DCOUNT, DMAX, DMIN, DSUM
Hàm văn bản CONCATENATE, FIXED, LEFT, RIGHT, MID, LEN
Hàm logic IF, AND, OR, FALSE, TRUE, NOT
Hàm thông tin CELL, TYPE
5
Công thức và hàm
Monday, April 9, 2012
Các toán tử và dạng dữ liệu
Toán tử
Danh sách
Số học
+, -, *, /, %, ^
So sánh
=, >, <, >=, <=, <>
Văn bản
&
Tham chiếu
: ,
6
Công thức và hàm
Monday, April 9, 2012
Toán tử và dạng số liệu
7
Công thức và hàm
Monday, April 9, 2012
Toán tử và dạng số liệu
8
Công thức và hàm
Monday, April 9, 2012
Toán tử và dạng số liệu
Toán tử dãy tạo ra một tham chiếu của một dãy ô chỉ bằng 2
tham chiếu đầu và cuối của dãy
Toán tử liên kết sẽ kết hợp các tham chiếu tạo thành một tham
chiếu
9
Công thức và hàm
Monday, April 9, 2012
Toán tử và dạng số liệu
Dạng số : 1; 1.2
Dạng tiền tệ : $1; ¥2
Dạng ngày tháng : 3/8/2002
Dạng thời gian : 7:00 AM
Dạng phần trăm : 10%
Dạng phân số : 1/4; 3/10
Dạng khoa học : 1.00E+05
Dạng văn bản : abc-ABC
10
Công thức và hàm
Monday, April 9, 2012
Tham chiếu tương đối, tuyệt đối
Tham chiếu tương đối và tham chiếu tuyệt đối được sử dụng khi
chúng ta cần sao chép công thức từ một ô này cho các ô khác
Tham chiếu tương đối : là tham chiếu của một ô hay dãy ô mà địa chỉ
ô không chứa kí tự $. Ví dụ : A1
Tham chiếu tuyệt đối : là tham chiếu của một ô hay dãy ô mà địa chỉ
có chứa kí tự $. Ví dụ : $A$1
Khi sao chép (copy) một ô có công thức chứa tham chiếu tương đối
cho một ô (hay nhiều ô) thì tham chiếu trong công thức của ô đó (hay
nhiều ô) sẽ thay đổi tương ứng
11
Công thức và hàm
Monday, April 9, 2012
Tham chiếu tương đối, tuyệt đối
Ví dụ về tham chiếu tương đối
Thành tiền (USD) = Đơn giá x Số lượng
hay
E3 = C3 * D3
1. Nhập vào ô E3 công thức “=C3*D3” rồi nhấn phím Ctrl + Enter
2. Sao chép công thức này bằng cách nhấn phím tắt Ctrl+C
3. Di chuyển đến ô E4 rồi nhấn Enter (dán công thức đó vào ô E4)
4. Lúc này khi nhìn lên thanh công thức (Formular Bar) thì ta thấy công thức trong ô E4 sẽ là
“=C4*D4” và đây cũng là công thức đúng như mong muốn
Giải thích như sau : ô E4 cũng cột (cột E) với E3 nhưng có số thứ tự hàng tăng lên 1. Như vậy,
khi copy công thức từ ô E3 đến ô E4 thì tất cả các tham chiếu trong công thức của ô E4 sẽ tăng
thêm 1 hàng, nghĩa là C3 → C4 và D3 → D4
12
Công thức và hàm
Monday, April 9, 2012
Tham chiếu tương đối, tuyệt đối
Ví dụ về tham chiếu tuyệt đối
Thành tiền (VND) = Thành tiền (USD) x Tỷ giá
hay
F3 = E3 * F1
1. Nhập vào ô F3 công thức “=E3*F1” rồi nhấn phím Ctrl+Enter
2. Sao chép công thức này bằng cách nhấn phím tắt Ctrl+C (hay dùng Fill handle)
3. Di chuyển đến ô F4 rồi nhấn Enter (dán công thức đó vào ô F4)
4. Lúc này khi nhìn lên thành công thức (Formular Bar) thì ta thấy công thức trong ô F4 sẽ là
“=E4*F2” và không phải là công thức đúng. Kết quả của ô F4 là #VALUE!
Giải thích như sau : ô F4 cũng cột (cột F) với F3 nhưng có số thứ tự hàng tăng lên 1. Như vậy,
khi copy công thức từ ô F3 đến ô F4 thì tất cả các tham chiếu trong công thức của ô F4 sẽ tăng
thêm 1 hàng, nghĩa là E3 → E4 (đúng) và F1 → F2
13
Công thức và hàm
Monday, April 9, 2012
Tham chiếu tương đối, tuyệt đối
Ví dụ về tham chiếu tuyệt đối
Như vậy muốn copy công thức từ ô F3 cho ô F4 thì chúng ta phải thay đổi trong công thức của ô
F3 như sau :
Đổi địa chỉ tham chiếu tương đối F1 thành địa chỉ tuyệt đối F$1 (cố định hàng) bằng cách di
chuyển đến ô F3 rồi nhấn phím F2 (chuyển sang chế độ chỉnh sửa nội dung của ô) sau đó di
chuyển dấu nháy đến F1 rồi nhấn phím F4 để thay đổi dạng tham chiếu tuyệt đối đến khi nào F1
chuyển thành F$1 thì nhấn Enter.
Chú ý : có 3 dạng tham chiếu tuyệt đối
- Cố định hàng có dạng A$1
- Cố định cột có dạng $A1
- Cố định cả hàng và cột $A$1
14
Công thức và hàm
Monday, April 9, 2012
Cách sử dụng hàm mảng
Thông thường sau khi thành lập công thức xong chúng ta nhấn
phím Enter là kết thúc. Nhưng khi thành lập công thức mảng
•
chúng ta phải nhấn tổ hợp phím Ctrl + Shift + Enter
Ví dụ
•
CTRL+SHIFT+ENTER
15
Công thức và hàm
Monday, April 9, 2012
Cách kiểm tra công thức
Để tìm ra lỗi sai trong công thức chúng ta sử dụng phím F9 và công
cụ Formula Auditing
Tìm lỗi bằng F9
Sau khi thiết lập công thức như trên để tính NPV trong ô B4 và chúng
ta nhấn Enter thì thông báo lỗi #VALUE! hiện ra
16
Công thức và hàm
Monday, April 9, 2012
Cách kiểm tra công thức
Cách tìm sai sót : Di chuyển đến ô B4 sau đó nhấn F2 (chế độ soạn
thảo ô) sau đó tuần tự quét chọn từng tham chiếu và nhấn phím F9
để xem từng giá trị của các ô tham chiếu đó. Từ đó chúng ta sẽ tìm ra
lỗi sai.
F9
17
Công thức và hàm
Monday, April 9, 2012
Cách kiểm tra công thức
F9
F9
Đối số này
có tham chiếu sai
Kết quả hàm bị lỗi
F9
18
Công thức và hàm
Monday, April 9, 2012
Tìm lỗi bằng Auditing
Chọn ô F4 rồi bấm Trace Precedents. Kết quả hiển thị cho thấy rõ đường mũi tên chỉ
đến các tham chiếu trong ô công thức làm cho việc phát hiện ra lỗi sai dễ dàng hơn
19
Công thức và hàm
Monday, April 9, 2012
Các chú ý khi thành lập công thức
Đặt tên ô hay dãy ô : để dễ dàng thiết lập công thức và làm cho
công thức dễ hiểu hơn
•
Ghi nhớ các dạng báo lỗi của ô công thức :
•
#NAME Do đánh vào sai tên hàm hay tên tham chiếu
#REF!
Do ô tham chiếu trong hàm bị xoá
#VALUE! Trong công thức tính toán có phần tử tính là dạng văn bản (không
phải trị số nên không tính được)
####### Trị số trong ô có số con số dài vượt quá chiều rộng cột
DIV/0!
#N/A
Trong công thức có chứa phép chia cho zero
Công thức không có kết quả
20
Công thức và hàm
Monday, April 9, 2012
Tải về để xem bản đầy đủ
21 trang
16800
Free
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Tin học ứng dụng - Chương: Công thức và hàm - Lê Viết Mẫn", để tải tài liệu gốc về máy hãy click vào nút Download ở trên
File đính kèm:
- bai_giang_tin_hoc_ung_dung_chuong_cong_thuc_va_ham_le_viet_m.pdf
