Các hàm excel thường dùng trong văn phòng [Tổng hợp đầy đủ]
Theo dõi Maison Office trên
Đối với nhân viên văn phòng, việc sử dụng thành thạo các hàm trong Excel mang lại lợi thế quan trọng trong công việc. Excel cung cấp cho người dùng một loạt các công cụ mạnh mẽ để xử lý thông tin một cách chính xác và nhanh chóng. Bạn đã thuần thục các hàm Excel thường dùng trong văn phòng chưa?
Nội dung chính
Các hàm Excel thường dùng trong văn phòng để tính toán
Excel cho nhân viên văn phòng là một phần mềm bảng tính được sử dụng rộng rãi trong nhiều lĩnh vực, từ kinh doanh, kế toán đến giáo dục, nghiên cứu. Một trong những tính năng mạnh mẽ của Excel là khả năng tính toán dữ liệu một cách nhanh chóng và chính xác. Để thực hiện các phép tính trong Excel, chúng ta sử dụng các hàm thường dùng trong Excel văn phòng sau.
1. Hàm tính tổng SUM trong Excel
Công dụng: Hàm SUM được sử dụng để tính tổng của các số trong một số ô hoặc một dãy ô mà bạn đã chọn.
Công thức: =SUM(Number1, Number2, Number3,…)
Trong hàm SUM: Number1, Number2, Number3 đại diện cho các số hạng cần tính tổng. Kết quả của hàm SUM sẽ là tổng của các giá trị đã được chọn.
Ví dụ: Tính tổng các dãy số từ B2 đến C2, =SUM(B2:C2) cho ra kết quả làm 1460.
2. Hàm MIN/MAX
Các hàm MIN/MAX được dùng để tìm giá trị nhỏ nhất/lớn nhất trong một phạm vi dữ liệu cụ thể hoặc trên toàn bộ trang tính.
Công thức: =MIN(Number1,Number2,…)
Kết quả trả về sẽ là giá trị nhỏ nhất trong vùng dữ liệu.
Công thức: =MAX(Number1,Number2,…)
Kết quả trả về sẽ là giá trị lớn nhất trong vùng dữ liệu.
Number1 và Number2 là các giá trị mà bạn muốn so sánh.
Ví dụ: Để tìm điểm cao nhất trong lớp, bạn có thể làm theo các bước sau:
Nhập công thức =MAX(C2:E6) vào ô hiển thị kết quả. Trong đó, C2:E6 là vùng dữ liệu chứa các điểm số và giá trị cao nhất sẽ được hiển thị.
Ví dụ: Để tìm điểm thấp nhất trong lớp, hãy:
Nhập công thức =MIN(E2:E6) vào ô hiển thị kết quả để tìm giá trị nhỏ nhất trong vùng dữ liệu. Trong đó, E2:E6 là vùng dữ liệu chứa các điểm số và giá trị thấp nhất sẽ được hiển thị.
Sau đó, nhấn ENTER để hiển thị kết quả.
3. Hàm COUNT/COUNTA
Hàm COUNT được sử dụng để đếm số lượng ô trong một vùng dữ liệu có chứa giá trị số.
Công thức hàm Count: =COUNT(Value1,…)
Trong đó: Value1,…: Tham chiếu đến các ô hoặc phạm vi mà bạn muốn đếm số lượng ô có chứa số.
Ví dụ: Để tìm số lượng ô trong vùng dữ liệu có chứa giá trị số, bạn có thể sử dụng công thức =COUNT(A2:C5). Kết quả sẽ là tổng số lượng ô có chứa số.
Hàm COUNTA được sử dụng để đếm số lượng ô không trống trong một vùng dữ liệu cụ thể.
Công thức Excel văn phòng hàm COUNTA: =COUNTA(Value1,…)
Trong đó: Value1,…: Là các ô hoặc phạm vi mà bạn muốn đếm số lượng ô không trống.
Ví dụ: Để đếm số lượng ô chứa ký tự hoặc số trong bảng dưới đây, bạn có thể sử dụng công thức =COUNTA(A2:C5). Kết quả sẽ là số lượng ô có chứa ký tự hoặc số.
4. Hàm AVERAGE
Hàm AVERAGE được sử dụng để tính giá trị trung bình của một dãy số trong trang tính.
Công thức =AVERAGE(Number1, Number2, …)
Trong đó:
- Number1 (Bắt buộc): Là số đầu tiên, tham chiếu ô hoặc phạm vi mà bạn muốn tính giá trị trung bình.
- Number2, … (Tùy chọn): Là các số, tham chiếu ô hoặc phạm vi bổ sung mà bạn muốn tính giá trị trung bình. Số lượng tối đa là 255.
Ví dụ: Để tính giá trị điểm trung bình dựa trên thông tin bên dưới, bạn có thể sử dụng công thức =AVERAGE(D3:F3), và kết quả sẽ là điểm trung bình của 3 môn.
5. Hàm ODD/EVEN
Hàm ODD được sử dụng để làm tròn số thập phân lên số nguyên là số lẻ gần nhất, trong khi hàm EVEN được sử dụng để làm tròn số thập phân lên số nguyên là số chẵn gần nhất.
Công thức hàm ODD: =ODD(Number)
Trong đó: Number: Là giá trị cần làm tròn, là một tham số bắt buộc.
Ví dụ: Để làm tròn số 1.3 lên số nguyên lẻ gần nhất, bạn có thể sử dụng công thức =ODD(1.3). Kết quả sẽ là 3.
Hàm EVEN được sử dụng để làm tròn số thập phân lên số nguyên chẵn gần nhất.
Công thức hàm EVEN: =EVEN(Number)
Trong đó: Number: Là giá trị cần làm tròn, là một tham số bắt buộc.
Ví dụ: Để làm tròn số 2.99lên số nguyên chẵn gần nhất, bạn có thể sử dụng công thức =EVEN(2.99). Kết quả sẽ là 4.
Các hàm Excel có điều kiện
Các hàm Excel có điều kiện là các hàm cho phép chúng ta kiểm tra điều kiện và trả về kết quả khác nhau tùy thuộc vào kết quả của điều kiện. Các hàm này thường được sử dụng để phân loại dữ liệu, tạo báo cáo và tự động hóa các tác vụ.
6. Hàm COUNTIF
Hàm COUNTIF được sử dụng để đếm số ô trong một vùng dữ liệu được chọn thỏa mãn một điều kiện cụ thể.
Công thức hàm COUNTIF =COUNTIF(range,criteria)
Trong đó:
- range: Là vùng dữ liệu mà bạn muốn đếm.
- criteria: Là điều kiện để đếm.
Ví dụ: Giả sử bạn có một bảng thống kê các mặt hàng và số lượng tồn như trong hình dưới.
Để đếm số lượng mặt hàng có số lượng tồn trên 150, bạn có thể sử dụng công thức sau:
=COUNTIF(C2:C11,”>150″)
Kết quả trả về sẽ cho thấy tổng cộng có 8 mặt hàng có số lượng tồn trên 150 sản phẩm.
7. Hàm IF trong Excel
Hàm IF được sử dụng để kiểm tra một điều kiện và trả về kết quả dựa trên giá trị logic đúng hoặc sai.
Công thức hàm IF =IF(Logical_test;Value_if_true;Value_if_false)
Trong đó:
- Logical_test: Là điều kiện cần kiểm tra.
- Value_if_true: Là giá trị trả về nếu điều kiện là đúng.
- Value_if_false: Là giá trị trả về nếu điều kiện là sai.
Lưu ý: Nếu bạn bỏ trống Value_if_true và Value_if_false, hàm sẽ trả về giá trị 0 nếu điều kiện là đúng và giá trị FALSE nếu điều kiện là sai.
Ví dụ: Để xác định kết quả “Đỗ” hoặc “Trượt” của một học sinh dựa vào Điểm số, bạn có thể sử dụng công thức sau: =IF(Diem>5,”Đỗ”,”Trượt”)
Ở đó, B2 đại diện cho ô chứa Điểm số, và đây là điều kiện mà chúng ta sẽ kiểm tra để biết kết quả của học sinh. Nếu Điểm số lớn hơn 5, công thức sẽ trả về “Đỗ”, ngược lại nếu không thỏa điều kiện, công thức sẽ trả về kết quả “Trượt”.
8. Hàm SUMIF
Hàm SUMIF được sử dụng để tính tổng các giá trị trong một phạm vi dựa trên một tiêu chí cụ thể.
Công thức hàm SUMIF =SUMIF(range,criteria,[sum_range])
Trong đó:
- range: Là phạm vi ô mà bạn muốn đánh giá theo tiêu chí.
- criteria: Là tiêu chí để xác định các ô sẽ được tính tổng.
- sum_range: Là phạm vi thực tế của các ô mà bạn muốn tính tổng. Nếu bỏ qua đối số này, hàm SUMIF sẽ tính tổng các ô trong phạm vi đã xác định.
Ví dụ: Để tính tổng thành tiền của các hóa chất có số lượng lớn hơn 200, bạn có thể làm như sau:
Bước 1: Nhập công thức =SUMIF(D2:D10, “>200”, E2:E10) vào ô tham chiếu mà bạn muốn hiển thị kết quả (H1).
Trong đó:
- D2:D10: Là phạm vi dữ liệu chứa 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ụ (số lượng lớn hơn 200).
- E2:E10: Là phạm vi dữ liệu chứa giá trị thành tiền để tính tổng.
Bước 2: Nhấn Enter để hiển thị kết quả.
Các công thức Excel thường dùng trong văn phòng sử dụng cho văn bản
Trong văn phòng, Excel thường được sử dụng để quản lý dữ liệu, trong đó có dữ liệu văn bản. Dưới đây là một số công thức Excel thường dùng trong văn phòng sử dụng cho văn bản:
9. Hàm LEFT, RIGHT và MID
9.1 Hàm LEFT
Hàm LEFT được sử dụng để cắt chuỗi ký tự từ phía bên trái của một chuỗi văn bản mà người dùng chọn.
Công thức hàm LEFT =LEFT(text,[num_chars])
Trong đó:
- text: Là chuỗi văn bản chứa ký tự mà bạn muốn trích xuất từ phía bên trái.
- num_chars: Là số lượng ký tự mà hàm LEFT sẽ trích xuất từ phía bên trái của chuỗi.
Ví dụ: Để lọc mã hàng hóa trong bảng Excel dưới đây:
Để truy xuất 2 chữ cái đầu tiên từ cột “Mã Số” ở cột A, ta thực hiện như sau:
– Trên cột D “MÃ HÀNG HÓA”, ta sử dụng công thức =LEFT(D2, 2) và nhấn Enter.
Kết quả sẽ là 2 ký tự tính từ phía bên trái của chuỗi ký tự trong ô A2.
9.2 Hàm RIGHT
Hàm RIGHT được sử dụng để tách chuỗi ký tự từ bên phải của một dãy ký tự mà người dùng chọn.
Công thức hàm RIGHT =RIGHT(text,[num_chars])
Trong đó:
- text: Là chuỗi ký tự chứa các ký tự mà bạn muốn trích xuất từ bên phải.
- num_chars: Là số lượng ký tự mà hàm RIGHT sẽ trích xuất từ phía bên phải của chuỗi.
Ví dụ: Trong bảng dưới đây, để tìm 7 ký tự cuối của ô B2, ta sử dụng hàm RIGHT. Nhập công thức =RIGHT(B2, 7), sau đó nhấn Enter để xem kết quả như hình minh họa.
Hàm MID được sử dụng để trích xuất một số lượng ký tự cụ thể từ một chuỗi văn bản, bắt đầu từ vị trí mà bạn chỉ định và dựa vào số lượng ký tự mà bạn chỉ định.
Công thức Hàm MID =MID(text, start_num, num_chars)
Trong đó:
- text: Là chuỗi văn bản chứa các ký tự mà bạn muốn trích xuất.
- start_num: Là vị trí của ký tự đầu tiên mà bạn muốn trích xuất trong chuỗi văn bản.
- num_chars: Bắt buộc cho hàm MID. Đây là số lượng ký tự mà bạn muốn hàm MID trả về từ văn bản.
Ví dụ, trong ô A2 chứa họ và tên đầy đủ được phân tách bằng ký tự khoảng trắng. Để tách họ và tên trong trường hợp này, bạn có thể sử dụng công thức sau:
– Công thức để tách họ hàm MID:
=MID(A2, 1, SEARCH(” “, A2) – 1)
10. Hàm LEN
Hàm LEN được sử dụng để đếm số ký tự trong một chuỗi hoặc trong một ô chứa chuỗi ký tự, bao gồm cả khoảng trắng.
Công thức: =LEN(chuỗi_ký_tự) hoặc =LEN(ô_chứa_chuỗi_ký_tự)
Ví dụ: Trong ví dụ này, ta thực hiện việc đếm số ký tự trong ô A1, trong đó ô A1 chứa một chuỗi ký tự “Maison Office”.
11. Hàm CONCATENATE
Hàm CONCAT được sử dụng để nối hai hoặc nhiều chuỗi văn bản thành một chuỗi văn bản.
Công thức: =CONCAT(Logical_test;Value_if_true;Value_if_false, …)
Với các tham số sau:
- Logical_test: Điều kiện được kiểm tra.
- Value_if_true: Giá trị được trả về nếu điều kiện thỏa mãn.
- Value_if_false: Giá trị được trả về nếu điều kiện không thỏa mãn.
Lưu ý: Nếu bạn không cung cấp Value_if_true và Value_if_false, giá trị trả về sẽ là 0 nếu điều kiện thỏa mãn và FALSE nếu điều kiện không thỏa mãn.
Ví dụ:
=CONCAT(“Maison”, ” “, “Office”, ” “, “Xin”, ” “, “Cám”, ” “, “ơn.”) sẽ trả về chuỗi văn bản “Maison Office Xin Cám ơn.”
Các công thức Excel – hàm ngày tháng
Trong Excel, có một số hàm được sử dụng để xử lý dữ liệu ngày tháng. Dưới đây là các hàm Excel thông dụng trong văn phòng dùng cho ngày tháng:
12. Hàm NOW
Hàm NOW được sử 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 để 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ông thức: =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.
13. Hàm DATE
Hàm DATE được sử dụng để trả về một số đại diện cho một ngày cụ thể.
Công thức: =DATE(Năm, Tháng, Ngày)
Trong đó:
- Năm: Đại diện cho năm.
- Tháng: Đại diện cho tháng.
- Ngày: Đại diện cho ngày.
Ví dụ: =DATE(2023, 2, 11) sẽ trả về số đại diện cho ngày 11 tháng 2 năm 2023.
Công thức Excel – hàm tìm kiếm
Dưới đây là các hàm excel thường dụng trong văn phòng dùng cho tìm kiếm:
14. Hàm VLOOKUP
Hàm VLOOKUP được sử dụng để tìm kiếm dữ liệu trong một bảng hoặc phạm vi theo hàng dọc và trả về dữ liệu tương ứng theo hàng ngang.
Công thức =VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)
Trong đó:
- Lookup_value: Giá trị cần tìm kiếm, có thể là giá trị trực tiếp hoặc tham chiếu đến một ô trong bảng tính.
- Table_array: Phạm vi dữ liệu để tìm kiếm.
- Col_index_ num: Số thứ tự của cột chứa dữ liệu cần tìm trong bảng, tính từ trái sang phải.
- Range_lookup: Xác định cách tìm kiếm trong phạm vi dữ liệu, nếu bỏ qua sẽ mặc định là 1.
– Nếu Tìm_kiếm_tương_đối = 1 (TRUE): Tìm kiếm tương đối.
– Nếu Tìm_kiếm_tương_đối = 0 (FALSE): Tìm kiếm chính xác.
Ví dụ: Đánh giá và phân loại học sinh dựa trên điểm số.
Trong ô E4, bạn nhập công thức: =VLOOKUP(D4, $B$11:$C$15, 2, 1).
- Trong đó, dấu $ được sử dụng để cố định các dòng và cột của bảng 2 khi bạn sao chép công thức sang các ô khác.
- Số 2 là số thứ tự của cột dữ liệu trong bảng.
- Tham số Range_lookup được đặt là 1 (TRUE) để tìm kiếm giá trị điểm gần nhất.
Excel sẽ lấy giá trị điểm từ ô D4 và tìm kiếm trong bảng 2. Khi tìm thấy giá trị gần nhất của D4 trong bảng (trong trường hợp này là 8.5), Excel sẽ trả về kết quả tương ứng từ cột thứ 2 là “Giỏi”.
15. Hàm HLOOKUP
Hàm HLOOKUP được sử dụng để tìm kiếm một giá trị trên dòng đầu tiên của một bảng tham chiếu và trả về giá trị tương ứng trong dòng chỉ định.
Công thức: =HLOOKUP(Lookup_value,Table_ array,Row_index_Num,Range_lookup)
Trong đó:
- Lookup_value: Giá trị được so sánh với dòng đầu tiên của bảng tham chiếu để tìm giá trị cần lấy.
- Table_ array: Địa chỉ tuyệt đối của bảng tham chiếu, không bao gồm dòng tiêu đề.
- Row_index_Num: Số thứ tự của dòng chứa giá trị cần lấy trong bảng tham chiếu (đếm từ trên xuống và bắt đầu từ số 1).
- Range_lookup: Cách tìm kiếm trên bảng tham chiếu.
“+ 0”: Bảng tham chiếu chưa được sắp xếp.
“+ 1”: Bảng tham chiếu đã được sắp xếp theo thứ tự tăng dần (mặc định).
Ví dụ: Ta cần xếp loại học sinh trong Bảng 1 (B3:D8) dựa trên các điều kiện trong Bảng 2 (B11:F12).
Ở ô D4, chúng ta sử dụng hàm HLOOKUP:
=HLOOKUP(C4, $B$11:$F$12, 2, 1)
Hàm HLOOKUP sẽ tìm kiếm giá trị điểm số trong ô C4 trong Bảng 2 từ trái qua phải. Khi tìm thấy giá trị gần nhất, nó sẽ trả về kết quả xếp loại tương ứng nằm ở hàng 2.
16. Hàm INDEX
Hàm INDEX được sử dụng để trả về một giá trị hoặc tham chiếu đến một giá trị trong một bảng hoặc một phạm vi cụ thể.
Công thức: =INDEX(array, row_num, column_num)
Trong đó:
- array: Phạm vi ô hoặc một mảng số học. Nếu mảng chỉ chứa một hàng hoặc một cột, thì số_dòng hoặc số_cột 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 và bạn chỉ khai báo một trong hai đối số số_dòng hoặc số_cột, hàm sẽ trả về một mảng chứa toàn bộ hàng hoặc cột trong mảng.
- row_num: Thứ tự của hàng trong mảng mà giá trị trả về nằm. Đây là đối số bắt buộc, trừ khi bạn khai báo số_cột. Nếu bỏ qua số_dòng, bạn cần khai báo số_cột.
- column_num: Thứ tự của cột trong mảng mà giá trị trả về nằm. Nếu bỏ qua số_cột, bạn cần khai báo số_dòng.
17. Hàm MATCH
Hàm MATCH được sử dụng để tìm kiếm và xác định vị trí tương đối của một giá trị trong một mảng hoặc một ô cụ thể.
Công thức Hàm MATCH: =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 ô mà bạn muốn xác định vị trí của Lookup_value. Khu vực dữ liệu bạn chọn chỉ có thể có duy nhất một hàng hoặc một cột.
- Match_type: Xác định kiểu khớp là -1, 0 và 1.
Bài viết này của Maison Office đã tổng hợp các hàm Excel thường dùng trong văn phòng, giúp bạn có thể tự học và làm quen với những hàm cơ bản nhất. Tự học Excel cơ bản sẽ giúp bạn tích lũy kiến thức và hỗ trợ hiệu quả trong công việc. Hy vọng rằng các hàm Excel cơ bản được giới thiệu ở đây sẽ hữu ích cho bạn. Chúc bạn thành công trong công việc của mình!
TÌM HIỂU THÊM:
Nhà biên tập và sản xuất nội dung tại Maison Office.
Có hơn 3 năm kinh nghiệm sản xuất nội dung trong lĩnh vực bất động sản và nội thất văn phòng. Với hiểu biết và kiến thức của mình, tôi luôn cố gắng tạo ra những bài viết có tính chuyên môn cao, mang đến giá trị thông tin cho khách hàng.
