Lọc dữ liệu trong Excel. Bộ lọc trong Excel: cách tạo và lý do sử dụng Bộ lọc Tự động và Bộ lọc Nâng cao

Microsoft Excel là một công cụ bảng tính phổ biến và tiện lợi.

Chức năng rộng rãi làm cho chương trình này trở nên phổ biến thứ hai sau MS Word trong số tất cả các chương trình văn phòng.

Nó được sử dụng bởi các nhà kinh tế, kế toán, nhà khoa học, sinh viên và đại diện của các ngành nghề khác, những người cần xử lý dữ liệu toán học.

Một trong những tính năng tiện lợi nhất của chương trình này là lọc dữ liệu. Hãy xem cách thiết lập và sử dụng bộ lọc MS excel.

Việc tìm kiếm các bộ lọc trong chương trình này thật dễ dàng - bạn cần mở menu chính hoặc chỉ cần giữ phím Ctrl+Shift+L.

Các hàm lọc cơ bản trong Excel:

lựa chọn theo màu sắc: cho phép sắp xếp dữ liệu theo phông chữ hoặc màu tô,
bộ lọc văn bản trong excel: cho phép bạn đặt các điều kiện nhất định cho các hàng, ví dụ: nhỏ hơn, lớn hơn, bằng, không bằng và các điều kiện khác, cũng như đặt các điều kiện logic - và, hoặc,
bộ lọc số: sắp xếp theo tiêu chí số, ví dụ: dưới mức trung bình, top 10 và các tiêu chí khác,
thủ công: việc lựa chọn có thể được thực hiện theo tiêu chí tự chọn.

Rất dễ dàng để sử dụng chúng. Cần phải chọn bảng và chọn phần có bộ lọc trong menu, sau đó chỉ định dữ liệu sẽ được lọc theo tiêu chí nào.

Cách sử dụng bộ lọc nâng cao trong Excel - cách thiết lập

Bộ lọc tiêu chuẩn có một nhược điểm đáng kể - để nhớ tiêu chí lựa chọn nào đã được sử dụng, bạn cần mở menu. Và điều này càng bất tiện hơn khi có nhiều hơn một tiêu chí được chỉ định. Từ quan điểm này, bộ lọc nâng cao sẽ thuận tiện hơn, bộ lọc này được hiển thị trong một bảng riêng phía trên dữ liệu.

VIDEO HƯỚNG DẪN

Thủ tục thiết lập:

Tạo một bảng chứa dữ liệu để tiếp tục làm việc với nó. Nó không nên chứa các dòng trống.
Tạo bảng với điều kiện lựa chọn
Khởi chạy bộ lọc nâng cao.

Hãy xem xét một thiết lập ví dụ.
Chúng ta có một bảng gồm các cột Sản phẩm, Số lượng và Giá cả.

Ví dụ: bạn cần sắp xếp các hàng có tên sản phẩm bắt đầu bằng từ “Nails”. Một số hàng nằm trong điều kiện này.

Bảng có điều kiện sẽ được đặt trong ô A1:A2. Điều quan trọng là phải chỉ ra tên của cột nơi việc lựa chọn sẽ diễn ra (ô A1) và từ chỉ chính lựa chọn - Nails (ô A2).

Sẽ thuận tiện nhất khi đặt nó phía trên dữ liệu hoặc ở bên cạnh. Theo nó cũng không bị cấm, nhưng không phải lúc nào cũng thuận tiện, vì đôi khi có thể cần phải thêm dòng bổ sung. Thụt lề ít nhất một dòng trống giữa hai bảng.

Sau đó, bạn cần phải:

chọn bất kỳ ô nào,
mở “Bộ lọc nâng cao” theo đường dẫn: Dữ liệu – Sắp xếp và lọc – Nâng cao,
kiểm tra những gì được chỉ định trong trường “Phạm vi ban đầu” - toàn bộ bảng có thông tin sẽ nằm ở đó,
trong “Phạm vi điều kiện”, bạn cần đặt giá trị của các ô với điều kiện lựa chọn, trong ví dụ này là phạm vi A1:A2.

Sau khi nhấp vào nút “OK”, thông tin cần thiết sẽ được chọn và chỉ những hàng có từ mong muốn mới xuất hiện trong bảng, trong trường hợp của chúng tôi là “Nails”. Các số dòng còn lại sẽ chuyển sang màu xanh. Để hủy bộ lọc đã chỉ định, chỉ cần nhấn CTRL+SHIFT+L.

Cũng dễ dàng thiết lập tính năng lọc các dòng chứa từ chính xác "Nails" mà không phân biệt chữ hoa chữ thường. Trong phạm vi B1:B2, chúng tôi sẽ đặt một cột với tiêu chí lựa chọn mới, không quên chỉ ra tiêu đề của cột sẽ thực hiện sàng lọc. Trong ô B2, bạn phải nhập công thức sau ="=Nails".

chọn bất kỳ ô bảng nào,
mở "Bộ lọc nâng cao"
kiểm tra xem toàn bộ bảng có dữ liệu có được bao gồm trong “Phạm vi nguồn” hay không
trong “Phạm vi điều kiện” chỉ ra B1:B2.

Sau khi nhấn “OK”, dữ liệu sẽ được lọc ra.

Đây là những ví dụ đơn giản nhất làm việc với bộ lọc trong excel. Trong phiên bản nâng cao, thật thuận tiện khi đặt các điều kiện khác để lựa chọn, ví dụ: sàng lọc với tham số “OR”, sàng lọc với tham số “Nails” và giá trị trong cột “Số lượng” >40.

Thông tin trong bảng có thể được lọc theo cột - một hoặc nhiều cột. Hãy xem ví dụ về một bảng có các cột “Thành phố”, “Tháng” và “Doanh số”.

ví dụ 1

Nếu bạn cần lọc dữ liệu theo cột có tên thành phố theo thứ tự bảng chữ cái, bạn cần chọn bất kỳ ô nào của cột này, mở “Sắp xếp” và “Bộ lọc” rồi chọn tùy chọn “AY”. Kết quả là thông tin sẽ được hiển thị có tính đến chữ cái đầu tiên của tên thành phố.

Để có được thông tin theo nguyên tắc ngược lại, bạn cần sử dụng tham số “YA”.

Ví dụ 2

Cần phải lọc thông tin theo tháng và thành phố có doanh số bán hàng lớn nên nằm trong bảng phía trên thành phố có doanh số bán hàng thấp hơn. Để giải quyết vấn đề, bạn cần chọn tùy chọn “Sắp xếp” trong “Sắp xếp và lọc”. Trong cửa sổ cài đặt xuất hiện, chỉ định “Sắp xếp theo” - “Tháng”.

Tiếp theo bạn cần thêm cấp độ sắp xếp thứ hai. Để thực hiện việc này, bạn cần chọn trong “Sắp xếp” - “Thêm cấp độ” và chỉ định cột “Bán hàng”. Trong cột cài đặt “Thứ tự”, chọn “Giảm dần”. Sau khi nhấn “OK”, dữ liệu sẽ được chọn theo các thông số đã chỉ định.

VIDEO HƯỚNG DẪN

Tại sao bộ lọc có thể không hoạt động trong Excel

Người dùng thường gặp khó khăn khi làm việc với các công cụ như bộ lọc. Thông thường chúng có liên quan đến việc vi phạm các quy tắc sử dụng một số cài đặt nhất định.

Vấn đề với bộ lọc ngày là một trong những vấn đề phổ biến nhất. Xảy ra sau khi tải dữ liệu từ hệ thống kế toán dưới dạng mảng. Khi bạn cố gắng lọc các hàng theo cột chứa ngày tháng, việc lọc xảy ra không phải theo ngày mà theo văn bản.

Giải pháp cho vấn đề:

chọn cột ngày,
mở tab Excel trong menu chính,
Chọn nút “Ô” và chọn tùy chọn “Chuyển đổi văn bản thành ngày” từ danh sách thả xuống.

Những lỗi phổ biến của người dùng khi làm việc với chương trình này còn bao gồm:

thiếu tiêu đề cột (không có chúng, tính năng lọc, sắp xếp và một số tham số quan trọng khác sẽ không hoạt động),
sự hiện diện của các hàng và cột trống trong bảng có dữ liệu (điều này gây nhầm lẫn cho hệ thống sắp xếp; Excel coi thông tin là hai bảng khác nhau độc lập với nhau),
đặt nhiều bảng trên một trang (sẽ thuận tiện hơn khi đặt mỗi bảng trên một trang riêng),
vị trí trong một số cột dữ liệu cùng loại,
sắp xếp dữ liệu trên một số trang, chẳng hạn như theo tháng hoặc năm (khối lượng công việc có thể được nhân ngay với số trang có thông tin).

Và một sai lầm nghiêm trọng khác không cho phép bạn sử dụng hết các khả năng của Excel là sử dụng sản phẩm không được cấp phép.

Hoạt động chính xác của nó không thể được đảm bảo và lỗi sẽ xuất hiện liên tục. Nếu bạn có ý định sử dụng liên tục công cụ xử lý thông tin toán học này, hãy mua phiên bản đầy đủ của chương trình.

Cơ sở dữ liệu rất thuận tiện cho việc lưu trữ thông tin, nhưng chúng tôi tạo ra chúng để nhận được sự trợ giúp cần thiết khi có nhu cầu như vậy.

Ví dụ: chúng ta cần lịch trình các chuyến tàu khởi hành đến Moscow vào thứ Sáu sau bốn giờ chiều, v.v.

Việc tìm kiếm thông tin cần thiết được thực hiện bằng cách chọn các hàng thỏa mãn một số tiêu chí. Trong hầu hết các trường hợp, tiêu chí lựa chọn là nội dung của ô bằng một giá trị nhất định.

Ngoài các so sánh đẳng thức, bạn có thể sử dụng các thao tác so sánh khác khi chọn bản ghi. Ví dụ: lớn hơn, nhỏ hơn, lớn hơn hoặc bằng, nhỏ hơn hoặc bằng. Việc sử dụng các thao tác này cho phép bạn xây dựng tiêu chí truy vấn ít nghiêm ngặt hơn. Ví dụ: nếu bạn muốn tìm thông tin về một người có họ bắt đầu bằng “Ku” thì bạn có thể sử dụng quy tắc “nội dung của ô Họ lớn hơn hoặc bằng Ku và nội dung của ô Họ nhỏ hơn L” làm tiêu chí.

Quá trình chọn các hàng từ một bảng thỏa mãn một tiêu chí (quy tắc) nhất định được gọi là lọc và tiêu chí (điều kiện để chọn các hàng) được gọi là bộ lọc.

1. Chọn hàng của bảng chứa tên cột, mở tab Home -> Editing và chọn lệnh Filter trong danh sách Sorting and Filter. Kết quả là các nút danh sách thả xuống sẽ xuất hiện ở hàng được đánh dấu bên cạnh tên các cột trong bảng.
2. Mở rộng danh sách bằng cách nhấp vào tiêu đề cột và trong cửa sổ mở ra, trước tiên hãy đặt lại công tắc (Chọn tất cả), sau đó chọn giá trị sẽ được sử dụng làm tiêu chí để chọn hàng.

Do thực hiện các hành động trên, chỉ những hàng thỏa mãn điều kiện truy vấn mới còn lại trên màn hình.

Là tiêu chí để chọn cột chứa các giá trị số, bạn có thể chỉ định phạm vi chứa các giá trị ô. Ví dụ: để có được danh sách chi phí, số tiền dao động từ 100 rúp. lên tới 1000 rúp, bạn cần mở cửa sổ bộ lọc cho cột Số tiền, chọn Bộ lọc số -> giữa và trong cửa sổ Bộ lọc tự động tùy chỉnh xuất hiện, nhập ranh giới phạm vi.

Hãy xem cách bạn có thể tìm kiếm thông tin trong MS Excel bằng bộ lọc.

Để thực hiện việc này, hãy mở Excel và vẽ một bảng nhỏ trong đó.

Chọn bất kỳ ô nào trong hàng tiêu đề, sau đó đi tới " Dữ liệu" và nhấp vào nút " Lọc»:

Trong hàng có tiêu đề của bảng của chúng ta, các “mũi tên” sẽ xuất hiện ở mỗi cột.

Xin lưu ý rằng nếu bảng của bạn không có hàng có tiêu đề thì Excel sẽ tự động chèn bộ lọc vào hàng dữ liệu đầu tiên:

Giai đoạn chuẩn bị đã hoàn tất. Bạn có thể bắt đầu tìm kiếm thông tin.

Lọc thông tin cơ bản

Áp dụng bộ lọc cho bảng

Nhấn vào biểu tượng ở cột Người quản lý. Menu sau sẽ mở ra:

Trong menu này, bằng cách sử dụng các hộp kiểm, bạn có thể đánh dấu các phần tử mà bạn muốn lọc dữ liệu.

Nếu có nhiều giá trị trong bảng thì hãy sử dụng chuỗi tìm kiếm. Để làm điều này, hãy bắt đầu nhập một phần của từ mà bạn cần tìm. Danh sách các tùy chọn sẽ được tự động rút ngắn.

Nhược điểm của phương pháp này là bạn chỉ có thể đánh dấu một giá trị hoặc một số giá trị chứa cụm từ tìm kiếm, nhưng không thể tìm thấy các giá trị hoàn toàn khác nhau. Ví dụ: bạn sẽ không thể tìm thấy ngay những người quản lý có tên là Sergey và Alexander theo cách này, nhưng bạn có thể tìm thấy tất cả các giá trị có chứa “Serg”: Sergey, Sergeev, Sergienko, v.v.

Ví dụ: bạn chỉ cần đánh dấu 2 giá trị trong số vài chục giá trị. Việc bỏ chọn thủ công từng mục ngoại trừ những mục bạn cần khá tốn thời gian. Để tăng tốc quá trình này, hãy bỏ chọn hộp kiểm “(Chọn tất cả)”. Điều này sẽ xóa tất cả các hộp kiểm khác. Bây giờ bạn chỉ có thể đánh dấu những mục mà bạn cần.

MS Excel hỗ trợ nhiều bộ lọc, tức là lọc trên nhiều cột cùng một lúc.

Ví dụ: bạn cần tìm tất cả các đơn đặt hàng của người quản lý Ivanov ngày 18 tháng 1 năm 2014.

Để bắt đầu, hãy nhấp vào cột “Người quản lý” và chọn Ivanov.

Bây giờ hãy nhấp vào cột "Ngày giao hàng", bỏ chọn "(Chọn tất cả)" và chọn 18/01/2014 hoặc nhập 18 vào thanh tìm kiếm và nhấp vào "OK".

Bảng sẽ trông như thế này:

Tương tự như vậy, bạn có thể tiếp tục lọc dữ liệu theo các cột “Mô tả”, “Số lượng”, v.v.

Lưu ý rằng trong các cột đã được lọc, biểu tượng sẽ thay đổi thành .

Bằng cách này, bạn sẽ luôn biết dữ liệu được lọc theo cột nào.

Hủy bộ lọc

Để xóa tất cả các bộ lọc cùng một lúc, hãy đi tới “ Dữ liệu" và nhấp vào nút " Thông thoáng».

Nếu bạn chỉ cần xóa bộ lọc khỏi một cột, để lại các bộ lọc ở các cột khác, hãy nhấp vào biểu tượng cho cột này, ví dụ: “Ngày giao hàng” và nhấp vào mục “Xóa bộ lọc khỏi<Название столбца>»:

hoặc

Nếu bạn cần loại bỏ hoàn toàn các bộ lọc trong bảng, hãy đi tới “ Dữ liệu" và nhấp vào nút " Lọc" Nó sẽ ngừng tô sáng, các biểu tượng sẽ biến mất khỏi thanh tiêu đề và tất cả dữ liệu sẽ được hiển thị trong bảng.

Trước

Sau đó

Cài đặt bộ lọc bổ sung

Tùy thuộc vào loại nội dung của các cột, bộ lọc có các tùy chọn bổ sung.

Bộ lọc văn bản

Nhấp vào biểu tượng cột “Trình quản lý”, di chuột qua “Bộ lọc văn bản”, đợi menu xuất hiện và chọn bất kỳ tiêu chí lựa chọn nào hoặc mục “Bộ lọc tùy chỉnh…”. Cửa sổ sau sẽ xuất hiện:

1. Điều khoản "bằng" hoặc "không công bằng" giả sử rằng biểu thức mong muốn khớp với nội dung của ô một trăm phần trăm. Tiêu chuẩn "bằng" trong bảng chỉ để lại những hàng chứa giá trị đã chọn. Theo đó, tiêu chí "không công bằng"để lại tất cả các giá trị ngoại trừ giá trị đã chọn. Để đơn giản hóa tác vụ, bạn có thể chọn giá trị mong muốn từ danh sách thả xuống:

2. Điều khoản "hơn" Và "ít hơn" giả sử bảng sẽ giữ lại các giá trị bắt đầu bằng một chữ cái sớm hơn hoặc muộn hơn trong bảng chữ cái. Ví dụ: nếu bạn chọn giá trị “Ivanov” cho tùy chọn “thêm”, thì chỉ những ô bắt đầu bằng chữ “Y” mới còn lại trong bảng (Kartov, Yoghurtov, v.v.). Và với tùy chọn “ít hơn” - các giá trị bắt đầu bằng chữ cái “Z” (Zakharov, Bukin).
3. Sự khác biệt duy nhất về điều kiện "nhiều hơn hoặc bằng" Và "ít hơn hoặc bằng" từ đoạn trước trong đó bộ lọc bao gồm giá trị đã chọn.
4. Nếu bạn cần tìm tất cả các giá trị bắt đầu bằng "Cây liễu", sau đó sử dụng điều kiện "bắt đầu với", và nếu bạn muốn biết có bao nhiêu giá trị trong bảng kết thúc bằng "rovich", sau đó chọn tùy chọn "kết thúc bằng".
5. Theo đó, điều kiện "không bắt đầu bằng" Và "không kết thúc bằng" giả sử rằng bạn không cần hiển thị các giá trị chứa cụm từ tìm kiếm trong bảng.
6. Khi chọn điều kiện "chứa" hoặc "không chứa" Bạn có thể chỉ định bất kỳ cụm từ hoặc tổ hợp chữ cái nào mà bạn muốn bao gồm hoặc loại trừ khỏi bộ lọc. Sự khác biệt giữa điểm này với điểm 1, 4 và 5 là cụm từ tìm kiếm có thể nằm ở bất kỳ đâu trong ô. Ví dụ: đặt “Willow” làm bộ lọc, kết quả sẽ là “Alexey Ivanov”, “Sergey Ivarovsky”, “đường cong”, v.v.

Bộ lọc số

Hầu hết các điều kiện đều giống như đối với bộ lọc văn bản. Chúng ta hãy chỉ xem xét những cái mới.

1. Điều kiện "giữa". Khi bạn chọn điều kiện này, các tiêu chí cần thiết sẽ ngay lập tức được đặt trong cửa sổ xuất hiện, giúp công việc của bạn dễ dàng hơn:

Tất cả những gì bạn phải làm là nhập các giá trị từ bàn phím hoặc chọn chúng từ danh sách.

Lọc theo ngày

Những điều kiện này không yêu cầu giải thích đặc biệt vì ý nghĩa của chúng rất dễ hiểu từ tên gọi. Điều duy nhất đáng chú ý là trong cửa sổ tiêu chuẩn để chọn điều kiện lọc, nút Lịch xuất hiện để bạn nhập ngày dễ dàng hơn.

Và thêm một chút về bộ lọc

Có một cách khác để lọc dữ liệu. Hãy biến đổi bảng của chúng tôi một chút:

Như bạn có thể thấy, chúng tôi đã tô màu nó.

Ví dụ: bây giờ chúng ta cần tìm tất cả các dòng có Krasotkin. Nhấp chuột phải vào ô có người này và chọn “Bộ lọc” từ menu xuất hiện. Menu mới có một số tùy chọn mới. Trong ví dụ này chúng ta cần mục "Lọc theo giá trị...".

Nếu bạn chọn điều kiện "Lọc theo màu sắc...", khi đó bảng sẽ chứa các hàng có ô cùng màu với ô hiện hoạt (tô màu vàng).

Nếu bạn bấm vào “Lọc theo màu phông chữ…”, thì chỉ những ô có phông chữ màu đỏ hoặc đen sẽ vẫn còn trong bảng của chúng ta, tùy thuộc vào ô nào hiện đang hoạt động.

Mục bộ lọc cuối cùng chỉ áp dụng được nếu bảng sử dụng định dạng có điều kiện bằng biểu tượng.

Quản trị trang web trang mạng bày tỏ lòng biết ơn tới tác giả Alexander Tsarevđể chuẩn bị nguyên liệu.

Đôi khi, khi làm việc với cơ sở dữ liệu lớn, cần phải chọn thông tin theo một tiêu chí nhất định (ví dụ: chỉ hiển thị những người sinh năm 1980). Để thực hiện các tác vụ thuộc loại này, hãy sử dụng bộ lọc trong Excel.

Video lọc và sắp xếp dữ liệu trong Excel

Các loại bộ lọc trong Excel

Lọc là việc lựa chọn dữ liệu cần thiết từ danh sách để làm việc tiếp theo với nó. Kết quả của thủ tục này sẽ là một số dòng phù hợp với tiêu chí lựa chọn. Các mục còn lại tạm thời bị ẩn và không được sử dụng cho đến khi người dùng tắt bộ lọc. Bạn có thể thực hiện các hành động tiêu chuẩn với dữ liệu đã chọn: chỉnh sửa, định dạng, in, tạo đồ thị, biểu đồ, v.v.

Có 2 phương pháp lọc trong Excel là AutoFilter và Advanced Filter. Bạn có thể khởi chạy chúng thông qua thanh menu bằng cách nhấp vào “Dữ liệu - Bộ lọc”. Sử dụng tùy chọn đầu tiên, bạn nhanh chóng chọn được thông tin cần thiết với tiêu chí tìm kiếm đơn giản. Ở chế độ lọc tự động, dòng tiêu đề của bảng trong mỗi cột sẽ chứa một nút có mũi tên, bằng cách nhấp vào đó bạn có thể chỉ định tiêu chí lựa chọn. Đối với mỗi cột, bạn có thể đặt cài đặt của riêng mình. Ở chế độ này bạn có thể thiết lập các thông số sau:

Sắp xếp theo thứ tự tăng dần hoặc giảm dần.
“Tất cả” - Excel sẽ hiển thị (khôi phục) tất cả các hàng.
Top 10—Excel hiển thị 10 bản ghi đầu tiên. Khi bạn chọn mục này, một cửa sổ mới sẽ mở trong đó bạn có thể chỉ định số lượng bản ghi, chọn bản ghi nào sẽ hiển thị (lớn nhất hoặc nhỏ nhất) cũng như đặt giới hạn về số lượng bản ghi được hiển thị.
“Điều kiện” - tại đây người dùng có thể tạo độc lập 2 tiêu chí lựa chọn dữ liệu, kết hợp chúng với các toán tử AND, OR đơn giản.
Bất kỳ yếu tố nào. Tại đây bạn có thể chọn bất kỳ giá trị nào có trong cột. Ví dụ: nếu một cột bao gồm tên sản phẩm thì tất cả các sản phẩm sẽ được chỉ định trong danh sách các thành phần. Người dùng có thể chỉ định bất kỳ trong số họ.
Trống và Không trống—Excel sẽ hiển thị các ô trống (hoặc không trống). Tùy chọn này chỉ xuất hiện nếu cột có trường trống.
Để xóa bộ lọc khỏi một cột, bạn cần nhấp vào mục “Tất cả” trong danh sách các thành phần. Nếu muốn hủy toàn bộ bảng thì phải chọn “Data - Filter - Display all” trên thanh menu. Việc xóa bộ lọc tự động được thực hiện giống như cách khởi chạy nó.

Bộ lọc nâng cao

Ví dụ về tạo bộ lọc nâng cao - bạn cần chỉ định phạm vi dữ liệu nguồn, bộ lọc và phạm vi đặt dữ liệu được lọc

Tùy chọn lọc thứ hai cung cấp cho người dùng nhiều tùy chọn hơn để chọn dữ liệu cần thiết. Để chạy tùy chọn nâng cao, bạn cần tạo một bản sao của tiêu đề bảng (tức là chỉ cần sao chép tiêu đề). Đây sẽ là phạm vi của các điều kiện. Sau đó, bạn cần điền vào phạm vi này với các tiêu chí lựa chọn. Nhưng ở đây, điều quan trọng là phải tuân thủ các quy tắc: nếu bạn cần chọn các giá trị theo hai tham số (ví dụ: họ và lớp của học sinh), thì các điều kiện được viết thành một dòng; nếu tiêu chí được chọn ở chế độ “HOẶC” (nhãn hiệu ô tô hoặc kích thước động cơ), thì chúng sẽ được ghi ở các dòng khác nhau.

Giả sử có một bảng có 2 cột - tên sản phẩm và số lượng. Tổng cộng có 3 sản phẩm - chuối, cam, quýt và số lượng lần lượt là 10, 20 và 15 miếng. Sau khi sao chép tiêu đề, bạn có thể tạo một điều kiện, chẳng hạn như hiển thị các sản phẩm có số lượng nhỏ hơn hoặc bằng 15. Nghĩa là, dưới tiêu đề được sao chép trong cột “Số lượng”, bạn cần viết<=15. Затем надо запустить расширенный фильтр, указать исходный диапазон (исходная таблица), диапазон условий (таблица, где указано «кол-во <=15») и нажать «ОК». Исходная таблица изменится: теперь тут будут отображены только бананы (10 штук) и мандарины (15 штук).

Vì vậy, nguyên tắc lọc phải rõ ràng. Bằng cách này, bạn có thể chọn bất kỳ phần tử nào từ cơ sở dữ liệu, bất kể nó lớn đến mức nào. Quy trình này sẽ giúp đơn giản hóa công việc của người dùng với lượng dữ liệu lớn.

Để thuận tiện khi làm việc với lượng lớn dữ liệu trong bảng, chúng liên tục cần được sắp xếp theo một tiêu chí nhất định. Ngoài ra, để thực hiện các mục đích cụ thể, đôi khi không cần toàn bộ mảng dữ liệu mà chỉ cần các hàng riêng lẻ. Vì vậy, để không bị nhầm lẫn trong lượng thông tin khổng lồ, một giải pháp hợp lý là sắp xếp dữ liệu và lọc nó khỏi các kết quả khác. Hãy cùng tìm hiểu cách sắp xếp và lọc dữ liệu trong Microsoft Excel.

Sắp xếp là một trong những công cụ tiện lợi nhất khi làm việc trong Microsoft Excel. Sử dụng nó, bạn có thể sắp xếp các hàng của bảng theo thứ tự bảng chữ cái, theo dữ liệu có trong các ô cột.

Bạn có thể sắp xếp dữ liệu trong Microsoft Excel bằng cách sử dụng nút “Sắp xếp và lọc”, nằm trong tab “Trang chủ” trên dải băng trong khối công cụ “Chỉnh sửa”. Nhưng trước tiên, chúng ta cần nhấp vào bất kỳ ô nào của cột mà chúng ta sắp sắp xếp.

Ví dụ: trong bảng bên dưới, nhân viên nên được sắp xếp theo thứ tự bảng chữ cái. Đi tới bất kỳ ô nào trong cột “Tên” và nhấp vào nút “Sắp xếp và lọc”. Để sắp xếp tên theo thứ tự bảng chữ cái, hãy chọn “Sắp xếp từ A đến Z” từ danh sách xuất hiện.

Như bạn có thể thấy, tất cả dữ liệu trong bảng được sắp xếp theo danh sách họ theo thứ tự bảng chữ cái.

Để sắp xếp theo thứ tự ngược lại, trong cùng một menu, chọn nút Sắp xếp từ Z đến A.

Danh sách được xây dựng lại theo thứ tự ngược lại.

Cần lưu ý rằng kiểu sắp xếp này chỉ được chỉ định cho định dạng dữ liệu văn bản. Ví dụ: với định dạng số, việc sắp xếp được chỉ định là “Từ tối thiểu đến tối đa” (và ngược lại) và với định dạng ngày – “Từ cũ đến mới” (và ngược lại).

Sắp xếp tùy chỉnh

Tuy nhiên, như chúng ta thấy, với những kiểu sắp xếp theo một giá trị này, dữ liệu chứa tên của cùng một người được sắp xếp trong phạm vi theo thứ tự ngẫu nhiên.

Nhưng nếu chúng ta muốn sắp xếp tên theo thứ tự bảng chữ cái, nhưng ví dụ nếu tên trùng khớp thì phải đảm bảo dữ liệu được sắp xếp theo ngày tháng? Để thực hiện việc này cũng như sử dụng một số tính năng khác, tất cả đều nằm trong cùng một menu “Sắp xếp và lọc”, chúng ta cần vào mục “Sắp xếp tùy chỉnh…”.

Sau đó, cửa sổ cài đặt sắp xếp sẽ mở ra. Nếu bảng của bạn có tiêu đề thì hãy đảm bảo rằng trong cửa sổ này có dấu kiểm bên cạnh tùy chọn “Dữ liệu của tôi chứa tiêu đề”.

Trong trường “Cột”, cho biết tên của cột mà việc sắp xếp sẽ được thực hiện. Trong trường hợp của chúng tôi, đây là cột “Tên”. Trường "Sắp xếp" cho biết loại nội dung sẽ được sắp xếp theo. Có bốn lựa chọn:

Giá trị;
Màu tế bào;
Màu chữ;
Biểu tượng ô.

Tuy nhiên, trong phần lớn các trường hợp, mục “Giá trị” được sử dụng. Nó được đặt theo mặc định. Trong trường hợp của chúng tôi, chúng tôi cũng sẽ sử dụng điểm cụ thể này.

Trong cột “Thứ tự”, chúng ta cần chỉ ra thứ tự dữ liệu sẽ được sắp xếp: “Từ A đến Z” hoặc ngược lại. Chọn giá trị “Từ A đến Z”.

Như vậy chúng ta đã thiết lập sắp xếp theo một trong các cột. Để định cấu hình sắp xếp theo cột khác, hãy nhấp vào nút “Thêm cấp độ”.

Một tập hợp các trường khác xuất hiện, bạn phải điền vào để sắp xếp theo cột khác. Trong trường hợp của chúng tôi, theo cột “Ngày”. Vì định dạng ngày được đặt trong các ô này, nên trong trường “Thứ tự”, chúng tôi đặt các giá trị không phải “Từ A đến Z”, mà là “Từ cũ đến mới” hoặc “Từ mới đến cũ”.

Theo cách tương tự, trong cửa sổ này, bạn có thể định cấu hình, nếu cần, sắp xếp theo các cột khác theo thứ tự ưu tiên. Khi tất cả các cài đặt hoàn tất, hãy nhấp vào nút “OK”.

Như bạn có thể thấy, bây giờ trong bảng của chúng tôi, tất cả dữ liệu được sắp xếp, trước hết là theo tên nhân viên, sau đó là theo ngày thanh toán.

Nhưng đó không phải là tất cả khả năng sắp xếp tùy chỉnh. Nếu muốn, trong cửa sổ này, bạn có thể định cấu hình sắp xếp không phải theo cột mà theo hàng. Để thực hiện việc này, hãy nhấp vào nút “Tùy chọn”.

Trong cửa sổ tùy chọn sắp xếp mở ra, hãy di chuyển nút chuyển từ vị trí “Hàng phạm vi” sang vị trí “Cột phạm vi”. Bấm vào nút “OK”.

Bây giờ, bằng cách tương tự với ví dụ trước, bạn có thể nhập dữ liệu để sắp xếp. Nhập dữ liệu và nhấp vào nút “OK”.

Như bạn có thể thấy, sau đó, các cột đã được hoán đổi vị trí theo các tham số đã nhập.

Tất nhiên, đối với bảng của chúng tôi, lấy làm ví dụ, việc sử dụng cách sắp xếp bằng cách thay đổi vị trí của các cột không đặc biệt hữu ích, nhưng đối với một số bảng khác, kiểu sắp xếp này có thể rất thích hợp.

Lọc

Ngoài ra Microsoft Excel còn có chức năng lọc dữ liệu. Nó cho phép bạn chỉ hiển thị những dữ liệu mà bạn cho là cần thiết và ẩn phần còn lại. Nếu cần, dữ liệu ẩn luôn có thể được đưa về chế độ hiển thị.

Để sử dụng chức năng này, hãy đứng trên bất kỳ ô nào trong bảng (tốt nhất là ở tiêu đề), nhấp lại vào nút “Sắp xếp và lọc” trong khối công cụ “Chỉnh sửa”. Tuy nhiên, lần này, trong menu xuất hiện, hãy chọn mục “Bộ lọc”. Bạn cũng có thể chỉ cần nhấn tổ hợp phím Ctrl+Shift+L thay vì những hành động này.

Như bạn có thể thấy, trong các ô có tên của tất cả các cột, một biểu tượng xuất hiện dưới dạng hình vuông, trong đó có ghi một hình tam giác ngược.

Nhấp vào biểu tượng này trong cột mà chúng tôi sẽ lọc. Trong trường hợp của chúng tôi, chúng tôi quyết định lọc theo tên. Ví dụ: chúng ta chỉ cần để lại dữ liệu của nhân viên Nikolaev. Do đó, chúng tôi xóa dấu kiểm khỏi tên của tất cả các nhân viên khác.

Khi thủ tục hoàn tất, hãy nhấp vào nút “OK”.

Như bạn có thể thấy, chỉ những hàng có tên nhân viên của Nikolaev vẫn còn trong bảng.

Hãy phức tạp hóa nhiệm vụ và chỉ để lại trong bảng những dữ liệu liên quan đến Nikolaev trong quý 3 năm 2016. Để thực hiện việc này, hãy nhấp vào biểu tượng trong ô “Ngày”. Trong danh sách mở ra, hãy bỏ chọn các tháng “Tháng 5”, “Tháng 6” và “Tháng 10”, vì chúng không thuộc quý thứ ba và nhấp vào nút “OK”.

Như bạn có thể thấy, chỉ còn lại dữ liệu chúng tôi cần.

Để xóa bộ lọc cho một cột cụ thể và hiển thị dữ liệu ẩn, hãy nhấp lại vào biểu tượng nằm trong ô có tiêu đề của cột này. Trong menu mở ra, nhấp vào mục “Xóa bộ lọc khỏi…”.

Nếu bạn muốn đặt lại toàn bộ bộ lọc cho bảng, thì bạn cần nhấp vào nút “Sắp xếp và lọc” trên dải băng và chọn mục “Xóa”.

Nếu bạn cần loại bỏ hoàn toàn bộ lọc, thì cũng như khi khởi động nó, bạn nên chọn mục “Bộ lọc” trong cùng một menu hoặc nhập phím tắt Ctrl+Shift+L.

Ngoài ra, cần lưu ý rằng sau khi chúng tôi kích hoạt chức năng “Bộ lọc”, khi bạn nhấp vào biểu tượng tương ứng trong các ô của tiêu đề bảng, các chức năng sắp xếp mà chúng tôi đã thảo luận ở trên sẽ có sẵn trong menu xuất hiện: “Sắp xếp từ A đến Z”, “Sắp xếp từ Z đến A” và “Sắp xếp theo màu sắc”.

Bàn thông minh

Tính năng sắp xếp và lọc cũng có thể được kích hoạt bằng cách biến vùng dữ liệu bạn đang làm việc thành cái gọi là “bảng thông minh”.

Có hai cách để tạo một bảng thông minh. Để sử dụng cái đầu tiên trong số chúng, hãy chọn toàn bộ khu vực bảng và trong tab “Trang chủ”, nhấp vào nút “Định dạng dưới dạng bảng” trên dải băng. Nút này nằm trong khối công cụ “Styles”.

Sau đó, một hộp thoại sẽ mở ra trong đó bạn có thể thay đổi tọa độ của bảng. Tuy nhiên, nếu trước đó bạn đã chọn đúng khu vực thì bạn không cần phải làm gì khác. Điều chính là đảm bảo rằng có dấu kiểm bên cạnh tham số “Bảng có tiêu đề”. Tiếp theo, chỉ cần nhấp vào nút “OK”.

Nếu bạn quyết định sử dụng phương pháp thứ hai, thì bạn cũng cần chọn toàn bộ khu vực bảng, nhưng lần này hãy chuyển đến tab “Chèn”. Khi ở đây, trên dải băng trong khối công cụ “Bảng”, hãy nhấp vào nút “Bảng”.

Sau đó, giống như lần trước, một cửa sổ sẽ mở ra nơi bạn có thể điều chỉnh tọa độ của vị trí bảng. Bấm vào nút “OK”.

Bất kể bạn sử dụng phương pháp nào để tạo “bảng thông minh”, bạn sẽ có một bảng trong các ô tiêu đề mà các biểu tượng bộ lọc mà chúng tôi đã mô tả trước đó sẽ được cài đặt.

Khi bạn nhấp vào biểu tượng này, tất cả các chức năng tương tự sẽ có sẵn như khi bạn khởi chạy bộ lọc theo cách tiêu chuẩn thông qua nút “Sắp xếp và Lọc”.

Như bạn có thể thấy, các công cụ sắp xếp và lọc, khi được sử dụng đúng cách, có thể giúp người dùng làm việc với bảng dễ dàng hơn nhiều. Câu hỏi về việc sử dụng chúng trở nên đặc biệt phù hợp nếu bảng chứa một mảng dữ liệu rất lớn.