Nhắc tới excel chúng ta không thể không nhắc tới các phép tính toán trên bảng dữ liệu, những công thức này thực hiện những phép tính rất chính xác như cộng, nhân, phép so sánh. Có rất hiều hàm số để sử dụng cho việc tính toán của bạn, tuy nhiên dưới đây chúng tôi giới thiệu tới bạn các hàm cơ bản trong excel thường xuyên sử dụng nhất đồng thời là những ví dụ để bạn có thể hiểu rõ hơn.
1. Các thành phần của một hàm cơ bản trong excel
1.1. Thành phần của một hàm cơ bản trong excel
Một công thức trong excel luôn luôn được bắt đầu bằng một dấu bằng (=), phía sau nó có thể là số, các toán tử hay các hàm excel.
Ví dụ:
- = 3*4+9 : Trong công thức này biểu thị 3 nhân 4 sau đó cộng thêm với 8.
- =A1*A4-A6: Đây là các toán tử thể hiện giá trị ở ô A1 nhân với giá trị ô A4 sau đó trừ đi giá trị ở ô A6.
- =Sum(A1:A5): Đây là một hàm tính tổng, hàm Sum tính tổng giá trị các ô từ A1 đến A5.
- =TODAY(): Hàm TODAY trả về giá trị của ngày hiện tại.
- = UPPER(“thanh nga”): Hàm UPPER dùng để chuyển đổi văn bản chữ thường thành văn bản chữ hoa, kết quả chuyển ở thânh từ “thanh nga” sẽ thành “THANH NGA”.
Công thức hàm excel cơ bản có thể bao gồm một trong số tất các các thành phần như hàm, tham chiếu, toán tử và hằng số.
Ví dụ : = Pi()*A1 ^ 2
Trong đó:
- Hàm Pi(): Trả về giá trị của số pi là 3.14.
- Tham chiếu A1: Trả về giá trị trong ô A1.
- Hằng số : Giá trị hoặc văn bản được nhâp trực tiếp vào công thức, trong trường hợp này thì hằng số là 2.
- Toán tử: Toán tứ * (dấu sao) sử dụng để nhân các số với nhau và dấu ^ (mũ) để nâng một số hay một giá trị lên bậc lũy thừa nào đó, trong ví dụ trên là lũy thừa bậc 2.
Các hàm cơ bản trong excel
1.2. Cách nhập công thức trong excel
- Đặt con trỏ vào ô cần nhập công thức và hiển thị kết quả.
- Gõ dấu = (bằng).
- Nhập biểu thức mà bạn muốn tính.
- Sau khi nhập xong bấm enter, kết quả của phép tính sẽ được hiển thị trong ô bạn vừa nhập biểu thức.
- Trong trường hợp công thức bị lỗi sẽ hiển thị bắt đầu bằng một dấu #.
1.3. Cách nhập miền tham chiếu bằng con trỏ
- Đặt con trỏ chuột vào ô cần hiển thị kết quả công thức.
- Nhập công thức để tính trên một hay miền giá trị.
- Sử dụng các phím mũi tên trên bàn phím để di chuyển con trỏ ô từ ô tham chiếu đầu tiên, công thức sẽ tiến hành kiểm tra và giá trị hiện hành sẽ được đưa vào công thức để tính toán.
- Gõ phép +.
- Tiếp tục sử dụng các phím mũi tên để di chuyển con trỏ ô tới ô tham chiếu thứ 2, nếu bạn tính toán trong một vùng dữ liệu thì nhấn giữ phím shift đồng thời di chuyển mũi tên tới tất cả các ô cần tính.
- Sau khi đã chọn hết các ô cho công thức hãy nhấn enter để có kết quả chính xác nhất.
2. Danh sách các hàm excel thông dụng
Dưới đây là các hàm excel thông dụng chúng tôi liệt kê với cách dùng, cú pháp và ví dụ để bạn tham khảo:
2.1 Hàm đếm và tính tổng
2.1.1.Hàm đếm COUNT
Với một bảng 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ì mất thời gian ngồi đếm một cách thủ công có khi lại không chính xác thì bạn có thể sử dụng hàm COUNT trong excel.
Cú pháp hàm: =COUNT(giá trị 1, giá trị 2,…)
Trong đó: Giá trị 1, giá trị 2, … là những ô hoặc vùng cần đếm.
Lưu ý khi sử dụng hàm COUNT:
- Hàm COUNT được sử dụng để đếm các tham số thuộc dạng số, ngày tháng hoặc những tham số được trình bày ở dạng văn bản của số.
- Trong mảng tham chiếu được lựa chọn, các tham số là ô trống, giá trị logic, văn bản hoặc mỗi giá trị lỗi thì hàm COUNT sẽ không đếm.
Ví dụ: Bạn cần đếm giá trị từ ô C1 tới C10 thì hãy gõ =COUNT(C1:C10).
Cách sử dụng hàm COUNT trong excel
2.1.2. Hàm đếm COUNTIF với điều kiện cụ thể
Với loại hàm này bạn có thể đếm với điều kiện được định sẵn, vì với cách đếm thông thường ta không thể nào lọc được các điều kiện cần thiết.
Cú pháp: =COUNTIF(range, criteria).
Trong đó:
- Range là vùng dữ liệu chứa dữ liệu cần đếm.
- Criteria: Là điều kiện đếm.
Ví dụ: Đếm số dòng có giá trị lớn hơn 20, ta có thể sử dụng hàm COUNTIF sau đây: =COUNTIF(C10:C10,”>20”), kết quả được thể hiện ở hình bên dưới:
Hàm COUNTIF trong excel
2.2.1.Hàm tính tổng SUM
Đây có thể nói là một trong các hàm excel cơ bản trong kế toán và cũng là hàm đầu tiên mà bấy 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 sẽ giúp bạn tính tổng các đối số trong file, chúng thực hiện khá nhanh.
Cú pháp: =SUM(Number1, number2)
Trong đó: number1, number 2 là một đối số mà bạn muốn tính tổng cho chúng trong bảng tính.
Trong các giá trị logic TRUE được coi là 1, FALSE là 0, nếu đối số của bạn là một mảng hay tham chiếu thì chỉ có các giá trị số trong mảng đó hoặc tham chiếu đó mới được tính, các giá trị còn lại bị hàm bỏ qua.
Ví dụ: Bạn muốn cộng các con số trong ô C2 với C10 sau đó hiển thị kết quả ô C13. Để thực hiện bạn chỉ cần di chuyển con trỏ chuột đến ô C13 sau đó gõ cụm từ =SUM như hình dưới, sau đó chọn hàm =SUM vừa xuất hiện ở danh sách nổi lên:
Tiếp theo bạn nhấp phím Ctrl cùng với thao tác chọn vào ô C2 và C10 rồi cuối cùng nhấp phím enter. Kết quả sẽ hiển thị ở ô C13 mà bạn đặt con trỏ chuột, nếu không bạn viết luôn công thức =SUM(C2,C10) vào ô C13 sẽ cũng cho kết quả tại ô này khi bấm enter.
Kết quả khi sử dụng hàm SUM
2.2.2.Hàm Sumif tính tổng có điều kiện
Hàm SUMIF là hàm tính tổng trong excel chúng khác với hàm sum đó là có kèm điều kiện được sử dụng phổ biến nhất trong việc thống kê, đồng thời cũng lọc dữ liệu cần thiết để thực hiện việc tính tổng.
Cú pháp: =SUMIF(range, criteria, sum_range)
Trong đó:
- Range: Vùng chứa dữ liệu cần được tính tổng.
- Criteria: Điều kiện để tính tổng.
- Sum_range: Vùng dữ liệu cần được tính tổng.
Ví dụ: Tính tổng các giá trị lớn hơn 20 trong bảng dưới đây, để tính tổng có thêm điều kiện này hãy sử dụng hàm SUMIF.
Tại ô C13 bạn điền công thức tính tổng có điều kiện để excel thực hiện tính như sau =SUMIF(C1:C10,”>20”)
Cách sử dụng hàm SUMIF trong excel
2.2.3.Hàm Dsum tính tổng có các điều kiện trong excel
Trong excel có nhiều hàm tính tổng như Sum, Sumif ngoài ra còn có hàm tính tổng kèm các điều kiện khác.
Cú Pháp: =Dsum(Database, Field, Criteria)
Trong đó:
- Database: Bảng dữ liệu cần tính tổng bao gồm cả cột tiêu đề.
- Field : Trường cần tính tổng.
- Criteria: Điều kiện để tính tổng dữ liệu.
Hàm tính tổng ở trường “Field” theo điểu kiện “Criteria” trong bảng dữ liệu “Database” cho sẵn.
Ví dụ : Tính tổng tiền của hãng điện thoại Sam Sung đã bán được.
Đặt điều kiện trường tên có giá trị bằng “Sam Sung”
Như vậy, dựa theo cú pháp của hàm dsum ta được công thức tính:
=Dsum(B9:G15,G9,E5:E6)
Trong đó:
- B9:G15: hàm sẽ thực hiện thao tác trong dùng dữ liệu từ B9 đến G15.
- G9: TÍnh tổng giá trị trong cột G8 sao cho các giá trị trong cột này tương ứng với điều kiện có tên là Sam Sung và tương ứng với vùng điều kiện E5:E6.
Ta được kết quả ở hình dưới đây:
Kết quả khi sử dụng hàm Dsum kết hợp nhiều điều kiện trong excel
Như vậy, với hàm DSUM bạn có thể tính tổng và lọc các điều kiện chi tiết hơn để được kết quả mà bạn mong muốn.
2.2.Hàm tính giá trị trung bình AVERAGE
2.2.1.Hàm AVERAGE – tính giá trị trung bình
Hàm Average trong excel là hàm giúp trả về các giá trị trung bình cộng của một dãy số, với cú pháp đơn giản của hàm ta có thể tính được trung bình cộng của dãy số một cách khá đơn giản và nhanh chóng.
Cú pháp: =AVERAGE(number1,[number2],…)
Trong đó:
- 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 255.
Lưu ý:
- Đối số có thể là số hoặc tên, phạm vi hoặc tham chiếu ô có chứa số.
- Các giá trị logics và biểu thị số dạng văn bản mà bạn gõ trực tiếp vào danh sách các dối số sẽ được đếm.
- Nếu một đối số tham chiếu ô hoặc phạm vi có chứa giá trị logic, văn bản hay ô trống thì những giá trị này sẽ bị bỏ qua, tuy nhiên những ô có giá trị 0 sẽ được tính.
- Những đối số là văn bản hay giá trị lỗi không thể chuyển đổi thanh số sẽ khiến xảy ra lỗi.
- Nếu bạn muốn bao gồm các giá trị logic và dạng biểu thị số bằng văn bản trong một ham chiếu như một phần phép tính, hãy sử dụng hàm AVERAGEA.
Những lỗi thường xảy ra khi sử dụng hàm AVERAGE
- #p/0!: lỗi này xảy ra nếu các giá trị được tính trung bình không phải là số.
- #VALUE: Lỗi xảy ra nếu bất kỳ đối số nào được cung cấp trực tiếp mà hàm AVERAGE không thể diễn giải các giá trị đó là giá trị số, bạn khắc phục bằng cách xây dựng một công thức bỏ qua công thức vùng tham chiếu chứa lỗi để tính trung bình các giá trị bình thường còn lại.
Ví dụ: Tính trung bình các giá trị từ ô C1 tới ô C10.
Theo cú pháp tính giá trị trung bình ta được công thức dưới đây:
=AVERAGE(C1:C10)
Kết quả sẽ ở dưới hình sau:
Kết quả khi sử dụng hàm tính trung bình cộng AVERAGE
Kết quả đưa ra trong C13 là con số có giá trị trung bình giữa các ô từ C1 tới C10, ngoài cách điền trực tiếp công thức bạn cũng có thể sử dụng con trỏ chuột kéo thả và đánh dấy 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ề liên tiếp nhau.
2.2.2.Hàm AVERAGEIFS – Tính giá trị trung bình nhiều điều kiện
Hàm AVERAGEIFS giúp bạn tính giá trị trung bình cộng của tất các các ô đáp ứng nhiều tiêu chí.
Cú pháp: =AVERAGEIFS (average_range,criteria_range1,criteria1,criteria_range2,criteria2…)
Trong đó:
- average_range: Các ô cần tính giá trị trung bình.
- criteria_range1, criteria_range2: các vùng cần xác định điều kiện tương ứng.
- criteria1, criteria2: là các điều kiện cho trước với từng criteria_range ở phía trên.
Ví dụ: Có bảng điểm của bài kiểm tra các sinh viên như dưới đây:
Tính điểm trung bình của bài kiểm tra thứ 1 đạt từ 70 tới 90 dành cho tất cả các sinh viên.
Ta có công thức tính như sau:
=AVERAGEIFS(B2:B5, B2:B5, “>70”, B2:B5, “<90”)
Ta được kết quả ở bảng dưới đây:
Kết quả khi sử dụng hàm tính trung bình kết hợp nhiều điều kiện AVERAGEIFS
Kết quả được ghi ở cột B8, những điểm được đánh dấy “chưa hoàn thành” sẽ không được đưa vào phép tính vì nó không phải là giá trị số.
2.3.Hàm đếm ô chứa dữ liệu COUNTA
Hàm COUNTA trong excel có chức năng dùng để đếm số ô có chứa nội dung bất kỳ bao gồm cả chữ số và chữ cái hay biểu tượng, nói cách khác chúng để đếm các ô chứa dữ liệu (không trống).
Cú pháp: =COUNTA (value1, [value2],…)
Trong đó:
- Value1: đối số bắt buộc. Đây là đối số đầu tiên, nó đại diện cho giá trị mà bạn cần đếm, gọi đầy đủ là đối số 1, và nó bắt buộc phải có.
- Value2: đối số tùy chọn. Đây là những đối số tiếp mà đại diện cho giá trị bạn muốn đếm, nó được gọi đầy đủ là đối số 2, cho phép giá trị trong khoảng 255 đối số, tức là bạn có thể lựa chọn 255 đối số cho một phép tính.
Ví dụ: Đếm các ô chứa dữ liệu trong bảng.
Điền cú pháp: =COUNTA(A1:A7) vào trong ô A9. Ta có kết quả được hiển thị trong bảng dưới:
Cách sử dụng hàm COUNTA trong excel
2.4. Hàm tính tổng các phần tử mảng trong excel – SUMPRODUCT
Là hàm tính tổng các tích của các phần tử trong một mảng bao tồm một dãy các giá trị liên tiếp, nhờ đó mà bạn có thể tính được tổng các tích phần tử của một hay nhiều mảng.
Cú pháp: =SUMPRODUCT(Array1, [Array2], [Array3],…[ArrayN])
Trong đó:
- Array1: mảng thứ nhất cần tính.
- [Array2]: mảng thứ 2 cần tính.
- [Array3] : mảng thứ 3 cần tính.
- [ArrayN]: mảng thứ N cần tính.
Ví dụ: Sử dụng hàm SUMPRODUCT để tính tổng số tiền đã tiêu
Cụ thể ta sử dụng hàm SUMPRODUCT sẽ thực hiện phép tính: (2*1000) + (4*250) + (4*100) + (2*50)
Cú pháp =SUMPRODUCT(B2:B5,C2:C5).
Cách sử dụng hàm SUMPRODUCT
2.5. Hàm logic
2.5.1. Hàm IF – hàm kiểm tra điều kiện
Hàm IF giúp bạn kiểm tra giá trị xem điều kiện có được đáp ứng không, nếu đúng chúng sẽ trả về giá trị đúng và sai sẽ trả về giá trị sai.
Cú pháp: =IF (điều kiện, giá trị 1, giá trị 2)
Trong đó:
- Điều kiện: là điều kiện chúng ta đêm đi so sanh với vùng
- Giá trị 1: là giá trị trả về nếu hàm so sánh là đúng.
- Giá trị 2: là giá trị trả về nếu hàm so sánh là sai.
Ví dụ: Hãy kiểm tra kem trong ô A có thỏa mãn điều kiện lớn hơn 10 không? Nếu thỏa mãn thì trả về giá trị Correct trong ô C1, ngược lại nếu không thỏa mãn trả về giá trị Incorrect.
Tại ô C1 bạn nhập vào công thức =IF(A1>10,”Correct”,”Incorrect”), ta có kết quả ở hình bên dưới:
Sử dụng hàm if trong excel để lọc các dữ liệu thỏa mãn các điều kiện
2.5.1.Hàm AND
Hàm sẽ trả về TRUE nếu các đối số chỉ định của hàm mang giá trị đúng, còn lại thì đều trả về kết quả FALSE nếu có bất kỳ giá trị nào mang số chỉ định là False.
Cú pháp: =AND(Logical1; [Logical2]; [Logical3];…).
Trong đó:
- Logical 1: số chỉ định hàm Thứ 1 (bắt buộc phải có).
- Logical 2: số chỉ định hàm Thứ 2 (tuỳ chọn).
- Logical 3: số chỉ định hàm Thứ 3 (tuy chọn).
- Logical N: số chỉ định hàm Thứ N ( tuỳ chọn).
Hàm AND chứa 256 đối và hàm sẽ bỏ qua các ô có giá trị là văn bản hoặt rỗng.
Ví dụ: Hãy kết hợp kiểm tra 2 điều kiện A1 lớn hơn 10, B1 lớn hơn 5 nếu cả 2 thỏa mãn sẽ trả về Correct, ngược lại trả về Incorrect. Tại ô D1 bạn nhập =IF(AND(A1>10,B1>5),”Correct”,”Incorrect”) Kết quả được thể hiện ở hình dưới đây.
Sử dụng hàm AND trong excel
Như vậy, Hàm AND trả về giá trị sai vì ô B1 nhỏ hơn 5, do đó hàm IF trả về giá trị sai tương ứng ở đây là Incorrect.
2.6. Hàm Vlookup – Hàm tra cứu và tham chiếu
Hàm Vlookup là hàm tra cứu và tham chiếu giúp hỗ tợ tìm kiếm nội dung bảng tính theo cột, hàm này có ai giá trị tìm kiếm đó là tìm kiếm ở giá trị tương đối và tìm kiếm ở giá trị tuyệt đối giúp việc tìm kiếm, lọc các dữ liệu được nhanh và hiệu quả hơn.
Cú pháp hàm vlookup: VLOOKUP(lookup_value;table_array;col_index_num;[range_lookup]).
Trong đó:
- Lookup_value: giá trị dùng để do tìm.
- Table_array: vùng chứa dữ liệu trong bản phụ mà chúng ta cần tìm VD(A6:D10).
- Col_index_num: lấy giá trị ở cột bao nhiêu trong bản phụ.
- Range_lookup: dùng cho bạn muốn tìm chính xác hay tương đối, giá trự True tương ứng với 1, False tương ứng với giá trị 0.
Ví dụ: Bài tập dưới đây cho bạn biết cách sử dụng hàm Vlookup nhằm trả về giá đĩa phanh là 85.73.
Cú pháp: VLOOKUP(D13,B2:E11,3,FALSE)
Trong đó:
- D13 là lookup_value hay giá trị bạn muốn tra cứu.
- B2 tới E11 (được tô sáng bằng màu vàng trong bảng) là table_array hay dải ô chứa giá trị tra cứu.
- 3 là col_index_num hay số cột trong table_array chứa giá trị trả về. Ở ví dụ này, cột thứ ba trong mảng bảng là Giá Linh kiện, nên đầu ra công thức sẽ là một giá trị từ cột Giá Linh kiện.
- FALSE là range_lookup, do đó giá trị trả về sẽ là một kết quả chính xác.
- Đầu ra của công thức VLOOKUP là 85,73, giá của Đĩa phanh.
2.7. Hàm Date – Hàm trả về giá trị ngày tháng năm
Hàm date là hàm tính toán trả về kết quả ngày tháng năm dựa trên định dạng tương ứng ngày tháng năm đã được nhập với ngày tháng năm tiêu chuẩn, ngày thứ nhất của hệ thống này đó là ngày 1 tháng 1 năm 1900.
Cú pháp: =DATE(year, month, day)
Trong đó:
- Year: Đây là giá trị đối số của năm. Nó gồm 4 giá trị trong khoảng 1 – 4 chữ số (0 – 9999). Năm này sẽ phụ thuộc vào năm hệ thống máy tính bạn đang dùng. Như đã nói thì năm mặc định và bắt buộc đầu tiên là 1900.
- Month: giá trị đối số của tháng. Giá trị month có thể là âm hoặc dương đại diện cho tháng trong 1 năm kể từ tháng thứ nhất đến tháng mười hai. Đây cũng là giá trị bắt buộc.
- Day: giá trị đối số của ngày: Day là giá trị bắt buộc, có thể là âm hoặc dương đại diện tương ứng cho ngày thứ nhất đến ngày thứ 31 trong một tháng.
Ví dụ: Tính ngày kỷ niệm năm nay thành lập, ngày thành lập là 2/4/2015
Đối với văn phòng, các hàm cơ bản trong excel là hành trang không thể thiếu khi bắt đầu một công việc, hay học tập, vì thế hãy tham khảo và bỏ túi bài viết trên để giúp được nhiều hơn trong công việc của bạn nhé.
