có cách nào kết hợp lọc để tính tổng có thể copy nhanh không ạh? (1 người xem)

[Phanhanhdai]

Xin hãy đổi cho em từ Sub về Funtion với

Em có tìm được bài viết của bác Anhtuan1066, đúng là ý tưởng mà em đã nghĩ, em rất thích kiểu làm này. Xin hãy chỉ cho em cách chuyển Sub về Fution với ạh (để em biến nó thành Add_Ins).

 

[ptm0412]

Viết thử 1 Function SubSum đơn giản, bạn thử test xem, có điều khi filter chưa vừa ý lắm.

Function SubSum(CriteriaRng As Range, SumRng As Range)
   Application.Volatile (True)
   EndR = CriteriaRng.End(xlDown).Row - 1
   FirstR = SumRng.Row
   SumCol = SumRng.Column
   SubSum = Application.Sum(Range(Cells(FirstR, SumCol), Cells(EndR, SumCol)))
End Function

Mượn file bài trên: Có 2 sheet, 1 sheet có chừa dòng tổng con trong cột và 1 sheet không chừa dòng, phải sum ở cột khác.
Cách dùng đơn giản ở chỗ:
- Chỉ có 2 tham số
- Tham số thứ nhất là ô điều kiện (ngày, số phiếu)
- Tham số thứ 2 là vùng tính tổng, ô đầu là ô đầu tiên chứa số cần tính tổng trở xuống, ô cuối là ô cuối cùng của cả bảng, hoặc dư ra cũng được. Và chỉ chọn 1 ô đầu thôi, cũng được nốt!
- Có thể copy paste

Tất nhiên đơn giản, dễ dùng thì cũng có hạn chế là cấu trúc bảng phải đồng nhất và theo mẫu (đã có 2 mẫu). Nếu bạn nào có mẫu khác thì gởi lên để mình cải tiến lại cho tổng quát

 

[concogia]

...được:
1) Viết một Macro sao cho khi tính ta quét chọn 2 cột A và D chẳng hạn, theo nguyên tắc số dòng quét 2 cột A và D là phải bằng nhau tựa như hàm sumif vậy (A là cột đánh số thứ tự có vai trò chia phân đoạn cần tính tổng, D là cột cần tính tổng theo từng phân đoạn) sau đó khi chạy Alt+F8 thì chương trình cứ nhìn tên số thứ tự của cột A mà tự động tham chiếu điền công thức sum vào cột D.
2) Để sử dụng cho các file ta có thể xuất Macro này ra dưới dạng Add-Ins thêm vào Menu (vì qua sử dụng em thấy nếu chuyển đổi sang Add-Ins chương trình Bkav home không quét virut Macro_rất hay trong việc sử dụng).
Như vậy bất cứ khi nào cần tính tổng cho mọi công việc hàng ngày có tính chất như bài này; bất kỳ lúc nào ta cũng thực hiện được nhanh, chóng mà vẫn đảm bảo được trực quan như làm thủ công(công thức thì thể hiện như sum bình thường giống file 02 em vừa gửi lên ngay trên ở cùng chủ đề này ah).

Cái này làm cũng được, lấy dữ liệu bài đầu, ta chọn cột B cho hợp lý (không cần chọn 2 cột)

Public Sub YeuCauNgoWa()
    Dim Vung, iDau, iCuoi, J
    Set Vung = Application.InputBox("Nhap dia chi", "Vùng chon", Type:=8)
    With Range([c2], [c10000].End(xlUp))
        .Font.ColorIndex = 0
        .Interior.ColorIndex = xlNone
    End With
        For J = Vung.Rows.Count To 1 Step -1
            If J = Vung.Rows.Count Then
                iCuoi = J + 1
            ElseIf Vung(J).Offset(, -1) = "" And Vung(J + 1).Offset(, -1) <> "" Then
                iCuoi = J + 1
            End If
                If Vung(J).Offset(, -1) = "" And Vung(J - 1).Offset(, -1) <> "" Then iDau = J + 1
                    If Vung(J).Offset(, -1) <> "" Then
                        With Vung(J).Offset(, 1)
                            .Formula = "=SUM(" & Cells(iDau, 3).Address & ":" & Cells(iCuoi, 3).Address & ")"
                            .Font.ColorIndex = 3
                            .Interior.ColorIndex = 6
                        End With
                    End If
        Next
End Sub

Cách làm:
Bấm Ctrl + M ==> chọn vùng dữ liệu ở cột B cho hộp Input (trong bài là B2:B21) ==> Ok ==> nó sẽ gán công thức như mình làm thủ công vậy
(Bấm Ctrl + Shilf + M ==> chọn vùng dữ liệu ở cột B (trong bài là B2:B21) ==> Ok ==> nó công luôn, không gán công thức)
Theo mình nghĩ, dạng bài thế này, dữ liệu của TA, code của TA thì cần "đếch" gì hộp thoại, ta cứ khai báo trong code, nó chạy cái "vèo" là xong, có thêm, bớt, sửa ta cũng chỉ bấm Ctrl + M cái "vèo" là...xong (nhanh gấp mấy lần xài công thức ý)
Híc, đây là ý kiến riêng của mình thôi nhé

 

[nguyenmanhnam]

Xin đa tạ các thày đã chỉ bảo, Excel em đã biết làm kha khá. Giờ này được mọi người giúp đỡ, chắc dịp nghỉ này em có dịp nghiên cứu nâng cao tay nghề VBA rùi. Em sẽ cố gắng trong thời gian ngắn sắp tới tới sẽ tự mình làm được một số công việc (không trình độ A, B, C) lắm lúc mệt thật. Chúc mọi người trên diễn đàn GPE sang năm mới mọi sự an khang, thịnh vượng.

 

[nguyenmanhnam]

Cái này làm cũng được, lấy dữ liệu bài đầu, ta chọn cột B cho hợp lý (không cần chọn 2 cột)

Public Sub YeuCauNgoWa()
Dim Vung, iDau, iCuoi, J
Set Vung = Application.InputBox("Nhap dia chi", "Vùng chon", Type:=8)
With Range([c2], [c10000].End(xlUp))
.Font.ColorIndex = 0
.Interior.ColorIndex = xlNone
End With
For J = Vung.Rows.Count To 1 Step -1
If J = Vung.Rows.Count Then
iCuoi = J + 1
ElseIf Vung(J).Offset(, -1) = "" And Vung(J + 1).Offset(, -1) <> "" Then
iCuoi = J + 1
End If
If Vung(J).Offset(, -1) = "" And Vung(J - 1).Offset(, -1) <> "" Then iDau = J + 1
If Vung(J).Offset(, -1) <> "" Then
With Vung(J).Offset(, 1)
.Formula = "=SUM(" & Cells(iDau, 3).Address & ":" & Cells(iCuoi, 3).Address & ")"
.Font.ColorIndex = 3
.Interior.ColorIndex = 6
End With
End If
Next
End Sub

Cách làm:
Bấm Ctrl + M ==> chọn vùng dữ liệu ở cột B cho hộp Input (trong bài là B2:B21) ==> Ok ==> nó sẽ gán công thức như mình làm thủ công vậy
(Bấm Ctrl + Shilf + M ==> chọn vùng dữ liệu ở cột B (trong bài là B2:B21) ==> Ok ==> nó công luôn, không gán công thức)
Theo mình nghĩ, dạng bài thế này, dữ liệu của TA, code của TA thì cần "đếch" gì hộp thoại, ta cứ khai báo trong code, nó chạy cái "vèo" là xong, có thêm, bớt, sửa ta cũng chỉ bấm Ctrl + M cái "vèo" là...xong (nhanh gấp mấy lần xài công thức ý)
Híc, đây là ý kiến riêng của mình thôi nhé

Vâng, đối với thày thì nó quá đơn giản, nhưng nó rất có ý nghĩa với em thày ạh. Nó là cơ sở em tiếp cận VBA từng bước 1. Cám ơn thày nhiều.

 

[Phanhanhdai]

Cái này làm cũng được, lấy dữ liệu bài đầu, ta chọn cột B cho hợp lý (không cần chọn 2 cột)

Public Sub YeuCauNgoWa()
Dim Vung, iDau, iCuoi, J
Set Vung = Application.InputBox("Nhap dia chi", "Vùng chon", Type:=8)
With Range([c2], [c10000].End(xlUp))
.Font.ColorIndex = 0
.Interior.ColorIndex = xlNone
End With
For J = Vung.Rows.Count To 1 Step -1
If J = Vung.Rows.Count Then
iCuoi = J + 1
ElseIf Vung(J).Offset(, -1) = "" And Vung(J + 1).Offset(, -1) <> "" Then
iCuoi = J + 1
End If
If Vung(J).Offset(, -1) = "" And Vung(J - 1).Offset(, -1) <> "" Then iDau = J + 1
If Vung(J).Offset(, -1) <> "" Then
With Vung(J).Offset(, 1)
.Formula = "=SUM(" & Cells(iDau, 3).Address & ":" & Cells(iCuoi, 3).Address & ")"
.Font.ColorIndex = 3
.Interior.ColorIndex = 6
End With
End If
Next
End Sub

Cách làm:
Bấm Ctrl + M ==> chọn vùng dữ liệu ở cột B cho hộp Input (trong bài là B2:B21) ==> Ok ==> nó sẽ gán công thức như mình làm thủ công vậy
(Bấm Ctrl + Shilf + M ==> chọn vùng dữ liệu ở cột B (trong bài là B2:B21) ==> Ok ==> nó công luôn, không gán công thức)
Theo mình nghĩ, dạng bài thế này, dữ liệu của TA, code của TA thì cần "đếch" gì hộp thoại, ta cứ khai báo trong code, nó chạy cái "vèo" là xong, có thêm, bớt, sửa ta cũng chỉ bấm Ctrl + M cái "vèo" là...xong (nhanh gấp mấy lần xài công thức ý)
Híc, đây là ý kiến riêng của mình thôi nhé

Đúng kiểu em cần rồi, nếu thày chữa kiểu gì mà vùng dữ liệu cần nhập là cột A và cột C thì tốt quá (bỏ qua vai trò cột B). Vì mục đích của em là nghiên cứu để phát triển áp dụng trong thực tế thày ạh.
Của thày có ưu điểm lựa chọn nhanh (do chỉ cần 1 cột) nhưng có nhược điểm hơi cứng do 3 cột A, B, C liền nhau thì chương trình mới hiểu. Em muốn khi sử dụng em có thể dùng 2 cột cách nhau như cột E và H chẳng hạn (trong đó cột H sẽ do em tạo ra bằng cách dùng hàm Excel đơn thuần để tự đánh số thứ tự đáp ứng đầu bài thực tế có vai trò như cột A trong ví dụ đính kèm của thày, cột E có vai trò như cột C trong ví dụ đính kèm của thày).

 

[Phanhanhdai]

Viết thử 1 Function SubSum đơn giản, bạn thử test xem, có điều khi filter chưa vừa ý lắm.

Function SubSum(CriteriaRng As Range, SumRng As Range)
Application.Volatile (True)
EndR = CriteriaRng.End(xlDown).Row - 1
FirstR = SumRng.Row
SumCol = SumRng.Column
SubSum = Application.Sum(Range(Cells(FirstR, SumCol), Cells(EndR, SumCol)))
End Function

Mượn file bài trên: Có 2 sheet, 1 sheet có chừa dòng tổng con trong cột và 1 sheet không chừa dòng, phải sum ở cột khác.
Cách dùng đơn giản ở chỗ:
- Chỉ có 2 tham số
- Tham số thứ nhất là ô điều kiện (ngày, số phiếu)
- Tham số thứ 2 là vùng tính tổng, ô đầu là ô đầu tiên chứa số cần tính tổng trở xuống, ô cuối là ô cuối cùng của cả bảng, hoặc dư ra cũng được. Và chỉ chọn 1 ô đầu thôi, cũng được nốt!
- Có thể copy paste

Tất nhiên đơn giản, dễ dùng thì cũng có hạn chế là cấu trúc bảng phải đồng nhất và theo mẫu (đã có 2 mẫu). Nếu bạn nào có mẫu khác thì gởi lên để mình cải tiến lại cho tổng quát

Ý em là thày làm sao mà như kết quả hiện công thức như làm thủ công đơn thuần tức là gần giống của thày concogia ấy (sum chứ không phải là Subsum thày ạh), như vây nó đảm bảo được tính trực quan vì mục đích chỉ là làm nhanh nhưng để cho những người mới biết Excel cũng hiểu được, chứ họ nhìn vào hàm Subsum họ chẳng hiểu gì. Ví dụ em đưa ra đơn giản thôi lựa chọn 2 cột A và B (trong thực tế khi ứng dụng vào công việc thật em có thể lựa chọn 2 cột khác có thể cách nhau cũng được). Cụ thể ở ví dụn này nếu A2 có số liệu thì kết quả sẽ điền vào ô B2 (nếu B2 đã có dữ liệu rồi thì xoá đi thay giá trị khác).
Ví dụ của các thày em tự nghiên cứu đã quá tốt rồi, nếu thày có thể hãy giúp em vào file em đính kèm nhé.

 

[concogia]

Đúng kiểu em cần rồi, nếu thày chữa kiểu gì mà vùng dữ liệu cần nhập là cột A và cột C thì tốt quá (bỏ qua vai trò cột B). Vì mục đích của em là nghiên cứu để phát triển áp dụng trong thực tế thày ạh.
Của thày có ưu điểm lựa chọn nhanh (do chỉ cần 1 cột) nhưng có nhược điểm hơi cứng do 3 cột A, B, C liền nhau thì chương trình mới hiểu. Em muốn khi sử dụng em có thể dùng 2 cột cách nhau như cột E và H chẳng hạn (trong đó cột H sẽ do em tạo ra bằng cách dùng hàm Excel đơn thuần để tự đánh số thứ tự đáp ứng đầu bài thực tế có vai trò như cột A trong ví dụ đính kèm của thày, cột E có vai trò như cột C trong ví dụ đính kèm của thày).

Hihi, thấy topic này "zui" quá nên tham gia thôi, chữa như ý bạn cũng không khó, bi giờ bạn chép code này thế cái cũ thử xem nhé

Public Sub YeuCauNgoWa()
    Dim VungDk, VungKq, iDau, iCuoi, J, kK, mM
    Set VungDk = Application.InputBox("Nhap dia chi", "Vùng Dieu Kien", Type:=8)
    Set VungKq = Application.InputBox("Nhap dia chi", "Vùng Ket Qua", Type:=8)
    With VungKq
        .Font.ColorIndex = 0
        .Interior.ColorIndex = xlNone
    End With
        For J = VungDk.Rows.Count To 1 Step -1
        kK = VungDk(J).Row: mM = VungKq(J).Column
            If J = VungDk.Rows.Count Then
                iCuoi = kK
            ElseIf VungDk(J) = "" And VungDk(J + 1) <> "" Then
                iCuoi = kK
            End If
                If VungDk(J) = "" And VungDk(J - 1) <> "" Then iDau = kK
                    If VungDk(J) <> "" Then
                        With VungKq(J)
                            .Formula = "=SUM(" & Cells(iDau, mM).Address & ":" & Cells(iCuoi, mM).Address & ")"
                            .Font.ColorIndex = 3
                            .Interior.ColorIndex = 6
                        End With
                    End If
        Next
End Sub

Khi chạy code sẽ ra 2 hộp:
- Hộp thứ nhất bạn chọn vùng nào tương đương như cột A trong bài trước
- Hộp thứ hai bạn chọn vùng tương đương với cột C trong bài trước nhé
Cột nào cũng được, tùy theo sự sắp xếp dữ liệu của bạn, không nhất thiết phải là E và H đâu nhé
"Xí quên", mình chưa viết đoạn kiểm tra xem 2 vùng bạn chọn có tương đương nhau không? Bạn cứ thử "em" này. có gì nói tiếp, Tết cũng buồn, nhậu hoài mệt quá, cho cái đầu làm việc tí cũng tốt
Thân

 

[ptm0412]

Ý em là thày làm sao mà như kết quả hiện công thức như làm thủ công đơn thuần tức là gần giống của thày concogia ấy (sum chứ không phải là Subsum thày ạh), như vây nó đảm bảo được tính trực quan vì mục đích chỉ là làm nhanh nhưng để cho những người mới biết Excel cũng hiểu được, chứ họ nhìn vào hàm Subsum họ chẳng hiểu gì. Ví dụ em đưa ra đơn giản thôi lựa chọn 2 cột A và B (trong thực tế khi ứng dụng vào công việc thật em có thể lựa chọn 2 cột khác có thể cách nhau cũng được). Cụ thể ở ví dụn này nếu A2 có số liệu thì kết quả sẽ điền vào ô B2 (nếu B2 đã có dữ liệu rồi thì xoá đi thay giá trị khác).
Ví dụ của các thày em tự nghiên cứu đã quá tốt rồi, nếu thày có thể hãy giúp em vào file em đính kèm nhé.

Bài viết thành hàm là đáp ứng yêu cầu của chủ topic Dauthivan và nguyenmanhnam.
Nhưng sao bây giờ thấy rối quá, nguyenmanhnam lúc trước yêu cầu function:

Em đã xem rồi, nhưng những ví dụ trong đó đều là dạng Sub, ý em là làm VBA theo kiểu hàm Funtion, qua đó nhân dịp nghỉ Tết em sẽ thay đổi đầu bài để ôn luyện cho kỹ,

Bây giờ lại khen Cò già: (Cò già dùng Sub)

Vâng, đối với thày thì nó quá đơn giản, nhưng nó rất có ý nghĩa với em thày ạh. Nó là cơ sở em tiếp cận VBA từng bước 1. Cám ơn thày nhiều.

Chắc khuynh hướng Sub phát triển mạnh hơn rồi. Vậy dùng Sub của Cò và của ndu đi, xài cũng được mà?

 

[Phanhanhdai]

Bài viết thành hàm là đáp ứng yêu cầu của chủ topic Dauthivan và nguyenmanhnam.
Nhưng sao bây giờ thấy rối quá, nguyenmanhnam lúc trước yêu cầu function:


Bây giờ lại khen Cò già: (Cò già dùng Sub)


Chắc khuynh hướng Sub phát triển mạnh hơn rồi. Vậy dùng Sub của Cò và của ndu đi, xài cũng được mà?

Ý bạn nguyenmanhnam diễn đạt chưa toát có lẽ do bọn em đều là mới tiếp cận VBA nên cách viết chưa được, mục đích của nó là:
- Kết quả đầu ra sẽ liên kết với đầu vào để lỡ đầu vào thay đổi thì kết quả lại thay đổi chứ không phải là kết quả bằng Value (ý nghĩa Funtion là thế thày ah).
- Còn Fution này thực chất chỉ là hàm sum thông thường được điền tự động thôi (khi chạy Macro), mục đích là khi copy ra máy khác không có VBA thì khi không liên kết được đoạn Code thì nó vẫn dùng hàm Excel thông thường (hàm sum).
- Em hiểu như vậy chứ không phải là hàm Subsum tự tạo bởi nếu mang sang máy khác ở cơ quan Office không cho chạy VBA thì file báo lỗi.
Bạn ý nhầm vì nhận thức rằng Sub chỉ tạo ra Value, còn Fution là tạo ra hàm sum đó thày.
Em cảm ơn thày nhiều, chắc chắn các bài phức tạp em phải nhờ học đến kiểu tạo hàm mới (như là hàm Subsum của thày rồi, khi đó nếu có gì vướng mắc gì thày chỉ giúp em với) vì bài này thực chất là hàm sum đơn giản nhưng KL bọn em sử lý mấy ngàn dòng sợ thao tác bằng tay nhầm). Chúc thày đón năm mới tràn ngập niềm vui.

 

[nguyenmanhnam]

Bài viết thành hàm là đáp ứng yêu cầu của chủ topic Dauthivan và nguyenmanhnam.
Nhưng sao bây giờ thấy rối quá, nguyenmanhnam lúc trước yêu cầu function:


Bây giờ lại khen Cò già: (Cò già dùng Sub)


Chắc khuynh hướng Sub phát triển mạnh hơn rồi. Vậy dùng Sub của Cò và của ndu đi, xài cũng được mà?

Bác Phanhanhdai nói đúng đó thày, do em chưa biết cách biểu đạt. Có lẽ tiêu đề chắc phải là Dùng Sub để tự động điền công thức phải không ạh? Do em mới trình độ ở lớp vỡ lòng nên chắc thày không hình dung được tuy ban đầu chỉ có bài toán đơn giản này có được các thày giúp đỡ thành công, qua đó em có thể học hỏi, tiếp cận những bài mở đầu làm em thấy rất vui, em đã bắt đầu thích học VBA rồi. Từ những khởi điểm các thày chỉ bảo em có thể nghiên cứu thành công để có thể truyền đạt cho mọi người đi sau. Em tin lúc đó em có thể nói ăn GPE, ngủ GPE như các thày nhỉ? Chúc các thày luôn hạnh phúc, năm mới ngập tràn niềm vui

 

[ptm0412]

Code Con Cò già bài 28 đã đạt yêu cầu rồi mà: Chọn vùng điều kiện, chọn vùng cần tính tổng, ra kết quả công thức SUM(...)

Có lẽ tiêu đề chắc phải là Dùng Sub để tự động điền công thức phải không ạh?

Chính xác là dùng Sub gán công thức, chứ không phải dùng Sub gán Function, hay dùng Function.
Còn tiêu đề là của DauThiVan, yêu cầu là dùng công thức hoặc hàm tự tạo có thể copy được. Những bài có yêu cầu không giống vậy là ăn theo đấy, không sửa tiêu đề được.

 

[trungvdb]

Code của thày Concogia

Public Sub YeuCauNgoWa()
    Dim VungDk, VungKq, iDau, iCuoi, J, kK, mM
    Set VungDk = Application.InputBox("Nhap dia chi", "Vùng Dieu Kien", Type:=8)
    Set VungKq = Application.InputBox("Nhap dia chi", "Vùng Ket Qua", Type:=8)
    With VungKq
        .Font.ColorIndex = 0
        .Interior.ColorIndex = xlNone
    End With
        For J = VungDk.Rows.Count To 1 Step -1
        kK = VungDk(J).Row: mM = VungKq(J).Column
            If J = VungDk.Rows.Count Then
                iCuoi = kK
            ElseIf VungDk(J) = "" And VungDk(J + 1) <> "" Then
                iCuoi = kK
            End If
                If VungDk(J) = "" And VungDk(J - 1) <> "" Then iDau = kK
                    If VungDk(J) <> "" Then
                        With VungKq(J)
                            .Formula = "=SUM(" & Cells(iDau, mM).Address & ":" & Cells(iCuoi, mM).Address & ")"
                            .Font.ColorIndex = 3
                            .Interior.ColorIndex = 6
                        End With
                    End If
        Next
End Sub

Trường hợp dữ liệu bắt đầu chọn từ dòng 1 thì sẽ bị lỗi (J-1=0) nên sẽ không thể thực hiện được VungDk(J - 1), tuy nhiên trường hợp này ít xảy ra vì thông thường dòng trên cùng thường là dòng tiêu đề

Cách này sẽ cho tốc độ chậm hơn là dùng biến tạm cho vào mảng rồi xuất hiện kết quả ra màn hình dưới dạng Value, tuy nhiên trường hợp gửi báo cáo cho đối tác kiểm tra thì lại là rất cần thiết

Bài Tham khảo http://www.giaiphapexcel.com/forum/showthread.php?60101-Hỏi-giải-pháp-sao-cho-Code-trở-thành-Code-động-có-tính-co-dãn-cùng-bảng-tính/page2do thày Ndu giải.