Bài giảng Tin học ứng dụng nâng cao - Chương: Công thức và hàm - Lê Viết Mẫn

 Công thức và hàm
 v 1.1 - 04/2013
 Lê Viết Mẫn - lvman@hce.edu.vn 1 Công thức và hàm
Sunday, April 21, 13
 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
 Lê Viết Mẫn - lvman@hce.edu.vn 2 Công thức và hàm
Sunday, April 21, 13
 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
 Lê Viết Mẫn - lvman@hce.edu.vn 3 Công thức và hàm
Sunday, April 21, 13
 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
 Lê Viết Mẫn - lvman@hce.edu.vn 4 Công thức và hàm
Sunday, April 21, 13
 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
 Lê Viết Mẫn - lvman@hce.edu.vn 5 Công thức và hàm
Sunday, April 21, 13
 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 : ,
 Lê Viết Mẫn - lvman@hce.edu.vn 6 Công thức và hàm
Sunday, April 21, 13
 Toán tử và dạng số liệu
 Lê Viết Mẫn - lvman@hce.edu.vn 7 Công thức và hàm
Sunday, April 21, 13
 Toán tử và dạng số liệu
 Lê Viết Mẫn - lvman@hce.edu.vn 8 Công thức và hàm
Sunday, April 21, 13
 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
 Lê Viết Mẫn - lvman@hce.edu.vn 9 Công thức và hàm
Sunday, April 21, 13
 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
 Lê Viết Mẫn - lvman@hce.edu.vn 10 Công thức và hàm
Sunday, April 21, 13
 Tham chiếu tương đối và 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
 Lê Viết Mẫn - lvman@hce.edu.vn 11 Công thức và hàm
Sunday, April 21, 13
 Tham chiếu tương đối và 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
 Lê Viết Mẫn - lvman@hce.edu.vn 12 Công thức và hàm
Sunday, April 21, 13
 Tham chiếu tương đối và 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
 Lê Viết Mẫn - lvman@hce.edu.vn 13 Công thức và hàm
Sunday, April 21, 13
 Tham chiếu tương đối và 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
 Lê Viết Mẫn - lvman@hce.edu.vn 14 Công thức và hàm
Sunday, April 21, 13
 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
 Lê Viết Mẫn - lvman@hce.edu.vn 15 Công thức và hàm
Sunday, April 21, 13
 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
 Lê Viết Mẫn - lvman@hce.edu.vn 16 Công thức và hàm
Sunday, April 21, 13
 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
 Lê Viết Mẫn - lvman@hce.edu.vn 17 Công thức và hàm
Sunday, April 21, 13
 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
 Lê Viết Mẫn - lvman@hce.edu.vn 18 Công thức và hàm
Sunday, April 21, 13
 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
 Lê Viết Mẫn - lvman@hce.edu.vn 19 Công thức và hàm
Sunday, April 21, 13
 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! Trong công thức có chứa phép chia cho zero
 #N/A Công thức không có kết quả
 Lê Viết Mẫn - lvman@hce.edu.vn 20 Công thức và hàm
Sunday, April 21, 13
 Cảm ơn sự chú ý
 Câu hỏi ?
 Lê Viết Mẫn - lvman@hce.edu.vn 21 Công thức và hàm
Sunday, April 21, 13