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

Bỏ túi ngay những hàm cơ bản trong Excel để mọi công việc tính toán thêm nhanh chóng!

Bên cạnh Word, Powerpoint, Microsoft Excel là một trong những công cụ phổ biến và không thể thiếu đối với sinh viên, người đi làm, đặc biệt là dân văn phòng. Không chỉ cung cấp những trang tính để ghi dữ liệu dưới dạng bảng, phần mềm này còn giúp xử lý thông tin chính xác và nhanh chóng. Để tận dụng tối đa tính năng này của Excel bạn cần phải nắm ngay các hàm tính. Nếu như:

• Bạn vẫn còn thao tác chậm trên Excel?
• Ưu điểm khi sử dụng các hàm cơ bản excel?
• Bạn muốn tìm các hàm excel cơ bản để làm việc thêm hiệu quả?

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

 

Sử dụng các hàm cơ bản trong excel sẽ giúp bạn tính toán nhanh chóng, chính xác hơn

 

I. Sử dụng hàm excel cơ bản có lợi ích gì?

Excel cung cấp hàng trăm hàm cơ bản và nâng cao để giúp cho mọi thao tác thêm nhanh chóng và chính xác hơn. Người làm việc, học tập nên sử dụng các hàm tính bởi: 

• Trên bàn phím chỉ có thể thực hiện các phép tính thông thường như phím cộng (+), trừ (-), nhân (*) và thực hiện tốt với số lượng 2-4 con số. Nếu như yêu cầu đưa ra phải tính số lượng nhiều hơn thì rất mất thời gian khi bạn phải nhập 100 ô với 99 dấu +. Còn sử dụng các hàm tính thì chỉ với vài thao tác cùng kí tự, bạn đã có thể tính toán nhanh chóng cho dữ liệu lớn.
• Bên cạnh đó, có những phép tính bạn không thể sử dụng các phím có sẵn, VD: tính trung bình, tìm số lớn nhất, nhỏ nhất…Đừng lo, các hàm excel sẽ làm nhiệm vụ này.
• Nếu bạn chỉ tính toán bằng những phím thông thường sẽ không đảm bảo được độ chính xác, bộ não của Excel sẽ tiếp nhận các hàm và cho ra kết quả đúng nhất.
• Excel còn cho phép nhập một lần ở 1 khung và có thể kéo chuột để cho ra kết quả ở các dòng khác. Đồng thời, Excel có chức năng gợi ý hàm, khi bạn nhập 1-2 chữ cái đầu của hàm, các hàm thông dụng sẽ hiện ra. Đây sẽ là ưu điểm mạnh để bạn hoàn thành công việc tính toán chỉ trong thời gian ngắn.

 

 

II. 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

VD:

Sử dụng hàm Count để đếm số cột thành tiền trong bảng tính

 

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ượng Cam loại 2 bán ra, bạn nhập =COUNTIF(B5:B12,”CAM 2”)

 

Hàm Countif trong Excel

 

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”)

 

Sử dụng hàm Countifs để kết hợp nhiều điều kiện

 

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)

 

Hàm COUNTBLANK để đếm vùng trống

 

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

 

Dùng hàm COUNTA để đếm ô có dữ liệu

 

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)

 

Hàm SUM để tính tổng giá trị các ô dữ liệu

 

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)

 

Hàm SUMIF để kết hợp điều kiện khi tính tổng

 

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")

 

Hàm SUMIFS để kết hợp nhiều điều kiện

 

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)

 

Hàm SUMPRODUCT để tính tổng các tích

 

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)

 

 Dùng hàm AVERAGE để tìm giá trị trung bình

 

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)

 

Ví dụ về hàm MAX

 

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")

 

Dùng hàm IF để kiểm tra có thoả điều kiện hay không 

 

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: 

 

Hàm AND kết hợp với hàm IF

 

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: 

 

Hàm OR kết hợp với hàm IF

 

6. Hàm tra cứu

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)

 

Hàm VLOOKUP dùng để tra cứu dữ liệu dựa trên bảng kèm

 

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)

 

Hàm HLOOKUP trả kết quả theo hàng dọc 

 

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)

 

Ví dụ hàm INDEX

 

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)

 

Ví dụ về hàm MATCH trong Excel

 

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: 

 

Hàm Day trong Excel

 

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ụ: 

 

Ví dụ về hàm DATE trong Excel

 

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: 

 

Hàm LEFT để tìm kí tự từ trái sang 

 

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: 

 

Hàm MID trong Excel

 

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: 

 

Hàm LEN để đếm số lượng kí tự

 

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: 

 

Hàm CONCATENATE để nối các ô dữ liệu

 

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ụ:

 

Hàm TRIM cắt ô trống

 

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ụ: 

 

Các hàm điều chỉnh văn bản

 

III. 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à MEGA đã tổng hợp. Hi vọng những thông tin này sẽ hữu ích với bạn!

 

Xem thêm >>>

Các phím tắt cơ bản trong excel mà bạn cần nên biết

Cách tìm dữ liệu trong excel bằng hàm vlookup và hlookup nhanh chóng

Cách tạo biểu đồ Waterfall trong Excel hoàn chỉnh

copyright © mega.com.vn