10 Công thức Excel nâng cao bạn phải biết - Phần 1
Danh mục bài viết:
3. OFFSET kết hợp với SUM hoặc AVERAGE
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!
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.
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.
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.
Công thức: = CHOOSE (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.
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 đó.
Ở phần 1 này chúng ta đã tìm hiểu và nắm được 5 công thức Excel nâng cao và để có thể khai thác thêm về các công thức Excel nâng cao chúng ta cùng theo dõi và đọc bài viết " 10 Công thức Excel nâng cao bạn phải biết - Phần 2 "
Bạn đang muốn tìm kiếm 1 công việc với mức thu nhập cao.
✅ Hoặc là bạn đang muốn chuyển đổi công việc mà chưa biết theo học ngành nghề gì cho tốt.
✅ Giới thiệu với bạn Chương trình đào tạo nhân sự dài hạn trong 12 tháng với những điều đặc biệt mà chỉ có tại IMIC và đây cũng chính là sự lựa chọn phù hợp nhất dành cho bạn:
👉 Thứ nhất: Học viên được đào tạo bài bản kỹ năng, kiến thức chuyên môn lý thuyết, thực hành, thực chiến nhiều dự án và chia sẻ những kinh nghiệm thực tế từ Chuyên gia có nhiều năm kinh nghiệm dự án cũng như tâm huyết truyền nghề.
👉 Thứ hai: Được ký hợp đồng cam kết chất lượng đào tạo cũng như mức lương sau tốt nghiệp và đi làm tại các đối tác tuyển dụng của IMIC. Trả lại học phí nếu không đúng những gì đã ký kết.
👉 Thứ ba: Cam kết hỗ trợ giới thiệu công việc sang đối tác tuyển dụng trong vòng 10 năm liên tục.
👉 Thứ tư: Được hỗ trợ tài chính với mức lãi suất 0 đồng qua ngân hàng VIB Bank.
👉 Có 4 Chương trình đào tạo nhân sự dài hạn dành cho bạn lựa chọn theo học. Gồm có:
1) Data Scientist full-stack
2) Embedded System & IoT development full-stack
3) Game development full-stack
4) Web development full-stack
✅ Cảm ơn bạn đã dành thời gian lắng nghe những chia sẻ của mình. Và tuyệt vời hơn nữa nếu IMIC được góp phần vào sự thành công của bạn.
✅ Hãy liên hệ ngay với Phòng tư vấn tuyển sinh để được hỗ trợ về thủ tục nhập học.
✅ Chúc bạn luôn có nhiều sức khỏe và thành công!