CÔNG THỨC VÀ HÀM
ĐỀ ÁN 112
Giảng viên: Th.s Phan Nam Anh
Phòng Công Nghệ Thông Tin – VDC2
E-mail: pnanh@vdc.com.vn
© VDC - Vietnam Datacommunication Company
Copyright 2004 –Vietnam Datacommunication Company – VDC
2
Mục đích
Mục đích:
Xây dựng các công thức số học cơ bản
Nhận biết các thông báo lỗi chuẩn khi sử dụng công thức
Địa chỉ tham chiếu tuyệt đối và địa chỉ tham chiếu tương đối
Sao chép công thức
Sử dụng các hàm thông dụng như ROUND, SUM, AVERAGE, MAX, MIN, IF, VLOOKUP, COUNTIF, SUMIF… trong công thức
TẠO CÔNG THỨC CƠ BẢN
© VDC - Vietnam Datacommunication Company
Copyright 2004 –Vietnam Datacommunication Company – VDC
4
TẠO CÔNG THỨC CƠ BẢN
Công thức được tạo ra để tính toán và trả lại giá trị cho ô bảng tính
Nhập ký tự “=” trước khi nhập nội dung công thức
Ký tự “=” này giúp MS-Excel nhận biết và thực hiện công thức
Copyright 2004 –Vietnam Datacommunication Company – VDC
5
TẠO CÔNG THỨC CƠ BẢN
Ví dụ, G4 = E3+E4
Thanh công thức Formula Bar
Copyright 2004 –Vietnam Datacommunication Company – VDC
6
TẠO CÔNG THỨC CƠ BẢN
Ví dụ, G4 = E3+E4
Kết quả
Copyright 2004 –Vietnam Datacommunication Company – VDC
7
TẠO CÔNG THỨC CƠ BẢN
Kết quả tính toán sẽ hiển thị tại ô
Còn nội dung (gồm cả ký tự “=“) có thể nhìn thấy trên thanh công thức Formular Bar.
Có thể chỉnh sửa nội dung công thức và cuối cùng là nhấn phím Enter để công thức được tính toán lại và trả giá trị cho ô.
Copyright 2004 –Vietnam Datacommunication Company – VDC
8
PHÉP TOÁN TRONG CÔNG THỨC SỐ HỌC
Danh sách các phép toán và các ký tự khác dùng trong công thức số học được liệt kê trong bảng sau:
Copyright 2004 –Vietnam Datacommunication Company – VDC
9
PHÉP TOÁN TRONG CÔNG THỨC SỐ HỌC
Copyright 2004 –Vietnam Datacommunication Company – VDC
10
PHÉP SO SÁNH TRONG CÔNG THỨC LOGIC
Công thức logic có kết quả trả về chỉ thuộc 1 trong 2 trạng thái sau:
True
False
Danh sách các phép so sánh dùng trong công thức logic được liệt kê trong bảng sau:
Copyright 2004 –Vietnam Datacommunication Company – VDC
11
PHÉP SO SÁNH TRONG CÔNG THỨC LOGIC
Copyright 2004 –Vietnam Datacommunication Company – VDC
12
THỨ TỰ TÍNH TOÁN
Khi tính toán Excel sẽ ưu tiên các phép toán logic trước rồi mới đến phép toán số học.
Thứ tự thực hiện các phép toán số học trong Excel có mức ưu tiên như sau:
Copyright 2004 –Vietnam Datacommunication Company – VDC
13
THỨ TỰ TÍNH TOÁN
Copyright 2004 –Vietnam Datacommunication Company – VDC
14
THỨ TỰ TÍNH TOÁN
Để làm thay đổi trật tự tính toán, chúng ta chỉ được dùng cặp dấu ngoặc đơn để nhóm các biểu thức ưu tiên tính trước
Ví dụ minh họa
=10+5*2, kết quả được 20
=(10+5)*2, kết quả được 30
Copyright 2004 –Vietnam Datacommunication Company – VDC
15
CÁCH NHẬP CÔNG THỨC VÀO Ô
Nhấp đúp chuột chọn ô
Trước tiên nhập ký tự “=“, sau đó nhập nội dung công thức
Nhấn phím Enter để kết thúc và thực hiện tính toán công thức
Copyright 2004 –Vietnam Datacommunication Company – VDC
16
NHẬN BiẾT VÀ SỬA LỖI
Copyright 2004 –Vietnam Datacommunication Company – VDC
17
NHẬN BiẾT CÁC THÔNG BÁO LỖI
#####
Lỗi xảy ra khi chiều rộng cột không đủ để hiển thị kết quả tính toán. Sửa lỗi bằng cách tăng chiều rộng cột đó hoặc thu nhỏ kích thước phông chữ
#VALUE!
Lỗi xảy ra do sử dụng dữ liệu hoặc toán tử tham gia vào công thức không đúng yêu cầu của công thức, ví dụ:
Copyright 2004 –Vietnam Datacommunication Company – VDC
18
NHẬN BiẾT CÁC THÔNG BÁO LỖI
Công thức toán học song lại tính toán trên ô chứa dữ liệu kiểu văn bản. Sửa lỗi bằng cách nhập lại địa chỉ hoặc định dạng lại ô chứa dữ liệu
Công thức sử dụng dữ liệu là địa chỉ ô chứa công thức khác. Sửa lỗi bằng cách thay đổi công thức
Copyright 2004 –Vietnam Datacommunication Company – VDC
19
NHẬN BiẾT CÁC THÔNG BÁO LỖI
#NAME?
Lỗi xảy ra khi MS-Excel không xác định được các ký tự trong công thức, ví dụ sử dụng một tên vùng ô chưa được định nghĩa.
Copyright 2004 –Vietnam Datacommunication Company – VDC
20
NHẬN BiẾT CÁC THÔNG BÁO LỖI
#N/A
Lỗi xảy ra do không có dữ liệu để tính toán.
#NUM!
Lỗi xảy ra do sử dụng dữ liệu không đúng kiểu số.
Cần định dạng lại dữ liệu tham gia vào công thức
THỰC HÀNH

TRANG 71
© VDC - Vietnam Datacommunication Company
ĐỊA CHỈ TUYỆT ĐỐI VÀ ĐỊA CHỈ TƯƠNG ĐỐI
© VDC - Vietnam Datacommunication Company
Copyright 2004 –Vietnam Datacommunication Company – VDC
23
ĐỊA CHỈ TƯƠNG ĐỐI-TUYỆT ĐÓI
Các ô là thành phần cơ sở của một bảng tính MS-Excel.
Ô có thể chứa các kết quả tính toán theo một công thức nào đó với sự tham gia của nhiều ô khác, chẳng hạn chứa tổng số của các ô trong cùng một cột hay cùng dòng
Địa chỉ ô được phép có mặt trong công thức và tự động được điều chỉnh theo thao tác sao chép công thức
Copyright 2004 –Vietnam Datacommunication Company – VDC
24
ĐỊA CHỈ TƯƠNG ĐỐI - TUYỆT ĐốI
Các loại địa chỉ
Địa chỉ tuyệt đối
Địa chỉ tham chiếu tương đối
Địa chỉ tham chiếu hỗn hợp
Copyright 2004 –Vietnam Datacommunication Company – VDC
25
ĐỊA CHỈ TƯƠNG ĐỐI-TUYỆT ĐốI
Địa chỉ tuyệt đối:
Chỉ đến một ô hay các ô cụ thể
Địa chỉ tham chiếu tương đối
Chỉ đến một ô hay các ô trong sự so sánh với một vị trí nào đó
Địa chỉ tham chiếu hỗn hợp
Gọi tắt là dịa chỉ tương đối có công thức sẽ thay đổi theo vị trí ô khi chúng ta thực hiện sao chép công thức từ một ô đến ô khác.
Copyright 2004 –Vietnam Datacommunication Company – VDC
26
ĐỊA CHỈ THAM CHIẾU TUYỆT ĐỐI
Địa chỉ tuyệt đối:
Gọi tắt là địa chỉ tuyệt đối. Dấu “$” thêm vào trước chữ cái chỉ cột hoặc trước số thứ tự dòng khi viết địa chỉ tuyệt đối trong công thức.
Lúc này, địa chỉ ô ghi trong công thức sẽ cố định không thay đổi theo thao tác sao chép công thức giữa các ô.
Copyright 2004 –Vietnam Datacommunication Company – VDC
27
ĐỊA CHỈ THAM CHIẾU HỖN HỢP
Ví dụ
Xem ví dụ trang 72 / thao tác trực tiếp
Copyright 2004 –Vietnam Datacommunication Company – VDC
28
SAO CHÉP CÔNG THỨC
Nhấp chọn ô D1 chứa công thức
Đặt con trỏ chuột vào góc dưới, bên phải của ô
Biểu tượng chuột sẽ đổi thành hình chữ thập màu đen, nét đơn
Kéo chuột theo dòng – hoặc theo cột đi qua vùng ô cần sao chép
THỰC HÀNH

TRANG 73
© VDC - Vietnam Datacommunication Company
THAO TÁC VỚI CÁC HÀM

TRANG 74
© VDC - Vietnam Datacommunication Company
Copyright 2004 –Vietnam Datacommunication Company – VDC
31
GiỚI THIỆU VỚI CÁC HÀM
Hàm có thể tham gia như một phép toán trong công thức
Hàm sẽ trả về một giá trị
Kiểu số
Kiểu ngày tháng
Kiểu xâu ký tự
…
Dạng thức chung của hàm
(đối số 1, đối số 2,…, đối số n )
Copyright 2004 –Vietnam Datacommunication Company – VDC
32
DANH SÁCH CÁC ĐỐI SỐ
Phần lớn các hàm trong Excel đều có một hoặc nhiều đối số
Đối số có thể là
một giá trị kiểu số
một xâu ký tự
Địa chỉ ô hay vùng ô
Tên vùng
Công thức
Những hàm khác
Copyright 2004 –Vietnam Datacommunication Company – VDC
33
DANH SÁCH CÁC ĐỐI SỐ
Các bước để nhập một hàm
Khi con trỏ nhập văn bản đang ở tại vị trí cần nhập hàm thì nhấn nút biểu tượng hàm fx
Hiển thị hộp thoại Paste Function
Trong hộp thoại Paste Function
Chọn nhóm hàm cần thực hiện trên khung Function Category
Chọn tên hàm cần thực hiện trên khung Function Name, nhấn nút OK
Một hộp thoại sẽ hiển thị để hướng dẫn nhập các đối số tùy thuộc vào chức năng của hàm. Nhập các đối số cần thiết
Nhấn OK
Copyright 2004 –Vietnam Datacommunication Company – VDC
34
TÍNH TỔNG NHANH
MS-Excel có khả năng tính tổng dãy giá trị số và cho ngay kết quả trên thanh trạng trái
Các bước thực hiện
Chọn vùng dữ liệu gồm các ô kiểu số
ĐỌc kết quả trên thanh trạng thái cửa sổ, hiển thị theo dạng SUM=
CÁC HÀM THƯỜNG DÙNG

TRANG 76
© VDC - Vietnam Datacommunication Company
Copyright 2004 –Vietnam Datacommunication Company – VDC
36
HÀM ROUND
Làm tròn một số đến một số thập phân nhất định
Dạng thức ROUND (Đối số 1, đối số 2)
Đối số 1: là đối số muốn làm tròn
Đối số 2: là số thập phân muốn làm tròn
Ví dụ ROUND (2.75,1) – kết quả 2.8
Màu hồng là những chữ cố định của hàm
Copyright 2004 –Vietnam Datacommunication Company – VDC
37
HÀM MAX, MIN
Cho giá trị cao nhất và thấp nhất trong danh sách đối số
Dạng thức MAX (Đối số 1, đối số 2, đối số n)
Dạng thức MIN (Đối số 1, đối số 2, đối số n)
Ví dụ MAX(G4:G13) – kết quả cho giá trị lớn nhất trong danh sách từ G4 đến G13
Màu hồng là những chữ cố định của hàm
Copyright 2004 –Vietnam Datacommunication Company – VDC
38
HÀM AVERAGE
Tính giá trị trung bình cộng các giá trị của danh sách đối số.
Dạng thức AVERAGE(Đối số 1, đối số 2,…, đối số n)
Đối số có thể là giá trị số, địa chỉ ô, vùng ô…
Ví dụ AVERAGE(A1, B5:B12) – kết quả cho giá trị trung bình trong danh sách từ B5 đến B12 và ô A1
Copyright 2004 –Vietnam Datacommunication Company – VDC
39
HÀM COUNT
Đếm những ô chứa dữ liệu số trong vùng.
Dạng thứcCOUNT(Đối số 1, đối số 2,…, đối số n)
Ví dụ COUNT(F4:F6) – Đếm những ô dữ liệu số, các ô kiểu khác không được đếm
Copyright 2004 –Vietnam Datacommunication Company – VDC
40
HÀM COUNTA
Đếm những ô chứa dữ liệu trong vùng.
Dạng thứcCOUNTA(Đối số 1, đối số 2,…, đối số n)
Ví dụ COUNTA(F4:F6) – Đếm những ô dữ liệu cùng kiểu từ ô F4 đến F6
Copyright 2004 –Vietnam Datacommunication Company – VDC
41
HÀM SUM
Tính tổng các giá trị của danh sách đối số
Dạng thứcSUM(Đối số 1, đối số 2,…, đối số n)
Ví dụ SUM(F4:F6) – tính tổng số trong phạm vi từ ô F4 đến F6
Copyright 2004 –Vietnam Datacommunication Company – VDC
42
HÀM IF
Nếu biểu thức điều kiện đúng thì hàm nhận giá trị khi đúng
Nếu không sẽ nhận giá trị khi sai
Dạng thứcIF(Biểu thức điều kiện, giá trị nếu đúng, giá trị nếu sai)
Biểu thức điều kiện là biểu thức logic bất kỳ, nhận giá trị đúng hoặc sai
THỰC HÀNH

TRANG 77
© VDC - Vietnam Datacommunication Company
Copyright 2004 –Vietnam Datacommunication Company – VDC
44
HÀM VLOOKUP
Dạng thứcVLOOKUP(Giá trị tìm kiếm, vùng bảng đối chiếu, cột kết quả trả về, sắp xếp vùng đối chiếu)
THỰC HÀNH

TRANG 78
quan trọng
© VDC - Vietnam Datacommunication Company
Copyright 2004 –Vietnam Datacommunication Company – VDC
46
HÀM SUMIF
Tính tổng theo điều kiện đơn giản
Dạng thứcSUMIF(Vùng ước lượng, điều kiện, vùng tính toán)
Vùng ước lượng: là một vùng ô
Điều kiện: có thể là
Hằng số
Địa chỉ của một ô
Hay dạng thức “>160000”
Vùng tính toán: các ô thật sự cần tính toán, nếu bỏ qua tham số này thì vùng ước lượng được lấy làm vùng tính toán
Copyright 2004 –Vietnam Datacommunication Company – VDC
47
HÀM COUNTIF
Đếm theo điều kiện đơn giản
Dạng thứcCOUNTIF(Vùng ước lượng, điều kiện)
Vùng ước lượng: là một vùng ô
Điều kiện: có thể là
Hằng số
Tọa độ của một ô
Hay dạng thức “>160000”
BÀI TẬP TỔNG HỢP

TRANG 80
© VDC - Vietnam Datacommunication Company