Bạn đang xem bài viết Cách Sử Dụng Consolidate Và Subtotal Để Thống Kê Dữ Liệu Trong Excel được cập nhật mới nhất trên website Nhatngukohi.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Tương tự như Pivot Table đã được giới thiệu tại Hay học hỏi, SUBTOTAL và CONSOLIDATE cũng là các hàm thống kê dữ liệu rất hiệu quả.
Subtotal dùng để tính toán trên các nhóm con trong 1 danh sách, bảng tính; còn Consolidate giúp chúng ta gộp dữ liệu từ nhiều trang tính hay nhiều file dữ liệu khác nhau.
Lưu ý: Để thực hiện theo hướng dẫn của bài viết các bạn có thể tải file mẫu ở liên kết sau: https://goo.gl/8DXtSx
1. Cách sử dụng Subtotal trong Excel 2010 – How to use Subtotal in Excel
Bước 1: Sắp xếp danh sách (hay bảng dữ liệu) theo cột cần gom nhóm để tính toán
Bảng Subtotal xuất hiện, thực hiện:
Ô At each change in: chọn Cột muốn gom nhóm
Ô Use Funtion: chọn hàm tính toán
(Lưu ý: check chọn Replace current subtotals và Summary below data)
Ví dụ cho bảng dữ liệu sau với yêu cầu thống kê Tổng Số lượng theo từng Quận
Đầu tiên, các bạn chọn bảng dữ liệu cần thống kê, trên menu chọn Data → Sort trong phần Sort by chọn Quận và nhấn OK hướng dẫn hình sau
Sau khi nhấn OK thì bảng tính đã được sắp xếp theo Quận, các bạn chọn lại bảng từ cột Quận rồi trong menu Data lệnh Subtotal ta được hình sau:
Các bạn chọn vùng At each change in (với mỗi thay đổi) là Quận; vùng Use function là: Sum (tính tổng); Add subtotal to là Doanh thu rồi nhấn OK ta được kết quả như sau (check chọn: Replace current subtotals và Summary below data):
Các bạn có thể nhấn vào các số 1,2,3 (vùng khoanh đỏ) để mở rộng hay thu gọn bảng số liệu, và để xoá bỏ subtotal bạn chọn lại lệnh subtotal và chọn Remove all.
có thể bạn muốn xem:
Cách thống kê dữ liệu báo cáo cực nhanh sử dụng Pivot Table
2. Cách sử dụng Consolidate trong Excel - How to use Consolidate in Excel
Bước 1: Copy các cột tiêu đề cần thống kê sang 1 bảng tính mới
Bước 2: Chọn 1 vùng dữ liệu có tiêu đề ở Bước 1 (bao gồm tiêu đề và 1 số dòng trống phía dưới)
Bước 3: Chọn Consolidate trong menu Data, bảng Consonlidate xuất hiện, thực hiện:
Ô Funtion: Chọn hàm tính toán
Mục Use labels in: Check chọn Top row và Left column → chọn OK
Mời các bạn xem Video hướng dẫn chi tiết cách sử dụng Consolidate và Subtotal để thống kê dữ liệu trong Excel – How to use Consolidate in Excel
Mẹo Sử Dụng Hàm Subtotal Trong Excel
Lượt Xem:2003
Mẹo sử dụng hàm Subtotal trong excel
Excel online đưa ra Lệnh Excel Subtotal cung cấp một cách nhanh chóng để tạo ra các tổng phụ cho các trường riêng lẻ trong một bảng dữ liệu.
Lưu ý rằng lệnh này không giống như hàm Subtotal của Excel , thực hiện các phép tính toán học cho một phạm vi ô hiển thị.
Bảng tính số liệu bán hàng trong ba tháng
Chúng tôi sẽ minh họa lệnh Excel Subtotal sử dụng bảng tính bên phải , trong đó liệt kê các số liệu bán hàng cho ba đội trong ba tháng đầu tiên của năm.
Các bước sau có thể được sử dụng để hiển thị tổng phụ cho mỗi tháng, tháng 1, tháng 2 và tháng 3:
Chọn bất kỳ ô nào trong bảng dữ liệu của bạn (Excel sẽ tự động phát hiện toàn bộ phạm vi dữ liệu để sử dụng trong lệnh tổng phụ);
Nhấp vào tùy chọn Subtotal nằm trong nhóm ‘ Phác thảo‘, trong tab Dữ liệu của ruy-băng Excel.
Điều này sẽ làm cho hộp thoại Subtotal mở ra (xem bên phải ). Trong hộp này:
Trong A t mỗi thay đổi trong: trường nhập, chọn Tháng ;
Trong hàm U se: trường nhập, chọn Sum ;
Trong trường A d d subtotal to: field, đảm bảo rằng tùy chọn Sales được chọn;
Các tùy chọn này yêu cầu Excel hiển thị tổng phụ mỗi lần giá trị trong cột Tháng thay đổi và tổng số phụ này sẽ hiển thị Tổng của dữ liệu trong cột Bán hàng .
Bảng tính kết quả được hiển thị bên dưới:
Nhấp vào các phác thảo, ở bên trái của bảng cho phép bạn ẩn hoặc hiển thị chi tiết của mỗi phần trong bảng dữ liệu của bạn.
Tùy chọn phụ khác
Hộp thoại cho lệnh Subtotal của Excel
Các Một t mỗi thay đổi trong: lĩnh vực của hộp thoại Subtotal cho phép bạn chọn những lĩnh vực bảng dữ liệu của bạn, bạn muốn Subtotals để áp dụng cho.
Ví dụ, trong bảng tính trên, nếu chúng ta đã chọn lĩnh vực Đội , một tổng số phụ sẽ xuất hiện mỗi khi tên đội thay đổi (mặc dù trong trường hợp này, nó sẽ hữu ích hơn để đặt hàng các bảng dữ liệu bởi đội ngũ trước khi áp dụng tổng số phụ).
Hàm U se: trường của hộp thoại Subtotal cho phép bạn chọn một phép toán được thực hiện trên các nhóm dữ liệu.
Ví dụ: trong bảng tính ở trên, thay vì tính Tổng của các số liệu bán hàng hàng tháng, chúng tôi có thể tính số liệu doanh số trung bình cho mỗi tháng.
Các A d d tổng số phụ để: lĩnh vực của Subtotal hộp thoại cho phép bạn chọn các cột của bảng mà bạn muốn các phép toán được lựa chọn để được áp dụng cho. Bạn có thể chọn nhiều trường để áp dụng thao tác tổng phụ, mặc dù tùy chọn này thực sự chỉ có ý nghĩa đối với các trường chứa số.
Các Replace c tổng số phụ urrent checkbox cho bạn tùy chọn để thêm nhiều hơn một tổng số phụ vào một bảng dữ liệu.
Ví dụ, nếu trong bảng tính trên, chúng tôi muốn thể hiện sự Sum và các Trung bình số liệu bán hàng cho từng tháng, chúng tôi có thể lần đầu tiên áp dụng Sum tổng phụ (như trong ví dụ) và sau đó chúng ta có thể chọn tùy chọn tổng phụ một lần nữa và yêu cầu Tính trung bình . Theo đây:
Nếu Replace c tổng số phụ urrent hộp được kiểm tra, yêu cầu tổng phụ thứ hai (đối với trung bình ) sẽ thay thế các Sum tổng số phụ;
Nếu Replace c tổng số phụ urrent hộp là không kiểm tra, yêu cầu tổng phụ thứ hai (đối với trung bình ) sẽ được hiển thị ngoài các Sum tổng số phụ.
Bảng tính của các số liệu bán hàng có tổng phụ và tổng số trung bình được áp dụng
Các bảng tính ở bên phải cho thấy kết quả sau khi áp dụng cả Sum và các trung bình tổng số phụ.
P nghỉ tuổi giữa các nhóm
Nếu bạn đánh dấu vào P nghỉ tuổi giữa các nhóm hộp kiểm, Excel sẽ chèn ngắt trang vào bảng tính của bạn sau mỗi nhóm dữ liệu. Điều này có thể hữu ích nếu bạn muốn in bảng tính của mình.
S ummary bên dưới dữ liệu
Hộp kiểm S ummary bên dưới dữ liệu chỉ định rõ vị trí của các bản tóm tắt nhóm.
Cách xóa Subtotals khỏi bảng tính của bạn
Để xóa tổng phụ từ bảng của bạn:
Chọn bất kỳ ô nào trong bảng dữ liệu;
Nhấp vào tùy chọn Subtotal (trong tab Dữ liệu của ruy-băng Excel):
Khi hộp thoại Subtotal mở ra, nhấp vào nút R emove All .
Các ví dụ khác về hàm Excel Subtotal được cung cấp trên trang web Hỗ trợ Microsoft Office .
Các bài viết mới
Các tin cũ hơn
Hàm Subtotal, Các Ứng Dụng Của Subtotal Trong Excel
Trong bài viết này, Excel Online sẽ giải thích chi tiết hàm SUBTOTAL và hướng dẫn các bạn cách sử dụng công thức SUBTOTAL để cộng dữ liệu trong các ô nhìn thấy.
HÀM SUBTOTAL – CÚ PHÁP VÀ CÁCH SỬ DỤNG
Hàm SUBTOTAL trong Excel có nhiệm vụ để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong trường hợp này, “tổng phụ” (subtotal) không phải tổng các con số trong 1 dãy ô xác định. Các hàm Excel khác được thiết kế chỉ để thực hiện 1 chức năng cụ thể, nhưng hàm SUBTOTAL lại rất linh hoạt – có thể tính toán hoặc làm phép logic như đếm số ô, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất…
Hàm SUBTOTAL có ở tất cả các phiên bản Excel từ 2016 đến 2007 và cả phiên bản thấp hơn.
Cú pháp hàm SUBTOTAL: SUBTOTAL(function_num, ref1, [ref2],…)
Trong đó:
Function_num: con số xác định chức năng thực hiện
Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ. Cần phải có Ref 1, từ Ref 2 đến 254 là tuỳ chọn.
Số xác định chức năng thực hiện có 2 loại sau:
1 -11 bỏ qua các ô đã được lọc ra, nhưng để lại các hàng được ẩn thủ công.
101 – 111 bỏ qua các ô ẩn – đã lọc ra và ẩn thủ công.
Bạn không cần phải nhớ hết các con số chức năng. Ngay khi bạn nhập hàm SUBTOTAL vào 1 ô hoặc trên thanh công thức, Excel sẽ đưa ra danh sách các con số cho bạn.
Ví dụ, đây là cách bạn dùng công thức SUBTOTAL 9 để cộng tổng các giá trị trong ô từ C2 đến C8:
=SUBTOTAL(9,C2:C8)
Tương tự, bạn có thể viết công thức SUBTOTAL 1 để tính trung bình, SUBTOTAL 2 để đếm ô chứa số, SUBTOTAL 3 để đếm ô không trống. Trong hình dưới, 3 chức năng khác đang được dùng.
3 LÝ DO ĐỂ DÙNG HÀM SUBTOTAL
So sánh với các hàm Excel truyền thống, SUBTOTAL cho bạn những lợi thế sau:
Tính giá trị trong các hàng được chọn
Vì hàm Excel SUBTOTAL bỏ qua các hàng đã được lọc ra, bạn có thể sử dụng nó để tính tổng dữ liệu 1 cách linh hoạt, các giá trị trong tổng phụ được tự động tính lại theo bộ lọc.
Ví dụ, nếu chúng ta lọc bảng doanh số bán hàng chỉ của vùng Miền Đông, công thức SUBTOTAL sẽ tự động điều chỉnh để bỏ qua tất cả vùng khác khi tính tổng.
Bỏ qua giá trị trong công thức SUBTOTAL lồng ghép
Nếu dãy ô trong công thức SUBTOTAL có chứa công thức SUBTOTAL khác, công thức SUBTOTAL được lồng vào sẽ bị bỏ qua. Vậy nên con số trong bảng sẽ không phải tính 2 lần.
Trong hình dưới, công thức tính trung bình chính SUBTOTAL(1, C2:C10) bỏ qua kết quả của công thức SUBTOTAL trong ô C3 và C10.
Khi bạn mới biết hàm SUBTOTAL, có thể thấy hàm này phức tạp, rắc rối vô nghĩa. Nhưng khi bạn dùng nó để giải quyết công việc, bạn sẽ nhận ra thuần thục hàm SUBTOTAL không khó. Ví dụ sau đây sẽ giúp bạn với một số mẹo nhỏ khi dùng hàm SUBTOTAL.
VÍ DỤ 1. SUBTOTAL 9 VS. SUBTOTAL 109
Như bạn đã biết, Excel SUBTOTAL chấp nhận 2 bộ số xác định chức năng 1-11 và 101-111. Cả 2 bộ số đều bỏ qua các hàng đã được lọc ra, nhưng 1-11 bao gồm các hàng được ẩn thủ công còn 101-111 thì loại trừ. Để hiểu rõ hơn về điểm khác biệt này, xem ví dụ sau:
Với tổng các hàng đã được chọn, bạn có thể dùng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới:
VÍ DỤ 2. IF + SUBTOTAL TÍNH TỔNG DỮ LIỆU
Nếu bạn đang lập báo cáo tổng kết, cần phải đưa ra các dữ liệu tổng kết khác nhau nhưng bạn không có chỗ trống cho tất cả số liệu, thì cách sau có thể là giải pháp:
Trong 1 ô, tạo 1 danh sách dạng thả xuống (drop-down) có chứa tên các hàm như Total, Max, Min…
Trong ô kế ô danh sách, thêm công thức hàm IF lồng ghép có chứa công thức SUBTOTAL tương ứng với các hàm trong danh sách.
Ví dụ, giả sử giá trị để tính tổng phụ trong ô C12:C16, và danh sách trong ô A17 chứa Total, Average, Max, Min, công thức hàm SUBTOTAL sẽ như sau:
=IF(A17=”total”, SUBTOTAL(9,C2:C16), IF(A17=”average”, SUBTOTAL(1,C2:C16), IF(A17=”min”, SUBTOTAL(5,C2:C16), IF(A17=”max”, SUBTOTAL(4,C2:C16),””))))
Và tiếp theo, tuỳ vào hàm mà người sử dụng chọn trong danh sách, công thức SUBTOTAL tương ứng sẽ tính giá trị trong các hàng được chọn.
HÀM EXCEL SUBTOTAL KHÔNG HOẠT ĐỘNG – NHỮNG LỖI THƯỜNG GẶP
Nếu hàm SUBTOTAL trả về lỗi, thì nguyên nhân có thể là một trong những lý do sau đây
#VALUE! Số xác định chức năng không nằm trong khoảng 1-11 hoặc 101-111 hay có tham chiếu (ref) là tham chiếu 3D.
#DIV/0! Xảy ra khi 1 tổng cụ thể phải chia cho 0 (ví dụ: tính trung bình cộng hoặc độ lệch chuẩn của 1 dãy ô không chứa giá trị số)
#NAME? tên hàm SUBTOTAL sai chính tả.
Mẹo nhỏ:
Nếu bạn chưa quen với hàm SUBTOTAL, bạn có thể dùng các chức năng cài đặt sẵn của hàm này và để công thức tự hoàn thiện giúp bạn hoặc theo dõi video sau đây:
Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel.
Một số hàm cơ bản thường gặp như:
SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…
Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…
Một số công cụ hay sử dụng như:
Định dạng theo điều kiện với Conditional formatting
Thiết lập điều kiện nhập dữ liệu với Data Validation
Cách đặt Name và sử dụng Name trong công thức
Lập báo cáo với Pivot Table…
Cố Định Vùng Dữ Liệu Trong Excel
Ký tự $ trong các công thức Excel có ý nghĩa như thế nào.
Đây là một trong những ký tự phổ biến nhất khi áp dụng các công thức của Excel, mục đích của nó là để cố định một ô hay một vùng dữ liệu trong excel.
Thông thường ứng dụng của việc cố định công thức áp dụng khi viết hàm.
Bạn viết hàm, bạn kéo công thức, muốn một vài chỉ tiêu cố định, một vài chỉ tiêu di chuyển. Vậy càng phải hiểu và dùng linh hoạt thao tác này.
Một số hàm thường xuyên dùng đến việc cố định dòng cột
Hàm Vlookup: Áp dụng trong trường hợp tự động tên hàng, DVT hay tên KH, Địa chỉ khi đã có mã. Chúng ta sẽ áp dụng việc cố định bảng dữ liệu tìm kiếm
Hàm Sumif/ Sumifs: Cố định vùng dữ liệu tính tổng/ Vùng điều kiện
Hàm Match: Cố định điều kiện tham chiếu và vùng tham chiếu
Hàm If: Cố định điều kiện so sánh
Rất nhiều hàm khác tại: https://excel.webkynang.vn
Hầu hết các ô, vùng không cần cố định khi công thức ấy là duy nhất. Chỉ cần công thức ấy cần phải copy, di chuyển đến các vị trí khác, có thể áp dụng thì cần cố định
Phím tắt sử dụng để cố định ô/ vùng dữ liệu trong excel:
Cho bảng dữ liệu từ A1:D10, Tính tổng số liệu cột D (cột thành tiền)
Tại D11 =sum(D1:D10
+ F4 lần 1 công thức sẽ tự động cô định cả dòng cả cột thành =sum($D$1:$D$10
+F4 lần 2 công thức sẽ tự động cố định dòng thành =sum(D$1:D$10
+ F4 lần 3 công thức sẽ tự động cố định cột thành =sum($D1:$D10
Để kết thúc công thức, ta đóng ngoặc đơn lại và nhấn enter.
Phân tích
Một ô trong Excel có địa chỉ:
Tên Cột_Số Thứ Tự Dòng : [A1]
Một Vùng dữ liệu gồm: [A1:A100]
Địa chỉ ô bên trái đầu tiền : Địa chỉ ô cuối cùng bên phải
Ví dụ:
– A1: ô A1 sẽ cho chúng ta biết nó thuộc Cột A và dòng số 1
– AZ1000 : ô AZ1000 sẽ cho chúng ta biết nó thuộc Cột AZ và dòng số 1000
– B2:D15: Đây là một vùng dữ liệu được xác định bằng địa chỉ 2 ô đầu tiên bên trái (B2) và địa chỉ ô cuối cùng bên phải (D15)
Như vậy, khi viết công thức có sử dụng địa chỉ một ô hay một vùng dữ liệu để làm tham chiếu hoặc làm điều kiện thì chúng ta cần phải cố định vùng dữ liệu hoặc ô đó để có thể copy công thức sang các ô khác mà không làm thay đổi vùng dữ liệu/ ô trong công thức.
Các kiểu cố định vùng dữ liệu
Có 3 kiểu cố định ô/ vùng dữ liệu:
– A$1: Cố định dòng mà không cố định cột. Khi copy công thức sang một cột khác thì chỉ có địa chỉ cột thay đổi, trong khi số thứ tự dòng không thay đổi.
– $A1: Cố định cột mà không cố định dòng. Khi copy công thức sang một dòng khác thì địa chỉ cột không thay đổi, trong khi số thứ tự dòng thay đổi.
– $A$1: Cố định cả dòng và cột. Khi copy công thức sang một cột khác thì cả địa chỉ cột và số thứ tự dòng đều không thay đổi.
Để cố định một vùng dữ liệu ta áp dụng nguyên tắc tương tự:
B$2:D$15
$B2:$D15
$B$2:$D$15
Cập nhật thông tin chi tiết về Cách Sử Dụng Consolidate Và Subtotal Để Thống Kê Dữ Liệu Trong Excel trên website Nhatngukohi.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!