Phá vỡ quan hệ với cột trợ giúp và COUNTIF
Công thức chung
= A1 ( COUNTIF ( exp_rng , A1 ) – 1 ) * điều chỉnh
Mời bạn xem thêm:
>> mẫu d01-ts bảng kê thông tin
>> mục tiêu nghề nghiệp ngân hàng
>> mau bien ban doi chieu cong no
Giải trình
Để phá vỡ các mối quan hệ, bạn có thể sử dụng cột trợ giúp và hàm COUNTIF để điều chỉnh các giá trị sao cho chúng không chứa các bản sao và do đó sẽ không dẫn đến các mối quan hệ. Trong ví dụ được hiển thị, công thức trong D5 là:
= C5 ( COUNTIF ( $ C $ 5: C5 , C5 ) – 1 ) * 0,01
Bối cảnh
Đôi khi, khi bạn sử dụng các hàm như SMALL, LARGE hoặc RANK để xếp hạng các giá trị cao nhất hoặc thấp nhất, bạn kết thúc với các mối quan hệ, bởi vì dữ liệu chứa các bản sao. Một cách để phá vỡ các mối quan hệ như thế này là thêm một cột trợ giúp với các giá trị đã được điều chỉnh, sau đó xếp hạng các giá trị đó thay vì các giá trị gốc.
Trong ví dụ này, logic được sử dụng để điều chỉnh giá trị là ngẫu nhiên – giá trị trùng lặp đầu tiên sẽ “giành được”, nhưng bạn có thể điều chỉnh công thức để sử dụng logic phù hợp với trường hợp cụ thể và trường hợp sử dụng của bạn.
Cách thức hoạt động của công thức này
Ở cốt lõi, công thức này sử dụng hàm COUNTIF và phạm vi mở rộng để đếm số lần xuất hiện của các giá trị. Các tài liệu tham khảo mở rộng được sử dụng để COUNTIFS trả về một số hoạt động của lần xuất hiện , thay vì một tổng số cho mỗi giá trị:
COUNTIF ( $ C $ 5: C5 , C5 )
Tiếp theo, 1 được trừ khỏi kết quả (làm cho tổng số của tất cả các giá trị không trùng lặp bằng không) và kết quả được nhân với 0,01. Giá trị này là “điều chỉnh” và cố ý nhỏ để không ảnh hưởng trọng yếu đến giá trị ban đầu.
Trong ví dụ được hiển thị, Metrolux và Diamond đều có cùng mức ước tính là 5000 đô la. Vì Metrolux xuất hiện đầu tiên trong danh sách, số đếm 5000 là 1 và bị hủy bỏ bằng cách trừ đi 1, vì vậy ước tính vẫn không thay đổi trong cột trợ giúp:
= C8 ( COUNTIF ( $ C $ 5: C8 , C8 ) – 1 ) * 0,01
= C8 ( 1 – 1 ) * 0,01
= C8 0
= C8
Tuy nhiên, đối với Diamond, số lần chạy là 5000 là 2, vì vậy ước tính được điều chỉnh:
= C11 ( COUNTIF ( $ C $ 5: C11 , C11 ) – 1 ) * 0,01
= C11 ( 2 – 1 ) * 0,01
= C11 1 * 0,01
= C11 0,01
Cuối cùng, các giá trị được điều chỉnh được sử dụng để xếp hạng thay vì các giá trị ban đầu trong các cột G và H. Công thức trong G5 là:
= NHỎ ( $ D $ 5: $ D $ 12 , F5 )
Công thức trong H5:
= INDEX ( $ B $ 5: $ B $ 12 , MATCH ( G5 , $ D $ 5: $ D $ 12 , 0 ))