Nhờ các anh/chị giúp em bài toán " tính chi phí hạng mục"

Cát Lượng

Thành viên thường trực
Tham gia ngày
14 Tháng mười một 2018
Bài viết
320
Được thích
36
Điểm
185
Tuổi
32
Em đang tính theo cách trên ( như trong bảng tính) nhưng với bảng tính có tầm 2000 dòng thì rất lâu và dễ nhầm lẫn, em nhờ các anh/chị viết giúp em code ( hay nêu cho em xin ý tưởng) để tính bài toán được thuận lợi hơn.

Bài toán như sau:

1. Ô J16, Ô K16, Ô L16, Ô M16 là các ô là “chi phí xây dựng (Gxd)” cần tính :

Chi phí xây dựng (Gxd) = Tổng chi phí các hạng mục

Cụ thể Ô J16 (chi phí xây dựng) = J17 (chi phí hạng mục phá dỡ) + J31 ( chi phí hạng mục nhà làm việc ) + J118 (chi phí hạng mục san lấp……)

Các ô K16, Ô L16, Ô M16 cũng tương tự.

Các ô J17, J31, J118 là các chi phí xây dựng của các hạng mục.

2. Chi phí của các hạng mục = tổng chi phí của từng phần (hạng mục nhỏ) trong hạng mục lớn đó

Cụ thể Ô J17 (chi phí hạng mục phá dỡ ) = J18 (chi phí phá dỡ trạm y tế) + J31 (chi phí phá dỡ cổng tường rào),

Các ô J17, J31, J118 tính tương tự

3. Chi phí của các hạng mục nhỏ trong hạng mục lớn = tổng các dòng có đánh số thứ tự cho đến khi chuyển sang một hạng mục nhỏ khác.

Cụ thể Ô J18 (phá dỡ trạm y tế) = sum (J19: J24)

Các hạng mục nhỏ khác có tính chất tương tự.
anh.png
 

File đính kèm

Lần chỉnh sửa cuối:

snow25

Thành viên gắn bó
Tham gia ngày
24 Tháng bảy 2018
Bài viết
2,350
Được thích
2,177
Điểm
360
Em đang tính theo cách trên ( như trong bảng tính) nhưng với bảng tính có tầm 2000 dòng thì rất lâu và dễ nhầm lẫn, em nhờ các anh/chị viết giúp em code ( hay nêu cho em xin ý tưởng) để tính bài toán được thuận lợi hơn.

Bài toán như sau:

1. Ô J16, Ô K16, Ô L16, Ô M16 là các ô là “chi phí xây dựng (Gxd)” cần tính :

Chi phí xây dựng (Gxd) = Tổng chi phí các hạng mục

Cụ thể Ô J16 (chi phí xây dựng) = J17 (chi phí hạng mục phá dỡ) + J31 ( chi phí hạng mục nhà làm việc ) + J118 (chi phí hạng mục san lấp……)

Các ô K16, Ô L16, Ô M16 cũng tương tự.

Các ô J17, J31, J118 là các chi phí xây dựng của các hạng mục.

2. Chi phí của các hạng mục = tổng chi phí của từng phần (hạng mục nhỏ) trong hạng mục lớn đó

Cụ thể Ô J17 (chi phí hạng mục phá dỡ ) = J18 (chi phí phá dỡ trạm y tế) + J31 (chi phí phá dỡ cổng tường rào),

Các ô J17, J31, J118 tính tương tự

3. Chi phí của các hạng mục nhỏ trong hạng mục lớn = tổng các dòng có đánh số thứ tự cho đến khi chuyển sang một hạng mục nhỏ khác.

Cụ thể Ô J18 (phá dỡ trạm y tế) = sum (J19: J24)

Các hạng mục nhỏ khác có tính chất tương tự.
View attachment 211610
Code Đây bạn.
Mã:
Sub tinhtong()
Dim arr, arr1(1 To 1, 1 To 4), lr As Long, i As Long, j As Long
  With Sheets("3.PL03A-TT08-2016")
       arr = .Range("A16:M137").Value
     For j = 10 To 13
       For i = 2 To UBound(arr, 1)
           If IsNumeric(arr(i, 1)) = True And arr(i, 1) <> Empty Then
               arr1(1, j - 9) = arr1(1, j - 9) + arr(i, j)
           End If
       Next i
     Next j
     .Range("j16:m16").Value = arr1
  End With
End Sub
 

Ba Tê

Cạo Rồi Khỏi Gội
Tham gia ngày
5 Tháng năm 2009
Bài viết
11,253
Được thích
16,015
Điểm
1,860
Tuổi
60
Nơi ở
An Giang
Em đang tính theo cách trên ( như trong bảng tính) nhưng với bảng tính có tầm 2000 dòng thì rất lâu và dễ nhầm lẫn, em nhờ các anh/chị viết giúp em code ( hay nêu cho em xin ý tưởng) để tính bài toán được thuận lợi hơn.

Bài toán như sau:

1. Ô J16, Ô K16, Ô L16, Ô M16 là các ô là “chi phí xây dựng (Gxd)” cần tính :

Chi phí xây dựng (Gxd) = Tổng chi phí các hạng mục

Cụ thể Ô J16 (chi phí xây dựng) = J17 (chi phí hạng mục phá dỡ) + J31 ( chi phí hạng mục nhà làm việc ) + J118 (chi phí hạng mục san lấp……)

Các ô K16, Ô L16, Ô M16 cũng tương tự.

Các ô J17, J31, J118 là các chi phí xây dựng của các hạng mục.

2. Chi phí của các hạng mục = tổng chi phí của từng phần (hạng mục nhỏ) trong hạng mục lớn đó

Cụ thể Ô J17 (chi phí hạng mục phá dỡ ) = J18 (chi phí phá dỡ trạm y tế) + J31 (chi phí phá dỡ cổng tường rào),

Các ô J17, J31, J118 tính tương tự

3. Chi phí của các hạng mục nhỏ trong hạng mục lớn = tổng các dòng có đánh số thứ tự cho đến khi chuyển sang một hạng mục nhỏ khác.

Cụ thể Ô J18 (phá dỡ trạm y tế) = sum (J19: J24)

Các hạng mục nhỏ khác có tính chất tương tự.
View attachment 211610
1/ Bạn phải nhập các "cấp" đầy đủ vào cột A.
Ví dụ "I", "II"... là "cha" của "x"
"x" là "cha" của "xx"
"xx" là "cha" của các STT 1,2,3... như trong file
2/ Bấm nút và kiểm tra lại số liệu bằng công thức xem có chính xác không nhé.
 

File đính kèm

Cát Lượng

Thành viên thường trực
Tham gia ngày
14 Tháng mười một 2018
Bài viết
320
Được thích
36
Điểm
185
Tuổi
32
1/ Bạn phải nhập các "cấp" đầy đủ vào cột A.
Ví dụ "I", "II"... là "cha" của "x"
"x" là "cha" của "xx"
"xx" là "cha" của các STT 1,2,3... như trong file
2/ Bấm nút và kiểm tra lại số liệu bằng công thức xem có chính xác không nhé.
Dạ! am cảm ơn thầy, em nhờ thầy giúp em chút xíu nữa ạ?

Code khi chạy sẽ chỉ hiện kết quả và mọi công thức không còn nữa.

Thầy cho em hỏi có cách nào để tính cùng lúc các cột J, K, L, M mà khi chạy code vẫn hiện : công thức lên như hình dưới ( để người theo dõi muốn kiểm tra thì có thể kiểm tra lại )
Em thấy khi xuất từ phần mềm dự toán ra excel thì các kết quả vẫn thể hiện công thức, cách tính và vẫn có nguyên đường link.
Em cám ơn thầy!
Kha.png
Bài đã được tự động gộp:

Code Đây bạn.
Mã:
Sub tinhtong()
Dim arr, arr1(1 To 1, 1 To 4), lr As Long, i As Long, j As Long
  With Sheets("3.PL03A-TT08-2016")
       arr = .Range("A16:M137").Value
     For j = 10 To 13
       For i = 2 To UBound(arr, 1)
           If IsNumeric(arr(i, 1)) = True And arr(i, 1) <> Empty Then
               arr1(1, j - 9) = arr1(1, j - 9) + arr(i, j)
           End If
       Next i
     Next j
     .Range("j16:m16").Value = arr1
  End With
End Sub
Vấng cám ơn anh ạ!
 

tam888

Thành viên tích cực
Tham gia ngày
22 Tháng tám 2013
Bài viết
840
Được thích
499
Điểm
435
Bài dạng này , nếu dùng công thức thì nên dùng Subtotal() là chuẩn, tiện lợi và tránh nhầm lẫn
 

Ba Tê

Cạo Rồi Khỏi Gội
Tham gia ngày
5 Tháng năm 2009
Bài viết
11,253
Được thích
16,015
Điểm
1,860
Tuổi
60
Nơi ở
An Giang
Code khi chạy sẽ chỉ hiện kết quả và mọi công thức không còn nữa.

Thầy cho em hỏi có cách nào để tính cùng lúc các cột J, K, L, M mà khi chạy code vẫn hiện : công thức lên như hình dưới ( để người theo dõi muốn kiểm tra thì có thể kiểm tra lại )
Em thấy khi xuất từ phần mềm dự toán ra excel thì các kết quả vẫn thể hiện công thức, cách tính và vẫn có nguyên đường link.
1/ Nếu có phần mềm thì cần gì code nữa.
2/ Đã dùng VBA mà vẫn viết công thức vào từng ô thì code làm gì.
3/ Họ muốn thử kiểm tra thì có thể dùng công thức ở các cột phụ bên ngoài.
4/ File của bạn đã link với hàng chục file khác rồi, xem trong Name sẽ biết.
 

Cát Lượng

Thành viên thường trực
Tham gia ngày
14 Tháng mười một 2018
Bài viết
320
Được thích
36
Điểm
185
Tuổi
32
1/ Nếu có phần mềm thì cần gì code nữa.
2/ Đã dùng VBA mà vẫn viết công thức vào từng ô thì code làm gì.
3/ Họ muốn thử kiểm tra thì có thể dùng công thức ở các cột phụ bên ngoài.
4/ File của bạn đã link với hàng chục file khác rồi, xem trong Name sẽ biết.
Vâng em cám ơn sự góp ý của thấy!
Bài đã được tự động gộp:

Bài dạng này , nếu dùng công thức thì nên dùng Subtotal() là chuẩn, tiện lợi và tránh nhầm lẫn
Dạ! em cám ơn
 

tam888

Thành viên tích cực
Tham gia ngày
22 Tháng tám 2013
Bài viết
840
Được thích
499
Điểm
435
Anh có thể giúp em file đính kèm với cách dùng hàm Subtotal() được không ạ!
hàm Subtotal() thì tự tìm hiểu, search trên diễn đàn cũng có đầy

Làm tạm 3 ô này (sheet 3.PL03A-TT08-2016) cho bạn dễ hiểu:
Mã:
ô(cell) j18:
=SUBTOTAL(9, J19:J24)

ô(cell) J25:
=SUBTOTAL(9,J26:J30)

ô(cell) J17:
=SUBTOTAL(9,J18:J30)
ĐỂ ý sẽ thấy ô J17 có tổng đúng (nó không tính các ô có subtotal trong J18:J30, nên được tổng như ý muốn)

Làm tương tự cho các phần khác
Như thế tiện lợi
 

Cát Lượng

Thành viên thường trực
Tham gia ngày
14 Tháng mười một 2018
Bài viết
320
Được thích
36
Điểm
185
Tuổi
32
hàm Subtotal() thì tự tìm hiểu, search trên diễn đàn cũng có đầy

Làm tạm 3 ô này (sheet 3.PL03A-TT08-2016) cho bạn dễ hiểu:
Mã:
ô(cell) j18:
=SUBTOTAL(9, J19:J24)

ô(cell) J25:
=SUBTOTAL(9,J26:J30)

ô(cell) J17:
=SUBTOTAL(9,J18:J30)
ĐỂ ý sẽ thấy ô J17 có tổng đúng (nó không tính các ô có subtotal trong J18:J30, nên được tổng như ý muốn)

Làm tương tự cho các phần khác
Như thế tiện lợi
Dạ em cám ơn anh! chúc anh cuối tuần vui vẻ!
 

Ba Tê

Cạo Rồi Khỏi Gội
Tham gia ngày
5 Tháng năm 2009
Bài viết
11,253
Được thích
16,015
Điểm
1,860
Tuổi
60
Nơi ở
An Giang
Thầy cho em hỏi có cách nào để tính cùng lúc các cột J, K, L, M mà khi chạy code vẫn hiện : công thức lên như hình dưới ( để người theo dõi muốn kiểm tra thì có thể kiểm tra lại )
Giữ nguyên các công thức của bạn, chỉ tính các dòng tổng I, II, x, xx nhé
Mấy dòng này mà viết từng công thức thì "oải lắm".
 

File đính kèm

tam888

Thành viên tích cực
Tham gia ngày
22 Tháng tám 2013
Bài viết
840
Được thích
499
Điểm
435
Giữ nguyên các công thức của bạn, chỉ tính các dòng tổng I, II, x, xx nhé
Mấy dòng này mà viết từng công thức thì "oải lắm".
Có gì mà oải lắm, tính toán liên quan đến tiền mà bác, làm dự toán phải làm cân nhắc từng dòng từng con số, định mức tra, hạng mục tính - thế mới giúp tránh lãng phí (đỡ phí thuế dân)

Còn xuất từ phần mềm, thì đề nghị nhà cung cấp phần mềm , sửa lại xuất công thức đúng kiểu mong muốn (ví như dùng subtotal)
 
Lần chỉnh sửa cuối:

Cát Lượng

Thành viên thường trực
Tham gia ngày
14 Tháng mười một 2018
Bài viết
320
Được thích
36
Điểm
185
Tuổi
32
Giữ nguyên các công thức của bạn, chỉ tính các dòng tổng I, II, x, xx nhé
Mấy dòng này mà viết từng công thức thì "oải lắm".
Hi, giá mà chạy code mà các các dòng tổng I, II, x, xx ra được công thức thì tốt quá thầy ạ!
File của em gồm rất nhiều hạng mục con, thầy khi nào có thời gian nhờ thầy giúp em để các dòng tổng I, II, x, xx hiện công thức mới nhé!
Em cám ơn thầy nhiều!
 
Lần chỉnh sửa cuối:

Cát Lượng

Thành viên thường trực
Tham gia ngày
14 Tháng mười một 2018
Bài viết
320
Được thích
36
Điểm
185
Tuổi
32
hàm Subtotal() thì tự tìm hiểu, search trên diễn đàn cũng có đầy

Làm tạm 3 ô này (sheet 3.PL03A-TT08-2016) cho bạn dễ hiểu:
Mã:
ô(cell) j18:
=SUBTOTAL(9, J19:J24)

ô(cell) J25:
=SUBTOTAL(9,J26:J30)

ô(cell) J17:
=SUBTOTAL(9,J18:J30)
ĐỂ ý sẽ thấy ô J17 có tổng đúng (nó không tính các ô có subtotal trong J18:J30, nên được tổng như ý muốn)

Làm tương tự cho các phần khác
Như thế tiện lợi
Anh ơi, nếu dùng hàm SUBTOTAL mà khi tạo nhóm để ẩn đi cho dễ quan sát hoặc khi lọc thì giá trị SUBTOTAL bị thay đổi, như vậy rất khó theo dõi.
 

tam888

Thành viên tích cực
Tham gia ngày
22 Tháng tám 2013
Bài viết
840
Được thích
499
Điểm
435
Anh ơi, nếu dùng hàm SUBTOTAL mà khi tạo nhóm để ẩn đi cho dễ quan sát hoặc khi lọc thì giá trị SUBTOTAL bị thay đổi, như vậy rất khó theo dõi.
Đọc kỹ hướng dẫn của hàm, sẽ thấy subtotal có tham số hàm 9 (hay 109) cho SUM sẽ Bao gồm (hay không bao gồm) dòng ẩn (hidden rows) được tính; thì sẽ hiểu
 
Lần chỉnh sửa cuối:

tam888

Thành viên tích cực
Tham gia ngày
22 Tháng tám 2013
Bài viết
840
Được thích
499
Điểm
435
Vâng, được rồi anh ạ!
Có làm VBA mà muốn công thức liên kết thì cũng nên dùng VBA đặt công thức =Subtotal(9,....)

Tuy vậy nếu bạn thực sự là người làm dự toán bằng Excel từ đầu từng bước từng bước cho công trình thì sẽ thấy: việc đặt công thức vào ô tổng cho các hạng mục thế là chuyện nhỏ chiếm thời gian không đáng kể, vì khi làm dự toán phải làm chọn theo từng hạng mục công việc cân nhắc từng dòng tra đúng đơn giá..vv -- nghĩa là tại sao phải VBA chạy công thức khi đã liệt kê xong (?) nên VBA có vẻ là thừa
 

Cát Lượng

Thành viên thường trực
Tham gia ngày
14 Tháng mười một 2018
Bài viết
320
Được thích
36
Điểm
185
Tuổi
32
Có làm VBA mà muốn công thức liên kết thì cũng nên dùng VBA đặt công thức =Subtotal(9,....)

Tuy vậy nếu bạn thực sự là người làm dự toán bằng Excel từ đầu từng bước từng bước cho công trình thì sẽ thấy: việc đặt công thức vào ô tổng cho các hạng mục thế là chuyện nhỏ chiếm thời gian không đáng kể, vì khi làm dự toán phải làm chọn theo từng hạng mục công việc cân nhắc từng dòng tra đúng đơn giá..vv -- nghĩa là tại sao phải VBA chạy công thức khi đã liệt kê xong (?) nên VBA có vẻ là thừa
Dạ! em cũng mới tìm hiểu và kiến thức về excel còn chưa biết nhiều! cám ơn sự góp ý, chỉ bảo của các anh!
 

Cát Lượng

Thành viên thường trực
Tham gia ngày
14 Tháng mười một 2018
Bài viết
320
Được thích
36
Điểm
185
Tuổi
32
Có làm VBA mà muốn công thức liên kết thì cũng nên dùng VBA đặt công thức =Subtotal(9,....)

Tuy vậy nếu bạn thực sự là người làm dự toán bằng Excel từ đầu từng bước từng bước cho công trình thì sẽ thấy: việc đặt công thức vào ô tổng cho các hạng mục thế là chuyện nhỏ chiếm thời gian không đáng kể, vì khi làm dự toán phải làm chọn theo từng hạng mục công việc cân nhắc từng dòng tra đúng đơn giá..vv -- nghĩa là tại sao phải VBA chạy công thức khi đã liệt kê xong (?) nên VBA có vẻ là thừa
E chào anh, anh ơi cho em hỏi lại một chút ạ!
Nếu dùng hàm =Subtotal(9,....) hoặc =Subtotal(109,....) và có dùng thêm chức năng Fillter để lọc thì giá trị tổng sẽ bị thay đổi, như vậy thì sẽ khó theo dõi.
Em cảm ơn anh ạ!
 

tam888

Thành viên tích cực
Tham gia ngày
22 Tháng tám 2013
Bài viết
840
Được thích
499
Điểm
435
E chào anh, anh ơi cho em hỏi lại một chút ạ!
Nếu dùng hàm =Subtotal(9,....) hoặc =Subtotal(109,....) và có dùng thêm chức năng Fillter để lọc thì giá trị tổng sẽ bị thay đổi, như vậy thì sẽ khó theo dõi.
Em cảm ơn anh ạ!
Vậy nếu có excel (version 2010 hay mới hơn) dùng hàm AGGREGATE theo mẫu kiểu như sau, thử coi
Mã:
=AGGREGATE(9,2,C2:C11)
 
Top Bottom