Mỗi nhà phân tích tài chính dành nhiều thời gian trong Excel hơn là 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.
1. INDEX MATCH
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 [một số hạn chế và hạn chế]. INDEX MATCH là một 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 của bạn và mô hình tài chính lên cấp độ tiếp theo.
Hàm 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.
Dưới đây là 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 tra cứu 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 là cả hai biến trong công thức, chúng ta có thể thay đổi cả hai biế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 MATCH MATCH MATCH trong Excel .
2. IF kết hợp với AND / OR
Công thức: = IF [VÀ [C2> = C4, C2 = 21 ″]
Hai công thức tiên tiến này là sử dụng tuyệt vời các hàm có điều kiện. SUMIF thêm tất cả các ô đáp ứng các tiêu chí nhất định và COUNTIF tính 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 số chai rượu sâm banh bạn cần cho một sự kiện 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.
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ân hàng thương mại , bất động sản, FP & A hoặc bất kỳ vị trí phân tích tài chính nào đề cập đế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 gian vay. Bạn có thể sử dụng nó cùng 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 đó thanh toán tiền gốc và tiền lãi riêng.
Dưới đây là ví dụ về cách sử dụng chức năng PMT để nhận khoản thanh toán thế chấp hàng tháng cho khoản vay 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, nhưng chắc chắn rất phức tạp. Các ứng dụng này rất tuyệt vời cho các nhà phân tích tài chính cần tổ chức và thao tác với 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ư khoảng trống ở đầu hoặc cuối ô
Trong ví dụ dưới đây, bạn có thể xem cách thức công thức TRIM dọn sạch dữ liệu Excel.
9. CONCATENATE
Công thức: = A1 & ”văn bản khác”
Kết nối không thực sự là một chức năng riêng của nó, nó chỉ là một cách sáng tạo của việc tham gia thông tin từ các tế bào khác nhau, và làm cho bảng 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 để tìm hiểu thêm].
Trong ví dụ bên dưới, bạn có thể xem cách văn bản “New York” cộng “,” được kết hợp với “NY” để tạo “New York, NY”. Điều này cho phép bạn tạo tiêu đề động và nhãn 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 tập dữ liệu lớn, đây là một kỹ năng có giá trị để bạn sử dụng.
10. Hàm 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 tiên tiến 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 một ô [tên, vị trí, hàng, cột, v.v.]. Hàm LEFT có thể trả về văn bản từ đầu ô [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 ô [trái sang phải] và hàm RIGHT trả về văn bản từ cuối ô [phải sang trái].
Dưới đây là minh họa cho ba công thức này.
Để xem cách 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 sẽ 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 ta .