Xem Nhiều 12/2022 #️ Danh Sách Được Lọc Với Tiêu Chí Trong Excel? / 2023 # Top 14 Trend | Trucbachconcert.com

Xem Nhiều 12/2022 # Danh Sách Được Lọc Với Tiêu Chí Trong Excel? / 2023 # Top 14 Trend

Cập nhật thông tin chi tiết về Danh Sách Được Lọc Với Tiêu Chí Trong Excel? / 2023 mới nhất trên website Trucbachconcert.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.

Làm thế nào để đếm dữ liệu / danh sách được lọc với tiêu chí trong Excel?

Bạn có thể nhận thấy rằng bất kể bạn đã lọc bảng của mình hay chưa, hàm COUNTIF sẽ bỏ qua việc lọc và trả về một giá trị cố định. Trong một số trường hợp, bạn được yêu cầu đếm dữ liệu đã lọc với một tiêu chí cụ thể, vậy làm thế nào để hoàn thành việc này? Trong bài viết này, tôi sẽ giới thiệu một số cách để đếm dữ liệu / danh sách đã lọc trong Excel một cách nhanh chóng.

Countif đã lọc dữ liệu với tiêu chí bằng cách thêm cột trợ giúp trong Excel

Trong bài viết này, tôi sẽ lấy bảng sau làm ví dụ. Ở đây, tôi đã lọc ra Julie và Nicole trong cột Người bán hàng.

Dữ liệu gốc:

Dữ liệu đã lọc:

Phương pháp này sẽ hướng dẫn bạn thêm một cột trợ giúp bổ sung và sau đó bạn có thể áp dụng hàm COUNTIFS để đếm dữ liệu đã lọc trong Excel. (Chú thích: Phương pháp này yêu cầu bạn phải lọc bảng gốc của mình trước khi làm theo các bước.)

1. Tìm một ô trống bên cạnh bảng đã lọc ban đầu, cho biết Ô G2, nhập = IF (B2 = “Quả lê”, 1, “”), sau đó kéo Fill Handle đến phạm vi bạn cần. (Lưu ý: Trong công thức của = IF (B2 = “Quả lê”, 1, “”), B2 là ô bạn sẽ đếm và “Quả lê” là tiêu chí bạn sẽ đếm.) Bây giờ một cột trợ giúp được thêm vào bên cạnh bảng đã lọc ban đầu. “1” cho biết đó là quả lê trong Cột B, trong khi dấu hiệu trống cho biết nó không phải quả lê trong Cột B.

2. Tìm một ô trống và nhập công thức =COUNTIFS(B2:B18,”Pear”,G2:G18,”1″)và nhấn Đi vào Chìa khóa. (Lưu ý: Trong công thức của =COUNTIFS(B2:B18,”Pear”,G2:G18,”1″), B2: B18 và G2: G18 là các phạm vi bạn sẽ đếm, và “Quả lê” và “1” là tiêu chí bạn sẽ tính theo.)

Bây giờ bạn sẽ nhận được số đếm cùng một lúc. Xin lưu ý rằng số đếm sẽ không thay đổi nếu bạn tắt tính năng lọc hoặc thay đổi tính năng lọc.

Tổng / Đếm / Trung bình các ô hiển thị chỉ trong một phạm vi cụ thể với việc bỏ qua các ô / hàng / cột ẩn hoặc được lọc

Hàm SUM / Đếm / Trung bình thông thường sẽ đếm tất cả các ô trong phạm vi được chỉ định trên các ô vật chất có bị ẩn / lọc hay không. Trong khi hàm Subtotal chỉ có thể tính tổng / đếm / trung bình với việc bỏ qua các hàng ẩn. Tuy nhiên, Kutools cho Excel TÓM TẮT/HỢP TÁC/TRUNG THỰC các hàm sẽ dễ dàng tính toán phạm vi được chỉ định mà bỏ qua bất kỳ ô, hàng hoặc cột nào bị ẩn.

Countif đã lọc dữ liệu với tiêu chí bằng các hàm Excel

Nếu bạn muốn số đếm thay đổi khi bộ lọc thay đổi, bạn có thể áp dụng các hàm SUMPRODUCT trong Excel như sau: Trong một ô trống, hãy nhập công thức =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)),ISNUMBER(SEARCH(“Pear”,B2:B18))+0)và nhấn Đi vào Chìa khóa.

Ghi chú: (1) Trong công thức trên, B2: B18 là phạm vi bạn sẽ đếm và “Quả lê” là tiêu chí bạn sẽ tính theo. (2) Giá trị trả về sẽ thay đổi khi bạn tắt tính năng lọc hoặc thay đổi lọc.

Dễ dàng chia phạm vi thành nhiều trang tính dựa trên tiêu chí trong một cột trong Excel

So với các công thức mảng phức tạp, việc lưu tất cả các bản ghi đã lọc vào một trang tính mới có thể dễ dàng hơn nhiều, sau đó áp dụng hàm Đếm để đếm phạm vi hoặc danh sách dữ liệu đã lọc.

Kutools cho Excel’s Dữ liệu phân tách tiện ích có thể giúp người dùng Excel dễ dàng chia một phạm vi thành nhiều trang tính dựa trên tiêu chí trong một cột của phạm vi ban đầu.

Các công cụ năng suất văn phòng tốt nhất

Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn

Tái sử dụng:

Chèn nhanh

công thức phức tạp, biểu đồ

 và bất cứ thứ gì bạn đã sử dụng trước đây;

Mã hóa ô

với mật khẩu;

Tạo danh sách gửi thư

và gửi email …

Super Formula Bar

(dễ dàng chỉnh sửa nhiều dòng văn bản và công thức);

Bố cục đọc

(dễ dàng đọc và chỉnh sửa số lượng ô lớn);

Dán vào Dải ô đã Lọc

hữu ích. Cảm ơn !

Hợp nhất các ô / hàng / cột

mà không làm mất dữ liệu; Nội dung phân chia ô;

Kết hợp các hàng / cột trùng lặp

… Ngăn chặn các ô trùng lặp;

So sánh các dãy

hữu ích. Cảm ơn !

Chọn trùng lặp hoặc duy nhất

Hàng;

Chọn hàng trống

(tất cả các ô đều trống);

Tìm siêu và Tìm mờ

trong Nhiều Sổ làm việc; Chọn ngẫu nhiên …

Bản sao chính xác

Nhiều ô mà không thay đổi tham chiếu công thức;

Tự động tạo tài liệu tham khảo

sang Nhiều Trang tính;

Chèn Bullets

, Hộp kiểm và hơn thế nữa …

Trích xuất văn bản

, Thêm Văn bản, Xóa theo Vị trí,

Xóa không gian

; Tạo và In Tổng số phân trang;

Chuyển đổi giữa nội dung ô và nhận xét

hữu ích. Cảm ơn !

Siêu lọc

(lưu và áp dụng các lược đồ lọc cho các trang tính khác);

Sắp xếp nâng cao

theo tháng / tuần / ngày, tần suất và hơn thế nữa;

Bộ lọc đặc biệt

bằng cách in đậm, in nghiêng …

Kết hợp Workbook và WorkSheets

; Hợp nhất các bảng dựa trên các cột chính;

Chia dữ liệu thành nhiều trang tính

;

Chuyển đổi hàng loạt xls, xlsx và PDF

hữu ích. Cảm ơn !

Hơn 300 tính năng mạnh mẽ

. Hỗ trợ Office / Excel 2007-2019 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ các tính năng dùng thử miễn phí 30 ngày. Đảm bảo hoàn tiền trong 60 ngày.

Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint

, Publisher, Access, Visio và Project.

Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.

Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!

Làm Thế Nào Để Đếm Dữ Liệu / Danh Sách Được Lọc Với Tiêu Chí Trong Excel? / 2023

Làm thế nào để đếm dữ liệu / danh sách được lọc với tiêu chí trong Excel?

Bạn có thể nhận thấy rằng bất kể bạn đã lọc bảng của mình hay chưa, hàm COUNTIF sẽ bỏ qua việc lọc và trả về một giá trị cố định. Trong một số trường hợp, bạn được yêu cầu đếm dữ liệu đã lọc với một tiêu chí cụ thể, vậy làm thế nào để hoàn thành việc này? Trong bài viết này, tôi sẽ giới thiệu một số cách để đếm dữ liệu / danh sách đã lọc trong Excel một cách nhanh chóng.

Countif đã lọc dữ liệu với tiêu chí bằng cách thêm cột trợ giúp trong Excel

Trong bài viết này, tôi sẽ lấy bảng sau làm ví dụ. Ở đây, tôi đã lọc ra Julie và Nicole trong cột Người bán hàng.

Dữ liệu gốc:

Dữ liệu đã lọc:

Phương pháp này sẽ hướng dẫn bạn thêm một cột trợ giúp bổ sung và sau đó bạn có thể áp dụng hàm COUNTIFS để đếm dữ liệu đã lọc trong Excel. ( Chú thích: Phương pháp này yêu cầu bạn phải lọc bảng gốc của mình trước khi làm theo các bước.)

1. Tìm một ô trống bên cạnh bảng đã lọc ban đầu, cho biết Ô G2, nhập = IF (B2 = “Quả lê”, 1, “”), sau đó kéo Fill Handle đến phạm vi bạn cần. ( Lưu ý: Trong công thức của = IF (B2 = “Quả lê”, 1, “”), B2 là ô bạn sẽ đếm và “Quả lê” là tiêu chí bạn sẽ đếm.) Bây giờ một cột trợ giúp được thêm vào bên cạnh bảng đã lọc ban đầu. “1” cho biết đó là quả lê trong Cột B, trong khi dấu hiệu trống cho biết nó không phải quả lê trong Cột B.

2. Tìm một ô trống và nhập công thức =COUNTIFS(B2:B18,”Pear”,G2:G18,”1″)và nhấn Đi vào Chìa khóa. ( Lưu ý: Trong công thức của =COUNTIFS(B2:B18,”Pear”,G2:G18,”1″), B2: B18 và G2: G18 là các phạm vi bạn sẽ đếm, và “Quả lê” và “1” là tiêu chí bạn sẽ tính theo.)

Bây giờ bạn sẽ nhận được số đếm cùng một lúc. Xin lưu ý rằng số đếm sẽ không thay đổi nếu bạn tắt tính năng lọc hoặc thay đổi tính năng lọc.

Tổng / Đếm / Trung bình các ô hiển thị chỉ trong một phạm vi cụ thể với việc bỏ qua các ô / hàng / cột ẩn hoặc được lọc

Hàm SUM / Đếm / Trung bình thông thường sẽ đếm tất cả các ô trong phạm vi được chỉ định trên các ô vật chất có bị ẩn / lọc hay không. Trong khi hàm Subtotal chỉ có thể tính tổng / đếm / trung bình với việc bỏ qua các hàng ẩn. Tuy nhiên, Kutools cho Excel TÓM TẮT/ HỢP TÁC/ TRUNG THỰC các hàm sẽ dễ dàng tính toán phạm vi được chỉ định mà bỏ qua bất kỳ ô, hàng hoặc cột nào bị ẩn.Bản dùng thử miễn phí đầy đủ tính năng trong 30 ngày!

Countif đã lọc dữ liệu với tiêu chí bằng các hàm Excel

Nếu bạn muốn số đếm thay đổi khi bộ lọc thay đổi, bạn có thể áp dụng các hàm SUMPRODUCT trong Excel như sau: Trong một ô trống, hãy nhập công thức =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)),ISNUMBER(SEARCH(“Pear”,B2:B18))+0)và nhấn Đi vào Chìa khóa.

Ghi chú: (1) Trong công thức trên, B2: B18 là phạm vi bạn sẽ đếm và “Quả lê” là tiêu chí bạn sẽ tính theo. (2) Giá trị trả về sẽ thay đổi khi bạn tắt tính năng lọc hoặc thay đổi lọc.

Dễ dàng chia phạm vi thành nhiều trang tính dựa trên tiêu chí trong một cột trong Excel

So với các công thức mảng phức tạp, việc lưu tất cả các bản ghi đã lọc vào một trang tính mới có thể dễ dàng hơn nhiều, sau đó áp dụng hàm Đếm để đếm phạm vi hoặc danh sách dữ liệu đã lọc.

Kutools cho Excel’s Dữ liệu phân tách tiện ích có thể giúp người dùng Excel dễ dàng chia một phạm vi thành nhiều trang tính dựa trên tiêu chí trong một cột của phạm vi ban đầu.Bản dùng thử miễn phí đầy đủ tính năng trong 30 ngày!

Tái sử dụng: Chèn nhanh công thức phức tạp, biểu đồ và bất cứ thứ gì bạn đã sử dụng trước đây; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email …

Hơn 300 tính năng mạnh mẽ. Hỗ trợ Office / Excel 2007-2019 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ các tính năng dùng thử miễn phí 30 ngày. Đảm bảo hoàn tiền trong 60 ngày.

Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.

Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.

Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!

Hướng Dẫn Cách Tạo Danh Sách Động Trong Excel Với Hàm Offset / 2023

Tạo danh sách không có dòng trống thừa ở cuối danh sách

Ví dụ bạn có 1 danh sách nhân viên, bạn dự tính danh sách đó sẽ gồm tối đa là 20 người. Thế nhưng tại thời điểm tháng 1, bạn mới chỉ có 5 người trong danh sách, còn lại 15 dòng trống. Và trong những tháng tiếp theo thì số người có thể tăng thêm, giảm đi.

Vậy làm cách nào để danh sách nhân viên đó tự động thay đổi theo số nhân viên hiện có, và những dòng trống sẽ bị loại ra khỏi danh sách?

Tìm điểm bắt đầu

Danh sách bắt đầu từ ô B3, vì vậy trong hàm Offset chúng ta có thể:

Bắt đầu với tham chiếu B3, không thay đổi về dòng, cột

Bắt đầu với tham chiếu B2, xuống 1 dòng, không đổi về cột

Bắt đầu với tham chiếu A2, xuống 1 dòng, sang phải 1 cột

Việc chọn vị trí bắt đầu chỉ đơn giản là căn cứ để chúng ta tìm được điểm đầu của danh sách. Tùy theo vị trí tham chiếu mà chúng ta có thực hiện di chuyển số dòng, số cột không. Nếu có thì di chuyển bao nhiêu dòng, bao nhiêu cột.

Ví dụ công thức bắt đầu với ô B3

=OFFSET(B3,0,0,độ rộng theo sốdòng, độ rộng theo số cột)

Xác định độ rộng của danh sách

Tiếp đến là xác định danh sách đó có bao nhiêu ô. Để biết điều này, chúng ta dùng hàm COUNTA. Hàm COUNTA có tác dụng đếm những ô có chứa nội dung (không phải ô trống)

Chúng ta đếm trong cột B, từ dòng 3 tới dòng 22 (tương ứng với tối đa 20 nhân viên trong danh sách):

COUNTA(B3:B20)

Với 5 nhân viên, kết quả hàm COUNTA = 5

Với 7 nhân viên, kết quả hàm COUNTA=7

Khi đặt giá trị này vào tham số Weight của hàm OFFSET, chúng ta sẽ có độ rộng của danh sách là 5 hay 7 ô, tính từ ô B3.

Không cần nhập tham số độ rộng theo số cột, vì ở đây chúng ta chỉ áp dụng trên 1 cột B

Tạo danh sách chọn với Data Validation

Sau khi đã viết đúng được hàm OFFSET thì chúng ta chỉ việc copy công thức đó để đưa vào trong mục tạo danh sách chọn trong Data Validation:

Ví dụ về danh sách phụ thuộc nhau, danh sách theo nhóm như sau:

Trong danh sách nhân viên ở trên, chúng ta có thêm cột Bộ phận. Tại đó chia làm 2 bộ phận là Kinh doanh và Kế toán. Khi chọn bộ phận Kinh doanh, chúng ta sẽ có 1 danh sách chỉ có những nhân viên thuộc bộ phận kinh doanh:

Xác định điểm bắt đầu làm tham chiếu gốc

Bởi vì bộ phận có thể thay đổi, nên chúng ta sẽ xác định điểm bắt đầu là 1 vị trí cố định. Ví dụ như ô B2

=OFFSET(B2, ….)

Xác định hướng thay đổi điểm bắt đầu theo dòng

Tùy vào bộ phận được chọn ở ô F2 mà chúng ta sẽ xác định từ vị trí B2 sẽ di chuyển bao nhiêu dòng. Việc này xác định một cách khá đơn giản với hàm MATCH như sau:

=MATCH(F2,B3:B22,0)

Dò tìm giá trị F2 ở trong vùng B3:B22. Kết quả sẽ là vị trí dòng thứ mấy trong vùng B3:B22 ứng với giá trị tại F2, nếu có nhiều hơn 1 giá trị đúng thì sẽ trả về vị trí của giá trị đầu tiên.

=OFFSET(B2,MATCH(F2,B3:B22,0), ….)

Xác định hướng thay đổi điểm bắt đầu theo cột

Do danh sách cần tìm là Họ tên nhân viên, cách vị trí ô B2 là 1 cột, nên sẽ thay đổi 1 cột. Chúng ta chọn số 1

=OFFSET(B2,MATCH(F2,B3:B22,0),1, …)

Xác định độ rộng theo dòng

Để xác định độ rộng theo dòng, chúng ta cần đếm xem có bao nhiêu dòng trong cột Bộ phận có giá trị giống với Bộ phận được chọn ở ô F2. Đây là việc đếm theo điều kiện, do đó chúng ta dùng hàm COUNTIF như sau:

=COUNTIF(B3:B22,F2)

=OFFSET(B2,MATCH(F2,B3:B22,0),1,COUNTIF(B3:B22,F2), …)

Xác định độ rộng theo cột

Vì danh sách này chúng ta chỉ lấy trên 1 cột, do đó không cần tới điều chỉnh về độ rộng số cột. Có thể không cần nhập tham số này. Chúng ta kết thúc hàm OFFSET với nội dung

=OFFSET(B2,MATCH(F2,B3:B22,0),1,COUNTIF(B3:B22,F2))

=OFFSET($B$2,MATCH($F$2,$B$3:$B$22,0),1,COUNTIF($B$3:B$22,$F$2))

Hàm OFFSET và các kết hợp hàm của nó trong Excel, công thức ví dụ Tạo Drop-down list có giá trị phụ thuộc một list khác Hướng dẫn cách tạo danh sách nhân viên phụ thuộc theo chi nhánh

Hướng Dẫn Cách Lọc Danh Sách Tài Khoản Chi Tiết Theo Tài Khoản Tổng Hợp Trong Excel / 2023

Cách lọc danh sách tài khoản chi tiết theo tài khoản tổng hợp trong Excel

Và trong Sổ chi tiết, yêu cầu của chúng ta là tạo ra danh sách tài khoản chi tiết tương ứng theo tài khoản tổng hợp được chọn.

Sử dụng Advanced Filter lọc danh sách tài khoản chi tiết

Advanced Filter có đặc điểm là giúp chúng ta có thể kết hợp đồng thời việc lọc và trích xuất dữ liệu. Các bước thực hiện Advanced Filter như sau:

Bước 1: Tạo vùng điều kiện

Advanced Filter khá đặc biệt ở việc thiết lập vùng điều kiện. Nguyên tắc là: Mỗi điều kiện sẽ gắn với 1 tên tiêu đề cột dữ liệu.

Do đó vùng điều kiện chúng ta thiết lập như sau:

Nội dung điều kiện: là tài khoản tổng hợp chọn trong ô F2 (trong hình ví dụ trên)

Cột chứa điều kiện: cột Số TK (cách viết như tại ô A2)

Criteria range: Vùng điều kiện của Advanced Filter đã thiết lập ở bước 1

Khi bấm OK, chúng ta có kết quả như sau:

Chúng ta thiết lập Data Validation trong ô chọn tài khoản chi tiết (ô F3) như sau:

Chọn ô F3

Thiết lập công thức trong Source như sau:

=OFFSET($H$9,0,0,COUNTA($H$9:$H$26))

Hàm OFFSET là 1 hàm trả về kết quả mảng linh động:

Bắt đầu từ ô H9 (là ô bắt đầu của danh sách tài khoản chi tiết trong kết quả của Advanced Filter)

Hai số 0 tiếp theo: không thay đổi về dòng, cột tính tại điểm bắt đầu là ô H9

Hàm COUNTA: Đếm số ô chứa giá trị (không phải ô trống) trong vùng H9:H26 (có thể mở rộng hơn nữa)

Bước 4: Tự động hóa Advanced Filter mỗi khi thay đổi tài khoản tổng hợp

Để có thể tự động hóa cho thao tác Advanced Filter, chúng ta sử dụng thêm tính năng Record Macro và Sử dụng Macro trong VBA như sau:

Kết quả thu được Macro có nội dung như sau:

Range(“A2:B232”).AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range(“H3:H4”), CopyToRange:=Range(“H7:I7”), Unique:=False

Trong đó chú ý các vùng Range chính là tọa độ của các đối tượng của Advanced Filter mà chúng ta vừa chọn:

Range(“A2:B232”).AdvancedFilter : vùng dữ liệu gốc (List Range)

Action:=xlFilterCopy : hành động thực hiện là copy (chọn Copy to another location)

CriteriaRange:=Range(“H3:H4”) : vùng điều kiện (Criteria)

CopyToRange:=Range(“H7:I7”) : Vùng kết quả (Copy to)

Tiếp theo chúng ta gọi sự kiện Worksheet_Change tại Sheet chứa Sổ chi tiết, gọi tên Macro1 (là macro ghi lại thao tác Advanced Filter vừa thu được ở trên)

Hướng dẫn cách sử dụng chức năng Advanced Filter trong VBA

Tải về file mẫu theo bài viết: http://bit.ly/2FDWlnJ

Bạn đang xem bài viết Danh Sách Được Lọc Với Tiêu Chí Trong Excel? / 2023 trên website Trucbachconcert.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!