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.
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:
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
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)
Không có nhận xét nào:
Đăng nhận xét