Cách tính tổng theo vùng (1 người xem)

[NoDiNaDu]

Em mới biết về Excel nên có 1 vấn đề mong mọi người giúp đỡ ạ. Giờ em có 1 bảng dữ liệu gồm các hạng mục lớn (A,B,C) và các hạng mục nhỏ (A:A1.A2,A3...- B: B1,B2,B3). Ở ô hạng mục nhỏ là phép tính nhân khối lượng. Giờ em muốn dùng hàm để ô khối lượng đáp ứng yêu cầu sau:
1. Ở hạng mục lớn là tổng các hạng mục nhỏ : A=A1+A2+A3
2. ở các hạng mục nhỏ là kết quả của phép tính .
Mọi người giúp e với ạ, em xin cảm ơn ạ.( Nếu cộng tay thì em làm được nhưng hơi vất ạ ( )

 

[Phan Thế Hiệp]

Em mới biết về Excel nên có 1 vấn đề mong mọi người giúp đỡ ạ. Giờ em có 1 bảng dữ liệu gồm các hạng mục lớn (A,B,C) và các hạng mục nhỏ (A:A1.A2,A3...- B: B1,B2,B3). Ở ô hạng mục nhỏ là phép tính nhân khối lượng. Giờ em muốn dùng hàm để ô khối lượng đáp ứng yêu cầu sau:
1. Ở hạng mục lớn là tổng các hạng mục nhỏ : A=A1+A2+A3
2. ở các hạng mục nhỏ là kết quả của phép tính .
Mọi người giúp e với ạ, em xin cảm ơn ạ.( Nếu cộng tay thì em làm được nhưng hơi vất ạ ( )

Tạo cột phụ G:

G5=LOOKUP("zz",$B$5:B5)
F5=IF(B5=G5,SUMIF(G6:$G$100,G5,F6:$F$100),PRODUCT(C5:E5))

Thân

 

[NoDiNaDu]

Tạo cột phụ G:

G5=LOOKUP("zz",$B$5:B5)
F5=IF(B5=G5,SUMIF(G6:$G$100,G5,F6:$F$100),PRODUCT(C5:E5))

Thân

Bác ơi, cái dk ("zz" ) là điều kiện gì vậy ạ

 

[Phan Thế Hiệp]

Bác ơi, cái dk ("zz" ) là điều kiện gì vậy ạ

"zz" là giá trị lớn nhất trong các chuỗi chữ cái khi kết hợp với nhau (theo cách bình thường).

Vd: "zz" thì lớn hơn cả các ký tự "A; B; C; ....". Hay bạn thường đặt mã nhóm như "XAYLAP001" "GIANGIAO005".... thì "zz" cũng là giá trị lớn nhất.
Nhờ vào chức năng của LOOKUP(), khi nó không tìm được giá trị nào lớn hơn "zz" nó sẽ tìm và lấy giá trị chuỗi xuất hiện gần nhất sau cùng. Vd: G9, thì ký tự "A" tại B5 là gần nhất, vì các ô B6: B8 là các số không phải là chuỗi, đến khi xuống đến G10, thì ký tự "B" tại ô B10 là gần nhất sau cùng. Cứ như vậy nó lấy ra các ký tự phù hợp cho từng đoạn mà bạn muốn phân thành Vùng để cộng.

Thân

 

[NoDiNaDu]

"zz" là giá trị lớn nhất trong các chuỗi chữ cái khi kết hợp với nhau (theo cách bình thường).

Vd: "zz" thì lớn hơn cả các ký tự "A; B; C; ....". Hay bạn thường đặt mã nhóm như "XAYLAP001" "GIANGIAO005".... thì "zz" cũng là giá trị lớn nhất.
Nhờ vào chức năng của LOOKUP(), khi nó không tìm được giá trị nào lớn hơn "zz" nó sẽ tìm và lấy giá trị chuỗi xuất hiện gần nhất sau cùng. Vd: G9, thì ký tự "A" tại B5 là gần nhất, vì các ô B6: B8 là các số không phải là chuỗi, đến khi xuống đến G10, thì ký tự "B" tại ô B10 là gần nhất sau cùng. Cứ như vậy nó lấy ra các ký tự phù hợp cho từng đoạn mà bạn muốn phân thành Vùng để cộng.

Thân

Dạ vâng, em áp dụng và được rồi ạ.
bác có thể cho em hỏi thếm 1 chút đc không ạ ?
Với việc có thếm 1 hạng mục nhỏ nữa ( A gồm I và II, trong đó I là tổng của các giá trị I1,I2,I3; II là tổng của II1,II2, II3) thì làm soa ạ. Bác thông cảm về cái excel em hơi thụ động ạ (

 

[Phan Thế Hiệp]

Dạ vâng, em áp dụng và được rồi ạ.
bác có thể cho em hỏi thếm 1 chút đc không ạ ?
Với việc có thếm 1 hạng mục nhỏ nữa ( A gồm I và II, trong đó I là tổng của các giá trị I1,I2,I3; II là tổng của II1,II2, II3) thì làm soa ạ. Bác thông cảm về cái excel em hơi thụ động ạ (

Bạn đưa mần chi dữ liệu giả định!
Tôi đã làm mấy bài có yêu cầu giống vầy với dữ liệu thiệt rồi!

Tôi nghĩ là bạn sẽ có nội dung quản lý file y sì với chủ đề: Phân các Vùng để Cộng hoặc Tính toán trị giá theo dòng, bạn qua đó xem và download file về làm giống vậy.

Thân

 

[NoDiNaDu]

Bạn đưa mần chi dữ liệu giả định!
Tôi đã làm mấy bài có yêu cầu giống vầy với dữ liệu thiệt rồi!

Tôi nghĩ là bạn sẽ có nội dung quản lý file y sì với chủ đề: Phân các Vùng để Cộng hoặc Tính toán trị giá theo dòng, bạn qua đó xem và download file về làm giống vậy.

Thân

Vâng, em sẽ chú ý, cái dữ liệu này là do nay có bản dữ liệu cứng có phân vùng và dòng như này giao cho em nên e mới làm file tối giản để nhờ giúp bác ạ. giờ em nhập liệu với tính kết quả chứ chưa có bảng dữ liệu sẵn.

 

[Phan Thế Hiệp]

Vâng, em sẽ chú ý, cái dữ liệu này là do nay có bản dữ liệu cứng có phân vùng và dòng như này giao cho em nên e mới làm file tối giản để nhờ giúp bác ạ. giờ em nhập liệu với tính kết quả chứ chưa có bảng dữ liệu sẵn.

Nếu file dữ liệu thật của bạn khác với file của "chủ đề theo đường link trên", thì bạn cứ gửi file thiệt đã giản lược dòng chi tiết (lưu giữ khoảng 100 dòng chẳng hạn), lúc đó mới xem cụ thể mà tạo công thức phù hợp được.

Thân

 

[NoDiNaDu]

Nếu file dữ liệu thật của bạn khác với file của "chủ đề theo đường link trên", thì bạn cứ gửi file thiệt đã giản lược dòng chi tiết (lưu giữ khoảng 100 dòng chẳng hạn), lúc đó mới xem cụ thể mà tạo công thức phù hợp được.

Thân

đây bác ạ. File em đang cần làm giống với fie này.Các cột J,L,M là cột em cần làm bác ạ, các cột kia thì em xài vlookup để lấy dữ liệu.

 

[Phan Thế Hiệp]

đây bác ạ. File em đang cần làm giống với fie này.Các cột J,L,M là cột em cần làm bác ạ, các cột kia thì em xài vlookup để lấy dữ liệu.

1/ Bạn phải điều chỉnh theo quy tắc sau;

• Nếu là Mục "A,B,C..." thì phải thêm dấu "/" phía sau thành "A/" "B/" "C/", vì nếu không làm vậy thì tỷ như bạn có mục chữ "I" hay chữ "V" hoặc "X" thì bạn không thể phân biệt với các số La Mã "I=1; V=5; X=10"....

2/ Vì không biết máy của bạn đang xài Excel >2010 không, nên tôi tạm tạo dãy số La Mã Q19: Z19, nếu như có số lớn hơn bạn tự điền bổ sung vào tiếp cột sau đó, và điều chỉnh lại Vùng trong công thức.

3/ Tạo cột theo dõi số thứ tự phân Mục tại O:

O19=IF(A19="","",IF(RIGHT(A19)="/",TEXT(CODE(A19)-64,"00"),LOOKUP(2,1/(MAX(LEN(O18)-IF(ISTEXT(A19),2,ISNUMBER(D19*A18))*3,2)=LEN(O$18:O18)),O$18:O18)&TEXT(IFERROR(MATCH(A19,$Q$19:$Z$19,),A19),"\.00")))

Enter, fill xuống.

4/ Tạo công thức Tổng Cộng theo Phân Mục:

J19=IFERROR(1/(1/($H19*D19)),SUMIF($O20:$O$500,$O19&".??",J20:J$500))

Enter, fill xuống. Rồi copy cả cột qua phải ba cột.

5/ Xem thêm các công thức trong Conditional Formatting.

Thân

 

[NoDiNaDu]

dạ vâng bác. em cảm ơn bác ạ.em đang xài excel 2007 ạ. bác cho em hỏi là với file như vậy ( Các ô, cột luôn có dạng thế này) thì có thể viết thành 1 code không ạ ?
với công thức của phần J,L,M em bôi vàng đó ạ. Bác xem có thể viết giúp e 1 đoạn code được không ạ ?

 

[Phan Thế Hiệp]

dạ vâng bác. em cảm ơn bác ạ.em đang xài excel 2007 ạ. bác cho em hỏi là với file như vậy ( Các ô, cột luôn có dạng thế này) thì có thể viết thành 1 code không ạ ?
với công thức của phần J,L,M em bôi vàng đó ạ. Bác xem có thể viết giúp e 1 đoạn code được không ạ ?

Viết code VBA thì tôi chịu thua!

Vậy nha.

Thân

 

[NoDiNaDu]

Dạ vâng. Em cảm ơn bác ạ

 

[HieuCD]

dạ vâng bác. em cảm ơn bác ạ.em đang xài excel 2007 ạ. bác cho em hỏi là với file như vậy ( Các ô, cột luôn có dạng thế này) thì có thể viết thành 1 code không ạ ?
với công thức của phần J,L,M em bôi vàng đó ạ. Bác xem có thể viết giúp e 1 đoạn code được không ạ ?

Thích thì chìu
Chọt mặt cười chạy code

Sub Tong_Tich()
  Dim sArr(), Cap1(), Cap2(), Res(), stt
  Dim i As Long, j As Long, sRow As Long

  With Sheets("GPE")
    i = .Range("A" & Rows.Count).End(xlUp).Row
    If i < 19 Then MsgBox ("Khong co du lieu"): Exit Sub
    sArr = .Range("A19:I" & i).Value
  End With
  sRow = UBound(sArr)
  ReDim Res(1 To sRow, 1 To 4)
  ReDim Cap1(1 To 4):  ReDim Cap2(1 To 4)
  For i = sRow To 1 Step -1
    stt = sArr(i, 1)
    If Len(stt) > 0 Then
      If IsNumeric(stt) Then
        Res(i, 1) = sArr(i, 4) * sArr(i, 8)
        Res(i, 2) = sArr(i, 5) * sArr(i, 8)
        Res(i, 3) = sArr(i, 6) * (sArr(i, 8) + sArr(i, 9))
        Res(i, 4) = Res(i, 2) + Res(i, 3)
        For j = 1 To 4
          Cap2(j) = Cap2(j) + Res(i, j)
          Cap1(j) = Cap1(j) + Res(i, j)
        Next j
      ElseIf Right(stt, 1) <> "/" Then
        For j = 1 To 4
          Res(i, j) = Cap2(j)
        Next j
        ReDim Cap2(1 To 4)
      Else
        For j = 1 To 4
          Res(i, j) = Cap1(j)
        Next j
        ReDim Cap1(1 To 4): ReDim Cap2(1 To 4)
      End If
    End If
  Next i
  Sheets("GPE").Range("J19").Resize(sRow, 4) = Res
End Sub

 

[NoDiNaDu]

Thầy Hiếu cho e nhờ chút ạ. hiện tại thì code đã chạy r ạ, tuy nhiên khi e nhập liệu vào ô cột K ấy ạ, thì bấm code , ô M không ra kết quả( M=K+L).Với ô J = F*H chứ không phải D*H ạ, em bị nhầm công thức.Thầy có thể chỉnh giúp e đê khi e nhập dữ liệu vào ô k mà ô M chạy ra kq không ạ ?Với lại nếu e tạo code này thành 1 addins để chạy có các file khác tương tự thì đc không ạ. rất mong sự giúp đỡ của Thầy ạ .

 

[Phan Thế Hiệp]

Bác hiếu

Ôi! bạn ghi cái gì vậy??????

Gặp tôi, tôi cho đó là sự thiếu tôn trọng! nếu ngược lại là bạn, bạn thấy có phải vậy không!?

 

[HieuCD]

Bác hiếu cho e nhờ chút ạ. hiện tại thì code đã chạy r ạ, tuy nhiên khi e nhập liệu vào ô cột K ấy ạ, thì bấm code , ô M không ra kết quả( M=K+L).Bác có thể chỉnh giúp e đê khi e nhập dữ liệu vào ô k mà ô M chạy ra kq không ạ ?Với lại nếu e tạo code này thành 1 addins để chạy có các file khác tương tự thì đc không ạ. rất mong sự giúp đỡ của bác ạ .

Bạn nhập vào cột E, cột K và M tự tính
Addin mình không rành, code phải lường trước vùng dữ liệu và thao tác rất phức tạp, không hình dung đúng code sẽ sai. Linh hoạt hơn nên dùng công thức Excel

 

[NoDiNaDu]

Ôi! bạn ghi cái gì vậy??????

Gặp tôi, tôi cho đó là sự thiếu tôn trọng! nếu ngược lại là bạn, bạn thấy có phải vậy không!?

dạ em chưa hiểu ạ? ý là việc tạo addins là thiếu tôn trọng ấy ạ ? vậy thì em xin lỗi ạ, tại em thấy nếu tạo thành 1 addins thì tiện hơn khi có các file tương tự ( em được giao ktra lại số liệu các file như vậy). nếu vậy thì em xin lỗi các bác ạ. em không có ý gì khác đâu ạ.

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

Ôi! bạn ghi cái gì vậy??????

Gặp tôi, tôi cho đó là sự thiếu tôn trọng! nếu ngược lại là bạn, bạn thấy có phải vậy không!?

Dạ em không để ý vấn đề xưng hô nên có chỗ không phải khi xưng hô với các thầy. Mong các thầy bõ qua cho em ạ. em sẽ chú ý hơn ạ.

 

[Phan Thế Hiệp]

dạ em chưa hiểu ạ? ý là việc tạo addins là thiếu tôn trọng ấy ạ ? vậy thì em xin lỗi ạ, tại em thấy nếu tạo thành 1 addins thì tiện hơn khi có các file tương tự ( em được giao ktra lại số liệu các file như vậy). nếu vậy thì em xin lỗi các bác ạ. em không có ý gì khác đâu ạ.

Tôi có trích dẫn đoạn bạn ghi ở trên bài mà!?

Bác hiếu

Mặc dù tuổi cũng lơn lớn, nhưng tôi nhớ không lầm là danh từ riêng phải viết hoa, đó là sự trân trọng tên một người, và điều này cũng áp dụng cho quốc tế dùng ngôn ngữ La tinh.

Thân

 

[NoDiNaDu]

Tôi có trích dẫn đoạn bạn ghi ở trên bài mà!?

Mặc dù tuổi cũng lơn lớn, nhưng tôi nhớ không lầm là danh từ riêng phải viết hoa, đó là sự trân trọng tên một người, và điều này cũng áp dụng cho quốc tế dùng ngôn ngữ La tinh.

Thân

Dạ vâng, em sẽ chú ý hơn ạ.Mong mọi người bỏ qua cho e ạ, Lần sau em sẽ chú ý hơn ạ.