Marty có một bảng tính lớn liệt kê tất cả nhân viên (trước đây và hiện tại) trong công ty của anh ấy. Trang tính theo dõi các thông tin khác nhau về từng nhân viên, chẳng hạn như tên, địa chỉ, bộ phận, giới tính, trạng thái, v.v.

Marty thường lọc dữ liệu để đáp ứng nhu cầu của mình. Anh ấy muốn có một cách để xác định tỷ lệ phần trăm nhân viên là nam và tỷ lệ phần trăm là nữ, dựa trên các hàng được hiển thị sau khi lọc. Hàm SUBTOTAL có thể hoạt động trên một cột được lọc để cung cấp nhiều số lượng khác nhau, nhưng nó sẽ không cho phép anh ta xác định số lượng dựa trên nội dung (“M” hoặc “F”) của cột được lọc.

Một cách tiếp cận là sử dụng PivotTable để xác định tỷ lệ phần trăm.

PivotTables tương đối dễ sử dụng, đặc biệt là để trả lời một câu hỏi như thế này. Tuy nhiên, chúng không quá tuyệt khi cho phép bạn xem thông tin chi tiết về nhân viên của mình — bạn có thể thấy câu trả lời tổng hợp cho câu hỏi nam / nữ, nhưng bạn không thể đồng thời xem chi tiết về những nhân viên đó. Vì vậy, tôi muốn tập trung vào việc sử dụng các công thức thẳng để trả lời cho bài toán của Marty.

Tạo công thức để có được tỷ lệ phần trăm mong muốn khó hơn so với lúc đầu. Ví dụ, thật dễ dàng để xác định số lượng khi tất cả các bản ghi nhân viên được hiển thị. Ví dụ: bạn có thể chỉ cần sử dụng một cái gì đó như thế này, giả sử rằng giới tính nằm trong cột C, để xác định tỷ lệ phần trăm hồ sơ dành cho nhân viên nam:

=COUNTIF(C:C,"M")/COUNTA(C:C)-1

Vấn đề là nếu bạn lọc các bản ghi theo một cột khác với cột C. Ví dụ: bạn có thể lọc nó theo bất kỳ thứ gì có trong cột trạng thái. Công thức trên vẫn sẽ cung cấp cho bạn tỷ lệ phần trăm dựa trên tất cả các bản ghi trong cột giới tính, không chỉ những bản ghi hiện đang hiển thị do lọc.

Tại thời điểm này, bạn có thể nghĩ rằng hàm SUBTOTAL có thể hoạt động, nhưng Marty phát hiện ra rằng nó không. Một lần nữa, nếu giới tính nằm trong cột C, bạn có thể đặt nội dung sau ở cuối cột C:

=SUBTOTAL(103,C2:C9999)/COUNTA(C2:C9999)

Tuy nhiên, điều này sẽ không hoạt động cho các nhu cầu mong muốn. Vấn đề chính là hàm SUBTOTAL cung cấp cho bạn số lượng bản ghi nào được hiển thị, nhưng không có sự phân biệt giữa việc các bản ghi đó chứa “M” hay “F” trong cột C. Vấn đề thứ hai là COUNTA đếm tất cả các bản ghi, không chỉ những cái được hiển thị. Do đó, công thức không cung cấp cho bạn phần trăm các bản ghi được hiển thị có chứa “M” hoặc “F”

nhưng thay vào đó, tỷ lệ phần trăm số lượng bản ghi được hiển thị trên tổng số bản ghi.

Bạn có thể thử sử dụng cột trợ giúp nếu muốn. Chỉ cần thêm một cột đại diện cho trạng thái “M” hoặc “F” của mỗi nhân viên là 1 hoặc 0.

Điều này có thể được thực hiện với một công thức đơn giản, chẳng hạn như sau:

=IF(C2="M",1,0)

Công thức này giả định, một lần nữa, cột giới tính là C. Giả sử rằng công thức này được đặt trong cột X (cột trợ giúp của bạn), thì bạn có thể sử dụng hai hàm SUBTOTAL, theo cách này:

=SUBTOTAL(109,X2:X9999)/SUBTOTAL(103,C2:C9999)

Lần đầu tiên sử dụng SUBTOTAL cung cấp cho bạn số hàng có chứa “M”

và việc sử dụng SUBTOTAL thứ hai cung cấp cho bạn tổng số hàng hiển thị. Kết quả của công thức là tỷ lệ phần trăm lao động nam có hồ sơ được hiển thị. Bạn có thể xác định tỷ lệ lao động nữ bằng cách trừ kết quả nam cho 1.

Nếu bạn không thể sử dụng cột trợ giúp (hoặc bạn không muốn sử dụng cột trợ giúp), sẽ phức tạp hơn một chút để giải quyết những thiếu sót của các phương pháp đã thảo luận trước đó; nó cần một công thức phức tạp hơn một chút. Sau đây là một công thức sẽ hoạt động, nhưng tôi đã chia công thức đơn thành bốn dòng để giải thích dễ dàng hơn một chút.

=SUMPRODUCT(SUBTOTAL(103,OFFSET(C2:C9999, ROW(C2:C9999)-MIN(ROW(C2:C9999)),,1)), ISNUMBER(SEARCH("M",C2:C9999))+0)

/SUBTOTAL(103,C2:C9999)

Tôi sẽ đi vào rất chi tiết về cách thức hoạt động của công thức này, vì vậy hãy chịu khó với tôi một chút — điều này sẽ mất một lúc. Đầu tiên chúng ta hãy xem phần này của công thức, kéo dài đến cuối hàng đầu tiên và tổng của hàng thứ hai:

OFFSET(C2:C9999,ROW(C2:C9999)-MIN(ROW(C2:C9999)),,1)

Việc sử dụng hàm OFFSET ở đây dựa trên 3 tham số. Đầu tiên tương ứng với điểm bắt đầu để tính toán tham chiếu sẽ được trả về (trong trường hợp này, điểm bắt đầu là C2: C9999). Tham số thứ hai là số hàng được bù đắp từ đầu của phạm vi đó được xác định trong tham số đầu tiên. Trong trường hợp này, các hàng cần bù trừ được xác định bằng cách trừ số hàng thấp nhất trong phạm vi (sẽ luôn trả về giá trị 2) cho hàng thực tế đang được phân tích. Vì vậy, ví dụ: nếu hàng đang được phân tích là hàng 10, thì việc trừ đi 2 (hàng bắt đầu) từ đó sẽ cho chúng ta một phần bù của 8 hàng từ đầu phạm vi được chỉ định trong tham số đầu tiên.

Tham số thứ ba là trống, vì vậy nó được mặc định là 0. Đây là số cột được bù trừ từ cột đầu tiên trong phạm vi được chỉ định trong tham số đầu tiên. Cuối cùng, tham số thứ tư là số 1, cho biết bạn muốn OFFSET trả về một phạm vi chỉ cao 1 ô.

Điểm mấu chốt là toàn bộ phần này của công thức được bao gồm để nó trả về một tham chiếu đến một ô duy nhất trong cột đang được phân tích. Để giải thích trong công thức này, hãy gọi những gì được trả về là “SingleCell”. Cắm cái này vào công thức ban đầu, chúng ta nhận được cái này:

=SUMPRODUCT(SUBTOTAL(103,SingleCell), ISNUMBER(SEARCH("M",C2:C9999))+0)

/SUBTOTAL(103,C2:C9999)

Sau đó, hàm SUBTOTAL đầu tiên trả về kết quả COUNTA (được chỉ ra bởi giá trị 3 đang được sử dụng cho tham số đầu tiên) cho một ô. Hiệu quả là SUBTOTAL trả về 0 hoặc 1, tùy thuộc vào việc ô có hiển thị hay không. (Nếu ô được lọc ra khỏi kết quả, thì 0 được trả về. Nếu ô không được lọc ra — nó hiển thị — thì 1 được trả về.)

Phần tiếp theo của công thức dựa vào các hàm ISNUMBER và SEARCH. Phần mã này trả về 0 hoặc 1 tùy thuộc vào ô có chứa ký tự “M” hay không. Sau đó, những gì bạn kết thúc với, là một cái gì đó sôi nổi này:

=SUMPRODUCT(1,0)/SUBTOTAL(103,C2:C9999)

Trong trường hợp của một hàng này, hàm SUMPRODUCT sẽ trả về 0, nghĩa là hàng “không được tính” trong tổng số. Vì hàm SUMPRODUCT là một hàm dựa trên mảng, nên nó sẽ tính toán một sản phẩm dựa trên phép nhân mỗi hàng trong phạm vi ban đầu, riêng lẻ. Do đó, nó xác định tổng số tất cả các hàng thỏa mãn hai điều kiện: hàng có thể nhìn thấy và hàng có chứa ký tự “M.”

Điều này cuối cùng được chia cho kết quả của hàm SUBTOTAL cuối cùng, là kết quả COUNTA của số hàng hiển thị. Kết quả cuối cùng là phần trăm số hàng hiển thị có ký tự “M” hiển thị trong cột C — kết quả chính xác mà Marty muốn.

Để có được tỷ lệ phần trăm nữ trong các hàng hiển thị, tất cả những gì bạn cần làm là thay đổi những gì đang được tìm kiếm: thay đổi “M” thành “F” trong công thức và bạn sẽ ổn.

Tuy nhiên, có một cảnh báo cần được đề cập. Hàm SEARCH không phân biệt chữ hoa và chữ thường. Do đó, nếu bạn sử dụng “Nam” thay vì “M” và “Nữ” thay vì “F” trong cột giới tính của bạn (cột C), thì việc tìm kiếm “Nam” trong công thức sẽ khớp với mọi hàng vì các ô chứa ” Female “sẽ chứa các ký tự” male “. Vì vậy, tốt nhất là gắn với “M” và “F” hoặc, nếu bạn phải sử dụng “Nam” và “Nữ”, thì chỉ cần sử dụng “Nữ” trong công thức và tính phần trăm bản ghi nam bằng 1 trừ. tỷ lệ nữ.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên các trang WordTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (13550) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.