Tóm tắt nội dung
Excel là công cụ quan trọng trong phân tích tài chính, quản trị kinh doanh và xử lý dữ liệu. Bài viết này cung cấp hướng dẫn chi tiết về các công thức cơ bản trong Excel, bao gồm SUM, AVERAGE, COUNT, IF, VLOOKUP, HLOOKUP, INDEX, MATCH và nhiều hàm hữu ích khác. Với người mới bắt đầu, việc nắm vững các công thức cơ bản giúp tiết kiệm thời gian, giảm thiểu sai sót và nâng cao hiệu quả công việc. Ngoài ra, bài viết còn bổ sung các ví dụ thực tiễn, case study ứng dụng trong doanh nghiệp, cũng như các mẹo và ghi chú để bạn khai thác tối đa tiềm năng của Excel. Đặc biệt, phần FAQS giải đáp những thắc mắc thường gặp khi học Excel, giúp người đọc áp dụng kiến thức vào thực tế nhanh chóng và hiệu quả. Tài liệu này là một cẩm nang đầy đủ cho người học Excel từ cơ bản đến nâng cao, hướng đến mục tiêu làm chủ công cụ để phục vụ công việc và phát triển kỹ năng nghề nghiệp.
Giới thiệu
Microsoft Excel không chỉ là một phần mềm bảng tính mà còn là công cụ phân tích dữ liệu mạnh mẽ, được sử dụng rộng rãi trong hầu hết các ngành nghề. Từ kế toán, tài chính, quản lý nhân sự cho đến nghiên cứu thị trường, Excel đều có thể giúp người dùng xử lý, phân tích và trực quan hóa dữ liệu một cách dễ dàng. Trong bối cảnh chuyển đổi số và nhu cầu phân tích dữ liệu ngày càng cao, việc thành thạo Excel trở thành yêu cầu thiết yếu cho mọi nhân sự văn phòng.
Một trong những bước đầu tiên để chinh phục Excel là nắm vững các công thức cơ bản. Những công thức này chính là nền tảng, giúp bạn tiết kiệm thời gian, xử lý dữ liệu chính xác và mở rộng khả năng ứng dụng khi bước vào các hàm nâng cao hơn. Tuy nhiên, nhiều người mới học Excel thường gặp khó khăn trong việc nhớ cú pháp, hiểu cách vận hành của hàm và áp dụng vào thực tiễn.
Trong bài viết này, chúng ta không chỉ dừng lại ở việc liệt kê công thức, mà còn đi sâu vào ứng dụng thực tế thông qua ví dụ minh họa, case study, ghi chú và mẹo nhỏ để tránh sai lầm thường gặp. Bên cạnh đó, phần FAQS sẽ đóng vai trò như một người hướng dẫn, giải đáp các thắc mắc phổ biến để người học có thể áp dụng kiến thức một cách hiệu quả. Đây sẽ là tài liệu hữu ích cho cả sinh viên, nhân viên văn phòng và các nhà quản lý muốn tối ưu hóa công việc bằng Excel.
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
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: Hoang. Tại ô H6 ta thực hiện công thức: =MATCH(B6;B4:B8;0)
Những điều cần ghi nhớ:
● Một công thức phải luôn bắt đầu bằng một dấu bằng. Khác, nó sẽ hiển thị một lỗi.
● Nếu chúng tôi nhập bất kỳ giá trị văn bản nào thay vì cung cấp địa chỉ ô, chúng tôi nên cung cấp giá trị văn bản trong dấu phẩy ngược (“”).
● Trước khi nhập hàm vào một ô, hãy đảm bảo rằng định dạng ô là chung. Nếu một định dạng văn bản được chọn, thì công thức sẽ không hoạt động.
● Khoảng cách (_) luôn được tính là một ký tự. Vì vậy, nếu bạn đang làm việc với các ô trống, hãy nhớ rằng nếu một ô chỉ có khoảng trắng, ô đó sẽ không được tính là ô trống.
Ứng dụng thực tiễn của công thức Excel trong công việc
Excel không đơn thuần là một bảng tính, mà đã trở thành công cụ phân tích dữ liệu và quản trị công việc đa năng. Các công thức cơ bản như SUM, AVERAGE, COUNT, IF, VLOOKUP, INDEX, MATCH không chỉ phục vụ tính toán mà còn giúp tối ưu hóa dòng công việc trong tài chính, kế toán, nhân sự và cả marketing. Phần này sẽ cung cấp góc nhìn toàn diện, từ lý thuyết đến thực tiễn, để người đọc thấy rõ giá trị khi áp dụng Excel trong môi trường làm việc hiện đại.
1. Tối ưu hóa quy trình tài chính và kế toán
Trong doanh nghiệp, bộ phận tài chính – kế toán thường phải xử lý hàng nghìn dòng dữ liệu giao dịch mỗi tháng. Các công thức như SUMIF hay SUMPRODUCT cho phép cộng có điều kiện, giúp nhanh chóng tổng hợp doanh thu theo chi nhánh, theo sản phẩm hoặc theo nhân viên phụ trách. Ví dụ, thay vì mất 3 giờ lọc và cộng thủ công, chỉ cần một công thức SUMIF có thể trả về kết quả trong vài giây.
Ngoài ra, IF kết hợp với VLOOKUP còn giúp tạo báo cáo động. Chẳng hạn, kế toán có thể tự động phân loại giao dịch vào nhóm "Chi phí hợp lệ" hoặc "Chi phí cần xem xét" dựa trên bảng quy tắc có sẵn.
ROUND hoặc ROUNDUP
để làm tròn số liệu, đảm bảo sự nhất quán khi trình bày trước ban lãnh đạo.
2. Quản trị nhân sự và chấm công thông minh
Bộ phận nhân sự thường xuyên phải quản lý bảng chấm công, lương thưởng và hiệu suất làm việc. Các công thức như NETWORKDAYS giúp tính số ngày công chính xác, loại bỏ ngày nghỉ cuối tuần và ngày lễ. Trong khi đó, IF + OR + AND hỗ trợ phân loại nhân viên theo mức hoàn thành KPI.
Ví dụ: Một công ty 200 nhân viên đã tiết kiệm 20% thời gian xử lý bảng lương hàng tháng
nhờ áp dụng công thức kết hợp IF và VLOOKUP để tự động tính phụ cấp theo cấp bậc.
F4
để cố định ô tham chiếu trong bảng dữ liệu.
3. Ứng dụng trong Marketing và phân tích thị trường
Marketing là lĩnh vực cần nhiều dữ liệu để đưa ra quyết định. Excel cho phép xử lý danh sách khách hàng, tính toán tỷ lệ chuyển đổi, chi phí quảng cáo trên mỗi khách hàng (CPA) và lợi nhuận trên đầu tư (ROI).
Ví dụ, kết hợp COUNTIF để đếm số khách hàng tiềm năng trong từng phân khúc, rồi sử dụng AVERAGE để tính giá trị đơn hàng trung bình. Nhờ đó, bộ phận marketing có thể dự báo doanh thu dựa trên số lượng khách hàng mục tiêu.
Case Study 1: Doanh nghiệp bán lẻ tối ưu tồn kho bằng Excel
Một doanh nghiệp bán lẻ với 15 cửa hàng thường xuyên gặp vấn đề tồn kho dư thừa. Họ đã áp dụng VLOOKUP để đối chiếu dữ liệu nhập – xuất từ nhiều bảng khác nhau, và dùng IF để cảnh báo sản phẩm tồn kho dưới ngưỡng an toàn. Kết quả, chỉ trong 6 tháng, tỷ lệ hàng tồn giảm 25%, giúp tiết kiệm hơn 1 tỷ đồng chi phí lưu kho.
Không chỉ dừng lại ở đó, việc kết hợp PivotTable và công thức SUMIF cho phép ban giám đốc nắm được xu hướng bán hàng theo mùa vụ, từ đó lên kế hoạch nhập hàng chính xác hơn.
Case Study 2: Công ty dịch vụ tài chính dự báo dòng tiền
Một công ty tài chính nhỏ phải quản lý dòng tiền hàng ngày. Họ đã áp dụng INDEX + MATCH để trích xuất dữ liệu từ bảng lịch sử giao dịch, sau đó dùng FORECAST để dự báo dòng tiền trong 3 tháng tiếp theo. Nhờ đó, công ty có thể chủ động chuẩn bị vốn lưu động, giảm rủi ro thiếu hụt ngân quỹ.
Case Study 3: Trường đại học phân tích điểm số sinh viên
Một trường đại học đã sử dụng Excel để quản lý điểm của hơn 5000 sinh viên. Thông qua hàm AVERAGEIF, bộ phận đào tạo dễ dàng tính điểm trung bình theo lớp, còn IF được dùng để tự động phân loại sinh viên theo chuẩn “Đạt” – “Không đạt”. Ngoài ra, RANK giúp sắp xếp sinh viên theo thành tích, hỗ trợ công tác khen thưởng.
FAQS – Câu hỏi thường gặp
❓ Tôi thường bị lỗi #N/A khi dùng VLOOKUP, nguyên nhân là gì?
✅ Lỗi này xuất hiện khi giá trị tra cứu không có trong bảng dữ liệu hoặc khi tham số cuối (range_lookup) chưa được xác định chính xác. Luôn kiểm tra xem dữ liệu có được sắp xếp hợp lý chưa.
❓ Nên dùng PivotTable hay công thức SUMIF để tổng hợp dữ liệu?
✅ Nếu muốn phân tích nhanh, lọc theo nhiều điều kiện thì PivotTable phù hợp. Còn SUMIF gọn nhẹ, thích hợp khi bạn chỉ cần một kết quả nhanh trong báo cáo.
❓ Có thể dùng Excel thay thế phần mềm kế toán không?
✅ Với doanh nghiệp nhỏ, Excel có thể đáp ứng tạm thời. Tuy nhiên, để đảm bảo tính pháp lý và quản trị lâu dài, nên sử dụng phần mềm chuyên dụng.
Lợi ích lâu dài khi làm chủ Excel
Khi nắm vững Excel, cá nhân và tổ chức đều nhận được nhiều lợi ích:
- ⏱️ Tiết kiệm thời gian xử lý dữ liệu.
- 📊 Ra quyết định dựa trên dữ liệu (data-driven decision).
- 📉 Giảm thiểu sai sót trong báo cáo.
- 🚀 Tăng năng suất và hiệu quả công việc.
- 👩💼 Nâng cao năng lực nghề nghiệp, mở rộng cơ hội thăng tiến.
Mẹo và ghi chú nâng cao
Ctrl + T để tạo Table,
giúp công thức tự động mở rộng khi thêm dòng mới.
Kết luận
Excel là công cụ mạnh mẽ, nhưng giá trị của nó chỉ thực sự phát huy khi người dùng nắm vững công thức và áp dụng vào thực tiễn. Việc học Excel không chỉ dừng lại ở lý thuyết mà cần gắn liền với các bài tập thực hành, tình huống thực tế và cách giải quyết vấn đề trong công việc. Với tóm tắt, giới thiệu, ví dụ ứng dụng, case study và FAQS trong bài viết này, hy vọng bạn đã có được một cái nhìn toàn diện và đủ tự tin để bắt đầu hành trình làm chủ Excel.