hàm lọc dữ liệu có điều kiện trong excel

Đôi khi, bạn phải dùng hàm thanh lọc tài liệu với ĐK vô Excel nhằm đo lường và tính toán hoặc thanh lọc bảng tính cho 1 đối tượng người tiêu dùng rõ ràng. Lúc tê liệt, chúng ta có thể tìm hiểu thêm nội dung bài viết tiếp sau đây nhằm nằm trong Hoàng Hà Mobile lần hiểu cách sử dụng cỗ thanh lọc FILTER, hàm FILTER và cơ hội xử lý một vài lỗi thông thường gặp gỡ khi thanh lọc tài liệu vô Excel.

Hàm thanh lọc tài liệu với ĐK vô Excel thông thường được dùng nhằm thanh lọc tài liệu từ là một bảng tính hoặc phạm vi tài liệu dựa vào những ĐK rõ ràng. Dưới đó là một vài tình huống chúng ta có thể tiếp tục sử dụng cỗ thanh lọc hoặc hàm này:

Bạn đang xem: hàm lọc dữ liệu có điều kiện trong excel

ham-loc-du-lieu-co-dieu-kien-trong-excel-1

  • Lọc tài liệu đáp ứng nhu cầu một hoặc nhiều điều kiện: Khi mình thích hiển thị những tài liệu vừa lòng một hoặc nhiều ĐK rõ ràng, chúng ta có thể dùng hàm thanh lọc này. Chẳng hạn như thanh lọc list người sử dụng có tính tuổi hạc kể từ 30 trở lên trên với số chi phí mua sắm chọn lựa bên trên 10 triệu đồng.
  • Tạo bảng dữ liệu: Từ một bảng tính rộng lớn, chúng ta có thể dùng hàm thanh lọc tài liệu với ĐK vô Excel nhằm tổ hợp trở thành một bảng tính nhỏ rộng lớn. Chẳng hạn như chúng ta có thể tạo ra những bảng bổng riêng rẽ cho từng nhân viên cấp dưới kể từ bảng bổng cộng đồng của một văn phòng/công ty.
  • Lọc tài liệu với năng lực thay cho thay đổi dựa vào ĐK hoặc lựa lựa chọn tùy chỉnh: quý khách hàng hoàn toàn có thể tạo ra một bảng hoặc biểu loại động dựa vào ĐK thay cho thay đổi. Chẳng hạn như tạo ra bảng hiển thị chỉ số sản phẩm của những thành phầm và được chấp nhận người tiêu dùng lựa chọn loại thành phầm rõ ràng kể từ list thả xuống nhằm coi sản phẩm cụ thể.
  • Tính toán bên trên tài liệu vẫn lọc: Tương tự động, chúng ta có thể thanh lọc tài liệu và tiến hành luật lệ tính như tính tổng, khoảng, min/max… Chẳng hạn như sẽ tạo bảng đo đếm lệch giá của từng sản phẩm, chúng ta có thể thanh lọc và tính tổng theo đòi từng thành phầm riêng rẽ.

Cách người sử dụng cỗ thanh lọc FILTER vô Excel

Để thanh lọc tài liệu, chúng ta có thể dùng hàm thanh lọc tài liệu với ĐK vô Excel – FILTER, hoặc dùng cỗ thanh lọc FILTER có trước bên trên thanh dụng cụ của Excel.

Trước khi tìm hiểu cách sử dụng hàm FILTER, tất cả chúng ta tiếp tục lần hiểu về cỗ thanh lọc FILTER. Sở thanh lọc FILTER vô Excel được chấp nhận người tiêu dùng thanh lọc tài liệu đáp ứng nhu cầu một hoặc nhiều ĐK rõ ràng. Sau khi vận dụng cỗ thanh lọc, toàn bộ tài liệu vừa lòng ĐK tiếp tục hiển thị, còn những tài liệu sót lại sẽ ảnh hưởng ẩn cút.

Trong Excel với 2 tính năng thanh lọc cơ bạn dạng là:

  • Auto Filter: Sở thanh lọc đem toan.
  • Advanced Filter: Sở thanh lọc nâng lên.

Đầu tiên, bạn phải tạo ra cỗ thanh lọc FILTER như sau:

  • Bước 1: Nhập tài liệu hoặc ngỏ bảng tính có trước tuy nhiên bạn phải thanh lọc bên trên Excel.

ham-loc-du-lieu-co-dieu-kien-trong-excel-2

  • Bước 2: Chọn vùng chứa chấp tài liệu bằng phương pháp bôi thâm toàn cỗ tài liệu của bảng tính, hoặc lựa chọn mặt hàng chi tiêu nhằm tuy nhiên mình thích thanh lọc.
  • Bước 3: quý khách hàng nhấn vào tab trang chủ, tiếp sau đó click lựa chọn Sort & Filter rồi nhấn vào Filter. Trong khi, các bạn cũng hoàn toàn có thể demo cách tiếp này là bên trên tab Data, các bạn lựa chọn Filter.

Sử dụng Auto Filter

Để dùng hàm thanh lọc tài liệu với ĐK vô Excel với cỗ thanh lọc đem toan, các bạn tiến hành quá trình sau:

Bước 1: Click vô vết mũi thương hiệu nằm ở lân cận mục mình thích thanh lọc.

ham-loc-du-lieu-co-dieu-kien-trong-excel-3

Bước 2: Bây giờ, các bạn thứu tự bấm lựa chọn những tiêu chuẩn tuy nhiên mình thích thanh lọc, ví dụ như tick lựa chọn dù “Nam” nhằm thanh lọc những người dân với nam nữ là phái mạnh.

ham-loc-du-lieu-co-dieu-kien-trong-excel-4

Bước 3: Sau khi lựa chọn tiêu chuẩn xong xuôi, các bạn bấm OK và khối hệ thống tiếp tục chính thức thanh lọc. Những tài liệu đáp ứng nhu cầu tiêu chuẩn được lựa chọn sẽ tiến hành hiển thị, trong những khi tê liệt, những tài liệu ko vừa lòng ĐK sẽ ảnh hưởng ẩn cút.

Sử dụng Advanced Filter

Với cỗ thanh lọc tài liệu nâng lên, các bạn tiến hành quá trình sau:

Bước 1: Trước tiên, bạn phải tạo ra một bảng tiêu chuẩn thanh lọc mới mẻ. Cần chú ý là title của những tiêu chuẩn dùng để làm thanh lọc này cần trùng với title các bạn vẫn bịa vô bảng tài liệu.

ham-loc-du-lieu-co-dieu-kien-trong-excel-5

Bước 2: quý khách hàng nhấn vào mục Data, tiếp sau đó lựa chọn Sort & Filter rồi click vô Advanced.

ham-loc-du-lieu-co-dieu-kien-trong-excel-6

Bước 3: Tại trên đây, bạn phải quan hoài cho tới một vài thông số kỹ thuật thanh lọc như:

  • Action: Tại trên đây, bạn phải xác lập được địa điểm sản phẩm tài liệu sau khoản thời gian thanh lọc được xuất là ở đâu. Nếu lựa chọn [Filter the list, in-place], sản phẩm sẽ tiến hành xuất và hiển thị vô bảng tài liệu được thanh lọc, còn [Copy to lớn another location] được hiểu là sự hiển thị tài liệu ở một địa điểm không giống vô trang tính.
  • List Range: Khu vực được xác lập nhằm thanh lọc tài liệu.
  • Criteria range: Bảng chứa chấp những tiêu chuẩn (điều kiện) nhằm thanh lọc.
  • Copy to: Nếu như các bạn vẫn lựa chọn [Copy to lớn another location] ở mục kích hoạt như vẫn kể phía trên, bạn phải cung ứng địa điểm xuất và hiển thị sản phẩm tài liệu thanh lọc rõ ràng bên trên trên đây.

ham-loc-du-lieu-co-dieu-kien-trong-excel-7

Bước 4: Bấm OK và sản phẩm tiếp tục hiển thị trong tích tắc theo như đúng ĐK các bạn vẫn đề ra.

ham-loc-du-lieu-co-dieu-kien-trong-excel-8

Cách người sử dụng FILTER – Hàm thanh lọc tài liệu với ĐK vô Excel

Bên cạnh cỗ thanh lọc FILTER, các bạn cũng hoàn toàn có thể dùng một hàm thanh lọc tài liệu với ĐK vô Excel mang tên tương tự động là FILTER nhằm lần tìm tòi và thanh lọc tài liệu dựa vào 1 hoặc nhiều ĐK được xác lập lúc đầu. Tuy nhiên, cần thiết chú ý là hàm này hiện tại mới chỉ tương hỗ cho tới Excel 365.

Công thức của hàm FILTER được quy toan là:

=FILTER(array,include,if_empty)

ham-loc-du-lieu-co-dieu-kien-trong-excel-9

Trong đó:

  • Array là nhân tố cần phải với, bên trên trên đây bạn phải cung ứng phạm vi hoặc mảng tài liệu lúc đầu tuy nhiên mình thích lọc
  • Include cũng là 1 trong những bộ phận cần, dùng để làm xác lập ĐK và cột tài liệu mình thích thanh lọc.
  • If_empty là 1 trong những tùy lựa chọn, được chấp nhận các bạn hướng dẫn và chỉ định SERP trong mỗi tình huống không tồn tại ngẫu nhiên độ quý hiếm nào là vừa lòng những ĐK được đề ra bên trên. Nếu quăng quật trống trải độ quý hiếm này, SERP tiếp tục là 1 trong những vùng trống trải.

Lưu ý cần phải biết khi dùng hàm thanh lọc tài liệu với ĐK vô Excel

Khi dùng hàm FILTER, bạn phải chú ý một vài điều sau:

ham-loc-du-lieu-co-dieu-kien-trong-excel-10
Hàm FILTER mới chỉ tương hỗ cho tới phiên bạn dạng Microsoft Excel 365
  • Hàm FILTER mới chỉ tương hỗ cho tới phiên bạn dạng Microsoft Excel 365.
  • Trong [Array], hàm ko đòi hỏi cần phải với mặt hàng title.
  • Trong [Include], bạn phải đảm nói rằng thông số này chứa chấp số hàng/số cột ứng với phạm vi/mảng tài liệu mình thích thanh lọc.
  • Sau khi gõ cú pháp và bấm Enter, sản phẩm tiếp tục hiển thị bên dưới dạng mảng tài liệu và ko đi kèm theo với format của chống tài liệu gốc được dùng để làm thanh lọc.

Một số lỗi thông thường gặp gỡ khi dùng hàm thanh lọc tài liệu với ĐK vô Excel

Trong một vài tình huống, chúng ta có thể tiếp tục phát hiện một vài lỗi khi dùng hàm FILTER. Tuy nhiên, các bạn không cần thiết phải quá phiền lòng, hãy tìm hiểu thêm ngay lập tức một vài lỗi thông thường gặp gỡ, vẹn toàn nhân và cơ hội xử lý được share bên trên phần này của bài xích viết:

Xem thêm: các câu ca dao tục ngữ

Lỗi #CALC!

Nếu như bảng tính của doanh nghiệp không tồn tại ngẫu nhiên tài liệu nào là vừa lòng ĐK được vận dụng nhằm thanh lọc, lỗi #CALC! tiếp tục xuất hiện tại. Lúc này, các bạn chỉ việc nhập thêm  [If_empty] hoặc gửi lịch sự thanh lọc tài liệu với những ĐK không giống.

ham-loc-du-lieu-co-dieu-kien-trong-excel-11

Lỗi #NA!, #VALUE!

#NA!, #VALUE! thông thường xuất hiện tại khi [Include] chứa chấp ĐK bất hợp lí hoặc độ quý hiếm lỗi. Lúc này, các bạn chỉ việc đánh giá và sửa đổi lại [Include] là được.

ham-loc-du-lieu-co-dieu-kien-trong-excel-12

Lỗi #SPILL!

Bạn tiếp tục phát hiện lỗi #SPILL khi vô chống xuất và hiển thị sản phẩm vẫn chứa chấp những độ quý hiếm không giống. Vậy nên, hãy đảm nói rằng vùng xuất tài liệu tuy nhiên các bạn lựa chọn là vùng trống trải và không tồn tại ngẫu nhiên độ quý hiếm nào là ở lẫn lộn vô tê liệt.

ham-loc-du-lieu-co-dieu-kien-trong-excel-13

Bài luyện ví dụ về kiểu cách người sử dụng FILTER – hàm thanh lọc tài liệu với ĐK vô Excel

Để rõ rệt rộng lớn về kiểu cách dùng hàm thanh lọc tài liệu với ĐK vô Excel, chúng ta có thể tìm hiểu thêm một vài ví dụ sau.

Lưu ý: Ví dụ tiếp sau đây được thực hiện bên trên Microsoft Excel 365.

Bài tập: Giảng viên đòi hỏi các bạn thanh lọc vấn đề cá thể và điểm đua của chúng ta nằm trong lớp như hình bên dưới. quý khách hàng rất cần được thực hiện gì?

Lọc 1 điều kiện

Với bảng bên trên, chúng ta có thể dùng hàm thanh lọc tài liệu với ĐK vô Excel – FILTER. Chẳng hạn như bạn phải thanh lọc rời khỏi những SV là phái nam, các bạn với cú pháp sau:

=FILTER(A1:E16,B1:B16=”nam”)

Sau khi nhập cú pháp và bấm Enter, SERP sẽ tiến hành hiển thị như hình bên dưới đây:

ham-loc-du-lieu-co-dieu-kien-trong-excel-15

Lúc này, các bạn sẽ phát hiện ra một bảng bao gồm 5 học viên phái mạnh xuất hiện tại ở dù các bạn vẫn nhập hàm FILTER.

Trong tình huống mình thích lần những SV với điểm đua kể từ 9 trở lên trên, cú pháp ứng tiếp tục là:

=FILTER(A1:E16,E1:E16>=9)

ham-loc-du-lieu-co-dieu-kien-trong-excel-16

Lọc 2 điều kiện

Hoặc mình thích thanh lọc rời khỏi chúng ta SV phái mạnh với điểm đua bên trên 9 điểm, hãy phối kết hợp 2 ĐK thanh lọc tự vết sao (*) như cú pháp bên dưới đây:

=FILTER(A1:E16,(B1:B16=”nam”)*(E1:E16>=9))

ham-loc-du-lieu-co-dieu-kien-trong-excel-17

Lọc 2 ĐK và bố trí theo đòi loại tự

Để thanh lọc những SV phái đẹp đạt kể từ 9 điểm đua trở lên trên và bố trí list tê liệt theo đòi trật tự năm sinh tăng dần dần, bạn phải phối kết hợp hàm thanh lọc tài liệu với ĐK vô Excel – FILTER và hàm bố trí – SORT.

Cụ thể, hàm SORT vô Excel thông thường được dùng để làm bố trí những tài liệu vô một phạm vi hoặc mảng tài liệu theo đòi trật tự kể từ tăng dần dần hoặc rời dần dần. Cú pháp của hàm SORT là:

=SORT(array,sort_index,sort_order,by_col)

Trong đó:

  • Array: Là phạm vi/mảng tài liệu mình thích bố trí theo đòi độ quý hiếm tăng dần dần hoặc rời dần dần. Đây là bộ phận cần vô hàm.
  • Sort_index: Đây là 1 trong những tùy lựa chọn, được dùng để làm đã cho thấy cột/hàng tài liệu tuy nhiên mình thích bố trí (cột 1, mặt hàng 1), nếu như quăng quật trống trải dù này, hàm tiếp tục bố trí dựa vào cả mảng tài liệu.
  • Sort_order: Đây là 1 trong những tùy lựa chọn, được dùng để làm hướng dẫn và chỉ định trật tự bố trí là tăng dần dần hoặc rời dần dần. Nếu quăng quật trống trải dù này, Excel tiếp tục bố trí theo đòi trật tự đem toan là trật tự tăng dần dần.
  • By_col: Đây là 1 trong những tùy lựa chọn. Trong số đó, TRUE là bố trí theo đòi cột còn FALSE là theo đòi mặt hàng. Nếu quăng quật trống trải dù này, Excel bố trí theo đòi đem toan là theo đòi cột.

Quay lại ví dụ, tất cả chúng ta với cú pháp:

=SORT(FILTER(A1:E16,(B1:B16=”nữ”)*(E1:E16>=9)),3,1)

Trong tê liệt, số “3” dùng để làm hướng dẫn và chỉ định bố trí theo đòi năm sinh và số 1 được dùng để làm hướng dẫn và chỉ định bố trí theo đòi độ quý hiếm tăng dần dần. Ta với sản phẩm là:

Xem thêm: truyện công chúa ngủ trong rừng

ham-loc-du-lieu-co-dieu-kien-trong-excel-18

Lời kết

Bên bên trên là cơ hội dùng hàm thanh lọc tài liệu với ĐK vô Excel và cơ hội xử lý một vài lỗi thông thường gặp gỡ tuy nhiên chúng ta có thể tìm hiểu thêm. Hy vọng nội dung bài viết tiếp tục hữu ích và nhớ rằng nhằm lại phản hồi bên dưới nếu như với ngẫu nhiên vướng mắc hoặc thắc mắc nào là rất cần được trả lời, các bạn nhé!

Xem thêm:

  • Cách người sử dụng hàm Countifs vô Excel
  • Cách gửi chữ thông thường trở thành chữ hoa và ngược lại vô Word, Excel nhanh chóng nhất