Mẫu lệnh filter trong excel năm 2021

Mẫu lệnh filter trong excel năm 2021

Lệnh filter trong excel là một trong những lệnh rất hữu dụng đối với bất kỳ ngành nghề nào khi phải sử dụng excel, nhất là đối với mấy bạn học sinh và sinh viên khi lần đầu tiếp xúc với excel. Để có thể thành thạo sử dụng lệnh filter thì các bạn hãy tham khảo ngay bài viết dưới đây nhé. Những thông tin này đã được mình tổng hợp từ rất nhiều nguồn uy tín hiện nay.

 

Hướng dẫn dùng lệnh filter trong excel

# 1 – bật hoặc tắt bộ lọc
Ctrl + Shift + L là phím tắt để bật / tắt bộ lọc. Bạn có thể thấy lối tắt này bằng cách chuyển đến tab Dữ liệu trên Ruy-băng và di chuột qua nút Bộ lọc. Chú giải công cụ xuất hiện bên dưới nút và một phím tắt xuất hiện trên dòng trên cùng. Điều này hoạt động với rất nhiều nút trên ruy-băng và là một cách tuyệt vời để học các phím tắt. Xem hình ảnh bên dưới.

Để áp dụng bộ lọc thả xuống, trước tiên bạn cần chọn một ô trong phạm vi dữ liệu. Nếu phạm vi dữ liệu của bạn chứa các cột hoặc hàng trống, cách tốt nhất là chọn toàn bộ phạm vi ô.
Sau khi chọn các ô dữ liệu, nhấn Ctrl + Shift + L để áp dụng bộ lọc. Các menu thả xuống của bộ lọc sẽ xuất hiện trong thanh tiêu đề của dữ liệu của bạn, như được hiển thị trong hình ảnh bên dưới.

# 2 Trình đơn thả xuống bộ lọc hiển thị
Alt + Mũi tên Xuống là một phím tắt để mở menu thả xuống. Để sử dụng phím tắt này:
1. Chọn một ô trong thanh tiêu đề. Ô phải chứa biểu tượng bộ lọc thả xuống.
2. Nhấn và giữ phím Alt, sau đó nhấn phím mũi tên xuống trên bàn phím để mở menu bộ lọc.

Khi menu thả xuống mở ra, có nhiều phím tắt có thể được áp dụng cho menu đó. Các nhãn này được giải thích bên dưới.
Mẹo bổ sung: nếu bạn đang sử dụng bảng tính Excel và tôi thực sự khuyên bạn nên làm như vậy, bạn có thể nhấn Shift + Alt + Mũi tên Xuống từ bất kỳ ô nào trong bảng để mở menu thả xuống bộ lọc cho cột đó.

Phím tắt này đã được giới thiệu trong Excel 2010 dành cho Windows và hoạt động trong tất cả các phiên bản mới hơn.
# 3 các chữ cái được gạch chân và các phím mũi tên
Các chữ cái được gạch chân trong menu thả xuống là phím tắt cho mỗi lệnh. Ví dụ: nhấn chữ “S” trên bàn phím của bạn sẽ sắp xếp cột từ A đến Z. Trước tiên, bạn phải nhấn Alt + Mũi tên xuống để hiển thị menu thả xuống. Vì vậy, đây là các phím tắt hoàn chỉnh cho menu thả xuống của bộ lọc:
• Alt + mũi tên xuống + S – sắp xếp từ A đến Z
• Alt + mũi tên xuống + O – sắp xếp từ Z đến A
• Alt + Mũi tên Xuống + T – Menu con Sắp xếp Theo Màu sắc.
• Alt + mũi tên xuống + I – menu con “Lọc theo màu”.
• Alt + Mũi tên Xuống + F – Menu con bộ lọc Văn bản hoặc Ngày.

# 4 Kiểm tra / Bỏ chọn Các mục Bộ lọc
Các phím mũi tên lên và xuống di chuyển qua các mục trong menu thả xuống. Bạn có thể nhấn phím Enter để thực hiện hành động này. Thao tác này yêu cầu mở menu thả xuống bằng cách nhấn Alt + Mũi tên xuống trước tiên. Xem hình ảnh ở # 3 ở trên để biết chi tiết.
Bắt đầu từ Excel 2007, danh sách các mục duy nhất xuất hiện ở cuối trình đơn thả xuống bộ lọc với các hộp kiểm bên cạnh mỗi mục. Bạn có thể sử dụng các phím mũi tên lên / xuống để chọn các mục này trong danh sách. Khi một mục được chọn, nhấn phím cách đặt / bỏ chọn hộp kiểm. Sau đó nhấn Enter để áp dụng bộ lọc.

# 5 Hộp tìm kiếm – YÊU THÍCH mới của tôi
Bắt đầu từ Excel 2010, một hộp tìm kiếm đã được thêm vào menu thả xuống của bộ lọc. Người dùng Excel 2011 cho Mac cũng nhận được tính năng này.
Hộp tìm kiếm này cho phép bạn nhập tìm kiếm và thu hẹp kết quả của các mục bộ lọc trong danh sách bên dưới nó.

Xem thêm:  Cách tải game plants vs zombies 2 về máy tính -tải và cài đặt

Khi menu thả xuống bộ lọc mở ra, bạn có thể nhấn vào chữ cái “E” trên bàn phím để chuyển đến hộp tìm kiếm. Thao tác này sẽ đặt con trỏ của bạn vào hộp tìm kiếm và bạn có thể bắt đầu nhập cụm từ tìm kiếm của mình.
Alt + Down + Arrow + E là phím tắt để mở bộ lọc, menu thả xuống và chuyển trực tiếp đến hộp tìm kiếm.
Tôi vừa xem xét lối tắt này và đó là lối tắt yêu thích mới của tôi vì nó cho phép bạn nhanh chóng nhập và lọc chính xác những gì bạn đang tìm kiếm trong danh sách. Trước khi tìm hiểu về điều này, tôi đã sử dụng phím mũi tên xuống để đến hộp tìm kiếm. Điều này yêu cầu tôi phải nhấn phím mũi tên xuống 7 lần để chuyển đến hộp tìm kiếm. Bây giờ tôi có thể làm tương tự trong một bước bằng cách nhấp vào ký tự “E”. Thật là tiết kiệm thời gian!
Phần thưởng – chuyển đến danh sách các hộp kiểm
Nếu bạn muốn đi đến danh sách các hộp kiểm bên dưới hộp tìm kiếm, bạn có thể chỉ cần nhấn Tab sau Alt + Mũi tên Xuống, E.

Vì vậy, phím tắt đầy đủ là Alt + Mũi tên Xuống, E, Tab (hoặc Mũi tên Xuống).
Bạn có thể sử dụng mũi tên xuống hoặc phím tab. Mũi tên xuống có lẽ sẽ dễ dàng hơn, vì bạn chỉ cần nhấn vào mũi tên xuống để mở menu bộ lọc.
Tuy nhiên, nếu cột chứa ngày tháng, bạn cần nhấn Tab hai lần. Có một menu thả xuống bổ sung để tìm kiếm theo năm, tháng và ngày. Mũi tên xuống sẽ mở menu này và chọn từng mục.
Vì vậy, có lẽ tốt nhất bạn nên làm quen với việc sử dụng Tab vì nó hoạt động trong mọi tình huống.
Sau khi bạn nhấp vào phím tắt, tiêu điểm sẽ nằm trên hộp kiểm (Chọn Tất cả) trong danh sách. Sau đó, bạn có thể sử dụng các phím tắt sau để điều hướng và chọn các hộp.
• Space bar kiểm tra / bỏ chọn các mục.
• Mũi tên lên / xuống để chọn các mục.
• Chuyển đổi cuối cùng sang p

 

Mẫu lệnh filter trong excel

Hàm FILTER trong Excel được sử dụng để lọc một loạt dữ liệu dựa trên tiêu chí bạn chỉ định.
Hàm thuộc loại hàm mảng động. Kết quả là một mảng giá trị được tự động trải rộng trên phạm vi ô, bắt đầu bằng ô mà bạn nhập công thức.
Cú pháp của hàm FILTER như sau:
FILTER (mảng; bao gồm; [if_empty])
Ở đâu:
• Mảng (bắt buộc) – Dải ô hoặc mảng giá trị mà bạn muốn lọc.
• Bao gồm (bắt buộc) – các tiêu chí được trình bày dưới dạng một mảng logic (các giá trị TRUE và FALSE).
Chiều cao của nó (khi dữ liệu ở trong cột) hoặc chiều rộng (khi dữ liệu ở trong hàng) phải bằng chiều cao của đối số mảng.
• If_empty (tùy chọn): Giá trị được trả về nếu không có đầu vào nào phù hợp với tiêu chí.
Ghi chú. FILTER hiện chỉ khả dụng với đăng ký Office 365. Nó không được hỗ trợ trong Excel 2019, Excel 2016 và các phiên bản cũ hơn.
Công thức bộ lọc Excel cơ bản
Hãy bắt đầu bằng cách xem xét một vài trường hợp rất đơn giản để hiểu rõ hơn về cách hoạt động của công thức lọc dữ liệu trong Excel.
Từ tập dữ liệu sau, giả sử bạn muốn truy xuất các bản ghi có giá trị cụ thể trong cột Nhóm, giả sử nhóm C. Để thực hiện việc này, chúng tôi chuyển biểu thức B2: B13 = “C” vào đối số include, tạo mảng boolean bắt buộc, trong đó TRUE khớp với các giá trị “C”.
= FILTER (A2: C13, B2: B13 = “C”, “Không có kết quả”)
Trong thực tế, sẽ thuận tiện hơn khi nhập tiêu chí vào một ô riêng biệt. Ví dụ F1 và sử dụng tham chiếu ô thay vì mã hóa giá trị trực tiếp trong công thức:
= FILTER (A2: C13, B2: B13 = F1, “Không có kết quả”)
Không giống như hàm lọc trong Excel, hàm không thực hiện bất kỳ thay đổi nào đối với dữ liệu gốc. Truy xuất các bản ghi đã lọc vào cái gọi là phạm vi tràn (E4: G7 trong ảnh chụp màn hình bên dưới), bắt đầu từ ô nơi công thức được nhập:

Xem thêm:  Cài đặt mathtype trong word 2010 -29 Bước chi tiết nhất + Link tải mới nhất

Nếu không có bản ghi nào phù hợp với tiêu chí đã chỉ định, công thức trả về giá trị mà bạn đã nhập vào đối số if_empty, trong ví dụ này là “Không có kết quả”:

Nếu bạn không muốn trả về bất kỳ thứ gì trong trường hợp này, hãy cung cấp một chuỗi trống (“”) làm đối số cuối cùng:
= BỘ LỌC (A2: C13; B2: B13 = F1; “”)
Nếu dữ liệu của bạn nằm ngang từ trái sang phải, như được hiển thị trong ảnh chụp màn hình bên dưới, chức năng FILTER cũng sẽ hoạt động tốt. Chỉ cần nhớ xác định các phạm vi thích hợp cho mảng và bao gồm các đối số để mảng ban đầu và mảng boolean có cùng chiều rộng:
= FILTER (B2: M4, B3: M3 = B7, “Không có kết quả”)

Hàm FILTER trong Excel – ghi chú sử dụng
Để lọc hiệu quả trong Excel bằng cách sử dụng công thức, có một số điểm quan trọng cần ghi nhớ:
• Chức năng FILTER tự động phân phối kết quả trên trang tính theo chiều dọc hoặc chiều ngang, tùy thuộc vào cách tổ chức dữ liệu nguồn. Vì vậy, hãy đảm bảo bạn luôn có đủ ô trống ở bên dưới và bên phải, nếu không bạn sẽ gặp lỗi #SPILL.
• Kết quả LỌC trong Excel là động, có nghĩa là chúng được cập nhật tự động khi các giá trị trong tập dữ liệu ban đầu thay đổi. Tuy nhiên, phạm vi được chỉ định cho đối số mảng không được cập nhật khi các bản ghi mới được thêm vào dữ liệu ban đầu. Nếu bạn muốn mảng tự động thay đổi kích thước, hãy chuyển đổi nó sang bảng tính Excel và tạo các công thức tham chiếu có cấu trúc hoặc tạo một phạm vi động được đặt tên.
Cách lọc trong Excel – Ví dụ về Công thức
Bây giờ bạn đã biết cách hoạt động của công thức bộ lọc Excel cơ bản, đã đến lúc có một số ý tưởng về cách bạn có thể mở rộng nó để giải quyết các vấn đề phức tạp hơn.
Lọc theo nhiều tiêu chí (logic AND)
Để lọc dữ liệu dựa trên nhiều tiêu chí, hãy cung cấp hai hoặc nhiều biểu thức Boolean cho đối số include:
FILTER (mảng; (rank1 = tiêu chí1) * (rank2 = tiêu chí2), “Không có kết quả”)
Phép toán nhân xử lý mảng bằng cách sử dụng logic AND, đảm bảo rằng chỉ các bản ghi đáp ứng tất cả các tiêu chí mới được trả về. Về mặt kỹ thuật, nó hoạt động như thế này:
Kết quả của mỗi biểu thức boolean là một mảng các giá trị boolean, trong đó TRUE bằng 1 và FALSE bằng 0. Sau đó, các phần tử của tất cả các mảng được nhân ở các vị trí giống nhau. Vì phép nhân với 0 luôn trả về 0, nên chỉ các phần tử có tất cả các tiêu chí là TRUE mới được đưa vào mảng kết quả và do đó chỉ những phần tử đó mới được truy xuất.
Các ví dụ sau đây chứng minh công thức linh hoạt này đang hoạt động.

 

Cú pháp lệnh filter trong excel

Sau đây là cú pháp của hàm FILTER:
= FILTER (mảng; bao gồm; [if_empty])
• mảng: đây là một dải ô mà bạn có dữ liệu và bạn muốn lọc một số dữ liệu từ nó.
• bao gồm: Đây là điều kiện cho hàm biết những bản ghi nào cần lọc.
• [if_empty] là một đối số tùy chọn, nơi bạn có thể chỉ định những gì sẽ trả về nếu hàm FILTER không tìm thấy kết quả. Theo mặc định (trừ khi có ghi chú khác) #CALC được trả về! sai lầm
Bây giờ chúng ta hãy xem xét một số ví dụ tuyệt vời về các chức năng lọc và những thứ bạn có thể làm mà trước đây khá phức tạp khi không có chúng.
Nhấp vào đây để tải xuống tệp mẫu và tiếp tục
Ví dụ 1: lọc dữ liệu theo tiêu chí (vùng)
Giả sử bạn có một tập dữ liệu được hiển thị bên dưới và bạn muốn lọc tất cả các bản ghi chỉ dành cho Hoa Kỳ.

Dưới đây là công thức FILTER sẽ thực hiện việc này:
= BỘ LỌC ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = “US”)

Trong công thức trên, tập dữ liệu được sử dụng như một mảng và điều kiện là $ B $ 2: $ B $ 11 = “US”.
Điều kiện này sẽ khiến hàm FILTER kiểm tra mọi ô trong cột B (ô có vùng) và chỉ những bản ghi phù hợp với tiêu chí này mới được lọc.
Cũng trong ví dụ này, tôi có dữ liệu gốc và dữ liệu đã lọc trong một trang tính, nhưng bạn cũng có thể lưu trữ nó trên các trang tính riêng biệt hoặc thậm chí là sổ làm việc.
Hàm bộ lọc trả về một kết quả, là một mảng động (có nghĩa là thay vì trả về một giá trị, nó sẽ trả về một mảng truyền đến các ô khác).
Để điều này hoạt động, bạn phải có một khu vực mà kết quả sẽ trống. Trong bất kỳ ô nào trong khu vực này (trong ví dụ này là E2: G5) bạn đã có ô nào đó, hàm sẽ cung cấp cho bạn lỗi #SPILL.
Ngoài ra, vì nó là một mảng động, bạn không thể thay đổi một phần của kết quả. Bạn có thể xóa toàn bộ phạm vi có kết quả hoặc ô E2 (nơi công thức được nhập). Cả hai sẽ loại bỏ toàn bộ ma trận kết quả. Nhưng bạn không thể thay đổi bất kỳ ô nào (hoặc xóa nó).
Trong công thức trên, tôi đã mã hóa cứng giá trị vùng, nhưng bạn cũng có thể đặt nó vào một ô và sau đó tham chiếu ô có giá trị vùng đó.
Ví dụ, trong ví dụ dưới đây, tôi có giá trị vùng trong ô I2 và sau đó nó được chỉ định trong công thức:
= BỘ LỌC ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = I1)
Điều này làm cho công thức hữu ích hơn và bây giờ bạn có thể chỉ cần thay đổi giá trị vùng trong ô I2 và bộ lọc sẽ tự động thay đổi.
Bạn cũng có thể có một danh sách thả xuống trong ô I2 nơi bạn có thể chỉ cần thực hiện lựa chọn và nó sẽ cập nhật ngay lập tức dữ liệu đã lọc.
Ví dụ 2: lọc dữ liệu theo tiêu chí (cộng hoặc trừ)
Bạn cũng có thể sử dụng toán tử so sánh trong hàm bộ lọc và truy xuất tất cả các bản ghi lớn hơn hoặc nhỏ hơn một giá trị nhất định.
Ví dụ: giả sử bạn có tập dữ liệu được hiển thị bên dưới và bạn muốn lọc ra tất cả các bản ghi có doanh số bán hàng lớn hơn 10.000.

Xem thêm:  Microsoft office 2013 fshare - Hướng dẫn chi tiết nhất tải và cài đặt

Điều này có thể được thực hiện bằng cách sử dụng công thức sau:
= BỘ LỌC ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000))

Đối số mảng tham chiếu đến toàn bộ tập dữ liệu và điều kiện trong trường hợp này là ($ C $ 2: $ C $ 11> 10.000).
Công thức kiểm tra mỗi bản ghi để tìm một giá trị trong cột C. Nếu giá trị lớn hơn 10.000, nó sẽ được lọc, nếu không, nó sẽ bị bỏ qua.
Nếu bạn muốn nhận được tất cả các bản ghi nhỏ hơn 10.000, bạn có thể sử dụng công thức sau:
= BỘ LỌC ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11 <10000))
Bạn cũng có thể sáng tạo hơn với công thức FILTER. Ví dụ: nếu bạn muốn lọc ba bản ghi đầu tiên dựa trên giá trị bán hàng, bạn có thể sử dụng công thức sau:
= BỘ LỌC ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = LARGE (C2: C11,3)))

Công thức trên sử dụng hàm BIG để nhận giá trị lớn thứ ba trong tập dữ liệu. Giá trị này sau đó được sử dụng trong tiêu chí LỌC để truy xuất tất cả các bản ghi trong đó giá trị bán hàng lớn hơn hoặc bằng giá trị cao thứ ba.
Nhấp vào đây để tải xuống tệp mẫu và tiếp tục
Ví dụ 3: lọc dữ liệu theo nhiều tiêu chí (Y)
Giả sử bạn có tập dữ liệu sau và bạn muốn lọc ra tất cả các bản ghi từ Hoa Kỳ có giá trị bán hàng lớn hơn 10.000.

Đây là điều kiện VÀ mà bạn cần kiểm tra hai điều: khu vực cần Mỹ và doanh số bán hàng phải lớn hơn 10.000. Nếu chỉ đáp ứng một điều kiện thì không nên lọc kết quả.
Dưới đây là công thức LỌC để lọc các bản ghi có Hoa Kỳ là khu vực và doanh số bán hàng trên 10.000:
= BỘ LỌC ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = “US”) * ($ C $ 2: $ C $ 11> 10.000))

123doc