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

[Cát Lượng]

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ự.

 

[snow25]

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.

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ê]

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é.

 

[Cát Lượng]

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!

Bài đã được tự động gộp: 25/1/19

Code Đây bạn.

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]

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ê]

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]

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: 25/1/19

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

 

[Cát Lượng]

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

Anh có thể giúp em file đính kèm với cách dùng hàm Subtotal() được không ạ!

 

[tam888]

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:

ô(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]

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:

ô(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ê]

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".

 

[tam888]

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)

 

[Cát Lượng]

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!

 

[Cát Lượ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:

ô(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]

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

 

[Cát Lượng]

Đọ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

Vâng, được rồi anh ạ!

 

[tam888]

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]

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]

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]

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

=AGGREGATE(9,2,C2:C11)