Beth đang tìm cách xác định có bao nhiêu nhân viên đã tham gia ít nhất mười lớp học được cung cấp tại công ty của cô. Cô đã thiết lập Excel để cột A (bắt đầu từ A2) chứa tên nhân viên và cột B (bắt đầu từ B2) chứa tên lớp. Khi một nhân viên tham gia một lớp học, tên của họ được nhập vào cột A và tên của lớp họ đã học được đặt trong cột B. Do đó, cột A sẽ chứa nhiều trường hợp tên của mỗi nhân viên và cột B sẽ chứa nhiều trường hợp của tên các lớp.

Câu hỏi về cách xác định thời điểm một nhân viên đã học mười lớp tốt nhất có thể được trả lời bằng cách sử dụng hàm COUNTIF. Ví dụ, công thức sau có thể được nhập vào mỗi ô của cột C:

=IF(COUNTIF(A:A,A2)>9,"10 classes or more","")

Để sử dụng công thức một cách chính xác, trường hợp cụ thể này sẽ được đặt trong ô C2 và sau đó được sao chép xuống phần còn lại của các ô của cột C.

Điều này làm cho tham chiếu A2 trong công thức thay đổi để nó luôn tham chiếu đến ô hai cột ngay bên trái của nó. (Công thức trong C2 tham chiếu A2, công thức trong C3 tham chiếu A3, v.v.)

Sử dụng công thức này, khi một người cụ thể đã học lớp thứ mười — nghĩa là tên của họ xuất hiện trong cột A lần thứ mười — thì cột C sẽ điền vào cụm từ “10 lớp trở lên” bên cạnh mỗi lớp mà người đó đã học. Điều này thật tuyệt, ngoại trừ khi một loạt nhân viên khác nhau bắt đầu vượt qua ngưỡng mười cấp.

Sau đó, cột C bắt đầu trông lộn xộn.

Để khắc phục sự lộn xộn, một biến thể của phương pháp trên là sử dụng công thức sau trong các ô của cột C:

=IF(COUNTIF($A$2:A2,A2)>9,"10 classes or more","")

Trong trường hợp này, hàm COUNTIF không xem xét mọi ô trong cột A; thay vào đó, nó nhìn vào các ô trong cột A nằm trong các hàng nhỏ hơn hoặc bằng hàng mà công thức xuất hiện. Do đó, nếu công thức này ở hàng 8, thì COUNTIF sẽ chỉ xem xét các hàng từ 2 đến 8 để thực hiện số đếm của nó. Kết quả là cụm từ “10 lớp trở lên”

chỉ xuất hiện trong cột C khi một nhân viên cụ thể đã vượt qua mốc mười hạng. Nó sẽ không xuất hiện đối với các trường hợp nhân viên học lớp thứ nhất, thứ hai, thứ ba và lớp thứ chín của họ.

Cuối cùng, bảng lớp nhân viên có thể được làm lạ mắt hơn một chút và bạn có thể xem chính xác mỗi nhân viên đã học bao nhiêu lớp. Nếu cột A và B vẫn chứa tên và lớp, bạn có thể đặt một phiên bản duy nhất của mỗi tên nhân viên trong các hàng đầu tiên của cột D, để trống cột C. Trong cột E, bạn có thể đặt công thức sau ở bên phải của mỗi tên nhân viên:

=COUNTIF(A:A,D2)

Kết quả là cột E chứa số trường hợp của tên trong cột D xuất hiện trong cột A. Nói cách khác, bạn có thể đếm xem mỗi nhân viên đã học bao nhiêu lớp. Sau đó, bạn có thể sử dụng định dạng có điều kiện trên các ô trong cột D để chúng được hiển thị bằng màu đỏ nếu số lượng trong cột E là 10 trở lên.

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

Mẹo này (2552) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: