Bài 1 · Chương 6: Sắp xếp, lọc và in
Sắp xếp và lọc danh sách
Chuẩn bị danh sách đúng cách, sắp xếp theo một hoặc hai cột, lọc bằng AutoFilter theo giá trị và theo điều kiện số hoặc chữ, rồi xóa bộ lọc để hiện lại toàn bộ dữ liệu.
Mục lục bài
Ôn lại trước khi học
Thử nhớ lại trước, rồi mới xem đáp án.
Trong vùng điểm có một ô chứa chữ
vắngvà vài ô trống. Hàm tính điểm trung bình xử lý các ô đó thế nào?Xem đáp án
Hàm bỏ qua ô chứa chữ và ô trống, chỉ tính các ô có số. Ô chứa số 0 thì vẫn được tính.Viết công thức nhân ô B2 với ô C2. Công thức bắt đầu bằng ký tự nào?
Xem đáp án
Công thức là=B2*C2. Công thức luôn bắt đầu bằng dấu=.Bảng dài, cuộn xuống là mất hàng tiêu đề. Làm cách nào để hàng tiêu đề luôn hiện khi cuộn?
Xem đáp án
Dùng Freeze Panes (cố định hàng). Chọn ô ngay dưới hàng cần giữ, rồi chọn View › Freeze Panes › Freeze Panes. Excel giữ các hàng phía trên ô đã chọn.
Mục tiêu
Sau bài này bạn có thể:
- Chuẩn bị danh sách đúng cách: một hàng tiêu đề, không hàng hoặc cột trống, mỗi ô một dữ liệu.
- Sắp xếp danh sách theo một cột (A đến Z, nhỏ đến lớn) và theo hai cột.
- Lọc danh sách bằng AutoFilter theo giá trị và theo điều kiện số hoặc chữ.
- Xóa bộ lọc để hiện lại toàn bộ dữ liệu.
Ví dụ có lời giải
Chuẩn bị danh sách
Sắp xếp và lọc chạy tốt nhất trên một danh sách gọn. Theo hướng dẫn của Microsoft, hãy làm như sau:
- Đặt tiêu đề cột ở hàng đầu tiên của danh sách. Danh sách cần có tiêu đề để sắp xếp và lọc cho kết quả tốt nhất.
- Không để hàng hoặc cột trống bên trong danh sách.
- Mỗi cột chứa cùng một loại thông tin ở mọi hàng, ví dụ cột Lương chỉ có số. Vì vậy hãy ghi Họ và tên và Phòng ở hai cột riêng, mỗi ô một dữ liệu, thay vì gộp vào một ô.
- Để ít nhất một cột trống và một hàng trống giữa danh sách và dữ liệu khác trên cùng trang tính.
- Không gõ dấu cách ở đầu hay cuối ô để căn lề, vì các dấu cách thừa có thể ảnh hưởng tới sắp xếp và tìm kiếm.
- Hiện lại các hàng và cột đã ẩn trước khi thay đổi danh sách. Hàng và cột ẩn không được di chuyển khi bạn sắp xếp.
Phòng hành chính có danh sách sáu nhân viên. Bạn cần xếp theo phòng ban từ A đến Z, và trong cùng phòng thì xếp lương từ lớn đến nhỏ. Sau đó bạn lọc để chỉ xem phòng KD.
| A | B | C | |
|---|---|---|---|
| 1 | Họ và tên | Phòng | Lương cơ bản |
| 2 | Trần Thị Lan | KD | 8,500,000 |
| 3 | Lê Văn Hùng | KT | 9,200,000 |
| 4 | Phạm Thu Hà | HC | 7,800,000 |
| 5 | Võ Minh Nam | KD | 10,000,000 |
| 6 | Đặng Kim Linh | IT | 12,000,000 |
| 7 | Nguyễn Văn An | KT | 8,000,000 |
Sắp xếp theo hai cột
Sắp xếp theo Phòng (A đến Z), rồi theo Lương (lớn đến nhỏ)
- Chỗ hay sai
Làm gì: Bấm một ô bất kỳ trong danh sách, ví dụ ô A3. Đừng chọn riêng một cột.
Vì sao: Mỗi hàng là một nhân viên, nên các cột phải di chuyển cùng nhau. Nếu chỉ sắp xếp một phần của danh sách, các nhân viên sẽ gắn với phòng ban khác với trước đó.
Làm gì: Mở thẻ Data và bấm Sort (hoặc chọn Home › Sort & Filter › Custom Sort). Trong hộp thoại Sort, đánh dấu ô My data has headers.
Vì sao: Hộp thoại Sort cho phép sắp xếp theo nhiều cột (tối đa 64 cột). Ô My data has headers báo cho Excel biết hàng 1 là hàng tiêu đề.
Làm gì: Ở cấp đầu, chọn cột Phòng, thứ tự A đến Z. Bấm Add Level, chọn cột Lương cơ bản, thứ tự từ lớn đến nhỏ.
Vì sao: Cấp đầu là tiêu chí chính. Cấp thứ hai quyết định thứ tự của các nhân viên cùng phòng.
Làm gì: Bấm OK. Danh sách xếp lại: HC, IT, rồi KD, KT. Trong KD, Võ Minh Nam (10,000,000) đứng trước Trần Thị Lan (8,500,000). Trong KT, Lê Văn Hùng (9,200,000) đứng trước Nguyễn Văn An (8,000,000).
Vì sao: Mỗi nhân viên vẫn giữ nguyên tên, phòng và lương của mình. Chỉ thứ tự các hàng đổi.
Tự hỏi mình
- Nếu bạn chỉ chọn cột Lương rồi sắp xếp, tên và phòng ban đứng cạnh mỗi mức lương sẽ ra sao?
- Vì sao các phòng KD và KT cần cấp sắp xếp thứ hai?
Lọc để chỉ xem một phòng
Lọc danh sách để chỉ hiện phòng KD
Làm gì: Bấm một ô trong danh sách, rồi chọn Data › Filter.
Vì sao: Mỗi tiêu đề cột có một mũi tên. Mũi tên cho biết bộ lọc đã bật nhưng chưa lọc gì.
- Chỗ hay sai
Làm gì: Bấm mũi tên ở cột Phòng. Bỏ chọn (Select All), rồi chọn ô KD.
Vì sao: Excel chỉ hiện các hàng thỏa điều kiện. Các hàng còn lại bị ẩn đi.
Làm gì: Nhìn danh sách: chỉ còn hai nhân viên KD. Nút ở tiêu đề Phòng đổi thành biểu tượng bộ lọc.
Vì sao: Biểu tượng bộ lọc cho biết cột này đang lọc. Bốn nhân viên các phòng khác chưa mất: họ chỉ bị ẩn.
Làm gì: Muốn lọc theo điều kiện số, bấm mũi tên ở cột Lương cơ bản, chọn Number Filters, rồi chọn một phép so sánh như Less Than và nhập số, ví dụ
9000000.Vì sao: Text Filters và Number Filters cho phép lọc theo phép so sánh thay vì chọn từng giá trị.
Làm gì: Để hiện lại tất cả: bấm nút lọc ở tiêu đề cột rồi chọn Clear Filter. Trên Excel cho web, bấm lại nút Filter ở thẻ Data cũng gỡ bộ lọc khỏi mọi cột.
Vì sao: Gỡ bộ lọc hiện lại toàn bộ dữ liệu, nên không có gì bị mất.
Tự hỏi mình
- Sau khi lọc, bạn thấy ít hàng hơn. Dữ liệu ở các hàng ẩn đang ở đâu?
- Khi nào nên dùng Number Filters thay vì chọn từng giá trị trong danh sách?
Điền tiếp từng bước
Bài này không có công thức, nên bạn hoàn thành các bước bằng câu hỏi. Mỗi câu hỏi đi tiếp một bước.
Bạn sắp danh sách theo Phòng (A đến Z) rồi theo Lương (lớn đến nhỏ). Bạn đã chọn một ô trong danh sách, mở hộp thoại Sort và đặt cấp đầu là Phòng, A đến Z. Bước tiếp theo là gì?
Bạn chỉ cần xếp danh sách theo Lương từ lớn đến nhỏ. Cách nhanh nhất là gì?
Tìm chỗ sai
Một bạn chỉ chọn cột Lương cơ bản, bấm sắp xếp, rồi chọn Continue with the current selection. Điều gì xảy ra với danh sách?
Sau đây là phần luyện tập, gồm hai bài trên bảng lương: một bài sắp xếp theo hai cột, một bài lọc theo chi nhánh.
Đọc xong, giờ thử
Luyện tập ngay (2 bài tập)
Làm trực tiếp trên lưới trong trình duyệt. Mỗi lần bấm chấm bài, DeskLab kiểm tra kết quả và gợi ý nếu bạn chưa làm đúng.
Bắt đầu luyện tậpChấm tự động ngay trên máy bạn. Không cần đăng nhập. Tiến độ lưu trên trình duyệt này.
Kiểm tra nhanh
Cách nào đúng để chuẩn bị một danh sách trước khi sắp xếp hoặc lọc?
Sau khi lọc theo Phòng KD, các nhân viên phòng khác biến mất khỏi màn hình. Chuyện gì đã xảy ra với họ?
Bạn muốn chỉ xem nhân viên có Lương cơ bản nhỏ hơn 9,000,000. Bạn chọn gì ở mũi tên lọc của cột Lương?
Cột ngày vào làm bị sắp xếp sai thứ tự thời gian. Nguyên nhân nào mà Microsoft nêu?
Lỗi hay gặp
Sắp xếp một cột, và tên với số liệu ở các cột khác không còn khớp nhau.
- Nguyên nhân
- Bạn chỉ chọn một cột rồi chọn
Continue with the current selection, nên chỉ cột đó đổi thứ tự. - Cách sửa
- Bấm một ô trong danh sách thay vì chọn riêng một cột. Nếu Excel hỏi, chọn
Expand the selection. Lỡ làm sai thì hoàn tác (Undo).
Danh sách có hàng trống ở giữa, và kết quả sắp xếp hoặc lọc không như mong đợi.
- Nguyên nhân
- Hàng trống nằm giữa danh sách. Microsoft khuyên tránh hàng và cột trống bên trong vùng dữ liệu.
- Cách sửa
- Xóa các hàng trống giữa danh sách, rồi sắp xếp hoặc lọc lại.
Lọc xong, tưởng các hàng khác đã bị xóa.
- Nguyên nhân
- Bộ lọc chỉ ẩn các hàng không thỏa điều kiện.
- Cách sửa
- Gỡ bộ lọc (Clear Filter) để hiện lại tất cả. Đừng xóa hàng khi đang lọc nếu bạn chỉ muốn xem.
Cột ngày được sắp xếp sai thứ tự thời gian.
- Nguyên nhân
- Các ô ngày được lưu dạng chữ chứ không phải ngày thật.
- Cách sửa
- Chuyển các ô thành ngày thật, rồi sắp xếp lại.
Trên Excel thật
- Thứ tự chữ có dấu: Lưới DeskLab sắp xếp văn bản theo mã ký tự đã đổi sang chữ thường, nên các chữ cái có dấu như
ĐhoặcẾđứng sau chữZ. Trên Excel thật, thứ tự sắp xếp thay đổi theo thiết lập vùng của máy, nên danh sách tên tiếng Việt có thể ra thứ tự khác. Vì vậy các bài tập ở đây chỉ sắp xếp theo số. - Vị trí lệnh: Trên lưới DeskLab, các nhóm lệnh sắp xếp và lọc nằm ở thẻ Dữ liệu của ribbon. Trên Excel thật, bạn tìm các lệnh Sort và Filter ở thẻ Data. Lệnh Custom Sort còn nằm ở Home › Sort & Filter.
- Hộp thoại: Excel thật có hộp thoại Sort với ô My data has headers và nút Add Level. Hãy tự thử trên Excel thật vì lưới luyện tập không giống hệt.