Tính dự toán đến bảng cuối thì bị ‘đứng bánh’ ngay vòng gửi xe !!!!

LỖI THAM CHIẾU NGƯỢC TRONG EXCEL VÀ CÁCH KHẮC PHỤC

Đây là vấn đề rất thường gặp khi lập dự toán xây dựng, ở bước cuối: Tính tổng mức đầu tư, rất nhiều anh chị em kỹ sư định giá cho đến sinh viên mới ra trường gặp lỗi này mà không biết khắc phục làm sao, không biết tìm đâu và hỏi ai…

Vì vậy nên hôm nay Tuấn Anh hết sức cố gắng dành thời gian để viết bài này, chia sẻ thông tin để anh em nào gặp tình huống này thì có thể tham khảo. Và để biết đâu có anh chị em nào đã tìm nát trên Google không có, bỗng lạc đến đây và: A, nó đây rồi!!!

Câu chuyện bắt đầu từ một ngày đẹp trời…. “Alo, em mới gửi mail nhờ anh xem giúp em file dự toán tính đến tổng mức đầu tư bị báo lỗi mũi tên xanh, không thẩm tra được…”

1. Tìm hiểu vấn đề

Sau khi xem file tôi hiểu ra đây là lỗi “tham chiếu ngược trong excel”

Cụ thể: File bị lỗi công thức tính chi phí thẩm định hồ sơ báo cáo kinh tế, chi phí thẩm tra quyết toán… làm cho hồ sơ bị đứng tại vòng gửi xe…

Trong excel, một giá trị A được tạo thành từ giá trị B (tức là A = … +/-/*/: với B) thì không thể có chiều ngược lại (B = … A),

Ở file dự toán trên ô tổng cuối Z = Sum (tất cả các ô bên trên, vậy đi cho dễ hiểu), trong khi theo qui định thì các ô có số 0 tô màu vàng lại = ô tổng cuối Z nhân với 1 tỷ lệ % K để ra giá trị.

Điều này excel không bao giờ chấp nhận vì nó cho rằng phi logic, phi toán học, nếu ta gõ công thức vào 2 vị trí đó sẽ tạo ra một “tham chiếu ngược”, excel sẽ xuất hiện mũi tên màu xanh 2 chiều và cho kết quả = 0. Giống như bài toán "con gà - quả trứng cái nào có trước", giả thuyết cái nào có trước đều có lý và chấp nhận, cái có trước sẽ sinh ra cái có sau, nhưng không thể 2 cái cùng có 1 lúc.

Mới đầu tôi nghĩ chắc không có cách giải quyết bằng công thức, và có lẽ vẫn như lâu nay là anh em gõ 1 số áng chừng vào các vị trí tô màu (để lấy kêt quả gần đúng), nhưng sau đó nghĩ lại thì đã tìm ra một cách khá là hay.

(Nói thêm cho rõ: Theo các qui định hiện hành, rất 'ngược đời' và khó hiểu của Bộ Tài Chính, một số khoản mục như thẩm định hồ sơ…, thẩm tra quyết toán… lại tính theo tỷ lệ % của con số cuối (tổng mức đầu tư), mà để có được con số cuối thì lại phải có các khoản mục con ở phía trên, lòng vòng như vậy nên excel “không chịu”, vấn đề là ở chỗ đó, sinh ra rắc rối trong thực hiện suốt bấy lâu nay).

2. Cách xử lý lỗi như sau:

Chúng ta sẽ khảo sát để tìm ra 1 công thức chung tổng quát, để có thể áp dụng với mọi trường hợp khác nhau. Nếu chỉ tập trung vào trường hợp cụ thể này, thì qua trường hợp khác sợ là anh em sẽ không làm được vì cũng khá là phức tạp.

Cách làm là biến đổi công thức tính để đưa về việc giải phương trình bậc nhất 1 ẩn số.

Anh em xem kỹ sẽ hiểu vấn đề và làm được nếu gặp tính huống này nhé.

Vì excel sẽ không có ngoại lệ, mọi phiên bản office và không chừa một ai với lỗi này:

Tổng quát và đơn giản lại bảng tính như sau cho dễ hiểu:

Như bảng tính trên và các ký hiệu trong hình (x1, x2, x3 là các ẩn số, k1 k2, k3 là các hằng số, Z là tổng bao gồm x1, x2, x3), làm sao để tính được các giá trị x1, x2, x3 ? (để ý là công thức Z và n4, x1, x2, x3 có liên quan 2 chiều với nhau)

Nếu gõ công thức vào x1: = 1%*Z, ngay sau khi Enter, chắc chắn excel sẽ báo lỗi:

Nguyên nhân là x1, x2, x3 = 1 tỷ lệ của Z mà Z lại Sum (tổng) của x1, x2, x3.

Biến đổi công thức tính x1, x2, x3 như sau:

Giả sử đã tính được x1, x2, x3 theo Z, vậy:

x1 = k1*Z

x2 = k2*Z

x3 = k3*Z

<=> x1+x2+x3 = Z*(k1+k2+k3)        (1)

Phương trình (1) là phương trình bậc nhất 3 ẩn số (x1, x2, x3)

Để đơn giản, đặt X = x1+x2+x3 và K =k1+k2+k3, phương trình (1) thành:

X = K*Z       (*)

Đặt Z1 = Z – (x1+x2+x3) = Z - X  (Z1 = tổng tất cả các trị số nhưng không bao gồm x1, x2, x3, mục đich để xác định được Z1, vì x1, x2, x3 chưa biết)

=> Z = Z1 + X, thay vào (*):

<=> X = K*(Z1 + X)

<=> X = K*Z1 + K*X

<=> X – K*X = K*Z1

<=> X*(1-K) = K*Z1

<=> X = K*Z1/(1-K)       (2)

Đến đây, bài toán dẫn đến việc giải phương trình bậc nhất 1 ẩn số là X.

Đã có K và Z1, thay vào sẽ tính được X, bài toán xem như được giải.

Từ X và k1, k2, k3 sẽ tính được x1, x2, x3 theo công thức tỷ lệ:

x1 = X*(k1/K); x2 = X*(k2/K); x3 = X*(k3/K)

Tính xong nhập số vào các ô x1, x2, x3 ở cột E là xong.

Trên đây là lý thuyết

Bây giờ kiểm tra với số liệu thực tế xem có đúng không:

Đầu tiên tính Z1:

Z1 = 100.000.000+50.000.000+50.000.000+100.000+50.000.000 = 250.100.000

(không bao gồm x1, x2, x3)

Tính K:

K = k1+k2+k3 = (1+2+3)% = 6% (=0,06)

Thay vào (2), được:

X = 0,06*250.100.000/(1-0,06) = 15.963.830

x1 = X*(k1/K) = 15.963.830*(0,01/0,06) = 2.660.638

Tương tự:

x2 = 5.321.277

x3 = 7.981.915

Nhập vào vị trí các ô x1, x2, x3 trong bảng tính (cột E):

Ta được tổng Z = 266.063.830

Kết quả Z đã chấp nhận các giá trị này.

Thử kiểm tra lại sau khi nhập giá trị x1, x2, x3:

x1 = 1%*Z = 1%*266.063.830 = 2.660.638 (đúng)

x2 = 2%*Z = 2%*266.063.830 = 5.321.277 (đúng)

x3 = 3%*Z = 3%*266.063.830 = 7.981.915 (đúng)

Thử nghiệm cho thấy:

Trường hợp chỉ có 1 hoặc 2 hoặc 4, 5 giá trị phụ thuộc Z công thức trên vẫn đúng.

Công thức (2) là công thức tổng quát đúng trong mọi trường hợp.

Chỉ việc thay số liệu thực tế sẽ có giá trị tương ứng của x1, x2, x3, sau đó nhập tay hoặc copy dán giá trị (không được link) vào bảng tính là ok.

Trên đây là cách khắc phục lỗi tham chiếu ngược trong excel bằng cách biến đổi công thức và cho kết quả đúng chính xác 100%, không phải dùng phương pháp mò dần đến khi gần đúng.

Và quan trọng: Có thể áp dụng cho thực tế công việc.

Tải file excel: TẠI ĐÂY

* * *

Tuy Hòa, 29/9/2020.

Ngô Tuấn Anh.

* * *

(Chẳng ai muốn phức tạp, mất thời gian được gì, nhưng do hoàn cảnh thành ra phải vậy)