[Chủ đề: Ứng dụng excel trong dự toán]
|Dự
toán cập nhật bộ đơn giá 2059-UBND tỉnh Phú Yên 2020|
1. DIỄN GIẢI CHO NGƯỜI KHÁC HIỂU DÙNG HÀM GÌ ?
Ví
dụ: ô A1 = 2, ô A2 = 3, ô A3 = A1*A2 = 6, ô A4 là ô diễn giải hiển thị là 2*3 hoặc =2*3 để khi in ra giấy người khác hiểu số 6 từ đâu ra và đồng thời khi thay đổi giá trị A1, A2 thì ô A4 thay
đổi theo, dùng hàm gì cho ô A4 ?
Chủ đề này ngược lại với chủ đề 1. ở bên trên, ở trên (1.) là diễn giải cho một kêt quả đã có, ở đây là từ chuỗi diễn giải tính ra kết quả.
Ví
dụ: ô A1 có nội dung diễn giải là 2*3, ô A2 là ô kết quả, dùng hàm gì cho ô A2 để tính A2 theo A1 (= 6) ?
Trên đây là 2 nội dung mà em C. ở Tuy An hôm qua vừa chạy vào Tuy
Hòa, đến tận nơi để hỏi về cách sử dụng file điều tiến độ và các nội dung trên,
vì thấy vấn đề cũng rất thực tế nên tôi sẽ phân tích và pots lên blog để anh chị
em kỹ thuật viên, dự toán viên có thể tham khảo hoặc áp dụng cho công việc.
Cách đây ít ngày, trong một bài viết trước Tuấn Anh có chia sẻ 1
file tính giá ca máy hiện tại để sử dụng lập dự toán một số công trình giao
thông, hôm nay chủ đề này cũng lại xuất phát từ 1 file dự toán giao thông.
CHỦ ĐỀ 1: DIỄN GIẢI CHO NGƯỜI KHÁC HIỂU DÙNG HÀM GÌ ?
Bên dưới là hình minh họa, ô B3, B4 là 2 ô giá trị chứa các tham số, ô B4 là ô kết quả (=B3+B4), dùng hàm nào để có 1 ô 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ách khác đơn giản hơn nhưng không định dạng được phần lẻ thập phân:
Mới đầu cứ nghĩ phải dùng đến hàm Text, sau thí nghiệm tiếp thì phát hiện ra 1 cách khác đơn giản đến
không ngờ là dùng hàm & (hàm nối
chuỗi) vẫn được:
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é anh em.
- Link tải file excel: Hàm
&.xls
CHỦ ĐỀ 2: TÍNH KHỐI LƯỢNG MỘT BIỂU THỨC TRONG DỰ TOÁN DÙNG HÀM GÌ?
Dùng cách gì để tính toán khối lượng của 1 ô 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ả ô E8 là 6. (ô D8 là diễn giải còn ô E8 tính ra kết quả).
Tôi đã xem file em C. Tuy An gửi, trong đó người ta dùng cách đặt
các NAME,
NAME có dạng như “=IF('Tên sheet'!$D8<>0;EVALUATE('Tên
sheet'!$D8);0)”
Mỗi 1 NAME gắn 1 hàm EVALUATE (như là đặt
hàm ngầm trong file, cũng là 1 cách, nhưng cách này khó sử dụng ngoài ra file nặng vì phải tạo nhiều NAME). .
- Giải pháp: Tôi đề xuất 1 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 ô, sau đó copy xuống, cách này có thẻ gõ dấu '= phía trước phần diễn giải; thay đỏi giá trị diễn giải sẽ nhảy kết quả tính toán.
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 Atl+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
Như hình
dưới:
Tại cột khối lượng (Cột E), nhập hàm: =KL(ô cần tính), Enter sẽ 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 2*3+3*4
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ụ: ‘=2*3+3*4
3.
Nhập dạng công thức: (để khi số liệu tham chiếu thay đổi thì kết
quả tự thay đổi theo): Tham chiếu có thể trong cùng 1 sheet hoặc sheet khác,
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.
Sau đây là trích dẫn trao đổi, trợ giúp một bạn
khác với cùng chủ đề:
- Bạn P. ở TP Tuy Hòa: em có file chiết tính Kl này, mà đọc
hướng dẫn trên block anh mà không tạo được hàm tính, anh trợ giúp tý, thanks a.
- Ý em là tạo 1 hàm người
dùng để tính ra kết quả của biểu thức bên trái ?
- đúng rồi anh
… (ý bạn P. muôn 1 công thức đơn giản thay cho công thức rất dài đang có trong file)
- ... Em tải về, mở file ra xem
đúng ý chưa nhé.
- A cho xin hướng dẫn luôn nhé, chứ không
gặp file khác chắc bó tay quá.
- ok em, công thức chỉ hoạt động trên file này, file khác phải tạo lại, nhưng cũng nhanh, chỉ mất khoảng 30s.
* Bước 1. Với file khác ko đặt pass thì bấm Alt+F11 để mở cửa sổ soạn thảo VBA, còn file dự toán của anh có đặt pass thì nhập pass…
* Bước 2. Copy và paste
đoạn mã lệnh tô vàng ở trên vào Module mới tạo.
* Bước 3. Đóng cửa sổ VBA
Vậy là sử dụng được hàm.
Xem hình minh họa, với công thức mới gọn hơn rất nhiều. Công thức cũ rất phức tạp, ngoài ra với cách cũ, khi mang file sang máy tính khác không cài Add-In sẽ không hoạt động được, còn cách mới thì ok.
Em, sau khi xem lại
file em, anh thấy cần xét đến trường hợp này:
Có dòng thì ô diễn giải ở cột E, có dòng thì ô diễn giải ở cột F:
Công thức
cần có tính tổng quát để có thể copy, còn ô nào lập công thức cho ô nấy thì mất
công lắm, vậy nên anh sửa lại công thức để nó có thể copy đến các ô khác mà vẫn
đúng, em tải file về xem nhé (chỉ sửa công thức, ko can thiệp vào hàm người
dùng):
Cách bật macro để sử dụng hàm:
Nếu
Excel chưa bật
Macro thì sẽ gặp lỗi #NAME? hoặc không hiển thị kết quả. Do đó
cần bật macro lên thì hàm mới hoạt động được:
- Với Excel 2003: Vào Tolls > Macro > Security… > Chọn LOW
> OK.
- Với Excel 2007, nhấn vào biểu tượng Office Button, chọn Excel Option >
- Với Excel 2010, (2013 tương tự), nhấn vào menu File, chọn Options >
Sau đó đóng hết excel, mở lại và sử dụng hàm KL() như là 1 hàm của excel.
3. HỎI THÊM VỀ DẤU + TRƯỚC CÔNG THỨC:
Em thấy nhiều công thức trong một số file excel của người khác có
dấu cộng (+) phía trước. Ví dụ: =+ROUND(…;2),
và tương tự, không biết dấu + đó để
làm gì ?
Nếu bỏ dấu cộng phía trước đi thì có sao đâu, như vậy xin hỏi tác
dụng dấu + này là gì, có cần thiết không?
* * *
Thật sự là tôi cũng chưa sử dụng nó bao giờ nên cũng chẳng biết tác dụng nó để
làm màu hay làm gì!
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 trong mọi file excel, có thể ứng dụng để biểu đạt giá trị 1 hay nhiều ô trong 1 ô khác (trong cùng 1 sheet, hoặc khác sheet).
- Hàm KL() (viết tắt của chữ Khối Lượng cho dễ nhớ), là hàm do người sử dụng tạo ra, không có sẵn trong
excel, do đó phải tạo nó trong file excel muốn sử dụng, ứng dụng để tính
toán kết quả cho 1 biểu thức (như: 2*3 …) cho 1 hay nhiều ô trong cùng 1 sheet,
hoặc khác sheet.
- Một số file
excel có thấy dấu cộng phía trước công thức để làm gì thì thực sự là tôi cũng
không biết để làm gì.
* * *
Tuy Hòa : 24/04/2021
Ngô Tuấn Anh
2 nhận xét:
Dấu (+) phía trước là thói quen đánh máy ở ô Numpad. Ví dụ bạn gõ công thức: "+A1+B1" thì công thức hiện trong ô là:"=+A1+B1".
Cho em hỏi là kho tắt đi bật lại file đã cài thì phần công thức lại hiện name chứ không còn trả kết quả tính nữa mặc dù đã bật macros setting
Đăng nhận xét