Các Công Thức Excel – Thông tin tuyển sinh đào tạo Đại học Cao đẳng
Các Công Thức Excel đang là thông tin được nhiều người quan tâm tìm hiểu để lựa chọn theo học sau nhiều đợt giãn cách kéo dài do dịch. Website BzHome sẽ giới thiệu cho bạn những thông tin mới nhất chính xác nhất về Các Công Thức Excel trong bài viết này nhé!
Nội dung chính
Các hàm cơ bản trong Excel giúp bạn thực hiện các phép tính, xử lý số liệu trong quá trình học tập và làm việc. Cùng bài viết điểm qua các hàm Excel cơ bản nhất, cùng tìm hiểu nhé!
Bài viết được thực hiện trên phần mềm Excel 2013. Bạn có thể sử dụng tương tự cho các phiên bản Excel khác như: Excel 2007, 2010, 2017, 2020 và Microsoft Excel 365.
1Hàm tính toán Logic
1. Hàm SUM
Hàm SUM dùng để tính tổng tất cả các số trong dãy ô. Nghĩa là SUM có nhiệm vụ tính tổng giá trị các số hoặc dãy số trong ô mà bạn muốn tính.
Cú pháp:
=SUM(Number1,Number2,Number3)
Với:
Number1,Number2,Number3: là giá trị các số hạng cần tính tổng
Kết quả của hàm SUM là tổng các giá trị được chọn.
Ví dụ: =SUM(20,40,60) nghĩa là cộng các số hạng lại với nhau và cho ra kết quả 120.
Ví dụ: Tính tổng các dãy số từ A1 đến A3, =SUM(A1:A3) cho ra kết quả làm 120.
2. Hàm MIN/MAX
Hàm MIN/MAX dùng để tìm giá trị nhỏ nhất/lớn nhất trong một vùng dữ liệu hoặc trong cả bảng tính.
Cú pháp:
=MIN(Number1,Number2,…)
Với:
Number1,Number2: Các giá trị cần so sánh
Kết quả trả về giá trị nhỏ nhất trong vùng dữ liệu
Ví dụ: Giá trị nhỏ nhất trong vùng dữ liệu của ví dụ này, ta có công thức =MIN(A2:C5) và nhận được kết quả 25 là giá trị nhỏ nhất cần tìm.
Cú pháp:
=MAX(Number1,Number2,…)
Với:
Number1,Number2: các giá trị cần so sánh
Kết quả trả về giá trị lớn nhất trong vùng dữ liệu
Ví dụ: Tìm giá trị lớn nhất trong vùng dữ liệu của ví dụ này, ta có công thức =MAX(A2:C5) kết quả đạt được là 89 là giá trị lớn nhất cần tìm.
3. Hàm COUNT/COUNTA
Hàm COUNT dùng để đếm số lượng ô có chứa số trong vùng dữ liệu.
Cú pháp:
=COUNT(Value1,…)
Với:
Value1,…: Tham chiếu ô hoặc phạm vi muốn đếm số
Ví dụ: Tìm số lượng mà giá trị trong ô của vùng dữ liệu là số, ta có công thức cho ví dụ này là =COUNT(A2:C5). Kết quả đạt được là tổng số lượng ô có chứa số.
– Hàm COUNTA dùng để đếm các ô không trống trong một vùng dữ liệu nhất định.
Cú pháp:
=COUNTA(Value1,…)
Với:
Value1,…: Là những ô cần đếm hoặc một vùng cần đếm. Số ô tối đa có thể đếm là 255 (với Excel từ 2007 về sau) và tối đa 30 (với Excel từ 2003 về trước)
Ví dụ: Đếm số lượng ký tự là số trong bảng bên dưới, ta có công thức =COUNTA(A2:C5). Kết quả đạt được là số lượng ô có chứa kí tự hoặc số.
4. Hàm ODD/EVEN
– Hàm ODD là hàm trả về số được làm tròn lên số nguyên lẻ gần nhất.
Cú pháp:
=ODD(Number)
Với
Number: Bắt buộc phải có, là giá trị cần làm tròn
Ví dụ: Làm tròn số 4.6 đến số nguyên lẻ gần nhất nhận được kết quả là 5.
– Hàm EVEN là hàm trả về số được làm tròn lên số nguyên chẵn gần nhất.
Cú pháp:
=EVEN(Number)
Với:
Number: Bắt buộc phải có, là giá trị cần làm tròn
Ví dụ: Làm tròn 53.4 đến số nguyên chẵn gần nhất nhận được kết quả là 54.
5. Hàm AVERAGE
Hàm AVERAGE dùng để tính trung bình cộng của một dãy số trong trang tính.
Cú pháp:
=AVERAGE(number1,number2,…)
Với:
number1 (Bắt buộc): Số thứ nhất, tham chiếu ô, hoặc phạm vi mà bạn muốn tính trung bình.
number2,… (Tùy chọn): Các số, tham chiếu ô hoặc phạm vi bổ sung mà bạn muốn tính trung bình, tối đa là 255.
Ví dụ: Tính lương trung bình theo thông tin bên dưới, ta có công thức =AVERAGE(C3:C8) và nhận được kết quả là giá trị trung bình của 6 tháng lương.
2Hàm điều kiện Logic
1. Hàm COUNTIF
Hàm COUNTIF được dùng để đếm ô thỏa mãn điều kiện trong một vùng dữ liệu được chọn.
Cú pháp:
=COUNTIF(range,criteria)
Với:
range: Vùng dữ liệu cần đếm.
criteria: Điều kiện để đếm.
Ví dụ: Có bảng thống kê các mặt hàng và số lượng tồn như hình dưới.
Để thống kê xem có bao nhiêu mặt hàng còn tồn trên 150 sản phẩm, nhập công thức:
=COUNTIF(C2:C11,”>150″)
Kết quả trả về cho thấy có tổng cộng 8 mặt hàng còn tồn trên 150 sản phẩm.
2. Hàm IF
Hàm IF được dùng để kiểm tra dữ liệu có thỏa điều kiện người dùng đặt ra và trả về kết quả theo biểu thức logic đúng hoặc sai.
Cú pháp:
=IF(Logical_test;Value_if_true;Value_if_false)
Với:
Logical_test: Điều kiện.
Value_if_true: Giá trị trả về nếu thỏa điều kiện
Value_if_false: Giá trị trả về nếu không thỏa điều kiện.
Lưu ý: Nếu bỏ trống Value_if_true và Value_if_false, nếu điều kiện thỏa thì giá trị trả về sẽ là 0 và điều kiện không thỏa thì giá trị trả về sẽ là FALSE.
Ví dụ: Xét học sinh có qua môn với điều kiện:
– Điểm số từ 7 trở lên: Đạt
– Điểm số thấp hơn 7: Không Đạt
Tại ô D2, ta có công thức: =IF(C2>=7,”Đạt”,”Không Đạt”), và được kết quả như hình bên dưới.
3. Hàm SUMIF
Hàm SUMIF dùng để tính tổng các giá trị trong một phạm vi đáp ứng tiêu chí xác định.
Cú pháp:
=SUMIF(range,criteria,[sum_range])
Với:
range: Phạm vi ô bạn muốn đánh giá theo tiêu chí
criteria: Tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng ở ô nào
sum_range: Các ô thực tế để cộng nếu bạn muốn cộng các ô không phải là ô đã xác định trong đối số range. Nếu đối số sum_range bị bỏ qua, Excel cộng các ô được xác định trong đối số range.
Ví dụ: Tính tổng số màu cam ở cột A có số lượng xuất hiện trong cột B, có công thức =SUMIF(A1:A8,”cam”,B1:B8)
3Hàm sử dụng trong văn bản
1. Hàm LEFT/RIGHT/MID
– Hàm LEFT dùng để cắt chuỗi ký tự bên trái chuỗi văn bản mà người dùng chọn.
Cú pháp:
=LEFT(text,[num_chars])
Với:
text: Chuỗi văn bản chứa ký tự mà bạn muốn trích xuất
num_chars: Là số lượng ký tự mà hàm LEFT trích xuất
Ví dụ: Trong bảng dưới đây, dùng hàm LEFT để tìm 3 ký tự đầu tiên tại ô B2. Nhập công thức =LEFT(B3,3), nhấn Enter và được kết quả như hình.
– Hàm RIGHT dù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:
=RIGHT(text,[num_chars])
Với:
text: Chuỗi văn bản chứa ký tự mà bạn muốn trích xuất
num_chars: Là số lượng ký tự mà hàm RIGHT trích xuất
Ví dụ: Trong bảng dưới đây, dùng hàm RIGHT để tìm 7 ký tự cuối tại ô B2. Nhập công thức =RIGHT(B3,7), nhấn Enter và được kết quả như hình.
– Hàm MID trả về một số lượng ký tự cụ thể từ một chuỗi văn bản, bắt đầu từ vị trí do bạn chỉ định và dựa vào số lượng ký tự do bạn chỉ định.
Cú pháp:
=MID(text, start_num, num_chars)
Với:
text: Chuỗi văn bản có chứa các ký tự muốn trích xuất.
start_num: Ví trí của ký tự thứ nhất mà bạn muốn trích xuất trong văn bản
num_chars: Bắt buộc đối với mid. Chỉ rõ số ký tự mà bạn muốn hàm MID trả về từ văn bản.
Ví dụ: Trong bảng dưới đây, dùng hàm MID để tìm 7 ký tự tại ô B2 bắt đầu từ vị trí thứ 3. Nhập công thức =LEFT(B3,3,7), nhấn Enter và được kết quả “ạm Văn “
2. Hàm LEN
Hàm LEN dùng để đếm ký tự trong một chuỗi hoặc một ô chứa chuỗi ký tự và bao gồm cả khoảng trắng.
Cú pháp:
=LEN(chuỗi ký tự)
hoặc
=LEN(ô chứa chuỗi ký tự)
Ví dụ: Trong ví dụ ta thực hiện đếm ô A1 đang chứa chuỗi ký tự “Điện máy XANH“
3. Hàm CONCAT
Hàm CONCAT dùng để nối hai hay nhiều chuỗi văn bản thành một chuỗi văn bản.
Cú pháp:
=IF(Logical_test;Value_if_true;Value_if_false)
Với:
Logical_test: Điều kiện.
Value_if_true: Giá trị trả về nếu thỏa điều kiện
Value_if_false: Giá trị trả về nếu không thỏa điều kiện.
Lưu ý: Nếu bỏ trống Value_if_true và Value_if_false, nếu điều kiện thỏa thì giá trị trả về sẽ là 0 và điều kiện không thỏa thì giá trị trả về sẽ là FALSE.
Ví dụ:
=CONCAT(“Điện”,” “,”máy”,” “,”XANH”,” “,”Xin”,” “,”Cám”,” “,”ơn.”) sẽ trả về “Điện máy XANH Xin Cám ơn.“
4Hàm ngày tháng
1. Hàm NOW
Hàm NOW 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 muốn 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ú pháp:
=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
2. Hàm DATE
Hàm DATE trả về kết quả số cho ngày cụ thể.
Cú pháp:
=DATE(Year,Month,Day)
Với:
Year: chỉ năm. Excel diễn giải đối số năm theo hệ thống ngày được thiết lập trên máy tính của bạn.
Month: chỉ tháng. Một số nguyên đại diện cho tháng trong năm, từ tháng 1 đến tháng 12.
Day: chỉ ngày. Một số nguyên dương ứng với ngày trong tháng.
Ví dụ: =DATE(2015,5,20) trả về kết quả ngày 20 tháng 5 năm 2015.
5Hàm tra cứu dữ liệu
1. Hàm VLOOKUP
– Hàm VLOOKUP là hàm được sử dụng khi cần dò tìm dữ liệu trong một bảng, một phạm vi theo hàng dọc và trả về dữ liệu tương ứng theo hàng ngang.
Cú pháp:
=VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)
Với:
Lookup_value: Giá trị cần dò tìm, có thể điền giá trị trực tiếp hoặc tham chiếu tới một ô trên bảng tính.
Table_array: Bảng giới hạn để dò tìm.
Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm, tính từ trái qua phải.
Range_lookup: tìm kiếm chính xác hay tìm kiếm tương đối với bảng giới hạn, nếu bỏ qua thì mặc định là 1.
- Nếu Range_lookup = 1 (TRUE): dò tìm tương đối.
- Nếu Range_lookup = 0 (FALSE): dò tìm chính xác.
Ví dụ: Xác định mức phụ cấp cho nhân viên. Tại ô E4, bạn điền công thức: =VLOOKUP(D4,$H$3:$I$8,2,0)
Giải thích công thức (theo thứ tự các đối số trong công thức từ trái sang):
- Dấu $ được sử dụng để cố định các dòng, các cột của bảng 2 khi bạn copy công thức sang các ô khác.
- 2 là số thứ tự của cột dữ liệu.
- Range_lookup = 0 (FALSE) để dò tìm chính xác.
2. Hàm INDEX
Hàm INDEX là hàm cho kết quả trả về là một giá trị hoặc tham chiếu tới một giá trị trong bảng hoặc một phạm vi nhất định.
Cú pháp:
=INDEX(array, row_num, column_num)
Với:
array: Phạm vi ô hoặc một hằng số mảng. Nếu mảng chỉ chứa một hàng hoặc cột thì row_num hoặc column_num 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 mà bạn chỉ khai báo 1 trong 2 đối số row_num hoặc column_num, hàm sẽ trả về một mảng của toàn bộ hàng hoặc cột trong mảng.
row_num: Thứ tự của hàng trong mảng chứa giá trị trả về. Đây là đối số bắt buộc phải có, trừ khi bạn khai báo column_num. Nếu bỏ qua row_num, bạn cần phải khai báo column_num.
column_num: Thứ tự của cột trong mảng chứa giá trị trả về. Nếu bỏ qua column_num, bạn cần khai báo row_num.
Ví dụ: Bây giờ, giả sử bạn cần tìm giá trị của phần tử ở dòng thứ 4, cột thứ 1 trong mảng. Mảng ở đây gồm 10 dòng và 4 cột (có địa chỉ là B4:E13). Bạn nhập công thức: =INDEX(B4:E13,4,1)
Giải thích công thức (theo thứ tự các đối số trong công thức từ trái sang):
- B4:E13 là mảng chứa giá trị cần trả về.
- 4 là số thứ tự của hàng trong mảng chứa giá trị cần trả về.
- 1 là số thứ tự của cột trong mảng chứa giá trị cần trả về.
Sau khi nhập xong, bạn nhấn Enter. Kết quả trả về sẽ như hình dưới.
3. Hàm MATCH
Hàm MATCH là hàm tìm kiếm, xác định vị trí tương đối của một giá trị trong một mảng hoặc một ô.
Cú pháp:
=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 ô bạn muốn xác định vị trí của Lookup_value. Vùng dữ liệu mà bạn chọn chỉ có thể có duy nhất 1 hàng hoặc 1 cột.
– Match_type: Xác định kiểu khớp là -1; 0 và 1.
- Kiểu khớp là 0: Trả về vị trí tương đối của giá trị trong trường hợp vùng dữ liệu chưa được sắp xếp.
- Kiểu khớp là 1: Trả về vị trí của giá trị lớn nhất nhưng nhỏ hơn hoặc bằng giá trị cần tìm. Giả sử vùng dữ liệu được sắp xếp theo thứ tự tăng dần.
- Kiểu khớp là -1: Trả về vị trí của giá trị nhỏ nhất nhưng lớn hơn hoặc bằng giá trị cần tìm. Giả sử vùng dữ liệu được sắp xếp theo thứ tự giảm dần.
Ví dụ: Dưới đây là hồ sơ thi tuyển của các học sinh và bạn muốn tìm vị trí của học sinh Lâm Thiên Trang.Tại ô G3 ta thực hiện công thức: =MATCH(G1,A2:A9,0)
4. Hàm HLOOKUP
Hàm HLOOKUP là hàm tìm kiếm một giá trị trên dòng đầu tiên của bảng tham chiếu và cho kết quả tương ứng trong dòng chỉ định.
Cú pháp:
=HLOOKUP(Lookup_value,Table_ array,Row_index_Num,Range_lookup)
Với:
Lookup_value: giá trị đối chiếu với dòng đầu tiên của bảng tham chiếu để lấy được giá trị cần tìm.
Table_array: là địa chỉ tuyệt đối của bảng tham chiếu, không lấy cột tiêu đề.
Row_index_Num: Số thứ tự dòng chứa giá trị cần lấy của bảng tham chiếu (theo thứ tự từ trên xuống dưới và bắt đầu từ số 1).
Range_lookup: Cách tìm kiếm trên bảng tham chiếu.
- “+ 0”: Dòng đầu tiên của bảng tham chiếu chưa sắp xếp.
- “+ 1”: dòng đầu tiên của bảng tham chiếu đã sắp xếp theo chiều tăng dần (ngầm định).
Ví dụ: Ta cần xếp loại học sinh trong Bảng 1 (B3:D8) với dữ kiện ở Bảng 2 (B11:F12), tại D4 ta có: =HLOOKUP(C4,$B$11:$F$12,2,1). Hàm HLOOKUP sẽ dò tìm điểm số ở ô C4 trong Bảng 2 từ trái qua phải. Khi tìm thấy giá trị gần bằng, nó sẽ trả về kết quả xếp loại tương ứng nằm ở hàng 2.
Mời bạn tham khảo một số phần mềm Microsoft đang kinh doanh tại Điện máy XANH
Điện máy XANH đã giới thiệu cho bạn một số hàm cơ bản trong Excel, mong rằng những thông tin trên có thể giúp ích cho bạn. Chúc bạn thành công và nếu có góp ý, xin bạn hãy comment bên dưới để chúng ta cùng trao đổi nhé!
Công thức Excel – những điều cơ bản
Trong MS Excel, công thức excel là các phương trình thực hiện các tính toán khác nhau trong các bảng tính. Mặc dù Microsoft giới thiệu thêm nhiều hàm mới qua các năm, khái niệm công thức bảng tính Excel vẫn giống nhau trong tất cả các phiên bản Excel 2016, Excel 2013, Excel 2010, Excel 2007 và thấp hơn.
- Tất cả các công thức Excel bắt đầu với một dấu bằng (=).
- Sau dấu bằng, bạn nhập hoặc là phép tính hoặc hàm. Ví dụ: để cộng các giá trị trong các ô từ B1 tới B5, bạn có thể:
- Gõ toàn bộ phương trình: = B1 + B2 + B3 + B4 + B5
- Sử dụng hàm SUM: = SUM (B1: B5)
- Nhấn phím Enter để hoàn thành công thức. Làm xong!
Các thành phần của công thức Microsoft Excel
Khi bạn tạo một công thức trong Excel, bạn có thể sử dụng các phần tử khác nhau để cung cấp dữ liệu nguồn cho công thức và chỉ ra các toán tử nên được thực hiện trên những dữ liệu đó. Tùy thuộc vào loại công thức mà bạn tạo ra, nó có thể bao gồm bất kỳ hoặc tất cả các phần sau: ·
- Hằng số – số hoặc các giá trị văn bản mà bạn nhập trực tiếp vào một công thức, như = 2 * 3. ·
- Tham chiếu ô – tham chiếu đến ô chứa giá trị bạn muốn sử dụng trong công thức Excel của bạn, ví dụ: = SUM (A1, A2, B5). Để tham khảo dữ liệu trong hai hoặc nhiều ô tiếp giáp, hãy sử dụng một tham chiếu phạm vi như A1: A5. Ví dụ, để tổng hợp giá trị trong tất cả các ô giữa A1 và A5, sử dụng công thức này: = SUM (A1: A5).·
- Tên – đượ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 – các công thức được xác định trước trong Excel thực hiện tính toán sử dụng các giá trị được cung cấp trong các đối số của chúng. ·
- Toán tử – 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.
Các toán tử trong các bảng tính Excel
Để nói với Microsoft Excel loại công việc bạn muốn thực hiện trong một công thức, bạn sử dụng các ký hiệu đặc biệt được gọi là các toán tử. Có 4 loại toán tử trong Excel:
- Số học – để thực hiện các phép toán cơ bản.
- So sánh (hợp lý) – để so sánh các giá trị.
- Kết nối – để kết hợp các giá trị văn bản vào một chuỗi duy nhất.
- Tham chiếu – để tạo ra các cơn thịnh nộ và các đối số riêng biệt trong các hàm của Excel.
1. Những điều cơ bản về công thức trong Excel bạn cần nắm vững
Trước khi áp dụng bất kỳ công thức hoặc hàm nào vào để tính toán và thống kê số liệu bạn cần phải nắm bắt được những lưu ý cơ bản sau đây:
– Tất cả các hàm và công thức tính trong Excel đều bắt đầu bằng dấu ” = ” Nếu không có thì coi như công thức đó không có giá trị.
– Cú pháp nhập để tính giá trị là ” = công thức hoặc phép tính ” kết thúc bằng enter để hoàn thành.
– Công thức đúng sẽ trả về kết quả. Công thức sai thì kết quả xuất hiện : #N/A, #REF!, #NAME?,….
2. 20 công thức trong Excel và các hàm bạn nên nhớ
Các công thức Excel cơ bản sẽ giúp bạn xác định mối quan hệ giữa các giá trị trong các ô của bảng tính, thực hiện các phép tính trong excel bằng các giá trị đó và trả về giá trị kết quả trong ô bạn chọn. Các công thức bạn có thể tự động thực hiện bao gồm tổng, trừ, tỷ lệ phần trăm, chia, trung bình…
Trong các phiên bản Excel gần đây thì bạn có thể thể tìm và chèn các công thức tính trong excel vào ô tính cụ thể ở trong tab Formulas trên thanh menu.
Các công thức trong Excel
Sử dụng các công thức của các hàm trong Excel sẽ khiến bạn tiết kiệm rất nhiều thời gian của mình.
>> Xem thêm: 5 Cách tính tổng trong excel đơn giản nhất
2.1. 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 – Hình 1
– Bước 2: Nhấn Enter để nhận kết quả.
Hàm Sum trong Excel – Hình 2
2.2. Percentage
Đây 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 trị 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 – Hình 1
– 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 đủ.
Tính phần trăm trong tổng số trong Excel – Hình 2
2.3. 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 – Hình 1
– Bước 2: Nhấn Enter để nhận kết quả.
Thực hiện phép trừ bằng hàm SUM – Hình 2
2.4. Phép nhân
Để 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
2.5. 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
2.6. 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 (1)
– Tự động thiết lập ngày hôm nay nhập công thức:
Hàm Day trong Excel (2)
– 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.
2.7. ARRAY
Mảng số rất hữu ích để thực hiện nhiều công thức cùng một lúc trong một ô để bạn có thể thấy một tổng, hiệu, sản phẩm hoặc thương số cuối cùng.
Cách sử dụng của công thức mảng Array:
Ví dụ: Quan sát bảng dữ liệu sau, dựa vào số lượng bán và đơn giá, yêu cầu tính doanh thu cho sản phẩm.
Công thức mảng trong Excel – Hình 1
– Bước 1: Dùng chuột bôi đen vùng dữ liệu C2:C7, sau đó nhập công thức: =A2:A7*B2:B7
Công thức mảng trong Excel – Hình 2
– Bước 2: Nhấn tổ hợp phím Ctrl + Shift+ Enter để nhận kết quả.
Công thức mảng trong Excel – Hình 3
2.8. Hàm COUNT
Công thức 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 – Hình 1
– Bước 2: Nhấn Enter để nhận kết quả.
Cách sử dụng hàm COUNT trong Excel – Hình 2
2.9. AVERAGE
Để thực hiện công thức tính trung bình trong Excel, sử dụng công thức theo hàm average 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 – Hình 1
– Bước 2: Nhấn Enter để nhận kết quả.
Cách dùng hàm AVERAGE trong Excel – Hình 2
2.10. SUMIF
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 – Hình 1
– Bước 2: Nhấn Enter để nhận kết quả.
Hàm SUMIF trong Excel – Hình 2
2.11. TRIM
Công thức TRIM trong Excel có nhiệm vụ loại bỏ các khoảng trắng trước và sau văn bản được nhập vào ô. Thay vì siêng năng loại bỏ và thêm khoảng trắng khi cần, bạn có thể dọn sạch mọi khoảng cách không đều bằng cách sử dụng chức năng TRIM, được sử dụng để xóa khoảng trắng thừa khỏi dữ liệu (ngoại trừ khoảng trắng giữa các từ). Cú pháp: =TRIM(text)
Trong đó:
– TRIM: Lệnh thực hiện thao tác
– Text: Ô dữ liệu có đoạn văn bản chứa khoảng trắng cần loại bỏ.
Cách sử dụng của hàm TRIM:
Quan sát ví dụ sau, yêu cầu loại bỏ khoảng trống của dữ liệu bằng hàm TRIM.
– Bước 1: Trong ô cần hiển thị kết quả, bạn nhập công thức: =TRIM(B2). Trong đó, B2 là ô tham chiếu.
Hàm Trim trong Excel – Hình 1
– Bước 2: Nhấn Enter để hiển thị kết quả. Sau đó giữ chuột và kéo xuống hết các ô Excel còn lại để đảm bảo kết quả được hiển thị hết.
Hàm Trim trong Excel – Hình 2
2.12. 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 – Hình 1
– Bước 2: Kết quả sau khi hoàn thành như sau.
Hàm Vlookup trong Excel – Hình 2
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ình 3
2.13. AutoSum
Có lẽ SUM () là hàm được sử dụng nhiều nhất trong Microsoft Excel. Tuy nhiên, nhiều người dùng gõ chức năng này bằng tay, điều này thực sự không hiệu quả. Thay vào đó, bạn có thể sử dụng tùy chọn AutoSum có sẵn trên Formulas trên thanh menu hoặc sử dụng đơn giản phím tắt Alt + =
AutoSum rất thông minh để đoán phạm vi tổng và bạn có thể dễ dàng thay đổi phạm vi bên trong chức năng.
Autosum trong Excel
Cách sử dụng Autosum để tính tổng:
Quan sát bảng dữ liệu sau, yêu cầu tính tổng điểm có trong bảng dữ liệu.
– Bước 1: Mở File Excel cần tính tổng, sau đó chọn Autosum để mà hình tự động nhập hàm tính tổng.
Cách tính tổng với Autosum – Hình 1
– Bước 2: Nhấn Enter để hiển thị kết quả.
Cách tính tổng với Autosum – Hình 2
2.14. 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ú 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.
Ví dụ về hàm Max trong Excel – Hình 1
– Bước 2: Nhấn Enter để nhận kết quả.
Ví dụ về hàm Max trong Excel – Hình 2
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: = MAX(E2:E6), trong đó E2:E6 là vùng dữ liệu có chứa giá trị nhỏ nhất.
Ví dụ về hàm Min trong Excel – Hình 3
– Bước 2: Nhấn Enter để nhận kết quả.
Ví dụ về hàm Min trong Excel – Hình 4
>> Xem thêm: Hướng dẫn cách tính tỷ lệ phần trăm trong Excel kèm ví dụ
2.15. CONCAT / CONCATENATE
Các hàm CONCAT và CONCATENATE thực hiện cùng một điều: cả hai kết hợp nhiều ô, phạm vi hoặc chuỗi dữ liệu vào một ô. Việc sử dụng phổ biến nhất của chức năng này là kết hợp tên và họ vào một ô.
Công thức hàm CONCATENATE: =CONCATENATE (text1, [text2], …)
Trong đó:
– Text 1: Là chuỗi văn bản, tham chiếu ô hoặc giá trị trong công thức.
– Text 2: Chuỗi có thể có hoặc không.
Cách sử dụng hàm CONCATENATE trong Excel:
Quan sát bảng dữ liệu sau, yêu cầu nối Họ và Tên bằng hàm CONCATENATE.
– Bước 1: Trong ô muốn hiển thị kết quả, bạn nhập công thức sau: =CONCATENATE(B4,C4)
Hàm CONCATENATE trong Excel – Hình 1
– Bước 2: Nhấn Enter để nhận kết quả.
Hàm CONCATENATE trong Excel – Hình 2
2.16. 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 – Hình 1
– Bước 2: Nhấn Enter để nhận kết quả sau khi áp dụng công thức hàm.
Hàm NOW trong Excel – Hình 2
2.17. CONDITIONAL FORMATTING
Về mặt kỹ thuật, đây không phải là một công thức, nhưng nó là một công cụ cực kỳ hữu ích được tích hợp ngay trong Excel. Nếu bạn về Home –> Styles –> Conditional formatting, bạn có thể chọn nhiều tùy chọn sẽ đưa ra kết quả nếu một số điều là đúng. Bạn có thể làm rất nhiều thứ với cách làm trên.
Cách sử dụng của Conditional Formatiing trong Excel.
Ví dụ minh họa: Quan sát bảng dữ liệu sau, yêu cầu làm nổi bật các ô có chứa chữ Việt Nam trong cột Quốc Tịch.
– Bước 1: Dùng chuột bôi đen vùng dữ liệu cần làm nổi bật. Trên thanh công cụ bạn lần lượt chọn các thao tác như sau: Chọn Home->Conditional formatting ->Highlight Cells Rules->Text that Contains…
Ví dụ về cách sử dụng Conditional Formatiing trong Excel – Hình 1
– Bước 2: Hộp thoại Text that Contains hiện ra, bạn nhập chữ Việt Nam vào ô Format Cells that contain the text . Sau đó nhấn OK để hoàn tất.
Ví dụ về cách sử dụng Conditional Formatiing trong Excel – Hình 2
2.18. INDEX + MATCH
Sự kết hợp của 2 hàm chức năng để truy xuất dữ liệu đa chiều, này sẽ giúp bạn khắc phục những hạn chế gây ra những khó khăn của hàm vlookup, công thức kết hợp hàm:
= INDEX (danh sách các giá trị, MATCH (những gì bạn muốn tra cứu, cột tra cứu, định danh sắp xếp))
Bạn có thể tìm kiếm toàn bộ bảng tính cho các giá trị thay vì chỉ tìm kiếm cột ngoài cùng bên trái.
Cách sử dụng của hàm INDEX và hàm MATCH trong Excel:
Quan sát bảng dữ liệu sau, dựa vào Sản phẩm và hãng sản xuất, yêu cầu điền đơn giá vào cột D trong bảng 1.
Cách thực hiện như sau:
– Bước 1: Trong ô D4 để hiển thị kết quả, bạn nhập công thức sau:
=INDEX($B$15:$E$18,MATCH(B4,$B$15:$B$18,0),MATCH(C4,$B$15:$E$15,0))
Trong đó, kết quả trả về giá trị tương ứng của sản phẩm Mouse là hàng 4 trong bảng 2, cột 2 (Cột Samsung) trong Bảng 2 là 5.
Hàm INDEX kết hợp với hàm Match trong Excel – Hình 1
– Bước 2: Copy công thức để hiển thị kết quả ở những ô tiếp theo.
Hàm INDEX kết hợp với hàm Match trong Excel – Hình 2
2.19. PROPER
PROPER rất hữu ích khi cơ sở dữ liệu của bạn có nhiều văn bản được định dạng kỳ lạ trông lộn xộn với chữ viết hoa ở sai vị trí. Vì thế khi sử dụng hàm PROPER sẽ giúp bạn thực hiện thao tác chuyển chữ thường thành chữ in hoa một cách nhanh chóng.
Cú pháp hàm: =PROPER(text)
Trong đó:
– Text: Dữ liệu chứa đoạn văn bản muốn chuyển đổi.
Cách sử dụng của hàm PROPER trong Excel:
Quan sát bảng dữ liệu sau, yêu cầu chuyển chữ đổi chữ bằng hàm PROPER.
– Bước 1: Trong ô tham chiếu muốn hiển thị kết quả, bạn nhập công thức sau: =PROPER(B14)
Hàm PROPER trong Excel – Hình 1
– Bước 2: Nhấn Enter để nhận kết quả. Sau đó dùng trỏ chuột kéo xuống hết các ô Excel bên dưới để hiển thị kết quả.
Hàm PROPER trong Excel – Hình 2
2.20. AND
Đây là một hàm logic khác trong các hàm Excel và nó sẽ kiểm tra xem một số thứ là đúng hay sai. Ví dụ: = AND (A1 = “TỐT”, B2> 10) sẽ xuất TRUE nếu A1 là TỐT và giá trị của B2 lớn hơn 10. Bạn cũng có thể kiểm tra nhiều giá trị hơn hai, chỉ cần thêm nó vào dấu phẩy khác.
Cú pháp hàm AND: =AND(Điều kiện 1,Điều kiện 2,…)
Trong đó:
– Điều kiện 1, điều kiện 2 là mệnh đề Logic.
Cách sử dụng của hàm AND trong Excel.
Quan sát bảng dữ liệu sau, yêu cầu xét điều kiện sinh giỏi của các học sinh trong bảng bằng hàm AND.
– Bước 1: Trong ô E3 cần hiển thị kết quả, bạn nhập công thức sau: =AND(C3>=5, D3>=5)
Trong đó:
– AND: Lệnh hàm bắt buộc phải có
– C3>=5 và D3>=5: Điều kiện để xét học sinh giỏi là điểm môn toàn và văn lớn hơn hoặc bằng 5.
Hàm AND trong Excel – Hình 1
– Bước 2: Nhấn Enter để nhận kết quả. Sau đó dùng trỏ chuột kéo xuống hết các ô Excel bên dưới để hiển thị kết quả.
Với kết quả như bảng, những bạn hiển thị kết quả FLASE là Lê B và Phạm D không đáp ứng được tiêu chí của đề bài là cả 2 môn toán và văn lớn hơn hoặc bằng 5 điểm nên 2 bạn này không được học sinh giỏi.
Hàm AND trong Excel – Hình 2
Đây là một số những công thức trong Excel được dùng phổ biến và hỗ trợ đắc lực cho người sử dụng thực hiện cho các công việc khi dùng Excel. 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 thông qua các khoá học Excel giúp bạn xử lý số liệu, báo cáo một cách nhanh chóng và chính xác nhất từ đó giảm thiểu tối đa thời gian cũng như nâng cao hiệu suất công việc. Hoặc một giải pháp Excel hiệu quả bằng cách tham khảo cuốn sách Excel độc quyền chỉ có trên Unica.
Xin cảm ơn và chúc bạn thành công.
Chúc bạn thành công!
Unica gợi ý cho bạn: Khóa học “Làm chủ excel qua 100+ chuyên đề từ cơ bản đến nâng cao”
XEM TRỌN BỘ KHÓA HỌC TẠI ĐÂY
Tags:
Excel
I. Các Hàm Trong Excel Cơ Bản – Hàm Tính Toán
1. Hàm SUM trong Excel
Hàm SUM dùng để tính tổng tất cả các số trong một số ô hoặc một dãy ô bạn chọn.
Công thức: =SUM(Number1, Number2, Number3,…)
2. Hàm MIN/MAX trong Excel
Các hàm MIN/ MAX được sử dụng để tìm giá trị nhỏ nhất/ lớn nhất trong một phạm vi dữ liệu hoặc toàn bộ trang tính.
Công thức: =MIN(Number1,Number2,…)
Công thức: =MAX(Number1,Number2,…)
3. Hàm COUNT/COUNTA
– Hàm COUNT đếm số ô trong phạm vi dữ liệu có chứa số.
Công thức: =COUNT(Value1,…)
– Hàm COUNTA được sử dụng để đếm các ô không trống trong một phạm vi dữ liệu.
Công thức: =COUNTA(Value1,…)
4. Hàm ODD/EVEN
– Hàm ODD là hàm làm tròn số thập phân lên số nguyên là số lẻ gần nhất.
Công thức: =ODD(Number)
– Hàm EVEN là hà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: =EVEN(Number)
5. Hàm AVERAGE
Hàm AVERAGE được sử dụng để tính giá trị trung bình của một chuỗi số trong trang tính.
Công thức: =AVERAGE(number1, number2,…)
II. Các Hàm Trong Excel: Hàm Điều Kiện
1. Hàm COUNTIF trong Excel
Hàm COUNTIF được sử dụng để đếm các ô trong phạm vi dữ liệu đã chọn thỏa mãn điều kiện.
Công thức: =COUNTIF(range,criteria)
2. Hàm IF trong Excel
Hàm IF được dùng để kiểm tra điều kiện của dữ liệu có thỏa có thỏa mãn yêu cầu của người dùng đặt ra hay không và hiện kết quả theo biểu thức logic đúng hoặc sai.
Công thức: =IF(Logical_test;Value_if_true;Value_if_false)
3. Hàm SUMIF trong Excel
Hàm SUMIF được sử dụng để tính tổng một dãy giá trị thỏa mãn một điều kiện cụ thể.
Công thức: =SUMIF(range,criteria,[sum_range])
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 toàn bộ bảng tính chỉ chứa chữ số, không chứa chữ cái. Thay vì phải ngồi đếm một cách thủ công thì bạn có thể sử dụng hàm =COUNT.
Hàm COUNT trong Excel trả về số lượng các giá trị là số. Số ở đây bao gồm số âm, tỷ lệ phần trăm, ngày, giờ, phân số và công thức trả về số. Các ô trống và giá trị văn bản bị bỏ qua.
Cú pháp:
COUNT (value1, [value2], ...)Cú pháp hàm COUNT có các đối số sau:
value1: Bắt buộc. Mục đầu tiên, tham chiếu ô hoặc phạm vi mà bạn muốn đếm số.
value2, …: Tùy chọn. Lên đến 255 mục bổ sung, tham chiếu ô hoặc phạm vi mà bạn muốn đếm số.
Lưu ý: Các đối số có thể chứa hoặc tham chiếu đến nhiều loại dữ liệu khác nhau, nhưng chỉ số mới được tính.
Những giá trị được tính bao gồm:
- Các đối số là số, ngày tháng hoặc đại diện văn bản của số (ví dụ: một số được đặt trong dấu ngoặc kép, chẳng hạn như “1”) được tính.
- Những giá trị logic và biểu diễn văn bản của các số mà bạn nhập trực tiếp vào danh sách các đối số sẽ được tính.
- Các đối số là giá trị lỗi hoặc văn bản không thể dịch thành số sẽ không được tính.
- Nếu đối số là một mảng hoặc tham chiếu, thì chỉ các số trong mảng hoặc tham chiếu đó mới được tính. Các ô trống, giá trị logic, văn bản hoặc giá trị lỗi trong mảng hoặc tham chiếu không được tính.
Nếu bạn muốn đếm các giá trị logic, văn bản hoặc giá trị lỗi, hãy sử dụng hàm COUNTA (COUNT tính toán số lượng ô có giá trị số, còn COUNTA tìm số ô có dữ liệu không trống). Nếu bạn chỉ muốn đếm các số đáp ứng những tiêu chí nhất định, hãy sử dụng hàm COUNTIF hoặc hàm COUNTIFS.
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ể
Hàm COUNTIF của Microsoft Excel đếm số ô trong một phạm vi đáp ứng tiêu chí nhất định.
Hàm COUNTIF là một hàm tích hợp trong Excel được phân loại là hàm thống kê. Nó có thể được sử dụng như một hàm trang tính (WS) trong Excel. Là một hàm trang tính, hàm COUNTIF có thể được nhập như một phần của công thức trong một ô của trang tính.
Nếu bạn muốn áp dụng nhiều tiêu chí, hãy thử sử dụng hàm COUNTIFS.
Cú pháp cho hàm COUNTIF trong Microsoft Excel là:
COUNTIF( range, criteria )Tham số hoặc đối số
- range: Phạm vi ô mà bạn muốn đếm dựa trên tiêu chí.
- criteria: Tiêu chí được sử dụng để xác định ô nào cần đếm.
Hàm COUNTIF trả về một giá trị số. Hàm này có thể áp dụng cho Excel cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 cho Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.
Bạn cũng có thể sử dụng một Named Range trong hàm COUNTIF. Named Range là tên mô tả cho một tập hợp các ô hoặc dải ô trong một trang tính.
Nếu công thức COUNTIF của bạn sử dụng tiêu chí phù hợp với một chuỗi dài hơn 255 ký tự, nó sẽ trả về lỗi. Để khắc phục điều này, hãy sử dụng hàm CONCATENATE để khớp các chuỗi dài hơn 255 ký tự. Bạn có thể tránh nhập toàn bộ hàm bằng cách chỉ cần sử dụng dấu và (&), như được minh họa bên dưới.
=COUNTIF(A2:A5,"long string"&"another long string")Một hành vi của hàm COUNTIF cần lưu ý là nó bỏ qua các chuỗi chữ hoa và chữ thường. Các tiêu chí bao gồm một chuỗi chữ thường và một chuỗi chữ hoa sẽ khớp với các ô giống nhau và trả về cùng một giá trị.
Để đếm các ô dựa trên một điều kiện cụ thể (ví dụ, lớn hơn 9), hãy sử 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 sử dụng hàm COUNTIFS sau.
Hàm tính tổng SUM
Đây có thể nói là hàm đầu tiên mà bất kỳ người dùng nào mới học Excel cũng cần phải biết đến. Hàm SUM trong Excel cộng các giá trị số trong một phạm vi ô. Được phân loại theo hàm toán học và lượng giác, hàm được nhập bằng cách gõ “=SUM” theo sau là các giá trị được tính tổng. Các giá trị được cung cấp cho hàm có thể là số, tham chiếu ô hoặc phạm vi.
Công thức SUM tự động cập nhật khi người dùng chèn hoặc xóa một giá trị. Nó cũng bao gồm những thay đổi được thực hiện đối với một phạm vi ô hiện có. Hơn nữa, hàm cũng tự bỏ qua các ô trống và giá trị văn bản.
Cú pháp của hàm SUM trong Excel được hiển thị trong hình sau:
Hàm chấp nhận các đối số sau:
- Number1: Đây là giá trị số đầu tiên được thêm vào.
- Number2: Đây là giá trị số thứ hai được thêm vào.
- Đối số “number1” là bắt buộc trong khi các số tiếp theo (“number2”, “number3”, v.v…) là tùy chọn.
Các quy tắc điều chỉnh việc sử dụng hàm được liệt kê như sau:
- Các đối số được cung cấp có thể là số, mảng, tham chiếu ô, hằng số, phạm vi và kết quả của những hàm hoặc công thức khác.
- Trong khi cung cấp một dải ô, chỉ dải ô đầu tiên (cell1:cell2) là bắt buộc.
- Đầu ra là số và đại diện cho tổng các giá trị được cung cấp.
- Các đối số được cung cấp có thể lên tới tổng số 255.
Lưu ý: Hàm SUM excel trả về giá trị “#VALUE!” lỗi nếu tiêu chí được cung cấp là một chuỗi văn bản dài hơn 255 ký tự. Phạm vi ô được cung cấp phải khớp với kích thước của nguồn.
Ô chứa đầu ra phải luôn được định dạng dưới dạng số.
Cùng tìm hiểu ví dụ sau:
Giả sử, bạn muốn cộng các con số 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 chọn hàm =SUM vừa xuất hiện trong danh sách nổi lên.
Tiếp theo, hãy nhấn 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à nhấn phím Enter. Kết quả tổng của hai con số trong hai ô A2 và B2 mà bạn vừ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 chọn thêm những ô cần thiết vào trong nội dung của hàm.
Hàm tính tổng SUMIF có điều kiện
Hàm SUMIF sẽ tổng hợp các ô đáp ứng các tiêu chí đã cho. Các tiêu chí dựa trên ngày, số và văn bản. Nó hỗ trợ các toán tử logic như (>, <, <>, =) và cả các ký tự đại diện (*, ?).
Đóng vai trò công cụ phân tích tài chính, SUMIF là một trong những hàm được sử dụng thường xuyên nhất. Giả sử bạn được đưa cho một bảng liệt kê các lô hàng rau từ những nhà cung cấp khác nhau. Tên loại rau, tên nhà cung cấp và số lượng lần lượt ở cột A, cột B và cột C. Trong trường hợp như vậy, bạn có thể sử dụng hàm SUMIF để tìm ra tổng số tiền liên quan đến một loại rau cụ thể từ một nhà cung cấp mong muốn.
Công thức chung của hàm SUMIF trong Excel là:
=SUMIF(range, criteria, [sum_range])Công thức trên sử dụng các đối số sau:
- range (đối số bắt buộc) – Đây là phạm vi ô mà bạn muốn áp dụng tiêu chí.
criteria (đối số bắt buộc) – Đây là tiêu chí được sử dụng để xác định ô nào cần được thêm vào. Đối số criteria có thể là:- Giá trị số (có thể là số nguyên, số thập phân, ngày, giờ hoặc giá trị logic) (ví dụ: 10, 01/01/2018, TRUE)
- Chuỗi văn bản (ví dụ: “Văn bản”, “Thứ Năm”)
- Một biểu thức (ví dụ: “>12”, “<>0”).
- sum_range (đối số tùy chọn) – Đây là một mảng các giá trị số (hoặc các ô chứa giá trị số) sẽ được cộng lại với nhau nếu mục nhập phạm vi tương ứng đáp ứng các tiêu chí được cung cấp. Nếu đối số [sum_range] bị bỏ qua, thay vào đó, các giá trị từ đối số phạm vi sẽ được tính tổng.
Ví dụ, để tính tổng các ô dựa trên một điều kiện (ví dụ, lớn 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 sử 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ố đầu tiên là phạm vi bảng tính cần tính tổng).
Hàm tính giá trị trung bình AVERAGE
Hàm =AVERAGE làm đúng chức năng như tên gọi của nó là đưa ra con số có giá trị trung bình trong những số được chọn. Hàm AVERAGE có thể xử lý tới 255 đối số riêng lẻ, có thể bao gồm số, tham chiếu ô, phạm vi, mảng và hằng số.
Cú pháp của hàm AVERAGE là:
= AVERAGE (number1, [number2],...)Trong đó:
- number1 – Một số hoặc tham chiếu ô đề cập đến các giá trị số.
- number2 – [tùy chọn] Một số hoặc tham chiếu ô đề cập đến các giá trị số.
Hàm AVERAGE tính giá trị trung bình của các số được cung cấp dưới dạng đối số. Để tính giá trị trung bình, Excel tính tổng tất cả các giá trị số và chia cho số giá trị số.
AVERAGE nhận nhiều đối số ở dạng number1, number2, number3, v.v. lên đến tổng số 255. Các đối số có thể bao gồm số, tham chiếu ô, phạm vi, mảng và hằng số. Các ô trống và ô có chứa văn bản hoặc giá trị logic bị bỏ qua. Tuy nhiên, các giá trị không (0) được bao gồm. Bạn có thể bỏ qua các giá trị không (0) bằng hàm AVERAGEIFS, như được giải thích bên dưới.
Hàm AVERAGE sẽ bỏ qua các giá trị logic và số được nhập dưới dạng văn bản. Nếu bạn cần đưa các giá trị này vào giá trị trung bình, hãy xem hàm AVERAGEA.
Nếu các giá trị được cung cấp cho AVERAGE có lỗi, thì AVERAGE trả về lỗi. Bạn có thể sử dụng hàm AGGREGATE để bỏ qua lỗi.
Ví dụ bạn muốn tính giá trị trung bình từ ô A10 đến ô J10, chỉ cần gõ =AVERAGE(A10:J10) rồi nhấn Enter.
Kết quả đưa 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 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 nếu các ô không nằm kế cận nhau.
Tương tự như SUMIF, COUNTIF bạn có thể sử dụng AVERAGEIF và AVERAGEIFS để tính giá trị trung bình của các ô dựa trên một hoặc nhiều tiêu chí.
Hàm AVERAGE tự động bỏ qua các ô trống. Trong màn hình bên dưới, thông báo ô C4 trống và AVERAGE chỉ cần bỏ qua nó và tính giá trị trung bình của B4 và D4:
Tuy nhiên, lưu ý rằng giá trị không (0) trong C5 được bao gồm trong giá trị trung bình, vì nó là một giá trị số hợp lệ. Để loại trừ các giá trị 0, hãy sử dụng AVERAGEIF hoặc AVERAGEIFS thay thế. Trong ví dụ dưới đây, AVERAGEIF được sử dụng để loại trừ các giá trị bằng không. Giống như hàm AVERAGE, AVERAGEIF tự động loại trừ các ô trống.
=AVERAGEIF(B3:D3,">0") // exclude zeroHàm đếm ô trống COUNTBLANK
Hàm COUNTBLANK được phân loại theo các hàm thống kê của Excel. Bạn sử dụng hàm này khi cần đếm các ô trống. Trong phân tích tài chính, hàm này có thể hữu ích trong việc đánh dấu hoặc đếm các ô trống trong một phạm vi nhất định. Cú pháp của hàm là:
=COUNTBLANK(phạm vi bảng tính cần đếm)Trong đó:
Phạm vi bảng tính cần đếm chỉ định phạm vi ô mà bạn muốn đếm các ô trống.
Đối với hàm COUNTBLANK, hãy nhớ rằng:
- Bất kỳ ô nào chứa văn bản, số, lỗi, v.v… đều không được tính bởi hàm này.
- Các công thức trả về văn bản trống (“”) được coi là trống và sẽ được tính. Vì vậy, nếu một ô chứa một chuỗi văn bản trống hoặc một công thức trả về một chuỗi văn bản trống, thì ô đó sẽ được tính là trống bởi hàm COUNTBLANK.
- Các ô chứa số 0 được coi là không trống và sẽ không được tính.
Là một hàm trang tính, COUNTBLANK có thể được nhập như một phần của công thức trong một ô của trang tính. Để hiểu cách sử dụng hàm, hãy xem xét một ví dụ:
Nếu muốn biết số lượng ô trống, bạn có thể sử dụng định dạng có điều kiện với hàm này.
Giả sử bạn được cung cấp dữ liệu dưới đây:
Ở đây, bạn có thể sử dụng công thức =COUNTBLANK(A2:D5) để đếm các hàng trống:
Ta được kết quả dưới đây:
Nếu muốn, bạn có thể sử dụng định dạng có điều kiện để đánh dấu các hàng có ô trống bằng cách sử dụng hàm COUNTBLANK. Chọn phạm vi mong muốn và trên định dạng có điều kiện đã chọn và áp dụng hàm COUNTBLANK(). Điều này sau đó sẽ highlight tất cả các ô trống trên phạm vi mong muốn.
Hàm đếm ô không trống 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).
Lưu ý khi sử dụng:
Hàm COUNTA đếm các ô có chứa giá trị, bao gồm số, văn bản, logic, lỗi và văn bản trống (“”). COUNTA không đếm các ô trống.
Hàm COUNTA trả về số lượng giá trị trong danh sách các đối số được cung cấp. COUNTA nhận nhiều đối số ở dạng value1, value2, value3, v.v… Các đối số có thể là các giá trị được hardcode riêng lẻ, tham chiếu ô hoặc phạm vi lên đến tổng số 255 đối số. Tất cả các giá trị đều được tính, bao gồm văn bản, số, tỷ lệ phần trăm, lỗi, ngày, giờ, phân số và công thức trả về chuỗi trống (“”). Các ô trống được bỏ qua.
Ví dụ về cách sử dụng hàm COUNTA
Trong ví dụ được hiển thị, COUNTA được thiết lập thành các giá trị trong phạm vi B5:B15:
=COUNTA(B5:B15) // returns 9COUNTA trả về 9, vì có 9 ô không trống trong phạm vi B5:B15.
Hàm COUNTA đếm số và văn bản:
=COUNTA(1,2,3) // returns 3
=COUNTA(1,"a","b") // returns 3
=COUNTA(1,2,3,"a",5%) // returns 5Để đếm các ô không trống trong phạm vi A1:A10:
=COUNTA(A1:A10) // count non-empty cells in A1:A10Để đếm các ô không trống trong phạm vi A1:A10 và phạm vi C1:H2:
=COUNTA(A1:A10,C1:H2) // two rangesLưu ý rằng COUNTA bao gồm các chuỗi rỗng (“”) trong số đếm, có thể được trả về bằng công thức. Ví dụ, công thức bên dưới sẽ trả về “OK” khi giá trị trong A1 ít nhất là 10 và một chuỗi rỗng (“”) khi giá trị nhỏ hơn 10:
=IF(A1>=10,"OK","")Hàm COUNTA sẽ tính cả hai kết quả là không trống.
Hàm tính tổng giá tiền sản phẩm SUMPRODUCT
Để tính tổng sản phẩm của các số tương ứng (kiểu tính tổng tiền của các sản phẩm 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.
Hàm SUMPRODUCT trong Excel nhân các phạm vi hoặc mảng với nhau và trả về tổng các tích. Điều này nghe có vẻ nhàm chán nhưng SUMPRODUCT là một hàm cực kỳ hữu ích có thể được sử dụng để đếm và tính tổng như COUNTIFS hoặc SUMIFS, nhưng linh hoạt hơn. Có thể dễ dàng sử dụng các hàm khác bên trong SUMPRODUCT để mở rộng chức năng hơn nữa.
Hàm SUMPRODUCT nhân các mảng với nhau và trả về tổng các tích. Nếu chỉ có một mảng được cung cấp, SUMPRODUCT sẽ chỉ tính tổng các mục trong mảng. Có thể cung cấp tối đa 30 phạm vi hoặc mảng.
Khi bạn gặp SUMPRODUCT lần đầu tiên, nó có vẻ nhàm chán, phức tạp và thậm chí vô nghĩa. Nhưng SUMPRODUCT là một hàm linh hoạt đáng kinh ngạc với nhiều công dụng. Bởi vì nó sẽ xử lý các mảng một cách duyên dáng, bạn có thể sử dụng nó để xử lý các phạm vi ô theo những cách vô cùng thông minh.
Ví dụ dưới đây dùng hàm SUMPRODUCT để tính tổng số tiền đã tiêu.
Cụ thể ở trên, hàm SUMPRODUCT đã thực hiện phép tính: (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500.
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!
Nếu giá trị trong ô không phải dạng số thì SUMPRODUCT sẽ mặc định giá trị của chúng là 0.
Lưu ý
- SUMPRODUCT coi các mục không phải là số trong mảng là số không.
- Các đối số mảng phải có cùng kích thước. Nếu không, SUMPRODUCT sẽ tạo ra giá trị lỗi #VALUE!.
- Các kiểm tra logic bên trong mảng sẽ tạo ra những giá trị TRUE và FALSE. Trong hầu hết các trường hợp, bạn sẽ muốn ép buộc các giá trị này thành 1 và 0.
- SUMPRODUCT thường có thể sử dụng trực tiếp kết quả của các hàm khác
Hàm MIN, MAX
Để tìm giá trị thấp nhất (số nhỏ nhất) trong một tập hợp các giá trị, hãy sử dụng hàm MIN.
Hàm MIN có các đối số sau trong cú pháp của nó:
- number1 – bắt buộc
- number2 đến number255 – tùy chọn
number1, [number2], … là chuỗi giá trị từ đó bạn muốn nhận giá trị nhỏ nhất. Number1 là bắt buộc trong khi [number2] và các mục sau đó là tùy chọn.
Đối với mỗi đối số, bạn có thể nhập số, tên, mảng hoặc tham chiếu trang tính nơi lưu trữ số.
Nếu đối số là một tham chiếu hoặc mảng, thì mọi ô trống, giá trị logic (true hoặc false) hay giá trị văn bản đều bị bỏ qua khi tính toán giá trị tối thiểu.
Lưu ý: Nếu phạm vi không chứa bất kỳ giá trị số nào, công thức MIN sẽ trả về số không
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 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 đó.
Có tối đa 255 đối số được phép trong một hàm. Các đối số có thể là số, ô, mảng tham chiếu và phạm vi. Tuy nhiên, các đối số như giá trị logic, văn bản, ô trống sẽ bị bỏ qua.
Ví dụ về cách sử dụng hàm MIN:
Giả sử bạn có một số loại trái cây trong kho. Nhiệm vụ của bạn là kiểm tra xem có loại nào bị hết không.
Trường hợp 1: Nhập từng chữ số từ cột Qty in stock:
=MIN(366, 476, 398, 982, 354, 534, 408)Trường hợp 2: Tham chiếu lần lượt các ô từ cột Qty:
=MIN(B2,B3,B4,B5,B6,B7,B8)Trường hợp 3: Hoặc đơn giản là tham chiếu toàn bộ phạm vi:
=MIN(B2:B8)Trường hợp 4: Ngoài ra, bạn có thể tạo một dải ô đã đặt tên và sử dụng dải ô đó để tránh mọi tham chiếu trực tiếp:
=MIN(Qty-in-stock)Ngược với hàm =MIN vừa đề cập, 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. Hàm Excel MAX có các đối số sau trong cú pháp của nó:
- number1 – bắt buộc
- number2 đến number255 – tùy chọn
Đối với mỗi đối số, bạn có thể nhập số, tên, mảng hoặc tham chiếu trang tính nơi lưu trữ số.
Nếu đối số là một tham chiếu hoặc mảng, thì bất kỳ ô trống, giá trị logic hoặc văn bản nào đều bị bỏ qua khi tính toán giá trị tối đa.
Lưu ý: Nếu phạm vi không chứa bất kỳ giá trị số nào, thì kết quả của công thức MAX bằng 0.
Trong Excel 2019 hoặc Excel for Office 365, bạn có thể sử dụng các hàm MINIFS và MAXIFS để tìm giá trị tối thiểu hoặc giá trị tối đa, dựa trên một hoặc nhiều tiêu chí. Đối với các phiên bản Excel trước, các hàm MINIFS và MAXIFS không khả dụng
I. Sử dụng hàm excel cơ bản có lợi ích gì?
Excel cung cấp hàng trăm hàm cơ bản và nâng cao để giúp cho mọi thao tác thêm nhanh chóng và chính xác hơn. Người làm việc, học tập nên sử dụng các hàm tính bởi:
- Trên bàn phím chỉ có thể thực hiện các phép tính thông thường như phím cộng (+), trừ (-), nhân (*) và thực hiện tốt với số lượng 2-4 con số. Nếu như yêu cầu đưa ra phải tính số lượng nhiều hơn thì rất mất thời gian khi bạn phải nhập 100 ô với 99 dấu +. Còn sử dụng các hàm tính thì chỉ với vài thao tác cùng kí tự, bạn đã có thể tính toán nhanh chóng cho dữ liệu lớn.
- Bên cạnh đó, có những phép tính bạn không thể sử dụng các phím có sẵn, VD: tính trung bình, tìm số lớn nhất, nhỏ nhất…Đừng lo, các hàm excel sẽ làm nhiệm vụ này.
- Nếu bạn chỉ tính toán bằng những phím thông thường sẽ không đảm bảo được độ chính xác, bộ não của Excel sẽ tiếp nhận các hàm và cho ra kết quả đúng nhất.
- Excel còn cho phép nhập một lần ở 1 khung và có thể kéo chuột để cho ra kết quả ở các dòng khác. Đồng thời, Excel có chức năng gợi ý hàm, khi bạn nhập 1-2 chữ cái đầu của hàm, các hàm thông dụng sẽ hiện ra. Đây sẽ là ưu điểm mạnh để bạn hoàn thành công việc tính toán chỉ trong thời gian ngắn.
II. Tổng hợp những hàm cơ bản trong Excel
1. Hàm đếm số liệu
Hàng trăm, hàng nghìn dữ liệu với đầy ắp những con số sẽ không thể đếm theo cách thông thường được. Đừng lo lắng bởi đã có các hàm sau:
1.1. Hàm đếm COUNT
Chức năng: Thường được dùng trong các trường hợp cần đếm số ô trong 1 vùng dữ liệu hoặc toàn bộ dữ liệu trong file. COUNT sẽ trả kết quả và các giá trị số ( như số âm, phần trăm, số ngày tháng năm, phân thức…) còn các ký tự văn bản sẽ không được đếm.
Cú pháp: =COUNT(value1, [value2],…)
Trong đó:
Value 1, value 2,… là những giá trị trong vùng tuỳ chọn để đếm các ô giá trị
Bạn có thể đếm được 255 đối số trong 1 lần đến trong các phiên bản Excel 2007 – 2016
VD:
Sử dụng hàm Count để đếm số cột thành tiền trong bảng tính
1.2. Hàm đếm có điều kiện COUNTIF
Chức năng: Thường được dùng để đếm số ô giá trị thoả mãn điều kiện cụ thể mà người dùng đặt ra.
Cú pháp: =COUNTIF(range; criteria)
Trong đó:
Range là vùng dữ liệu cần đếm
Criteria là điều kiện để đếm
Ví dụ ở bảng này, bạn muốn đếm số lượng Cam loại 2 bán ra, bạn nhập =COUNTIF(B5:B12,”CAM 2”)
Hàm Countif trong Excel
1.3. Hàm đếm có nhiều điều kiện COUNTIFS
Chức năng: Dùng để tìm số lượng ô có giá trị thoả mãn từ 2 điều kiện trở lên.
Cú pháp: =COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2] …)
Trong đó:
criteria_range1: Vùng dữ liệu đầu tiên, bắt buộc cần điền
criteria1: Điều kiện của vùng criteria_range1.
[criteria_range2, criteria2]: Các cặp vùng chọn và điều kiện bổ sung.
Hàm này cho phép bạn thêm tối đa 127 cặp điều kiện.
Lưu ý: Khi chọn vùng và điều kiện bổ sung cần phải có cùng số hàng, cột với vùng 1 trước đó, không nhất thiết phải nằm liền kề nhau
Ví dụ: Bạn có danh hàng trái cây như bảng sau. Muốn xem thử còn bao nhiêu hàng tồn kho chưa bán được. Lúc này, điều kiện sẽ là sản phẩm còn phải lớn hơn 0 và không hết hàng. Bạn chỉ cần nhập cú pháp: =COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)
Sử dụng hàm Countifs để kết hợp nhiều điều kiện
1.4. Hàm đến ô trống COUNTBLANK
Chức năng: Được dùng để đếm các ô trống không có dữ liệu trong vùng được chọn
Cú pháp: =COUNTBLANK(range)
Trong đó:
Range là vùng dữ liệu cần đếm
Ví dụ: Bạn muốn đếm số buổi có sinh viên vắng. Hãy nhập công thức: =COUNBLANK(C3:F7)
Hàm COUNTBLANK để đếm vùng trống
1.5. Hàm đếm ô không trống COUNTA
Chức năng: Dùng để các ô có chứa dữ liệu ( số, ký tự, biểu tượng..) trong vùng dữ liệu tuỳ chọn
Cú pháp: =COUNTA(range)
Trong đó:
Range là vùng dữ liệu cần đếm
Ví dụ: Muốn số buổi sinh viên có mặt, bạn chỉ cần nhập =COUNTA(C3:F7) như bảng sau
Dùng hàm COUNTA để đếm ô có dữ liệu
2. Hàm tính tổng
2.1. Hàm SUM
Chức năng: dùng để cộng các ô có giá trị số trong một vùng phạm vi. Công thức sẽ tự động cập nhật khi người dùng thay đổi hoặc chèn thêm giá trị. Hàm sẽ tự bỏ qua các ô trống và các ô chứa văn bản không phải số.
Cú pháp: =SUM(number1,[number2],…)
Trong đó:
Number1, number2 là các giá trị số được thêm vào.
Ví dụ: Ở bảng hàng sau, bạn muốn tính tổng số lượng hàng còn, nhập =SUM(B2:B7)
Hàm SUM để tính tổng giá trị các ô dữ liệu
2.2. Hàm tính tổng có điều kiện SUMIF
Chức năng: dùng để tính tổng có điều kiện của các ô dữ liệu
Cú pháp: =SUMIF(range,criteria,[sum_range])
Trong đó:
Range: vùng chứa các ô dữ liệu cần tính tổng
Criteria: Điều kiện/tiêu chí
Sum_range: vùng dữ liệu cần tính tổng
Ví dụ: để tính tổng thuế thu nhập cá nhân của những người có thu nhập dưới 50 triệu đồng từ bảng dưới đây. Nhập công thức: =SUMIF(B2:B7,”<50,000,000″,D2:D7)
Hàm SUMIF để kết hợp điều kiện khi tính tổng
2.3. Hàm tính tổng nhiều điều kiện SUMIFS
Chức năng: dùng để tính tổng các ô dữ liệu theo nhiều điều kiện
Cú pháp: = SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Trong đó:
Sum_range: Là phạm vi ô cần tính tổng (số hoặc tên), đây là phần bắt buộc.
Criteria_range1: phạm vi cần đánh giá theo điều kiện
Criteria1: Điều kiện/tiêu chí
Criteria_range2, criteria2,…: các cặp phạm vi, điều kiện bổ sung
Ví dụ: Muốn tính số nhân viên bán hàng có lương trên 15 triệu. Bạn nhập =SUMIFS(B2:B7,A2:A7,”>15000000″,F2:F7,”bán hàng”)
Hàm SUMIFS để kết hợp nhiều điều kiện
2.4. Hàm tính tổng giá tiền sản phẩm SUMPRODUCT
Chức năng: tính tổng từ các tích. Hay nói dễ hiểu hơn, hàm sẽ được sử dụng để tính tổng tiền các sản phẩm bằng cách nhân đơn giá x số lượng và cộng tất cả lại với nhau.
Cú pháp: =SUMPRODUCT(array1; array2; array3;…)
Trong đó:
Array1: là ô dữ liệu bắt buộc đầu tiên của thành phần mà bạn muốn nhân ( ví dụ đơn giá/ số lượng)
Array2, array3: những ô dữ liệu tiếp theo, lưu ý phải chọn cùng số hàng và cột để không lỗi #VALUE!
Ví dụ: Tính tổng tiền của các đơn hàng sau. Bạn nhập: =SUMPRODUCT(B2:B7,C2:C7)
Hàm SUMPRODUCT để tính tổng các tích
3. Hàm tính giá trị trung bình AVERAGE
Chức năng: cho ra con số trung bình của tổng các ô dữ liệu được chọn
Cú pháp: =AVERAGE(range)
Trong đó:
Range: là vùng dữ liệu cần tính giá trị trung bình
Ví dụ: Để tính điểm trung bình ba môn cho Nguyễn Văn B, ta nhập : =AVERAGE(B2:D2)
Dùng hàm AVERAGE để tìm giá trị trung bình
4. Hàm MIN,MAX
Chức năng: đúng như tên gọi, hàm này dùng để tìm ra giá trị lớn nhất, giá trị nhỏ nhất của 1 vùng dữ liệu
Cú pháp:
MIN(range)
MAX(range)
Trong đó:
Range: là vùng dữ liệu bạn cần tìm số nhỏ nhất (lớn nhất)
Ví dụ: Muốn tìm điểm cao nhất lớp, nhập =MAX(B2:D6)
Ví dụ về hàm MAX
5. Hàm LOGIC
5.1. Hàm điều kiện IF
Chức năng: dùng để kiểm tra xem ô dữ liệu có thoả điều kiện được đặt ra hay không, nếu đúng sẽ giả về giá trị đúng, sai sẽ trả giá trị sai.
Cú pháp: =IF(Logical_test;Value_if_true;Value_if_false)
Trong đó:
Logical_test: Điều kiện đặt ra cho dữ liệu
Value_if_true: giá trị trả về nếu thoả mãn điều kiện
Value_if_false: giá trị trả về nếu không thoả mãn điều kiện
Ví dụ: Muốn xem học sinh có đạt hay không dựa trên điều kiện điểm lớn hơn 8. Nhập =IF(B2>=8,”ĐẠT”,”KHÔNG ĐẠT”)
Dùng hàm IF để kiểm tra có thoả điều kiện hay không
5.2. Hàm AND
Chức năng: kiểm tra các ô dữ liệu bằng cách sẽ trả về giá trị đúng nếu tất cả các điều kiện đều được thoả mãn, nếu có bất kì điều kiện nào sai, kết quả sẽ trả về sai
Cú pháp: =AND(Điều kiện 1,Điều kiện 2,…)
Thường hàm AND được sử dụng kết hợp với hàm IF để xét nhiều mệnh đề điều kiện cùng lúc với cú pháp: =IF(AND(điều kiện 1,Điều kiện 2,…),“Giá trị 1”,“Giá trị 2”)
Ví dụ: Nếu bạn muốn tìm kết quả đạt hay không đạt dựa trên điều kiện điểm thực hành trên 7, lý thuyết trên 8. Cú pháp sẽ như sau:
Hàm AND kết hợp với hàm IF
5.3. Hàm OR
Chức năng: khác với hàm AND, hàm OR kiểm tra các ô dữ liệu nhưng sẽ trả về giá trị đúng nếu có bất kì điều kiện thoả mãn nhưng sẽ trả về giá trị sai nếu tất cả điều kiện đều không thoả mãn.
Cú pháp: =OR(logical 1, [logical 2], …)
Thường hàm OR cũng được kết hợp với hàm IF để kiểm tra các giá trị trong bảng dữ liệu
Ví dụ: Cũng tương tự như hàm IF, nếu bạn muốn tìm kết quả đạt hay không đạt dựa trên điều kiện điểm thực hành trên 7, lý thuyết trên 8. Cú pháp sẽ như sau:
Hàm OR kết hợp với hàm IF
6. Hàm tra cứu
6.1. Hàm VLOOKUP
Chức năng: dò tìm dữ liệu ở cột ngoài cùng bên trái của bảng theo hàng dọc và trả về dữ liệu tương ứng theo hàng ngang
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: bảng để dò tìm.
Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng, đếm từ trái qua phải.
Range_lookup: tìm kiếm chính xác hay tìm kiếm tương đối với bảng giới hạn, thường mặc định là 1.
Ví dụ: Để tìm định mức dựa vào bảng loại hàng, ta cần nhập cú pháp sau: =VLOOKUP(C7,$B$16:$D$20,2,0)
Hàm VLOOKUP dùng để tra cứu dữ liệu dựa trên bảng kèm
6.2. Hàm HLOOKUP
Chức năng: dò tìm dữ liệu ở cột ngoài cùng bên trái của bảng theo hàng ngang và trả về dữ liệu tương ứng theo hàng dọc
Cú pháp: =HLOOKUP(Lookup_value, Table_array, Row_index_ num, Range_lookup)
Trong đó:
Lookup_value: Giá trị cần tìm
Table_array: Bảng để dò tìm. ( Sau khi kéo bảng, nhấn F4)
Row_index_num: Số thứ tự của hàng lấy dữ liệu trong bảng cần dò tìm, đếm từ trên xuống dưới.
Range_lookup: tìm kiếm chính xác hay tìm kiếm tương đối với bảng giới hạn, thường mặc định là 1.
Ví dụ: Nếu bạn muốn tìm ngành học dựa trên mã số ngành và mã ưu tiên, ta nhập: =HLOOKUP(LEFT(D7,1),$B$13:$F$14,2,0)
Hàm HLOOKUP trả kết quả theo hàng dọc
6.3. Hàm INDEX
Chức năng: trả về giá trị hoặc tham chiếu tới một giá trị trong bảng hoặc phạm vi
Cú pháp: INDEX(array, row_num, [column_num])
Trong đó:
array: Phạm vi ô hoặc một hằng số mảng.
row_num: Thứ tự của hàng trong mảng chứa giá trị trả về
column_num: Thứ tự của cột trong mảng chứa giá trị trả về.
Ví dụ: Muốn tìm giá trị ở ô thứ 4 cột thứ 1 trong bảng sau, ta nhập cú pháp: =INDEX(B4:E13,4,1)
Ví dụ hàm INDEX
6.4. Hàm MATCH
Chức năng: tìm kiếm, xác định vị trí tương đối của một giá trị trong một vùng dữ liệu
Cú pháp: =Match(Lookup_value, Lookup_array, Match_type)
Trong đó:
Lookup_value: Giá trị cần tìm
Lookup_array: Vùng bạn muốn xác định vị trí của giá trị. Lưu ý phải chọn cùng cột hoặc hàng
Match_type: Xác định kiểu khớp
0: kết quả là vị trí tương đối của vùng dữ liệu chưa được sắp xếp
1: kết quả là vị trí của giá trị lớn nhất nhưng nhỏ hơn hoặc gần bằng giá trị cần tìm
-1: kết quả là vị trí của giá trị nhỏ nhất nhưng lớn hơn hoặc gần bằng giá trị cần tìm
Ví dụ: Để tìm vị trí của Lê Văn Kiên để tra dữ liệu, bạn nhập: =MATCH(F2;A1:A8;0)
Ví dụ về hàm MATCH trong Excel
7. Hàm Ngày Tháng
7.1. Hàm YEAR, MONTH, DAY
Chức năng: Hàm về thời gian thường được dùng để xử lý các dữ liệu có liên quan đến thời hạn, ngày tháng. Cụ thể như:
Hàm Day: để xác định ngày trong 1 vùng dữ liệu ngày/tháng/năm
Hàm Month: xác định tháng trong 1 vùng dữ liệu ngày/tháng/ năm
Hàm Year: xác định năm trong 1 vùng dữ liệu ngày/tháng/ năm
Cú pháp: =DAY(serial_number).
Thực hiện tương tự ở các hàm Month và Year.
Trong đó:
Serial_number là ngày mà bạn muốn tìm
Ví dụ: Muốn tìm ngày, tháng trong bản dữ liệu sau, ta chỉ cần nhập:
Hàm Day trong Excel
7.2. Hàm DATE
Chức năng: tạo các ngày hợp lệ từ ô dữ liệu ngày tháng năm
Cú pháp: =DATE(year, month, day)
Ví dụ:
Ví dụ về hàm DATE trong Excel
8. Hàm sử dụng trong văn bản
8.1. Hàm LEFT/RIGHT
Chức năng: cắt chuỗi kí tự từ bên trái/ bên phải văn bản ở ô được chọn
Cú pháp:
=LEFT(text,[num_chars])
=RIGHT(text,[num_chars])
Trong đó:
Text: là ô văn bản chứa kí tự muốn cắt
Num_chars: số lượng kí tự đếm từ trái/phải qua mà bạn muốn lấy
Ví dụ: Muốn tìm tên lớp của học sinh, biết rằng số lớp là số kí tự đầu của số báo danh, ta nhập như hình sau:
Hàm LEFT để tìm kí tự từ trái sang
8.2. Hàm MID
Chức năng: cắt số kí tự từ ô văn bản được chọn, tính từ vị trí con trỏ chuột của bạn
Cú pháp: =MID(text, start_num, num_chars)
Trong đó:
Text: ô văn bản muốn chứa kí tự muốn cắt
start_num: vị trí của kí tự đầu tiên mà bạn muốn lấy
num_chars: số lượng kí tự bạn muốn lấy
Ví dụ: Muốn tìm họ của tên học sinh dưới đây, chỉ cần nhập:
Hàm MID trong Excel
8.3. Hàm LEN
Chức năng: đếm ký tự ở ô văn bản bao gồm cả khoảng trắng
Cú pháp: =LEN(ô chứa chuỗi ký tự)
Ví dụ: Muốn đếm ô của học sinh Phạm Minh Nhật, ta nhập:
Hàm LEN để đếm số lượng kí tự
8.4. Hàm CONCATENATE (CONCAT)
Chức năng: nối hai hoặc nhiều ô văn bản thành một
Cú pháp: =CONCATENATE(text1, text2,…)
Trong đó:
Text 1,text2: là ô chứa văn bản
Ví dụ: Để gộp họ và tên của học sinh, ta nhập:
Hàm CONCATENATE để nối các ô dữ liệu
8.5. Hàm SUBSTITUTE
Chức năng: thay thứ văn bản trong ô văn bản được chọn
Cú pháp: =SUBSTITUTE(text, old_text, new_text, [instance_num])
Trong đó:
Text: ô văn bản muốn thay thế các ký tự trong đó.
Old_text: ô chứa văn bản cũ bạn muốn được thay thế.
New_text: văn bản mới mà bạn muốn thay thế cho văn bản cũ.
Instance_num: vị trí mà văn bản cũ xuất hiện mà bạn muốn thay bằng văn bản mới.
Ví dụ:
8.6. Hàm cắt khoảng trống TRIM
Chức năng: Cắt các ô khoảng trắng thừa khi dán văn bản khác vào bảng tính
Cú pháp: TRIM(text)
Trong đó:
Text là ô văn bản muốn loại bỏ các khoảng trắng
Ví dụ:
Hàm TRIM cắt ô trống
8.7. Hàm đổi chữ hoa, chữ thường LOWER, UPPER, PROPER
Chức năng: chuyển đổi các phần văn bản in thường thành chữ hoa và ngược lại.
Cú pháp:
=LOWER(text) để chuyển chữ hoa thành chữ thường
=UPPER(text) để chuyển chữ thường thành chữ hoa
=PROPER(text) để viết hoa các chữ cái đầu của văn bản
Ví dụ:
Các hàm điều chỉnh văn bản
III. Tổng kết
Excel là trợ thủ tính toán đắc lực vừa giúp tiết kiệm thời gian vừa cho ra kết quả chính xác. Nắm được các hàm cơ bản excel sẽ giúp bạn xử lý mọi công việc thêm nhanh chóng, hiệu quả hơn. Trên đây là các hàm excel cơ bản mà MEGA đã tổng hợp. Hi vọng những thông tin này sẽ hữu ích với bạn!
Xem thêm >>>
Các phím tắt cơ bản trong excel mà bạn cần nên biết
Cách tìm dữ liệu trong excel bằng hàm vlookup và hlookup nhanh chóng
Cách tạo biểu đồ Waterfall trong Excel hoàn chỉnh
copyright © mega.com.vn
