- Tham gia
- 4/6/06
- Bài viết
- 901
- Được thích
- 2,714
NHỮNG BÍ KÍP VỀ EXCEL FILTER CÓ THỂ BẠN CHƯA BIẾT (P1)
Nếu bạn thường xuyên sắp xếp các dữ liệu thì bạn cần phải biết các thủ thuật của filter. Nếu bạn muốn xem dữ liệu cụ thể dựa trên các tiêu chí thì filter là cách tốt nhất để thực hiện. Giả sử bạn biết cách thức hoạt động của filter trong Excel, GPE xin giới thiệu với bạn những điều ẩn giấu của Filter mà có thể bạn chưa biết. Những tính năng của Filter luôn được quan tâm bởi người mới bắt đầu và cả người dùng giàu kinh nghiệm.
Trước hết, hãy nói về các chức năng Subtotal. Giả sử bạn có một dữ liệu kinh doanh bao gồm khoảng 5000 dòng như dưới đây:
Bây giờ nếu quản lý của bạn đến và yêu cầu bạn cung cấp cho anh ta danh sách top 10 mặt hàng đem lại lợi nhuận cho công ty nhiều nhất và giá trị nhỏ nhất trong 10 mặt hàng. Bạn sẽ làm gì? Bạn có thể sắp xếp các dữ liệu với cột "Trị giá†từ số lớn nhất đến nhỏ nhất. Sau đó, bạn sẽ dán 10 dòng đầu tiên vào 1 sheet khác và áp dụng hàm MIN để có được số tiền nhỏ nhất trong top 10 mặt hàng. Nhưng nếu ngày mai tiêu chí thay đổi, bạn sẽ phải làm điều tương tự như vậy ư?
Hãy áp dụng Filter vào dữ liệu của bạn. Bạn có thể chọn toàn bộ dữ liệu bằng cách nhấn tổ hợp phím CTRL + SHIFT + mũi tên phải và sau đó, nhấn CTRL + SHIFT + mũi tên xuống. Nếu lỡ tay nhấn chọn thêm một số hàng khác, bạn có thể nhấn tổ hợp phím CTRL + SHIFT + mũi tên lên để điều chỉnh tương tự. Sau khi chọn xong dữ liệu, vào thẻ Data >> Filter. Hoặc bạn có thể sử dụng phím tắt Alt + A + T để áp dụng Filter cho dữ liệu của bạn.
Sau khi áp dụng Filter cho dữ liệu của bạn, click vào nút mũi tên thả xuống ở cột "Trị giá". Chọn Number Filters >> Top 10.
Ngay sau khi bạn click vào Top 10, một hộp thoại mới sẽ xuất hiện để xác nhận lại lần nữa. Bạn có thể chọn số lượng mong muốn trong trường ở giữa và sau đó click OK. Trong ví dụ này là 10.
Bạn sẽ có được kết quả như hình dưới đây, top 10 mặt hàng đem lại lợi nhuận cho công ty nhiều nhất.
Bây giờ bạn muốn biết xem Số lượng tối thiểu là bao nhiêu. Trong ví dụ này, bạn không thể sử dụng hàm MIN, vì hàm MIN sẽ chọn toàn bộ phạm vi bao gồm các hàng ẩn và sẽ cho kết quả từ toàn bộ dữ liệu của bạn, không phải từ dữ liệu đã lọc. Vì vậy, phương pháp tốt nhất để làm điều đó là gì?
Chỉ cần chọn ô dưới cùng nơi dữ liệu được lọc của bạn kết thúc. Sau đó nhấn tổ hợp phím Alt + =. bạn sẽ có ngay một công thức Subtotal.
Excel rất thông minh. Nó nhận biết được dữ liệu đã lọc của bạn và bạn sẽ làm việc với dữ liệu được lọc đó. Đó là lý do tại sao chúng ta có được công thức Subtotal.
Mục đích của chúng ta là tìm ra con số giá trị nhỏ nhất từ top 10. Bây giờ thay đổi số 9 thành 5, bởi vì 9 được sử dụng cho SUM và 5 được sử dụng cho MIN, nhấn Enter. Bạn sẽ nhận được số nhỏ nhất từ dữ liệu được lọc của bạn..
Bây giờ bảng tính đã năng động hơn rồi đấy. Bạn hãy vào lại mũi tên thả xuống ở cột "Trị giá" và lần này chỉ chọn Top 5 thôi. Bạn sẽ nhận được giá trị nhỏ nhất trong top 5 đó. Tương tự như vậy bạn cũng có thể sử dụng các số khác nhau cho các hàm khác nhau. Bạn có thể sử dụng số 9 cho SUM, số 4 cho MAX, … Bạn không cần phải ghi nhớ danh sách các số ứng với các hàm. Nó sẽ tự động hiện lên nhưng bạn cần phải biết rõ logic của mình.
Bạn hãy nhìn thật kỹ vào vùng dữ liệu được lọc, nó bắt đầu từ dòng 177 (trong khi dữ liệu thực tế được bắt đầu từ dòng thừ 2) và kết thúc ở dòng 847 (dữ liệu thực tế kết thúc tại dòng 856). Nó không thêm vào những con số khác được ẩn giữa dòng 847 và 856. Điều đó có nghĩa là trong tương lai nếu bạn mở rộng danh sách, giả sử 30 mặt hàng và bạn vẫn giữ công thức Subtotal, bạn sẽ thấy nó không bao gồm các giá trị bên ngoài dòng 847
Vì vậy, để an toàn, hãy nhấn phím tắt Alt + = để kích hoạt công thức Subtotal sau khi thay đổi dữ liệu. Đừng quên nhé.
Chúc bạn vui với bài viết này
Nguyễn Bảo Khanh
Một số bài viết có liên quan:
1/ Spreadsheet Design: TRÌNH BÀY BẢNG TÍNH
2/ Chiêu số 23: Bật, tắt chức năng Conditional Formatting bằng 1 checkbox
3/ Chiêu thứ 21: Tô màu dòng xen kẽ
4/ Chiêu số 19: Đánh dấu những ô chứa công thức bằng Conditional Formatting
5/ Sử dụng định dạng có điều kiện (Conditional Formatting) để làm nổi bật ngày tháng trong Excel
6/ Chiêu số 18: Điều khiển Conditional Formating bằng checkbox
7/ Chiêu số 11: Tìm dữ liệu xuất hiện 2 hoặc nhiều lần bằng công cụ Conditional Formating
8/ Chiêu thứ 8: Giới hạn vùng cuộn của bảng tính
9/ Ebook: Dữ liệu & Báo cáo trong Excel 2013
10/ Phân tích tồn kho theo phương pháp ABC
Nếu bạn thường xuyên sắp xếp các dữ liệu thì bạn cần phải biết các thủ thuật của filter. Nếu bạn muốn xem dữ liệu cụ thể dựa trên các tiêu chí thì filter là cách tốt nhất để thực hiện. Giả sử bạn biết cách thức hoạt động của filter trong Excel, GPE xin giới thiệu với bạn những điều ẩn giấu của Filter mà có thể bạn chưa biết. Những tính năng của Filter luôn được quan tâm bởi người mới bắt đầu và cả người dùng giàu kinh nghiệm.
Trước hết, hãy nói về các chức năng Subtotal. Giả sử bạn có một dữ liệu kinh doanh bao gồm khoảng 5000 dòng như dưới đây:
Bây giờ nếu quản lý của bạn đến và yêu cầu bạn cung cấp cho anh ta danh sách top 10 mặt hàng đem lại lợi nhuận cho công ty nhiều nhất và giá trị nhỏ nhất trong 10 mặt hàng. Bạn sẽ làm gì? Bạn có thể sắp xếp các dữ liệu với cột "Trị giá†từ số lớn nhất đến nhỏ nhất. Sau đó, bạn sẽ dán 10 dòng đầu tiên vào 1 sheet khác và áp dụng hàm MIN để có được số tiền nhỏ nhất trong top 10 mặt hàng. Nhưng nếu ngày mai tiêu chí thay đổi, bạn sẽ phải làm điều tương tự như vậy ư?
Hãy áp dụng Filter vào dữ liệu của bạn. Bạn có thể chọn toàn bộ dữ liệu bằng cách nhấn tổ hợp phím CTRL + SHIFT + mũi tên phải và sau đó, nhấn CTRL + SHIFT + mũi tên xuống. Nếu lỡ tay nhấn chọn thêm một số hàng khác, bạn có thể nhấn tổ hợp phím CTRL + SHIFT + mũi tên lên để điều chỉnh tương tự. Sau khi chọn xong dữ liệu, vào thẻ Data >> Filter. Hoặc bạn có thể sử dụng phím tắt Alt + A + T để áp dụng Filter cho dữ liệu của bạn.
Sau khi áp dụng Filter cho dữ liệu của bạn, click vào nút mũi tên thả xuống ở cột "Trị giá". Chọn Number Filters >> Top 10.
Ngay sau khi bạn click vào Top 10, một hộp thoại mới sẽ xuất hiện để xác nhận lại lần nữa. Bạn có thể chọn số lượng mong muốn trong trường ở giữa và sau đó click OK. Trong ví dụ này là 10.
Bạn sẽ có được kết quả như hình dưới đây, top 10 mặt hàng đem lại lợi nhuận cho công ty nhiều nhất.
Bây giờ bạn muốn biết xem Số lượng tối thiểu là bao nhiêu. Trong ví dụ này, bạn không thể sử dụng hàm MIN, vì hàm MIN sẽ chọn toàn bộ phạm vi bao gồm các hàng ẩn và sẽ cho kết quả từ toàn bộ dữ liệu của bạn, không phải từ dữ liệu đã lọc. Vì vậy, phương pháp tốt nhất để làm điều đó là gì?
Chỉ cần chọn ô dưới cùng nơi dữ liệu được lọc của bạn kết thúc. Sau đó nhấn tổ hợp phím Alt + =. bạn sẽ có ngay một công thức Subtotal.
Excel rất thông minh. Nó nhận biết được dữ liệu đã lọc của bạn và bạn sẽ làm việc với dữ liệu được lọc đó. Đó là lý do tại sao chúng ta có được công thức Subtotal.
Mục đích của chúng ta là tìm ra con số giá trị nhỏ nhất từ top 10. Bây giờ thay đổi số 9 thành 5, bởi vì 9 được sử dụng cho SUM và 5 được sử dụng cho MIN, nhấn Enter. Bạn sẽ nhận được số nhỏ nhất từ dữ liệu được lọc của bạn..
Bây giờ bảng tính đã năng động hơn rồi đấy. Bạn hãy vào lại mũi tên thả xuống ở cột "Trị giá" và lần này chỉ chọn Top 5 thôi. Bạn sẽ nhận được giá trị nhỏ nhất trong top 5 đó. Tương tự như vậy bạn cũng có thể sử dụng các số khác nhau cho các hàm khác nhau. Bạn có thể sử dụng số 9 cho SUM, số 4 cho MAX, … Bạn không cần phải ghi nhớ danh sách các số ứng với các hàm. Nó sẽ tự động hiện lên nhưng bạn cần phải biết rõ logic của mình.
Bạn hãy nhìn thật kỹ vào vùng dữ liệu được lọc, nó bắt đầu từ dòng 177 (trong khi dữ liệu thực tế được bắt đầu từ dòng thừ 2) và kết thúc ở dòng 847 (dữ liệu thực tế kết thúc tại dòng 856). Nó không thêm vào những con số khác được ẩn giữa dòng 847 và 856. Điều đó có nghĩa là trong tương lai nếu bạn mở rộng danh sách, giả sử 30 mặt hàng và bạn vẫn giữ công thức Subtotal, bạn sẽ thấy nó không bao gồm các giá trị bên ngoài dòng 847
Vì vậy, để an toàn, hãy nhấn phím tắt Alt + = để kích hoạt công thức Subtotal sau khi thay đổi dữ liệu. Đừng quên nhé.
Chúc bạn vui với bài viết này
Nguyễn Bảo Khanh
Một số bài viết có liên quan:
1/ Spreadsheet Design: TRÌNH BÀY BẢNG TÍNH
2/ Chiêu số 23: Bật, tắt chức năng Conditional Formatting bằng 1 checkbox
3/ Chiêu thứ 21: Tô màu dòng xen kẽ
4/ Chiêu số 19: Đánh dấu những ô chứa công thức bằng Conditional Formatting
5/ Sử dụng định dạng có điều kiện (Conditional Formatting) để làm nổi bật ngày tháng trong Excel
6/ Chiêu số 18: Điều khiển Conditional Formating bằng checkbox
7/ Chiêu số 11: Tìm dữ liệu xuất hiện 2 hoặc nhiều lần bằng công cụ Conditional Formating
8/ Chiêu thứ 8: Giới hạn vùng cuộn của bảng tính
9/ Ebook: Dữ liệu & Báo cáo trong Excel 2013
10/ Phân tích tồn kho theo phương pháp ABC
Lần chỉnh sửa cuối:
Upvote
0
