Lập công thức tính tổng con cho từng phân đoạn (1 người xem)

[anhtuan1066]

Các bạn mở file đính kèm lên xem...
Cấu trúc dử liệu dạng này tôi thấy có khá nhiều người sử dụng, hậu quả dẩn đến là ko tài nào SUMIF dc theo Mã...
Bài toán này dùng vòng lập For thì quá dể.. quét cái rẹt ra ngay... nhưng các bạn nghĩ xem nếu dùng công thức, có cách nào thiết lập công thức 1 cách tổng quát cho cột C ko? Cụ thể trong file, dử liệu tổng sẽ xuất hiện tại C6, C11 và C16 , còn các cell khác sẽ cho rổng
ANH TUẤN

 

[minhlev]

Trong trường hợp tương tự như bài của bác, em có thêm 1 cột số thứ tự vào và dùng hàm sumproduct.

 

[salam]

Em làm theo hướng như trong bài nhưng lại cột phụ bác xem thử xem.

 

[anhtuan1066]

Cách của bạn minhlev vẫn chưa ổn ở chổ:
1> Tốn công gõ cột phụ A
2> Và nếu đã tốn công như thế thôi thì kéo fill cột TÊN cho đầy đủ rồi dùng SUMIF có phải thuận tiện hơn ko?
-------------------------------
Cách làm của Salam giống 90% cach tôi đang làm... Bỏ cột phụ chẳng khó khăn gì, chẳng hạn đặt name:
VT=IF($A$6:$A$22=0,"",ROW($A$6:$B$22))
rồi dùng công thức:
=IF(A6="","",SUM(INDIRECT("B"&SMALL(VT,COUNTA($A$6:A6))&":B"&SMALL(VT,COUNTA($A$6:A6)+1)-1)))
--------------------------------
Thật sự tôi cũng đã nghĩ dc cách làm trong điều kiện tổng quát nhất, nhưng qua nhận xét thì thấy file nhiều dử liệu (khoảng 1000 dòng) thì tốc độ tính toán chậm quá
VBA tôi chưa làm nhưng tôi nghĩ là.. quá dể... Dùng FOR quét từ dưới lên, cộng dồn cột KL lại, đến khi gặp cột TÊN có dử liệu thì điền giá trị cộng dồn vào cột TỔNG, cho biến cộng dồn =0, lại tiếp tục quét..và cứ thế.. cứ thế đến hết...
Dùng công thức ko biết có ai còn cách khác làm cho tốc độ tính toán tăng lên dc ko nhỉ?
ANH TUẤN

 

[tigertiger]

Thật sự tôi cũng đã nghĩ dc cách làm trong điều kiện tổng quát nhất, nhưng qua nhận xét thì thấy file nhiều dử liệu (khoảng 1000 dòng) thì tốc độ tính toán chậm quá

Công thức trên là khá tổng quát đấy chứ, còn cách khác ah a???

VBA tôi chưa làm nhưng tôi nghĩ là.. quá dể... Dùng FOR quét từ dưới lên, cộng dồn cột KL lại, đến khi gặp cột TÊN có dử liệu thì điền giá trị cộng dồn vào cột TỔNG, cho biến cộng dồn =0, lại tiếp tục quét..và cứ thế.. cứ thế đến hết...
Dùng công thức ko biết có ai còn cách khác làm cho tốc độ tính toán tăng lên dc ko nhỉ?
ANH TUẤN

sao a không làm lun nhỉ, nhưng nên làm hàm (function) cho nó hay, vì nếu Sub thì thay đổi số liệu phải chạy lại

 

[hoangdanh282vn]

Các bạn mở file đính kèm lên xem...
Cấu trúc dử liệu dạng này tôi thấy có khá nhiều người sử dụng, hậu quả dẩn đến là ko tài nào SUMIF dc theo Mã...
Bài toán này dùng vòng lập For thì quá dể.. quét cái rẹt ra ngay... nhưng các bạn nghĩ xem nếu dùng công thức, có cách nào thiết lập công thức 1 cách tổng quát cho cột C ko? Cụ thể trong file, dử liệu tổng sẽ xuất hiện tại C6, C11 và C16 , còn các cell khác sẽ cho rổng
ANH TUẤN

Tối hôm qua mình đã nghĩ rất nhiều về vần đề mà Anhtuan đặt ra, thật nhức đầu về những công thức và cuối cùng mình cũng đúc kết được rằng :

Đừng dùng sự phức tạp để giải quyết những vần đề đơn giản
Hãy kết hợp những gì đơn giản để hóa giải vấn đề phức tạp.

 

[anhtuan1066]

Tigertiger ơi, cách làm bằng VBA thì tôi cũng có 1 bài tương tự tại đây:
http://www.giaiphapexcel.com/forum/showthread.php?t=8641
Nhưng tôi nghỉ ko nên tạo Function... vì nếu thế chẳng hóa ra lại trở về như củ.. tức chỉ lấy giá trị có vài cell mà phải làm công thức cho cả cột... nên tôi vẫn theo quan điểm:
1> Nếu dùng VBA thì tôi sẽ làm 1 sub chứ ko dùng Function
2> Nếu dùng công thức thì phải đảm bảo tốc độ tính toán nhanh và tổng quát cho mọi trường hợp
Tổng quát phải như vầy đây:
-Vì bảng tính của mỗi người có khác nhau (cột C của ta chưa chắc là cột C của người khác)... Vậy nếu làm bài toán này ta nên có 1 chổ nào đó cho mọi người khai báo rằng : "Tôi muốn tính tổng cho cột ... ??? theo cột.... ???""
Tóm lại: 1 công thức đúng với mọi bảng tính, tốc độ nhanh.. đó mới là vấn đề...
Vậy chúng ta thử nghiên cứu theo hướng này xem... Còn cách làm đơn giãn nhất đến thời điểm hiện giờ có lẽ là cách mà bạn hoangdanh282vn đang làm...
Mến
ANH TUẤN

 

[TKT]

Tốt nhất là dùng cột phụ để công thức đơn giản và dễ hiểu thì hay nhất.

 

[Mr Okebab]

Tigertiger ơi, cách làm bằng VBA thì tôi cũng có 1 bài tương tự tại đây:
http://www.giaiphapexcel.com/forum/showthread.php?t=8641
Nhưng tôi nghỉ ko nên tạo Function... vì nếu thế chẳng hóa ra lại trở về như củ.. tức chỉ lấy giá trị có vài cell mà phải làm công thức cho cả cột... nên tôi vẫn theo quan điểm:
1> Nếu dùng VBA thì tôi sẽ làm 1 sub chứ ko dùng Function
Tổng quát phải như vầy đây:
-Vì bảng tính của mỗi người có khác nhau (cột C của ta chưa chắc là cột C của người khác)... Vậy nếu làm bài toán này ta nên có 1 chổ nào đó cho mọi người khai báo rằng : "Tôi muốn tính tổng cho cột ... ??? theo cột.... ???""
Tóm lại: 1 công thức đúng với mọi bảng tính, tốc độ nhanh.. đó mới là vấn đề...

ANH TUẤN

Tại sao không phải là 1 Function mà lại là một Sub hả bác ??? Function thì sẽ đúng cho mọi trường hợp mà, chỉ cần chọn khối là xong.
Dùng VBA thì lại rất nhanh rồi, chỉ cần lặp khoảng chục lần là thoát thôi mà.
Như vậy mới đúng với cái "Tóm lại" của bác.

Function TC(MangMa As Range, MangSo As Range) As Double
    Application.Volatile (False)
    Dim i As Integer
    If MangMa.Rows.Count <> MangSo.Rows.Count Then Exit Function
    If MangMa(1) = "" Then Exit Function
    TC = MangSo(1)
    For i = 2 To MangMa.Rows.Count
        If MangMa(i) = "" Then
            TC = TC + MangSo(i)
        Else
            Exit Function
        End If
    Next
End Function

Thân!

 

[hoangdanh282vn]

Em làm theo hướng như trong bài nhưng lại cột phụ bác xem thử xem.

Sao ta không dùng =IF(A6<>"",ROW(A6),"")
để thay thế cho =IF(A6=0,"",MATCH(A6,$A$1:$A$22,0))
như thế sẽ gọn và nhanh hơn chứ nhỉ

 

[Mr Okebab]

He... he... ý tôi nói là: nếu phải chọn giữa UDF và công thức thường thì tôi sẽ chọn công thức thường... vì đàng nào cũng phải tốn công thức cho các cell rổng (vẫn phải tốn dung lượng)...
Còn nếu như phải chọn giữa công thức và VBA thì tôi sẽ chọn VBA, nhưng sẽ làm thành 1 Sub đễ khi cần thì chạy chứ ko xài UDF trong trường hợp này....
ANH TUẤN

Nếu muốn sự linh hoạt và cho mọi trường hợp thì chỉ có UDF mới làm được, Sub sao là làm được bác ???

Còn ý bác muốn dùng công thức thường, và nếu dùng VBA thì chỉ dùng Sub thì . . tùy bác.

Dùng lượng khi dùng UDF đâu có tăng lên nhiều đâu.
Còn nếu vì vấn đề dung lượng thì bác nên dùng UDF kết hợp với Sub thì sẽ tạo ra sự linh hoạt tuyệt vời, lại không bị tăng dữ liệu.

(Bác xem lại bài trên của em nhé)

Thân!

 

[anhtuan1066]

Ah... vấn đề tăng dung lượng nghĩa là tăng khi sử dụng ấy... tức khi ta điền dấu = vào cell thì cell đó phải nhận 1 giá tri nào đó, đương nhiên dung lượng cũng từ đó mà tăng... chứ tôi ko nói đoạn code làm tăng dung lượng
Vậy khi ta dùng UDF cho 100 cell với công thức = cái gì đó (....) thì nó đâu khác gì với việc ta dùng công thức thường đâu nhỉ?
Sub tất nhiên cũng sẽ làm dc trong trường hợp tổng quát nhất, chẳng hạn thêm thằng em InputBox cho phép chọn vùng, đơn giãn vậy thôi... nhưng bảo đãm là dung lượng tăng rất ít do cell chỉ chứa Value trên 1 vài vùng cụ thể nào đó chứ ko phải toàn bộ cột
he... he...
--------------
Nói thế nào cho mọi người hiểu nhỉ? Đại khái nếu cần điền giá trị vào toàn bộ cột C ko chừa em nào thì khi ấy dùng Function tôi chẳng có ý kiến... đàng này, trong file ví dụ của tôi chỉ cần điền 3 cell lại phải tốn công thức cho toàn bộ cột C... Ý tôi nói "tốn" là vậy đấy!
ANH TUẤN

 

[Mr Okebab]

Ah... vấn đề tăng dung lượng nghĩa là tăng khi sử dụng ấy... tức khi ta điền dấu = vào cell thì cell đó phải nhận 1 giá tri nào đó, đương nhiên dung lượng cũng từ đó mà tăng... chứ tôi ko nói đoạn code làm tăng dung lượng
Vậy khi ta dùng UDF cho 100 cell với công thức = cái gì đó (....) thì nó đâu khác gì với việc ta dùng công thức thường đâu nhỉ?
Sub tất nhiên cũng sẽ làm dc trong trường hợp tổng quát nhất, chẳng hạn thêm thằng em InputBox cho phép chọn vùng, đơn giãn vậy thôi... nhưng bảo đãm là dung lượng tăng rất ít do cell chỉ chứa Value trên 1 vài vùng cụ thể nào đó chứ ko phải toàn bộ cột
he... he...
--------------
Nói thế nào cho mọi người hiểu nhỉ? Đại khái nếu cần điền giá trị vào toàn bộ cột C ko chừa em nào thì khi ấy dùng Function tôi chẳng có ý kiến... đàng này, trong file ví dụ của tôi chỉ cần điền 3 cell lại phải tốn công thức cho toàn bộ cột C... Ý tôi nói "tốn" là vậy đấy!
ANH TUẤN

Vậy thì cứ làm SUB cho nó . . nhanh bác ạ.

Mấy cái trường hợp đơn giản này, nói thế nào là nhanh hay chậm thì cũng vô cùng lắm. Vì đơn giản thường gắn với sự tiện lợi.

Thân!

 

[tigertiger]

Nói thế nào cho mọi người hiểu nhỉ? Đại khái nếu cần điền giá trị vào toàn bộ cột C ko chừa em nào thì khi ấy dùng Function tôi chẳng có ý kiến... đàng này, trong file ví dụ của tôi chỉ cần điền 3 cell lại phải tốn công thức cho toàn bộ cột C... Ý tôi nói "tốn" là vậy đấy!
ANH TUẤN

Nếu thế thì chỉ dùng công thức UDF cho các ô cần tính thôi, bằng cách filter -> điền công thức UDF cho các ô đó - đơn giản vậy thôi atuan

 

[anhtuan1066]

He... he... Nghiên cứu đã có kết quả... Xin đưa lên giãi pháp cực kỳ đơn giản:
Cách 1: Dùng VBA như tôi đã nói là dùng vòng lập For quét ngược từ dưới lên... Cái này quá dễ ai cũng biết, chắc ko cần nói nhiều... (Xem tại sheet CT02 của file đính kèm)
Cách 2: Dùng công thức... Cũng theo thuật toán quét ngược từ dưới lên giống như cách dùng VBA, chính vì thế nếu các bạn nhìn công thức tại cell đầu tiên sẽ cãm giác hơi khó hiểu... Hảy chuyển xuống cell cuối cùng xem nhé... Chỉ là SUM bình thường, ko có cột phụ, ko có name... Tốc độ nó mà ko nhanh thì thật là chuyện lạ đấy!... ha.. ha... (Xem giải pháp tại sheet CT01 của file đính kèm)
Tin chắc với giải pháp dùng công thức này thì ai ai cũng hiểu, vì nó.. quá.. quá đơn giãn...
ANH TUẤN

 

[anhtuan1066]

Cải tiến bài toán thêm 1 bước, cho phép đặt kết quả trực tiếp vào cột thành tiền (vì tôi biết cũng có 1 số bạn có ý muốn bố trí dử liệu theo kiểu vậy)... Xem tại Sheet CT03 (cái này dùng công thức thì thua.. cả UDF cũng thua)
----------------------
Nhân đây cũng xin hỏi: Thay vì tính tổng, ta có giãi pháp nào cho việc trích xuất dử liệu đối với dạng cấu trúc dử liệu này ko? Ví dụ cần lấy ra toàn bộ dử liệu thuộc số phiếu N01?
ANH TUẤN

 

[anhtuan1066]

File trên vẩn còn 1 nhược điểm... Nếu tính tổng mà cho vào cột thành tiền luôn rồi lại chạy Sub tính tổng lần nữa sẽ cho kết quả sai (vì nó đã cộng dồn luôn cả kết quả lần trước...
Cải tiến mới, tránh dc sai sót đáng tiếc ấy!
ANH TUẤN

 

[HuuThanh]

Viết VBA như anh Mr_Okebad và anh anhtuan1066 thì kết quả ra thật tốt rồi. Em mới vào nhìn thấy các anh làm cái gì cũng nhanh và gọn quá. Cũng muốn học VBA một chút để phục vụ trong công việt. Rất mong các anh hướng dẫn dùm và cho em mấy địa chỉ. Em đã đọc bài hướng dẫn của anh Hưởng rồi. Nhưng đê viết được bài như các anh chắc còn dài lắm. Cảm ơn các anh rất nhiều !!!

 

[anhtuan1066]

Viết VBA như anh Mr_Okebad và anh anhtuan1066 thì kết quả ra thật tốt rồi. Em mới vào nhìn thấy các anh làm cái gì cũng nhanh và gọn quá. Cũng muốn học VBA một chút để phục vụ trong công việt. Rất mong các anh hướng dẫn dùm và cho em mấy địa chỉ. Em đã đọc bài hướng dẫn của anh Hưởng rồi. Nhưng đê viết được bài như các anh chắc còn dài lắm. Cảm ơn các anh rất nhiều !!!

Dể lắm... Nếu bạn ở Sài Gòn thì chạy ra Số 10 đường Bàu Cát, P.13, Quận Tân Bình để mua sách!
Thông tin chi tiết tại đây:
http://www.giaiphapexcel.com/forum/showthread.php?t=9294
Bảo đãm trong vòng 1 năm vừa đọc sách vừa đều đều lên GPE, bạn sẽ trở thành cao thủ chẳng mấy hồi...
ANH TUẤN

 

[HuuThanh]

Em đang học ngoài Hà Nội, cũng hay chạy đi đi lại lại các tỉnh phía bắc. Em chưa vào trong đó lần nào. Anh ở trong đó hay ngoài này. Em cũng đã đọc một chút nhưng thấy cũng khó quá, một mình ngồi cũng thấy nản, mấy bữa nay được ở nhà vào vô GPE thấy các anh nói chuyện, hướng dẫn mới thấy ham trở lại (Trước em cũng đã tự học nhưng lại nản). Rất mong được sự giúp đỡ của các anh, có gì không phải mong các anh bỏ quá.
Xin chân thành cám ơn !!!