Một số hàm trong Excel? | Tóm tắt lý thuyết Tin học đại cương | Đại học Kinh Tế, Đại học Đà Nẵng

Trình bày cú pháp chung và cách sử dụng một số hàm thông dụng trong Excel. Tóm tắt lý thuyết môn học Tin học đại cương giúp bạn khái quát kiến thức môn học, ôn tập và đạt điểm cao.

Một số hàm trong Excel?

CÚ PHÁP CHUNG VÀ CÁCH SỬ DỤNG

1. Xem danh sách các hàm

Muốn xem danh sách các hàm thì Click chọn nút Paste Function trên thanh Standard hoặc chọn menu Insert/ Function hoặc gõ tổ hợp phím Shift + F3. Hộp thoại Paste Function sẽ xuất hiện như hình 

Tài liệu VietJack

2. Cú pháp chung

= 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 quy định trong Windows (thường sử dụng dấu phẩy). Số đối số của hàm nhiều hay ít
là tuỳ theo từng hàm cụ thể.

Đối số của hàm có thể là:

  • Các giá trị số: =SUM(10, 12, 6, 8, -7)
  • Địa chỉ ô, địa chỉ vùng: =MAX(A2, A4, C3, D2:D5, 6)
  • Một chuỗi ký tự: =RIGHT(“Dai hoc Can Tho”, 7)
  • Một biểu thức logic: =IF(A4 >= $D$2, 7, 8)
  • Một hàm khác: =IF(C2>=0,SQRT(C2),“Số âm không có căn bậc hai!”)
  • Tên của một vùng: =A4 * DON_GIA

3. Cách sử dụng hàm

Nếu công thức bắt đầu là một hàm, thì phải có dấu = (hoặc dấu @, hoặc dấu +) ở phía trước. Nếu hàm là đối số của một hàm khác thì không cần nhập các dấu trên.

Có 2 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.

- Gõ Enter để kết thúc.

Tài liệu VietJack

Cách 2: thông qua hộp thoại Paste Function

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

- Click chọn nút Paste Function trên thanh Standard hoặc chọn menu Insert/ Function hoặc gõ tổ hợp phím Shift + F3. Hộp thoại Paste Function sẽ xuất hiện như hình

- Chọn nhóm hàm trong danh sách Function category.

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

- Click OK để chọn hàm.

- Tuỳ 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ố. Tiến hành nhập các đối số.

- Click OK để kết thúc

Tài liệu VietJack

CÁC HÀM THÔNG DỤNG

1. Các hàm toán học (Math & Trig)

Cú pháp Ý nghĩa và ví dụ
ABS(number)

Trả về giá trị tuyệt đối của một số thực.

=ABS(12 - 20)  8

INT(number)

Trả về số nguyên lớn nhất không vượt quá number.

=INT(5.6)  5

=INT(-5.6)  -6

MOD(number, divisor)

Trả về số dư của phép chia nguyên number cho divisor (number, divisor là các số nguyên).

=MOD(5, 3)  2

ODD(number)

Làm tròn trên tới một số nguyên lẻ gần nhất.

=ODD(3.6)  5

=ODD(-2.2) -3

PRODUCT(number1, number2, ...)

Tính tích của các giá trị trong danh sách tham số.

=PRODUCT(2, -6, 3, 4)  -144

RAND( )

Trả về một số ngẫu nhiên trong khoảng từ 0 đến 1.

=RAND( )  Số ngẫu nhiên

ROUND(number, num_digits)

Làm tròn số number với độ chính xác đến num_digits chữ số thập phân (với qui ước 0 là làm tròn tới hàng đơn vị, 1 là lấy 1 chữ số thập phân, -1 là làm tròn tới hàng chục, ...).

=ROUND(5.13687, 2)  5.14

=ROUND(145.13687, -2)  100

SQRT(number)

Tính căn bậc 2 của một số dương number.

=SQRT(36)  6

SUM(number1, number2, ...)

Tính tổng của các giá trị trong danh sách tham số.

=SUM(2, -6, 8, 4)  8

SUMIF(range, criteria [, sum_range])

Tính tổng các ô thỏa mãn điều kiện.

- range: vùng mà điều kiện sẽ được so sánh.

- criteria: chuỗi mô tả điều kiện. Ví dụ: "10", ">15", "<20", …

- sum_range: vùng được tính tổng. Các ô trong vùng này sẽ được tính tổng nếu các ô tương ứng trong vùng range thỏa điều kiện. Nếu không có sum_range thì vùng range sẽ được tính.

=SUMIF(C4:C12, “>=6”, F4:F12)

=SUMIF(C4:C12, “>=6”)

=SUMIF(B4:B12, “NV”, G4:G12)

2. Các hàm thống kê (Statistical)

Cú pháp Ý nghĩa và ví dụ
MAX(number1, number2, ...)

Trả về giá trị lớn nhất của các giá trị số trong
danh sách tham số.

=MAX(1, 2, 3, 5)  5

MIN(number1, number2, ...)

Trả về giá trị nhỏ nhất của các giá trị số trong
danh sách tham số.

=MIN(1, 2, 3, 5)  1

AVERAGE(number1, number2, ...)

Trả về giá trị trung bình cộng của các số trong
danh sách tham số.

=AVERAGE(1, 2, 3, 5)  2.75

COUNT(value1, value2, ...)

Đếm số các giá trị số trong danh sách tham số.

=COUNT(2, “hai”, 4, -6)  3

COUNTA(value1, value2, ...)

Đếm số các ô không rỗng trong danh sách tham
số.

=COUNT(2, “hai”, 4, -6)  4

COUNTBLANK(range)

Đếm số các rỗng trong vùng range.

=COUNTBLANK(B4:B12)

COUNTIF(range, criteria)

Đếm các ô thỏa mãn điều kiện criteria trong vùng range.

- range: là vùng mà điều kiện sẽ được so sánh.

- criteria: là chuỗi mô tả điều kiện. Ví dụ: "10",
">15", "<20".

=COUNTIF(B4:B12, “>=6”)

RANK(number, ref [, order])

=RANK(F4, $F$4:$F$12, 0)

=RANK(G4, $G$4:$G$12, 1)

3. Các hàm Logic (Logical)

Cú pháp Ý nghĩa và ví dụ
AND(logical1, logical2, …)

Trả về giá trị TRUE nếu tất cả các điều kiện đều là
TRUE.

=AND(3>2, 5<8, 9>-12)  TRUE

OR(logical1, logical2, …)

Trả về giá trị TRUE nếu có ít nhất một điều kiện là
TRUE.

=OR(2>3, 12<8, 9>3)  TRUE

=OR(2>3, 12<8, -9>3)  FALSE

NOT(logical)

Lấy phủ định của giá trị logical.

=NOT(2>3)  TRUE

IF(logical_test,
value_if_true,
value_if_false)

Trả về giá trị thứ nhất value_if_true nếu điều kiện
logical_test là TRUE, ngược lại sẽ trả về giá trị thứ hai
value_if_false.

=IF(A1 >=5, “Đậu”,”Rớt”)

Nếu giá trị tại A1 >= 5 thì kết quả của hàm là Đậu.
Ngược lại nếu giá trị ở ô A1 < 5 thì kết quả là Rớt.

4. Các hàm xử lý chuỗi (Text)

Cú pháp Ý nghĩa và ví dụ
LOWER(text)

Chuyển chuỗi text thành chữ thường.

=LOWER(“Dai hoc CAN Tho”)  dai hoc can tho

UPPER(text)

Chuyển chuỗi text thành chữ in hoa.

=UPPER(“Dai hoc CAN Tho”)  DAI HOC CAN THO

PROPER(text)

Đổi các ký tự đầu của mỗi từ trong chuỗi text thành chữ in hoa, còn lại đều là chữ thường.

=PROPER(“Dai hoc CAN Tho”)  Dai Hoc Can Tho

TRIM(text)

Cắt bỏ các ký tự trống vô ích trong chuỗi text.

=TRIM(“ Can Tho ”) Can Tho

LEN(text)

Trả về độ dài của chuỗi text (số ký tự trong chuỗi text).

=LEN(“Dai hoc CAN Tho”)  15

LEFT(text, num_chars)

Trả về num_char ký tự bên trái chuỗi text.

=LEFT(“Dai hoc CAN Tho”, 7)  Dai hoc

RIGHT(text, num_chars)

Trả về num_char ký tự bên phải chuỗi text.

=RIGHT(“Dai hoc CAN Tho”, 7)  CAN Tho

MID(text, start_num,
num_chars)

Trả về chuỗi ký tự có độ dài num_chars bắt đầu từ vị trí start_num của chuỗi text.

=MID(“Dai hoc CAN Tho”, 5, 3)  hoc

VALUE(text) = VALUE("123") + 2  125
FIND(find_text, within_text
[, start_num])

Trả về vị trí xuất hiện (nếu có) của find_text trong
within_text (bắt đầu tìm từ vị trí start_num).

Chú ý:
- Nếu không có start_num thì vị trí bắt đầu tìm từ đầu
chuỗi.
- Hàm FIND phân biệt chữ in hoa và chữ thường.
- Nếu không tìm thấy find_text thì sẽ trả về lỗi

#VALUE!

=FIND(“Excel”, “Microsoft Excel”)  11

=FIND(“Excel”, “Microsoft Excel”, 6)  11

=FIND(“excel”, “Microsoft Excel”, 6)  #VALUE!

SEARCH(find_text,
within_text [, start_num])

Tương tự như hàm FIND nhưng không phân biệt chữ in
hoa hay thường.

=SEARCH(“Excel”, “Microsoft Excel”)  11

=SEARCH(“excel”, “Microsoft Excel”)  11

REPLACE(old_text,
num_start, num_chars,
new_text)

Thay thế num_chars ký tự trong old_text bằng new_text bắt đầu từ vị trí num_start.

=REPLACE(“Ngon ngu lap trinh”, 10, 3, “chuong”)
 Ngon ngu chuong trinh

5. Các hàm ngày và giờ (Date & Time)

Giả sử ô A1 chứa ngày 28/09/2004 (Thứ ba)

Cú pháp Ý nghĩa và ví dụ
TODAY( )

Trả về ngày hiện hành của hệ thống.

=TODAY( )  Tuỳ vào ngày hiện hành của hệ thống.

NOW( )

Trả về ngày và giờ hiện hành của hệ thống.

=NOW( )  Tuỳ vào ngày và giờ hiện hành của hệ thống.

DAY(date)

Trả về giá trị ngày trong tháng của biểu thức ngày date.

=DAY(A1)  28

MONTH(date)

Trả về giá trị tháng trong năm của biểu thức ngày date.

=MONTH(A1)  9

YEAR(date)

Trả về giá trị năm của biểu thức ngày date.

=YEAR(A1) 2004

WEEKDAY(date)

Trả về số thứ tự ngày trong tuần của biểu thức date.

Giá trị 1: Sunday, 2:Monday, ..., 7: Saturday.

=WEEKDAY(A1)  3

DATEVALUE(date_text)

Đổi chuỗi ngày date_text (theo qui ước nhập ngày) thành trị số ngày.

Ghi chú: ta có thể định dạng kết quả trên thành dạng
Date bằng cách sử dụng menu Format/Cells.

= DATEVALUE("22/8/55")  20323  22/8/55

DATE(year, month, day)

Trả về giá trị dạng Date theo quy định của hệ thống.

=DATE(2004,09,28)  28/09/2004

=DATE(04,9,28)  28/09/2004

TIME(hour, minute,
second)

Trả về giá trị dạng Time.

=TIME(8,25,28)  8:25:28 AM

=TIME(17,2,46)  5:2:46 PM

6. Các hàm tìm kiếm (Lookup & Reference)

6.1 VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Tìm giá trị lookup_value trong cột trái nhất của bảng table_array theo chuẩn dò tìm

range_lookup, trả về trị tương ứng trong cột thứ col_index_num (nếu tìm thấy).

range_lookup = 1 (mặc nhiên):

Tìm tương đối, danh sách các giá trị dò tìm của bảng table_array phải sắp xếp theo thứ tự tăng dần

Nếu tìm không thấy sẽ trả về giá trị lớn nhất nhưng nhỏ hơn lookup_value.

range_lookup = 0:

Tìm chính xác, danh sách các giá trị dò tìm của bảng table_array không cần sắp xếp thứ tự.

Nếu tìm không thấy sẽ trả về lỗi #N/A.

6.2 HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

Tương tự như hàm VLOOKUP nhưng tìm giá trị lookup_value trong dòng trên cùng của bảng table_array theo chuẩn dò tìm range_lookup, trả về trị tương ứng trong dòng thứ row_index_num (nếu tìm thấy)

Ví dụ: Cho bảng tính với số liệu như sau:

  A B C D E F
1 A01 5   12 16 10
2 C02 6   15 20 24
3 B75 8   25 22 18
4            
5 A02 10   A01 B75 D25
6 B555 12   CẦN THƠ GẠO
7 D25 15   TRẮNG NƯỚC TRONG
=VLOOKUP("B75", A1:B3, 2, 0) Æ 8 = HLOOKUP(16, D1:F3, 3, 0) Æ 22
=VLOOKUP("B8", A1:B3, 2, 0) Æ #N/A = HLOOKUP(15, D1:F3, 3, 0) Æ #N/A
=VLOOKUP("B85", A1:B3, 2, 1) Æ 5 = HLOOKUP(15, D1:F3, 3, 1) Æ 25
=VLOOKUP("B85", A1:B3, 2) Æ 5 = HLOOKUP(15, D1:F3, 3) Æ 25
=VLOOKUP(A6, A5:B7, 2, 0) Æ 12 = HLOOKUP(F5, D5:F7, 2, 0) Æ GẠO
=VLOOKUP("B555", A5:B7, 2, 0) Æ 12 = HLOOKUP(“B75”, D5:F7, 3, 1) Æ NƯỚC
=VLOOKUP("B85", A5:B7, 2, 1) Æ 12 = HLOOKUP(“E95”, D5:F7, 2, 0) Æ #N/A
=VLOOKUP("E05", A5:B7, 2) Æ 15 = HLOOKUP(“E95”, D5:F7, 3) Æ TRONG

6.3 MATCH(lookup_value, lookup_array, match_type): trả về vị trí (nếu tìm được) của lookup_value trong mảng lookup_array theo cách tìm match_type

match_type = 1:

Tìm tương đối, danh sách các giá trị dò tìm của bảng table_array phải sắp xếp theo thứ tự tăng dần

Nếu tìm không thấy sẽ trả về vị trí của giá trị lớn nhất nhưng nhỏ hơn
lookup_value

match_type = 0:

Tìm chính xác, danh sách các giá trị dò tìm của bảng table_array không cần sắp xếp thứ tự

Nếu tìm không thấy sẽ trả về lỗi #N/A

match_type = -1:

Tìm tương đối, danh sách phải sắp xếp các giá trị dò tìm của bảng table_array theo thứ tự giảm dần

Nếu tìm không thấy sẽ trả về vị trí của giá trị nhỏ nhất nhưng lớn hơn
lookup_value

6.4 INDEX(array, row_num, column_num): trả về giá trị của ô ở hàng thứ row_num, cột thứ column_num trong mảng array.

7. Các hàm thông tin (ISfunction)

Các hàm thông tin dùng để kiểm tra xem kiểu của một giá trị hay của một ô có thỏa mãn một điều kiện nào đó không. Chẳng hạn: ô dữ liệu có phải là giá trị số không? Có phải là chuỗi ký tự không? ...

Các hàm thông tin luôn trả về một trong hai giá trị TRUE hoặc FALSE. Như vậy các hàm này có thể đáp ứng được trong các trường hợp mà có một số dữ liệu ngoại lệ trong một bảng dữ liệu cần tính toán.

ISBLANK(value): trả về giá trị TRUE nếu value là giá trị rỗng (blank), ngược lại thì trả về giá trị FALSE

ISERROR(value): trả về giá trị TRUE nếu value là một lỗi bất kỳ, ngược lại thì trả về giá trị FALSE.

ISLOGICAL(value): trả về giá trị TRUE nếu value là một giá trị logic, ngược lại thì trả về giá trị FALSE.

ISNA(value): trả về giá trị TRUE nếu value là lỗi #N/A, ngược lại thì trả về giá trị FALSE.

ISNUMBER(value): trả về giá trị TRUE nếu value là giá trị số, ngược lại thì trả về giá trị FALSE.

ISTEXT(value): trả về giá trị TRUE nếu value là một một chuỗi, ngược lại thì trả về giá trị FALSE.

8. Ví dụ về cách sử dụng hàm

8.1 Hàm IF(logical_test, value_if_true, value_if_false)

Ví dụ 1:

=IF(B1 >= 5, “Đậu”, “Rớt”)

Excel sẽ kiểm tra biểu thức B1 >= 5, nếu biểu thức đúng (giá trị tại ô B1 là >= 5) thì sẽ in ra “Đậu” và kết thúc hàm, ngược lại sẽ in ra “Rớt” và kết thúc hàm.

Ví dụ 2:

= IF(B1 > 0, “Số dương”, IF(B1 = 0, “Số không”, “Số âm”))

- Excel sẽ kiểm tra biểu thức B1 > 0, nếu biểu thức đúng thì sẽ in ra “Số dương” và kết thúc hàm, ngược lại sẽ xét tiếp biểu thức B1 = 0.

- Nếu biểu thức B1 = 0 là đúng thì sẽ in ra “Số không” và kết thúc hàm, ngược lại sẽ in ra “Số âm” và kết thúc hàm.

8.2 Hàm VLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

Ví dụ: Cho dữ liệu như bảng dưới đây:

Tài liệu VietJack

Yêu cầu:

1) Tính cột Học bổng dựa vào cột Xếp loại và BẢNG HỌC BỔNG.

2) Tính cột Trợ cấp = Học bổng * Tỉ lệ

Trong đó Tỉ lệ được tính nhờ vào cột Mã TC và BẢNG TRỢ CẤP.

3) Tính cột Tổng cộng = Học bổng + Trợ cấp

Giải

1) Tính cột Học bổng

+ Trước hết ta viết công thức cho ô D11:

Lấy giá trị trong ô C11 (lookup_value) để dò trong vùng $C$3:$D$6 (table_array), trong bảng này ta muốn lấy cột Học bổng tức là cột thứ 2 (col_index_num), do trong BẢNG HỌC BỔNG cột Xếp loại đã sắp xếp theo thứ tự tăng dần nên ta có thể dò tìm tương đối (range_lookup là 1 hoặc có thể bỏ qua).

Vì vùng C3:D6 sử dụng chung để dò tìm nên phải lấy địa chỉ tuyệt đối. Ta được công thức cho ô D11 như sau:

=VLOOKUP(C11, $C$3:$D$6, 2, 1) hoặc =VLOOKUP(C11, $C$3:$D$6, 2)

+ Sao chép công thức tại ô D11 đến vùng D12:D18.

2) Tính cột Trợ cấp

+ Trước hết ta viết công thức cho ô F11:

Để tính Tỉ lệ ta lấy giá trị trong ô E11 (lookup_value) để dò trong vùng $F$3:$G$5 (table_array), trong bảng này ta muốn lấy cột Tỉ lệ tức là cột thứ 2 (col_index_num), do trong BẢNG TRỢ CẤP cột Mã TC chưa được sắp xếp nên ta phải dò tìm tuyệt đối (range_lookup là 0).

Ta được công thức cho ô F11 như sau: =D11 * VLOOKUP(E11, $F$3:$G$5, 2, 0)

+ Sao chép công thức tại ô F11 đến vùng F12:F18.

3) Tính cột Tổng cộng

+ Trước hết ta viết công thức cho ô G11: =D11 + F11

+ Sao chép công thức tại ô G11 đến vùng G12:G18.

Xem thêm 

Tóm tắt lý thuyết Tin học đại cương

Phần I: NHỮNG HIỂU BIẾT CƠ BẢN VỀ TIN HỌC

Tóm tắt lý thuyết Tin học đại cương: Thông tin và xử lý thông tin 

Tóm tắt lý thuyết Tin học đại cương: Cấu trúc tổng quát của máy tính điện tử 

Tóm tắt lý thuyết Tin học đại cương: Hệ điều hành 

Tóm tắt lý thuyết Tin học đại cương: Quản lý dữ liệu bằng Windows Explorer 

Tóm tắt lý thuyết Tin học đại cương: Sử dụng tiếng việt trong Windows 

Tóm tắt lý thuyết Tin học đại cương: Bảo vệ sữ liệu và phòng chống Virus

Phần III: XỬ LÝ BẢNG TÍNH VỚI MICROSOFT EXCEL

Tóm tắt lý thuyết Tin học đại cương: Giới thiệu Microsoft Excel 

Tóm tắt lý thuyết Tin học đại cương: Các thao tác cơ bản 

Tóm tắt lý thuyết Tin học đại cương: Một số hàm trong Excel 

Tóm tắt lý thuyết Tin học đại cương: Thao tác trên cơ sở dữ liệu 

Tóm tắt lý thuyết Tin học đại cương: Tạo biểu đồ trong Excel 

Tóm tắt lý thuyết Tin học đại cương: Định dạng và in ấn trong Excel 

Phần IV: TRÌNH DIỄN VỚI MICROSOFT POWER POINT

Tóm tắt lý thuyết Tin học đại cương: Giới thiệu Microsoft PowerPoint 

Tóm tắt lý thuyết Tin học đại cương: Cập nhập và định dạng 

Tóm tắt lý thuyết Tin học đại cương: Làm việc với các trình diễn 

Phần V: SỬ DỤNG DỊCH VỤ WEB VÀ EMAIL

Tóm tắt lý thuyết Tin học đại cương: Internet và dịch vụ World Wide Web 

Tóm tắt lý thuyết Tin học đại cương: Dịch vụ thư điện tử

Việc làm dành cho sinh viên:

Việc làm thực tập sinh IT

Việc làm gia sư các môn cập nhật theo ngày mới nhất

Việc làm thêm nhân viên phục vụ nhà hàng/ quán cafe dành cho sinh viên

Việc làm gia sư, trợ giảng Tiếng anh mới nhất

Mức lương của thực tập sinh IT là bao nhiêu?

Bình luận (0)

Đăng nhập để có thể bình luận

Chưa có bình luận nào. Bạn hãy là người đầu tiên cho tôi biết ý kiến!