Mọi nhà phân tích tài chính Hướng dẫn Analyst Trifecta® Hướng dẫn cơ bản về cách trở thành một nhà phân tích tài chính đẳng cấp thế giới. Bạn có muốn trở thành một nhà phân tích tài chính đẳng cấp thế giới? Bạn đang muốn làm theo các phương pháp hay nhất hàng đầu trong ngành và nổi bật so với đám đông? Quy trình của chúng tôi, được gọi là The Analyst Trifecta® bao gồm phân tích, trình bày và các kỹ năng mềm dành nhiều thời gian trong Excel hơn những gì họ có thể thừa nhận. Dựa trên 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 phải biết.
1. TRÒ CHƠI CHỈ SỐ
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ố nhược điểm và hạn chế). INDEX MATCH Index Match Công thức Kết hợp các hàm INDEX và MATCH là một công thức tra cứu mạnh hơn hàm VLOOKUP. Tìm hiểu cách sử dụng INDEX MATCH trong hướng dẫn Excel này. Chỉ mục trả về giá trị của một ô trong bảng dựa trên số cột và hàng và Kết hợp trả về vị trí của ô trong một hàng hoặc cột. Tìm hiểu cách thực hiện chúng trong hướng dẫn này 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à hàng.
MATCH trả về vị trí của một ô trong một hàng hoặc cột.
Đây là một ví dụ về các công thức INDEX và MATCH được kết hợp với nhau. Trong ví dụ này, chúng tôi tìm kiếm và trả về 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 đều là các biến trong công thức, nên chúng ta có thể thay đổi cả hai!
Công thức Kết hợp Chỉ mục Kết hợp các hàm INDEX và MATCH là một công thức tra cứu mạnh hơn hàm VLOOKUP. Tìm hiểu cách sử dụng INDEX MATCH trong hướng dẫn Excel này. Chỉ mục trả về giá trị của một ô trong bảng dựa trên số cột và hàng và Kết hợp trả về vị trí của ô trong một hàng hoặc cột. Tìm hiểu cách thực hiện chúng trong hướng dẫn này
Để được giải thích từng bước hoặc cách sử dụng công thức này, vui lòng xem hướng dẫn miễn phí của chúng tôi về cách sử dụng hàm INDEX MATCH trong Excel Công thức đối sánh chỉ mục Kết hợp hàm INDEX và MATCH là một công thức tra cứu mạnh mẽ hơn hàm VLOOKUP. Tìm hiểu cách sử dụng INDEX MATCH trong hướng dẫn Excel này. Chỉ mục trả về giá trị của một ô trong bảng dựa trên số cột và hàng và Kết hợp trả về vị trí của ô trong một hàng hoặc cột. Tìm hiểu cách thực hiện chúng trong hướng dẫn này.
2. IF kết hợp với AND / OR
Công thức: = IF (AND (C2> = C4, C2 <= C5), C6, C7)
Bất kỳ 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 Các loại mô hình tài chính Các loại mô hình tài chính phổ biến nhất bao gồm: mô hình 3 tuyên bố, mô hình DCF, mô hình M&A, mô hình LBO, mô hình ngân sách. Khám phá 10 loại hàng đầ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 OR có thể là một cách tuyệt vời để giữ cho công thức dễ kiểm tra hơn và người dùng khác dễ hiểu hơn. Trong ví dụ dưới đây, bạn sẽ thấy cách chúng tôi sử dụng kết hợp các hàm riêng lẻ để tạo ra một công thức nâng cao hơn.
Để biết phân tích chi tiết về cách thực hiện hàm này trong Excel, vui lòng xem hướng dẫn miễn phí của chúng tôi về cách sử dụng IF với Câu lệnh AND / OR IF giữa hai số Tải xuống mẫu miễn phí này cho câu lệnh IF giữa hai số trong Excel. Trong hướng dẫn này, chúng tôi chỉ cho bạn từng bước cách tính IF bằng câu lệnh AND. Tìm hiểu cách tạo câu lệnh IF sẽ kiểm tra xem ô có chứa giá trị giữa hai số hay không và sau đó xuất ra kết quả bạn muốn nếu điều kiện đó được đáp ứng. 
Câu lệnh IF giữa hai số Tải xuống mẫu miễn phí này cho câu lệnh IF giữa hai số trong Excel. Trong hướng dẫn này, chúng tôi chỉ cho bạn từng bước cách tính IF bằng câu lệnh AND. Tìm hiểu cách tạo câu lệnh IF sẽ kiểm tra xem một ô có chứa giá trị giữa hai số hay không và sau đó xuất ra kết quả bạn muốn nếu điều kiện đó được đáp ứng
3. OFFSET kết hợp với SUM hoặc AVERAGE
Công thức: = SUM (B4: OFFSET (B4,0, E2-1))
Hàm OFFSET Hàm OFFSET Hàm OFFSET được phân loại dưới các hàm Tra cứu và Tham chiếu trong Excel. OFFSET sẽ trả về một dải ô. Nghĩa là, nó sẽ trả về một số hàng và cột được chỉ định từ một phạm vi ban đầu đã được chỉ định. về bản thân nó không phải là đặc biệt nâng cao, 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ính tổng một số ô thay đổi. Với công thức SUM thông thường, bạn bị giới hạn trong một phép tính tĩnh, nhưng bằng cách thêm OFFSET, bạn có thể di chuyển tham chiếu ô.
Cách hoạt động: Để 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 trở nên độ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 ta đã có một số công thức Excel nâng cao!
Dưới đây là ảnh chụp màn hình của công thức phức tạp hơn một chút này.
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 (“3”), trừ đi một. Thao tác này sẽ di chuyển phần cuối của công thức tổng qua 2 ô, tính 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, đó là công thức bù và tổng cung cấp cho chúng ta.
Tìm hiểu cách xây dựng công thức này từng bước trong khóa học Excel nâng cao của chúng tôi.
4. CHỌN
Công thức: = CHOOSE (lựa chọn, tùy chọn1, tùy chọn2, tùy chọn 3)
Chức năng CHOOSE Chức năng CHOOSE được phân loại trong các chức năng Tra cứu và Tham chiếu trong Excel. Nó sẽ trả về một giá trị từ một mảng tương ứng với số chỉ mục được cung cấp. Hàm sẽ trả về mục nhập thứ n trong một danh sách nhất định. Là một nhà phân tích tài chính, chức năng CHOOSE rất hữu ích trong việc lựa chọn từ một bộ dữ liệu nhất định. Ví dụ, chúng tôi 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” mà bạn đã chọn. Ví dụ: hãy tưởng tượng bạn có ba giả định khác nhau về tăng trưởng doanh thu trong năm tới: 5%, 12% và 18%. Sử dụng công thức CHOOSE, bạn có thể trả về 12% nếu cho Excel biết bạn muốn lựa chọn số 2.
Đọc thêm về phân tích tình huống trong Excel Phân tích tình huống Phân tích tình huống là một kỹ thuật được sử dụng để phân tích các quyết định bằng cách suy đoán các kết quả khác nhau có thể xảy ra trong đầu tư tài chính. Trong mô hình tài chính, điều này.
Để xem video minh họa, hãy xem Khóa học Công thức Excel Nâng cao của chúng tôi.
5. XNPV và XIRR
Công thức: = XNPV (tỷ lệ chiết khấu, dòng tiền, ngày tháng)
Nếu bạn là một nhà phân tích làm việc trong lĩnh vực ngân hàng đầu tư Con đường sự nghiệp Ngân hàng đầu tư Hướng dẫn nghề nghiệp ngân hàng đầu tư - hãy lập kế hoạch cho con đường sự nghiệp IB của bạn. Tìm hiểu về mức lương ngân hàng đầu tư, cách được tuyển dụng và những việc cần làm sau khi học IB. Bộ phận ngân hàng đầu tư (IBD) giúp các chính phủ, tập đoàn và tổ chức huy động vốn và hoàn thành các thương vụ mua bán và sáp nhập (M&A). , nghiên cứu vốn chủ sở hữu, lập kế hoạch và phân tích tài chính (FP&A FP&A Analyst Trở thành nhà phân tích FP&A tại một công ty. Chúng tôi phác thảo mức lương, kỹ năng, tính cách và đào tạo bạn cần cho công việc FP&A và sự nghiệp tài chính thành công. Các nhà phân tích, quản lý và giám đốc FP&A là chịu trách nhiệm cung cấp cho các nhà điều hành những phân tích và thông tin họ cần), 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à một 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à chúng giả định 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ẽ gặp phải những tình huống mà dòng tiền không được tính đúng thời gian và công thức này là cách bạn khắc phục điều đó.
Để biết phân tích chi tiết hơn, hãy xem hướng dẫn công thức IRR vs XIRR miễn phí của chúng tôi XIRR vs IRR Tại sao lại sử dụng XIRR vs IRR. XIRR ấn định ngày cụ thể cho từng dòng tiền riêng lẻ, làm cho nó chính xác hơn IRR khi xây dựng mô hình tài chính trong Excel. cũng như hướng dẫn XNPV của chúng tôi.
6. SUMIF và COUNTIF
Công thức: = COUNTIF (D5: D12, ”> = 21 ″)
Hai công thức nâng cao này là công dụng tuyệt vời của các hàm điều kiện. SUMIF thêm tất cả các ô đáp ứng các tiêu chí nhất định và COUNTIF đếm tất cả các ô đáp ứng các tiêu chí nhất định. Ví dụ: hãy tưởng tượng bạn muốn đếm tất cả các ô lớn hơn hoặc bằng 21 (độ tuổi uống rượu hợp pháp ở Hoa Kỳ) để tìm ra bạn cần bao nhiêu chai sâm panh cho một sự kiện của khách hàng. Bạn có thể sử dụng COUNTIF làm giải pháp nâng cao, như được hiển thị trong ảnh chụp màn hình bên dưới.
Trong khóa học Excel nâng cao của chúng tôi, chúng tôi chia nhỏ các công thức này chi tiết hơn.
7. PMT và IPMT
Công thức: = PMT (lãi suất, số kỳ, giá trị hiện tại)
Nếu bạn làm việc trong ngành ngân hàng thương mại Hồ sơ nghề nghiệp Ngân hàng thương mại Con đường sự nghiệp Ngân hàng thương mại là cung cấp cho khách hàng các sản phẩm tín dụng như cho vay có kỳ hạn, hạn mức tín dụng quay vòng, phương tiện hợp vốn, dịch vụ quản lý tiền mặt và các sản phẩm thu nhập cố định khác. Là một nhà phân tích tín dụng hoặc người quản lý tài khoản, bạn cung cấp lời khuyên tài chính, bất động sản, Lập kế hoạch và Phân tích Tài chính FP&A FP&A (FP&A) là một chức năng quan trọng tại một công ty. Các chuyên gia FP&A hỗ trợ việc ra quyết định điều hành cho Giám đốc điều hành, Giám đốc tài chính và Hội đồng quản trị bằng việc lập kế hoạch, phân tích và mô hình hóa. Tìm hiểu công việc của một nhà phân tích FP&A, người quản lý hoặc giám đốc yêu cầu - mức lương, yêu cầu, giáo dục, kỹ năng hoặc bất kỳ vị trí phân tích tài chính nào liên quan đến lịch trình nợ, bạn sẽ muốn hiểu hai công thức chi tiết này.
Công thức PMT cung cấp cho bạn giá trị của các khoản thanh toán bằng nhau trong suốt thời hạn của khoản vay. Bạn có thể sử dụng nó kết hợp với IPMT (cho bạn biết các khoản thanh toán lãi suất cho cùng một loại khoản vay), sau đó tách riêng các khoản thanh toán gốc và lãi.
Dưới đây là một ví dụ về cách sử dụng hàm PMT để nhận khoản thanh toán thế chấp hàng tháng cho khoản thế chấp 1 triệu đô la ở mức 5% trong 30 năm.
8. LEN và TRIM
Công thức: = LEN (văn bản) và = TRIM (văn bản)
Đây là những công thức ít phổ biến hơn một chút, nhưng chắc chắn rất phức tạp. Những ứng dụng này rất phù hợp cho các nhà phân tích tài chính Hướng dẫn Analyst Trifecta® Hướng dẫn cuối cùng về cách trở thành một nhà phân tích tài chính đẳng cấp thế giới. Bạn có muốn trở thành một nhà phân tích tài chính đẳng cấp thế giới? Bạn đang muốn làm theo các phương pháp hay nhất hàng đầu trong ngành và nổi bật so với đám đông? Quy trình của chúng tôi, được gọi là The Analyst Trifecta® bao gồm phân tích, trình bày và kỹ năng mềm, những người cần tổ chức và xử lý lượng lớn dữ liệu. Thật không may, dữ liệu chúng tôi nhận được không phải lúc nào cũng được tổ chức hoàn hảo và đôi khi có thể có vấn đề như thừa khoảng trắng ở đầu hoặc cuối ô
Trong ví dụ dưới đây, bạn có thể thấy cách công thức TRIM làm sạch dữ liệu Excel.
9. CONCATENATE
Công thức: = A1 & ”thêm văn bản”
Concatenate không thực sự là một chức năng tự nó - nó chỉ là một cách sáng tạo để nối thông tin từ các ô khác nhau và làm cho các trang tính năng động hơn. Đây là một công cụ rất mạnh mẽ cho các nhà phân tích tài chính thực hiện mô hình tài chính (xem hướng dẫn lập mô hình tài chính miễn phí của chúng tôi Hướng dẫn lập mô hình tài chính miễn phí Hướng dẫn lập mô hình tài chính này bao gồm các mẹo Excel và các phương pháp hay nhất về giả định, trình điều khiển, dự báo, liên kết ba báo cáo, phân tích DCF, hơn thế nữa để tìm hiểu thêm).
Trong ví dụ dưới đây, bạn có thể thấy cách kết hợp văn bản “New York” cộng với “,” với “NY” để tạo ra “New York, NY”. Điều này cho phép bạn tạo tiêu đề và nhãn động trong trang tính. Bây giờ, thay vì cập nhật ô B8 trực tiếp, bạn có thể cập nhật ô B2 và D2 một cách độc lập. Với một bộ dữ liệu lớn, đây là một kỹ năng quý giá để bạn có thể sử dụng.
10. Các chức năng CELL, LEFT, MID và RIGHT
Các hàm Excel nâng cao này có thể được kết hợp để tạo ra một số công thức rất nâng cao và phức tạp để sử dụng. Hàm CELL có thể trả về nhiều thông tin khác nhau về nội dung của ô (chẳng hạn như tên, vị trí, hàng, cột, v.v.). Hàm LEFT có thể trả về văn bản từ đầu ô (từ trái sang phải), MID trả về văn bản từ bất kỳ điểm bắt đầu nào của ô (từ trái sang phải) và RIGHT trả về văn bản từ cuối ô (phải sang trái).
Dưới đây là minh họa của ba công thức này đang hoạt động.
Để xem làm thế nào chúng có thể được kết hợp một cách mạnh mẽ với hàm CELL, chúng tôi chia nhỏ nó cho bạn từng bước trong lớp công thức Excel nâng cao của chúng tôi.
Đào tạo thêm về công thức Excel
Chúng tôi hy vọng 10 công thức Excel nâng cao hàng đầu này hữu ích cho bạn. Họ sẽ đi một chặng đường dài để cải thiện kỹ năng phân tích tài chính và mô hình tài chính của bạn Kỹ năng lập mô hình tài chính Tìm hiểu 10 kỹ năng lập mô hình tài chính quan trọng nhất và những gì cần thiết để thành thạo mô hình tài chính trong Excel. Kỹ năng quan trọng nhất: kế toán.
Dưới đây là các tài nguyên Tài chính khác để giúp bạn trở thành người dùng thành thạo Excel:
- Bảng tính công thức Excel Cheat Bảng tính công thức Excel Cheat Bảng tính công thức Excel của Tài chính sẽ cung cấp cho bạn tất cả các công thức quan trọng nhất để thực hiện phân tích và mô hình tài chính trong bảng tính Excel. Nếu bạn muốn trở thành một bậc thầy về phân tích tài chính Excel và một chuyên gia về xây dựng các mô hình tài chính thì bạn đã đến đúng nơi.
- Các phím tắt Excel Phím tắt Excel Phím tắt PC Mac Excel - Danh sách các phím tắt MS Excel quan trọng và phổ biến nhất cho người dùng PC & Mac, tài chính, kế toán. Các phím tắt giúp tăng tốc kỹ năng lập mô hình của bạn và tiết kiệm thời gian. Tìm hiểu chỉnh sửa, định dạng, điều hướng, ruy-băng, dán đặc biệt, thao tác dữ liệu, chỉnh sửa công thức và ô cũng như các đoạn ngắn khác
- Khóa học lỗi Excel miễn phí
- Khóa học Excel nâng cao
- Excel để lập mô hình tài chính