Giáo trình MS Excel (Bài 4)

BÀI 4
CÔNG THỨC VÀ HÀM
Mục tiêu bài học
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, địa chỉ tham chiếu tương đối
Sao chép công thức
Sử dụng một số hàm thông dụng: SUM, IF, AVERAGE, MAX, MIN, VLOOKUP,... trong công thức
1. Tạo công thức cơ bản
Tạo công thức số học cơ bản
Thanh công thức
Công thức được tạo ra để tính toàn và trả lại giá trị cho
ô bảng tính
Chú ý: Phải nhập ký tự “=”
trước khi nhập công thức
Tạo công thức cơ bản(t.t)
Phép toán trong công thức số học
Tạo công thức cơ bản(t.t)
Phép so sánh trong công thức logic
Tạo công thức cơ bản(t.t)
Thứ tự tính toán
Nhận biết và sửa lỗi
Một số lỗi phổ biến thường gặp khi thao tác với bảng tính (Học viên tham khảo thêm ở tài liệu)
Nhận diện các thông báo lỗi
#####: xảy ra khi độ rộng cột không đủ rộng
#VALUE!: lỗi xảy ra do sử dụng dữ liệu tham gia vào công thức không đúng yêu cầu của công thức
#DIV/0: lỗi xảy ra khi chia một số cho số 0
#NAME?: lỗi xảy ra khi Excel không xác định được các kí tự trong công thức
#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ố
2. Các loại địa chỉ
Địa chỉ tuyệt đối
Địa chỉ tương đối
Địa chỉ hỗn hợp
Địa chỉ tuyệt đối đối với hàng
Địa chỉ tuyệt đối đối với cột
Địa chỉ ô
Địa chỉ tương đối (Relative Address): vd A1
Địa chỉ hỗn hợp (Mixel Address): vd A$1, $A1
Địa chỉ tuyệt đối (Absolute Address): vd $A$1
Địa chỉ tuyệt đối là địa chỉ không tự động thay đổi trong khi sao chep, di chuyen
Chú ý: Có thể chọn địa chỉ cần tuyệt đối sau đó nhấn F4 nhiều lần để chọn hình thức thích hợp.
Địa chỉ tuyệt đối
Địa chỉ của một ô, vùng
Không thay đổi khi sao chép công thức
Địa chỉ tương đối
Địa chỉ của ô, vùng
Thay đổi khi sao chép công thức
Địa chỉ hỗn hợp
Địa chỉ tuyệt đối đối với hàng
Địa chỉ của ô
Thay đổi địa chỉ của cột khi sao chép công thức
Địa chỉ tuyệt đối đối với cột
Địa chỉ của ô
Thay đổi địa chỉ của hàng khi sao chép công thức
Ví dụ địa chỉ hỗn hợp
Địa chỉ tuyệt đối đối với cột
3. Giới thiệu về hàm
Hàm trả về một giá trị hay một thông báo lỗi
Dạng thức chung
(Đối số 1, đối số 2,..., đối số n)
Hàm tính
trung bình cộng
Khái niệm đối số
Các hàm trong Excel đều có một hay 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 (lồng hàm)
Ví dụ
Ô D8 chứa giá trị trung bình cộng ô D2 đến D7
Hàm Average tính trung bình cộng các giá trị trong danh sách đối số
Các bước để lập một hàm
Đặt con trỏ tại vị trí cần nhập hàm  nhấn nút biểu tượng

Nhóm hàm cần
thực hiện
Tên hàm cần
thực hiện
Tính tổng
Đặt con trỏ tại vị trí cần tính tổng
Nhấp chọn
Chọn hàm SUM
Nhấn OK để kết thúc
tính tổng

Hàm Round
Làm tròn số đến một số thập phân nhất định
=Round(đối số 1, đối số 2)

Số muốn
làm tròn
Số thập phân
muốn làm tròn
Các hàm thường dùng
Hàm Max/Min
Cho giá trị cao nhất, thấp nhất trong danh sách số
=Max(đối số 1, đối số 2,... đối số n)
=Min(đối số 1, đối số 2,... đối số n)

Hàm Average
Tính trung bình cọng các giá trị trong danh sách đối số
=Average(đối số 1, đối số 2,... đối số n)
Hàm Sum
Tính tổng các giá trị danh sách đối số
=Sum(đối số 1, đối số 2,... đối số n)
Hàm COUNT
Đếm những ô chứa dữ liệu số trong vùng
=Count(đối số 1, đối số 2,... đối số n)
Hàm COUNTA
Đếm những ô chứa dữ liệu trong vùng (kể cả dữ liệu chuỗi)
=Count(đối số 1, đối số 2,... đối số n)
Hàm If
Nếu biểu thức điều kiện đúng nhận giá trị đúng còn không nhận giá trị sai
=If(Biểu thức đk, gt đúng, gt sai)
Hàm VLOOKUP
Hàm tìm kiếm và thay thế
=Vlookup(giá trị tìm kiếm,bảng đối chiếu,cột trả kết quả,sắp xếp vùng đối chiếu)
Hàm SUMIF
Tính tổng theo điều kiện đơn giản
=Sumif(vùng ước lượng, điều kiện,vùng tính toán)
Hàm COUNTIF
Đếm theo điều kiện đơn giản
=Countif(vùng ước lượng, điều kiện)
Tổng kết
Tạo các công thức cơ bản
Hiểu thêm về địa chỉ tuyệt đối, tương đối
Tính toán trong bảng một cách nhanh chóng
Thao tác với các hàm để tính toán