Công thức chung
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Tóm lược
Để phá vỡ 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ị để chúng không chứa trùng lặp và do đó sẽ không dẫn đến 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 ràng buộc, vì dữ liệu chứa các bản sao. Một cách để phá vỡ mối quan hệ như thế này là thêm 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ị ban đầu.
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ẽ "thắng", nhưng bạn có thể điều chỉnh công thức để sử dụng logic phù hợp với tình huống và trường hợp sử dụng cụ thể của bạn.
Giải trình
Về cơ bản, 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 giá trị. Tham chiếu mở rộng được sử dụng để COUNTIFS trả về số lần xuất hiện đang chạy, thay vì 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 số lượng tất cả các giá trị không trùng lặp bằng 0) 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 nghiêm trọng đến giá trị ban đầu.
Trong ví dụ được hiển thị, cả Metrolux và Diamond đều có cùng ước tính là $ 5000. Vì Metrolux xuất hiện đầu tiên trong danh sách, số 5000 đang chạy 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 Kim cương, số lần chạy 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ị đã điều chỉnh được sử dụng để xếp hạng thay vì các giá trị ban đầu trong cột G và H. Công thức trong G5 là:
=SMALL($D$5:$D$12,F5)
Công thức trong H5:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Xem trang này để biết giải thích về các công thức này.
Cột trợ giúp tạm thời
Nếu bạn không muốn sử dụng cột trợ giúp trong giải pháp cuối cùng, bạn có thể tạm thời sử dụng cột trợ giúp để nhận các giá trị được tính toán, sau đó sử dụng Dán Đặc biệt để chuyển đổi các giá trị "tại chỗ" và xóa cột trợ giúp sau đó. Video này trình bày kỹ thuật.
