Các hàm trong excel cơ bản dân văn phòng cần biết

Đây có thể coi là bài tổng hợp nhiều hàm của Microsoft Excel, rất cần thiết cho người mới bắt đầu tìm hiểu về Excel. Trong bài viết này, chúng tôi sẽ liệt kê các hàm văn bản, toán học, thống kê, logic và các hàm khác cần thiết nhất cùng với các ví dụ về công thức và liên kết đến các tài nguyên liên quan. Xin mời các bạn cùng theo dõi!

Excel cung cấp một số lượng lớn các hàm để phân tích, kiểm tra và tính toán dữ liệu
Excel cung cấp một số lượng lớn các hàm để phân tích, kiểm tra và tính toán dữ liệu

Giới thiệu về các hàm trong excel

Excel cung cấp một số lượng lớn các hàm để phân tích, kiểm tra và tính toán dữ liệu từ cơ bản đến nâng cao. Tuy nhiên chúng ta không nhất thiết phải biết hết những chức năng này và nói thật là chúng ta không thể biết hết được, nhớ hết chúng sẽ giảm tuổi thọ đi rất nhiều. Thay vì nhớ nhiều chức năng như vậy thì bạn nên tìm hiểu để lựa chọn lọc một số chức năng thông dụng nhất.

Đúng ! Và trong bài viết này chúng tôi sẽ liệt kê ra những hàm thông dụng và được sử dụng nhiều nhất trong Excel. Tuy nhiên chúng tôi sẽ không khám phá từng hàm trong Microsoft Excel mà chỉ cung cấp cho bạn cái nhìn tổng quan về các hàm hữu ích nhất trong Excel. Kèm theo đó là các ví dụ chi tiết để làm rõ vấn đề.

Những tính năng và lợi ích khi sử dụng excel

Excel được coi là công cụ hỗ trợ đắc lực cho việc quản lý và điều hành doanh nghiệp một cách hiệu quả.

1. Xây dựng biểu đồ

Excel là công cụ mà trong đó cho phép người dùng có thể thực hiện khai thác dữ liệu tiềm năng bằng cách sử dụng một số công thức bảng tính. Các dữ liệu sẽ được chèn vào từng ô trong excel theo hàng ngang hoặc cột dọc. Sau đó chúng sẽ được sắp xếp, lọc và trình bày một cách trực quan thông qua các biểu đồ theo ý muốn của người dùng. Từ đó giúp tăng tính thuyết phục cho các báo cáo kinh doanh của doanh nghiệp. Ngoài ra, Excel cũng đề xuất các biểu đồ tốt nhất cho từng loại dữ liệu.

2. Sử dụng định dạng có điều kiện

Người dùng Excel có thể định dạng bảng tính của họ với các kiểu phông chữ khác nhau (đậm, nghiêng, gạch dưới, …), phân biệt dữ liệu giữa các cột / hàng, tìm giá trị lớn nhất của dữ liệu và hơn thế nữa. Ngoài ra, người dùng cũng có thể chọn màu phù hợp bằng nút lệnh Phân tích nhanh, sau đó sử dụng công cụ định dạng.

 3. Giúp xác định xu hướng

Khi hiển thị dữ liệu dưới dạng biểu đồ hoặc đồ thị, Excel sẽ giúp người dùng xác định các mốc quan trọng trong dữ liệu đang được phân tích. Điều này giúp doanh nghiệp xác định được xu hướng để đưa ra những dự đoán chính xác nhằm thực hiện chiến lược phù hợp cho doanh nghiệp.

4. Truy cập trực tuyến

Excel chính là một phần của Office 365 Business và Office 365 Business Premium. Điều này có nghĩa là các nhà lãnh đạo doanh nghiệp có thể truy cập chương trình này thông qua các thiết bị khác nhau và ở bất kỳ vị trí nào. Miễn là bạn có PC, máy tính xách tay, điện thoại thông minh hoặc máy tính bảng có thể truy cập web, bạn có thể truy cập Excel ở mọi nơi. Điều này làm cho môi trường làm việc từ xa trở nên phổ biến hơn bao giờ hết.

Tổng hợp các hàm trong excel bạn cần biết

Sau đây là 15 hàm cơ bản trong excel bạn nên làm quen!

1. Hàm SUM: Hàm có chức năng tính tổng các đối số

Các chức năng của hàm Sum: Hàm SUM trong Excel thường được dùng để tính tổng các đối số trên bảng tính. Sử dụng hàm Sum trong Excel giúp bạn tiết kiệm rất nhiều thời gian và công sức so với cách tính thủ công thông thường.

– Cú pháp thực hiện: = SUM (số 1, số 2, …)

– Chức năng: Tính tổng số 1, số 2, …

Trong đó: số 1, số 2, … là các đối số mà bạn muốn tính tổng

– Chú ý:

Các ô có giá trị logic TRUE được coi là 1, FALSE được coi là 0.

Nếu một đối số là một mảng hoặc tham chiếu, thì chỉ các giá trị số trong mảng hoặc tham chiếu đó mới được tính. Ngoài ra, các giá trị khác trong mảng hoặc tham chiếu sẽ bị bỏ qua.

Ví dụ về hàm SUM
Ví dụ về hàm SUM

VD1:

= SUM (2, 3, 4) có giá trị là 9.

= SUM (2, 3, TRUE) có giá trị là 6.

= SUM (“2”, “3”, 1) bằng 6.

2. Hàm AVERAGE: Hàm có tính năng tính giá trị trung bình

– Hàm Average: Hàm AVERAGE là một hàm cơ bản trong Excel hỗ trợ tính giá trị trung bình của một dãy số trong bảng tính Excel, hỗ trợ người dùng tính toán nhanh hơn nếu số phần tử trong dãy nhiều và dài.

– Cú pháp: AVERAGE (Number1, [Number2], [Number3],…)

Hàm AVERAGE có tối đa 256 đối số, có thể là số, tên, phạm vi hoặc tham chiếu ô chứa một số. Tham chiếu ô hoặc phạm vi có chứa tham chiếu lôgic, văn bản hoặc ô trống, các giá trị đó bị bỏ qua, trừ đi 0 hoặc được nhập trực tiếp vào danh sách đối số.

Ví dụ: Cho bảng điểm của một số học sinh, tính điểm trung bình các môn của mỗi học sinh. Tính điểm trung bình chung của học sinh.

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

– Hàm Min / Max: Cách sử dụng hàm MAX và hàm MIN để tìm giá trị lớn nhất và nhỏ nhất của các đối số hoặc phạm vi dữ liệu.

– Cú pháp:

MAX (số 1, số 2, …)

MIN (số 1, số 2, …)

Số 1 và số 2 là các đối số hoặc phạm vi dữ liệu.

– Chức năng: Trả về giá trị lớn nhất và nhỏ nhất của các đối số hoặc dải dữ liệu có chứa số

Ví dụ:

– Trả về giá trị lớn nhất trong dãy số 10, -20, 3, 4, 11

Chúng ta sẽ có công thức được xây dựng: MAX (10, -20, 3, 4, 11) = 11.

– Trả về giá trị nhỏ nhất trong các số: 20, 100, 30, 45.

Chúng ta sẽ có công thức xây dựng: MIN (20, 100, 30, 45) = 20.

4. Hàm TRIM: Hàm xóa khoảng trắng trong văn bản

Hàm Trim: Hàm TRIM là một trong những hàm Excel cơ bản không thể bỏ qua giúp bạn xử lý các khoảng trắng phân tán trong trang tính khi nhập hoặc dán văn bản từ cơ sở dữ liệu, trang web, chương trình văn bản khác, … Giả sử, bạn muốn Để loại bỏ khoảng trắng trong cột A2, bạn chỉ cần sử dụng hàm TRIM trong ô B2 với công thức: = TRIM (A2). Sau đó bạn thực hiện copy công thức xuống cuối danh sách để chúng ta có được kết quả xóa các khoảng trắng tương tự như ở B2.

– Cú pháp: TRIM (Văn bản)

Trong đó: Văn bản là văn bản chứa khoảng trắng cần xóa

Ví dụ: Bạn có một sổ làm việc với cột Họ và Tên chứa các ký tự màu trắng, bây giờ cần xóa những khoảng trắng này.

Hàm xóa khoảng trắng dư thừa bỏ khoảng trắng trong Excel, hàm cơ bản trong excel.

Để loại bỏ các khoảng trắng, thì tại ô D6, chúng ta sẽ nhập công thức =TRIM(A6) và có được kết quả như hình.

 Hàm TRIM trong excel
Hàm TRIM trong excel

5. Hàm COUNT / COUNTA: Hàm đếm dữ liệu / đếm các ô không trống

– Chức năng Count / Counta: Hàm này sẽ giúp bạn đếm các ô chứa số trong một bảng dữ liệu khổng lồ thay vì phải đếm thủ công. Ví dụ: nhập = COUNT (A1: A20) sẽ dẫn đến có bao nhiêu ô chứa các số từ ô A1 đến A20 trong trang tính. Tương tự, hàm COUNTA cũng sử dụng hàm COUNTA để hỗ trợ đếm ô.

Hơn nữa, COUNTA thay vì chỉ đếm ô có số thì hàm này sẽ hỗ trợ bạn để có thể đếm bất kỳ ô nào có chứa nội dung.

Hàm Count để đếm dữ liệu, các đối tượng trong chuỗi trong một trang tính nhất định.

Hàm Counta trong Excel là hàm cho phép bạn đếm các ô có dữ liệu trong một phạm vi nhất định.

– Cú pháp của hàm Count / Counta

Counta (Giá trị1, [giá trị2], [giá trị3], …)

Value1 là đối số bắt buộc, phạm vi dữ liệu được tính.

Value2 và Value3 là tùy chọn vì chúng được đặt trong [], không bắt buộc.

COUNT (lựa chọn để đếm)

Ý nghĩa: Dùng để đếm số ô chứa dữ liệu Number trong vùng được chọn.

Ví dụ 1:

– Đếm số học sinh đạt trong kỳ thi này, học sinh không đạt được ký hiệu là “tr”.

Để có thể thực hiện được, bạn sẽ sử dụng công thức ô C15: =COUNT(C6:C14)

Hàm Counta trong excel
Hàm Counta trong excel

Ví dụ 2: Đếm số ô không rỗng ở bảng sau:

Chúng ta sẽ sử dụng công thức tại ô D10 như sau: =COUNTA(A4:D9)

Ví dụ về hàm counta trong excel
Ví dụ về hàm counta trong excel

6. Hàm LEN: Hàm có tính năng đo độ dài của chuỗi

– Sử dụng hàm Len: Để đo độ dài của bất kỳ chuỗi ký tự nào, bạn có thể sử dụng hàm LEN trong Excel, hàm này trả về độ dài chính xác của chuỗi mà từ đó bạn có thể làm những gì bạn muốn. .

– Cú pháp: LEN (text)

Trong đó, text là chuỗi ký tự

– Chức năng: Trả về độ dài của chuỗi, kể cả ký tự khoảng trắng.

Ví dụ :

Đo độ dài của chuỗi “Đại học Đà Nẵng”.

Công thức tính sẽ là: = LEN (“Đại học Đà Nẵng”) = 21.

 7. Hàm CONCATENATE: Hàm nối chuỗi ký tự

Sử dụng hàm CONCATENATE: Hàm CONCATENATE giúp kết hợp nội dung của các ô với nhau. Ví dụ: nếu bạn muốn kết hợp các ô họ và tên thành một ô biểu thị tên đầy đủ, bạn sử dụng hàm CONCATENATE và di chuyển số lượng ô cần kết hợp để có được kết quả mong muốn. Để tạo khoảng trắng hoặc dấu chấm, dấu phẩy, … giữa các ô kết hợp, bạn chỉ cần đặt dấu chấm, dấu phẩy, ô trống, … trong dấu ngoặc kép.

– Cú pháp thực hiện: CONCATENATE (text 1, text 2, …)

Trong đó:

Văn bản 1: là chuỗi thứ nhất. Cần thiết.

Văn bản 2…: tùy chọn. Tối đa 255 có thể là chuỗi. Các chuỗi phải được phân tách bằng dấu phẩy.

– Chức năng: Dùng để nối các chuỗi văn bản thành một chuỗi đơn. Các chuỗi được nối có thể là số, văn bản, tham chiếu ô.

Hàm CONCATENATE trong excel
Hàm CONCATENATE trong excel

8. Hàm DAYS: Hàm có chức năng trả về số ngày giữa 2 ngày trong Excel

– Sử dụng hàm Days: Hàm trả về số ngày giữa 2 ngày trong Excel

– Cú pháp: DAYS (end_date, start_date).

Trong đó:

end_date: Là ngày kết thúc muốn chỉ định số ngày, là một tham số bắt buộc.

start_date: Là ngày đầu tiên muốn chỉ định số ngày, là tham số bắt buộc.

– Chú ý:

Nếu đối số nằm ngoài phạm vi cho ngày -> hàm trả về giá trị lỗi #NUM! Giá trị lỗi.

Nếu đối số là văn bản không thể chuyển đổi thành định dạng ngày hợp lệ -> hàm trả về giá trị lỗi #VALUE! Giá trị lỗi.

Ví dụ: Tính số năm làm việc của nhân viên bằng cách sử dụng hàm Days ().

Ví dụ về hàm Days trong excel
Ví dụ về hàm Days trong excel

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

– Chức năng NETWORKDAYS: Thay vì sử dụng các phương pháp thủ công như đếm tay để tính số ngày làm việc. Hàm NETWORKDAYS trong Excel sẽ hỗ trợ bạn để tính số ngày làm việc toàn thời gian giữa hai ngày một cách chính xác và nhanh chóng.

– Cú pháp: = NETWORKDAYS (START_DATE, END_DATE, [HOLIDAYS])

– Chức năng: Trả về số ngày làm việc không kể ngày nghỉ và cuối tuần bắt đầu từ start_date và kết thúc bằng end_date.

Ví dụ: Chúng tôi có bảng chấm công của nhân viên, bao gồm số ngày nghỉ như sau:

– Sử dụng hàm networkdays để tính số ngày làm việc với ngày bắt đầu là 23-01-2013, ngày kết thúc là 18-02-2013. Công thức là = NETWORKDAYS (B3, C3).

Ví dụ về hàm NETWORKDAYS
Ví dụ về hàm NETWORKDAYS

10. Hàm NOW: Hàm thực hiện nhiệm vụ trả về ngày và giờ hiện tại

– Công dụng của hàm Now: Nếu bạn muốn hiển thị ngày giờ hiện tại trên trang tính hoặc muốn tính toán dựa trên thời gian hiện tại thì hàm NOW là giải pháp dành cho bạn.

– Cú pháp: = NOW ()

Cú pháp hàm NOW không sử dụng đối số.

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

– Chức năng: Trả về giá trị ngày, giờ và thời gian hiện tại.

Ví dụ: Lúc này là 10:30 sáng ngày 14 tháng 8 năm 2014. Khi hàm = NOW () được viết cho ô B6, nó trả về giá trị chính xác cho ngày và giờ hiện tại.

Hàm Now trong excel
Hàm Now trong excel

 11. Hàm CHOOSE tìm chuỗi ký tự

Định dạng: = CHOOSE (chuỗi giá trị, chuỗi đầu tiên, chuỗi thứ hai, …)

Ví dụ: Cho các ô dữ liệu: B1 = “Toan”; B2 = “Van”; B3 = “Ngoai Ngu”; B4 = “Công nghệ thông tin”

= CHỌN (3, B1, B2, B3, B4) = Ngoại Ngữ

12. Định dạng MATCH (giá trị phát hiện, danh sách, phương pháp phát hiện)

Hàm MATCH sẽ mang lại “giá trị phát hiện” của bảng (bảng là phạm vi ô trên một hàng hoặc trên một cột). Nếu tìm thấy, trả về thứ tự của các ô tìm thấy trong bảng. Nếu bảng là cột, số hàng được trả về, nếu bảng là hàng, số cột được trả về.

Hàm MATCH có 3 cách để phát hiện 3 giá trị sau:

0: bảng không cần sắp xếp, hàm MATCH sẽ cho kết quả nếu tìm đúng giá trị, ngược lại trả về giá trị # N / A.

1: bảng phải được sắp xếp tăng dần. Nếu “giá trị phát hiện” không được tìm thấy chính xác, hàm MATCH sẽ trả về thứ tự của ô có giá trị lớn nhất trong bảng và nhỏ hơn “giá trị phát hiện”.

-1: bảng phải được sắp xếp. Nếu không tìm thấy “giá trị phát hiện”, hàm MATCH sẽ trả về thứ tự của ô có giá trị nhỏ nhất trong bảng và lớn hơn “giá trị phát hiện”.

Ví dụ: = MATCH (5, {3,7,5,8}, 0) = 3 hoặc = MATCH (5, {3,5,7,8}, 1) = 2

hoặc = MATCH (5, {8,7,5,3}, – 1) = 3

 13. Định dạng INDEX (bảng, hàng số, cột số)

Định dạng INDEX (bảng, hàng số, cột số)

Hàm INDEX trả về giá trị của một ô trong bảng được xác định bởi giao điểm của hàng và cột. Hàng đầu tiên trong bảng là hàng số 1 và cột đầu tiên trong bảng là cột số 1.

Ví dụ: Khi gõ công thức INDEX (A1: C5,2,3) thì hàm INDEX sẽ trả về giá trị với kết quả là giá trị của ô được chỉ định bởi giao điểm của hàng 2 và cột số 3 trong bảng, tức là giá trị của ô C3.

 14. Hàm VLOOKUP

– Hàm VLOOKUP có chức năng lấy thông tin từ bảng con với điều kiện tìm kiếm trong bảng chính để điền vào một cột nào đó của bảng chính nếu thỏa mãn điều kiện tìm kiếm.

Lưu ý: Sự cố với hàm vlookup thường xảy ra khi chúng tôi buộc tham chiếu cột từ một bảng phụ nhất định.

Cú pháp hàm Excel nâng cao – vlookup:

= VLOOKUP (lookup_value, table_array, row_index_num, range_lookup)

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

lookup_value: là giá trị để tham chiếu có thể là một ký tự, chuỗi, tham chiếu hoặc một số giá trị khác của bảng chính.

table_array: vùng chứa dữ liệu của bảng con (lấy diện tích bằng cách dùng chuột quét bảng con). Luôn phải ở trong địa chỉ tuyệt đối bằng cách quét và sau đó nhấn F4 để có ký hiệu $ phía trước.

row_index_num: giá trị được truy xuất ở cột nào trong bảng phụ

range_lookup: muốn tìm chính xác hoặc tương đối (0 là chính xác, 1 là tương đối) thường giá trị này là 0.

Ví dụ về hàm vlookup trong excel
Ví dụ về hàm vlookup trong excel

Ví dụ: Vui lòng nhập tên vào cột tên hàng dựa trên ký tự của cột mã hàng, là một tham chiếu từ bảng phụ.

Ví dụ tóm tắt ngắn gọn: nghĩa là bạn nhìn vào cột mã hàng trong bảng chính nó sẽ trùng với mã hàng trong cột bảng con. G = “Chicken”, V = “Duck”, C = “Dog” để điền vào cột tên hàng trong bảng chính.

Từ ví dụ, chúng ta có công thức = VLOOKUP (C6, $ B $ 11: $ C $ 14,2,0)

Bạn nhìn vào màu từ công thức trong hình và xem cách bạn lấy hoặc quét dữ liệu.

C6 (lookup_value): giá trị để tham chiếu nó chính là 1 ký tự khớp với ký tự từ bảng phụ

$ B $ 11: $ C $ 14 (table_array): vùng chứa dữ liệu bảng con quét bảng con và nhấn F4 để có địa chỉ tuyệt đối

2 (row_index_num): bằng cách lấy dữ liệu ở cột thứ 2 của bảng con là, 2. Nếu lấy ở cột thứ 3 thì 3 hoặc, 4, 5, 6 …

Lưu ý: Bài toán ít khi đưa ra ở dạng ví dụ 1 mà thường ở dạng ví dụ 2, phần sau thường kết hợp với một hàm khác như hàm trái, hàm phải, hàm giữa … để tham khảo ký tự và điền vào bảng chính.

 15. Hàm tra cứu theo hàng: HLOOKUP

Hàm HLOOKUP là một hàm Excel nâng cao, thực hiện chức năng phát hiện một giá trị trong hàng đầu tiên của bảng dữ liệu. Nó sẽ trả về giá trị ở đầu cột với giá trị được tìm thấy trên hàng mà chúng tôi đã chỉ định. Hàm HLOOPUP thường được sử dụng để điền bảng dữ liệu được lấy từ bảng dữ liệu thứ cấp.

Cú pháp: Hlookup (giá trị cần thăm dò, bảng giá trị phát hiện, số hàng được truy xuất, phạm vi tìm kiếm)

Giải thích:

Giá trị tìm kiếm: giá trị dùng để tìm kiếm, có thể là một chuỗi, một tham chiếu, một giá trị từ bảng chính. Giá trị này phải nằm trong trường tìm kiếm.

Vùng dữ liệu tìm kiếm: Vùng dữ liệu cần tìm kiếm thường nằm trong bảng phụ.

Hàng trả về giá trị tra cứu: hàng có giá trị trong bảng phụ

Tham số: muốn tìm chính xác hoặc tương đối. Tham số này nhập “0” nếu bạn muốn phát hiện chính xác, nhập “1” nếu bạn muốn phát hiện giá trị tương đối.

Tổng kết

Thành thạo các hàm trong Excel trên sẽ giúp bạn tự tin và thành thạo hơn trong các công việc hành chính, kế toán, … Chúc bạn sẽ có được cho mình kiến thức bổ ích và phục vụ thật tốt cho các công việc liên quan đến Excel!

 

Leave a Reply

Your email address will not be published. Required fields are marked *