công ty kế toán minh việt

10 Công thức Excel Nâng cao Bạn Phải Biết

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 <= C5), C6, C7)
 
Bất cứ ai dành nhiều thời gian trong các loại mô hình tài chính đề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à cách tuyệt vời để giữ hoặc các công thức dễ kiểm toán hơn và cho người dùng khác hiểu. 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.
 
Để biết chi tiết về cách thực hiện chức năng 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 AND / OR .

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 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á tinh vi. Giả sử bạn muốn tạo một hàm động có thể tổng hợp một số lượng các ô. Với công thức SUM thông thường, bạn bị giới hạn 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.
 
Làm thế nào nó 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 có bao nhiêu ô liên tiếp bạn muốn thêm. 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 này phức tạp hơn một chút 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 (“3”) trừ đi một. Điều này di chuyển kết thúc của công thức tổng trên 2 ô, tổng hợp 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ủa các ô B4: D4 là 15 là công thức bù trừ và tổng cho chúng ta.
Tìm hiểu cách xây dựng công thức này theo từng bước trong khóa học Excel nâng cao của chúng tôi .

4. CHOOSE 

Công thức: = CHOOSE  (lựa chọn, tùy chọn1, tùy chọn2, tùy chọn3)
 
Hàm CHOOSE rất tốt cho 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ả lại “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 bạn nói Excel bạn muốn lựa chọn # 2.
 
Đọc thêm về phân tích kịch bản trong Excel .
 

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à nhà phân tích làm việc trong ngân hàng đầu tư , nghiên cứu cổ phần, hoặc lập kế hoạch và 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ì các công thức này là một phao cứu sinh!
 
Đơn giản chỉ cần đặt, XNPV và XIRR cho phép bạn áp dụng ngày cụ thể cho từng luồ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ả sử khoảng thời gian giữa dòng tiền là bằng nhau. Thường xuyên, 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 theo thời gian đồng đều, và công thức này là cách bạn khắc phục điều đó.
Để xem chi tiết hơn, hãy xem hướng dẫn công thức IRR và XIRR miễn phí của chúng tôi 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 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 .
 
Chúc bạn thành công 1
54.237.183.249

Bình luận

Các bài viết mới

Các tin cũ hơn