Excel Bài 30: Các mục vượt ngân sách (Over-Budget Items) – Công thức Excel

Bài 30: Các mục vượt ngân sách

Bảng tính của chúng tôi

Khi bạn đã tải xuống bảng tính của chúng tôi, hãy mở tệp trong Excel hoặc ứng dụng bảng tính khác. Như bạn có thể thấy, chúng tôi đã cung cấp một ngân sách khá đơn giản: Mỗi danh mục nằm trên một hàng riêng biệt và có các cột dành cho chi phí dự kiến ​​và chi phí thực tế.

Chúng ta đang cố gắng tìm hiểu điều gì?

Trước khi chúng tôi cố gắng tạo công thức của mình, hãy nghĩ về câu hỏi mà chúng tôi đang cố gắng trả lời: “Những chi phí nào vượt quá ngân sách?”

Làm thế nào để chúng tôi biết nếu một cái gì đó vượt quá ngân sách? Trong ví dụ này, chúng ta có thể so sánh chi phí dự kiến ​​và chi phí thực tế. Nếu chi phí thực tế cao hơn chi phí dự kiến, thì nó vượt quá ngân sách.

Viết công thức

Bây giờ chúng ta chỉ cần quyết định cách thể hiện ý tưởng này trong một công thức. Một cách để so sánh hai giá trị là trừ đi cái này từ cái kia. Trong ví dụ của chúng tôi, chúng tôi có thể chỉ cần trừ chi phí thực tế khỏi chi phí dự kiến.

Nếu kết quả là âm, thì chúng tôi biết danh mục đó vượt quá ngân sách. Trong ví dụ của chúng tôi, chúng tôi dự kiến ​​chi 14.000 đô la cho Cơ sở và chúng tôi thực sự đã chi 15.000 đô la. Nếu chúng tôi trừ 15.000 đô la từ 14.000 đô la, kết quả sẽ là -$1,000 (âm $1.000). Chúng ta cũng có thể nói thể loại này là 1.000 đô la vượt quá ngân sách.

Chi phí dự kiến ​​của chúng tôi ở cột B và chi phí thực tế của chúng tôi ở cột C. Vì vậy, đối với danh mục đầu tiên, công thức để so sánh các giá trị này sẽ là:

=B2-C2

Hãy nhập công thức này vào ô D2:

Tiếp theo, chúng ta chỉ cần kéo thanh điều khiển điền xuống để thêm công thức vào các ô khác trong cột:

Bây giờ chúng ta có thể thấy rằng một số danh mục có giá trị âm, có nghĩa là chúng vượt quá ngân sách.

Mặc dù bảng tính này cho chúng ta câu trả lời, nhưng nó không dễ đọc lắm. Có một vài cách khác nhau mà chúng tôi có thể làm cho các hạng mục vượt quá ngân sách trở nên đáng chú ý hơn. Ví dụ:

• Chúng ta có thể lọc dữ liệu để chỉ hiển thị các hàng có kết quả âm.
• Chúng ta có thể thêm định dạng có điều kiện để làm cho các ô âm có màu đỏ.
• Chúng ta có thể chọn dữ liệu và tạo biểu đồ điều này sẽ làm cho dữ liệu trực quan hơn.

Tất cả các giải pháp này sẽ hiệu quả—không có câu trả lời đúng hay sai. Bạn có thể muốn thử nghiệm các phương pháp khác nhau để xem phương pháp nào bạn cho là rõ ràng nhất. Trong ví dụ của chúng tôi, chúng tôi sẽ thêm định dạng có điều kiện.

Đầu tiên, đảm bảo các giá trị trong cột D được chọn. Tiếp theo, chúng ta sẽ cần tạo quy tắc định dạng có điều kiện tìm kiếm các ô chứa giá trị nhỏ hơn 0. Quá trình này sẽ khác nhau tùy thuộc vào chương trình bảng tính bạn đang sử dụng:

• Với Excel 2007-2019: Từ tab Trang Chủ (Home), chọn Định dạng có điều kiện > Đánh dấu quy tắc ô > Ít hơn.
  
• Với Excel 2003 trở về trước: Đi đến Định dạng > Định dạng có điều kiện.
• Với Google Trang tính: Đi đến Định dạng > Định dạng có điều kiện.

Đảm bảo rằng quy tắc được đặt thành “Nhỏ hơn” và giá trị được đặt thành “0” (để tìm các ô nhỏ hơn 0) và chọn một tùy chọn định dạng nổi bật. Trong trường hợp này, nền đỏ với chữ đỏ là một lựa chọn tốt.

Nếu bạn đang sử dụng Microsoft Excel, một tùy chọn khác là áp dụng một Currency định dạng số sẽ làm cho các số âm xuất hiện dưới dạng văn bản màu đỏ.

Đó là nó! Bây giờ chúng ta có thể dễ dàng nhìn thấy những hạng mục vượt quá ngân sách.