Các kế toán cần nằm rõ một số hàm cơ bản trong Excel để phục vụ công việc hàng ngày của mình. Bài viết của Ketoan.vn sẽ tổng hợp một số hàm Excel để tính toán, thống kê… và rất có ích với các bạn thường xuyên phải làm việc trên bảng tính Excel, đặc biệt là trong lĩnh vực kế toán, hành chính nhân sự. Hy vọng bạn sẽ biết cách sử dụng những hàm cơ bản trong Excel này để tăng hiệu quả làm việc nhé.
Dưới đây chúng tôi đã tổng hợp những hàm cơ bản trong Excel mà bạn nên biết để đem lại hiệu quả công việc cao.
1. HÀM ĐẾM VÀ TÍNH TỔNG
Hàm COUNT
Bạn muốn biết có bao nhiêu ô dữ liệu kiểu số trong một bảng tính phức tạp? Thay vì phải ngồi đếm thủ công và mất nhiều thời gian thì bạn có thể sử dụng hàm =COUNT. Ví dụ bạn cần đếm dữ liệu từ ô A1 đến ô H1, bạn hãy áp dụng công thức: =COUNT(A1:H1).
Khi đó kết quả trả về sẽ là 8 ô dữ liệu kiểu chữ số.
Hàm COUNTIF với 1 điều kiện
Để đếm các ô dựa trên một điều kiện cụ thể bạn hãy sử dụng hàm COUNTIF. Ví dụ dưới đây sẽ đếm các ô dữ liệu với điều kiện lớn hơn 50. Bạn hãy quan sát công thức trong hình dưới đây:
Hàm COUNTIFS với nhiều điều kiện
Hàm này cũng tương tự như hàm COUNTIF. Tuy nhiên để đếm các ô dựa trên nhiều điều kiện thì bạn hãy sử dụng hàm COUNTIFS như sau.
Hàm SUM
Đây có thể nói là hàm cơ bản trong Excel đối với những người mới làm việc với bảng tính dữ liệu. Hàm này sẽ giúp bạn tính tổng các con số trong bảng dữ liệu một cách nhanh chóng. Giả sử, bạn muốn cộng các con số từ ô A2 đến ô H2 bạn hãy nhập công thức như hình dưới đây. Kết quả trả về được hiển thị tại ô B4.
Hàm SUMIF với 1 điều kiện
Để tính tổng các ô dựa trên một điều kiện bạn hãy sử dụng hàm SUMIF. Ví dụ dưới đây tính tổng các ô dữ liệu lớn hơn 40. Quan sát công thức và kết quả phía dưới để hiểu rõ hơn.
Hàm SUMIFS với nhiều điều kiện
Để tính tổng các ô dựa trên nhiều điều kiện bạn hãy sử dụng hàm SUMIFS. Trong ví dụ này bạn sẽ thực hiện tính tổng các ô dữ liệu với 2 điều kiện cho trước. Kết quả trả về được hiển thị tại ô B4.
Hàm AVERAGE
Hàm =AVERAGE sẽ giúp người dùng đưa ra con số có giá trị trung bình trong những số được chọn. Ví dụ bạn muốn tính giá trị trung bình từ ô A2đến ô H2, chỉ cần gõ =AVERAGE(A2:H2) rồi nhấn Enter. Ngay sau đó trên màn hình sẽ xuất hiện kết quả bạn cần tìm tại ô B4.
Ngoài ra, bạn cũng có thể sử dụng con trỏ chuột kéo thả và đánh dấu chọn vùng dữ liệu. Hoặc có thể đồng thời nhấn phím Ctrl rồi nhấn chuột vào từng ô riêng rẽ trong trường hợp các ô không kế nhau.
Tương tự như SUMIF, COUNTIF bạn có thể sử dụng AVERAGEIF và AVERAGEIFS để tính giá trị trung bình. Hàm AVERAGEIF sẽ tính trung bình của các ô dựa trên một điều kiện. Hàm AVERAGEIFS sẽ là nhiều điều kiện.
Hàm COUNTBLANK
Hàm này sẽ giúp bạn đọc khi đếm các ô trống trong vùng dữ liệu đã chọn. Bạn sử dụng cú pháp =COUNTBLANK(phạm vi bảng tính cần đếm). Với ví dụ trong bảng sau bạn sẽ hiểu rõ hơn về hàm này.
Hàm COUNTA
Hàm COUNTA có chức năng dùng để đếm số ô có chứa nội dung bất kỳ bao gồm chữ số, chữ cái hay biểu tượng, hay nói cách khác nó dùng để đếm các ô không trống. Cú pháp =COUNTA(phạm vi bảng tính cần đếm).
Hàm SUMPRODUCT
Để tính tổng sản phẩm của các số tương ứng trong một hoặc nhiều dãy, bạn hãy sử dụng hàm SUMPRODUCT của Excel. Thông thường hàm này sẽ tính tổng các sản phẩm dựa trên số lượng và đơn giá.
Ví dụ dưới đây dùng hàm SUMPRODUCT để tính tổng số tiền các sản phẩm đã nhập kho..
Cụ thể ở trên, hàm SUMPRODUCT đã thực hiện phép tính: (5 * 4000) + (1 * 5000) + (4 * 6000) + (2 * 15000) + (1* 10000) = 89000.
Bạn cần lưu ý rằng phạm vi tính tổng phải có cùng kích thước, nếu không Excel sẽ hiển thị lỗi #VALUE!
Trong trường hợp giá trị trong ô không phải dạng số thì SUMPRODUCT sẽ mặc định giá trị của chúng là 0. Tham khảo hình ảnh dưới đây để biết rõ hơn.
Hàm MIN, MAX
Nếu bạn muốn tìm một con số có giá trị nhỏ nhất trong một vùng dữ liệu, hàm =MIN có thể làm việc đó. Chỉ cần áp dụng theo công thức =MIN(A2:H2), Excel sẽ trả về cho bạn số nhỏ nhất nằm trong phạm vi đó. Kết quả trả về sẽ hiển thị tại ô B4.
Ngược với hàm =MIN thì hàm =MAX sẽ trả về con số có giá trị lớn nhất trong phạm vi cần tìm. Cú pháp của hàm này cũng tương tự như hàm =MIN, gồm địa chỉ ô đầu tiên cho đến ô cuối cùng.
2. HÀM LOGIC
Hàm IF
Hàm IF sẽ giúp bạn kiểm tra xem điều kiện có được đáp ứng không. Nếu đúng nó sẽ trả về giá trị đúng, nếu sai sẽ trả về giá trị sai. Bạn hãy nhập theo công thức =IF(điều kiện,”giá trị đúng”,”giá trị sai”). Để áp dụng cho nhiều điều kiện bạn sẽ phải dùng đến hàm IF lồng nhau.
Trong ví dụ dưới đây, chúng ta sẽ kiểm tra xem ô A1 có thỏa mãn điều kiện nhỏ hơn 10 không. Nếu thỏa mãn thì trong ô C2 trả về giá trị Correct, nếu không thỏa mãn sẽ trả về giá trị Incorrect. Tại ô C2, bạn nhập vào hàm theo cú pháp: =IF(A1<10,”Correct”,”Incorrect”).
Hàm IF trả về kết quả là Incorrect vì giá trị trong ô A1 lớn hơn 10.
Hàm AND
Hàm AND sẽ trả về giá trị đúng nếu tất cả các điều kiện được thỏa mãn và trả về giá trị sai nếu có bất kỳ điều kiện nào sai. Bạn hãy sử dụng công thức: =IF(AND(điều kiện),”giá trị đúng”,”giá trị sai).
Vẫn ví dụ trên, giờ ta kiểm tra đồng thời 2 điều kiện là A1 lớn hơn 10 và B1 lớn hơn 5. Nếu cả 2 cùng thỏa mãn kết quả sẽ trả về Correct, ngược lại trả về Incorrect. Tại ô C2 bạn nhập =IF(AND(A1>10,B1>5),”Correct”,”Incorrect”)
Hàm AND trả về giá trị đúng vì cả 2 ô đều trả về kết quả là đúng.
Hàm OR
Ngược lại với AND, hàm OR trả về giá trị đúng nếu có bất kỳ điều kiện nào được thỏa mãn. Và ngược lại, trả về giá trị sai nếu tất cả các điều kiện đều không được đáp ứng.
Thử lại ví dụ trên với hàm OR. Tại ô C2 bạn nhập =IF(OR(A1>10,B1<5),”Correct”,”Incorrect”)
Hàm OR trả về giá trị đúng vì ô A1 lớn hơn 10, vì thế kết quả là hàm IF trả về giá trị Correct.
3. HÀM NGÀY THÁNG
Hàm YEAR, MONTH, DAY
Để điền ngày vào Excel, bạn sử dụng ký tự “/” hoặc “-“. Để điền thời gian, bạn sử dụng ký tự “:”. Ngoài ra bạn cũng có thể điền ngày và thời gian trong cùng một cột. Kết quả trả về như hình dưới đây.
Để lấy năm trong ngày tháng năm bạn sử dụng hàm YEAR. Tương tự như vậy, để lấy tháng ta dùng hàm MONTH và lấy ngày dùng hàm DATE. Công thức như sau: =YEAR(cột chứa năm cần lấy).
Hàm DATE
Để thêm số ngày vào ngày tháng chỉ cần sử dụng công thức đơn giản là lấy ô chứa ngày tháng + với số ngày bạn muốn thêm. Ví dụ dưới đây sẽ minh họa rõ hơn cho bạn đọc.
Để thêm số năm, tháng và ngày vào ngày tháng nào đó, bạn cungcx thực hiện tương tự. Tuy nhiên bạn sẽ phải sử dụng đến hàm DATE. Công thức =DATE(YEAR(ô chứa ngày tháng gốc)+số năm cần thêm,MONTH(ô chứa ngày tháng gốc)+số tháng cần thêm,DAY(ô chứa ngày tháng gốc)+số ngày cần thêm). Bạn hãy quan sát hình ảnh dưới đây:
Hàm NOW
Trong trường hợp bạn muốn điền thời gian hiện tại vào một ô nào đó trong bảng tính Excel, hãy sử dụng hàm =NOW. Trước hết, tại một ô bất kỳ rong bảng tính bạn chỉ cần gõ cú pháp =NOW(). Điểm tiện lợi của hàm này là không yêu cầu bất kỳ tham số nào bên trong dấu ngoặc. Kết quả trả về chính là thời gian hiện tại trên hệ thống máy tính.
Hàm HOUR, MINUTE, SECOND
Để trả về thời gian là giờ, phút, giây, bạn sẽ sử dụng hàm HOUR, MINUTE, SECOND tương ứng. Công thức áp dụng như sau: =HOUR(ô chứa số giờ cần lấy).
Kết quả trả về được hiển thị trên màn hình. Bạn có thể thực hiện tương tự đối với hàm MINUTE và SECOND.
Hàm TIME
Hàm TIME được sử dụng để thêm số giờ, phút, giây vào thời gian ban đầu, cũng tương tự hàm DATE. Công thức áp dụng=TIME(HOUR(ô thời gian ban đầu)+số giờ cần thêm,MINUTE(ô thời gian ban đầu)+số phút cần thêm,SECOND(ô thời gian ban đầu)+số giây cần thêm).
Hàm DAYS
Đây là một hàm rất hữu ích để tính số ngày giữa hai mốc thời gian nên bạn không cần phải để ý là có bao nhiêu ngày trong mỗi tháng của dải ô cần tính. Ví dụ, ô A2 chứa dữ liệu ngày bắt đầu là 16/05/2019 và ô B2 chứa ngày kết thúc là 10/08/2019. Bạn hãy nhập theo cú pháp =DAYS(A2,B2)
Hàm WEEKDAY
Hàm WEEKDAY trả về giá trị từ 1 (Chủ nhật) đến 7 (thứ 7), đại diện cho ngày trong tuần của ngày tháng năm. Bạn hãy áp dụng ông thức =WEEKDAY(ô chứ ngày tháng năm) vào ví dụ dưới đây:
Hàm TEXT
Bạn sử dụng hàm TEXT để hiển thị ngày trong tuần dưới dạng chữ. Công thức như sau: =TEXT(ô chứa ngày tháng,”dddd”). Khi đó kết quả trả về sẽ hiển thị tại ô B2 như hình dưới đây.
Hàm NETWORKDAYS
Hàm =NETWORKDAYS() dùng để tính số ngày làm việc trong một khoảng thời gian cụ thể. Tương tự như hàm =DAYS, bạn hãy sử dụng công thức: =NETWORKDAYS(A2,B2).
Hàm này giúp người dùng tính toán số ngày công một cách nhanh chóng và rất tiện lợi.
Hàm EOMONTH
Để lấy ngày cuối cùng của ngày tháng hiện tại bạn có thể sử dụng hàm EOMONTH. Áp dụng công thức sau =EOMONTH(ô chứa ngày tháng hiện tại,0). Bạn có thể sử dụng một số khác thay cho không để lấy ngày cuối cùng của các tháng sau đó. Ví dụ bạn thay bằng 1 thì sẽ lấy ngày cuối cùng của 1 tháng sau đó. Ví dụ dưới đây sẽ minh họa rõ hơn cho bạn đọc.
4. HÀM LÀM VIỆC VỚI CHUỖI VĂN BẢN
Nối chuỗi văn bản
Để nối chuỗi văn bản bạn sử dụng ký tự &, muốn chèn thêm dấu cách thì sử dụng ” “. Chỉ với thao tác đơn giản trong hình dưới đây bạn đã có thể nối các chuỗi lại với nhau.
Hàm LEFT
Để lấy những ký tự bên trái của một chuỗi, chúng ta sử dụng hàm LEFT. Công thức =LEFT(ô chứa chuỗi cần lấy, số ký tự muốn lấy). Bạn thực hiện như hình dưới đây là xong.
Hàm RIGHT
Dùng hàm RIGHT khi cần lấy những ký tự từ phía bên phải của chuỗi. Công thức =RIGHT(ô chứa chuỗi cần lấy, số ký tự muốn lấy). Cách thực hiện tương tự bên trên và chỉ thay tên hàm thành RIGHT.
Hàm MID
Dùng để lấy các ký tự ở giữa tính từ trái qua phải. Bạn áp dụng công thức =MID(ô chứ chuỗi cần lấy, vị trí lấy, số ký tự cần lấy). Trong ví dụ này ta sẽ lấy 2 ký tự, bắt đầu từ ký tự thứ 2.
Hàm LEN
Nếu bạn muốn đếm tổng số ký tự hay còn gọi là chiều dài chuỗi ký tự trong một ô thì hãy dùng hàm LEN. Hàm này cho phép bạn tính tổng bao gồm cả chữ cái, chữ số và khoảng trắng. Công thứcáp dụng như sau: =LEN(ô chứa chuỗi cần đếm).
Ví dụ, để biết được có bao nhiêu ký tự trong ô A1, chỉ cần di chuyển đến một ô khác bất kỳ và gõ vào cú pháp =LEN(A1). Ngay lập tức, Excel sẽ trả về một con số giá trị tổng các ký tự trong ô A1.
Có thể nói hàm LEFT, RIGHT, MID, LEN là những hàm cơ bản trong Excel và được sử dụng thường xuyên.
Hàm FIND
Dùng để tìm vị trí của một chuỗi con trong chuỗi. Công thức như sau: =FIND(“chuỗi con”, ô chứa chuỗi). Trong ví dụ này ta tìm vị trí của chuỗi con “in” trong chuỗi “minh anh”, và nó ở vị trí thứ 2.
Hàm SUBSTITUTE
Để thay thế văn bản trong chuỗi bằng một văn bản khác bạn sẽ sử dụng hàm SUBSTITUTE. Áp dụng công thức: =SUBSTITUTE(ô chứa chuỗi ban đầu,”văn bản ban đầu”,”văn bản thay thế”).
Hàm LOWER, UPPER, PROPER
Các hàm cơ bản này trong Excel được sử dụng thường xuyên và đem lại tiện lợi hơn cho người dùng.
Bạn sử dụng hàm LOWER để chuyển đổi tất cả các chữ in hoa trong chuỗi thành chữ thường. Công thức như sau: =LOWER(ô chứa chữ cần chuyển đổi).
Ngược lại, để chuyển tất cả các chữ thường trong chuỗi ký tự thành chữ in hoa, bạn sử dụng hàm UPPER. Công thức như sau: =UPPER(ô chứa chuỗi cần chuyển đổi).
Nếu muốn viết hoa các chữ đầu từ bạn sử dụng hàm PROPER. Áp dụng công thức =PROPER(ô chứa chuỗi cần viết hoa chữ cái đầu từ).
Hàm EXACT
Khi cần so sánh các cột xem giá trị của chúng có trùng nhau không bạn sử dụng EXACT. Công thức áp dụng:=EXACT(ô cần so sánh).
Thao tác này giúp bạn kiểm tra giá trị giữa các cột dữ liệu một cách nhanh chóng.
Hàm CONCATENATE
Hàm này sẽ kết hợp nội dung của hai hay nhiều ô vào trong một ô duy nhất. Chẳng hạn, khi bạn muốn nhập các dữ liệu Giờ, Ngày, Tháng, Năm vào cùng 1 ô thì hãy nhập: =CONCATENATE(A1,B1,C1,D1). Kết quả trả về sẽ hiển thị như hình bên dưới.
5. HÀM TRA CỨU VÀ THAM CHIẾU
Hàm VLOOKUP
Hàm này dùng để tìm kiếm các giá trị trong cột ngoài cùng bên trái của bảng và trả về giá trị trong cùng một hàng từ cột khác mà bạn chỉ định. Cú pháp như sau: =VLOOKUP(ô chứa giá trị tìm kiếm, vùng bảng chứa giá trị tìm kiếm và trả về,cột chứa giá trị trả về), có thể thêm đối số thứ 4 như ví dụ bên dưới.
Hàm VLOOKUP tìm mã SP trong cột ngoài cùng bên trái của dải $D$3:$E$6 và trả về giá trị trong cùng một hàng từ cột thứ 2. Đối số thứ 4 đặt là FALSE để trả lại kết quả chính xác hoặc lỗi #N/A nếu không tìm thấy.
Do vùng tham chiếu giữ nguyên nên phải dùng $ trước các ký hiệu để tạo tham chiếu tuyệt đối. Khi áp dụng công thức xong bạn sẽ tìm thấy tên sản phẩm là máy tính. Sau đó bạn chỉ cần kéo hàm VLOOKUP xuống dưới để áp dụng cho các hàng bên dưới.
Hàm MATCH
Hàm MATCH trả về vị trí của giá trị trong một dải nhất định. Bạn sẽ hiểu hơn khi tham khảo ví dụ dưới đây của chúng tôi.
Hàm INDEX
Hàm INDEX dưới đây trả về một giá trị cụ thể trong một phạm vi hai chiều. Kết quả trả về cho ví dụ này được hiển thị tại ô C2.
Hàm CHOOSE
Hàm CHOOSE trả về giá trị từ danh sách bạn chỉ định, dựa trên vị trí được chọn sẵn. Ở ví dụ này, hàm CHOOSE trả về giá trị là hạ ở vị trí thứ 2.
Bài viết trên đây Ketoan.vn đã giới thiệu cho bạn đọc những hàm cơ bản nhất trong Excel. Hy vọng những hàm này sẽ giúp ích cho bạn khi làm việc với bảng tính Excel. Chúc bạn đọc thực hiện thành công.
Xem thêm:
3 Cách khắc phục tình trạng file Excel bị nặng, chậm, mở lâu
Cách chuyển đổi số thập phân sang thời gian trong Excel
Hướng dẫn một số cách tính tổng lũy kế trong Excel
