Tổng hợp những hàm cơ bản trong Excel thường được sử dụng nhiều nhất

Trong bài viết này, vitinhhuynhlong.com sẽ tổng hợp những hàm cơ bản trong Excel mà bạn không nên bỏ qua!

I. Tổng hợp những hàm cơ bản trong Excel

1. Hàm đếm số liệu

Hàng trăm, hàng nghìn dữ liệu với đầy ắp những con số sẽ không thể đếm theo cách thông thường được. Đừng lo lắng bởi đã có các hàm sau:

1.1. Hàm đếm COUNT

Chức năng: Thường được dùng trong các trường hợp cần đếm số ô trong 1 vùng dữ liệu hoặc toàn bộ dữ liệu trong file. COUNT sẽ trả kết quả và các giá trị số ( như số âm, phần trăm, số ngày tháng năm, phân thức…) còn các ký tự văn bản sẽ không được đếm.

Cú pháp: =COUNT(value1, [value2],…)

Trong đó:

Value 1, value 2,… là những giá trị trong vùng tuỳ chọn để đếm các ô giá trị

Bạn có thể đếm được 255 đối số trong 1 lần đến trong các phiên bản Excel 2007 – 2016

Ví dụ:

1.2. Hàm đếm có điều kiện COUNTIF

Chức năng: Thường được dùng để đếm số ô giá trị thoả mãn điều kiện cụ thể mà người dùng đặt ra.

Cú pháp: =COUNTIF(range; criteria)

Trong đó:

Range là vùng dữ liệu cần đếm

Criteria là điều kiện để đếm

Ví dụ ở bảng này, bạn muốn đếm số lần Cam bán ra, bạn nhập =COUNTIF(A2:A7,”CAM”)

1.3. Hàm đếm có nhiều điều kiện COUNTIFS

Chức năng: Dùng để tìm số lượng ô có giá trị thoả mãn từ 2 điều kiện trở lên.

Cú pháp: =COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2] …)

Trong đó:

criteria_range1: Vùng dữ liệu đầu tiên, bắt buộc cần điền

criteria1: Điều kiện của vùng criteria_range1.

[criteria_range2, criteria2]: Các cặp vùng chọn và điều kiện bổ sung. 

Hàm này cho phép bạn thêm tối đa 127 cặp điều kiện.

Lưu ý: Khi chọn vùng và điều kiện bổ sung cần phải có cùng số hàng, cột với vùng 1 trước đó, không nhất thiết phải nằm liền kề nhau.

Ví dụ: Bạn có danh hàng trái cây như bảng sau. Muốn xem thử còn bao nhiêu hàng tồn kho chưa bán được. Lúc này, điều kiện sẽ là sản phẩm còn phải lớn hơn 0 và không hết hàng. Bạn chỉ cần nhập cú pháp: =COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)

1.4. Hàm đến ô trống COUNTBLANK

Chức năng: Được dùng để đếm các ô trống không có dữ liệu trong vùng được chọn

Cú pháp: =COUNTBLANK(range)

Trong đó:

Range là vùng dữ liệu cần đếm

Ví dụ: Bạn muốn đếm số buổi có sinh viên vắng. Hãy nhập công thức: =COUNBLANK(C3:F7)

1.5. Hàm đếm ô không trống COUNTA

Chức năng: Dùng để các ô có chứa dữ liệu ( số, ký tự, biểu tượng..) trong vùng dữ liệu tuỳ chọn

Cú pháp: =COUNTA(range)

Trong đó:

Range là vùng dữ liệu cần đếm

Ví dụ: Muốn số buổi sinh viên có mặt, bạn chỉ cần nhập =COUNTA(C3:F7) như bảng sau

2. Hàm tính tổng

2.1. Hàm SUM

Chức năng: dùng để cộng các ô có giá trị số trong một vùng phạm vi. Công thức sẽ tự động cập nhật khi người dùng thay đổi hoặc chèn thêm giá trị. Hàm sẽ tự bỏ qua các ô trống và các ô chứa văn bản không phải số.

Cú pháp: =SUM(number1,[number2],…)

Trong đó:

Number1, number2 là các giá trị số được thêm vào.

Ví dụ: Ở bảng hàng sau, bạn muốn tính tổng số lượng hàng còn, nhập =SUM(B2:B7)

2.2. Hàm tính tổng có điều kiện SUMIF

Chức năng: dùng để tính tổng có điều kiện của các ô dữ liệu

Cú pháp: =SUMIF(range,criteria,[sum_range])

Trong đó:

Range: vùng chứa các ô dữ liệu cần tính tổng

Criteria: Điều kiện/tiêu chí

Sum_range: vùng dữ liệu cần tính tổng

Ví dụ: để tính tổng thuế thu nhập cá nhân của những người có thu nhập dưới 50 triệu đồng từ bảng dưới đây. Nhập công thức: =SUMIF(B2:B7,"<50,000,000",D2:D7)

2.3. Hàm tính tổng nhiều điều kiện SUMIFS

Chức năng: dùng để tính tổng các ô dữ liệu theo nhiều điều kiện

Cú pháp: = SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Trong đó:

Sum_range: Là phạm vi ô cần tính tổng (số hoặc tên), đây là phần bắt buộc.

Criteria_range1: phạm vi cần đánh giá theo điều kiện

Criteria1: Điều kiện/tiêu chí

Criteria_range2, criteria2,…: các cặp phạm vi, điều kiện bổ sung

Ví dụ: Muốn tính số nhân viên bán hàng có lương trên 15 triệu.

Bạn nhập =SUMIFS(B2:B7,A2:A7,">15000000",F2:F7,"bán hàng")

2.4. Hàm tính tổng giá tiền sản phẩm SUMPRODUCT

Chức năng: tính tổng từ các tích. Hay nói dễ hiểu hơn, hàm sẽ được sử dụng để tính tổng tiền các sản phẩm bằng cách nhân đơn giá x số lượng và cộng tất cả lại với nhau.

Cú pháp: =SUMPRODUCT(array1; array2; array3;...)

Trong đó:

Array1: là ô dữ liệu bắt buộc đầu tiên của thành phần mà bạn muốn nhân ( ví dụ đơn giá/ số lượng)

Array2, array3: những ô dữ liệu tiếp theo, lưu ý phải chọn cùng số hàng và cột để không lỗi #VALUE!

Ví dụ: Tính tổng tiền của các đơn hàng sau. Bạn nhập: =SUMPRODUCT(B2:B7,C2:C7)

3. Hàm tính giá trị trung bình AVERAGE

Chức năng: cho ra con số trung bình của tổng các ô dữ liệu được chọn

Cú pháp: =AVERAGE(range)

Trong đó:

Range: là vùng dữ liệu cần tính giá trị trung bình

Ví dụ: Để tính điểm trung bình ba môn cho Nguyễn Văn B, ta nhập : =AVERAGE(B2:D2)

4. Hàm MIN,MAX

Chức năng: đúng như tên gọi, hàm này dùng để tìm ra giá trị lớn nhất, giá trị nhỏ nhất của 1 vùng dữ liệu

Cú pháp:

MIN(range)

MAX(range)

Trong đó:

Range: là vùng dữ liệu bạn cần tìm số nhỏ nhất (lớn nhất)

Ví dụ: Muốn tìm điểm cao nhất lớp, nhập =MAX(B2:D6)

5. Hàm LOGIC

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

Chức năng: dùng để kiểm tra xem ô dữ liệu có thoả điều kiện được đặt ra hay không, nếu đúng sẽ giả về giá trị đúng, sai sẽ trả giá trị sai.

Cú pháp: =IF(Logical_test;Value_if_true;Value_if_false)

Trong đó:

Logical_test: Điều kiện đặt ra cho dữ liệu

Value_if_true: giá trị trả về nếu thoả mãn điều kiện

Value_if_false: giá trị trả về nếu không thoả mãn điều kiện

Ví dụ: Muốn xem học sinh có đạt hay không dựa trên điều kiện điểm lớn hơn 8. Nhập =IF(B2>=8,"ĐẠT","KHÔNG ĐẠT")

5.2. Hàm AND

Chức năng: kiểm tra các ô dữ liệu bằng cách sẽ trả về giá trị đúng nếu tất cả các điều kiện đều được thoả mãn, nếu có bất kì điều kiện nào sai, kết quả sẽ trả về sai

Cú pháp: =AND(Điều kiện 1,Điều kiện 2,…)

Thường hàm AND được sử dụng kết hợp với hàm IF để xét nhiều mệnh đề điều kiện cùng lúc với cú pháp: =IF(AND(điều kiện 1,Điều kiện 2,…),“Giá trị 1”,“Giá trị 2”)

Ví dụ: Nếu bạn muốn tìm kết quả đạt hay không đạt dựa trên điều kiện điểm thực hành trên 7, lý thuyết trên 8. Cú pháp sẽ như sau: 

5.3. Hàm OR

Chức năng: khác với hàm AND, hàm OR kiểm tra các ô dữ liệu nhưng sẽ trả về giá trị đúng nếu có bất kì điều kiện thoả mãn nhưng sẽ trả về giá trị sai nếu tất cả điều kiện đều không thoả mãn.

Cú pháp: =OR(logical 1, [logical 2], …)

Thường hàm OR cũng được kết hợp với hàm IF để kiểm tra các giá trị trong bảng dữ liệu

Ví dụ: Cũng tương tự như hàm IF, nếu bạn muốn tìm kết quả đạt hay không đạt dựa trên điều kiện điểm thực hành trên 7, lý thuyết trên 8. Cú pháp sẽ như sau: 

6. Hàm tra cứu, dò tìm

6.1. Hàm VLOOKUP

Chức năng: dò tìm dữ liệu ở cột ngoài cùng bên trái của bảng theo hàng dọc và trả về dữ liệu tương ứng theo hàng ngang

Cú pháp: =VLOOKUP (Lookup_value, Table_array, Col_index_ num, Range_lookup)

Trong đó:

Lookup_value: là giá trị cần dò tìm

Table_array: bảng để dò tìm.

Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng, đếm từ trái qua phải.

Range_lookup: tìm kiếm chính xác hay tìm kiếm tương đối với bảng giới hạn, thường mặc định là 1.

Ví dụ: Để tìm định mức dựa vào bảng loại hàng, ta cần nhập cú pháp sau: =VLOOKUP(C7,$B$16:$D$20,2,0)

6.2. Hàm HLOOKUP

Chức năng: dò tìm dữ liệu ở cột ngoài cùng bên trái của bảng theo hàng ngang và trả về dữ liệu tương ứng theo hàng dọc

Cú pháp: =HLOOKUP(Lookup_value, Table_array, Row_index_ num, Range_lookup)

Trong đó:

Lookup_value: Giá trị cần tìm

Table_array: Bảng để dò tìm. ( Sau khi kéo bảng, nhấn F4) 

Row_index_num: Số thứ tự của hàng lấy dữ liệu trong bảng cần dò tìm, đếm từ trên xuống dưới.

Range_lookup: tìm kiếm chính xác hay tìm kiếm tương đối với bảng giới hạn, thường mặc định là 1.

Ví dụ: Nếu bạn muốn tìm ngành học dựa trên mã số ngành và  mã ưu tiên.

Ta nhập: =HLOOKUP(LEFT(D7,1),$B$13:$F$14,2,0)

6.3. Hàm INDEX

Chức năng: trả về giá trị hoặc tham chiếu tới một giá trị trong bảng hoặc phạm vi

Cú pháp: INDEX(array, row_num, [column_num])

Trong đó:

array: Phạm vi ô hoặc một hằng số mảng.

row_num: Thứ tự của hàng trong mảng chứa giá trị trả về

column_num: Thứ tự của cột trong mảng chứa giá trị trả về.

Ví dụ: Muốn tìm giá trị ở ô thứ 4 cột thứ 1 trong bảng sau, ta nhập cú pháp: =INDEX(B4:E13,4,1)

6.4. Hàm MATCH

Chức năng: tìm kiếm, xác định vị trí tương đối của một giá trị trong một vùng dữ liệu

Cú pháp: =Match(Lookup_value, Lookup_array, Match_type)

Trong đó:

Lookup_value: Giá trị cần tìm

Lookup_array: Vùng bạn muốn xác định vị trí của giá trị. Lưu ý phải chọn cùng cột hoặc hàng

Match_type: Xác định kiểu khớp

0: kết quả là vị trí tương đối của vùng dữ liệu chưa được sắp xếp

1: kết quả là vị trí của giá trị lớn nhất nhưng nhỏ hơn hoặc gần bằng giá trị cần tìm

-1: kết quả là vị trí của giá trị nhỏ nhất nhưng lớn hơn hoặc gần bằng giá trị cần tìm

Ví dụ: Để tìm vị trí của Lê Văn Kiên để tra dữ liệu, bạn nhập: =MATCH(F2;A1:A8;0)

7. Hàm Ngày Tháng

7.1. Hàm YEAR, MONTH, DAY

Chức năng: Hàm về thời gian thường được dùng để xử lý các dữ liệu có liên quan đến thời hạn, ngày tháng. Cụ thể như:

Hàm Day: để xác định ngày trong 1 vùng dữ liệu ngày/tháng/năm

Hàm Month: xác định tháng trong 1 vùng dữ liệu ngày/tháng/ năm

Hàm Year: xác định năm trong 1 vùng dữ liệu ngày/tháng/ năm

Cú pháp: =DAY(serial_number).

Thực hiện tương tự ở các hàm Month và Year.

Trong đó:

Serial_number là ngày mà bạn muốn tìm

Ví dụ: Muốn tìm ngày, tháng trong bản dữ liệu sau, ta chỉ cần nhập: 

7.2. Hàm DATE

Chức năng: tạo các ngày hợp lệ từ ô dữ liệu ngày tháng năm

Cú pháp: =DATE(year, month, day)

Ví dụ: 

8. Hàm sử dụng trong văn bản

8.1. Hàm LEFT/RIGHT

Chức năng: cắt chuỗi kí tự từ bên trái/ bên phải văn bản ở ô được chọn

Cú pháp:

=LEFT(text,[num_chars])

=RIGHT(text,[num_chars])

Trong đó:

Text: là ô văn bản chứa kí tự muốn cắt

Num_chars: số lượng kí tự đếm từ trái/phải qua mà bạn muốn lấy

Ví dụ: Muốn tìm tên lớp của học sinh, biết rằng số lớp là số kí tự đầu của số báo danh, ta nhập như hình sau: 

8.2. Hàm MID

Chức năng: cắt số kí tự từ ô văn bản được chọn, tính từ vị trí con trỏ chuột của bạn

Cú pháp: =MID(text, start_num, num_chars)

Trong đó:

Text: ô văn bản muốn chứa kí tự muốn cắt

start_num: vị trí của kí tự đầu tiên mà bạn muốn lấy

num_chars: số lượng kí tự bạn muốn lấy

Ví dụ: Muốn tìm họ của tên học sinh dưới đây, chỉ cần nhập: 

8.3. Hàm LEN

Chức năng: đếm ký tự ở ô văn bản bao gồm cả khoảng trắng

Cú pháp: =LEN(ô chứa chuỗi ký tự)

Ví dụ: Muốn đếm ô của học sinh Phạm Minh Nhật, ta nhập: 

8.4. Hàm CONCATENATE (CONCAT)

Chức năng: nối hai hoặc nhiều ô văn bản thành một

Cú pháp: =CONCATENATE(text1, text2,…)

Trong đó:

Text 1,text2: là ô chứa văn bản

Ví dụ: Để gộp họ và tên của học sinh, ta nhập: 

8.5. Hàm SUBSTITUTE

Chức năng: thay thế văn bản trong ô văn bản được chọn

Cú pháp: =SUBSTITUTE(text, old_text, new_text, [instance_num])

Trong đó:

Text: ô văn bản muốn thay thế các ký tự trong đó.

Old_text: ô chứa văn bản cũ bạn muốn được thay thế.

New_text: văn bản mới mà bạn muốn thay thế cho văn bản cũ.

Instance_num: vị trí mà văn bản cũ xuất hiện mà bạn muốn thay bằng văn bản mới.

Ví dụ:

8.6. Hàm cắt khoảng trống TRIM

Chức năng: Cắt các ô khoảng trắng thừa khi dán văn bản khác vào bảng tính

Cú pháp: TRIM(text)

Trong đó:

Text là ô văn bản muốn loại bỏ các khoảng trắng

Ví dụ:

8.7. Hàm đổi chữ hoa, chữ thường LOWER, UPPER, PROPER

Chức năng: chuyển đổi các phần văn bản in thường thành chữ hoa và ngược lại.

Cú pháp:

=LOWER(text) để chuyển chữ hoa thành chữ thường

=UPPER(text) để chuyển chữ thường thành chữ hoa

=PROPER(text) để viết hoa các chữ cái đầu của văn bản

Ví dụ: 

II. Tổng kết

Excel là trợ thủ tính toán đắc lực vừa giúp tiết kiệm thời gian vừa cho ra kết quả chính xác. Nắm được các hàm cơ bản excel sẽ giúp bạn xử lý mọi công việc thêm nhanh chóng, hiệu quả hơn.

Trên đây là các hàm excel cơ bản mà chúng tôi đã tổng hợp. Hi vọng những thông tin này sẽ hữu ích với bạn!