Tổng quan về các công thức trong Excel thường được sử dụng

Excel là công cụ văn phòng hữu ích chứa đựng rất nhiều các hàm khác nhau giúp việc thực hiện các phép tính được nhanh chóng và thuận tiện hơn. Trong nội dung bài học Excel hôm nay, Unica sẽ tổng hợp các hàm công thức trong excel thường được sử dụng trong quá trình học tập và làm việc, hãy cùng tìm hiểu nhé.

Công thức Excel là gì?

Công thức excel được biết đến là các phương trình thực hiện các phép tính toán khác nhau ở trong bảng tính. Hiện nay mặc dù Microsoft đã giới thiệu thêm rất nhiều hàm tính toán để phục vụ cho nhu cầu làm việc và học tập của người dùng, tuy nhiên khái niệm công thức excel về cơ bản vẫn giữ nguyên và không có gì thay đổi trên tất cả các phiên bản.

Đặc điểm chung của các công thức excel có thể kể đến như:

• Tất cả các công thức excel đều sẽ bắt đầu bằng dấu =.
• Sau dấu bằng bạn sẽ bắt đầu nhập phép tính hoặc hàm. Ví dụ: Bạn muốn cộng giá trị từ ô B1 đến ô B5 thì bạn có thể nhập công thức excel như sau: = B1 + B2 + B3 + B4 + B5 hoặc SUM: = SUM (B1: B5).
• Sau khi nhập xong công thức bạn nhấn phím Enter để hoàn tất.

Ví dụ công thức excel

Thành phần của các công thức Excel

Trong quá trình tạo một công thức trong excel bạn sẽ sử dụng các phần tử khác nhau để cung cấp dữ liệu nguồn cho công thức. Đồng thời, bạn cũng phải chỉ ra được các toán tử sẽ được thực hiện trong dữ liệu đó. Tuỳ thuộc vào công thức excel mà bạn tạo ra mà nó có thể bao gồm những thành phần sau:

• Hằng số: Đây là số hoặc các giá trị văn bản mà bạn sử dụng để nhập trực tiếp vào một công thức tính toán. ·

• Tham chiếu: Đây là giá trị tham chiếu đến ô chứa giá trị bạn muốn sử dụng trong công thức Excel. Ví dụ: = SUM (A1, A2, B5), trong đó: A1, A2, B5 là tham chiếu.

• Tên: Tên sẽ được xác định cho một phạm vi ô, hằng, bảng, hoặc hàm, ví dụ = SUM (my_name).

• Các hàm: Đây là công thức được xác định trước trong Excel chức năng tính toán.

• Toán tử: Đây là các ký hiệu đặc biệt chỉ định loại hoạt động hoặc tính toán sẽ được thực hiện.

Ví dụ: Cho một công thức excel như sau: PI() * A2 ^ 2

Các thành phần của công thức này như sau:

• Hàm PI(): Hàm này sẽ trả về giá trị số PI=3,142.

• Tham chiếu: A2 trả về giá trị trong ô A2

• Hằng số: Giá trị số hoặc văn bản được nhập trực tiếp vào công thức.

• Toán tử: Mũ ^ chức năng nâng một số lên một bậc luỹ thừa và toán tử * chức năng nhân các số.

Tạo các công thức excel tham chiếu đến giá trị trong các ô khác

Để tạo các công thức excel tham chiếu đến giá trị trong các ô thì bạn thực hiện như sau:

• Đầu tiên chọn một ô

• Sau đó nhập dấu = để bắt đầu.

• Tiếp theo chọn một ô hoặc nhập trực tiếp địa chỉ vào ô được chọn.

• Nhập một toán tử. Ví dụ toán tử “-” cho phép trừ hoặc toán tử “+” cho phép cộng.

• Chọn một ô tiếp theo hoặc nhập địa chỉ của ô đó vào ô được chọn.

• Cuối cùng nhấn Enter. Khi này kết quả của phép tính sẽ xuất hiện trong ô đang chứa công thức excel.

Nhập công thức excel có chứa một hàm tích hợp sẵn

Nếu bạn muốn nhập công thức excel chứa một hàm tích hợp sẵn thì bạn thực hiện như sau:

• Đầu tiên cần chọn một ô trống.

• Tiếp theo sau đó nhập dấu = đằng trước rồi nhập hàm. Ví dụ nếu muốn tính tổng thì nhập công thức excel như sau: =SUM.

• Tiếp theonhập dấu mở ngoặc và chọn dải ô mà bạn muốn tính tổng.

• Đóng dấu ngoặc đơn lại và nhấn Enter để nhận kết quả.

Xem công thức excel đã nhập như thế nào?

Sau khi đã nhập công thức vào một ô thì công thức đó sẽ xuất hiện trên thanh công thức. Để xem công thức trong thanh công thức rất đơn giản, bạn chỉ cần nhấn vào ô đó là được.

Xem công thức đã nhập trên thanh công thức

Công thức excel chuyên sâu

Dưới đây là tổng hợp các công thức excel chuyên sâu thường hay được sử dụng nhất trong quá trình học tập và làm việc, bạn hãy tham khảo nhé.

Công thức excel với hàm SUM

Tất cả công thức trong Excel được thực hiện bằng dấu = và theo sau là các thẻ văn bản cụ thể biểu thị công thức bạn muốn Excel thực hiện.

Công thức sum trong Excel là một trong những công thức cơ bản nhất, cho phép bạn tìm tổng của 2 hoặc nhiều các giá trị khác nhau. Muốn thực hiện tính tổng của các giá trị chỉ cần nhập công thức =SUM(giá trị 1, giá trị 2,…)

Các giá trị ở đây cũng có thể là các số thực, hoặc bằng số trong một ô cụ thể của bảng tính của bạn.

Ví dụ về cách sử dụng của hàm SUM trong Excel:

Quan sát bảng dữ liệu dưới đây, yêu cầu tính tổng điểm của các môn học của từng học sinh. 

Bước 1: Trong ô tham chiếu muốn hiển thị kết quả, bạn nhập công thức hàm: =SUM(C9:F9)

Hàm Sum trong Excel

Bước 2: Nhấn Enter để nhận kết quả. 

Kết quả hàm SUM

>>> Tất cả đều có trong cuốn sách “HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO”

ĐĂNG KÝ MUA NGAY

Công thức excel với hàm MIN/MAX 

Nhắc đến các lệnh trong excel hàm logic không thể không nhắc đến hàm MIN/MAX. Sử dụng hàm MIN để tìm số nhỏ nhất trong một phạm vi giá trị và hàm MAX để tìm số cao nhất. Cụ thể cách tính trong excel hàm này như sau:

Cú pháp hàm MAX: 

=MAX(number1, number2,…)

Trong đó: 

• Number 1, Number 2: là giá trị cần so sánh.

Ví dụ về hàm Max: Dựa vào bảng số điểm về điểm số của các bạn học sinh, yêu cầu tìm điểm CAO nhất trong lớp.

Bước 1: Trong ô muốn hiển thị kết quả, bạn nhập công thức sau: = MAX(C2:E6), trong đó C2:E6 là vùng dữ liệu có chứa giá trị lớn nhất. 

Hàm Max trong Excel

Bước 2: Nhấn Enter để nhận kết quả. 

Kết quả hàm Max trong Excel

Cú pháp hàm MIN: =MIN(number1, number2,…)

Trong đó: 

• Number 1, Number 2 là giá trị cần so sánh.

Ví dụ về hàm MIN: 

Dựa vào bảng số điểm về điểm số của các bạn học sinh, yêu cầu tìm điểm THẤP nhất trong lớp.

Bước 1: Trong ô muốn hiển thị kết quả, bạn nhập công thức sau: = MIN(E2:E6), trong đó E2:E6 là vùng dữ liệu có chứa giá trị nhỏ nhất. 

Hàm MIN trong Excel 

Bước 2: Nhấn Enter để nhận kết quả. 

Kết quả hàm Min trong Excel

Công thức excel với hàm COUNT/COUNTA 

Hàm COUNT/COUNTA cũng là những hàm logic trong excel cơ bản được ứng dụng rất nhiều trong công việc và học tập.

Công thức hàm COUNT trong Excel được ký hiệu =COUNT(Value1, Value2,….)

Trong đó:

• Value 1: Là giá trị đầu tiên để tham chiếu hoặc vùng chọn để đếm ô giá trị.
• Value 2: Là tham số không bắt buộc. 

Công thức này sẽ trả về một giá trị bằng với số lượng mục được tìm thấy trong phạm vi ô mong muốn của bạn. Ví dụ: nếu có 8 ô có giá trị được nhập giữa A1 và A10, = COUNT (A1: A10) sẽ trả về giá trị 8.

Công thức COUNT trong Excel đặc biệt hữu ích cho các bảng tính lớn , trong đó bạn muốn xem có bao nhiêu ô chứa các mục thực tế. Công thức này sẽ không thực hiện bất kỳ phép toán nào về giá trị của các ô. Công thức này chỉ đơn giản là tìm ra có bao nhiêu ô trong một phạm vi được chọn bị chiếm dụng với một cái gì đó.

Ví dụ về cách sử dụng hàm COUNT:

Quan sát bảng dữ liệu sau, yêu cầu dùng công thức Excel COUNT để đếm dữ liệu trong cột.

Bước 1: Trong ô tham chiếu muốn hiển thị kết quả, bạn nhập công thức sau: =COUNT(E4:E7)

Cách sử dụng hàm COUNT trong Excel

Bước 2: Nhấn Enter để nhận kết quả. 

Kết quả hàm COUNT trong Excel

Hàm COUNTA là hàm dùng để đếm các ô trong khoảng trống ở một vùng dữ liệu nhất định. Hàm COUNTA bao gồm các ô chứa giá trị số, chuỗi, giá trị lỗi, và văn bản trống.

Cú pháp hàm COUNTA trong Excel là: =COUNTA(Value1,…)

Trong đó:

• value1 là đối số bắt buộc, là phạm vi ô hoặc giá trị cần đếm.

Ví dụ: Cho một bảng số dưới đây, yêu cầu bạn đếm số lượng ký tự số. Để đếm bạn áp dụng công thức =COUNTA(A2:C5). Kết quả đạt được như hình:

Ví dụ hàm COUNTA trong excel

Công thức excel với hàm AVERAGE

Sẽ là một thiếu xót rất lớn nếu như nhắc tới công thức và hàm trong excel mà không sử dụng hàm AVERAGE. Đây là hàm tính trung bình trong excel.

Bạn có thể sử dụng công thức tính hàm AVEGARE theo định dạng: =AVERAGE(number1,number2,…), hoặc =AVERAGE(giá trị đầu:giá trị cuối). Điều này sẽ tính trung bình của tất cả các giá trị hoặc phạm vi của các ô có trong ngoặc đơn.

Việc tìm trung bình của một phạm vi ô trong Excel giúp bạn không phải tìm các tổng riêng lẻ và sau đó thực hiện một phương trình phân chia riêng trên tổng số của bạn. Sử dụng = AVERAGE làm mục nhập văn bản ban đầu của bạn, bạn có thể để Excel thực hiện tất cả công việc cho bạn.

Cách sử dụng hàm AVERAGE trong Excel:

Quan sát ví dụ sau, yêu cầu tính lương trung bình theo các tháng dựa vào các số liệu trong bảng. 

Bước 1: Tại ô C9 cần hiển thị kết quả, bạn nhập công thức sau: =AVERAGE(C3:C8)

Cách dùng hàm AVERAGE trong Excel

Bước 2: Nhấn Enter để nhận kết quả. 

Kết quả hàm AVERAGE trong Excel 

Phép trừ

Để thực hiện các phép trừ trong Excel, bạn chỉ cần nhập ô cần thực hiện phép trừ và sử dụng công thức hàm SUM hoặc là sử dụng định dạng.

Cách sử dụng của phép trừ bằng hàm Sum:

Quan sát bảng dữ liệu sau, yêu cầu dùng hàm SUM để thực hiện phép trừ với các số liệu cụ thể trong bảng tính. 

Bước 1: Trong ô A3 muốn hiển thị kết quả, bạn nhập công thức như sau: =SUM(A1;-B1;-C1;-D1;-E1)

Thực hiện phép trừ bằng hàm SUM 

Bước 2: Nhấn Enter để nhận kết quả.

Kết quả phép trừ bằng hàm SUM 

Phép nhân 

Phép nhân là cách tính trong excel cơ bản bạn nhất định phải biết. Để thực hiện công thức nhân trong Excel, bạn chỉ cần thực hiện nhập công thức theo định dạng và công thức này dùng dấu hoa thị để biểu thị công thức nhân.

Ví dụ với phép tính:

Phép nhân trong Excel

Phép chia

Phân chia trong Excel là một trong những chức năng đơn giản nhất bạn có thể thực hiện. Để làm như vậy, hãy chọn một ô trống, nhập dấu bằng, “=,” và theo dõi nó với hai (hoặc nhiều) giá trị bạn muốn chia bằng dấu gạch chéo về phía trước, “/,” ở giữa.

Phép chia trong Excel

Hàm COUNTIF

Hàm COUNTIF trong Excel là hàm đếm số ô thỏa mãn một điều kiện nhất định trong một phạm vi ô. Cú pháp của hàm COUNTIF là:

=COUNTIF(range, criteria)

Trong đó:

• range: là đối số bắt buộc, là phạm vi ô cần đếm.
• criteria: là đối số bắt buộc, là điều kiện để xác định các ô cần đếm.

Ví dụ: Cho một bảng thống kê với dữ liệu là các mặt hàng và số lượng tồn như bên dưới. Yêu cầu bạn đếm xem có bao nhiêu mặt hàng còn tồn trên 150 sản phẩm.

Để đếm bạn nhập vào bảng công thức: =COUNTIF(C2:C11,”>150″)

Kết quả bạn sẽ nhận về được 8 mặt hàng còn tồn trên 150 sản phẩm.

Ví dụ hàm COUTIF trong excel

Hàm IF

Hàm IF là hàm logic có điều kiện được ứng dụng rất nhiều trong cuộc sống, nhất là những ai làm trong lĩnh vực kinh doanh, buôn bán. Hàm IF trong Excel là hàm logic cho phép bạn trả về một giá trị nếu điều kiện là True và một giá trị khác nếu điều kiện là False.

Cú pháp của hàm IF là: =IF(logical_test, value_if_true, value_if_false)

Trong đó:

• logical_test: là đối số bắt buộc, là điều kiện cần kiểm tra.
• value_if_true: là đối số bắt buộc, là giá trị trả về nếu điều kiện là True.
• value_if_false: là đối số bắt buộc, là giá trị trả về nếu điều kiện là False.

Ví dụ: Cho một bảng dữ liệu gồm tên học sinh và điểm số. Nhiệm vụ của bạn là phải tính số học sinh có qua môn với điều kiện: Điểm số từ 7 trở lên là đạt và điểm số thấp hơn 7 là không đạt.

Để tính bạn áp dụng công thức vào D2 là: =IF(C2>=7,”Đạt”,”Không Đạt”). Kết quả tính được như hình bên dưới.

Ví dụ cách tính hàm IF trong excel

Công thức excel với hàm SUMIF

SUMIF là công thức tính cơ bản trong excel có chức năng tính tổng các giá trị trong một phạm vi đáp ứng đầy đủ các tiêu chí xác định.

Công thức hàm sumif trong Excel: =SUMIF(range, criteria, [sum_range]).

Trong đó: 

• Range: Là vùng được chọn có chứa các ô điều kiện.
• Criteria: Là điều kiện để thực hiện hàm này.
• Sum_range: Vùng dữ liệu cần tính tổng.

Cách sử dụng hàm SUMIF trong Excel: 

Giả sử bạn muốn xác định lợi nhuận bạn đã tạo từ danh sách khách hàng tiềm năng được liên kết với mã vùng cụ thể hoặc tính tổng tiền lương của một số nhân viên – nhưng chỉ khi họ vượt quá một mức cụ thể. Với hàm SUMIF, bạn có thể dễ dàng cộng tổng các ô đáp ứng các tiêu chí nhất định.

Ví dụ: Quan sát bảng dữ liệu dưới đây, yêu cầu tính tổng thành tiền của các hóa chất có số lượng >200 bằng hàm SUMIF. 

Bước 1: Trong ô muốn hiển thị kết quả, bạn nhập công thức như sau: =SUMIF(D2:D10; “>200”; E2:E10)

Trong đó:

• D2:D10: Là vùng dữ liệu Số lượng theo yêu cầu của ví dụ.
• “>200”: Là điều kiện theo yêu cầu của ví dụ (có số lượng > 200).
• E2:E10: Là vùng dữ liệu Thành Tiền để lấy kết quả.

Hàm SUMIF trong Excel

Bước 2: Nhấn Enter để nhận kết quả. 

Hàm SUMIF trong Excel

Hàm LEFT

Đây là hàm dùng để cắt chuỗi ký tự bên trái chuõi văn bản mà người dùng lựa chọn.

Cú pháp hàm LEFT đó là: =LEFT(text,[num_chars])

Trong đó: 

• text: là chuỗi văn bản chứa ký tự muốn cắt.
• num_chars: là số lượng ký tự mà hàm cắt.

Ví dụ: Cho bảng dữ liệu, yêu cầu bạn sử dụng hàm LEFT để tìm 3 ký tự đầu tiên tại ô B2.

Để tìm bạn nhập vào đó hàm =LEFT(B3,3) => Enter là sẽ nhận được kết quả.

Kết quả tính sử dụng hàm LEFT

Hàm RIGHT

Đây là hàm excel có chức nă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 hàm đó là: =RIGHT(text,[num_chars])

Trong đó:

• text: là chuỗi văn bản chứa ký tự mà bạn muốn tách.
• num_chars: là số lượng ký tự mà hàm muốn tách.

Ví dụ: Cho bạn dữ liệu, nhiệm vụ của bạn là dùng hàm RIGHT để tìm 7 ký tự cuối tại ô B2. Cách tìm như sau:

Nhập công thức =RIGHT(B3,7) => Nhấn Enter.

Kết quả sau khi tính công thức hàm RIGHT

Hàm MID

Đối với hàm MID bạn có thể tách văn bản từ vị trí mà mình mong muốn và cũng có thể tách số lượng ký tự bao nhiêu tuỳ ý.

Lệnh trong excel hàm MID như sau: =MID(text, start_num, num_chars)

Trong đó:

• text: là chuỗi văn bản có chứa các ký tự muốn tách.
• start_num: vị trí của ký tự đầu tiên trong văn bản mà bạn muốn tách.
• num_chars: là chỉ rõ số ký tự mà bạn muốn hàm MID trả về từ văn bản. Đây là chỉ số bắt buộc.

Ví dụ: Cho một bảng dữ liệu dưới đây, yêu cầu bạn dùng hàm MID để tìm 7 ký tự tại ô B2 bắt đầu từ vị trí thứ 3.

Để tìm bạn nhập công thức =LEFT(B3,3,7) => Nhấn Enter và được kết quả.

Ví dụ cách sử dụng hàm MID

Hàm LEN

Hàm LEN trong Excel là hàm đếm số ký tự trong một chuỗi ký tự, kể cả ký tự khoảng trống.

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

Trong đó:

• text: là đối số bắt buộc, là chuỗi ký tự cần đếm.

Ví dụ: Bạn đếm chuỗi ký tự tại ô A1 xem có bao nhiêu ký tự

Ví dụ sử dụng hàm LED

Hàm CONCAT

Hàm CONCAT trong Excel là hàm nối chuỗi, cho phép bạn nối hai hoặc nhiều chuỗi văn bản thành một chuỗi.

Cú pháp của hàm CONCAT là: =CONCATENATE(text1, [text2], …)

Trong đó:

• text1: là đối số bắt buộc, là chuỗi văn bản đầu tiên cần nối.
• text2, [text3], … là các đối số tùy chọn, là các chuỗi văn bản cần nối bổ sung.

Ví dụ trong bảng dưới đây

Ví dụ hàm CONCAT trong excel

Hàm NOW

Bạn muốn xem ngày giờ hiện tại bất cứ khi nào bạn mở một bảng tính cụ thể? Nhập = NOW() vào ô nơi bạn muốn xem. Nếu bạn muốn xem một ngày trong tương lai từ ngay bây giờ, bạn có thể sử dụng công thức như =NOW()+32. Hàm now không nhận bất kỳ đối số nào vì vậy đừng đặt bất cứ thứ gì vào giữa các dấu ngoặc.

Cách sử dụng của hàm NOW trong Excel:

Quan sát bảng dữ liệu sau, yêu cầu dùng hàm NOW để trả về ngày và thời gian hiện tại.

Bước 1: Trong ô kết quả, bạn nhập công thức sau: =NOW()

Hàm NOW trong Excel 

Bước 2: Nhấn Enter để nhận kết quả sau khi áp dụng công thức hàm. 

Kết quả hàm NOW trong Excel

>>> Xem thêm: 7 cách đổi chữ thường thành chữ hoa trong excel nhanh chóng

Hàm DATE

Công thức DATE trong Excel được ký hiệu = DATE (year,month,day). Công thức này sẽ trả về một ngày tương ứng với các giá trị được nhập trong ngoặc đơn – ngay cả các giá trị được gọi từ các ô khác.

Tạo ngày từ các giá trị ô:

Hàm Day trong Excel 

Tự động thiết lập ngày hôm nay nhập công thức:

Nhập công thức hàm Day trong Excel

Trong cả hai cách sử dụng công thức ngày của Excel, ngày trả về của bạn phải ở dạng “mm/dd/yy” – trừ khi chương trình Excel của bạn được định dạng khác nhau.

Hàm VLOOKUP

Hàm VLOOKUP thực hiện tra cứu dọc bằng cách tìm kiếm một giá trị trong cột đầu tiên của bảng và trả về giá trị trong cùng một hàng ở vị trí index_number.

Hàm VLOOKUP là một hàm tích hợp trong các hàm của Excel. Nó có thể được sử dụng như một hàm bảng tính (WS) trong Excel. Là một hàm trang tính, hàm VLOOKUP có thể được nhập như một phần của công thức trong một ô của trang tính.

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: là bảng cần dò tìm
• col_index_num: là vị trí cột cần lấy giá trị
• range_lookup: nhập vào số 0 để tìm chính xác và nhập vào số 1 để tìm gần đúng

Cách sử dụng của hàm Vlookup:

Quan sát ví dụ sau, yêu cầu xếp loại học sinh theo thứ hạng yếu, trung bình, khá, giỏi.

Bước 1: Trong ô cần hiển thị kết quả, bạn nhập công thưc sau: =VLOOKUP(B2,$E$2:$G$9,2,0)

Trong đó:

• B2: Giá trị cần dò tìm giá trị
• $E$2:$G$9: Bảng đối chiếu giá trị (đã khóa)
• 2: Vị trí cột cần dò tìm
• 0: Chọn giá trị tuyệt đối để dò tìm

Hàm Vlookup trong Excel

Bước 2: Kết quả sau khi hoàn thành như sau. 

Hàm Vlookup trong Excel

Quan sát bảng dữ liệu bạn có thể thấy có lỗi #N/A xảy ra. Nguyên nhân là do giá trị cần tìm phải là số nguyên dương từ 1-10, lỗi xảy ra do không chấp nhập số trong khoảng hoặc chứa dấu phẩy. 

Vì vậy khi sử dụng hàm Vlookup, bạn cần lưu ý chọn điều kiện cho giá trị hiển thị, tuyệt đối hay tương đối để đảm bảo kết quả hiển thị đúng với mục đích tìm kiếm của mình.

Sau khi sửa lại công thức đúng, bạn có kết quả như sau: 

Hàm Vlookup trong Excel 

Hàm INDEX

Hàm INDEX trong Excel là hàm trả về một giá trị hoặc tham chiếu tới một giá trị trong bảng hoặc phạm vi.

Cú pháp của hàm INDEX là: =INDEX(array, row_num, column_num)

Trong đó:

• array: là đối số bắt buộc, là phạm vi ô hoặc một hằng số mảng.
• row_num: là đối số tùy chọn, là số thứ tự hàng trong mảng chứa giá trị được trả về. Nếu bỏ qua đối số này, hàm INDEX sẽ trả về giá trị đầu tiên trong mảng.
• column_num: là đối số tùy chọn, là số thứ tự cột trong mảng chứa giá trị được trả về. Nếu bỏ qua đối số này, hàm INDEX sẽ trả về giá trị đầu tiên trong cột.

Ví dụ: Cho bảng dữ liệu, yêu cầu bạn tìm giá trị dữ liệu ở dòng thứ 4 cột 1 trong mảng, mảng này sẽ gồm 10 dòng và 4 cột (B4: E13).

Để tìm dữ liệu bạn nhập vào bảng công thức: =INDEX(B4:E13,4,1)

Trong đó:

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

Kết quả cuối cùng nhận được

Hàm MATCH

Hàm MATCH trong Excel là hàm tìm kiếm vị trí tương đối của một giá trị trong một phạm vi ô.

Cú pháp của hàm MATCH là: =MATCH(lookup_value, lookup_array, match_type)

Trong đó:

• lookup_value: là đối số bắt buộc, là giá trị cần tìm kiếm.
• lookup_array: là đối số bắt buộc, là phạm vi ô cần tìm kiếm.
• match_type: là đối số tùy chọn, xác định cách so sánh giá trị tìm kiếm với giá trị trong lookup_array.

Ví dụ: Cho bảng hồ sơ tuyển sinh, yêu cầu bạn tìm vị trí của học sinh tên Lâm Thiên Trang. Cách thực hiện:

Tại ô G3 bạn nhập vào đó công thức: =MATCH(G1,A2:A9,0) => Nhấn Enter để nhận kết quả.

Cách tính hàm MATCH trong excel

Hàm HLOOKUP

Hàm HLOOKUP trong Excel là hàm dò tìm dữ liệu trong một bảng, một phạm vi theo hàng ngang (từ trái qua phải) và trả về dữ liệu tương ứng theo hàng dọc (từ trên xuống dưới) tương ứng.

Cú pháp của hàm HLOOKUP là: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Trong đó:

• lookup_value là đối số bắt buộc, là giá trị cần tìm kiếm.
• table_array là đối số bắt buộc, là phạm vi ô chứa dữ liệu cần tìm.
• row_index_num là đối số bắt buộc, là số thứ tự hàng trong table_array chứa giá trị được trả về.
• range_lookup là đối số tùy chọn, xác định cách so sánh giá trị tìm kiếm với giá trị trong table_array.

Ví dụ: Cho bảng dữ liệu, nhiệm vụ của bạn là xếp loại học sinh trong bảng 1 (B3:D8) với dữ kiện ở bảng 2 (B11:F12).

Cách thực hiện: Nhập vào ô D4 hàm =HLOOKUP(C4,$B$11:$F$12,2,1). Kết quả nhận được:

Ví dụ hàm HLOOKUP trong excel

>>> Xem thêm: Cách sử dụng Goal Seek trong Excel đơn giản và nhanh chóng

Hàm tính % trong excel

Percentage là công thức tính tỷ lệ phần trăm trong Excel, bạn hãy nhập các ô tìm tỷ lệ phần trăm, để chuyển đổi giá trị thập phân thành tỷ lệ phần trăm, chọn ô và nhấp vào tab trang chủ chọn Percentage.

Percentage trong Excel

Không có công thức Excel để tính phần trăm cụ thể, nhưng chúng ta có thể quy đổi và dạng tỉ lệ phần trăm để các bạn không bị vướng mắc.

Cách sử dụng của Percentage trong Excel:

Quan sát ví dụ sau, yêu cầu tính tổng phần trăm trong tổng số của các dữ liệu trong bảng.

Công thức áp dụng như sau: Tỷ lệ phần trăm (%) = 1 phần / tổng số

Bước 1: Nhập công thức tính tỷ lệ phần trăm (%) = 1 phần / tổng số (Total) vào ô muốn hiển thị kết quả.

Đầu tiên, bạn nhấp vào ô C2 để tính % của Cherries trong tổng số. Tiếp theo bạn nhập dấu chia (/), sau đó click vào ô tổng số B10. 

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

Bước 2: Nhấn Enter để nhận kết quả. Lưu ý để mẫu số làm tham chiếu tuyệt đối, bạn phải nhập $ theo cách thủ công.

Cuối cùng, đặt trỏ chuột vào ô kết quả đầu tiên cho xuất hiện dấu + rồi kéo xuống hết các ô Excel tiếp theo để đảm bảo kết quả được hiển thị đầy đủ. 

Kết quả hiển thị

Hàm làm tròn số

Để làm tròn số lên bạn áp dụng công thức hàm Round =ROUND(Number,N)

Trong đó:

• Number: là số cần làm tròn.
• N: là giá trị thực tế truyền vào cho tham số khi gọi hàm, có thể âm hoặc dương. Đây là đối số

Ví dụ: Cách làm tròn số bằng hàm ROUND như sau:

Cho một bảng tính excel, bạn nhập vào đó với cú pháp =ROUND(Number,N).

Trường hợp nếu N = 0 thì bạn sẽ làm tròn tới số nguyên gần nhất.

Trường hợp n=0

Trường hợp nếu N > 0 thì bạn sẽ được làm tròn tới vị trí thập phân chỉ định. Nếu N =1 lấy 1 số lẻ, n=2 lấy 2 số lẻ,…

Trường hợp n>0

Trường hợp nếu N  thì bạn sẽ được làm tròn sang bên trái dấu thập phân. Nếu N = -1 thì làm tròn đến hàng chục, N = -2 làm tròn đến trăm và N = -3 là đến hàng nghìn…

Trường hợp n

Kết luận

Trên đây Unica đã giới thiệu cho bạn một số các công thức trong excel cơ bản, mong rằng những thông tin trên có thể giúp ích cho bạn. Chúc bạn làm việc với excel thành thạo để phục vụ hiệu quả nhất cho công việc. Nếu bạn đọc quan tâm có thể tham khảo thêm các khóa học tin học văn phòng khác trên Unica để hiểu rõ hơn về các thủ thuật, cách sử dụng hàm trong Excel.