Sử dụng hàm trong excel là một trong những từ khóa được gg search nhiều nhất về chủ đề sử dụng hàm trong excel. Trong bài viết này, giaiphapvieclam.vn sẽ viết bài viết hướng dẫn sử dụng hàm trong excel mới nhất 2020.

Sử Dụng Hàm Trong Excel

Hướng dẫn sử dụng hàm trong excel mới nhất 2020

1. HÀM ĐẾM VÀ TÍNH TỔNG

Hàm đếm COUNT

Bạn có một bảng tính với cơ sở dữ liệu khổng lồ và cần biết có bao nhiêu ô trong một vùng nào đó hay trong all bảng tính chỉ chứa chữ số, k chứa chữ cái. Thay vì phải ngồi đếm một cách thủ công thì bạn đủ nội lực dùng hàm =COUNT. ví dụ, bạn cần đếm từ ô B1 đến B10, hãy gõ =COUNT(B10:B10).

Hàm đếm COUNTIF với điều kiện cụ thể

Để đếm các ô dựa trên một điều kiện cụ thể (ví dụ, lớn hơn 9), hãy dùng hàm COUNTIF sau đây.

Hàm hàm đếm COUNTIFS với nhiều điều kiện

Để đếm các ô dựa trên nhiều điều kiện (ví dụ, green và lớn hơn 9), hãy dùng hàm COUNTIFS sau.

Hàm tính tổng SUM

Đây đủ sức nói là hàm trước nhất mà bất kỳ user nào mới học Excel cũng cần phải biết đến. Giả sử, bạn muốn cộng chiếc sim trong ô A2 và B2 lại với nhau, sau đó hiển thị kết quả trong ô B3. Để thực hiện, bạn chỉ cần di chuyển đến ô B3 và gõ cụm từ “=SUM” vào rồi lựa chọn hàm =SUM vừa xuất hiện trong danh sách nổi lên.

tiếp theo, hãy bấm phím Ctrl cùng với thao tác nhấn chuột chọn vào ô A2 và B2 rồi cuối cùng là click phím Enter. kết quả tổng của hai con số trong hai ô A2 và B2 mà bạn vừa lựa chọn sẽ xuất hiện tức thì trong ô B3. Bạn có thể sử dụng hàm SUM để tính tổng của hai hay nhiều ô, chỉ với thao tác lựa chọn thêm những ô quan trọng vào trong nội dung của hàm.

Hàm tính tổng SUMIF có điều kiện

Để tính tổng các ô dựa trên một điều kiện (ví dụ, to hơn 9), hãy sử dụng hàm SUMIF sau (hai đối số).

Để tính tổng các ô dựa trên một tiêu chí (ví dụ, green), hãy dùng hàm SUMIF với 3 đối số (đối số cuối cùng là phạm vi cần tính tổng).

Hàm tính tổng 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 (ví dụ, blue và green), hãy sử dụng hàm SUMIFS sau (đối số trước nhất là phạm vi bảng tính cần tính tổng).

Hàm tính trị giá trung bình AVERAGE

Hàm =AVERAGE sử dụng đúng chức năng như tên gọi của nó là mang ra số lượng có giá trị trung bình trong những số được lựa chọn. gợi ý bạn muốn tính giá trị trung bình từ ô A10 đến ô J10, chỉ cần gõ =AVERAGE(A10:J10) rồi bấm Enter.

hiệu quả mang ra trong ô K10 là con số có giá trị trung bình giữa các ô từ A10 đến J10. ngoài ra, bạn cũng đủ nội lực sử dụng con trỏ chuột dẫn thả và đánh dấu chọn vùng dữ liệu hoặc có thể song song click phím Ctrl rồi bấm chuột vào từng ô riêng rẽ trong trường hợp nếu các ô k nằm kế cận nhau.

Tương tự giống như SUMIF, COUNTIF bạn đủ nội lực dùng AVERAGEIF và AVERAGEIFS để tính trị giá trung bình của các ô dựa trên một hoặc nhiều tiêu chí.

Hàm đếm ô trống COUNTBLANK

Bạn sử dụng hàm này khi cần đếm các ô trống. Cú pháp =COUNTBLANK(phạm vi bảng tính cần đếm).

Hàm đếm ô k trống COUNTA

Hàm COUNTA có chức năng dùng để đếm số ô có chứa nội dung bất kỳ gồm có chữ số, chữ cái hay biểu tượng, hay nói mẹo khác nó sử dụng để đếm các ô k trống. Cú pháp =COUNTA(phạm vi bảng tính cần đếm).

Hàm tính tổng mức giá món hàng SUMPRODUCT

Để tính tổng hàng hóa của các số tương ứng (kiểu tính tổng tiền tài các món hàng dựa trên số lượng và giá tương ứng của từng sản phẩm) trong một hoặc nhiều dãy, bạn hãy sử dụng hàm SUMPRODUCT mạnh mẽ của Excel.

ví dụ dưới đây sử dụng hàm SUMPRODUCT để tính tổng số tài nguyên vừa mới tiêu.

Cụ thể ở trên, hàm SUMPRODUCT đang thực hiện phép tính: (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500.

Phạm vi tính tổng cần có cùng click thước, nếu không Excel sẽ hiển thị lỗi #VALUE!

Nếu trị giá trong ô không phải dạng số thì SUMPRODUCT sẽ mặc thẩm định giá trị của chúng là 0.

Hàm MIN, MAX

Nếu bạn mong muốn tìm một số lượng có giá trị nhỏ nhất trong một vùng dữ liệu, hàm =MIN có thể sử dụng việc đó. Chỉ cần gõ vào cụm từ =MIN(D3:J13), Excel sẽ trả về cho bạn số nhỏ nhất nằm trong phạm vi đó.

Ngược với hàm =MIN vừa đề cập, hàm =MAX sẽ trả về số lượng 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ỉ ô trước tiên cho đến ô cuối cùng.

2. HÀM LOGIC

Hàm IF

Hàm IF giúp bạn kiểm tra nhìn thấy điều kiện có được cung cấp không, nếu đúng nó sẽ trả về trị giá đúng, nếu sai sẽ trả về giá trị sai. mẹo =IF(điều kiện,”giá trị đúng”,”giá trị sai”). Để vận 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 nhìn thấy ô A có thỏa mãn điều kiện lớn hơn 10 k, nếu thảo mãn thì trả về trị giá Correct trong ô C1, nếu không thỏa mãn sẽ trả về giá trị Incorrect. Tại ô C1, bạn nhập vào hàm =IF(A1>10,”Correct”,”Incorrect”).

Hàm IF trả về Correct vì trị giá trong ô A1 lớn hơn 10.

Hàm AND

Hàm AND sẽ trả về giá trị đúng nếu all các điều kiện được thỏa mãn và trả về trị giá sai nếu có bất kỳ điều kiện nào sai. mẹo =IF(AND(điều kiện),”giá trị đúng”,”giá trị sai)

luôn luôn ví dụ trên, giờ ta kết hợp rà soát 2 điều kiện là A1 to hơn 10 và B1 to hơn 5, nếu cả 2 thỏa mãn sẽ trả về Correct, trái lại trả về Incorrect. Tại ô D1 bạn nhập =IF(AND(A1>10,B1>5),”Correct”,”Incorrect”)

Hàm AND trả về trị giá sai vì ô B1 nhỏ hơn 5, cho nên hàm IF trả về trị giá sai tương ứng ở đây là Incorrect.

Hàm OR

ngược lại với AND, OR trả về trị giá đúng nếu có bất kỳ điều kiện nào được thỏa mãn và trả về giá trị sai nếu tất cả các điều kiện đều không được cung cấp.

Thử lại điều kiện trên với hàm OR. Tại ô E1 bạn nhập =IF(OR(A1>10,B1>5),”Correct”,”Incorrect”)

Hàm OR trả về trị giá đúng vì ô A1 to hơn 10, cho nên hiệu quả là hàm IF trả về trị giá Correct.

Ghi chú chung: Hàm AND và OR có thể tra cứu đến 255 điều kiện.

Hàm IF lồng nhau

giống như vừa mới nói bên trên, khi bạn có nhiều hơn một điều kiện cần check thì đó là lúc sử dụng đến hàm IF lồng nhau. giá trị sai sẽ được thay thế bằng một hàm IF khác để thực hiện thêm một lần kiểm tra. (Nếu dùng Excel 2016, bạn chỉ cần sử dụng IFS là được).

Hãy nhìn thấy các ví dụ dưới đây:

Bạn nhập vào phương thức =IF(A1=1,”Bad”,IF(A1=2,”Good”,IF(A1=3,”Excellent”,”No Valid Score”))) để test giá trị của ô A1, nếu bằng 1 trả về Bad, nếu bằng 2 trả về Good, nếu bằng 3 trả về Excellent, nếu là một trị giá không giống sẽ trả về No Valid Score.

Bạn nhập vào cách thức =IF(A1<=10,350,IF(A1<=20,700,IF(A1<=30,1400,2000))) để kiểm tra trị giá ô A1, nếu nhỏ hơn hoặc bằng 10 thì trả về giá trị 350, 10 < A1 <= 20 thì trả về 700, 20 < A1 <= 30 thì trả về 1400, nếu lớn hơn 30 thì trả về 2000.

3. HÀM NGÀY THÁNG

Hàm YEAR, MONTH, DAY

Để điền ngày vào Excel, bạn dùng “/” hoặc “-“. Để điền thời gian, sử dụng “:” (dấu hai chấm). Bạn cũng có thể điền ngày và thời gian trong cùng một cột.

Để get năm trong ngày tháng năm bạn dùng hàm YEAR. Tương tự để lấy tháng sử dụng hàm MONTH và lấy ngày dùng hàm DATE. phương pháp =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 dùng phương thức không khó khăn là quét ô chứa ngày tháng + với số ngày bạn mong muốn thêm, như ví dụ dưới đây:

Để thêm số năm, tháng và ngày vào ngày tháng nào đó, bạn sẽ phải sử dụng đến hàm DATE. phươ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).

Chú ý: Hàm DATE chỉ có 3 đối số là năm, tháng và ngày. Excel biết rằng 6+2=8= Tháng 8, tháng 8 chỉ có 31 ngày, nên nó sẽ tự động tính sang tháng tiếp theo (23 tháng 8 + 9 ngày thành 1 tháng 9).

Hàm NOW hiển thị thời gian nền móng

Nếu muốn điền thời gian bây giờ vào một ô nào đó trong bảng tính Excel, hãy sử dụng hàm =NOW. trước hết, hãy di chuyển con trỏ chuột đến một ô bất kỳ rồi chỉ cần gõ cú pháp =NOW(). Điểm tiện dụng của hàm này là k yêu cầu bất kỳ tham số nào bên trong dấu ngoặc. hiệu quả trả về chính là thời gian hiện nay trên hệ thống máy tính.

Hàm HOUR, MINUTE, SECOND

Để trả về giờ, phút, giây trong thời gian, bạn sẽ sử dụng HOUR, MINUTE, SECOND tương ứng. phương pháp =HOUR(ô chứa số giờ cần lấy).

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, giống hàm DATE bên trên vậy. phương thức =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 tính số ngày giữa 2 mốc thời gian DAYS

Đây là một công thức hữu dụng để tính số ngày giữa hai mốc thời gian nên bạn k cần phải quan tâm là có bao nhiêu ngày trong mỗi tháng của dải ô cần tính. ví dụ, ô E4 chứa dữ liệu ngày khởi đầu là 30/04/2015 và ô F4 chứa ngày kết thúc là 02/09/2015. Bạn hãy nhập là =DAYS(F4,E4)

Hàm DATEIF

Để quét số ngày, tháng, năm của 2 mốc thời gian trong Exel bạn sẽ dùng hàm DATEIF. bí quyết =DATEIF(mốc thời gian thứ nhất,mốc thời gian thứ hai,”d”). Đối số thứ 3 là cái bạn mong muốn lấy, “d” là số ngày, mong muốn get số năm bạn thay bằng “y”, quét số tháng thay bằng “m”.

Thay đối số thứ 3 bằng “yd” để bỏ qua năm, chỉ tính số ngày giữa các tháng của khoảng thời gian đó, “md” sẽ bỏ qua tháng, chỉ tính số ngày, “ym” sẽ bỏ qua năm, chỉ đếm số tháng chênh lệch.

note quan trọng: Hàm này có thể trả về hiệu quả sai nếu ngày/tháng của ngày tháng thứ 2 (nằm ở đối số 2) thấp hơn ngày/tháng ở ngày tháng thứ nhất (đối số 1). xem ví dụ dưới đây:

Khi đó bạn nên dùng phương thức như trong ảnh để trả về hiệu quả đúng:

Hàm WEEKDAY

Hàm WEEKDAY trả về trị giá 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. ví dụ, 12/18/2017 rơi vào thứ Hai. công thức =WEEKDAY(ô chứ ngày tháng năm).

Hàm TEXT

sử dụng hàm TEXT để hiển thị ngày trong tuần dưới dạng chữ. phương pháp =TEXT(ô chứa ngày tháng,”dddd”).

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

Hàm =NETWORKDAYS() dùng để tính số ngày làm việc (nghĩa là một tuần sử dụng việc gồm 5 ngày) trong một khung thời gian cụ thể. Tương tự hàm =DAYS, hãy sử dụng công thức: =NETWORKDAYS(E3,F3).

Nếu bạn phân phối danh sách ngày nghỉ lễ (như trong vùng E1:E2 của gợi ý dưới) NETWORKDAYS sẽ trả về số ngày sử dụng việc (đã trừ đi ngày nghỉ cuối tuần và ngày nghỉ lễ).

xem lịch dưới đây để hiểu rõ hơn nhé, màu đỏ là nghỉ lễ, màu xanh là ngày sử dụng việc:

Hàm EOMONTH

Để lấy ngày cuối cùng của ngày tháng hiện giờ bạn sẽ dùng hàm EOMONTH. cách thức =EOMONTH(ô chứa ngày tháng bây giờ,0). sử dụng một số khác thay cho k để get ngày cuối cùng của các tháng sau đó, ví dụ thay bằng 2 thì sẽ quét ngày cuối cùng của 2 tháng sau đó, thay bằng -8 là lấy ngày cuối cùng của 8 tháng trước đây.

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 dùng ký tự &, mong muốn chèn thêm dấu cách thì dùng ” ” (mở ngoặc kép, cách, đóng ngoặc kép).

Hàm LEFT

Để quét những ký tự bên trái của một chuỗi sử dụng hàm LEFT. phương thức =LEFT(ô chứa chuỗi cần quét, số ký tự mong muốn lấy)

Hàm RIGHT

dùng hàm RIGHT khi cần get những ký tự từ phía bên phải của chuỗi. bí quyết =RIGHT(ô chứa chuỗi cần quét, số ký tự muốn lấy)

Hàm MID

dùng để get các ký tự từ khi đâu (tính từ trái sang). bí quyết =MID(ô chứ chuỗi cần quét, vị trí lấy, số ký tự cần lấy). Trong ví dụ này ta quét 3 ký tự, bắt đầu từ ký tự thứ 5.

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 ô gồm có cả chữ cái, chữ số và khoảng trắng, hãy nghĩ đến hàm =LEN. công thức =LEN(ô chứa chuỗi cần đếm).

ví dụ, để biết được có bao nhiêu ký tự trong ô B4, chỉ cần di chuyển đến một ô khác bất kỳ và gõ vào cú pháp =LEN(B4). Ngay lập tức, Excel sẽ trả về một con số trị giá tổng các ký tự trong ô B4.

Hàm find

dùng để tìm vị trí của một chuỗi con trong chuỗi. công thức =FIND(“chuỗi con”, ô chứa chuỗi). Trong ví dụ này ta tìm vị trí của chuỗi con “am” trong chuỗi “example text”, và nó ở vị trí thứ 3.

Hàm SUBSTITUTE thay thế văn bản

Để thay thế văn bản trong chuỗi bằng một văn bản không giống bạn sẽ dùng hàm SUBSTITUTE, với mẹo =SUBSTITUTE(ô chứa chuỗi ban đầu,”văn bản ban đầu”,”văn bản thay thế”)

Hàm cắt các khoảng trống TRIM

Nếu phải nhập hay dán văn bản vào Excel chẳng hạn giống như dữ liệu từ một cơ sở dữ liệu, website, software giải quyết văn bản hay các chương trình văn bản không giống, bạn sẽ nhận thấy văn bản được nhập có nhiều khoảng trắng nằm rải rác trong mục lục.

TRIM là một bí quyết giải quyết hiện tượng này bằng cách nhanh chóng loại bỏ các khoảng trống. Hàm =TRIM chỉ đủ nội lực giải quyết văn bản trong một ô duy nhất. gợi ý, để thực hiện cắt các khoảng trắng trong cột E8, bạn chỉ cần gõ hàm =TRIM(E8) một lần vào ô F8 bên cạnh, rồi sau đó chép lại cách thức này xuống cho đến cuối danh sách.

Hàm LOWER, UPPER, PROPER để chuyển biến chữ hoa, chữ thường

Bạn dùng hàm LOWER để biến đổi tất cả các chữ in hoa trong chuỗi thành chữ thường, phương pháp =LOWER(ô chứa chữ cần chuyển đổi).

trái lại, để chuyển toàn bộ 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: =UPPER(ô chứa chuỗi cần chuyển đổi).

Nếu mong muốn viết hoa các chữ đầu từ bạn dùng hàm PROPER. phương thức =PROPER(ô chứa chuỗi cần viết hoa chữ cái đầu từ).

Hàm EXACT để so sánh hai cột

Khi cần so sánh các cột nhìn thấy trị giá của chúng có trùng nhau k bạn sử dụng EXACT. cách thức =EXACT(ô cần so sánh).

Hàm CONCATENATE phối hợp content

Hàm này sẽ hòa hợp content của hai hay nhiều ô vào trong một ô duy nhất. Chẳng hạn, khi bạn mong muốn nhập chung Ngày, tháng, năm, bài, người nổi tiếng vào cùng 1 ô thì hãy gõ =CONCATENATE(A1,B1,C1,D1) giống như bên dưới

5. HÀM rà soát VÀ THAM CHIẾU

Hàm VLOOKUP

Hàm VLOOKUP kiếm tìm các giá trị trong cột ngoài cùng bên trái của bảng và trả về trị giá trong cùng một hàng từ cột khác mà bạn chỉ định. Cú pháp =VLOOKUP(ô chứa trị giá tìm kiếm, vùng bảng chứa giá trị kiếm tìm và trả về,cột chứa giá trị trả về), đủ nội lực thêm đối số thứ 4 như ví dụ dưới.

Hàm VLOOKUP tìm ID (104) trong cột ngoài cùng bên trái của dải $E$4:$G$7 và trả về giá trị trong cùng một hàng từ cột thứ 3 (đối số thứ 3 được chỉ định là 3). Đối số thứ 4 đặt là FALSE để trả lại hiệu quả chuẩn xác hoặc lỗi #N/A nếu không tìm thấy.

Bạn chỉ cần thực hiện trên 1 ô, sau đó kéo hàm VLOOKUP xuống dưới để vận dụng cho các hàng bên dưới. Vì vùng tham chiếu giữ nguyên nên phải sử dụng $ trước các ký hiệu để tạo tham chiếu tuyệt đối.

Để hiểu rõ hơn về cách dùng hàm VLOOKUP bạn có thể tham khảo bài viết: phương pháp dùng hàm Vlookup trong Excel mà Quantrimang.com đã giới thiệu trước đây.

Hàm MATCH

Hàm MATCH trả về vị trí của trị giá trong một dải nhất định.

Yellow được tìm thấy ở vị trí thứ 3 trong dải E4:E7. Đối số thứ 3 là tùy lựa chọn, bạn đủ nội lực đặt đối số này là 0 để trả về vị trí của trị giá trong ô hoặc trả về #N/A nếu không tìm thấy.

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.

92 được tìm thấy tại giao điểm của hàng 3 và cột 2 trong dải E4:F7.

Hàm INDEX dưới đây trả về trị giá đặc biệt trong phạm vi một chiều.

giá trị 97 được tìm thấy ở vị trí thứ 3 của dải E4:E7.

Hàm CHOOSE

Hàm CHOOSE trả về giá trị từ danh mục bạn chỉ định, dựa trên vị trí được yêu cầu.

ví dụ trên, hàm CHOOSE trả về giá trị Boat ở vị trí thứ 3.

  • MS Excel – Bài 5: Các phương pháp và hàm Excel
  • làm thế nào giữ cố định các ô trong Excel?
  • làm sao để để chuyển số thành chữ trong Excel?

Các bạn nhớ bookmark bài này lại nhé, mình sẽ cải tiến tiếp tục đến khi hết “vốn” thì thôi

Nguồn: https://quantrimang.com/