Các câu hỏi về hàm SUBTOTAL (1 người xem)

ThangAcc · 11/1/08

Bình thường ở Excel nếu ấn vào nút AutoSum thì nó sẽ tự động chèn hàm sum() vào như dưới hình:

Bây giờ tôi không muốn nó chèn hàm sum() nữa mà chèn hàm subtotal() thì làm như thế nào? Mong các bác giúp.

BNTT · 11/1/08

Được mà PhamNhuKhang ơi, quét chọn khối cell đó (E11:E28), rồi nhấn nút chuột phải, chọn Create List... Sau đó đứng trong khối cell này, nhấn nút Toggle Total Row trên Toolbar, sẽ tự động có một cái SUBTOTAL ở E29.

kongcom · 11/1/08

Bạn tham khảo tại đây xem http://www.giaiphapexcel.com/forum/showthread.php?t=7368 http://<a href=

LikeIt · 12/1/08

BNTT đã viết:
Được mà PhamNhuKhang ơi, quét chọn khối cell đó (E11:E28), rồi nhấn nút chuột phải, chọn Create List... Sau đó đứng trong khối cell này, nhấn nút Toggle Total Row trên Toolbar, sẽ tự động có một cái SUBTOTAL ở E29.

Gửi Bác BNTT,
Làm ơn cho em hỏi, sau khi làm xong như anh hướng dẫn, mà ko muốn có subtotal nữa thì làm thế nào để thoát trở lại data bình thường?tks

kongcom · 12/1/08

Nguyễn Hương Thơm đã viết:
Gửi Bác BNTT,
Làm ơn cho em hỏi, sau khi làm xong như anh hướng dẫn, mà ko muốn có subtotal nữa thì làm thế nào để thoát trở lại data bình thường?tks

Trả lời Thơm thay cho BNTT nha
Bạn nhấn chuột phải vào vùng List-----> chọn List ---->Convert to Range----> Yes ->Ok

Để bật tắt chức năng SubTotal cũng làm tương tự nhưng là menu Total Row

ThangAcc · 12/1/08

Hàm Subtotal hay hơn sum nhiều ở chỗ:
- Khi sử dụng auto filter để lọc theo 1 điều kiện nào đó, tổng số những ô lựa chọn sẽ tự động cập nhật ở ô subtotal, nếu mà dùng sum thì nó mất luôn chứ không còn.
- Subtotal thì có thể cộng tổng toàn bộ cả cột luôn mà không lo bị trùng kết quả thành 2 lần. Còn sum thì ta phải cộng từng ô 1 rất thủ công.
Các bác cần vd không tôi post lên?

Cách làm thì đúng là có nhiều, tôi cũng biết làm. Nhưng mà có các nào tiện lợi thì tốt quá các bác à.

Thuyan.acc · 11/3/08

Ứng dụng Subtotal - Xử lý số số học - Định đạng kiểu dữ liệu

Em xin gởi file này lên với 3 nội dung cần xử lý :
1.- Em Có thể ứng dụng subtotal vào trường hợp này không ? (file đính kèm - sheet subtotal) Ở cột B30 - Nếu cài công thức theo cột D em thấy quá dỡ vì nếu có nhiều cấp thì ngồi làm thủ công dễ sai sót.

Em có xem qua bài viết của Thầy Phong nhưng chưa biết ứng dụng trong trường hợp số liệu có nhiều cấp như file ví dụ của em.

Để hiểu rõ hơn về SubTotal bạn có thể xem tại đây:
http://www.giaiphapexcel.com/forum/s...9&postcount=31
http://www.giaiphapexcel.com/forum/a...0&d=1189580574

2.- Em có copy file của ngân hàng trên webstie để biết tình hình số dư tài khoản của mình. (Ngân hàng thường cập nhật cho khách hàng các số dư tức thời) nhưng kiểu dữ liệu theo dạng file em đính kèm - sheet Xử lý số. (Xin vui lòng xem sheet này).
Khi em lập báo cáo nhanh cho chief em không muốn gõ lại số liệu trên nên em dùng copy - paste. Sau đó cộng lại không được (Dòng Total D10 - Màu xanh).
Cách làm thủ công của em là dùng copy cột A sang D rồi dùng edit lại từng số 1 - Điều này cũng dễ sai sót và quá dỡ.

3.- Cho biết Kiểu dữ định dạng giữa General và text khác nhau như thế nào. Trong trường hợp sheet - định dạng đính kèm thì nên dùng dạng text hay General. Vì khi khai báo tài khoản em định dạng General em thấy sao lúng túng chỗ này quá. Vả dọc dữ liệu nó xem không có ngay hàng thẳng lối gì cả ?

Kính nhờ Quý Thầy Cô cùng anh chị hướng dẫn em thêm.

Kính,

anhtuan1066 · 11/3/08

Câu 1: Bạn dùng SUBTOTAL là đúng rồi... nó sẽ bỏ quả mấy SUBTOTAL đã tính ở trên... tuy nhiên B30 lý ra phải là:

Mã:

B30 =SUBTOTAL(9,B3:B29)

Tính từ B3 chứ ko phải B2... còn nữa, ko hiểu tại sao B26 lại có công thức =B27 ??? Xóa cell B26 đi là công thức đúng ngay
Câu 2: Sửa công thức lại thành:

Mã:

=SUMPRODUCT((D1:D9)*1)

thì bạn chẳng sợ gì dử liệu là TEXT hay NUMBER... cái gì nó cũng cộng dc tuốt
Câu 3: Ko thể nói định dạng nào tốt hơn dc... Nếu là Text thì đương nhiên ko thể tính toán dc (Ví dụ trong trường hợp bạn SUM tại Sheet Xử lý số)
Vậy thì tùy theo nhu cầu mà định dạng hợp lý... Lấy ví dụ SỐ THẺ, SỐ ĐT, SỐ TÀI KHOẢN vân vân thì nên định dạng Text... vì khi ấy nếu bạn định dạng Number hoặc General, bạn sẽ ko thể gõ số 0 vào đầu chuổi dc (nó sẽ loại ngay số 0 này)...
-----------------------
Góp ý thêm: Bạn ko nên đặt tên sheet là tiếng Việt có dấu, hơn nữa cũng ko nên đặt tên sheet có khoảng trắng... Nói chung càng ngắn gọn càng tốt... Nếu bạn vẫn làm như hiện giờ thì sau này bạn sẽ thấy có nhiều tác hại ko lường trước dc...
ANH TUẤN

Thuyan.acc · 11/3/08

anhtuan1066 đã viết:
Câu 1: Bạn dùng SUBTOTAL là đúng rồi... nó sẽ bỏ quả mấy SUBTOTAL đã tính ở trên... tuy nhiên B30 lý ra phải là:

Mã:

B30 =SUBTOTAL(9,B3:B29)

Tính từ B3 chứ ko phải B2... còn nữa, ko hiểu tại sao B26 lại có công thức =B27 ??? Xóa cell B26 đi là công thức đúng ngay

Thưa Thầy ý của em là trong mỗi nhóm A thì có A1,A2,A3, ...An và B cũng tương tự
Nhưng khi em muốn làm grand total cho cả bảng tính (của các nhóm chính lại như A + B,...) thì ở đây em có thể ứng dụng nhanh subtotal vào trường hợp này không ? Vì hiện nay theo cột D30 em vẫn làm thủ công

Mã:

=+D2+D26

Trường hợp này chỉ có 2 nhóm chính là A và B thôi nhưng số liệu của em hiện nay nó nhiều nếu sử dụng như cách của em dễ bị sót lắm.
Các vùng em tô màu xanh lá cây.

anhtuan1066 đã viết:
Câu 2: Sửa công thức lại thành:

Mã:

=SUMPRODUCT((D1:D9)*1)

thì bạn chẳng sợ gì dử liệu là TEXT hay NUMBER... cái gì nó cũng cộng dc tuốt

Quả thật là tuyệt.

anhtuan1066 đã viết:
Góp ý thêm: Bạn ko nên đặt tên sheet là tiếng Việt có dấu, hơn nữa cũng ko nên đặt tên sheet có khoảng trắng... Nói chung càng ngắn gọn càng tốt... Nếu bạn vẫn làm như hiện giờ thì sau này bạn sẽ thấy có nhiều tác hại ko lường trước dc...
ANH TUẤN

Em kính cám ơn việc Thầy nhắc nhớ và lưu ý cách đặt tên sheet, em sẽ rút kinh nghiệm, do em muốn đặt tên vào cho dễ nhớ đó mà.

Kính,

Thùy An

ptm0412 · 11/3/08

1. Sheet Subtotal hình như là 1 báo cáo tài chính hoặc Bảng CDKT rút gọn? Ký hiệu A, B không số là tổng các ký hiệu A#, B# tương ứng? A#, B# là tổng các ký hiệu A###, B### tương ứng? vậy thì em dùng sum cho A# và B#, A=A1+A2, B=B1+B2. Grand total=A+B. Cho nó đơn giản vì thực ra báo cáo tài chình đâu có dài quá 2 trang in. và đâu thể xóa B theo ý anhtuan1066.
2. Câu 2: Sumproduct chỉ đúng nếu dấu thập phân trong option - international là cùng dấu thập phân với dữ liệu. Tốt nhất là em làm như sau:
- Đánh dấu chọn khối số liệu.
- Dùng Find and replace thay thế "," bằng trống, thay thế ".00" bằng trống. Lúc đó dữ liệu sẽ là số hoặc định dạng lại là số.
- trường hợp tài khoản ngoại tệ và có số lẻ (cents), thay "." = "," hoặc dùng 1 hàm tự tạo đổi text dạng số thập phân thành số.
3. Câu 3; Làm theo anh Anhtuan là đúng.

anhtuan1066 · 11/3/08

Câu 1:
Trong mỗi nhóm A, B có các nhóm con A1, A2... B1, B2 và các nhóm con này dùng SUBTOTAL sẽ ko có vấn đề... Khi bạn GrandTotal nó sẽ tự động loại bỏ mấy SUBTOTAL phía trên, và công thức vẫn đúng... Tuy nhiên trong cột B này bạn lại có công thức tại cell B2 và B26, chính nó đã làm sai kết quả tại B30 đấy!
Nếu bạn vẫn nhất định ko xóa cell B2 và B26 thì vẫn có cách.. tại B30 thiết lập công thức:

Mã:

=SUBTOTAL(9,$B$2:$B$29)-SUMPRODUCT((LEN($A$2:$A$29)=1)*($B$2:$B$29))

ANH TUẤN

Thuyan.acc · 12/3/08

ptm0412 đã viết:
1. Sheet Subtotal hình như là 1 báo cáo tài chính hoặc Bảng CDKT rút gọn? Ký hiệu A, B không số là tổng các ký hiệu A#, B# tương ứng? A#, B# là tổng các ký hiệu A###, B### tương ứng? vậy thì em dùng sum cho A# và B#, A=A1+A2, B=B1+B2. Grand total=A+B. Cho nó đơn giản vì thực ra báo cáo tài chình đâu có dài quá 2 trang in. và đâu thể xóa B theo ý anhtuan1066.

Thầy kính,

Chắc Thầy chưa gặp phải 1 bảng cân đối số phát sinh có hàng chục trang trở lên thì phải. Nói như Thầy chỉ có 1 vài trang thì em đặt vấn đề này lên làm chi. Tìm việc dễ chứ ai tội vạ mang cái khó vào thân. --=--

Trăm nghe không bằng mắt thấy thưa Thầy. Em đã xem qua bảng cân đối số phát sinh chi tiết thật tuyệt - Nhưng đây là dùng phần mềm kế toán.
(Bảng cân đối số phát sinh có thể in cấp 1 riêng, hoặc cấp 2, hoặc cấp chi tiết. Phần mềm Cho phép tùy chọn- các báo cáo họ đưa ra gần kế toán quản trị chứ không phải kế toán đại cương nữa)

Giờ xin phép trở lại vấn đề Thầy trò mình cùng thảo luận nhe.

Số liệu này em xin được phép đưa lên gồm có số hiệu tài khoản, nợ, có còn các cột D,E,F là em tự tạo ra thêm.

Yêu cầu bài toán : Giữa các dòng cấp 1 họ tạo ra 1 subtotal của các cấp 2 và các cấp 2 là cộng của tất cả các chi tiết còn nằm trong cấp 2.

Như bài 1 em có nêu, những trường hợp này thì mình ứng dụng hàm subtotal như thế nào để khỏi bị trùng số liệu.

Em thì suy nghĩ như thế này nhé, ở bài 1 em dùng hàm

Mã:

 =SUM(E2:E29)/3

Suy nghĩ đơn giản :Sở dĩ chia cho 3 là do có 3 cấp -> Cái này quá tệ rồi +-+-+-+

. Chắc có nước Thầy Tuấn hoặc Thầy cho em ăn đòn quá.

Kính.
----
P/S :
- Rất rõ và không phủ nhận bảng cân đối kế toán chỉ có từ 3 trang trở lại
- Riêng về Bảng cân đối số phát sinh chi tiết thì phải xem lại - tùy theo góc độ quản lý mà có thể lên hàng chục trang là chuyện bình thường
- Sáng nay dậy sớm gởi bài lền công trình gõ đã gởi đi xem lại không có gì cả. Buồn quá phải lọ mọ gõ lại.

ptm0412 · 12/3/08

Tại dữ liệu em đưa mẫu ít quá nên anh đoán là ít. Nếu nhiều thì cũng có cách:
1- Dùng sub total 2 cấp trong menu data, làm 2 lần.
- Nếu subtotal nằm dưới; hoàn toàn đúng
- Nếu subtottal nằm trên: sẽ có 1 số dòng nằm lệch chỗ 1 dòng
2. Dùng công thức, và phải chèn từng dòng (không ít dòng). Nhưng công thức có thể copy theo màu. Copy từ màu nào paste vào màu đó.
3. Dùng Access, kết quả như sau:

File kèm theo là gồm 1 file xls làm theo 2 cách trện, 1 file xls gốc có đổi tên cột, 1 file Access có chọn lựa in theo cấp và có thể đưa ra Excel dù cái vị trí cột không đúng lắm.

Mr Okebab · 12/3/08

Thuyan.acc đã viết:
Em xin gởi file này lên với 3 nội dung cần xử lý :
1.- Em Có thể ứng dụng subtotal vào trường hợp này không ? (file đính kèm - sheet subtotal) Ở cột B30 - Nếu cài công thức theo cột D em thấy quá dỡ vì nếu có nhiều cấp thì ngồi làm thủ công dễ sai sót.

Em có xem qua bài viết của Thầy Phong nhưng chưa biết ứng dụng trong trường hợp số liệu có nhiều cấp như file ví dụ của em.
2.- Em có copy file của ngân hàng trên webstie để biết tình hình số dư tài khoản của mình. (Ngân hàng thường cập nhật cho khách hàng các số dư tức thời) nhưng kiểu dữ liệu theo dạng file em đính kèm - sheet Xử lý số. (Xin vui lòng xem sheet này).
Khi em lập báo cáo nhanh cho chief em không muốn gõ lại số liệu trên nên em dùng copy - paste. Sau đó cộng lại không được (Dòng Total D10 - Màu xanh).
Cách làm thủ công của em là dùng copy cột A sang D rồi dùng edit lại từng số 1 - Điều này cũng dễ sai sót và quá dỡ.

3.- Cho biết Kiểu dữ định dạng giữa General và text khác nhau như thế nào. Trong trường hợp sheet - định dạng đính kèm thì nên dùng dạng text hay General. Vì khi khai báo tài khoản em định dạng General em thấy sao lúng túng chỗ này quá. Vả dọc dữ liệu nó xem không có ngay hàng thẳng lối gì cả ?

Kính nhờ Quý Thầy Cô cùng anh chị hướng dẫn em thêm.

Kính,

Câu 1 :
Có rất nhiều cách để làm điều này :
-Dùng Subtotal : Cũng tiện nhưng hơi thủ công
-Dùng Sumif : Như trong File
-Dùng Sumproduct : Như trong File
- . . . . . . . . . . . . . .

Câu 2 : Nhấn mạnh : Mình dùng Office 2003 SP3

Rõ ràng đây là Text nên không thể dùng bất cứ công thức nào để cộng được, kể cả sumproduct ), vì vậy em phải chuyển đổi về number thì mới có thể tiến hành được, mà việc này nhờ vào hàm substitute thì phải 2 lần (như fie đính kèm), hoặc dùng 1 hàm VBA để chuyển đổi, hoặc là dùng biện pháp Find – Replace thôi.

Câu 3 :

Mình có rất nhièu kinh nghiệm trong khi làm việc với các tài khoản Kế toán, vì vậy mình khuyên bạn nên cho tài khoản kế toán là text (thực sự là text chứ không phải nhờ định dạng), VD như việc thêm 1 ký tự không phải là số trước hoặc sau tài khoản, việc này mình nghĩ đối với bạn thì quá dễ.

Thân!

anhtuan1066 · 12/3/08

Đúng là tôi có sơ xuất về dấu phẩy và chấm... nhưng cũng ko cần cột phụ đâu Bắp ơi... Sao ko là:

Mã:

=SUMPRODUCT((SUBSTITUTE(SUBSTITUTE(D1:D9,",",""),".",""))*1)/100

ANH TUẤN

thaotv · 16/5/08

Dùng hàm subtotal trong hàm Sumproduct

Các bác ơi! Trong Sumproduct có tính tổng theo kiểu subtotal (chỉ tính những dòng không bị ẩn) được không vậy?

cadafi · 17/5/08

thaotv đã viết:
Các bác ơi! Trong Sumproduct có tính tổng theo kiểu subtotal (chỉ tính những dòng không bị ẩn) được không vậy?

Bạn xem file đính kèm, mình sử dụng lồng hai hàm sumproduct và subtotal để giải quyết bài toán của bạn.

Trojan · 6/6/08

Hàm sum và hàm subtotal(9, range) khác nhau vấn đề gì?

Tôi đã sử dụng hàm sum và subtotal với đối số là 9 nhưng chưa tìm thấy sự khác nhau giữa hai hàm này. Ai biết xin trả lời dùm. Xin cảm ơn.

cadafi · 6/6/08

Trojan đã viết:
Tôi đã sử dụng hàm sum và subtotal với đối số là 9 nhưng chưa tìm thấy sự khác nhau giữa hai hàm này. Ai biết xin trả lời dùm. Xin cảm ơn.

Đơn giản là bạn thử autofilter lấy ra một số dòng thì biết ngay mà!

Mr Okebab · 6/6/08

Trojan đã viết:
Tôi đã sử dụng hàm sum và subtotal với đối số là 9 nhưng chưa tìm thấy sự khác nhau giữa hai hàm này. Ai biết xin trả lời dùm. Xin cảm ơn.

Hàm Sum : Sẽ cộng tất cả các giá trị trong vùng
Subtotal(đối số 9) : Sẽ cộng tất cả các giá trị trong vùng trừ các ô có chứa công thức Subtotal

Thân!

BNTT · 6/6/08

Bạn tự làm một ví dụ đi, sẽ thấy nó khác nhau thôi:

- Mở một bảng tính mới, chừa một hàng trống để dễ thấy.

- Chọn dãy A2:B100, nhập công thức: = RANDBETWEEN(-100, 100) rồi nhấn Ctrl-Enter (Điền ngẫu nhiên vào dãy đó các con số trong khoảng -100 đến 100)

- Vẫn chọn A2:B100, Copy, Paste Special với tham số Value (tức là hủy bỏ cái hàm RANDBETWEEN đi, chỉ lấy mấy con số thôi)

- Chọn dãy C2:C100, nhập công thức: = B2 (sau khi nhập công thức này cũng nhấn Ctrl-Enter nha bạn)

- Ở B101, bạn dùng công thức: = SUM(B2:B100)

- Ở C101, bạn dùng công thức: = SUBTOTAL(9, C2:C100)

- Bạn sẽ thấy con số tổng ở hai ô B101 và C101 này giống nhau, phải không.

- Chọn A1:C100, chọn Data/Autofilter

- Bi giờ bạn đứng ở cột A, áp dụng Autofiter cho cột A với một giá trị gì đó (ví dụ chỉ lọc ra những con số lớn hơn bao nhiêu đó chẳng hạn, hoặc là lọc bỏ mấy con số > 0, v.v...)

- Xem lại B101 và C101. Kết quả khác nhau rồi.

Bạn tự suy nghĩ xem nó khác nhau là do đâu ?

Mr Okebab đã viết:
Hàm Sum : Sẽ cộng tất cả các giá trị trong vùng
Subtotal(đối số 9) : Sẽ cộng tất cả các giá trị trong vùng trừ các ô có chứa công thức Subtotal

Thân!

Hic, OkBap ơi, xem lại dùm cái định nghĩa SUBTOTAL !

nhatthai · 6/6/08

Cólẽ Là Thế Này, Bạn Xem File

Trần Thị Ánh Tuyết · 6/6/08

Chào bạn, khi cài công thức Sum thì dùng auto filter để lọc giá trị nào đó trong bảng tính phải tính lại công thức Sum, với Subtotal thi không cần
"Cái gì không thể mua được bằng tiền thì có thể mua được bằng nhiều tiền hơn" là sao nhỉ ? nếu vậy thì cái gì cũng mua được bằng tiền hết, phải không ?

Mr Okebab · 6/6/08

BNTT đã viết:
Hic, OkBap ơi, xem lại dùm cái định nghĩa SUBTOTAL !

Không hẳn đâu, chỉ thiếu trường hợp Filter thôi.
Subtotal làm ra chính yếu là để nhằm không cộng các tổng con vào (Cho khác SUM),

Bác làm luôn cái sự phân biệt giữa các hàng bị ẩn do người dùng và do Auto Filter nhé. Vì hai cái này sẽ khác nhau đấy (Với các đối số >100)

Và thêm cái là hàm Sum thì cộng tất cả, còn hàm Subtotal thì sẽ loại trừ các giá trị của các ô có công thức Subtotal, đây mới chính là cái chính yếu của Subtotal (Như công cụ Subtotal cũng lợi dụng điểm này)

Cảm ơn bác nhiều!

"Cái gì không thể mua được bằng tiền thì có thể mua được bằng nhiều tiền hơn" là sao nhỉ ? nếu vậy thì cái gì cũng mua được bằng tiền hết, phải không ?

Phủ định để khẳng định nhằm nhấn mạnh ý nghĩa khẳng định : Không gì không thể mua được bằng tiền!! (Đây là dịch nghĩa chứ không liên quan đến quan điểm)

Thân!

huycuongxd · 23/6/08

Cách dùng hàm Subtotal

E vừa mới gia nhập diễn đàn, kiến thức về Excel còn ít ỏi quá! Mong các tiền bối chỉ dạy cho e cách sử dụng Hàm Subtotal với nhé, e đọc sách nhưng khó hiểu và chưa làm được. Cảm ơn các bác nhiều lắm!

luonchinguoc · 23/6/08

Hướng dẫn sử dụng Subtotal

huycuongxd đã viết:
E vừa mới gia nhập diễn đàn, kiến thức về Excel còn ít ỏi quá! Mong các tiền bối chỉ dạy cho e cách sử dụng Hàm Subtotal với nhé, e đọc sách nhưng khó hiểu và chưa làm được. Cảm ơn các bác nhiều lắm!

SUBTOTAL (function_num, ref1, ref1,...)

Trong đó, function_num là những số từ 1 đến 11 (includes hidden value - bao gồm cả những giá trị ẩn), và từ 101 đến 111 (ignores hidden value - bỏ qua những giá trị ẩn)

Giá trị ẩn ở đây thường là số liệu trong những hàng (row) đã được dấu đi, ví dụ như lọc bằng Autofilter.

Cách dùng hàm bằng các số (1 tương đương với 101, 2 tương đương 102, v.v...)
1 : AVERAGE
2 : COUNT
3 : COUNTA
4 : MAX
5 : MIN
6 : PRODUCT
7 : STDEV
8 : STDEVP
9 : SUM
10 : VAR
11 : VARP

=SUBTOTAL(102,$D$4

9) nghĩa là đếm trong dãy từ $D$4 đến D9 có bao nhiêu cell, bỏ qua (không đếm) nhừng cell đã bị Filter dấu đi.
__________________

Link gốc:
http://www.giaiphapexcel.com/forum/showpost.php?p=27149&postcount=31
http://www.giaiphapexcel.com/forum/attachment.php?attachmentid=3770&d=1189580574

ndu96081631 · 23/6/08

luonchinguoc đã viết:
SUBTOTAL (function_num, ref1, ref1,...)

Trong đó, function_num là những số từ 1 đến 11 (includes hidden value - bao gồm cả những giá trị ẩn), và từ 101 đến 111 (ignores hidden value - bỏ qua những giá trị ẩn)

Cái này chỉ đúng với Office từ 2003 trở lên thôi nhé! Với Office2002 về trước thì chỉ có tham số từ 1 đến 11 (không có 101 đến 111)

minhduc13 · 17/7/08

Cách này tui cũng từng xem qua và từng xài, nhưng vấn đề là không hiểu cách làm việc của nó như thế nào, chỉ biết bị động thay những chỗ cần thay mà chả hiểu sao nó lại như thế cả --> vì không hiểu nên không sáng tạo được. Có ai giải thích xem nó làm việc như thế nào không?

tedaynui · 2/9/08

Hàm SUBTOTAL với cột

Mình dùng hàm SUBTOTAL để tính tổng các ô không ẩn. Ví dụ
SUBTOTAL(109,A1:A100) --> khi ẩn dòng thì OK

Nhưng mình cũng muốn với cách làm như vậy đối với cột thì không được. Ví dụ
SUBTOTAL(109,A1:H1) --> khi ẩn cột thì nó vẫn tính tất cả

Vậy có công thức nào áp dụng được với cột mong được các bạn giúp đỡ. Xin cám ơn !
(Không dùng VBA)

TDN

ndu96081631 · 2/9/08

Theo tôi thì SUBTOTAL có liên quan mật thiết với Filter... Và ta cũng chỉ thấy Filter theo chiều dọc chứ có cái Filter nào theo chiều ngang đâu
E rằng nếu muốn thế phải UDF

TKT · 2/9/08

tedaynui đã viết:
Mình dùng hàm SUBTOTAL để tính tổng các ô không ẩn. Ví dụ
SUBTOTAL(109,A1:A100) --> khi ẩn dòng thì OK

Nhưng mình cũng muốn với cách làm như vậy đối với cột thì không được. Ví dụ
SUBTOTAL(109,A1:H1) --> khi ẩn cột thì nó vẫn tính tất cả

Vậy có công thức nào áp dụng được với cột mong được các bạn giúp đỡ. Xin cám ơn !
(Không dùng VBA)

TDN

Trong phần trợ giúp của excel nói về hàm Subtotal như sau:
Trích:

Mã:

For the function_num constants from 1 to 11, the SUBTOTAL function includes  the values of [COLOR=Red][B]rows[/B][/COLOR] hidden by the Hide command under the Row submenu of the Format menu). Use these  constants when you want to subtotal hidden and nonhidden numbers in a list. For  the function_Num constants from 101 to 111, the SUBTOTAL function ignores values  of [B][COLOR=Red]rows hidden[/COLOR][/B] by the Hide command under the Row submenu of the Format menu). Use these  constants when you want to subtotal only nonhidden numbers in a list.

Không nói gì về column cả.
Cũng giống như chức năng Pivot Table và Auto Filter.
Em không bao giờ nhập liệu theo hàng vì bảng thân người lập trình Excel đã thiết kế số dòng luôn luôn nhiều hơn số cột vả lại còn liên quan đến các trường (Field) bên Access nữa , cũng như một số phần mềm khác.

Po_Pikachu · 2/9/08

Tuy là không bằng hàm SubTotal hoặc Sum về việc đa năng và quét mảng nhưng xài cho cột và dòng ẩn thì được.

PHP:

Function Tong(cel As Range)
For j = 1 To InStr(1, cel.Address(0, 0), ":")
    k = Mid(cel.Address(0, 0), j, 1)
    If IsNumeric(k) = True Then
    rn1 = rn1 & k
    End If
Next
For m = InStr(1, cel.Address(0, 0), ":") To Len(cel.Address(0, 0))
    k = Mid(cel.Address(0, 0), m, 1)
    If IsNumeric(k) = True Then
    rn2 = rn2 & k
    End If
Next
If rn1 = rn2 Then
For i = 1 To cel.Columns.Count
    If ActiveSheet.Columns(i).Hidden <> True Then
    Tong = Tong + cel.Cells(i)
    End If
Next
Else
For i = 1 To cel.Rows.Count
    If ActiveSheet.Rows(i).Hidden <> True Then
    Tong = Tong + cel.Cells(i)
    End If
Next
End If
End Function

ndu96081631 · 2/9/08

Code dài quá đồng chí ơi...
Tôi thì làm vầy:

PHP:

Function SumVisible(Rng As Range)
   Dim VCel As Range
   Dim Temp As Double
   For Each VCel In Rng
       If Not VCel.Rows.Hidden And Not VCel.Columns.Hidden Then Temp = Temp + VCel
   Next
   SumVisible = Temp
End Function

Tôi còn nghĩ sẽ dùng SpecialCells(xlCellTypeVisible) nhưng đáng tiếc không thực hiện được (Khi dùng Sub thì cho kết quả chính xác, nhưng gán vào Function thì trật lất---> Không hiểu tại sao)

Ah quên... phải thêm Application.Volatile vào đầu code nữa (nếu không nó sẽ không cập nhật được giá trị mới khi ta ẩn dòng hoặc cột

Po_Pikachu · 2/9/08

Tuy hơi yếu, nhưng cũng cố gắng "lết" đến đây!

PHP:

Function Tong(cel As Range, Optional loai As String = "") Application.Volatile  If UCase$(loai)  "ROW" Then For i = 1 To cel.Count     If ActiveSheet.Columns(cel.Cells(i).Column).Hidden  True Then     Tong = Tong + cel.Cells(i)     End If Next Else For i = 1 To cel.Count     If ActiveSheet.Rows(cel.Cells(i).Row).Hidden  True Then     Tong = Tong + cel.Cells(i)     End If Next End If End Function

Mong các bác đừng chê cười! Thân.

ndu96081631 · 2/9/08

Tôi làm thí nghiệm với 2 đoạn code sau:

PHP:

Function SumVisible(Rng As Range)
  Dim VRng As Range
  Application.Volatile
  Set VRng = Rng.SpecialCells(xlCellTypeVisible)
  SumVisible = Application.WorksheetFunction.Sum(VRng)
End Function

PHP:

Sub Test()
  Dim VRng As Range
  Set VRng = Selection.SpecialCells(xlCellTypeVisible)
  MsgBox Application.WorksheetFunction.Sum(VRng)
End Sub

2 code này giống nhau hoàn toàn, ấy thế mà Sub thì cho kết quả chính xác còn Function thì sai
Nhờ các cao thủ giãi thích giúp
File đính kèm đây

tedaynui · 2/9/08

Cám ơn các bác đã quan tâm ! Như mình đã nêu : Nếu dùng công thức thì thế nào ?

@ ndu :
Em nghĩ Function nó không chịu .SpecialCells(xlCellTypeVisible). Vì vậy, nếu dùng VBA thì chắc phải đưa vào vòng lặp duyệt từng Cell nhưng như vậy thì sẽ rất chậm. Có lẽ, dùng vòng lặp duyệt từng cột có ẩn hay không, nếu không ẩn thì Subtotal và đưa kết quả vào mảng ; Sau đó cộng chúng lại.

@ Po_Pikachu :
Hàm của bạn cho kết quả như ý (#4). Tuy nhiên, ẩn dòng thì OK nhưng khi ẩn cột thì nó không tự động cập nhật kết quả mới kể cả nhấn F9. Ngoại trừ F2 và Enter.

TDN

ndu96081631 · 2/9/08

tedaynui đã viết:
Có lẽ, dùng vòng lặp duyệt từng cột có ẩn hay không, nếu không ẩn thì Subtotal và đưa kết quả vào mảng ; Sau đó cộng các mảng đó lại.

Tôi cũng nghĩ đây là cách nhanh nhất!
Vậy tạm thời tôi dùng:

PHP:

Function SumVisible(Rng As Range) As Double
  Dim i As Integer
  Dim iRng As Range
  Dim Temp As Double
  Application.Volatile
  For i = 1 To Rng.Columns.Count
    If Not Rng(, i).Columns.Hidden Then
      Set iRng = Rng(, i).Resize(Rng.Rows.Count, 1)
      Temp = Temp + Application.WorksheetFunction.Subtotal(109, iRng)
    End If
  Next
  SumVisible = Temp
End Function

Nhờ thầy Phước xem lại giúp còn chổ nào không ổn không?

tedaynui · 2/9/08

ndu96081631 đã viết:
Tôi cũng nghĩ đây là cách nhanh nhất!

Cám ơn anh, em thấy kết quả như ý rồi nhưng kẹt chỗ : khi ẩn cột thì F9 nó mới chịu Calculate, hic hic

Thân
TDN

ndu96081631 · 2/9/08

tedaynui đã viết:
Cám ơn anh, em thấy kết quả như ý rồi nhưng kẹt chỗ : khi ẩn cột thì F9 nó mới chịu Calculate, hic hic

Thân
TDN

Điều này đương nhiên rồi, vì ẩn hiện cột đâu phải là sự kiện change... Chính vì lẽ đó nên tôi phải thêm Application.Volatile vào
Còn nữa: Tôi nghĩ code trên cần phải cải tiến thêm trong 1 vài trường hợp đặc biệt, chẳng hạn như range là 2 vùng không liên tục ---> Vậy phải viết code thế nào đây? Mong thầy gợi ý thêm (khó quá, chưa nghĩ ra được)

kinghoang · 2/9/08

To ndu: như vậy không có cách nào cho nó tự động calculate lại khi mình ẩn cột ah? Phải tự bấm F9 sao? Mình cũng đang mắc ở chỗ này mong bạn chỉ giáo.

ndu96081631 · 3/9/08

kinghoang đã viết:
To ndu: như vậy không có cách nào cho nó tự động calculate lại khi mình ẩn cột ah? Phải tự bấm F9 sao? Mình cũng đang mắc ở chỗ này mong bạn chỉ giáo.

Điều này đương nhiên! Kết quả của 1 công thức chỉ được cập nhật khi có sự thay đổi giá trị tại các cell khác
Còn những sự kiện sau đây:
- Ẩn dòng, ẩn cột
- Tô màu, kẽ khung
......
vân vân....
Đều không được xem là sự kiện change ---> Dẩn đến công thức không nhận biết có sự thay đổi! Vì lẽ đó ta phải F9 giúp nó (Nếu không có đoạn Application.Volatile thì thậm chí bấm F9 cũng không có tác dụng)
Trong các hàm hình như chỉ có SUBTOTAL là có khả năng nhận biết được việc ẩn dòng... đáng tiếc nó lại không được thiết kế với việc ẩn cột, vì thế mới có topic này đây!

salam · 3/9/08

ndu96081631 đã viết:
Còn nữa: Tôi nghĩ code trên cần phải cải tiến thêm trong 1 vài trường hợp đặc biệt, chẳng hạn như range là 2 vùng không liên tục ---> Vậy phải viết code thế nào đây? Mong thầy gợi ý thêm (khó quá, chưa nghĩ ra được)

Em có một đoạn code này Bác xem thử:

Mã:

Public Function SumVisible(ParamArray a() As Variant) As Double
 Dim rng As Variant
 Dim value As Double
 Application.Volatile
 value = 0
 For Each rng In a
    If Not TypeName(rng) = "Range" Then
        value = ""
        Exit For
    End If
    For Each c In rng.Cells
        If c.EntireColumn.Hidden = False And c.EntireRow.Hidden = False Then
            If IsNumeric(c.value) = True Then
                value = value + c.value
            End If
        End If
    Next
 Next
 SumVisible = value
End Function

ndu96081631 · 3/9/08

Chiêu này thật đặc biệt... Tôi bó tay chứ không tài nào nghĩ ra được!
Salam có thể thêm 1 vài ví dụ nhỏ đơn giản về vấn đề chọn nhiều range không liên tục trong UDF (tương tự như trên) được không?
Phải đọc thêm 1 vài ví dụ nữa may ra mới nắm bắt được vấn đề (mới mẽ quá)

Po_Pikachu · 3/9/08

Code bác đặc biệt quá, đọc vô chẳng hiểu gì trọi, bác có thể biên dịch vài dòng được không? Public Function SumVisible(ParamArray a() As Variant) As Double If Not TypeName(rng) = "Range" Then Tại sao lại viết như vậy ạ! Thanks.

ptm0412 · 3/9/08

ParamArray a() As Variant = Khai báo tham số a của hàm là mảng chưa xác định số phần tử và chưa xác định loại (mảng = nhiều phần tử, ở đây sẽ là nhiều range)
If Not TypeName(rng) = "Range" Then = Nếu phần tử rng của a trong vòng lặp "For Each Rng in a" không phải loại biến "Range" thì go out, xét phần tử rng tiếp theo.

Mới thiệt!

Supernoop · 24/9/08

có cách nào không dùng code hok mấy bác! em dùng subtotal sau khi lọc thì lúc trong bảng 1 đối tượng ra kết quả khác. cho hiển thị tất cả bảng thì lại ra kết quả khác. Bro nào giúp em cái này cái. thanks trước

teeen · 8/11/08

Hỏi về hàm SubTotal?

huhu mọi người cho em hỏi với
em hỏi về subtotal ạ
đề bài là cho 1 bảng Báo cáo hoạt động khách sạn, subtoal tổng hợp tiền theo ngày đi của khách để biết tổng số tiền thu được trong hôm đó
sau khi subtotal xong phải trình bày theo 1 bảng: chỉ bao gồm các cột Ngày, Tổng tiền, Ghi chú

vấn đề là sau khi subtotal xong, em không biết cách nào trình bày được cái bảng như trên ạ

đây nó đây ạ

View attachment Bai 5.xls

hoangminhtien · 8/11/08

sau khi bạn subtotal xong, bạn nhấp vào số 2 trong biểu tượng ở phía trên góc bên trái, nằm ngang hàng với các biểu tượng cột A,B,C...là được như như cái bảng bạn cần

firstknight · 4/8/10

Hỏi về hàm Subtotal

Tôi có công thức: SUBTOTAL(9,C3:C1500)

Do phải áp dụng cho nhiều cột, khoảng số liệu lại thay đổi nên tôi muốn sử dụng giá trị của 1 ô nào đó đưa vào công thức trên thay cho giá trị 1500 để khi thay đổi giá trị của ô đó, khoảng cộng của SUBTOTAL sẽ thay đổi theo
Vậy có ai trên diễn đàn biết cách làm hướng dẫn giúp tôi.

Cám ơn nhiều

firstknight · 5/8/10

Nhờ vào bài viết về Offset của bạn TranThanhPhong, tôi đã tìm được cách xử lý
Tôi dùng hàm:
=SUBTOTAL(9,OFFSET(A5,0,0,$A$1,1))
và A1 sẽ nhập số dòng max cần lấy tổng, ví dụ 12, thì lệnh trên tương đương với
=SUBTOTAL(9,A5:A16)

Cám ơn bạn Phong, cám ơn diễn đàn GPE !

miko_080888 · 13/5/14

Bạn ơi, bạn có thể chỉ rõ hơn bằng hình ảnh được không? Mình thì nhấn vào Autosum thì nó bị chuyển thành Subtotal, giờ muốn chuyển thành Sum thì làm thế nào ah, mình thử cách của bạn nhưng không được

queluatb · 26/6/14

khi e sử dụng hàm subtotal, dùng chức năng lọc dữ liệu thì mất dữ liệu ở dòng tổng cộng, xin mọi người hướng dẫn e lọc để có dữ liệu ở dòng tổng cộng với

Liliane · 28/6/14

Nhân đây các anh chị cho e hỏi một chút.
Có cách nào (hay phím tắt nào hay đại loại thế) để cộng tổng các dòng bên dưới (nghĩa là công thức tính tổng ở bên trên, dữ liệu tính tổng ở bên dưới) không ạ?

queluatb · 2/7/14

queluatb đã viết:
khi e sử dụng hàm subtotal, dùng chức năng lọc dữ liệu thì mất dữ liệu ở dòng tổng cộng, xin mọi người hướng dẫn e lọc để có dữ liệu ở dòng tổng cộng với

ai làm ơn giúp e với

VMH0307 · 2/7/14

queluatb đã viết:
ai làm ơn giúp e với

Bạn tìm hiểu sự khác nhau về tham số FUNCTION_NUM là 9 và 109 trong hàm subtotal nhé.

queluatb · 10/7/14

tttmuahmuah đã viết:
Bạn tìm hiểu sự khác nhau về tham số FUNCTION_NUM là 9 và 109 trong hàm subtotal nhé.

e đã tìm hiểu và thay tham số rồi vẫn không lọc được dữ liệu, làm ơn chỉ giúp cho e ví dụ

VMH0307 · 10/7/14

queluatb đã viết:
e đã tìm hiểu và thay tham số rồi vẫn không lọc được dữ liệu, làm ơn chỉ giúp cho e ví dụ

Trong help subtotal có câu:

For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide Rows command under the Hide & Unhide submenu of the Format command in the Cells group on the Home tab

do vậy tham số này có hiệu lực với lệnh hiden và unhide (hoặc với alt+shift+->) còn với filter thì nó chỉ tính toán trên phần đã lọc được.
Do vậy với trường hợp của bạn nên dùng 1 trong 2 cái, nếu dùng subtotal thì dùng alt+shift+-> và không filter trường "ghi nhớ" nữa, còn nếu dùng filter thì thay subtotal bằng sum cho đơn thuần.

ndu96081631 · 10/7/14

tttmuahmuah đã viết:
Trong help subtotal có câu:
do vậy tham số này có hiệu lực với lệnh hiden và unhide (hoặc với alt+shift+->) còn với filter thì nó chỉ tính toán trên phần đã lọc được.
Do vậy với trường hợp của bạn nên dùng 1 trong 2 cái, nếu dùng subtotal thì dùng alt+shift+-> và không filter trường "ghi nhớ" nữa, còn nếu dùng filter thì thay subtotal bằng sum cho đơn thuần.

Hình như người ta hỏi vầy:

queluatb đã viết:
khi e sử dụng hàm subtotal, dùng chức năng lọc dữ liệu thì mất dữ liệu ở dòng tổng cộng, xin mọi người hướng dẫn e lọc để có dữ liệu ở dòng tổng cộng với

Và hình như là.. hổng liên quan đến câu trả lời của bạn thì phải

queluatb · 10/7/14

ndu96081631 đã viết:
Hình như người ta hỏi vầy:

Và hình như là.. hổng liên quan đến câu trả lời của bạn thì phải

làm ơn chỉ giúp cho e cách lọc

ndu96081631 · 10/7/14

queluatb đã viết:
làm ơn chỉ giúp cho e cách lọc

Tôi không biết chỉ bạn cái gì nữa... vì tôi test trên máy tôi (dùng Office 2010), mọi thứ đều bình thường

queluatb · 14/7/14

ndu96081631 đã viết:
Tôi không biết chỉ bạn cái gì nữa... vì tôi test trên máy tôi (dùng Office 2010), mọi thứ đều bình thường

em up file ảnh lên mong anh xem và giúp đỡ cho e, e không hiểu thế nào nữa cả

queluatb · 14/7/14

View attachment 125404 View attachment 125405

ndu96081631 đã viết:
Tôi không biết chỉ bạn cái gì nữa... vì tôi test trên máy tôi (dùng Office 2010), mọi thứ đều bình thường

em up file ảnh lên mong anh xem và giúp đỡ cho e, e không hiểu thế nào nữa cả

Các câu hỏi về hàm SUBTOTAL (1 người xem)

Người dùng đang xem chủ đề này

Thành viên hoạt động

Bùi Nguyễn Triệu Tường

Thành viên hoạt động

Thành viên tiêu biểu

Thành viên hoạt động

Thành viên hoạt động

Thành viên thường trực

File đính kèm

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

Thành viên thường trực

Bad Excel Member

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

Thành viên thường trực

File đính kèm

Bad Excel Member

File đính kèm

Ngon Ngất Ngây

File đính kèm

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

Thành viên mới

Hành động từ trái tim

File đính kèm

Thành viên hoạt động

Hành động từ trái tim

Ngon Ngất Ngây

Bùi Nguyễn Triệu Tường

Thành viên thường trực

File đính kèm

Thành viên mới

Ngon Ngất Ngây

Thành viên thường trực

Thành viên chính thức

Huyền thoại GPE

Thành viên mới

(*_*)

Huyền thoại GPE

Thành viên tiêu biểu

Po_pikachu@ymail.com

Huyền thoại GPE

Po_pikachu@ymail.com

Huyền thoại GPE

File đính kèm

(*_*)

Huyền thoại GPE

(*_*)

Huyền thoại GPE

Thành viên mới

Huyền thoại GPE

Thành viên gắn bó

Huyền thoại GPE

Po_pikachu@ymail.com

Bad Excel Member

Thành viên mới

Thành viên mới

Thành viên gắn bó

Thành viên mới

Thành viên mới

Thành viên chính thức

Thành viên thường trực

File đính kèm

Thành viên mới

Thành viên thường trực

real women for private encounters

Thành viên thường trực

real women for private encounters

Huyền thoại GPE

Thành viên thường trực

Huyền thoại GPE

Thành viên thường trực

Thành viên thường trực

File đính kèm

Thời gian đếm ngược.

(_)

(_)

(_)