Hướng dẫn advanced function excel - chức năng nâng cao excel

Thực hiện theo việc tạo từng bước của một công thức mẫu, hoàn thành với các tài liệu tham khảo và chức năng.

Một Cách sử dụng Advanced Filter rất hay mà mình rất thích và hay sử dụng là chúng ta có thể sắp xếp lại thứ tự của các cột trong bảng dữ liệu rất nhanh chóng và đơn giản. Để làm được điều này, mời các bạn theo dõi video sau đây của ĐT Nguyễn

Advanced Filter là một hàm mới nhưng ứng dụng khá tuyệt vời. Học Excel Onlinexin chúc các bạn nhanh chóng làm chủ cách sử dụng Advanced Filter sớm nhất. 

I am going to show you how to write a basic formula to demonstrate a couple of important concepts.

First, to help us keep everything straight, let's click this + sign to add a new worksheet. Then, double-click the worksheet tabs and type names.

Now, we can add the data.

The formula we are going to write will go in this column and use the amounts in these two columns and a percentage amount.

We could format the data as a table, but let's leave it as is for this demonstration.

The first thing we need is a cell to hold the percentage amount.Number group and click the % button to format the cell as a percentage.

Pick a cell outside the data and type .1.

Bây giờ, chúng ta có thể nhập công thức.

Công thức thêm các tế bào vật liệu và lao động, và sau đó, nhân số lượng đó với phần trăm bộ đệm.

Dấu hoa thị được sử dụng để nhân. Nhấn Enter để xem những gì xảy ra.

Chà, rõ ràng là số tiền này không phải là 10 phần trăm của hai cột đầu tiên. Và lý do không phải là vì tôi không xem xét thứ tự tính toán.

Đây là thứ tự trong đó Excel tính toán các phần của công thức.

Bạn không cần nhớ tất cả các chi tiết.

Chỉ cần lưu ý rằng phép nhân và chia được tính toán trước khi thêm và trừ. Và trong công thức của chúng tôi, chúng tôi muốn Excel thêm các ô đầu tiên, sau đó nhân lên.

Để nói với Excel để thêm đầu tiên, chúng tôi chỉ cần chèn dấu ngoặc đơn. Bây giờ thay vì nhân, sau đó thêm, Excel sẽ thêm hai số đầu tiên, sau đó nhân.

Bây giờ chúng ta có thể sử dụng Autofill để thêm các công thức vào các ô khác.AutoFill to add the formulas to the other cells.

Nhưng bây giờ chúng tôi gặp phải một vấn đề khác.

Trong ô đầu tiên, công thức nhận được tỷ lệ phần trăm bộ đệm từ D20.

Nhưng trong ô tiếp theo, công thức đề cập đến D21. Và vì D21 trống, công thức nhân với 0 và trả về giá trị bằng không.

Nhưng tại sao Excel lại thay đổi tham chiếu tế bào này thành D21?

Bởi vì khi bạn sử dụng Autofill hoặc sao chép một công thức, Excel sử dụng các tài liệu tham khảo tương đối theo mặc định. Đây là cách hoạt động.AutoFill or copy a formula, Excel uses relative references by default. Here's how that works.

Trong ô đầu tiên, công thức đề cập đến các ô này trong hàng 6 và d20. Khi bạn sử dụng Autofill, Excel sẽ thay đổi tham chiếu ô dựa trên vị trí thành hàng 7 và D21.AutoFill, Excel changes the cell reference based on location to row 7 and D21.

Trong hầu hết các trường hợp, đây là những gì bạn muốn.

Nhưng trong trường hợp này, chúng tôi muốn các công thức luôn sử dụng giá trị trong ô cụ thể này. Để thực hiện điều đó, chúng ta cần thay đổi tham chiếu ô thành một tham chiếu tuyệt đối.

Trong công thức, nhấp vào D20, sau đó nhập các dấu hiệu đô la trước D và 20.

Các dấu hiệu đô la nói với Excel rằng công thức phải luôn luôn tham khảo ô trong cột D ở hàng thứ 20. Nhấn Enter.

Bây giờ khi bạn tự động phát tế bào, tất cả các công thức đều chỉ vào D20. Và chúng ta có thể thay đổi tỷ lệ phần trăm để tính một lượng bộ đệm khác.AutoFill the cell, all the formulas point to D20. And we can change the percentage to calculate a different buffer amount.

Điều cuối cùng chúng tôi sẽ làm là thêm một cột chi phí ước tính sử dụng hàm.

Với G6 được chọn, nhấp vào Autosum.AutoSum.

Excel tự động giả định chúng tôi muốn thêm 3 cột vào bên trái.

Vì vậy, nhấn Enter. Và sau đó tự động xử lý công thức cho các ô khác.AutoFill the formula to the other cells.

Trong khóa học này, bạn đã học được những điều cơ bản về việc tạo sổ làm việc Excel đầu tiên của bạn.

Bạn đi đâu tiếp theo? Vâng, hãy thử khám phá Excel 2013 của riêng bạn. Ví dụ: bạn có thể nhanh chóng tạo biểu đồ và sử dụng các nút này để thêm định dạng.

Để đào sâu hơn nữa vào các tính năng, công cụ, chức năng và công thức của Excel, hãy xem các liên kết trong bản tóm tắt khóa học.

Một Cách sử dụng Advanced Filter rất hay mà mình rất thích và hay sử dụng là chúng ta có thể sắp xếp lại thứ tự của các cột trong bảng dữ liệu rất nhanh chóng và đơn giản. Để làm được điều này, mời các bạn theo dõi video sau đây của ĐT Nguyễncách sử dụng Advanced Filter trong Excel qua các ví dụ cụ thể. Chức năng lọc dữ liệu nâng cao Advanced Filter giúp chúng ta lọc ra những dòng dữ liệu thoả mãn các điều kiện từ đơn giản tới phức tạp.

Advanced Filter là một hàm mới nhưng ứng dụng khá tuyệt vời. Học Excel Onlinexin chúc các bạn nhanh chóng làm chủ cách sử dụng Advanced Filter sớm nhất. chức năng lọc Auto Filter trong Excel a cung cấp cho chúng ta rất nhiều chức năng lọc nhiều kiểu dữ liệu khác nhau như lọc theo chuỗi, lọc theo số, lọc ngày tháng và có thể xử lý và thoả mãn rất nhiều tình huống trong công việc. Tuy nhiên, vẫn có những tình huống chúng ta không thể sử dụng Auto Filter để giải quyết, Advanced Filter có thể giúp bạn giải quyết những tình huống như thế này.

Trong bài viết này, chúng ta sẽ học cách sử dụng Advanced Filter trong Excel qua các ví dụ cụ thể. Chức năng lọc dữ liệu nâng cao Advanced Filter giúp chúng ta lọc ra những dòng dữ liệu thoả mãn các điều kiện từ đơn giản tới phức tạp.

• Có thể các bạn đã biết chức năng lọc Auto Filter trong Excel a cung cấp cho chúng ta rất nhiều chức năng lọc nhiều kiểu dữ liệu khác nhau như lọc theo chuỗi, lọc theo số, lọc ngày tháng và có thể xử lý và thoả mãn rất nhiều tình huống trong công việc. Tuy nhiên, vẫn có những tình huống chúng ta không thể sử dụng Auto Filter để giải quyết, Advanced Filter có thể giúp bạn giải quyết những tình huống như thế này.
• Chức năng Advanced Filter có mặt trong các phiên bản Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 và Excel 2003.
  • Sự khác biệt giữa Advanced Filter và AutoFilter
  • Làm thế nào để sử dụng Advanced Filter trong Excel
  • Chuẩn bị dữ liệu để lọc
  • Thiết lập vùng điều kiện lọc
• Áp dụng Advanced Filter
  • Thiết lập cho Advanced Filter
  • Advanced Filter – Lọc dữ liệu chuỗi
    • Ví dụ 1. Lọc dữ liệu text chính xác
    • Ví dụ 2. Lọc dữ liệu text bắt đầu bởi
  • Sử dụng công thức trong vùng điều kiện Advanced Filter
  • Advanced Filter với điều kiện VÀ
  • Advanced Filter với điều kiện HOẶC
  • Trong hình minh hoạ dưới đây, bạn có thể thiết lập vùng điều kiện HOẶC với các điều kiện được viết trên nhiều dòng khác nhau
  • Điều kiện này được đọc ra là: lọc dữ liệu của

Có thể các bạn đã biết chức năng lọc Auto Filter trong Excel a cung cấp cho chúng ta rất nhiều chức năng lọc nhiều kiểu dữ liệu khác nhau như lọc theo chuỗi, lọc theo số, lọc ngày tháng và có thể xử lý và thoả mãn rất nhiều tình huống trong công việc. Tuy nhiên, vẫn có những tình huống chúng ta không thể sử dụng Auto Filter để giải quyết, Advanced Filter có thể giúp bạn giải quyết những tình huống như thế này.

Chức năng Advanced Filter có mặt trong các phiên bản Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 và Excel 2003.Advanced Filter khác biệt ở một vài điểm quan trọng như sau

• Sự khác biệt giữa Advanced Filter và AutoFilter sẽ yêu cầu chúng ta phải có một số thiết lập về vùng dữ liệu, vùng điều kiện trước khi sử dụng.
  Chức năng lọc AutoFilter cho phép chúng ta lọc dữ liệu dựa trên những bộ lọc được định nghĩa sẵn.
• Làm thế nào để sử dụng Advanced Filter trong Excelcó giới hạn tối đa 2 điều kiện lọc, thường được định nghĩa trong hộp thoại Custom AutoFilter.
  Sử dụng bộ lọc nâng cao Advanced Filter cho phép chúng ta lọc dữ liệu thoả mãn nhiều điều kiện trong nhiều cột, ngoài ra, Advanced Filter còn cho chúng ta lựa chọn copy dữ liệu thoả mãn điều kiện lọc ra một vùng khác.

Xem thêm: Hướng dẫn cách sử dụng filter trong excel 365 cơ bảnHướng dẫn cách sử dụng filter trong excel 365 cơ bản

Làm thế nào để sử dụng Advanced Filter trong Excel

Sử dụng chức năng lọc nâng cao Advanced Filter sẽ khó hơn AutoFilter một chút (thế mới gọi là nâng cao). Tuy nhiên, một khi đã thành thạo, bạn sẽ thấy Advanced Filter lợi hại tới mức nào. Hãy cùng khám phá cách sử dụng Advanced Filter trong phần sau đây của bài viết.

Chuẩn bị dữ liệu để lọc

Để đảm bảo kết quả lọc chính xác nhất, có 2 điều bạn cần phải chú ý:

• Tiêu đề các cột dữ liệu trong bảng dữ liệu là duy nhất, không có 2 hay nhiều cột dữ liệu có tiêu đề giống nhau.
• Không có dòng trống trong bảng dữ liệu cần lọc

Thiết lập vùng điều kiện lọc

Một vài điểm lưu ý khi chuẩn bị vùng điều kiện lọc:

• Tiêu đề của vùng điều kiện lọc và tiêu đề của bảng dữ liệu cần phải đồng nhất.
• 2 điều kiện được đặt trên cùng một dòng tương ứng với điều kiện VÀ – nghĩa là những dòng thoả mãn cả 2 điều kiện cùng lúc sẽ có mặt trong kết quả
• 2 điều kiện được đặt trên 2 dòng khác nhau tương ứng với điều kiện HOẶC – nghĩa là những dòng thoả mãn 1 trong 2 điều kiện sẽ có mặt trong kết quả

Ví dụ, chúng ta cần lọc ra những dòng của Miền Bắc và có Sub-total lớn hơn hoặc bằng 900, thì chúng ta cần thiết lập vùng điều kiện cho Advanced Filter như sau:

Chú ý: bạn cần đảm bảo tiêu đề của vùng điều kiện và tiêu đề của cột tương ứng trong vùng dữ liệu đồng nhất, giống nhau hoàn toàn, vậy nên, khi chuẩn bị vùng điều kiện, bạn có thể copy tiêu đề cột ở vùng dữ liệu và dán vào tiêu đề ở vùng điều kiện.bạn cần đảm bảo tiêu đề của vùng điều kiện và tiêu đề của cột tương ứng trong vùng dữ liệu đồng nhất, giống nhau hoàn toàn, vậy nên, khi chuẩn bị vùng điều kiện, bạn có thể copy tiêu đề cột ở vùng dữ liệu và dán vào tiêu đề ở vùng điều kiện.

Áp dụng Advanced Filter

Sau khi đã chuẩn bị xong vùng điều kiện, bạn có thể áp dụng Advanced Filter như sau:

• Chọn 1 ô bất kì trong vùng dữ liệu
• Trong Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, chọn thẻ Data > Nhóm lệnh Sort & Filter sau đó bấm AdvancedAdvanced

Thiết lập cho Advanced Filter

Trong cửa sổ Advanced Filter, bạn có các lựa chọn về thiết lập sau đây:

• Action: lựa chọn lọc “tại chỗ” – Filter the list, in-place hay lọc dữ liệu rồi copy ra nơi khác trên bảng tính – Copy to another locationNếu bạn chọn lọc “tại chỗ” – Filter the list, in-place thì sau khi lọc, các dòng không thoả mãn điều kiện lọc sẽ được ẩn đi.lựa chọn lọc “tại chỗ” – Filter the list, in-place hay lọc dữ liệu rồi copy ra nơi khác trên bảng tính – Copy to another location
  Nếu bạn chọn lọc “tại chỗ” – Filter the list, in-place thì sau khi lọc, các dòng không thoả mãn điều kiện lọc sẽ được ẩn đi.
• List range: Vùng dữ liệu bạn sẽ lọc, bao gồm cả tiêu đề Trước khi bạn mở cửa sổ thiết lập Advanced Filter, nếu bạn đang để chuột trong vùng dữ liệu, thì Excel sẽ tự động thiêt lập List Range một cách tự động. Trong trường hợp Excel làm điều này không chính xác, bạn hãy lựa chọn lại vùng dữ liệu bằng cách bấm nút chọn lại dữ liệu ở bên tay phải ô List Range sau đó chọn lại vùng dữ liệu bằng chuộtVùng dữ liệu bạn sẽ lọc, bao gồm cả tiêu đề
  Trước khi bạn mở cửa sổ thiết lập Advanced Filter, nếu bạn đang để chuột trong vùng dữ liệu, thì Excel sẽ tự động thiêt lập List Range một cách tự động. Trong trường hợp Excel làm điều này không chính xác, bạn hãy lựa chọn lại vùng dữ liệu bằng cách bấm nút chọn lại dữ liệu ở bên tay phải ô List Range sau đó chọn lại vùng dữ liệu bằng chuột
• Criteria range: Vùng điều kiện chúng ta vừa thiết lập như đoạn phía trên đã viếtVùng điều kiện chúng ta vừa thiết lập như đoạn phía trên đã viết
• Copy to: Nếu trong phần Action bạn chọn Copy to another location thì lựa chọn này sẽ được kích hoạt và bạn có thể chọn vùng bạn muốn copy kết quả lọc tới. Nếu trong phần Action bạn chọn Filter the list, in-place thì lựa chọn này sẽ bị mờ đi, không được kích hoạt.Nếu trong phần Action bạn chọn Copy to another location thì lựa chọn này sẽ được kích hoạt và bạn có thể chọn vùng bạn muốn copy kết quả lọc tới. Nếu trong phần Action bạn chọn Filter the list, in-place thì lựa chọn này sẽ bị mờ đi, không được kích hoạt.
• Unique records only: Nếu chọn hộp kiểm này, Advanced Filter sẽ lọc ra những kết quả duy nhất cho bạn.Nếu chọn hộp kiểm này, Advanced Filter sẽ lọc ra những kết quả duy nhất cho bạn.

Kết quả sau khi bấm nút OK ở hộp thoại thiết lập Advanced Filter

Bạn hoàn toàn có thể đạt được kết quả lọc này với chức năng AutoFilter, Đúng! Tuy nhiên đến đây, bạn đã biết cách sử dụng Advanced Filter một cách cơ bản, để chúng ta có thể đến với những ví dụ nâng cao hơn ở phần tiếp theo.

Sử dụng Advanced Filter – vùng điều kiện

Tính nâng cao của chức năng lọc Advanced Filter nằm ở việc thiết lập vùng điều kiện, nắm được kiến thức này, bạn sẽ áp dụng được Advanced Filter trong rất nhiều tình huống.

Advanced Filter điều kiện so sánh số và ngày tháng

Một số toán tử so sánh và ý nghĩa được trình bày trong bảng sau đây

Ví dụ sau đây, chúng ta cần lọc ra những dòng thoả mãn tất cả các điều kiện sau:

• Vùng: Miền Bắc
• Ngày đặt: trong khoảng 01/07/2020 và 30/07/2020
• Thành tiền: lớn hơn 800

Cách thiết lập điều kiện như hình minh hoạ. Như đã nói phía trên, điều kiện trên cùng 1 dòng là điều kiện VÀ. Điều kiện trong khoảng ngày, được thể hiện bởi 2 cột Ngày đặt như trong hình.

Chú ý: khi lọc dữ liệu ngày tháng với Advanced Filter, bạn cần chú ý tới 2 điều:khi lọc dữ liệu ngày tháng với Advanced Filter, bạn cần chú ý tới 2 điều:

• Ngày tháng trong bảng dữ liệu của bạn đã đúng là ngày tháng hay chưa
• Cách nhập ngày tháng trong Excel của bạn cho đúng

Tham khảo bài viết về thiết lập ngày tháng trong Excel để xử lý khi gặp lỗi

Advanced Filter – Lọc dữ liệu chuỗi

Ngoài việc sử dụng Advanced Filter để lọc dữ liệu số và ngày tháng, chúng ta còn có thể lọc chuỗi.

Ví dụ 1. Lọc dữ liệu text chính xác

Lọc ra những dòng dữ liệu mà cột Sản Phẩm có giá trị chính xác là “Chuối”. Điều kiện lọc trong A2 mà bạn cần gõ vào là =”=Chuối” trong ô A2 sẽ hiển thị là =Chuối =”=Chuối” trong ô A2 sẽ hiển thị là =Chuối 

Ví dụ 2. Lọc dữ liệu text bắt đầu bởi

Lọc ra những dòng dữ liệu mà cột Sản Phẩm có giá trị bắt đầu bởi chữ CC

Sử dụng công thức trong vùng điều kiện Advanced Filter

Đây thực sự là tính năng mà mình thích nhất khi sử dụng Advanced Filter và đây cũng là chức năng mà AutoFilter không có, đó chính là sử dụng công thức trong vùng điều kiện của Advanced Filter. Để Advanced Filter hoạt động đúng, chúng ta cần lưu ý như sau:

• Công thức sử dụng cho vùng điều kiện trả về kết quả TRUE hoặc FALSE
• Vùng điều kiện bao gồm ít nhất 2 ô: ô chứa công thức và ô tiêu đề
• Ô tiêu đề của vùng điều kiện phải có nội dung khác với tiêu đề các cột trong vùng dữ liệu
• Nếu công thức áp dụng cho mỗi dòng dữ liệu, sử dụng tham chiếu tương đối và tham chiếu tới dòng dữ liệu đầu tiên trong vùng dữ liệu
• Tham chiếu tới vùng dữ liệu luôn là tham chiếu tuyệt đối (nên đặt tên cho vùng dữ liệu – sử dụng Named Range)

Trong ví dụ sau, chúng ta cần lọc ra các dòng dữ liệu mà doanh thu tháng 7 lớn hơn doanh thu tháng 8. Vùng điều kiện với công thức được thiết lập với công thức =C7>D7 như trong hình, và dòng thứ 7 cũng là dòng đầu tiên trong vùng dữ liệu.=C7>D7 như trong hình, và dòng thứ 7 cũng là dòng đầu tiên trong vùng dữ liệu.

Advanced Filter với điều kiện VÀ

Trong hình minh hoạ dưới đây, bạn có thể thiết lập vùng điều kiện VÀ với các điều kiện được viết trên cùng 1 dòng.

Advanced Filter với điều kiện HOẶC

Trong hình minh hoạ dưới đây, bạn có thể thiết lập vùng điều kiện HOẶC với các điều kiện được viết trên nhiều dòng khác nhau

Trong hình minh hoạ dưới đây, bạn có thể thiết lập vùng điều kiện HOẶC với các điều kiện được viết trên nhiều dòng khác nhau

Advanced Filter với điều kiện hỗn hợp

Advanced Filter với điều kiện hỗn hợp

Bạn có thể kết hợp cả điều kiện VÀ và điều kiện HOẶC như sauvà Sub-total >= 900) hoặc  (vùng Miền Bắc và Trung bình >=300)

Điều kiện này được đọc ra là: lọc dữ liệu của

(vùng Miền Bắc và Sub-total >= 900) hoặc  (vùng Miền Bắc và Trung bình >=300)

Advanced Filter để sắp xếp lại các cột trong bảng dữ liệu là một hàm mới nhưng ứng dụng khá tuyệt vời. Học Excel Onlinexin chúc các bạn nhanh chóng làm chủ cách sử dụng Advanced Filter sớm nhất.