thuật giải đối với công thức cộng dữ liệu có điều kiện (help me) (1 người xem)

[giaosy]

Trong excel có ham sumif dùng để cộng dữ liệu có điều kiện, tuy nhiên khi áp dụng vào bảng tính lớn thì nó làm dung lượng của ứng dụng tăng lên rất nhiều, ảnh hưởng đến việc tính toán của excel. Mong các bác giúp em thuật giải của hàm này: dùng vba để thay thế hàm sumif như thế nào. Cảm ơn các bác nhiều.

Tất nhiên là có một cách hết sức đơn giản là dùng record macro ghi lại quá trình làm công thức sumif, sau đó mở edit macro ra thêm vào một đoạn code là: active một vùng cần nhập công thức sumif, gán công thức đã ghi lại bằng macro vào nhưng cách này không triệt để , đôi khi gây ra lỗi trong khi chạy. Mong các bác quan tâm, chỉ bảo cho em, cảm ơn các bác.

 

[Mr Okebab]

Bạn hãy cho file VD, còn dùng macro như bạn thì chỉ cần dùng hàng đầu tiên thôi, các hàng dưới đó thì copy công thức xuống, copy dán giá trị lên. Đó là một cách làm đơn giản nhưng không triệt để.
Để dễ hình dung thì bạn up file của mình lên đi.

 

[Đào Việt Cường]

Trong excel có ham sumif dùng để cộng dữ liệu có điều kiện, tuy nhiên khi áp dụng vào bảng tính lớn thì nó làm dung lượng của ứng dụng tăng lên rất nhiều, ảnh hưởng đến việc tính toán của excel. Mong các bác giúp em thuật giải của hàm này: dùng vba để thay thế hàm sumif như thế nào. Cảm ơn các bác nhiều.

Dear all,
--------
1. Theo em, quan điểm của giaosy là hết sức sai lầm. Ngược lại, chính việc tạo ra module các hàm tự tạo mới khiến cho Excel trở lên cồng kềnh và chậm chạp hơn.
2. Việc sử dụng lại hàm SUMIF trong vĩ lệnh VBA thay vì viết trực tiếp trên Worksheet không được coi là giải thuật thay thế hàm SUMIF. Lý do là, để được kết quả tính toán của hàm này trên Formula, Excel phải thực hiện quá trình tính toán bằng cách gọi lớp thư viện Worksheet Functions để thực thi. Thư viện này đã được biên dịch ra mã máy nên tốc độ tính toán đương nhiên phải nhanh hơn hàm trong module VBA.
3. Giải thuật của SUMIF:
o Đầu vào:
- Vùng tính tổng
- Vùng điều kiện
- Điều kiện so sánh
o Đầu ra:
- Kết quả tính toán theo Điều kiện so sánh tìm thấy trong Vùng điều kiện
o Xử lý:
- So sánh Điều kiện với từng giá trị trong Vùng điều kiện, nếu kết quả đúng thì trả về giá trị tương ứng trong Vùng tính tổng
- Kết quả trả về được cộng dồn sau mỗi lần tìm thấy
Từ đây các bạn viết một hàm thay thế hàm SUMIF có sẵn, mức đơn giản như sau:

Function mySUMIF(rgSumRange As Range, rgCriteriaRange As Range, varCriteria As Variant)
[COLOR=darkgreen]'Bước đầu tiên phải kiểm tra tính hợp lệ và tương xứng của đầu vào:[/COLOR]
[COLOR=#006400]' - Các vùng dữ liệu chỉ có 1 cột[/COLOR]
[COLOR=#006400]' - Số dòng giữa rgSumRange phải bằng số dòng rgCriteriaRange[/COLOR]
[COLOR=#006400]' - ... vv...[/COLOR]
[COLOR=darkgreen]'(Các bạn tự viết)[/COLOR]
[COLOR=darkgreen]'[/COLOR]
[COLOR=darkgreen]'[/COLOR]
[COLOR=darkgreen]'Khai báo biến:[/COLOR]
Dim varSumResult As Variant [COLOR=#006400]'Lưu trữ kết quả tính tổng hiện hành[/COLOR]
Dim lngRowCount As Long [COLOR=#006400]'Số dòng của vùng điều kiện[/COLOR]
Dim i As Long
lngRowCount = rgCriteriaRange.Rows.Count
[COLOR=darkgreen]'Duyệt qua từng phần tử trong vùng rgCriteriaRange để so sánh[/COLOR]
For i = 1 To lngRowCount
    If rgCriteriaRange.Cells(i) = varCriteria Then
        varSumResult = varSumResult + rgSumRange.Cells(i)
    End If
Next
mySUMIF = varSumResult
End Function

Từ bài tập này các bạn có thể tự phát triển để mySUMIF có thể cộng theo nhiều điều kiện mà hàm SUMIF trong phiên bản Office 2003 trở về trước không làm được. Với phiên bản mới, Office 2007, các bạn làm được điều này dễ dàng với hàm SUMIFS:

SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)
Sum_range is one or more cells to sum, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to evaluate the associated criteria.
Criteria1, criteria2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.
Remarks

• Each cell in sum_range is summed only if all of the corresponding criteria specified are true for that cell.
• Cells in sum_range that contain TRUE evaluate as 1; cells in sum_range that contain FALSE evaluate as 0 (zero).
• Unlike the range and criteria arguments in the SUMIF function, in SUMIFS each criteria_range must be the same size and shape as sum_range.
• You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

 

[giaosy]

Cảm ơn các bác đã quan tâm, hướng dẫn nhất là ý kiến quý báu của bác Cường. Đề bài của em thì đơn giản lắm, vấn đề chỉ là ở chỗ là bảng thống kê to quá và em đang tìm cách làm giảm dung lượng của ứng dụng khi hoàn thành thôi.
Do đó em hy vọng là dùng vba để thay thế hàm sumif và gán nó cho một vùng dữ liệu để ứng dụng có thể nhẹ và chạy ổn định hơn. Qua gợi ý của bác Cường, có lẽ em sẽ phải suy nghĩ kỹ hơn về cách thực hiện.
Mong các bác tiếp tục hướng dẫn, rất cảm ơn các bác!