Nắm vững các công thức Excel cơ bản là rất quan trọng đối với người mới bắt đầu để trở nên thành thạo trong phân tích tài chính. Microsoft Excel được coi là phần mềm tiêu chuẩn công nghiệp trong phân tích dữ liệu. Chương trình bảng tính của Microsoft cũng là một trong những phần mềm được các chủ ngân hàng đầu tư và nhà phân tích tài chính ưa thích nhất trong xử lý dữ liệu, lập mô hình tài chính và trình bày.
Được thiết kế chủ yếu như một chương trình bảng tính, Microsoft Excel cực kỳ mạnh mẽ và linh hoạt khi tính toán các con số hoặc giải các bài toán và kỹ thuật. Nó cho phép bạn tính tổng hoặc tính trung bình một cột số trong nháy mắt. Ngoài ra, bạn có thể tính toán lãi kép và trung bình có trọng số hoặc lập lịch làm việc tối ưu cho nhân viên của bạn. Tất cả điều này được thực hiện bằng cách nhập công thức vào ô.
Hướng dẫn này sẽ cung cấp một cái nhìn tổng quan và danh sách một số chức năng Excel cơ bản, nhằm mục đích dạy cho bạn các yếu tố cần thiết của các hàm Excel và chỉ ra cách sử dụng các công thức cơ bản trong Excel.
Trước khi cung cấp danh sách công thức Excel cơ bản, hãy xác định các thuật ngữ chính để đảm bảo rằng chúng ta đang ở trên cùng một trang. Vì vậy, chúng ta gọi một công thức Excel và hàm Excel là gì?
Công thức là một biểu thức tính toán các giá trị trong một ô hoặc trong một dải ô.
Ví dụ: =B1+B2+B3 là một công thức cộng các giá trị trong các ô từ B1 đến B3 (tìm tổng của phạm vi giá trị từ ô B1 đến ô B3).
Hàm là một công thức định sẵn đã có sẵn trong Excel. Họ loại bỏ việc nhập công thức thủ công tốn nhiều công sức trong khi đặt cho chúng những cái tên thân thiện với con người. Các hàm thực hiện các phép tính cụ thể theo một thứ tự cụ thể dựa trên các giá trị đã chỉ định, được gọi là đối số hoặc tham số.
Ví dụ: thay vì chỉ định từng giá trị được tính tổng như trong công thức trên, bạn có thể sử dụng hàm SUM để cộng một phạm vi ô:=SUM(B1:B3). Hàm tính tổng tất cả các giá trị từ B1 đến B3.
Bạn có thể tìm thấy tất cả các hàm Excel có sẵn trong Thư viện Hàm trên tab Công thức:
Có hơn 400 hàm trong Excel và con số này đang tăng lên theo từng phiên bản. Tất nhiên, gần như không thể ghi nhớ tất cả chúng và bạn thực sự không cần phải làm vậy. Trình hướng dẫn Hàm sẽ giúp bạn tìm hàm phù hợp nhất cho một tác vụ cụ thể.
Nhấp vào tên của hàm sẽ biến nó thành một siêu liên kết màu xanh lam, siêu liên kết này sẽ mở ra chủ đề Trợ giúp cho hàm đó.
Lưu ý:
● 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.
Năm cách tiết kiệm thời gian để chèn dữ liệu vào Excel
Khi phân tích dữ liệu, có năm cách phổ biến để chèn các công thức Excel cơ bản. Mỗi chiến lược đi kèm với lợi thế riêng của mình. Do đó, trước khi đi sâu hơn vào các công thức chính, chúng tôi sẽ làm rõ các phương pháp đó để bạn có thể tạo quy trình làm việc ưa thích của mình dễ dàng hơn.
1. Chèn đơn giản: Gõ công thức bên trong ô
Nhập công thức vào một ô hoặc thanh công thức là phương pháp đơn giản nhất để chèn các công thức Excel cơ bản. Quá trình này thường bắt đầu bằng cách nhập một dấu bằng, theo sau là tên của một hàm Excel.
Excel khá thông minh ở chỗ khi bạn bắt đầu nhập tên của hàm, một gợi ý hàm bật lên sẽ hiển thị (xem hình ảnh bên dưới). Chính từ danh sách này, bạn sẽ chọn tùy chọn của mình. Tuy nhiên, đừng nhấn phím Enter sau khi thực hiện lựa chọn của bạn. Thay vào đó, nhấn phím Tab và Excel sẽ tự động điền tên hàm.
2. Sử dụng Tùy chọn Chèn hàm từ Tab Công thức
Nếu bạn muốn toàn quyền kiểm soát việc chèn hàm của mình, sử dụng hộp thoại Chèn hàm Excel là tất cả những gì bạn cần. Để đạt được điều này, hãy chuyển đến tab Công thức và chọn menu đầu tiên có nhãn Chèn Hàm. Hộp thoại sẽ chứa tất cả các hàm bạn cần để hoàn thành công việc của mình.
3. Chọn Công thức từ một trong các Nhóm trong Tab Công thức
Tùy chọn này dành cho những người muốn tìm hiểu kỹ các hàm yêu thích của mình một cách nhanh chóng. Để tìm menu này, hãy điều hướng đến tab Công thức và chọn nhóm ưa thích của bạn. Nhấp để hiển thị menu phụ chứa đầy danh sách các hàm.
Từ đó, bạn có thể chọn tùy chọn của bạn. Tuy nhiên, nếu bạn thấy nhóm ưa thích của mình không có trên tab, hãy nhấp vào tùy chọn Chức năng khác – có thể nó chỉ bị ẩn ở đó.
4. Sử dụng tùy chọn AutoSum
Đối với các tác vụ nhanh và hàng ngày, chức năng AutoSum là tùy chọn tiếp theo của bạn. Điều hướng đến tab Công thức và nhấp vào tùy chọn AutoSum. Sau đó nhấp vào dấu mũ hướng xuống để hiển thị các công thức ẩn khác. Tùy chọn này cũng có sẵn trong tab Trang chủ.
5. Chèn nhanh: Sử dụng các tab được sử dụng gần đây
Nếu bạn thấy việc nhập lại công thức gần đây nhất của mình là một nhiệm vụ đơn điệu, thì hãy sử dụng lựa chọn Đã sử dụng gần đây. Nó nằm trên tab Công thức, một tùy chọn menu thứ ba ngay bên cạnh Tự động tính tổng.
15 hàm cơ bản trong excel
Dưới đây là danh sách 10 chức năng đơn giản nhưng thực sự hữu ích, là một kỹ năng cần thiết cho tất cả những ai muốn chuyển từ một người mới sử dụng Excel thành một chuyên gia Excel.
SUM (HÀM TÍNH TỔNG)
Hàm Excel đầu tiên mà bạn nên làm quen là hàm thực hiện phép tính số học cơ bản của phép cộng. Công thức Excel cơ bản này được sử dụng để lấy tổng giá trị trong một hoặc nhiều ô hoặc phạm vi.
SUM (number1, [number2], …)
Trong cú pháp của tất cả các hàm Excel, một đối số được đặt trong [dấu ngoặc vuông] là tùy chọn, các đối số khác là bắt buộc. Có nghĩa là, công thức tính tổng của bạn phải bao gồm ít nhất 1 số, tham chiếu đến một ô hoặc một dải ô. Ví dụ:
=SUM(B2:B6)- cộng các giá trị trong các ô từ B2 đến B6.
=SUM(B2, B6)- cộng các giá trị trong ô B2 và B6.
Nếu cần, bạn có thể thực hiện các phép tính khác trong một công thức, ví dụ: cộng các giá trị trong các ô từ B2 đến B6, sau đó chia tổng cho 5:
=SUM(B2:B6)/5
Để tính tổng có điều kiện, hãy sử dụng hàm SUMIF: trong đối số thứ nhất, bạn nhập phạm vi ô sẽ được kiểm tra theo tiêu chí (A2:A6), trong đối số thứ 2 - chính tiêu chí (D2) và trong đối số cuối cùng - các ô tính tổng (B2:B6):
=SUMIF(A2:A6, D2, B2:B6)
Trong trang tính Excel của bạn, các công thức có thể trông giống như sau:
Mẹo. Cách nhanh nhất để tính tổng một cột hoặc một hàng số là chọn một ô bên cạnh các số bạn muốn tính tổng (ô ngay bên dưới giá trị cuối cùng trong cột hoặc bên phải của số cuối cùng trong hàng) và bấm nútAutoSum trên tab Trang chủ ,trong nhóm Định dạng .Excel sẽ tự động chèn công thức SUM cho bạn.
AVERAGE (HÀM TRUNG BÌNH)
Hàm Excel AVERAGE thực hiện đúng như tên gọi của nó, tức là tìm giá trị trung bình hoặc trung bình cộng của các số. Công thức cơ bản này trong Excel được sử dụng để lấy giá trị trung bình trong một hoặc nhiều ô hoặc phạm vi. Cú pháp của nó tương tự như của SUM:
AVERAGE (number1, [number2], …)
Xem xét kỹ hơn công thức từ phần trước ( =SUM(B2:B6)/5), nó thực sự làm gì? Tính tổng các giá trị trong các ô từ B2 đến B6, sau đó chia kết quả cho 5. Và bạn gọi việc cộng một nhóm các số rồi chia tổng cho tổng của các số đó là gì? Đúng, một mức trung bình!
Hàm Excel AVERAGE sẽ thực hiện các phép tính này thay bạn. Vì vậy, thay vì chia tổng cho số đếm, bạn chỉ cần đặt công thức này vào một ô.
=AVERAGE(B2:B6)
Để tính trung bình các ô dựa trên điều kiện, hãy sử dụng công thức AVERAGEIF sau, trong đó A2:A6 là phạm vi tiêu chí, D3 là tiêu chí và B2:B6 là các ô cần tính trung bình:
=AVERAGEIF(A2:A6, D3, B2:B6)
MAX & MIN (HÀM LỚN NHẤT & NHỎ NHẤT)
Các công thức MAX và MIN trong Excel lần lượt lấy giá trị lớn nhất và nhỏ nhất trong một tập hợp số.
=MIN(number1, [number2], …)
Ví dụ:
=MIN(B2:B6) – Tìm số nhỏ nhất giữa cột B từ B2 đến B6.
=MAX(number1, [number2], …)
Ví dụ:
=MAX(B2:B6) – Tương tự, nó tìm số lớn nhất giữa cột B từ B2 đến B6.
COUNT & COUNTA (HÀM ĐẾM )
Nếu bạn tò mò muốn biết có bao nhiêu ô trong một phạm vi nhất định chứa các giá trị số (số hoặc ngày), đừng lãng phí thời gian để đếm chúng bằng tay. Hàm Excel COUNT sẽ mang lại cho bạn số đếm trong tích tắc:
COUNT (number1, [number2], …)
Mặc dù hàm COUNT chỉ xử lý những ô có chứa số, nhưng hàm COUNTA đếm tất cả các ô không trống, cho dù chúng có chứa số, ngày tháng, thời gian, văn bản, giá trị logic TRUE và FALSE, lỗi hoặc chuỗi văn bản trống ("" ):
COUNTA (number1, [number2], …)
Ví dụ: để biết có bao nhiêu ô trong cột B chứa số, hãy sử dụng công thức sau:
=COUNT(B:B)
Để đếm tất cả các ô không trống trong cột B, hãy làm như sau:
=COUNTA(B:B)
Trong cả hai công thức, bạn sử dụng cái gọi là "tham chiếu toàn bộ cột" (B:B) đề cập đến tất cả các ô trong cột B.
Ảnh chụp màn hình sau đây cho thấy sự khác biệt: trong khi COUNT chỉ xử lý các số, COUNTA đếm tổng số ô không trống trong cột B, bao gồm cả giá trị văn bản trong tiêu đề cột.
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 100 sản phẩm, nhập công thức:
=COUNTIF(B2:B6,">100")
Kết quả trả về cho thấy có tổng cộng 2 mặt hàng còn tồn trên 100 sản phẩm.
IF (HÀM ĐIỀU KIỆN)
Xét theo số lượng nhận xét liên quan đến IF trên blog của chúng tôi, đây là hàm phổ biến nhất trong Excel. Nói một cách đơn giản, bạn sử dụng công thức IF để yêu cầu Excel kiểm tra một điều kiện nhất định và trả về một giá trị hoặc thực hiện một phép tính nếu điều kiện được đáp ứng và một giá trị hoặc phép tính khác nếu điều kiện không được đáp ứng. Hàm IF thường được sử dụng khi bạn muốn sắp xếp dữ liệu của mình theo một logic nhất định. Phần hay nhất của công thức IF là bạn có thể nhúng các công thức và hàm vào đó.
IF(kiểm_soát_logic, [giá_trị_if_true], [giá_trị_if_false])
Ví dụ, câu lệnh IF sau đây kiểm tra xem đơn hàng đã được hoàn thành chưa (tức là có giá trị trong cột C) hay chưa. Để kiểm tra xem một ô có trống không, bạn sử dụng toán tử "không bằng" ( <>) kết hợp với một chuỗi rỗng (""). Kết quả là, nếu ô C2 không trống, công thức sẽ trả về "Có", nếu trống thì trả về "Không":
TRIM (HÀM CẮT)
Nếu các công thức Excel rõ ràng đúng của bạn chỉ trả về một loạt lỗi, thì một trong những điều đầu tiên cần kiểm tra là khoảng trắng thừa trong các ô được tham chiếu (Bạn có thể ngạc nhiên khi biết có bao nhiêu khoảng trắng ở đầu, cuối và ở giữa ẩn trong trang tính của bạn mà không được chú ý cho đến khi có sự cố xảy ra!).
Hàm TRIM đảm bảo rằng các hàm của bạn không trả về lỗi do có thêm khoảng trắng trong dữ liệu của bạn. Nó đảm bảo rằng tất cả các khoảng trống được loại bỏ. Không giống như các hàm khác có thể hoạt động trên nhiều ô, TRIM chỉ hoạt động trên một ô duy nhất. Do đó, nó đi kèm với nhược điểm là thêm dữ liệu trùng lặp vào bảng tính của bạn.
Có một số cách để xóa khoảng trắng không mong muốn trong Excel, với hàm TRIM là cách dễ nhất:
TRIM(text)
Ví dụ: để cắt bớt khoảng trắng thừa trong cột A, hãy nhập công thức sau vào ô C2, rồi sao chép xuống cột:
=TRIM(A2)
Nó sẽ loại bỏ tất cả các khoảng trắng thừa trong các ô ngoại trừ một ký tự khoảng trắng giữa các từ:
HÀM LEN
Bất cứ khi nào bạn muốn biết số ký tự trong một ô nào đó, LEN là hàm nên sử dụng:
LEN(text)
Muốn biết có bao nhiêu ký tự trong ô A2? Chỉ cần gõ công thức dưới đây vào một ô khác:
=LEN(A2)
Xin lưu ý rằng hàm LEN của Excel đếm hoàn toàn tất cả các ký tự kể cả khoảng trắng:
AND & OR (VÀ & HOẶC)
Đây là hai hàm logic phổ biến nhất để kiểm tra nhiều tiêu chí. Sự khác biệt là cách họ làm điều này:
AND trả về TRUE nếu tất cả các điều kiện được đáp ứng, FALSE nếu không.
OR trả về TRUE nếu bất kỳ điều kiện nào được đáp ứng, FALSE nếu không.
Mặc dù hiếm khi được sử dụng riêng, nhưng các hàm này rất tiện dụng như một phần của các công thức lớn hơn.
Ví dụ: để kiểm tra kết quả kiểm tra trong cột B và C và trả về "Đạt" nếu cả hai đều lớn hơn 80, "Không đạt" nếu không, hãy sử dụng công thức IF sau với câu lệnh AND được nhúng:
=IF(AND(B2>80, C2>80), "Đạt", "Không đạt")
Nếu chỉ cần có một điểm kiểm tra lớn hơn 80 (bài kiểm tra 1 hoặc bài kiểm tra 2) là đủ, hãy dùng câu lệnh OR:
=IF(OR(B2>80, C2>80), "Đạt", "Không đạt")
CONCATENATE (HÀM NỐI)
Trong trường hợp bạn muốn lấy giá trị từ hai ô trở lên và gộp chúng vào một ô, hãy sử dụng toán tử nối (&) hoặc hàm CONCATENATE:
CONCATENATE(text1, [text2],…)
Ví dụ: để kết hợp các giá trị từ ô A2 và B2, chỉ cần nhập công thức sau vào một ô khác:
=CONCATENATE(A2, B2)
Để phân tách các giá trị kết hợp bằng dấu cách, hãy nhập ký tự khoảng trắng (" ") vào danh sách đối số:
=CONCATENATE(A2, " ", B2)
TODAY & NOW (HÀM HÔM NAY & BÂY GIỜ)
Để xem ngày và giờ hiện tại bất cứ khi nào bạn mở trang tính của mình mà không phải cập nhật thủ công hàng ngày, hãy sử dụng:
=TODAY() để chèn ngày hôm nay vào một ô.
=NOW() để chèn ngày và giờ hiện tại vào một ô.
Cái hay của các hàm này là chúng không yêu cầu bất kỳ đối số nào cả, bạn nhập các công thức chính xác như đã viết ở trên.
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 ô D2, bạn điền công thức: =VLOOKUP(C2;$M$1:$N$6;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 lấy dữ liệu.
Range_lookup = 0 (FALSE) để dò tìm chính xác.
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 (A12:E13), tại D4 ta gõ công thức: =HLOOKUP(C4;$A$12:$E$13;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.
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 5 dòng và 3 cột (có địa chỉ là B4:D8). Bạn nhập công thức: =INDEX(B4:D8,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:D8 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.
Hàm MATCH
