10 Công thức Excel nâng cao bạn phải biết - Phần 1

Cập nhật ngày: 28/09/2021 - Đã có 497 lượt xem bài viết này!
10 Công thức Excel nâng cao bạn phải biết - Phần 1
Mỗi nhà phân tích tài chính dành nhiều thời gian trong Excel hơn họ có thể quan tâm để thừa nhận. Dựa trên nhiều năm và nhiều năm kinh nghiệm, chúng tôi đã biên soạn các công thức Excel quan trọng và tiên tiến nhất mà mọi nhà phân tích tài chính đẳng cấp thế giới đều phải biết.

10 Công thức Excel nâng cao bạn phải biết - Phần 1

1. CHỈ SỐ INDEX
Công thức: = INDEX (C3: E9, MATCH (B13, C3: C9,0), MATCH (B14, C3: E3,0))
Đây là một thay thế nâng cao cho các công thức VLOOKUP hoặc HLOOKUP (có một số hạn chế và hạn chế).  INDEX MATCH  là sự kết hợp mạnh mẽ của các công thức Excel sẽ đưa phân tích tài chính và mô hình tài chính của bạn lên một tầm cao mới.
INDEX trả về giá trị của một ô trong bảng dựa trên số cột và số hàng.
MATCH trả về vị trí của một ô trong một hàng hoặc cột.
Dưới đây là một ví dụ về các công thức INDEX và MATCH kết hợp với nhau. Trong ví dụ này, chúng tôi tìm kiếm và trả lại chiều cao của một người dựa trên tên của họ. Vì tên và chiều cao là cả hai biến trong công thức, chúng ta có thể thay đổi cả hai!

imic-edu-vn-10-cong-thuc-excel-nang-cao-ban-phai-biet-hoc-tai-imic-1

2. NẾU kết hợp với AND / OR
Công thức: = IF (AND (C2> = C4, C2 <= C5), C6, C7)
Bất cứ ai đã dành rất nhiều thời gian để thực hiện các loại mô hình tài chính khác nhau đều biết rằng các công thức IF lồng nhau có thể là một cơn ác mộng. Kết hợp IF với hàm AND hoặc hàm OR có thể là một cách tuyệt vời để giữ cho các công thức dễ kiểm toán hơn và dễ hiểu hơn cho người dùng khác. Trong ví dụ dưới đây, bạn sẽ thấy cách chúng tôi sử dụng các hàm riêng lẻ kết hợp để tạo ra một công thức nâng cao hơn.

imic-edu-vn-10-cong-thuc-excel-nang-cao-ban-phai-biet-hoc-tai-imic-2

3. OFFSET kết hợp với SUM hoặc AVERAGE
Công thức: = SUM (B4: OFFSET (B4,0, E2-1))
Bản thân hàm OFFSET không đặc biệt tiên tiến, nhưng khi chúng ta kết hợp nó với các hàm khác như SUM hoặc AVERAGE, chúng ta có thể tạo ra một công thức khá phức tạp. Giả sử bạn muốn tạo một hàm động có thể tổng hợp một số lượng ô khác nhau. Với công thức SUM thông thường, bạn bị giới hạn trong tính toán tĩnh, nhưng bằng cách thêm OFFSET, bạn có thể di chuyển tham chiếu ô xung quanh.
Cách thức hoạt động: Để làm cho công thức này hoạt động, chúng tôi thay thế ô tham chiếu kết thúc của hàm SUM bằng hàm OFFSET. Điều này làm cho công thức động và ô được tham chiếu là E2 là nơi bạn có thể cho Excel biết bạn muốn thêm bao nhiêu ô liên tiếp. Bây giờ chúng tôi đã có một số công thức Excel nâng cao!
Dưới đây là một ảnh chụp màn hình của công thức hơi phức tạp hơn trong hành động.

imic-edu-vn-10-cong-thuc-excel-nang-cao-ban-phai-biet-hoc-tai-imic-2

Như bạn thấy, công thức SUM bắt đầu trong ô B4, nhưng nó kết thúc bằng một biến, đó là công thức OFFSET bắt đầu từ B4 và tiếp tục bằng giá trị trong E2 (Tạm thời 3), trừ đi một. Thao tác này sẽ kết thúc công thức tính tổng trên 2 ô, tổng 3 năm dữ liệu (bao gồm cả điểm bắt đầu). Như bạn có thể thấy trong ô F7, tổng các ô B4: D4 là 15, đây là công thức bù và tổng cho chúng ta.

4. CHỌN
Công thức: = CHỌN (lựa chọn, tùy chọn1, tùy chọn2, tùy chọn3)
Hàm CHOOSE là tuyệt vời để phân tích kịch bản trong mô hình tài chính. Nó cho phép bạn chọn giữa một số tùy chọn cụ thể và trả về lựa chọn của bạn mà bạn đã chọn. Ví dụ, hãy tưởng tượng bạn có ba giả định khác nhau để tăng trưởng doanh thu trong năm tới: 5%, 12% và 18%. Sử dụng công thức CHỌN, bạn có thể trả lại 12% nếu bạn nói với Excel bạn muốn lựa chọn số 2.

imic-edu-vn-10-cong-thuc-excel-nang-cao-ban-phai-biet-hoc-tai-imic-4

5. XNPV và XIRR
Công thức: = XNPV (tỷ lệ chiết khấu, dòng tiền, ngày)
Nếu bạn là nhà phân tích làm việc trong ngân hàng đầu tư , nghiên cứu vốn cổ phần, hoặc lập kế hoạch & phân tích tài chính ( FP & A ) hoặc bất kỳ lĩnh vực tài chính doanh nghiệp nào khác yêu cầu chiết khấu dòng tiền, thì những công thức này là cứu cánh!
Nói một cách đơn giản, XNPV và XIRR cho phép bạn áp dụng các ngày cụ thể cho từng dòng tiền riêng lẻ đang được chiết khấu. Vấn đề với các công thức NPV và IRR cơ bản của Excel là họ cho rằng khoảng thời gian giữa các dòng tiền là bằng nhau. Thông thường, với tư cách là một nhà phân tích, bạn sẽ có những tình huống mà dòng tiền không được tính thời gian đồng đều và công thức này là cách bạn khắc phục điều đó.

imic-edu-vn-10-cong-thuc-excel-nang-cao-ban-phai-biet-hoc-tai-imic

Xem khóa đào tạo nhân sự theo danh mục!

Xem các khóa đào tạo nhân sự