Mình muốn hỏi về cách cộng các giá trị có điều kiện và không liên tục (1 người xem)

[linkhoang]

trong biểu này mình đã tách chi phí của công ty được 6 tháng và phân từng vật tư thành nhóm bằng mã tk ở cột D ; Bình thường có thể dùng filter lọc những tk giống nhau rồi cộng ; nhưng mình muốn hỏi làm cách nào để máy tự tìm những vật tư có cùng tk ở cột D rồi đối chiếu với giá trị bằng tiền ở cột B sau đó đưa ra 1 bảng ( Ví dụ Vật tư có tk 74 có tổng = ??? ; 75 = ???? ) hay không. Để mình có thể tiện cộng tổng 6 tháng các vật tư có cùng tài khoản lại với nhau Cám ơn mọi người rất nhiều ^^!

 

[hieuxd]

trong biểu này mình đã tách chi phí của công ty được 6 tháng và phân từng vật tư thành nhóm bằng mã tk ở cột D ; Bình thường có thể dùng filter lọc những tk giống nhau rồi cộng ; nhưng mình muốn hỏi làm cách nào để máy tự tìm những vật tư có cùng tk ở cột D rồi đối chiếu với giá trị bằng tiền ở cột B sau đó đưa ra 1 bảng ( Ví dụ Vật tư có tk 74 có tổng = ??? ; 75 = ???? ) hay không. Để mình có thể tiện cộng tổng 6 tháng các vật tư có cùng tài khoản lại với nhau Cám ơn mọi người rất nhiều ^^!

Góp ý: cần thống nhất mã tài khoản và tên vật tư thống nhất với nhau không nên để 1 tk với 2 tên vật tư, ở bảng tổng hợp phải có đầy đủ tên Tk và tên vật tư
công thức cho tháng 1: T1
=SUMIF('t1'!$D$2:$D$42;bm!B3;'t1'!$B$2:$B$42)
công thức tổng quát cho tất cả các tháng
=SUMIF(INDIRECT("'"&C$2&"'!"&"$D$2:$D$300");bm!$B3;INDIRECT("'"&C$2&"'!"&"$b$2:$b$300"))

Bạn thêm cột tổng cộng ở cột cuối cùng (sau tháng 6) rồi cộng từ tháng 1 đến tháng 6

 

[nhapmon]

trong biểu này mình đã tách chi phí của công ty được 6 tháng và phân từng vật tư thành nhóm bằng mã tk ở cột D ; Bình thường có thể dùng filter lọc những tk giống nhau rồi cộng ; nhưng mình muốn hỏi làm cách nào để máy tự tìm những vật tư có cùng tk ở cột D rồi đối chiếu với giá trị bằng tiền ở cột B sau đó đưa ra 1 bảng ( Ví dụ Vật tư có tk 74 có tổng = ??? ; 75 = ???? ) hay không. Để mình có thể tiện cộng tổng 6 tháng các vật tư có cùng tài khoản lại với nhau Cám ơn mọi người rất nhiều ^^!

bạn sử dụng excel 2007 trở lên, bạn có thể tìm hiểu tính năng "multiple consolidation ranges" của pivot table

 

[Hai Lúa Miền Tây]

trong biểu này mình đã tách chi phí của công ty được 6 tháng và phân từng vật tư thành nhóm bằng mã tk ở cột D ; Bình thường có thể dùng filter lọc những tk giống nhau rồi cộng ; nhưng mình muốn hỏi làm cách nào để máy tự tìm những vật tư có cùng tk ở cột D rồi đối chiếu với giá trị bằng tiền ở cột B sau đó đưa ra 1 bảng ( Ví dụ Vật tư có tk 74 có tổng = ??? ; 75 = ???? ) hay không. Để mình có thể tiện cộng tổng 6 tháng các vật tư có cùng tài khoản lại với nhau Cám ơn mọi người rất nhiều ^^!

Đặt lại tiêu đề cột, dùng ado như sau:

[GPECODE=sql]Sub GopSheet_HLMT()
Dim cn As Object, rst As Object, cat As Object, tbl As Object, str$, str1 As String
Set cn = CreateObject("ADODB.Connection")
Set cat = CreateObject("ADOX.Catalog")
Set tbl = CreateObject("ADOX.Table")
Set rst = CreateObject("ADODB.Recordset")
With cn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
cat.ActiveConnection = cn
For Each tbl In cat.Tables
If Right(Replace(tbl.Name, "'", ""), 1) = "$" And tbl.Name <> "Ketqua$" And tbl.Name <> "bm$" Then
str = str & " union all SELECT TENVT,MA,TIEN,TIEN1 from [" & Replace(Replace(tbl.Name, "$", ""), "'", "") & "$] "
str1 = Right(str, Len(str) - 10)
End If
Next

With rst
.ActiveConnection = cn
.Open "select [TENVT],MA,sum(TIEN),sum(TIEN1) from (" & str1 & ") group by ma,tenvt having ma is not null"
End With
With Sheets("KetQua")
.[A2:IV65000].ClearContents
.[A2].CopyFromRecordset rst
End With
rst.Close: Set rst = Nothing
cn.Close: Set cn = Nothing
Set cat = Nothing: Set tbl = Nothing

End Sub
[/GPECODE]

 

[linkhoang]

Góp ý: cần thống nhất mã tài khoản và tên vật tư thống nhất với nhau không nên để 1 tk với 2 tên vật tư, ở bảng tổng hợp phải có đầy đủ tên Tk và tên vật tư
công thức cho tháng 1: T1
=SUMIF('t1'!$D$2:$D$42;bm!B3;'t1'!$B$2:$B$42)
công thức tổng quát cho tất cả các tháng
=SUMIF(INDIRECT("'"&C$2&"'!"&"$D$2:$D$300");bm!$B3;INDIRECT("'"&C$2&"'!"&"$b$2:$b$300"))

Bạn thêm cột tổng cộng ở cột cuối cùng (sau tháng 6) rồi cộng từ tháng 1 đến tháng 6

cám ơn bạn đã góp ý ^^ mình đề tên nhiều vật tư trùng nhau là vì bản báo cáo này do doanh nghiệp gửi lên ; và các font chữ không thống nhất nên đành phải để như vậy ^^! . Mình là người tổng hợp và phân tích thôi. Với cả mỗi 1 mã là 1 nhóm các vật tư chứ không phải chỉ 1 loại vật tư ( ví dụ mã 76 : là nhóm Kim Loại Đúc Sẵn : gồm bu lông; đai ốc ; xoong; nồi ..... ) Mình muốn xuất bảng cho từng tháng . với cả mình vẫn chưa hiểu ct kia lắm :-/

 

[linkhoang]

Đặt lại tiêu đề cột, dùng ado như sau:

[GPECODE=sql]Sub GopSheet_HLMT()
Dim cn As Object, rst As Object, cat As Object, tbl As Object, str$, str1 As String
Set cn = CreateObject("ADODB.Connection")
Set cat = CreateObject("ADOX.Catalog")
Set tbl = CreateObject("ADOX.Table")
Set rst = CreateObject("ADODB.Recordset")
With cn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
cat.ActiveConnection = cn
For Each tbl In cat.Tables
If Right(Replace(tbl.Name, "'", ""), 1) = "$" And tbl.Name <> "Ketqua$" And tbl.Name <> "bm$" Then
str = str & " union all SELECT TENVT,MA,TIEN,TIEN1 from [" & Replace(Replace(tbl.Name, "$", ""), "'", "") & "$] "
str1 = Right(str, Len(str) - 10)
End If
Next

With rst
.ActiveConnection = cn
.Open "select [TENVT],MA,sum(TIEN),sum(TIEN1) from (" & str1 & ") group by ma,tenvt having ma is not null"
End With
With Sheets("KetQua")
.[A2:IV65000].ClearContents
.[A2].CopyFromRecordset rst
End With
rst.Close: Set rst = Nothing
cn.Close: Set cn = Nothing
Set cat = Nothing: Set tbl = Nothing

End Sub
[/GPECODE]

Mình chưa hiểu cách của bạn lắm Mình không cần chi tiết như thế ; Ý mình là nó sẽ xuất ra 1 bảng ở sheet t1 t2 .... 30 = ..... ; 31 = ...... ; Vì các nhóm vật tư tương ứng với mã 30 nó nằm rời rạc nhau ; Mình chỉ cần tìm tổng của tất cả các vật tư trong các mã . rồi sau đó mình cộng tổng các mã 30 của các tháng vào với nhau sẽ ra tổng cả năm của nhóm vật tư ấy . Xin lỗi nếu mình chưa hiểu hết được cách làm của bạn .Giải thích rõ thêm là ở từng tháng : cột A là tên vật tư ; cột B là giá trị bằng tiền của vật tư ; Cột D là Mã Vật Tư được đối chiếu ở sheet bm ; Cột C là mình làm tổng các vật tư cùng mã bằng cách filter + sum . Ý mình là muốn xuất Cột C ấy ấy ^^!

 

[hieuxd]

Đã gọi là mã TK thì phải thống nhất
nếu không thống nhất thì tổng hợp chắc chắn sai, => chuyển tổng hợp theo mã bằng cách tổng hợp theo tên
Bạn lọc ra danh sách tổng hợp tên vật tư duy nhất của tất cả các tháng vào một sheet tổng hợp rồi tổng hợp theo tên vật tư

mình thấy
DÇu rửa b¸t đĩa mã tk 66
ChÊt tÈy sinh hµn mã tk cũng là 66
Thö vÕt nøt (microcheck) mã tk cũng là 66

Bạn xem ở bài #2 mình làm luôn trên sheet Bm

 

[Hai Lúa Miền Tây]

Mình chưa hiểu cách của bạn lắm Mình không cần chi tiết như thế ; Ý mình là nó sẽ xuất ra 1 bảng ở sheet t1 t2 .... 30 = ..... ; 31 = ...... ; Vì các nhóm vật tư tương ứng với mã 30 nó nằm rời rạc nhau ; Mình chỉ cần tìm tổng của tất cả các vật tư trong các mã . rồi sau đó mình cộng tổng các mã 30 của các tháng vào với nhau sẽ ra tổng cả năm của nhóm vật tư ấy . Xin lỗi nếu mình chưa hiểu hết được cách làm của bạn .Giải thích rõ thêm là ở từng tháng : cột A là tên vật tư ; cột B là giá trị bằng tiền của vật tư ; Cột D là Mã Vật Tư được đối chiếu ở sheet bm ; Cột C là mình làm tổng các vật tư cùng mã bằng cách filter + sum . Ý mình là muốn xuất Cột C ấy ấy ^^!

Chỉnh lại như sau:

[GPECODE=sql]Sub GopSheet_HLMT()
Dim cn As Object, rst As Object, cat As Object, tbl As Object, str$, str1 As String
Set cn = CreateObject("ADODB.Connection")
Set cat = CreateObject("ADOX.Catalog")
Set tbl = CreateObject("ADOX.Table")
Set rst = CreateObject("ADODB.Recordset")
With cn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
cat.ActiveConnection = cn
For Each tbl In cat.Tables
If Right(Replace(tbl.Name, "'", ""), 1) = "$" And tbl.Name <> "Ketqua$" And tbl.Name <> "bm$" Then
str = str & " union all SELECT TENVT,MA,TIEN,TIEN1 from [" & Replace(Replace(tbl.Name, "$", ""), "'", "") & "$] "
str1 = Right(str, Len(str) - 10)
End If
Next

With rst
.ActiveConnection = cn
.Open "select MA,sum(TIEN),sum(TIEN1) from (" & str1 & ") group by ma having ma is not null"
End With
With Sheets("KetQua")
.[A2:IV65000].ClearContents
.[A2].CopyFromRecordset rst
End With
rst.Close: Set rst = Nothing
cn.Close: Set cn = Nothing
Set cat = Nothing: Set tbl = Nothing

End Sub
[/GPECODE]

 

[linkhoang]

Đã gọi là mã TK thì phải thống nhất
nếu không thống nhất thì tổng hợp chắc chắn sai, => chuyển tổng hợp theo mã bằng cách tổng hợp theo tên
Bạn lọc ra danh sách tổng hợp tên vật tư duy nhất của tất cả các tháng vào một sheet tổng hợp rồi tổng hợp theo tên vật tư

mình thấy
DÇu rửa b¸t đĩa mã tk 66
ChÊt tÈy sinh hµn mã tk cũng là 66
Thö vÕt nøt (microcheck) mã tk cũng là 66

Bạn xem ở bài #2 mình làm luôn trên sheet Bm

à cái đấy như mình nói ở trên mã 66 là dành cho Sản phẩm hóa chất khác ( ngoài các hóa chất cơ bản như Nito ; Oxi ) : vì vậy nó bao gồm cả dầu rửa bát ; chất tẩy rửa ; chất thử vết nứt ; sơn ; thuốc nổ ; pháo ; diêm .... chính vì thế nên mình mới phải hỏi mọi người cách tính tổng các vật tư khác nhau cùng nằm trong 1 nhóm và rời rạc mà

 

[linkhoang]

Mình muốn nói rõ hơn : Mình muốn xuất 1 bảng như file đính kèm . Nhấn mạnh lại là mã là mã ngành : nó gồm nhiều vật tư. Mình cũng chưa biết diễn đạt thế đúng ý chưa . về cơ bản là có thể sòft hoặc filter rồi chép tay ra . Nhưng mình muốn hỏi cách làm tự động để tìm hiểu và học tập thêm ^^!

 

[nhapmon]

Mình muốn nói rõ hơn : Mình muốn xuất 1 bảng như file đính kèm . Nhấn mạnh lại là mã là mã ngành : nó gồm nhiều vật tư. Mình cũng chưa biết diễn đạt thế đúng ý chưa . về cơ bản là có thể sòft hoặc filter rồi chép tay ra . Nhưng mình muốn hỏi cách làm tự động để tìm hiểu và học tập thêm ^^!

gởi bạn một số cách tham khảo, nhưng chắc bạn phải xây dựng lại cơ sở dữ liệu, mình thấy nó sao sao đó..........
thông thường người ta gõ mã nó ra tên, bạn thì gõ tên ra mã.........,mà tên tiếng việt thì dấu tùm lum......hỏng biết sao mà trúng được.......

 

[Hai Lúa Miền Tây]

Mình muốn nói rõ hơn : Mình muốn xuất 1 bảng như file đính kèm . Nhấn mạnh lại là mã là mã ngành : nó gồm nhiều vật tư. Mình cũng chưa biết diễn đạt thế đúng ý chưa . về cơ bản là có thể sòft hoặc filter rồi chép tay ra . Nhưng mình muốn hỏi cách làm tự động để tìm hiểu và học tập thêm ^^!

Chỉnh lại code ở file trên như sau:

[GPECODE=sql]Option Explicit
Sub GopSheet_HLMT()
Dim cn As Object, rst As Object, i As Integer, str$, str1 As String
Set cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
With cn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
For i = 1 To 6
str1 = str1 & " union all select ma, tien1, 'T" & i & "' as Thang " & _
"from [t" & i & "$] "
Next
str1 = Right(str1, Len(str1) - 10)
str = "TRANSFORM Sum(Tien1) " & _
"SELECT Ma, Sum(Tien1) " & _
"FROM ( " & str1 & ") " & _
"WHERE Ma Is Not Null " & _
"GROUP BY Ma " & _
"PIVOT Thang"
With rst
.ActiveConnection = cn
.Open str
End With
With Sheets("KetQua")
.[A2:IV65000].ClearContents
.[A2].CopyFromRecordset rst
End With
rst.Close: Set rst = Nothing
cn.Close: Set cn = Nothing

End Sub

[/GPECODE]