Đăng ký học Excel và VBA cùng GPE tháng 11 - TPHCM

Mua sách "VBA trong Excel - Cải thiện và tăng tốc" tái bản

Cộng thức cộng các cột cách nhau tuần tự

Thảo luận trong 'Hàm và công thức Excel' bắt đầu bởi ngũ sắc, 10 Tháng mười một 2006.

  1. ngũ sắc

    ngũ sắc Thành viên mới

    Tôi có một bảng tính rất dài, sử dụng hết các cột cho phép trong 1 sheet
    Tôi cần tính tổng tuần tự giá trị của các cột C + f + i .....(tuần tự cộng giá trị của cột đầu và những cột cách nhau 3 cột) cho đến hết sheet.
    Nếu cộng bằng tay tôi sợ bị sót, có công thức nào để làm được điều đó không? Nhờ các bạn chỉ giáo
    cảm ơn nhièu
     
  2. SA_DQ

    SA_DQ Thành viên danh dự

    Trước tiên bạn thử với macro này

    Macro sẽ tô màu xanh lơ các cột bạn cần, bạn thử kiểm tra nha!
    Sau đó bạn sửa lại để nó làm phép cộng cho bạn;
    -> /(/ếu bạn không tự sửa được thì nhờ một ai đó, hẹn gặp!


    Sub CongCotCach3()
    On Error Resume Next
    Dim StrC0 As String, StrC1 As String
    ReDim MStrC(1 To 255) As String
    Dim iZ As Integer, iJ As Integer

    StrC0 = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    For iJ = 1 To 10
    StrC1 = Choose(iJ, "", "A", "B", "C", "D", "E", "F", "G", "H", "I")
    For iZ = 1 To 26
    MStrC(iZ) = StrC1 & Mid$(StrC0, (iZ Mod 27), 1)
    If iZ Mod 3 = 0 Then
    Range(MStrC(iZ) & 2).Select: Selection.Interior.ColorIndex = 35
    End If
    Next iZ, iJ

    End Sub
     
  3. nvson

    nvson Geotechnics

    Nhấn Alt+F11 để vào Microsoft Visual Basic
    Nhấn Insert/Module, rồi thêm code sau:
    Mã:
    Option Explicit
    Public Sub CongcacCot()
    On Error GoTo thoat
    MsgBox "Ban hay chon vung (boi den) de tinh tong" & vbCrLf & "Macro nay se tinh tong tu cot hien tai cho den cot cuoi cung" & vbCrLf & "Nhan OK de bat dau"
    Dim i As Integer, col1 As Integer, row1 As Integer, row2 As Integer
    Dim n As Integer
    Dim rngData As Range
    Set rngData = Selection
    n = CInt(InputBox("Nhap buoc nhay n = ", , 2))
    col1 = rngData.Column
    row1 = rngData.Row
    row2 = rngData.Rows.Count + row1 - 1
    For i = col1 To 256 Step n
        If Not IsEmpty(Cells(row2, i)) Then Cells(row2 + 1, i).FormulaR1C1 = "=SUM(R[" & -rngData.Rows.Count & "]C:R[-1]C)"
    Next i
    thoat:
    End Sub
     
    
     
  4. Đào Việt Cường

    Đào Việt Cường Cu Tí sành điệu

    Dear all,
    --------
    Có vẻ như các giải pháp đưa ra không thoả mãn yêu cầu của bài toán:
    [​IMG] Giải Pháp Excel > Kiến thức Excel > Excel và các công thức
    [​IMG] Cộng thức cộng các cột cách nhau tuần tự :
    Theo tôi, hãy nghĩ tới các công thức có sẵn của Excel trước khi làm điều này thông qua VBA.
    Công thức:
    Phương pháp vận vận dụng đối với bài toán này là phép tính chẵn lẻ: lấy số dư của một phép chia phần nguyên. Ở đây, yêu cầu là sau mỗi 3 cột thì sẽ cộng giá trị một cột, nói cách khác sẽ cộng ở cột thứ 4.
    Chèn vào một dòng và lập công thức và sử dụng hàm MOD(<Number>,<divisor>), với:
    + <Number> = Column(<reference>) - <chỉ số cột trước cột bắt đầu tính tổng> (tại ô đầu tiên kết quả = 1),
    + <divisor> = 4.
    Nếu cột nào mà hàm trả về kết quả là 1 thì thoả mãn điều kiện của phép cộng.
    Bây giờ ở cột tổng cộng, bạn sử dụng hàm SUMIF(). Ngoài ra các bạn có thể vận dụng hàm SUMPRODUCT() để làm điều này.
    Xem ví dụ trong file đính kèm:
     

    Các file đính kèm:

    Lần chỉnh sửa cuối: 11 Tháng mười một 2006
  5. ThuNghi

    ThuNghi Hãy cho rồi sẽ nhận!

    Triển khai như Anh Cường, có thể dùng hàm sumproduct theo mod
    =SUMPRODUCT(((MOD(COLUMN(a1:x1),Xi)=1)*(a1:x1)))
    Xi là biến
    Nếu cộng từ cột 5 thì vận dụng vào Mod(...)-4
     
  6. hoangdanh282vn

    hoangdanh282vn Nguyễn Cảnh Hoàng Danh Staff Member

    B1=C1+F1+I1+...+IV
    B1 : {=SUM((MOD(COLUMN(C1:IV1),3)=0)*C1:IV1)}
    Nhớ ấn Ctrl+shift+enter sau khi gõ xong công thức
     
  7. levietdan

    levietdan Thành viên mới

    Bạn chỉ giúp mình làm thế nào để sao chép công thức trên xuống các ô ở dưới với
     
  8. levietdan

    levietdan Thành viên mới

    làm cách nào để sao chép công thức xuống các ô bên dưới vậy bạn
     
  9. cadic666

    cadic666 Thành viên mới

    Chi làm được khi từ C1:IV1 là số ; còn khi từ C1:IV1 lẫn số và ký tự bị lỗi, có cách nào khác không??
     
  10. thanh73bs

    thanh73bs Thành viên mới

    Công thức cộng các cột cách nhau tuần tự

    Câu trả lời của bạn hoangdanh282vn rất hữu ích cho công việc của mình. Cảm ơn bạn, tuy nhiên trong bảng tính của mình có nhiều cột với nhiều dữ liệu cần phải cộng. Ở bảng mình đính kèm, mình muốn cộng tổng các cột: F, I, L, O.... trả về cột C, tương tự như vậy: các số liệu các cột: H, K, N, Q... trả về cột: E
    Mong các cao thủ chỉ giáo. Cảm ơn rất nhiều!
     

    Các file đính kèm:

  11. HieuCD

    HieuCD Thành viên gạo cội

    cần gì rắc rối vậy, chỉ cần
    Mã:
     C7 =SUMIF($F$5:$AI$5,F$5,$F7:$AI7)
    copy xuống và copy cho cột E
     
    quocgiacan thích bài này.
  12. quocgiacan

    quocgiacan Thành viên GPE

    Rủi chủ thớt thích rắc rối sao bạn hiền, khà khà khà }}}}}}}}}}}}}}}

    Chào thanh73bs,

    • Công thức dễ áp dụng và tiện nhất (Bạn HieuCD đã hướng dẫn trên):
      Mã:
      C7 =SUMIF(D$5:$AI$5,C$5,D7:$AI7)
      Enter, rồi fill xuống. Chú ý: phải điều chỉnh cho khớp tiêu đề tại dòng từ C5:AI5, (Vd: Là "S. lượng" giống nhau hết giữa các cột, chứ đừng vừa có "Sản lượng" vừa có "S. lượng"....
    • Nếu bạn muốn áp dụng theo công thức của hoangdanh282vn, thì hơi rắc rối thêm chút, như vầy:
      Mã:
      C7=SUM((MOD(COLUMN(D$1:$AI$1)-COLUMN(),3)=0)*D7:$AI7)
      Ctrl+Shift+Enter, rồi fill xuống. Cái quan trọng là bạn phải biết vị trí mình đứng tại đâu để tính cột cho khớp bội số 3, mới cho kết quả đúng.
    • Nếu bạn thấy cộng cách khoảng tiện cho việc của bạn, thì chơi thuần mảng như vầy:
      Mã:
      C7=SUM(N(OFFSET(C7,,ROW($1:$20)*3)))
      Ctrl+Shift+Enter, rồi fill xuống. (theo phong thái của Leoheocon }}}}})
    • Sau đó cứ lấy công thức tại cột C vừa tạo trên, copy và dán qua cột D và E (áp dụng cho cả ba công thức)

    Lưu ý:
    • Hai công thức mảng phía dưới chỉ ra kết quả đúng khi bạn giữ nguyên trạng thái bố trí cột, nó sẽ sai khi bạn chèn thêm một cột nào đó vào giữa bảng từ C:AI. Cho nên việc cộng tổng theo điều kiện: tốt và hay nhất là làm theo cách đầu tiên như bạn HieuCD đã nêu, chỉ với lưu ý nhỏ tôi đã nêu là phải khớp các tiêu đề.

    Chúc bạn học tập vui với gia đình GPE. /-*+//-*+//-*+/
     
  13. thanh73bs

    thanh73bs Thành viên mới

    Cảm ơn bạn HieuCD rất rất nhiều!!!
    Cảm ơn giaiphapexcel
    Mình đã giải quyết được cái bảng tính của mình. Mình đang bị cuồng excel đến nơi mất rồi. Cảm ơn bạn một lần nữa nhé }}}}} }}}}} }}}}}
     
  14. hoanhnnc

    hoanhnnc Thành viên mới

    Mọi người giúp công thức tính giờ thử việc, ngày thường, chủ nhật , lễ trong thời gian thử việc và chính thức , E nghĩ mãi không ra dùng sumif thì tính đến cuối tháng vì lương thử việc và chính thức khác nhau.
     

    Các file đính kèm:

Chia sẻ trang này