Cập nhật nội dung chi tiết về Cách Sử Dụng Consolidate Và Subtotal Để Thống Kê Dữ Liệu Trong Excel mới nhất trên website Comforttinhdauthom.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác 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
Cách Lọc Dữ Liệu Trong Excel
Cách lọc dữ liệu trong excel hay cách sử dụng autofiler – đây là tính năng tuyệt vời của excel. Sử dụng Autofiler trong excel 2007 2010 tuyệt vời hơn excel 2003 tương đối nhiều.
Một số điểm nổi bật của cách lọc dữ liệu trong excel 2007 2010 (autofilter)
Giao diện đẹp hơn
Tính năng lọc được nâng cấp
Hiển thị nhiều tính năng hơn excel 2003
Tên tiếng anh của chức năng lọc dữ liệu trong excel là: Autofilter
Trong autofilter có 2 phần chính:
Autofilter mặc định của excel
Advanced filter: chức năng lọc nâng cao của excel sẽ giúp các bạn lọc được theo nhiều điều kiện khác nhau, tùy ý của bạn. Thậm chí có thể di chuyển kết quả lọc được sang một vùng hoàn toàn mới.
1. Chức năng autofilter mặc định của excel (lọc dữ liệu mặc định trong excel)
1.1. Ứng dụng của chức năng lọc dữ liệu trong excel.
Theo yêu cầu quản trị của quản lý cửa hàng, giám đốc công ty, khi các bạn làm kế toán bán hàng, nhân viên bán hàng hoặc nhân viên thống kê: Các bạn sẽ phải tổng hợp tình hình bán hàng của cửa hàng theo ngày, theo tuần theo tháng hoặc theo năm. Các tiêu chí dùng để lập báo cáo bán hàng là khác nhau.
Và đặc biệt, vào một thời điểm bất kỳ nào đó trong ngày, cửa hàng trưởng yêu cầu bạn:
“Em thống kê cho anh xem, tổng doanh số của quầy Rau quả tính đến thời điểm này là bao nhiêu?”
Để làm được điều đó, bạn hãy sử dụng tính năng lọc dữ liệu trong excel (autofilter). Đây chính là cứu cánh là giải pháp cho bạn. Bạn sẽ không cần phải lo lắng gì về việc làm thế nào để lọc ra dữ liệu nữa.
– Nhìn nhanh tổng doanh số của Ngành hàng D
– Liệt kê top 10 quầy hàng có doanh số bán hàng tốt nhất
– Liệt kê những quầy hàng có doanh số trên 400.000 KVND
1.2. Bật hoặc tắt tính năng lọc dữ liệu trong excel 2007 2010
Trước khi thành thạo cách lọc dữ liệu trong excel, bạn cần phải biết làm thế nào để kích hoạt chức năng autofilter trong excel 2007 2010.
Việc xuất hiện một hình vuông nền trắng bên trong đó là một hình tam giác màu đen xuất hiện ở góc phía dưới bên phải của mỗi ô được chọn.
– Để tắt tính năng lọc dữ liệu (autofilter) trong excel đi, ta chỉ cần vào Data và chọn filter là được.
Yêu cầu 1: Cách lọc dữ liệu trong excel 2007 2010 để tìm tổng doanh số của ngành hàng D
Bước 1:
Nhấn vào hình vuông bên cạnh chữ “Ngành hàng”
Bỏ tick “select all”
Tiếp tục chọn “D”
Nhấn “OK”
Bạn tiếp tục dùng chuột bôi đen doanh số của tất cả các quầy thuộc Ngành hàng D. Khi đó, tổng doanh số của ngành hàng D sẽ hiện lên trên thanh status bar như hình sau:
Yêu cầu 2: Cách lọc dữ liệu trong excel để tìm ra top 10 quầy hàng có doanh số tốt nhất.
Với yêu cầu liệt kê TOP 10 quầy hàng có doanh số tốt nhất, thì dường như còn dễ hơn cả yêu cầu số 1.
B2: Chọn “Number filter”
Trong yêu cầu, sếp bạn muốn liệt kê 10 quầy có doanh số tốt nhất (top 10). Do đó, bạn không cần phải chỉnh sửa gì trong bảng này mà nhấn “OK” luôn.
Đây có lẽ là tùy chọn lọc dữ liệu phổ biến nhất trong excel. Ta thực hiện như sau:
Bước 1: Chọn hình vuông bên cạnh “Doanh số (KVND) “
B2: Chọn Number filters
B3: Chọn “Greater than”
Và nhấn “OK”
Hướng Dẫn Chi Tiết Cách Sử Dụng Hàm Subtotal Trong Excel
Cú pháp và cách sử dụng hàm SUBTOTAL
Microsoft định nghĩa hàm SUBTOTAL trong Excel là hàm trả về tổng phụ trong danh sách hoặc cơ sở dữ liệu. Trong trường hợp này “tổng phụ” không chỉ là tổng số trong một phạm vi ô xác định.
Không giống như các hàm Excel khác được thiết kế để chỉ thực hiện một việc cụ thể, hàm SUBTOTAL linh hoạt hơn khi có thể thực hiện các phép toán số học và logic khác nhau như đếm ô, tính trung bình, tìm giá trị tối thiểu hoặc tối đa ….
Hàm SUBTOTAL hỗ trợ tất cả các phiên bản Excel và cú pháp trong Excel như sau:
=SUBTOTAL(function_num, ref1, [ref2],…)
Trong đó:
Function_num – Một số chỉ định hàm nào sẽ sử dụng cho tổng phụ.
Ref1, Ref2 … – Một hoặc nhiều ô hoặc phạm vi thành tổng phụ. Đối số Ref đầu tiên là bắt buộc, các đối số khác (tối đa là 254) là tùy chọn.
Đối số Function_num có thể thuộc về một trong các số sau:
1 – 11 bỏ qua các ô được lọc, nhưng bao gồm các hàng ẩn thủ công.
101 – 111 bỏ qua tất cả các ô ẩn – được lọc và ẩn thủ công.
Trong thực tế, không cần phải ghi nhớ tất cả các số chức năng ở trên. Ngay khi bạn bắt đầu nhập công thức SUBTOTAL trong một ô hoặc trong thanh công thức, Microsoft Excel sẽ hiển thị danh sách các hàm số có sẵn cho bạn.
Ví dụ: Đây là cách bạn có thể tạo công thức SUBTOTAL 9 để tổng hợp các giá trị trong các ô từ C2 đến C8:
Để thêm số hàm vào công thức, bấm đúp vào nó, sau đó nhập dấu phẩy, chỉ định một phạm vi, nhập dấu ngoặc đơn đóng và nhấn Enter. Công thức đầy đủ sẽ như sau:
Theo cách tương tự, bạn có thể viết công thức SUBTOTAL 1 để tính trung bình, SUBTOTAL 2 để đếm các ô có số, SUBTOTAL 3 để đếm các khoảng trống ….
Ghi chú. Khi bạn sử dụng công thức SUBTOTAL với các hàm tóm tắt như SUM hoặc AVERAGE, nó chỉ tính toán các ô có số bỏ qua khoảng trắng và các ô chứa giá trị không phải là số.
3 lý do để sử dụng hàm SUBTOTAL trong Excel
So với các hàm Excel truyền thống khác, SUBTOTAL có những lợi thế quan trọng sau.
1. Tính giá trị trong các hàng được lọc
Vì hàm SUBTOTAL của Excel bỏ qua các giá trị trong các hàng được lọc, bạn có thể sử dụng để tạo một bản tóm tắt dữ liệu động trong đó các giá trị Subtotal được tính lại tự động theo bộ lọc.
Ví dụ: Nếu lọc bảng để chỉ hiển thị doanh số cho khu vực phía Đông (Easst), công thức SUBTOTAL phụ sẽ tự động điều chỉnh để tất cả các khu vực khác được xóa khỏi tổng số:
Ghi chú. Vì cả hai bộ hàm số (1-11 và 101-111) đều bỏ qua các ô được lọc, bạn có thể sử dụng công thức SUBTOTAL 9 hoặc SUBTOTAL 109 trong trường hợp này đều được.
2. Chỉ tính các ô có thể nhìn thấy
Các công thức SUBTOTAL với Function_num 101 đến 111 bỏ qua tất cả các ô ẩn – được lọc và ẩn thủ công.
3. Bỏ qua các giá trị trong các công thức Subtotal lồng nhau
Nếu phạm vi được cung cấp cho công thức Subtotal trong Excel của bạn chứa bất kỳ công thức Subtotal nào khác thì các hàm Subtotal được lồng vào sẽ bị bỏ qua, do đó các số tương tự sẽ không được tính hai lần.
Sử dụng hàm SUBTOTAL trong Excel – Ví dụ về công thức
Ví dụ 1: SUBTOTAL 9 với SUBTOTAL 109
Như bạn đã biết, SUBTOTAL chấp nhận 2 bộ hàm số: 1-11 và 101-111. Cả hai đều bỏ qua các hàng được lọc, nhưng các số 1-11 bao gồm các hàng được ẩn thủ công, trong khi 101-111 lại loại trừ chúng. Để hiểu rõ hơn về sự khác biệt, hãy xem xét ví dụ sau.
Bây giờ nếu muốn tổng giá trị trong các hàng hiển thị thì hàm SUBTOTAL 109 là lựa chọn duy nhất:
Ví dụ: Để đếm các ô được lọc không trống bạn có thể sử dụng công thức SUBTOTAL 3 hoặc SUBTOTAL 103.
Nhưng chỉ hàm SUBTOTAL 103 mới có thể đếm chính xác các khoảng trống có thể nhìn thấy nếu có bất kỳ hàng nào được ẩn nào trong phạm vi tính toán:
Ghi chú. Hàm SUBTOTAL của Excel với Function_num từ 101-111 sẽ bỏ qua các giá trị trong các hàng ẩn, nhưng không ở các cột ẩn .
Ví dụ: Nếu bạn sử dụng công thức như SUBTOTAL(109,A1:E1) để tính tổng các số trong phạm vi ngang, việc ẩn một cột sẽ không ảnh hưởng đến SUBTOTAL.
Ví dụ 2. Sử dụng hàm IF + SUBTOTAL để tự động tóm tắt dữ liệu
Nếu bạn đang tạo một báo cáo tóm tắt hoặc bảng điều khiển phải hiển thị các bản tóm tắt dữ liệu khác nhau nhưng không có không gian cho tất cả mọi thứ, cách tiếp cận sau đây có thể là một giải pháp:
Bước 1: Trong một ô, tạo danh sách thả xuống có chứa các tên hàm như Total, Max, Min …
Bước 2: Trong một ô bên cạnh danh sách thả xuống, hãy nhập công thức IF lồng nhau với các hàm SUBTOTAL được nhúng tương ứng với các tên hàm trong danh sách thả xuống.
Ví dụ: Giả sử các giá trị cho SUBTOTAL nằm trong các ô C2:C16 và danh sách thả xuống trong ô A17 chứa các mục Total, Average, Max và Min. Vậy thì công thức SUBTOTAL “động” 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),””))))
Bây giờ, tùy thuộc vào chức năng nào mà người dùng chọn từ danh sách thả xuống, hàm SUBTOTAL tương ứng sẽ tính toán các giá trị trong các hàng được lọc:
Các lỗi SUBTOTAL phổ biến trong Excel
Nếu công thức SUBTOTAL của bạn trả về lỗi, có thể do một trong những lý do sau:
VALUE! – Đối số Function_num không phải là số nguyên trong khoảng 1 – 11 hoặc 101 – 111; hoặc bất kỳ đối số Ref nào chứa tham chiếu 3-D .
DIV/0! – Nếu một hàm tóm tắt được chỉ định phải thực hiện phép chia cho 0.
NAME? – Tên của hàm SUBTOTAL bị sai chính tả.
Sắp Xếp, Lọc Và Ẩn Dữ Liệu Trong Excel
Ở đây chúng tôi có một tập dữ liệu nhỏ. Không có gì theo bất kỳ loại thứ tự cụ thể nào, nhưng chúng tôi thực sự muốn nó được sắp xếp theo thứ tự bảng chữ cái. Đây là nơi loại có một số cách để sắp xếp một tập hợp dữ liệu trong Excel. Hãy xem xét một phương pháp.
Đầu tiên, hãy chọn các cột mà bạn muốn sắp xếp. Một cách nhanh chóng để thực hiện việc này là nhấp vào cột đầu tiên, sau đó giữ phím Shift và nhấp vào cột cuối cùng. Trong ảnh chụp màn hình ví dụ bên dưới, chúng tôi nhấp vào “A”, giữ phím Shift và nhấp vào “C” để chọn các cột từ A đến C cho tất cả các hàng.
Tiếp theo, trong tab Trang chủ, nhấp vào nút Sắp xếp & Bộ lọc rồi nhấp vào Bộ lọc.
Bây giờ bạn sẽ thấy một nút thả xuống / nút mũi tên ở đầu mỗi cột trên bảng tính. Phương pháp này hoạt động tốt hơn so với việc nhấp vào các nút Sắp xếp trong Sắp xếp và Bộ lọc vì nó thiết lập trang tính cho nhiều chức năng cùng một lúc mà chúng tôi sẽ đề cập ngay sau đây.
Bây giờ hãy nhấp vào mũi tên trên cột bạn muốn sắp xếp và nhấp vào Sắp xếp. Trong ví dụ này, chúng tôi muốn sắp xếp theo thứ tự bảng chữ cái theo tên nên chúng tôi sẽ nhấp vào mũi tên theo Tên, sau đó Sắp xếp từ A đến Z.
Hãy xem lại tập dữ liệu mẫu của chúng tôi.
Chức năng sắp xếp không chỉ sắp xếp các tên theo thứ tự bảng chữ cái, mà còn cả dữ liệu trong các hàng được liên kết với chúng! Điều này cũng có thể được thực hiện cho các cột Tuổi hoặc Màu ưa thích.
Bây giờ chúng ta đã thành thạo việc sắp xếp, hãy thử lọc dữ liệu này một chút. Chúng tôi thích màu xanh lá cây hơn, vì vậy đó là những người duy nhất chúng tôi muốn thấy trong dữ liệu của mình. Để làm điều này, chúng tôi sẽ sử dụng lọc chức năng.
Bấm vào mũi tên trong cột Màu ưa thích và bỏ chọn hộp dữ liệu bạn không muốn xem. Ngoài ra, bạn có thể nhấp vào hộp “Chọn tất cả” để bỏ chọn mọi thứ và sau đó chọn những thứ bạn muốn. Sau khi thực hiện lựa chọn của bạn, hãy nhấp vào “Ok”.
Hãy có một cái nhìn khác về tập dữ liệu đó.
Bây giờ chúng tôi đang thấy dữ liệu mà chúng tôi thực sự quan tâm. Bây giờ chúng tôi muốn chụp ảnh màn hình của điều này, nhưng Jeff thực sự nhạy cảm về tuổi của mình. Chúng tôi có thể làm cho nó biến mất khỏi màn hình mà không xóa dữ liệu trong cột đó bằng cách sử dụng ẩn chức năng.
Nhấp chuột phải vào cột bạn muốn ẩn và sau đó nhấp vào “Ẩn”. Bạn có thể ẩn nhiều cột theo cách này nếu bạn đã chọn tất cả chúng.
Xem lại lần cuối tập dữ liệu.
Nếu bạn muốn xem lại thông tin bị ẩn, chỉ cần nhấp chuột phải vào khoảng trống của cột đó và nhấp vào “Hiện”.
Bây giờ bạn đã sẵn sàng để cắt giảm các bảng tính đó và bắt đầu đi vào trọng tâm của dữ liệu của mình.
Hãy nhớ rằng, nếu bạn cần hỗ trợ hoặc có câu hỏi về việc báo cáo, bạn luôn có thể liên hệ với Nhóm HMIS tại Thương mại.
Chia sẻ bài viết này
Bạn đang đọc nội dung bài viết Cách Sử Dụng Consolidate Và Subtotal Để Thống Kê Dữ Liệu Trong Excel trên website Comforttinhdauthom.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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. Chúc bạn một ngày tốt lành!