10 công thức trong Excel nâng cao khả năng phân tích

Bạn muốn trở thành một người phân tích dữ liệu hoặc phân tích kinh doanh, bạn cầm nắm được các công thức quan trọng trong Excel và thực hành chúng một cách thành thạo.

10 công thức sau bạn chắc chắn phải nắm được nếu bạn muốn trở thành một nhà phân tích giỏi.

1. SUMIF

Công thức: =SUMIFS(cái bạn muốn tính tổng, Cột điều kiện số 1, điều kiện, Cột điều kiện số 2, điều kiện….)

sumifs-excel-formula-demo

Ví dụ: =SUMIFS(sales, regions, “A”, products, “B”, customer types, “C”, month, “M”)

2. Vlookup

Công thức: =VLOOKUP(cái bạn muốn tìm, bảng dữ liệu, Cột bạn muốn có kết quả, bảng của bạn đã được sắp xếp?)

vlookup

Ví dụ: =VLOOKUP(“C00023”, customers, 2, false)

3. INDEX & MATCH

Khi sử dụng Excel với hàm Vlookup, rất nhiều người sẽ cảm thấy bực tức bởi lẽ Vlookup chỉ tìm kiếm các dữ liệu phía bên trái bảng dữ liệu mà thôi.

Ví dụ khi các bạn có một bảng dữ liệu gồm cột CustomerID ở bên trái và cột Customer Names ở bên phải, nếu bạn dùng Vlookup bạn chỉ có thể tìm theo cột CustomerID mà thôi. Chúng ta không thể tìm được customerID nếu chỉ có tên của ông đó. Ví dụ như Kiểm toán PRO chẳng hạn.

Tuy nhiên chúng ta đã có kết hợp giữa INDEX và MATCH để có thể giải quyết nhu cầu đó.

Công thức: =INDEX(danh sách giá trị, MATCH(cái bạn muốn tìm, cột muốn tìm, Cột của bạn đã được sắp xếp?))

Ví dụ: =INDEX(customerID, MATCH(“Kiểm toán PRO”, Customer names, 0) )

4. IF

Ví dụ như các công ty kiểm toán Big 4 sẽ ra điều kiện rằng nếu bạn chăm đọc Kiểm toán PRO và subscribe trang web này bạn sẽ được cộng thêm 10% số điểm, nếu không các bạn sẽ bị trừ đi 10% số điểm thi đầu vào. Vậy thể hiện nó trong Excel như thế nào?

Bạn sẽ dùng công thức như sau: =IF(nếu bạn đọc http://www.kiemtoanpro.com, “tăng 10% số điểm”, “giảm 10% số điểm”)

Công thức: =IF (Điều kiện, kết quả nếu TRUE, kết quả nếu FALSE)

5. Tổ hợp các công thức

Cũng với ví dụ nêu trên, việc bạn thường xuyên đọc Kiểm Toán PRO cũng sẽ là điều kiện để bạn được gia tăng số điểm. Nếu bạn đọc Kiểm toán PRO trên 3 lần trong 1 ngày, bạn sẽ được tăng 15% số điểm, nếu không sẽ chỉ có 10% thôi và nếu không đọc thì sẽ giảm 10% số điểm.

Khi đó công thức Excel của bạn sẽ là:

=IF(số lần đọc http://www.kiemtoanpro.com >=3, “tăng 15% điểm”, IF( số lần đọc http://www.kiemtoanpro.com >0, “tăng 10% điểm”, “giảm 10% điểm”))

6. Các ký hiệu căn bản

Bạn phải nắm được ý nghĩa của các ký hiệu đặc biệt như: $, %, &, @, và * trong các công thức trên Excel.

Nếu bạn nhớ được ý nghĩa của các ký tự này, hãy comment vào trong bài viết này.

7. Các công thức liên quan đến ký tự

Các công thức tối thiểu bạn cần phải nắm là:

LEFT, RIGHT & MID
TRIM
SUBSTITUTE
LEN
TEXT
FIND

8. NETWORKDAY & WORKDAY

Trong việc tính thời gian của dự án, chúng ta luôn có thời hạn làm việc của một dự án dự kiến là 180 ngày làm việc và bắt đầu từ ngày 14 tháng 12 năm 2015 vậy thì ngày nào là ngày làm cuối cùng của dự án đó?

Excel cho phép chúng ta sử dụng các công thức về Workday và Networkday để giải quyết vấn đề đó.

NETWORKDAYS: Tính số ngày làm việc giữa 2 thời điểm (giả định Thứ 7 và CN là ngày nghỉ)
NETWORKDAYS.INTL: giống NETWORKDAYS, nhưng cho phép bạn tuỳ chỉnh ngày cuối tuần [Excel 2010+ trở lên]
WORKDAY: Tính ngày cuối cùng từ ngày bắt đầu và số ngày làm việc
WORKDAY.INTL: giống WORKDAY, nhưng cho phép bạn tuỳ chỉnh ngày cuối tuần [Excel 2010+ trở lên]

9. Các công thức về so sánh

Các bạn luôn được đặt câu hỏi rằng: Ai là người khách hàng lớn thứ 2, khách hàng nợ xấu thứ 3 là ai, quán cà phê nổi tiếng thứ 5 ở Sài Gòn là quán nào?

Excel cho chúng ta những công thức sau:

SMALL: Dùng để tìm phần tử nhỏ nhất thứ n trong danh sách =SMALL(range of values, n).
LARGE: Dùng để tìm phần tử lớn thứ n trong danh sách.
MIN: Đưa ra giá trị nhỏ nhất trong danh sách.
MAX: Đưa ra giá trị lớn nhất trong danh sách.
RANK: Tìm thứ hạng của một giá trị trong danh sách =RANK(value, in this list, order)

10. IFERROR

Trong các công thức của Excel, nhiều khi các bạn sẽ không tìm kiếm được kết quả hoặc mang lại giá trị là #NA. Để giải quyết vấn đề này, Excel cung cấp công thức IFERROR để xử lý vấn đề đó.

Công thức: IFERROR(công thức, việc cần làm nếu bị error)

Ví dụ: IFERROR(VLOOKUP(….), “Value not found!”)

Nếu bạn nắm được 10 dạng công thức trên, bạn đã bắt đầu sử dụng excel thành thạo rồi đó. Để trở thành một Master, bạn cần phải nắm được thêm 3 công thức sau: OFFSET, SUMPRODUCT và SUBTOTAL.

Các bài tiếp theo sẽ nói nhiều hơn về 3 công thức này.

Nhớ điền kết quả của các bạn vào comment với các công thức ở phần 6 nhé.

Kiểm toán PRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s