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

[ThangAcc]

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]

Đượ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]

Bạn tham khảo tại đây xem http://www.giaiphapexcel.com/forum/showthread.php?t=7368http://<a href=

 

[LikeIt]

Đượ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]

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

Trả lời Thơm thay cho BNTT nha
Bạn nhấn chuột phải vào vùng List-----> chọn List ---->Convert to Range----> Yes ->Ok

Để bật tắt chức năng SubTotal cũng làm tương tự nhưng là menu Total Row

 

[ThangAcc]

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]

Ứ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]

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à:

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:

=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]

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à:

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

=+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.

Câu 2: Sửa công thức lại thành:

 =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.

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]

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]

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:

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

ANH TUẤN

 

[Thuyan.acc]

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

 =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]

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]

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]

Đúng là tôi có sơ xuất về dấu phẩy và chấm... nhưng cũng ko cần cột phụ đâu Bắp ơi... Sao ko là:

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

ANH TUẤN

 

[thaotv]

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]

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]

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]

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]

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!