Rất nhiều người xem Microsoft Excel là một công cụ chỉ hữu ích trong kinh doanh. Sự thật là, có rất nhiều cách nó có thể mang lại lợi ích cho bạn ở nhà. Chìa khóa để tìm ra cách sử dụng Excel trong cuộc sống hàng ngày là chọn đúng công thức giải quyết vấn đề.
Cho dù bạn đang mua một khoản vay mua ô tô mới, muốn tìm ra khoản đầu tư quỹ tương hỗ nào là tốt nhất cho bạn hoặc nếu bạn chỉ đang cố gắng hiểu ý nghĩa của tài khoản ngân hàng của mình, Excel là một công cụ mạnh mẽ có thể giúp ích.
Chúng tôi đã chọn ra 15 công thức đơn giản, mạnh mẽ và giúp bạn giải quyết các vấn đề phức tạp.
Công thức tài chính
Mua sắm cho một ngôi nhà mới và bối rối bởi tất cả các biệt ngữ thế chấp? Tìm kiếm một chiếc xe mới và bị nhầm lẫn bởi các điều khoản cho vay xe hơi mà nhân viên bán hàng tiếp tục ném vào bạn?
Đừng sợ hãi. Trước khi bạn vay tiền, hãy nghiên cứu với Excel bên cạnh bạn!
1. PMT—Payment
Bất cứ khi nào bạn so sánh bất kỳ điều khoản cho vay nào và muốn nhanh chóng tìm ra khoản thanh toán hàng tháng thực tế của mình với các biến thể khác nhau trong các điều khoản, hãy tận dụng công thức PMT mạnh mẽ (và đơn giản) .
Đây là những gì bạn cần để sử dụng công thức này:
- Lãi suất của khoản vay
- Thời hạn của khoản vay (bao nhiêu thanh toán?)
- Nguyên tắc bắt đầu của khoản vay
- Giá trị trong tương lai, nếu vì một lý do nào đó, khoản vay sẽ được coi là đã được trả hết trước khi nó về 0 (không bắt buộc)
- Loại khoản vay 0 0 nếu thanh toán vào cuối mỗi tháng hoặc 1 nếu đến hạn vào lúc bắt đầu (tùy chọn)
Đây là một cách tuyệt vời để nhanh chóng so sánh nhiều khoản vay khác nhau để xem các khoản thanh toán của bạn sẽ như thế nào. Tạo một bảng Excel liệt kê mọi khoản vay tiềm năng và tất cả thông tin có sẵn về chúng. Sau đó, tạo một cột Thanh toán trên mạng và sử dụng công thức PMT .
Chỉ cần lấy góc dưới bên phải của ô PMT bạn vừa tạo và kéo nó xuống để nó tính tổng số thanh toán cho tất cả các điều khoản cho vay được liệt kê trong bảng. Các tính năng tự động điền Excel là một trong những tính năng mà bạn sẽ sử dụng rất nhiều với những thủ đoạn.
Bây giờ bạn có thể so sánh các khoản thanh toán hàng tháng cho các loại khoản vay khác nhau.
(Một lời cảm ơn rất lớn đến Mark Jones (@redtexture trên Twitter), người đã chỉ ra rằng đối với các công thức PMT và FV, bạn phải rất cẩn thận về việc sử dụng cùng kỳ hạn trong trường hợp này bằng cách sử dụng thanh toán hàng tháng đòi hỏi phải chia thời hạn lãi suất 12 tháng)
Đây là lý do tại sao độc giả của chúng tôi rất tuyệt vời. Cảm ơn đã giúp đỡ với sửa chữa này Mark!
2. FV—Future Value
Công thức tiếp theo có ích khi bạn đang muốn đầu tư một số tiền vào một thứ gì đó như Chứng chỉ tiền gửi (CD) và bạn muốn biết nó sẽ có giá trị gì vào cuối kỳ hạn.
Đây là những gì bạn cần biết để sử dụng công thức FV :
- Lãi suất của khoản vay
- Số lượng thanh toán (hoặc thời hạn đầu tư tính theo tháng)
- Thanh toán cho từng thời kỳ (thường là hàng tháng)
- Số dư bắt đầu hiện tại (tùy chọn)
- Loại khoản vay 0 0 nếu thanh toán vào cuối mỗi tháng hoặc 1 nếu đến hạn vào lúc bắt đầu (tùy chọn)
Vì vậy, hãy so sánh một số đĩa CD bằng cách sử dụng các thuật ngữ mà bạn biết từ thông tin mà các ngân hàng đã cung cấp cho bạn. Trong ví dụ dưới đây, giả sử bạn có khoản thừa kế 20.000 đô la để đầu tư vào CD.
Lãi suất một lần nữa được thể hiện dưới dạng thập phân (lấy lãi suất mà ngân hàng đưa cho bạn và chia cho 100). Thanh toán bằng 0 vì CD thường dựa trên giá trị bắt đầu và giá trị thanh toán trong tương lai. Đây là so sánh trông giống như khi bạn sử dụng công thức FV cho mọi CD bạn đang xem xét.
Không nghi ngờ gì, CD lãi suất cao hơn trong một khoảng thời gian dài trả nhiều hơn. Hạn chế duy nhất là bạn không thể chạm vào bất kỳ khoản tiền nào trong ba năm, nhưng đó là bản chất của đầu tư!
3-4. Công thức logic IF và AND
Hầu hết các ngân hàng ngày nay cung cấp cho bạn khả năng tải xuống các giao dịch ngân hàng trị giá gần một năm về định dạng như CSV. Đây là một định dạng hoàn hảo để phân tích chi tiêu của bạn bằng Excel, nhưng đôi khi dữ liệu bạn nhận được từ các ngân hàng rất vô tổ chức.
Sử dụng các công thức logic là một cách tuyệt vời để phát hiện bội chi.
Lý tưởng nhất là ngân hàng sẽ tự động phân loại chi tiêu của bạn hoặc bạn đã thiết lập tài khoản của mình để mọi thứ được đưa vào danh mục chi tiêu. Ví dụ: bất kỳ nhà hàng nào chúng tôi sẽ được gắn nhãn với nhãn EatOut .
Điều này giúp bạn dễ dàng sử dụng một công thức hợp lý để xác định bất cứ khi nào chúng tôi đi ăn và chi hơn 20 đô la.
Để làm điều này, chỉ cần tạo một công thức logic trong một cột mới để tìm kiếm bất kỳ giá trị nào trong đó cột danh mục là Ăn tối, và cột giao dịch lớn hơn – $ 20
Lưu ý: So sánh dưới đây cho thấy <11, ít hơn, bởi vì các giá trị trong cột C đều âm.
Đây là những gì trông giống như:
Sử dụng IF và AND cùng nhau trong một công thức có vẻ khó khăn, nhưng thực ra nó khá đơn giản. Câu lệnh IF sẽ xuất ra số tiền đô la (C2) nếu câu lệnh AND là đúng hoặc SAI nếu không. Câu lệnh AND kiểm tra xem danh mục đó có phải là Ăn tối hay không và giao dịch có lớn hơn $ 20 hay không.
Có bạn có nó! Không cần phải tự sàng lọc tất cả các giao dịch đó, giờ đây bạn biết chính xác những thời điểm khi bạn sử dụng quá mức trong một danh mục nhất định.
5-6. COUNT và COUNTIF
Excel có thể giúp bạn nhanh chóng sắp xếp và sắp xếp các giá trị là một danh sách. Hãy lấy ví dụ về PTC. Đây là danh sách quyên góp từ các thành viên cộng đồng.
Chúng tôi muốn xem bao nhiêu lần tên của một người xuất hiện trong danh sách. Để làm điều này, bạn có thể kết hợp công thức COUNT với công thức IF . Đầu tiên, tạo một cột để kiểm tra xem người đó có phải là Michelle hay không. Công thức sẽ sử dụng câu lệnh IF để điền vào ô với 1 1 nếu điều này là đúng.
Tiếp theo, tạo một cột khác tính số lần bạn tìm thấy Michelle Johnson trong danh sách.
Điều này cung cấp cho bạn số lượng của mỗi vị trí trong Cột E nơi có 1 thay vì trống.
Vì vậy, đây là cách đơn giản nhất để làm loại việc này, nhưng nó đòi hỏi hai bước.
6-8. SUMIF, COUNTIF, AVERAGEIF
Nếu bạn không phiền khi sử dụng công thức nâng cao hơn một chút, bạn có thể cân nhắc sử dụng một trong nhiều công thức kết hợp IF IF như SUMIF , COUNTIF hoặc AVERAGEIF . Điều này cho phép bạn thực hiện công thức (COUNT, SUM hoặc AVERAGE) nếu điều kiện logic là đúng. Đây là cách nó hoạt động bằng cách sử dụng ví dụ trên.
Công thức này xem xét cột A, chứa tất cả các tên của nhà tài trợ và nếu ô trong phạm vi khớp với tiêu chí trong dấu ngoặc kép, thì nó sẽ được tính theo một. Điều này cung cấp cho bạn tổng số lần tất cả các tên nhà tài trợ bằng với Michelle Michelle Johnson trong một bước duy nhất.
Nó nhanh hơn nhiều so với sử dụng hai cột, nhưng hơi phức tạp – vì vậy hãy sử dụng phương pháp phù hợp nhất với tình huống của bạn.
Các công thức SUMIF và AVERAGEIF hoạt động theo cùng một cách, chỉ với các kết quả toán học khác nhau. Sử dụng SUMIF trong ví dụ này sẽ cung cấp cho bạn tổng số tiền quyên góp cho Michelle Johnson nếu bạn sử dụng nó thay thế.
9. LEN
Một cách thú vị để sử dụng điều này trong ví dụ trên là làm nổi bật các nhà tài trợ đã quyên góp hơn 1.000 đô la bằng cách đếm số chữ số trong cột quyên góp. Nếu độ dài của số là 4 hoặc lớn hơn, thì họ đã quyên góp ít nhất $ 1.000.
Bây giờ bạn có thể thêm định dạng bổ sung để dễ nhìn hơn.
Để thực hiện việc này, bạn cần làm nổi bật tất cả các ô trong cột Đóng góp, chọn tab Trang chủ trong menu và nhấp vào Định dạng có điều kiện trên thanh công cụ. Sau đó chọn Sử dụng công thức để xác định các ô cần định dạng .
Đặt phạm vi dưới các giá trị Định dạng trong đó công thức này là đúng: thành cột / phạm vi nơi tất cả các đầu ra công thức LEN của bạn được hiển thị.
Trong ví dụ này, nếu bạn thực hiện điều kiện thì >> 3, thì bất cứ thứ gì trên 1.000 đô la sẽ nhận được định dạng đặc biệt. Đừng quên nhấp vào nút Định dạng và chọn loại định dạng đặc biệt bạn muốn cho các định dạng này.
Ngoài ra, một lưu ý nhanh chóng. Bạn sẽ nhận thấy phạm vi của tôi được định nghĩa là Hồi $ E2: $ E11, chứ không phải là $ E $ 2: $ E $ 11. Khi bạn chọn phạm vi, nó sẽ mặc định là phạm vi trước, nó sẽ không hoạt động. Bạn cần sử dụng địa chỉ tương đối như trong hình trên. Sau đó, định dạng có điều kiện của bạn sẽ hoạt động dựa trên điều kiện của phạm vi thứ hai.
Tổ chức tải xuống tài chính và ngân hàng
Đôi khi, khi bạn tải xuống thông tin từ các doanh nghiệp, cho dù đó là ngân hàng hay công ty bảo hiểm y tế của bạn, định dạng của dữ liệu đến không phải lúc nào cũng khớp với những gì bạn cần.
Ví dụ: giả sử rằng trong dữ liệu được xuất từ ngân hàng của bạn và bạn được cung cấp ngày ở định dạng chuẩn.
Nếu bạn muốn thêm một cột mới của riêng bạn với cột của riêng bạn được đặt trước theo năm và bao gồm thông tin Người nhận thanh toán (cho mục đích sắp xếp của riêng bạn), trích xuất các mẩu thông tin từ một cột thực sự dễ dàng.
10-14. 10-14. RIGHT, LEFT, TEXT, and CONCATENATE
Bạn có thể kéo năm ra khỏi văn bản trong cột đó bằng công thức ĐÚNG .
Công thức trên là yêu cầu Excel lấy văn bản trong cột D và trích xuất bốn ký tự từ phía bên phải. Công thức CONCATENATE ghép bốn chữ số đó lại với văn bản Người nhận thanh toán từ cột E.
Hãy nhớ rằng nếu bạn muốn trích xuất văn bản từ một ngày, bạn sẽ cần phải chuyển đổi nó thành định dạng văn bản (thay vì ngày) bằng cách sử dụng công thức Hồi = văn bản (D2, ĐM mm / dd / yyyy)). Sau đó, bạn có thể sử dụng công thức ĐÚNG để rút ra năm.