Mẹo này là một phần mở rộng của mẹo Excel tuần trước. Bạn có thể sử dụng lệnh Đi đến Đặc biệt để chọn các ô trống từ một phạm vi. Đây là kỹ thuật Excel tuyệt vời để làm sạch dữ liệu trong bảng tổng hợp.
Bảng tổng hợp là công cụ tuyệt vời. Nhưng điều gì sẽ xảy ra nếu bảng tổng hợp của bạn chỉ là một bước trung gian trong các thao tác dữ liệu của bạn và bạn muốn sử dụng thêm dữ liệu tóm tắt trong bảng tổng hợp? Tôi đã từng thường xuyên nhận được 5000 hàng dữ liệu từ hệ thống máy tính lớn cũ. Dữ liệu này có các cột cho sản phẩm, tháng, khu vực, trạng thái và số lượng. Tôi cần tóm tắt dữ liệu trong một bảng tổng hợp với các tháng đi qua các hàng trên cùng, sản phẩm, khu vực và trạng thái. Bảng tổng hợp giúp việc này trở nên nhanh chóng.
Từ đó, tôi cần lấy dữ liệu từ bảng tổng hợp và nhập vào Access. Như bạn có thể thấy, bảng tổng hợp mặc định không phù hợp cho việc này. Excel sử dụng kiểu phác thảo để mỗi vùng chỉ xuất hiện một lần, với các ô trống theo sau nó cho đến Tổng vùng phụ. Các ô trong phần dữ liệu không có dữ liệu sẽ trống thay vì số. Tôi cũng không muốn các tổng phụ trong dữ liệu.
Bắt đầu từ Excel 97, có các tùy chọn cho bảng tổng hợp để kiểm soát một số hành vi này.
Trong hộp thoại bố cục Bảng tổng hợp, bấm đúp vào tên trường cho vùng. Trong phần Tổng phụ, nhấp vào nút radio cho "Không có". Điều này sẽ loại bỏ các tổng phụ không mong muốn.
Trong Excel 2000, nút tùy chọn sẽ đưa bạn đến hộp thoại nơi bạn có thể chỉ định "Đối với ô trống, hãy hiển thị:" và điền vào số 0 để bạn không có bảng đầy ô trống.
Để thao tác thêm với bảng tổng hợp, bạn sẽ phải làm cho nó không còn là một bảng tổng hợp nữa. Nếu bạn thử và thay đổi các ô trong bảng tổng hợp, Excel sẽ cho bạn biết rằng bạn không thể thay đổi một phần của bảng tổng hợp. Để thay đổi từ bảng tổng hợp thành chỉ các giá trị, hãy làm theo các bước sau:
- Di chuyển con trỏ ô ra bên ngoài bảng tổng hợp.
- Nếu bảng tổng hợp bắt đầu trên hàng 1, thì hãy chèn một hàng 1 mới.
- Bắt đầu đánh dấu trong hàng trống 1 mới và kéo xuống để chọn toàn bộ bảng tổng hợp.
- Thực hiện Ctrl + C (hoặc Chỉnh sửa - Sao chép) để sao chép phạm vi này.
- Với cùng một vùng được chọn, hãy thực hiện Chỉnh sửa - Dán Đặc biệt - Giá trị - OK để thay đổi bảng tổng hợp thành chỉ giá trị tĩnh.
Nhưng đây là mẹo thực sự cho tuần này. Jennifer viết
Tôi đã liên tục gặp sự cố khi sử dụng tùy chọn bảng tổng hợp, để tóm tắt hàng loạt dữ liệu, nhưng nó không điền vào các hàng bên dưới mỗi thay đổi trong danh mục hàng. Bạn có biết làm thế nào để làm cho bảng tổng hợp điền vào bên dưới mỗi thay đổi trong danh mục ?? Tôi đã phải kéo và sao chép mọi mã xuống để có thể thực hiện nhiều bảng tổng hợp hoặc sắp xếp hơn. Tôi đã thử thay đổi các tùy chọn trong bảng tổng hợp, nhưng không có kết quả.
Câu trả lời là không dễ học. Nó không trực quan. Nhưng, nếu bạn ghét việc kéo những ô đó xuống, bạn sẽ thích dành thời gian để tìm hiểu quá trình này! Hãy làm theo - nó có vẻ dài và được vẽ ra, nhưng nó thực sự hiệu quả. Khi bạn nhận được nó, bạn có thể làm điều này trong 20 giây.
Thực tế có 2 hoặc 3 mẹo mới ở đây. Giả sử bạn có 2 cột ở bên trái ở định dạng phác thảo cần được điền vào. Đánh dấu từ ô A3 xuống đến ô B999 (hoặc bất kỳ hàng dữ liệu cuối cùng của bạn là gì).
Thủ thuật # 1. Chọn tất cả các ô trống trong phạm vi đó.
Nhấn Ctrl + G, alt = "" + S + K rồi nhấn Enter. Huh?
Ctrl + G hiển thị hộp thoại GoTo
Alt + S sẽ chọn nút "Đặc biệt" từ hộp thoại
Hộp thoại Goto-Special là một điều tuyệt vời mà ít người biết đến. Nhấn "k" để chọn "ô trống". Nhấn enter hoặc nhấp vào OK và bây giờ bạn sẽ chỉ chọn tất cả các ô trống trong các cột phác thảo bảng tổng hợp. Đây là tất cả các ô mà bạn muốn điền vào.
Thủ thuật # 2. Đừng xem màn hình trong khi bạn làm điều này - nó quá đáng sợ và khó hiểu.
Nhấn phím bằng. Nhấn vào Mũi tên lên. Giữ Ctrl và nhấn Enter. Nhấn bằng và mũi tên lên cho biết, "Tôi muốn ô này giống như ô phía trên tôi." Nhấn giữ Ctrl khi bạn nhấn enter cho biết, "Hãy nhập cùng một công thức này vào mọi ô đã chọn, nhờ Thủ thuật # 1 là tất cả các ô trống mà chúng tôi muốn điền vào.
Thủ thuật # 3. Điều mà Jennifer đã biết, nhưng ở đây để hoàn thiện.
Bây giờ bạn cần thay đổi tất cả các công thức đó thành giá trị. Chọn lại tất cả các ô trong A3: B999, không chỉ chọn các ô trống. Nhấn Ctrl + C để sao chép phạm vi này. Nhấn alt = "" + E rồi nhấn sv (enter). để Dán Giá trị Đặc biệt các công thức này.
Ta-da! Bạn sẽ không bao giờ dành cả buổi chiều để kéo các tiêu đề cột trong bảng tổng hợp xuống theo cách thủ công nữa.
