Các hàm cơ bản dùng trong Excel

Các hàm cơ bản dùng trong Excel

Để sử dụng thành thạo Excel thì các bạn nên để ý và học thuộc cách sử dụng các hàm cơ bản. Đây là các hàm hữu ích đáp ứng phần lớn nhu cầu tính toán, tìm giá trị,…trong cuộc sống thường ngày của chúng ta. Nào, chúng ta sẽ bắt đầu từ các hàm khó nhớ nhất đến dần dần các hàm dễ nhất.

1. Hàm IF

Hàm IF được dùng để kiểm tra dữ liệu có thỏa điều kiện người dùng đặt ra và trả về kết quả theo biểu thức logic đúng hoặc sai.

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

Với:

  • Logical_test: Điều kiện.
  • Value_if_true: Giá trị trả về nếu thỏa điều kiện
  • Value_if_false: Giá trị trả về nếu không thỏa điều kiện.

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

Ví dụ: Xét học sinh có qua môn với điều kiện:

– Điểm số từ 7 trở lên: Đạt

– Điểm số thấp hơn 7: Không Đạt

Tại ô D2, ta có công thức: =IF(C2>=7,”Đạt”,”Không Đạt”), và được kết quả như hình bên dưới.

Làm hàm IF tại ô D2

2. Hàm SUMIF

Hàm SUMIF dùng để tính tổng các giá trị trong một phạm vi đáp ứng tiêu chí xác định.

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

Với:

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

Ví dụ: Tính tổng số màu cam ở cột A có số lượng xuất hiện trong cột B, có công thức =SUMIF(A1:A8,”cam”,B1:B8)

Giá trị cần tìm tính tổng thì ở giữa, trái là cột cần tìm giá trị, phải là trị cần tính tổng

3. Hàm VLOOKUP

– Hàm VLOOKUP là hàm được sử dụng khi cần dò tìm dữ liệu trong một bảng, một phạm vi 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)

Với:

  • Lookup_value: Giá trị cần dò tìm, có thể điền giá trị trực tiếp hoặc tham chiếu tới một ô trên bảng tính.
  • Table_array: Bảng giới hạn để dò tìm.
  • Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm, tính 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, nếu bỏ qua thì mặc định là 1.

  • Nếu Range_lookup = 1 (TRUE): dò tìm tương đối.
  • Nếu Range_lookup = 0 (FALSE): dò tìm chính xác.

Ví dụ: Xác định mức phụ cấp cho nhân viên.

Tại ô E4, bạn điền công thức: =VLOOKUP(D4,$H$3:$I$8,2,0)

Giải thích công thức (theo thứ tự các đối số trong công thức từ trái sang):

  • Dấu $ được sử dụng để cố định các dòng, các cột của bảng 2 khi bạn copy công thức sang các ô khác.
  • 2 là số thứ tự của cột dữ liệu.
  • Range_lookup = 0 (FALSE) để dò tìm chính xác.
Chú ý bấm F4 để ra trị tuyệt đối của bảng cần dò

4. Hàm HLOOKUP

Hàm HLOOKUP là hàm tìm kiếm một giá trị trên dòng đầu tiên của bảng tham chiếu và cho kết quả tương ứng trong dòng chỉ định.

Cú pháp:     =HLOOKUP(Lookup_value,Table_ array,Row_index_Num,Range_lookup)

Với:

  • Lookup_value: giá trị đối chiếu với dòng đầu tiên của bảng tham chiếu để lấy được giá trị cần tìm.
  • Table_array: là địa chỉ tuyệt đối của bảng tham chiếu, không lấy cột tiêu đề.
  • Row_index_Num: Số thứ tự dòng chứa giá trị cần lấy của bảng tham chiếu (theo thứ tự từ trên xuống dưới và bắt đầu từ số 1).

Range_lookup: Cách tìm kiếm trên bảng tham chiếu.

  • “+ 0”: Dòng đầu tiên của bảng tham chiếu chưa sắp xếp.
  • “+ 1”: Dòng đầu tiên của bảng tham chiếu đã sắp xếp theo chiều tăng dần (ngầm định).

Ví dụ: Ta cần xếp loại học sinh trong Bảng 1 (B3:D8) với dữ kiện ở Bảng 2 (B11:F12), tại D4 ta có: =HLOOKUP(C4,$B$11:$F$12,2,1). Hàm HLOOKUP sẽ dò tìm điểm số ở ô C4 trong Bảng 2 từ trái qua phải. Khi tìm thấy giá trị gần bằng, nó sẽ trả về kết quả xếp loại tương ứng nằm ở hàng 2.

HLookup sẽ tìm theo dòng

5. Hàm COUNTIF

Hàm COUNTIF được dùng để đếm ô thỏa mãn điều kiện trong một vùng dữ liệu được chọn.

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

Với:

  • range: Vùng dữ liệu cần đếm.
  • criteria: Điều kiện để đếm.

Ví dụ: Có bảng thống kê các mặt hàng và số lượng tồn như hình dưới.

Để thống kê xem có bao nhiêu mặt hàng còn tồn trên 150 sản phẩm, nhập công thức:

=COUNTIF(C2:C11,”>150″)

Kết quả trả về cho thấy có tổng cộng 8 mặt hàng còn tồn trên 150 sản phẩm.

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

6. Hàm AVERAGE

Hàm AVERAGE dùng để tính trung bình cộng của một dãy số trong trang tính.

Cú pháp:      =AVERAGE(number1,number2,…)

Với:

  • number1 (Bắt buộc): Số thứ nhất, tham chiếu ô, hoặc phạm vi mà bạn muốn tính trung bình.
  • number2,… (Tùy chọn): Các số, tham chiếu ô hoặc phạm vi bổ sung mà bạn muốn tính trung bình, tối đa là 255.

Ví dụ: Tính lương trung bình theo thông tin bên dưới, ta có công thức =AVERAGE(C3:C8) và nhận được kết quả là giá trị trung bình của 6 tháng lương.

Tính giá trị trung bình của một dãy số

7. Hàm SUM

Hàm SUM dùng để tính tổng tất cả các số trong dãy ô. Nghĩa là SUM có nhiệm vụ tính tổng giá trị các số hoặc dãy số trong ô mà bạn muốn tính.

Cú pháp:   =SUM(Number1,Number2,Number3)

Với:      Number1,Number2,Number3: là giá trị các số hạng cần tính tổng

Kết quả của hàm SUM là tổng các giá trị được chọn.

Ví dụ: =SUM(20,40,60) nghĩa là cộng các số hạng lại với nhau và cho ra kết quả 120.

Hàm Sum cho các đối số bên trong
Hàm SUM cho 1 dãy số
SUM cho dãy A1 đến A4

8. Hàm MIN/MAX

Hàm MIN/MAX dùng để tìm giá trị nhỏ nhất/lớn nhất trong một vùng dữ liệu hoặc trong cả bảng tính.

Cú pháp:    =MIN(Number1,Number2,…)

Với:        Number1,Number2: Các giá trị cần so sánh

Kết quả trả về giá trị nhỏ nhất trong vùng dữ liệu

Ví dụ: Giá trị nhỏ nhất trong vùng dữ liệu của ví dụ này, ta có công thức =MIN(A2:C5) và nhận được kết quả 25 là giá trị nhỏ nhất cần tìm.

Hàm Min cho 1 dãy số

Cú pháp:   =MAX(Number1,Number2,…)

Với:    Number1,Number2: các giá trị cần so sánh

Kết quả trả về giá trị lớn nhất trong vùng dữ liệu

Ví dụ: Tìm giá trị lớn nhất trong vùng dữ liệu của ví dụ này, ta có công thức =MAX(A2:C5) kết quả đạt được là 89 là giá trị lớn nhất cần tìm.

Tìm giá trị lớn nhất bằng hàm MAX

9. Hàm COUNT/COUNTA

Hàm COUNT dùng để đếm số lượng ô có chứa số trong vùng dữ liệu.

Cú pháp:    =COUNT(Value1,…)

Với:    Value1,…: Tham chiếu ô hoặc phạm vi muốn đếm số

Ví dụ: Tìm số lượng mà giá trị trong ô của vùng dữ liệu là số, ta có công thức cho ví dụ này là =COUNT(A2:C5). Kết quả đạt được là tổng số lượng ô có chứa số.

Đếm số lượng ô có giá trị là số, không phải là chữ

– Hàm COUNTA dùng để đếm các ô không trống trong một vùng dữ liệu nhất định.

Cú pháp:      =COUNTA(Value1,…)

Với:

Value1,…: Là những ô cần đếm hoặc một vùng cần đếm. Số ô tối đa có thể đếm là 255 (với Excel từ 2007 về sau) và tối đa 30 (với Excel từ 2003 về trước)

Ví dụ: Đếm số lượng ký tự là số và chữ trong bảng bên dưới, ta có công thức =COUNTA(F4:J8). Kết quả đạt được là số lượng ô có chứa chữ hoặc số.

Đếm số lượng ô là chữ và số trong 1 vùng

10. Hàm ODD/EVEN

– Hàm ODD là hàm trả về số được làm tròn lên số nguyên lẻ gần nhất.

Cú pháp:   =ODD(Number)

Với    Number: Bắt buộc phải có, là giá trị cần làm tròn

Ví dụ: Làm tròn số 4.6 đến số nguyên lẻ gần nhất nhận được kết quả là 5.

Hàm ODD làm tròn lên

Hàm EVEN là hàm trả về số được làm tròn lên số nguyên chẵn gần nhất.

Cú pháp:    =EVEN(Number)

Với:   Number: Bắt buộc phải có, là giá trị cần làm tròn

Ví dụ: Làm tròn 53.4 đến số nguyên chẵn gần nhất nhận được kết quả là 54.

Tròn với số chẵn gần nhất

11. Hàm ngày tháng

1. Hàm NOW

Hàm NOW dùng để hiển thị ngày và thời gian hiện tại trên hệ thống của bạn, hoặc muốn tính toán giá trị dựa trên ngày và thời gian hiện tại và cập nhật lại mỗi khi bạn mở lại trang tính.

Cú pháp:    =NOW()

Ví dụ:     =NOW() : Trả về ngày và thời gian hiện tại trên hệ thống của bạn.

=NOW()+7: Trả về ngày và thời gian 7 ngày trong tương lai

2. Hàm DATE

Hàm DATE trả về kết quả số cho ngày cụ thể.

Cú pháp:     =DATE(Year,Month,Day)

Với:

  • Year: chỉ năm. Excel diễn giải đối số năm theo hệ thống ngày được thiết lập trên máy tính của bạn.
  • Month: chỉ tháng. Một số nguyên đại diện cho tháng trong năm, từ tháng 1 đến tháng 12.
  • Day: chỉ ngày. Một số nguyên dương ứng với ngày trong tháng.

Ví dụ: =DATE(2015,5,20) trả về kết quả ngày 20 tháng 5 năm 2015.

Kiểu trả về khi dùng hàm DATE

12. Hàm INDEX

Hàm INDEX là hàm cho kết quả trả về là 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: =INDEX(array, row_num, column_num)

Với:

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

Ví dụ: Bây giờ, giả sử bạn cần tìm giá trị của phần tử ở dòng thứ 4, cột thứ 1 trong mảng. Mảng ở đây gồm 10 dòng và 4 cột (có địa chỉ là B4:E13). Bạn nhập công thức: =INDEX(B4:E13,4,1)

Giải thích công thức (theo thứ tự các đối số trong công thức từ trái sang):

  • B4:E13 là mảng chứa giá trị cần trả về.
  • 4 là số thứ tự của hàng trong mảng chứa giá trị cần trả về.
  • 1 là số thứ tự của cột trong mảng chứa giá trị cần trả về.

Sau khi nhập xong, bạn nhấn Enter. Kết quả trả về sẽ như hình dưới.

Lấy giá trị dòng, cột trong bảng cần lấy

 13. Hàm MATCH

Hàm MATCH là hàm tìm kiếm, xác định vị trí tương đối của một giá trị trong một mảng hoặc một ô.

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

Với:

  • Lookup_value: Giá trị mà bạn muốn tìm kiếm.
  • Lookup_array: Mảng hoặc dải ô 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 1 hàng hoặc 1 cột.

– Match_type: Xác định kiểu khớp 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.

Ví dụ: Dưới đây là hồ sơ thi tuyển của các học sinh và bạn muốn tìm vị trí của học sinh Lâm Thiên Trang.Tại ô G3 ta thực hiện công thức: =MATCH(G1,A2:A9,0)

Trả về số thứ tự của ô chứa giá trị cần tìm

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

1. Hàm LEFT/RIGHT/MID

– Hàm LEFT dùng để cắt chuỗi ký tự bên trái chuỗi văn bản mà người dùng chọn.

Cú pháp:    =LEFT(text,[num_chars])

Với:

  • text: Chuỗi văn bản chứa ký tự mà bạn muốn trích xuất
  • num_chars: Là số lượng ký tự mà hàm LEFT trích xuất

Ví dụ: Trong bảng dưới đây, dùng hàm LEFT để tìm 3 ký tự đầu tiên tại ô B2. Nhập công thức =LEFT(B3,3), nhấn Enter và được kết quả như hình.

Lấy 3 ký tự bên trái bằng hàm LEFT

Hàm RIGHT dùng để tách chuỗi ký tự từ bên phải trong một dãy ký tự mà người dùng chọn.

Cú pháp:    =RIGHT(text,[num_chars])

Với:

  • text: Chuỗi văn bản chứa ký tự mà bạn muốn trích xuất
  • num_chars: Là số lượng ký tự mà hàm RIGHT trích xuất

Ví dụ: Trong bảng dưới đây, dùng hàm RIGHT để tìm 7 ký tự cuối tại ô B2. Nhập công thức =RIGHT(B3,7), nhấn Enter và được kết quả như hình.

Lấy 7 ký tự tính từ phải qua

Hàm MID 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í do bạn chỉ định và dựa vào số lượng ký tự do bạn chỉ định.

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

Với:

  • text: Chuỗi văn bản có chứa các ký tự muốn trích xuất.
  • start_num: Ví trí của ký tự thứ nhất mà bạn muốn trích xuất trong văn bản
  • num_chars: Bắt buộc đối với mid. Chỉ rõ số ký tự mà bạn muốn hàm MID trả về từ văn bản.

Ví dụ: Trong bảng dưới đây, dùng hàm MID để tìm 7 ký tự tại ô B2 bắt đầu từ vị trí thứ 3. Nhập công thức =LEFT(B3,3,7), nhấn Enter và được kết quả “ạm Văn “

Lấy 7 ký tự từ vị trí 3 từ trái qua

Hàm LEN

Hàm LEN dùng để đếm ký tự trong một chuỗi hoặc một ô chứa chuỗi ký tự và bao gồm cả khoảng trắng.

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

Ví dụ: Trong ví dụ ta thực hiện đếm ô E3 đang chứa chuỗi ký tự “QuangNghia.com

Đếm số lượng ký tự trong ô

3. Hàm CONCAT

Hàm CONCAT dùng để nối hai hay nhiều chuỗi văn bản thành một chuỗi văn bản.

Cú pháp:   =CONCAT(chuỗi 1, chuỗi 2, chuỗi 3,….)

Ví dụ: =CONCAT(“Quan”,”g”,” “,”Nghĩa”,”.”,”com”) sẽ trả về “Quang Nghĩa.com

Nối chuỗi text bằng hàm CONCAT

15. CONDITIONAL FORMATTING

Công dụng: Làm nổi bật giá trị của ô hoặc một nhóm ô cần tìm.

Ví dụ minh họa: Quan sát bảng dữ liệu sau, yêu cầu làm nổi bật các ô có chứa chữ Việt Nam trong cột Quốc Tịch.

– Bước 1: Dùng chuột bôi đen vùng dữ liệu cần làm nổi bật. Trên thanh công cụ bạn lần lượt chọn các thao tác như sau: Chọn Home->Conditional formatting ->Highlight Cells Rules->Text that Contains…

Tab Home

 Bước 2: Hộp thoại Text that Contains hiện ra, bạn nhập chữ Đĩa mềm 1.4Mb vào ô Format Cells that contain the text . Sau đó nhấn OK để hoàn tất.

Các ô sẽ có giá trị bôi đỏ

 16. PROPER

PROPER rất hữu ích khi cơ sở dữ liệu của bạn có nhiều văn bản được định dạng kỳ lạ trông lộn xộn với chữ viết hoa ở sai vị trí. Vì thế khi sử dụng hàm PROPER sẽ giúp bạn thực hiện thao tác chuyển chữ thường thành chữ in hoa một cách nhanh chóng. 

Cú pháp hàm:  =PROPER(text)

Viết hoa ký tự đầu của mỗi từ

Tính phần trăm trong tổng số

Bước 1: Nhập công thức chia

Nhập F4 khi muốn có giá trị tuyệt đối

Bước 2: Chuyển đổi sang dạng %

Copy giá trị qua cột G, chọn Percentage

17. Hàm AND

Hàm AND là một trong những hàm logic cơ bản trong Excel, được sử dụng để xác định xem liệu tất cả các điều kiện trong một kiểm tra có đúng hay không. Hàm AND ít được dùng độc lập mà thường được dùng kết hợp với những hàm thực hiện các kiểm nghiệm logic khác, đặc biệt là hàm IF.

– Cú pháp của hàm AND: =AND(Điều kiện 1,Điều kiện 2,…)

Trong đó: Điều kiện 1, Điều kiện 2,… là các mệnh đề logic.

– Kết quả của hàm AND là:

  • TRUE: khi tất cả các mệnh đề logic đều đúng.
  • FALSE: khi một mệnh đề logic bất kỳ nào trong hàm sai.
Hàm AND cho ra giá trị Đúng – Sai
Kết hợp hàm AND và IF

18. Hàm VALUE

– Công dụng: là hàm chuyển đổi chuỗi ký tự thành số.

Cú pháp:                         =VALUE(text)

Trong đó:  Text: Văn bản trong ngoặc kép hoặc một tham chiếu đến một ô có chứa văn bản bạn muốn chuyển đổi.

Hướng dẫn cách dùng hàm VALUE với hàm RIGHT, MID, LEFT

Ngoài ra, hàm VALUE trong Excel còn được kết hợp với hàm RIGHT, MID, LEFT để tính toán.

Hàm VALUE với hàm RIGHT/ LEFT

Mục đích để trích xuất các ký tự đầu/ cuối từ một chuỗi và trả về kết quả dưới dạng số. Hàm RIGHT dùng cho việc trích xuất các ký tự bên phải của chuỗi mà bạn muốn trích xuất. Tương tự, hàm LEFT là dùng cho các ký tự muốn trích xuất bên trái.

Công thức: =VALUE(RIGHT(A,n)) hoặc =VALUE(LEFT(A,n))

Trong đó:

  • A: Là ô có chứa đoạn văn bản mà bạn muốn chuyển đổi.
  • n: Là số ký tự mà bạn muốn trích xuất.
Đổi text 1123 sau khi lấy bằng hàm RIGHT thành dạng số

Hàm VALUE với hàm MID

Mục đích của việc kết hợp hàm VALUE và MID trong Excel là để trích xuất các ký tự từ giữa chuỗi bắt đầu bằng ký tự thứ t.

Công thức: =VALUE(MID(A,t,n))

Trong đó:

  • A: Là ô có chứa đoạn văn bản mà bạn muốn chuyển đổi.
  • t: Số ký tự bắt đầu muốn trích xuất
  • n: Là số ký tự mà bạn muốn trích xuất.
Lấy 1234 từ vị trí số 6

Tách giờ, phút, giây

  • Hàm HOUR: Hay còn gọi là hàm giờ, là hàm được sử dụng trong Excel để trả về các giá trị giờ. Giá trị giờ được trả về ở dạng số nguyên từ 0 (12:00 A.M) đến 23 (11:00 P.M).
  • Hàm MINUTE: Hay còn gọi là hàm phút, là hàm được sử dụng trong Excel để trả về các giá trị phút. Giá trị phút được trả về ở dạng số nguyên từ 0 đến 59.
  • Hàm SECOND: Hay còn gọi là hàm giây, là hàm được sử dụng trong Excel để trả về các giá trị giây. Giá trị giây được trả về ở dạng số nguyên từ 0 đến 59.

Cú pháp:           

  • =HOUR(serial_number)
  • = MINUTE(serial_number)
  • =SECOND(serial_number)

Với serial_number: Đối số bắt buộc, là ô chứa thời gian mà bạn muốn tách ra.

Lấy giờ bằng hàm Hour

Link download: