Thủ thuật CNTT

Các hàm cơ bản trong Excel được sử dụng nhiều nhất

Excel là một phần mềm rất quen thuộc với sinh viên và nhân viên văn phòng. Nó là một công cụ hữu ích để thực hiện các phép tính, sắp xếp dữ liệu và trình bày thông tin trên bảng tính một cách nhanh chóng và thuận tiện. Việc tổng hợp các hàm cơ bản trong Excel thông qua bài viết này sẽ giúp bạn hiểu rõ hơn về khả năng và tính linh hoạt của công cụ này. Cùng tham khảo ngay nhé!

1. Các hàm cơ bản trong Excel về tính toán

Hàm SUM: Hàm tính tổng các đối số

Hàm SUM trong Excel là một công cụ quan trọng được sử dụng để tính tổng các đối số trên bảng tính. Việc sử dụng hàm này giúp tiết kiệm thời gian và công sức so với việc tính tổng thủ công.

Cú pháp của hàm SUM là: =SUM(number1, number2, ...)

Chức năng của nó là tính tổng của các số được chỉ định (number1, number2, ...) trên bảng tính.

Trong đó, các số (number1, number2, ...) là các đối số mà bạn muốn tính tổng.

Lưu ý rằng:

Giá trị logic TRUE được coi là 1, và FALSE là 0.
Nếu đối số là một mảng hoặc tham chiếu, chỉ các giá trị số trong mảng hoặc tham chiếu đó mới được tính. Các giá trị khác sẽ bị bỏ qua.

Hàm MIN/MAX: Hàm cho giá trị lớn nhất, nhỏ nhất

Hàm MIN/MAX trong Excel được sử dụng để tìm giá trị lớn nhất và nhỏ nhất trong một tập hợp các số hoặc vùng dữ liệu.

Cú pháp của hàm MAX và hàm MIN là:

MAX(number1, number2, ...):Trả về giá trị lớn nhất trong số các đối số hoặc vùng dữ liệu.
MIN(number1, number2, ...): Trả về giá trị nhỏ nhất trong số các đối số hoặc vùng dữ liệu.
Trong đó, các đối số (number1, number2, ...) là các số hoặc vùng dữ liệu chứa số mà bạn muốn tìm giá trị lớn nhất hoặc nhỏ nhất.

Ví dụ:

Để tìm giá trị lớn nhất trong dãy số 10, -20, 3, 4, 11, bạn có thể sử dụng công thức: MAX(10, -20, 3, 4, 11) = 11.
Để tìm giá trị nhỏ nhất trong dãy số 20, 100, 30, 45, bạn có thể sử dụng công thức: MIN(20, 100, 30, 45) = 20.

Hàm AVERAGE: Hàm tính trung bình cộng

Hàm AVERAGE trong Excel được sử dụng để tính trung bình cộng của một tập hợp các số trong bảng tính.

Cú pháp của hàm AVERAGE là: AVERAGE(Number1, [Number2], [Number3], ...)

Trong đó:

Number1 là bắt buộc.
Number2, Number3,... là tùy chọn.
Hàm AVERAGE có thể chấp nhận tối đa 256 đối số, có thể là số, tên, phạm vi hoặc tham chiếu ô chứa số. Các đối số tham chiếu ô hoặc phạm vi chứa giá trị logic, văn bản hoặc ô rỗng sẽ bị bỏ qua, trừ khi giá trị là 0 hoặc được nhập trực tiếp vào danh sách đối số.

Hàm AVERAGE: Hàm tính trung bình cộng

Hàm COUNT/COUNTA: Hàm đếm dữ liệu/đếm số ô không trống

Hàm COUNT/COUNTA trong Excel được sử dụng để đếm số lượng dữ liệu trong một phạm vi nhất định trên bảng tính.

Công dụng của hàm Count/Counta: Thay vì phải đếm thủ công các ô chứa số trong một bảng dữ liệu lớn, bạn có thể sử dụng hàm COUNT. Ví dụ, với công thức =COUNT(A1:A20), bạn có thể đếm số lượng ô chứa số từ ô A1 đến ô A20 trên bảng tính. Tương tự, hàm COUNTA cũng được sử dụng để đếm số lượng ô trong một phạm vi. Tuy nhiên, COUNTA không chỉ đếm các ô chứa số mà còn đếm cả các ô có chứa nội dung khác nhau (bao gồm cả chữ cái).

Cú pháp của hàm COUNT và hàm COUNTA là:

COUNTA(Value1, [value2], [value3], ...): Đếm số lượng ô có dữ liệu trong một phạm vi cụ thể. Trong đó, Value1 là đối số bắt buộc, là vùng dữ liệu cần đếm, và các value2, value3,... là các đối số tùy chọn.
COUNT(vùng chọn để đếm): Đếm số lượng ô chứa dữ liệu số trong phạm vi được chọn.

Hàm NETWORKDAYS: Hàm tính số lượng ngày làm việc

Hàm NETWORKDAYS trong Excel được sử dụng để tính số ngày làm việc giữa hai ngày cụ thể.

Công dụng của Hàm NETWORKDAYS: Thay vì phải tính số ngày làm việc bằng cách đếm thủ công, Excel cung cấp hàm NETWORKDAYS để tính toán một cách chính xác và nhanh chóng số ngày làm việc toàn thời gian giữa hai ngày.

Cú pháp của hàm NETWORKDAYS là: =NETWORKDAYS(START_DATE, END_DATE, [HOLIDAYS])

Trong đó:

START_DATE là ngày bắt đầu.
END_DATE là ngày kết thúc.
[HOLIDAYS] là tùy chọn và đại diện cho danh sách các ngày nghỉ lễ.
Chức năng của hàm này là trả về số ngày làm việc giữa hai ngày bắt đầu và kết thúc, loại bỏ các ngày nghỉ lễ và cuối tuần.

2. Các hàm cơ bản trong Excel về điều kiện Logic

Hàm đếm COUNTIF với điều kiện cụ thể

Hàm COUNTIF trong Microsoft Excel được sử dụng để đếm số ô trong một phạm vi thỏa mãn một tiêu chí cụ thể.

Hàm COUNTIF là một hàm thống kê tích hợp trong Excel, được sử dụng như một hàm trang tính. Nó có thể được nhập vào một ô trang tính như một phần của một công thức.

Nếu bạn cần áp dụng nhiều tiêu chí, bạn có thể sử dụng hàm COUNTIFS.

Cú pháp cho hàm COUNTIF trong Microsoft Excel là:

COUNTIF( range, criteria )

Trong đó:

range: Là phạm vi các ô mà bạn muốn đếm dựa trên tiêu chí.
criteria: Là tiêu chí để xác định các ô cần đếm.
Bạn cũng có thể sử dụng Named Range (Phạm vi được đặt tên) trong hàm COUNTIF, đó là tên được gán cho một tập hợp các ô hoặc một dải ô trên trang tính.

Nếu tiêu chí của bạn có độ dài lớn hơn 255 ký tự, hàm COUNTIF sẽ trả về lỗi. Để khắc phục điều này, bạn có thể sử dụng hàm CONCATENATE để kết hợp các chuỗi dài hơn 255 ký tự. Bạn cũng có thể sử dụng toán tử & để kết hợp chuỗi, như ví dụ sau:

=COUNTIF(A2:A5,"long string"&"another long string")

Một điều cần lưu ý về hàm COUNTIF là nó không phân biệt chữ hoa và chữ thường. Điều này có nghĩa là các tiêu chí có chữ hoa hoặc chữ thường sẽ khớp với các ô có cùng giá trị và trả về kết quả giống nhau.

Hàm IF

Hàm IF trong Excel được sử dụng để kiểm tra xem dữ liệu có thỏa mãn điều kiện mà người dùng đặt ra hay không, và trả về kết quả dựa trên biểu thức logic đúng hoặc sai.

Công thức tính của hàm IF là:

=IF(Logical_test, Value_if_true, Value_if_false)

Lưu ý: Nếu người dùng bỏ trống cả hai điều kiện Value_if_true và Value_if_false, giá trị trả về sẽ là 0 nếu điều kiện được thỏa mãn và sẽ trả về giá trị FALSE nếu không thỏa mãn.

Hàm SUMIF

Hàm SUMIF trong Excel được sử dụng để tính tổng các giá trị thỏa mãn một điều kiện cụ thể.

Công thức tính của hàm SUMIF là:

=SUMIF(range, criteria, [sum_range])

Trong đó:

range: Là phạm vi các ô mà bạn muốn đánh giá theo tiêu chí.
criteria: Là tiêu chí được xác định dưới dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng các giá trị ở ô nào.
sum_range: Là phạm vi các ô thực tế mà bạn muốn cộng nếu bạn không muốn cộng các ô đã xác định trong đối số range. Nếu đối số sum_range bị bỏ qua, Excel sẽ cộng các giá trị trong phạm vi được xác định trong đối số range.

Hàm SUMIF

3. Các hàm cơ bản trong Excel dùng để tra cứu dữ liệu

Hàm VLOOKUP: Hàm tham chiếu cột

Hàm VLOOKUP, hay còn được biết đến là hàm tham chiếu cột, được sử dụng để trích xuất thông tin từ một bảng phụ dựa trên điều kiện tìm kiếm trong bảng chính và điền vào một cột cụ thể trong bảng chính khi điều kiện được thỏa mãn.

Bài toán liên quan đến hàm VLOOKUP thường phát sinh khi chúng ta cần tham chiếu dữ liệu từ một bảng phụ cho trước.

Cú pháp của hàm excel nâng cao – VLOOKUP là:

=VLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

Giải thích cú pháp:

lookup_value: Là giá trị để thực hiện tham chiếu, có thể là một ký tự, chuỗi ký tự, tham chiếu hoặc giá trị nào đó trong bảng chính.
table_array: Là vùng chứa dữ liệu của bảng phụ. Luôn cần được thiết lập ở dạng địa chỉ tuyệt đối bằng cách quét và nhấn F4 để đảm bảo địa chỉ không thay đổi khi sao chép công thức.
row_index_num: Là số thứ tự của cột trong bảng phụ mà bạn muốn trích xuất dữ liệu từ.
range_lookup: Xác định cách tìm kiếm, có thể là chính xác (0) hoặc tương đối (1). Thường thì giá trị này được thiết lập là 0 để thực hiện tìm kiếm chính xác.

Hàm HLOOKUP: Hàm dò tìm theo hàng

Hàm HLOOKUP trong Excel được sử dụng để tìm kiếm một giá trị trên dòng đầu tiên của bảng tham chiếu và trả về kết quả tương ứng từ dòng chỉ định.

Cú pháp của hàm HLOOKUP là:

=HLOOKUP(Lookup_value, Table_array, Row_index_Num, Range_lookup)

Trong đó:

Lookup_value: Là giá trị được so sánh với dòng đầu tiên của bảng tham chiếu để tìm ra giá trị cần tìm.
Table_array: Là địa chỉ tuyệt đối của bảng tham chiếu, không bao gồm cột tiêu đề.
Row_index_Num: Là số thứ tự của dòng chứa giá trị cần lấy trong bảng tham chiếu (đánh số từ trên xuống dưới và bắt đầu từ 1).
Range_lookup: Xác định cách tìm kiếm trên bảng tham chiếu:
- "+ 0": Tìm kiếm với dòng đầu tiên của bảng tham chiếu không được sắp xếp.
- "+ 1": Tìm kiếm với dòng đầu tiên của bảng tham chiếu đã được sắp xếp theo thứ tự tăng dần (mặc định).

Hàm INDEX

Hàm INDEX trong Excel được sử dụng để trả về một giá trị hoặc tham chiếu tới một giá trị trong bảng hoặc một phạm vi nhất định.

Cú pháp của hàm INDEX là:

=INDEX(array, row_num, column_num)

Với:

array: Là phạm vi ô hoặc một mảng số học. Nếu mảng chỉ bao gồm một hàng hoặc một cột, thì row_num hoặc column_num là tùy chọn. Nếu mảng có nhiều hàng và nhiều cột và bạn chỉ khai báo một trong hai đối số row_num hoặc column_num, hàm sẽ trả về một mảng chứa toàn bộ hàng hoặc cột trong mảng.
row_num: Là số thứ tự của hàng trong mảng chứa giá trị cần trả về. Đây là đối số bắt buộc, trừ khi bạn đã khai báo column_num. Nếu bỏ qua row_num, bạn phải khai báo column_num.
column_num: Là số thứ tự của cột trong mảng chứa giá trị cần trả về. Nếu bỏ qua column_num, bạn phải khai báo row_num.

Hàm MATCH

Hàm MATCH trong Excel được sử dụng để tìm kiếm và xác định vị trí tương đối của một giá trị trong một mảng hoặc một dải ô.

Cú pháp của hàm MATCH là:

=MATCH(Lookup_value, Lookup_array, Match_type)

Với:

Lookup_value: Là giá trị mà bạn muốn tìm kiếm.

Lookup_array: Là mảng hoặc dải ô mà bạn muốn xác định vị trí của Lookup_value. Vùng dữ liệu mà bạn chọn chỉ có thể có duy nhất một hàng hoặc một cột.
Match_type: Xác định kiểu khớp, có thể là -1, 0 và 1.
- Kiểu khớp là 0: Trả về vị trí tương đối của giá trị trong trường hợp vùng dữ liệu chưa được sắp xếp.
- Kiểu khớp là 1: Trả về vị trí của giá trị lớn nhất nhưng nhỏ hơn hoặc bằng giá trị cần tìm, giả sử vùng dữ liệu đã được sắp xếp theo thứ tự tăng dần.
- Kiểu khớp là -1: Trả về vị trí của giá trị nhỏ nhất nhưng lớn hơn hoặc bằng giá trị cần tìm, giả sử vùng dữ liệu đã được sắp xếp theo thứ tự giảm dần.

4. Các hàm cơ bản trong Excel sử dụng trong văn bản

Hàm LEFT/ RIGHT

Hàm LEFT và RIGHT trong Excel được sử dụng để xác định các phần bên trái và bên phải của một đoạn văn bản hoặc chuỗi ký tự.

Cú pháp của hàm LEFT là: LEFT(text, [num_chars])

Tác dụng của hàm LEFT là lấy ra phần bên trái của một đoạn văn bản hoặc chuỗi ký tự.

Ứng dụng của hàm LEFT bao gồm:

Kết hợp với hàm LEN để xác định số tài khoản cấp 1, cấp 2 trong danh mục tài khoản.
Sử dụng trong hàm SUMPRODUCT để tính tổng cho bất kỳ loại tài khoản nào dựa trên một mảng là phần bên trái của cột tài khoản Nợ hoặc tài khoản Có trong Nhật ký chung.

Cú pháp của hàm RIGHT là:RIGHT(text, num_chars)

Trong đó:

Text: là biểu thức ký tự mà bạn muốn xác định.
Num_chars: là số lượng ký tự cần lấy ra từ bên phải của Text.
Chức năng của hàm RIGHT là trả về một phần của biểu thức ký tự được lấy ra từ bên phải của Text với số lượng ký tự được xác định bởi num_chars.

Hàm LEFT/ RIGHT

Hàm MID

Hàm MID trong Excel trả về một số lượng ký tự cụ thể từ một chuỗi văn bản, bắt đầu từ vị trí được chỉ định và dựa vào số lượng ký tự cụ thể bạn chỉ định.

Cú pháp của hàm MID là: =MID(text, start_num, num_chars)

Trong đó:

Text: Là chuỗi văn bản chứa các ký tự mà bạn muốn trích xuất.
Start_num: Là vị trí của ký tự đầu tiên mà bạn muốn trích xuất trong chuỗi văn bản.
Num_chars: Là số ký tự mà bạn muốn hàm MID trả về từ chuỗi văn bản. Đây là một tham số bắt buộc đối với hàm MID và chỉ định số lượng ký tự cần trích xuất từ văn bản.

Hàm LEN: Hàm đo độ dài của chuỗi ký tự

Hàm LEN trong Excel được sử dụng để đo độ dài của chuỗi ký tự.

Công dụng của hàm LEN là để đo độ dài chính xác của một chuỗi ký tự. Bằng cách này, bạn có thể biết được số lượng ký tự trong chuỗi và thực hiện các yêu cầu phù hợp.

Cú pháp của hàm LEN là: LEN(text)

Trong đó:

Text: Là chuỗi ký tự mà bạn muốn đo độ dài.
Chức năng của hàm LEN là trả về giá trị là độ dài của chuỗi ký tự, bao gồm cả các ký tự khoảng trống.

Ví dụ:

Nếu bạn muốn đo độ dài của chuỗi "University of Da Nang", công thức tính sẽ là: =LEN("University of Da Nang"), kết quả trả về sẽ là 21.

5. Các hàm cơ bản trong Excel về ngày tháng

Hàm NOW: Hàm trả về ngày và giờ hiện tại

Công dụng của hàm NOW trong Excel là để hiển thị ngày và giờ hiện tại trên trang tính hoặc để sử dụng trong các tính toán dựa trên thời gian hiện tại.

Cú pháp của hàm NOW là: =NOW()

Hàm NOW không sử dụng bất kỳ đối số nào.

Kết quả của hàm NOW sẽ được cập nhật khi bạn thực hiện một công thức mới hoặc chỉnh sửa một ô giá trị, và không được cập nhật liên tục.

Chức năng của hàm NOW là trả về giá trị ngày, tháng, năm, giờ và phút hiện tại.

Hàm DATE

Hàm DATE trong Excel được sử dụng để trả về một giá trị số cho một ngày cụ thể.

Cú pháp của hàm DATE là: =DATE(Year, Month, Day)

Trong đó:

Year: Đại diện cho năm. Excel sử dụng đối số năm theo hệ thống ngày được cài đặt trên máy tính của bạn.
Month: Đại diện cho tháng. Là một số nguyên từ 1 đến 12, tương ứng với các tháng trong năm.
Day: Đại diện cho ngày. Là một số nguyên dương từ 1 đến 31, tương ứng với các ngày trong tháng.

Ví dụ: =DATE(2024, 2, 28) sẽ trả về kết quả là ngày 28 tháng 2 năm 2024.

Vậy là Megaweb đã tổng hợp các hàm cơ bản trong Excel được sử dụng nhiều nhất trong công việc và học tập. Hy vọng qua bài viết này sẽ giúp bạn hiểu chi tiết hơn về các hàm cũng như sử dụng đúng cách nhất nhé!