Xem Nhiều 2/2023 #️ Cách Sử Dụng Consolidate Và Subtotal Để Thống Kê Dữ Liệu Trong Excel # Top 3 Trend | 3mienmoloctrungvang.com

Xem Nhiều 2/2023 # Cách Sử Dụng Consolidate Và Subtotal Để Thống Kê Dữ Liệu Trong Excel # Top 3 Trend

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 mới nhất trên website 3mienmoloctrungvang.com. 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.

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

Hướng Dẫn Sử Dụng Chức Năng Subtotal Thống Kê Dữ Liệu Theo Điều Kiện Trong Excel

Đôi khi ta cần thể hiện doanh số bán hàng theo từng nhân viên, hoặc số lượng thực phẩm theo vùng miền, hay nhiệt độ trung bình của từng tháng… Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn làm điều trên thông qua chức năng SUBTOTAL thống kê dữ liệu theo điều kiện.

Chức năng SUBTOTAL thống kê dữ liệu theo điều kiện

Trong ví dụ lần này, ta sẽ mô phỏng đơn giản nhất bảng thống kê nhiệt độ và độ ẩm của Hà Nội 3 tháng đầu năm 2017.

Công việc của ta bây giờ là thống kê nhiệt độ và độ ẩm trung bình từng tháng. Và ta sẽ thực hiện bằng công cụ SUBTOTAL.

Công cụ SUBTOTAL

Thêm các đối tượng thích hợp để sử dụng công cụ SUBTOTAL

Với một bảng dài ngoằng như này, hẳn bạn sẽ không muốn thêm những dòng tính trung bình vào dưới mỗi khi kết thúc tháng đâu nhỉ? Hãy đến với SUBTOTAL.

Chức năng SUBTOTAL sẽ giúp bạn tính được kết quả trung bình theo từng tháng một cách nhanh chóng.

Bước 1: Chọn toàn bộ bảng tính (trong trường hợp bạn chưa biết, chỉ cần chọn một ô bất kì trong bảng tính rồi chọn Subtotal, Excel sẽ tự tính toán và chọn tất cả những ô còn lại).

Bước 2: Chọn Subtotal. Một hộp thoại sẽ hiện ra:

Use function: Hàm được sử dụng để tính. Với hàm SUM, Excel sẽ trả về tổng, hàm AVERAGE trả về trung bình,… trả lời cho câu hỏi: Phương thức thống kê?

Add subtotal to: Mục được áp dụng hàm ở trên. Chọn các cột cần thiết và Excel sẽ tính toán giá trị ở đó. Trả lời câu hỏi: Thống kê dữ liệu gì?

Ngoài ra ta có:

Replace current subtotal: Nếu đã tồn tại trước đó một subtotal, nó sẽ thay thế bằng subtotal mới.

Page break between groups: Bạn biết page break (ngắt trang) chứ? Tùy chọn này giúp cho mỗi nhóm của at each change in được nằm trong một trang khác nhau.

Summary below data: Đưa function xuống dưới các nhóm. Nếu bỏ chọn ô này, kết quả sẽ không mất, mà đưa lên đầu.

Ok, ấn đi các bạn!

At each change in thời gian, use function AVERAGE, Add subtotal to Nhiệt độ ngày, Nhiệt độ đêm, Độ ẩm.

Mình biết điều đang xảy ra với trang tính của các bạn.

Để khắc phục, đầu tiên là Undo đi nào (Ctrl+Z).

Tại cột Tháng, nhập công thức vào như sau:

Sau đó định dạng lại về General nếu kết quả đang ở dạng Date.

Sử dụng autofill áp dụng kết quả cho các ô còn lại. Trong trường hợp bạn chưa biết autofill, hãy xem bài viết này.

Kết quả:

– Ứng dụng SUBTOTAL kết hợp COUNTIF thiết lập số thứ tự thành phần.

– Tổng quan về công cụ SUBTOTAL

Bạn Đã Biết Cách Sử Dụng Consolidate Trong Excel Chưa?

Cùng với Pivot Table, hàm SUBTOTAL thì CONSOLIDATE là một trong những hàm thống kê dữ liệu rất hiệu quả trong excel. Consolidate giúp chúng ta tính toán cực hiệu quả khi được tích hợp cho phép thực hiện nhanh các phép tính tổng (sum), trung bình (evergage), max, min … trong bảng tính.

1. Chức năng của CONSOLIDATE trong excel

Trong thực tế, chúng ta thường phải xử lý số liệu từ một file excel lớn từ phần mềm bán hàng hay dữ liệu từ phần mềm nhân sự chuyển qua chẳng hạn. Công việc sẽ nhanh chóng, hiệu quả hơn nếu chúng ta kết hợp các công cụ tích hợp sẳn của excel, Consolidate là một điển hình.

Consolidate có thể tạo ra một bảng báo cáo tổng hợp của tất cả các trường dữ liệu được lựa chọn chỉ vài thao tác đơn giản. Trong bài viết này, chúng ta sẽ tính tổng bằng công cụ Consolidate trong excel 2010 (excel 2007, 2013, 2016 cũng tương tự)

2. Ví dụ cụ thể về cách sử dụng CONSOLIDATE trong excel

Tính tổng số lượng và tổng  tiền với từng mã/ mặt hàng tương ứng.

Trong bảng tính trên có nhiều mặt hàng trùng, lặp và nhiệm vụ của chúng ta là tính gộp chúng lại của từng mã hàng, tức tạo một báo cáo tổng hợp.

Ảnh 1: Bạn đã biết cách sử dụng CONSOLIDATE trong excel chưa?

Đầu tiên, đặt con trỏ chuột tại vị trí mà nơi muốn tạo báo cáo (trong sheet hoặc sheet bất kỳ), nhấp chuột vào menuData chọn Consolidate.

Ảnh 2: Bạn đã biết cách sử dụng CONSOLIDATE trong excel chưa?

Hộp thoại Consolidate xuất hiện và nhiệm vụ của chúng ta là điền các giá trị thích hợp vào các hộp tương ứng:

Trong hộp Function chúng ta chọn Sum để tính tổng (có nhiều chức năng khác như tính trung bình (evergage), max, min … các bạn có thể tìm hiểu thêm).

Tại hộp Reference gõ vào vùng tham chiếu, chọn phạm vi để Consolidate tính toán bằng cách nhấp chuột vào ôReference rồi rê chuột để chọn khối dữ liệu.

Lưu ý dòng tiêu đề (Top row) là dòng đầu tiên của khối chọn (Top row của chúng ta là dòng thứ 2: Tên hàng hóa, số lương, thành tiền).

Cột tiêu đề (Left column) là cột đầu tiên của khối chọn (Left column chúng ta đang chọn là cột C: Tên hàng hóa); còn nếu cột tiêu đề là “Mã hàng: thì phải điều chỉnh vùng chọn sang cột B trong ô Reference.

Kế tiếp, chúng ta nhấn Add đưa địa chỉ tham chiếu vào ô All reference để có hiệu lực.

Ảnh 3: Bạn đã biết cách sử dụng CONSOLIDATE trong excel chưa?

Đừng quên chọn dấu kiểm tại hai mục Top row và Left column trong vùng Use labels để báo cáo hiện tiêu đề dòng, cột (nếu không chọn dấu kiểm 2 mục này dòng, cột tiêu sẽ bị ẩn mà chỉ có số liệu).

Cuối cùng, nhấn OK để xem báo cáo:

Ảnh 4: Bạn đã biết cách sử dụng CONSOLIDATE trong excel chưa?

Kết quả Consolidate sẽ cộng gộp chính xác số lượng và thành tiền của các mặt hàng, tất nhiên cột đơn giá cộng gộp trong trường hợp này đối với chúng ta không có ý nghĩa gì cả.

Nếu các bạn chọn thêm dấu kiểm tại mục Creat links to soure data trước khi nhấn OK thì báo cáo sẽ có thêm phần chi tiết (tương tự tính năng của Subtotal) khi nhấp chuột vào dấu + bên trái.

Cách Sử Dụng Advanced Filter Trong Excel, Lọc Dữ Liệu Bảng Tính Theo Đ

Mặc dù là Advanced Filter nhưng cách sử dụng của nó lại hoàn toàn cơ bản, dễ dàng sử dụng ngay cả với người mới. Để sử dụng Advanced Filter

Khi thao tác trên bảng tính Excel, thao tác lọc dữ liệu theo điều kiện rất quan trọng và không thể thiếu. Ở bài viết trước chúng ta đã được tìm hiểu cách Lọc dữ liệu trùng nhau trong bảng tính thì ở bài viết này các bạn sẽ có thêm kinh nghiệm sử dụng chức năng Advanced Filter trong Excel.

Cách sử dụng chức năng Advanced Filter trong Excel

Trước tiên để dùng chức năng Advance Filter thì bảng dữ liệu của bạn phải theo các yêu cầu sau:

1. Chừa ít nhất 3 dòng trống trên cùng bảng dữ liệu.2. Chỉ sử dụng một dòng duy nhất để làm tiêu đề bảng dữ liệu.3. Không merge bất cứ ô nào của bảng dữ liệu.

Khi sử dụng Advance Filter thì bạn sẽ phải dùng tới một bảng phụ làm điều kiện so sánh để lọc dữ liệu từ bảng dữ liệu chính của các bạn. Và yêu cầu của bảng phụ này phải dùng tiêu đề của cột dữ liệu bạn cần lọc từ bảng chính làm tiêu chí lọc, cho nên bạn phải copy tiêu đề cột dữ liệu bạn cần lọc trong bảng chính và dán vào dòng tiêu đề điều kiện cần lọc trong bảng phụ. Và Advance Filter chỉ có thể lọc 1 lần 2 điều kiện cho 1 cột dữ liệu. Các bạn có thể theo dõi ví dụ dưới để nắm bắt trực quan hơn về chức năng Advance Filter.

Ví dụ: Ta có bảng dữ liệu như sau:

Đầu tiên bạn cần phải tạo một bảng phụ dùng để lọc dữ liệu.

Bước 2: Dán vào một ô trống bất kỳ. Và đặt điều để lọc cho nó. Ở ví dụ này mình đặt điều kiện là lọc các mặt hàng có giá lớn hơn 50$.

Như vậy là bạn đã có được một bảng phụ để lọc dữ liệu từ bảng chính.

Bước 1: Đặt trỏ chuột vào ô bảng dữ liệu chính. Vào Data → Advance

Bước 2: Trong ô List Range là vùng dữ liệu chính bạn muốn lọc. Khi bạn đặt trỏ chuột và chọn chức năng Advance Filter thì Excel sẽ tự động chọn bảng dữ liệu cho bạn (vùng được bao quanh bởi các đường nhấp nháy).

Bước 3: Trong ô Criteria range, bạn chọn bảng phụ vừa tạo dùng để lọc dữ liệu.

Bước 4: Bạn để ý trong bảng Advance Filter sẽ có 2 mục chọn là:

– Filter the list, in-place: lọc dữ liệu và cho ra kết quả ở chính bảng lọc. Các dữ liệu không phù hợp sẽ bị mất đi.– Copy to another location: lọc dữ liệu và copy chúng trong một khu vực khác.

Kết quả sau khi lọc dữ liệu:

Bài viết trên giới thiệu với các bạn về cách sử dụng Advanced Filter cũng như chức năng chính của nó. Tính năng này giúp bạn lọc dữ liệu giống chức năng Auto Filter nhưng không bị hạn chế về các điều kiện lọc như Auto Filter.

Chú ý Ngoài ra các bạn cũng cần biết thêm về các ký tự đặc biệt khi lọc: 1. Ký tự *: ví dụ ở cột Name. Bạn muốn lọc tất cả các ký tự có bắt đầu bằng chữ M thì bạn sẽ nhập điều kiện lọc là M*. Tức là lọc chuỗi ký tự bắt đầu bằng chữ M, ký tự * đại diện cho các ký tự còn lại trong chuỗi. 2. Ký tự ?: tương tự như ký tự * nhưng nó chỉ đại diện cho một ký tự duy nhất. Ví dụ bạn lọc M? tức là lọc chuỗi gồm 2 ký tự bắt đầu bằng M. 3. Ký tự ~: để loại bỏ 2 ký tự đặc biệt trên khi lọc dữ liệu. Ví dụ trong bảng dữ liệu của bạn có chuỗi là M*D, khi bạn lọc nếu nhập điều kiện như vậy thì nó sẽ lọc tất cả các chuỗi có bắt đầu bằng M và kết thúc bằng D.

Bạn đang xem bài viết Cách Sử Dụng Consolidate Và Subtotal Để Thống Kê Dữ Liệu Trong Excel trên website 3mienmoloctrungvang.com. 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. Chúc bạn một ngày tốt lành!