Excel là phần mềm bảng tính phổ biến nhất hiện nay, được sử dụng rộng rãi trong các ngành công nghiệp và lĩnh vực khác nhau. Nhưng việc sử dụng Excel đôi khi không đơn giản như vậy, đặc biệt là khi bạn gặp phải những tính toán phức tạp và công thức phức hợp. Trong bài viết này, EDUSA sẽ giới thiệu đến bạn full công thức Excel để bạn có thể áp dụng chúng vào công việc của mình một cách dễ dàng.
1. Khái niệm HÀM trong Excel
Hàm là những công thức được định sẵn trên phần mềm Excel (function) nhằm thực hiện một chức năng tính toán riêng biệt nào đó, hoặc để thực hiện các thao tác trên bảng tính, hoặc giúp đỡ cho người dùng ra một quyết định dựa trên những thông tin mà nó cung cấp.
2. Tổng hợp full công thức Excel cơ bản
2.1 Hàm VLOOKUP trong Excel
Hàm này là hàm dò tìm dữ liệu trong Excel. Ta sử dụng hàm nay khi tìm một giá trị nào đó, thông qua một giá trị khác. Nói 1 cách dễ hiểu hơn, khi có 1 giá trị tra cứu, chúng ta có thể thông qua nó để dò thông tin nào đó từ một mảng dữ liệu khác.
Cú pháp hàm VLOOKUP: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Trong công thức này, chúng ta có các tham số và ý nghĩa như sau:
| Lookup_value | Giá trị bạn muốn tra cứu. Giá trị bạn muốn tìm kiếm phải nằm trong cột đầu tiên của phạm vi ô mà bạn chỉ định trong đối số table_array.
Lookup_value có thể là một giá trị hoặc tham chiếu đến một ô. |
| Table_array | Phạm vi các ô mà VLOOKUP sẽ tìm kiếm cho lookup_value và giá trị trả về. Bạn có thể sử dụng phạm vi đã đặt tên hoặc bảng và bạn có thể dùng tên trong đối số thay vì tham chiếu ô.Cột đầu tiên trong phạm vi ô phải chứa lookup_value. Phạm vi ô cũng cần bao gồm giá trị trả về bạn muốn tìm. |
| Col_index_num | Số cột (bắt đầu bằng 1 cho cột ngoài cùng bên trái của table_array) có chứa giá trị trả về. |
| Range_Lookup | Một giá trị lô-gic sẽ xác định xem bạn muốn hàm VLOOKUP tìm kết quả khớp tương đối hay kết quả khớp chính xác:
– Kết hợp gần đúng – 1/True giả định cột đầu tiên trong bảng được sắp xếp thành số hoặc số thứ tự, sau đó sẽ tìm kiếm giá trị gần nhất. Đây sẽ là cách thức mặc định nếu bạn không xác định cách thức nào khác. – Kết hợp chính xác – 0/ các tìm kiếm false cho giá trị chính xác trong cột đầu tiên. |
2.2 Hàm SUMIF trong Excel
Hàm SUMIF trong Excel là một trong những hàm Excel cơ bản, hàm tính toán thường được dùng trong Excel. Để tính tổng trong Excel chúng ta sẽ dùng đến hàm SUM, nếu muốn thêm 1 điều kiện nhất định cho hàm tính tổng đó sẽ dùng hàm SUMIF.
Cú pháp hàm SUMIF: =SUMIF(range,criteria,[sum_range])
Cú pháp hàm SUMIF có các đối số sau đây:
- Range (Bắt buộc): Phạm vi ô bạn muốn được đánh giá theo tiêu chí. Các ô trong mỗi phạm vi phải là số hoặc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua. Phạm vi được chọn có thể chứa các ngày ở định dạng Excel tiêu chuẩn (ví dụ bên dưới).
- Criteria (Bắt buộc): 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 các ô nào. Ký tự đại diện có thể được bao gồm-dấu chấm hỏi (?) để khớp với bất kỳ ký tự đơn nào, dấu hoa thị (*) để khớp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.
- sum_range Tùy chọn. Các ô thực tế để cộng nếu bạn muốn cộng các ô không phải là các ô đã 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 (chính các ô đã được áp dụng tiêu chí).
- Sum_range phải có cùng kích cỡ và hình dạng theo phạm vi. Nếu không, hiệu suất có thể bị, và công thức sẽ tính tổng phạm vi ô bắt đầu với ô đầu tiên trong sum_range nhưng có cùng kích thước như phạm vi.
Xem thêm: Cách định dạng trang tính Excel chi tiết nhất
2.3 Hàm COUNTIF trong Excel
Hàm Countif là một trong các hàm thống kê, để đếm số lượng ô đáp ứng một tiêu chí; Ví dụ như để đếm số lần một thành phố nhất định xuất hiện trong danh sách khách hàng, đếm số lượng tài khoản lặp lại, hay đến số người cùng tên…
Cú pháp hàm COUNTIF: =COUNTIF(range, criteria)
Trong đó:
- Range: Vùng điều kiện mà ta muốn đếm, muốn thống kê.
- Criteria: Điều kiện hay tiêu chí mà ta muốn lấy kết quả
2.4 Hàm IF trong Excel
Hàm IF là một trong những hàm thông dụng và tiện ích nhất trong Excel, hàm này cho phép bạn thực hiện so sánh lô-gic giữa một giá trị với giá trị bạn mong muốn. Một câu lệnh IF khi thực hiện sẽ có hai kết quả. Kết quả đầu tiên là nếu so sánh của bạn là True (đúng), kết quả thứ hai là nếu so sánh của bạn là False (sai).
Cú pháp hàm IF trong Excel: =IF(logical_test,[value_if_true],[value_if_false])
Trong đó:
- Logical_test là điều kiện kiểm tra;
- Value_if_true là giá trị nếu điều kiện đúng;
- Value_if_false là giá trị nếu điều kiện sai.
2.5 Hàm INDEX trong Excel
Hàm INDEX trong Excel là một Function (chức năng) được sử dụng nhằm mục đích trả về một ô tham chiếu trong một mảng hoặc một dải nhất định. Nói cách khác, bạn sử dụng INDEX khi bạn biết (hoặc có thể tính toán) vị trí của một phần tử trong dải (bảng tính Excel) và bạn muốn nhận giá trị thực của phần tử đó.
Cú pháp cơ bản của hàm INDEX: =INDEX(array;row_num;[column_num])
Trong công thức này:
- array: vùng ô hoặc một hàng số mảng nào đó;
- row_num: chọn hàng trong mảng từ đó trả về một giá trị;
- column: chọn cột trong mảng từ đó trả về một giá trị.
Xem thêm: Bí kíp học Excel cơ bản đến nâng cao hiệu quả nhất
2.6 Hàm ROUND trong Excel
Hàm ROUND trong Excel là hàm làm tròn số cho các số có đơn vị lẻ, việc làm tròn số đến mức độ nào sẽ tùy thuộc vào mong muốn của người sử dụng hàm khi kết quả xuất ra.
Cú pháp cơ bản của hàm ROUND: =ROUND(number,num_digits)
Trong công thức này:
- number (Bắt buộc): Số mà bạn muốn làm tròn;
- num_digits (Bắt buộc): Số chữ số mà bạn muốn làm tròn số tới đó, có các trường hợp sau:
+ Nếu num_digits lớn hơn 0 (không), thì số được làm tròn tới số vị trí thập phân được chỉ định;
+ Nếu num_digits bằng 0, thì số được làm tròn tới số nguyên gần nhất;
+ Nếu num_digits nhỏ hơn 0, thì số được làm tròn sang bên trái dấu thập phân;
+ Để luôn luôn làm tròn lên (ra xa số không), hãy dùng công thức ROUNDUP;
+ Để luôn luôn làm tròn xuống (về phía số không), hãy dùng công thức ROUNDDOWN;
+ Để làm tròn một số tới một bội số cụ thể (ví dụ, làm tròn tới 0,5 gần nhất), hãy dùng hàm MROUND.
2.7 Hàm COUNT trong Excel
Hàm COUNT là một công thức, chức năng (Function) trong Excel dùng để đếm các ô có chứa số trong một bảng tính nhất định. Sử dụng hàm COUNT để biết số mục nhập trong trường số nằm trong phạm vi hoặc dãy số.
Cú pháp hàm COUNT: =COUNT(value1,[value2], …)
Cú pháp hàm COUNT có các đối số sau đây:
- value1 (Bắt buộc): Mục đầu tiên, tham chiếu ô hoặc phạm vi trong đó bạn muốn đếm số.
- value2, … (Tùy chọn): Tối đa 255 mục, tham chiếu ô hoặc phạm vi bổ sung trong đó bạn muốn đếm số.
2.8 Hàm SUBTOTAL trong Excel
Hàm SUBTOTAL trong Excel có nhiệm vụ để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong trường hợp này, “tổng phụ” (subtotal) không phải tổng các con số trong 1 dãy ô xác định.
Cú pháp hàm SUBTOTAL: =SUBTOTAL(function_num,ref1,[ref2],…)
Trong công thức trên, có các đối số sau:
- Function_num: con số xác định chức năng thực hiện
- Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ. Cần phải có Ref 1, từ Ref 2 đến 254 là tuỳ chọn.
Số xác định chức năng thực hiện có 2 loại sau:
- 1 -11 bỏ qua các ô đã được lọc ra, nhưng để lại các hàng được ẩn thủ công
- 101 – 111 bỏ qua các ô ẩn – đã lọc ra và ẩn thủ công.
Xem thêm: Cách định dạng trang tính Excel chi tiết nhất
2.9 Hàm HLOOKUP trong Excel
Hàm HLOOKUP là một công thức trong Excel dùng để tìm kiếm một giá trị trong hàng trên cùng của bảng hoặc mảng giá trị, sau đó trả về giá trị trong cùng cột từ hàng mà bạn chỉ định trong bảng hoặc mảng.
Cú pháp của hàm HLOOKUP: =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
Trong đó chúng ta có các đối số với ý nghĩa như sau:
| Đối số | Điều kiện | Ý nghĩa |
| Lookup_value | Bắt buộc | Giá trị cần tìm trong hàng thứ nhất của bảng. Lookup_value có thể là một giá trị, tham chiếu hoặc chuỗi văn bản |
| Table_array | Bắt buộc | Một bảng thông tin để tìm kiếm dữ liệu trong đó. Hãy dùng tham chiếu tới một phạm vi hoặc một tên phạm vi |
| Row_index_num | Bắt buộc | Số hàng trong table_array có giá trị kết hợp sẽ được trả về.
Row_index_num là 1 thì trả về giá trị hàng đầu tiên trong table_array, row_index_num là 2 thì trả về giá trị hàng thứ hai trong table_array, v.v. Nếu row_index_num nhỏ hơn 1, hàm HLOOKUP trả về #VALUE! giá trị lỗi; Nếu row_index_num lớn hơn số hàng trên table_array, hàm HLOOKUP trả về #REF! . |
| Range_lookup | Tùy chọn | Một giá trị lô-gic cho biết bạn có muốn HLOOKUP tìm thấy một kết quả khớp chính xác hay kết quả khớp tương đối. Nếu đối số này là
TRUE hoặc được bỏ qua, thì hàm sẽ trả về kết quả khớp tương đối. Nói cách khác, nếu không tìm thấy một kết quả khớp chính xác thì hàm sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn lookup_value. Nếu đối số này là FALSE, hàm HLOOKUP sẽ tìm một kết quả khớp chính xác. Nếu không tìm thấy kết quả khớp chính xác, hàm sẽ trả về giá trị lỗi #N/A. |
2.10 Các hàm trong Excel khác
Trong Excel còn rất nhiều hàm thông dụng khác ngoài các hàm đã nêu chi tiết ở trên, bao gồm:
| STT | Tên Hàm | Ý nghĩa |
| 1 | AVERAGE | Hàm tính trung bình cộng của một dãy số |
| 2 | AVERAGEIF | Hàm tính trung bình cộng của một dãy số theo một điều kiện cho trước |
| 3 | INT | Hàm làm tròn số xuống tới số nguyên gần nhất |
| 4 | MOD | Hàm trả về số dư sau khi chia một số cho ước số |
| 5 | AND | Hàm lô-gic, để xác định xem liệu tất cả các điều kiện trong một kiểm tra có là TRUE hay không |
| 6 | COUNTA | Hàm đếm số ô không trống trong một phạm vi |
| 7 | DATEDIF | Hàm tính toán số ngày, tháng hoặc năm giữa hai ngày |
| 8 | VALUE | Hàm chuyển đổi chuỗi văn bản thành giá trị số |
| 9 | FIND | Hàm tìm vị trí của một ký tự hay chuỗi phụ trong một chuỗi văn bản |
| 10 | INDIRECT | Hàm tham chiếu gián tiếp các ô, dãy, các trang tính, bảng tính |
| 11 | OFFSET | Hàm trả về tham chiếu đến một vùng nào đó |
| 12 | RIGHT | Hàm tách chuỗi ký tự bên phải |
| 13 | TEXT | Hàm chuyển đổi giá trị số sang chuỗi văn bản ở một định dạng cụ thể |
| 14 | WEEKDAY | Hàm tìm các thứ trong tuần tương ứng với một ngày |
| 15 | CONCATENATE | Hàm nối các đoạn văn bản khác nhau lại hoặc kết hợp các giá trị từ một vài ô vào một ô |
3. Trung tâm đào tạo các khóa học Excel uy tín – EDUSA
EDUSA là một trong những đơn vị luyện thi các chứng chỉ tin học chất lượng và uy tín nhất hiện nay. Trở thành học viên của EDUSA bạn sẽ được đào tạo và giảng dạy mọi kiến thức để có thể thi đậu chứng chỉ 100%.
Toàn bộ các giáo trình, video bài giảng đều được chọn lọc và biên soạn kỹ càng đảm bảo tính độc nhất và sát với đề thi thật. Hơn hết, bạn sẽ được trải nghiệm cảm giác như thi thật để giúp bạn làm quen với cấu trúc đề và áp lực về thời gian. Từ đó, bạn sẽ không còn bỡ ngỡ khi đi thi chính thức.
Một số ưu điểm của trung tâm EDUSA:
- Cam kết giúp các học viên đạt chứng chỉ ngay từ lần thi đầu tiên.
- Các bài giảng được đội ngũ giảng viên hệ thống lại kiến thức chi tiết, cụ thể, ngắn gọn, dễ hiểu phù hợp với các trình độ.
- Học bất cứ thời gian nào rảnh, tổng thời lượng học kéo dài trong 1-2 giờ
- Lộ trình học Excel cụ thể, chi tiết giúp học viên nắm vững kiến thức sau mỗi buổi học.
- Cam kết học và thi lại miễn phí 100% nếu học viên thi không đạt chứng chỉ.
- Hỗ trợ đăng ký thi, nhận chứng chỉ và giao chứng chỉ đến tận nhà học viên.
- Đội ngũ giảng viên chuyên nghiệp, nhiệt tình.
Xem thêm: Khóa học Excel offline chất lượng tại EDUSA
Xem thêm: Khóa học Excel Online chất lượng tại EDUSA
4. Câu hỏi thường gặp
4.1 Cách sử dụng các hàm trong Excel như thế nào?
Các bạn theo dõi chi tiết cách sử dụng các hàm trong Excel ở phía trên bài viết nhé.
4.2 Các hàm Excel cơ bản là hàm nào?
Các hàm Excel cơ bản gồm một số hàm như: Hàm VLOOKUP, hàm LEFT, hàm LEN,…
4.3 Khóa học Excel tại EDUSA có bao đậu không?
Khóa học của EDUSA đều sát với đề thi thật, bài tập và đề thi thử được cập nhật thường xuyên. Với chính sách cam kết đầu ra 100% bằng văn bản cụ thể sẽ giúp học viên yên tâm trong suốt quá trình học tại đây.
5. Kết luận
Trong bài viết này, EDUSA đã giới thiệu đến bạn full công thức Excel. Hy vọng rằng thông tin này sẽ giúp bạn trở thành một người thành thạo trong việc sử dụng công thức Excel để giải quyết các vấn đề phức tạp.
Để lại thông tin cần tư vấn