Biểu thức và hàm

5. Biểu thức và hàm

5.1. Biểu thức số học

5.1.1. Khái niệm biểu thức số học

Biểu thức lập nên từ các toán hạng và các phép tính để tạo nên những giá trị mới. Biểu thức dùng để diễn đạt một công thức, một quy trình tính toán, là sự kết hợp giữa các phép toán và các toán hạng để diễn đạt một công thức toán học nào đó.

5.1.2. Tạo biểu thức số học đơn giản

Biểu thức phải bắt đầu bởi dấu '='. Khi cần lấy số liệu ở ô nào, nhấp chuột vào ô đó hoặc gõ địa chỉ ô đó vào công thức.

Ví dụ: Tính lương và tỷ lệ theo công thức:

Lương = Số ngày công * Lương ngày

Tỷ lệ = Lương / Tổng cộng Lương

Cách thực hiện như sau:

Tính lương cho nhân viên đầu tiên ta làm như sau:

- Đưa con trỏ về ô E2

- Gõ dấu ‘=’

- Nhấp chuột vào ô C2 (hoặc gõ C2)

- Gõ dấu ‘*’

- Nhấp chuột vào ô D2 (hoặc gõ D2)

- Nhấn Enter

- Để tính lương cho những nhân viên còn lại, đưa con trỏ về ô E2, sao chép công thức cho tới ô E7.

- Tương tự tính tỷ lệ: cách làm tương tự như trên.

5.1.3. Các lỗi thường gặp

Biểu thức bắt đầu bởi dấu bằng (=). Sau khi nhấn phím Enter công thức nhập vào chỉ thể hiện trên thanh công thức còn kết quả được thể hiện trong ô.

Một số thông báo lỗi thường gặp khi sử dụng công thức:

- ####: Cột quá hẹp

- Err: 503 Chia cho 0, sai về kiểu của toán hạng

- #NAME? Thực hiện phép tính với một biến không xác định (tên không gắn với một ô hay vùng nào cả)

- #N/A Tham chiếu đến ô rỗng hoặc không có trong danh sách

5.2. Hàm

5.2.1. Khái niệm hàm, cú pháp, cách nhập hàm

Khái niệm hàm:

Hàm dùng để tính toán và trả về một giá trị, trong ô chứa hàm sẽ trả về một giá trị, một chuỗi ký tự hoặc một thông báo lỗi, … Excel có một tập hợp các hàm rất phong phú và được phân loại theo từng nhóm phục vụ cho việc tính toán trên nhiều kiểu dữ liệu và nhiều mục đích khác nhau.

Cú pháp:

= TÊN HÀM ([Danh sách đối số])

Đa số các hàm của Excel đều có đối số nhưng cũng có những hàm không có đối số. Nếu hàm có nhiều đối số thì giữa các đối số phải được phân cách bằng ký hiệu phân cách, các ký hiệu phân cách được quy định trong Control Panel… với mặc định là dấu phẩy.

Cách nhập hàm

- Cách 1: Nhập trực tiếp từ bàn phím

· Đặt trỏ chuột tại ô muốn nhập hàm.

· Nhập dấu = (hoặc dấu @, hoặc dấu +).

· Nhập tên hàm cùng các đối số theo đúng cú pháp.

· Nhấn Enter để kết thúc.

- Cách 2: Thông qua hộp thoại Insert Function

· Đặt trỏ tại ô muốn nhập hàm.

· Nhấp chuột chọn Insert Function hoặc Shift+F3

Hình 4.5. Hộp thoại Insert Function

· Chọn Group hàm trong danh sách Function category.

· Chọn hàm cần sử dụng trong danh sách Function name.

· Nhấp chọn OK để chọn hàm.

· Tùy theo hàm được chọn, Excel sẽ mở hộp thoại kế tiếp cho phép nhập các đối số (nhập hoặc quét chọn). Tiến hành nhập các đối số. Ví dụ danh sách các đối số cần nhập của hàm IF:

5.2.2. Toán tử so sánh =, < , >

5.2.3. Các hàm cơ bản

5.2.3.1. Hàm SUM

- Cú pháp: =SUM(number1, number2, …)

- Công dụng: Tính tổng các giá trị chỉ định. (number1, number2, … có thể là các hằng số, biểu thức, địa chỉ ô, địa chỉ vùng, …)

5.2.3.2. Hàm AVERAGE

- Cú pháp: =AVERAGE(number1, number2,…)

- Công dụng: lấy giá trị trung bình cộng của các số chỉ định. (number1, number2,… có thể là các hằng số, biểu thức, địa chỉ ô, địa chỉ vùng,…)

5.2.3.3. Hàm MIN

- Cú pháp: =MIN(number1, number2,…)

- Công dụng: lấy giá trị nhỏ nhất của các số chỉ định. (number1, number2, … có thể là các hằng số, biểu thức, địa chỉ ô, địa chỉ vùng,…)

5.2.3.4. Hàm MAX

- Cú pháp: =MAX(number1, number2,…)

- Công dụng: lấy giá trị lớn nhất của các số chỉ định. (number1, number2, … có thể là các hằng số, biểu thức, địa chỉ ô, địa chỉ vùng,…)

5.2.3.5. Hàm COUNT

- Cú pháp: =COUNT(value1, value2,…)

- Công dụng: Đếm các giá trị số trong vùng chỉ định. (value1, value2, … có thể là các hằng số, biểu thức, địa chỉ ô, địa chỉ vùng, …)

5.2.3.6. Hàm COUNTA

- Cú pháp: =COUNTA(value1, value2, …)

- Công dụng: Đếm các ô có dữ liệu trong vùng chỉ định (value1, value2, … có thể là các hằng số, biểu thức, địa chỉ ô, địa chỉ vùng, …)

5.2.3.7. Hàm ROUND

- Cú pháp: =ROUND(number, num_digits)

- Công dụng:

· Làm tròn số number tại vị trí num-digits.

· Vị trí làm tròn num_disgits tương tự trục số

· Nguyên tắc làm tròn:

Nếu ký số bên phải vị trí làm tròn num_digits ≥ 5 thì lấy số tại vị trí num_digits sẽ cộng thêm 1.

Ngược lại, nếu < 5 sẽ bị cắt bỏ (đối với phần thập phân) hoặc làm tròn về 0 (đối với phần nguyên)

Ví dụ:

=ROUND(15271.382, -2) à 15300

=ROUND(15271.382, 0) à 15271

=ROUND(15271.385, 2) à 15271.39

5.2.4. Hàm điều kiện IF

- Cú pháp: =IF(logical_test, value if true, value if false)

- Công dụng: Kiểm tra biểu thức điều kiện logical_test:

· Nếu là TRUE: trả về giá trị value if true.

· Nếu là FALSE: trả về giá trị value if false.

Hàm IF đơn

Hàm IF lồng

5.2.5. Các hàm ngày (DAY, MONTH, YEAR, NOW)

5.2.5.1. Hàm DAY

- Cú pháp: =DAY(serial_number)

- Công dụng: Trả về giá trị ngày của ngày serial_number

Ví dụ: =DAY(A1) à 13

5.2.5.2. Hàm MONTH

- Cú pháp: =MONTH(serial_number)

- Công dụng: Trả về giá trị tháng của ngày serial_number

Ví dụ: =MONTH(A1) à 12

5.2.5.3. Hàm YEAR:

- Cú pháp: =YEAR(serial_number)

- Công dụng: Trả về giá trị năm của ngày serial_number

Ví dụ: =YEAR(A1) à 2018

5.2.6. Các hàm tìm kiếm (VLOOKUP, HLOOKUP)

5.2.6.1. Hàm VLOOKUP

- Cú pháp: =VLOOKUP(lookup_value, table_array, column_index_num, range_lookup)

- Công dụng: Dò tìm giá trị lookup_value trong số các giá trị ở cột đầu tiên trong bảng dò table_array.

Nếu tìm thấy, trả về giá trị cột column_index_num và cùng dòng với vị trí tìm thấy

Nếu không tìm thấy, trả về giá trị #N/A

- Trong đó:

· lookup_value: giá trị dò tìm.

· table_array: bảng dò là vùng có địa chỉ tuyệt đối. Cột đầu tiên bên trái chứa danh sách các giá trị dò tìm. Các cột còn lại chứa giá trị tương ứng để tham chiếu.

· column_index_num: chỉ số của cột muốn lấy giá trị trả về trong bảng dò ( cột đầu tiên là 1, cột kế tiếp là 2,… tính từ bên trái qua).

· column_index_num >1.

· range_lookup: giá trị kiểu số chỉ định cách thức dò tìm:

0 (FALSE): dò tìm giá trị đầu tiên đúng bằng lookup_value. Table_array không cần sắp xếp.

1 (TRUE): tìm giá trị lớn nhất ≤ lookup_value. Cột đầu tiên của table_array phải được sắp thứ tự tăng dần.

5.2.6.2. Hàm HLOOKUP

- Cú pháp: =HLOOKUP(lookup_value, table_array, row_index_num,range_lookup)

- Công dụng: Dò tìm giá trị lookup_value trong số các giá trị ở dòng đầu tiên trong bảng dò table_array

Nếu tìm thấy, trả về giá trị dòng row_index_num và cùng cột với vị trí tìm thấy.

Nếu không tìm thấy, trả về giá trị #N/A

- Trong đó:

· lookup_value: giá trị dò tìm

· table_array: bảng dò là vùng có địa chỉ tuyệt đối. Dòng đầu tiên trên cùng chứa danh sách các giá trị dò tìm. Các dòng còn lại chứa các giá trị tương ứng để tham chiếu.

· row_index_num: chỉ số của dòng muốn lấy giá trị trả về trong bảng dò ( dòng đầu tiên là 1, dòng kế tiếp là 2,… tính từ trên xuống dưới)

· row_index_num > 1.

· range_lookup: giá trị kiểu số chỉ định cách thức dò tìm.

0 (FALSE): dò tìm giá trị đầu tiên đúng bằng lookup_value. Table_array không cần sắp xếp.

1 (TRUE): tìm giá trị lớn nhất ≤ lookup_value. Dòng đầu tiên của table_array phải được sắp thứ tự tăng dần.