Lập dự toán xây dựng trên excel: Điểm qua một số hàm (công thức) Excel cho công việc

[Chủ đề: Ứng dụng excel trong dự toán]

Làm thế nào để giúp người khác hiểu công thức?

Với Excel, nếu bạn muốn người khác hiểu được giá trị của một ô khi in ra, bạn cần sử dụng công thức diễn giải. Ví dụ, nếu ô A1 = 2, ô A2 = 3, và ô A3 = A1A2 = 6, ô A4 có thể hiển thị diễn giải là “23″ hoặc “=2*3” để người khác hiểu số 6 là từ đâu ra và khi thay đổi giá trị A1 và A2, ô A4 sẽ tự động thay đổi theo. Vậy hãy sử dụng hàm nào cho ô A4?

Tính khối lượng một biểu thức trong dự toán

Trái ngược với chủ đề trên, chủ đề này tập trung vào việc tính toán kết quả từ một chuỗi diễn giải. Ví dụ, nếu ô A1 có nội dung diễn giải là “2*3” và ô A2 là kết quả, bạn cần sử dụng hàm nào cho ô A2 để tính A2 theo A1 (=6)?

Đây là hai chủ đề mà tôi đã thảo luận với C. ở Tuy An, người đã đến Tuy Hòa để tìm hiểu về cách sử dụng file điều tiến độ và các nội dung tương tự. Vì vấn đề này rất thực tế, tôi sẽ phân tích và chia sẻ trên blog để những kỹ thuật viên và dự toán viên có thể tham khảo và áp dụng vào công việc.

Gần đây, trong một bài viết trước đó, Tuấn Anh đã chia sẻ một file tính giá ca máy hiện tại để sử dụng trong việc lập dự toán cho một số công trình giao thông. Và ngày hôm nay, chủ đề này xuất phát từ một file dự toán giao thông khác.

Chủ đề 1: Diễn giải cho người khác hiểu dùng hàm gì?

Dưới đây là ví dụ minh họa, ô B3 và B4 chứa giá trị của các tham số, và ô B4 là kết quả (=B3+B4). Bạn cần sử dụng hàm nào để có một ô hiển thị phần diễn giải công thức như trong ô B6?

  • Giải pháp: Sử dụng hàm Text để diễn giải như hình trên.

Có một cách đơn giản hơn nhưng không định dạng được phần lẻ thập phân. Ban đầu tôi nghĩ rằng cần phải sử dụng hàm Text, nhưng sau một thí nghiệm, tôi đã phát hiện ra một cách khác đơn giản và không ngờ đó là sử dụng hàm & (hàm nối chuỗi):

Công thức trong ô B6 đổi thành: =”=” & B3 & “+” & B4

Vậy hàm đề xuất thay thế là hàm & (hàm nối chuỗi) nhé.

Chủ đề 2: Tính khối lượng một biểu thức trong dự toán dùng hàm gì?

Cách nào để tính toán khối lượng của một ô diễn giải? Ví dụ, ô D8 nhập: 2*3, ô E8 nhập =tên hàm(D8), nhấn Enter, kết quả của ô E8 là 6 (ô D8 là diễn giải, ô E8 tính ra kết quả).

Tôi đã xem file từ C. Tuy An gửi, trong đó người ta sử dụng cách đặt các NAME, mỗi NAME gắn 1 hàm EVALUATE (như là đặt hàm ngầm trong file). Đó cũng là một cách, nhưng cách này khó sử dụng ngoài ra file và làm tăng kích thước của file vì phải tạo nhiều NAME.

  • Giải pháp: Tôi đề xuất một cách khác đơn giản hơn là tạo 1 hàm (công thức) người dùng, sau đó thiết lập công thức cho 1 ô và copy xuống. Cách này cho phép bạn gõ dấu ‘=’ phía trước phần diễn giải và khi thay đổi giá trị diễn giải, kết quả tính toán cũng sẽ tự động thay đổi.

Cách làm như sau (chỉ làm 1 lần cho 1 file):

  1. Mở file excel cần tạo hàm người dùng, nhấn tổ hợp phím Alt+F11 để mở cửa sổ VBA.
  2. Click menu Insert > Module.
  3. Copy toàn bộ phần text màu vàng bên dưới và dán vào khung bên phải:
Function KL(s As String) As Double
    T = Replace(s, " ", "")
    T = Replace(T, ",", ".")
    T = Replace(T, "=", "")
    T = Replace(T, "x", "*")
    KL = Round(Evaluate(T), 3)
End Function
  1. Lưu và trở về màn hình làm việc của Excel.
    Tại cột khối lượng (Cột E), nhập hàm: =KL(ô cần tính), Enter để có kết quả.

Cách nhập phần diễn giải:
Có ít nhất 3 cách sau đây:

  1. Nhập text, ví dụ nhập 23+34.
  2. Nếu muốn có dấu ‘=’ ở trước vẫn được, khi đó phải nhập dấu nháy đơn ‘ ngay trước dấu ‘=’, ví dụ: ‘=”23+34′.
  3. Nhập dạng công thức để số liệu tham chiếu thay đổi, ví dụ: =”=” & Sheet1!B2 & “+” & Sheet1!B3

Trong đó:

  • & là hàm nối chuỗi có sẵn của Excel để diễn đạt biểu thức.
  • Sheet1 là tên sheet khác, Sheet1!B2, Sheet1!B3 là địa chỉ các ô muốn đưa dữ liệu vào.
  • Các dấu “=” và dấu “+” nằm trong ngoặc kép.

Để sử dụng hàm KL, bạn cần bật Macro trong Excel. Nếu Excel chưa bật Macro, sẽ gặp lỗi #NAME? hoặc không hiển thị kết quả. Do đó, hãy bật Macro lên để hàm KL hoạt động được.

  1. Hỏi thêm về dấu + trước công thức

Trong một số file Excel của người khác, bạn có thể thấy một dấu cộng (+) phía trước công thức. Ví dụ: =+ROUND(…), và tương tự. Bạn không biết dấu + đó được sử dụng để làm gì và có cần thiết không?

Thực sự, tôi cũng chưa sử dụng nó bao giờ nên không biết tác dụng của nó là gì. Tuy nhiên, nếu bỏ dấu cộng phía trước thì cũng không có vấn đề gì. Vì vậy, tôi không chắc liệu dấu + đó có tác dụng gì hay không.

Tóm tắt nội dung bài viết:

  • Hàm & là hàm nối chuỗi có sẵn trong Excel, dùng để biểu đạt giá trị của một hay nhiều ô trong một ô khác (trong cùng một sheet hoặc khác sheet).
  • Hàm KL (viết tắt của Khối Lượng) là hàm do người sử dụng tạo ra, không có sẵn trong Excel. Bạn cần tạo nó trong file Excel muốn sử dụng và ứng dụng để tính toán kết quả cho một biểu thức trong cùng một sheet hoặc khác sheet.
  • Một số file Excel có thể có dấu cộng phía trước công thức để làm gì, tôi thật sự không biết.

Tuy Hòa, 24/04/2021
Ngô Tuấn Anh