Tạo tổng đặc biệt cho tập dữ liệu Excel nơi bạn sẽ lọc dữ liệu. Cũng trong video này: Sử dụng SUBTOTAL (109,) để tổng hợp dữ liệu đã được ẩn thủ công hàng. Sử dụng Ctrl + Ditto để sao chép công thức chính xác xuống một hàng.
Sau khi bạn đã áp dụng một bộ lọc, sẽ thật tuyệt khi xem tổng số các ô hiển thị.
Chọn ô trống bên dưới mỗi cột số của bạn. Nhấp vào nút AutoSum.
Thay vì chèn công thức SUM, Excel sẽ chèn =SUBTOTAL(9,… )công thức. Công thức bên dưới cho bạn biết tổng chỉ các ô hiển thị.
Chèn một vài hàng trống phía trên dữ liệu của bạn. Cắt các công thức từ bên dưới dữ liệu và dán vào hàng 1 với nhãn Tổng số hiển thị.
Bây giờ, khi bạn thay đổi bộ lọc, ngay cả khi dữ liệu lấp đầy nhiều hơn một màn hình dữ liệu toàn màn hình, bạn sẽ thấy tổng số ở đầu trang tính của mình.
Cảm ơn Sam Radakovitz trong nhóm Excel cho Lọc theo Lựa chọn - không phải vì đề xuất Bộ lọc theo Lựa chọn, mà vì đã chính thức hóa Bộ lọc theo Lựa chọn!
Xem video
- Tạo tổng đặc biệt cho tập dữ liệu Excel nơi bạn sẽ lọc dữ liệu. Cũng trong video này: Sử dụng SUBTOTAL (109,) để tổng hợp dữ liệu đã được ẩn thủ công hàng. Sử dụng Ctrl + Ditto để sao chép công thức chính xác xuống một hàng.
Bản ghi video
Podcast được tài trợ bởi "MrExcel XL", một cuốn sách với hơn 40 mẹo Excel, phim hoạt hình Excel, cocktail, tweet và truyện cười.
Học Excel từ podcast, tập 1976 - Tổng số ô có thể nhìn thấy!
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Tất nhiên, tất cả mọi người, công thức đầu tiên, mà hầu hết mọi người tạo ra, là AutoSum. Bạn đang ở dưới đây bên dưới một cột số, bạn nhận được nút Tự động cộng và nó sẽ sử dụng hàm sum để tính tổng các số bên trên nó, cho đến khi nó gặp công thức hoặc văn bản. Đúng? Hoặc Alt + = cũng làm điều tương tự. Hoặc, thậm chí tốt hơn, chọn nhiều ô và sau đó nhấn Tự động tính tổng, và nó sẽ đặt tất cả các công thức đó mà bạn không cần phải xác nhận các công thức.
Nhưng đây là một mẹo hay, được rồi, hãy bật Tự động lọc. Vì vậy, chúng ta sẽ sử dụng thủ thuật Lọc theo Lựa chọn, từ podcast của ngày hôm qua. Dữ liệu được lọc. Tôi sẽ chuyển đến hàng trống đầu tiên bên dưới dữ liệu, sau đó nhấn nút Tự động cộng gộp. Nhưng điều gì đó rất khác đang xảy ra, bạn thấy đấy. Thay vì hàm SUM, chúng ta nhận được = SUBTOTAL (9! Và đây là gì, đây là Tổng số ô hiển thị. Được rồi, rất tuyệt vời. Nó hoạt động.
Bây giờ là vấn đề, tôi đã thực hiện một buổi hội thảo Power Excel trực tiếp gần mười năm trước, ở Fort Wayne, IN, cho Viện Kiểm toán nội bộ. Và ai đó đã chỉ cho tôi thủ thuật này, và tôi thực sự thích thủ thuật này. Tôi chưa bao giờ nhìn thấy mẹo này trước đây, nó rất hay, tôi đã nói điều này thật tuyệt, tôi sẽ thêm nó vào hội thảo. Và năm sau, tôi quay lại Fort Wayne, và người quản lý của anh chàng đó xuất hiện, đúng vậy, anh ta đã không đến vào năm đầu tiên, vì anh ta nghĩ rằng anh ta biết mọi thứ về Excel. Nhưng anh ta quay lại để yêu cầu thủ thuật này và anh ta nói: "Nghe này, tôi nghe nói rằng nhà phân tích tài chính của tôi đã nói với bạn thủ thuật này, nhưng anh ta đã không nói cho bạn biết phần tốt nhất, hãy đi lên phía trên dữ liệu của bạn và chèn hai hàng lên đó. Và sau đó khi bạn có tổng số ô có thể nhìn thấy ở dưới cùng, hãy cắt, CTRL + X và dán, CTRL + V, được rồi, điều đó thực hiện được,nếu bạn chuyển sang một khách hàng khác, "Được rồi, vì vậy tôi sẽ chọn thêm một vài khách hàng ở đây." Và bây giờ tôi có nhiều khách hàng hơn những gì sẽ hiển thị trên màn hình! Và tôi không phải cuộn xuống dưới cùng để xem tổng, tổng luôn ở trên cùng! "
Được rồi, thủ thuật này, sử dụng dữ liệu bộ lọc AutoSum bên dưới, hoạt động khi bạn ẩn các hàng bằng bộ lọc. Điều gì sẽ xảy ra nếu bạn đang ẩn các hàng theo cách thủ công? Vì vậy, ở đây, tôi đã chọn một vài hàng, đã đi đến Định dạng, Ẩn & Bỏ ẩn, Ẩn hàng! Vì vậy, bây giờ chúng ta có các hàng bị ẩn. Và tôi xuống dưới cùng, và tôi nhấn Alt + = hoặc AutoSum, và nó không cung cấp cho tôi hàm SUBTOTAL, nó cung cấp cho tôi hàm SUM.
Được rồi, tại thời điểm này, tôi sẽ làm một điều đặc biệt, tôi sẽ thực hiện CTRL + '(ditto, dấu nháy đơn). Nó sẽ đưa cùng một công thức xuống mà không thay đổi tham chiếu ô. Vì vậy, nó là E2: E8 đã thay đổi, hoặc không thay đổi, thông thường, nếu bạn sao chép và dán vào một thay đổi thành E3: E9. Tôi sẽ thay đổi điều này thành SUBTOTAL (9 và bạn sẽ thấy rằng nó không hoạt động, nó vẫn cung cấp cho chúng ta tất cả các ô. 1000 + 400 + 800 không phải là 4500, nó bao gồm các hàng ẩn. Nhưng đây là những gì bạn phải làm, nhập CTRL + 'và thay đổi số 9 đó thành 109, và bạn sẽ nhận được: Tổng số thực sự có thể nhìn thấy dựa trên các hàng được ẩn theo cách thủ công!
Được rồi, bạn hiểu rồi, vì vậy một vài thủ thuật khác nhau, AutoSum, Alt + = chỉ cung cấp cho chúng ta hàm SUM. Nếu bạn đang ở dưới dữ liệu và bạn nhấn Tự động cộng, bạn sẽ nhận được SUBTOTAL (9, hoạt động tốt cho các bộ lọc hoặc bạn chỉ có thể nhập SUBTOTAL theo cách thủ công (109, để nhận tổng số hiển thị, khi bạn đã lọc theo cách thủ công.
Được rồi, đây là bản tóm tắt các khái niệm trong video ngày hôm nay: Nhân tiện, đây thậm chí không phải là mẹo số 3 trong sách, đây là Mẹo số 2b đúng, vì vậy giữa mẹo 2 và mẹo 3. Cuốn sách bao gồm tất cả những điều này mặt hàng. Cảm ơn tất cả những người đã gợi ý điều này, những người ở Fort Wayne Indiana Chapter of The IIA, là nơi tôi nghe thấy nó lần đầu tiên. Đó là một cuốn sách tuyệt vời, bạn có thể mua nó dưới dạng sách in hoặc sách điện tử, hãy nhấp vào chữ "i" ở góc trên bên phải. Bạn sẽ được đưa đến thêm thông tin về cuốn sách.
Được rồi, tôi muốn cảm ơn bạn đã ghé qua, chúng ta sẽ gặp lại bạn vào lần sau với một netcast khác từ!
