Giả sử rằng bạn muốn có thể đếm các mục duy nhất từ một danh sách, nhưng với một sự thay đổi. Và giả sử bạn đang làm việc với trang tính này:
Cột D đếm số hàng trong mỗi phần từ cột B và cột C đếm số phần duy nhất dựa trên năm ký tự đầu tiên của cột A cho phần đó. Các ô B2: B11 chứa ARG và bạn có thể đếm tám mục duy nhất trong năm ký tự đầu tiên của A2: A11 vì A7: A9 mỗi ô chứa 11158, vì vậy hai mục trùng lặp không được tính. Tương tự, số 5 trong D12 cho bạn biết có năm hàng cho BRD, nhưng trong hàng 12:16, có ba mục duy nhất trong năm ký tự đầu tiên, vì 11145 được lặp lại và 11173 được lặp lại.
Nhưng làm thế nào để bạn yêu cầu Excel làm điều này? Và bạn có thể sử dụng công thức nào trong C2 có thể được sao chép sang C12 và C17?
Công thức đếm đơn giản trong D2, =COUNTIF(B:B,B2)đếm số lần B2 (ARG) tồn tại trong cột B.
Bạn sử dụng cột trợ giúp để tách năm ký tự đầu tiên của cột A, như trong hình sau:
Tiếp theo, bạn cần chỉ ra rằng đối với ARG, bạn chỉ quan tâm đến các ô F2: F11 để tìm số lượng các mục duy nhất. Nói chung, bạn sẽ tìm thấy giá trị này bằng cách sử dụng công thức mảng được hiển thị trong hình này:
Bạn tạm thời sử dụng ô C3 chỉ để hiển thị công thức; bạn có thể thấy rằng nó không có ở C3 trong các hình trước. (Bạn sẽ sớm biết cách hoạt động của công thức này.)
Vậy công thức trong C2, C12 và C17 là gì? Câu trả lời đáng ngạc nhiên (và thú vị) được hiển thị trong hình này:
Ái chà! Cái này hoạt động ra sao?
Hãy xem phần Trả lời trong các tên được xác định trong hình này:
Đó là công thức tương tự từ một hình trước đó, nhưng thay vì sử dụng phạm vi F2: F11, nó sử dụng phạm vi có tên Rg. Ngoài ra, công thức là một công thức mảng, nhưng các công thức được đặt tên được coi như thể chúng là công thức mảng! Tức là, =Answerkhông được nhập bằng Ctrl + Shift + Enter mà chỉ được nhập như bình thường.
Vậy Rg được định nghĩa như thế nào? Nếu ô C1 được chọn (đây là bước quan trọng để hiểu thủ thuật này), thì nó được định nghĩa như trong hình sau:
Đó là =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).
Loan_Details là tên của trang tính, nhưng bạn có thể nhìn vào công thức này mà không có tên trang tính dài. Một cách dễ dàng để làm điều này là tạm thời đặt tên trang tính đơn giản, chẳng hạn như x, và sau đó xem lại tên đã xác định:
Công thức này dễ đọc hơn!
Bạn có thể thấy rằng công thức này khớp với $ B1 (lưu ý tham chiếu tương đối đến hàng hiện tại) với tất cả cột B và số trừ 1. Bạn trừ 1 vì bạn đang sử dụng OFFSET từ F1. Bây giờ bạn đã biết về công thức của C, hãy xem công thức cho C2:
Phần MATCH($B2,$B:$B,0)của công thức là 2, vì vậy công thức (không có tham chiếu đến tên trang tính) là:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
hoặc là:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
hoặc là:
=OFFSET($F$1,1,0,10,1)
Bởi vì COUNTIF($B:$B,$B2)là 10, có 10 ARG. Đây là phạm vi F2: F11. Thực tế là, nếu ô C2 được chọn và bạn nhấn F5 để chuyển đến Rg, bạn sẽ thấy như sau:
Nếu ô bắt đầu là C12, nhấn F5 để chuyển đến Rg sẽ tạo ra điều này:
Vì vậy, bây giờ, với Câu trả lời được định nghĩa là =SUM(1/COUNTIF(rg,rg)), bạn đã hoàn tất!
Hãy xem xét kỹ hơn cách thức hoạt động của công thức này, sử dụng một ví dụ đơn giản hơn nhiều. Thông thường, cú pháp của COUNTIF là =COUNTIF(range,criteria), chẳng hạn như =COUNTIF(C1:C10, "b")trong hình này:
Điều này sẽ cho 2 là số b trong phạm vi. Nhưng việc vượt qua chính phạm vi làm tiêu chí sẽ sử dụng từng mục trong phạm vi làm tiêu chí. Nếu bạn đánh dấu phần này của công thức:
và nhấn F9, bạn sẽ thấy:
Mỗi mục trong phạm vi được đánh giá và chuỗi số này có nghĩa là có một a và có hai b, ba c và bốn d. Các số này được chia thành 1, cho 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, như bạn có thể thấy ở đây:
Vì vậy, bạn có 2 nửa, 3 phần ba, 4 phần tư và 1 toàn bộ, và cộng chúng lại với nhau sẽ thu được 4. Nếu một mục được lặp lại 7 lần, thì bạn có 7 phần bảy, v.v. Tuyệt đấy! (Cảm ơn David Hager vì đã khám phá / phát minh ra công thức này.)
Nhưng hãy giữ trong một phút. Như hiện tại, bạn chỉ phải nhập công thức này trong C2, C12 và C17. Sẽ không tốt hơn nếu bạn có thể nhập nó vào C2 và điền xuống và chỉ hiển thị nó trong các ô đúng? Trong thực tế, bạn có thể làm điều này. Bạn có thể sửa đổi công thức trong C2 thành =IF(B1B2,Answer,"")và khi bạn điền vào đó, nó sẽ thực hiện công việc:
Nhưng tại sao lại dừng ở đây? Tại sao không tạo công thức thành một công thức được đặt tên, như được hiển thị ở đây:
Để điều này hoạt động, ô C2 phải là ô hiện hoạt (hoặc công thức sẽ cần phải khác). Bây giờ bạn có thể thay thế các công thức của cột C bằng =Answer2:
Bạn có thể thấy rằng C3 có =Answer2, cũng như tất cả các ô trong cột C. Tại sao không tiếp tục điều này trong cột D? Công thức trong D2, sau khi cũng áp dụng so sánh với B1 và B2, được hiển thị ở đây:
Vì vậy, nếu bạn giữ ô D2 được chọn và xác định một công thức khác, hãy nói Answer3:
thì bạn có thể nhập =Answer3vào ô D2 và điền vào:
Đây là phần trên cùng của trang tính, với các công thức hiển thị, theo sau là cùng một ảnh chụp màn hình với các giá trị hiển thị:
Khi người khác cố gắng tìm ra điều này, họ có thể sẽ vò đầu bứt tai!
Bài viết của khách này là từ Excel MVP Bob Umlas. Nó là từ cuốn sách, Thêm Excel Bên ngoài Hộp. Để xem các chủ đề khác trong cuốn sách, bấm vào đây.
